以前自己学习时候的笔记
导入数据
load data local infile "路径" into table 表名
person
load data local infile 'C:\\Users\\Administrator\\Desktop\\xiaosuo_l.txt' into table xiaosuo_l fields terminated by ',';
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
2019-6-5###MySQL
####数据库简介
- 之前通过流操作文件方式保存数据的弊端：
1. 效率低
2. 实现过程比较复杂,开发效率低
3. 一般只能保存小量数据
4. 只能保存文本
####什么是DB
- DataBase： 数据库，数据库是一个文件集合
####什么是DBMS
- DataBaseManagementSystem:数据库管理系统(软件)，用于管理数据库文件，常见的DBMS有哪些？ Oracle MySQL DB2 SQLserver Sqlite
####数据库的分类
1. 关系型数据库
以表为数据库存储数据的单位，关系型数据库是经过数学理论验证可以保存现实生活中任何关系型的数据
2. 非关系型数据库
主要应用在一些特定场景，解决某些具体问题比如解决高并发访问时的数据缓存，（redis数据库属于非关系型数据库，redis内部以键值对的形式保存数据）
name=xxx age=xxx
###主流关系型数据库的介绍
1. MySQL： Oracle公司产品， 08年被Sun公司收购，09年Sun公司被Oracle收购，开源产品，MySQL被收购后性能大幅提高，面临闭源风险，
原MySQL程序员离开Oracle创建了MariaDB，MariaDB和MySQL区别不大 市场占有率第一
2. Oracle： 闭源产品 价格最贵性能最高 市场排名第二，拉里埃里森 32
3. DB2： IBM公司产品，闭源 主要应用在IBM公司整套解决方案中
4. SqlServer： 微软公司产品，闭源，主要应用在微软公司整套解决方案中
5. Sqlite： 轻量级数据库 安装包只有几十k，主要应用在嵌入式设备中或移动设备中
####开源和闭源
- 开源：公开源代码 ，靠卖服务盈利， 有大牛程序员无偿维护升级
- 闭源：不公开源代码，靠卖产品和服务盈利，有大牛攻击找漏洞
###什么是SQL
- Structured Query Language：结构化查询语言，用于程序员和DBMS（数据库管理软件）进行交互
###连接数据库
- Linux系统： mysql -uroot -p
- Windows系统：开始菜单-》所有程序-》MySQL/MariaDB-》MySQL Client
###数据库相关的SQL
1. 查看所有数据库
show databases;
2. 创建数据库
格式：create database 数据库名称;
create database db1;
3. 删除
格式：drop database 数据库名称;
- 练习： 创建数据库 db1 db2 db3 db4 db5 然后再一个个删除
4. 查看数据库详情
格式：show create database 数据库名称;
show create database db1;
5. 创建数据库指定字符集
格式：create database 数据库名称 character set utf8/gbk;
create database db2 character set gbk;
6. 使用数据库
格式: use 数据库名称
use db1;
- 练习： 创建mydb1 字符集为gbk ，并使用， 再创建mydb2 字符集为utf8，查看详情，然后使用，然后删除mydb1和mydb2
create database mydb1 character set gbk;
use mydb1;
create database mydb2 character set utf8;
show create database mydb2;
use mydb2;
drop database mydb1;
drop database mydb2;
###表相关SQL
- 什么是表： 表是数据库中保存数据的单位
1. 创建表
格式： create table 表名 (字段1名 字段1类型, 字段2名 字段2类型,...);
创建一个person表 保存名字、性别、年龄、工资
create table person(name varchar(10),gender varchar(5),age int,sal int);
2. 查看所有表
show tables;
- 练习1： 创建数据库mydb1 并使用 在mydb1里面创建学生(student)表,保存姓名（name），年龄（age），语文成绩（chinese），数学成绩（math），英语成绩（english）
- 练习2：创建数据库mydb2 字符集为gbk 并使用，在里面创建book表，字段有书名（book_name）作者（author）页数（page）价格（price）
3. 查看表详情
格式： show create table 表名; show create table person;
4. 创建表指定引擎和字符集
格式： create table 表名 (字段1名 字段1类型, 字段2名 字段2类型,...) engine=innodb/myisam charset=gbk/utf8;
create table t1(nama varchar(10),age int) engine=myisam charset=gbk; show create table t1;
5. 查看表字段信息
格式： desc 表名; desc person;
6. 修改表名
格式： rename table 原名 to 新名; rename table person to t_person;
7. 修改表引擎和字符集
格式： alter table 表名 engine=myisam/innodb charset=gbk/utf8; alter table t_person engine=myisam charset=gbk;
8. 添加表字段
格式1： alter table 表名 add 字段名 字段类型; 最后
格式2： alter table 表名 add 字段名 字段类型 first; 最前面
格式3： alter table 表名 add 字段名 字段类型 after xxx; alter table t1 add gender varchar(5); alter table t1 add id int first; alter table t1 add sal int after age;
9. 删除表字段
格式： alter table 表名 drop 字段名 alter table t1 drop sal;
10. 修改字段名称和类型
格式： alter table 表名 change 原字段名 新字段名 新类型; alter table t1 change nama name varchar(5);
11. 修改字段类型和位置
格式： alter table 表名 modify 字段名 新类型 first/after xxx alter table t1 modify name varchar(10) after age;
12. 删除表 drop table 表名;
13. 练习：
14. 创建一个thero的英雄表，有id，name两个字段 create table thero (id int,name varchar(10));
15. 修改表名为hero rename table t_hero to hero;
16. 修改表引擎为myisam 字符编码为gbk alter table hero engine=myisam charset=gbk;
17. 添加类型type字段在最后面 alter table hero add type varchar(10);
18. 添加money字段在name的后面 alter table hero add money int after name;
19. 删除id字段 alter table hero drop id;
20. 修改type字段名称为herotype alter table hero change type herotype varchar(10);
21. 修改money字段到最后面 alter table hero modify money int after hero_type;
###SQL回顾
####数据库相关SQL
1. 查询所有 show databases;
2. 创建数据库 create database db1 character set utf8/gbk;
3. 查看详情 show create database db1;
4. 删除 drop database db1;
5. 使用 use db1;
####表相关SQL
1. 创建表 create table t1(name varchar(10),age int) engine=myisam/innodb charset=gbk/utf8;
2. 查询所有表 show tables;
3. 查询表详情 show create table t1;
4. 查看表字段 desc t1;
5. 修改表名 rename table t1 to t2;
6. 修改表引擎字符集 alter table t1 engine=myisam/innodb charset=gbk/utf8;
7. 添加表字段 alter table t1 add age int first/after xxx;
8. 删除表字段 alter table t1 drop age;
9. 修改表字段名称和类型 alter table t1 change age xxx 类型;
10. 修改表字段的类型和位置 alter table t1 modify age 新类型 first/after xxx;
11. 删除表 drop table t1;
####数据相关SQL
create table hero(id int,name varchar(10),gender varchar(5));
1. 插入数据
2. 全表插入： insert into hero values(1,'Tom','Man');
3. 指定字段插入： insert into hero (name,gender) values('张飞','男');
4. 批量插入数据： insert into hero values(5,'唐僧','男'),(6,'悟空','男'),(7,'八戒','男'); insert into hero (name) values('李雷'),('韩梅梅'),('Lucy');
5. 查询数据
格式：select 字段信息 from 表名 where id<5; select * from hero;//查询所有数据的所有字段信息 select name,gender from hero; //查询所有的名字和性别 select * from hero where name='悟空';//查询悟空的所有信息 查询性别为Man的所有名字？ select name from hero where gender='Man';
6. 修改数据
格式： update 表名 set gender='女' where id=1; update hero set gender='女' where id=1;
修改id小于5的性别为女 update hero set gender='女' where id<5; 修改八戒的名字为悟能 update hero set name='悟能' where id=7;
7. 删除数据
格式: delete from 表名 where 条件; delete from hero where name='张飞';
删除id小于3的信息 delete from hero where id<3; 删除表中所有数据 delete from hero;
8. 练习：创建员工emp表 字段有id，name，sal，dept(部门) create table emp (id int,name varchar(10),sal int, dept varchar(10));
9. 插入以下数据
insert into emp values(1,'李白',3000,'打野部门'),(2,'刘备',4000,'打野部门'),(3,'诸葛亮',6000,'法师部门'),(4,'周瑜',5500,'法师部门'),(5,'程咬金',2000,'战士部门');
10. 查询打野部门的员工姓名 select name from emp where dept='打野部门';
11. 修改诸葛亮的名字为卧龙 update emp set name='卧龙' where id=3;
12. 修改程咬金的工资为3500 update emp set sal=3500 where id=5;
13. 删除工资低于4000的员工 delete from emp where sal<4000;
14. 删除打野部门的所有员工 delete from emp where dept='打野部门';
####数据库表的引擎
1. innodb:支持数据库的复杂操作，包括外键、事务等
2. myisam:只支持数据基础的增删改查操作
####字符编码问题
如果使用Windows电脑SQL语句中写中文报错的话 通过以下指令解决 set names gbk;
###全天回顾
####数据库相关SQL
1. 查询所有数据库 show databases;
2. 创建 create database db1 character set utf8/gbk;
3. 查看详情 show create database db1;
4. 删除 drop database db1;
5. 使用 use db1;
####表相关SQL
1. 创建表 create table t1(name varchar(10),age int) engine=myisam/innodb charset=utf8/gbk;
2. 查询所有 show tables;
3. 查看表详情 show create table t1;
4. 查看表字段 desc t1;
5. 修改表名 rename table t1 to t2;
6. 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
7. 添加字段 alter table t1 add age int first/after xxx;
8. 删除字段 alter table t1 drop age;
9. 修改字段名和类型 alter table t1 change 原名 新名 新类型;
10. 修改字段类型和位置 alter table t1 modify 字段名 类型 first/after xxx;
11. 删除表 drop table t1;
####数据相关
1. 插入数据 insert into 表名 (字段名1，2，3) values(值1，2，3),(值1，2，3);
2. 查询数据 select * from 表名 where 条件;
3. 修改数据 update 表名 set age=18 where id=10;
4. 删除数据 delete from 表名 where id<5;
##作业：
1. 创建数据库newdb1 并使用
2. 创建商品表t_item， 字段有id和商品标题title 指定引擎为myisam字符集为gbk
3. 修改表名为item
4. 添加价格price字段在最后
5. 添加分类category字段在title的后面
6. 插入以下数据 1 苹果x 手机分类 5000，2 海尔滚筒洗衣机 家电 3000，3 小米电视 家电， 4 双飞燕鼠标 电脑配件 50，5 机械键盘 电脑配件 150
7. 查询单价低于2000的商品信息
8. 修改家电为家用电器
9. 删除价格低于100的商品
10. 给表格添加库存num字段
11. 修改id小于3的商品库存为100
12. 删除表所有数据
13. 删除表
14. 删除newdb1
第二天=========================================================================================================================
###课程回顾
####数据库相关SQL
1. 创建数据库 create database db1 character set utf8/gbk
2. 查询所有 show databases
3. 查看详情 show create database db1;
4. 删除数据库 drop database db1;
5. 使用数据库 use db1;
####表相关
1. 创建表 create table t1(name varchar(10),age int) engine=myisam/innodb charset=utf8/gbk;
2. 查询所有表 show tables;
3. 查询表详情 show create table t1;
4. 查看表字段 desc t1;
5. 修改表名 rename table t1 to t2;
6. 修改引擎和字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
7. 添加表字段 alter table t1 add age int;
8. 删除表字段 alter table t1 drop age;
9. 修改字段名和类型 alter table t1 change 原名 新名 新类型;
10. 修改类型和位置 alter table t1 modify 字段名 类型 first/after xxx;
11. 删除表 drop table t1;
####数据相关
1. 插入数据 insert into t1 (字段名1，字段名2，字段名3) values(1，2，3),(1,2,3);
2. 查询数据 select * from t1 where id<5;
3. 修改数据 update t1 set name='xxx' where id=2;
4. 删除数据 delete from t1 where id<5;
####练习：
1. 创建数据库newdb2 指定字符集为utf8,并使用
create database newdb2 character set utf8;
use newdb2;
2. 在数据库中创建员工emp表，有员工编号empno，姓名name，工资sal，部门dept，奖金comm
create table emp(empno int,name varchar(10),sal int,dept varchar(10),comm int);
3. 插入刘关张和取经四人组共7个人 刘关张为三国部 取经四个人为取经部，刘关张工资为 8000，7000，5000， 取经四个人按级别 工资为800，700，600，500，奖金刘备和唐僧为500其它人员为null
insert into emp values(1,'刘备',8000,'三国部',500),
(2,'关羽',7000,'三国部',null),(3,'张飞',6000,'三国部',null),(4,'唐僧',800,'取经部',500),(5,'悟空',700,'取经部',null),
(6,'八戒',600,'取经部',null),(7,'沙僧',500,'取经部',null);
4. 修改三国部的奖金为400
update emp set comm=400 where dept='三国部';
5. 修改悟空为齐天大圣
update emp set name='齐天大圣' where name='悟空';
6. 修改唐僧为玉帝哥哥，并且工资改为1000
update emp set name='玉帝哥哥',sal=1000 where name='唐僧';
7. 查询取经部的所有人的名字和工资
select name,sal from emp where dept='取经部';
8. 查询所有员工的名字、工资和奖金
select name,sal,comm from emp;
9. 删除工资为5000块钱的员工信息
delete from emp where sal=5000;
10. 查询奖金为null的员工有哪些
select * from emp where comm is 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
insert into t1 values(2,'ddd');
####主键约束+自增
- 格式： create table t2(id int primary key auto_increment,name varchar(10));
测试： insert into t2 values(null,'aaa'); 1
insert into t2 (name) values('bbb');
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
delete from t2;
insert into t2 values(null,'fff'); 13
- 自增数值只增不减，从历史最大值的基础上+1
- truncate table t2; //删除表并创建新表 自增数值清零
####注释 comment
- 格式：
create table t3(id int primary key auto_increment comment '这是个主键',name varchar(10) comment '这是名字');
查看注释内容： show create table t3;
####`和单引号的区别
- `:用于修饰表名和字段名
create table `t4`(`id` int,`name` varchar(10));
- ':用于修饰文本
####数据冗余
- 如果表设计不合理，可能会出现大量的重复数据，这种现象称为数据冗余，通过拆分表的形式解决此问题
- 练习： 创建表保存以下信息
保存集团总部下财务部里面的财务A部的张三工资8000 年龄18
保存集团总部下研发部的李四工资800 年龄75
需要保存的信息有：部门名 员工姓名 工资 年龄
-考虑数据冗余问题所以创建两个表员工表和部门表
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,age int,dept int);
create table t_dept(id int primary key auto_increment,name varchar(10),parent_id int);
-插入数据 先插入部门表数据然后再插入员工表数据
insert into t_dept values(null,'集团总部',null),(null,'财务部',1),(null,'财务A部',2),(null,'研发部',1);
insert into t_emp values(null,'张三',8000,18,3),(null,'李四',800,75,4);
- 练习： 分类category 价格price 库存num
1. 保存家电分类下洗衣机分类下的海尔洗衣机价格2300，库存38
2. 保存办公用品下，笔分类下的晨光圆珠笔 价格5，库存100
- 创建表：
create table category(id int primary key auto_increment,name varchar(10),parent_id int);
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
- 插入数据：
insert into category values(null,'家电',null),(null,'洗衣机',1),(null,'办公用品',null),(null,'笔',3);
insert into item values(null,'海尔洗衣机',2300,38,2),(null,'晨光圆珠笔',5,100,4);
###事务
- 事务(transaction)是数据库中执行同一业务多条SQL语句的工作单元，事务可以保证多条SQL语句全部执行成功或全部执行失败
- 验证事务：
1. 创建表
create table user(id int primary key auto_increment,name varchar(10),money int,state varchar(5));
2. 插入数据
insert into user values(null,'李雷',5000,'正常'),(null,'韩梅梅',50,'正常'),(null,'Lucy',10,'冻结');
3. 李雷给Lucy转账SQL：
update user set money=4000 where id=1 and state='正常';
update user set money=1010 where id=3 and state='正常';
select * from user;
- 以上代码无事务保护，则导致数据库内部的数据李雷丢了1000块钱
- 以下操作有事务保护的步骤：
-开启事务：
begin;
-让李雷-1000
update user set money=3000 where id=1 and state='正常';
-让Lucy+1000
update user set money=1010 where id=3 and state='正常';
-因为一条成功一条失败 所以不能把内存中的改变提交到磁盘中，所以需要执行回滚指令，执行之前可以打开另外一个窗口查看数据库中的数据是否发生改变（变的是内存中的数据，数据库里面的数据并没发生改变）
rollback;
- 转账成功执行流程：
1. 开启事务
begin;
2. 让李雷-1000
update user set money=3000 where id=1 and state='正常';
3. 让韩梅梅+1000
update user set money=1050 where id=2 and state='正常';
4. 由于转账业务的多条SQL全部执行成功 所以可以执行提交指令 把内存中的改动提交到磁盘中
commit;
- 和事务相关的SQL语句：
1. begin: 开启事务
2. rollback：回滚事务 把内存中的改动清除
3. commit：提交事务 把内存中的改动一次性提交到磁盘中
- 第二种开启事务的方式
数据库中事务默认是自动提交的
1. 查看数据库自动提交的状态
show variables like '%autocommit%';
2. 修改自动提交的状态 0关闭 1 开启
set autocommit=0;
3. 修改李雷钱为50000
update user set money=50000 where id=1;
- savepoint：保存回滚点
set autocommit=1;
李雷开始是50000
begin;
update user set money=10000 where id=1;
savepoint s1
update user set money=20000 where id=1;
savepoint s2
update user set money=30000 where id=1;
rollback to s1;
- 总结事务相关指令：
1. begin开启事务
2. rollback 回滚事务
3. commit 提交事务
4. 查看自动提交状态 show variables like '%autocommit%'
5. 修改自动提交状态 set autocommit=0/1;
6. 保存回滚点 savepoint s1;
7. 回滚到回滚点 rollback to s1;
###SQL分类
####DDL Data Definition Language
- 数据定义语言
- 包括： create，alter，drop，truncate
- 不支持事务
####DML Data Manipulation Language
- 数据操作语言
- 包括： insert delete update select（DQL）
- 支持事务
###DQL Data Query Language
- 数据查询语言
- 只包括select
###TCL Transaction Control Language
- 事务控制语言
- 包括：begin,rollback commit,savepoint xxx,rollback to xxx
###DCL Data Control Language
- 数据控制语言
- 用于分配用户权限相关的SQL
###数据类型
1. 整数类型： int(m)对应java中的int， bigint(m)对应java中的long，m代表显示长度，需要结合 zerofill使用
create table t_int(id int,age int(8) zerofill);
insert into t_int values(1,28);
select * from t_int;
2. 浮点数： double(m,d) m代表总长度，d代表小数长度 76.232(m=5 d=3),decimal超高精度小数，当涉及超高精度运算时使用。
3. 字符串： char(m) 固定长度
255 char(10) "abc" 所占长度10，varchar(m) 可变长度 最大65535 varchar(10) "abc" 所占长度3，可变长度更节省空间，固定长度执行效率略高
- varchar最大65535 但是建议保存255以内的长度，超过255使用textphone
- text可变长度 最大65535，
4. 日期：
- data：只能保存年月日
- time：只能保存时分秒
- datetime：保存年月日时分秒，最大值9999-12-31，默认值null
- timestamp：保存年月日时分秒，最大值2038-1-19，默认值为当前时间
create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
insert into t_date values('2018-11-15','16:58:33',null,null);
insert into t_date values
('2018-11-15','16:58:33','2018-10-18 12:30:18',null);
###课程回顾
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(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
- 查询所有表 show tables;
- 查看表详情 show create table t1;
- 查看表字段 desc t1;
- 修改表名 rename table t1 to t2;
- 添加字段 alter table t1 add DownloadNumber int;
- 删除字段 alter table t1 drop age;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型;
- 修改字段类型和位置 alter table t1 modify 字段名 类型 first/after xxx;
- 删除表 drop table t1;
- 删除并创建表 truncate table t1;
- 修改表引擎 alter table t1 engine=myisam/innodb charset=utf8/gbk;
3. 数据相关
- 插入数据 insert into t1 (字段名1，字段名2) values(指1，值2),(指1，值2);
- 查询数据 select name,age from t1 where id=3;
- 修改数据 update t1 set age=18 where id=5;
- 删除数据 delete from t1 where id<10;
4. 主键约束+自增
create table t2(id int primary key auto_increment)
- 自增数值 只增不减，从历史最大值+1
5. 注释 comment 添加注释后 通过 show create table t1;
6. `修饰表名和字段名 '修饰字符串
7. 数据冗余：建表不合理 数据大量重复，拆分表解决
8. 事务：数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败，相关SQL：begin commit rollback savepoint rollback to xxx; show variables like '%autocommit%'; set autocommit=0/1;
9. SQL分类
- DDL：数据定义语言 包括：create alter drop truncate，不支持事务
- DML：数据操作语言 包括：insert delete update select(DQL) 支持事务
- DQL：数据查询语言 只包括select
- TCL：事务控制语言 包括：begin commit rollback savepoint rollback to xxx; show variables like '%autocommit%'; set autocommit=0/1;
- DCL：数据控制语言 分配用户权限的SQL
10. 数据类型
- 整数：int bigint(m) m代表显示长度 需要结合zerofill使用
- 浮点数：double(m,d) m代表总长度 d代表小数长度，decimal超高精度小数
- 字符串：char(m)固定长度最大255 varchar(m)可变长度 最大65535 超过255建议使用text text可变长度 最大65535。
- 日期：date只能保存年月日 time时分秒 datetime 最大值9999-12-31 默认值null， timestamp 最大值2038-01-19 默认值为当前时间
- 其它
###练习：
1. 设计表保存以下数据
小明 男 18岁 工资3000 销售部 北京 无领导
小丽 女 20岁 工资4000 市场部 上海 无领导
小黄 女 25岁 工资1500 销售部 北京 上级领导小明
李白 男 35岁 工资800 市场部 上海 小丽
典韦 男 33岁 工资1500 市场部 上海 小丽
2. 查询市场部的所有员工姓名、性别和工资
3. 查询工作地点在上海的部门名称
4. 修改小丽的名字为丽丽
5. 查询25岁以下的女员工有谁
6. 查询市场部的男员工姓名和工资
7. 删除没有领导的男员工
8. 指定字段插入三个员工的姓名和工资，刘备2000、关羽1500、张飞100
9. 删除工资低于1000的员工
10. 删除表里所有数据、删除表 、删除库。
第三天=================================================================================================================================
###课程回顾：
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(id int,name varchar(10));
- 查询所有 show tables;
- 详情 show create table t1;
- 表字段 desc t1;
- 改表名 rename table t1 to t2;
- 改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
- 添加表字段 alter table t1 add age int；
- 删除表字段 alter table t1 drop age;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型;
- 修改类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;
- 删除表 drop table t1;
- 删除并创建 truncate table t1;
3. 数据相关SQL
- 插入数据 insert into t1 (字段1，字段2) values(值1，值2),(值1，值2);
- 查询数据 select * from t1 where id<10;
- 修改数据 update t1 set age=18 where id=5;
- 删除数据 delete from t1 where age<20;
4. 主键约束+自增 create table t1(id int primary key auto_increment,name varchar(10));
5. 注释 comment create table t2(id int comment '这是id');
6. `是用于修饰表名和字段名 '是用于修饰字符串
7. 冗余就是设计表不合理导致的大量重复数据，通过拆分表解决
8. 事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部执行失败
- begin开启事务 ，回滚rollback 提交 commit ，查看自动提交状态 show variables like '%autocommit%', 修改状态 set autocommit=0/1; 保存回滚点 savepoint s1 回滚到回滚点 rollback to s1;
9. SQL分类
- DDL：数据定义语言 包括：create drop alter truncate 不支持事务
- DML：数据操作语言 包括：insert delete update select(DQL) 支持事务
- DQL：数据查询语言 只包括 select
- TCL：事务控制语言，包括上面的内容
- DCL：数据控制语言 分配用户权限相关SQL
10. 数据类型
- 整数： int bigint(m) m代表显示长度 需要结合zerofill使用
- 浮点数： double(m,d) m代表总长度 d代表小数长度 涉及超高精度运算时使用decimal
- 字符串： char固定长度 最大255 varchar可变长度 最大65535 超过255建议使用text text可变长度最大65535
- 日期： date只能保存年月日 time只能保存时分秒 datetime 最大值9999-12-31 默认值null timestamp 最大2038-1-19 默认值为当前时间
###练习：
1. 设计表保存以下数据
小明 男 18岁 工资3000 销售部 北京 无领导
小丽 女 20岁 工资4000 市场部 上海 无领导
小黄 女 25岁 工资1500 销售部 北京 上级领导小明
李白 男 35岁 工资800 市场部 上海 小丽
典韦 男 33岁 工资1500 市场部 上海 小丽
员工表： 姓名 性别 年龄 工资 上级领导 部门表： 部门 地点
create table emp(id int primary key auto_increment,name varchar(10),gender varchar(5),age int, sal int,parent_id int,dept_id int);
create table dept(id int primary key auto_increment,name varchar(10),loc varchar(10));
insert into dept values(null,'销售部','北京'),(null,'市场部','上海');
insert into emp values(null,'小明','男',18,3000,null,1),
(null,'小丽','女',20,4000,null,2),
(null,'小黄','女',25,1500,1,1),
(null,'李白','男',35,800,2,2),
(null,'典韦','男',33,1500,2,2);
2. 查询市场部的所有员工姓名、性别和工资
select name,gender,sal from emp where dept_id=2;
3. 查询工作地点在上海的部门名称
select name from dept where loc='上海';
4. 修改小丽的名字为丽丽
update emp set name='丽丽' where name='小丽';
5. 查询25岁以下的女员工有谁
select * from emp where age<25 and gender='女';
6. 查询市场部的男员工姓名和工资
select name,sal from emp where dept_id=2 and gender='男';
7. 删除没有领导的男员工
delete from emp where parent_id is null and gender='男';
8. 指定字段插入三个员工的姓名和工资，刘备2000、关羽1500、张飞100
insert into emp (name,sal) values('刘备',2000),('关羽',1500),('张飞',100);
9. 删除工资低于1000的员工
delete from emp where sal<1000;
10. 删除表里所有数据、删除表 、删除库。
delete from emp;
drop table emp;
drop table dept;
drop database newdb3;
###导入*.sql数据到数据库
- window系统
source d:/tables.sql;
- linux系统
source /home/soft01/桌面/tables.sql;
- 查询所有表 看是否有四张表
####is null 和 is not null
1. 查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
2. 查询emp表中没有奖金comm的员工姓名、工资、奖金
select ename,sal,comm from emp where comm is null;
3. 查询emp表中有奖金的员工信息
select * from emp where comm is not null and comm>0;
####别名
- 把查询到的员工姓名ename 改成 名字
select ename as '名字' from emp;
select ename '名字' from emp;
select ename 名字 from emp;
####去重 distinct
1. 查询emp表中出现的所有职位job
select distinct job from emp;
####比较运算符 >,<,>=,<=,=,!=和<>
1. 查询工资小于等于1600的员工姓名和工资
select ename,sal from emp where sal<=1600;
2. 查询部门编号是20的所有员工的姓名、职位job和部门编号deptno
select ename,job,deptno from emp where deptno=20;
3. 查询职位是manager的所有员工姓名和职位
select ename,job from emp where job='manager';
4. 查询部门不是10号部门的所有员工姓名和部门编号（两种写法）
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
5. 查询t_item表中单价price 等于23的商品信息
select * from t_item where price=23;
6. 查询t_item表中单价不等于8443的商品标题title和单价
select title,price from t_item where price!=8443;
### and 和 or
- and和java中的&&效果一样
- or 和java中的||效果一样
1. 查询不是10号部门并且工资小于3000的员工信息
select * from emp where deptno!=10 and sal<3000;
2. 查询部门编号为30号或者上级领导为7698的员工姓名，职位，上级领导和部门编号
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;
#### in
- 当查询某个字段的值为多个的时候使用in关键字
1. 查询emp表中工资为5000，1500，3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
-使用in:
select * from emp where sal in(5000,1500,3000);
2. 查询emp表中工资不等于5000，1500，3000的员工信息
select * from emp where sal not in(5000,1500,3000);
#### between x and y 包括x和y
1. 查询emp表中工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
- between and
select * from emp where sal between 2000 and 3000;
2. 查询emp表中工资在1000到3000之外的员工信息
select * from emp where sal not between 1000 and 3000;
###模糊查询 like
- _:代表单个未知字符
- %：代表0或多个未知字符
- 举例：
1. 查询以a开头 like 'a%'
2. 以m结尾 like '%m'
3. 第二个字符是a like '_a%'
4. 第三个字符是x 倒数第二个字符是y like '__x%y_'
5. 倒数第三个字符是x like '%x__'
6. 查询包含a like '%a%'
####like案例
1. 查询t_item表中 标题中包含记事本的商品信息
select title from t_item where title like '%记事本%';
2. 查询单价低于100的记事本(title包含记事本)
select * from t_item where price<100 and title like '%记事本%';
3. 查询单价在50到200之间的得力商品(title包含得力)
select * from t_item where price between 50 and 200 and title like '%得力%';
4. 查询有图片的得力商品 （有图片image字段不为null）
select * from t_item where image is not null and title like '%得力%';
5. 查询分类(category_id)为238，917的商品信息
select * from t_item where category_id in(238,917);
6. 查询有赠品的商品信息（卖点sellpoint包含赠字）
select * from t_item where sell_point like '%赠%';
7. 查询标题中不包含得力的商品标题
select title from t_item where title not like '%得力%';
8. 查询价格在50到200以外的商品信息
select * from t_item where price not between 50 and 200;
####排序 order by
- 格式 order by 字段名 desc降序/asc升序;
1. 查询所有员工的姓名和工资，按照工资升序排序
select ename,sal from emp order by sal;
2. 查询所有员工的姓名，工资和部门编号，按照部门编号降序排序
select ename,sal,deptno from emp order by deptno desc;
- 多字段排序： order by 字段名1 asc/desc,字段名2 asc/desc;
1. 查询所有员工的姓名，工资和部门编号，按照部门编号降序排序,如果部门一致则按照工资升序排序
select ename,sal,deptno from emp order by deptno desc,sal;
####分页查询 limit
- 格式： limit 跳过的条数,请求的数量
- 举例：
1. 请求第一页20条数据 limit 0,20
2. 请求第三页10条数据 limit 20,10
3. 请求第5页 8条数据 limit 32,8
4. 请求第四页 7条数据 limit 21,7
- 公式：limit((页数-1)*每页数量,每页数量)
- 练习：
1. 查询所有商品按照单价升序排序，显示第二页每页7条数据
select price from t_item order by price limit 7,7;
2. 查询员工表中所有员工的姓名和工资，按照工资降序排序，显示第3页每页三条数据
select ename,sal from emp order by sal desc limit 6,3;
3. 查询所有员工中工资前三名的姓名和工资
select ename,sal from emp order by sal desc limit 0,3;
4. 查询工资最高的员工姓名和工资
select ename,sal from emp order by sal desc limit 0,1;
####数值计算 + - * / % 7%2 等效 mod(7,2)
1. 查询员工姓名，工资，及年终奖(年终奖=工资*5)
select ename,sal,sal*5 年终奖 from emp;
2. 查询t_item表中商品单价，库存和总金额(单价*库存)
select price,num,price*num 总金额 from t_item;
3. 修每个员工的工资，每个人涨10块钱
update emp set sal=sal+10;
- 改回去
update emp set sal=sal-10;
####日期相关的函数
select 'helloworld';
1. 获取当前 日期+时间 now()
select now();
2. 获取当前的日期 curdate() cur(current当前)
select curdate();
3. 获取当前时间 curtime()
select curtime();
4. 从年月日时分秒中提取年月日 和 提取时分秒
select date(now());
select time(now());
- 查询商品上传的日期
select date(created_time) from t_item;
select time(created_time) from t_item;
5. 从年月日时分秒中提取时间分量 extract(year from now())
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;
6. 日期格式化
- 格式：
%Y 四位年 2018 %y两位年 18
%m 两位月 05 %c一位月 5
%d 日
%H 24小时 %h 12小时
%i 分
%s 秒
- date_format(时间,格式)
- 举例： 把时间默认格式转成 年月日时分秒
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
- 把非标准格式的时间转回默认格式 str_to_date('非标准格式的时间',格式)
- 举例： 把 14.08.2018 08:00:00 转成标准格式
select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s');
####ifnull(x,y)函数
- age = ifnull(x,y) 如果x的值为null则age=y 如果x值不为null则age=x
1. 把员工表中奖金为null的改成0 其它的不变
update emp set comm=ifnull(comm,0);
####聚合函数
- 聚合函数用于对多行数据进行统计，平均值，最大值，最小值，求和，统计数量
- 平均值： avg(字段名称)
1. 查询所有员工的平均工资
select avg(sal) from emp;
2. 查询10号部门的平均工资
select avg(sal) from emp where deptno=10;
3. 查询戴尔商品的平均单价
select avg(price) from t_item where title like '%戴尔%';
- 最大值: max(字段名)
1. 查询所有员工中的最高工资
select max(sal) from emp;
2. 查询30号部门中的最高奖金
select max(comm) from emp where deptno=30;
- 最小值：min(字段名)
1. 查询20号部门的最低工资
select min(sal) from emp where deptno=20;
2. 查询商品表中所有商品中最便宜的价格是多少
select min(price) from t_item;
- 求和: sum(字段名)
1. 查询30号部门每个月需要发多少工资
select sum(sal) from emp where deptno=30;
2. 查询戴尔商品的库存总量
select sum(num) from t_item where title like '%戴尔%';
- 统计数量： count(字段名)
1. 查询员工表中的员工数量
select count(*) from emp;
2. 查询员工表中30号部门工资大于2000块钱的人数
select count(*) from emp where deptno=30 and sal>2000;
回顾： 平均值avg() 最大值max() 最小值min() 求和sum() 统计数量count()
###字符串相关
1. 字符串拼接 concat('aa','bb') ->aabb
- 案例： 查询emp表中 员工姓名和工资 工资后面显示单位元
select ename,concat(sal,'元') from emp;
2. 获取字符串的长度 char_length('abc') 3
- 案例 ：查询员工姓名和姓名的长度
select ename,char_length(ename) from emp;
3. 获取字符串在另一个字符串出现的位置
-格式1：instr(str,substr)
select instr('abcdefg','c');
-格式2：locate(substr,str)
select locate('c','abcdefg');
4. 插入字符串 insert(str,start,length,newstr)
select insert('abcdefg',3,2,'m');
5. 转大小写
- 格式： upper(str) lower(str)
select upper('nba'),lower('NBa');
6. 去两端空白
select trim(' a b ');
7. 截取字符串
- left(str,num)
select left('abcdefg',2);
- right(str,num)
select right('abcdefg',2);
- substring(str,start,?length)
select substring('abcdefg',2,3);
8. 重复 repeat
select repeat('ab',2);
9. 替换 replace
select replace('abcdefg','c','x');
10. 反转 reverse
select reverse('abc');
###课程回顾：
1. is null 和 is not null
2. 别名
3. 去重 distinct
4. > < >= <= = != 和 <>
5. and 和 or
6. in not in
7. between x and y 包含x和y
8. 模糊查询 like _单个未知 %0或多个未知
9. 排序 order by 字段名 asc/desc , 字段名 asc/desc
10. 分页 limit 跳过条数,请求数量(每页数量)
11. + - * / 7%2 等效 mod(7,2)
12. 日期相关： now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now())
Y y m c d H h i s date_format(时间，格式) str_to_date(非标准格式的时间，格式)
13. ifnull(x,y)
14. 聚合函数： 平均值avg 最大值max 最小值min 求和 sum 统计数量 count
15. 字符串相关： concat char_length instr locate insert upper() lower trim left right substring repeat replace reverse
###作业：
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_id 中 含有 CLERK 的）
13.案例：销售人员 job: SALESMAN 的最高薪水
14.案例：最早和最晚入职时间
15.案例：查询类别 163的商品总库存量
16.案例：查询 类别 163 的商品
17.案例：查询商品价格不大于100的商品名称列表
18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格
19.案例：查询品牌是三木,或价格在50以下的商品名称和价格
20.案例：查询品牌是三木、广博、齐心的商品名称和价格
21.案例：查询品牌不是联想、戴尔的商品名称和价格
22.案例：查找品牌是联想且价格大于10000的电脑名称
23.案例：查询联想或戴尔的电脑名称列表
24.案例：查询联想、戴尔、三木的商品名称列表
25.案例：查询不是戴尔的电脑名称列表
26.案例：查询所有是记事本的商品品牌、名称和价格
27.案例：查询品牌是末尾字符是'力'的商品的品牌、名称和价格
28.案例：名称中有联想字样的商品名称
29.案例：查询卖点含有'赠'产品名称
30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
32.案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
36.案例：查询不是30号部门的员工的所有信息
37.案例：查询奖金不为空的员工的所有信息
38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
43.案例：统计emp表中员工的总数量
44.案例：统计emp表中获得奖金的员工的数量
45.案例：求出emp表中所有的工资累加之和
46.案例：求出emp表中所有的奖金累加之和
47.案例：求出emp表中员工的平均工资
48.案例：求出emp表中员工的平均奖金
49.案例：求出emp表中员工的最高工资
50.案例：求出emp表中员工编号的最大值
51.案例：查询emp表中员工的最低工资。
52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金
58.案例：查询员工的编号是7369，7521，
59.案例：查询emp表中，职位是ANALYST，
60.案例：查询emp表中职位不是ANALYST
第四天=======================================================================================================================
###课程回顾
####day01
1. 数据库相关SQL
- 创建数据库 create database db1 character set utf8/gbk;
- 查看所有 show databases;
- 查看详情 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;
- 修改表名 rename table t1 to t2;
- 修改引擎字符集 alter table t1 engine=myisam/innodb charset=gbk/utf8
- 添加字段 alter table t1 add age int first/ after xxx;
- 删除字段 alter table t1 drop age;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型
- 修改类型和位置 alter table t1 modify 字段名 新类型 first/after xxx
- 删除表 drop table t1;
- 删除表并创建新表 truncate table t1;
3. 数据相关SQL
- 增加数据 insert into t1 (字段名1，字段名2) values(值1，值2),(值1，值2),(值1，值2);
- 查询数据 select * from t1 where age=18;
- 修改数据 update t1 set age=20 where id=10;
- 删除数据 delete from t1 where id<10;
###day02
1. 主键约束+自增 create table t1(id int primary key auto_increment,age int); 唯一且非空
2. 注释 comment
3. `用于修饰表名字段名可以省略 '修饰字符串
4. 冗余，由于表设计不合理导致出现大量的重复数据， 通过拆分表解决
5. 事务：数据库中执行同一业务多条SQL语句的工做单元，可以保证全部执行成功或全部执行失败
- 相关指令： begin rollback commit savepoint s1,rollback to s1
- 查看自动提交状态 show variables like '%autocommit%'; set autocommit=0/1;
6. SQL分类
- DDL：数据定义语言，包括create drop alter truncate,不支持事务
- DML：数据操作语言，包括insert delete update select(DQL) 支持事务
- DQL：数据查询语言，只包括select
- TCL：事务控制语言，包括事务相关的指令
- DCL：数据控制语言，分配用户权限相关SQL
7. 数据类型
- 整数： int(m) bigint(m) m代表显示长度 需要结合zerofill使用
- 浮点数： double(m,d)m代表总长度 d代表小数长度 ，decimal
- 字符串：char(m)固定长度 最大255 varchar(m)可变长度最大65535 超过255建议text ，text可变长度 最大65535
- 日期： date 保存年月日 time 时分秒 datetime 默认null 最大值9999-12-31，timestamp 默认当前时间 最大 2038-1-19
- 其它类型
###day03
1. is null 和 is not null
2. 别名
3. 去重 distinct
4. 比较运算符 > < >= <= = != <>
5. and 和 or
6. in not in
7. between x and y 包括x和y not between x and y
8. like _单个未知 %0或多个未知
9. 排序 order by 字段名 asc/desc,字段名 asc/desc;
10. 分页查询 limit 跳过条数,每页条数
11. 数值计算 + - * / % mod()
12. 日期 now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now())；
- 日期格式化： Y y m c d H h i s date_format(时间,格式) str_to_date(时间字符串,格式)
13. ifnull(x,y)
14. 聚合函数： 平均值avg，最大值max，最小值min，求和sum，统计数量 count
15. 字符串相关，concat char_length instr locate upper lower trim left right substring insert replace repeat reverse
1.案例：查询没有上级领导的员工的编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
select empno,ename,sal,comm from emp where comm is null or comm=0;
3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
select empno,ename,job,comm from emp where comm is not null and 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 ename 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,price from t_item where title like '%联想%' and price>10000;
26.案例：查询所有是记事本的名称和价格
select title,price from t_item where title like '%记事本%';
27.案例：查询品牌是末尾字符是'力'的名称和价格
select title,price from t_item where title like '%_力%';
30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
select empno,ename,job,sal from emp where sal between 1000 and 2000;
31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
32.案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
select empno,ename,job,sal from emp where ename like '%e%' and job !='manager';
33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
select empno,ename,deptno from emp where deptno in(10,20);
34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
select empno,ename,job,comm from emp where comm is null or ename not like '%t_';
35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
36.案例：查询不是30号部门的员工的所有信息
select * from emp where deptno!=30;
38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
select empno,ename,job from emp order by empno desc;
39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
select ename,job,sal from emp where dpetno in(10,30) order by sal;
40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
select * from emp order by deptno,empno desc;
41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;
42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
select empno,ename,sal,comm from emp where ename not like '%s%' order by sal,empno desc;
43.案例：统计emp表中员工的总数量
select count(*) from emp;
44.案例：统计emp表中获得奖金的员工的数量
select count(*) form emp where comm>0;
45.案例：求出emp表中所有的工资累加之和
select sum(sal) from emp;
47.案例：求出emp表中员工的平均工资
select avg(sal) from emp;
49.案例：求出emp表中员工的最高工资
select max(sal) from emp;
51.案例：查询emp表中员工的最低工资。
select min(sal) from emp;
52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
select count(*) 总人数, sum(sal) 总工资,avg(sal) 平均工资, max(comm) 最高奖金, min(comm) 最低奖金 from emp;
53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp where sal between 1000 and 3000;
###数学相关函数
- 向下取整 floor(num)
select floor(3.84); 3
- 四舍五入 round(num)
select round(23.8); 24
- round(num,m)
select round(23.879,2); 23.88
- 非四舍五入
select truncate(23.879,2); 23.87
- 随机数 rand() 0-1
获取3、4、5 随机数
select floor(rand()*3)+3;
###分组查询
- 将某个字段的相同值分为一组，对其它字段的数据进行聚合函数的统计，称为分组查询
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. 查询每个职位中工资大于1000的人数
select job,count(*) from emp where sal>1000 group by job;
5. 查询每个领导的手下人数
select mgr,count(*) from emp
where mgr is not null group by mgr;
- 多字段分组查询
1. 查询每个部门每个主管的手下人数
select deptno,mgr,count(*) from emp
where mgr is not null group by deptno,mgr;
53. 案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
select deptno,count(*),sum(sal) from emp group by deptno
order by count(*),sum(sal) desc;
select deptno,count(*) c,sum(sal) s from emp group by deptno
order by c,s desc;
54. 案例：查询工资在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;
55. 案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a;
###having
- having后面可以写普通字段的条件也可以写聚合函数的条件，但是不推荐在having后面写普通字段的条件
- where后面不能写聚合函数的条件
- having要结合分组查询使用
1. 查询每个部门的平均工资，要求平均工资大于2000.
-错误写法（where后面不能写聚合函数）
select deptno,avg(sal) from emp where avg(sal)>2000
group by deptno;
-正确写法：
select deptno,avg(sal) a from emp
group by deptno having a>2000;
2. 查询t_item表中每个分类category_id的平均单价要求平均单价低于100
select category_id,avg(price) a from t_item
group by category_id having a<100;
3. 查询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 desc;
4. 查询emp表中每个部门的平均工资高于2000的部门编号，部门人数，平均工资，最后根据平均工资降序排序
select deptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by a desc;
5. 查询emp表中不是以s开头，每个职位的名字，人数，工资总和，最高工资，高滤掉平均工资是3000的职位，根据人数升序排序 如果人数一致则根据工资总和降序排序
select job,count(*) c,sum(sal) s,max(sal) from emp where job not like 's%' group by job having avg(sal)!=3000 order by c,s desc;
6. 查询emp表中每年入职的人数。
select extract(year from hiredate) year,count(*) from emp
group by year;
7. 查询每个部门的最高平均工资（提高题）
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
- 拿最高平均工资的部门编号 (并列第一的问题无法解决)
select deptno from emp group by deptno order by avg(sal) desc limit 0,1;
###子查询（嵌套查询）
1. 查询emp表中工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=5000;
-通过子查询把上面两条合并成一条
select * from emp where sal=(select max(sal) from emp);
2. 查询emp表中工资大于平均工资的所有员工的信息
select avg(sal) from emp;
select * from emp where sal>(select avg(sal) from emp);
3. 查询工资高于20号部门最高工资的所有员工信息
select max(sal) from emp where deptno=20
select * from emp where sal>(select max(sal) from emp where deptno=20);
4. 查询和jones相同工作的其他员工信息
select job from emp where ename='jones'
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
5. 查询工资最低的员工的同事们的信息(同事=相同job)
-得到最低工资
select min(sal) from emp;
-得到最低工资哥们的工作
select job from emp where sal=(select min(sal) from emp);
-通过工作找到同事们 还要排除最低工资那哥们儿
select * from emp where job=(select job from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
6. 查询最后入职的员工信息
select max(hiredate) from emp;
select * from emp where hiredate=(select max(hiredate) from emp);
7. 查询员工king的部门编号和部门名称(需要用到dept表)
select deptno from emp where ename='king';
select deptno,dname from dept where deptno=(select deptno from emp where ename='king');
8. 查询有员工的部门信息
-查询员工表中出现的部门编号
select distinct deptno from emp;
select * from dept where deptno in (select distinct deptno from emp);
9. 查找平均工资最高的部门信息 (最大难度，需要考虑并列第一问题)
-得到最高平均工资
select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1;
- 通过平均工资找到部门编号
select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1);
- 通过部门编号查询部门信息
select * from dept where deptno in(上面一坨);
select * from dept where deptno in(select deptno from emp group by deptno having avg(sal)=(select avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1));
- 子查询总结：
1. 嵌套在SQL语句中的查询语句称为子查询
2. 子查询可以嵌套n层
3. 子查询可以写在哪些位置？
- 写在where或having后面作为查询条件的值
- 写在from后面当成一张表使用 **必须有别名**
select * from emp where deptno=20;
select ename from (select * from emp where deptno=20) t1;
- 写在创建表的时候
create table newemp as (select ename,sal,deptno from emp where deptno=20);
###关联查询
- 同时查询多张表的查询方式称为关联查询
1. 查询每一个员工姓名和其对应的部门名称
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
2. 查询在new york工作的所有员工的信息
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.loc='new york';
3. 查询价格在50以内的商品标题和商品分类名称
select i.title,c.name
from t_item i ,t_item_category c
where i.category_id=c.id and i.price<50;
####笛卡尔积
- 如果关联查询不写关联关系，则得到两张表结果的乘积，这个乘积称为笛卡尔积
- 笛卡尔积是错误的查询方式导致的结果，工作中切记不要出现
###等值连接和内连接
- 这两种查询方式得到的结果是一样的
1. 等值连接： select * from A,B where A.x=B.x and A.age=18;
2. 内连接：select * from A join B on A.x=B.x where A.age=18;
- 1. 查询每一个员工姓名和其对应的部门名称
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;
###外链接
查询A，B两张表的数据，如果查询两张表的交集数据使用内连接或等值连接，如果查询某一张表的全部数据另外一张表的交集数据则用外链接
-左外链接： select * from A left join B on A.x=B.x where A.age=18;
-右外链接： select * from A right join B on A.x=B.x where A.age=18;
1. 查询所有员工和对应的部门名称
-插入新数据
insert into emp (empno,ename,sal) values(10010,'Tom',500);
select e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;
2. 查询所有部门名称和对应的员工姓名
select e.ename,d.dname
from emp e right join dept d
on e.deptno=d.deptno;
###关联查询总结：
- 关联查询的查询方式：1. 等值连接 2.内连接 3.外链接（左外和右外）
- 查询两张表的交集用等值或内连接，推荐使用内连接
- 查询一张表的全部数据和另外一张表的交集数据使用外链接
####课程回顾
1. 数学相关
- 向下取整 floor()
- 四舍五入 round(num,m)
- 非四舍五入 truncate(num,m)
- 随机数 rand()
2. 分组查询 group by
3. having 后面写聚合函数的条件，需要结合group by使用
4. 子查询 可以嵌套n层， 1. where或having后面 2. from后面 3.创建表的时候
5. 关联查询：三种：1.等值连接 2.内连接 3.外链接
作业：
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 hiredate,count(*)c from emp group by hiredate;
5. 少于等于3个人的部门信息
select deptno from emp group by deptno having count(*)<=3;
select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3)
6. 拿最低工资的员工信息
select min(sal) from emp
select * from emp where sal=(select min(sal) from emp);
7. 只有一个下属的主管信息
select mgr from emp where is not null group by mgr having count(*) =1;
select * from emp where mgr in (select mgr from emp where is not null group by mgr having count(*) =1);
8. 每月发工资最多的部门信息
select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;
select deptno from emp group by deptno having sum(sal)=( select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1);
select * from dept where deptno in ( select deptno from emp group by deptno having sum(sal)=( select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1))
9. 下属最多的人，查询其个人信息
select deptno from emp where is not null group by deptno order by count(*) desc limit 0,1;
select * from dept where deptno = (select deptno from emp where is not null group by deptno order by count(*) desc limit 0,1);
select count(*) from emp where mgr is not null group by mgr order by count(*) desc limit 0,1;
select mgr from emp group by mgr having count(*)=( select count(*) from emp where mgr is not null group by mgr order by count(*) desc limit 0,1)
select * from emp where empno in ( select mgr from emp group by mgr having count(*)=( select count(*) from emp where mgr is not null group by mgr order by count(*) desc limit 0,1))
10. 拿最高工资员工的同事信息
select max(sal) from emp;
select job from emp where sal = (select max(sal) from emp)
select * from emp where job=(select job from emp where sal = (select max(sal) from emp));
11. 和最后入职的员工在同一部门的员工信息
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)
12. 查询平均工资高于20号平均工资的部门信息
select deptno from emp group by deptno order by avg(sal) desc limit 0,1;
select * from dept where deptno=( select deptno from emp group by deptno order by avg(sal) desc limit 0,1);
select avg(sal) from emp where deptno=20;
select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20);
select * from dept where deptno=(select deptno from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20))
13. 查询员工信息和员工对应的部门名称
select e.*,d.dname
from emp e join dept d on e.deptno=d.deptno
14. 查询员工信息，部门名称，所在城市
select e.*,d.dname,d.loc
from emp e join dept d on e.deptno=d.deptno
15. 查询Dallas市所有的员工信息
select e.*
from emp e join dept d
on e.deptno = d.deptno
where d.loc='dallas'
select d.loc,count(e.ename)
from emp e right join dept d
on e.deptno = d.deptno
group by d.loc
select e.ename,d.dname
from emp e join dept d
on e.deptno = d.deptno
查询每一个员工名字和对应的主管名字
如果表中建立关系的字段指向当前表的主健
select e.ename,m.ename
from emp e left join emp m
on e.mgr = m.empno
select e.ename,m.ename,d.dname
from emp e join emp m
on e.mgr = m.empno
join dept d
on e.deptno = d.deptno
select e.ename,m.empno,m.ename,m.job
from emp e left join emp m
on e.mgr=m.empno;
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e left join dept d
on e.deptno = d.deptno
where e.ename not like '%k%'
select d.*,e.ename,e.sal
from emp e right join dept d
on e.deptno=d.deptno
where e.deptno!=10
order by e.deptno desc ,e.sal;
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(uid int,nick varchar(10),age int,phone int);
insert into user values(null,'libai','admin'),(null,'liubei','liu'),(null,'guanyu','234');
insert into userinfo values(1,'李白',28,'138383833388'),(2,'刘备',33,'13222333322'),(3,'关羽',30,'13555333355')
select u.*,o.*
from user u join userinfo o
on u.id=o.uid
select u.username,o.nick,o.phone
from user u join userinfo o
on u.id = o.uid
select u.username,u.password
from user u join userinfo o
on u.id = o.uid
where o.nick='李白'
select o.nick
from user u join userinfo o
on u.id = o.uid
where u.username='guanyu'
+----+----------+----------+------+--------+------+------------+
| id | username | password | uid | nick | age | phone|
+----+----------+----------+------+--------+------+------------+
| 1 | libai | admin | 1 | 李白 | 28 | 2147483647 |
| 2 | liubei | liu | 2 | 刘备 | 33 | 2147483647 |
| 3 | guanyu | 234 | 3 | 关羽 | 30 | 2147483647 |
+----+----------+----------+------+--------+------+------------+
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,dept_id int);
create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(10));
insert into t_dept values (null,'法师部','A国'),(null,'战士部','B国'),(null,'射手部','C国');
insert into t_emp values (null,'诸葛亮',3000,1),(null,'周瑜',2000,1),(null,'关羽',1500,2),(null,'张飞',1000,2),(null,'孙郑香',5000,3)
select u.*,o.*
from t_emp u join t_dept o
on u.dept_id=o.id
select t.name,t1.name
from t_emp t join t_dept t1
on t.dept_id = t1.id
select
from t_emp t join t_dept t1
on
create table student(id int primary key auto_increment,name varchar(10));
create table teacher(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);
insert into teacher values(null,'长老师'),(null,'范老师');
insert into student values(null,'小明'),(null,'小红'),(null,'小绿'),(null,'小兰');
select s.name,t.name
from student s join teacher t
join t_s
on s.id = t_s.sid and t.id=t_s.tid
select t.name,s.name
from student s join teacher t
join t_s
on s.id = t_s.sid and t.id=t_s.tid
where t.name='长老师'
select t.name,s.name
from student s join teacher t
join t_s
on s.id = t_s.sid and t.id=t_s.tid
where s.name='小红'
create table user(id int primary key auto_increment,name varchar(10));
create table role(id int primary key auto_increment,name varchar(10));
create table module(id int primary key auto_increment,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
insert into user values(null,'男浏览'),(null,'男发帖'),(null,'男删除'),(null,'女浏览'),(null,'女发帖'),(null,'女删除');
insert into role values(null,'男会员'),(null,'男管理员'),(null,'女游客'),(null,'女会员');
insert into module(name) values('男浏览'),('男发帖'),('男删除'),('女浏览'),('女发帖'),('女删除');
insert into role(name) values('男会员'),('男管理员'),('女游客'),('女会员');
insert into user(name) values('刘德华'),('张学有'),('凤姐');
insert into u_r values(1,1),(1,3),(2,2),(3,4),(3,1);
insert into r_m values(1,1),(1,2),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id;
select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id where u.name='凤姐';
select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id where m.name='男发帖';
第五天=====================================================================================================
###课程回顾
1. 主键约束+自增 create table t1(id int primary key auto_increment) 唯一且非空 ，
2. 注释 comment
3. ` 修饰表名字段名 '修饰字符串
4. 冗余
5. 事务：数据库执行同一业务多条SQL语句的工作单元，全部成功或全部失败
- 开启事务 begin 回滚 rollback 提交 commit 保存回滚点savepoint s1; rollback to s1; show variables like '%autocommit%'; set autocommit=0/1;
6. SQL分类
- DDL：数据定义语言，包含：create alter drop truncate 不支持事务
- DML：数据操作语言，包括：insert delete update select（DQL）支持事务
- DQL：数据查询语言，包括：select
- TCL：事务控制语言
- DCL：数据控制语言
7. 数据类型
- 整数： int bigint(m) m代表显示长度 需要结合zerofill使用
- 浮点数:double(m,d) m总长度 d小数长度 decimal
- 字符串：char 固定长度 最大255 varchar可变长度最大65535 超过255建议使用text，text 可变长度 最大65535
- 日期： date time datetime 默认null 最大值9999-12-31 timestamp默认当前时间最大2038-1-19
###day03
1. is null 和 is not null
2. 别名
3. 去重 distinct
4. 比较运算符 > < >= <= = !=和<>
5. and 和 or
6. in not in
7. between x and y
8. like _单个未知 %0或多个未知
9. 排序 order by 字段名 asc/desc,字段名 asc/desc；
10. limit 跳过条数，每页条数
11. 日期： now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now())
- Y y m c d H h i s date_format() str_to_date()
12. ifnull(x,y)
13. 聚合函数： 平均值avg 最大值max 最小值min 求和sum 统计数量count
14. 字符串： concat char_length instr locate upper lower trim left right substring insert replace repeat reverse
15. 数值计算 + - * / % mod()
###day04
1. 数学相关 floor() round(num,m) truncate(num,m) rand() 0-1
2. 分组查询 group by 字段名,字段名
3. having 后面写聚合函数的条件 where只能写普通字段的条件
4. 子查询 可以嵌套n层
- 写在where和having后面 作为查询条件的值
- 写在from后面当成一张表 需要起别名
- 写在创建表的时候 把结果保存到新的表中
5. 关联查询
- 查询方式：1.等值连接 2.内连接 3.外链接
- 如果查询的数据是两个表的交集用等值连接或内连接(推荐)
- 如果查询一张表的全部数据另外一张表的交集数据则使用外链接
- 笛卡尔积
作业：
select ..... from 表名 where ... group by .... having .... order by .... limit ....
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) year,count(*) from emp
group by year;
5. 少于等于3个人的部门信息
select deptno from emp group by deptno
having count(*)<=3;
select * from dept where deptno in (select deptno from emp group by deptno
having count(*)<=3);
6. 拿最低工资的员工信息
select * from emp where sal=(select min(sal) from emp);
7. 只有一个下属的主管信息
-得到每一个领导的下属人数
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);
8. 每月发工资最多的部门信息
-得到最多的钱数
select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1;
-通过钱数找到对应的部门编号
select deptno from emp group by deptno having sum(sal)=(select sum(sal) from emp group by deptno order by sum(sal) desc limit 0,1);
- 通过编号得到部门信息
select * from dept where deptno in (上面一坨);
9. 下属最多的人，查询其个人信息
- 得到最多的人数
select count(*) from emp where mgr is not null group by mgr order by count(*) desc limit 0,1;
- 通过人数找到对应的领导编号
select mgr from emp group by mgr having count(*)=(select count(*) from emp where mgr is not null group by mgr order by count(*) desc limit 0,1);
- 通过得到的领导编号查询其个人信息
select * from emp where empno in(上面一坨);
10. 拿最高工资员工的同事信息
- 最高工资
select max(sal) from emp;
- 找到最高工资员工的工作
select job from emp where sal=(select max(sal) from emp);
- 找到相同工作的人
select * from emp where job=(select job from emp where sal=(select max(sal) from emp)) and sal!=(select max(sal) from emp);
11. 和最后入职的员工在同一部门的员工信息
张三 李四 王五 赵六
- 最后入职的时间
select max(hiredate) from emp;
- 通过时间找到部门编号
select deptno from emp where hiredate=(select max(hiredate) from emp);
- 通过部门编号找到部门内部的其它员工
select * from emp where deptno in(select deptno from emp where hiredate=(select max(hiredate) from emp)) and hiredate!=(select max(hiredate) from emp);
12. 查询平均工资高于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));
13. 查询员工信息和员工对应的部门名称
select e.*,d.dname
from emp e join dept d
on e.deptno=d.deptno;
14. 查询员工信息，部门名称，所在城市
select e.*,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno;
15. 查询Dallas市所有的员工信息
select e.*
from emp e join dept d
on e.deptno=d.deptno
where d.loc='dallas';
16. 查询每个城市的员工人数（需要查询出 波士顿0人）
select d.loc,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
group by d.loc;
17. 查询每个员工的名字和对应的主管名字
-如果表中建立关系的字段指向当前表的主键，则称为自关联，关联查询时把一张表当成两张表
select e.ename,m.ename
from emp e left join emp m
on e.mgr=m.empno;
18. 查询每个员工的名字、对应的主管名字和对应的部门名字
select e.ename,m.ename,d.dname
from emp e left join emp m
on e.mgr=m.empno
join dept d
on e.deptno=d.deptno;
19. 查询emp表中所有员工的姓名以及该员工的领导编号，领导姓名，领导职位
select e.ename,m.empno,m.ename,m.job
from emp e left join emp m
on e.mgr=m.empno;
20. 查询emp表名字中没有字母k的所有员工的编号，姓名，职位，工资，部门名
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e left join dept d
on e.deptno=d.deptno
where e.ename not like '%k%';
21. 查询dept表中所有部门的信息和与之关联的员工名字和工资，排除掉10号部门，根据部门编号降序排序，如果部门编号一致则按照工资升序排序
select d.*,e.ename,e.sal
from emp e right join dept d
on e.deptno=d.deptno
where e.deptno!=10
order by e.deptno desc,e.sal;
###表设计之关联关系
- 什么是外键：主键是用于表示数据唯一性的字段，外键是用于建立关系的字段，值通常指向另外一张表的主键
####一对一
- 什么是一对一关系： 有A、B两张表，A表中一条数据对应B表中的一条数据，称为一对一关系
- 应用场景： 用户表和用户信息扩展表，商品表和商品信息扩展表
- 如何建立关系：在从表中添加外键指向主表的主键
- 练习： 创建user（id,username,password）和userinfo（uid,nick,age,phone） 然后插入以下数据
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(uid int,nick varchar(10),age int,phone varchar(15));
1 libai admin 李白 28 13838383388
2 liubei admin 刘备 33 13222333322
3 guanyu 123456 关羽 30 13555333355
insert into user values(null,'libai','admin'),(null,'liubei','admin'),(null,'guanyu','123456');
insert into userinfo values(1,'李白',28,'13838383388'),
(2,'刘备',33,'13222333322'),(3,'关羽',30,'13555333355');
1. 查询每一个用户的用户名、昵称和电话
select u.username,ui.nick,ui.phone
from user u join userinfo ui
on u.id=ui.uid;
2. 查询李白的用户名和密码
select u.username,u.password
from user u join userinfo ui
on u.id=ui.uid
where ui.nick='李白';
3. 查询guanyu的昵称
select ui.nick
from user u join userinfo ui
on u.id=ui.uid
where u.username='guanyu';
####一对多
- 什么是一对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的一条数据
- 应用场景：员工表和部门表 商品表和商品分类表
- 如何建立关系：在多的表中添加外键指向另外一张表的主键
- 练习：创建 t_emp(id,name,sal,dept_id)和t_dept(id,name,loc)
create table t_emp(id int primary key auto_increment,name varchar(10),sal int,dept_id int);
create table t_dept(id int primary key auto_increment,name varchar(10),loc varchar(10));
- 保存以下数据：
1 诸葛亮 3000 法师部 A国
2 周瑜 2000 法师部 A国
3 关羽 1500 战士部 B国
4 张飞 1000 战士部 B国
5 孙尚香 5000 射手部 C国
insert into t_dept values(null,'法师部','A国'),(null,'战士部','B国'),(null,'射手部','C国');
insert into t_emp values(null,'诸葛亮',3000,1),(null,'周瑜',2000,1),(null,'关羽',1500,2),(null,'张飞',1000,2),(null,'孙尚香',5000,3);
1. 查询每个员工的名字和所在部门的名称
select e.name,d.name
from t_emp e join t_dept d
on e.dept_id=d.id;
2. 查询A国的员工姓名
select e.name
from t_emp e join t_dept d
on e.dept_id=d.id where d.loc='A国';
3. 查询张飞的部门名称
select d.name
from t_emp e join t_dept d
on e.dept_id=d.id where e.name='张飞';
4. 查询工资在2000以下员工姓名，所在地
select e.name,d.loc
from t_emp e join t_dept d
on e.dept_id=d.id where e.sal<2000;
####多对多
- 什么是多对多：AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条对应A表中的多条
- 应用场景： 老师表和学生表
- 如何建立关系：创建一个关系表，两个外键字段，分别指向另外两张表的主键
- 练习： 创建student(id,name) teacher(id,name) 和关系表 t_s(tid,sid)
create table student(id int primary key auto_increment,name varchar(10));
create table teacher(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
保存以下数据：
苍老师：小明 小红 小绿 小蓝
范老师：小明 小红
insert into student values(null,'小明'),(null,'小红'),(null,'小绿'),(null,'小蓝');
insert into teacher values(null,'苍老师'),(null,'范老师');
insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);
1. 查询每个学生对应的老师
select s.name,t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id;
2. 查询苍老师的学生有哪些
select s.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id where t.name='苍老师';
3. 查询小红的老师是谁
select t.name
from student s join t_s ts
on s.id=ts.sid
join teacher t
on ts.tid=t.id where s.name='小红';
###自关联
- 当前表的外键指向当前表的主键，这种关联方式叫做自关联
- 应用场景：需要保存上下级关系时
1. 查询员工姓名和对应的主管姓名
select e.ename,m.ename
from emp e join emp m
on e.mgr=m.empno;
###连接方式和关联关系
- 连接方式：包括等值连接，内连接，外连接 是指查询多张表数据时使用的查询方式
- 关联关系：包括 一对一 一对多 多对多，是指设计表时，两张表之间存在的逻辑关系
####表设计案例：权限管理
- 需要创建5张表，3张主表 2张关系表
create database db5 character set utf8;
use db5;
create table user(id int primary key auto_increment,name varchar(10));
create table role(id int primary key auto_increment,name varchar(10));
create table module(id int primary key auto_increment,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
插入以下数据 并在关系表中建立关系：
权限：男浏览 男发帖 男删帖 女浏览 女发帖 女删帖
角色：男会员，男管理员，女游客，女会员
用户：刘德华（男会员，女游客） 张学友（男管理员） 凤姐（女会员，男会员）
insert into module (name) values('男浏览'),('男发帖'),('男删帖'),('女浏览'),('女发帖'),('女删帖');
insert into role (name) values('男会员'),('男管理员'),('女游客'),('女会员');
insert into user (name) values('刘德华'),('张学友'),('凤姐');
insert into u_r values(1,1),(1,3),(2,2),(3,4),(3,1);
insert into r_m values(1,1),(1,2),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
1. 查询每个用户对应的权限有哪些
select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id;
2. 查询凤姐的权限有哪些
select m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id where u.name='凤姐';
3. 查询拥有 男发帖权限的用户有谁
select u.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on ur.rid=rm.rid
join module m
on rm.mid=m.id where m.name='男发帖';
create table Trade(id int primary key auto_increment,name varchar(10),leixing varchar(10),xing varchar(5))
create table xiaoming(id int primary key auto_increment,name varchar(10));
create table jiaoyi(tid int,xid int,jin int);
create table jiaoyix(xid int,tid int,jin int);
insert into Trade values(null,'亲戚','微信','男'),(null,'朋友','微信','女'),(null,'同事','支付宝','女'),(null,'妈妈','微信','女'),(null,'爸爸','微信','男');
insert into xiaoming values(null,'小维')
insert into jiaoyi values(1,1,50),(2,1,40),(3,1,30),(4,1,20),(5,1,60);
insert into jiaoyix values(1,1,20),(1,2,40),(1,3,30),(1,4,20),(1,5,50);
select sum(distinct j.jin)收益,sum(distinct x.jin)支出
from Trade t join jiaoyi j
on t.id = j.tid
join jiaoyix x
on x.xid = j.xid
join xiaoming g
on j.xid = g.id
select distinct t.name,j.jin
from Trade t join jiaoyi j
on t.id = j.tid
join jiaoyix x
on x.xid = j.xid
join xiaoming g
on j.xid = g.id
where t.xing='女' and j.jin>10;
select distinct t.leixing,sum( distinct j.jin)中金
from Trade t join jiaoyi j
on t.id = j.tid
join jiaoyix x
on x.xid = j.xid
join xiaoming g
on j.xid = g.id
group by t.leixing ;
select sum(jin)收益 from jiaoyi;
select sum(jin)支出 from jiaoyix;
create table trade(id int primary key auto_increment,time date,money int,type varchar(5),pid int)
create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');
insert into trade values(null,'2018-03-20',1000,'微信',1),(null,'2018-03-20',500,'现金',2),(null,'2018-03-20',-50,'现金',2),(null,'2018-03-20',2000,'支付宝',3),(null,'2018-03-20',-5,'支付宝',3),(null,'2018-03-20',2000,'微信',4),(null,'2018-03-20',-2000,'微信',5);
select sum(money)from trade where time>str_to_date('2018年2月15号','%Y年%c月%d号');
select p.name,t.money
from trade t join person p
on t.pid=p.id
where time>str_to_date('2018年2月15号','%Y年%c月%d号')
and t.money not between -100 and 100
and p.gender='女'
and p.rel='亲戚';
select t.type,sum(t.money)
from trade t join person p
on t.pid=p.id
where money>0
group by t.type
;
create view v_emp_110 as (select * from emp where deptno=10)
create view v_emp_nosal as(select empno,ename,comm,deptno from emp);
create view v_emp_inof as(select avg(sal),max(sal),min(sal) from emp);
select*from v_emp_inof;
insert into v_emp_110(empno,ename,deptno)values(10011,'刘备',10);
insert into v_emp_110(empno,ename,deptno)values(10111,'关羽',20);
create view v_emp_20 as(select*from emp where deptno=20)with check option;
insert into v_emp_20(empno,ename,deptno)values(10013,'赵云',20);
update v_emp_20 set ename='赵云2' where ename='赵云';
update v_emp_20 set ename='刘备2' where ename='刘备';
delete from v_emp_20 where deptno=10;
create or replace view v_emp_10 as(select*from emp where deptno=10 and sal>2000);
create view v_emp_10 as(select ename name from emp where deptno=10);
select name from v_emp_10;成功
select enmae from v_emp_10;失败
drop view v_emp_10
create table t_null(id int,age int not null);
insert into t_null values(1,18);
insert into t_null values(2,null)
create table t_unique(id int,age int unique);
insert into t_unique values(1,28);
insert into t_unique values(2,28);
create table t_default(id int,age int default 20);
insert into t_default(id) values(1);
insert into t_default values(2,null);
insert into t_default values(3,30);
1.创建部门表
create table detp(id int primary key auto_increment,name varchar(10));
2,创建员工表
create table emp(id int primary key auto_increment,name varchar(10),detp_id int,constraint fk_dept foreign key(detp_id) references detp(id));
插入数据
insert into detp values(null,'神仙'),(null,'妖怪')
insert into emp values(null,'悟空',1)
insert into emp values(null,'晒亚人',3)
delete form detp where id = 1;
drop table detp;
source /home/soft01/桌面/item_backup.sql
select * from item2 where title='100';
create index index_item_title on item2(title);
show index from item2;
drop index index_item_title on item2;
create index index_item_title_price on item2(title,price);
select deptno,group_concat(ename) from emp group by deptno;
create table p(pno int,pname varchar,paddr varchar);
create table s(sno int,sname varchar,sprice varchar);
create table sp(sno int,pno int,spcnt int);
create table student(id int primary key auto_increment,name varchar(5),subject varchar(5),score int);
insert into student values(null,'张三','语文',66),(null,'张三','数学',77),(null,'张三','英语',55),(null,'张三','体育',77),
insert into student values(null,'李四','语文',59),(null,'李四','数学',88),(null,'李四','英语',78),(null,'李四','体育',95);
insert into student values(null,'王无','语文',75),(null,'王无','数学',54),(null,'王无','英语',98),(null,'王无','体育',88);
select name,avg(score)a from student group by name order by a desc;
select name,group_concat(subject,"-",score)from student group by name;
select name,max(score),min(score)from student group by name;
select name,group_concat(subject,"-",score),count(*)from student where score<60 group by name;
练习题
create table p(pno int,pname varchar(10),paddr varchar(10));
create table s(sno int,sname varchar(10),sprie varchar(10));
create table sp(sno int,pno int,spcnt int);
insert into p values(1,'亚太长裤','背景'),(2,'西安长裤','景');
insert into s values(1,'苹果','400');
insert into sp values(1,1,3000);
select p1.pno,p1.paddr,s.spcnt
from p p1 join sp s
on p1.pno=s.pno
join s s1
on s1.sno=s.sno
where p1.pname='亚太长裤' and s.sno=1;
select s1.sno,s1.sname,s.spcnt*s1.sprie
from p p1 join sp s
on p1.pno=s.pno
join s s1
on s1.sno=s.sno
where s.spcnt>1000;
select s.spcnt+s.spcnt/2
from p p1 join sp s
on p1.pno=s.pno
join s s1
on s1.sno=s.sno
where p1.pname='亚太长裤';
第六天===========================================================================================================
###课程回顾
1. 一对一：AB两张表A表中一条对应B表中一条 同时B表一条对应A表一条，在从表中添加外键指向主表的主键
2. 一对多：AB两张表A表中一条对应B表中的多条，同时B表中的一条数据对应A表中的一条, 在多的表中添加外键指向另外一张表的主键
3. 多对多：AB两张表A表中一条对应B表中的多条，同时B表中的一条数据对应A表中的多条，创建一张关系表保存另外两张表的主键
4. 自关联： 当前表的外键指向当前表的主键
5. 连接方式：等值连接，内连接，外链接
6. 关联关系：一对一 一对多 多对多
7. 权限管理案例， 3张主表：用户表 角色表 权限表 2张关系表 用户角色关系表 角色权限关系表
流水表
流水号 时间 金额 支付类型 pid
人物表
id 名字 性别 关系
1. 创建表
create table trade(id int primary key auto_increment,time date,money int,type varchar(5),pid int);
create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
- 插入人物数据：
刘德华 男 亲戚，杨幂 女 亲戚，马云 男 同事，特朗普 男 朋友，
貂蝉 女 朋友
insert into person (name,gender,rel) values('刘德华','男','亲戚'),('杨幂','女','亲戚'),('马云','男','同事'),('特朗普','男','朋友'),('貂蝉','女','朋友');
- 插入流水数据：
刘德华 微信 收1000 2018-03-20
杨幂 现金 收500 发50 2018-04-14
马云 支付宝 收20000 发5 2018-3-11
特朗普 微信 收2000 2018-05-18
貂蝉 微信 发20000 2018-07-22
insert into trade values(null,'2018-03-20',1000,'微信',1),
(null,'2018-04-14',500,'现金',2),
(null,'2018-04-14',-50,'现金',2),
(null,'2018-3-11',20000,'支付宝',3),
(null,'2018-3-11',-5,'支付宝',3),
(null,'2018-05-18',2000,'微信',4),
(null,'2018-07-22',-20000,'微信',5);
2. 查询2018年2月15号到现在的红包收益
select sum(money) from trade where time>str_to_date('2018年2月15号','%Y年%c月%d号');
3. 查询2018年2月15号到现在金额大于100 所有女性亲戚的名字和金额
select p.name,t.money
from trade t join person p
on t.pid=p.id
where time>str_to_date('2018年2月15号','%Y年%c月%d号')
and t.money not between -100 and 100
and p.gender='女'
and p.rel='亲戚';
4. 查询三个平台分别收入的红包金额
select type,sum(money) from trade
where money>0
group by type;
###视图
- 什么是视图： 数据库中的表和视图都是其内部的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一条SQL查询语句。
- 为什么使用视图：因为有些数据的查询需要书写大量的SQL语句，每次书写效率太低，使用视图可以起到SQL重用的作用，视图可以隐藏敏感信息
- 创建视图的格式：
create view 视图名 as 子查询;
create table 表名 as 子查询
- 创建一个10号部门员工的视图
create view v_emp_10 as (select * from emp where deptno=10);
-从视图中查询数据
select * from v_emp_10;
- 创建一个没有工资的员工表视图
create view v_emp_nosal as (select empno,ename,comm,deptno from emp);
-查询 select * from v_emp_nosal
####视图的分类
1. 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询。可以对视图中的数据进行增删改查操作
2. 复杂视图：和简单视图相反，只能对视图中的数据进行查询操作
- 创建一个复杂视图
create view v_emp_info as (select avg(sal),max(sal),min(sal) from emp);
-查询 select * from v_emp_info;
####对简单视图进行增删改查，操作方式和table一样
1. 插入数据
insert into v_emp_10 (empno,ename,deptno) values(10011,'刘备',10);
select * from v_emp_10;
select * from emp;
- 如果插入一条在视图中不可见，但是原表中却可见的数据称为 数据污染。
insert into v_emp_10 (empno,ename,deptno) values(10012,'关羽',20);
select * from v_emp_10;
select * from emp;
- 通过 with check option 解决数据污染问题
create view v_emp_20 as (select * from emp where deptno=20) with check option;
insert into v_emp_20 (empno,ename,deptno) values(10013,'赵云',20); //成功
insert into v_emp_20 (empno,ename,deptno) values(10014,'黄忠',30); //失败
- 修改和删除视图中的数据（只能修改删除视图中有的数据）
update v_emp_20 set ename='赵云2' where ename='赵云';
update v_emp_20 set ename='刘备2' where ename='刘备';//修改失败
delete from v_emp_20 where deptno=10;//没有数据被删除
- 创建或替换视图
create or replace view v_emp_10 as (select * from emp where deptno=10 and sal>2000);
- 删除视图
drop view 视图名;
drop view v_emp_10;
show tables;
- 如果创建视图的子查询中使用了别名 则对视图操作时只能使用别名
create view v_emp_10 as (select ename name from emp where deptno=10);
select name from v_emp_10;//成功
select ename from v_emp_10;//失败
####视图总结：
1. 视图是数据库中的对象，代表一段SQL语句，可以理解成一张虚拟的表
2. 作用： 重用SQL，隐藏敏感信息
3. 分类：简单视图（创建视图时不使用去重、函数、分组、关联查询，可以对数据进行增删改查）和复杂视图（和简单视图相反，只能对数据进行查询操作）
4. 插入数据时有可能出现数据污染，可以通过with check option解决
5. 删除和修改只能操作视图中存在的数据
6. 起了别名后只能用别名
###约束
- 什么是约束： 约束就是给表字段添加的限制条件
####主键约束+自增 primary key auto_increment
- 作用：唯一且非空
####非空约束 not null
- 作用：该字段的值不能为null
create table t_null(id int,age int not null);
insert into t_null values(1,18); //成功
insert into t_null values(2,null);//失败
####唯一约束 unique
- 作用： 该字段的值不能重复
create table t_unique(id int,age int unique);
insert into t_unique values(1,28);//成功
insert into t_unique values(2,28);//失败 不能重复
####默认约束 default
- 作用： 给字段设置默认值
create table t_default(id int,age int default 20);
insert into t_default (id) values(1); //默认值会生效
insert into t_default values(2,null);//默认值不会生效
insert into t_default values(3,30);//可以赋值其它值
####外键约束
- 外键：用来建立关系的字段称为外键
- 外键约束： 添加外键约束的字段，值可以为null，可以重复，但是值不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表也不能先删除
- 如何使用外键约束
use db6;
1. 创建部门表
create table dept(id int primary key auto_increment,name varchar(10));
2. 创建员工表
create table emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) references dept(id));
-格式介绍：constraint 约束名称 foreign key(外键字段名) references 依赖的表名(依赖的字段名)
- 测试插入数据
insert into dept values(null,'神仙'),(null,'妖怪');
insert into emp values(null,'悟空',1);
insert into emp values(null,'赛亚人',3);//失败
delete from dept where id=1;//失败
drop table dept; //失败
- 由于添加外键约束后 会影响测试效率，所以工作中很少使用，一般都是通过java代码实现逻辑外键。
###索引
- 什么是索引：索引是数据库中用来提高查询效率的技术，类似于目录
- 为什么使用索引：如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树桩结构保存，查询数据时会大大降低磁盘块的访问数量，从而提高查询效率。
- 有索引就一定好吗？
如果表中的数据很少，使用索引反而会降低查询效率
- 索引是越多越好吗？
不是，索引会占用磁盘空间，只针对查询时常用的字段创建索引
- 导入数据
windows系统： source d:/item_backup.sql
linux系统： source /home/soft01/桌面/item_backup.sql
- 导入完后：
show tables; 查看是否有item2 这张表
select count(*) from item2; 172万
- 测试查询耗时
select * from item2 where title='100'; //耗时1.13
####如何创建索引 title varchar(10)
- 格式： create index 索引名 on 表名(字段名(字符长度))
create index index_item_title on item2(title);
- 测试查询耗时
select * from item2 where title='100'; //耗时0.04
####索引分类
- 聚集索引：通过主键创建的索引称为聚集索引，聚集索引中保存数据，只要给表添加主键约束，则会自动创建聚集索引
- 非聚集索引：通过非主键字段创建的索引称为非聚集索引，非聚集索引中没有数据
####如何查看索引
- 格式：show index from 表名;
show index from item2;
####删除索引
- 格式： drop index 索引名 on 表名;
drop index index_item_title on item2;
####复合索引
- 通过多个字段创建的索引称为复合索引
- 格式： create index 索引名 on 表名(字段1,字段2);
create index index_item_title_price on item2(title,price);
####索引总结
1. 索引是用来提高查询效率的技术，类似目录
2. 因为索引会占用磁盘空间，所以不是越多越好
3. 因为数据量小的时候使用索引会降低查询效率所以不是有索引就一定好
4. 分类：聚集索引和非聚集索引
5. 通过多个字段创建的索引称为复合索引
###事务
- 数据库中执行同一业务多条SQL语句的工作单元，可以保证全部执行成功或全部执行失败
####事务的ACID特性
- ACID是保证数据库事务正确执行的四大基本要素
1. Atomicty：原子性，最小不可拆分，保证全部成功或全部失败
2. Consistency：一致性，保证事务从一个一致状态到另外一个一致状态
3. Isolation：隔离性，多个事务之间互不影响
4. Durablity: 持久性，事务提交之后数据保存到数据库文件中持久生效
####事务相关的SQL
1. 开启事务
begin;
2. 回滚
rollback;
3. 提交
commit;
4. 保存回滚点
savepoint s1;
5. 回滚到某个回滚点
rollback to s1;
6. 查看自动提交状态
show variables like '%autocommit%';
7. 修改自动提交状态
set autocommit=0/1;
###group_concat()
- 查询每一个部门所有员工的姓名和工资
select deptno,group_concat(ename,'-',sal) from emp group by deptno;
- 查询每个部门的员工姓名，要求每个部门只显示一行
select deptno,group_concat(ename) from emp group by deptno;
###面试题
创建学生成绩表 student(id主键,name姓名,subject学科,score成绩)
create table student(id int primary key auto_increment,name varchar(10),subject varchar(10),score int);
保存以下数据：
insert into student (name,subject,score) values
('张三', '语文', 66),('张三', '数学', 77),('张三','英语', 55),('张三', '体育', 77),('李四' ,'语文', 59),('李四', '数学', 88),('李四', '英语', 78),('李四', '体育', 95),('王五', '语文', 75),('王五', '数学', 54),('王五', '英语', 98),('王五', '体育', 88);
1. 查询每个人的平均分 从大到小排序
select name,avg(score) a from student
group by name order by a desc;
2. 查询每个人的名字 科目和成绩 一行显示出来
select name,group_concat(subject,':',score) from student
group by name;
3. 查询每个人的最高分和最低分
select name,max(score),min(score) from student group by name;
4. 查询每个人的名字 不及格的科目以及分数，不及格的科目数量 一行显示
select name,group_concat(subject,':',score),count(*) from student
where score<60
group by name;
###课程回顾
####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(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
- 查询所有表 show tables;
- 查看表详情 show create table t1;
- 查看表字段 desc t1;
- 修改表名 rename table t1 to t2;
- 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
- 添加表字段 alter table t1 add age int first/after xxx;
- 删除表字段 alter table t1 drop 字段名;
- 修改字段名和类型 alter table t1 change 原名 新名 新类型
- 修改类型和位置 alter table t1 modify 字段名 新类型 first/after xxx;
- 删除表 drop table t1
- 删除并创建 truncate table t1
3. 数据相关SQL
- 插入数据 insert into t1 (字段1，字段2，字段3) values(1,2,3),(1,2,3);
- 删除 delete from t1 where id=10;
- 修改 update t1 set age=18 where id=10;
- 查询 select * from t1;
###day02
- 主键+自增 primary key auto_increment
- 注释 comment
- `用于修饰表名和字段名 '修饰字符串
- 冗余：表设计不合理导致大量重复数据 通过拆分表解决
- SQL分类：DDL数据定义语言 包括 create alter drop truncate 不支持事务，DML数据操作语言 包括 insert update delete select(DQL) 支持事务，DQL数据查询语言 只包括select ，TCL事务控制语言 DCL数据控制语言 用于分配用户权限
- 数据类型
1. 整数：int bigint(m) m代表显示长度 结合 zerofill使用
2. 浮点数：double(m,d) m总长度 d小数长度 decimal
3. 字符串：char固定长度 最大255 varchar可变长度最大65535 text可变长度最大65535
4. 日期： date time datetime 默认null最大9999-12-31 ， timestamp 默认当前时间 最大 2038-1-19
5. 其它
###day03
- is null 和 is not null
- 别名
- 去重 distinct
- 比较运算符 > < >= <= = !=和<>
- and 和 or
- in 和 not in
- not between x and y
- 模糊查询 like _单个未知 %0或多个未知
- 排序 order by 字段名 asc/desc,字段名 asc/desc;
- 分页查询 limit 跳过的条数,每页条数
- 数值计算 + - * / % mod
- 日期相关 now() date() time() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) %YymcdHhis date_format() str_to_date()
- ifnull(x,y)
- 聚合函数 avg max min sum count
- 字符串相关 concat char_length instr locate insert upper lower left right substring trim replace repeat reverse
###day04
- 数学相关 floor() round() truncate() rand()
- 分组 group by
- having
- 子查询
1. 写在where或having后面
2. from后面
3. 创建表和创建视图的时候
- 关联查询：
1. 等值连接 select * from A,B where A.x=B.x and A.age=18
2. 内连接 select * from A join B on A.x=B.x where A.age=18
3. 外链接 select * from A left/right join B on A.x=B.x where A.age=18
- 笛卡尔积
###day05
- 关联关系
1. 1对1 在从表中添加外键指向主表中的主键
2. 1对多 在多的表中添加外键 指向另一张表的主键
3. 多对多 创建一个关系表 添加两个外键 指向另外两张表的主键
4. 自关联： 当前表外键指向当前表的主键 查询时把一张表当成两张表
- 权限管理案例： 用户表 角色表 权限表 用户角色关系表 角色权限关系表
jdbc第一天===================================================================================
##JDBC
###什么是JDBC
- Java DataBase Connectivity，Java数据库连接，jdbc实际是java中的一套和数据库交互的api（application program interface 应用程序编程接口）
####为什么使用JDBC
- 因为Java程序员需要连接多种数据库，为了避免每一个数据库都学习一套新的API，sun公司提出了一个JDBC接口，各个数据库厂商根据此接口写实现类（驱动），这样java程序员只需要掌握JDBC接口中的一套方法，就可以访问任何数据库。
####如何使用JDBC
内网访问：maven.tedu.cn 外网访问 maven.aliyun.com
1. 从maven网站上搜索mysql 找到5.1.6版本的坐标，把坐标复制到工程的pom.xml文件中 ，在 文件源码里面 project标签内部 添加<dependecies>标签，把复制的内容粘贴到里面 ，保存 在工程中出现奶瓶即可
2. 创建Demo01.java 在main方法中写以下代码：
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接对象
Connection conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/newdb3",
"root", "root");
//System.out.println(conn);
//3.创建执行SQL语句的对象
Statement stat = conn.createStatement();
//4.执行SQL
String sql = "create table if not exists jdbct1"
+ "(id int primary key auto_increment,"
+ "name varchar(10))";
stat.execute(sql);
System.out.println("执行完成");
//5.关闭资源
stat.close();
conn.close();
####执行SQL的方法
- execute(sql): 此方法可以执行任意SQL，但是推荐执行DDL(create drop alter truncate) ,方法的返回值为布尔值, 返回值代表是否有结果集
- executeUpdate(sql): 此方法执行增删改的SQL，方法返回值为int类型，代表生效行数
- executeQuery(sql):此方法执行select查询的SQL，返回值为ResultSet结果集,见到resultSet就while循环遍历
####ResultSet
- 获取数据当方式有两种：
- 通过查询到的字段名称获取数据 rs.getString("ename");
- 通过查询到的字段位置获取数据 rs.getString(2);
- 数据库类型和java类型对比：
MySQL java
int getInt()
varchar getString()
float/double getFloat()/getDouble()
datetime/timestamp getDate()
###数据库连接池DBCP
- DataBase Connection Pool:数据库连接池
- 为什么使用：如果没有数据库连接池，每次业务都需要和数据库建立一次连接，用完之后断开，如果有1万次业务请求则有1万次连接的开和关，频繁开关连接，浪费资源，使用数据库连接池可以将连接重用，避免资源的浪费
- 如何使用数据库连接池
1. 下载jar包： 从maven私服中搜索 dbcp
2. 把坐标复制粘贴到pom.xml中
3. 代码如下：
//创建数据源对象
BasicDataSource dataSource = new BasicDataSource();
//设置数据库连接信息
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/newdb3");
dataSource.setUsername("root");
dataSource.setPassword("root");
//设置初始连接数量
dataSource.setInitialSize(3);
//设置最大连接数量
dataSource.setMaxActive(5);
//从连接池中得到连接
Connection conn = dataSource.getConnection();
System.out.println(conn);
jdbc第二天===============================================================================================================================
###PreparedStatement
预编译的SQL对象
- 好处：1. 代码更直观避免了拼接SQL语句 2. 可以避免SQL注入 3. 执行效率略高于statement
- 如果SQL语句中存在变量使用PreparedStatement，如果没有变量则使用Statement
1. 为什么PreparedStatement可以避免SQL注入？
因为预编译的SQL执行对象 在创建stat对象的时候已经将SQL进行编译并且把SQL语句的逻辑固定，不会因为后期替换进去的内容改变SQL语句的逻辑
- 演示SQL注入：
create table t_user(id int primary key auto_increment,username varchar(10),password varchar(10));
insert into t_user values(null,'libai','admin'),(null,'liubei','123456');
- 登录的SQL
select count(*) from t_user where username='libai' and password='admin1';
select count(*) from t_user where username='asdfsdf' and password='' or '1'='1'
' or '1'='1
###批量操作
- 将多次和数据库服务器的数据交互合并成一次，提高执行效率
//添加到批量操作中
stat.addBatch(sql1);
stat.addBatch(sql2);
stat.addBatch(sql3);
//执行批量操作
stat.executeBatch();
name1 101 name2 102 name3 103
创建Demo09
程序一执行
提示：请输入查询的页数 3
请输入查询的条数 10
21-30
###分页查询 代码参见 Demo09.java
###事务
- 设置自动提交的状态
conn.setAutoCommit(false/true);
- 提交
conn.commit();
- 回滚
conn.rollback();
- 案例：实现超人有5000 蝙蝠侠100 让超人给蝙蝠侠转账2000，程序第一次运行转账成功 第二次转账成功 第三次转账失败
create table person(id int primary key auto_increment,name varchar(10), money int);
insert into person values(null,'超人',5000),(null,'蝙蝠侠',100);
###获取自增主键的值
- 练习：
创建球队表和球员表
create table team(id int primary key auto_increment,name varchar(10));
create table player(id int primary key auto_increment,name varchar(10),team_id int);
请输入球队名称
火箭
请输入球员名称
姚明
扩展功能：如果再次输入 火箭 麦迪
###获取元数据
- 元数据包括：数据库的元数据和表的元数据
1. 数据库元数据里面保存一些和数据库相关的信息 如：数据库厂商名称，数据库驱动版本，数据库连接地址，用户名等等
//得到数据库的元数据
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("驱动版本:"
+dbmd.getDriverVersion());
System.out.println("连接地址:"
+dbmd.getURL());
System.out.println("用户名:"
+dbmd.getUserName());
System.out.println("数据库厂商:"
+dbmd.getDatabaseProductName());
2. 表的元数据里面保存一些和表相关的信息 如：表有多少字段，每个字段的名称、类型等
//从结果集中得到表的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//得到表字段的数量
int count = rsmd.getColumnCount();
//得到每个字段的字段名和类型
for (int i = 0; i < count; i++) {
String name = rsmd.getColumnName(i+1);
String type = rsmd.getColumnTypeName(i+1);
System.out.println(name+":"+type);
}
find_in_set('5',id)
如果有什么不懂可以加入qq群讨论讨论：668535367
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
