MySQL基础知识

1.数据库操作

• 创建数据库：CREATE DATABASE 【IF NOT EXISTS】 test;
• 删除数据库：DROP DATABASE 【IF EXISTS】 test;
• 显示所有数据库：SHOW DATABASES;
• 定位到某一数据库：USE sys;

2.数据库表字段的常用类型

• 整型：
  • int 用于存放标准的整数 4个字节
• 字符串：
  • varchar 0—65535 可变字符串（对应 Java 中的 String）
  • text 2^16 - 1 长文本类型
• 日期：
  • datetime YYYY–MM–DD HH : mm : ss 最常用的时间格式
  • timestamp 时间戳 1970.1.1到现在的毫秒数
• null：
  • 没有值，不要用于计算

3.创建一张数据库表

• 示例：

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL语句创建
-- 学号int，登录密码varchar(20)，姓名，性别varchar(2)，出生日期datatime，家庭住址email

-- 表和字段名最好用 `` 括起来
-- auto_increment 自增
-- 字符串用单引号括起来
-- 所有语句后加 , 最后一个不用加

CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL auto_increment COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

• 基本格式

CREATE TABLE 【IF NOT EXISTS】 `表名` (
`字段名` 列类型 [属性] [索引、默认值] [注释],
`字段名` 列类型 [属性] [索引、默认值] [注释],
`字段名` 列类型 [属性] [索引、默认值] [注释],
.......
`字段名` 列类型 [属性] [索引、默认值] [注释],
PRIMARY KEY(`主键名`)
)[表类型] [字符集设置] [注释]

4.修改删除表

修改

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE student RENAME AS student1;
-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性;
ALTER TABLE student1 ADD age INT(11);

-- 修改表的名字（重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(11); -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE student1 CHANGE age age1 INT(11); -- 字段重命名

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age1;

删除

-- 删除表，如果表存在就删除
DROP TABLE IF EXISTS student1; 

5.MySQL数据管理

5.1 修改操作

• 插入语句

  INSERT INTO 表名([字段1,字段2,...]) VALUES (‘值1’),(‘值2’),(‘值3’)...
  

• 修改语句

  UPDATE TABLE 表名 SET 列字段名1=修改值,列字段名2=修改值,... WHERE [条件]
  

• 删除语句

  DELETE FROM 表名 WHERE [条件]
  

  • 清空表的 2 中操作
    1. DELETE FROM 表名
    2. TRUNCATE 表名
  • delete 和 TRUNCATE 的区别
    • 相同点：都会清空数据表，但不会删除表结构
    • 不同点：
      • TRUNCATE 重新设置 自增列 计数器会归零；delete 不会清空计数器
      • TRUNCATE 不会影响事物
      • 5.2 查询操作（重点）

5.2 查询操作

SELECT 字段名,... FROM 表名 WHERE [条件]

1. 去重 distinct

作用：去除 SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

SELECT * FROM result; -- 查询全部的考试成绩
SELECT StudentNo FROM result; -- 查询有哪些同学参加了考试
SELECT DISTINCT StudentNo FROM result; -- 发现数据重复，去重

2. 查询数据库的列（表达式）

SELECT version(); -- 查询系统版本
SELECT 100*3-1 AS 计算结果 -- 用来计算表达式
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩 +1 分查询后
SELECT StudentNo, StudentResult+1 AS '提分后' FROM result;

SELECT 表达式 FROM 表名 WHERE [条件]

3. WHERE 条件查询逻辑连接符

• AND
• OR
• NOT

4. 模糊查询：比较运算符

运算符	语法	描述
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 ，结果为真。常常与 %（表示0-无限个字符） 和 _（表示一个字符） 搭配。
In	a in (a1,a2,a3,…)	如果 a 在 a1, a2, …中的某一个值，结果为真

5.2.1 联表查询（join on）

【补充】
join on -- 连接查询
where -- 等值查询

操作	描述
Inner join	如果表中至少有一个匹配，就返回行（取左表和右表的交集）
left join	会从左表中返回所有的值，即使右表中没有匹配（以左表为基准）
right join	会从右表中返回所有的值，即使左表中没有匹配（以右表为基准）

例子

SELECT studentNo,studentName,SubjectName,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo = s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo;

-- 我要查询哪些数据 select ...
-- 从哪几张中查 FROM 表 XXX Join 连接的表 ON 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加

5.2.2 分页和排序

排序

-- 排序：升序 ASC ， 降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩升序 排序

SELECT studentName,studentNo
FROM student
WHERE studentNo > 10
ORDER BY studentNo ASC

分页

-- 第一页		limit 0,5			(1-1)*5
-- 第二页		limit 5,5			(2-1)*5
-- 第三页		limit 10,5			(3-1)*5
-- 第N页		 limit (n-1)*5,5	 (n-1)*pagesize,pagesize

-- 【pagesize：页面大小】
-- 【(n-1)*pagesize：页面起始值】
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】

语法：limit 查询起始下标， pagesize

5.2.3 子查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句

where（select * from）

SELECT studentNo,studentName FROM student WHERE studentNo IN(
	SELECT studentNo FROM result WHERE studentResult>80 AND subjectNo = (
		SELECT subjectNo from SUBJECT WHERE subjectName = '高等数学-1'
	)
)

5.3 聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

5.4 分组和过滤

使用Group By 后就不能用where来筛选了，要用having

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub 
ON r.subjectNO = sub.subjectNO
GROUP BY r.subjectNo -- 通过哪个字段来分组
HAVING 平均分>80

5.5 MD5 加密

INSERT INTO student VALUES(1,"xiaoming",MD5('123456'));

当查询时也要将密码进行MD5加密再次比对

5.6 SELECT总结

SELECT [ALL | DISTINCT]
{ * | TABLE.* | [TABLE.FIELD1[AS aliasl],TABLE.FIELD2[AS alias2]....]}
FROM table_name [AS table_alias]
		[left | right | inner JOIN table_name2] -- 联合查询
		[WHERE ...] -- 指定结果需满足的条件
		[GROUP BY ...] -- 指定结果按照哪几个字段来分组
		[HAVING] -- 过滤分组的记录必须满足的次要条件
		[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
		[LIMIT {[OFFSET,]roe_count | row_countOFFSET OFFSET}]; -- 指定查询的记录从哪条至哪条

注意：[]括号是可选的，{}括号是必选的

6.事务

什么是事务？

一组SQL操作，要么都成功，要么都失败。

事务原则

ACID

• 原子性：要么都成功，要么都失败
• 一致性：事务前后的数据完整性要保持一致
• 隔离性：多个用户访问数据库时，每个用户之间的事务操作是隔离的，互不影响
• 持久性：事务一旦提交就不可逆，持久化到数据库中

隔离性导致的问题

脏读：一个事务读取了另一个事务未提交的数据

不可重复读：某事务多次读取某数据表中某一行的数据，但结果都不同

幻读：一个事务读到了别的事务插入的数据，导致前后读取不一致

---

代码示例

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

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这之后的sql都在同一个事务内

INSERT XX
INSERT XX

-- 提交:持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

7.索引

索引是一个数据结构，在数据量很多的时候会提高查询效率

索引的分类：

• 主键索引（PRIMARY KEY）
• 唯一索引（UNIQUE KEY)
• 常规索引（KEY/INDEX）
• 全文索引（FULLText）

8.数据库中的三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式 和 第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。