数据库笔记
###表的增删改差(CRUD:Creat,Retrieve,Update,Delete)
1. 插入是否更新
由于主键或者唯一键对应的值已经存在而导致插入失败
选择性的进行同步更新操作
1
2insert ... on duplicate key update
column = value [,column = value] ...举例
1
2
3
4
5
6
7insert 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: 表中有冲突数据,并且数据已经被更新查看函数获取受到影响的数据行数
1
select row_count();
####2.替换(replace)
- 主键或者唯一键没有冲突,则直接插入
- 主键或者唯一键有冲突,则删除后再插入
1 | replace into students (sn,name) values (20001,'曹阿瞒'); |
Retrieve
案例:
1 | CREATE TABLE exam_result ( |
####1.select列
全列查询
1
select * from 表明;
- 查询的列越多,意味着传输的数据越大
- 可能会影响引索的使用
指定列查询
1
2--- 指定列的顺序不需要按定义表的顺序进行查询
select id,name,yuwen from exam_result;查询字段为表达式
1
2--- 表达式不包含字段
select id,name,666 from exam_result;1
2--- 表达式包含一个字段
select id,name,yuwen+10 from exam_result;1
2--- 表达式包含多个字段,并且将查询结果指定别名
select id,name,yuwen+shuxue+yingyu as total from exam_result;查询结果去重(distinct)
1
select distinct shuxue from exam_result;
2.where条件
>,>=,<,<= 英语不及格的同学以及英语成绩(<60)
1
select neme,yingyu from exam_result where yingyu<60;
between a0 and a1 语文成绩在[80,90]分的同学以及语文成绩
1
2
3select 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;in(option,…) 数学成绩是58或者59或者98或者99分的同学及数学成绩
1
2
3select 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);like 姓孙的同学及孙某同学
1
2--- % 匹配多个任意字符 _ 匹配一个任意字符
select name from exam_result where name like '孙%' or '孙_';语文成绩好于英语成绩的同学
1
select name,yuwen,shuxue from exam_result where yuwen>shuxue;
总分在200分一下的同学
1
2--- 注意:由于执行顺序的原因(后面详细说明),别名不能在where条件中使用
select name ,yuwen+shuxue+yingyu as total from exam_result where yuwen+shuxue+yingyu<200;not 语文成绩>80且不姓孙的同学
1
select name,yuwen from exam_result where yuwen>80 and name not like '孙%';
and,or 孙某同学,否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80
1
select name,yuwen+shuxue+yingyu as total from exam_result where name like '孙_' or (yuwen+shuxue+yingyu>200 and yuwen<shuxue and yingyu>80);
NULL查询
<=>:等于,NULL安全,例如NULL<=>NULL的结果为TRUE(1)
=:等于,NULL不安全,;例如NULL=NULL的结果为NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16--- 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.排序结果
语法:
1 | -- ASC 为升序(从小到大) |
注意:没有order by子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
同学及数学成绩,按数学成绩升序显示
1
select name,shuxue from exam_result order by shuxue;
NULL视为比任何值都要小,升序出现在最上面,降序出现在最下面
查询同学各门成绩,一次按数学降序,英语升序,语文升序的方式显示
1
2--- 多字段排序,排序优先级随书写顺序
select name,yuwen,shuxue,yingyu from exam_result order by shuxue desc,yingyu,yuwen;查询同学及总分,由高到低
1
select name,yuwen+shuxue+yingyu as total from exam_result order by total desc;
查询姓孙的同学或者姓曹的同学数学成绩,结果按照数学成绩由高到低显示
1
select name,shuxue from exam_result where name like '孙%' or name like '曹%' order by shuxue desc;
4.筛选分页结果
语法:
1 | ---其实下标为0 |
- 建议:队未知表进行查询时,最好加一条limit 1,避免因为表中数据过大,查询全表数据导致数据库卡死
按id进行分页,每页3条记录,分别显示第1,2,3页
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27--- 第一页
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
语法:
1 | UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...] |
对查询到的接轨进行列值更新
将孙悟空同学的数学成绩变更为80分
1
2
3update exam_result set shuxue=80 where name='孙悟空';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0将曹孟德同学成绩变更为60分,语文成绩变更为70分
1
2
3update 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位同学的数学成绩加上30分
1
update exam_result set shuxue=shuxue+30 order by shuxue+yuwen+yingyu limit 3;
将所有同学的语文成绩更新为原来的2倍
1
update exam_result set yuwen=yuwen*2;
6.Delete
语法:
1 | DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...] |
删除孙悟空同学的考试成绩
1
delete from exam_result where name='孙悟空';
删除整张表数据
1
delete from 表明;
截断表(truncate)
语法:
1
TRUNCATE [TABLE] table_name
- 注意:该操作慎用
- 只能对整张表操作,不能像delete一样针对部分数据操作
- 直接全部删除,不关心表中所存储的数据
- 会重置auto_increment项
- 注意:该操作慎用
7. 插入查询结果(将一张表查询结果插入到另外一张表中,再重命名即可)
语法:
1 | INSERT INTO table_name [(column [, column ...])] SELECT ... |
案例:删除表中重复记录,重复的数据值能有一份
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39-- 创建原表数据
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17-- 这里以 * 做统计,不受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)统计班级收集qq号有多少
1
2
3
4
5
6
7
8-- null不会计入结果
select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)统计本次考试的数学成绩分数个数
1
2
3
4select count(shuxue) from exam_result;
统计去重后数学成绩分数个数
select count(distinct shuxue) from exam_result;统计数学成绩总分
1
select sum(shuxue) from exam_result;
统计平均总分
1
select avg(shuxue+yingyu+yuwen) from exam_result;
返回英语最高分
1
select max(yingyu) from exam_result;
返回>70分以上的数学最低分
1
select min(shuxue) from exam_result where shuxue>70;
9.group by子句的使用
语法:在select中使用group by子句可以对指定列进行分组查询
1 | select column1, column2, .. from table group by column; |
案例:
创建一个雇员信息表
- emp 员工表
- dept 部门表
- salgrade 工资等级表
显示每个部门的平均工资和最高工资
1
2
3
4
5
6
7
8select 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 |
+--------+-------------+----------+显示每个部门的每种岗位的平均工资和最高工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14select 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的部门和它的平均工资
1
2
3
4
5
6
7
8-- 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查询中各个关键字的执行先后顺序
1
from > on >join > where > group by > with > having > select > distinct > order by > limit
复合查询(重点)
基本查询回顾
查询工资高于或岗位为manager的雇员,同时满足他们的姓名首字母为大写的J
1
select * from emp where (sal>500 or job='manager') and ename like 'J%';
按照部门号升序雇员的工资降序排序
1
select * from emp order by deptno,sal desc;
使用年薪进行将序排序
isnuff
如果为第一个参数不为空就取第一个参数,为空取第二个参数1
select ename,sal*12+ifnull(comm,0) as '年薪' from emp order by 年薪 desc
显示工资最高的员工名字和岗位
1
select ename,job from emp where sal=(select max(sal) from emp);
显示工资高于平均工资的员工信息
1
select ename,job from emp where sal>(select avg(sal) from emp);
显示每个部门的平均工资和最高工资
fromat(29999,2)
含义是小数点后保留两位1
2
3select deptno,max(sal),avg(sal) from emp group by deptno;
select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;显示平均工资低于2000的部门和它的平均工资
1
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal<2000;
显示每中岗位的雇员总数,平均工资
1
2
3select job,count(job),avg(sal) from emp group by job;
select job,count(*), format(avg(sal),2) from EMP group by job;
多表查询
显示雇员名,雇员工资以及所在部门的名字因为上面的数据来自emp和dept表,因此要联合查询
1
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;
显示部门号为10的部门名,员工名和工资
1
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;
显示各个员工姓名,工资,以及工资级别
1
select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;
自连接
自连接是指在同一张表连接查询
显示员工ford的上级领导的编号和姓名(mgr是员工领导的编号–empno)
使用的子查询
1
select empno,ename from emp where empno=(select mgr from emp where ename='ford');
使用多表查询 – 使用别名将一张表分成两份分别使用
1
select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='ford';
子查询
单行子查询
显示smith同一部门的员工
1
select ename from emp where deptno=(select deptno from emp where ename='smith');
多行子查询
in关键字:查询和10号部门的工作相同的雇员的名字,岗位,工资,部门号,但是不包含10自己
1
select ename,job,sal,empno from emp where job in(select job from emp where deptno=10) and deptno<>10;
all关键字:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
1
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
any关键字:显示工资比部门30的任意员工的工资高的员工的姓名,工资和部门号
1
select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);
多列子查询
查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人
1
select ename from emp where (deptno, job)=(select deptno, job from emp where ename='smith') and ename<>'smith'
在from子句中使用子查询
显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资
- 将子查询当做成一个临时表
1
2
3select 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;显示每个部门的信息(部门号,编号,地址)和人员数量
- 使用多表查询
1
select dept.dname,dept.deptno,dept.loc,count(*) from emp,dept where emp.deptno=dept.deptno group by dept.deptno;
- 使用子查询 – 将emp表当做成临时表去使用
1
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的人找出来
1
2select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job='manager';
union all 该操作符取得两个结果的并集。当使用该操作符时,不会去掉集中的重复行
将工资大于2500或职位为manager的人找出来
1
2select ename,sal,job from emp where sal>2500 union all
select ename,sal,job from emp where job='manager';
在SQL语句中 || 用于连接两个字符串
表的内连和外连
内连接
语法:
1
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件
显示smith的名字和部门名称
1
select ename,dname from emp inner join dept on emp.deptno=dept.deptno and ename='smith';
外连接
外连接分为左外连接和右外连接
#####左外连接
语法:
1
select 字段名 from 表明1 left join 表明2 on 连接条件
案例:
1
2
3
4-- 建两张表
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);查询所有学生的成绩,如果这个学生没有成绩,也要将学生的个人信息显示出来
1