# not null 非空 | null 空
create table t01(
id int,
name char(16) null
);
insert into t01(id) values(1); # name为空 默认值为null
create table t01(
id int,
name char(16) not null
);
insert into t01(id,name) values(1,"allen")
# name不能空 规定不能为空,可以不传值但是需要设置一个默认值
十八、mysql约束条件之default
# default 默认值
create table t01(
id int,
name char(16),
gender enum("male","female") not null default "male"
);
insert into t01(id,name) values(1,"allen"); # gender不传值默认为male
# 通常not null 和 default 一起使用
十九、mysql约束条件之unique
# unique :唯一
create table t01(
id int unique,
name char(16)
);
insert into t01 values(1,"allen");
insert into t01 values(2,"kevin");
# 联合唯一unique(字段1,字段2)
create table t01(
id int unique,
ip char(15),
port int,
unique(ip,port)
);
insert into t01 values(1,"127.0.0.1",3306); # ip和端口联合唯一
insert into t01 values(2,"127.0.0.2",3306);
二十、mysql约束条件之primary key
# primary key => 不为空且唯一
create table t01(
id int primary key,
name char(16)
);
insert into t01(1,"allen")
# primary key 单从约束角度去看,primary key就等同于 unique not null
1.一张表中必须有,并且只能有一个主键
# 有几种情况:
1.如果没有设置primary key,从上到下找到一个不为空且唯一的字段设置为主键
2.如果从上到下没有primary key也没有找到不为空且唯一的字段,mysql默认使用隐藏的字段设置为主键来组织数据
3.通常一张表中有一个id字段,而且通常应该把id设置为主键
create table t01(
id int,
name char(16) not null unique, # 该字段别识别设置为主键字段
uuid char(32) not null unique
)engine=innodb;
# 联合主键
create table t01(
ip char(15),
port int,
primary key(ip,port)
)engine=innodb;
# 把ip和port联合作为主键
二十一、mysql约束条件之auto_increment
# auto_increment 自增
create table t01(
id int primary key auto_increment,
name char(16)
)engine=innodb;
# primary key和auto_increment一起使用,在插入数据值时我们可以省略id字段默认自增1
insert into t01(name) values("allen");
# 注意
# auto_increment必须指定给被定义成key的字段使用
二十二、mysql约束条件之foreign key
# foreign key 外键 建立表和表之间关系
create table dep(
id int primary key auto_increment,
name char(64),
comment text
)engine=innodb;
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
)engine=innodb;
# 约束一:在创建表时,先建被关联的表depm,才能建关联表emp
# 约束二:在插入记录时,必须先插入被关联表的记录,才能插入关联表的记录
# 约束三:更新和删除都需要考虑到关联于被关联的关系
insert into dep(name,comment) values('teacher','hello teacher');
insert into dep(name,comment) values('student','hello student');
insert into emp(name,age,dep_id) values('allen',18,1);
insert into emp(name,age,dep_id)values('kevin',18,2);
insert into emp(name,age,dep_id) values('collins',18,2);
# 同步更新于同步删除
create table dep(
id int primary key auto_increment,
name char(16),
comment text
)engine=innodb;
create table emp(
id int primary key auto_increment,
name char(16),
age int,
dep_id int,
foreign key(dep_id) references dep(id) on update cascade on delete cascade
)engine=innodb;
# 以后删除或更新被关联表的记录对应关联表多关联的记录也会被删除或更新
# 两张表之间是一个双向的多对一的关系,称之为多对多
# 建立第三章表,表中有一个字段fk左表的id,表中有一个字段fk右表的id
create table author(
id int primary key auto_increment,
name char(16),
age int
)engine=innodb;
create table book(
id int primary key auto_increment,
name char(16),
price decimal(5,2)
)engine=innodb;
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) on update cascade on delete cascade,
foreign key(book_id) references book(id) on update cascade on delete cascade
)engine=innodb;
二十五、mysql之一对一
id
name
phone
qq
1
allen
110
110
2
kevin
120
120
3
collins
119
119
4
mark
114
114
id
class_name
customer_id
1
python01
1
2
linux01
2
3
python01
3
4
linux02
4
# 一对一只需要在外键字段加上unique唯一约束
# 左表的一条记录唯一对应右表的一条记录,反之也是一样的
create table customer(
id int primary key auto_increment,
name char(16),
phone char(11),
qq char(10)
)engine=innodb;
create table student(
id int primary key auto_increment,
class_name char(16),
customer_id int unique,
foreign key(customer) references customer(id) on update cascade on delete cascade
)engine=innodb;
二十六、mysql之关联表练习
# 查找表的关联关系使用sql语句创建
# 1
账号信息表 用户组 主机表 主机组
create table usergroup(
id int primary key auto_increment,
username char(16) unique,
password char(16)
)engine=innodb;
create table user(
id int primary key auto_increment,
name char(16),
)engine=innodb;
create table hostgroup(
id int primary key auto_increment,
name char(16)
)engine=innodb;
create table host(
id int primary key auto_increment,
ip char(15) not null default,
)engine=innodb;
create table user2usergroup(
id int primary key auto_increment,
user_id int,
usergroup_id int,
foreign key(user_id) references d1.user(id) on update cascade on delete cascade,
foreign key(usergroup_id) references d1.usergroup(id) on update cascade on delete cascade
)engine=innodb;
create table host2hostgroup(
id int primary key auto_increment,
host_id int,
hostgroup_id int,
foreign key(host_id) references d1.host(id) on update cascade on delete cascade,
foreign key(hostgroup_id) references d1.host(id) on update cascade on delete cascade
)engine=innodb;
s
# 2
班级表 学生表 老师表 课程表 成绩表
create table class(
id int primary key auto_increment,
name char(16)
)engine=innodb;
create table student(
id int primary key auto_increment,
name cahr(16),
age int,
class_id int,
foreign key (class_id) references class(id) on update cascade on delete cascade
)engine=innodb;
create table teacher(
id int primary key auto_increment,
name char(16),
age int,
gender enum("male","female"),
)engine=innodb;
create table course(
id int primary key auto_increment,
name char(16),
teacher_id int,
foreign key (teacher_id) references teacher(id) on update cascade on delete cascade,
)engine=innodb;
create table score(
id int primary key auto_increment,
score int,
student_id int,
course_id int,
foreign key (student_id) references student(id) on update cascade on delete cascade,
foreign key (course_id) references course(id) on update cascade on delete cascade
)engine=innodb;
二十七、mysql之修改表alter table
语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;
2. 增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…],ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
alter table order add goods_sn varchar(128) unque;
3. 删除字段
ALTER TABLE 表名 DROP 字段名;
4. 修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
#创建表
create table t01(
id int primary key auto_increment,
name char(16),
age int,
gender enum("male","female"),
hobbies set("read","music","running"),
birth date,
birth_year year,
create_time datetime,
class_record time
)engine=innodb;
# 修改表名 rename
alter table t01 rename test01;
# 添加字段 add
# 单字段添加[默认从后追加]
alter table test01 add username char(16);
# 多字段添加[默认从后追加]
alter table test01 add password char(16),add email char(32);
# 指定位置添加字段
# 添加字段到第一个位置
alter table test01 add summary text first;
# 添加字段到某个字段的后面
alter table test01 add mobile char(11) after id;
# 指定多个位置添加
alter table test01 add id_number int first,add ssn int after summary;s
# 删除字段 drop
alter table test01 drop id_number;
# 修改字段[类型,约束] modify
alter table test01 modify ssn char(16);
# 修改字段名称[保留原来类型和约束]
alter table test01 change summary biref text;
# 修改字段名称并缺修改数据类型和约束
alter table test01 change age height float(3,2) not null default 1.80;
二十八、mysql之复制表
# 复制表
create table book_copy select * from book; # 复制表结构 + 记录 (key不会复制: 主键、外键和索引)
# 如果想要key我们可修改
alter table book_copy modify id int primary key auto_increment;
# 只复制表结构(key不会复制: 主键、外键和索引)
create table book_copy_desc select * from book where 1=0; # 条件为假,查不到任何记录
# 复制表结构包括key(不复制记录)
create table emp01 like emp;
二十九、mysql删除表
drop table 表名;
三十、mysql之插入数据
# 1.出入完整数据(顺序插入)
语法一：
insert into 表名(字段1,字段2,字段3,...字段n) values(value1,value2,value3,...valuen);
语法二:
insert into 表名 values(value1,value2,value3,...valuen); # value包括主键id
# 2.指定字段插入数据
语法:
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3)
# 3.插入多条记录
语法：
insert into 表名 values
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3);
# 4.插入查询结果
语法:
insert into 表名(字段1,字段2,字段3) select (字段1,字段2,字段3) from 表2 where 条件;
#
create table test01(
id int primary key auto_increment,
username char(16),
password char(16)
)engine=innodb;
# 正序插入
insert into test01(id,username,password) values(1,"allen","19891213");
# 非正序插入
insert into test01(password,username,id) values("19891213","kevin",2);
# 插入全部
insert into test01 values(3,"collins","19891213");
# 指定字段插入
insert into test01(username,password) values("mark","19891213");
# 插入多条记录
insert into test01 values(5,"mike","19891213"),(6,"lily","19891213");
# 插入查询结果
create table t01 like test01;
insert into t01 values(7,"zens","19891213"),(8,"dived","19891213");
insert into test01 select * from t01 where id > 6;
三十一、mysql之更新数据
# 语法
update 表名 set 字段1=值1,...字段n=值n where CONDITION[条件] # 不加条件默认修改整个表
update test01 set username='hello' where username='mark';
三十二、mysql之删除数据
# 语法
delete from 表名 where CONDITION[条件];
delete from mysql.user where password='';
delete from test01 where id<2;
三十三、mysql单表查询语法和优先级
# 单表查询完整语法!!!
select distinct 字段1,字段2,...字段n from 表名
where 分组前过滤条件
group by 分组字段
having 分组后过滤条件
order by 排序字段
limit 限制条数;
# 关键字执行优先级!!!
from where group by having select distinct order by limit
# 准备数据
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
e部门编号 dep_id int
create table employee(id int primary key auto_increment,
name char(16),
age int,
gender enum("male","female"),
hire_date date,
post char(16),
post_comment char(32),
salary float,
office int,
dep_id int
)engine=innodb;
insert into employee(name,gender,age,hire_date,post,salary,office,dep_id) values
('egon','male',18,'20170301','foreign office',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('cl','male',48,'20101111','teacher',10000,401,1),
('yy','female',48,'20150311','sale',3000.13,402,2),
('yaya','female',38,'20101101','sale',2000.35,402,2),
('dd','female',18,'20110312','sale',1000.37,402,2),
('xx','female',18,'20160513','sale',3000.29,402,2),
('gg','female',28,'20170127','sale',4000.33,402,2),
('zy','male',28,'20160311','operation',10000.13,403,3),
('cyj','male',18,'19970312','operation',20000,403,3),
('cyy','female',18,'20130311','operation',19000,403,3),
('cyt','male',18,'20150411','operation',18000,403,3),
('cy','female',18,'20140512','operation',17000,403,3);
三十四、mysql单表查询之简单查询
select * from employee;
select name,gender,age,hire_date,post,salary,office,dep_id from employee;
select name,salary from employee;
三十五、mysql单表查询之四则运算查询
# 通过四则运算查询
select name,salary*12 from employee;
select name,salary*12 as annual_salary from employee; # as 设置别名
select name,salary*12 annual_salary from employee; # 可以省略as
select name,age+1 from employee;
三十六、mysql单表查询之定义显示格式
# 定制显示格式
# concat() 函数用于连接字符串
concat()函数连接字符串
select concat("name: ",name,"salary_annuel",salary*12) as info from employee;
# concat_ws() 第一个参数为分隔符
select concat_ws(':',name,salary*12) as info from employee;
# case语句(分支)
select (case when name = 'egon' then name
when name ='alex' then concat(name,"_DSB")
else concat(name,"SB") end) as new_name from employee;
select case when name='egon' then name
when name='alex' then concat(name,'_DSB')
else concat(name,'SB') end as new_name from employee;
三十七、mysql单表查询之where
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
# 1.单条件查询
select name from employee where post='sale';
# 2.多条件查询
select name,salary from employee where post='teacher' and salary>10000;
# 3.关键字between and 在什么到什么中间
select name from employee where id between 1 and 10;
select name from employee where id not between 1 and 10;
# 4.关键字is null(判断某个字段是否为null不能用等号,需要使用is)
select name,post_comment from employee where post_comment is null; # 查询职位描述为空的
select name,post_comment from employee where post_comment is not null;
# 5.关键字IN集合查询
select name from employee where id in (1,2,3);
select name from employee where id not in (1,2,3);
# 6.关键字like模糊查询
# 通配符%
select name from employee where name like 'eg%';
# 通配符_
select name from employee where name like "_lex";
1.查看岗位是teacher的员工姓名、年龄
select name,age from employee where post ="teacher";
2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from employee where age>30;
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,age,concat("salary:",salary*12) as salary from employee where salary between 9000 and 10000;
4. 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where salary in (10000,9000,30000);
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where salary not in (10000,9000,30000);
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary from employee where name like "jin%";
三十八、mysql单表查询之distinct
# distinct 对查询结果去重
select distinct post from employee;
select distinct dep_id from employee;
三十九、mysql单表查询之group_by
# 首先明确group by 是在 where 之后使用的
# 注意:可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，
# 如果想查看组内信息，需要借助于聚合函数
sql_mode:ONLY_FULL_GROUP_BY
# ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，
# 在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，
# 要么是来自于group by list中的表达式的值
# 设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
--------------------------------------------------------------------------------------------
# 单独使用 group by 关键字分组
select post from employee group by post;
# group by 和 group_concat()函数使用
select post,group_concat(name) from employee group by post;
select post,group_concat(salary*12) as salary from employee group by post;
# group by 和聚合函数使用
select post,sum(salary) from employee group by post; # 每个职位的总薪水
# 聚合函数
# 强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组,也就是一张表作为一个分组
sum count max min avg
select count(*) from employee; # 所有员工的数量
select max(salary) from employee; # 工资最高的员工
select min(salary) from employee; # 工资最低的员工
select avg(salary) from employee; # 所有员工的平均工资
select sum(salary) from employee; # 所有员工的总工资
# 练习
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat("member:",name) as member from employee group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) as emp_count from employee group by post;
3. 查询公司内男员工和女员工的个数
select gender, count(id) from employee group by gender;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from employee group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select gender,avg(salary) from employee group by gender;
四十、mysql单表查询之having
#1.Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数
#2.Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;
四十一、mysql单表查询之order by
# order by
# 语法
select * from employee order by salary; # 默认是升序
# 升序
select * from employee order by salary asc;
# 降序
select * from employee order by salary desc;
# 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
select * from employee order by age asc,salary desc;
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age asc,hire_date desc;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) >10000 order by avg(salary) asc;
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
# regexp
# 查询ale开头的员工
select * from employee where name regexp '^ale';
# 查询on结尾的员工
select * from employee where name regexp 'on$';
# 查询名字有来两个m的员工
select * from employee where name regexp 'm{2}';
1.查看所有员工中名字是jin开头，n或者g结尾的员工信息
select * from employee where name regexp '^jin.*[ng]$';
四十四、mysql多表查询之join
# 准备数据
create table dep(id int primary key auto_increment,name char(32))engine=innodb;
create table emp(id int primary key auto_increment,name char(16),gender enum("male","female"),age int,dep_id int)engine=innodb;
insert into dep values
(200,'technology'),
(201,'Human Resources'),
(202,'Sales'),
(203,'Operation');
insert into emp(name,gender,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204);
--------------------------------------------------------------------------------------------
# 语法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
1.交叉连接
# 不适用任何匹配条件,生成笛卡尔积
select * from dep,emp;
2.内连接:只连接匹配的行
select * from emp inner join dep on emp.dep_id=dep.id;
# 建议：使用join语句时，小表在前，大表在后
3.外链接之左连接:优先显示左表全部记录
select * from dep left join emp on dep.id=emp.dep_id;
4 外链接之右连接:优先显示右表全部记录
select * from dep right join emp on dep.id=emp.dep_id;
5 全外连接:显示左右两个表全部记录(利用union去重特性)
select * from dep left join emp on dp.id=emp.dep_id union select * from dep right join emp on dep.id=emp.dep_id;
6 内连接(NATURAL JOIN):自连接的表要有共同的列名字
# 准备表
create table country(id int primary key auto_increment,name var(32),code int);
create table city(id int primary key auto_increment,name varchar(32),countrycode int);
create table countrylanguage(id int primary key auto_increment,name varchar(32),code int);
通过自连接查询city所属国家的语言
select city.name,countrylanguage.name from city natural join countrylanguage;
# 关联相同的字段
四十五、mysql多表查询之union
# union 将表进行上下拼接去重
select * from dep union select * from dep;
# union all 将表进行上下拼接不去重
select * from dep union all select * from dep;
四十六、mysql多表条件查询
# 以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select * from emp inner join dep on emp.dep_id=dep.id where age>25;
# 以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from emp inner join dep on emp.deo_id=dep.id order by age asc;
四十七、mysql之子查询
# 1.自查询是将一个查询语句嵌套在另一个查询语句中
# 2.内层查询语句的查询结果,可以为外层查询语句提供查询条件
# 3.子查询中可以包含:in、not in、any、all、exists、not exists等关键字
# 4.还可以包含比较运算符:=、!=、>、<等
---------------------------------------------------------------------------------------------
1.带in关键字的子查询
# 查询平均年龄在25岁以上的部门名
select name from dep where id in (select dep_id from employee where age>25);
# 子查询一定加上括号
# 查看技术部员工姓名
select name from emp where dep_id = (select id from dep where name='technology');
select name from emp dep_id in (select id from dep where name='technology');
# 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from dep where id in (select dep_id from emp group by dep_id having count(id)<1);
2.带比较运算的子查询
# 比较运算符：=、!=、>、>=、<、<=、<>
# 查询大于所有人平均年龄的员工名与年龄
select name,age from emp where age > (select avg(age) from emp);
#查询大于部门内平均年龄的员工名、年龄
select name,age from emp as t1 inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as t2 on t1.dep_id=t2.dep_id where t1.age>t2.avg_age;
3.带exists关键字的子查询
# EXISTS关字键字表示存在 在使用EXISTS关键字时 内层查询语句不返回查询的记录
# 而是返回一个真假值 True或False
# dep表中存在dept_id=203，Ture
select * from emp where exists (select id from dep where id=203);
# department表中存在dept_id=205，False
select * from emp where exists (select id from dep where id=205);
# 练习
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
create table emp(
id int primary key auto_increment,
name char(16),
gender enum("male","female"),
age int,
hire_date date,
post char(16),
post_comment text,
salary float,
office int,
dep_id int
)engine=innodb;
insert into emp(name,gender,age,hire_date,post,salary,office,dep_id) values
('egon','male',18,'20170301','foreign office',7300.33,401,1),
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('cehnglong','male',48,'20101111','teacher',10000,401,1),
('yaiyai','female',48,'20150311','sale',3000.13,402,2),
('yaya','female',38,'20101101','sale',2000.35,402,2),
('dingding','female',18,'20110312','sale',1000.37,402,2),
('xingxing','female',18,'20160513','sale',3000.29,402,2),
('gege','female',28,'20170127','sale',4000.33,402,2),
('zhangye','male',28,'20160311','operation',10000.13,403,3),
('chenyajin','male',18,'19970312','operation',20000,403,3),
('chenyayin','female',18,'20130311','operation',19000,403,3),
('chenyatong','male',18,'20150411','operation',18000,403,3),
('chenyatie','female',18,'20140512','operation',17000,403,3);
# 查询每个部门最新入职的那位员工
# 连表
select * from emp as t1 inner join (select dep_id,max(hire_date) as new_time from emp group by dep_id) as t2 on t1.dep_id=t2.dep_id where t1.hire_date=t2.new_time;
select * from emp as t1 inner join (select posdest,max(hire_date) as new_hire from emp group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_hire;
# 子查询
select * from emp where id in (select (select id from emp as t2 where t1.post=t2.post order by hire_date desc limit 1) from emp as t1 group by post);
四十八、mysql之pymysql
# 安装
pip3 install pymysql
import pymysql
conn = pymysql.connect(host='127.0.0.1',
port=3306,
user='root',
password='MYsql891213',
database='t001',
charset='utf8')# 连接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 获取游标,并将返回值设置为字典
sql ='select * from dep;'
rows = cursor.execute(sql)print(rows)# 行数# print(cursor.fetchone()) # 取出一个# print(cursor.fetchmany(1)) # 取出指定的数量print(cursor.fetchall())# 取出所有
cursor.scroll(1,'absolute')# 绝对移动,相对于一开始的位置往后移动1条
cursor.scroll(1,'relative')# 相对移动,相对于你cursor当前的位置往后移动1条print(cursor.fetchall())
cursor.close()# 关闭游标
conn.close()# 关闭连接# 针对修改的操作每次都需要提交[commit]# 增
sql ='insert into dep(id,name) values(%s,%s)'
rows = cursor.execute(sql,(204,'market'))print(cursor.lastrowid)# 获取当前插入记录的行数
cursor.commit()# 一次插入多行记录
sql ='insert into dep(id,name) values(%s,%s)'
rows = cursor.execute(sql,[(205,'m'),(206,'n')])
cursor.commit()# 改
sql ='update dep set name=%s where id=%s'
rows = cursor.execute(sql,('x',204))
cursor.execute()# 删
sql 'delete from dep where id=%s'
rows = cursor.execute(sql,(204,))
cursor.execute()
四十九、mysql之视图(view)
# 视图就是通过查询得到一张虚拟表然后保存下来,下次用的时候直接使用即可
# 频繁使用一张虚拟表,通过创建视图可以不需要重复查询
# 语法
create view 视图表名 as 查询语句;
# 1.在硬盘中,视图只有表结构文件,没有表数据文件
# 2.视图通常是用于查询,尽量不要修改视图中的数据(修改视图中数据会导致原表的数据也会跟着改变)
# 创建一张视图表
create view v_dep as select * from dep;
# 查询视图
select * from v_dep;
# 修改视图
# 语法 ALTER VIEW 视图名称 AS SQL语句
# 本质就是修改sql语句
alter view v_dep as select * from dep where id >200;
# 删除视图
drop view v_dep;
五十、mysql之触发器
# 在满足对表进行【增、删、改】操作的情况下,会触发的功能
# 触发器专门针对我们对某一张表的增删改的行为,这类行为一旦执行就会触发触发器的执行,即自动运行别外一段sql代码,
# 创建触发器
# 语法：
CREATE TRIGGER 触发器名 BEFORE/AFTER INSERT/DELETE/UPDATE ON 表名 FOR EACH ROW
BEGIN
sql代码
END;
# 准备表
create table cmd(
id int primary key auto_increment,
user char(16),
priv char(16),
cmd char(16),
sub_time datetime,
success enum("yes","no")
)engine=innodb;
create table errlog(
id int primary key auto_increment,
err_cmd char(16),
err_time datetime
)engine=innodb;
# 插入之前
# delimiter // 更换mysql的结束符
delimiter //
create trigger tri_before_insert_cmd before insert on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;
# 插入之后
delimiter //
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;
# 更新之前
delimiter //
create trigger tri_before_update_cmd before update on cmd for each row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;
# 更新之后
delimiter //
create trigger tri_after_update_cmd after update on cmd for eache row
begin
if new.success='no' then
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end//
delimiter ;
# 删除之前
delimiter //
create trigger tri_before_delete_cmd before delete on cmd for each row
begin
if old.success='no' then
insert into errlog(err_cmd,err_time) values(old.cmd,old.sub_tiem)l
end if;
end//
delimiter ;
# 删除之后
delimiter //
create trigger tri_after_delete_cmd after delete on cmd for each row
begin
if old.success='no' then
insert into errlog(err_cmd,err_time) values(old.cmd,old.sub_time);
end if;
end//
delimiter ;
# 注意:NEW表示即将插入的数据行，OLD表示即将删除的数据行。
# 使用触发器
# 触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的
# 查看触发器
# 语法
SHOW TRIGGERS
show create trigger 触发器名
# 删除触发器
# 语法
drop trigger 触发器名;
drop trigger tri_before_insert_cmd;
五十一、mysql之事务(原子操作)
01.什么是事务
开启一个事务可以包含一些sql语句,这些sql语句要么同时成功
要么同时失败,称之为事务的原子性
02.事务的作用
#准备表
create table user(
id int primary key auto_increment,
name char(16),
balance int
)engine=innodb;
insert into user(name,balance) values('allen',1000),('kevin',1000),('collins',1000);
# 原子操作
# 语法
start transaction;
sql代码
rollback;
commit;
start transaction;
update user set balance=900 where name='allen';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='collins';
rollback; # 回滚意味着回到一面sql语句修改之前
commit; # 提交本次操作[只要不执行commit数据都不会保存到硬盘中]
五十二、mysql存储过程
# 存储过程:包含了一系列可真行的sql语句,存储过程存放与mysql中,通过调用它的名字可以执行起=其内部的一堆sql
# 三种开发模式
1.应用程序:只需要开发应用程序的逻辑
mysql:编写好存储过程,以供应用程序调用
优点:开发效率,执行效率都高
缺点:扩展性差
2.应用程序:开发应用程序的逻辑和编写原生sql
mysql:只需正常运行
优点:扩展性高
缺点:开发效率,执行效率都低
3.应用程序:除了开发应用程序的逻辑,不需要编写原生sql,使用别人编写好的框架orm
mysql:
优点:不用再编写原生sql,开发效率高
# 创建存储过程
# 语法
delimiter //
# 定义存储过程
create procedure p1()
begin
sql代码
end//
delimiter ;
# 调用存储过程
call p1();
---------------------------------------------------------------------------------------------
# 无参数
delimiter //
create procedure p1()
begin
select * from dep;
end//
delimiter ;
call p1();
---------------------------------------------------------------------------------------------
# 有参数
# in 仅用于传入参数用
# out 仅用于返回值用
# inout 既可以传入又可以当作返回值
# 设置变量
set @x=1;
# 查看变量
select @x;
delimiter //
create procedure p1(in m int,in n int,out result int)
# (in/out/inout 变量名 声明数据类型)
begin
select name from dep where id > m and id < n;
set res=1;
end//
delimiter ;
---------------------------------------------------------------------------------------------
# 查看存储过程
show create procedure p1;
# 查看所有存储过程
show procedure status;
---------------------------------------------------------------------------------------------
# 如果使用存储过程
1.在mysql中调用
set @result=0
call p1(1,3,@result); # out/inout必须传入一个变量,传一个固定值会报错
select @result; # 查看返回值
2.在python程序中调用
import pymysql
conn = pymysql.connect(host='127.0.0.1',
port=3306,
user='root',
password='MYsql891213',
database='t001',
charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p02', (200, 202, 0)) # @_p02_0=200 @_p02_1=202 @_p02_2=0
print(cursor.fetchall())
cursor.execute('select @_p02_2') # 查看返回值结果
print(cursor.fetchall())
cursor.close()
conn.close()
---------------------------------------------------------------------------------------------
# 删除存储过程
drop procedure 存储过程名;
drop procedure p1;
---------------------------------------------------------------------------------------------
# 注意:视图触发器存储过程都存放在创建的数据库中
# 存储过程事务的使用
delimiter //
create procedure x01(out res int)
begin
declare exit handler for sqlexception
begin
set res=1;
rollback;
end;
declare exit handler for sqlwarning
begin
set res=2;
rollback;
end;
start transaction;
update user set balance=900 where name='allen';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='collins';
commit;
set res=0;
end//
delimiter ;
# 定义一个异常处理
# 注意:declare...handler语句必须出现在变量或条件声明的后面
语法 DECLARE condition_name CONDITION FOR [condition_type]
condition_name参数表示异常的名称
condition_type参数异常类型
# 基本格式
# 未命名
BEGIN
DECLARE CONTINUE HANDLER FOR 1051
END;
# 有命名
BEGIN
DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
END;
# 异常处理
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement
handler_type: CONTINUE|EXIT|UNDO
handler_type为错误处理方式，参数为3个值之一；
CONTINUE表示遇到错误不处理，继续执行；
EXIT表示遇到错误时马上退出；
UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作
# 作用域
begni..end内，哪果错误处理定义在begin ... end内，则在该begin...end之外的错误不会被捕获。
它能够捕获其它储过程的错误。
---------------------------------------------------------------------------------------------
condition_value: SQLSTATE [VALUE] sqlstate_value|
condition_name|
SQLWARNING|
NOT FOUND|
SQLEXCEPTION|
mysql_error_code
condition_value表示错误类型；SQLSTATE [VALUE] sqlstate_value为包含5个字符的字符串错误值；
condition_name表示DECLARE CONDITION定义的错误条件名称；
SQLWARNING匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
mysql_error_code匹配数值类型错误代码
---------------------------------------------------------------------------------------------
# 例子
//方法一：捕获sqlstate_value异常
//这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
//方法二：捕获mysql_error_code异常
//这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息；
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
//方法三：先定义条件，然后捕获异常
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
//方法四：使用SQLWARNING捕获异常
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND捕获异常
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
//方法六：使用SQLEXCEPTION捕获异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
五十五、mysql之流程控制(条件语句)
# 大前提mysql中的函数只能在sql语句中使用
# if
delimiter //
create procedure p01()
begin
declare i int default 0;
# 声明一个变量为整形默认值为0
if i =1 then
select 1;
elseif i=2 then
select 2;
else
select 3;
end if;
end//
delimiter ;
# case
select
(case
when name='allen' then
name
when name='kevin' then
concat('hey',name)
else
concat('hello',name)
end) from user;
五十六、mysql之流程控制(循环语句)
# 大前提mysql中的函数只能在sql语句中使用
delimiter //
create procedure auto_insert()
begin
declare i int default 1;
while (i<100001)do
insert into bench_index values(i,concat('allen',i),18,concat('allen',i,'@live.com'));
set i=i+1;
end while;
end//
delimiter ;
五十七、mysql之date_format
create table blog(
id int primary key auto_increment,
name char(16),
sub_time datetime
)engine=innodb;
# date_format格式化时间
select date_format(sub_time,'%Y-%m'),count(id) from blog
group by date_format(sub_time,'%Y-%m');
五十八、mysql之set
# set
# 语法
# set语句可用于向系统变量或用户变量赋值
SET @var_name = expr [, @var_name = expr]
# 使用select语句来定义：
SELECT @var_name := expr [, @var_name = expr] ...
select t1.id,t1.name,t2.ct from category as t1 inner join (select category_id,count(id) as ct from article where user_id=1 group by category_id) as t2 on t1.id=t2.category_id;
select t1.id,t1.name,t2.ct from tag as t1 inner join (select tag_id,count(id) as ct from article_tag group by tag_id) as t2 on t1.id=t2.tag_id where t1.user_id=1;
select t1.title,t1.summary,t1.status,t2
select * from tag where id=1;
t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t3.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join (select article_id from article_tag where tag_id=1) as t2 on t1.id=t2.article_id inner join (select id,name from category) as t3 on t1.category_id=t3.id;
select t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t3.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join (select article_id from article_tag where tag_id=1) as t2 on t1.id=t2.article_id inner join (select id,name from category) as t3 on t1.category_id=t3.id;
select t1.id,t1.title,t1.summary,t1.like_num,t1.comment_num,t2.name,t1.status,t1.is_hot,t1.create_time from article as t1 inner join category as t2 on t1.category_id=t2.id where date_format(t1.create_time,"%Y-%m")=%s
五十九、mysql之do
do sleep(5) # 睡眠5秒
SELECT c.Score, b.Rank FROM Scores c INNER JOIN ( SELECT Score, ( @i := @i + 1 ) AS Rank FROM ( SELECT Score FROM Scores GROUP BY Score ORDER BY Score DESC ) a, ( SELECT @i := 0 ) AS it ) b ON c.Score = b.Score ORDER BY c.Score DESC
六十、mysql之if、ifnull、nullif
ifnull
# MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数
# 例子
select ifnull(1,0);
--return 1
select ifnull('',0);
--return 0
nullif
# NULLIF函数是接受2个参数的控制流函数之一。如果第一个参数等于第二个参数，则NULLIF函数返回NULL，否则返回第一个参数
# 例子
select nullif(1,1);
--return null
select nullif(1,2);
--return 1
if
# 在mysql中if()函数的用法类似于java中的三目表达式，其用处也比较多，具体语法如下:IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false,则返回expr3的值
# 列子
select name,if(gender=0,'male','female') as gender from g01;
六十一、mysql之创建函数
# 格式
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select ifnull(
(select distinct Salary from Employee order by Salary desc limit 1 offset N),
null
)
);
END
1.什么是SQL
结构化的查询语句
2.sql的种类
# DDL(Data Definition Language):数据定义语言
库对象:库名字、库属性
开发规范库名小写
(1) create
创建库:
create database|schema
# 规范的建库语句
create database if not exists db01 character set=utf8 collate=utf8_general_ci;
if not exists:表示数据库不存在时创建,否则不做操作
character set:指定字符集
show charset; # 查看所有字符集
collate:校验规则
ci:大小写不敏感
cs|bin:大小敏感
show collation; # 查看所有校验规则
创建表:
create table
# 例子
create table t01(id int primary key auto_increment)engine=innodb;
(2) alter
修改定义的库
alter database
# 例子
alter database db01 charset gbk; # 修改字符编码
修改定义的表
alter table
# 例子
alter table t01 add name char(16);
(3) drop
删除定义的库
drop database
# 例子
drop database db01;
删除定义的表
drop table
# 例子
drop table t01;
# DCL(Data Control Language):数据控制语言,针对权限进行控制
(1) grant # 授权
# 例子
授权root@10.0.0.1用户所有权限(非超级管理员)
grant all on *.* to root@'10.0.0.1' identified by 'PASSWORD';
授权超级管理员
grant all on *.* to root@'10.0.0.1' identified by 'PASSWORD' with grant option;
其他参数(扩展)
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
# 语法
grant 权限... on 库.表 用户[@'host'] [identified by 'PASSWORD']
# 限制级别
单数据:
on db01.*
单数据库单表:
on db01.t01
单数据库单表单字段:
grant select(name) on db01.t01
(2) revoke # 收回权限
# 例子
收回select权限
revoke select on *.* root@'10.0.0.1';
查看权限
show grant for root@'10.0.0.1';
语法
revoke 权限... on 用户[@'host'];
# DML(Data Manipulation Language):数据操纵语言
(1) insert # 新增记录
# 例子
常规用法,插入数据
insert into t01 values(1,'allen',18,'male');
规范用法,插入数据
insert into t01(name,age,gender) values('kevin',18,'male');
插入多条数据
insert into t01(name,age,gender) values
('collins',18,'female'),
('lily',18,'female');
(2) update # 更新记录
# 例子
不规范
update t01 set name='mike'
规范uodate修改
update t01 set name='mike' where id=10;
如果要修改全部
update t01 set name='mike' where 1=1;
(3) delete # 删除流
# 例子
不规范
delete from t01;
规范删除(危险)
delete from t01 where id=1;
DDL删除表
truncate table t01;
# 注意:不推荐直接删除数据
(1) 使用update作为删除,添加一个状态字段
(2) 使用触发器 trigger
# DQL(Data Query Language):数据查询语言
(1) select
# 完整语法
select distinct field... from TABLE where CONDITION group by field having CONDITION order by field [asc|desc] limit num[,num|offset num];
# 连表查询
join:
natural join:自连接
inner join: 内连接
left join:左连接
right join:右连接
union:
union:去重合并
union all:不去重合并
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化
# 例子
explain select name from t01 where id=1;
explain等级(null,system,const,eq_ref,ref,range,index,all) # 查询速度从高到低
all : 即全表扫描
index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。
range:以范围的形式扫描。
explain select * from a where a_id > 1\G
ref:非唯一索引访问(只有普通索引)
create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
explain select * from a where a_id=1\G
eq_ref:使用唯一索引查找(主键或唯一索引)
const:常量查询在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取。
当结果不是一条时，就会变成index或range等其他类型
system:系统查询
null:优化过程中就已经得到结果，不在访问表或索引
六十八、mysql之主从复制(单机多实例)
# 环境准备[mysql3006]
port=3306
[mysql3007]
port=3307
[mysql3008]
port=3308
[mysql3009]
port=3309
# 主库操作[mysql3006]
1、修改配置文件
vim /etc/my.cnf
[mysqld]#主库server_id为1,server_id不能与从库重复
server_id=1
#开启binlog日志
log_bin=mysql-bin
2、重启主库mysql
systemctl restart mysqld
3、查看Position
show master status;# 记录 Position
3、创建主从复制用户
grant replication slave on *.* to rep@'%' identified by 'MYsql@891213';
flush privileges;# 从库操作[mysql3007]
修改配置文件
vim /etc/my.cnf
[mysqld]#主库server_id为1,从库server_id不能与主库重复
server_id=7
[mysql3008]
修改配置文件
vim /etc/my.cnf
[mysqld]#主库server_id为1,从库server_id不能与主库重复
server_id=8
[mysql3009]
修改配置文件
vim /etc/my.cnf
[mysqld]#主库server_id为1,从库server_id不能与主库重复
server_id=9
1、设置从库与主库进行通信
# 格式
CHANGE MASTER TO MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;# 执行change master(创建IO,SQL线程)
CHANGE MASTER TO MASTER_HOST='127.0.0.1',
MASTER_USER='rep',
MASTER_PASSWORD='MYsql@891213',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
2、启动主从
start slave;# stop slave; 关闭主从
3、查看从库状态
show slave status;# 显示如下表示主从启动成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 注意开启主从复制前,对主库做全备份,保证主从数据一致性# 主库
mysqldump -A -uroot -p > /tmp/full.sql
# 从库source /tmp/full.sql
1406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
