Mysql (Day 14)

• SQL语言

• DDL 数据定义语言
• DML 数据管理语言 (增删改)
• DQL 数据查询语言
• DCL 数据控制语言

一、DML 数据管理语言 (增删改)

1.1 增加  insert into

• -- 指定列添加数据  insert into 表格（列1，列2……）value（值1，值2……）

insert into staff(id,code,name,salary) value (2,'10002','lisi',8500.00);

• -- 自动递增列可以不指定  insert into 表格(列1,列2……)  values  (值1,值2……)(值1,值2……）

insert into staff(code,name,salary)  values ('10003','wangwu',10000),('10004','zhaoliu',11000);

• --特点： 自动递增列不会回撤，不会补齐（从该列最大值开使递增）
• 如果删去之后id为2和3行元素,id后依旧从4开始;如果此时我定义id为100，此时下一个id是101
• -- 如果有一列 可以为空（null），插入数据时可以不插入元素
• -- not null 修饰的列必须要指定数据

insert into staff(id,code,name) value (5,'10005','test');

• -- 可以不指定列，但需要将全部列指定数据（列的顺序一定要和原来一致）

insert into staff value (7,'10006','王维',9500.00);

1.2 删除表内元素 delete

• -- 删除表内数据  delete
• -- 删除表，数据库 drop

1. 清空表格

   delete from 表名;   删除每一行数据（后边可以跟where条件）
   truncate 表名; 

delete from staff  where id=5;

        2.truncate，delete，drop 三者区别

• truncate：删除表中的所有数据，但保留表的结构（即表的定义、列、索引等）；无法带 WHERE 子句，无法选择性地删除数据。通常不能回滚

• TRUNCATE TABLE 表名;

• delete：删除表中的数据行，但保留表的结构，可以带 WHERE 子句，用于指定删除哪些记录，可以回滚

• DELETE FROM 表名 WHERE 条件;
  DELETE FROM 表名; -- 删除所有记录

• drop：删除整个表，包括表的结构和所有数据，通常不能回滚

• DROP TABLE 表名;

补充：

• "回滚"（Rollback）是指撤销已经执行但尚未提交的事务操作，将数据库恢复到事务开始之前的状态。

• 事务具有以下四个特性（ACID）：

1. 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败，不存在部分执行的情况。
2. 一致性（Consistency）：事务执行前后，数据库的状态必须是一致的。
3. 隔离性（Isolation）：多个事务并发执行时，每个事务的执行应该是独立的，互不干扰。
4. 持久性（Durability）：事务一旦提交，其结果应该是永久性的，即使系统发生故障也不会丢失。

1.3 修改/编辑  update set

-- 修改/编辑 updat
update staff set name='李清照' where id=7;
-- 李清照工资上调2000
update staff set salary=salary+2000 where name='李清照'; 
-- id=4  改名，2000
update staff set name='孟浩然' ,salary=salary-2000 where id=4;

二、数据查询语言 DQL

2.1  查询表格

• -- 指定列名查询  select 列1,列2  from 表名;
• -- 使用*代替所有的列   select * from  表名;
• -- 使用as 只能指定别名（XXX: 列，方法结果，表，视图）  select XXX `别名` from 表名;

  -- 指定列名查询
  select name,salary from staff;
  -- 使用*代替所有的列
  select * from staff;
  -- 使用as 只能指定别名（列，方法结果，表，视图）
  select name as `姓名` from staff;

• --使用where指定条件语句 ：()等于   =    <=>     不等于 !=    <>     大于小于  >  >= < <=

-- 使用where指定条件语句
-- 等于
select * from staff where id=1;
select * from staff where id <=>2;
-- 不等于
select * from staff where id!=2;
select * from staff where id <>2;
-- 大于小于
select * from staff where id>2;
select * from staff where id>=2;
select * from staff where id<2;
select * from staff where id<=2;

2.2   对null值的判断

• -- 不可以使用=null 
• -- select * from staff where salary=null;

-- 对null值的判断
-- 不可以使用=null 
-- select * from staff where salary=null;
select * from staff where salary is null;
-- 不可以是not is NULL
-- select * from staff where salary not is null;
select * from staff where salary is not null;
-- 等于null
select * from staff where salary <=>null;

2.3 多条件

• -- and or 区别

-- and or 区别
select * from staff where id <=100 and salary>=500;
select * from staff where salary >8000 or salary<10000;

•  -- in   not in

-- in   not in
select * from staff where id in(1,3,7);
select * from staff where id not in(1,3,7);

• -- between and 数值范围判断,包含边界值

-- between and 数值范围判断
-- 包含边界值
select * from staff where salary between 7000 and 10000;

2.4 模糊查询 LIKE

• -- 任意个任意字符 &
• -- 一个任意字符（有且只有一个字符)   

-- 模糊查询 like
-- 任意个任意字符 &
-- 一个任意字符（有且只有一个字符)   _
select * from staff where name like '%w%';
-- 姓zhang的同学
select * from staff where name like 'zhang%';
-- zhang某
select * from staff where name like 'zhang_';

2.5 是否存在 exists    not exists

• -- 后面exists（）查询内容有结果，前面返回整个语句结果，否者不返回结果

select * from staff where exists(select *from staff where 1=2);

2.6  any    all 

-- any    all 
-- any 任意一个
-- 前面的查询语条件要比后面的结果集任意一个都大
select * from staff 
where salary >any(select 10000);

select * from staff 
where salary >any(select code from staff );

-- all 全部
select * from staff 
where salary >all(select code from staff );

2.7 排序 ORDER BY 

• -- 默认是正序 asc ,倒序 desc 
• -- 多个列排序，越往前优先排序

-- 排序 ORDER BY
-- 默认是正序 asc ,倒序 desc 
select * from staff order by salary ;
select * from staff order by salary asc ;
select * from staff order by salary desc;
-- 按照多个列排序，越往前优先排序
-- select * from staff order by salary,code desc;和下式是一样，每一列都需要指定排序方式，如果没有指定就是默认asc
select * from staff order by salary asc,code desc;

2.8  拼接（合并）查询结果  union     union all      去重 distinct

• union  （左右两个查询语句可以不是一张表）
• -- 约束：两个列的数量必须相同，类型可以不同
• -- union默认去重：将查询后重复的结果集去除
• -- union all没有去重效果

-- 去重 DISTINCT   对整个查询结果集去重

-- 拼接（合并）查询结果  union  （左右两个查询语句可以不是一张表）
-- 约束：两个列的数量必须相同，类型可以不同
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;

-- 去重 DISTINCT   对整个查询结果集去重
-- 而且结果集顺序就是先salary再name列
select distinct salary,name from staff;

2.9 部分查询 limit 

• -- 部分查询 limit count   获取前count条数据
• -- limit start,count   从下标为start开始查询count条数据（下标从0开始）

-- 部分查询 limit count
-- 查询薪资最高三人
select * from staff order by salary desc limit 3;
-- limit start,count   从下标为start开始查询count条数据（下标从0开始）
-- 查询薪资最高的第三名到第五名
select * from staff order by salary desc limit 3,3;

2.10  case when then 两种语法

（1）select  列…… ，

         case when 条件1 then 结果1 

         when 条件2 then 结果2

         else 结果n

         end（as  别名）

         from 表名

select * , case 
when salary <10000 then '薪资较低' 
when salary>=10000 and salary <=11000 then '薪资正常' 
else '薪资较高'
end 
from staff ;

（2）select 表达式（列名……）

        when 值1 then 结果1

        when 值2 then 结果2

        else 结果n

        end  （as  别名）

        from  表名

select *,case 
salary when  8000 then '还行'
when 10000 then '还可以'
when 11000 then '挺高'
end as `level` from staff;

2.11 聚合函数（5个）

• -- 最大值 max
• -- 最小值 min
• -- 计算平均数 avg()
• -- 求和 sum

-- 求个数 count

-- 查询当前时间函数
select now();
-- 聚合函数 （5个）：将多个数据聚合成一个数据的函数
-- 最大值 max
select max(salary) from staff;
-- 最小值 min
select min(salary) from staff;
-- 计算平均数 avg()
select avg(salary) from staff;
-- 求和 sum
select sum(salary) from staff;
-- 求个数 count
select count(salary) from staff;

2.12 分组查询 group by

• -- group by 常跟聚合函数连用
• -- JDK8 select 后面只能跟一个列名，
• -- JDK 5 select 可以跟多列

• select department ,
  avg(salary) as `平均薪资`,
  sum(salary) as `薪资总和`
  from staff group by department ;

  

2.13 过滤 分组筛选 having

• -- having （对分组之后的数据再筛选）
• -- having 后面可以跟聚合函数，where不可以
• -- 语句执行顺序： from -> where ->group by ->having  ->select

-- 过滤 分组筛选：having （对分组之后的数据再筛选）
-- having 后面可以跟聚合函数，where不可以
select department from staff group by department
having  avg(salary)<10000;
-- 语句执行顺序： from -> where ->group by ->having  ->select
-- 语句执行顺序： from -> ->on ->join ->……-> where ->group by ->having  ->select

2.14 null值  忽略

-- null  忽略
-- avg ,count会排除掉null值，
select department,avg(salary),count(salary) from staff group by department;
-- count(*)就会加上null值
select department,avg(salary),count(salary),count(*) from staff group by department;
-- 计算表内行数  count(*)  count(数字)
select count(*),count(2) from staff;
select count(2) from staff;
select count(*) from staff;

2.14 连接查询  ：外连接，内连接

• 外连接：  左外连接（左表为主表，右表为附表） left [out] join  
•                    右外连接（右表为主表，左表为附表） right  [out] join  
•                     全外连接 full [out]  join
•  内连接  innner join ,也可以写作join ，只显示有对应关系的数据
• -- 一般会采用左外连接
• -- 表连接时，主表数据都会查询出来，附表数据只有和主表有对应关系的数据才会显示
• -- mysql 一般条件下不支持全外连接 full [out]  join
• -- 内连接 innner join ：只显示有对应关系的数据
• -- 通过技巧实现全外连接

-- 连接查询
-- 查询老师的姓名（teacher表tname）及教授的课程（course表cname）
-- 两个表通过外键连接  teacher.tid=course.tid
-- sql 不分大小写
-- join 连接
select  tname,cname from teacher left join course on teacher.tid=course.tid;
-- as 起别名
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 
inner 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
UNION
select a.tname,b.cname from teacher as a left join course as b on a.tid=b.tid;

2.15 交叉连接 cross join 

• -- 交叉连接 cross join 
• -- 会先生成count*count条数据，数据量过于庞大，一般不采用
• -- 此处可以省略as

select * from teacher a ,course  b where a.tid=b.tid;

2.16 子查询

• -- 子查询如果当做一个表来处理时，必须要有一个别名
• -- 子查询执行了，查询数据也放到运行内存中，会降低运行效率，尽量避免使用子查询

-- 子查询
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;