数据库基础语句

###数据库

• 数据库简介:
  之前通过IO技术可以实现数据的增删改查操作,但是执行效率低,只能保存文本数据,所有功能都由程序员自己实现开发效率很低, 使用现成的数据软件,把数据的具体操作交由数据库软件处理,只需要学习如何使用数据库软件即可
• DBMS: DataBaseManagementSystem(数据库管理系统 俗称数据库软件),常见的DBMS有:MySQL Oracle SQLserver DB2 SQLite
• 数据库的分类

1. 关系型数据库: 经过数学理论验证,可以将现实生活中的任何关系进行保存,以表为单位保存数据
2. 非关系型数据库: 一般以键值对的形式保存数据,一般用于处理一些特殊场景如:数据缓存
   ####主流DBMS介绍
3. MySQL: Oracle公司产品, 08年被Sun公司收购 09Sun被Oracle收购,开源数据库 原MySQL程序员从Oracle离开创办MariaDB(Maria创始人女儿的名字),市场占有率排名第一
4. Oracle: Oracle公司产品, 公司老板拉里.艾莉森,闭源产品,市场占有率排名第二
5. SQLServer: 微软公司产品, 主要应用在微软的整套解决方案中,市场占有率排名第三
6. DB2: IBM公司产品, 主要应用在IBM整套解决方案中
7. SQLite: 轻量级数据库软件, 只具备基础的增删改查操作,一般应用在移动设备或嵌入式设备中

网站: 操作系统+web服务软件+开发语言+数据库软件

####开源和闭源

1. 开源:开放源代码 通过卖服务盈利 , 有大拿程序员对开源项目进行无偿的维护和升级
2. 闭源:不开放源代码 通过卖产品+服务, 有大拿程序员为了刷存在感或获利的需求对闭源项目进行攻击

###SQL

• Structured Query Language:结构化查询语言,用于程序员和数据库软件进行交互
  ###如何连接数据库软件
• 打开终端/命令行
• window系统: 开始菜单->所有程序->MariaDB/MySQL->MySQL Client 输入密码回车
• Linux系统:任何位置右键打开终端 输入以下指令
  mysql -uroot -p 回车 如果没有密码再次回车
• 退出指令:
  exit
  ###数据库相关SQL

1. 查看所有数据库

• 格式: show databases;

2. 创建数据库

• 格式: create database 数据库名;
  create database db1;

3. 查看数据库详情

• 格式: show create database 数据库名;
  show create database db1;

4. 创建数据库指定字符集

• 格式: create database 数据库名 character set utf8/gbk;
  create database db3 character set gbk;
  show create database db3; //验证一下

5. 删除数据库

• 格式: drop database 数据库名;
  drop database db3;

6. 使用数据库

• 格式: use 数据库名;
  use db1;
  ###表相关SQL

1. 创建表

• 格式: create table 表名(字段1名 字段1类型,字段2名 字段2类型);
  create table person(name varchar(10),age int);
• 练习: 创建学生表student 字段:学号id 姓名name 语文chinese 数学math 英语 english
  create table student(id int, name varchar(10), chinese int, math int, english int);

2. 查看所有表

• 格式: show tables;

3. 查看表详情

• 格式: show create table 表名;
  show create table student;
• 表引擎:
  1. innoDB(默认): 支持事务和外键等高级操作
  2. myisam: 只支持基础的增删改查操作

4. 创建表指定引擎和字符集

• 格式:create table 表名(字段1名 字段1类型,字段2名 字段2类型) engine=myisam/innodb charset=utf8/gbk;
  create table t1(name varchar(10),age int)engine=myisam charset=gbk;
  #####练习:

1. 创建2个数据库分别是mydb1和mydb2(字符集为gbk)
   create database mydb1;
   create database mydb2 character set gbk;
2. 在mydb1里面创建员工表emp 字段: 姓名 年龄 工资(sal)
   use mydb1;
   create table emp(name varchar(10),age int,sal int);
3. 在mydb2里面创建英雄表hero 字段: 姓名 年龄 英雄类型(type) 引擎为myisam字符集为gbk
   use mydb2;
   create table hero(name varchar(10),age int, type varchar(10))engine=myisam charset=gbk;
4. 删除刚创建的两个数据库
   drop database mydb1;
   drop database mydb2;

####表相关SQL(续)

1. 查看表字段

• 格式: desc 表名;
  desc student;

2. 删除表

• 格式: drop table 表名;
  drop table student;

3. 修改表名

• 格式: rename table 原名 to 新名;
  rename table t1 to t2;

4. 修改引擎和字符集

• 格式: alter table 表名 engine=myisam/innodb charset=utf8/gbk;
  alter table t2 engine=innodb charset=utf8;

5. 添加表字段

• 格式: alter table 表名 add 字段名 字段类型; //最后
• 格式: alter table 表名 add 字段名 字段类型 first; //最前面
• 格式: alter table 表名 add 字段名 字段类型 after xxx;//在xxx的后面
  create table emp (name varchar(10)); //创建
  alter table emp add age int; //最后
  alter table emp add id int first; //最前面
  alter table emp add gender varchar(5) after name; //name后面

6. 删除表字段

• 格式: alter table 表名 drop 字段名;
  alter table emp drop gender;

7. 修改表字段名和类型

• 格式: alter table 表名 change 原字段名 新名 新类型;
  alter table emp change age gender varchar(10);

8. 修改字段类型和位置

• 格式: alter table 表名 modify 字段名 新类型 first/after xxx;
  alter table emp modify gender varchar(5) first;
  alter table emp modify gender varchar(5) after id;

######表相关练习

1. 创建数据库mydb3 指定字符utf8 并使用
   create database mydb3 character set utf8;
   use mydb3;
2. 创建temp表 只有id字段 指定引擎为myisam 字符集为gbk
   create table temp(id int)engine=myisam charset=gbk;
3. 修改表名为t_emp ;
   rename table temp to t_emp;
4. 修改引擎为innodb 修改字符集为utf8
   alter table t_emp engine=innodb charset=utf8;
5. 在最后面添加name字段
   alter table t_emp add name varchar(10);
6. 在name前面添加age字段
   alter table t_emp add age int after id;
7. 在age后面添加工资sal字段
   alter table t_emp add sal int after age;
8. 修改sal字段名称为salary
   alter table t_emp change sal salary int;
9. 修改age字段到最后面
   alter table t_emp modify age int after name;
10. 删除salary字段
    alter table t_emp drop salary;
11. 删除表
    drop table t_emp;
12. 删除数据库
    drop database mydb3;
    ###数据相关SQL
    create database mydb1 character set utf8;
    use mydb1;
    create table emp(id int,name varchar(10),age int)engine=innodb charset=utf8;
13. 插入数据(增)

• 全表插入格式: insert into 表名 values(值1,值2,值3);
  insert into emp values(1,‘Tom’,8);
• 指定字段插入格式: insert into 表名(字段1名,字段2名) values(值1,值2);
  insert into emp (id,name) values(2,‘Jerry’);
• 中文问题:
  insert into emp values(3,‘刘备’,30);
  如果以上代码报错执行以下命令
  set names gbk;
• 批量插入:
  insert into emp values(4,‘关羽’,25),(5,‘张飞’,18);
  insert into emp (name) values(‘悟空’),(‘八戒’),(‘沙僧’);

2. 查询数据

• 格式: select 字段信息 from 表名 where 条件;
  select name,age from emp; //查询所有数据的name和age
  select name,age from emp where age<20;//20岁以下的name和age
  select * from emp; //查询所有数据的所有字段信息

3. 修改数据

• 格式: update 表名 set 字段名=xxx where 条件;
  update emp set age=500 where name=‘悟空’;
  update emp set age=10 where id=2;

4. 删除数据

• 格式: delete from 表名 where 条件;
  delete from emp where age<20;
  delete from emp where age is null;
  delete from emp;//删除全部数据
  ####数据相关练习

1. 创建hero表 id 名字name 类型type 价格money
2. 保存以下数据
   1,‘诸葛亮’,‘法师’,18888 2,‘周瑜’,‘法师’,13888
   3,‘孙悟空’,‘打野’,18888 4,‘小乔’,‘法师’,13888
   5,‘黄忠’,‘射手’,8888 6,‘刘备’,‘战士’,6888
3. 修改所有18888为28888
4. 修改所有法师为战士
5. 删除价格为6888的英雄
6. 修改小乔为猪八戒
7. 删除价格低于15000的英雄
8. 添加性别gender字段在name的后面
9. 修改所有英雄的性别为男
10. 删除所有数据
11. 删除表

---

###课程回顾

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) engine=innodb/myisam charset=utf8/gbk;
• 查询所有表 show tables;
• 查询表详情 show create table t1;
• 查询表字段 desc t1;
• 删除表 drop table t1;
• 修改表名 rename table t1 to t2;
• 修改表引擎和字符集 alter table t1 engine=innodb/myisam charset=utf8/gbk;
• 添加表字段 alter table t1 add 字段名 类型 first/after xxx;
• 删除表字段 alter table t1 drop 字段名;
• 修改字段名和类型 alter table t1 change 原名 新名 新类型
• 修改字段类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;

3. 数据相关SQL

• 插入数据 insert into 表名 values(值1,值2),(值1,值2),(值1,值2);
  insert into 表名 (字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);
• 查询数据 select 字段信息 from 表名 where 条件;
• 修改数据 update 表名 set 字段名=值 where 条件;
• 删除数据 delete from 表名 where 条件;
  ###练习题

1. 创建数据库mydb2 字符集utf8 并使用
   create database mydb2 character set utf8;
   use mydb2;
2. 创建员工表emp 有id和name字段
   create table emp (id int,name varchar(10));
3. 修改表名为t_emp
   rename table emp to t_emp;
4. 添加部门名称字段dept
   alter table t_emp add dept varchar(10);
5. 在name后面添加age字段
   alter table t_emp add age int after name;
6. 插入刘关张三个人id分别为1,2,3部门都是三国部，再插入唐僧师徒4个人，id分别为4567 部门为取经部
   insert into t_emp values
   (1,‘刘备’,38,‘三国部’),(2,‘关羽’,37,‘三国部’),(3,‘张飞’,36,‘三国部’),(4,‘唐僧’,30,‘取经部’),(5,‘悟空’,500,‘取经部’),(6,‘八戒’,300,‘取经部’),(7,‘沙僧’,200,‘取经部’);
7. 给表再添加工资字段，添加到部门字段的前面
   alter table t_emp add sal int after age;
8. 修改取经部门的工资为100
   update t_emp set sal=100 where dept=‘取经部’;
9. 修改唐僧的名字为唐长老
   update t_emp set name=‘唐长老’ where name=‘唐僧’;
10. 修改三国部为蜀国部
    update t_emp set dept=‘蜀国部’ where dept=‘三国部’;
11. 删除猪八戒
    delete from t_emp where name=‘八戒’;
12. 删除蜀国部的人
    delete from t_emp where dept=‘蜀国部’;
13. 修改所有员工的工资为800
    update t_emp set sal=800;
14. 删除所有人 15. 最后删除表
    delete from t_emp;
    drop table t_emp;
    ###主键约束 primary key

• 什么是主键: 表示数据唯一性的字段称为主键

• 什么是约束: 是创建表时给表字段添加的限制条件

• 主键约束: 让该字段的数据唯一且非空(不能重复,不能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
  ###主键约束+自增

• 自增数值只增不减,从历史最大值基础上+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(3,‘ccc’); //3
  insert into t2 values(10,‘ddd’); //10
  insert into t2 values(null,‘eee’); //11
  delete from t2 where id>=10;
  insert into t2 values(null,‘fff’); //12
  ###注释

• 格式:
  create table t3
  (id int primary key auto_increment comment ‘这是主键’,name varchar(10) comment ‘这是名字’);
  ###`的作用

• 用于修饰表名和字段名,可以省略
  create table t4(id int,name varchar(10));
  ###冗余

• 由于表设计不够合理导致的大量重复数据称为数据冗余

• 练习:

  1. 创建表保存以下数据
     集团总部下的教学研发部下的Java教研部下的苍老师工资200年龄18性别男
     集团总部下的市场部下的市场A部下的小明工资5000年龄25性别男
  • 创建员工表和部门表
    create table emp(id int primary key auto_increment,name varchar(10),age int,sal int,gender varchar(10),dept_id int);
    create table dept(id int primary key auto_increment,name varchar(10),parent_id int);
  • 插入数据:
    insert into dept values(null,‘集团总部’,null),(null,‘教学研发部’,1),(null,‘Java教研部’,2),(null,‘市场部’,1),(null,‘市场A部’,4);
    insert into emp values(null,‘苍老师’,18,200,‘男’,3),(null,‘小明’,25,5000,‘男’,5);

• 练习2:
  创建商品表item和分类表category保存以下数据

  1. 保存家电分类下电视机分类下的小米电视 价格(price)1888 库存(num)200
  2. 办公用品分类下的打印机分类下的惠普打印机 价格1500 库存100
  • 创建表
    create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
    create table category(id int primary key auto_increment,name varchar(10),parent_id int);
  • 插入数据:
    insert into category values(null,‘家电’,null),(null,‘电视机’,1),(null,‘办公用品’,null),(null,‘打印机’,3);
    insert into item values(null,‘小米电视’,1888,200,2),(null,‘惠普打印机’,1500,100,4);
    ###事务

• 什么是事务:事务是数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败.

• 事务相关指令:

  1. 开启事务 begin;
  2. 提交事务 commit;
  3. 回滚 rollback;

• 验证转账流程:
  create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
  insert into user values(null,‘超人’,50,‘冻结’),(null,‘蝙蝠侠’,5000,‘正常’),(null,‘灭霸’,20,‘正常’);

• 转账的SQL:
  update user set money=money-2000 where id=2 and status=‘正常’;
  update user set money=money+2000 where id=1 and status=‘正常’;

• 有事务保护的情况下 回滚流程:

  1. 开启事务
     begin;
  2. 蝙蝠侠-2000
     update user set money=money-2000 where id=2 and status=‘正常’;
  3. 此时在当前终端查询数据时 数据已经改变(因为查询到的是内存中的改动), 开启另外一个终端查询数据发现数据是没有改变的(因为新的终端查询到的是磁盘的数据)
  4. 超人+2000
     update user set money=money+2000 where id=1 and status=‘正常’;
  5. 此时从执行结果中发现一条成功一条失败,应该执行回滚操作
     rollback;

• 有事务保护的情况下 提交流程:

  1. 开启事务
     begin;
  2. 蝙蝠侠-2000
     update user set money=money-2000 where id=2 and status=‘正常’;
  3. 此时仍然是在内存中改动 磁盘数据没有发生改变
  4. 灭霸+2000
     update user set money=money+2000 where id=3 and status=‘正常’;
  5. 此时两次改动都是在内存中改完,发现两次全部成功,所以执行提交
     commit;

• 保存回滚点:
  begin;
  update user set money=1 where id=2;
  savepoint s1;
  update user set money=2 where id=2;
  savepoint s2;
  update user set money=3 where id=2;
  rollback to s2;

• 事务的ACID特性 面试常考
  保证事务正确执行的四大基本要素

1. Atomicity原子性: 最小不可拆分 保证全部执行成功或全部执行失败
2. Consistency一致性: 从一个一致状态到另一个一致状态
3. Isolation隔离性: 多个事务之间互相隔离互不影响
4. Durability持久性: 当事务提交后数据保存到磁盘中持久生效
   ###SQL分类
   ####DDL Data Definition Language数据定义语言

• truncate table 表名;
  删除表并创建新表 让自增数值清零
• 包括: create drop alter truncate
• 不支持事务
  ####DML Data Manipulation Language数据操作语言
• 包括: insert update delete select
• 支持事务
  ####DQL Data Query Language 数据查询语言
• 只包括: select
  ####TCL Transaction Control Language 事务控制语言
• 包括: begin,commit,rollback,savepoint xxx, rollback to xxx;
  ####DCL Data Control Language 数据控制语言
• 负责分配用户权限相关的SQL

###数据类型

1. 整数: int(m) 和 bigint(m) m代表的是显示长度,需要结合zerofill使用
   create table t_int(id int,age int(10) zerofill);
   insert into t_int values(1,18);
   select * from t_int;
2. 浮点数: double(m,d) m代表的是总长度 d代表小数长度,超高精度的浮点数decimal(m,d)
   25.234 m=5 d=3
3. 字符串:

• char(m) 固定长度 最大长度255 好处执行效率略高
• varchar(m) 可变长度 好处节省空间 最大长度65535(但是超过255建议使用text)
• text(m) 可变长度 最大长度65535

4. 日期:

• date: 保存年月日

• time: 保存时分秒

• datetime: 保存年月日时分秒,默认值为null,最大9999-12-31

• timestamp(时间戳):保存年月日时分秒,默认值为当前系统时间,最大值2038-1-19
  create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
  insert into t_date values(‘2019-6-20’,null,null,null);
  insert into t_date values(null,‘16:46:30’,‘2019-06-20 16:30:30’,null);
  ###导入*.sql文件

• windows系统 把文件放在C或D盘的根目录
  source d:/tables.sql;

• linux系统 把文件放在桌面
  source /home/soft01/桌面/tables.sql

  show databases;
  use newdb3;
  show tables; 四张表

  select * from t_item;
  如果是乱码 执行 set names gbk;

###课程回顾
####day01

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);
• show tables;
• show create table t1;
• desc t1;
• drop table t1;
• truncate table t1;
• rename table t1 to t2;
• alter table t1 engine=myisam/innodb charset=utf8/gbk;
• alter table t1 add 字段名 类型 first/after xxx;
• alter table t1 drop 字段名;
• alter table t1 change 原名 新名 新类型
• alter table t1 modify 字段名 新类型 first/after xxx;

3. 数据相关

• insert into t1 (name,age) values(值1,值2),(值1,值2),(值1,值2);
• select * from t1 where 条件;
• update t1 set xxx=xxxx where 条件;
• delete from t1 where 条件;
  ####day02

1. 主键约束: 唯一且非空 primary key
2. 主键+自增 auto_increment
   只增不减 从历史最大值+1
3. 注释 comment
4. ` 用于修饰表名和字段名 可以省略
5. 冗余: 由于表设计不够合理导致的大量重复数据 ,通过合理拆分表解决
6. 事务: 数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部执行成功或全部执行失败

• 相关指令: 开启事务begin 提交事务 commit 回滚 rollback
• 保存回滚点 savepoint xxx; rollback to xxx;
• 四大特性
  1. 原子性: 最小不可拆分 保证全部成功或全部失败
  2. 一致性: 从一个一致状态到另一个一致状态
  3. 隔离性: 多个事务互相隔离 互不影响
  4. 持久性: 事务提交后 数据保存到磁盘中持久生效

7. 数据类型

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

8. 导入sql文件
   source 路径;

   ---

   ###课程回顾

9. 主键约束 primary key
   唯一且非空

10. 主键约束+自增 primary key auto_increment
    历史最大值+1 只增不减

11. 注释 comment

12. `用于修饰表名和字段名 可以省略

13. 冗余: 由于表设计不够合理导致的大量重复数据,通过合理拆分表解决

14. 事务:数据库中执行同一业务多条SQL语句的工作单元,可以保证多条SQL全部成功或全部失败

• 相关SQL:
  开启事务begin;
  提交事务commit;
  回滚事务rollback;
  保存回滚点 savepoint xxx;
  回滚到某个点 rollback to xxx;
• 事务的四大特性ACID
  1. 原子性:最小不可拆分 保证全部成功或全部失败
  2. 一致性:从一个一致状态 到另一个一致状态
  3. 隔离性:多个事务互相隔离 互不影响
  4. 持久性:事务提交后 数据保存到磁盘中持久生效

7. SQL分类

• DDL:数据定义语言,包括:create drop alter truncate 不支持事务
• DML:数据操作语言,包括:insert update delete select(DQL) 支持事务
• DQL:数据查询语言,只包括select
• TCL:事务控制语言,包括:开启事务begin;
  提交事务commit;
  回滚事务rollback;
  保存回滚点 savepoint xxx;
  回滚到某个点 rollback to xxx;
• DCL:数据控制语言, 用于分配用户权限相关SQL

8. 数据类型

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

9. 导入*.sql文件
   source 路径;

###is null 和 is not null

1. 从员工表中查询 没有上级领导的员工姓名,工资
   select ename,sal from emp where mgr is null;
2. 查询有领导的员工姓名和上级领导的编号
   select ename,mgr from emp where mgr is not null;
   ###别名
   select ename as ‘姓名’ from emp;
   select ename ‘姓名’ from emp;
   select ename 姓名 from emp;
   ###比较运算符 > < >= <= = !=和<>
3. 查询工资在2000以下的员工姓名和工资
   select ename,sal from emp where sal<2000;
4. 查询职位(job)是manager的员工姓名、工资、职位
   select ename,sal,job from emp where job=‘manager’;
5. 查询工资小于等于1600的员工姓名、职位、工资
   select ename,job,sal from emp wehere sal<=1600;
6. 查询不是10号部门的员工姓名和部门编号（两种写法）
   select ename,deptno from emp where deptno!=10;
   select ename,deptno from emp where deptno<>10;
7. 查询商品表（t_item）中单价为23的商品信息
   select * from t_item where price=23 \G;
8. 查询单价不是8443的商品标题(title)
   select title from t_item where price!=8443;
   ###去重 distinct
9. 查询员工表中有哪些职位
   select distinct job from emp;
10. 查询员工表中出现了那几个部门编号
    select distinct deptno from emp;
    ###and 和 or

• 如果查询数据时需要同时满足多个条件则使用and &&
• 如果查询数据时只需要满足条件中的某一个则使用or ||

1. 查询10号部门中工资低于2000的员工信息
   select * from emp where deptno=10 and sal<2000;
2. 查询部门是30号或者工资高于3000的员工信息
   select * from emp where deptno=30 or sal>3000;
   ###模糊查询 like

• % 代表0或多个未知字符
• _ 代表单个未知字符
  举例: 第一个字符是x x%
  最后一个字符是y %y
  包含 %x%
  倒数第二个字符是x %x_
  x开头y结尾 x%y
  第二个字符是x 倒数第三个字符是y x%y_

1. 查询名字以j开头的所有员工姓名
   select ename from emp where ename like ‘j%’;
2. 查询名字第二个字符是l的员工姓名和工资
   select ename,sal from emp where ename like ‘_l%’;
3. 查询t_item表中 标题中包含记事本的商品标题
   select title from t_item where title like ‘%记事本%’;
4. 查询职位中包含an并且工资高于1500的员工姓名、工资、职位
   select ename,sal,job from emp where job like ‘%an%’ and sal>1500;
5. 查询有赠品的dell商品详情(卖点sell_point中包含“赠”，并且title包含dell)
   select * from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;
6. 查询单价低于100块钱的笔记本标题和价格(提示:title包含笔记本)
   select title,price from t_item where price<100 and title like ‘%笔记本%’;
7. 查询有图片的得力商品信息（image字段不为null,title包含得力）
   select * from t_item where image is not null and title like ‘%得力%’;
8. 查询不包含a的员工姓名
   select ename from emp where ename not like ‘%a%’;
   ###between x and y
9. 查询工资在2000到3000之间的员工姓名和工资(要求包含2000和3000)
   select ename,sal from emp where sal>=2000 and sal<=3000;
   select ename,sal from emp where sal between 2000 and 3000;
10. 查询单价在50到100之间的商品标题和单价
    select title,price from t_item where price between 50 and 100;
11. 查询工资在1000到2000之外的员工姓名和工资
    select ename,sal from emp where sal not between 1000 and 2000;
    ###in

• 当查询字段值为多个值的时候使用

1. 查询员工工资为800,1300,1500的员工信息
   select * from emp where sal=800 or sal=1300 or sal=1500;
   select * from emp where sal in(800,1300,1500);
2. 查询商品价格为56,58,89的商品标题和单价
   select title,price from t_item where price in(56,58,89);
3. 查询工资不等于3000,5000,1500的员工姓名和工资
   select ename,sal from emp where sal not in(3000,5000,1500);
   ####综合练习题
4. 查询分类id为238,917的商品信息 (涉及字段自己查)
   select * from t_item where category_id in(238,917);
5. 查询价格在50-200之间的得力商品标题和价格
   select title,price from t_item where price between 50 and 200 and title like ‘%得力%’;
6. 查询有上级领导并且工资小于2000的员工姓名、工资和领导编号
   select ename,sal,mgr from emp where mgr is not null and sal<2000;
7. 查询有奖金并且有上级领导的员工姓名、奖金、领导编号
   select ename,comm,mgr from emp where comm>0 and mgr is not null;
8. 查询名字中包含a,并且工资在3000以内的员工从事的职业有哪几种
   select distinct job from emp where ename like ‘%a%’ and sal<3000;
   ###排序 order by

• 格式: order by 字段名 asc/desc asc:升序(默认) desc:降序
• order by 写在条件的后面

1. 查询工资小于3000的员工姓名和工资 要求按照工资降序排序
   select ename,sal from emp where sal<3000 order by sal desc;
2. 查询10号部门每个员工的姓名和工资 按照工资升序排序
   select ename,sal from emp where deptno=10 order by sal;
3. 查询有奖金的员工姓名、工资、奖金 按照奖金降序排序
   select ename,sal,comm from emp where comm>0 order by comm desc;
4. 查询单价低于100的商品标题和单价并按照单价升序排序
   select title,price from t_item where price<100 order by price;

• 多字段排序: order by 字段1 asc/desc,字段2 asc/desc

5. 查询每个员工的姓名、工资和部门编号 按照部门编号降序排序,如果部门一致则按照工资升序排序
   select ename,sal,deptno from emp order by deptno desc,sal;
   ####分页查询 limit 跳过的条数,请求的条数

• 请求第一页的10条数据 limit 0,10
• 请求第三页的10条数据 limit 20,10
• 请求第八页的5条数据 limit 35,5
• 公式 limit (页数-1)*数量,数量
• 请求第四页的7条数据 limit 21,7

1. 查询每个员工的姓名和工资,按照工资降序排序 请求第三页的3条数据
   select ename,sal from emp order by sal desc limit 6,3;
2. 请求员工表中工资最高的前三名员工的信息
   select * from emp order by sal desc limit 0,3;
3. 查询商品标题和单价 按照单价升序排序 第三页的5条数据
   select title,price from t_item order by price limit 10,5;
4. 查询30号部门中工资最高的员工信息
   select * from emp where deptno=30 order by sal desc limit 0,1;
   ###数值计算 + - * / %
5. 查询员工姓名,工资和年终奖(年终奖=工资5)
   select ename,sal,sal5 年终奖 from emp;
6. 查询商品标题，商品单价，库存和总价值（单价库存）
   select title,price,num,pricenum 总价值 from t_item;
7. 查询每个员工的姓名和 加薪5块钱之后的工资
   select ename,sal+5 from emp;
   ###日期相关SQL
8. 获取系统时间 now()
   create table t_date(name varchar(10),birthday datetime);
   insert into t_date values(‘刘德华’,now());
9. 获取当前的年月日 和当前的时分秒 cur=current 当前
   select “hellworld”;
   select curdate(),curtime();
10. 从年月日时分秒中 提取年月日 和 提取时分秒
    select date(now());
    select time(now());
    查询商品的上传日期 只查询年月日
    select created_time from t_item; //商品上传年月日时分秒
    select date(created_time) from t_item;//商品上传年月日
11. 从年月日时分秒中提取时间分量
    select extract(year from now());
    select extract(month from now());
    select extract(day from now());
    select extract(hour from now());
    select extract(minute from now());
    select extract(second from now());
    查询员工入职的年份
    select extract(year from hiredate) from emp;
12. 日期格式化 date_format(时间,格式);

• 格式规则:
  %Y 四位年 2019 %y 两位年 19
  %m 2位月 06 %c 1位月 6
  %d 日
  %H 24小时 %h 12小时
  %i 分钟
  %s 秒
• 测试: 把now() 2019-06-21 15:29:30 转成 2019年06月21号 15点29分30秒
  select date_format(now(),’%Y年%m月%d号 %H点%i分%s秒’);

6. 反向格式化 str_to_date(时间,格式)
   21.06.2019 15点36分20秒 转回 2019-06-21 15:36:20
   select str_to_date(‘21.06.2019 15点36分20秒’,’%d.%m.%Y %H点%i分%s秒’);
   ###ifnull(x,y)

• age=ifnull(x,y) 如果x值为null则age=y 否则age=x
• 练习:修改奖金为null的值为0 不为null则不变
  update emp set comm=ifnull(comm,0);
  ###聚合函数
• 对查询的多条数据进行统计查询: 平均值,最大值,最小值,求和,计数

1. 平均值 avg(字段名)
   -查询员工的平均工资
   select avg(sal) from emp;
2. 最大值 max(字段名)
   -查询20号部门的最高工资
   select max(sal) from emp where deptno=20;
3. 最小值 min(字段名)
   -查询名字里面包含a的员工的最低工资
   select min(sal) from emp where ename like ‘%a%’;
4. 求和 sum(字段名)
   -查询10号部门工资总和
   select sum(sal) from emp where deptno=10;
5. 计数 count(字段名)
   -查询工资高于2000的员工数量
   select count(*) from emp where sal>2000;

• 查询20号部门的平均工资,最高工资,最低工资,工资总和,员工数量
  select avg(sal),max(sal),min(sal),sum(sal),count(*) from emp where deptno=20;
  ###字符串相关

1. 字符串拼接concat(s1,s2); s1s2
   select concat(‘aa’,‘bb’);
   • 查询每个员工的姓名和工资 要求工资以元为单位
     select ename,concat(sal,‘元’) from emp;
2. 获取字符串的长度 char_length(str);
   select char_length(‘abc’);
   • 查询每个员工的姓名和名字长度
     select ename,char_length(ename) from emp;
3. 获取字符串出现的位置 instr(str,substr);
   select instr(‘abcdefg’,‘d’);
4. 转大写和转小写
   select upper(‘abc’),lower(‘NBA’);
5. 截取字符串
   • 左边截取 select left(‘abcdefg’,2);
   • 右边截取 select right(‘abcdefg’,2);
   • 自由截取 select substring(‘abcdefg’,2,3); 3代表长度
6. 去两端空白 trim()
   select trim(’ a b ');
7. 重复repeat()
   select repeat(‘ab’,2);
8. 替换replace()
   select replace(‘abcde abc’,‘b’,‘m’);
9. 反转reverse()
   select reverse(‘abc’);

1 案例：查询没有上级领导的员工的编号，姓名，工资
2 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
3 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
4 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
5 案例：查询emp表中名字以‘S’开头的所有员工的姓名
6 案例：查询emp表中名字的最后一个字符是’S’的员工的姓名
7 案例：查询倒数的第2个字符是‘E’的员工的姓名
8 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
9 案例：查询emp表中员工的名字中包含‘A’的员工的姓名
10 案例：查询emp表中名字不是以’K’开头的员工的所有信息
11 案例：查询emp表中名字中不包含‘A’的所有员工的信息
12 案例：做文员的员工人数（job 中 含有 CLERK 的）
13 案例：销售人员 job: SALESMAN 的最高薪水
14 案例：最早和最晚入职时间
15 案例：查询类别 163的商品总库存量
16 案例：查询 类别 163 的商品
17 案例：查询商品价格不大于100的商品名称列表
18 案例：查询品牌是联想,且价格在40000以上的商品名称和价格
19 案例：查询品牌是三木,或价格在50以下的商品名称和价格
20 案例：查询品牌是三木、广博、齐心的商品名称和价格
21 案例：查询品牌不是联想、戴尔的商品名称和价格
22 案例：查找品牌是联想且价格大于10000的名称
23 案例：查询联想或戴尔的电脑名称列表
24 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
25 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
26 案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
27 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
28 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
29 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
30 案例：查询不是30号部门的员工的所有信息
31 案例：查询奖金不为空的员工的所有信息
32 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
33 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
34 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
35 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
36 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
37 案例：统计emp表中员工的总数量
38 案例：统计emp表中获得奖金的员工的数量
39 案例：求出emp表中所有的工资累加之和
40 案例：求出emp表中所有的奖金累加之和
41 案例：求出emp表中员工的平均工资
42 案例：求出emp表中员工的平均奖金
43 案例：求出emp表中员工的最高工资
44 案例：求出emp表中员工编号的最大值
45 案例：查询emp表中员工的最低工资。
46 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。

---

###课程回顾
####day01

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) engine=myisam/innodb charset=utf8/gbk;
• 查询所有 show tables;
• 查询详情 show create table t1;
• 查看表字段 desc t1;
• 删除 drop table t1;
• 修改表名 rename table t1 to t2;
• 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
• 添加表字段 alter table t1 add 字段名 类型 first/after xxx;
• 删除表字段 alter table t1 drop 字段名;
• 修改字段名和类型 alter table t1 change 原名 新名 新类型
• 修改类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;

3. 数据相关

• 插入数据 insert into t1 (字段名1,字段名2,字段名3) values(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
• 查询数据 select * from t1 where 条件;
• 修改数据 update t1 set xxx=xxxx where 条件;
• 删除数据 delete from t1 where 条件;
  ####day02

1. 主键约束: 唯一且非空 primary key
2. 自增: auto_increment ,只增不减 从历史最大值+1
3. 注释 comment
4. `用于修饰表名和字段名
5. 冗余: 由于表设计不够合理导致的大量重复数据,通过合理拆分表解决
6. 事务: 数据库中执行同一业务多条SQL语句的工作单元,可以保证全部执行成功或全部执行失败

• 相关SQL: 开启事务begin 提交事务commit 回滚事务 rollback 保存回滚点 savepoint xxx; 回滚到回滚点 rollback to xxx;
• 四大特性
  1. 原子性: 最小不可拆分 保证全部成功或全部失败
  2. 一致性: 从一个一致状态到另一个一致状态
  3. 隔离性: 多个事务互相隔离 互不影响
  4. 持久性: 事务提交后数据保存到磁盘中持久生效

7. SQL分类

• DDL:数据定义语言 包括:create drop alter truncate 不支持事务
• DML: 数据操作语言 包括:insert update delete select(DQL) 支持事务
• DQL:数据查询语言 包括:select
• TCL:事务控制语言 包括:开启事务begin 提交事务commit 回滚事务 rollback 保存回滚点 savepoint xxx; 回滚到回滚点 rollback to xxx;
• DCL:数据控制语言 分配用户权限相关SQL

8. 数据类型

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

9. 导入sql文件 source 路径;
   ####day03
10. is null 和 is not null
11. 别名
12. 比较运算符 > < >= <= = !=和<>
13. and 和 or
14. not between x and y
15. not in(2,3,4)
16. like _单个未知 % 0或多个未知
17. 排序 order by 字段名 asc(默认)/desc,字段名
18. 分页查询 limit 跳过的条数,请求条数
19. 去重 distinct
20. 数值计算 + - * / %
21. 日期相关:

• now() 当前系统时间
• curdate() 当前年月日
• curtime() 当前时分秒
• 提取年月日 date(now())
• 提取时分秒 time(now())
• 提取时间分量 extract(year/month/day/hour/minute/second from now());
• 日期格式化 date_format(时间,格式) %YymcdHhis
• 反向格式化 str_to_date(时间,格式)

13. 聚合函数 平均值avg() 最大值max() 最小值min() 求和sum() 统计数量count()
14. ifull(x,y) 如果x值为null 则取y 如果不为null则取x
15. 字符串相关:

• 拼接 concat()
• 获取字符长度 char_length()
• 获取字符串出现位置 instr()
• 转大小写 upper() lower()
• 截取字符串 left() right() substring()
• 去空白 trim()
• 重复 repeat()
• 替换 replace()
• 反转 reverse()
  ###练习题
  1 案例：查询没有上级领导的员工的编号，姓名，工资
  select empno,ename,sal from emp where mgr is null;
  2 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
  select ename,job,sal,comm from emp where comm=0 or comm is null;
  3 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
  select empno,ename,job,comm from emp where comm>0;
  4 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
  select ename,sal,mgr from emp where mgr is not null;
  5 案例：查询emp表中名字以‘S’开头的所有员工的姓名
  select ename from emp where ename like ‘s%’;
  6 案例：查询emp表中名字的最后一个字符是’S’的员工的姓名
  select ename from emp where ename like ‘%s’;
  7 案例：查询倒数的第2个字符是‘E’的员工的姓名
  select ename from emp where ename like ‘%e_’;
  8 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
  select ename from emp where ename like ‘%n__’;
  9 案例：查询emp表中员工的名字中包含‘A’的员工的姓名
  select ename from emp where ename like ‘%a%’;
  10 案例：查询emp表中名字不是以’K’开头的员工的所有信息
  select * from emp where ename not like ‘k%’;
  11 案例：查询emp表中名字中不包含‘A’的所有员工的信息
  select * from emp where ename not like ‘%a%’;
  12 案例：做文员的员工人数（job 中 含有 CLERK 的）
  select count() from emp where job=‘clerk’;
  13 案例：销售人员 job: SALESMAN 的最高薪水
  select max(sal) from emp where job=‘SALESMAN’;
  14 案例：最早和最晚入职时间
  select min(hiredate),max(hiredate) from emp;
  15 案例：查询类别 163的商品总库存量
  select sum(num) from t_item where category_id=163;
  16 案例：查询 类别 163 的商品
  select * from t_item where category_id=163;
  17 案例：查询商品价格不大于100的商品名称列表
  select title from t_item where price<=100;
  18 案例：查询品牌是联想,且价格在40000以上的商品名称和价格
  select title,price from t_item where title like ‘%联想%’ and price>40000;
  19 案例：查询品牌是三木,或价格在50以下的商品名称和价格
  select title,price from t_item where title like ‘%三木%’ or price<50;
  20 案例：查询品牌是三木、广博、齐心的商品名称和价格
  select title,price from t_item where title like ‘%三木%’ or title like ‘%广博%’ or title like ‘%齐心%’;
  21 案例：查询品牌不是联想、戴尔的商品名称和价格
  select title,price from t_item where title not like ‘%联想%’ and title not like ‘%戴尔%’;
  22 案例：查找品牌是联想且价格大于10000的名称
  select title from t_item where title like ‘%联想%’ and price>10000;
  23 案例：查询联想或戴尔的电脑名称列表
  select title from t_item where title like ‘%联想%’ or title like ‘%戴尔%’;
  24 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
  select empno,ename,job,sal from emp where sal between 1000 and 2000;
  25 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
  select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
  26 案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
  select empno,ename,job,sal from emp where ename like ‘%e%’ and job!=‘manager’;
  27 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
  select empno,ename,deptno from emp where deptno in(10,20);
  28 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
  select empno,ename,job,comm from emp where comm=0 or ename not like ‘%t_’;
  29 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
  select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
  30 案例：查询不是30号部门的员工的所有信息
  select * from emp where deptno!=30;
  31 案例：查询奖金不为空的员工的所有信息
  select * from emp where comm is not null;
  32 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
  select empno,ename,job from emp order by empno desc;
  33 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
  select ename,job,sal from emp where deptno in(10,30) order by sal;
  34 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
  select * from emp order by deptno,empno desc;
  35 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
  select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;
  36 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
  select empno,ename,sal,comm from emp where ename not like ‘%s%’ order by sal,empno desc;
  37 案例：统计emp表中员工的总数量
  select count() from emp;
  38 案例：统计emp表中获得奖金的员工的数量
  select count() from emp where comm>0;
  39 案例：求出emp表中所有的工资累加之和
  select sum(sal) from emp;
  40 案例：求出emp表中所有的奖金累加之和
  select sum(comm) from emp;
  41 案例：求出emp表中员工的平均工资
  select avg(sal) from emp;
  42 案例：求出emp表中员工的平均奖金
  select avg(comm) from emp;
  43 案例：求出emp表中员工的最高工资
  select max(sal) from emp;
  44 案例：求出emp表中员工编号的最大值
  select max(empno) from emp;
  45 案例：查询emp表中员工的最低工资。
  select min(sal) from emp;
  46 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
  select count() 人数,sum(sal) 工资总和,avg(sal) 平均工资, max(comm) 奖金最大, min(comm) 奖金最小 from emp;
  ####数学相关
• 向下取整 floor(num)
  select floor(3.93);
• 四舍五入 round(num)
  select round(3.93);
• 四舍五入 round(num,m) m代表小数位数
  select round(23.879,2);
• 非四舍五入 truncate(num,m) m代表小数位数
  select truncate(23.879,2);
• 随机数 rand() 获得0-1的随机数
  select rand();
  ###分组查询

1. 查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
2. 查询每种工作的最高工资
   select job,max(sal) from emp group by job;
3. 查询每个部门的人数
   select deptno,count(*) from emp group by deptno;
4. 查询每个部门工资高于1500的人数
   select deptno,count(*) from emp where sal>1500 group by deptno;
5. 查询每个主管mgr的手下人数
   select mgr,count(*) from emp where mgr is not null group by mgr;

• 多字段分组:
  group by 字段1名,字段2名

1. 查询每个部门下每种职业的平均工资
   select deptno,job,avg(sal) from emp group by deptno,job;

• 练习:
• 格式: select … from 表名 where … group by … order by … limit …;

1. 查询emp表中每个部门的编号、人数、工资总和,根据人数进行升序排序，如果人数一致根据工资总和降序排序。
   select deptno,count(*) c,sum(sal) s from emp group by deptno order by c,s desc;
2. 查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排序
   select deptno,avg(sal) a,min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by a;
3. 查询有上级领导的员工，每个职业的人数，工资总和，平均工资，根据人数进行降序排序，如果人数一致则根据平均工资进行升序排序
   select job,count(*) c,sum(sal),avg(sal) a from emp where mgr is not null group by job order by c desc,a;
   ####having

• where 后面只能写普通字段的条件
• having 后面写聚合函数的条件 ,having和分组查询结合使用
• 各个关键字的顺序:
  select …from 表名 where …group by … having …
  order by … limit …;

1. 查询每个部门的平均工资要求平均工资大于2000
   select deptno,avg(sal) a from emp group by deptno having a>2000;

2. 查询商品表中每个分类的平均单价，要求平均单价低于100
   select category_id,avg(price) a from t_item group by category_id having a<100;

3. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
   select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc;

4. 查询238，917这两个分类下的商品的平均单价
   select category_id,avg(price) from t_item where category_id in(238,917) group by category_id;

5. 查询emp表中工资在1000-3000之间的员工，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，最后按照平均工资进行升序排序
   select deptno,sum(sal),avg(sal) a from emp where sal between 1000 and 3000 group by deptno having a>=2000 order by a;

6. 查询每年入职的人数
   select extract(year from hiredate) y,count(*) from emp group by y;

7. 查询平均工资最高的部门编号和平均工资
   select deptno,avg(sal) a from emp group by deptno order by a desc limit 0,1;
   ###子查询(嵌套查询)

8. 查询工资高于平均工资的员工信息
   select avg(sal) from emp;
   select * from emp where sal>(select avg(sal) from emp);

9. 查询员工表中工资最高的员工信息
   select max(sal) from emp;
   select * from emp where sal=(select max(sal) from emp);

10. 查询工资高于20号部门最高工资的员工信息
    select max(sal) from emp where deptno=20;
    select * from emp where sal>(select max(sal) from emp where deptno=20);

11. 查询和jones相同工作的其它员工信息
    select job from emp where ename=‘jones’;
    select * from emp where job=(select job from emp where ename=‘jones’) and ename!=‘jones’;

12. 查询工资最低员工的同事们的信息（同事指同一部门）
    select min(sal) from emp;

    select deptno from emp where sal=(select min(sal) from emp);

    select * from emp where deptno=(第二坨) and sal != (第一坨);

13. 查询最后入职的员工信息
    select max(hiredate) from emp;
    select * from emp where hiredate=(select max(hiredate) from emp);

14. 查询king的部门信息(需要用到dept表)
    select deptno from emp where ename=‘king’;
    select * from dept where deptno=(select deptno from emp where ename=‘king’);

15. 查询员工表中出现过的部门的信息(部门表里面有个40号部门,需要过滤掉它)

    • 先查询员工表中出现的部门编号
      select distinct deptno from emp;
    • 去部门表中查询详情
      select * from dept where deptno in(select distinct deptno from emp);

16. 查询平均工资最高的部门信息(难度最高,需要考虑并列第一的问题)

    • 先查询平均工资最高的值
      select avg(sal) a from emp group by deptno order by a desc limit 0,1;
    • 通过最高的平均工资 查询 部门编号
      select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
    • 通过部门编号 查询部门信息
      select * from dept where deptno in(上面一坨);

• 子查询可以写的位置
  1. 写在where和having后面,当做查询条件的值
  2. 写在创建表的时候,把查询结果保存到新的表中
     create table emp_10 as (select * from emp where deptno=10);
  3. 写在from后面 一定要有别名
     select * from emp where deptno=10;
     select ename from (select * from emp where deptno=10) t;
     ###关联查询
• 同时查询多张表的查询方式称为关联查询
• 关联查询必须写关联关系,如果不写则会得到两张表的乘积,这个乘积称为笛卡尔积, 这是一个错误的查询结果,切记工作中不要出现.

1. 查询每一个员工的姓名和对应的部门名
   select e.ename,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;
   ####关联查询的查询方式之 等值连接和内连接
2. 等值连接:
   select * from A,B where A.x=B.x and A.y>2000;
3. 内连接:
   select * from A [?inner] join B on A.x=B.x where A.y>2000;
   -查询每一个员工的姓名和对应的部门名
   select e.ename,d.dname
   from emp e join dept d
   on e.deptno=d.deptno;
   -查询工资高于2000的每个员工的姓名 工资和对应的部门地点
   select e.ename,e.sal,d.loc
   from emp e join dept d
   on e.deptno=d.deptno
   where e.sal>2000;
   • 查询在new york工作的员工姓名和工资
     select e.ename,e.sal
     from emp e join dept d
     on e.deptno=d.deptno
     where d.loc=‘new york’;
   • 查询james的部门名称和地点
     select d.dname,d.loc
     from emp e join dept d
     on e.deptno=d.deptno
     where e.ename=‘james’;
     ###关联查询的查询方式之 外连接

• 等值连接和内连接查询的是两张表的交集数据
• 外连接查询的是一张表的全部数据和另外一张表的交集数据
• 格式:
  select * from A left/right join B on A.x=B.x where A.y<2000;
  • 查询所有的部门名和对应的员工姓名
    select d.dname,e.ename
    from emp e right join dept d
    on e.deptno=d.deptno;
• 总结: 如果查询的数据是两张表的交集数据使用等值连接或内连接(推荐),如果查询的是一张表的全部数据和另外一张表的交集数据则使用外连接

###练习题

1. 每个部门的人数，根据人数降序排序
   select deptno,count(*) c from emp group by deptno order by c desc;

2. 每个部门中，每个主管的手下人数
   select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

3. 每种工作的平均工资
   select job,avg(sal) from emp group by job;

4. 每年的入职人数
   select extract(year from hiredate) y,count(*) from emp group by y;

5. 拿最低工资的员工信息
   select * from emp where sal=(select min(sal) from emp);

6. 少于等于3个人的部门信息

   • 不考虑40号部门
     select deptno from emp group by deptno having count(*)<=3;

   select * from dept where deptno in(上面一坨);

   • 考虑40号部门
     select d.*
     from emp e right join dept d
     on e.deptno=d.deptno
     group by d.deptno
     having count(e.ename)<=3;

7. 只有一个下属的主管信息

8. 每月发工资最多的部门信息

9. 下属最多的人，查询其个人信息

10. 拿最高工资员工的同事信息

11. 和最后入职的员工在同一部门的员工信息 实现流程和第十题一样

12. 查询平均工资高于20号平均工资的部门信息

13. 查询员工信息和员工对应的部门名称

14. 查询员工信息，部门名称，所在城市

15. 查询Dallas市所有的员工信息

16. 计算每个城市的员工数量

17. 查询员工信息和他的主管姓名

18. 员工信息，员工主管名字，部门名

19. 员工名和他所在部门名

20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资

21. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址

22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

23. 只有一个下属的主管信息

    • 查询只有一个下属的主管编号
      select mgr from emp where mgr is not null group by mgr having count(*)=1;
    • 通过主管编号查询主管信息
      select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count(*)=1);

24. 每月发工资最多的部门信息

    • 得到最高的工资数
      select sum(sal) s from emp group by deptno order by s desc limit 0,1;
    • 通过最高工资获取部门编号
      select deptno from emp group by deptno having sum(sal)=(select sum(sal) s from emp group by deptno order by s desc limit 0,1);
    • 通过部门编号得到部门信息
      select * from dept where deptno in(上面一坨);

25. 下属最多的人，查询其个人信息

    • 得到最多的人数
      select count() from emp group by mgr order by count() desc limit 0,1;
    • 通过人数获取主管编号
      select mgr from emp group by mgr having count()=(select count() from emp group by mgr order by count(*) desc limit 0,1);
    • 通过主管编号得到员工信息
      select * from emp where empno in(上面一坨);

26. 拿最高工资员工的同事信息

    • 查询最高工资
      select max(sal) from emp;
    • 查询最高工资员工的部门编号
      select deptno from emp where sal=(select max(sal) from emp);
    • 通过部门编号查询员工信息
      select * from emp where deptno=(select deptno from emp where sal=(select max(sal) from emp)) and sal!=(select max(sal) from emp);

27. 和最后入职的员工在同一部门的员工信息 实现流程和第十题一样
    -得到时间最大值
    select max(hiredate) from emp;

    • 获取最后入职员工的部门编号
      select deptno from emp where hiredate=(select max(hiredate) from emp);
    • 通过部门编号获取员工信息
      select * from emp where deptno=(select deptno from emp where hiredate=(select max(hiredate) from emp)) and hiredate!=(select max(hiredate) from emp);

28. 查询平均工资高于20号平均工资的部门信息
    -20号部门的平均工资
    select avg(sal) from emp where deptno=20;

    • 查询高于20号部门平均工资的部门编号
      select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20);
    • 通过部门编号查询部门信息
      select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20));

29. 查询员工信息和员工对应的部门名称
    select e.*,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;

30. 查询员工信息，部门名称，所在城市
    select e.*,d.dname,d.loc
    from emp e join dept d
    on e.deptno=d.deptno;

31. 查询Dallas市所有的员工信息
    select e.*
    from emp e join dept d
    on e.deptno=d.deptno
    where d.loc=‘dallas’;

32. 计算每个城市的员工数量
    select d.loc,count(e.ename)
    from emp e right join dept d
    on e.deptno=d.deptno group by d.loc;

33. 查询员工信息和他的主管姓名

• 自关联查询时把一张表当成两张表
  select e.ename,m.ename
  from emp e join emp m
  on e.mgr=m.empno;

18. 员工信息，员工主管名字，部门名
    select e.*,m.ename,d.dname
    from emp e join emp m
    on e.mgr=m.empno
    join dept d
    on e.deptno=d.deptno;
19. 员工名和他所在部门名
    select e.ename,d.dname
    from emp e join dept d
    on e.deptno=d.deptno;
20. 案例：查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资
    select e.ename,m.empno,m.ename,m.job,m.sal
    from emp e left join emp m
    on e.mgr=m.empno;
21. 案例：查询emp表中名字中没有字母’K’的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
    select e.empno,e.ename,e.job,d.*
    from emp e join dept d
    on e.deptno=d.deptno
    where e.ename not like ‘%k%’;
22. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
    select d.*,e.empno,e.ename,e.job,e.sal
    from emp e right join dept d
    on e.deptno=d.deptno;

###课程回顾

1. 数学相关函数

• 向下取整 floor()
• 四舍五入 round(num,m) m代表小数位数
• 非四舍五入 truncate(num,m)
• 随机数 rand() 0-1

2. 分组查询 group by 字段名,字段名 having 聚合函数的条件
3. 子查询(嵌套查询)

• 可写的位置:
  1. 写在where和having后面 当做查询条件的值
  2. 写在创建表的时候 把查询的数据保存到新的表中
  3. 写在from后面 当成一张虚拟的表 必须要有别名

4. 关联查询

• 查询方式有:
  1. 等值连接 查询两张表的交集数据
     select * from A,B where A.x=B.x and A.y>2000;
  2. 内连接 查询两张表的交集数据
     select * from A join B on A.x=B.x where A.y>2000;
  3. 外连接 查询一张表的全部数据和另外一张表的交集数据
     select * from A left/right join B on A.x=B.x where A.y>2000;
• 关联查询必须接关联关系,如果不写会得到两张表数据的乘积 称为笛卡尔积

---

关系

1. 一对一
2. 一对多
3. 多对多

一对一

为了提高数据查询性能,经常将概要信息和详细信息进行分表存储, 两个表中数据是一对一关系.

[外链图片转存失败(img-vtBfMstd-1565311382216)(one2one.png)]

1. 创建商品表

   create table t_product(
   id int,
   name varchar(30),
   abstract varchar(100),
   price decimal(8,2)
   );

   create table t_product_info(
   	id int,
   	cpu varchar(20),
   	ram varchar(20),
   	hdd varchar(20)
   );
   

2. 添加数据

   insert into t_product (id, name, abstract, price)
   values (1, “P30”, “Top 1 Mobile”, 500.00);

   insert into t_product_info (id, cpu, ram, hdd)
   values (1, "960", "6GB RAM", "128GB");
   

3. 查询商品概要信息

   select id, name from t_product;

4. 查询商品详细信息, 两个表连接查询

   select p.id, name, cpu, ram, hdd
   from t_product p join t_product_info i
   on p.id = i.id;

一对多关系

一个表中的一行关联对应另外一个表中的多行, 称为一对多关系. 在表中采用外键关联实现, 在多方增加一个列, 其取值来自于另外一个表的主键.

订单与订单项目构成一对多关系

[外链图片转存失败(img-FDUaiyLe-1565311382223)(one2many.png)]

1. 创建表

   create table t_order(
   id int,
   username varchar(50)
   );

   create table t_order_item(
   	id int,
   	oid int,
   	product varchar(100),
   	price double
   );
   

   oid 是外键,关联到 t_order 表单的id主键

   外键关联: 一个表中的一个列取值于另外一个表的主键,称为外键关联关系, 逻辑上就是一对多关系.

2. 插入数据

   insert into t_order (id, username)
   values (1, ‘范传奇’);

   insert into t_order_item (id, oid, product, price)
   values (1, 1, '大黄蜂', 500);
   insert into t_order_item (id, oid, product, price)
   values (2, 1, '擎天柱', 1500);
   insert into t_order_item (id, oid, product, price)
   values (3, 1, '威震天', 200);
   

3. 查询订单编号为1的完整订单

   select o.id, username, product, price
   from t_order o
   join t_order_item i
   on o.id = i.oid
   where o.id=1;

案例: 完成 部门-员工的一对多关系, 建表及插入数据并且查询, 部门属性和员工数据请自行合理推测

特殊一对多关系: 自关联关系

商品分类问题?

利用自关联关系实现多级无限分类.

[外链图片转存失败(img-GrfQ6VRr-1565311382223)(category.png)]

1. 建表

   create table t_category(
   id int,
   pid int,
   name varchar(100)
   );

2. 插入数据

   – 根分类
   insert into t_category (id, pid, name)
   values (0, null, ‘主分类’);

   -- 一级分类
   insert into t_category (id, pid, name) 
   values (1, 0, '家电');
   insert into t_category (id, pid, name) 
   values (2, 0, '数码');
   insert into t_category (id, pid, name) 
   values (3, 0, '服装');
   
   -- 二级分类
   insert into t_category (id, pid, name) 
   values (4, 1, '电视');
   insert into t_category (id, pid, name) 
   values (5, 1, '冰箱');
   
   -- 三级分类
   insert into t_category (id, pid, name) 
   values (6, 4, '数码电视');
   insert into t_category (id, pid, name) 
   values (7, 4, '黑白电视');
   

3. 查询一级分类:

   select id, pid, name from t_category
   where pid = 0;

4. 查询家电的二级分类

   select id, pid, name from t_category
   where pid = 1;

多对多关系

软件业务中经常存在多对多关系, 如:

• 讲师和教过学生之间是多对多关系
• 学生和选修的课程之间的关系
• 用户和群组之间的关系

采用增加关系表的方式, 可以实现多对多的关联关系

讲师和教过学生之间是多对多关系

[外链图片转存失败(img-JWJoYssO-1565311382224)(teacher.png)]

1. 建表

   create table t_teacher(
   id int,
   name varchar(100)
   );

   create table t_student(
   	id int,
   	name varchar(100)
   );
   
   create table t_teach(
   	tid int,
   	sid int	
   );	
   

2. 插入数据

   -- 老师数据
   insert into t_teacher (id, name)
   values (1, '范传奇');
   insert into t_teacher (id, name)
   values (2, '刘国斌');
   
   -- 学生数据
   insert into t_student (id, name)
   values (1, '莫小贝');
   insert into t_student (id, name)
   values (2, '白展堂');
   insert into t_student (id, name)
   values (3, '吕秀才');
   insert into t_student (id, name)
   values (4, '李大嘴');
   
   -- 多对多关系数据
   -- 范传奇教过的学生
   insert into t_teach (tid, sid) 
   values (1, 1);		
   insert into t_teach (tid, sid) 
   values (1, 2);		
   insert into t_teach (tid, sid) 
   values (1, 3);		
   -- 刘国斌老师教过的学生
   insert into t_teach (tid, sid) 
   values (2, 2);
   insert into t_teach (tid, sid) 
   values (2, 3);
   insert into t_teach (tid, sid) 
   values (2, 4);
   

3. 查询

   验证多对多的可用性

   • 范传奇讲授了哪些学生:

     select t.name as teacher, s.name as student
     from t_teacher t
     join t_teach tt on t.id = tt.tid
     join t_student s on tt.sid=s.id
     where t.id=1;

4. 测试任务: 添加老师数据 王克晶, 教过所有学生, 并且查询验证

   – 添加讲师数据
   insert into t_teacher (id, name)
   values (3, ‘王克晶’);
   – 添加关联关系数据
   insert into t_teach (tid, sid)
   values (3, 1);
   insert into t_teach (tid, sid)
   values (3, 2);
   insert into t_teach (tid, sid)
   values (3, 3);
   insert into t_teach (tid, sid)
   values (3, 4);
   – 验证查询
   select t.name as teacher, s.name as student
   from t_teacher t
   join t_teach tt on t.id = tt.tid
   join t_student s on tt.sid=s.id
   where t.id=3;

5. 查询 莫小贝 的老师有哪些:

   select t.name as teacher, s.name as student
   from t_teacher t
   join t_teach tt on t.id = tt.tid
   join t_student s on tt.sid=s.id
   where s.id=1;

学生和选修的课程之间的关系

[外链图片转存失败(img-OWcxrze3-1565311382224)(student.png)]

1. 建表

   create table t_student(
   id int,
   name varchar(100)
   );

   create table t_course(
   	id int,
   	name varchar(100)
   );
   
   create table t_choose(
   	sid int,
   	cid int	
   );	
   

2. 插入数据

   -- 学生数据
   insert into t_student (id, name)
   values (1, '莫小贝');
   insert into t_student (id, name)
   values (2, '白展堂');
   insert into t_student (id, name)
   values (3, '吕秀才');
   insert into t_student (id, name)
   values (4, '李大嘴');
   
   -- 课程数据
   insert into t_course (id, name)
   values (1, 'Java');
   insert into t_course (id, name)
   values (2, 'PHP');
   insert into t_course (id, name)
   values (3, 'Python');
   insert into t_course (id, name)
   values (4, 'Cpp');
   
   -- 学生课程关系
   insert into t_choose (sid, cid)
   values (1, 1);
   insert into t_choose (sid, cid)
   values (1, 4);
   insert into t_choose (sid, cid)
   values (2, 1);
   insert into t_choose (sid, cid)
   values (2, 3);
   
   

3. 查询莫小贝上了哪些课

   select s.name as student, c.name as course
   from t_student s
   join t_choose cc on s.id = cc.sid
   join t_course c on cc.cid=c.id
   where s.id=1;

用户-角色-功能 的关系

由两个多对多关系连接而成:

[外链图片转存失败(img-iVufbdWe-1565311382225)(user-role.png)]

1. 编写表

   create database db5;
   use db5;

   create table t_user(
   	id int,
   	name varchar(100)	
   );
   
   create table t_role(
   	id int,
   	name varchar(100)
   );
   
   create table t_function(
   	id int,
   	name varchar(100)	
   );	
   
   create table t_user_role(
   	uid int,
   	rid int	
   );	
   
   create table t_role_function(
   	rid int,
   	fid int
   );			
   
   

2. 插入数据

   – 用户表插入数据
   insert into t_user (id, name)
   values (1, ‘范老’);
   insert into t_user (id, name)
   values (2, ‘王老’);
   insert into t_user (id, name)
   values (3, ‘刘老’);

   -- 角色表插入数据
   insert into t_role (id, name)
   values (1, '消费者');
   insert into t_role (id, name)
   values (2, '店家');
   insert into t_role (id, name)
   values (3, '店小二');
   insert into t_role (id, name)
   values (4, '管理员');
   
   -- 功能表插入数据
   insert into t_function (id, name)
   values (1, '购买商品');
   insert into t_function (id, name)
   values (2, '搜索商品');
   insert into t_function (id, name)
   values (3, '上货');
   insert into t_function (id, name)
   values (4, '管理用户');
   
   -- 用户角色关系
   insert into t_user_role (uid, rid)
   values (1, 1);
   insert into t_user_role (uid, rid)
   values (1, 2);
   insert into t_user_role (uid, rid)
   values (2, 3);
   
   -- 角色功能关系
   insert into t_role_function (rid, fid)
   values (1, 1);
   insert into t_role_function (rid, fid)
   values (2, 2);
   insert into t_role_function (rid, fid)
   values (2, 3);
   insert into t_role_function (rid, fid)
   values (3, 3);
   insert into t_role_function (rid, fid)
   values (4, 4);
   
   

3. 查询: 范老有哪些角色和功能

   select u.name as user, r.name as role, f.name as function
   from t_user u
   join t_user_role ur on u.id=ur.uid
   join t_role r on ur.rid=r.id
   join t_role_function rf on rf.rid=r.id
   join t_function f on f.id=rf.fid
   where u.id=1;

View (视图)

利用一个查询语句构建一个虚拟表. 如果需要查询就可以通过查询视图获得结果.

利用视图可以重用复杂的查询功能, 简化二次查询工作

[外链图片转存失败(img-Z6R41uvt-1565311382226)(view.png)]

视图不是表, 视图中不存储数据, 数据是通过内部封装的SQL语句动态查询得到的. 视图的本质是"复用复杂查询"!!!

1. 创建视图

   create view student_course as (
   select s.name as student, c.name as course
   from t_student s
   join t_choose cc on s.id = cc.sid
   join t_course c on cc.cid=c.id
   );

2. 利用视图进行查询:

   select student, course from student_course
   where student = ‘莫小贝’;

   select student, course from student_course 
   where student = '白展堂'; 
   
   

3. 管理view

   • 创建 create view 视图名 as 查询
   • 查询全部视图SHOW FULL TABLES WHERE TABLE_TYPE LIKE ‘VIEW’;
   • 删除视图 drop view 视图名
   • 视图可以嵌套使用

回顾

1. 关系
   1. 一对一, 性能优化
   2. 一对多, 解决业务关系, 使用最多
      1. 自关联 一对多 分类树 *
   3. 多对多: 中间的关系表
      1. 用户和角色
2. 视图 不是真实表, 是虚拟的表, 可以当做表用
   1. 最重要的目的是"重用复杂查询"

作业

1. 创建表,并且完成查询
   1. 设计表存储 存储红包记录相关信息, 包括发红包的联系人(属性: 编号, 姓名, 性别, 电话) 红包记录信息(属性: 时间, 金额), 其中每个联系人可以发生多笔红包记录, 红包记录包含收到红包和发出红包
   2. 查询一段时间内, 收到红包总数和发出红包总数.