​

数据库

1. 通过IO流操作文件的形式保存数据存在的弊端
2. 业务代码繁琐开发效率低
3. 执行效率低
4. 一般只能保存字符串
5. 一般只能保存小量数据

什么是DB

• Database：数据库，指保存数据的文件集合

什么是DBMS

• DataBaseManagementSystem：数据库管理系统(数据库软件) ，常见的数据库软件：Oracle、MySQL、SqlServer、DB2、sqlite等

数据库分类（了解）

1. 关系型数据库：经过数学理论验证可以将现实生活中存在的任何关系保存起来的数据库，以表为存储数据的单位
2. 非关系型数据库：解决一些特定的应用场景如：高并发、缓存等， 非关系型数据库存储数据并非以表为单位，如Redis数据库通过key-value形式保存数据

常见关系型数据库介绍

1. MySQL： 开源数据库 08年被Sun公司收购，09被Oracle收购，被收购后MySQL性能大幅提高，公司计划要将MySQL闭源，原MySQL程序员离开Oracle创办了MariaDB，宝贝女儿叫Maria， 市场占有率排名第一
2. Oracle：闭源数据库，性能最高，价格最贵， 拉力.艾莉森 32 市场占有率排名第二
3. SQLServer:微软公司产品，闭源数据库，应用在微软整体解决方案中
4. DB2：IBM公司产品，闭源数据库，应用在IBM的整体解决方案中
5. sqlite：轻量级数据库 只有几十k，应用在嵌入式和移动设备中

开源和闭源

1. 开源：开放源代码，免费，盈利方式：通过卖服务或等产品用户群足够大的时候改成闭源 ，会有技术大拿无偿维护和升级
2. 闭源：不公开源代码，收费，盈利方式：靠卖产品和卖服务，会有技术大拿攻击和破坏，但是闭源公司会有更多的钱养一帮程序员维护和升级

SQL

• Structured Query Language：结构化 查询 语言，用于程序员和数据库软件进行交互， sql语句执行在客户端(windows的命令行、Linux的终端或三方的可视化客户端) 或者通过java代码执行

如何连接数据库软件

1. windows系统 开始菜单-》所有程序-》MariaDB或MySQL-》MySQL Client，然后直接输入密码 学生机没有密码直接回车，如果自己电脑 密码自己想
2. Linux系统 桌面右键打开终端 执行以下命令 mysql -uroot -p 回车 输入密码 学生机无密码直接回车

退出： exit;

数据库相关的SQL

1. 查看所有数据库 show databases;
2. 创建数据库
3. 格式： create database 数据库名称; create database db1;
4. 查看数据库详情
5. 格式： show create database 数据库名称; show create database db1;
6. 创建数据库指定字符集
7. 格式： create database 数据库名称 character set utf8/gbk; create database db11 character set gbk;
8. 删除数据库
9. 格式： drop database 数据库名称; drop database db11; show databases;
10. 使用数据库
11. 格式： use 数据库名称; create database db1 character set utf8; use db1;

表相关的sql

• 操作表的sql一定要先使用数据库不然会报以下错误： ERROR 1046 (3D000): No database selected
• 创建表
• 格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型,.....); create table person(name varchar(10),age int,gender varchar(2));
• 练习：创建学生表student 有学号id，姓名name，语文chinese,数学math，应用english 这些字段 create table student(id int,name varchar(10),chinese int,math int,english int);
• 查询所有表

• 格式： show tables;

• 查看表详情

• 格式： show create table 表名; show create table student;
• 表引擎：(了解)
  1. innodb：支持数据库的高级操作如事务、外键等
  2. myisam：不支持数据库的高级操作，仅支持数据基础的增删改查操作
• 创建表时指定引擎和字符集

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

  show create table t1;

• 查看表字段信息 description描述
• 格式： desc 表名; desc person;
• 删除表
• 格式： drop table 表名; drop table t1;

修改表相关

1. 修改表名
2. 格式：rename table 原名 to 新名; rename table student to stu;
3. 修改表引擎和字符集

4. 格式：alter table 表名 engine=innodb/myisam charset=utf8/gbk; alter table stu engine=myisam charset=gbk;

   -测试 show create table stu;

5. 添加表字段
6. 最后添加格式：alter table 表名 add 字段名称 字段类型;
7. 最前面格式：alter table 表名 add 字段名称 字段类型 first;
8. 某个字段的后面：alter table 表名 add 字段名称 字段类型 after 字段名; 最后添加一个地址字段 alter table person add addr varchar(20); 最前面添加一个id字段 alter table person add id int first; 在name字段后面添加工资salary alter table person add salary int after name;
9. 删除表字段
10. 格式： alter table 表名 drop 字段名称; alter table person drop addr;
11. 修改表字段的名称和类型
12. 格式： alter table 表名 change 原字段名 新字段名 新字段类型; alter table person change salary sal int;
13. 修改表字段的类型和位置
14. 格式: alter table 表名 modify 字段名 新类型 first/after xxx; alter table person modify age int first; alter table person modify sal int after gender;

数据库相关 查看所有 show databases; 创建 create database db1 character set utf8/gbk; 查看详情 show create database db1; 删除 drop database db1; 使用 use db1; 表相关 创建 create table t1(id int,name varchar(10)) 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;

数据相关SQL

drop database db1; create database db1 character set utf8; use db1; create table person(id int,name varchar(10),age int); 1. 插入数据 - 全表插入格式： insert into 表名 values(值1,值2,值3); insert into person values(1,'Tom',8); - 指定字段插入： insert into 表名 (id,name) values(值1,值2); insert into person (id,name) values(2,'Jerry');

insert into person (name) values('张飞');

​​

• 如果插入中文报错执行以下指令 通过以下指令修改网络传输的字符集 set names gbk;

• 批量插入数据 insert into person values(10,'悟空',500),(11,'八戒',250);

• 查询数据
• 格式： select 字段信息 from 表名 where id<5; select name from person; select name,age from person where age<20; select * from person;
• 修改数据
• 格式：update 表名 set age=10 where id=2; update person set age=10 where id=2;
• 如果不写条件则修改表内所有数据
• 删除数据
• 格式：delete from 表名 where id=2; delete from person where id=2;
• 如果不写条件则删除表内所有数据

###主键约束

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

• 约束：是给表字段添加的限制条件

• 主键约束：限制主键字段的值 唯一并且非空

• 格式： create table t1(id int primary key,name varchar(10)); 测试： insert into t1 values(1,'悟空'); //成功 insert into t1 values(1,'八戒'); //失败 不能重复 insert into t1 values(null,'沙僧'); //失败 不能为null insert into t1 values(2,'玉帝哥哥');//成功 ###主键约束+自增 primary key auto_increment

• 格式： create table t2(id int primary key auto_increment,name varchar(10)); 测试： insert into t2 values(null,'悟空'); 1 insert into t2 (name) value ('八戒'); 2 insert into t2 values(10,'小龙女'); 10 insert into t2 values(null,'白龙马'); 11 delete from t2 where id>9; insert into t2 values(null,'黑熊怪'); 12 delete from t2; insert into t2 values(null,'奔波霸'); 13

• 自增数值只增不减

• 从历史最大值基础上+1 ###truncate

• 格式： truncate table t2;

• 删除表 并且创建新表

• 自增数值清零 ###注释 comment

• 格式： create table t3(id int primary key auto_increment comment '这是主键',name varchar(10) comment '这是名字'); 查看注释： show create table t3;

###数据冗余

• 如果表设计不够合理，当数据量增多时出现的大量重复数据，这种现象称为数据冗余，通过拆分表的方式解决冗余问题

• 外键： 用于建立关系的字段称为外键

• ###事务

• 事务是数据库中执行同一业务多条sql语句的工作单元 可以保证多条sql语句要么全执行成功，要么全执行失败。

• 创建用户表： create table user(id int,name varchar(10),money int,state varchar(5));

• 插入数据： insert into user values(1,'超人',50,'冻结'),(2,'蝙蝠侠',2000,'正常');

• 蝙蝠侠给超人转账200块钱 无事务保护： update user set money=money-200 where id=2 and state='正常'; update user set money=money+200 where id=1 and state='正常';

• 有事务保护： 转账失败 begin; //开启事务 update user set money=money-200 where id=2 and state='正常'; update user set money=money+200 where id=1 and state='正常'; rollback; //回滚

• 有事务保护： 转账成功 insert into user values(3,'海王',10,'正常'); -蝙蝠侠给海王转账500 begin; update user set money=money-500 where id=2 and state='正常'; update user set money=money+500 where id=3 and state='正常'; commit; //提交

• 保存回滚点 savepoint 标识; begin; update user set money=money-10 where id=2; savepoint s1; update user set money=money-50 where id=2; update user set money=money-100 where id=2; rollback to s1; ####SQL分类

#####DDL Data Definition Language 数据定义语言 一半是对表的操作

• 包括： create，drop，alter，truncate

• 不支持事务 #####DML Data Manipulation Language 数据操作语言 表中的数据

• 包括： insert，update，delete，select（DQL）

• 支持事务 select和事务没有关系 ####DQL Data Query Language 数据查询语言

• 只包括select ####TCL Transaction Control Language 事务控制语言

• 包括：begin 、 rollback、 commit、 savepoint s1 、rollback to s1 ####DCL Data Control Language 数据控制语言 数据库管理员做的事情

• 涉及分配用户权限相关的SQL

###数据库的数据类型 ####整数

• int对应java里面的int bigint对应java中的long

• int(m) m代表显示长度 需要结合zerofill使用 create table t_int(id int,age int(10) zerofill); 在前面补充0 insert into t_int values(1,18); select * from t_int; ####浮点数

• double(m,d) m代表总长度 d代表小数长度 76.234 m=5 d=3

• decimal超高精度浮点数，涉及超高精度运算时使用 ####字符串

• char(m): 不可变长度字符串 m=10 "abc" 所占长度为10 ，执行效率略高

• varchar(m):可变长度字符串 m=10 "abc" 所占长度3 好处是节省空间 最大长度为65535，但是建议长度不要超过255 超过255建议使用text

• text(m):可变长度字符串 m=10 "abc" 所占长度3 最大长度65535 ####日期

• 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-1-15',null,null,null);

  insert into t_date values(null,'16:57:38', '2018-10-22 20:18:30',null);

###day01

1. 数据库相关sql

• 查看所有 show databases;

• 创建数据库 create database db1 character set utf8/gbk;

• 查看详情 show create database db1;

• 删除数据库 drop database db1;

• 使用数据库 use db1;

1. 表相关sql

• 创建表 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;

• 查看所有表 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 age int;

• 删除表字段 alter table t1 drop age;

• 修改字段名和类型 alter table t1 change 原名 新名 新类型

• 修改类型和位置 alter table t1 modify 字段名 类型 first/after xxx;

1. 数据相关

• 插入数据：insert into t1 (字段1,字段2,字段3) values(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);

• 删除数据 delete from t1 where id=5;

• 修改数据 update t1 set age=20 where id<5;

• 查询数据 select name,age from t1 where id=10; ###day02

1. 主键约束：表示数据唯一性的字段称为主键, 唯一且非空 primary key

2. 主键+自增： 历史最大值+1 只增不减 create table t1(id int primary key auto_increment);

3. 注释 comment

4. `:用于修饰表名和字段名 可以省略，'：用于修饰字符串

5. 数据冗余： 表设计不合理导致的大量重复数据称为数据冗余，通过拆分表的形式解决

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

• 开启事务 begin

• 回滚事务 rollback

• 提交事务 commit

• 保存回滚点 savepoint xxx;

• 回滚到某个回滚点 rollback to xxx;

1. SQL分类

• DDL：数据定义语言 包括：create alter drop truncate 不支持事务

• DML：数据操纵语言 包括：insert delete update select(DQL) 支持事务

• DQL: 数据查询语言 只包括select

• TCL：事务控制语言 包括：begin rollback commit savepoint rollback to

• DCL: 分配用户权限相关sql

1. 数据类型

• 整数 、 浮点数、字符串、日期、其它

• 整数 int(m) bigint(m) m代表显示长度 需要结合zerofill使用

• 浮点数 double(m,d) m代表总长度 d代表小数长度 decimal超高精度浮点数

• 字符串 char:不可变长度 最大255 执行效率略高 varchar(m): 可变长度 最大65535 超过255建议使用text 节省空间 ， text可变长度 最大65535

• 日期： date年月日 time 时分秒 datetime年月日时分秒 最大9999-12-31 默认值为null timestamp年月日时分秒 最大2038-1-19 默认值为当前时间

​

####导入*.sql数据到数据库中

• window系统

1. 把*.sql 文件保存到 d盘的根目录

2. 在命令行中执行 source d:/tables.sql

• linux系统

• 把*.sql 文件保存到桌面

• 在命令行中执行 source /home/soft01/桌面/tables.sql

is null 和 is not null

1. 查询奖金为null的员工姓名、工资、奖金

select ename,sal,comm from emp where comm is null;

1. 查询没有上级领导mgr的员工信息(所有信息) select * from emp where mgr is null;

2. 有上级领导的员工姓名、工资和领导编号 select ename,sal,mgr from emp where mgr is not null;

3. 查询有奖金的员工信息 select * from emp where comm is not null and comm>0; ####别名 给一个列起另一个名字

4. 查询所有员工的姓名 select ename as '姓名' from emp; select ename '姓名' from emp; select ename 姓名 from emp;

5. 查询工资大于2000的员工信息要求每个字段的名称改成中文 select empno 编号,ename 姓名,job 工作,mgr 领导编号,hiredate 入职日期,sal 工资,comm 奖金,deptno 部门编号 from emp where sal>2000;

   ####去重 distinct

6. 查询员工从事的所有职位 select distinct job from emp;

7. 查询员工所属的部门编号有哪几个 select distinct deptno from emp; ####比较运算符 >,<,>=,<=,=,!=和<>

8. 查询工资小于1600的所有员工的姓名和工资 select ename,sal from emp where sal<1600;

9. 查询部门编号为20的所有员工的姓名，职位，和部门编号 select ename,job,deptno from emp where deptno=20;

10. 查询职位是manager的员工姓名，工资，职位 select ename,sal,job from emp where job='manager';

11. 查询不是10号部门的所有员工的姓名和部门编号（两种方式） select ename,deptno from emp where deptno!=10; select ename,deptno from emp where deptno<>10;

12. 查询商品表t_item中单价price 等于23的商品信息 select * from t_item where price=23;

13. 查询商品表中单价不等于8443的商品标题和价格 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;

1. 查询有上级领导并且工资低于2000的员工信息 select * from emp where mgr is not null and sal<2000;

2. 查询部门编号为30或者上级领导为7698的员工姓名、工资、部门编号、上级领导编号 select ename,sal,deptno,mgr from emp where deptno=30 or mgr=7698;

3. 查询没有上级领导或者工资小于1000的员工信息 select * from emp where mgr is null or sal<1000;

in not in

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

1. 查询员工工资为5000，800，950的员工信息 select * from emp where sal=5000 or sal=800 or sal=950; -使用in select * from emp where sal in(5000,800,950);

2. 查询员工工资不为5000，800，950的员工信息 select * from emp where sal not in(5000,800,950);

   ###between x and y 在什么什么之间 not between 1000 and 4000;

• 查询某个字段的数值在 x和y之间 包含x和y

1. 查询工资在1000到2000之间的员工信息 select * from emp where sal>=1000 and sal<=2000; select * from emp where sal between 1000 and 2000;

2. 查询商品表中，单价price在50到100之间的商品价格 select price from t_item where price between 50 and 100;

3. 查询工资在1000到4000之外的员工姓名和工资 select ename,sal from emp where sal<1000 or sal>4000; select ename,sal from emp where sal not between 1000 and 4000;

   ###模糊查询 like

• _:代表单个未知字符

• %：代表0或多个未知字符

• 举例：

  1. 以a开头 a%

  2. 以b结尾 %b

  3. 以x开头y结尾 x%y

  4. 包含abc %abc%

  5. 第二个字母是b _b%

  6. 第一个字母是m 倒数第三个是d m%d__

• 使用方式：

1. 查询名字以k开头的员工姓名和工资 select ename,sal from emp where ename like 'k%';

2. 查询员工表中，职位名称包含s的职位名称 select distinct job from emp where job like '%s%';

3. 查询员工姓名中第三个字母是n的员工信息 select * from emp where ename like '__n%';

4. 查询名字倒数第二个字母是e的员工姓名 select ename from emp where ename like '%e_';

5. 查询商品表中标题包含记事本的商品信息 select title from t_item where title like '%记事本%';

6. 查询有赠品的dell商品(有赠品：卖点字段中包含赠字, dell商品：标题字段中包含dell) select * from t_item where sell_point like '%赠%' and title like '%dell%'; . 查询价格在50到200之间的得力商品 select * from t_item where price between 50 and 200 and title like '%得力%';

7. 查询有图片的得力商品 (有图片：图片字段不为null) select * from t_item where image is not null and title like '%得力%';

8. 查询商品标题中不包含得力的商品信息 select * from t_item where title not like '%得力%';

9. 查询商品分类为238，917并且商品标题中不包含广博的商品信息 select * from t_item where category_id in (238,917) and title not like '%广博%';

   ###排序 order by 字段名 注意 不是查询条件 这个是排序 不需要where

• 字段名后面 什么都不加默认为升序 添加 desc为降序 asc升序

1. 查询所有员工姓名 按照名字排序 select ename from emp order by ename;

2. 查询所有员工的姓名，工资 按照工资进行升序排序 select ename,sal from emp order by sal ;

3. 查询所有员工的姓名，工资 按照工资进行降序排序 select ename,sal from emp order by sal desc;

4. 查询员工表中 有上级领导并且名字中包含a的员工 姓名，工资，上级领导编号，按照工资降序排序

select ename,sal,mgr from emp where mgr is not null and ename like '%a%' order by sal desc;

• 多字段排序，如果需要多个字段排序则在order by 后面写多个字段名通过逗号分隔即可

1. 查询所有员工的姓名，工资，部门编号 按照部门编号升序排序,如果部门一致则按照工资降序排序 select ename,sal,deptno from emp order by deptno desc,sal desc;

   ####分页查询

• 格式: limit 跳过的条数,请求的条数/每页的条数 (n-1)*m,m

• （想查询第几页 - 1 ）* 每页的条数 ，每页的条数

• 20 5 4 6 7 8 9 10

• 请求前20条 limit 0,20

• 请求第三页每页8条 limit 16,8 //(3-1)8,8 (n-1)m,m 0-8 9-17 18 25 （3-1）*8，8

• 请求第5页每页4条 limit 16,4 //(5-1)*4,4

1. 查询商品表中的前20条数据 select * from t_item limit 0,20;

2. 查询员工表第三页每页两条数据 limit select * from empt limit 4,2;

3. 查询工资前三名的员工姓名和工资 select ename,sal from emp order by sal desc limit 0,3;

4. 查询奖金最高员工信息

select * from emp order by comm desc limit 0,1;

###数值计算 + - * / 7%2 mod(7,2)

1. 查询每个员工的姓名，工资，年终奖(年终奖=工资5) select ename,sal,sal5 年终奖 from emp;

2. 查询商品表中商品单价，库存和总金额(单价库存) select price,num,pricenum 总金额 from t_item;

3. 给20号部门的员工涨薪10块钱 update emp set sal=sal+10 where deptno=20;

   ###日期相关的函数

• sql的helloWord select 'helloword';

1. 获取当前的日期+时间 now() select now();

2. 获取当前的年月日 curdate(); current当前 select curdate();

3. 获取当前的时间 curtime(); select curtime();

4. 从完整年月日时分秒中提取年月日 和提取时分秒 select date(now()),time(now());

5. 从完整年月日时分秒中提取时间分量 extract() 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 ename,extract(year from hiredate) year from emp;

1. 日期格式化 date_format(日期,格式)

• format：

• %Y 四位年 %y 两位年

• %m 两位月 %c 一位月

• %d 日

• %H 24小时 %h 12小时

• %i 分

• %s 秒

• 练习： 把now()转换成 当前 的YYYY mm d HH i s select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');

1. 把自定义时间格式转回标准格式 str_to_date(自定义的字符串时间,格式)

• 练习： 14.08.2008 08:00:00

select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');

###ifnull(x,y)函数

• age=ifnull(x,y) 如果x的值为null 则age=y 如果不为null则age=x

1. 修改emp表中奖金为null的改为0，不为null则不变 update emp set comm=ifnull(comm,0);

   ###聚合函数

• 对多行数据进行统计，可以统计平均值，最大值，最小值，求和，统计数量

1. 平均值 avg(字段名)

• 统计20号部门的平均工资 select avg(sal) from emp where deptno=20;

• 查询得力商品的平均单价 select avg(price) from t_item where title like '%得力%';

1. 最大值 max(字段名)

• 统计所有员工中的最高奖金 select max(comm) from emp;

• 查询30号部门的最高工资 select max(sal) from emp where deptno=30;

1. 最小值 min(字段名)

• 统计10号部门的最低工资 select min(sal) from emp where deptno=10;

• 查询商品表中的最低商品单价 select min(price) from t_item;

1. 求和 sum(字段名)

• 统计20号部门每个月共发多少工资 select sum(sal) from emp where deptno=20;

• 统计员工表中所有员工的奖金总和 select sum(comm) from emp;

1. 统计数量 count(字段名)

• 统计emp表中有多少员工 select count(*) from emp;

• 查询30号部门工资大于1500的员工数量

• select count(*) from emp where deptno=30 and sal>1500;

###字符串相关的函数

1. 字符串拼接 concat(s1,s2) s1s2

• 案例：查询员工姓名和工资 要求在工资的后面添加单位元 select ename,concat(sal,'元') from emp;

1. 获取字符串的长度 char_length(str) select char_length('abc');

• 案例： 查询每个员工的姓名和员工姓名的长度 select ename,char_length(ename) from emp;

1. 获取字符串在另外一个字符串中出现的位置 注意参数位置不同用的函数不同 -格式一： instr(str,substr) select instr('abcdefg','d'); -格式二： locate(substr,str); select locate('d','abcdefg');

2. 插入字符串 insert(str,start,length,newstr) length表示替代元数组的个数 select insert('abcdefg',3,2,'m');

3. 转大写 转小写 select upper('nba'),lower('ABC');

4. 去两端空白 trim select trim(' a b ');

5. 截取字符串

   • 从左边截取 select left('abcdefg',2);

   • 从右边截取 select right('abcdefg',2);

   • 任意位置截取 select substring('abcdefg',2); //从2截取到最后 select substring('abcdefg',2,3);//从2开始 长度为3

6. 重复 repeat(str,count) select repeat('ab',2);

7. 替换 replace(str,old,new) select replace('abcefg','e','mm');

8. 反转 reverse(str) select reverse('abc');

###数学相关的函数

1. 向下取整 floor(num) select floor(3.14);

2. 四舍五入 round(num) select round(23.8);

• round(num,m) select round(23.879,2);

1. 非四舍五入 truncate(num,m) select truncate(23.879,2);

2. 随机数 rand() 0-1 select rand();

• 获取0-5的整数随机数 select floor(rand()*6);

• 3-5的随机数

###分组查询 group by

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

2. 查询每个部门的工资总和 select deptno,sum(sal) from emp group by deptno;

3. 查询每种职业的最高工资 select job,max(sal) from emp group by job;

4. 查询每个领导下的人数 select mgr,count(*) from emp where mgr is not null group by mgr;

5. 查询每个部门工资大于1000的员工数量

1. select count(*) from emp group by deptno where sal>1000

select deptno,count(*) from emp where sal>1000 group by deptno;

• 多字段分组查询 只需要在group by后面写多个字段名通过逗号分隔

1. 每个部门每个主管的手下人数

select count(*) from emp group by deptno,mgr;

select deptno,mgr,count(*) from emp where mgr is not nullgroup by deptno,mgr;

53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。

select deptno,count(*) ,sum(sal) from emp group by deptno; *

select deptno,count(),sum(sal) from emp group by deptno order by count(*) asc,sum(sal) desc;

-别名写法 select deptno,count(*) c,sum(sal) s from emp group by deptno order by c,s desc;

54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。

select avg(sal) av,min(sal),max(sal),deptno from emp where sal between 1000 and 3000 group by deptno order by av;

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

where 前面不可以有group by having前面可以有 group by 但是 having 后面不可以有group by

group by 在 where 后面

group by 在having 前面

where--group by--having

• where后面只能写普通字段的条件 不能写聚合函数的条件 where sum(sal)>1000

• having和where类似都是用于添加条件的，having后面可以写普通字段的条件也可以写聚合函数的条件，但是建议写聚合函数的条件，而且要结合group by 使用

1. 查询每个部门的平均工资，要求平均工资大于2000

select avg(sal) a from emp where a>2000 group by deptno;

​ select avg(sal) a from emp group by deptno where a>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;

1. 查询商品表中每个分类的平均单价，要求平均单价小于100

select category_id,avg(price) a from t_item group by category_id having a<100;

1. 查询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;

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

2. 查询emp表中不是以s开头的职位，每个职位的名字，人数，工资总和，最高工资，过滤掉平均工资是3000的职位，根据人数升序，如果人数一致则根据工资总和降序

select job, count(*) c,sum(sal) s,max(sal) from emp where job not like 's%' having avg(sal) !=3000 order by c,s desc;

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;

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

2. 查询最高平均工资的部门编号（扩展） （并列第一的问题不能解决） select deptno from emp group by deptno order by avg(sal) desc limit 0,1;

   ###子查询（嵌套查询）

• 可以在查询语句中嵌套另一条sql语句

• 可以嵌套n层

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

2. 查询emp表中工资大于平均工资的员工信息 select * from emp where sal>(select avg(sal) from emp); select avg(sal) from emp; select * from emp where sal>(select avg(sal) from emp);

3. 查询工资高于20号部门最高工资的员工信息

   select * from emp where sal>(select max(sal) from emp where deptno=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);

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

2. 查询员工king所属的部门编号和部门名称（需要用到部门表dept）

select dname from dept where deptno=(select deptno from emp where ename='king');

select deptno from emp where ename='king'; select deptno,dname from dept where deptno=(select deptno from emp where ename='king');

1. 查询有员工的部门信息(查询在员工表中出现的部门的信息) 核心查询 所有的dept表里面的内容 -得到员工表中的部门编号

1select deptno from emp group by deptno;

2select distinct deptno from emp; -查询上面结果对应的部门详情

1select * from dept where deptno in (select deptno from emp group by deptno);

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

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

-查询最高的平均工资

select avg(sal) from emp group by deptno;

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 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 (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. 嵌套在sql语句中的查询语句称为子查询

2. 子查询可以嵌套n层

3. 子查询可以写在什么位置？

• 写在where和having的后面 当做查询条件的值

• 写在创建表的时候 -格式： create table 表名 as (子查询) create table newemp as (select * from emp where deptno=10);

• 写在from后面当成一个虚拟表 必须有别名 select ename from (select * from emp where deptno=10) newtable;

  ###关联查询

• 同时查询多张表的查询方式称为关联查询 n张表进行关联的时候至少需要 多少个连接条件？n-1个条件

1. 查询每一个员工的姓名和其对应的部门名称

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

1. 查询在new york工作的所有员工信息

   select deptno from dept where loc='new york';

   select * from emp where deptno=(select deptno from dept where loc='new york');

2. select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='new york';

select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';

####笛卡尔积

• 关联查询必须写关联关系，如果不写则得到两张表的乘积，这个乘积称为笛卡尔积

• 工作中不允许出现因为 超级耗内存 有可能直接崩溃

###等值连接和内连接

• 等值连接和内连接都是关联查询的查询方式

• 等值连接和内连接查询到的结果一样，都为两张表的交集数据

• 等值连接： select * from A,B where A.x=B.x and A.age=18;

• 内连接：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;

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

1. 查询在new york工作的所有员工信息 select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';

   ####外链接 右连接 会显示所有的副表信息 左连接 会显示所有主表的所有信息

• 格式：select * from A left/right join B on A.x=B.x where A.age=18;

• select * from a join b on a.x=b.x where a.age=18;

1. 查询部门表的全部名称和对应的员工姓名 select d.dname,e.ename from emp e right join dept d on e.deptno=d.deptno;

2. select d.dname,e.ename from dept d left join emp e on e.deptno=d.deptno;

####关联查询总结：

1. 关联查询的查询方式包括三种： 等值连接、内连接和外链接

2. 如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接

3. 如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接只需要掌握一种即可

###课程回顾：

1. 数学相关：

• 向下取整 floor()

• 四舍五入 round()

• 非四舍五入 turncate()

• 随机数 rand()

1. 分组查询 group by

2. having 和group by结合使用 having后面写聚合函数的条件 where写普通字段的条件

3. 子查询：写在sql语句中的查询语句称为子查询

• 子查询可以写在的位置：

  1. 在where和having的后面 当做查询条件的值

  2. 写在创建表的时候 把结果保存到一张新表中

  3. 写在from后面当成一张虚拟表 要起别名

1. 关联查询

• 查询方式：等值连接、内连接、外链接

• 等值连接： select * from A,B where A.x=B.x and A.age=18;

• 内连接：select * from A join B on A.x=B.x where A.age=18;

• 外连接：select * from A left/right join B on A.x=B.x where A.age=18;

• 等值连接和内连接查询的都是交集数据

• 外链接查询的是一张表的全部数据和另外一张表的交集数据

1. ###表设计之关联关系

在创建表时设置外键约束（一张表中的 字段是另一张表中的主键 我们就称这个字段是外键）

在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…] 例 1 为了展现表与表之间的外键关系，本例在 test_db 数据库中创建一个部门表 tb_dept1，表结构如下表所示。

字段名称 数据类型 备注 id INT(11) 部门编号 name VARCHAR(22) 部门名称 location VARCHAR(22) 部门位置

创建 tb_dept1 的 SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_dept1 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(22) NOT NULL, -> location VARCHAR(50) -> ); Query OK, 0 rows affected (0.37 sec)

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp6 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(25), -> deptId INT(11), -> salary FLOAT, -> CONSTRAINT fk_emp_dept1 -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id) -> ); Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp6; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | deptId | int(11) | YES | MUL | NULL | | | salary | float | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (1.33 sec)

以上语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can't create table”错误。

在修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

####一对一

• 什么是一对一：有A和B两张表，A表中的一条数据对应B表中的一条数据，同时B表的一条数据也对应A表的一条，称为一对一的关系。

• 应用场景： 用户表和用户信息扩展表 ，商品表和商品详情表

• 如何建立一对一的关系：在从表中添加外键指向主表的主键建立关系

• 练习：创建用户表和用户详情表并保存以下信息 user:id,username,password userinfo:user_id,nick,qq

• 创建表 create table user(id int primary key auto_increment,username varchar(10),password varchar(10)); create table userinfo(user_id int,nick varchar(10),qq varchar(15)); 用户名 密码 昵称 qq libai admin 小白白 112233 liubei 123456 刘皇叔 667788 diaochan 112233 媳妇儿 998877 -插入数据： insert into user (username,password) values('libai','admin'),('liubei','123456'),('diaochan','112233'); insert into userinfo values(1,'小白白','112233'),(2,'刘皇叔','667788'),(3,'媳妇儿','998877');

1. 查询每个用户的用户名，昵称和qq

select u.username,ui.nick,ui.qq from user u join userinfo ui on u.id=ui.user_id;

1. 查询小白白的用户名和密码 select u.username,u.password from user u join userinfo ui on u.id=ui.user_id where ui.nick='小白白';

2. 查询diaochan的所有信息 select * from user u join userinfo ui on u.id=ui.user_id where u.username='diaochan';

   ####一对多 emp dept

• 什么是一对多：有AB两张表：A表中的一条数据对应B表的多条数据，同时B表的一条数据对应A表的一条

• 应用场景：商品表 和 分类表 员工表和部门表

• 如何建立关系： 在两张表中多的表中添加外键指向另外一张表的主键

• 练习： 创建数据库db5并使用，创建员工表和部门表并保存以下数据 emp:id name dept_id dept:id name

• 创建表： create database db5; use db5; create table emp(id int primary key auto_increment,name varchar(10),dept_id int); create table dept(id int primary key auto_increment,name varchar(10));

  神仙部的员工 悟空和八戒 妖怪部的员工 蜘蛛精和白骨精

• 插入数据： insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1),(null,'八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);

1. 查询每个部门对应的员工姓名 select d.name,e.name from emp e join dept d on e.dept_id=d.id;

2. 查询八戒的部门名称 select d.name from emp e join dept d on e.dept_id=d.id where e.name='八戒';

3. 查询妖怪部有谁 select e.name from emp e join dept d on e.dept_id=d.id where d.name='妖怪';

   ####多对多

• 什么是多对多：AB两张表，A表中一条数据对应B表多条，同时B表中一条数据对应A表多条称为多对多

• 应用场景： 老师表和学生表 创建中间表

• 如何建立关系：创建一张关系表，在关系表中添加两个外键指向另外两张表的主键（中间表）

• 练习：创建老师和学生表保存以下信息

• 创建表：teacher：id name student：id name t_s:tid,sid create table teacher(id int primary key auto_increment,name varchar(10)); create table student(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 t.name,s.name from student s,t_s ts,teacher t where s.id=ts.tid and s.id=ts.sid;

select t.name,s.name from teacher t join t_s ts on t.id=ts.tid

join student s on s.id=ts.sid;

1. 查询苍老师的学生姓名 select s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.name='苍老师';

2. 查询小明的老师都有谁 n-1 select t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid 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,'孙老师'); insert into role values(null,'男游客'),(null,'男管理员'),(null,'女会员'),(null,'女管理员'); insert into module values(null,'男浏览'),(null,'男发帖'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖'),(null,'女删帖');

用户和角色关系：苍老师（男管理员，女管理员）小明（女会员）孙老师（女管理员，男游客）

insert into u_r values(1,2),(1,4),(2,3),(3,4)(3,1);

角色和权限关系：男游客（男浏览）男管理员（男浏览，男发帖，男删帖）女会员（女浏览，女发帖），女管理员（女浏览，女发帖，女删帖）

insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(3,5),(4,4),(4,5),(4,6)

1. 查询每个用户名称和拥有的权限名称

分析过程 select * from user u,u_r ur where ur.uid=u.id;

select u.name,m.name from user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join module m on rm.mid=m.id;

1. 查询苍老师的权限有哪些 select u.name,m.name from user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join module m on rm.mid=m.id where u.name='苍老师';

1. ###视图

• 什么是视图： 数据库中表和视图都是其内部的对象， 视图可以理解成是一张虚拟的表，视图本质就是取代了一段sql查询语句

• 为什么使用视图：因为有些数据的查询需要书写大量的sql语句， 每次书写开发效率太低，使用视图可以重用sql语句，可以隐藏敏感信息

• 创建视图的格式： create view 视图名 as (子查询); -创建视图 create view v_emp_10 as (select * from emp where deptno=10); -查询视图内的数据 select * from v_emp_10;

• 练习： 创建30号部门工资大于2000的视图 v_emp_30 create view v_emp_30 as (select * from emp where deptno=30 and sal>2000);

• 创建一个没有工资的员工表视图 create view v_emp_nosal as (select empno,ename,job,comm,hiredate,deptno from emp);

  select * from v_emp_10

  #####视图分类

1. 简单视图：创建视图的子查询中不包含去重、分组查询、聚合函数、 关联查询的视图称为简单视图,可以对视图中的数据进行增删改查操作

2. 复杂视图：和简单视图相反，只能进行查询

• 创建显示每个部门工资总和，平均工资、最大工资、最小工资的复杂视图： create view v_emp_info as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno); #####对简单视图进行增删改操作，操作方式和table一样

1. 插入数据 insert into v_emp_10(empno,ename) values(10011,'Tom'); insert into v_emp_10(empno,ename,deptno) values(10012,'Jerry',20);

   • 以上两行都为数据污染

• 如果插入一条数据在原表中显示但是在视图中不显示，则称为数据污染

• 数据污染可以通过 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,'刘德华',30); //插入数据失败 insert into v_emp_20 (empno,ename,deptno) values(10013,'刘德华',20); //插入数据成功！ -删除和修改数据 只能操作视图中存在的数据 修改删除都会改变原边 前提是 试图能修改或者删除成功 delete from v_emp_20 where deptno=10; //没有数据被删除 update v_emp_20 set sal=666 where deptno=10;//没有数据被修改 ####修改视图

• 创建或替换，如果不存在则创建，如果存在则替换 create or replace view v_emp_20 as (select * from emp where deptno=20 and sal>2000);

  ####删除视图

• 格式： drop view 视图名; drop view v_emp_20;

  ####视图别名

• 如果创建视图时的子查询使用了别名则视图操作时只能使用别名 create view v_emp_20 as (select ename name,sal from emp where deptno=20); select ename from v_emp_20;//报错不认识ename select name from v_emp_20; //执行成功

  ####视图总结：

1. 视图是数据库中的对象，代表一段sql语句，可以理解成是一张虚拟的表

2. 作用：重用sql、隐藏敏感信息

3. 分类：简单视图（不包含，去重、聚合函数、分组、关联查询 可以对数据进行增删改操作）和复杂视图（和简单视图相反，只能查询）

4. 插入数据时可能会出现数据污染问题，通过with check option解决

5. 删除和修改只能操作视图中存在的数据

6. 起了别名后只能用别名

###约束

• 什么是约束：约束就是给表字段添加的限制条件

  ####主键约束 primary key

• 唯一且非空

  ####非空约束 not null

• 字段的值不能为null

• 格式： create table t1(id int,age int not null); -测试： insert into t1 values(1,20);//成功 insert into t1 values(2,null);//失败

  ####唯一约束 unique

• 字段的值不能重复

• 格式： create table t2(id int,age int unique); -测试： insert into t2 values(1,20);//成功 insert into t2 values(2,20);//报错

  ####默认约束 default

• 给字段设置默认值 不插入值的时候生效 插入值的时候不生效

• 格式： create table t3(id int,age int default 25); -测试： insert into t3 (id) values(1); //触发默认值生效 insert into t3 values(2,null);//age值为null insert into t3 values(3,100); //age=100

  ####外键约束

• 外键：用来建立关系的字段称为外键

• 外键约束：保证数据的完整性，外键字段的值可以null，可以重复，但是不能是关系表中不存在的数据，被依赖的数据不能先删除，被依赖的表也不能被先删除

• 格式： 先创建被依赖的部门表 create table t_dept(id int primary key auto_increment,name varchar(10)); create table t_emp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) references t_dept(id)); -格式介绍：constraint 约束名称 foreign key(外键字段) references 表名(字段名) 外键放在整个创建语句的最后

  • 部门表插入数据 insert into t_dept values(null,'神仙'),(null,'妖怪'); insert into t_emp values(null,'悟空',1);//成功 insert into t_emp values(null,'八戒',1);//成功 insert into t_emp values(null,'刘德华',3);//失败 insert into t_emp values(null,'刘德华',null);//成功 delete from t_dept where id=1; //删除失败 drop table t_dept; //表删除失败 delete from t_emp where dept_id=1;//删除神仙部的员工 delete from t_dept where id=1;//再次删除神仙部 删除成功

    ###索引

• 什么是索引：索引是数据库中提高查询效率的技术，类似于字典的目录

• 为什么使用索引：如果不使用索引数据会零散的保存在每一个磁盘块中，要想找到数据需要挨个遍历每一个磁盘块，是用索引后，磁盘块会以树桩结构保存，查询数据时能够大大降低磁盘块的访问量从而提高查询效率

• 有索引就一定好吗？ 不是，因为索引会占存储空间，如果数据量小的话添加索引反而会降低查询效率

• 索引是越多越好吗？ 不是，因为索引会占存储空间，只针对常用的查询字段创建索引，否则就浪费存储空间

• 导入数据：

1. window系统 把文件解压出来后保存到 d盘根目录 source d:/item_backup.sql;

2. linux系统 把文件保存到 桌面 source /home/soft01/桌面/item_backup.sql;

• 导入完成后测试： show tables; 查看是否有item2; select count(*) from item2; 看是否有172万条数据

  select * from item2 where title='100'; //耗时1.16

  #####创建索引

• 格式： create index 索引名 on 表名(字段名(字符长度)); create index i_item_title on item2(title);

  select * from item2 where title='100'; //耗时0.02

  ####查看索引

• show index from 表名; show index from item2;

• 给表添加主键约束会自动根据主键字段创建索引

  ####删除索引

• 格式：drop index 索引名 on 表名; drop index i_item_title on item2;

  select * from item2 where title='100'; //耗时1.14

  ####复合索引 多个字段加索引

• 通过多个字段创建的索引称为复合索引

• 格式：create index 索引名 on 表名(字段1,字段2); create index i_item_title_price on item2(title,price);

• 查询数据时频繁使用多个字段作为查询条件时，使用复合索引

####索引总结：

1. 索引是数据库中用来提高查询效率的技术，类似于目录

2. 因为索引会占用磁盘空间不是越多越好，只针对常用的查询字段创建索引

3. 数据量小的表如果添加索引会降低查询效率，所以不是有索引就一定好

###事务

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

• 事务的ACID特性 ：是保证事务正确执行的四大基本要素

1. 原子性：Atomicity，最小不可拆分，保证全部成功或全部失败

2. 一致性：Consistency，从一个一致状态到另一个一致状态

3. 隔离性：Isolation， 多个事务之间互不影响

4. 持久性：Durability，事务提交后数据保存到数据库文件中持久生效

• 事务相关指令：

• 开启事务 begin

• 回滚事务 rollback

• 提交事务 commit

• 设置回滚点 savepoint xxx

• 回滚到某个回滚点 rollback to xxx;

• 查看自动提交状态： show variables like '%autocommit%';

• 开启事务的第二种方式： 关闭自动提交 改成手动提交 作用和begin类似 set autocommit=0/1;

###group_concat() 组连接

• 凡是在需求中提到 显示到一行(条) 时 都使用此关键字

• 案例1：查询每个部门的员工姓名，部门的员工姓名显示到一条数据中 select deptno,group_concat(ename) from emp group by deptno;

• 案例2：查询每个部门的员工姓名和对应的工资，要求每个部门的信息显示到一条数据内 select deptno,group_concat(ename,'-',sal) from emp group by deptno; ###面试题

• 创建学生成绩表student,id主键 name姓名 subject学科 score分数 create table student(id int primary key auto_increment,name varchar(10),subject varchar(5),score int);

• 保存以下12条数据： 张三 语文 66，张三 数学 77，张三 英语 55，张三 体育 77， 李四 语文 59，李四 数学 88，李四 英语 78，李四 体育 95， 王五 语文 75，王五 数学 54，王五 英语 98，王五 体育 88 insert into student (name,subject,score) values ('张三','语文',66),('张三','数学',66), ('张三','英语',66),('张三','体育',66), ('李四','语文',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;