mysql 添加外键_mysql -1

0 MySQL的数据类型

1 数据库创建删除 使用

2 数据表的显示 创建 删除 清空 修改表

3 数据表内容的 查找 增加 删除 改

4 数据库 用户以及权限的设置

0 MySQL的数据类型

数值、时间和字符串

bit[(M)] 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1tinyint[(m)][unsigned][zerofill] 小整数，数据类型用于保存一些范围的整数数值范围： 有符号： -128 ～ 127. 无符号： 0 ～ 255 特别的： MySQL中无布尔值，使用tinyint(1)构造。 int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围： 有符号： -2147483648 ～ 2147483647 无符号： 0 ～ 4294967295 特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002bigint[(m)][unsigned][zerofill] 大整数，数据类型用于保存一些范围的整数数值范围： 有符号： -9223372036854775808 ～ 9223372036854775807 无符号： 0 ～ 18446744073709551615decimal[(m[,d])] [unsigned][zerofill] 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。 特别的：对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储。 FLOAT[(M,D)][UNSIGNED][ZEROFILL] 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 无符号： -3.402823466E+38to-1.175494351E-38, 01.175494351E-38to3.402823466E+38 有符号： 01.175494351E-38to3.402823466E+38**** 数值越大，越不准确 ****DOUBLE[(M,D)][UNSIGNED][ZEROFILL] 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。 无符号： -1.7976931348623157E+308to-2.2250738585072014E-30802.2250738585072014E-308to1.7976931348623157E+308 有符号： 02.2250738585072014E-308to1.7976931348623157E+308**** 数值越大，越不准确 ****1 数据库创建删除 使用

• 显示数据库: show databases;

默认数据库：mysql - 用户权限相关数据, test - 用于用户测试数据, information_schema - MySQL本身架构相关数据

• 创建数据库: utf-8: create database xxx default charset utf8 collate utf8_general_ci;

gbk: create database xxx default character set gbk collate gbk_chinese_ci;
UTF8与GBK区别

• 删除数据库: drop database xxxx;
• 使用数据库: use xxxx;

2 数据表的显示 创建 删除 清空 修改表

1. 显示数据表: show tables;
2. 创建表:考虑四点:

• 是否为空 not null 不可为空，null 可为空
• 默认值

 create table tb1(
    nid int not null defalut 2,
    num int not null
 )

• 自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）对于自增列，必须是索引(含主键)， 对于自增可以设置布长和起始值

 create table tb1(
     nid int not null auto_increment primary key,
     num int null
)
或
create table tb1(
  nid int not null auto_increment,
  num int null,
  index(nid)
)
设置步长分为窗口级别以及全局的 session以及global：
show session variables like 'auto_inc%';
set session auto_increment_increment=2;
set session auto_increment_offset=10;

shwo global  variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;



                 desc t10;
		
		show create table t10;
		
		show create table t10 G;
		
		alter table t10 AUTO_INCREMENT=20;

                MySQL: 自增步长
			基于会话级别：
				show session variables like 'auto_inc%';	查看全局变量
                set session auto_increment_increment=2; 	设置会话步长
				# set session auto_increment_offset=10;
			基于全局级别：
				show global variables like 'auto_inc%';	    查看全局变量
                set global auto_increment_increment=2; 	    设置会话步长
				# set global auto_increment_offset=10;

• 主键: 一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。primary key(nid,num)

            单列组成的主键 create table tb1(
                nid int not null auto_increment primary key,
                num int null
            ) engine=innodb default charset=utf8;
            或 多列组成的主键
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )engine=innodb default charset=utf;

• 关于主键的补充: 一个表只能有一个主键,主键可以由多列组成
• 外键: 外键，一个特殊的索引，只能是指定内容 constraint fk_cc foreign key (color_id) references color(nid)

          creat table color(
                nid int not null primary key,
                name char(16) not null
            ) engine=innodb default charset=utf8;

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                           自定义             本表那个字段            外表以及外表的字段
                constraint fk_cc foreign key (color_id) references color(nid)
            ) engine=innodb default charset=utf8;

foreign key的名字不可重复，
多个外键
create table t6(
						id int auto_increment primary key,
						name char(10),
						id1 int,
						id2 int,
						CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
					)engine=innodb default charset=utf8;

删除表: drop table xxxx

清空表: delete from 表名 或者 truncate table xxxx

修改表:

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
 alter table 表名 modify column 列名 类型;  -- 类型
 alter table 表名 change 原列名 新列名 类型; -- 列名，类型
  
添加主键：
 alter table 表名 add primary key(列名);
删除主键：
 alter table 表名 drop primary key;
 alter table 表名  modify  列名 int, drop primary key;
  
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
  
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; 

3 数据表内容的 查找 增加 删除 改

增：

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表

查：

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

删:

delete from 表
delete from 表 where id＝1 and name＝'alex'

改:

update 表 set name ＝ 'alex' where id>1

高级查询： 重点

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)


b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）


c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行

 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前

f、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid

g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

4 数据库用户管理与权限操作:

用户管理：

添加用户: create user '用户名'@'IP' identified by 'zllas55'

删除用户: drop user '用户名'@'IP'

修改用户: rename user '用户名'@'IP' to '新用户'@'IP'

修改密码: set password for '用户名'@'IP' = Password('新密码')

授权:

所有权限，所有数据库: grant all privileges on *.* to 'root'@'IP' identified by '密码’

部分数据库，部分权限:

grant select,insert,update,delete on golang_db.* to testuser@localhost identified by "123456"

指定数据库的指定表: golang_db.tb1

指定IP： 192.168.1.% IP段 任意IP: 用户名@%

生效： flush privileges，将数据读取到内存中，从而立即生效。

当设置用户密码遇到 安全性问题:

SHOW VARIABLES LIKE 'validate_password%';

set global validate_password_policy=LOW;

set global validate_password_length=6;

补充：

2. SQL语句数据行操作补充
			create table tb12(
				id int auto_increment primary key,
				name varchar(32),
				age int
			)engine=innodb default charset=utf8;
	
		增
			insert into tb11(name,age) values('alex',12);
			
			insert into tb11(name,age) values('alex',12),('root',18);
			
			insert into tb12(name,age) select name,age from tb11;
		删
			delete from tb12;
			delete from tb12 where id !=2 
			delete from tb12 where id =2 
			delete from tb12 where id > 2 
			delete from tb12 where id >=2 
			delete from tb12 where id >=2 or name='alex'
		
		改
			update tb12 set name='alex' where id>12 and name='xx'
			update tb12 set name='alex',age=19 where id>12 and name='xx'
		查
			
			select * from tb12;
			
			select id,name from tb12;
			
			select id,name from tb12 where id > 10 or name ='xxx';
			
			select id,name as cname from tb12 where id > 10 or name ='xxx';
			
			select name,age,11 from tb12;
			
			其他：
				select * from tb12 where id != 1
				select * from tb12 where id in (1,5,12);
				select * from tb12 where id not in (1,5,12);
				select * from tb12 where id in (select id from tb11)
				select * from tb12 where id between 5 and 12;
	
			
				通配符：
				
				select * from tb12 where name like "a%"
				select * from tb12 where name like "a_"
	
			
				分页：
				
					select * from tb12 limit 10;
					
					select * from tb12 limit 0,10;
					select * from tb12 limit 10,10;
					select * from tb12 limit 20,10;
					
					select * from tb12 limit 10 offset 20;
		
		
					# page = input('请输入要查看的页码')
					# page = int(page)
					# (page-1) * 10
					# select * from tb12 limit 0,10; 1 
					# select * from tb12 limit 10,10;2
				
				
				排序：
					select * from tb12 order by id desc; 大到小
					select * from tb12 order by id asc;  小到大
					 select * from tb12 order by age desc,id desc;
					 
					取后10条数据
					select * from tb12 order by id desc limit 10;
			
				分组：
				
					select count(id),max(id),part_id from userinfo5 group by part_id;
					
					count
					max
					min
					sum
					avg
					
					**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
					select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
					
					select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
			
					
				连表操作：
				
					select * from userinfo5,department5
					
					select * from userinfo5,department5 where userinfo5.part_id = department5.id
					

					select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
					select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
					# userinfo5左边全部显示
					
					
					# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
					# department5右边全部显示
				
				
				
					select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
					将出现null时一行隐藏
					
					
				
				
				
				
					select * from 
						department5 
					left join userinfo5 on userinfo5.part_id = department5.id
					left join userinfo6 on userinfo5.part_id = department5.id
				
				
					select 
						score.sid,
						student.sid 
						from 
					score

						left join student on score.student_id = student.sid

						left join course on score.course_id = course.cid

						left join class on student.class_id = class.cid

						left join teacher on course.teacher_id=teacher.tid
					
			
			
			
			select count(id) from userinfo5;
				

--------------------------------------

二 第二篇

0 唯一索引

作用:

1 唯一: 约束不能重复（可以为空）PS: 主键不能重复（不能为空）

2 加速查找

外键的变种

一对一

一对多

多对多

一对一：

一对多:

c. 用户表（百合网） 相亲记录表
		
			示例1：
				用户表
				相亲表
				
			示例2：
				用户表
				主机表
				用户主机关系表
			===》多对多

                                create table userinfo2(
					id int auto_increment primary key,
					name char(10),
					gender char(10),
					email varchar(64)
				)engine=innodb default charset=utf8;

				create table host(
					id int auto_increment primary key,
					hostname char(64)
				)engine=innodb default charset=utf8;


				create table user2host(
					id int auto_increment primary key,
					userid int not null,
					hostid int not null,
					unique uq_user_host (userid,hostid),
					CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
					CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
				)engine=innodb default charset=utf8;

三：第三篇

MySQL练习题参考答案 - 武沛齐 - 博客园​www.cnblogs.com

上节回顾：
	1. MySQL：文件管理的软件
	2. 三部分：
		- 服务端
		- SQL语句
		- 客户端
	3. 客户端：
		- mysql
		- navicat
		
	4. 授权操作
		- 用户操作
		- 授权操作
	5. SQL语句
		- 数据库操作
			- create database xx default charset utf8;
			- drop database xx;
		- 数据表
			- 列
				- 数字
					整数
					小数
				- 字符串
				- 时间
				- 二进制
			- 其他：引擎，字符编码，起始值
			
			- 主键索引
			- 唯一索引
			- 外键
				- 一对多
				- 一对一
				- 多对多
		- 数据行
			- 增
			- 删
			- 改
			- 查
				- in   not in 
				- between and
				- limit
				- group by  having
				- order by 
				- like "%a"
				- left join xx on  关系
				- 临时表
					select * from (select * from tb where id< 10) as B;
					
				- 
					select 
						id,
						name,
						1,
						(select count(1) from tb)
					from tb2
					
					SELECT 
						student_id,
						(select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
						(select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
						(select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
					from score as s1;

				- 条件
					select 
						course_id,
						max(num),
						min(num),
						min(num)+1,
						case when min(num) <10 THEN 0 ELSE min(num) END as c  
					from score GROUP BY course_id
					
					select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc;
		PS: 数据放在硬盘上
	
	
	
	思想：
		- 操作
		- 设计
	

	
今日内容：
	1. 练习题
		7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
		-- select score.student_id,student.sname from score
		-- 
		-- left join student on score.student_id=student.sid
		-- 
		-- where course_id =1 or course_id =2 GROUP BY student_id HAVING count(course_id) > 1


		8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
		-- select student_id from score where course_id in (
		-- 	select cid from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
		-- ) GROUP BY student_id having count(course_id) =  (select count(cid) from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师")
		-- 
		--
		10、查询有课程成绩小于60分的同学的学号、姓名；
		-- select student_id from score where num < 60 GROUP BY student_id
		-- select DISTINCT student_id from score where num < 60

		-- 查询没有学全所有课的同学的学号、姓名；

		11、查询没有学全所有课的同学的学号、姓名；
		-- select student_id,count(1) from score GROUP BY student_id  HAVING count(1) < (select count(cid) from course);
		-- 

		-- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
		-- select course_id from score where student_id = 1;
		-- select student_id from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id

		-- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
		-- select course_id from score where student_id = 1;
		-- select student_id,count(1) from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(course_id) from score where student_id = 1)


		-- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

		-- 获取和方少伟选课个数相同的通许
		-- select count(1) from score where student_id = 1;
		-- 

		-- select student_id from score where  student_id in (
		-- 	select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)
		-- ) and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)
		-- 
		-- 
		-- insert into tb(student_id,course_id,num) 
		-- 
		-- select student_id,2,(SELECT AVG(num) from score where course_id = 2) from score where course_id != 2

		-- 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
		-- 1  90  80   99
		-- 2  90  80   99
		-- SELECT 
		-- 	student_id,
		-- 	(select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
		-- 	(select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
		-- 	(select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
		-- from score as s1;
		-- 
		-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
		-- select course_id,max(num),min(num),min(num)+1,case when min(num) <10 THEN 0 ELSE min(num) END as c  from score GROUP BY course_id

		-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；


		select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc;


	
	

	pymysql模块：
		pip3 install pymysql -i https://pypi.douban.com/simple
		Python模块：对数据库进行操作（SQL语句）
		
		1. Python实现用户登录
		2. MySQL保存数据
		
		
		- 连接、关闭（游标）
		- execute()   -- SQL注入
		- 增删改： conn.commit()
		- fetchone fetchall
		- 获取插入数据自增ID
		
		
练习：

	权限管理
	
		权限表：
			1   订单管理
			2   用户管理
			3   菜单管理
			4   权限分配
			5   Bug管理
		
		用户表：
			1   Alex
			2   egon
		
		用户权限关系表：
			1    1
			1    2
			2    1
			
	Python实现：

		某个用户登录后，查看自己拥有所有权限

---