2407-mysql笔记

数据库(Database)，简称db
mariadb=mysql

常见的数据库：mysql、oracle、高斯（Gauss）、redis、sqlserver、SQLite、HBase

一、SQL（Structured Query Language）：结构化查询语言

1、作用：用于访问和处理数据库的标准计算机语言

2、语法特点：

（1）SQL对关键字的大小不敏感（针对windows）

（2）SQL语句可以单行或者多行书写，每行以分号结束

（3）SQL注释

3、mysql基本操作-ddl

（1）对数据库的基本操作

（2）对数据库的常用操作-创建表

创建表格式：

create table [if not exists] 表名 (
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的一些设置];

create table emp(
	eid int(1) comment '员工编号',
	ename varchar(1) comment '员工姓名'
);

insert into emp values 
(-2147483648,'A'),
(21333,'B');

select * from emp;

注：
（1）常见的数据类型有：
①数值类型

tinyint范围：[0,255]
int范围：[-2147483648,2147383647]

②日期和时间类型

③字符串类型’
char-定长
varchar-变长
如果是字符串类型，会有宽度的限制，宽度填多少就代表字符串有多长

如果是数值类型，比如INT(1)，1不代表数据的长度，如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然显示出来

（3）对表结构的常用操作

show tables;

show create table emp;

desc emp;

drop table emp;

4、mysql数据库基本操作-dml

DML是指数据操作语言，Data Mainpulation Language，用于对数据库表中记录进行更新、删除、插入等操作
关键字：
insert-插入
delete-删除
update-更新

（1）数据插入

语法格式：
#向表中指定某些列插入数据
insert into 表名 (列名1,列名2,列名3...)
values (值1,值2,值3...);

#向表中所有列插入数据
insert into 表名 values (值1,值2,值3...);

#向student表中sid name gender age birth 字段添加数据
insert into student
(sid,name,gender,age,birth)
values
(1001,'张三','男',20,'1994-12-01');

#向student表中所有字段添加数据
insert into student
values 
(1002,'赵敏','女',18,'1995-12-03','上海');

#向student表中所有字段添加多行数据
insert into student
VALUES
(1003,'张三丰11','男',88,'1918-08-13','北京'),
(1004,'张三丰111','男',36,'1983-7-11','北京');

注：以上两种格式均可，SQL语句以分号结尾，可以在一条insert语句中同时插入多个列表值，values只需要写一次，括号之间用逗号隔开

（2）数据修改

语法格式：
update 表名 set 字段名 = 值,字段名 = 值...;

update 表名 set 字段名 = 值,字段名 = 值... where 条件;

#将所有学生的地址修改为重庆
update student set address = '重庆';

#将sid为1004的学生地址修改为北京
update student set address = '北京' where sid = 1004;

#将sid为1004的学生地址修改为beijing并且性别修改为男
update student 
set address = 'beijing',gender = '男'
where sid = 1004;

（3）数据删除

语法格式：
delete from 表名 [where 条件];

truncate table 表名
或者
truncate 表名

#删除sid为1004的学生数据
delete from student where sid = 1004;

#删除表所有数据
delete from student;

#再添加两条数据
insert into student
values 
(1003,'张三丰11','男',88,'1918-08-13','北京'),
(1004,'张无忌1','男',36,'1983-07-11','北京');

#清空表数据
truncate student;

注：delete和truncate原理不同，delete只删除内容，而truncate类似drop table再create table，可以理解将整个表删除，然后再创建该表

5、mysql约束

何为约束，constraint，约束实际上就是表中数据的限制条件
作用：为了保证表中记录的完整性和有效性，比如用户表的手机号不能为空，身份证号不能重复

分类：
（1）主键约束（primary key） PK
（2）自增长约束（auto_increment）
（3）非空约束（not null）
（4）唯一性约束（unique）
（5）默认约束（default）
（6）零填充约束（zerofill）
（7）外键约束（foregin key）FK

（1）主键约束

可以加到某个列上，也可以加到多个列上

①概念：

a.主键约束相当于唯一约束和非空约束的组合，即主键约束的列不允许重复，也不允许出现空值
b.每个表最多允许一个主键

②添加单列主键

a.在定义字段的同时指定主键

语法格式：
create table 表名(
	...
	<字段名> <数据类型> primary key,
	...
)

INSERT INTO emp1
(name,dept_id,salary)
VALUES
('张三',8801,3600);
#Field 'eid' doesn't have a default value
#设置了主键，该字段必须要给值

insert into emp1 
values 
(60051,'张三丰',5003,5000)

b.在定义完字段之后指定主键

语法格式：
create table 表名(
	...
	[constraint <约束名>] primary key(字段名)
);

create table emp2(
	eid int comment '编号',
  name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '工资',
	constraint pk1 primary key(eid)
);

#主键测试emp2
#主键=唯一+非空组合
insert into emp2 VALUES
(1001,'张安',10,6000);

insert into emp2 values
(1002,'杰森',40,6000);

insert into emp2 values 
(NULL,'杰森',40,6000);

insert into emp2 (name,dept_id,salary) values
('杰森',40,6000);

③添加联合主键(多列主键)

语法格式：
create table 表名 (
	...
	primary key (字段1,字段2,....字段n)
)

create table emp3 (
	name varchar(20) comment '姓名',
	dept_id int comment '部门编号',
	salary double comment '薪资',
	constraint ndpk primary key (name,dept_id)
)

insert into emp3 VALUES
('111',10,5000),
('112',10,6000);

insert into emp3 values
('113',10,7000),
('111',20,8000);

insert into emp3 VALUES
('114',10,9000),
('113',20,10000);

注：多个键作为主键时里面列的值不能完全相同，允许其中几个相同，也不允许出现空值

（2）自增长约束

概念：当字段设置为自增长约束后，在插入数据时，不需要用户输入数据，而由数据库系统根据定义自动赋值，每增加一条记录，该字段会以相同的步长进行增长，一般是跟主键一块搭配使用

语法格式：
create table 表名 (
	...
	字段名 数据类型 auto_increment
	...
)

#创建madb1数据库
create database if not exists madb1 charset = utf8;

create table t_user1(
	id int primary key auto_increment,
	name varchar(20)
);

insert into t_user1 VALUES
(NULL,'张三');

insert into t_user1 (name) VALUES ('李四');

①特点

a.auto_increment的初始值是1，每新增一条记录，字段值自动嘉1
b.一个表中只能有一个字段使用auto_increment
c.auto_increment约束的字段必须具备NOT NULL 属性
d.支持的类型（TINYINT、SMALLINT、INT、BIGINT）等
e.如果最大值达到上限，auto_increment就会失效

②指定自增字段的初始值

方式1：创建表时指定
语法格式：
creat table 表名(
	... primary key auto_increment,
	...
) auto_increment = 10;

#指定自增字段的初始值-方式1
create table t_user2 (
	id int primary key auto_increment,
	name varchar(20)
) auto_increment = 100;

insert into t_user2 values
(NULL,'aaa'),
(NULL,'bbb');

③delete和truncate区别

delete数据之后自动增长从断点开始
truncate数据之后自动增长是从默认起始值开始

（3）非空约束

①添加非空约束

语法格式：
create table 表名 (
	字段名 数据类型 not null,
	...
);

create table t_user6 (
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);

insert into t_user6 (id) values (1001); #不可以
insert into t_user6 (id,name,address) values (1001,NULL,NULL); #不可以
insert into t_user6 (id,name,address) values (1001,'NULL','NULL'); #可以
insert into t_user6 (id,name,address) values (1001,'',''); #可以

注：单纯的NULL才表示空值，加上引号会变成字符串

（4）唯一性约束

唯一约束（unique key）是指所有记录中的字段值不能重复，但是唯一约束的列可以为NULL

①添加唯一约束

语法格式：
create table 表名(
	字段名 字段类型 unique,
	...
)

create table t_user8 (
	id int,
	name varchar(20),
	phone_number varchar(20) unique
);

#测试唯一性约束
insert into t_user8 values
(10,'aaa','13111112222');

insert into t_user8 VALUES
(11,'aab','13111112222');

insert into t_user8 values
(12,'aac',NULL);

insert into t_user8 VALUES
(13,'aad',NULL);

insert into t_user8 (id,name) VALUES
(14,'aba');

#NULL和任何值都不相同，NULL!=NULL

（5）默认约束

默认值约束用来指定某列的默认值

①添加默认约束

语法格式：
create table 表名(
	字段名 字段类型 default 默认值,
	....
)

create table t_user10 (
	id int,
	name varchar(20),
	address varchar(20) default '北京'
);

insert into t_user10 (id,name)
values (10,'aac'),(11,'ace');

（6）零填充约束

zerofill：
①插入数据时，当该字段的值长度小于定义的长度时，会在该值的前面补上相应的0
②zerofill默认为int(10)

①添加零填充约束

语法格式：
create table 表名 (
	字段名 类型 zerofill,
	...
)

create table t_user11 (
	id INT zerofill,
	name varchar(20)
);

insert into t_user11 VALUES
(31,'aac'),(32,'ddd');

6、MySQL基本操作-DQL

DQL：Data Query Language，数据查询语言

语法格式：

select 
	[all|distinct]
	<目标列的表达式1> 别名1,
	<目标列的表达式2> 别名2...
from <表名> 别名,表名 别名
[where 条件表达式]
[group by 列名]
[having 条件表达式]	
[order by 列名 [asc|desc]]
[limit 数字1,数字2]

简化版语法

select *|列名 from 表名 where 条件;

create database if not exists mydb2;

（1）运算符

①算术运算符

select 5+2;

select 5-2;

select 5*2;

select 5/2;
select 5 DIV 2;

select 2%5;
select 2 MOD 5;

#将每件商品的价格加10元
select pname,price,price+10 from product;

#将每件商品的价格上调10%
select pname,price,price*1.1 from product;
select pname,price,price+price*0.1 from product;

注：乘法可能有运算结果不精确问题

②比较运算符

#使用least求最小值
select least(10,20); #10
select least(10,30,20); #10
select least(10,null,30); #null

#使用greatest求最大值
select greatest(10,20,30); #30
select greatest(10,null,30); #null

#1、查询商品名称为'海尔洗衣机'的商品的所有信息
#*代表全部字段
select * from product where pname = '海尔洗衣机';

#2、查询商品价格为800的商品
select * from product where price = 800;

#3、查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where price not in (800);
select * from product where not (price = 800);

#4、查询商品价格大于等于60元的所有商品信息
select * from product where price >= 60; 
select * from product where not (price < 60); 

#5、查询商品价格在200到1000之间(包含200和1000)的所有商品
select * from product where price >= 200 and price <= 1000;
select * from product where price between 200 and 1000;

#6、查询商品价格时200或800的所有商品
#or或||
select * from product where price = 200 or price = 800;
select * from product where price = 200 || price = 800;
select * from product where price in (200,800);

#7、模糊查询 LIKE
# %	用来匹配多个字符
# _ 用来匹配单个字符
#查询含有裤字的所有商品
select * from product where pname LIKE '%裤%';

#查询以海字开头的所有商品
select * from product where pname LIKE '海%';

#查询第二个字是蔻字的商品
select * from product where pname like '_蔻%';

#8、查询category_id为null的商品
select category_id,pname from product where category_id = null; #不可以
#原因：查询一条数据为空不能用=判断，需要用is
select category_id,pname from product where category_id is null;

#9、查询category_id不为null的商品
select category_id,pname from product where category_id is not null;

（2）排序查询

语法格式：
select 字段名1,字段名2...
from 表名 
order by 字段名1 [asc|desc],字段名2 [asc|desc]...

特点：
①asc代表升序，desc代表降序，如果不写默认是升序
②order by用于子句中可以支持单个字段或多个字段
③order by子句，可以放在查询语句的最后面，LIMIT子句除外

#1、查询商品信息并根据价格进行降序排序
select * from product order by price desc;

#2、在一条的前提上，对商品编号升序排序
select * from product order by price desc,category_id asc;

#3、显示商品的价格，把重复(distinct)的价格去掉，并按价格降序排序
select distinct price from product order by price desc;

（3）聚合查询

之前所做的查询都是横向查询，而聚合查询是属于纵向查询，另外聚合函数会忽略空值的计算

#查询商品的总数，count尽量使用count(*)，不要使用count(具体字段)
select count(*) from product;

#分别查询商品价格的总和、最大值、最小值、平均值
select sum(price) from product;

select max(price) from product;

select min(price) from product;

select avg(price) from product;

（4）分组查询

使用group by子句对查询出来的信息进行分组

语法格式：
select 字段1,字段2...
from 表名
group by 分组字段
having 分组条件;

#1、统计商品分类的个数
select category_id,count(*) from product group by category_id;

select category_id,pname,count(*) from product group by category_id;

select category_id,count(*),price from product group by category_id;

注：如果要进行分组的话，则在SELECT子句之后，只能出现分组的字段和统计函数，其他的字段尽量不要出现，避免查询结果错误

对分组的结果进行筛选的话要使用having，不能使用where

#2、统计商品分类的个数，且只显示个数大于4的信息
select category_id,count(*) from product
group by category_id having count(*) > 4;

练习

use mydb2;

create table stu(
	id int primary key auto_increment,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int
);

insert into stu values
(null,'张明','男',89,78,90),
(null,'李进','男',67,53,95),
(null,'王五','女',87,78,77),
(null,'李一','女',88,98,92),
(null,'李财','男',82,84,67),
(null,'张宝','男',55,85,45),
(null,'黄蓉','女',75,65,30),
(null,'郭襄','女',85,72,91);

#1、查询stu表中所有学生的信息
select * from stu;

#2、查询表中所有学生的姓名和对应的英语成绩
select name,english from stu;

#3、过滤表中的重复数据
select distinct * from stu;

#4、统计每个学生的总分
select chinese+english+math,name from stu;

#5、在所有学生总分数上加10分特长分
select chinese+english+math+10,name from stu;

#6、查出每个学生的各科成绩，并用别名(as)表示，as可省略
select name,chinese as '语文成绩',math as '数学成绩',
english  '英语成绩' from stu;

#7、查询英语成绩大于90分的同学信息
select * from stu where english > 90;

#8、查询总分大于200分的所有同学信息
select *,chinese+english+math from stu 
where chinese+english+math > 200;

#9、查询英语分数在80-90(包含80和90)之间的同学信息
select * from stu where english between 80 and 90;
select * from stu where english >=80 and english <= 90;

#10、查询英语分数不在80-90之间的同学信息
select * from stu where english < 80 or english > 90;
select * from stu where english < 80 || english > 90;
select * from stu where english not between 80 and 90;
select * from stu where english not in (80,81,82,83,84,85,86,87,88,89,90);

#11、查询数学分数为89,90,91的同学信息
select * from stu where math in (89,90,91);
select * from stu where
math = 89 or math = 90 or math = 91;

#12、查询所有姓李的学生英语成绩
select name,english from stu where name like '李%';

#13、查询数学分数为80并且语文分数为80的同学信息
select name,math,chinese
from stu
where math = 80 and chinese = 80;

#14、对数学成绩降序排序后输出
select * from stu order by math desc;

#15、对总分降序排序后输出
select *,math+english+chinese 总分
from stu
order by 总分 desc;

#16、对姓李的学生总成绩升序的输出
select name,math+english+chinese 总分
from stu where name like '李%'
order by 总分;

#17、查询男生和女生的人数，并降序输出，
#且只显示性别人数>4的数据
select count(*),gender from stu
group by gender
having count(*) > 4
order by count(*) desc;

（5）分页查询-limit

语法格式：
#方式1： n表示显示多少条数据
select 字段1,字段2,... from 表名 limit n;

#方式2：
#m：(当前页面值-1) * 显示的条数
#n：显示的条数
select 字段1,字段2,... from 表名 limit m,n;

#查询product表的前5条记录
select * from product limit 5;

#查询product表第3页数据，每页显示5条
select * from product limit 10,5;

#查询product表第1页数据，每页显示17条
select * from product limit 0,17;
select * from product limit 17;

（6）INSERT INTO SELECT语句

将一张表的数据导入到另一张表中

语法格式：
insert into 表名2(字段名1,字段名2...) select value1,value2,... from 表名1;
或
insert into 表名2 select * from 表名1;

create table product2(
	pname varchar(20),
	price double
)

insert into product2 (pname,price)
select pname,price from product;

7、mysql多表操作

多表关系：一对一，一对多/多对一，多对多

（1）一对一关系

一个学生只有一个身份证号，一个身份证号只能对应一个学生

（2）一对多/多对一

部分和员工

一个部分有多个员工，一个员工也有对应多个部分

（3）多对多关系

一个学生可以选择多门课程，一个课程也可以被多个学生选择

（4）外键约束

foreign key

①外键约束：一对多/多对一

语法格式：
create table 表名 (
	字段名1 类型,
	...
	[constraint <外键名>] foreign key (字段名) references 主表名 (字段名)
);

create table if not exists emp (
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	did varchar(20),
	#从did指向主表dept的dept_no字段
	constraint emp_fk foreign key (did)
	references dept (deptno)
);

insert into dept 
values (1001,'研发部'),
(1002,'销售部'),
(1003,'财务部');

insert into emp values
(1,'乔峰',20,1001),
(2,'段誉',21,1001),
(3,'虚竹',23,1001),
(4,'阿紫',18,1002),
(5,'扫地僧',35,1002),
(6,'李秋水',33,1003),
(7,'鸠摩智',50,1003);

#删除deptno=1001的部门
delete from dept where deptno = 1001;

#删除deptno=1001的部门
delete from dept where deptno = 1001;

#将emp表下面的1001的员工编号全部修改为1003
update emp set did = 1003 where did = 1001;

#删除deptno=1001的部门，此时没有1001的员工了
delete from dept where deptno = 1001;

外键约束：多对多关系

#创建学生表
create table student(
	sid int primary key,
	name varchar(4),
	age int,
	gender varchar(1)
);

#创建课程表
create table course (
	cid int primary key,
	cname varchar(20)
);

#创建中间表score
create table score (
	sid int,
	cid int,
	score double,
	constraint fk1 foreign key (sid) references student(sid),
	constraint fk2 foreign key (cid) references course(cid)
);

#给学生表(主表)添加数据
insert into student values
(1,'小龙女',18,'女'),
(2,'阿紫',19,'女'),
(3,'张无忌',20,'男');

#给课程表(主表)添加数据
insert into course VALUES
(1001,'语文'),
(1002,'数学'),
(1003,'英语');


#给中间表(从表)添加数据
insert into score values 
(1,1001,75),
(1,1002,83),
(2,1001,67),
(2,1003,80),
(3,1002,90),
(3,1003,80);

#删除score表中sid=2,cid=1003的数据
delete from score where sid = 2 and cid = 1003;

#删除student表中sid=2的数据
delete from student where sid=2;

#删除course表中cid=1001的数据
delete from course where cid=1001;

注：
往从表添加数据时，先要确保主表关联数据的存在；
删除从表数据时，可以正常删除，删除主表数据时，先要将从表相关数据先删除

（5）多表操作之联合查询

create table if not exists dept3(
	deptno varchar(20) primary key,
	name varchar(20)
);

create table if not exists emp3(
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	dept_id varchar(20)
);

insert into dept3 values 
('1001','研发部'),
('1002','销售部'),
('1003','财务部'),
('1004','人事部');

insert into emp3 values
('1','乔峰',20,'1001'),
('2','段誉',21,'1001'),
('3','虚竹',23,'1001'),
('4','阿紫',18,'1001'),
('5','扫地僧',85,'1002'),
('6','李秋水',33,'1002'),
('7','鸠摩智',50,'1002'),
('8','天山童姥',60,'1003'),
('9','慕容博',58,'1003'),
('10','丁春秋',71,'1003');

①笛卡尔积查询（交叉连接）

a.返回连接的两张表的所有行
b.可以理解为一张表的每一行去和另一张表的任意进行匹配
c.假设A有m条数据，B表里面有n条数据，笛卡尔积的结果是m*n条数据

语法格式：
select * from 表1,表2,表3...

select * from dept3,emp3;

②内连接查询(inner join或join)：求两张或多张表的交集部分

语法格式：
select * from A inner join B on 条件;

select * from emp3 inner join dept3
on emp3.dept_id = dept3.deptno;

select * from emp3 join dept3
on emp3.dept_id = dept3.deptno;

select * from dept3,emp3
where dept3.deptno = emp3.dept_id;

③外连接查询（outer join或者join）

外连接分为：左外（left join）、右外（right join）、全外（full join）

格式：
左外连接：
select * from A表 left join B表 on 条件;
右外连接
select * from A表 right join B表 on 条件;
全外连接	
select * from A表 full join B表 on 条件;

#查询哪些部门有员工，哪些部门没有员工
select * from dept3 left join emp3
on dept3.deptno = emp3.dept_id;
#等价于
select dept3.*,emp3.* from emp3 right join dept3
on dept3.deptno = emp3.dept_id;

#查询员工对应的部门信息
select * from emp3 left join dept3
on emp3.dept_id = dept3.deptno;
#等价于
select emp3.*,dept3.* from dept3 right join emp3
on emp3.dept_Id = dept3.deptno;

#mysql全外连接，可以使用左连接 union 右连接 代替
select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id
UNION
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;

select * from dept3
UNION
select * from dept3;

select * from dept3
UNION ALL
select * from dept3;

④子查询（嵌套查询）

语法格式：
select * from where 条件 [嵌套查询语句];

#1、查询emp3里面的最大年龄
select max(age) from emp3;

#2、查询出和最大年龄相等的员工信息
select * from emp3
where age = (select max(age) from emp3);

#3、查询研发部20岁以下的员工信息，包括员工号，员工名字，部门名字
#第一种方式
select eid,ename,NAME
from 
(select deptno,name from dept3 where name = '研发部') as a,
(select eid,ename,dept_id from emp3 where age < 20) as b
where a.deptno = b.dept_id;

#第二种方式
select eid,ename,NAME
from 
(select deptno,name from dept3 where name = '研发部') as a 
join
(select eid,ename,dept_id from emp3 where age < 20) as b
on a.deptno = b.dept_id;

#4、查询研发部和销售部的员工信息，包含员工号，员工名字
select dept_id,ename
from emp3
where
dept_id in 
(select deptno from dept3 where name = '研发部'
or name = '销售部');

⑤自查询（关联查询）

语法格式：
select 字段列表 from 表1 as a,表1 as b where 条件;
或
select 字段列表 from 表1 as a join 表1 as b on 条件;

#查询出研发部下面的员工信息(eid,ename,age) 部门名称
#第一种方式
select eid,ename,age,name
from dept3,emp3
where name = '研发部' and deptno=dept_id;

#第二种方式
select eid,ename,age,name
from dept3 join emp3
on name = '研发部' and deptno=dept_id;

create database test1 charset = utf8;

use test1;

create table dept(
deptno int PRIMARY KEY,
dname varchar(14),
loc varchar(13)
);

insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');

CREATE TABLE emp(
    empno INT primary key,
    ename VARCHAR(10),
    job VARCHAR(9),
    mgr INT,
    hiredate DATE,
    sal double,
    comm double,
    deptno INT
);

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

CREATE TABLE  salgrade
(
 grade INT PRIMARY KEY,
 losal INT,
 hisal INT);

INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);

#添加部门和员工之间的主外键关系
alter table emp add constraint foreign key
emp(deptno) references dept(deptno);

#1、查询拥有部门名称和部门编号的员工
select ename,dname,dept.deptno
from emp,dept
where emp.deptno is not null
and emp.deptno = dept.deptno;

#2、查询工资比SMITH高的员工信息
#子查询
select * from emp where 
sal >
(select sal from emp where ename = 'SMITH');

#自查询
select a.* from emp a,emp b
where b.ename = 'SMITH'
and a.sal > b.sal;

select a.* from emp a join emp b
on b.ename = 'SMITH'
and a.sal > b.sal;

#3、查询员工编号、姓名和所属上级的编号、姓名
select a.empno,a.ename,a.mgr,b.ename
from
emp a,emp b
where a.mgr = b.empno;

#4、查询员工的入职日期早于其上级入职日期的员工编号、
#姓名和所属上级姓名
select a.empno,a.ename,a.mgr,a.hiredate,b.ename,b.hiredate
from
emp a,emp b
where a.mgr = b.empno
and a.hiredate < b.hiredate;

8、存储过程

（1）定义

存储过程基本语法格式：
delimiter $
	create procedure 数据库名.存储过程名([in 变量名 类型,out 参数,...])
		begin
			[declare 变量名 类型 default 值;]
			存储过程语句块;
		end$	
delimiter;			

①存储过程参数主要是in（输入）、out（输出）、inout（输入和输出）
②存储过程语句必须要包含在begin和and之间
③declare用来声明变量，变量默认赋值default，如果想要赋值，使用SET 变量 = 值;

（2）存储过程使用

①定义一个存储过程

delimiter $
	create procedure mydb4.demo1()
	BEGIN
		declare de_name varchar(10) default '';
		set de_name = 'jim';
		select de_name;
	END$
delimiter;

#调用存储过程
call demo1();

②定义一个有参的存储过程

#查询emp表中job为SALESMAN的人数
delimiter @@!
create procedure 
mydb4.demo3(IN sjob VARCHAR(10),OUT jobcount INT)
BEGIN
	select count(*) INTO jobcount 
	FROM emp where job = sjob;
	select jobcount;
end @@!
delimiter;

#调用demo3
call demo3('SALESMAN',@jobcount);

③定义一个流程控制语句IF ELSE

delimiter @@!
create procedure mydb4.demo4(IN day INT)
BEGIN
	if day = 0
	THEN SELECT '星期天';
	elseif day = 1
	THEN select '星期一';
	elseif day = 2
	THEN select '星期二';
	else select '无效日期';
	END if;
END @@!
delimiter;

call demo4(2);

④定义一个条件控制语句CASE

第一种方式

delimiter @@!
create procedure demo5(IN num INT)
BEGIN
	CASE	#条件开始
	WHEN num < 0 THEN SELECT '负数';
	WHEN num > 0 THEN SELECT '正数';
	ELSE SELECT '不是正数也不是负数';
	END CASE; #条件结束
END @@!
delimiter;

call demo5(-10);

第二种方式

delimiter @@!
create procedure demo6(IN num INT)
BEGIN
	CASE num#条件开始
	WHEN 1 THEN SELECT '输入为1';
	WHEN 0 THEN SELECT '输入为0';
	ELSE SELECT '不是1也不是0';
	END CASE; #条件结束
END @@!
delimiter;

call demo6(0);

⑤定义一个循环语句WHILE

delimiter @@!
create procedure demo7(IN num INT,OUT sum INT)
BEGIN
	SET SUM = 0;
	WHILE num < 10 DO #循环开始
		SET num = num+1;
		SET SUM = SUM+num;
	END WHILE; #循环结束
END @@!;
delimiter;

call demo7(0,@sum);

select @sum;

⑥定义一个循环语句LOOP

执行过程中可以使用LEAVE或者ITEREATE来跳出循环，也可以嵌套IF等判断语句

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num <= 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;

CALL demo8(0,@sum);

SELECT @sum;

⑦定义一个循环语句REPEAT UNTLL

delimiter @@!
create procedure demo9(IN num INT,OUT sum INT)
BEGIN
	set sum = 0;
	repeat
	set num = num+1;
	set sum = sum+num;
	until num >=10 end repeat;
END @@!
delimiter;

call demo9(0,@sum);
select @sum;

9、day05下午练习单表、多表查询

CREATE DATABASE dept_emp charset = utf8;

USE dept_emp;    

CREATE TABLE dept (
	deptno INT(2) NOT NULL COMMENT '部门编号',
	dname VARCHAR (15) COMMENT '部门名称',
	loc VARCHAR (20) COMMENT '地理位置' 
);

-- 添加主键
ALTER TABLE dept ADD PRIMARY KEY (deptno);

-- 添加数据
INSERT INTO dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');
INSERT INTO dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');
INSERT INTO dept (deptno,dname,loc)VALUES (30,'销售部','长安区');
INSERT INTO dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');






CREATE TABLE emp(
	empno INT(4) NOT NULL COMMENT '员工编号',
	ename VARCHAR(10) COMMENT '员工名字',
	job VARCHAR(10) COMMENT '职位',
	mgr INT(4) COMMENT '上司',
	hiredate DATE COMMENT '入职时间',
	sal INT(7) COMMENT '基本工资',
	comm INT(7) COMMENT '补贴',
	deptno INT(2) COMMENT '所属部门编号'
);

-- 添加主键
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 添加外键约束
ALTER TABLE emp ADD CONSTRAINT f_ed_key
FOREIGN KEY (deptno) 
REFERENCES dept(deptno);

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');
INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');
INSERT INTO `emp` VALUES('7521','李兴','售货员','7698','1995-07-22','1250','500','30');
INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');
INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');
INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');
INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');
INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');
INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');
INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');
INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');
INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');
INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');
INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');



CREATE TABLE salgrade(
grade INT (10) COMMENT '工资等级',
losal INT (10) COMMENT '最低限额',
hisal INT (10) COMMENT '最高限额'
);

INSERT INTO salgrade (grade, losal, hisal)VALUES (1, 700, 1200);
INSERT INTO salgrade (grade, losal, hisal)VALUES (2, 1201, 1400);
INSERT INTO salgrade (grade, losal, hisal)VALUES (3, 1401, 2000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (4, 2001, 3000);
INSERT INTO salgrade (grade, losal, hisal)VALUES (5, 3001, 9999);

#1、查询部门编号为30的员工编号、姓名
select empno,ename
from emp
where deptno = 30;

#2、列出所有办事员的姓名、编号、部门编号
select ename,empno,deptno 
from emp
where job = '办事员';

#3、查询补贴高于薪资的员工
select * from emp where comm > sal;

#4、查询没有补贴的员工
select * from emp where 
comm is null or comm = 0;

#5、查询补贴高于薪资60%的员工
select * from emp
where comm > sal * 0.6;

#6、查询部门10中所有人事部长和部门20中所有办事员的详细信息
select * from emp
where deptno = 10 and job = '人事部长'
or deptno = 20 and job = '办事员';

#7、查询有补贴的员工
select * from emp where comm is not null and comm > 0;

#8、查询没有补贴或者补贴低于100的员工
select * from emp where comm is null
or comm < 100;

#9、查询姓张的员工信息
select * from emp where ename like '张%';

#10、显示员工的姓名和入职日期，新的员工排在前面
select ename,hiredate from emp
order by hiredate desc;

#11、查询员工的信息，按工作降序排序，若工作相同按薪资升序排序
select * from emp 
order by job desc,sal;

#12、查询不姓刘的员工信息
select * from emp where ename not like '刘%';

#13、查询出每一位员工的编号、姓名、职位、部门名称、位置
select empno,ename,job,dname,loc
from emp join dept
on emp.deptno = dept.deptno;

select empno,ename,job,dname,loc
from emp,dept
where emp.deptno = dept.deptno;

select empno,ename,job,dname,loc
from emp left join dept
on emp.deptno = dept.deptno;

#14、查询每一位员工的姓名、职位、上级姓名
select a.ename,a.job,b.ename
from emp a left join emp b
on a.mgr = b.empno;

#15、查询在部门"销售部"工作的员工的姓名
select ename from emp 
where deptno = 
(select deptno from dept where dname = '销售部');

#16、列出与'李兴'从事相同工作的员工
select * from emp
where job = 
(select job from emp where ename = '李兴');

select a.* from
emp a,emp b
where b.ename = '李兴' and a.job = b.job;

#17、列出薪资高于在部门30工作的所有员工薪资的姓名和薪资
select ename,sal 
from emp 
where sal > 
(select max(sal) from emp where deptno = 30);

#18、列出入职日期早于上级入职日期的员工
select a.*,b.hiredate
from emp a,emp b
where a.mgr = b.empno and a.hiredate < b.hiredate;

#19、查询出每个部门的人数、平均工资
select count(*),avg(sal)
from emp
group by deptno;

#20、查询每个职位的最高工资和最低工资、职位名称
select max(sal),min(sal),job
from emp
group by job;