mysql02

###课程回顾

1. 数据库相关SQL

• 查询所有 show databases;
• 创建 create database db1 character set utf8/gbk;
• 查询详情show create database db1;
• 删除 drop database db1;
• 使用 use db1;

2. 表相关SQL

• 创建 create table t1(name varchar(10),age int) charset=utf8/gbk;
• 查询所有 show tables;
• 详情 show create table t1;
• 字段 desc t1;
• 删除 drop table t1;
• 添加字段 alter table t1 add age int first/after xxx;
• 删除字段 alter table t1 drop age;
• 修改字段 alter table t1 change 原名 新名 新类型

3. 数据相关SQL

• 插入 insert into t1(name,age) values(值1,值2),(值1,值2),(值1,值2);
• 查询 select * from t1 where 条件;
• 修改 update t1 set xxx=xxx where 条件;
• 删除 delete from t1 where 条件;

4. 数据类型

• 整数: int(m) 和bigint m代表显示长度 结合zerofill使用
• 浮点数：double(m,d) m总长度 d小数长度 超高精度浮点数decimal(m,d)
• 字符串：
  1. char:固定长度 最大255
  2. varchar：可变长度 最大65535超过255建议使用text
  3. text：可变长度 最大65535
• 日期：
  1. date年月日
  2. time时分秒
  3. datetime： 默认值null 最大 9999-12-31
  4. timestamp：默认值系统时间 最大 2038-1-19
     ###练习

1. 创建数据库db2 字符集utf8并使用
2. 在数据库中创建员工表emp 字段：id,name,sal,deptId(部门id) 字符集utf8
3. 创建部门表dept 字段：id,name,loc(部门地点) 字符集utf8
4. 部门表插入以下数据:
   1 神仙部 天庭 2 妖怪部 盘丝洞
5. 员工表中插入以下数据：
   1 悟空 5000 1 2 八戒 2000 1
   3 蜘蛛精 8000 2 4 白骨精 9000 2

• 答案：

1. create database db2 character set utf8;
   use db2;

2. create table emp(id int,name varchar(10),sal int,deptId int);

3. create table dept(id int,name varchar(10),loc varchar(10));

4. insert into dept values(1,‘神仙部’,‘天庭’),(2,‘妖怪部’,‘盘丝洞’);

5. 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=‘取经部’;
    ###主键约束

• 约束：创建表时给表字段添加的限制条件
• 主键： 表示数据唯一性的字段称为主键
• 主键约束：用于限制字段的值必须唯一并且不能为null（唯一且非空）
• 格式：
  create table t1(id int primary key,name varchar(10));
  insert into t1 values(1,‘aaa’);
  insert into t1 values(1,‘bbb’); 报错 不能重复
  insert into t1 values(null,‘ccc’); 报错 不能为null
  ###主键约束+自增 primary key auto_increment
• 自增规则： 历史最大值+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
  ###注释 comment
• 创建表时给字段添加的注释
• 格式:
  create table t3(id int primary key auto_increment comment ‘这个是主键’,name varchar(10) comment ‘这是名字’);
  查看： show create table t3;
  ###导入*.sql文件

1. linux系统：
   source /home/soft01/桌面/emp.sql;
2. window系统:
   source d:/emp.sql;

• 检查是否成功
  show databases; 看是否有一个newdb3的数据库
  use newdb3;
  show tables; 看是否有两个表 emp 和 dept
  select * from emp; //如果出现乱码则执行下面的代码
  set names gbk;
  —出现乱码的同学按照步骤执行以下命令：
  1. 先把数据库删除掉
     drop database newdb3;
  2. set names utf8;
  3. source d:/emp.sql;
  4. set names gbk;
  5. select * from emp;
     以下是emp.sql文件链接：
     创建一个emq.sql把此内容粘贴进去即可使用

is null 和 is not null

1. 查询上级领导为空的员工姓名
   select ename from emp where mgr is null;
2. 查询员工奖金为null的姓名，工资和奖金
   select ename,sal,comm from emp where comm is null;
3. 查询有上级领导的员工姓名和工资
   select ename,sal from emp where mgr is not null;
   ####去重 distinct
4. 查询员工所从事的职业有哪几种
   select distinct job from emp;
   ####比较运算符 > < >= <= = !=和<>
5. 查询工作不是销售的员工姓名和工作
   select ename,job from emp where job!=‘销售’;
   select ename,job from emp where job<>‘销售’;
   ####and和or

• and:类似java中的 &&
• or：类似java中的 ||

1. 查询1号部门中工资低于1500的员工信息
   select * from emp where deptno=1 and sal<1500;
2. 查询2号部门员工或工资高于2000的员工姓名，工资和部门编号
   select ename,sal,deptno from emp where deptno=2 or sal>2000;
   ####between x and y 包含x和y
3. 查询工资在2000到3000之间的员工信息
   select * from emp where sal between 2000 and 3000;
   ####in(x,y,z)
4. 查询工资为800，3000，1500的员工信息
   select * from emp where sal in(800,3000,1500);
   ###综合练习
5. 查询有上级领导并且是1号部门的员工信息
   select * from emp where mgr is not null and deptno=1;
6. 查询2号部门中工资在1000到2000之间的员工信息
   select * from emp where deptno=2 and sal between 1000 and 2000;
7. 查询1号部门中有哪几种不同的工作
   select distinct job from emp where deptno=1;
   ###模糊查询 like

• %： 代表0或多个未知字符
• _：代表1个未知字符
• 举例：
  1. 以x开头： x%
  2. 以y结尾： %y
  3. 以a开头b结尾： a%b
  4. 第二个字符是a: _a%
  5. 包含a： %a%
  6. 第二个字符是a倒数第三个字符是b: a%b_

1. 查询名字中以孙开头的员工姓名
   select ename from emp where ename like ‘孙%’;
2. 查询工作中包含销售的员工姓名和工作
   select ename,job from emp where job like ‘%销售%’;
   ###排序 order by

• 格式： order by 字段名 asc（升序，默认）/desc（降序）

1. 查询每个员工的姓名和工资要求按照工资升序排序
   select ename,sal from emp order by sal;
2. 查询3号员工的姓名和工资按照工资降序排序
   select ename,sal from emp where deptno=3 order by sal desc;
3. 查询所有员工姓名工资和部门编号，按照部门编号降序排序
   select ename,sal,deptno from emp order by deptno desc,sal;
   ###分页查询 limit

• 格式： limit 跳过的条数,请求的条数
• 举例：
  1. 请求第一页的三条数据 limit 0,3
  2. 第三页的5条数据 limit 10,5;
  3. 第8页的6条数据 limit (8-1)*6,6

1. 查询工资最高的前三名 姓名和工资
   select ename,sal from emp order by sal desc limit 0,3;
2. 查询按照工资升序排序第三页的两条员工信息
   select * from emp order by sal limit 4,2;
3. 查询工资在1000到3000之间的员工信息 按照工资降序排序第二页的3条数据
   select * from emp where sal between 1000 and 3000
   order by sal desc limit 3,3;
   ###数值计算 + - * / %
4. 查询每个员工的姓名，工资和年终奖(年终奖=工资5)
   select ename 姓名,sal ‘工资’,sal5 as ‘年终奖’ from emp;
5. 查询每个员工的姓名、工资和涨薪10块钱之后的工资
   select ename,sal,sal+10 from emp;
   ###聚合查询

• 对多条数据进行统计查询
• 求和，平均值，最大值，最小值，计数

1. 求和sum(字段名)

• 查询1号部门的工资总和
  select sum(sal) from emp where deptno=1;

2. 平均值avg(字段名)

• 查询2号部门的平均工资
  select avg(sal) from emp where deptno=2;

3. 最大值max(字段名)

• 查询销售的最高工资
  select max(sal) from emp where job like ‘%销售%’;

4. 最小值min(字段名)

• 查询2号部门工资的最小值
  select min(sal) from emp where deptno=2;

5. 计数 count(字段)

• 查询2号部门的人数
  select count(*) from emp where deptno=2;

###分组查询

• 题目中只要出现类似：每个、每种 一般都使用分组查询

1. 查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
2. 查询每个部门的工资总和
   select deptno,sum(sal) from emp group by deptno;
3. 查询每种工作的人数
   select job,count(*) from emp group by job;
4. 查询每个部门工资大于1500的员工人数
   select deptno,count(*) from emp
   where sal>1500 group by deptno;
5. 查询1号和3号部门中每个部门的最低工资
   select deptno,min(sal) from emp
   where deptno in(1,3) group by deptno;
   ####having

• where后面只能写普通字段的条件
• having结合group by使用，后面写聚合函数的条件
• 关键字的顺序：
  select … from 表名 where … group by …having…order by … limit…;

1. 查询每个部门的平均工资，要求平均工资大于2000
   select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
2. 查询工资在1000-3000之间的员工,统计每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资升序排序。
   select deptno,sum(sal),avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>2000 order by avg(sal);
   • 别名的方式：
     select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>2000 order by a;
3. 查询有上级领导的员工，每个部门的编号，最高工资，最低工资，工资总和，要求工资总和大于5400，最后按照工资总和降序排序
   select deptno,max(sal),min(sal),sum(sal) s from emp
   where mgr is not null
   group by deptno
   having s>5400
   order by s desc;
   ###课程回顾
4. 主键约束 primary key

• 主键： 表示数据唯一性的字段称为主键
• 约束： 创建表时给表字段添加的限制条件
• 主键约束： 唯一且非空

2. 主键约束+自增 primary key auto_increment

• 自增规则：历史最大值+1

3. 注释comment
4. 如何导入*.sql 文件
   source 路径;
5. is null 和 is not null
6. 比较运算符 > < >= <= = !=和<>
7. 去重 distinct
8. and 和 or
9. between x and y 包含x和y
10. in(x,y,z)
11. 模糊查询 like %代表0或多个未知 _一个未知
12. 排序 order by 字段 asc/desc,字段 asc/desc
13. limit 跳过的条数,请求的条数
14. 数值计算 + - * / %
15. 聚合函数： 求和sum 平均值avg 最大值max 最小值min 计数count()
16. 分组查询: group by
17. having 和group by结合使用，写在group by的后面 用于添加聚合函数的条件

####综合练习题：

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条数据