python进阶08 MySQL基础补充

本次课程都是基于三张表格的使用

一、子查询

#如何找到‘张三’的成绩
#思路：先找到张三的学号，在拿这个张三的学号到成绩表里面去匹配，得出成绩
#如何用一条查询来得到结果
SELECT grade FROM grades WHERE student_number=(SELECT number FROM students WHERE name='张三');#后面括号内表示将学生表的查询结果，然后将这个查询结果嵌套到成绩表中去

#子查询不常用，99%的查询可以用连接查询来实现，所以一般都用连接查询

二、连接查询

　　1、在展示成绩的同时展示出其他相关信息

#连接（join）
(a JOIN b)#将a表和b表拼接在一起
SELECT * FROM (a JOIN b)#查询a表和b表拼接后的结果

va      vb
1         a
2         b
3

#a和b拼接后（交叉连接，又称笛卡尔连接）,会列出所有情况，其中一定有你需要的组合，筛选的工作需要自己来
va      vb
1         a
2         a
3         a
1         b
2         b
3         b

#交叉连接会列出所有情况，有些不需要的他也会给你列出来，要是行数太多的话太浪费资源了，所以我们要在列的时候就筛选
(stedents JOIN grades ON students.number=grades.students_number); #用ON添加条件，前一个表的学生编号和后一个表的学生编号一样

SELECT students.number,name,grades FROM (stedents JOIN grades ON students.number=grades.students_number);

SELECT students.number,name,grades FROM (stedents JOIN grades) WHERE students.number=grades.students_number;#这种思路也可以达到上面的效果，但这个是先拼接在筛选，速度比上面那个慢多了

#多个表拼接时的注意事项 
(students JOIN grades ON students.number=grades.student_number JOIN subject ON grades.subject_number=subject.number) #两个条件要分开，三个表一起拼接

三、其他约束

　　1、插入一条记录时，某列没有插入值，会怎么样

#默认值
#不设置默认值的时候，默认值就是null
CREATE TABLE t_default(default_value INT);#不设置默认值，默认值就是null
INSERT INTO t_default VALUES ();#输出一各空值
SELECT * FROM t_default;#显示一个null

#设置默认值
CREATE TABLE t_default(default_value INT DEFAULT 10);#用DEFAULT 这个关键字来设置默认值 10 
#这样，在没有给定该列值得情况下，使用默认值10

SHOW CREATE TABLE t_default G; #在查询创建信息后面加上一个G，可以显示默认值

　　2、确保某列的值不为空

#非空
#要限制一个列的值不能为空的关键字NOT NULL
CREATE TABLE t_notnull(notnull_value INT ONT NULL);#在后面加个ONT NULL 这一列就设置为非空列，必须填数据
#这样就不能在这一列中插入空数据了

　　3、确保某列的值不重复

#唯一键 UNIQUE KEY()#在括号内填入需要设置为唯一键的列
CREATE TABLE t_unique(unique_value INT,other_value INT,UNIQUE KEY(unique_value))#unique_value这一列的值就不能重复了

#主键
#通常每张表都需要一个主键来体现唯一性（同时要非空，与唯一键的区别就在这里）
CREATE TABLE t_primarykey(primary_value INT,PRIMARY KEY(primary_value))#这样就把primary_value这个列变为了主键

#主键=非空+唯一

#自增长
#如果希望某列整数（必须是整型）的值，默认加一填充
CREATE TABLE t_incr(incr_val INT AUTO_INCREMENT,PRIMARY KEY(incr_val))) #通常使用了AUTOINCREMENT的列是一个主键

INSERT INTO t_incr VALUES ();#按道理来说，主键非空，所以输入空值会报错，但由于有自增长在，所以他会自动给你加上去1

#功能：自动生成ID号或者流水号，号码并不在乎，单纯为了保证每个号码不同

　　4、确保某列的值必须参照另一列

#外键
#默认情况下，一列数据与其他列之间没有什么关系，如果希望某列的数据与某张表主键中有的值
#换句话说，我要限制A表的学号必须全部都是在B表中有的

CREATE TABLE c(c_col INT,PRIMARY KEY(c_col));#设定c表的主键

CREATE TABLE d(d_col INT,FOREIGN KEY(d_col) REFERENCES c(c_col));#这样，在向d表插入值时，如果d_col的值不在c表的c_col列中，则插入失败，插入的数值不会限制唯一性

四、补充

　　1、多个地方需要操作mysql那么游标需要多个

cur1=conn.cursor()
cur2=conn.cursor()
cur3=conn.cursor()
cur4=conn.cursor()

　　2、关于表结构的修改

#1、对开发而言不常用，项目很少回去做改动
#2、改表结构的时候，会出现数据丢失，这就需要专门的DBA（数据库管理员）或运维来做

　　3、作业

CREATE TABLE students(
  id INT AUTO_INCREMENT ,#都加上非空
  number CHAR (9) NOT NULL ,
  name VARCHAR (20) NOT NULL ,
  age INT DEFAULT 0 ,#设置默认值为0
  birth DATE ,#无限制
  UNIQUE KEY (number) ,#唯一键
  PRIMARY KEY (id)#主键
);

CREATE TABLE subjects(
  id INT AUTO_INCREMENT,
  number CHAR (4) NOT NULL ,
  title VARCHAR (30) NOT NULL ,
  duration INT NOT NULL ,

  UNIQUE KEY (number),
  PRIMARY KEY (id)
);

CREATE TABLE grades (
  id INT AUTO_INCREMENT,
  stud_number CHAR (9) NOT NULL ,
  subj_number CHAR (4) NOT NULL,
  grade INT NOT NULL,
  UNIQUE KEY (stud_number,subj_number) #这样就可以让他俩的组合唯一
  PRIMARY KEY (id)
);

CREATE TABLE grades(
  id INT AUTO_INCREMENT,
  stud_number CHAR (9)  ,
  subj_number CHAR (4) ,
  grade INT NOT NULL,

  FOREIGN KEY (stud_number) REFERENCES students(number),#外键的实现
  FOREIGN KEY (subj_number) REFERENCES subjects(number),
  PRIMARY KEY (id)
);