MySQL基础
1，初识SQL语句
SQL语句：
操作文件夹（库）
增
create database db1 charset utf8;
查
show create database db1;
show databases;
改
alter database db1 charset gbk;
删
drop database db1;
操作文件（表）
切换文件夹：use db1;
查看当前所在文件夹：select database();
增
create table t1(id int,name char);
查
show create table t1;
show tables;
desc t1;
改
alter table t1 modify name char(6);
alter table t1 change name NAME char(7);
删
drop table t1;
操作文件内容（记录）
增
insert t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');
查
select id,name from db1.t1;
select * from db1.t1;
改
update db1.t1 set name='SB';
update db1.t1 set name='ALEX' where id=2;
删
delete from t1;
delete from t1 where id=2;
2,存储引擎介绍
1、什么是存储引擎？
存储引擎就是表的类型
2、查看MySQL支持的存储引擎
show engines;
3、指定表类型/存储引擎
create table t1(id int)engine=innodb;
create table t2(id int)engine=memory;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=myisam;
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
3,日期类型
create table student(
id int,
name char(6),
born_year year,
birth_date date,
class_time time,
reg_time datetime
);
insert into student values
(1,'egon',now(),now(),now(),now());
insert into student values
(2,'alex',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12");
4，字符类型
char:定长
varchar：变长
#宽度指的是字符的个数
create table t13(name char(5));
create table t14(name varchar(5));
insert into t13 values('李杰 '); #'李杰 '
insert into t14 values('李杰 '); #'李杰 '
select char_length(name) from t13; #5
select char_length(name) from t14; #3
select name from t13 where name='李杰';
select name from t13 where name like '李杰';
name char(5)
egon |alex |wxx |
name varchar(5)
1bytes+egon|1bytes+alex|1bytes+wxx|
4+egon|4+alex|3+wxx|
5,枚举类型和集合类型
create table consumer(
id int,
name char(16),
sex enum('male','female','other'),
level enum('vip1','vip2','vip3'),
hobbies set('play','music','read','run')
);
insert into consumer values
(1,'egon','male','vip2','music,read');
insert into consumer values
(1,'egon','xxxxx','vip2','music,read');
6，约束条件nnot null与default
create table t15(
id int(11) unsigned zerofill
);
create table t16(
id int,
name char(6),
sex enum('male','female') not null default 'male'
);
insert into t16(id,name) values(1,'egon');
7,约束条件unique key
unique key
单列唯一
#方式一
create table department(
id int unique,
name char(10) unique
);
#方式二：
create table department(
id int,
name char(10)，
unique(id),
unique(name)
);
insert into department values
(1,'IT'),
(2,'Sale');
联合唯一
create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port)
);
insert into services values
(1,'192.168.11.10',80),
(2,'192.168.11.10',81),
(3,'192.168.11.13',80);
insert into services values
(4,'192.168.11.10',80);
8，约束条件primary key
primary key
约束：not null unique
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须有一个主键
# 单列主键
create table t17(
id int primary key,
name char(16)
);
insert into t17 values
(1,'egon'),
(2,'alex');
insert into t17 values
(2,'wxx');
insert into t17(name) values
('wxx');
create table t18(
id int not null unique,
name char(16)
);
# 复合主键
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
insert into t19 values
('1.1.1.1',80),
('1.1.1.1',81);
9,约束条件auto_increment
auto_increment
create table t20(
id int primary key auto_increment,
name char(16)
);
insert into t20(name) values
('egon'),
('alex'),
('wxx');
insert into t20(id,name) values
(7,'yuanhao');
insert into t20(name) values
('egon1'),
('egon2'),
('egon3');
#了解
show variables like 'auto_inc%';
#步长：
auto_increment_increment默认为1
#起始偏移量
auto_increment_offset默认1
#设置步长
set session auto_increment_increment=5;
set global auto_increment_increment=5;
#设置起始偏移量
set global auto_increment_offset=3;
强调：起始偏移量<=步长
create table t21(
id int primary key auto_increment,
name char(16)
);
insert into t21(name) values
('egon'),
('alex'),
('wxx'),
('yxx');
清空表：
delete from t20;
delete from t20 where id = 3;
insert into t20(name) values
('xxx');
truncate t20; #应该用它来清空表
10，约束条件之foreign key
foreign key:建立表之间的关系
#1、建立表关系：
#先建被关联的表,并且保证被关联的字段唯一
create table dep(
id int primary key,
name char(16),
comment char(50)
);
#再建立关联的表
create table emp(
id int primary key,
name char(10),
sex enum('male','female'),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
#2、插入数据
#先往被关联表插入记录
insert into dep values
(1,"IT","技术能力有限部门"),
(2,"销售","销售能力不足部门"),
(3,"财务","花钱特别多部门");
#再往关联表插入记录
insert into emp values
(1,'egon','male',1);
insert into emp values
(2,'alex','male',1),
(3,'wupeiqi','female',2),
(4,'yuanhao','male',3),
(5,'jinximn','male',2);
delete from emp where dep_id=1;
delete from dep where id=1;
delete from dep where id=3;
11，表之间的关系
两张表之间的关系：
1、多对一
出版社 书（foreign key(press_id) references press(id)）
2、多对多
作者 书
egon:
九阳神功
九阴真经
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(2,1),
(2,6);
3、一对一
customer表 student表
12，单表查询
单表查询
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
#where
select id,name,age from employee where id > 7;
select name,post,salary from employee where post='teacher' and salary > 8000;
select name,salary from employee where salary >= 20000 and salary <= 30000;
select name,salary from employee where salary between 20000 and 30000;
select name,salary from employee where salary < 20000 or salary > 30000;
select name,salary from employee where salary not between 20000 and 30000;
select * from employee where age = 73 or age = 81 or age = 28;
select * from employee where age in (73,81,28);
select * from employee where post_comment is Null;
select * from employee where post_comment is not Null;
select * from employee where name like "jin%";
select * from employee where name like "jin___";
#group by
mysql> set global sql_mode="ONLY_FULL_GROUP_BY";
分组之后，只能取分组的字段，以及每个组聚合结果
select post from employee group by post;
#聚合函数
max
min
avg
sum
count
#每个职位有多少个员工
select post,count(id) as emp_count from employee group by post;
select post,max(salary) as emp_count from employee group by post;
select post,min(salary) as emp_count from employee group by post;
select post,avg(salary) as emp_count from employee group by post;
select post,sum(age) as emp_count from employee group by post;
#没有group by则默认整体算作一组
select max(salary) from employee;
#group_concat
select post,group_concat(name) from employee group by post;
#练习：
select post,group_concat(name) from employee group by post;
select post,count(id) from employee where age > 50 group by post;
select sex,count(id) from employee group by sex;
select sex,avg(salary) from employee group by sex
#having
select post,group_concat(name),count(id) from employee group by post;
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#order by
select * from employee order by age asc; #升序
select * from employee order by age desc; #降序
select * from employee order by age asc,id desc; #先按照age升序排，如果age相同则按照id降序排
select distinct post,count(id) as emp_count from employee
where salary > 1000
group by post
having count(id) > 1
order by emp_count desc
;
#limit
select * from employee limit 3;
select * from employee order by salary desc limit 1;
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;
#总结：
语法顺序：
select distinct 字段1,字段2,字段3 from 库.表
where 条件
group by 分组条件
having 过滤
order by 排序字段
limit n;
执行顺序：
def from(db,table):
f=open(r'%s\%s' %(db,table))
return f
def where(condition,f):
for line in f:
if condition:
yield line
def group(lines):
pass
def having(group_res):
pass
def distinct(having_res):
pass
def order(distinct_res):
pass
def limit(order_res)
pass
def select():
f=from('db1','t1')
lines=where('id>3',f)
group_res=group(lines)
having_res=having(group_res)
distinct_res=distinct(having_res)
order_res=order(distinct_res)
res=limit(order_res)
print(res)
return res
#正则表达式
select * from employee where name like 'jin%';
select * from employee where name regexp '^jin';
select * from employee where name regexp '^jin.*(g|n)$';
13，连表操作
内连接：只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id ;
左连接:在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id ;
右连接：在内连接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id ;
全外连接：在内连接的基础上保留左右两表没有对应关系的记录
select * from employee full join department on employee.dep_id = department.id ;
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id ;
14，多表查询练习
13、查询全部学生都选修了的课程号和课程名
17、查询平均成绩大于85的学生姓名和平均成绩
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
15，权限管理
权限管理
1、创建账号
# 本地账号
create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123
# 远程帐号
create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon2 -p123 -h 服务端ip
create user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
create user 'egon3'@'%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip
2、授权
user:*.*
db:db1.*
tables_priv:db1.t1
columns_priv:id,name
grant all on *.* to 'egon1'@'localhost';
grant select on *.* to 'egon1'@'localhost';
revoke select on *.* from 'egon1'@'localhost';
grant select on db1.* to 'egon1'@'localhost';
revoke select on db1.* from 'egon1'@'localhost';
grant select on db1.t2 to 'egon1'@'localhost';
revoke select on db1.t2 from 'egon1'@'localhost';
grant select(id,name),update(age) on db1.t2 to 'egon1'@'localhost';
16，存储过程
#1、无参存储过程
delimiter //
create procedure p1()
BEGIN
select * from db7.teacher;
END //
delimiter ;
# MySQL中调用
call p1();
# Python中调用
cursor.callproc('p1')
#2、有参存储过程
delimiter //
create procedure p2(in n1 int,in n2 int,out res int)
BEGIN
select * from db7.teacher where tid > n1 and tid < n2;
set res = 1;
END //
delimiter ;
# MySQL中调用
set @x=0
call p2(2,4,@x);
select @x;
# Python中调用
cursor.callproc('p2',(2,4,0))# @_p2_0=2,@_p2_1=4,@_p2_2=0
cursor.execute('select @_p3_2')
cursor.fetchone()
应用程序与数据库结合使用
方式一：
Python:调用存储过程
MySQL:编写存储过程
方式二：
Python：编写纯生SQL
MySQL：
方式三：
Python：ORM->纯生SQL
MySQL：
17，函数与流程控制
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m')
