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')