MySQL学习笔记

The ZJH

于 2021-06-26 00:03:21 发布

阅读量144

点赞数

本文链接：https://blog.csdn.net/KINGZJH2580/article/details/118230654

版权

一、基本操作

1、创建数据库和表

创建数据库语句

CREATE DATABASE database;Copy

展示所有数据库

SHOW DATABASES;Copy

使用指定数据库

USE database;Copy

创建表

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);Copy

展示表

DESCRIBE table;Copy

2、对表的操作

查看操作

查看表中的记录

SELECT *FROM table;Copy

插入操作

INSERT INTO table VALUES ('', ''...);Copy

删除操作

DELETE FROM table WHERE cow = '';Copy

修改数据

UPDATE table SET cow1 = '' WHERE cow2 = '';Copy

二、约束

1、主键

能够唯一确定表中的一条记录。我们通过给某个字段添加该约束，就可以使得该字段不重复且不为空。

CREATE TABLE user (	id INT PRIMARY KEY,	name VARCHAR(20));Copy

联合主键

CREATE TABLE user2 (	id INT,	name VARCHAR(20),    PRIMARY KEY(id, name));Copy

此处字段id和name一同作为主键，联合主键要求每个字段加起来不同即可（无需每个字段都不同）

建表后添加主键

ALERT TABLE user2 ADD PRIMARY KEY (id);Copy

或者通过修改字段的方式来添加主键

ALERT TABLE user2 MODIFY id INT PRIMARY KEY;Copy

建表后删除主键

ALERT TABLE user2 DROP  PRIMARY KEY (id);Copy

2、自增约束

CREATE TABLE user3 (	id INT PRIMARY KEY AUTO_INCREAMENT,	name VARCHAR(20),);INSERT INTO user3 (name) VALUES ('name'); //插入成功，自动生成了idCopy

自增约束一般与主键搭配使用

3、唯一约束

约束修饰的字段不可以重复

CREATE TABLE user4 (	id INT PRIMARY KEY AUTO_INCREAMENT UNIQUE,	name VARCHAR(20));Copy

或者

CREATE TABLE user4 (	id INT PRIMARY KEY AUTO_INCREAMENT,	name VARCHAR(20),	UNIQUE(id, name));Copy

4、非空约束

CREATE TABLE user4 (	id INT NOT NULL,	name VARCHAR(20),);Copy

5、默认约束

CREATE TABLE user4 (	id INT DEFAULT 10,	name VARCHAR(20),);Copy

如果我们插入字段时没有传值，就会使用默认值

6、外键约束

主表

CREATE TABLE master(	id INT PRIMARY KEY,	name VARCHAR(20));Copy

从表

CREATE TABLE pet(	id INT PRIMARY KEY,	name VARCHAR(20),	master_id int,	FOREIGN KEY(master_id) REFERENCES master(id));Copy

主表中没有的数据，在副表中是不可以使用的
主表中的数据被副标引用时，是不可以删除的

三、数据库的三大范式

1、第一范式

字段还可以拆分的，就不满足第一范式

比如地址如果写为

地址：四川省成都市高新区天府一街Copy

就是可以被拆分的

如果字段写为

省份：四川省城市：成都市区域：高新区街名：天府一街Copy

就是不可拆分的

建表如下

CREATE TABLE student(	id INT PRIMARY KEY AUTO_INCRAEMENT,	name VARCHAR(20),	province VARCHAR(20),	city VARCHAR(20),	area VARCHAR(20),	street: VARCHAR(20));Copy

就是符合第一范式的，但并不是拆分的越详细越好

2、第二范式

满足第一范式的条件下，第二范式要求：除主键外的每一列，都必须完全依赖于主键
如果出现不完全依赖，则只可能发生在联合主键的情况下

不满足第二范式的例子

CREATE TABLE order(	product_id INT,	customer_id INT,	product_name VARCHAR(20),	customer_name VARCHAR(20),    PRIMARY KEY(product_id, customer_id));Copy

此处product_name只依赖于product_id，customer_name只依赖于customer_id，是完全依赖

满足第二范式的例子

CREATE TABLE order(	id INT PRIMARY KEY,	product_id INT,	customer_id INT);CREATE TABLE product(	id INT PRIMARY KEY,	name VARCHAR(20));CREATE TABLE customer(	id INT PRIMARY KEY,    name VARCHAR(20));Copy

3、第三范式

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

不满足第三范式的例子

CREATE TABLE order(	id INT PRIMARY KEY,	product_id INT,	customer_id INT,	customer_phone INT);Copy

此处customer_phone又依赖于customer_id，存在传递依赖关系，不满足第三范式

满足第三范式的例子

CREATE TABLE order(	id INT PRIMARY KEY,	product_id INT,	customer_id INT);CREATE TABLE customer(	id INT PRIMARY KEY,    name VARCHAR(20),    phone INT);Copy

四、查询练习

建表语句及插入语句

CREATE TABLE student(    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',    s_birthday DATETIME COMMENT'学生生日',    s_class VARCHAR(20) COMMENT'学生所在的班级');CREATE TABLE teacher(    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',    t_birthday DATETIME COMMENT'教师生日',    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门');CREATE TABLE course(    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',    FOREIGN KEY(t_no) references teacher(t_no));CREATE TABLE score (    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',    sc_degree decimal,    foreign key(s_no) references student(s_no),    foreign key(c_no) references course(c_no),    PRIMARY KEY(s_no,c_no));Copy--学生表数据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-03-12','讲师','电子工程系');INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');--添加课程表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');Copy

查询student表中所有记录的s_name,s_sex和s_class列

SELECT s_no,s_name,s_class FROM  student;Copy

查询教师所有的单位但是不重复的t_depart列

SELECT DISTINCT(t_depart) FROM teacher;Copy

查询score表中成绩在60-80之间所有的记录(sc_degree)

SELECT s_no, sc_degree FROM score WHERE sc_degree BETWEEN 60 AND 80;SELECT s_no, sc_degree FROM score WHERE sc_degree > 60 AND sc_degree < 80;Copy

查询score表中成绩为85, 86, 或者88的记录(sc_degree)

SELECT s_no, sc_degree FROM score WHERE sc_degree IN(85, 86, 88);Copy

查询student表中’95031’班或者性别为’女’的同学记录

SELECT *FROM student WHERE s_class = '95031' OR s_sex = '女';Copy

以sc_degree降序查询score表中所有的记录

SELECT s_no, sc_degree FROM score ORDER BY sc_degree DESC;Copy

查询’95031’班的学生人数

SELECT COUNT(*) FROM student WHERE s_class = '95031';Copy

查询score表中的最高分数的学生号和课程号

SELECT s_no, c_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);Copy

查询每门课的平均成绩

SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no;Copy

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

SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no HAVING COUNT(c_no) >=2 AND c_no LIKE '3%';Copy

查询所有的学生 s_name , c_no, sc_degree列

SELECT s_name, c_no, sc_degree FROM student, score WHERE student.s_no = score.s_no;Copy

查询所有学生的s_no, c_name, sc_degree列

SELECT s_no, c_name, sc_degree FROM student, score, course WHERE student.s_no = score.s_no AND score.c_no = course.c_no;Copy

查询所有的学生 s_name , c_name, sc_degree列

SELECT s_name, c_name, sc_degree FROM student, score, course WHERE student.s_no = score.s_no AND score.c_no = course.c_no;Copy

查询班级是’95031’班学生每门课的平均分

SELECT c_no, AVG(sc_degree) FROM score WHERE s_no IN (SELECT s_no FROM student WHERE s_class = '95031') GROUP BY(c_no);SELECT c_no, AVG(sc_degree) FROM student AS s LEFT JOIN score AS sc ON s.s_no = sc.s_no WHERE s_class = '95031' GROUP BY c_no;--查询内容包含课程名（c_name）SELECT c.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, course AS c, score AS sc WHERE s.s_no = sc.s_no AND sc.c_no = c.c_no AND s.s_class = '95031'  GROUP BY c.c_no;Copy

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

SELECT *FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND sc.c_no = '3-105' AND s.s_no = sc.s_no;Copy

查询所有与学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

SELECT *FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN ('101', '108'));Copy

查询张旭教师任课的学生的成绩

SELECT s.s_name, sc.sc_degree FROM score AS sc, student AS s WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name = '张旭')) AND s.s_no = sc.s_no;Copy

查询95033班和95031班全体学生的记录

SELECT * FROM student WHERE s_class IN ('95033', '95031') ORDER BY s_class;Copy

查询存在85分以上成绩的c_name和对应的老师

SELECT t_name, c_name FROM course AS c, teacher AS t WHERE c_no IN (SELECT c_no FROM score WHERE sc_degree > 85) AND c.t_no = t.t_no;Copy

查出所有’计算机系’ 教师所教课程的教师信息、课程信息及学生信息

SELECT  * FROM teacher AS t, course AS c, student AS s, score AS sc WHERE s.s_no = sc.s_no AND c.t_no = t.t_no AND sc.c_no = c.c_no AND t.t_no IN (SELECT t_no FROM teacher WHERE t_depart = '计算机系');Copy

查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof

SELECT * FROM teacher WHERE t_depart = '计算机系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系')UNION SELECT * FROM teacher WHERE t_depart = '电子工程系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系');Copy

查询选修编号为”3-105”课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序

SELECT * FROM score WHERE c_no = '3-105' AND sc_degree > ANY(SELECT sc_degree FROM score WHERE c_no = '3-245') ORDER BY sc_degree DESC;Copy

查询选修编号为”3-105”且成绩高于选修编号为”3-245”课程的同学c_no.s_no和sc_degree

SELECT * FROM score WHERE c_no = '3-105' AND sc_degree > ALL(SELECT sc_degree FROM score WHERE c_no = '3-245')ORDER BY sc_degree DESC;Copy

查出学生的信息,课程名称,分数(s_name c_name,sc_degree)

SELECT s.s_name, c.c_name, sc.sc_degree FROM student AS s, course AS c, score AS scWHERE s.s_no = sc.s_noAND c.c_no = sc.c_noAND sc.sc_degree > ALL(SELECT sc_degree FROM score WHERE c_no = '3-245')ORDER BY sc_degree DESC;Copy

总结:

ANY 和 ALL
- ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了
- ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行
查询所有教师和同学的 name ,sex, birthday

SELECT s_name, s_sex, s_birthday FROM studentUNIONSELECT t_name, t_sex, t_birthday FROM teacher;Copy

查询所有’女’教师和’女’学生的name,sex,birthday

SELECT s_name, s_sex, s_birthday FROM student WHERE s_sex = '女'UNIONSELECT t_name, t_sex, t_birthday FROM teacher WHERE t_sex = '女';Copy

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

SELECT * FROM score AS a WHERE sc_degree < (SELECT AVG(sc_degree) FROM score AS b WHERE a.c_no = b.c_no);Copy

查询成绩比该课程平均成绩低的同学的成绩表，并显示出学生name,课程name以及分数

SELECT s.s_name AS 学生姓名, c.c_name AS 课程名, a.sc_degree AS 成绩  FROM score AS a, student AS s, course AS c WHERE sc_degree < (SELECT AVG(sc_degree) FROM score AS b WHERE a.c_no = b.c_no)AND s.s_no = a.s_noAND c.c_no = a.c_no;Copy

查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)

SELECT * FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score));Copy

查出至少有2名男生的班号

SELECT s_class FROM student WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_no) > 1;Copy

查询student 表中不姓”王”的同学的记录

SELECT * FROM student WHERE s_name NOT LIKE '王%';Copy

查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)

SELECT s_name AS 姓名, (YEAR(NOW())-YEAR(s_birthday)) AS 年龄 FROM student;Copy

查询student中最大和最小的 s_birthday的值

SELECT MAX(s_birthday), MIN(s_birthday) FROM student;Copy

以班级号和年龄从大到小的顺序查询student表中的全部记录

SELECT * FROM student ORDER BY s_class DESC, (YEAR(NOW()) - YEAR(s_birthday)) DESC;Copy

查询”男”教师及其所上的课

SELECT t.t_name, c.c_name FROM teacher AS t, course AS c WHERE t.t_sex = '男'  AND t.t_no = c.t_no;Copy

查询最高分同学的s_no c_no 和 sc_degree;

SELECT * FROM student WHERE s_no = (SELECT s_no FROM score WHERE sc_degree  = (SELECT MAX(sc_degree) FROM score));Copy

查询和”李军”同性别的所有同学的s_name

SELECT s_name FROM student WHERE s_sex =  (SELECT s_sex FROM student WHERE s_name = '李军');Copy

查询和”李军”同性别并且同班的所有同学的s_name

SELECT s_name FROM student WHERE s_sex =  (SELECT s_sex FROM student WHERE s_name = '李军')AND s_class = (SELECT s_class FROM student WHERE s_name = '李军');Copy

查询所有选修’计算机导论’课程的’男’同学的成绩表，并显示出s_name,c_name

SELECT s.s_name, c.c_name, sc.sc_degree FROM student AS s, score AS sc, course AS c WHERE s.s_no = sc.s_no AND c.c_no = sc.c_no AND c.c_name = '计算机导论'AND s.s_sex = '男';Copy

五、连接查询

建表语句

CREATE TABLE person (	id INT,	NAME VARCHAR(20),	card_id 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);Copy

没有创建外键时，可以使用内外查询将两个表的内容合并在一起查询

1、内连接

INNER JOIN ... ON ...Copy

举例：

SELECT * FROM person AS p INNER JOIN card  AS c ON p.card_id = c.id;Copy

内联查询：两张表通过某个字段合并起来，查询出相关数据

2、外连接

左外连接

LEFT JOIN ... ON ...或LEFT OUTER JOIN ... ON ...Copy

举例：

SELECT * FROM person AS p LEFT JOIN card AS c ON p.card_id = c.id;或者SELECT * FROM person AS p LEFT OUTER JOIN card AS c ON p.card_id = c.id;Copy

左外连接：

会把左表里的所有数据都取出来
对于右表，如果有数据就取出，如果没有就为空

右外连接

RIGHT JOIN ... ON ...或RIGHT OUTER JOIN ... ON ...Copy

举例：

SELECT * FROM person AS p RIGHT JOIN card AS c ON p.card_id = c.id;或SELECT * FROM person AS p RIGHT OUTER JOIN card AS c ON p.card_id = c.id;Copy

右外连接

会把右表中的所有数据都取出来
对于左表，如果有数据就取出，如果没有就为空

3、三种连接的图解

内连接

左连接

右连接

六、事务

事务是一个最小的不可分割的单元，事务能够保证一个业务的完整性

多条sql语句要么同时成功，要么同时失败，这时就要用到事务

1、自动提交、手动提交和回滚

通过

ROLLBACK;Copy

指令可以回滚，但需要关闭自动提交

SET autocommit = 0查看自动提交状态SELECT @@autocommit;Copy

但如果在进行操作之后，执行

COMMIT;Copy

则无法再进行回滚（持久化）

2、手动开启事务

通过

BEGIN;或者START TRANSACTION;Copy

可以手动开启事务

插入数据前

插入数据后

BEGIN;INSERT INTO person VALUES (4, '老六', 7);INSERT INTO person VALUES (5, '老七', 9);或者START TRANSACTION;INSERT INTO person VALUES (4, '老六', 7);INSERT INTO person VALUES (5, '老七', 9);Copy

回滚后

ROLLBACK;Copy

3、四大特征 ACID

原子性：事务是最小的单位，不可再分
一致性：同一事务中的sql语句要么同时成功，要么同时失败
隔离性：事务1和事务2之间具有隔离性
持久性：事物一旦结束（commit），就不可返回（rollback）

隔离性

事务的隔离级别
- 读未提交 read uncommitted
- 读已提交 read committed
- 可以重复读 repeatable read
- 串行化 serializable

事务的并发问题
- 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
- 不可重复读：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
- 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读

总结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除

解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

查看数据库的隔离级别

版本 5.xSELECT @@global.tx_isolation;版本 8.0SELECT @@global.transaction_isolation;Copy

修改数据库的隔离级别

set global transaction isolation level read committed;Copy

The ZJH

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

一、基本操作1、创建数据库和表创建数据库语句CREATE DATABASE database;Copy展示所有数据库SHOW DATABASES;Copy使用指定数据库USE database;Copy创建表CREATE TABLE student( s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号', s_name VARCHAR(20) NOT NULL COMMENT'学生姓名不能为空', s_sex VARCHAR(
复制链接

扫一扫