Dyson's blog

数据库笔记

###表的增删改差（CRUD：Creat，Retrieve，Update，Delete）

1. 插入是否更新

由于主键或者唯一键对应的值已经存在而导致插入失败

• 选择性的进行同步更新操作

  insert ... on duplicate key update
  	column = value [,column = value] ...

  • 举例

    insert into students （id,sn,name）values （100,10010，'lalala'） 
    	on delicate key update sn=10010,name='lalala';
    
    三种情况
    -- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
    -- 1 row affected: 表中没有冲突数据，数据被插⼊
    -- 2 row affected: 表中有冲突数据，并且数据已经被更新

  • 查看函数获取受到影响的数据行数

    select row_count();

####2.替换（replace）

• 主键或者唯一键没有冲突，则直接插入
• 主键或者唯一键有冲突，则删除后再插入

replace into students (sn,name) values (20001,'曹阿瞒');

两种情况
-- 1 row affected: 表中没有冲突数据，数据被插⼊
-- 2 row affected: 表中有冲突数据，删除后重新插⼊

Retrieve

案例：

CREATE TABLE exam_result (   
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,   
name VARCHAR(20) NOT NULL COMMENT '同学姓名',   
yuwen float DEFAULT 0.0 COMMENT '语文成绩',    
shuxue float DEFAULT 0.0 COMMENT '数学成绩',    
yingyu float DEFAULT 0.0 COMMENT '英语成绩' );

INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES    
('唐三藏', 67, 98, 56),   
('孙悟空', 87, 78, 77),   
('猪悟能', 88, 98, 90), 
('曹孟德', 82, 84, 67),  
('刘玄德',55,85,45),
('孙权', 70, 73, 78),
('宋公明',75,65,30);

####1.select列

1. 全列查询

   select * from 表明;

   • 查询的列越多，意味着传输的数据越大
   • 可能会影响引索的使用

2. 指定列查询

   --- 指定列的顺序不需要按定义表的顺序进行查询
   select id,name,yuwen from exam_result;

3. 查询字段为表达式

   --- 表达式不包含字段
   select id,name,666 from exam_result;

   --- 表达式包含一个字段
   select id,name,yuwen+10 from exam_result;

   --- 表达式包含多个字段，并且将查询结果指定别名
    select id,name,yuwen+shuxue+yingyu as total from exam_result;

4. 查询结果去重（distinct）

   select distinct shuxue from exam_result;

2.where条件

1. >,>=,<,<= 英语不及格的同学以及英语成绩（<60）

   select neme,yingyu from exam_result where yingyu<60;

2. between a0 and a1 语文成绩在[80,90]分的同学以及语文成绩

   select name,yuwen from exam_result where yuwen>80 and yuwen<90;
   --- 使用between...and... 条件
   select name,yuwen from exam_result where yuwen between 80 and 90;

3. in(option,…) 数学成绩是58或者59或者98或者99分的同学及数学成绩

   select name,shuxue from exam_result where shuxue=58 or shuxue=59 or shuxue=98 or shuxue=99;
   --- 使用 in 条件,满足其中任意一个条件即可
   select name,shuxue from exam_result where shuxue in (58,59,98,99);

4. like 姓孙的同学及孙某同学

   --- % 匹配多个任意字符  _ 匹配一个任意字符
   select name from exam_result where name like '孙%' or '孙_';

5. 语文成绩好于英语成绩的同学

   select name,yuwen,shuxue from exam_result where yuwen>shuxue;

6. 总分在200分一下的同学

   --- 注意：由于执行顺序的原因(后面详细说明)，别名不能在where条件中使用
   select name ,yuwen+shuxue+yingyu as total from exam_result where yuwen+shuxue+yingyu<200;

7. not 语文成绩>80且不姓孙的同学

   select name,yuwen from exam_result where yuwen>80 and name not like '孙%';

8. and,or 孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80

   select name,yuwen+shuxue+yingyu as total from exam_result where name like '孙_' or (yuwen+shuxue+yingyu>200 and yuwen<shuxue and yingyu>80);

9. NULL查询

   <=>：等于，NULL安全，例如NULL<=>NULL的结果为TRUE(1)

   =：等于，NULL不安全，；例如NULL=NULL的结果为NULL

   --- NULL和NULL比较，=和<=>的区别
   select null=null,null=1,null=0;
   +-----------+--------+--------+
   | null=null | null=1 | null=0 |
   +-----------+--------+--------+
   |      NULL |   NULL |   NULL |
   +-----------+--------+--------+
   1 row in set (0.00 sec)
   
   select null<=>null,null<=>1,null<=>0;
   +-------------+----------+----------+
   | null<=>null | null<=>1 | null<=>0 |
   +-------------+----------+----------+
   |           1 |        0 |        0 |
   +-------------+----------+----------+
   1 row in set (0.03 sec)

####3.排序结果

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认 ASC

SELECT ... FROM table_name [WHERE ...]     
	ORDER BY column [ASC|DESC], [...];

注意：没有order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

1. 同学及数学成绩，按数学成绩升序显示

   select name,shuxue from exam_result order by shuxue;

2. NULL视为比任何值都要小，升序出现在最上面，降序出现在最下面

3. 查询同学各门成绩，一次按数学降序，英语升序，语文升序的方式显示

   --- 多字段排序，排序优先级随书写顺序
   select name,yuwen,shuxue,yingyu from exam_result order by shuxue desc,yingyu,yuwen;

4. 查询同学及总分，由高到低

   select name,yuwen+shuxue+yingyu as total from exam_result order by total desc;

5. 查询姓孙的同学或者姓曹的同学数学成绩，结果按照数学成绩由高到低显示

   select name,shuxue from exam_result where name like '孙%' or name like '曹%' order by shuxue desc;

4.筛选分页结果

语法：

---其实下标为0

---从0开始，筛选n条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n

---从s开始，筛选n条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

---从s开始，筛选n条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

• 建议：队未知表进行查询时，最好加一条limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死

1. 按id进行分页，每页3条记录，分别显示第1,2,3页

   --- 第一页
   select id,name,yuwen,shuxue,yingyu from exam_result order by id limit 3 offset 0;
   +----+-----------+-------+--------+--------+
   | id | name      | yuwen | shuxue | yingyu |
   +----+-----------+-------+--------+--------+
   |  1 | 唐三藏    |    67 |     98 |     56 |
   |  2 | 孙悟空    |    87 |     78 |     77 |
   |  3 | 猪悟能    |    88 |     98 |     90 |
   +----+-----------+-------+--------+--------+
   
   --- 第二页
   select id,name,yuwen,shuxue,yingyu from exam_result order by id limit 3 offset 3;
   +----+-----------+-------+--------+--------+
   | id | name      | yuwen | shuxue | yingyu |
   +----+-----------+-------+--------+--------+
   |  4 | 曹孟德    |    82 |     84 |     67 |
   |  5 | 刘玄德    |    55 |     85 |     45 |
   |  6 | 孙权      |    70 |     73 |     78 |
   +----+-----------+-------+--------+--------+
   
   --- 第三页，如果结果不足3个，不会对数据有影响
   select id,name,yuwen,shuxue,yingyu from exam_result order by id limit 3 offset 6;
   +----+-----------+-------+--------+--------+
   | id | name      | yuwen | shuxue | yingyu |
   +----+-----------+-------+--------+--------+
   |  7 | 宋公明    |    75 |     65 |     30 |
   +----+-----------+-------+--------+--------+

####5.Updata

语法：

UPDATE table_name SET column = expr [, column = expr ...]    	[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的接轨进行列值更新

1. 将孙悟空同学的数学成绩变更为80分

   update exam_result set shuxue=80 where name='孙悟空';
   Query OK, 1 row affected (0.14 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

2. 将曹孟德同学成绩变更为60分，语文成绩变更为70分

   update exam_result set shuxue=60,yuwen=70 where name='曹孟德';
   Query OK, 1 row affected (0.08 sec)
   Rows matched: 1  Changed: 1  Warnings: 0

3. 将总成绩倒数前三的3位同学的数学成绩加上30分

   update exam_result set shuxue=shuxue+30 order by shuxue+yuwen+yingyu limit 3;

4. 将所有同学的语文成绩更新为原来的2倍

   update exam_result set yuwen=yuwen*2;

6.Delete

语法：

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

1. 删除孙悟空同学的考试成绩

   delete from exam_result where name='孙悟空';

2. 删除整张表数据

   delete from 表明;

   • 截断表（truncate）

     语法：

     TRUNCATE [TABLE] table_name

     • 注意：该操作慎用
       1. 只能对整张表操作，不能像delete一样针对部分数据操作
       2. 直接全部删除，不关心表中所存储的数据
       3. 会重置auto_increment项

7. 插入查询结果（将一张表查询结果插入到另外一张表中，再重命名即可）

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

• 案例：删除表中重复记录，重复的数据值能有一份

  -- 创建原表数据
  mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
  Query OK, 0 rows affected (0.45 sec)
  
  -- 插入测试数据
  mysql> INSERT INTO duplicate_table VALUES
  	(100, 'aaa'),    
  	(100, 'aaa'),   
  	(200, 'bbb'),    
  	(200, 'bbb'),    
  	(200, 'bbb'),    
  	(300, 'ccc');
  Query OK, 6 rows affected (0.09 sec)
  Records: 6  Duplicates: 0  Warnings: 0
  
  -- 建立一张空表，空表的结构和原表的结构相同
  mysql> CREATE TABLE no_duplicate_table LIKE duplicate_table;
  Query OK, 0 rows affected (0.42 sec)
  
  -- 将原表去重后的数据插入到新表中
  mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
  Query OK, 3 rows affected (0.11 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  -- 通过重命名表，实现原子操作（就不用担心在去重的过程中会有新的数据插入）
  mysql> RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table;
  Query OK, 0 rows affected (0.47 sec)
  
  -- 查看结果
  mysql> SELECT * FROM duplicate_table
      -> ;
  +------+------+
  | id   | name |
  +------+------+
  |  100 | aaa  |
  |  200 | bbb  |
  |  300 | ccc  |
  +------+------+
  3 rows in set (0.00 sec)

8. 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和
AVG([DISTINCT] expr)	返回查询到的数据的平均值
MAX([DISTINCT] expr)	返回查询到的数据的最大值
MIN([DISTINCT] expr)	返回查询到的数据的最小值

1. 统计班级共有多少学生

   -- 这里以 * 做统计，不受mull影响
   select count(*) from students;
   +----------+
   | count(*) |
   +----------+
   |        4 |
   +----------+
   1 row in set (0.05 sec)
   
   -- 使表达式做统计
   select count(10) from students;
   +-----------+
   | count(10) |
   +-----------+
   |         4 |
   +-----------+
   1 row in set (0.01 sec)

2. 统计班级收集qq号有多少

   -- null不会计入结果
   select count(qq) from students;
   +-----------+
   | count(qq) |
   +-----------+
   |         1 |
   +-----------+
   1 row in set (0.00 sec)

3. 统计本次考试的数学成绩分数个数

   select count(shuxue) from exam_result;
   
   统计去重后数学成绩分数个数
   select count(distinct shuxue) from exam_result;

4. 统计数学成绩总分

   select sum(shuxue) from exam_result;

5. 统计平均总分

   select avg(shuxue+yingyu+yuwen) from exam_result;

6. 返回英语最高分

   select max(yingyu) from exam_result;

7. 返回>70分以上的数学最低分

   select min(shuxue) from exam_result where shuxue>70;

9.group by子句的使用

语法：在select中使用group by子句可以对指定列进行分组查询

select column1, column2, .. from table group by column;

案例：

• 创建一个雇员信息表

  • emp 员工表
  • dept 部门表
  • salgrade 工资等级表

• 显示每个部门的平均工资和最高工资

  select deptno,avg(sal),max(sal) from emp group by deptno;
  +--------+-------------+----------+
  | deptno | avg(sal)    | max(sal) |
  +--------+-------------+----------+
  |     10 | 2916.666667 |  5000.00 |
  |     20 | 2175.000000 |  3000.00 |
  |     30 | 1566.666667 |  2850.00 |
  +--------+-------------+----------+

• 显示每个部门的每种岗位的平均工资和最高工资

  select avg(sal),min(sal),job,deptno from emp group by deptno,job;
  +-------------+----------+-----------+--------+
  | avg(sal)    | min(sal) | job       | deptno |
  +-------------+----------+-----------+--------+
  | 1300.000000 |  1300.00 | CLERK     |     10 |
  | 2450.000000 |  2450.00 | MANAGER   |     10 |
  | 5000.000000 |  5000.00 | PRESIDENT |     10 |
  | 3000.000000 |  3000.00 | ANALYST   |     20 |
  |  950.000000 |   800.00 | CLERK     |     20 |
  | 2975.000000 |  2975.00 | MANAGER   |     20 |
  |  950.000000 |   950.00 | CLERK     |     30 |
  | 2850.000000 |  2850.00 | MANAGER   |     30 |
  | 1400.000000 |  1250.00 | SALESMAN  |     30 |
  +-------------+----------+-----------+--------+

• 显示平均工资低于2000的部门和它的平均工资

  -- aving与group by配合使用，对group by结构进行筛选
  select avg(sal) as avg_sal from emp group by deptno having avg_sal<2000;
  +-------------+
  | avg_sal     |
  +-------------+
  | 1566.666667 |
  +-------------+
  1 row in set (0.16 sec)

面试题（重要）：

• SQL查询中各个关键字的执行先后顺序

  from > on >join > where > group by > with > having > select > distinct > order by > limit

复合查询（重点）

基本查询回顾

• 查询工资高于或岗位为manager的雇员，同时满足他们的姓名首字母为大写的J

  select * from emp where (sal>500 or job='manager') and ename like 'J%';

• 按照部门号升序雇员的工资降序排序

  select * from emp order by deptno,sal desc;

• 使用年薪进行将序排序 isnuff如果为第一个参数不为空就取第一个参数，为空取第二个参数

  select ename,sal*12+ifnull(comm,0) as '年薪' from emp order by 年薪 desc

• 显示工资最高的员工名字和岗位

  select ename,job from emp where sal=(select max(sal) from emp);

• 显示工资高于平均工资的员工信息

  select ename,job from emp where sal>(select avg(sal) from emp);

• 显示每个部门的平均工资和最高工资 fromat(29999,2)含义是小数点后保留两位

  select deptno,max(sal),avg(sal) from emp group by deptno;
  
  select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

• 显示平均工资低于2000的部门和它的平均工资

  select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal<2000;

• 显示每中岗位的雇员总数，平均工资

  select job,count(job),avg(sal) from emp group by job;
  
  select job,count(*), format(avg(sal),2) from EMP group by job;

多表查询

• 显示雇员名，雇员工资以及所在部门的名字因为上面的数据来自emp和dept表，因此要联合查询

  select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

• 显示部门号为10的部门名，员工名和工资

  select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;

• 显示各个员工姓名，工资，以及工资级别

  select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;

自连接

自连接是指在同一张表连接查询

• 显示员工ford的上级领导的编号和姓名（mgr是员工领导的编号–empno）

  • 使用的子查询

    select empno,ename from emp where empno=(select mgr from emp where ename='ford');

  • 使用多表查询 – 使用别名将一张表分成两份分别使用

    select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='ford';

子查询

单行子查询

• 显示smith同一部门的员工

  select ename from emp where deptno=(select deptno from emp where ename='smith');

多行子查询

• in关键字：查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己

  select ename,job,sal,empno from emp where job in(select job from emp where deptno=10) and deptno<>10;

• all关键字：显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号

  select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);

• any关键字：显示工资比部门30的任意员工的工资高的员工的姓名，工资和部门号

  select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);

多列子查询

• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

  select ename from emp where (deptno, job)=(select deptno, job from emp where ename='smith') and ename<>'smith'

在from子句中使用子查询

• 显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资

  • 将子查询当做成一个临时表

  select ename,deptno,sal,avg(sal) from emp,
  (select avg(sal) avg_sal,deptno dt from emp group by deptno) tmp
  where emp.sal>tmp.avg_sal and emp.deptno=tmp.dt;

• 显示每个部门的信息（部门号，编号，地址）和人员数量

  • 使用多表查询

  select dept.dname,dept.deptno,dept.loc,count(*) from emp,dept where emp.deptno=dept.deptno group by dept.deptno;

  • 使用子查询 – 将emp表当做成临时表去使用

  select dept.dname,dept.deptno,dept.loc,mycount from dept,(select count(*) mycount,deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno;

合并查询

• union 该操作符用于取得两结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

  • 将工资大于2500或职位为manager的人找出来

    select ename,sal,job from emp where sal>2500 union
    select ename,sal,job from emp where job='manager';

• union all 该操作符取得两个结果的并集。当使用该操作符时，不会去掉集中的重复行

  • 将工资大于2500或职位为manager的人找出来

    select ename,sal,job from emp where sal>2500 union all
    select ename,sal,job from emp where job='manager';

在SQL语句中 || 用于连接两个字符串

表的内连和外连

内连接

• 语法：

  select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件

• 显示smith的名字和部门名称

  select ename,dname from emp inner join dept on emp.deptno=dept.deptno and ename='smith';

外连接

外连接分为左外连接和右外连接

#####左外连接

• 语法：

  select 字段名 from 表明1 left join 表明2 on 连接条件

• 案例：

  -- 建两张表 
  create table stu (id int, name varchar(30)); -- 学生表 
  insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); create table exam (id int, grade int); -- 成绩表 
  insert into exam values(1, 56),(2,76),(11, 8);

  • 查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来