2018年2月20日星期二
Mysql数据库
l ##MySql01
数据库简介
1. **数据库简介**
之前使用的流操作把数据存储到txt文档上,对于修改/插入等,首先代码繁琐,对于查询操作效率极低,另外,如果数据很大,不可能长期保存在文件中.
为了解决上述问题,我们需要使用数据库
- DB:文件仓库
- DBMS:数据库的管理软件
- 数据库的分类
- 关系型数据库
- 用"表"保存数据,相关数据存入一张表中
- 非关系数据库
- 键值数据库
- 对象数据库
l **** 将sql语句导入到数据库 ****
首先:创建名为tedu_store的数据库
其次:source D:/tedu_store.sql 回车
3. **SQL基本操作**
SQL语言是操作数据库的语言
每一种数据都有自己独特的方言
linux 打开终端
输入
mysql -u root -p回车
password:(有密码就输入密码回车,没密码就直接回车)
- **操作数据库**
- **对数据库的操作**
1. 显示所有的数据
- show databases;
2. 选定要操作的数据库
- use 库名;
3. 创建新的数据库
- create database 库名;
4. 查看创建数据库的语句
- show create database 库名;
5. 设置默认解码的创建库语句
- create database demo default character set utf8;
6. 删除数据库
- drop database 库名;
drop database demo;
7. 需求,创建库demo1,要求默认编码utf8,创建完成后,选定这个库demo1.
8. 显示所有表
- show tables;
9. 查询表结构
- desc 表名;
- **创建表**
- create table 表名(
列名 数据类型(长度) 约束,
列名 数据类型(长度) 约束,
...
列名 数据类型(长度) 约束
);
create table stu(
id int not null,
name varchar(10),
sex varchar(10)
);
- **查看表结构**
desc 表名;
CREATE TABLE `stu` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
**mysql的引擎有两种**ENGINE=
- InnoDB
- 支持数据库高级功能
- 事务
- 外键
- myisam
- 数据存储基本功能
- 效率非常高
-----
-- 课堂练习
create table user(
name varchar(16),
age int
)engine=innodb charset=gbk;
- **对已经创建好的表进行修改**
1. 修改表名
- rename table 旧表名 to 新表名
- rename table user to tb_user;
2. 修改表属性(引擎,字符编码)
- alter table 表名
engine=myisam charset=utf8;
- alter table tb_user
engine=myisam charset=utf8;
3. 添加字段
- alter table 表名 add 新字段 新字段的数据类型 新字段的约束 first;
- alter table tb_user add id int first;
- alter table tb_user add(
gender char(5),
tel char(11)
);
4. 修改字段名称
- alter table 表名 change 旧字段名
新字段名 新字段数据类型;
- alter table tb_user change gender sex varchar(10);
5. 改数据类型
- 删除某一个字段,再添加一个新的
- 使用命令关键字直接修改
- alter table 表名 modify 字段名 字段新类型
- alter table tb_user modify tel varchar(11);
6. 修改列的顺序
- alter table 表名 modify 目标列名 目标列数据类型 after 某一列列名
- alter table tb_user modify tel varchar(11) after age;
- 小练习,把sex 放到age字段的前面
- alter table tb_user modify sex varchar(10) after name;
7. 删除列
- alter table 表名 drop 列名
- alter table tb_user drop sex;
- **删除表**
- drop table 表名;
- DROP TABLE TB_USER;
- **课堂练习1**
-- 创建员工表emp
create table emp( //员工表
empno int(4), //工号
ename varchar(10),//姓名
job varchar(10), //工种
mgr int(4), //上级领导的工号
hiredate date, //入职时间
sal double(7,2), //工资
comm double(7,2), //奖金
deptno int(4) //所属部门编号
);
create table dept( //部门表
deptno int(4), //部门编号
dname varchar(14),//部门名称
loc varchar(13) //部门办公所在地
);
## DML--对表中数据做操作的语句
### INSERT语句
- insert into 表名 (列名1,列名2,...) values (值1,值2,...);
insert into emp(empno,ename,job,sal) values (1001,'lily','programmer',5500);
insert into emp(empno,ename,job,sal) values (1001,"lily","programmer",5500);
insert into emp(empno,ename,hiredate) values (1002,'simth','18-01-24');
### UPDATE语句
- update 表名 set 字段名=值 where 列名=值;
- update emp set ename='lucy' where empno=1001;
- 1.把叫simth的员工的入职时间改为17年10月12日
update emp set hiredate="17-10-12" where ename="simth";
- 2.插入新员工lilei,工号1003,工资3000,奖金5000,上级领导编号1002
insert into emp(empno,ename,sal,comm,mgr) values(1003,'lilei',3000,5000,1002);
- 3.插入新员工zhangsanfeng,工号1004,上级领导编号1001,工资8000,奖金1000
- 修改字段类型
alter table emp modify ename varchar(20);
insert into emp(empno,ename,mgr,sal,comm) values(1004,'zhangsanfeng',1001,8000,1000);
- 4.插入新员工liuchuanfeng,工号1005,上级领导1004,入职时间18年01月22日,工资800,奖金2000
- 5.修改zhangsanfeng的工资,修改为8500
- 6.修改lilei的奖金,修改为2000
- 7.修改liuchuanfeng的上级领导为1001,工资为3000
update emp set mgr=1001,sal=3000 where ename='liuchuanfeng'
### DELETE语句
- delete from 表名 where 条件
- delete from emp where job is null;
### select * from 表名
## DDL--是对表本身操作的语句
### TRUNCATE语句
- truncate删除表内容的原理
先把整张表删除,然后重新创建一个表结构一模一样的空表
truncate table 表名
truncate table emp;
### DDL不支持事务,运行后马上执行,不能回滚
### DML支持事务
#### 作业
1.把上课所有的代码重新敲一遍
### 练习1：
1. 创建员工表emp
2.创建部门表dept;
### 练习2：
1. 给dept表插入4条数据
10,'ACCOUNTING','NEW YORK' --财务部 纽约
20,'RESEARCH','DALLAS' --研究部 达拉斯市
30,'SALES','CHICAGO' --销售部 芝加哥
40,'OPERATIONS','BOSTON' --运营部 波士顿
### 练习3：
1. 创建 mydb 数据库,使用 utf8 编码
2. 创建 t_item 商品表
3. 在 t_item 表插入商品数据
7,'苹果',iphone X 9999 now()
4. 修改 id 是7的商品，修改库存量为20
5. 删除商品 7
insert into emp values (1001,'hanmeimei','sal',1001,now(),3000,5000);
##MySql
- 显示所有数据
show databases;
- 创建新数据库,设置编码方式utf8
create database demo2 default charset utf8;
- 显示创建数据的语句
show create database demo2;
- 删除数据库
drop database demo2;
- 选择使用指定的数据库
use demo1;
- 查看库中所有表
show tables;
- 创建表
create table book(
bid int(4) primary key comment '书id',
bname varchar(50) comment '书名',
pub varchar(50) comment '出版社',
author varchar(50) comment '作者'
)engine=myisam charset=utf8;
**所有字段名,使用``,所有的字符串使用''或者""**
- 查看建表语句
show create table book;
- 查看表结构
desc book;
- 修改表名
rename table book to book1;
- 修改表属性 ,引擎和字符集
alter table book1 engine=innodb charset=utf8;
- 添加字段 first after
alter table book1 add(
type varchar(20) comment '类型',
numinput int(10) comment '进货量',
numstore int(10) comment '库存量'
);
- 修改字段名 bid bno
alter table book1 change bid bno int(4);
- 修改顺序 pub 放到author后面
alter table book1 modify pub varchar(50) after author;
- 修改数据类型 bno int(4) -->int(10)
alter table book1 modify bno int(10);
- 删除字段
alter table book1 drop 字段名;
- 删除表
drop table 表名;
- 插入语句
insert into book1(bno,bname,author,type) values(1001,'斗破苍穹','天蚕土豆','玄幻');
insert into book1(bno,bname,author,type)
values(1002,'全职高手','蝴蝶兰','网游竞技');
insert into book1(bno,bname,author,type)
values(1003,'鬼吹灯','天下霸唱','恐怖');
insert into book1(bno,bname,author,type)
values(1004,'西游记','吴承恩','4大名著');
insert into book1(bno,bname,author,type)
values(1005,'java基础','王克晶','达内学习手册');
- update语句
- 把1005号书,修改成'天线宝宝',作者不详,类型少儿
- 把1004号书修改成'天龙八部',作者金庸,类型武侠
update book1 set bname="天线宝宝",author="作者不详",type="少儿" where bno=1005;
- 删除类型是'恐怖'的所有书籍
- 删除全表记录
- 删除表格
1. 修改book名称为book_item
rename table book to book_item;
2. 在表格尾部添加字段price double(7,2)
alter table book_item add price double(7,2);
3. 把price字段的位置放到author之后
alter table book_item modify price double(7,2) after author;
4. 把表中存在的数据添加价格,每本书都在100~1000之间,自定
update book_item set price=199 where bno=1001;
5. 修改1001的价格为500元
6. 把所有字段的null字段补全
update book_item set pub="达内出版社",numinput=500,numstore=100 where pub is null;
7. 删除价格小于150的所有条目
8. 删除所有数据
### SQL分类
- 数据定义语言 DDL 重点
- 数据操纵语言 DML 重点 增 删 改
- 数据查询语言 DQL select 查
- 事务控制语言 TCL
- 数据库控制语言 DCL
### 数据定义语言 DDL
- 负责数据结构定义,与创建数据库对象的语言
- 常用create alter drop
- DDL不支持事务,DDL语句执行之后,不能回滚
### 数据操纵语言 DML
- 对数据库中更改数据操作的语句
- select insert update delete--> CRUD 增删改查
- 通常把select相关操作,单独出来,称之为DQL
- DML支持事务,在非自动提交模式时,可以利用rollback回滚操作.
### 数据查询语言 DQL
- 筛选,分组,连表查询 **面试重点**
### TCL 和 DCL
- 事务控制语句TCL
- 负责实现数据库中事务支持的语言,commit rollback savepoint等指令
- DCL数据库控制语言
- 管理数据库的授权,角色控制等,grant(授权),revoke(取消授权)
### 练习：
1. 案例：创建一张表customer（顾客）
create table customer(
cid int(4) primary key comment '顾客编号',
cname varchar(50) comment '顾客姓名',
sex char(5) comment '顾客性别',
address varchar(50) comment '地址',
phone varchar(11) comment '手机',
email varchar(50) comment '邮箱'
);
show create table customer;
2. 插入5条数据
insert into customer values(1001,'小明','男','楼上18号','123','123@163.com');
insert into customer values(1002,'小红','女','楼上17号','1234','1234@163.com');
insert into customer values(1003,'老王','男','楼上18号隔壁','1234','1234@163.com');
insert into customer values(1004,'老宋','男','楼上17号隔壁','1234','1234@163.com');
insert into customer values(1005,'小马','女','楼上17号隔壁','1234','1234@163.com');
-1 修改一条数据的姓名 小红的姓名
-2 修改一条数据的性别 老王的性别
-3 修改一条数据的电话 1001号的电话
-4 修改一条数据的邮箱 邮箱为123@163.com,改成323@163.com
-5 查询性别为 男的所有数据
select * from customer where sex="男";
-6 自定义DDL操作的需求,5道题,可以同上面book表的操作
### 数据库数据类型
- 主要包括5大类
- 整数类型 int, big int
- 浮点数类型 double decimal
- 字符串类型 char varchar text
- 日期类型 date datetime timestamp time year...
- 其他数据类型 set....
### 字符串
- char(固定长度) 定长字符串 最多255个字节
- 定多少长度,就占用多少长度
- 多了放不进去,少了用空格补全
- 不认识内容尾部的空格
- varchar(最大长度) 变长字符串 最大65535字节,但是使用一般不超过255
- 只要不超过定的长度,都可以放进去
- 以内容真实长度为准
- 认识内容尾部的空格
- text 最大65535字节
- blob 大数据对象,以二进制(字节)的方式存储
### 整数
- tinyint 1字节
- smallint 2字节
- int 4字节
- bigint 8字节
int(6)
影响的是查询时显示长度(zerofill)
不影响数据的保存长度
create table t1(id1 int,id2 int(5));
insert into t1 values(111111,111111);
alter table t1 modify id1 int zerofill;
alter table t1 modify id2 int(5) zerofill;
insert into t1 values (1,1);
float 4字节
double 8字节 double(8,2)
可能会产生精度的缺失 10.0/3 3.3333333336
decimal
不会缺失精度,但是使用的时候需要指定总长度和小数位数
### 日期
- date 年月日
- time 时分秒
- datetime 年月日时分秒,到9999年,而且需要手动输入,如果没有手动输入,就显示null.
- timestamp 年月日时分秒,在没有数据手动插入时,自动填入当前时间.最大值2038
- bigint 1970-1-1 0:0:0 格林威治时间
案例：创建表t，字段d1 date，d2 time,d3 datetime,d4 timestamp
create table t(
id int,
d1 date,
d2 time,
d3 datetime,
d4 timestamp
);
insert into t (d1,d2) values ('1910-01-10','12:32:12');
insert into t values(1,'2018-12-21','15:12:00','1995-02-10 12:08:12','2030-10-10 15:19:32');
insert into t values(2,'3018-01-25','15:12:34','9234-12-31 12:12:12','2030-12-31 12:12:12');
insert into t values(2,'3018-01-25','15:12:34','9999-12-31 23:59:59','2030-12-31 12:12:12');
### 练习
创建人物表，插入，修改，查询
create table person(
id int(4) primary key,
name varchar(50),
age int(3)
);
insert into person values(1,"梅超风",36);
insert into person values(2,"洪七公",96);
insert into person values(3,"杨过",40);
insert into person values(4,"令狐冲",28);
insert into person values(5,"张三丰",100);
insert into person values(6,"张翠山",27);
insert into person values(7,"张无忌",27);
insert into person values(8,"赵敏",18);
insert into person values(9,"独孤求败",250);
insert into person values(10,"楚留香",36);
1.案例：修改张三丰的name为刘备，id为11
update person set name="刘备",id=11 where name="张三丰";
2.案例：修改2号人物的的name为夏侯渊
update person set name="夏侯渊" where id=2;
3.案例：根据条件修改person表中的数据,修改id是6的数据中，姓名改为'任我行', 年龄改为39
update person set name="任我行",age=39 where id=6;
4.案例：修改姓名是‘楚留香'的数据，把id改为20，年龄改为19
update person set id=20,age=19 where name="楚留香";
5.案例：把person所有的数据的年龄全部改为20
update person set age=20;
6.案例：修改id为7的数据，把id改为100，姓名改为杨过，年龄改为21
update person set id=100,name="杨过",age=21 where id=7;
7.案例：修改姓名是独孤求败，把年龄改为35
update person set age=35 where name="独孤求败";
8.案例：修改id=8的信息，把姓名改为房玄龄
update person set name="房玄龄" where id=8;
9.案例 :修改id为20并且年龄为20的人的姓名为刘德华(郑少秋也行)提示 where...and...
update person set name="郑少秋" where id=20 and age=20;
## 查询
### 没有条件的简单查询
1. select * from 表名;查询表中所有的数据
select * from person;
select * from t;
select * from emp;
select * from dept;
2. 查询某些列中的值
select name as '姓名' from person;
select name as '姓名',age as '年龄' from person;
select id as '编号',name as '姓名',age as '年龄' from person;
- 学习过程的编程习惯select * from 表;
- 工作中的编程习惯select id,name,age from person;
- 查询emp表中所有员工的姓名,上级领导的编号,职位,工资
select ename,mgr,job,sal from emp;
- 查询emp表中所有员工的编号,姓名,所属部门编号,工资
select empno,ename,deptno,sal from emp;
- 查询dept表中所有部门的名称和地址
select dname,loc from dept;
## 如果忘记了mysql的用户名和密码怎么办
1. 卸载重新装
2. 不重装软件如何修改密码
- 1.停止mysql服务
- 2.cmd中输入一个命令
mysqld --skip-grant-tables;
-通过控制台,开启了一个mysql服务
- 3.开启一个新的cmd
-mysql -u root -p
可以不使用密码进入数据库
- 4. show databases;----mysql
- 5. use mysql;
- 6. update user set password=password('新密码') where user="root";
- 7. 关闭mysqld这个服务/进程
- 8. 重启mysql服务
###
##MySql03
### 没有条件简单的查询语句
select * from emp;
select 字段名 from emp;
1. 查询表中所有的数据
案例：查询emp,dept,t_item表中所有的数据
select * from emp;
select * from dept;
select * from t_item;
2 查询某些列中所有的列值
案例：查询emp表中所有员工的姓名，上级领导的编号，职位，工资
select ename,mgr,job,sal from emp;
案例：查询emp表中所有员工的编号，姓名，所属部门的编号，工资和奖金
select empno,ename,deptno,sal,comm from emp;
案例：查询dept表中所有部门的名称和地址
select dname,loc from dept;
- 列值为null ： is null
案例：查询没有上级领导的员工的编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
select ename,job,sal,comm
from emp
where comm is nul;
- 列值不为null： is not null
案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
select empno,ename,job,comm
from emp
where comm is not null and comm >0;
案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
select ename,sal,mgr
from emp
where mgr is not null;
### 别名
select 字段名 as 别名 from 表名;
select 字段名 别名,字段名 别名 from 表名;
select ename '员工姓名' from emp;
select e.ename '员工姓名' from emp e;
select e.ename,e.job,e.sal
from emp e;---表的别名
#### 去重 distinct
- 查询emp中所有的职位
select job
from emp;
- 查询emp中所有的部门领导编号
- 查询emp中有多少个部门领导
select distinct mgr from emp;
### WHERE
- where子句用于规定选择的标准
- 如果需要有条件的从表中选取数据,可将where子句添加到select语句的后面
- where子句是决定一条查询语句最后显示多少条目数的关键
查询上级领导编号为7698的员工所有信息
select empno,ename,job,mgr
from emp
where mgr=7698; (条目数===对象数量)
- 在where中我们会使用一些运算符,大致与java相同
1. and or
where mgr=7698 and (comm>1000 or deptno=3)
2. like
3. < , > , <= , >= , = , != <>
4. is null , is not null, in , not
5. between XX and XXX
1.案例：查询工资高于2000的所有员工的编号，姓名，职位，工资
select empno,ename,job,sal
from emp
where sal>2000;
2.案例：查询工资小于等于1600的所有员工的编号，姓名，工资
select empno,ename,sal
from emp
where sal<=1600;
3.案例：查询部门编号是20的所有员工的姓名，职位以及所属部门的编号
select ename,job,deptno
from emp
where deptno=20;
4.案例：查询职位是MANAGER的所有员工的姓名，职位
select ename,job
from emp
where job="MANAGER";
5.案例：查询不是10号部门的所有员工的编号，姓名，以及所属部门的编号（2种方法）
select empno,ename,deptno
from emp
where deptno!=10;
where deptno <> 10;
6.案例： 查询单价等于128的商品
select *
from t_item
where price=89\G;
7.案例：查询单价不等于8443的商品
select *
from t_item
where price != 8443;
where prcie <> 8443;
### AND 和 OR 运算符
- 在where子句中两个或者多个条件结合起来
- 结合的结果与java中的&& || 一致
- 在不明确优先级的情况下,请使用()
1.案例：查询不是10号部门的并且工资小于3000的所有员工的编号，姓名，以及所属部门的编号
select empno,ename,deptno
from emp
where deptno<>10 and sal<3000;
2.案例：查询部门编号是30或者上级领导为7698的所有员工的姓名，职位以及所属部门的编号
select ename,job,deptno
from emp
where deptno=30 or mgr=7698;
### SQL LIKE 操作符
- like用于在where子句中搜索列中的指定的 模式
- sql 模式 --->通配符
- %代替一个或者多个字符
a% %a a%a
- _代替一个字符
A_ _B A_B _A_
- 小演示
%@%.% abc@163.com
%@%.cn %@163.%
where ename like '%a_'
1.案例：查询标题包含记事本的商品
select *
from t_item
where title like '%记事本%';
2.案例：查询有赠品的DELL产品 sell_point
select *
from t_item
where title like '%DELL%' and sell_point like '%赠%';
3.案例：查询单价低于100的笔记本 price
select *
from t_item
where title like '%笔记本%' and price<1000;
4.案例：查询价格介于50到200之间的得力商品
select *
from t_item
where title like '%得力%' and price>=50 and price <=200;
5.案例：查询有图片的得力商品 image
select *
from t_item
where title like '%得力%' and image is not null;
6.案例：查询分类为238,917的产品
select *
from t_item
where category_id=238 or category_id=917;
where category_id in (238,917);
7.案例：查询标题中不含得力的商品
select *
from t_item
where title not like '%得力%';
8.案例：查询分类不是238,917的商品
select *
from t_item
where category_id not in (238,917);
9.案例：查询价格介于50到200之外的商品
select *
from t_item
where price not between 50 and 200;
### SQL ORDER BY 子句
- 用于根据指定列对结果集进行排序
- 默认升序 小--->大
- asc为升序,desc降序
- order by必须写在where语句之后
1.案例：查询所有带燃字的商品,按单价升序排列
2.案例：查询所有DELL商品,按单价降序排列
3.案例：查询所有DELL商品,按分类升序单价降序排列
### 上午把3个排序案例再做一遍,然后好好理解order by 之后有多个字段,最后的效果
### limit 子句--分页
- limit begin,size
- begin 本页数据的起始行,从0开始
- size 本页显示多少行
-- 查询所有商品,并按单价正序排列,显示其中第1页(每页5条)
select price
from t_item
order by price limit 0,5;
-- 查询所有商品,并按单价正序排列,显示其中第2页(每页5条)
select price
from t_item
order by price limit 5,5;
## 很多函数功能与java中相关api类似,但是,除了几种特殊的需求以外,我们开发时,尽量不要在数据库中进行函数计算
### CONCAT()函数
concat(str1,str2,...)
- 如果任何一个str是null,返回值就为null
select concat('今天周5了','下周讲完数据库')
from dual;
select 'helloword' from dual;
select concat(ename,mgr) from emp;
-- 查询商品,并将标题和单价,加上元,拼到一起进行展现
select concat(title,price,'元')
from t_item;
### 数值计算
- 运算符 + - * / % mod()
- 弱数据类型 字符串与数字进行计算 '10'-5
- 取余 % 7%2 等同于 mod(7,2)
-- 查询商品,并在结果中显示商品的总价值
select price '单价',num '库存',price*num 总价值
from t_item
order by 总价值;
### DATE_FORMAT() 函数
- now() 返回当前的日期和时间
- curdate() 返回当前的日期
- curtime() 返回当前的时间
- date(时间日期表达式)提取这个表达式日期的部分
- time(时间日期表达式)提取这个表达式时间的部分
- extract(时间日期表达式)提取这个表达式单独的一部分(年月日时分秒)
select now() from dual;
select date(now());
select time(now());
select extract(year from now());
select extract(month from now());
select extract(day from now());
### date_format()
- 以不同的格式显示时间/日期数据
- 语法 date_format(日期表达式,'format');
date_format(now(),'%c-%d')
- format
- %c 月 1-12
- %d 月的天(00-31)
- %H 小时(00-23)
- %h 小时(01-12)
- %i 分钟
- %m 月 01-12
- %S 秒
- %Y 年
select date_format(now(),'%c--%m');
- emp中hiredate, XXXX年XX月XX日
select date_format(hiredate,"%Y年%m月%d日") from emp;
- 显示当前时间 格式为 23:12:16
select date_format(now(),"%h:%i:%s");
- 显示当前日期和时间,格式为XXXX-XX-XX xx:xx:xx
- 显示当前日期为 日/月/年
-- 查询商品,并显示商品上传日期(x年x月x日)
select date_format(created_time,"%Y年%m月%d日") from t_item;
### str_to_date 把字符串转换成日期格式
- 把字符串格式的时间,转换成时间格式
- 第一个参数为字符串,第二个参数为格式和第一个参数一样的fromat字符
- 如果格式不同,结果为null
select str_to_date('08/09/2008','%m/%d/%Y');
select str_to_date('08/09/08','%m/%d/%y');
select str_to_date('08.09.2008','%m.%d.%y');
select str_to_date('08.08.2008 08:00:00','%m.%d.%Y %h:%i:%S');
### IFNULL() 的函数
- 空值处理函数 comm 0 null
- ifnull(expr1,expr2) 如果expr1不是null,ifnull返回的就是expr1,如果是null,返回expr2
-- 查询没有奖金的员工，并把没有奖金的数值变成0
select ifnull(comm,0)
from emp;
### 聚合函数
- 对多行数据进行合并统计
- 使用聚合函数,要注意,聚合函数一般只有一行结果,如果其他要查询的列,有多行结果,那么只会显示,其他结果被舍弃
- 原因,数据库不支持行合并
- 不要把聚合函数和普通列放到同一个dql语句中,除非普通列只有一条数据
- sum():返回列的总数(总额)
select sum(sal)
from emp;
- avg(): 返回数值的平均值,但是null不包含在计算中
select sum(sal),avg(comm)
from emp;
select comm from emp;
- count(): 返回指定列的总数目,null不计数
select count(comm) from emp;
- max():这一列中的最大值,null不计算
- min():这一列中的最小值,null不计算
-- 查询得力商品的库存合计
select sum(num)
from t_item
where title like "%得力%";
-- 查询得力商品的平均单价
select avg(price)
from t_item
where title like '%得力%';
-- 查询得力商品的条目数
select count(1)
from t_item
where title like '%DELL%';
-- 查询DELL商品的最高单价
select max(price)
from t_item
where title like '%DELL%';
-- 查询DELL商品的最小库存
select min(num)
from t_item
where title like '%DELL%';
### 字符串的函数
- char_length()--字符数
select char_length(ename) from emp;
- instr('abcdefg','bcd') 返回第二个字符串在第一个中占的位置,从1开始,找不到返回0
select instr('abcdefg','bcd');
- locate('abc','---abc---abc---abc') 返回第一个字符串在第二个中占的位置,从1开始,找不到返回0
select locate('abc','---abc---abc---abc');
- insert("abcdefghajdfkafjsdak",2,5,'---') 用子串取代第一个字符串的位置,从2开始,取代5个长度
select insert("abcdefghajdfkafjsdak",2,5,'---');
- lower()转化成小写
- upper()转换成大写
select lower(ename) from emp;
- left("abcdef",3)返回左边3个字符
- right("abcdef",3)返回右边3个字符
select right("abcdef",3);
- trim(" a b c ")去除的是两边空格
- substring("fdafadfadsfsad",4)从4开始截取
- substring("fdafadfadsfsad",4,6);从4开始截取,截取6个字符
select substring("fdafadfadsfsad",4,6);
- repeat("abc",3) 重复3遍
- replace("hello my sql","my","your")子串替换
- reverse()-反转字符串
### 数学相关函数
- floor()向下取整
- round(32.25)四舍五入
- round(32.2523432,2)四舍五入,小数点后两位
- round(4332.25,-2)四舍五入
select round(4332.25,-2);
- truncate(234.234,1) 保留小数点后1位,不四舍五入
select truncate(234.294,1);
### 练习
1. 笔记内容都打一遍
2. 必须完成前30题
3. 后30题作为提升
1.案例：查询没有上级领导的员工的编号，姓名，工资
2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
5.案例：查询emp表中名字以‘S’开头的所有员工的姓名
6.案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
7.案例：查询倒数的第2个字符是‘E’的员工的姓名
8.案例：查询emp表中员工的倒数第3个字符是
9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名
10.案例：查询emp表中名字不是以'K'开头的员工的所有信息
11.案例：查询emp表中名字中不包含‘A’的所有员工的信息
12.案例：做文员的员工人数（job_id 中 含有 CLERK 的）
13.案例：销售人员 job: SA_XXXXX 的最高薪水
14.案例：最早和最晚入职时间
15.案例：查询没中类别的商品数量
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,
## 分组查询
- 查看每个部门的平均工资--错误演示
select avg(sal),deptno
from emp
order by deptno;
select avg(sal),deptno
from emp
group by deptno;
select mgr,count(*)
from emp
group by mgr;
- 分组查询通常和聚合函数一起使用
- 一般情况下,查询字段中出现聚合函数和普通列,一起查询的时候,那么分组的条件就是普通列
- 当select子句中含有聚合函数时,凡是不在聚合函数中的其他单独字段,都必须出现在group by子句中.
- group by子句要写在order by之前,where后
- group by 可以根据多个字段分组
- 查看同部门同职位的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job;
- 查询部门平均工资
select deptno avg(sal)
from emp
group by deptno;
- 查询每个领导有多少个员工,显示领导id和员工数量
select mgr,count(*)
from emp
group by mgr;
#### 练习
1.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
select deptno,count(*),sum(sal)
from emp
group by deptno
order by count(*),sum(sal) desc;
2.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
select deptno,avg(sal),min(sal),max(sal)
from emp
where sal>=1000 and sal<=3000
group by deptno
order by avg(sal);
3.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
select count(*),sum(sal),avg(sal),min(sal),job
from emp
where mgr is not null
group by job
order by count(*) desc,avg(sal);
4.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
select empno,ename,job,sal
from emp
where sal between 1000 and 3000;
5.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金
select empno,ename,sal,comm
from emp
where comm between 500 and 2000;
6.案例：查询员工的编号是7369，7521，XXXX
select * from emp
where empno in(7369,7521,XXXX);
7.案例：查询emp表中，职位是ANALYST，
select * from emp
where job='ANALYST'
8.案例：查询emp表中职位不是ANALYST,
select * from emp
where job!='ANALYST'
### 练习
-- 查询出所有分类商品所对应的库存总量
select category_id,sum(num)
from t_item
group by category_id;
-- 查询出所有分类商品所对应的平均单价
select category_id,avg(price)
from t_item
group by category_id;
-- 1. 每个部门的人数
select deptno,count(*)
from emp
group by deptno;
-- 2. 每个部门中，每个主管的手下人数
select deptno,mgr,count(*)
from emp
group by deptno,mgr;
-- 3. 每种工作的平均工资
select job,avg(sal)
from emp
group by job;
-- 提高题 4. 每年的入职人数
select count(*),extract(year from hiredate) year
from emp
group by year;
## 有条件分组统计
### HAVING 子句
**错误演示**
- 查询部门的平均工资,前提是该部门的平均工资高于2000
select avg(sal),deptno
from emp
where avg(sal)>2000
group by deptno;
**正确效果**
select avg(sal),deptno
from emp
group by deptno
having avg(sal)>2000;
- group by 使用having过滤条件
- 在sql语句中添加having子句的原因,是因为where关键字无法与聚合函数一起使用
- where条件用于过滤行数,having条件用于过滤分组数量
- 执行顺序,首先执行where,然后执行group by,根据一个或多个列进行分组,之后执行having.对分组以后的数据再次过滤.最后执行排序order by
-- 查询所有分类商品所对应的库存总量中,高于1000的总量
select sum(num),category_id
from t_item
group by category_id
having sum(num)>1000;
-- 查询所有分类商品所对应的平均单价中,低于100的均价
select avg(price),category_id
from t_item
group by category_id
having avg(price)<=100;
-- 查询编号238和编号917分类商品的平均单价
select avg(price),category_id
from t_item
group by category_id
having category_id in (238,917);
select avg(price),category_id
from t_item
where category_id in (238,917)
group by category_id;
- 总结,having子句经常跟聚合函数一起使用,如果没有使用到聚合函数,要注意是否可以写在where中,如果可以写在where,优先使用where过滤
### 课堂练习
1.案例：查询emp表中，每个部门的平均工资高于2000的部门的编号,部门的人数,平均工资, 最后根据平均工资进行升序排列。
select deptno,count(*),avg(sal)
from emp
group by deptno
having avg(sal)>2000
order by avg(sal);
2.案例：查询emp表中名字中不是以'K'开头的信息,每个部门的最低工资高于1000的部门的编号，工资总和,平均工资以及最低工资，最后根据平均工资进行升序排列。
select deptno,sum(sal),avg(sal),min(sal)
from emp
where ename not like 'K%'
group by deptno
having min(sal)>1000
order by avg(sal) asc;
3.案例：查询emp表中部门编号是10,30号部门的员工,每个职业的最高工资低于5000的职业的名称,人数，平均工资，最高工资，最后根据人数进行升序排列，如果人数一致，根据最高工资进行降序排列。
select job,count(*),avg(sal),max(sal)
from emp
where deptno in (10,30)
group by job
having max(sal)<=5000
order by count(*),max(sal) desc;
4.案例：查询emp表中，每个部门的编号，人数，工资总和，最高工资以及最低工资，过滤掉最高工资是5000的部门，根据部门的人数进行升序排列，如果人数一致，则根据最高工资进行降序排列。
select deptno,count(*),sum(sal),max(sal),min(sal)
from emp
group by deptno
having max(sal)!=5000
order by count(*),max(sal) desc;
5.案例：查询emp表中工资在1000~3000之间的员工信息，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排列
select deptno,sum(sal),avg(sal)
from emp
where sal>=1000 and sal<=3000
group by deptno
having avg(sal)>=2000
order by avg(sal);
6.案例：查询emp表中名字不是以‘S’开头，每个职位的名字，人数，工资总和，最高工资，过滤掉工资是3000的职位，根据人数进行升序排列，如果人数一致，根据工资总和进行降序排列。
select job,count(*),sum(sal),max(sal)
from emp
where ename not like 'S%' and sal!=3000
group by job
order by count(*) ,sum(sal) desc;
7.案例：查询emp表的信息，每个职位的名称，人数，平均工资，最低工资，过滤掉平均工资是3000的职位信息，根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
select job,count(*),avg(sal),min(sal)
from emp
group by job
having avg(sal)!=3000
order by count(*) desc,avg(sal);
## 子查询
### MySQL子查询
1. 子查询是指,在DML语句,嵌套了另外一个查询(DQL)语句
2. 某些DDL也可以使用子查询
3. 子查询语句,称为内部查询,而包含子查询的查询语句,称为外部查询
4. 常用的子查询会出现两种
- 外部查询的where子句使用子查询
- 子查询在from后面,用子查询的结果集充当一张表
5. 子查询可以在表达式的任何地方使用,但是必须在括号中关闭
6. 子查询可以嵌套在另外一个子查询中
1.案例：拿最低工资的员工信息
select min(sal) from emp
select *
from emp
where sal=(select min(sal) from emp);
2.案例：工资多于平均工资的员工信息
select avg(sal) from emp;
select *
from emp
where sal>(select avg(sal) from emp);
3.案例：最后入职的员工信息
select empno,ename,hiredate
from emp
where hiredate=(select max(hiredate) from emp);
4.案例：查询出有商品的 分类信息
select distinct category_id from t_item;
select *
from t_item_category
where id in (select distinct category_id from t_item);
5.案例：查询工资高于20号部门最高工资的员工的所有信息
select max(sal)
from emp
where deptno=20;
select *
from emp
where sal>(select max(sal)
from emp
where deptno=20);
6.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称
select deptno
from emp
where ename='KING';
select deptno,dname
from dept
where deptno=(select deptno
from emp
where ename='KING');
7.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号
select empno,ename,job,deptno
from emp
where deptno=(select deptno
from dept
where dname='SALES');
8.案例：查询部门地址是DALLAS的部门下所有员工的所有信息(查询在DALLAS工作的所有员工信息)
select *
from emp
where deptno=(select deptno
from dept
where loc='DALLAS');
9.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）
select *
from emp
where job=(select job from emp
where ename='JONES');
不包含JONES
select *
from emp
where job=(select job from emp
where ename='JONES') and ename <>'JONES';
## 关联查询数据
- 从多张表中查询相应记录信息emp.deptno dept.deptno
- 关联查询的重点在于这些表中记录的对应关系,这个关系也称为连接条件(关联条件)
- 查看每个员工的名字以及所在部门的名字
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
- 如果不写关联关系
select e.ename,d.dname
from emp e,dept d;
### 笛卡尔积
- 当多表关联时,如果没有写关联条件,返回的结果集是这几张表条目数的乘积,这个乘积就叫做笛卡尔积
- 多数情况下,笛卡尔积是无意义的
- 非常耗费资源,要尽量避免
1.查看在new york工作的员工
select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno and d.loc='NEW YORK';
2.查看工资高于3000的员工，名字，工资，部门名，所在地
select e.ename,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.sal>3000;
### 等值连接/内连接
- 语法
-表结构，内容存在上下级的关系，把这张表看作两张表进行查询
select * from A,B where A.某字段=B.某字段;
select * from A join B on A.某字段=B.某字段;
完整版:
select * from A [inner] join B on A.某字段=B.某字段;
1.查看在new york工作的员工
select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno and d.loc='NEW YORK';
select e.ename,d.loc
from emp e join dept d
on e.deptno=d.deptno
where d.loc='NEW YORK';
2.查看工资高于3000的员工，名字，工资，部门名，所在地
select e.ename,e.sal,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.sal>3000;
- 不满足连接条件的记录是不会在关联查询中被查询出来的
select e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e join dept d
on e.deptno=d.deptno;
### 左外连接
- 以join左侧表作为基准表(驱动表--所有数据都会被显示出来,不管是否符合连接条件),那么当该表中某条记录不满足连接条件时,来自右表的字段全部为null
- 语法 select * from A left join B on 连接条件;
select e.ename,e.sal,d.dname,d.loc,d.deptno
from dept d left join emp e
on e.deptno=d.deptno;
### 右外连接
- 以join右侧表作为基准表(驱动表--所有数据都会被显示出来,不管是否符合连接条件),那么当该表中某条记录不满足连接条件时,来自左表的字段全部为null
- 语法 select * from A right join B on 连接条件;
select e.ename,e.sal,d.dname,d.loc,d.deptno
from emp e right join dept d
on e.deptno=d.deptno;
### 关联查询数据案例
> 代码实践
-- 查询出所有可以匹配的商品分类及商品数据
select *
from t_item t1 join t_item_category t2
on t1.category_id=t2.id;
-- 查询出所有的分类,以及与之匹配的商品
select *
from t_item t1 right join t_item_category t2
on t1.category_id=t2.id;
-- 查询出所有的商品,以及与之匹配的分类
select *
from t_item t1 left join t_item_category t2
on t1.category_id=t2.id;
### 强化练习
## 练习
1. 每个部门的人数,根据人数排序
2. 每个部门中，每个主管的手下人数
3. 每种工作的平均工资
4. 每年的入职人数
5. 少于等于3个人的部门
6. 拿最低工资的员工信息
7. 只有一个下属的主管信息
8. 平均工资最高的部门编号
9. 下属人数最多的人，查询其个人信息
10. 拿最低工资的人的信息
11. 最后入职的员工信息
12. 工资多于平均工资的员工信息
13. 查询员工信息，部门名称
14. 员工信息，部门名称，所在城市
15. DALLAS 市所有的员工信息
16. 按城市分组，计算每个城市的员工数量
17. 查询员工信息和他的主管姓名
18. 员工信息，员工主管名字，部门名
19. 员工信息，部门名，和部门经理
20. 员工和他所在部门名
21. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，根据工资的等级进行升序排列
22. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资
23. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc
from emp e left join dept d
on e.deptno=d.deptno
where e.ename not like '%K%';
24. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
25. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，该等级的最低工资，按照员工的编号进行升序排列。
select e.empno,e.ename,e.job,e.sal,s.grade,s.losal
from emp e left join salgrade s
on e.sal between s.losal and s.hisal
order by e.empno;
**having子句中经常跟聚合函数经常使用，如果没有使用到聚合函数，就要注意是否可以写在where条件中。**
select category_id ,avg(price)
from t_item
where category_id in(238,917)
group by category_id;
- 子查询
1.案例：查询emp表中姓名是‘KING’所属的部门的编号，名称
select deptno from emp
where ename='KING';
select deptno,dname
from dept
where deptno=(select deptno from emp
where ename='KING');
2.案例：查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号
select deptno
from dept
where dname='SALES'
select empno,ename,job,deptno
from emp
where deptno=(select deptno
from dept
where dname='SALES');
3.案例：查询部门地址是DALLAS的部门下所有员工的所有信息
select deptno from dept
where loc='DALLAS'
select * from emp
where deptno in (select deptno from dept
where loc='DALLAS');
4.案例：查询跟JONES同样工作的员工的所有信息（包含JONES）
select job from emp
where ename='JONES'
select * from emp
where job=(select job from emp
where ename='JONES');
不包含JONES
select * from emp
where job=(select job from emp
where ename='JONES') and ename<>'JONES';
- **关联查询数据**
- 使用where关联查询
1.查看在new york工作的员工
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno and d.loc='new york';
- 使用内连接的关联查询 inner
1.查看在new york工作的员工
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno
where d.loc='new york';
2.查看工资高于3000的员工，名字，工资，部门名，所在地
select e.ename,e.sal,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.sal>1000;
3.查询emp表中员工的编号，姓名，职位以及所属部门的编号，名称
select e.empno,e.ename,e.job,e.deptno,d.dname
from emp e join dept d
on e.deptno=d.deptno;
4.查询emp表中员工的编号，姓名，入职时间以及所属部门的名称和地址
select e.empno,e.ename,e.hiredate,d.dname,d.loc
from emp e join dept d
on e.deptno=d.deptno;
- 外连接,在关联查询中,显示不满足连接条件的条目
- 左外连接
- 右外连接
- mysql oracle在外连接有区别
- oracle有全外连接 full varchar2 number
- 练习
1. 每个部门的人数,根据人数排序
select deptno,count(*) c
from emp
where deptno is not null
group by deptno
order by c;
2. 每个部门中，每个主管的手下人数
select deptno,mgr,count(*) c
from emp
where deptno is not null and mgr is not null
group by deptno,mgr
order by deptno,c;
3. 每种工作的平均工资
select job,avg(sal)
from emp
group by job
order by avg(sal);
4. 每年的入职人数
select extract(year from hiredate) year,count(*) c
from emp
group by year
order by c;
5. 少于等于3个人的部门(部门ID,部门人数,提高:部门名称)
select deptno,count(*) c
from emp
where deptno is not null
group by deptno
having c<=3;
select e.deptno,d.dname,count(e.ename) c
from emp e join dept d
on e.deptno=d.deptno
where e.deptno is not null
group by e.deptno
having c<=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. 平均工资最高的部门编号
- 1.分组求平均工资
select deptno,avg(sal) avg
from emp
where deptno is not null
group by deptno;
- 2.得到最高平均工资的部门
select max(avg)
from (select deptno,avg(sal) avg
from emp
where deptno is not null
group by deptno) t;
- 3.第二种方法
select deptno,avg(sal) avg
from emp
where deptno is not null
group by deptno
order by avg desc
limit 1;
9. 下属人数最多的人，查询其个人信息
select count(*) c
from emp
where mgr is not null
group by mgr
select max(c)
from (select count(*) c
from emp
where mgr is not null
group by mgr) t;
select mgr,count(*)
from emp
group by mgr
having count(*)=(select max(c)
from (select count(*) c
from emp
where mgr is not null
group by mgr) t);
10. 拿最低工资的人的信息
select *
from emp
where sal=(select min(sal) from emp);
11. 最后入职的员工信息
select *
from emp
where hiredate=(select max(hiredate)
from emp);
12. 工资多于平均工资的员工信息
select *
from emp
where sal>(select avg(sal) from emp);
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.*,d.loc
from emp e join dept d
on e.deptno=d.deptno
where d.loc='DALLAS';
16. 按城市分组，计算每个城市的员工数量
select d.loc,count(e.ename) c
from emp e join dept d
on e.deptno=d.deptno
group by d.loc
order by c;
17. 员工和他所在部门名
select e.*,d.dname
from emp e join dept d
on e.deptno=d.deptno;
18. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，根据工资的等级进行升序排列
select e.empno,e.ename,e.job,e.sal,s.grade
from emp e left join salgrade s
on e.sal between s.losal and s.hisal
order by s.grade;
19. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的编号，名称，地址
select e.*,d.*
from emp e left join dept d
on e.deptno=d.deptno
where e.ename not like '%K%';
20. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资
select d.*,e.empno,e.ename,e.job,e.sal
from emp e right join dept d
on e.deptno=d.deptno
21. 案例：查询emp表中所有员工的编号，姓名，职位，工资以及工资的等级，该等级的最低工资，按照员工的编号进行升序排列。
select e.empno,e.ename,e.job,e.sal,s.grade,
s.losal
from salgrade s right join emp e
on e.sal between s.losal and s.hisal
order by e.empno;
内连接 外连接(左,右)
from emp e join dept d
on e.deptno=d.deptno
### 自连接,自关联查询
自连接,当前表的一条记录可以对应当前表自己的多条记录
自连接是为了解决同类型数据,但是又存在上下级关系的树状结构数据时使用
- 把emp当成两张表，一张是员工，一张是领导
- 查询员工名及对应的领导名
select e1.ename,e2.ename
from emp e1,emp e2
where e1.empno=e2.mgr;
- 查看'SMITH'上司在哪个城市工作
select e.ename,m.ename,d.loc
from emp e join emp m
on e.mgr=m.empno
join dept d
on m.deptno=d.deptno
where e.ename='SMITH'
- 查询员工信息和他的主管姓名
select e.*,m.ename
from emp e join emp m
on e.mgr=m.empno
- 员工信息，员工主管名字，部门名
select e.*,m.ename,d.dname
from emp e join emp m
on e.mgr=m.empno
join dept d
on m.deptno=d.deptno;
- 案例：查询emp表中所有员工的编号，姓名，职位，工资以及该员工上级领导的编号，姓名，职位，工资
select e.*,m.*
from emp e left join emp m
on e.mgr=m.empno;
## 设计表结构的一些基本原则
### 一对多关联
- 学生与班级
- 员工和部门
- 直观理解(多的就是N,少的就是1)
- 本表一条数据,可以对应另外一张表多条数据,本表1
- 本表多条数据,可以对应另外一张表一条数据,本表N
- 设计方案,在N中增加一个字段(外键),保存1的主键,形成主外键关系
### 多对多关联
- 学生和课程
- 角色和功能
- 设计方案,增加中间表,保存双方的主键(中间表和另外两张表都是一对多的关联关系)
### 连接方式和关联关系的区别
- 连接方式是匹配数据的方式,是具体实现管理的语法,它能实现任何关联关系的数据查询--sql语句是语法
- 关联关系是逻辑角度阐述两张表的关系,在这个关系的基础上,可以用任何连接方式查询出相关数据--是设计表的时候的逻辑
## 数据库设计之权限管理
### 什么是权限管理
- 对用户访问软件的权利进行限制的手段
### 如何实现权限管理
1. 设计3张表:用户表,角色表,功能表
### 如何设计多对多关系的表
### 权限管理表实现
### 权限管理表实现（续1）
create table user(
uid int auto_increment,
uname varchar(100),
create_time timestamp,
primary key(uid)
);
insert into user values(null,'张三',now());
insert into user values(null,'李四',now());
insert into user values(null,'王五',now());
create table role(
id int auto_increment,
name varchar(100),
create_time timestamp,
primary key(id)
);
insert into role values(null,'管理员',now());
insert into role values(null,'店小二',now());
insert into role values(null,'普通用户',now());
create table user_role(
id int auto_increment,
user_id int,
role_id int,
primary key(id)
);
insert into user_role values(null,1,3);
insert into user_role values(null,2,3);
insert into user_role values(null,3,3);
- 查询用户以及他所对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id;
- 查询张三对应的角色
select u.uid,u.uname,r.name
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
where u.uname='张三';
### 权限管理表实现（续2）
create table work(
id int primary key auto_increment,
rid int,
work_item varchar(100)
);
insert into work values(null,1,'对用户信息,商品信息,订单信息,角色信息等后台数据进行操作');
insert into work values(null,2,'对商品信息,订单信息等后台数据进行操作');
insert into work values(null,3,'浏览商品,购买商品');
- 查询user表中所有用户可以执行的操作
select u.uname,w.work_item
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
join work w
on w.rid=r.id;
- 查看张三可以进行的操作
select u.uname,w.work_item
from user u join user_role ur
on u.uid=ur.user_id
join role r
on r.id=ur.role_id
\
join work w
on w.rid=r.id
where u.uname='张三';
###复习
#### 内连接
1. 表结构,内容存在上下级的关系
2. 把这张表看做两张表进行关联查询
### 数据库设计
- 一对多关系
- 学生与班级，员工和部门都是一对多关联关系.
A表的一条数据,可以对应B表的多条数据,A表就是1
B表的多条数据,可以对应A表的一条数据,B表就是多
解决方案,1的这张表是主表,多的这张表是从表
在从表中(多)创建一个字段,存放主表(1)的主键,形成主外键关系
- 多对多关系
- 学生与课程,角色与功能都是对对多关联关系.
- 设计方案:增加中间表,保存双方的主键.
## 视图
### 视图概述
1. 视图是一张虚拟表,使用方法和使用表一样
2. 视图不是真实存在的表
3. 视图中对应一个select语句的查询结果集
4. 创建视图的语法
create view 视图名 as 子查询;
create view v_emp_10
as
select empno,ename,deptno
from emp
where deptno=10;
5. 使用视图的目的就是简化sql语句的复杂程度
重用子查询
- 查看视图
select * from 视图名
select * from v_emp_10;
select * from (select empno,ename,deptno
from emp
where deptno=10) t;
- 视图本身不包含数据
- 视图只是映射到基表的一个查询语句
- 当基表数据发生变化时,视图显示的数据也随之发生变化
#### 如果创建视图时,子查询起了别名,那么视图只认识别名
create view v_emp_10_1
as
select empno a1,ename a2,deptno a3
from emp
where deptno=10;
#### 修改视图
create or replace view v_emp_10_1
as
select empno id,ename name,deptno
from emp
where deptno=10;
#### 视图的分类
- 视图分为简单视图和复杂视图
- 简单视图:创建视图的子查询中,不含有关联查询,查询的字段不包含函数和表达式,没有分组,没有去重
- 反之就是复杂视图
#### 对视图进行DML操作,只能针对简单视图可以使用
insert into v_emp_10_1 values(2001,'lily',10);
- 对视图进行dml操作,要遵循基表的约束,不然会失败
-- 视图只能看到10号部分,但是通过视图插入了20号部门的员工,数据是偷渡进去的,对基表进行了污染
insert into v_emp_10_1 values(2002,'lilei',20);
- 对视图的操作就是对基表的操作
- 操作不当会对基表产生数据污染
update v_emp_10_1 set name="bbb"
where deptno=10;
update v_emp_10_1 set deptno=20;
-- mysql更新视图不会数据污染
update v_emp_10_1 set name="bbb"
where deptno=30;
-- 删除视图中的数据,不会产生数据污染
delete from v_emp_10_1 where deptno=30;
#### 在mysql中,通过视图能够产生数据污染,只有insert
#### 为视图增加检查选项
#### 可以保证对视图进行DML操作后
#### 视图必须对改变的部分可见(不可见的不许改)
#### 否则不允许进行DML操作,这样避免数据污染
create or replace view v_emp_10_1
as
select empno id,ename name,deptno
from emp
where deptno=20
with check option;
-- 成功操作
insert into v_emp_10_1 values(2003,'lili', 20);
-- 操作错误
insert into v_emp_10_1 values(2003,'hanmeimei', 30);
#### 视图简化复杂查询语句,重用子查询
1. 简化复杂查询
2. 如果需要经常执行某项复杂查询,可以基于这个复杂查询创建视图
#### 限制数据访问
1. 视图本质上就是一条select语句
2. 当访问视图时,只能访问到select语句中涉及到的列
3. 对基表中其它列起到安全和保密的作用
#### 工作中,对视图一般只进行DQL操作,不进行DML操作
### 复杂视图
1. 创建一个复杂视图
- 创建一个含有公司部门工资情况的视图
- 内容如下
- 部门编号,部门名称
- 部门的最高,最低,平均工资
- 工资总和
create view v_dept_sal
as
select d.deptno,d.dname,
max(e.sal) max_sal,
min(e.sal) min_sal,
avg(e.sal) avg_sal,
sum(e.sal) sum_sal
from emp e join dept d
on e.deptno=d.deptno
group by d.deptno;
- 查询出 比自己所在部门的平均工资 高的员工
select e.ename,e.sal,e.deptno,v.avg_sal
from emp e,v_dept_sal v
where e.deptno=v.deptno and
e.sal>v.avg_sal;
### 删除视图
drop table 表名;
drop view 视图名;
drop view v_emp_10_1;
## 索引原理
### 索引概述
- 用来加快查询的技术有很多,最重要的就是索引
- 通常索引可以大幅度提高查询速度
- 如果不使用索引,mysql会从第一条数据开始,读完整个表,直到找到相关的数据,表越大,花费时间越多
- 索引可以用来改善性能,但是有时索引也可以降低性能
- 索引的统计和应用是数据库自动完成
- 使用索引的位置
- where deptno=10
- order by
- distinct
- like 不会使用索引
create index 索引名 on 表名(字段);
create index idx_emp_ename on emp(ename);
- 只要数据库认为可以使用某个已经创建的索引,索引就会自动应用
- 我们只需要决定要不要给某张表的某个字段创建索引
- mysql innodb B+TREE 3次磁盘IO就可以找到
#### 复合索引
create index idx_emp_job_sal on emp(job,sal)
select * from emp
order by job,sal;
#### 创建表的时候加索引
create table mytable(
id int not null,
uname varchar(6) not null,
index idx_mytable_uname (uname)
);
#### 更改表的索引
alter table mytable add index idx_mytable_uname1 (uname);
#### 删除索引
drop index idx_mytable_uname1 on mytable;
#### 索引总结
- 经常出现在where子句中的列创建索引
- 经常出现在order by子句中列创建索引
- 经常出现在distinct后面的列,创建索引
- 如果创建的是复合索引,索引的字段顺序和关键字顺序要一致
- 为经常做为表连接条件的列创建索引
- 不要在经常做DML操作的表和列上建立索引
- 不要在小表上创建索引
- 索引不是越多越好
- 删除很少使用,不合理的索引
## MySQL 约束
### 主键约束(PRIMARY KEY)
1. 不允许重复,不允许空值
2. 创建主键约束
- 列级语法
create table student1(
id int primary key,
name varchar(20)
);
- 表级语法 constraint 约束名 约束类型(列名)
create table student(
id int,
name varchar(20)
);
create table student2(
id int,
name varchar(20),
primary key(id)
);
#### 在表创建之后,添加主键约束--效果相当于表级语法
alter table student add primary key(id);
alter table student modify id int primary key;
#### 删除主键约束
alter table student drop primary key;
**mysql不起作用,oracle可以**
**alter table student modify id int;**
#### 主键自增
create table t1(
id int primary key auto_increment,
name varchar(10)
);
insert into t1 values(null,'aaa');
insert into t1 values(10,'bbb');
delete from t1 where id=13;
#### 主键自增长的细节
1. 插入数据时,使用null,自动增长
2. 插入数据时,如果插入一个比较大数,那么下次自增长从这个数开始累加
3. 删除末尾的条目,这个条目的id,不会再次在表中通过自增长出现
### 外键约束(FOREIGN KEY)
1. 工作中,除非特殊情况,一般不使用外键,使用逻辑外键
2. 外键约束是保证一个或两个表之间的参照完整性,保持数据一致
3. 表的外键可以是另一张表的主键,也可以是唯一的索引
4. 外键可以重复,可以是null
5. 使用外键约束的条件
- 父表和子表必须使用相同的存储引擎
- 存储引擎必须是innodb
- 外键列和参照列必须具有相似的数据类型,数字长度,若是有符号,必须相同----字符类型长度可以不同
- 外键列和参照列必须创建索引,如果外键列不存在索引,mysql自动为其创建
### 创建外键约束
1. 注意,虽然mysql支持外键列级语法创建外键,但是无效
2. 表级语法创建外键
constraint 外键约束名 foreign key(本表列名) references 目标表(目标列)
create table teacher_table(
teacher_id int auto_increment,
teacher_name varchar(255),
primary key(teacher_id)
);
insert into teacher_table values(null,'t1');
insert into teacher_table values(null,'t2');
insert into teacher_table values(null,'t3');
insert into teacher_table values(null,'t4');
create table student_table(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table(teacher_id)
);
insert into student_table values(null,'s1',1);
insert into student_table values(null,'s2',1);
insert into student_table values(null,'s3',2);
insert into student_table values(null,'s4',3);
### 唯一约束(UNIQUE)
- 指定表中某一列或者多列不能有重复的值
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值
- 每张表可以存在多个唯一约束的列
#### 创建唯一约束
create table t2(
name varchar(20) unique
)
create table t3(
a int,
b int,
constraint uk_name_pass unique(a)
);
create table temp(
id int not null,
name varchar(20),
password varchar(20),
constraint uk_name_pwd unique(name,password)
);
#### 删除约束
alter table temp drop index uk_name_pwd;
#### 创建表之后再添加唯一约束
alter table temp add unique uk_name_pwd(name,password);
alter table temp modify name varchar(25) unique;
### 非空约束(NOT NULL)
create table t4(
id int not null,
name varchar(25) not null default 'abc'
);
#### 表建好后,修改非空约束
alter table t4 modify id int null;
alter table t4 modify id int not null;
### 默认约束(DEFAULT)
1. 用于设定列的默认值
2. 要求
- 定义的默认值的常量,必须与这个列的数据类型,精度等相匹配
- 每个列只能定义一个default约束
create table t5(
id int ,
name varchar(20),
sex char(10) default '男'
);
insert into t5 (id,name) values(1,'aaa');
### CHECK约束
1. 检查约束的作用,验证数据
create table t6(
id int,
name varchar(20),
age int,
check(age>20)
);
insert into t6 values(1,'aaa',15);
2. mysql不支持检查约束,但是可以创建,并且不报错.只不过没有任何效果.
### 什么是事务
- 事务是一组原子性的sql查询,在事务内的语句,要么全都执行,要么全都不执行
### 事务的 ACID 性质
- 原子性:最小的不可分割的业务单元
- 一致性:都执行或者都不执行,保持同一个状态
- 隔离性:多个事务并发,相互不影响
- 持久性:commit之后,数据保存在数据库中
### MySQL事务
1. 使用事务的要求
- 在mysql众多的引擎中,innodb 和NDB Cluster支持事务
- mysql默认自动提交事务,想手动提交,需要把默认提交关闭
2. 关闭默认提交
show variables like 'autocommit';
set autocommit=0;
start transaction;
....业务逻辑....
commit;
rollback;
### 事务案例
create table account(
id int,
name varchar(20),
money float
);
insert into account values(1,'aaa',1000);
insert into account values(2,'bbb',1000);
start transaction;
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
- 此时没有输入commit,直接关闭终端
- 再次打开终端,把自动提交关闭
- 查询account账户,之前的操作回滚了
- 再次开启事务
- 完成两次update
- 输入commit--->数据真正保存在表中
二 视图
## 视图
### 视图概述
- 在数据库中，有很多对象
- 表table，视图view
- 视图是什么
- 视图也可以看作为表，但是表是真正存在的，而视图是假表，不真实存在
- 视图是表的映射，或者说是投影
- 视图是一条sql语句的查询结果集，把这个结果集当成表来使用
- VIEW本身不包含数据，他是一个DQL结果的映射
- 如果DQL结果集的基表发生数据改变，那么对应视图的数据也会随之发生变化
create table 表名（字段1 数据类型.......）;
create table 表名 as（子查询）;
create view 视图名 as (子查询（dql）);
(视图名以v_开头)
create view v_emp_dept_10 as
select empno,ename,deptno from emp where deptno=10;
select * from v_emp_dept_10;
相当于
select * from
(select empno,ename,deptno from emp where deptno=10);
- java中，如果有一个算法功能，需要反复使用，将其封装成一个方法
- 视图
是数据库对象之一，由于数据库对象名称不可以重复
所以视图的命名需要以V_开头
视图在SQL语句中，体现的角色与表相同
就是对表的操作在对视图的时候基本都可以操作
但是视图并不是一张真实存在的表，而是对应一个select查询结果集
- 视图最大的作用 ------> 重用子查询
- 视图对应的子查询字段若含有函数或者表达式，那么该字段必须指定别名
如果其他普通字段也制定了别名，那么在对视图操作时
只能通过别名找到相应字段
- 修改视图
create or replace view v_emp_3 as select * from emp;
- 视图的分类
- 简单视图
对应的子查询中，不含有关联查询，查询的字段不包括表达式，函数
没有分组，没有去重
- 复杂视图
有上述任意一点都叫作复杂视图
- 对视图进行dml操作，只针对简单视图可以使用
select * from v_emp_dept_10
insert into v_emp_dept_10 values(2001,'zs',10);
- 先插入deptno=10，查询视图，查询表，看结果----------表和视图都插入进去
- 插入deptno=20，查询视图，查询表，看结果-------------表中有，视图中无
- 数据污染v_emp_dept_10 一定要杜绝
- 如果原表某列有约束，那么不满足约束，不可以操作
- 对视图的操作就是对基表的操作，操作不当可能对基表进行数据污染
update v_emp_dept_10 set ename='lily' where deptno=20;
这句话不生效，因为在视图里看不到deptno=20的
- 对视图做update操作时，先去看where后面的条件，如果where条件在视图中可以找到
那么修改视图并且修改基表，若找不到，不修改视图不修改表
- 删除，删除是不会发生数据污染的
delete from v_emp_dept_10 where empno=10;
- with check option
为视图添加一个检查约束，可以保证对视图进行dml操作后，
必须保证你操作的数据在视图可见，否则不允许dml操作
这样就对基表避免了数据污染
create or replace view v_emp_5
as select empno,ename,deptno from emp where deptno=20
with check option;
-- 下面语句执行错误，违反检查约束
insert into v_emp_5 values(1011,'lileilei',30);
- 视图的作用
- 1.如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，
此后查询此视图即可，简化复杂查询
- 2.对基表数据的保护，限制数据访问
- 工作中对视图一般只进行DQL，不使用DML
- 删除视图
drop view 视图名;
- 删除视图不会影响基表数据
- 删除视图中的数据，会影响基表数据
- 复杂视图
创建一个含有公司部门工资情况的视图：
内容为，部门编号，部门名称，部门最高工资，部门平均工资，以及部门总工资
## 索引原理
### 索引概述
- 对于字典来说用来加快查询的技术有很多，拼音，笔画等等....
- 数据库中使用索引加快查询速度
- 如果不使用索引，mysql必须从第一条记录开始，然后读完整个表
直到找到相关的行，表越大，数据越多，花费的时间越多
- 索引可以用来改善性能，但有时候索引反而使性能降低
- 索引的统计与应用是数据库自动完成的
- 创建索引，删除索引
create index idx_emp_ename on emp(ename);
- where ename=''
- order by ename
- distinct ename的索引就会自动被使用
- 但是like不用索引
- 复合索引
create index idx_emp_job_sal on emp(job,sal);
select * from emp order by job,sal;在此时生效
- 创建表的时候直接添加索引
create table mytable(
id int,
username varchar(20),
index idx_mytable_username (username)
);
- 更改表的索引
alter table mytable add index idx_mytable_username1(username);
- 删除索引
drop index idx_mytable_username1 on mytable;
- 合理使用索引提升查询效率
1.不要在小表上建立索引 （大小指的是数据量多少）
2.不要在经常做dml操作的表上建立
3.限制表上的索引数目，索引不是越多越好
4.删除很少被使用，不合理的索引
5.为经常作为表的连接条件的列创建索引
6.为经常出现在where子句中的列创建索引
7.为经常出现在order by，distinct后面的字段建立索引
如果建立的是复合索引，
索引的字段顺序要和这些关键字后面的字段顺序一致
## MySQL约束
### 主键约束（PRIMARY KEY）
- 用来约束行不重复，任意的两行主键值都不相同，
每行都具有一个主键值，也不允许主键为null
- 创建主键约束
- 列级语法创建，约束写在了列级语法的后头
create table t1(
id int primary key,
name varchar(10)
);
- 表级语法创建
create table t2(
id int,
name varchar(10),
constraint pk_t2_id primary key(id)
);
constraint-----创建约束起名字的关键字
create table t3(
id int primary key auto_increment,
name varchar(10)
);
auto_increment 自增长
1.按最大的自增长
2.如果最大的被删了，则还是按照这个最大的自增长
被删的那个最大值不会出现
- 在建表之后，使用命令添加主键约束
create table t4(
id int,
name varchar(10)
);
alter table t4 add primary key(id);
alter table t4 modify id int primary key auto_increment;
- 删除主键约束
alter table t4 drop primary key;
- mysql这个命令不起作用
alter table t5 modify id int;
- 复合主键------>不推荐使用
### 外键约束（FOREIGN KEY）
- 一般除非特殊需求，否则只建立逻辑外键约束
- 外键约束是保证一个或两个表之间的参照完整性，保持数据一致性
- 表的外键可以是另一张表的主键，也可以是唯一的索引，
外键可以重复，可以是空值
- 实现一对一或者一对多关系
- 外键约束的要求：
- 1.要求主表和从表必须使用同一个数据库引擎engine
- 2.只要使用外键，必须使用innodb引擎
- 3.外键列和参照列必须创建索引
如果外键列不存在索引，mysql自动创建
- 4.外键列和参照列必须具有相似的数据类型，长度，或者有符号位必须相同
而字符的长度是可以不同的
- 外键的使用
-- 从表
create table class(
id int primary key auto_increment,
name varchar(10)
);
-- 主表，列级语法
create table s1(
id int primary key auto_increment,
name varchar(10),
class_id int references class(id)
);
-- 表级语法
create table s2(
id int auto_increment,
name varchar(10),
class_id int,
constraint pk_s2_id primary key(id),
constraint fk_class_id foreign key(class_id) references class(id)
);
- constraint 约束名称 约束（列名） references 表名（列名）
- 虽然mysql支持外键约束使用列级语法创建
但是这种列级语法的外键约束不会生效，只有表级语法的外键约束生效
- mysql提供列级外键约束的语法，是为了和标准sql保持兼容性
### 唯一约束（UNIQUE）
- 指定表中某一列或者多个列不能有相同的两行或者两行以上的数据存在
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值，而且空值可以有多个
- 每张表可以存在多个唯一约束
- 主要用途：用来防止数据插入的时候重复
-- 列级语法
create table t6(
id int primary key,
name varchar(20) unique
);
-- 表级语法
create table t7(
id int primary key,
name varchar(20),
unique key(name)
);
create table t8(
name varchar(20),
constraint 名 unique key(name)
);
- 复合的唯一约束
create table t9(
username varchar(20),
password varchar(20),
constraint uq_t9_name_pwd unique(username,password)
);
- 删除唯一约束
alter table t9 drop unique uq_t9_name_pwd ;
- 修改唯一约束
alter table t9 add unique 名....(字段)
alter table t9 modify name varchar(20) unique
### 非空约束（NOT NULL）
create table t10(
id int,
name varchar(20) not null
);
alter table t10 modify name varchar(20) not null;
alter table t10 modify name varchar(20) null;------取消not null
### 默认约束（DEFAULT）
create table t11(
id int,
name varchar(10),
sex char(10) default '男'
);
### CHECK约束 --mysql不支持
- 写上check约束不报错，但是没效果
create table t12(
id int,
name varchar(10),
age int,
check(age>20)
);
### 事务
### 事务的ACID性质 *****************
-- 1.原子性：业务的最小单元
-- 2.一致性：事务作为一个整体执行，
包含在其中的DML操作，要么都执行，要么都不执行
-- 3.隔离性：多个事务同时执行，每个事务不影响其他事务
-- 4.持久性：已经被提交的事务，对数据的修改真实的保存在数据库中
### MySQL事务
-- 1.必须使用innodb引擎
--2.mysql的事务默认自动开启事务提交
show variables like 'autocommit';
set autocommit=0; 0是关 1是开
### 事务案例
- 手动管理事务（aaa跟bbb借钱）
create table account(
id int,
name varchar(10),
money double
);
insert into account values (1,'aaa',1000);
insert into account values (2,'bbb',1000);
-- 关闭自动提交
set autocommit=0;
-- 转账失败
start transaction;-------开启事务
update account set money=money-100 where name='aaa';
update account set money=money+100 where name='bbb';
//commit; 不写commit的话白干
rollback;
1815
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
