mysql -u root -p -- 命令行连接数据库,-p后面可以直接写密码
create database 数据库名; -- 创建数据库
show databases; -- 查看所有的数据库
use 数据库名; -- 切换数据库
show tables; -- 查看当前数据库中所有的表
describe 表名; -- 查看表结构
exit; -- 退出连接
一、操作数据库
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名; -- []表示为可选内容，IF NOT EXISTS 表示该数据库如果不存在，就创建
2、删除数据库
DROP DATABASE [IF EXISTS] 数据库名; -- IF EXISTS 表示该数据库如果存在，则删除该数据库
3、使用数据库
USE 数据库名;
4、查看所有数据库
SHOW DATABASES;
二、数据库的列类型(数据类型)
数值
- tinyint：1个字节
- smallint：2个字节
- mediumint：3个字节
- int：4个字节
- bigint：8个字节
- float：4个字节
- double：8个字节
- decimal：字符串形式的浮点数，金融计算的时候一般使用该类型
字符串
- char：字符串固定大小的 0 ~ 255
- varchar：可变字符串 0 ~ 65535
- tinytext：微型为本 2^8 - 1
- text：文本串 2^16 - 1
时间
- date：YYYY-MM-DD 日期格式
- time：HH:mm:ss 时间格式
- datetime：YYYY-MM-DD HH:mm:ss
- timestamp 时间戳，1970.1.1到现在的毫秒数
- year 年份表示
null
- 表示该列没有值，未知的
- 如果使用null进行运算，结果为null
三、数据库的字段属性
UNSIGNED
- 无符号的整数
- 标志该类不能声明为负数
ZEROFILL
- 0填充
- 不足的位数，使用0进行填充。 例如：int(3) , 5 --> 005
自增 AUTO_INCREMENT
- 自动在上一条数据的基础上 +1
- 通常用来设计为唯一的主键，必须为整数类型
- 可以设置主键自增的起始值和自增量
非空 NOT NULL
- 设置为 not null后，如果不赋值则会报错
默认 DEFAULT
- 设置默认值
- 如果不指定该列的值，则默认生成
注释 COMMENT
- 在字段定义的时候，可以添加一些描述性的文字
四、创建表
CREATE
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', -- 不允许为null且自增长
`name` VARCHAR(30) NOT NULL DEFAULT '老登' COMMENT '姓名', -- 不允许为null，默认值为老登
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', -- 不允许为null，默认值为123456
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', -- 不允许为null，默认值为男
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期', -- 默认值为null
`address` VARCHAR(100) DEFAULT NULL COMMENT '住址', -- 默认值为null
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', -- 默认值为null
PRIMARY KEY(`id`) -- 设置id为主键
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 设置引擎和编码格式
格式:
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 列类型 [属性] [索引] [注释],
字段名 列类型 [属性] [索引] [注释],
...
)[表类型] [字符集设置] [注释]
拓展:
SHOW CREATE DATABASE 数据库名; -- 查看创建数据库的sql语句
SHOW CREATE TABLE 表名; -- 查看创建表的sql语句
DESC 表名; -- 查看表结构
MySQL常用的两种存储引擎:
| 存储引擎 | MYISAM | INNODB |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大 |
优势：
- MYISAM 节约空间，书都快
- INNODB 安全性高，支持事务和多表多用户操作
修改表：
ALTER
-
修改表名
ALTER TABLE 表名 RENAME AS 新表名; -
增加表字段
ALTER TABLE 表名 ADD 字段名 列属性 [属性] [索引] [注释]; -
修改表字段(重命名、修改约束)
ALTER TABLE 表名 MODIFY 字段名 新约束; -- MODIFY 只能修改字段约束，不能修改字段名 ALTER TABLE 表名 CHANGE 字段名 新字段名 新约束; -- CHANGE 在修改字段名时也能修改字段约束 -
删除表的字段：
ALTER TABLE 表名 DROP 字段名;
删除表：
DROP
DROP TABLE [IF EXISTS] 表名; -- IF EXISTS 如果存在就删除
五、数据库级别的外键（了解）
主表和从表：
- 从表：即其中的字段是其他表的主键，该字段可以不是该表的主键。
- 主表：即其中该表的主键是其他表的字段。
当创建表的时候给表添加外键约束：
-- 建立主表
CREATE TABLE IF NOT EXISTS `tab` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 建立从表时增加外键约束
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL DEFAULT '老登',
PRIMARY KEY(`id`),
KEY `FK_id` (`id`), -- KEY 指定外键名 (从表中外键约束的列)
CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `tab`(`id`) -- CONSTRAINT 外键名 FOREIGN KEY 从表中外键约束的列 REFERENCES 主表名(主表中外键约束的列)
)ENGINE=INNODB DEFAULT CHARSET=utf8
给存在的表添加外键约束：
ALTER TABLE 表名(从表) ADD CONSTRAINT 约束名 FOREIGN KEY(主表中外键约束的列) REFERENCES 主表名(主表中外键约束的列);
注意：
在实际开发中，应避免在数据库级别中使用外键，因为每次做删除或者修改表时都必须考虑外键约束，影响开发，测试数据的时候并不方便，一切外键概念应该在应用层解决
六、增删改操作(DML)
数据库存在的意义：数据存储，数据管理
- INSERT
- UPDATE
- DELETE
增加：
INSERT
INSERT INTO 表名 ([字段1,字段2,...]) VALUES (值1,值2,...); -- 添加单条记录
INSERT INTO 表名 ([字段1,字段2,...]) VALUES (值1,值2,...),(值1,值2,...); -- 添加多条记录
INSERT INTO 表名 VALUES (值1,值2,...); -- 省略 ([字段1,字段2,...]) 时，默认向所有字段添加数据,此时该表有多少个字段，就要对应添加多少个值
拓展：
当某个列为自增情况下，添加数据时，对应的值可以传入null或default，系统会自动填入对应的值，
或者可以直接不填入自动增长的字段，且不用插入对应的值
修改：
UPDATE
UPDATE 表名 SET 字段名=值 [WHERE 条件语句]; -- 如果不添加where语句，则默认将该字段名都改为修改的值
UPDATE 表名 SET 字段名1=值1,字段名2=值2,... [WHERE 条件语句]; -- 可以修改多个字段的值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5!=6 | true |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | true |
| <= | 小于等于 | 5<=6 | true |
| >= | 大于等于 | 5>=5 | true |
| BETWEEN | 某个范围内 | [2,5] | |
| AND | 并且 | 5>1 and 5 <6 | true |
| OR | 或者 | 5>1 or 6<1 | true |
删除：
DELETE
DELETE FROM 表名 WHERE 条件表达式; -- 根据条件表达式删除表中的某些数据
DELETE FROM 表名; -- 删除表中所有数据
TRUNCATE
TRUNCATE TABLE 表名; -- 删除表中所有数据,但是不能配合where语句使用,可以省略table关键字
delete和truncate的区别：
相同点：都能删除数据，都不会删除表的结构
不同：
- truncate会重新设置自增列，计数器归零
- truncate不会影响事务
拓展：
mysql5中（mysql8修复了）使用delete删除会出现的问题(计数器问题)，重启数据库时：
- InnoDB 自增列会重1开始（数据存储在内存中，断电即失）
- MyISAM 继续从上一个自增量开始（数据存储在文件中，不会丢失）
七、查询操作
SELECT
基本使用：
SELECT * FROM 表名; -- 查询该表的所有记录
SELECT 字段1,字段2,... FROM 表名; -- 查询表中指定字段的记录
SELECT 字段1 AS 别名,... FROM 表名; -- 使用别名，AS可以省略，别名可以省略''
去重：
DISTINCT
SELECT DISTINCT 字段 FROM 表名; -- 去除字段中重复出现的数据，只显示一条
拓展：
SELECT VERSION(); -- 查看系统版本
SELECT 100*3-1 AS 计算结果; -- 计算
SELECT @@auto_increment_increment; -- 查询自增步长
结论：数据库中的表达式可以是文本值，列，null，函数，计算公式，系统变量等
SELECT 表达式 FROM 表名;
WHERE
作用：查找数据中符合条件的值，搜索的条件由一个或多个表达式组成，结果为布尔值
逻辑运算符：
| 运算符 | 语法 | 描述 |
|---|---|---|
| and、&& | a and b，a&&b | 逻辑与 |
| or，|| | a orb，a||b | 逻辑或 |
| not，! | not a，!a | 逻辑非 |
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果a为null，则结果为true |
| IS NOT NULL | a is not null | 如果a为null，则结果为false |
| BETWEEN | a between b and c | 如果a在b和c之间，则结果为true |
| LIKE | a like b | 如果a匹配到b，则结果为true |
| IN | a in (a1,a2,a3,…) | 如果a是a1，a2，a3…中的某个值，则结果为true |
注意：
当判断值是否为null是，应使用is null或者is not null，不应该使用== !=
LIKE
模糊查询：常用符号%(匹配0到任意个字符)，_(匹配一个字符)
SELECT * FROM 表名
WHERE 字段1 LIKE '%北京%'; -- 查询表中该字段1记录中存在北京两个字符的所有记录
SELECT * FROM 表名
WHERE 字段2 LIKE '%北京'; -- 查询表中该字段2记录中以北京结尾的所有记录
SELECT * FROM 表名
WHERE 字段3 LIKE '北京%'; -- 查询表中该字段3记录中以北京开头的所有记录
SELECT * FROM 表名
WHERE 字段4 LIKE '北_'; -- 查询表中该字段4记录中以北开头的两个字符的所有记录
SELECT * FROM 表名
WHERE 字段5 LIKE '_北'; -- 查询表中该字段5记录中以北结尾的两个字符的所有记录
IN
SELECT * FROM 表名
WHERE 字段 IN(值1,值2，值3,...); -- 查询该表中字段为值1或值2或值3的所有记录
八、联表查询
- 多表查询：
| 操作 | 描述 |
|---|---|
| INNER JOIN | 返回两个表中匹配的行，即两个表中具有相同值的列的行 |
| LEFT JOIN | 从左表中返回所有值，即使右表中没有匹配 |
| RIGHT JOIN | 从右表中返回所有值，即使左表中没有匹配 |
SELECT 字段1,字段2,...
FROM 表1
INNER JOIN 表2
ON 两个表的连接点;
-- 左连接
SELECT 字段1,字段2,...
FROM 表1
LEFT JOIN 表2
ON 两个表的连接点;
-- 右连接
SELECT 字段1,字段2,...
FROM 表1
RIGHT JOIN 表2
ON 两个表的连接点;
-- 三个表连接
SELECT 字段1,字段2,...
FROM 表1
INNER JOIN 表2
ON 表1和表2的连接点,记为a
连接操作 JOIN 表3
ON a和表3的连接点;
- 自连接
SELECT 别名1.要查询的字段, 别名2.要查询的字段
FROM 表 AS 别名1, 表 AS 别名2
WHERE 别名1.连接点 = 别名2.连接点;
-- 例如
SELECT c1.`categoryName` 子栏目, c2.`categoryName` 父栏目
FROM category AS c1, category AS c2
WHERE c1.categoryid = c2.pid;
理解：同一张表分为两张表来使用
九、分页和排序
ORDER BY
SELECT * FROM 表名
ORDER BY 按照哪一个字段排序 升序还是降序 -- 默认为ASC升序,DESC为降序
-- 例如
SELECT * FROM `result`
ORDER BY studentresult DESC
LIMIT
SELECT * FROM 表名
LIMIT 起始下标,页面大小; -- 不写起始下标默认为0，0为第一条记录
-- 每页显示五条数据
-- 第一页 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为当前页，pagesize为页面大小
-- 起始值：(N-1)*pagesize
-- 数据总数/pagesize=总页数
十、子查询(嵌套查询)
SELECT *
FROM 表1
WHERE 字段 = (
SELECT 字段 FROM 表2
WHERE 条件表达式
)
-- 例子
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '数据库结构-1'
)
结论：将一个查询结果作为另一个查询语句中where的条件表达式
十一、聚合函数(常用)
| 函数 | 描述 |
|---|---|
| COUNT() | 统计 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| … | … |
COUNT
SELECT COUNT(字段) FROM 表名; -- COUNT(字段),会忽略该字段中为null的行
SELECT COUNT(*) FROM 表名; -- COUNT(*),不会忽略该字段中为null的行,计算行数
SELECT COUNT(1) FROM 表名; -- COUNT(1),不会忽略该字段中为null的行,计算行数
十二、分组和过滤
GROUP BY
-- 例子
SELECT `subjectname` 课程,
AVG(`studentresult`) 平均分,
MAX(`studentresult`) 最高分,
MIN(`studentresult`) 最低分
FROM `result` r
INNER JOIN `subject` s
ON r.`subjectno` = s.`subjectno`
GROUP BY `subjectname`
HAVING 平均分 >= 80
ORDER BY 平均分 DESC;
HAVING
注意：
- having子句：如果你需要对分组后的结果进行过滤，应该使用having子句而不是 where子句。having其实类似于where，不同的是where是过滤表数据的，而having是过滤分组数据的。
- 使用group by子句的时候，一定要记住下面的一些规则：
（1）select指定的每一列都应该出现在group by子句中，除非对这一列使用了聚合函数；
（2）不能group by在表中不存在的列；
（3）使用group by子句返回的组没有特定的顺序，可以使用order by子句指定次序。
总结select语法：
SELECT [ALL | DISTINCT]
字段 [别名] FROM 表1 [别名]
[LEFT | RIGHT | INNER JOIN 表2] -- 联表查询
[WHERE 条件表达式] -- 指定结果满足的条件
[GROUP BY 按照哪几个字段分组] -- 分组
[HAVING 条件表达式] -- 过滤分组后的数据
[ORDER BY ASC | DESC] -- 排序
[LIMIT 起始值,页面大小]; -- 分页
十三、事务
什么是事务？事务其实就是将一组SQL语句放在一个批次里面执行。
ACID原则：事务的四大特型钢原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）。
理解：
-
原子性
要么都成功，要么都失败。
-
一致性
事务前后的数据完整性保持一致。
-
隔离性
当多个用户并发访问数据时，数据库为每一个用户开启的事务之间互不干扰，事务之间相互隔离。
-
持久性
事务一旦提交则不可逆，数据被持久化到数据库中。
事务的隔离级别：
-
脏读
指一个事务读取了另一个事务没有提交的数据。
-
幻读
在一个事务内读取表中的某一行数据，多次读取的结果不同。
-
虚读（幻读）
一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
-- 模拟转账：事务
SET autocommit=0; -- 关闭自动提交（mysql默认开启自动提交）
START TRANSACTION; -- 开启一个事务（一组事务）
UPDATE `account` SET `money`=`money`-500 WHERE `name`='jack';
UPDATE `account` SET `money`=`money`+500 WHERE `name`='tom';
COMMIT; -- 提交事务，事务一旦提交，则数据被持久化
ROLLBACK; -- 回滚，如果出现错误，则回滚到事务之前
SET autocommit=1; -- 恢复默认值
十四、索引
索引的分类：
- 主键索引（PRIMARY KEY）
- 唯一索引（UNIQUE KEY）
- 常规索引（KEY/INDEX）
- 全文索引（FULLTEXT）
SHOW INDEX FROM 表名 -- 显示表中的所有索引信息
-- 创建索引的方式1
ALTER TABLE 表名 ADD 索引类型 索引名 (字段名);
-- 创建索引的方式2
CREATE 索引类型 索引名 ON 表 (字段名);
作用：
- 对于插入、删除数据频率高的表，不适用索引。
- 对于某列修改频率高的，该列不适用索引。
- 通过某列或某几列的条件查询频率高的，可以对这些列创建索引。
总结：帮助快速定位、检索数据。
十五、数据库用户管理
CREATE USER 用户名 IDENTIFIED BY '密码'; -- 创建一个用户
SET PASSWORD = PASSWORD('新密码'); -- 修改当前用户密码
SET PASSWORD FOR 用户名 = PASSWORD('新密码'); -- 修改其他用户密码（管理员权限）
RENAME USER 用户名 TO 新用户名; -- 修改用户名
GRANT ALL PRIVILEGES ON 哪个数据库.哪个表 TO 用户名; -- 设置用户权限
SHOW GRANTS FOR 用户; -- 查看用户权限
REVOKE ALL PRIVILEGES ON 哪一个数据.哪一个表 FROM 用户名; -- 撤销用户权限
DROP USER 用户名; -- 删除用户
十六、数据库备份
导出：
-- 命令行使用 mysqldump -h 主机名 -u 用户名 -p 密码 数据库 表1 表2 表3 ... > 文件名
mysqldump -h localhost -u root -p root school student > D:/a.sql
导入：
-- 方式1,命令行下直接使用
mysql -u 用户名 -p 密码 数据库名 < 备份文件
-- 方式2，登录到对应数据库后使用，建议使用该方式
source 备份文件
十七、三大范式
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只做一件事情
第三范式
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列都和主键直接关联，而不能间接关联
总结：
三大范式是用来规范数据库的设计的
规范性和性能的问题
关联查询的表不得超过三张表
- 商业化的需求和目标，数据库的性能更加重要
- 在规范性能的问题时，需要适当考虑一下规范性
- 给某些表增加一些冗余字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低到小数据量的查询）
36万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
