SQL语法
O. SQL语句不区分大小写,可以单行或多行书写，以分号结尾
O. 单行注释:#注释内容, 多行注释:*注释内容*
SQL分类:DDL、DML、DQL、DCL
DDL:用来定义数据库对象(数据库，表，字段),该部分可用软件操作,比如
Sqlyog,Navicat,DataGrip等工具都可
DML:用来表数据增、删、改操作
O,添加数据
给指定字段添加数据 insert into user(id,name) values(1,'aile');
给全部字段添加数据 insert into user values(1,'aile','男',18);
批量添加数据 insert into user values(1,'aile','男',18),(2,'mike','男',25);
O,修改数据 update user set name = 'aile' where id = 1;
O,删除数据 delete from user where gender = '男';
DQL:用来查询数据库中表的记录
O,总体结构 SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表
HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
O,基础查询 select distinct address '家庭住址' from user;
O,条件查询 select * from user where gender = '男' and age < 24;
BETWEEN .AND . 范围之内(含最小、最大值)
IN(.) 在in之后的列表中的值，多选一
LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL 是NULL
AND 或 && 并且 (多个条件同时成立)
OR 或 || 或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是
O,聚合函数 select avg(age) from user;
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
O,分组查询 查询年龄小于20的员工 , 并根据地址分组 , 获取员工数量大于等于3的地址
select address, count(*) address_count from aile where age < 20 group by address having address_count >= 3;
O,排序查询 select * from aile order by age desc;
O,分页查询 select * from aile limit 1,10;
DCL:数据控制语言,用来管理数据库用户、控制数据库的访问权限
函数
O,字符串函数 update aile set workno = lpad(workno, 5, '0');
CONCAT(S1,S2,...Sn) 字符串拼接，将S1，S2，... Sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
O,数值函数 select round(2.344,2);
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值，保留y位小数
O,日期函数 select name, datediff(curdate(), entrydate) as 'entrydays' from aile order by entrydays desc;
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date, INTERVAL exprtype) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数
O,流程函数
IF(value , t , f)
如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)
如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] ...ELSE [ default ] END
如果val1为true，返回res1，... 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN[res1] ... ELSE [ default ] END
如果expr的值等于val1，返回res1，... 否则返回default默认值
多表查询--员工表emp和部门表dept,其中左外连接和右外连接是可以相互替换的,常用左外连接
O,内连接: 查询两张表交集部分的数据
例:查询每一个员工的姓名,及关联的部门的名称
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
O,左外连接: 查询表1(左表)的所有数据，也包含表1和表2交集部分的数据
例:查询emp表的所有数据, 和对应的部门信息
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
O,右外连接: 查询表2(右表)的所有数据，也包含表1和表2交集部分的数据
例:查询dept表的所有数据, 和对应的员工信息
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
O,自连接: 自己连接自己，也就是把一张表连接查询多次,必须用别名
例:查询所有员工emp及其领导的名字 emp,如果员工没有领导,也需要查询出来
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;
O,联合查询 把多次查询的结果合并起来，形成一个新的查询结果集
例:将薪资高于10000的员工 , 和年龄小于30岁的员工全部查询出来.
select * from emp where salary > 10000 union all select * from emp where age < 30;
O,子查询 嵌套SELECT语句，称为嵌套查询，又称子查询
标量子查询（子查询结果为单个值）,常用的操作符：= <> > >= < <=
例:根据 "销售部" 部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
列子查询(子查询结果为一列,一个字段),常用的操作符
IN 在指定的集合范围之内，多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中，有任意一个满足即可
SOME 与ANY等同，使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足
例:查询比 财务部所有人工资 都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );
行子查询(子查询结果为一行,多个字段),常用的操作符：= 、<> 、IN 、NOT IN
例:查询与 "aile" 的薪资及直属领导 相同的员工信息
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'aile');
表子查询(子查询结果为多行多列),常用的操作符：IN
例:查询与 "aile" , "mike" 的职位和薪资相同的员工信息
select job, salary from emp where name = 'aile' or name = 'mike';
事务 把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么成功，要么失败
控制事务一
设置事务 SET @@autocommit = 0 ;
交事务 COMMIT;
回滚事务 ROLLBACK;
控制事务二
开启事务 START TRANSACTION 或 BEGIN ;
提交事务 COMMIT;
回滚事务 ROLLBACK;
索引 高效获取数据的数据结构(有序),实现快速查询,但是DML效率较低
O,索引分类
分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建, 只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT
创建普通索引 CREATE INDEX idx_name ON aile(name);
创建唯一索引 CREATE UNIQUE INDEX idx_phone ON aile(phone);
创建联合索引 CREATE INDEX idx_pro_age_sta ON aile(profession,age,status);
查看所有的索引 show index from aile;
性能分析
查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 SHOW GLOBAL STATUS LIKE 'Com_______';
慢查询日志 记录了所有执行时间超过指定参数的所有SQL语句的日志
profile详情 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
explain 获取如何执行SELECT 语句的信息
索引失效情况
O,索引列要是运算会失效
O,字符串不加引号会失效
O,头部模糊匹配会失效
O,用or连接的字段要是某个字段没有索引,其他有索引,也会失效
SQL优化
插入优化
批量插入多条数据: Insert into tb_aile values(1,'aile'),(2,'mike');
大批量插入数据 通过load命令,使用加载文件快速导入
O,连接数据库 mysql –-local-infile -u root -p
O,设置导入开关 set global local_infile = 1;
O,load加载数据 load data local infile '/root/load_aile.log' into table tb_aile
fields terminated by ',' lines terminated by '\n' ;
主键优化
O,降低主键的长度
O,使用AUTO_INCREMENT自增主键
O,尽量不要使用UUID做主键或者是其他自然主键
O,避免对主键的修改
order by优化
O,多字段排序时，也遵循最左前缀法则
O,尽量使用覆盖索引
O,多字段排序, 一个升序一个降序，此时需要联合索引
group by优化
O,在分组操作时,通过索引来提高效率
O,在分组操作时,索引的使用也是满足最左前缀法则的
limit优化
O,通过覆盖索引+子查询进行优化
count优化
O,count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)
update优化
O,针对索引的更改速度最快,比其他字段
视图
只有查询SQL语句,动态生成的,重复多次使用的,安全的虚拟表
创建视图 create or replace view aile_v as select id,name from aile where id >0;
查询视图 show create view aile_v;
select * from aile_v;
修改视图 alter view aile_v as select id,name from aile where id > 0;
删除视图 drop view if exists aile_v;
存储过程
事先编译并存储的一段可直接复用,可传参,可返回,高效率SQL语句集合
创建 create procedure p1()
begin
select count(*) from aile;
end;
调用 call p1();
查看 show create procedure p1;
删除 drop procedure if exists p1;
变量
O,系统变量 服务器提供,属于服务器层面.分为全局变量（GLOBAL）、会话变量（SESSION）
查看系统变量 show session variables ;
设置系统变量 set session autocommit = 1;
O,用户定义变量 当前连接,用户根据需要自己定义的变量
赋值 set @myname = 'aile';
使用 select @myname;
O,局部变量 根据定义局部生效的变量,访问需要DECLARE声明,在存储过程BEGIN...END块使用
create procedure p2()
begin
declare stu_count int default 0;--声明
select count(*) into stu_count from student;--赋值
select stu_count;//使用
end;
call p2();--调用
if(条件判断)和参数(in,out,inout)
--案例1
create procedure p4(in score int, out result varchar(10))--in,输入参数,out,输出参数
begin
if score >= 85 then
set result = '优秀';
elseif score >= 60 then
set result = '及格';
else
set result = '不及格';
end if;
end;
call p4(18, @result);--调用存储过程p4
select @result;--输出结果
--案例2
create procedure p5(inout score double)--inout,既是输入参数,又是输出参数
begin
set score = score * 0.5;
end;
set @score = 198;--设置输入
call p5(@score);--调用存储过程p5
select @score;--输出结果
case(流程控制)
create procedure p6(in age int)
begin
declare result varchar(10);
case
when age <=1 then
set result = '婴儿';
when age > 1 and age <= 3 then
set result = '幼儿';
when age > 3 and age <= 6 then
set result = '小童';
when age > 6 and age <= 12 then
set result = '儿童';
else
set result := '非法参数';
end case ;
select concat('您输入的年龄为: ',age, ', 所属的时期为: ',result);--输出结果
end;
call p6(16);--调用存储过程p6
while(循环控制)
create procedure p7(in n int)--in,输入参数
begin
declare total int default 0;--声明
while n>0 do--循环条件
set total = total + n;--赋值
set n = n - 1;
end while;
select total;--使用
end;
call p7(100);
repeat(先执行再循环),可参照上
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;
select total;
end;
call p8(100);
loop(灵活循环,退出循环,进入循环)
create procedure p10(in n int)
begin
declare total int default 0;--声明
sum:loop
if n<=0 then
leave sum;--满足条件退出循环sum
end if;
if n%2 = 1 then
set n = n - 1;
iterate sum;--满足条件,进入循环sum
end if;
set total = total + n;--赋值
set n = n - 1;
end loop sum;
select total;--使用
end;
call p10(100);--调用存储过程p10
游标 用来存储查询结果集的数据类型,用在存储过程中
声明 DECLARE 游标名称 CURSOR FOR 查询语句 ;
打开 OPEN 游标名称 ;
获取 FETCH 游标名称 INTO 变量 [, 变量 ] ;
关闭 CLOSE 游标名称 ;
Handler 用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤,异常处理
Cusor+handler+procedure案例:
create procedure p12(in uage int)
begin
declare uname varchar(100);--声明局部变量
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=uage;--声明游标
declare exit handler for not found close u_cursor;--当SQL语句执行抛出异常，将关闭游标u_cursor并退出
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(--新建新表tb_user_pro
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;--打开游标
while true do
fetch u_cursor into uname,upro;--获取游标记录,对应字段name=uname,profession=upro
insert into tb_user_pro values (null, uname, upro);--插入数据到tb_user_pro
end while;
close u_cursor;--关闭游标
end;
call p12(30);--调用存储过程
触发器
在增,删,改之前或之后触发并执行定义的SQL语句集合,保证数据完整性,数据检验
案例:
通过触发器记录 tb_aile 表的数据变更日志，将变更日志插入到日志表aile_logs中, 包含增加,修改 , 删除
日志表
create table aile_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_params varchar(500) comment '操作参数',
primary key(`id`)
)engine=innodb default charset=utf8;
插入触发器
create trigger tb_aile_insert_trigger
after insert on tb_aile for each row
begin
insert into
aile_logs(id, operation,operate_time,operate_params)
VALUES
(null, 'insert', now(), concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=', NEW.phone));
end;
插入触发器测试
-- 查看
show triggers ;
-- 插入数据到tb_aile
insert into
tb_aile(id, name, phone)
VALUES
(26,'三皇子','18809091212');
修改触发器
create trigger tb_aile_update_trigger
after update on tb_aile for each row
begin
insert into
aile_logs(id, operation, operate_time, operate_params)
VALUES
(null, 'update', now(), concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone'
| 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',new.phone));
end;
修改触发器测试
-- 查看
show triggers ;
-- 更新
update tb_aile set name = 'aile' where id = 12;
删除触发器
create trigger tb_aile_delete_trigger
after delete on tb_aile for each row
begin
insert into
aile_logs(id, operation, operate_time, operate_params)
VALUES
(null, 'delete', now(), concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone));
end;
删除触发器测试
-- 查看
show triggers ;
-- 删除数据
delete from tb_aile where id = 12;
锁
计算机协调多个进程或线程并发访问某一资源的机制,保证数据并发访问的一致性、有效性,有全局锁,表级锁,行级锁