MySQL基础学习
一、基本操作
1、创建数据库和表
创建数据库语句
CREATE DATABASE database;展示所有数据库
SHOW DATABASES;使用指定数据库
USE database;创建表
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'学生所在的班级'
);展示表
DESCRIBE table;
2、对表的操作
查看操作
查看表中的记录
SELECT *FROM table;插入操作
INSERT INTO table VALUES ('', ''...);删除操作
DELETE FROM table WHERE cow = '';修改数据
UPDATE table SET cow1 = '' WHERE cow2 = '';二、约束
1、主键
能够唯一确定表中的一条记录。我们通过给某个字段添加该约束，就可以使得该字段不重复且不为空。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);联合主键
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
PRIMARY KEY(id, name)
);此处字段id和name一同作为主键，联合主键要求每个字段加起来不同即可（无需每个字段都不同）
建表后添加主键
ALERT TABLE user2 ADD PRIMARY KEY (id);或者通过修改字段的方式来添加主键
ALERT TABLE user2 MODIFY id INT PRIMARY KEY;建表后删除主键
ALERT TABLE user2 DROP PRIMARY KEY (id);2、自增约束
CREATE TABLE user3 (
id INT PRIMARY KEY AUTO_INCREAMENT,
name VARCHAR(20),
);
INSERT INTO user3 (name) VALUES ('name'); //插入成功，自动生成了id自增约束一般与主键搭配使用
3、唯一约束
约束修饰的字段不可以重复
CREATE TABLE user4 (
id INT PRIMARY KEY AUTO_INCREAMENT UNIQUE,
name VARCHAR(20)
);或者
CREATE TABLE user4 (
id INT PRIMARY KEY AUTO_INCREAMENT,
name VARCHAR(20),
UNIQUE(id, name)
);4、非空约束
CREATE TABLE user4 (
id INT NOT NULL,
name VARCHAR(20),
);5、默认约束
CREATE TABLE user4 (
id INT DEFAULT 10,
name VARCHAR(20),
);如果我们插入字段时没有传值，就会使用默认值
6、外键约束
主表
CREATE TABLE master(
id INT PRIMARY KEY,
name VARCHAR(20)
);从表
CREATE TABLE pet(
id INT PRIMARY KEY,
name VARCHAR(20),
master_id int,
FOREIGN KEY(master_id) REFERENCES master(id)
);- 主表中没有的数据，在副表中是不可以使用的
- 主表中的数据被副标引用时，是不可以删除的
三、数据库的三大范式
1、第一范式
字段还可以拆分的，就不满足第一范式
比如地址如果写为
地址：四川省成都市高新区天府一街就是可以被拆分的
如果字段写为
省份：四川省
城市：成都市
区域：高新区
街名：天府一街就是不可拆分的
建表如下
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCRAEMENT,
name VARCHAR(20),
province VARCHAR(20),
city VARCHAR(20),
area VARCHAR(20),
street: VARCHAR(20)
);就是符合第一范式的，但并不是拆分的越详细越好
2、第二范式
满足第一范式的条件下，第二范式要求：除主键外的每一列，都必须完全依赖于主键
如果出现不完全依赖，则只可能发生在联合主键的情况下
不满足第二范式的例子
CREATE TABLE order(
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY(product_id, customer_id)
);此处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)
);3、第三范式
- 满足第二范式，除主键外的其他列之间不能有传递依赖关系
不满足第三范式的例子
CREATE TABLE order(
id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone INT
);此处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
);四、查询练习
建表语句及插入语句
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)
);--学生表数据
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');- 查询student表中所有记录的s_name,s_sex和s_class列
SELECT s_no,s_name,s_class FROM student;- 查询教师所有的单位但是不重复的t_depart列
SELECT DISTINCT(t_depart) FROM teacher;- 查询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;- 查询score表中成绩为85, 86, 或者88的记录(sc_degree)
SELECT s_no, sc_degree FROM score WHERE sc_degree IN(85, 86, 88);- 查询student表中’95031’班或者性别为’女’的同学记录
SELECT *FROM student WHERE s_class = '95031' OR s_sex = '女';- 以sc_degree降序查询score表中所有的记录
SELECT s_no, sc_degree FROM score ORDER BY sc_degree DESC;- 查询’95031’班的学生人数
SELECT COUNT(*) FROM student WHERE s_class = '95031';- 查询score表中的最高分数的学生号和课程号
SELECT s_no, c_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);- 查询每门课的平均成绩
SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no;- 查询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%';- 查询所有的学生 s_name , c_no, sc_degree列
SELECT s_name, c_no, sc_degree FROM student, score WHERE student.s_no = score.s_no;- 查询所有学生的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;- 查询所有的学生 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;- 查询班级是’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;- 查询选修”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;- 查询所有与学号为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'));- 查询 张旭 教师任课的学生的成绩
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;- 查询95033班和95031班全体学生的记录
SELECT * FROM student WHERE s_class IN ('95033', '95031') ORDER BY s_class;- 查询存在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;- 查出所有’计算机系’ 教师所教课程的教师信息、课程信息及学生信息
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 = '计算机系');- 查询’计算机系’与’电子工程系’ 不同职称的教师的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 = '计算机系');- 查询选修编号为”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;- 查询选修编号为”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;- 查出学生的信息,课程名称,分数(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 sc
WHERE s.s_no = sc.s_no
AND c.c_no = sc.c_no
AND sc.sc_degree > ALL(SELECT sc_degree FROM score WHERE c_no = '3-245')
ORDER BY sc_degree DESC;总结:
- ANY 和 ALL
- ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了
- ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行
- 查询所有教师和同学的 name ,sex, birthday
SELECT s_name, s_sex, s_birthday FROM student
UNION
SELECT t_name, t_sex, t_birthday FROM teacher;- 查询所有’女’教师和’女’学生的name,sex,birthday
SELECT s_name, s_sex, s_birthday FROM student WHERE s_sex = '女'
UNION
SELECT t_name, t_sex, t_birthday FROM teacher WHERE t_sex = '女';- 查询成绩比该课程平均成绩低的同学的成绩表
SELECT * FROM score AS a WHERE sc_degree < (SELECT AVG(sc_degree) FROM score AS b WHERE a.c_no = b.c_no);- 查询成绩比该课程平均成绩低的同学的成绩表，并显示出学生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_no
AND c.c_no = a.c_no;- 查询所有任课教师的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));- 查出至少有2名男生的班号
SELECT s_class FROM student WHERE s_sex = '男'
GROUP BY s_class HAVING COUNT(s_no) > 1;- 查询student 表中 不姓”王”的同学的记录
SELECT * FROM student WHERE s_name NOT LIKE '王%';- 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
SELECT s_name AS 姓名, (YEAR(NOW())-YEAR(s_birthday)) AS 年龄 FROM student;- 查询student中最大和最小的 s_birthday的值
SELECT MAX(s_birthday), MIN(s_birthday) FROM student;- 以班级号和年龄从大到小的顺序查询student表中的全部记录
SELECT * FROM student ORDER BY s_class DESC, (YEAR(NOW()) - YEAR(s_birthday)) DESC;- 查询”男”教师 及其所上的课
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;- 查询最高分同学的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));- 查询和”李军”同性别的所有同学的s_name
SELECT s_name FROM student WHERE s_sex = (SELECT s_sex FROM student WHERE s_name = '李军');- 查询和”李军”同性别并且同班的所有同学的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 = '李军');- 查询所有选修’计算机导论’课程的’男’同学的成绩表，并显示出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 = '男';五、连接查询
建表语句
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);没有创建外键时，可以使用内外查询将两个表的内容合并在一起查询
1、内连接
INNER JOIN ... ON ...举例：
SELECT * FROM person AS p INNER JOIN card AS c ON p.card_id = c.id;
内联查询：两张表通过某个字段合并起来，查询出相关数据
2、外连接
左外连接
LEFT JOIN ... ON ...
或
LEFT OUTER JOIN ... ON ...举例：
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;
左外连接：
会把左表里的所有数据都取出来
对于右表，如果有数据就取出，如果没有就为空
右外连接
RIGHT JOIN ... ON ...
或
RIGHT OUTER JOIN ... ON ...举例：
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;
右外连接
- 会把右表中的所有数据都取出来
- 对于左表，如果有数据就取出，如果没有就为空
3、三种连接的图解
内连接
左连接
右连接
六、事务
事务是一个最小的不可分割的单元，事务能够保证一个业务的完整性
多条sql语句要么同时成功，要么同时失败，这时就要用到事务
1、自动提交、手动提交和回滚
通过
ROLLBACK;指令可以回滚，但需要关闭自动提交
SET autocommit = 0
查看自动提交状态
SELECT @@autocommit;但如果在进行操作之后，执行
COMMIT;则无法再进行回滚（持久化）
2、手动开启事务
通过
BEGIN;
或者
START TRANSACTION;可以手动开启事务
插入数据前
插入数据后
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);
回滚后
ROLLBACK;
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.x
SELECT @@global.tx_isolation;
版本 8.0
SELECT @@global.transaction_isolation;
- 修改数据库的隔离级别
set global transaction isolation level read committed;本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！