数据库总结二qq48556383的博客-

课程回顾

1. 数据库相关SQL
2. 查询所有 show databases;
3. 创建 create database db1 character set utf8/gbk;
4. 查询详情 show create database db1;
5. 删除数据库 drop database db1;
6. 使用数据库 use db1;
7. 表相关SQL
8. 创建表 create table t1(name varchar(10),age int)charset=utf8;
9. 查询所有 show tables;
10. 查询详情 show create table t1;
11. 表字段 desc t1;
12. 删除表 drop table t1;
13. 修改表名 rename table t1 to t2;
14. 添加字段 alter table t1 add 字段名 类型 first/after xxx;
15. 删除字段 alter table t1 drop 字段名;
16. 修改字段 alter table t1 change 原名 新名 新类型;
17. 数据相关SQL
18. 插入数据 insert into t1(name,age) values(xxx,xxx),(xxx,xxx);中文问题 如果存在中文问题 执行 set names gbk;
19. 查询数据 select 字段信息 from t1 where 条件;
20. 修改数据 update t1 set xxx=xxx where 条件;
21. 删除数据 delete from t1 where 条件;
22. 数据类型
23. 整数 int(m) 和 bigint(m) m代表显示长度 需要结合zerofill关键字使用
24. 浮点数 double(m,d) m代表总长度 d代表小数长度 超高精度浮点数decimal
25. 字符串: char:不可变长度 执行效率高 255 varchar 可变长度 节省空间,建议保存255以下 实际最大长度65535 text 可变长度 建议保存大于255 并且65535以内
26. 日期: date年月日 time时分秒 datetime默认值为null 最大9999-12-31 timestamp: 默认值为系统时间,最大 2038-1-19
27. 导入*.sql 文件source 路径;
28. is null 和 is not null
29. 去重 distinct
30. 比较运算符 > < >= <= = !=和<>
31. and和or
32. in(x,y,z)
33. between x and y 包含x和y

综合练习

1. 查询有上级领导并且是3号部门的员工信息select * from emp where mgr is not null and deptno=3;
2. 查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;
3. 查询1号部门工资为800和1600的员工信息 select * from emp where deptno=1 and sal in(800,1600);
4. 查询1号和2号部门工资高于2000的员工信息select * from emp where sal>2000 and deptno in(1,2);
5. 查询员工表中出现的部门编号有哪几个select distinct deptno from emp;

练习:

1. 创建数据库mydb2 字符集utf8 并使用create database mydb2 character set utf8; use mydb2;
2. 在数据库中创建员工表emp 字段:id,name,sal,deptId(部门id) 字符集utf8 create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;
3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8 create table dept(id int,name varchar(10),loc varchar(10))charset=utf8;
4. 部门表插入以下数据: 1 神仙部 天庭 2 妖怪部 盘丝洞 insert into dept values(1,’神仙部’,’天庭’),(2,’妖怪部’,’盘丝洞’);
5. 员工表插入一下数据: 1 悟空 5000 1 2 八戒 2000 1 insert into emp values(1,’悟空’,5000,1),(2,’八戒’,2000,1),(3,’蜘蛛精’,8000,2),(4,’白骨精’,9000,2);
6. 查询工资6000以下的员工姓名和工资select name,sal from emp where sal<6000;
7. 修改神仙部的名字为取经部update dept set name=’取经部’ where name=’神仙部’;
8. 给员工添加奖金comm字段 alter table emp add comm int;
9. 修改部门id为1的部门奖金为500 update emp set comm=500 where deptId=1;
10. 把取经部的地址改成五台山update dept set loc=’五台山’ where name=’取经部’;
11. 删除两个表 drop table emp; drop table dept;

主键约束

• 主键: 表示数据唯一性的字段称为主键
• 约束: 创建表时给表字段添加的限制条件
• 主键约束: 限制主键的值 唯一且非空
• 格式: primary key create table t1(id int primary key,name varchar(10)); insert into t1 values(1,’aaa’); insert into t1 values(1,’bbb’);报错:Duplicate entry ‘1’ for key ‘PRIMARY’ 主键值重复insert into t1 values(null,’ccc’); 报错:Column ‘id’ cannot be null id值不能为null

自增 auto_increment

• 自增字段给null值会触发自增
• 从历史最大值+1
• 举例:create table t2(id int primary key auto_increment,name varchar(10)); insert into t2 values(null,’aaa’); 1 insert into t2 values(null,’bbb’); 2 insert into t2 values(10,’ccc’); 10 insert into t2 values(null,’ddd’); 11 delete from t2 where id>=10; insert into t2 values(null,’eee’); 12

模糊查询like

• _: 代表1个未知字符
• %: 代表0或多个未知字符
• 举例:
  1. 以x开头 x%
  2. 以x结尾 %x
  3. 包含x %x%
  4. 第二个字符x _x%
  5. 倒数第三个是x %x__
  6. 以x开头倒数第二个是y x%y_
• 查询员工表中姓孙的员工姓名select ename from emp where ename like ‘孙%’;
• 查询工作中包含销售的员工姓名和工作select ename,job from emp where job like ‘%销售%’;
• 查询名字以精结尾的员工姓名select ename from emp where ename like ‘%精’;
• 查询工作中第二个字是”序”的员工信息select * from emp where job like ‘_序%’;

排序

• 格式: order by 字段名 asc升序(默认)/desc降序
• 查询2号部门员工姓名和工资按照工资升序排序select ename,sal from emp where deptno=2 order by sal;
• 查询所有员工信息 安装工资降序select * from emp order by sal desc;
• 查询销售人员信息按照工资降序排序select * from emp where job like ‘%销售%’ order by sal desc;
• 多字段排序格式: order by 字段名,字段名
• 查询员工姓名 工资和部门编号 按照部门编号降序排序select ename,sal,deptno from emp order by deptno desc,sal;

分页查询limit

• 格式: limit 跳过条数(跳过页数*每页条数),请求条数(每页条数)
• 查询员工表第一页的3条数据select * from emp limit 0,3;
• 查询员工表第2页的3条数据select * from emp limit 3,3;
• 查询员工表第3页的2条数据(每页2条)select * from emp limit 4,2;
• 查询工资最高的前三名的员工信息select * from emp order by sal desc limit 0,3;
• 查询1号部门工资最少的员工信息select * from emp where deptno=1 order by sal limit 0,1;

数值计算+ – * /

1. 查询每个员工的姓名 工资及年终奖(5个月的工资)select ename 名字,sal 工资,sal*5 年终奖 from emp;
2. 查询每个员工的姓名 工资和涨薪5块钱之后的工资 select ename,sal,sal+5 from emp;

聚合函数

• 对查询的多条数据进行统计查询, 统计方式:平均值,最大值,最小值,求和,计数
• 平均值avg(字段名)
  1. 查询1号部门的平均工资select avg(sal) from emp where deptno=1;
  2. 查询程序员的平均工资select avg(sal) from emp where job=’程序员’;
• 最大值max(字段名)
  1. 查询2号部门的最高工资select max(sal) from emp where deptno=2;
• 最小值min(字段名)
  1. 查询有上级领导的员工中的最低工资select min(sal) from emp where mgr is not null;
• 求和sum(字段名)
  1. 查询3号部门的工资总和select sum(sal) from emp where deptno=3;
• 计数count(*)
  1. 查询工资大于2000的员工人数select count(*) from emp where sal>2000;
  2. 查询1号部门工资大于2000的员工人数select count(*) from emp where deptno=1 and sal>2000;

练习题

1. 查询员工表中工资高于2000的员工姓名和工资,按照工资升序排序,查询第二页 的2条数据select ename,sal from emp where sal>2000 order by sal limit 2,2;
2. 查询和销售相关的工作的工资总和select sum(sal) from emp where job like ‘%销售%’;
3. 查询程序员人数 select count(*) from emp where job=’程序员’;
4. 查询1号部门中有领导的员工中的最高工资select max(sal) from emp where deptno=1 and mgr is not null;
5. 查询2号部门的最高工资和最低工资 起别名select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno=2;
6. 查询1号部门里面名字中包含空字的员工姓名select ename from emp where ename like ‘%空%’ and deptno=1;

分组查询 group by

• 以某个字段作为分组条件,这个字段相同的值为一组,对该组数据进行统计查询
• 查询每个部门的平均工资select deptno,avg(sal) from emp group by deptno ;
• 查询每个部门的最高工资select deptno,max(sal) from emp group by deptno;
• 查询每种工作的人数select job,count(*) from emp group by job;
• 查询每个部门工资高于1500的人数select deptno,count(*) from emp where sal>1500 group by deptno;
• 查询1号部门和2号部门的最高工资select deptno,max(sal) from emp where deptno in(1,2) group by deptno;
• 查询1号部门每种工作的人数select job,count(*) from emp where deptno=1 group by job;
• 查询1号和2号部门中每种工作的工资总和select job,sum(sal) from emp where deptno in(1,2) group by job;
• 查询每种工作的最低工资select job,min(sal) from emp group by job;

having

• where后面只能写普通字段的条件,不能写聚合函数条件

• having后面写聚合函数的条件,需要结合分组查询使用.

• 查询每个部门的平均工资,只查询平均工资大于2000的信息select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

• 查询每种工作人数只查询人数为1的select job,count() from emp group by job having count()=1; select job,count(*) c from emp group by job having c=1;

• 查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门 select deptno,avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000;

各个关键字的顺序

• select 字段信息 from 表名 where 条件 group by 字段 having 聚合函数条件 order by字段 limit….;

子查询(嵌套查询)

1. 查询工资高于1号部门平均工资的员工信息
   • 得到1号部门的平均工资select avg(sal) from emp where deptno=1; 2325
   • 高于1号平均工资的员工信息select * from emp where sal>(select avg(sal) from emp where deptno=1);
2. 查询最高工资的员工信息select * from emp where sal=(select max(sal) from emp);
3. 查询工资高于2号部门最低工资的员工信息select * from emp where sal>(select min(sal) from emp where deptno=2);

4. 查询和孙悟空相同工作的其他员工信息

   • 得到孙悟空工作select job from emp where ename=’孙悟空’;

   select * from emp where job=(select job from emp where ename=’孙悟空’) and ename!=’孙悟空’;

5. 查询拿最低工资员工的同事们的信息(同事指同一部门的员工)
   • 得到最低工资select min(sal) from emp;
   • 通过最低工资得到部门编号select deptno from emp where sal=(select min(sal) from emp);
   • 根据部门编号查询员工信息 还要去掉最低工资的select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

6. 查询白骨精的部门信息(需要用到dept部门表)-得到白骨精的部门编号 select deptno from emp where ename=’白骨精’; select * from dept where deptno=(select deptno from emp where ename=’白骨精’);

7. 查询所有员工的部门信息(部门只有1,2,3但是部门表里面有1,2,3,4 只根据员工表中出现的部门编号去查询部门信息)

   • 查询员工表中出现的部门编号select distinct deptno from emp;

   select * from dept where deptno in(select distinct deptno from emp);

综合练习

1. 查询没有上级领导的员工编号empno，姓名，工资
2. 查询有奖金的员工姓名和奖金
3. 查询名字中包含精的员工姓名和工资
4. 查询名字中第二个字是八的员工信息
5. 查询1号部门工资大于2000的员工信息
6. 查询2号部门或者工资低于1500的员工信息
7. 查询工资为3000，1500，5000的员工信息按照工资升序排序
8. 查询3号部门的工资总和
9. 查询每个部门工资大于1000的员工人数，按照人数升序排序
10. 查询每种工作中有领导的员工人数按照人数降序排序
11. 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序
12. 查询有领导的员工，每个部门的编号和最高工资
13. 查询有领导的员工，按照工资升序排序，第3页的2条数据
14. 查询每个部门的工资总和，只查询有上级领导的员工并且要求工资总和大于5400，最后按照工资总和降序排序,只查询结果中的第一条数据