数据库学习
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 保存点名 – 撤销保存点名