MySQL — 数据库和单表操作
一、数据库的基本概念
数据库的英文:DataBase ,简称 DB
1. 什么是数据库
用于存储和管理数据的仓库
2. 数据库的特点
- 持久化存储数据,其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库 —— SQL
3. 常用的数据库软件
关系型数据库(SQL):
MySQL,Oracle,DB2(银行系统),SQLServer(C#,.NET),SQLite(嵌入式,手机端)
非关系型数据库(NoSQL):
MongoDB,Redis
二、SQL
1. 什么是SQL?
Structured Query Language:结构化查询语句
其实就是定义了操作所有关系型数据库的规则。
每一种数据库的操作方式存在差异,这种差异称为***方言***。
2. 通用语法
- SQL语句可以当行或多行书写,以分号(;)结尾。
- 可以使用空格和缩进(Tab)来增强SQL语句的可读性。
- MySQL的SQL语句不区分大小写,关键字可以大写
- 三种注释:
- 单行注释: --(空格) 注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
3. SQL分类
3.1 DDL
Data Definition Language
数据定义语言
用来定义数据库对象:数据库、表、列等。
关键字:create
,drop
,alter
3.2 DML
Data Manipulation Language
数据操作语言
用来对数据库中表的数据进行 增、删、改 。
关键字:insert
, update
, delete
3.3 DQL
Data Query Language
数据查询语言
用来查询数据库中表的记录(数据)
关键字:select
, where
3.4 DCL
Data Control Language
数据控制语言
用来定义数据库的访问权限和安全级别,以及创建用户
关键字:grant
, revoke
三、DDL 操作数据库、表
1. 操作数据库 CRUD
1.1 创建数据库 C
-
创建数据库
-- CREATE DATABASE 数据库名称; CREATE DATABASE `tb_user`;
-
创建数据库,判断不存在,在创建
-- CREATE DATABASE IF NOT EXISTS 数据库名称; CREATE DATABASE IF NOT EXISTS `tb_user`;
-
创建数据库,并设置字符集
-- CREATE DATABASE 数据库名称 CHARACTER SET 字符集; CREATE DATABASE `tb_user` CHARACTER SET utf8;
-
结合
-- CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集; CREATE DATABASE IF NOT EXISTS `tb_user` CHARACTER SET gbk;
1.2 查询数据库 R
-
查询所有数据库的名称:
SHOW DATABASES;
-
查询某个数据库的创建语句:
-- SHOW CREATE DATABASE 数据库名称; SHOW CREATE DATABASE `ssm`;
1.3 修改数据库 U
-
修改数据库字符集
-- ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称; ALTER DATABASE `ssm` CHARACTER SET utf8;
1.4 删除数据库 D
-
删除数据库
-- DROP DATABASE 数据库名称; DROP DATABASE `ssm`;
-
先判断数据库是否存在再删除
-- DROP DATABASE IF EXISTS 数据库名称; DROP DATABASE IF EXISTS `ssm`;
1.5 使用数据库
-
查询当前正在使用的数据库
SELECT DATABASE();
-
使用某个数据库
-- USE 数据库名称; USE `ssm`;
2. 操作表 CRUD
2.1 创建表 C
-
语法
CREATE TABLE 表名 ( 字段1 数据类型 是否允许为null COMMENT '该字段备注注释', 字段2 数据类型 是否允许为null COMMENT '该字段备注注释', PRIMARY KEY (主键字段) ) ENGINE=存储引擎 DEFAULT CHARSET=字符集 COMMENT='该表备注/注释';
-
示例
CREATE TABLE `tb_brand` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '品牌id', `name` varchar(32) NOT NULL COMMENT '品牌名称', `image` varchar(128) DEFAULT '' COMMENT '品牌图片地址', `letter` char(1) DEFAULT '' COMMENT '品牌的首字母', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=325403 DEFAULT CHARSET=utf8 COMMENT='品牌表,一个品牌下有多个商品(spu),一对多关系';
-
MySQL 数据库中的常用数据类型
数据类型 描述 int 整数类型, 4字节,对应Java中的int bigint 整数类型, 8字节,对应Java中的long char 1~255个字符的定长串,它的长度必须在创建时指定,否则MySQL假定为CHAR(1) varchar 可变长度,最多不超过255字节,如在创建时指定VARCHAR(n),则可存储0~n个字符的变长串 text 最大长度为64K的变长文本 float 单精度浮点数值, 4字节,对应Java中的 float double 双精度浮点数值,8字节,对应Java中的double tinyint 1字节,0对应Java中的false,1对应Java中的true date 日期类型,3字节,格式为 YYYY-MM-DD,对应Java中的Date datetime 混合日期和时间值, 8字节,格式为, YYYY-MM-DD HH:MM:SS datestamp 时间戳, 混合日期和时间值, 4字节,格式为 YYYYMMDD HHMMSS
2.2 表查询 R
-
查询某个数据库中的所有表的名称
/* USE 数据库名称; SHOW TABLES; */ USE ssm; SHOW TABLES;
-
查询表结构
-- DESC 表名; DESC `tb_user`;
-
查询表的创建语句
-- SHOW CREATE TABLE 表名; SHOW CREATE TABLE `tb_user`;
-
查询表中的字段信息
/* SELECT COLUMN_NAME,COLUMN_COMMENT,COLUMN_TYPE FROM information_schema.`COLUMNS` WHERE TABLE_NAME = '表名' AND table_schema = '该表所在的数据库名称'; COLUMN_NAME:字段名 COLUMN_COMMENT:字段备注 COLUMN_TYPE:字段的数据类型 information_schema.`COLUMNS`:存放数据库的字段信息的一张表 */ SELECT COLUMN_NAME,COLUMN_COMMENT,COLUMN_TYPE FROM information_schema.`COLUMNS` WHERE TABLE_NAME = '`account`' AND table_schema = 'db1';
2.3 修改表 U
-
修改表名
-- ALTER TABLE 表名 RENAME TO 新表名; ALTER TABLE `user` RENAME TO `tb_user`;
-
修改表的字符集
-- ALTER TABLE 表名 CHARACTER SET 字符集名称; ALTER TABLE `user` CHARACTER SET gbk;
-
向表中添加一列(添加字段)
-- ALTER TABLE 表名 ADD 列(字段)名 字段数据类型 是否允许为空 DEFAULT 默认值 COMMENT '备注注释'; ALTER TABLE `user` ADD `address` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '地址';
-
修改列(字段)名、数据类型
-- 修改列名 -- ALTER TABLE 表名 CHANGE 列名 新列名 数据类型...; ALTER TABLE `user` CHANGE `address` `addr` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '地址'; -- 修改列(字段)的数据类型 -- ALTER TABLE 表名 MODIFY 列名 新数据类型 ...; ALTER TABLE `user` MODIFY `addr` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '地址';
-
删除表中的某一列(字段)
-- ALTER TABLE 表名 DROP 列名; ALTER TABLE `user` DROP `addr`;
-
删除表
-- DROP TABLE IF EXISTS 表名; DROP TABLE IF EXISTS `user`;
四、DML 增删改表中的数据
1. 添加数据
-- INSERT INTO 表名 (列名1,列名2,列名3,....) VALUES (列1值,列2值,列3值,...);
INSERT INTO `user` (username,gender,age) VALUES ('萧炎','男',32);
-- 一次插入多条数据
-- INSERT INTO 表名 (列名1,列名2,....) VALUES (列1值,列2值,...),(....);
INSERT INTO `user` (username,gender,age) VALUES ('萧炎','男',32),("云韵","女",25);
2. 修改数据
-- UPDATE 表名 SET 列名1 = 列1值, 列名2 = 列2值,... WHERE 条件语句;
-- 如果没有条件语句,将修改表中的所有数据
UPDATE `user` SET username = 'guoshihua', age = 22 WHERE id = 2;
3. 删除数据
-
根据条件删除
-- DELETE FROM `user` WHERE [条件语句]; DELETE FROM `user` WHERE id = 1;
-
删除表中全部数据
-- DELETE FROM 表名;
DELETE FROM `user`; -- 不建议使用
-- TRUNCATE TABLE 表名;
TRUNCATE TABLE `user`; -- 更常用
TRUNCATE删除表,再创建一个结构相同空表,效率更高,因此更常用
五、DQL 查询表中数据
1. 语法
SELECT
字段列表
FROM
表名
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组之后的条件列表
ORDER BY
排序字段
LIMIT
分页限定
2. 基础查询
-
多字段查询
-- SELECT 字段1,字段2,... FROM 表名 [WHERE 条件列表]; -- 如果没有条件语句,将查询表中所有 SELECT username,age FROM `user` WHERE id = 2;
-
去重
关键字:
DISTINCT
distinct-- SELECT DISTINCT 字段1,字段2,... FROM 表名 [WHERE 条件列表]; SELECT DISTINCT username,age FROM `user` WHERE id = 2;
-
取别名
关键字:
AS
as-- SELECT 字段名 AS 别名 FROM 表名 [where 条件列表]; SELECT username AS name FROM `user` where id = 2;
-
计算列
一般可以使用四则运算来计算一些列的值(一般只进行数值类型的计算)
null参与的运算,结果都为null
IFNULL(expr1,expr2)
: expr1:哪个字段需要判断是否为null
expr2:该字段为null时的替换值
-- SELECT IFNULL(字段名,该字段为null时的替换值) AS age FROM `user` [where 条件列表];; SELECT IFNULL(age,0) AS age FROM `user`where id = 2; -- 计算用户年龄平均值时,如果为null,就值替换为0 SELECT AVG(IFNULL(age,0)) AS age_avg FROM `user`;
3. 条件查询
关键字WHERE
where
SELECT username,gender,age FROM `user` WHERE id = 3;
运算符:
基本:< ,>,=,<=,>=,<>(不等于)
-
BETWEEN ... AND ...
在 … 和…之间
SELECT username,gender,age FROM `user` WHERE id BETWEEN 1 AND 4;
-
IN
值在该集合中
SELECT username,gender,age FROM `user` WHERE age IN(24,25,26,32);
-
LIKE
模糊查询
占位符:1.
_
:单个任意字符,2.%
:0个或多个任意字符-- 查询姓萧的用户 SELECT username,gender,age FROM `user` WHERE username LIKE "萧%"; -- 查询名字中带“杜”字的用户 SELECT username,gender,age FROM `user` WHERE username LIKE "%杜%"; -- 查询名字中第二个字为“杜”字的用户 SELECT username,gender,age FROM `user` WHERE username LIKE "_杜%"; -- 查询名字为三个字的用户 SELECT username,gender,age FROM `user` WHERE username LIKE "___";
-
IS NULL
和IS NOT NULL
IS NULL
:值为nullIS NOT NULL
:值不为null-- 查询年龄(age字段)为null的用户 SELECT username,gender,age FROM `user` WHERE age IS NULL; -- 查询年龄(age字段)不为null的用户 SELECT username,gender,age FROM `user` WHERE age IS NOT NULL;
-
AND
和&&
连接两个或多个条件,相连的两个条件之间是
且
的关系建议使用
AND
-- 查询年龄为22且性别为男的用户 SELECT username,gender,age FROM `user` WHERE age = 22 AND gender = '男'; -- 不建议使用 SELECT username,gender,age FROM `user` WHERE age = 22 && gender = '男';
-
OR
和||
连接两个或多个条件,相连的两个条件之间是
或
的关系建议使用
OR
-- 查询年龄为22或者性别为男的用户 SELECT username,gender,age FROM `user` WHERE age = 22 OR gender = '男'; -- 不建议使用 SELECT username,gender,age FROM `user` WHERE age = 22 || gender = '男';
-
NOT
和!
非
-- 查询年龄不为22的用户 SELECT username,gender,age FROM `user` WHERE age != 22; SELECT username,gender,age FROM `user` WHERE age <> 22; SELECT username,gender,age FROM `user` WHERE age IS NOT TRUE;
4. 排序查询
关键字:ORDER BY
order by
-
格式:
ORDER BY 排序字段1,排序字段2, ... 排序方式;
-
排序方式
- 升序:
ASC
,不写排序方式时,默认为升序,因此可省略 - 降序:
DESC
- 升序:
-- 根据age字段升序排列
SELECT username,gender,age FROM `user` ORDER BY age;
-- 先根据age字段降序排列,然后再将age值相同的数据根据username字段降序排列
SELECT username,gender,age FROM `user` ORDER BY age DESC,username DESC;
多个排序字段,先根据第一个字段进行第一次排序,如果第一次排序后,存在着第一个字段的值相同的数据,才会根据第二个字段进行第二次排序,一次类推。
5. 聚合函数
将一列数据作为一个整体,进行纵向的计算
***注意:***要排除null值(IFNULL(expr1,expr2)
),因为null参与的所有运算,其结果都为null
-
计算个数
COUNT()
-- * 查询所有字段,不可能所有字段都同时为null SELECT COUNT(*) FROM `user`; -- 排除null值 SELECT COUNT(IFNULL(age,0)) AS count FROM `user`;
-
计算最大值最小值
MAX()
和MIN()
-- 计算age最大值,排除null值(如果为null,就替换值为0) SELECT MAX(IFNULL(age,0)) AS max_age FROM `user`; -- 计算age最小值,排除null值(如果为null,就替换值为0) SELECT MIN(IFNULL(age,0)) AS min_age FROM `user`
-
计算和
SUM()
-- 计算age值的和,排除null值(如果为null,就替换值为0) SELECT SUM(IFNULL(age,0)) AS sum_age FROM `user`;
-
计算平均值
-- 计算age最小值,排除null值(如果为null,就替换值为0) SELECT AVG(IFNULL(age,0)) AS avg_age FROM `user`;
6. 分组查询
关键字:GROUP BY
HAVING
分组之后,查询的字段只能是:分组字段 或 聚合函数
-- 分组查询 男性用户和女性用户的年龄平均值
SELECT gender,AVG(IFNULL(age,0)) AS avg_age FROM `user` GROUP BY gender;
-- 分组查询 男性用户和女性用户的年龄平均值,要求分组后的每个组的人数要大于2(HAVING count > 2)
SELECT gender,AVG(IFNULL(age,0)) AS avg_age ,COUNT(id) AS count FROM `user` GROUP BY gender HAVING count > 2;
WHERE
和HAVING
的区别
(1)where在分组之前进行限定,如果不满足条件,则不会参与分组
having在分组之后进行限定,如果不满足条件,则不会被查询
(2)where后面的条件列表不可以有聚合函数,而having可以进行聚合函数的判断
7. 分页查询
关键字:LIMIT
LIMIT分页操作是一个方言(MySQL数据库的)
LIMIT 开始索引,每页查询条数
开始索引 = (当前页码-1) 每页显示条数*
开
始
索
引
=
(
当
前
页
码
−
1
)
∗
每
页
显
示
条
数
开始索引 = (当前页码-1)* 每页显示条数
开始索引=(当前页码−1)∗每页显示条数
-- 查询第2页的用户数据,每页显示2条数据
SELECT id,username,gender,age FROM `user` LIMIT 1,2;
六、约束
概念:
对表中的数据进行限定,保证数据的正确性、有效性和完整性
1. 主键约束
``PRIMARY KEY (主键字段)`
非空且唯一。一张表只能有一个字段作为主键,主键就是表中的唯一标识。
-
创建表时添加主键约束
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) DEFAULT NULL, `gender` varchar(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) -- 添加字段id作为主键(更常用) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 -------------------------------------------------------------------------------------------- CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, -- 添加字段id作为主键 `username` varchar(100) DEFAULT NULL, `gender` varchar(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
-
删除主键
-- ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE `user` DROP PRIMARY KEY;
-
在创建表之后添加主键约束
-- ALTER TABLE 表名 MODIFY 主键字段 数据类型 是否为允许null PRIMARY KEY; ALTER TABLE `user` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
注意:在创建表之后添加主键约束,一定要保证当前要设置的主键字段非空且唯一
-
自动增长
关键字:
AUTO_INCREMENT
概念:
如果某一列的值是数值类型的,可以使用
AUTO_INCREMENT
来完成自动增长通常是配合数值型主键来使用的。
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, -- 自动增长(主键自增) ... PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
删除自动增长:
ALTER TABLE `user` MODIFY id INT NOT NULL; -- 不会删除主键约束
添加自动增长:
ALTER TABLE `user` MODIFY id INT NOT NULL AUTO_INCREMENT;
2. 非空约束
NOT NULL
值不能为null
-
在创建表时添加非空约束
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, -- 添加非空约束 `username` varchar(100) NOT NULL, -- 添加非空约束 `age` int(3), -- ...... PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
-
表创建完成后添加非空约束
ALTER TABLE `user` MODIFY `age` int(3) NOT NULL;
-
删除非空约束
ALTER TABLE `user` MODIFY `age` int(3);
-
给字段设置默认值
-- 创建表时给字段添加默认值 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `age` int(3) NOT NULL DEFAULT 0, -- 给age字段设置默认值 PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- 创建表之后给字段添加默认值 ALTER TABLE `user` MODIFY `age` int(3) NOT NULL DEFAULT 0;
3. 唯一约束
UNIQUE
又称唯一索引,索引
值不能重复,不能为null
-
创建表时添加唯一约束
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL UNIQUE, -- 给username字段添加唯一约束 `age` int(3) NOT NULL DEFAULT 0, -- ...... PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -------------------------------------------------------------------------------------------- CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `age` int(3) NOT NULL DEFAULT 0, -- ...... PRIMARY KEY (`id`), UNIQUE(`username`) -- 给username字段添加唯一约束(常用) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-
删除唯一约束
-- ALTER TABLE 表名 DROP INDEX 唯一约束的字段名; ALTER TABLE `user` DROP INDEX username;
-
表创建之后添加唯一约束
-- ALTER TABLE 表名 MODIFY 唯一约束的字段名 数据类型 是否允许为null UNIQUE; ALTER TABLE `user` MODIFY username varchar(100) NOT NULL UNIQUE;
4. 外键约束
FOREIGN KEY
foreign key
-
创建表时添加外键约束
-- CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 关联的表名(关联的列名); CONSTRAINT user_student_name FOREIGN KEY (username) REFERENCES student(name); ------------------------- CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `age` int(3) NOT NULL DEFAULT 0, `pro_id` int NOT NULL, PRIMARY KEY (`id`), UNIQUE(`username`), -- 添加外键约束(pro_字段关联职业表(profession)中的主键id) CONSTRAINT user_pro FOREIGN KEY (pro_id) REFERENCES profession(id); ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-
删除外键约束
-- ALTER TABLE 表名 DROP FOREIGN KEY 外键名; ALTER TABLE `user` DROP FOREIGN KEY user_pro;
-
表创建之后添加外键约束
-- 添加外键约束(外键名称为user_pro, pro_字段关联职业表(profession)中的主键id) -- ALTER TABLE `user` ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 关联的表名(关联的列名); ALTER TABLE `user` ADD CONSTRAINT user_pro FOREIGN KEY (pro_id) REFERENCES profession(id);
-
级联操作
添加外键约束时使用
**谨慎使用
级联更新:
-- 级联更新(添加外键时使用) -- CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 关联的表名(关联的列名) ON UPDATE CASCADE; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `age` int(3) NOT NULL DEFAULT 0, `pro_id` int NOT NULL, PRIMARY KEY (`id`), UNIQUE(`username`), -- 添加外键约束(pro_字段关联职业表(profession)中的主键id) CONSTRAINT user_pro FOREIGN KEY (pro_id) REFERENCES profession(id) ON UPDATE CASCADE; -- 级联更新 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 关联的表名(关联的列名) ON UPDATE CASCADE; ALTER TABLE `user` ADD CONSTRAINT user_pro FOREIGN KEY (pro_id) REFERENCES profession(id) ON UPDATE CASCADE; -- 添加外键约束(pro_字段关联职业表(profession)中的主键id),并开启级联更新
级联删除
-- 级联删除 -- CONSTRAINT 外键名 FOREIGN KEY (外键列名) REFERENCES 关联的表名(关联的列名) ON DELETE CASCADE;