2021-7-28python学习日志——MySQL

今日内容详细

约束条件

	unsigned
	zerofill
	not null
	unique
	primary key
	auto_increment
	default  
	foreign key  (外键 重要)

约束条件之外键（重要）

	一对一
	一对多
	多对多

查询语句

	select 
	from
	where
	group by
	order by
	distinct
	limit 
	having

约束条件

	1. unsigned  设置无符号
	2. zerofill   零填充
	3. not null   非空
		不等于 ''
	4. default  默认值
	5. unique  唯一
		# 单列唯一
	    	create table t6 (id int, name varchar(16) unique);
		
	    localhost
	    192.168.  局域网
	    47.193.1*****
	    		host		  port
	    mysql -h 127.0.0.1 -P 3306 -u root -p 密码
	    # 多列唯一
	        create table t8 (
	        	id int,
	            host varchar(32),
	            port int,
	            unique(host, port)
	        );
	6. primary key
		# 1.从限制角度来说，主键相当于非空且唯一
	    	id int primary key == id int not null unique
	        create table t9 (
	        	id int primary key
	        );
	    # InnoDB存储引擎规定一张表中必须有一个主键，
	    为什么之前的创建表没有指定主键，也能创建成功？
	    	InnoDB引擎帮你用一个隐藏的字段创建了一个主键，隐藏意味着看不到，也不能用。
	    # 主键的功能: 查询速度快，  主键本质也是一种索引
	    
	7. auto_increment
		# 你就理解为为主键使用的，自增，每次增加1
	    create table t13 (
	    	id int primary key auto_increment,
	        name varchar(16)
	    );
		
	    
	    # 结论：
	    	id int primary key auto_increment
	        cid nt primary key auto_increment
	        uid int primary key auto_increment

清空表数据

	1. delete from t13;
	2. truncate table t13; 以后只要是清空表数据，推荐你用truncate
	
	binlog  恢复数据

外键前期准备

	1. 在数据表中一般不存中文
	
	# 问题
	扩展性差

表关系判断

	一对一
	一对多
	多对多
	
	
	#########################一对多######################
	 站在不同的角度去判断
	    1. 一个用户可以有多个部门吗？
	    	不可以
	    2. 一个部门可以有多个用户吗？
	    	可以
	    # 结论：只要是一个可以，一个不可以，那么，他们就是 一对多 的关系    
	        
	#########################多对多######################    
	 站在不同的角度去判断
	    1. 一个图书可以有多个作者吗？
	    	可以
	    2. 一个作者可以有多个书吗？
	    	可以
	    # 结论：只要是一个可以，另外一个可以，那么，他们就是 多对多 的关系
	        
	#########################一对一######################    
	 站在不同的角度去判断
	    1. 一个作者可以有多个作者详情吗？
	    	不可以
	    2. 一个作者详情可以有多个作者吗？
	    	不可以
	    # 结论：只要是一个不可以，另外一个也不可以，那么，他们就是 一对一 的关系
	    
	  	一对一关系外键建在任何一个表中都可以
	    但是，推荐建在查询频率高的一个表，
	        

SQL语句实现表关系

	####################一对多#######################
	1. 实现一对多, 一对多的外键建在多的一方
	
	# 创建带有表关系的表
	# 1.先创建基表，
	# 2.在添加外键关系
	# 3.先创建被关联的表，  先创建没有外键的表
	create table userinfo(
		id int primary key auto_increment,
	    username varchar(32),
	    salary decimal(8, 2),
	    dep_id int,
	    foreign key (dep_id) references dep(id)
	);
	
	create table dep(
		id int primary key auto_increment,
	    name varchar(32),
	    descript varchar(64)
	);
	
	####################多对多#######################
	create table book(
		id int primary key auto_increment,
	    title varchar(16),
	    price int
	);
	
	create table author(
		id int primary key auto_increment,
	    name varchar(16),
	    phone int
	);
	
	# 多对多一定要先创建第三张表
	
	create table book2author(
		id int primary key auto_increment,
		book_id int,
	    author_id int,
	    foreign key (book_id) references author(id)
	    on update cascade  # 级联更新
	    on delete cascade,  # 级联删除
	    
	    foreign key (author_id) references book(id)
	    on update cascade  # 级联更新
	    on delete cascade  # 级联删除
	);
	
	
	####################一对一#######################
	create table author_detail(
		id int primary key auto_increment,
	    title varchar(16),
	    price int
	);
	
	
	create table author_2(
		id int primary key auto_increment,
	    name varchar(16),
	    phone int,
	    author_detail_id int unique,
	    foreign key (author_detail_id) references author_detail(id)
	    on update cascade  # 级联更新
	    on delete cascade  # 级联删除
	);

级联更新级联删除

	create table userinfo_1 (
		id int primary key auto_increment,
	    username varchar(32),
	    salary decimal(8, 2),
	    dep_id int,
	    foreign key (dep_id) references dep_1(id)
	    on update cascade  # 级联更新
	    on delete cascade  # 级联删除
	);
	
	
	create table dep_1(
		id int primary key auto_increment,
	    name varchar(32),
	    descript varchar(64)
	);
	
	insert into dep_1 (name , descript) values ('技术部', '技术');
	
	insert into dep_1 (name , descript) values ('外交部', '外交');

查询关键字

表准备

	create table emp(
	  id int primary key auto_increment,
	  name varchar(20) not null,
	  sex enum('male','female') not null default 'male', #大部分是男的
	  age int(3) unsigned not null default 28,
	  hire_date date not null,
	  post varchar(50),
	  post_comment varchar(100),
	  salary double(15,2),
	  office int, #一个部门一个屋子
	  depart_id int
	);
	
	#插入记录
	#三个部门：教学，销售，运营
	insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
	('tom','male',78,'20150302','teacher',1000000.31,401,1),
	('kevin','male',81,'20130305','teacher',8300,401,1),
	('tony','male',73,'20140701','teacher',3500,401,1),
	('owen','male',28,'20121101','teacher',2100,401,1),
	('jack','female',18,'20110211','teacher',9000,401,1),
	('jenny','male',18,'19000301','teacher',30000,401,1),
	('sank','male',48,'20101111','teacher',10000,401,1),
	('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
	('呵呵','female',38,'20101101','sale',2000.35,402,2),
	('西西','female',18,'20110312','sale',1000.37,402,2),
	('乐乐','female',18,'20160513','sale',3000.29,402,2),
	('拉拉','female',28,'20170127','sale',4000.33,402,2),
	('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
	('程咬金','male',18,'19970312','operation',20000,403,3),
	('程咬银','female',18,'20130311','operation',19000,403,3),
	('程咬铜','male',18,'20150411','operation',18000,403,3),
	('程咬铁','female',18,'20140512','operation',17000,403,3);

	1.select
		用来指定表的字段数据
	    select * from emp;
	    select id,name from emp;
	    """
	    在工作中一般很少使用*号 我们只是为了教学方便
	    """
	 
	2.from
		后面跟需要查询的表名即可
	    
	3.where
		筛选数据
    

查询关键字之where

	# 1.查询id大于等于3小于等于6的数据
	select id,name from emp where id >= 3 and id <= 6;
	select *  from emp where id between 3 and 6;  
	
	# 2.查询薪资是20000或者18000或者17000的数据
	select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
	select * from emp where salary in (20000,18000,17000);  # 简写
	
	"""
	模糊查询
		关键字  
			like
		关键符号
			%:匹配任意个数的任意字符
			_:匹配单个个数的任意字符
		show variables like '%mode%';
	""" elasticsearch
	# 3.查询姓名中带有字母o的员工姓名和薪资
	select name,salary from emp where name like '%o%';
	
	# 4.查询姓名由四个字符组成的员工姓名和薪资
	select name,salary from emp where name like '____';
	select name,salary from emp where char_length(name) =4;
	
	# 5.查询id小于3或者大于6的数据
	select *  from emp where id not between 3 and 6;
	
	# 6.查询薪资不在20000，18000，17000范围的数据
	not in 不走索引
	select * from emp where salary not in (20000,18000,17000);
	
	# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
	select name,post from emp where post_comment = NULL;  # 查询为空！
	select name,post from emp where post_comment is NULL;
	select name,post from emp where post_comment is not NULL;

查询关键字之group by分组

	分组
		将单个单个的个体按照指定的条件分成一个个整体
	"""
	分组之后默认只能直接获取到分组的依据
	其他字段无法再直接获取(可以间接获取)
	"""
	# 严格模式
	set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'
	
	# 1.每个部门的最高薪资
	select post,max(salary) from emp group by post;
	# 2.每个部门的最低薪资
	select post,min(salary) from emp group by post;
	# 3.每个部门的平均薪资
	select post,avg(salary) from emp group by post;
	# 4.每个部门的人数
	select post,count(id) from emp group by post;
	# 5.每个部门的月工资总和
	select post,sum(salary) from emp group by post;

	"""
	可以给字段起别名(as还可以给表起别名)
	    select post as '部门',sum(salary) as '总和' from emp group by post;
	"""
	# 查询分组之后的部门名称和每个部门下所有的员工姓名
	"""
	group_concat()  获取分组以外的字段数据 并且支持拼接操作
	select post,group_concat(name) from emp group by post;
	select post,group_concat(name,':',salary) from emp group by post;
	
	concat()  未分组之前使用的拼接功能
	select concat(name,':',sex) from emp;
	
	concat_ws()
	select concat_ws(':',name,sex,salary,age) from emp;
	"""

聚合函数

	分组之后频繁需要使用的
		max		最大值
	    min		最小值
	    sum		求和
	    count	计数
	    avg		平均值

作业

	1.书籍表与出版社表
	2.课程表与老师表
	3.班级表与学生表
	4.作者表与作者详情表
	"""
	书写出完整的判断过程
	并且写上对应的SQL语句
	"""
	
	
	book
	publish   
	图书和出版社是一对多关系
	author
	书和作者是多对多关系
	author_detail
	作者和作者详情一对一
	
	create table publish(
		id int primary key auto_increment,
	    p_name varchar(30)
	);
	
	create table book(
		id int primary key auto_increment,
	    b_name varchar(50),
	    price decimal(5,2),
	    p_id int,
	    foreign key (p_id) references publish(id)
	    on update cascade 
	    on delete cascade
	);
	create table author_detail(
		id int primary key auto_increment,
	    phone varchar(20)
	);
	
	create table author(
		id int primary key auto_increment,
	    name varchar(10),
	    author_detail_id int unique,
	    foreign key (author_detail_id) references author_detail(id)
	    on update cascade 
	    on delete cascade
	);
	create table book_author(
		id int primary key auto_increment,
		book_id int,
	    author_id int,
	    foreign key (book_id) references author(id)
	    on update cascade  
	    on delete cascade, 
	    
	    foreign key (author_id) references book(id)
	    on update cascade  
	    on delete cascade  
	);