image.png

笔记部分参考香草味的橙子、https://www.bilibili.com/video/av39807944/?p=1

mysql学习笔记**

关系型数据库

一、如何使用终端操作数据库

1.数据库的启动

https://www.cnblogs.com/winton-nfs/p/11524007.html

启动

net start mysql

mysql -u (username) -p(password)
//root root

2.如何查询数据库服务器中所有数据库

show databases;

3.如何选中某一个数据库进行操作

create database (database name);
use (database name);
show tables;
select * from (table name);//显示完整表

4.如何创建一个数据表? 创建一个pet表

create TABLE pet(
                   name VARCHAR(20),
                   owner VARCHAR(20),
                   specise VARCHAR(20),
                   sex CHAR(1),
                   brith DATE,
                   death DATE );

注意事项:

var()与varchar()的区别在于var()是定常的,哪怕存储的字符串没有达到"()"中数字的上限,var()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到"()"中的上限则会自动去掉后面的空格;
性别不要用:sex 要用:gender 一个是性一个是性别;
定义最后一个字段的时候不要加",";
上面的"VAR","VARCHAR","DATE"可以用小写.不过最好用大写来表示区分关键字,若不然也许写到后面你自己都不知道这个词是数据库中的关键字还是你自己自定义的一些数据,同时一定要用英文的标点符号也必须半角输入

5.如何查看数据表的架构?

describe tableName;

说明:

Field	Type	Null	Key	Default	Extra

Field : 字段的名称
Type : 字段的类型,可以有int var varchar
Key : 是否是关键字如可以定义为: primary key 或者 unique key ...
Default: : 若是该字段没有主动设置值的时候,该字段的默认值是什么?

6.如何查看数据表的架构

INSERT INTO pet VALUES('kk','cc','dog','1','1998-8-2',null);

注：

NULL代表空值，不能主动填写NULL，否则表示该属性值为null

另一种写法：

INSERT INTO pet(name,owner) VALUES ('xx','cc');
//代表我只在name和owner字段上面插入的一条,其他皆为NULL/默认值的数据

7.mysql常用数据类型

注意:金钱最好用int/bigint(整数,单位用分,拿出来进行*100换成元),千万不要直接用浮点,会有精度损失.

8.如何删除数据

DELETE FROM tablesName WHRER 条件;//删除
UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;//更新表格中符合条件的数据

二、mysql建表中的约束

1.主键约束

它能够唯一确定一张表中的一条记录,增加主键约束之后,就可以使得字段不重复而且不为空

create table user(
    id int PRIMARY KEY,
    name VARCHAR(20)
);
INSERT INTO user VALUES (1,'张三');

+----+------+
| id | name |
+----+------+
|  1 | 张三 |
+----+------+

运行DESCRIBE user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

发现 id是不可以为null 而且 key的值也变为:PRI(primary)

2.复合主键

CREATE TABLE user2(
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY key(id,name)
);

运行DESCRIBE user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

INSERT INTO user2 VALUES (1,'老王','123456');
INSERT INTO user2 VALUES (2,'老王','123456');

+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 老王 | 123456   |
|  2 | 老王 | 123456   |
+----+------+----------+

说明了复合主键只要所有的字段都不是相同的情况下可以允许其中的字段重复:

INSERT INTO user2 VALUES (1,'老李','123456');

SELECT * FROM user2;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 老李 | 123456   |
|  1 | 老王 | 123456   |
|  2 | 老王 | 123456   |
+----+------+----------+
场景:表中有班级号以及学生座位号,我们可以用班级号+学生的座位号可以准确的定位一个学生,如:(1班5号可以准确的确定一个学生)，即班级和座位号不能都一样

3.自增约束

CREATE TABLE user3(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

运行DESCRIBE user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

INSERT INTO user3(name) VALUES('张三');
INSERT INTO user3(name) VALUES('李四');
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
没有自定义id值 但是自动生成了id

4.唯一约束

CREATE TABLE user5(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
运行 DESCRIBE user5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

新增name为唯一约束:
ALTER TABLE user5 ADD UNIQUE(name);
运行 ALTER TABLE user5 ADD UNIQUE(name);
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
测试:插入数据
INSERT INTO user5(name) VALUES ('cc');
运行 SELECT * FROM user5; 查看结果:
+----+------+
| id | name |
+----+------+
|  1 | cc   |
+----+------+
再次插入INSERT INTO user5(name) VALUES ('cc');
出现:ERROR 1062 (23000): Duplicate entry 'cc' for key 'name'

换个试试 INSERT INTO user5(name) VALUES ('aa');
运行 SELECT * FROM user5; 查看结果:
+----+------+
| id | name |
+----+------+
|  3 | aa   |
|  1 | cc   |
+----+------+
总结一下:
    主键约束(primary key)中包含了唯一约束
场景:业务需求:设计一张用户注册表,用户姓名必须要用手机号来注册,而且手机号和用户名称都不能为空,那么:
CREATE TABLE user_test(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
    name VARCHAR(20) NOT NULL COMMENT'用户姓名,不能为空',
    phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'
);
运行 DESCRIBE user_test;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20) | NO   |     | NULL    |                |
| phone_number | int(11)     | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.
用户姓名可以重复,但是手机号码却不能重复,复合正常的逻辑需求

5.非空约束

在user_test中已经添加了非空约束: NOT NULL;
name和phone_number都设置了非空,先只设置name参数不设置phone_number参数试一试
INSERT INTO user_test (name) VALUES ('张三');
会出现Field 'phone_number' doesn't have a default value

两个非空参数一起设置:
INSERT INTO user_test (name,phone_number) VALUES ('张三','12345678901');
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
|  1 | 张三 | 12345678901  |
+----+------+--------------+

6.默认约束

当插入字段值的时候，没有传值则使用默认值

create table user10(
id int,
name varchar(20),
    age int default 10
);

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+

insert into user10 (id,name) values(1,'zhangsan');
mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)
//没填年龄时，可以看到年龄的默认值为10

7.外键约束

涉及到两个表，父表（主表），子表（副表）

create table classes(
    id int primary key,
    name varchar(20)
);

create table students(
    id int primary key,
    name varchar(20),
    class_id int,    
    foreign key(class_id) references classes(id)
);

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

insert into classes (id,name) values(1,'一班');
insert into classes (id,name) values(2,'二班');
insert into classes (id,name) values(3,'三班');
insert into classes (id,name) values(4,'四班');
mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | 一班 |
|  2 | 二班 |
|  3 | 三班 |
|  4 | 四班 |
+----+------+
//或者将id改为自增    只能增加新列为自增，转移数据，删除原有列。或者建立新表转移数据

insert into students values(1001,'zhangsan',1);
insert into students values(1002,'zhangsan',2);
insert into students values(1003,'zhangsan',3);
insert into students values(1004,'zhangsan',4);

mysql> select * from students;
+------+----------+----------+
| id   | name     | class_id |
+------+----------+----------+
| 1001 | zhangsan |        1 |
| 1002 | zhangsan |        2 |
| 1003 | zhangsan |        3 |
| 1004 | zhangsan |        4 |
+------+----------+----------+
4 rows in set (0.00 sec)
//但无法插入5班，因为班级表没有5班
insert into students values(1005,'zhangsan',5);

mysql> insert into students values(1005,'zhangsan',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
mysql>

1、主表中没有的数据，副表无法使用

2、主表中数据被副表引用，则该数据不可以在主表被删除

delete form classes where id=4;
mysql> delete form classes where id=4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'classes where id=4' at line 1

8.如何建表之后添加主键约束

三、数据库的三大设计范式

其实一共有六个设计范式，一般满足三个即可。

目前关系数据bai库有六种范式，即第一du范式（1NF）、第二范式（zhi2NF）、第dao三范式（3NF）、巴斯−科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式依次类推。一般来说，数据库只需满足第三范式（3NF）。

第一范式（1NF）第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。

简而言之，第一范式（1NF）是最基本的范式，如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足第一范式（1NF）。在任何一个关系数据库中，第一范式（1NF）是对关系模式设计的基本要求，所有设计的数据模型都必须满足第一范式（1NF）

1、第一范式（1NF）

数据表中的所有字段都是不可分割的原子值

create table student2(
    id int primary key,
    name varchar(20),
    address varchar(30)
);
insert into student2 values(1,'zhangsan','中国江苏南京龙山北路100号');
insert into student2 values(2,'liu','中国北京王府井200号');
insert into student2 values(3,'jarvis','中国四川省武侯区武侯大道10号');

mysql> select * from student2;
+----+----------+---------+
| id | name     | address |
+----+----------+---------+
|  1 | zhangsan | 中国江苏南京龙山北路100号   |
|  2 | liu      | 中国北京王府井200号 |
|  3 | jarvis   | 中国四川省武侯区武侯大道10号     |
+----+----------+---------+
3 rows in set (0.00 sec)

student2 的address字段值还可以继续拆分，不满足第一范式

create table student3(
    id int primary key,
    name varchar(20),
    country varchar(30),
    provience varchar(20),
    city varchar(30),
    details varchar(30)
);

这个表相对于上一个更满足第一范式

范式，设计的越详细，对于某些实际操作可能更方便，但也会带来处理的麻烦。

实际中要根据实际情况设计，不一定非要拘泥于范式。

2、第二范式（2NF）

在满足第一范式的前提下，第二范式要求，除了主键外的每一列都必须完全依赖于主键

如果要出现不完全依赖，只可能发生在联合主键的情况下。

//订单表
create table myorder(
    product_id int,
    customer_id int,
    product_name varchar(20),
    customer_name varchar(20),
    primary key(product_id,customer_id)
);

问题：顾客名只与顾客id有关，货品名只与货品ID有关（主键之外的其他列，只依赖于主键的部分字段）

不满足第二范式。

拆表。

create table myorder(
    order_id int primary key,
    product_id int,
    foreign key(product_id) references product(id)，
    customer_id int，
    foreign key(customer_id) references customer(id)
);
create table product(
    id int primary key,
    name varchar(20)
);
create table customer(
    id int primary key,
    name varchar(20)
);

此时满足了第二范式

3、第三范式（3NF）

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系

create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int，
   customer_phone varchar(15)
);
问题：phone列和顾客id列产生了关系，应该把phone放到顾客表中
create table customer(
    id int primary key,
    name varchar(20),
    phone varchar(15)
);

四、查询练习

先创建几个表

学生表 student
- 学号
- 姓名
- 性别
- 生日
- 班级
课程表 course
- 课程号
- 课程名称
- 任课教师
成绩表 score
- 学号
- 课程号
- 成绩
教师表 teacher
- 教师号
- 教师名
- 性别
- 生日
- 职称
- 所在部门

create table student(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(10) not null,
    sbirthday datetime,
    class varchar(20)
);
create table course(
    cno varchar(20) primary key,
    cname varchar(20) not null,
    tno varchar(20) not null,
    foreign key(tno) references teacher(tno)
);
create table teacher(
    tno varchar(20) primary key,
    tname varchar(20) not null,
    tsex varchar(10) not null,
    tbirthday datetime,
    prof varchar(20),
    depart varchar(20) not null
);
create table score(
    sno varchar(20) not null,
    cno varchar(20) not null,
    degree decimal,
    foreign key(sno) references student(sno),
    foreign key(cno) references course(cno),
    primary key(sno,cno)
);

添加数据

insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆军','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-02-20','95033');
insert into student values('108','张全蛋','男','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');

insert into teacher values('804','李成','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-12-02','讲师','电子');
insert into teacher values('825','王萍','女','1972-12-02','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-12-02','助教','电子');

insert into course values('3-105','计算机','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高数','831');

insert into score values ('103','3-245','86');
insert into score values ('105','3-245','75');
insert into score values ('109','3-245','68');
insert into score values ('103','3-105','92');
insert into score values ('105','3-105','88');
insert into score values ('109','3-105','76');
insert into score values ('103','6-166','85');
insert into score values ('105','6-166','79');
insert into score values ('109','6-166','81');

查询

1~10

1595919329871.png

1/
select * from student;
2/
select sname,ssex,class from student;
3/   distinct 排除重复
select distinct depart from teacher;
4/
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;
5/
select * from score where degree in(85,86,88);
6/
select * from student where class='95031' or ssex='女';
7/   asc desc  排序
select * from student order by class desc;
8/
select * from score order by cno asc,degree desc;
9/   count
select count(*) from student where class='95031';
10/ 嵌套方法  排序方法
select sno,cno from score where degree=(select max(degree) from score);

select sno,cno from score order by degree desc limit 0,1;

11.查询每门课的平均成绩

select avg(degree) from score where cno='3-105';
select cno,avg(degree) from score group by cno;

group by 分组 把某个属性按照值相同的分组

12.查询score表中至少有两名学生选修的并以3开头的课程的平均分

select cno from score group by cno having count(cno)>=2 and cno like '3%';

13.查询score table中分数大于70 小于90的sno列

select sno,degree from score where degree>70 and degree <90;

select sno,degree from score where degree between 70 and 90;

14.查询所有学生的sname，cno和degree列(联合查询两个表)

select sname,sno from student;
select cno degree from score;

select sname,cno,degree from student,score where student.sno = score.sno;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 王丽   | 3-105 |     92 |
| 王丽   | 3-245 |     86 |
| 王丽   | 6-166 |     85 |
| 王芳   | 3-105 |     88 |
| 王芳   | 3-245 |     75 |
| 王芳   | 6-166 |     79 |
| 赵铁柱 | 3-105 |     76 |
| 赵铁柱 | 3-245 |     68 |
| 赵铁柱 | 6-166 |     81 |
+--------+-------+--------+
9 rows in set (0.07 sec)

15.查询所有学生的sno，cname和degree列(2表查询)

select sno,cname,degree from course,score where course.cno = score.cno;

mysql> select sno,cname,degree from course,score where course.cno = score.cno;
+-----+----------+--------+
| sno | cname    | degree |
+-----+----------+--------+
| 103 | 计算机   |     92 |
| 105 | 计算机   |     88 |
| 109 | 计算机   |     76 |
| 103 | 操作系统 |     86 |
| 105 | 操作系统 |     75 |
| 109 | 操作系统 |     68 |
| 103 | 数字电路 |     85 |
| 105 | 数字电路 |     79 |
| 109 | 数字电路 |     81 |
+-----+----------+--------+
9 rows in set (0.00 sec)

16.三表查询查询所有学生的sname，cname，和degree

select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+--------+----------+--------+
| sname  | cname    | degree |
+--------+----------+--------+
| 王丽   | 计算机   |     92 |
| 王丽   | 操作系统 |     86 |
| 王丽   | 数字电路 |     85 |
| 王芳   | 计算机   |     88 |
| 王芳   | 操作系统 |     75 |
| 王芳   | 数字电路 |     79 |
| 赵铁柱 | 计算机   |     76 |
| 赵铁柱 | 操作系统 |     68 |
| 赵铁柱 | 数字电路 |     81 |
+--------+----------+--------+
9 rows in set (0.00 sec)

select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
+--------+----------+--------+-----+-------+
| sname  | cname    | degree | sno | cno   |
+--------+----------+--------+-----+-------+
| 王丽   | 计算机   |     92 | 103 | 3-105 |
| 王丽   | 操作系统 |     86 | 103 | 3-245 |
| 王丽   | 数字电路 |     85 | 103 | 6-166 |
| 王芳   | 计算机   |     88 | 105 | 3-105 |
| 王芳   | 操作系统 |     75 | 105 | 3-245 |
| 王芳   | 数字电路 |     79 | 105 | 6-166 |
| 赵铁柱 | 计算机   |     76 | 109 | 3-105 |
| 赵铁柱 | 操作系统 |     68 | 109 | 3-245 |
| 赵铁柱 | 数字电路 |     81 | 109 | 6-166 |
+--------+----------+--------+-----+-------+
使用as关键字起别名
select sname,cname,degree,student.sno as stu_no,course.cno as cour_no 
from student,course,score 
where student.sno=score.sno 
and course.cno=score.cno;
+--------+----------+--------+--------+---------+
| sname  | cname    | degree | stu_no | cour_no |
+--------+----------+--------+--------+---------+
| 王丽   | 计算机   |     92 | 103    | 3-105   |
| 王丽   | 操作系统 |     86 | 103    | 3-245   |
| 王丽   | 数字电路 |     85 | 103    | 6-166   |
| 王芳   | 计算机   |     88 | 105    | 3-105   |
| 王芳   | 操作系统 |     75 | 105    | 3-245   |
| 王芳   | 数字电路 |     79 | 105    | 6-166   |
| 赵铁柱 | 计算机   |     76 | 109    | 3-105   |
| 赵铁柱 | 操作系统 |     68 | 109    | 3-245   |
| 赵铁柱 | 数字电路 |     81 | 109    | 6-166   |

17.查询95031班学生每门课的均分(in)

select * from score where sno in (select sno from student where class='95031');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

select cno,avg(degree) 
from score 
where sno in (select sno from student where class='95031') 
group by cno;

18.子查询 1

image.png

查询选修3-105的课程成绩高于109号同学3-105成绩的所有同学的记录

select degree from score where sno='109' and cno='3-105';

select * from score where degree>(select degree from score where sno='109' and cno='3-105') ;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

select * from score where degree>(select degree from score where sno='109' and cno='3-105') and cno='3-105';

select sno from score where degree>(select degree from score where sno='109' and cno='3-105') and cno='3-105';

19.子查询2

查询成绩高于学号为109 课程号为3-105的成绩的所有记录

select degree from score where sno='109' and cno='3-105';

select * from score where degree>(select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

20.year 函数与带in的子查询

查询和学号为108.101的同学同年出生的所有学生的sno，sname和sbirthday列

select * from student where sno in (108,101);

select year(sbirthday) from student where sno in (108,101);

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101)) ;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男   | 1975-02-10 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+

21. 查询张旭教师任课的学生成绩(多重嵌套子查询)

select tno from teacher where tname='张旭';
select * from course where tno=(select tno from teacher where tname='张旭');
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

22.查询选修某课程的同学人数多于2人的教师姓名(多表查询)

select cno from score group by cno having count(*)>2;

select tno from course where cno in (select cno from score group by cno having count(*)>2);
select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(*)>2));

23.查询95033、95031班级所有学生记录

select * from student where class in (95033,95031);

24. distinct(去除重复)

查询存在85分以上成绩的课程的cno

select distinct cno from score where degree>85;

25.查询计算机系教师所教课程的成绩表

select tno from teacher where depart='计算机系';
select cno from course where tno in (select tno from teacher where depart='计算机系');
select sno,cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'));

26.union（合并）和 not in（去除）

26.查询计算机系和电子工程系不同职称教师的tname和prof(题目有问题，按照两个集合去掉职称交集理解，即两个职位相同的不要)

select prof from teacher where depart='电子';

select * from teacher where depart = '计算机系' and prof not in (select prof from teacher where depart='电子')
union
select * from teacher where depart = '电子' and prof not in (select prof from teacher where depart='计算机系');

27.any 、desc

27.查询选修编号为3-105课程且成绩至少高于选修编号为3-245的同学cno、sno和degree，并且degree从高到低次序排序

select degree from score where cno = '3-105';
select degree from score where cno='3-245';


select * from score where cno='3-105' and degree>any(select degree from score where cno ='3-245')
order by degree desc;

28.all的用法

查询选修编号为3-105且成绩高于选修编号为3-245课程的同学的cno、sno、degree

select * from score where cno='3-105' and degree>all(select degree from score where cno ='3-245');

any表示大于任意一个就行，相当于大于里面的最小值即可

all表示大于所有的，相当于大于里面的最大值

29. 查询所有教师和同学的name、sex和birthday

select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from student;
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 李成   | 男   | 1958-12-02 00:00:00 |
| 王萍   | 女   | 1972-12-02 00:00:00 |
| 刘冰   | 女   | 1977-12-02 00:00:00 |
| 张旭   | 男   | 1969-12-02 00:00:00 |
| 曾华   | 男   | 1977-09-01 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 李军   | 男   | 1976-02-20 00:00:00 |
| 王芳   | 女   | 1975-02-10 00:00:00 |
| 陆军   | 男   | 1974-06-03 00:00:00 |
| 王尼玛 | 男   | 1976-02-20 00:00:00 |
| 张全蛋 | 男   | 1975-02-10 00:00:00 |
| 赵铁柱 | 男   | 1974-06-03 00:00:00 |
+--------+------+---------------------+

表头不对
select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;

30.查询所有女性教师和女同学的name、sex和birthday

select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'
union
select sname,ssex,sbirthday from student where ssex = '女';

31.复制表数据做条件查询

查询成绩比该课程平均成绩低的同学的成绩表

select cno,avg(degree) from score group by cno;
select * from score;
select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);

32.查询所有任课教师的tname和depart

select * from teacher;
\\课程表中安排了课程的才是任课教师
select * from course;
select tname,depart from teacher where tno in (select tno from course);

33.查询至少有两名男生的班号

按照班号分组，然后筛查
select * from student group by class;
select class from student where ssex='男' group by class having count(*) >1;

34.查询student表中不姓王的同学(not like + 正则)

select * from student where sname not like ='王%';

35.查询student表中每个学生的姓名和年龄

当前年份-出生年份

select year(now());
select year(sbirthday) from student;
select sname,year(now())-year(sbirthday) as '年龄' from student;

36.查询student表中最大和最小的sbirthday

select sbirthday from student order by sbirthday;
select max(sbirthday),min(sbirthday) from student;

37.以班号和年龄从大到小的顺序查询student所有记录

select * from student order by class desc,sbirthday;

38.查询男教师和所执教课程

select * from teacher where tsex='男';

select * from course where tno in (select tno from teacher where tsex='男');

39.查询最高分同学的sno，cno，degree

select max(degree) from score;
select * from score where degree = (select max(degree) from score);

40.查询和李军同性别的所有同学的sname

select ssex from student where sname='李军';
select sname from student where ssex=(select ssex from student where sname='李军');

41.查询和李军同性别并且同班级的同学sname

select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');

42.查询所有选修计算机导论课程的男同学的成绩表

select * from course where cname='计算机';

select * from student where ssex = '男';
select * from score where cno=(select cno from course where cname='计算机') and sno in (select sno from student where ssex = '男');

43.假设使用如下命令创建grade 表

mysql数据存储时区分大小写，查询时默认不区分，可以在where 后加binary关键字使其区分大小写,或者在建表时就使用binary关键字。

creat table grade(
    low int(3),
    upp int(3),
    grade char(1)
);

insert into grade values(90,100,'a');
insert into grade values(80,89,'b');
insert into grade values(70,79,'c');
insert into grade values(60,69,'d');
insert into grade values(0,59,'e');
现查询所有同学的sno，cno和rank等级
select * from grade;
select sno,cno,grade from score,grade where degree between low and upp;
//mysql是逐条操作数据的

五、连接查询

一共有四种

内连接

inner join 

join

外连接

分为左右

左连接
```
left join
left outer join
```
右连接
```
right join
right outer join
```
完全外连接
```
 full join
 full outer join
```

练习

person表：
id
name
cardId

card 表：
id
name

create database testJoin;
carete table person(
    id int,
    name varchar(20),
    cardId int
);
create table card(
    id int,
    name varchar(20)
);

insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工行卡');
insert into card values(5,'邮政卡');

insert into person values(1,'张三',1);
insert into person values(2,'张三',3);
insert into person values(3,'张三',6);

没有创建外键（尽量不使用）

inner join 查询

select * from person inner join card on person.cardId=card.id;

image.png

内联查询，就是两张表中的数据，通过某个字段相对，查询出相关记录数据

left join 查询

select * from person left join card on person.cardId=card.id;

image.png

左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的则显示，如果没有的则显示null

right join 查询

select * from person right join card on person.cardId=card.id;

image.png

右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的则显示，如果没有的则显示null

full join查询(mysql 不支持)

select * from person full join card on person.cardId=card.id;
//相当于使用union连接左右查询
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;

[图片上传失败...(image-eaa432-1596732383962)]

四种连接的示意图

优点：不用创建外键，外键难以检查

六、事务

1.事务是什么

mysql事务其实是一个最小的不可分割的工作单元。

事务能够保证一个业务的完整性。

例如银行的转账：

    a->-100

   update user set money=money-100 where name='a';

   b->+100

   update user set money=money+100 where name='b';

实际的程序中，如果只有一条语句执行成功了，而另一条没有执行成功，则前后数据会不一致。

实际开发中，将多条sql语句可能存在同时成功或同时失败的要求，事务就用于解决这一问题。

2.如何控制事务（commit_rollback）

mysql 默认是开启事务的(自动提交关闭)

select @@autocommit;
默认值为1

默认事务开启的作用是？

当我们去执行一个sql语句时，效果会立即体现出来，且不能回滚

create database bank;

create table user{
id int primary key,
name varchar(20),
money int
};
insert into user values(1,'a',1000);
rollback;
但是结果不会发生变化，这就是事务。

事务回滚 rollback：撤销sql语句执行效果（对drop无效）

设置mysql自动提交为false

set autocommit=0;//此时可以通过rollback回滚

上面的操作关闭了mysql的自动提交（commit）

如果要求此时不能回滚，在输入commit;即可提交，之后无法回滚。这就是commit的持久性

自动提交？@@autocommit=1

手动提交？commit;

事务回滚？rollback;

如果此时转账，一个减100，一个加100？

若只执行了第一个，因为自动提交被关闭，在第二个没有执行成功时，可以rollback撤销第一个操作。

事务提供了一个可以反悔的机会

3.开启事务的begin_start_transaction

set autocommit = 1或者0;是从全局设置，如果只需要一次事务呢？

begin;或者
start transaction;
都可以帮助我们手动开启一个事务


begin;
update ......
update ......
rollback;
回到了begin之前一句话
若要使其无法回滚，需要以commit;结尾

4.事务的ACID体征和使用

四大体征

A（atom）原子性：事务是最小的单位，不可以再分割。
C（consistant）一致性：事务要求，同意事务中的sql，必须保证同时成功或者同时失败。
I（isolation）隔离性：事务1和事务2之间具有隔离性。
D（durability）持久性：事务一旦结束，就无法回滚。

事务开启：

修改默认提交 set autocommit = 0；
begin；
start transaction；

事务提交：

commit；

事务手动回滚：

rollback；

5.事务的隔离性

read uncommitted;读未提交的
read committed;读已经提交的
repeatable read;可以重复读
serializable;串行化

1）read uncommitted（脏读）

如果有事务a，和事务b

a事务对数据进行操作，在操作过程中，事务没有被提交，但是b可以看见a操作的结果

如何查看数据库的隔离级别（默认隔离级别为 REPEATABLE READ）

系统级别的：

select @@global.transaction.isolation;

会话级别的：

select @@transaction.isolation;

如何修改隔离级别？

set global transaction isolation level read uncommitted;

举例：

转账：小明在淘宝店买鞋子：800

开启事务

小明在成都 atm转账

淘宝店在广州 atm查询

此时执行完两句话后，淘宝店可以查到自己已经收款

但是可以被rollback

这样就会出现脏读：一个事务读取到了另外一个事务没有提交的数据，实际开发不允许出现。

2）read committed

首先修改隔离级别为 read committed

set global transaction isolation level read committed;
select @@global.transaction.isolation;

例子：

bank数据库 user表

小张：银行会计

start transaction;

select * from user;

小张上厕所出去了

小王：

start transaction;

insert into user values(5,'c',100);

commit;

小张回来了

select avg(money) from user;

发现平均值不是1000，变少了

虽然只能读到另外一个事务提交的数据，但是还是会出现问题，就是读取同一个表的数据，发现前后不一致。

这就是不可重复读现象（即两次读取不一样）

3）repeatable read（幻读）

在repeatable read级别下探讨

张全蛋成都

start transaction;

王尼玛北京

start transaction;

张全蛋

insert into user values(6,'d',1000);

commit；

此时问题出现，张全蛋可以查到6号账户，但王尼玛查不到，然而王尼玛在尝试添加6号账户时会报错

这就是幻读

事务a和事务b同时操作一张表，事务a提交的数据也不能被事务b读取到，这就造成幻读

4）serializable 串行化

修改隔离级别为串行化

张全蛋成都

start transaction;

王尼玛北京

start transaction;

张全蛋

insert into user values(7,'赵铁柱',1000);

commit;

王尼玛

select * from user;因为已经提交，所以能查到7号

张全蛋

start transaction;

insert into user values(8,'王小花',1000);

此时会卡住在这一句

因为此时操作是串行化，王尼玛没有commit时，张全蛋只会进入阻塞状态，王尼玛commit的瞬间，张全蛋这一句也会执行完

当user表被另外一个事务操作的时候，其他事务里面的写操作是不可以进行的，只能进行读操作。

串行化会出现的问题：

性能特差

一般来说，性能方面

read uncommitted >read committed >repeatable read>serializable

也就是说，隔离级别越高，性能越差

5）总结

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

如何使用可视化工具操作数据库

navicat

不赘述，原理一样。

如何在编程语言中操作数据库

mybatis

待续

MySQL的简单学习

一、如何使用终端操作数据库

1.数据库的启动

2.如何查询数据库服务器中所有数据库

3.如何选中某一个数据库进行操作

4.如何创建一个数据表? 创建一个pet表

5.如何查看数据表的架构?

6.如何查看数据表的架构

7.mysql常用数据类型

8.如何删除数据

二、mysql建表中的约束

1.主键约束

2.复合主键

3.自增约束

4.唯一约束

5.非空约束

6.默认约束

7.外键约束

8.如何建表之后添加主键约束

三、数据库的三大设计范式

1、第一范式（1NF）

2、第二范式（2NF）

3、第三范式（3NF）

四、查询练习

先创建几个表

添加数据

查询

1~10

11.查询每门课的平均成绩

12.查询score表中至少有两名学生选修的并以3开头的课程的平均分

13.查询score table中分数大于70 小于90的sno列

14.查询所有学生的sname，cno和degree列(联合查询两个表)

15.查询所有学生的sno，cname和degree列(2表查询)

16.三表查询 查询所有学生的sname，cname，和degree

17.查询95031班学生每门课的均分(in)

18.子查询 1

19.子查询2

20.year 函数与带in的子查询

21. 查询张旭教师任课的学生成绩(多重嵌套子查询)

22.查询选修某课程的 同学人数多于2人的教师姓名(多表查询)

23.查询95033、95031班级所有学生记录

24. distinct(去除重复)

25.查询计算机系教师所教课程的成绩表

26.union（合并） 和 not in（去除）

27.any 、desc

28.all的用法

29. 查询所有教师和同学的name、sex和birthday

30.查询所有女性教师和女同学的name、sex和birthday

31.复制表数据做条件查询

32.查询所有任课教师的tname和depart

33.查询至少有两名男生的班号

34.查询student表中不姓王的同学(not like + 正则)

35.查询student表中每个学生的姓名和年龄

36.查询student表中最大和最小的sbirthday

37.以班号和年龄从大到小的顺序查询student所有记录

38.查询男教师和所执教课程

39.查询最高分同学的sno，cno，degree

40.查询和李军同性别的所有同学的sname

41.查询和李军同性别并且同班级的同学sname

42.查询所有选修计算机导论课程的男同学的成绩表

43.假设使用如下命令创建grade 表

五、连接查询

内连接

外连接

分为左右

练习

inner join 查询

left join 查询

right join 查询

full join查询(mysql 不支持)

六、事务

1.事务是什么

2.如何控制事务（commit_rollback）

3.开启事务的begin_start_transaction

4.事务的ACID体征和使用

5.事务的隔离性

1）read uncommitted（脏读）

2）read committed

3）repeatable read（幻读）

4）serializable 串行化

16.三表查询查询所有学生的sname，cname，和degree

22.查询选修某课程的同学人数多于2人的教师姓名(多表查询)

26.union（合并）和 not in（去除）