2021-10-04

MySQL学习
– 如果已经创建成功的数据库，可以通过show来进行查看
SHOW CREATE DATABASE school
SHOW CREATE TABLE student
DESC student – 查看整个表的结构
– 修改
ALTER TABLE student RENAME AS children
– 添加 字段需要添加属性
ALTER TABLE children ADD color INT (10)
DESC children
– 修改类型 将int类型修改为varchar类型 --修改约束
ALTER TABLE children MODIFY color VARCHAR (20)
DESC children
ALTER TABLE children RENAME AS school
– 修改字段名，需要使用change
ALTER TABLE school CHANGE sub brave VARCHAR (20)
– 删除字段，使用drop
ALTER TABLE school DROP brave

PRIMARY KEY (id),
KEY FK_gradeid (gradeid), – 定义外键
CONSTRAINT FK_gradeid FOREIGN KEY (gradeid) REFERENCES grade (gradeid)

– 同时插入多个值
INSERT INTO student (name,email,birthday)
VALUES (‘旺财’,‘IE11@163.com’,‘2000-10-24 10:10:10’),
(‘来福’,‘IE12@163.com’,‘2000-10-24 10:10:11’),
(‘大黄’,‘IE13@163.com’,‘2000-10-24 10:10:12’)

– 修改表格中的某一项 ，关键字为update，需要注意使用限定条件
– update 表 set 列某一属性=‘修改之后的值’ where 限定条件
– 如果不指定条件 默认修改全部内容
UPDATE student SET name =‘小黑’ WHERE id =1;
UPDATE student SET name =‘小白’,email=‘IE10@163.com’ WHERE id =1;
– 使用关键字between and修改某一个闭合区间内符合条件的内容
UPDATE student SET birthday =‘2021-10-4 15:28:00’ WHERE id BETWEEN 5 AND 7;
– 使用and关键字，限定多个条件。或者or全部符合条件的
UPDATE student SET birthday =‘2021-10-4 15:32:00’ WHERE id BETWEEN 5 AND 7 OR email=‘IE11@163.com’;
UPDATE student SET birthday =‘2021-10-4 15:32:00’ WHERE id BETWEEN 5 AND 7 AND email=‘IE11@163.com’;
UPDATE student SET birthday =‘2021-10-4 15:32:00’ WHERE id BETWEEN 5 AND 7 OR email=‘IE11@163.com’ ANDname=‘旺财’;

– 清空表 ,数据库仍然保留，完全清空表中内容
TRUNCATE student

– 查询
– select 列属性 from 表
SELECT loginpwd FROM student
SELECT studentno, studentname FROM student
– 使用别名的方法,展示的结果中直接出现起的别名，更加方便观看搜索结果
SELECT studentnoAS 学号, studentnameAS 学生姓名 FROM student AS s
– 利用 concat 函数对结果进行拼接，出现在一列
SELECT CONCAT(‘姓名：’,studentname) AS 新名字 FROM student
– 去重 利用关键字distinc
SELECT studentno FROM result – 查询结果
SELECT DISTINCT studentno FROM result – 查询结果。顺带去重
– 直接根据结果进行加法
SELECT studentno,studentresult AS ‘新成绩’ FROM result
SELECT studentno,studentresult+1 AS ‘新成绩’ FROM result

– 模糊查询 %（0-无穷多个字符） _下划线（后面只有一个字符）
SELECT studentname,studentno FROM student
WHERE studentname LIKE ‘鱼%’

SELECT studentname,studentno FROM student
WHERE studentname LIKE ‘张_’
– 查询名字中带有某个字
SELECT studentname,studentno FROM student
WHERE studentname LIKE ‘%鱼%’
– 关键字in 只要满足in中的条件，即可被查询
SELECT studentno,studentname FROM student
WHERE studentno IN (1001 ,1002 ,1003)

– 联表查询 关键字left join inner join right join
/*
1.分析需求，确定查询的字段的来源
2.使用连接的方式
3.确定交叉点
4.判断条件：在这个过程中需要使用别名，主要是为了区分不同表中的相同字段

*/
SELECT s.studentno,studentresult,studentname,address
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

/*
1.分析需求，确定查询的字段的来源 subject student result
2.使用连接的方式 right join inner join
3.确定交叉点
4.判断条件：在这个过程中需要使用别名，主要是为了区分不同表中的相同字段
5.如果存在多张表查询，先查询两张表，然后在慢慢增加
左右连接举例如下
from a left join b 由于为左连接，因此左边a的为主表
from a right join b 右连接，右边的表b为主表
*/
SELECT s.studentno ,studentname, subjectname ,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN subject AS sub
ON sub.subjectno= r.subjectno
– 自连接，核心是将一张表拆成两张表,一张表中的同一项目别名

SELECT a.categoryname AS ‘父栏目’ ,b.categoryname AS ‘子栏目’
FROM categoryAS a , category AS b
WHERE a.categoryid=b.pid

– 学号 学生姓名 科目名-数据库结构-1 分数 student subject result
SELECT s.studentno ,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON sub.subjectno = r.subjectno
WHERE subjectname =‘数据库结构-1’

– 排序 asc 升序 desc 降序
SELECT s.studentno ,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
ORDER BY studentresult ASC

– 分页
LIMIT 1,5 当前1的位置，每页五条 的情况下的实质第一页
LIMIT 6,5 当前1的位置，每页五条 的情况下的实质第二页

– 例题 分数不小于80分的学生的学号和姓名
– 方法一
SELECT DISTINCT s.studentno AS ‘学号’ , studentname ‘姓名’,studentresult ‘学生成绩’
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80

– 方法3 – 嵌套查询
SELECT DISTINCT s.studentno AS ‘学号’ , studentname ‘姓名’,studentresult ‘学生成绩’
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno = (
SELECT subjectno FROM SUBJECT
WHERE subjectname = ‘高等数学-2’
)

– 分组以及过滤 使用关键字group by 以及having
– 查询不同课程的平均分，最高分，最低分
– 核心 需要根据不同的课程分组

SELECT subjectname ,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno – 通过什么字段设定分组
HAVING AVG(studentresult) > 80

– 事务 一组SQL放在一个批次中执行 同时成功，要么同时失败
– ACID 原则
– 原子性 同时成功，要么同时失败
– 一致性 事务操作前后的状态一致
– 隔离性 多用户操作是，排除其他事务对本次事务的影响
– 持久性 事务操作结束后，不会随着外界的原因导致数据丢失

– 脏读 一个事务读取了另外一个事务未提交的数据
– 虚读 一个事务内读取到了别的事务插入的数据，导致前后读取不一致

– set autocommit = 0 关闭 设置为1 表示开始
– start transaction 标记一个事务的开始
– savepoint 保存点
– rollback 回滚
– rollback to savepoint

– 例题
– 模拟转账 创建数据表accounts 数据库shop 模拟实现转账操作
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE IF NOT EXISTS account (
id INT(3) NOT NULL AUTO_INCREMENT COMMENT ‘序号’,
name VARCHAR(20) NOT NULL COMMENT ‘姓名’,
money DECIMAL(9,2) NOT NULL COMMENT ‘余额’,
PRIMARY KEY (id)
)ENGINE INNODB DEFAULT CHARSET utf8

CREATE TABLE IF NOT EXISTS accounts (
id INT(3) NOT NULL AUTO_INCREMENT COMMENT ‘序号’,
name VARCHAR(20) NOT NULL COMMENT ‘姓名’,
money DECIMAL(9,2) NOT NULL COMMENT ‘余额’,
PRIMARY KEY (id)
)ENGINE INNODB DEFAULT CHARSET utf8

– 模拟转账操作
SET autocommit = 0;
START TRANSACTION
UPDATE accounts SET money = money -500 WHERE name = ‘来福’
UPDATE accounts SET money = money +500 WHERE name = ‘旺财’

COMMIT
ROLLBACK
SET autocommit = 1;

– 主键索引 primary key 不可以重复
– 唯一索引unique key 避免重复的列出现，唯一索引可以重复，多个列可以标识位
– 常规索引 key/index 默认的
– 全文索引 fulltxt 快速定位数据

– 索引一般加在常用来查询的字段 不对小数据量加索引 不对进程变动的数据加索引