数据库学习

数据库学习

1.修改表名

ALTER TABLE student1 rename as student;

格式: ALTER TABLE 原表名 rename as 新表名;

2.增加表的字段

ALTER Table student ADD name int(11);

ALTER Table 表名 ADD 字段名字 格式;

3.修改表的字段

ALTER Table 表名 MODIFY 字段名列属性[] 
ALTER Table student MODIFY age VARCHAR(11)  --修改约束
ALTER Table student CHANGE 旧名字 新名字 列属性[]
ALTER Table student CHANGE age age1 INT(11)--字段重命名

4.删除表的字段

ALTER Table 表名 DROP 字段名

ALTER Table student IF EXISTS DROP age1;

== 所有的创建和删除操作尽量加上判断，以免报错==

5.MySql数据管理

5.1 外键

学生表	年级表
id	id
name	name
age
grade

学生的grade列引年级表得id（约束 ）

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

方拾二：创建表时不要加外键关系，

ALTER TABLE ‘表名’

ADD CONSTRAINT ‘约束名’ FOREIGN KEY(作为外键的列) REFERENCES 哪个表（哪个字段）；

以上操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多混乱）

最佳实践：

1.数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。

2.使用多张表的数据，想要使用外键（程序实现）

5.2 DML语言

数据库的意义：数据存储、数据管理

DML语言：数据操作语言

5.3添加

INSERT INTO 表名（[字段名1，字段名2] ）values(‘值1’),(‘值2’)、…);

INSERT INTO student(‘studentname’) VALUES (‘donghu’);

5.4修改

update 表名 set 字段=新值 [ where 条件] 不加条件就全部修改。

UPDATE student SET Sname=‘sb’ WHERE Sno=‘201215121’;

5.5删除

​ 语句： DELETE from 表名 [where 条件]；

DELETE from ‘student’ where Sname=‘donghu’；

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束不变

TRUNCATE student；

区别：

相同点：都能删除数据，不会删除表结构

不同：TRUNCATE 重新设置 自增列 计数器会归零

​ TRUNCATE 不会影响事务

6.DQL查询数据

查询全部

$$

$$
SELECT *from 表；

SELECT * FROM result;

查询指定字段

SELECT 字段 from 表；

SELECT studentno FROM result;

查询学生成绩大于95的

select studentno ,studentresult from result where studentresult >95;

去重 distinct

SELECT DISTINCT studentno from result;

作用：去除SELECT查询出来的结果中重复的数据

6.1模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
LIKE	a like b	SQL匹配，如果a匹配b，则为真
IN	a in（a1,a2,a3…）	假设a在a1，或者a2…其中某一个 结果为真

LIKE用法

查询姓刘的同学

like结合 %（代表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 '刘__';

查询名字中间有嘉字的同学

SELECT studentname,studentno FROM student
where studentname like '%嘉%';

​ IN用法（具体的一个值或者多个值）

联表查询JOIN详解

查询所有参加考试的学生的姓名学员号 科目 成绩

1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询（七种）
3.确定交叉点（两张表中哪些数据是相同的）
4.判断的条件:学生表的studentNo=成绩表的studentNo;

①INNER JOIN

SELECT s.studentno,studentname,studentresult,subjectno
FROM	student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno;

②RIGHT JOIN

SELECT s.studentno,studentname,studentresult,subjectno
FROM	student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno;

③LEFT JOIN

SELECT s.studentno,studentname,studentresult,subjectno
FROM	student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno;

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT join	会从左表中返回所有的值，即使右表中没有
RIGHT join	会从右表中返回所有的值，即使左表中没有

6.2子查询和嵌套查询

定义:在where 语句之后嵌套查询语句

6.3分页和排序

分页：

> （1）limit分页公式：curPage是当前第几页；pageSize是一页多少条记录
>
> limit  (curPage-1)*pageSize,pageSize
> （2）用的地方：sql语句中
>
> select * from student limit(curPage-1)*pageSize,pageSize;
>
> （1）总页数公式：totalRecord是总记录数；pageSize是一页分多少条记录
>
> int totalPageNum = (totalRecord +pageSize - 1) / pageSize;
> （2）用的地方：前台UI分页插件显示分页码
> （3）查询总条数：totalRecord是总记录数，SELECT COUNT(*) FROM tablename

>

排序：

语法：order by ASC /DASC;

7. Mysql 常用函数

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

常用函数不用记，用的时候查文档即可

8.聚合函数及分组过滤（常用）

聚合函数：

函数名称	描述
count（）	计数
sum()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

> -- 聚合函数 
> -- count（）能统计表中数据（想查询一个表中有多少个记录，就使用这个count（））
> SELECT COUNT(studentname) FROM student; -- count(字段),会忽略所有的null值
> SELECT count(*) FROM student;-- count（*）,不会忽略NULL值
> SELECT count(1) FROM student;-- count（1）不会忽略所有的NULL值
>
> -- 求和
> SELECT SUM(studentresult) as 总和 FROM result
> -- 平均分
> SELECT AVG(studentresult) as 平均分 FROM result
> -- 最高
> SELECT MAX(studentresult) as 最高 FROM result
> -- 最低
> SELECT MIN(studentresult) as 最低 FROM result

分组:GROUP BY

-- 查询不同课程的平均分，最高分，最低分
SELECT subjectname,AVG(studentresult) as A,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN subject sub
where r.subjectno =sub.subjectno
GROUP BY r.subjectno


-- 查询不同课程的平均分，最高分，最低分,平均分大于70（需要用having进行筛选,不能用where）
SELECT subjectname,AVG(studentresult) as A,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN subject sub
where r.subjectno =sub.subjectno
GROUP BY r.subjectno
HAVING A >70

9.事务

9.1 什么是事务

事务：要么都成功，要么都失败

---

1.SQL 执行 A给B转账 A 1000 -》-200 A 800

2.sql 执行 B收到A的钱 B 200 -》+200 B400

将一组SQL放在一次批次中去执行

9.2事务原则:ACID原则

参考博客链接: https://blog.csdn.net/dengjili/article/details/82468576

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

原子性

要么都成功,要么都失败

一致性

事务前后的数据完整性要保持一致->A+B的钱一共1200

隔离性

事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据干扰到

持久性

事务一旦提交则不可逆，被持久化到数据库中

9.3 模拟事务

-- mysql 是默认开启事务自动提交的
SET autocommit =0 /*关闭*/
SET autocommit =1 /*开启（默认）*/

-- 手动处理事务
SET autocommit =0 -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个开始之后的SQL都在同个事务内
INSERT xxxx
INSERT xxxx

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！！）
ROLLBACK

-- 事务结束
SET autocommit=1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点的名字 -- 设置一个事务的保存点
ROLLBACK To SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点名

是默认开启事务自动提交的
SET autocommit =0 /关闭/
SET autocommit =1 /开启（默认）/

– 手动处理事务
SET autocommit =0 – 关闭自动提交

– 事务开启
START TRANSACTION – 标记一个事务的开始，从这个开始之后的SQL都在同个事务内
INSERT xxxx
INSERT xxxx

– 提交：持久化（成功！）
COMMIT
– 回滚：回到原来的样子（失败！！）
ROLLBACK

– 事务结束
SET autocommit=1 – 开启自动提交
– 了解
SAVEPOINT 保存点的名字 – 设置一个事务的保存点
ROLLBACK To SAVEPOINT 保存点名 – 回滚到保存点
RELEASE SAVEPOINT 保存点名 – 撤销保存点名