一、数据库的完整性
作用：控制用户输入的数据保存到数据库是正确的
实质：在创建表的时候给数据添加约束
分类：
实体完整性
域完整性
引用完整性
1.实体完整性
实体：表中的一行数据【一条记录】被称为一个实体
作用：标识每一行数据不重复
约束类型：
主键约束【primary key】
唯一约束【unique】
自动增长列【auto_increment】
1.1主键约束【primary key】
特点：数据唯一，不能为null
注意：主关键字是表中的一个字段，用于唯一的标记表中的某一条记录，在两个表的关系中，主关键字用来在一个表中引用另外一个表中的数据
演示命令： lichongchong@ubuntu01:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lcc | | mydb1 | | mydb2 | | mysql | | performance_schema | | practice | | sys | +--------------------+ 8 rows in set (0.02 sec) mysql> use mydb1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_mydb1 | +-----------------+ | worker | +-----------------+ 1 row in set (0.01 sec) mysql> drop table worker; Query OK, 0 rows affected (0.04 sec) mysql> show tables; Empty set (0.00 sec) mysql> create table stu1( id int primary key, name varchar(50)); Query OK, 0 rows affected (0.02 sec) mysql> create table stu2( -> id int, -> name varchar(50), -> primary key(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> create table stu3( -> id int, -> name varchar(50) -> ); Query OK, 0 rows affected (0.03 sec) mysql> alter table stu3 add constraint stu3_id primary key(id); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
1.2唯一约束【unique】
作用：确保键列中不出现重复数据
#注意： #1.primary key和unique这些约束不是必须的，主要根据需求而定，为了保证数据库的完整性，建议尽量加上，primary key用来约束当前数据库中的唯一标记字段，如果不想要数据重复，则建议添加unique #2.在同一个表中，可以对多个字段定义unique约束，但是只对一个字段添加primary key #3.primary key修饰的键的值不能为null，但是unique修饰的键的值可以为null 演示命令： mysql> create table stu4( -> id int primary key, -> name varchar(50) unique -> ); Query OK, 0 rows affected (0.04 sec)
1.3自动增长列【auto_increment】
作用：一般用于给主键添加自动增长的数值，只能是整数类型
注意：如果删除之前增长的序号，后面添加的序号不会重新开始，而是接着原来的显示
演示命令： mysql> create table stu5( -> id int primary key auto_increment, -> name varchar(50) unique -> ); Query OK, 0 rows affected (0.03 sec)
2.域完整性
域：当前单元格
作用：限制当前单元格数据的完整性，不和其他的单元格比对
约束分类：
数据类型
数字类型：int float double
二进制：blob
日期：date
字符型：char varchar
非空约束【not null】
默认值约束【default】
注意：域完整性和实体完整性同时使用
2.1非空约束
对应键的值不能为空，为空则报错
演示命令： mysql> create table stu6( -> id int primary key auto_increment, -> name varchar(50) unique not null -> ); Query OK, 0 rows affected (0.03 sec)
2.2默认值约束
如果不设置值，则使用默认值
演示命令： mysql> create table stu7( -> id int primary key auto_increment, -> name varchar(50) unique not null, -> address varchar(50) default "beijing" -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into stu7(name) values("aaaa"); Query OK, 1 row affected (0.01 sec) mysql> select * from stu7; +----+------+---------+ | id | name | address | +----+------+---------+ | 1 | aaaa | beijing | +----+------+---------+ 1 row in set (0.00 sec) mysql> insert into stu7(name) values("aaaa"); ERROR 1062 (23000): Duplicate entry 'aaaa' for key 'name' mysql> insert into stu7(name,address) values("aaaa","shenzhen"); ERROR 1062 (23000): Duplicate entry 'aaaa' for key 'name' mysql> insert into stu7(name,address) values("bbbb","shenzhen"); Query OK, 1 row affected (0.01 sec) mysql> select * from stu7; +----+------+----------+ | id | name | address | +----+------+----------+ | 1 | aaaa | beijing | | 4 | bbbb | shenzhen | +----+------+----------+ 2 rows in set (0.00 sec) mysql> insert into stu7(name,address) values("cccc",default); Query OK, 1 row affected (0.00 sec) mysql> select * from stu7; +----+------+----------+ | id | name | address | +----+------+----------+ | 1 | aaaa | beijing | | 4 | bbbb | shenzhen | | 5 | cccc | beijing | +----+------+----------+ 3 rows in set (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_mydb1 | +-----------------+ | stu1 | | stu2 | | stu3 | | stu4 | | stu5 | | stu6 | | stu7 | +-----------------+ 7 rows in set (0.00 sec)
3.引用完整性
外键：要有外键就必须有主键，主键和外键的类型必须一致
外键约束：foreign key
演示命令： mysql> create table student( -> stuid varchar(10) primary key, -> stuname varchar(50) -> ); Query OK, 0 rows affected (0.04 sec) mysql> create table score( -> stuid varchar(10), -> score int, -> courseid int -> ); Query OK, 0 rows affected (0.02 sec) mysql> insert into student(stuid,stuname) values("1001","jack"),("1002","tom"),("1003","bob"),("1004","zhangsan"); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into score(stuid,score,courseid) values("1001",67,1),("1001",80,2),("1002",70,3),("1002",67,2),("1003",90,1),("1004",75,2); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from student; +-------+----------+ | stuid | stuname | +-------+----------+ | 1001 | jack | | 1002 | tom | | 1003 | bob | | 1004 | zhangsan | +-------+----------+ 4 rows in set (0.00 sec) mysql> select * from score; +-------+-------+----------+ | stuid | score | courseid | +-------+-------+----------+ | 1001 | 67 | 1 | | 1001 | 80 | 2 | | 1002 | 70 | 3 | | 1002 | 67 | 2 | | 1003 | 90 | 1 | | 1004 | 75 | 2 | +-------+-------+----------+ 6 rows in set (0.00 sec) #添加外键约束 #如果两张表中有重名的键，如果不手动设置关联，则两张表之间完全独立的 #方式一;创建表的过程中进行关联 mysql> create table stud1( -> sid int primary key, -> name varchar(50) not null -> ); Query OK, 0 rows affected (0.05 sec) mysql> create table score1( -> sid int, -> score double, #给当前表中的某个键添加外键约束，就可以将两张表产生关联 -> constraint stu_sco_id foreign key(sid) references stud1(sid) -> ); Query OK, 0 rows affected (0.03 sec) #方式二：先创建表，然后通过alter修改 mysql> create table stud2( -> sid int primary key, -> name varchar(50) not null -> ); Query OK, 0 rows affected (0.03 sec) mysql> create table score2( -> sid int, -> score double -> ); Query OK, 0 rows affected (0.04 sec) mysql> alter table score2 add constraint stu_sco_id2 foreign key(sid) references stud2(sid); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
二、多表查询
1.表和表之间的关系
一对一
表1
qqid password 123456 abc123 表2
qqid name address hobby 123456 zhangsan 地球村 睡觉 一对多【多对一】
表1:学生表
sid name 1001 zhangsan 1002 lisi 1003 jack 表2：成绩表
sid score 1001 95 1001 100 1002 80 1003 75 多对多
表1：教师表
tid name 1001 王老师 1002 李老师 1003 张老师 表2：学生表
sid name 01 zhangsan 02 lisi 03 jack 表3：教师和学生之间的关系表
tid sid 1001 01 1002 01 1002 03 1003 02
2.使用
2.1合并结果集
作用：合并结果集就是把两个select语句查询的结果合并到一起
合并结果集的两种方式
union：去除重复记录【相当于求并集】
union all：不去除重复记录
演示命令： mysql> create table A( -> name varchar(50), -> score int -> ); Query OK, 0 rows affected (0.04 sec) mysql> create table B( -> name varchar(50), -> score int -> ); Query OK, 0 rows affected (0.04 sec) mysql> insert into A values("a",10),("b",20),("c",30); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into B values("a",10),("b",20),("c",30),("d",40); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from A; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | +------+-------+ 3 rows in set (0.00 sec) mysql> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | d | 40 | +------+-------+ 4 rows in set (0.00 sec) mysql> select * from A -> union -> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | d | 40 | +------+-------+ 4 rows in set (0.01 sec) mysql> select * from A -> union all -> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | a | 10 | | b | 20 | | c | 30 | | d | 40 | +------+-------+ 7 rows in set (0.00 sec) mysql> #问题：如果需要被合并的两张表列数不相同，该怎么解决？ 不再使用select * from。。。，使用select filed1,filed2 from filed1,filed2取决于列数比较少的表，作为参照物
2.2连接查询
作用：求出多个表的乘积
例如：t1连接t2,查询的结果为t1 * t2
mysql> select * from student; +-------+----------+ | stuid | stuname | +-------+----------+ | 1001 | jack | | 1002 | tom | | 1003 | bob | | 1004 | zhangsan | +-------+----------+ 4 rows in set (0.00 sec) mysql> select * from score; +-------+-------+----------+ | stuid | score | courseid | +-------+-------+----------+ | 1001 | 67 | 1 | | 1001 | 80 | 2 | | 1002 | 70 | 3 | | 1002 | 67 | 2 | | 1003 | 90 | 1 | | 1004 | 75 | 2 | +-------+-------+----------+ 6 rows in set (0.00 sec) #按照select * from student,score;进行连接查询，会产生笛卡尔积 #在实际应用中，需要去除重复的记录，则需要通过条件进行筛选 #解决方案：可以使用主外键的关系作为条件去除无用的信息 mysql> select * from student,score; +-------+----------+-------+-------+----------+ | stuid | stuname | stuid | score | courseid | +-------+----------+-------+-------+----------+ | 1001 | jack | 1001 | 67 | 1 | | 1002 | tom | 1001 | 67 | 1 | | 1003 | bob | 1001 | 67 | 1 | | 1004 | zhangsan | 1001 | 67 | 1 | | 1001 | jack | 1001 | 80 | 2 | | 1002 | tom | 1001 | 80 | 2 | | 1003 | bob | 1001 | 80 | 2 | | 1004 | zhangsan | 1001 | 80 | 2 | | 1001 | jack | 1002 | 70 | 3 | | 1002 | tom | 1002 | 70 | 3 | | 1003 | bob | 1002 | 70 | 3 | | 1004 | zhangsan | 1002 | 70 | 3 | | 1001 | jack | 1002 | 67 | 2 | | 1002 | tom | 1002 | 67 | 2 | | 1003 | bob | 1002 | 67 | 2 | | 1004 | zhangsan | 1002 | 67 | 2 | | 1001 | jack | 1003 | 90 | 1 | | 1002 | tom | 1003 | 90 | 1 | | 1003 | bob | 1003 | 90 | 1 | | 1004 | zhangsan | 1003 | 90 | 1 | | 1001 | jack | 1004 | 75 | 2 | | 1002 | tom | 1004 | 75 | 2 | | 1003 | bob | 1004 | 75 | 2 | | 1004 | zhangsan | 1004 | 75 | 2 | +-------+----------+-------+-------+----------+ 24 rows in set (0.00 sec) #方式一：使用where添加条件进行数据的过滤 #student.stuid=score.stuid 表名.字段名 mysql> select * from student,score where student.stuid=score.stuid; +-------+----------+-------+-------+----------+ | stuid | stuname | stuid | score | courseid | +-------+----------+-------+-------+----------+ | 1001 | jack | 1001 | 67 | 1 | | 1001 | jack | 1001 | 80 | 2 | | 1002 | tom | 1002 | 70 | 3 | | 1002 | tom | 1002 | 67 | 2 | | 1003 | bob | 1003 | 90 | 1 | | 1004 | zhangsan | 1004 | 75 | 2 | +-------+----------+-------+-------+----------+ 6 rows in set (0.01 sec) #问题：主键stuid还是显示了两遍 mysql> select * from student s,score c where s.stuid=c.stuid; +-------+----------+-------+-------+----------+ | stuid | stuname | stuid | score | courseid | +-------+----------+-------+-------+----------+ | 1001 | jack | 1001 | 67 | 1 | | 1001 | jack | 1001 | 80 | 2 | | 1002 | tom | 1002 | 70 | 3 | | 1002 | tom | 1002 | 67 | 2 | | 1003 | bob | 1003 | 90 | 1 | | 1004 | zhangsan | 1004 | 75 | 2 | +-------+----------+-------+-------+----------+ 6 rows in set (0.01 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec)
1>内连接
[inner] join on
mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #内连接，等价于上面where方式实现的结果 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #练习：查询成绩大于70的学生信息 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid where score>70; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 80 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 3 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid and score>70; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 80 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 3 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid and score>70; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 80 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 3 rows in set (0.00 sec)
2>外连接
[outer] join on
左外连接：left [outer] join on
右外连接：right [outer] join on
全外连接：mysql不支持，full join on
特点：使用外连接，都是以其中一个表作为参照连接另外一个表
演示命令： #左外连接 #参照student mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #参照score mysql> select s.stuid,s.stuname,c.score,c.courseid from score c left join student s on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #右外连接 #参照score mysql> select s.stuid,s.stuname,c.score,c.courseid from student s right join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #参照student mysql> select s.stuid,s.stuname,c.score,c.courseid from score c right join student s on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec)总结： 1.A join on B A和B匹配的行会出现在结果集中 2.A left join on B A和B匹配的行会出现在结果集中，外加A中独有的数据，未对应的数据使用null填充 3.A right join on B A和B匹配的行会出现在结果集中，外加B中独有的数据，未对应的数据使用null填充 4.如果A和B中的数据正好都能匹配上，使用上面三种方式查询的结果完全相同 演示命令： mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from score c left join student s on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) mysql> insert into student values("1005","bob"); Query OK, 1 row affected (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | | 1005 | bob | NULL | NULL | +-------+----------+-------+----------+ 7 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | | 1005 | bob | NULL | NULL | +-------+----------+-------+----------+ 7 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from score c left join student s on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) mysql> insert into student values("1006","aaa"); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +-------+----------+ | stuid | stuname | +-------+----------+ | 1001 | jack | | 1002 | tom | | 1003 | bob | | 1004 | zhangsan | | 1005 | bob | | 1006 | aaa | +-------+----------+ 6 rows in set (0.00 sec) mysql> select * from score; +-------+-------+----------+ | stuid | score | courseid | +-------+-------+----------+ | 1001 | 67 | 1 | | 1001 | 80 | 2 | | 1002 | 70 | 3 | | 1002 | 67 | 2 | | 1003 | 90 | 1 | | 1004 | 75 | 2 | +-------+-------+----------+ 6 rows in set (0.00 sec) mysql> insert into score values("1007",46,2); Query OK, 1 row affected (0.01 sec) mysql> select * from score; +-------+-------+----------+ | stuid | score | courseid | +-------+-------+----------+ | 1001 | 67 | 1 | | 1001 | 80 | 2 | | 1002 | 70 | 3 | | 1002 | 67 | 2 | | 1003 | 90 | 1 | | 1004 | 75 | 2 | | 1007 | 46 | 2 | +-------+-------+----------+ 7 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | | 1005 | bob | NULL | NULL | | 1006 | aaa | NULL | NULL | +-------+----------+-------+----------+ 8 rows in set (0.00 sec) mysql> select s.stuid,s.stuname,c.score,c.courseid from score c left join student s on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | | NULL | NULL | 46 | 2 | +-------+----------+-------+----------+ 7 rows in set (0.00 sec)
3>自然连接
select * from student,score;得到的是student * score的结果，产生笛卡尔积，出现大量的重复数据
select * from student s,score c where s.stuid=c.stuid;
select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;
natural join
#连接查询会产生笛卡尔积，通过使用主外键关系等式去除它，而自然连接无需给出主外键连接等式，它会自动找到这一等式 #方式一：自然连接 mysql> select * from student natural join score; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.01 sec) #方式二：内连接 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #方式三：where子句 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s, score c where s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | jack | 67 | 1 | | 1001 | jack | 80 | 2 | | 1002 | tom | 70 | 3 | | 1002 | tom | 67 | 2 | | 1003 | bob | 90 | 1 | | 1004 | zhangsan | 75 | 2 | +-------+----------+-------+----------+ 6 rows in set (0.00 sec) #说明：自然连接可以简化内连接【join on】
2.3子查询
一个select语句中包含另外一个select语句【select语句嵌套】
子查询就是嵌套查询，一个sql语句中可以同时出现两条或者两条以上的select语句
注意：
a.子查询出现的位置
where的后面，作为条件的一部分查询
b.子查询如果出现在where的后面作为条件，可以结合关键字any,all使用
c.子查询结果集的形式
单行单列【作为条件】、单行多列【作为条件】、多行单列【作为条件】、多行多列【表】
演示命令 #查询部门编号为20的所有员工 mysql> select * from emp where deptno=20; +-------+---------+---------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+---------+------+------------+------+------+--------+ | 7369 | smith | clark | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000 | NULL | 20 | +-------+---------+---------+------+------------+------+------+--------+ 2 rows in set (0.00 sec) #1.查询和ward在同一个部门的员工 #思路：先查询ward所在的部门编号，然后再通过部门查询其他的员工 mysql> select * from emp where empname="ward"; +-------+---------+----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+----------+------+------------+------+------+--------+ | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | +-------+---------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select deptno from emp where empname="ward"; +--------+ | deptno | +--------+ | 30 | +--------+ 1 row in set (0.00 sec) mysql> select * from emp where deptno =30; +-------+---------+----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+----------+------+------------+------+------+--------+ | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | +-------+---------+----------+------+------------+------+------+--------+ 5 rows in set (0.00 sec) mysql> select * from emp where deptno=(select deptno from emp where empname="ward"); +-------+---------+----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+----------+------+------------+------+------+--------+ | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | +-------+---------+----------+------+------------+------+------+--------+ 5 rows in set (0.01 sec) #2.查询工资高于scott的员工 #思路：首先查scott的工资，在查高于 #3.查询工资高于20号部门所有人工资的员工 #思路：查询20号部门的最高工资，再查高于 #4.查询工作和工资和allen完全相同的员工 #思路：查询allen的工作和工资，再查找相同 mysql> select * from emp where job=(select job from emp where empname="allen") and sal=(select sal from emp where empname="allen"); +-------+---------+----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+----------+------+------------+------+------+--------+ | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | +-------+---------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select * from emp where (job,sal) in(select job,sal from emp where empname="allen"); +-------+---------+----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+----------+------+------------+------+------+--------+ | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | +-------+---------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) #5.查询有两个以上下属的员工的信息 mysql> select mgr,count(mgr) from emp group by mgr; +------+------------+ | mgr | count(mgr) | +------+------------+ | NULL | 0 | | 7566 | 1 | | 7698 | 3 | | 7839 | 3 | | 7902 | 1 | +------+------------+ 5 rows in set (0.00 sec) mysql> select mgr,count(mgr) from emp group by mgr having count(mgr)>2; +------+------------+ | mgr | count(mgr) | +------+------------+ | 7698 | 3 | | 7839 | 3 | +------+------------+ 2 rows in set (0.00 sec)
2.4自连接
自连接：自己连接自己，相当于起别名
类似于子查询
#1.查询员工编号为7782的姓名，上级的姓名和编号 mysql> select mgr from emp where empno=7782; +------+ | mgr | +------+ | 7839 | +------+ 1 row in set (0.00 sec) mysql> select * from emp where empno=7839; +-------+---------+-----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+-----------+------+------------+------+------+--------+ | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+---------+-----------+------+------------+------+------+--------+ 1 row in set (0.01 sec) mysql> select * from emp where empno=(select mgr from emp where empno=7782); +-------+---------+-----------+------+------------+------+------+--------+ | empno | empname | job | mgr | hiredate | sal | comm | deptno | +-------+---------+-----------+------+------------+------+------+--------+ | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+---------+-----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) #2.查询各个部门中薪水最高的员工 #思路：查询各个部门的最高薪水，然后根据薪水查询员工信息 mysql> select max(sal) from emp group by deptno; +----------+ | max(sal) | +----------+ | 5000 | | 3000 | | 2975 | +----------+ 3 rows in set (0.00 sec) mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000 | | 20 | 3000 | | 30 | 2975 | +--------+----------+ 3 rows in set (0.00 sec) mysql> select * from emp where sal in(select max(sal) from emp group by deptno); +-------+---------+
三、数据库的备份和恢复
1.备份
生成SQL脚本，导出数据
#命令：mysqldump -u root -p 数据库名>生成的脚本的文件路径 #本质：将数据库中的内容写入到一个指定的文件上【dump】 #说明：本地的文件的后缀.sql 演示命令： mysql> exit Bye lichongchong@ubuntu01:~$ mysqldump -u root -p mydb2>/home/lichongchong/Desktop/text.sql Enter password: #或者 lichongchong@ubuntu01:~$ mysqldump -u root -prock1204 mydb1>/home/lichongchong/Desktop/text.sql #回车之后可以不用输入密码 #登录数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lcc | | mydb1 | | mydb2 | | mysql | | performance_schema | | practice | | sys | +--------------------+ 8 rows in set (0.00 sec) mysql> drop database mydb2; Query OK, 2 rows affected (0.02 sec) #注意：mysqldump备份的过程中其实先要登录数据库，文件可以不存在，在进行写入内容的过程中会自动创建
2.恢复
执行SQL脚本，恢复数据
#前提：登录数据库，创建一个新的数据库[新创建的数据库的名称可以和原来的数据库的名称不同] #流程： mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lcc | | mydb1 | | mysql | | performance_schema | | practice | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql> create database text; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lcc | | mydb1 | | mysql | | performance_schema | | practice | | sys | | text | +--------------------+ 8 rows in set (0.00 sec) mysql> use text; #注意：将数据库切换到要恢复的数据库下 Database changed #source:让后面的文件生效 mysql> source /home/lichongchong/Desktop/text.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_text | +----------------+ | emp | | student | +----------------+ 2 rows in set (0.00 sec) mysql> select * from emp;
四、MySQL中的内置函数
select xxx
演示命令： mysql> select date_add(now(),interval -1 month); +-----------------------------------+ | date_add(now(),interval -1 month) | +-----------------------------------+ | 2018-11-25 16:16:28 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select charset("abc"); +----------------+ | charset("abc") | +----------------+ | utf8 | +----------------+ 1 row in set (0.00 sec) mysql> select concat("aaa","bbbb"); +----------------------+ | concat("aaa","bbbb") | +----------------------+ | aaabbbb | +----------------------+ 1 row in set (0.00 sec) mysql> select instr("root","t"); +-------------------+ | instr("root","t") | +-------------------+ | 4 | +-------------------+ 1 row in set (0.01 sec) mysql> select instr("root","a"); +-------------------+ | instr("root","a") | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec) mysql> select right("abcd",2); +-----------------+ | right("abcd",2) | +-----------------+ | cd | +-----------------+ 1 row in set (0.00 sec) mysql> select left("abcd",2); +----------------+ | left("abcd",2) | +----------------+ | ab | +----------------+ 1 row in set (0.00 sec) mysql> select replace("hello","llo","ab"); +-----------------------------+ | replace("hello","llo","ab") | +-----------------------------+ | heab | +-----------------------------+ 1 row in set (0.00 sec) mysql> select format(100.992,2); +-------------------+ | format(100.992,2) | +-------------------+ | 100.99 | +-------------------+ 1 row in set (0.00 sec) mysql> select format(100.996,2); +-------------------+ | format(100.996,2) | +-------------------+ | 101.00 | +-------------------+ 1 row in set (0.00 sec) mysql> select abs(-10); +----------+ | abs(-10) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> select least(10,5,23); +----------------+ | least(10,5,23) | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) mysql>
五、MySQL事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
注意事项：
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- **事务用来管理 insert,**update,delete语句
1.事务性质
事务具有以下四个标准属性，通常由首字母缩写ACID简称：
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
注意事项：
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交
2.事务控制语句
- BEGIN或START TRANSACTION；显式地开启一个事务；
- COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
- RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier；把事务回滚到标记点；
- SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
3.MySQL事务处理
MySQL事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN：开始一个事务
- ROLLBACK： 事务回滚
- COMMIT： 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0：禁止自动提交
- SET AUTOCOMMIT=1： 开启自动提交
#1.不管autocommit 是1还是0 START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。 #2.当autocommit 为 0 时 不管有没有START TRANSACTION。 只有当commit数据才会生效，ROLLBACK后就会回滚。 #3.如果autocommit 为1 ，并且没有START TRANSACTION 。 调用ROLLBACK是没有用的。即便设置了SAVEPOINT。
#关键字begin，commit和rollback主要用于mysql的事务 #commit：当一个事务处理完成之后，commit会对所有涉及到的表生效 #rollback：如果sql语句执行的过程中，发生故障，使用rollback可以将每个涉及到的表恢复到事务开始【begin】之前的状态 begin---->commit begin-----<rollback 演示命令： mysql> create table abc( id int ); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_text | +----------------+ | abc | | emp | | student | +----------------+ 3 rows in set (0.00 sec) mysql> begin; #开启事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into abc value(5); Query OK, 1 row affected (0.00 sec) mysql> insert into abc value(6); Query OK, 1 row affected (0.00 sec) mysql> commit; #提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from abc; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; #开启事务 Query OK, 0 rows affected (0.00 sec) mysql> select * from abc; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.00 sec) mysql> insert into abc value(7); Query OK, 1 row affected (0.00 sec) mysql> select * from abc; +------+ | id | +------+ | 5 | | 6 | | 7 | +------+ 3 rows in set (0.00 sec) mysql> rollback; #回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from abc; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.00 sec)
六、MySQL与Python
1.安装第三方模块
mysql和Python的交互
为了实现mysql和python的交互，python提供了一个PyMySQL模块
在py文件中import PyMySQL
PyMySQL属于第三方模块，在使用之前需要进行安装
PyMySQL是从Python连接到mysql数据库的接口
#第一步：pip3 install xxx """ 问题说明： 1.权限不够 sudo pip3 install xxx 2.需要更新 sudo pip install --upgrade pip #当前系统中有最新版本，你的版本比较低 """ #第二步；与pycharm建立连接 在pycharm中settings设置,将PyMySQL安装 #第三步：在py文件中进行使用，import pymysql
2.使用
代码演示：
import pymysql #1：链接数据库 #参数：mysql服务器地址，用户名，密码，数据库名 #注意：先登录数据库，在数据库中必须存在一个名为testdb的数据库 db = pymysql.connect("localhost","root","rock1204","testdb"); #2.获取一个游标 #游标的作用：执行sql中的语句，通过游标完成的 cur = db.cursor(); #3.其他操作 """ #1. #查看当前数据库的版本信息 cur.execute("select version()") #获取游标中的一条信息 data = cur.fetchone() print(data) """ #2.创建表 cur.execute("DROP TABLE IF EXISTS student") #相当于sql语句的操作 sql = """create table student( id int primary key, name varchar(20) not null, score double default 0 );""" cur.execute(sql) print("ok") #4.关闭连接 db.close()
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
