数据库的超级用户是root
一、注释
（1）“-- ”减号减号空格 注意不要省略空格
（2）“#” 井号
二、数据库操作
1、创建
CREATE DATABASE [IF NOT EXISTS] <数据库名> [CHARACTER SET utf8]2、删除
DROP DATABASE <数据库名>3、查看
（1）查看服务中心所有的数据库
SHOW DATABASES;（2）查看数据库创建细节
SHOW CREATE DATABASE <数据库名>;4、选择
USE <数据库名>三、表
表是数据库中存储数据的主体
1、创建
创建表 表名 t_user user
create table staff(
-- 表中字段
-- 主键 是表中数据唯一的标识 不可重复 不可为null
-- auto_increment在插入数据时，该列的值可以不指定，会自动递增
id int auto_increment PRIMARY KEY,
-- varchar(最大长度) 可变字符串类型
code varchar(20) not null,
name varchar(20) not null,
-- 数值一共存储10位，其中两位用来存储小数
salary DECIMAL(10,2)
)2、修改
（1）重命名表
alter table staff rename to t_staff;
alter table t_staff rename as staff;to和as均可
（2） 设置编码
mysql8 utf8 mb3 mb4 maxbyte
不同的字符占用的空间是不一样的
数字、字母欧洲部分语言字符 占1个字节
中东、阿拉伯语言 占2个字节
中文、韩文、日文、东南亚地区字符 占3个字节
alter table staff character set utf8mb3;（3） 添加列
alter table staff add column sex varchar(2);（4） 重新定义列
alter table staff modify sex varchar(20) not null;（5）定义列的位置
#放在name属性列后
alter table staff modify sex varchar(20) not null after name;
#放在第一个
alter table staff modify sex varchar(20) not null first;（6）列重命名
alter table staff change sex age int not null;（7）删除列表
alter table staff drop column age;（8）表名、列名与关键词相同时的处理
在起名时 表名经常会用到一些关键字 name 使用`标识
CREATE TABLE `table`(
`varchar` varchar(20)
)3、 删除
drop table `table`;
四、表数据操作
1、DML 数据管理语言 增删改
（1）新增
指定列添加数据
insert into staff(id,code,name,salary) value(2,'1002','李四',8000);自动递增列可以不指定
insert into staff(code,name,salary) values('1003','王五',9000),('1004','赵六',11000);
自动递增列不会回撤 不会补齐 从该列最大值开始递增
null列可以不指定数据 但not null列必须指定数据
insert into staff(code,name) value('1005','杜甫'); 可以不指定列,但是需要将全部列指定数据
insert into staff value(6,'1006','王维',3000);（2）删除
删除表中数据 delete
delete from staff where id=6;
清空表
delete from staff;-- 删除掉每一行数据
truncate staff;-- 清空表 性能更快一些（3）修改
修改/编辑 UPDAT
update staff set name='李清照' where id=4;
update staff set salary=salary+2000 where name='李清照';
update staff set name='孟浩然',salary=salary-2000 where id=6;2、查询 DQL数据查询语言
select 1;
select now();（1）查询表格
①指定列名查询
select name,salary from staff;
可以用*代替表中所有的列
select * from staff;使用as 指定别名 列 方法结果 表 视图
select name as '姓名' from staff;②where 指定条件语句
select * from staff where id=5;不等于
select * from staff where id!=1;
select * from staff where id<>1;大于小于
select * from staff where id<2;
select * from staff where id>=3; ③对null值的判断
select * from staff where salary=null;#不对等于null判断
select * from staff where salary is null;
select * from staff where salary <=> null;不等于null判断
select * from staff where salary is not null;④多条件查询
select * from staff where id<100 and salary>=10000;
select * from staff where salary>=10000 or salary<=5000;⑤in not in
select * from staff where id in(1,3,5,7,8,10,12);
select * from staff where id not in(1,3,5,7,8,10,12);⑥between and
select * from staff where salary between 5000 and 10000;⑦模糊查询 like
like %代表任意个数任意字符 _代表有且只有一个字符
select * from staff where name like '%a%';
-- 查询姓张的员工
select * from staff where name like '张%';
-- 张某
select * from staff where name like '张_';
-- 张某某
select * from staff where name like '张__';⑧是否存在 exists
如果子查询有结果那么父查询正常查询,如果子查询没有结果那么父查询不返回数据
子查询中的sql语句可以与父查询的无关
select * from staff where exists (select * from staff where id=2);⑨any all
select *
from staff
where salary > any(select code from staff);#大于任何一个select *
from staff
where salary > all(select code from staff);#大于所有（2）排序 order by
select * from staff order by salary;正序 asc 倒序 desc
select * from staff order by salary asc;
select * from staff order by salary desc;select * from staff order by salary asc,code desc;-- salary相同,按code倒序
select * from staff order by salary,code desc;-- 默认为正序（3）拼接(合并)查询结果
拼接两方查询的列数要一样
select name,salary from staff union
select code,name from staff;union会将重复的结果去重
select name,salary from staff union
select name,salary from staff;
union all 不会去重
select name,salary from staff union all
select name,salary from staff;（4）去重
对整个查询结果去重
select distinct salary,name from staff;（5）部分查询
select * from staff order by salary desc limit 3;limit start,count;
-- 查询第三到第五名
select * from staff order by salary desc limit 2,3; （6）case when then
when后面可以是范围，也可以是固定值
select *,case
when salary<10000 then '薪资较低'
when salary>=10000 and salary<=12000 then '薪资正常'
else '薪资较高'
end
from staff;select * ,case salary when 1000 then '有点低了'
when 8000 then '不错'
when 9000 then '还行'
when 10000 then '还可以'
when 13000 then '哇呜'
end as 'level' from staff;（7）聚合函数
将多个数据聚合成一个数据的函数
-- 最大值
select max(salary) from staff;
-- 最小值
select min(salary) from staff;
-- 平均数
select avg(salary) from staff;
-- 求和
select sum(salary) from staff;
-- 个数
select count(salary) from staff;聚合函数都会忽略null
select department,avg(salary),count(salary),count(*),count(1)
from staff group by department;count(*)和count(常数)都是计算表中每组数据的条数
（8）分组
①分组查询
只能查询分组依据列和聚合函数
select department,avg(salary) as '平均薪资',sum(salary) as '薪资总和'
from staff
group by department;②分组筛选
having 对分组之后的数据再筛选
select department from staff group by department
having avg(salary)<9000;3、连接查询
（1）外连接
包括
左外连接 left join
右外连接 right join
全外连接 full join(mysql不支持)
①左外连接
select a.tname,b.cname from teacher as a
left join
course as b on a.tid=b.tid;左连接以左表为主表,会显示所有的数据,右表为附表,只会显示和主表有关的数据
右连接相反
常用左连接
②右外连接
select a.tname,b.cname from teacher as a
right join
course as b on a.tid=b.tid;③实现全外连接
select a.tname,b.cname from teacher as a
left join
course as b on a.tid=b.tid
union
select a.tname,b.cname from teacher as a
right join
course as b on a.tid=b.tid;（2）内连接
inner join 也可直接写为join 只显示有对应关系的数据
select a.tname,b.cname from teacher as a
join
course as b on a.tid=b.tid;（3）交叉连接 cross join
select * from teacher as a,course as b where a.tid=b.tid;4、子查询
-- 查询有不及格成绩的学生姓名
select sname from student where sid in(select sid from sc where score<60
);将子查询当做表进行查询
select sname from(select * from student where sid=01) as b;5、行列转换
使用case when then 实现 按name分组并用sum()或max()合并
select name,
sum(case Subject when '语文' then Fraction
else 0
END) '语文',
sum(case Subject when '数学' then Fraction
else 0
END)'数学',
sum(case Subject when '英语' then Fraction
else 0
END) '英语'
from t_score GROUP BY name;使用if函数实现 按name分组并用sum()或max()合并，若用null填充也可使用avg()合并
SELECT name,
avg(if(Subject='语文',fraction,null)) '语文',
avg(if(Subject='数学',fraction,null)) '数学',
avg(if(Subject='英语',fraction,null)) '英语'
FROM t_score GROUP BY name;五、 数据类型
常用的数据类型
整数类型 TINYINT(1字节) SMALLINT(2字节) MEDIUMINT(3字节) INT(4字节)
BIGINT(8字节)
浮点型 double(总长度,小数位数)(8字节) FLOAT(4字节) DECIMAL
字符串 char varchar text LONGTEXT(不常用)
char 最大长度255个字符 默认为1个字符
varchar 能够存储的字节数是65535
1.结构varchar类型的第一个字节不存储数据
2.varchar 前两个字节存储数据长度
3.有效位就剩下65532字节 编码格式决定能够存储多少个字符
4.行中列的总字节长度不能超过65535
如果要存储长文本 使用text类型
text 长文本 不需要设置长度 用于存储大量的文本数据，如文章、评论等
char和varchar的区别
1.char 是定长的 varchar 是变长的
char(20) 不论是存了abc还是a 都占20个字符位
varchar(20) 变长的 存abc 占3个字符位
2.char的性能更好 varchar稍逊 因为要计算字符位
3.场景:身份证号 学号 手机号 订单号 char
介绍信息 店名 姓名 地址 varchar
4.存储范围 char最大255个字符 varchar计算出来的
日期
date 年月日 YYYY-MM-DD time 时分秒 HH:MM:SS 可包含秒的小数部分
datetime 年月日时分秒 YYYY-MM-DD HH:MM:SS
六、视图 view
是一个已经编译好的SQL语句 视图中不存储数据 数据还是储存在表中
要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
1、创建 v_
create view v_student_score as
select a.sname,c.cname,b.score from student a
LEFT JOIN sc b on a.sid=b.SId
left JOIN course c on c.cid=b.CId;2、 删除
drop VIEW v_student_score;3、使用
使用上类似于表
SELECT * from v_student_score;4、优点
- 定制用户数据，聚焦特定的数据。不同的用户可能对不同的数据有不同的要求
- 简化数据操作。在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。
- 提高数据的安全性。视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。
- 共享所需数据。通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。
- 更改数据格式。通过使用视图，可以重新格式化检索出的数据
七、触发器 trigger
相当于一个事件,一旦表中发生了指定的事件,该触发器就会自动运行
触发器只对三种操作触发 增删改
触发时机 before after
尽量不用触发器 会影响正常业务逻辑 可以用java逻辑代码完成触发器的工作
1、创建
create trigger tgg_i_a_student after insert
on student for each row BEGIN
UPDATE log set val=(SELECT COUNT(*) from student) where `key`='studentcount';
END;其中student是学生表、log是日志表
create trigger tgg_u_b_student BEFORE UPDATE
on student for each row BEGIN-- old.列名-- new.列名
update log set val=CONCAT(CONCAT('{',old.sid,',',old.sname,'}'),'->',
CONCAT('{',new.sid,',',new.sname,'}'))
WHERE `key`='lastupdate';
end;old.列名：在UPDATE和DELETE触发器中，
old关键字用于访问受触发器影响的行在触发器
操作之前的状态。
new.列名：在INSERT和UPDATE触发器中，new关键字用于访问受触发器影响的行在触发器
操作之后的状态。
2、删除
drop trigger tgg_u_b_student;八、函数
1、自定义函数
create function method(score int)returns varchar(20)
BEGIN
-- 判断score的数值 60分以上及格 否则不及格-- 定义一个变量记录返回结果DECLARE result VARCHAR(20); #DEFAULT ''IF score >=60 THEN-- 对变量赋值 两种语法SET result='及格';ELSESET result='不及格';END IF;RETURN result;END;调用以上函数
SELECT method(50);SELECT *, method(score) from sc;2、常用的内置函数
（1）数学函数
- ABS(x) 返回x的绝对值
- BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
- CEILING(x)和CEIL(x) 返回大于x的最小整数值
- EXP(x) 返回值e（自然对数的底）的x次方
- FLOOR(x) 返回小于x的最大整数值
- GREATEST(x1,x2,...,xn)返回集合中最大的值
- LEAST(x1,x2,...,xn) 返回集合中最小的值
- LN(x) 返回x的自然对数
- LOG(x,y)返回x的以y为底的对数
- MOD(x,y) 返回x/y的模（余数）
- PI()返回pi的值（圆周率）
- POW(x,y)和POWER(x,y) x的y次方的结果值
- RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
- ROUND(x,y)返回参数x的四舍五入的有y位小数的值
- SIN(x) 求正弦值
- ASIN(x) 求反正弦值,与SIN互为反函数
- COS(x) 求余弦值
- ACOS(x) 求反余弦值,与COS互为反函数
- TAN(x) 求正切值
- ATAN(x) 求反正切值,与TAN互为反函数
- COT(x) 求余切值
- SIGN(x) 返回代表数字x的符号的值
- SQRT(x) 返回一个数的平方根
- TRUNCATE(x,y) 返回数字x截短为y位小数的结果
(2)字符串函数
ASCII(char) 返回字符的ASCII码值
BIT_LENGTH(str) 返回字符串的比特长度
CHAR_LENGTH(str) 返回字符串 str 的字符数
CONCAT(s1,s2...,sn) 将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x) 返回字符串str中最左边的x个字符
LENGTH(s) 返回字符串str中的字节数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr) 返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2) 比较字符串s1和s2
TRIM(str) 去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
(3)日期和时间函数
- CURDATE()或CURRENT_DATE() 返回当前的日期
- CURTIME()或CURRENT_TIME() 返回当前的时间
- DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
- DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
- DATE_SUB(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
- DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
- DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
- DAYOFYEAR(date) 返回date是一年的第几天(1~366)
- DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
- FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
- HOUR(time) 返回time的小时值(0~23)
- MINUTE(time) 返回time的分钟值(0~59)
- MONTH(date) 返回date的月份值(1~12)
- MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
- NOW() 返回当前的日期和时间
- QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
- WEEK(date) 返回日期date为一年中第几周(0~53)
- YEAR(date) 返回日期date的年份(1000~9999)
- SYSDATE() 返回当前日期和时间 SELECT SYSDATE() -> 2018-09-19 20:57:43
部分函数调用代码:
SELECT floor(12.99);SELECT CHAR_LENGTH('你好'),LENGTH('你好');SELECT left('123456',3),right('123456',3);SELECT CONCAT(left('13366669999',3),'****',RIGHT('13366669999',4));SELECT trim(' 123 ');SELECT REPLACE('123123123','1','A');SELECT SUBSTR('abcdefg' FROM 2 FOR 3);SELECT SUBSTR('abcdefg' FROM 4);SELECT REVERSE('abcdefghi');SELECT NOW(),SYSDATE();SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s');九、存储过程 procedure
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
参数有in，out，inout三种
可以返回参数和结果集
1、创建
create PROCEDURE pro_insert_student_log(in num int)
BEGIN-- 循环语句 loop 死循环 只能用leave跳出循环DECLARE i int DEFAULT 0;DECLARE stuname varchar(20);DECLARE randomS int;aa: LOOP-- 获取学生的名字 生成随机数SELECT sname into stuname from student limit i,1;SET randomS=floor(RAND()*100);-- 插入log表insert into log value(stuname,randomS);SET i=i+1;IF i>=num THENLEAVE aa;END IF;END LOOP aa;
END2、调用
call pro_insert_student_log(3);3、删除
drop PROCEDURE pro_insert_student_log;十、事务（未完）
定义： 一组有序的不可分割的数据库操作的集合
1、ACID四大特性
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：事务执行前后, 数据库状态保持一致。以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
2、执行事务的语法和流程
- 开始事务 BEGIN;或者START TRANSACTION;
- 提交事务 COMMIT;
- 回滚（撤销）事务 ROLLBACK;
BEGIN;DELETE from student;SELECT * from student;ROLLBACK;
3、并发访问数据混乱
- 脏读 一个事务读取到了另一个事务修改未提交的记录 例如:数据表中一条记录值为v1，事务A执行, 将值改为v2, 但并没有提交, 此时事务B读取, 如果读取到的记录值为v2,则为脏读
- 幻读 当事务不是独立执行时出现的一种现象 例如:A事务读取或检索了多条数据,B事务添加或删除了一条新的数据 并且提交了B事务,A事务再去读取相同条件的内容,就会读取到B事务中新提交的数据,好像出现了幻觉一样 即为幻读
- 不可重复读 一个事务两次读取的记录数据不一致 例如: 事务A开启, 查找数据表记录r1, 并未提交, 此时事务B修改记录r1, 并提交, 事务A再次查找数据表记录r1, 如果两次得到的r1不一致, 即为不可重复读
4、事务的隔离级别（未完）
十一、索引（未完）
十二、三范式
- 1NF 每一列的数据是不可分割的 关系中元组中的分量不可再分
- 2NF 每一列数据完全依赖主键(不可以部分依赖) 每一个非主属性完全函数依赖于任何一个候选码(非主属性一般表示不属于任何候选码中的属性，选定主码后，其余属性即为非主属性。)
- 3NF 不可以传递依赖 每一个非主属性既不传递依赖于码，也不部分依赖于码。
- BCNF (修正的第三范式) 每一个决定因素都包含码
