目录
数据模型
数据库 存储数据的仓库，数据是有组织的进行存储 DataBase（DB）
数据库管理系统 操纵和管理数据库的大型软件 DataBase Management System（DBMS）
SQL 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准，（SQL）
通用语法及分类
关系型数据库（RDBMS)
概念:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点：
1.使用表存储数据，格式统一，便于维护2.使用sQL语言操作，标准统一，使用方便
SQL通用语法
1.SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释:
单行注释:--注释内容或#注释内容(MySQL特有)
多行注释:/*注释内容*/
- DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）
- DML: 数据操作语言，用来对数据库表中的数据进行增删改
- DQL: 数据查询语言，用来查询数据库中表的记录
- DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限
DDL（数据定义语言）
数据库操作
查询所有数据库：
SHOW DATABASES;
查询当前数据库：
SELECT DATABASE();
创建数据库：
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库：
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库：
USE 数据库名;
注意事项
- UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集
表操作-查询
查询当前数据库所有表：
SHOW TABLES;
查询表结构：
DESC 表名;
查询指定表的建表语句：
SHOW CREATE TABLE 表名;
表操作-创建
-
CREATE
TABLE 表名(
-
字段
1 字段
1类型
[COMMENT 字段1注释],
-
字段
2 字段
2类型
[COMMENT 字段2注释],
-
字段
3 字段
3类型
[COMMENT 字段3注释],
-
...
-
字段n 字段n类型
[COMMENT 字段n注释]
-
)
[ COMMENT 表注释 ];
注意：最后一个字段后面没有逗号
注意:[...]为可选参数，最后一个字段后面没有逗号
表操作-修改
添加字段：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
修改数据类型：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例：将emp表的nickname字段修改为username，类型为varchar(30)ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
表操作-删除
删除字段：
ALTER TABLE 表名 DROP 字段名;
修改表名：
ALTER TABLE 表名 RENAME TO 新表名
删除表：
DROP TABLE [IF EXISTS] 表名;
删除表，并重新创建该表：
TRUNCATE TABLE 表名;
注意在删除表时， 表中的全部数据也会被删除
表操作-数据类型
DDL-表操作-数据类型
MysQL中的数据类型有很多，主要分为三类:数值类型、字符串类型、日期时间类型。
一个增删改查的例子
-
mysql
> use itcast;
-
Database changed
-
mysql
>
create
table tb_user(
-
-
> id
int comment
'编号',
-
-
> name
varchar(
50) comment
'姓名',
-
-
> age
int comment
'年龄',
-
-
> gender
varchar(
1) comment
'性别'
-
-
> ) comment
'用户表';
-
Query OK,
0
rows affected (
0.03 sec)
-
-
mysql
>
show tables;
-
+
------------------+
-
| Tables_in_itcast
|
-
+
------------------+
-
| tb_user
|
-
+
------------------+
-
1
row
in
set (
0.00 sec)
-
-
mysql
>
desc tb_user;
-
+
--------+-------------+------+-----+---------+-------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
--------+-------------+------+-----+---------+-------+
-
| id
|
int
| YES
|
|
NULL
|
|
-
| name
|
varchar(
50)
| YES
|
|
NULL
|
|
-
| age
|
int
| YES
|
|
NULL
|
|
-
| gender
|
varchar(
1)
| YES
|
|
NULL
|
|
-
+
--------+-------------+------+-----+---------+-------+
-
4
rows
in
set (
0.00 sec)
-
-
mysql
>
show
create
table tb_user;
-
+
---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
|
Table
|
Create
Table
|
-
+
---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
| tb_user
|
CREATE
TABLE `tb_user` (
-
`id`
int
DEFAULT
NULL COMMENT
'编号',
-
`name`
varchar(
50)
DEFAULT
NULL COMMENT
'姓名',
-
`age`
int
DEFAULT
NULL COMMENT
'年龄',
-
`gender`
varchar(
1)
DEFAULT
NULL COMMENT
'性别'
-
) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8mb4
COLLATE
=utf8mb4_0900_ai_ci COMMENT
=
'用户表'
|
-
+
---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-
1
row
in
set (
0.01 sec)
-
-
mysql
>
create
table emp(
-
-
> id
int comment
'编号',
-
-
> workno
varchar(
10) comment
'工号',
-
-
> name
varchar(
10) comment
'姓名',
-
-
> gender
char(
1) comment
'性别',
-
-
> age tinyint unsigned comment
'年龄',
-
-
> idcard
char(
18) comment
'身份证号',
-
-
> entrydate
date comment
'入职时间'
-
-
> ) comment
'员工表';
-
Query OK,
0
rows affected (
0.11 sec)
-
-
mysql
>
desc emp
-
-
>
-
-
>
desc emp
-
-
>
desc emp;
-
ERROR
1064 (
42000): You have an error
in your
SQL syntax;
check the manual that corresponds
to your MySQL server version
for the
right syntax
to use near
'desc emp
-
desc emp'
at line
3
-
mysql
>
desc emp；
-
-
>
desc emp;
-
ERROR
1064 (
42000): You have an error
in your
SQL syntax;
check the manual that corresponds
to your MySQL server version
for the
right syntax
to use near
'desc emp'
at line
2
-
mysql
>
-
mysql
>
create
table emp(
-
-
>
-
> id
int comment
'编号',
-
-
>
-
> workno
varchar(
10) comment
'工号',
-
-
>
-
> name
varchar(
10) comment
'姓名',
-
-
>
-
> gender
char(
1) comment
'性别',
-
-
>
-
> age tinyint unsigned comment
'年龄',
-
-
>
-
> idcard
char(
18) comment
'身份证号',
-
-
>
-
> entrydate
date comment
'入职时间'
-
-
>
-
> ) comment
'员工表';
-
ERROR
1064 (
42000): You have an error
in your
SQL syntax;
check the manual that corresponds
to your MySQL server version
for the
right syntax
to use near
'-> id int comment '编号
',
-
-> workno varchar(10) comment '工号
',
-
-> n'
at line
2
-
mysql
>
-
mysql
>
desc emp;
-
+
-----------+------------------+------+-----+---------+-------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
-----------+------------------+------+-----+---------+-------+
-
| id
|
int
| YES
|
|
NULL
|
|
-
| workno
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| name
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| gender
|
char(
1)
| YES
|
|
NULL
|
|
-
| age
| tinyint unsigned
| YES
|
|
NULL
|
|
-
| idcard
|
char(
18)
| YES
|
|
NULL
|
|
-
| entrydate
|
date
| YES
|
|
NULL
|
|
-
+
-----------+------------------+------+-----+---------+-------+
-
7
rows
in
set (
0.00 sec)
-
-
mysql
>
alter
table emp
add nickname
varchar(
20) comment
'昵称';
-
Query OK,
0
rows affected (
0.11 sec)
-
Records:
0 Duplicates:
0 Warnings:
0
-
-
mysql
>
-
mysql
>
desc emp;
-
+
-----------+------------------+------+-----+---------+-------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
-----------+------------------+------+-----+---------+-------+
-
| id
|
int
| YES
|
|
NULL
|
|
-
| workno
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| name
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| gender
|
char(
1)
| YES
|
|
NULL
|
|
-
| age
| tinyint unsigned
| YES
|
|
NULL
|
|
-
| idcard
|
char(
18)
| YES
|
|
NULL
|
|
-
| entrydate
|
date
| YES
|
|
NULL
|
|
-
| nickname
|
varchar(
20)
| YES
|
|
NULL
|
|
-
+
-----------+------------------+------+-----+---------+-------+
-
8
rows
in
set (
0.00 sec)
-
-
mysql
>
-
mysql
>
alter
table emp change nickname username
varchar(
30) comment
'用户名';
-
Query OK,
0
rows affected (
0.08 sec)
-
Records:
0 Duplicates:
0 Warnings:
0
-
-
mysql
>
-
mysql
>
desc emp;
-
+
-----------+------------------+------+-----+---------+-------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
-----------+------------------+------+-----+---------+-------+
-
| id
|
int
| YES
|
|
NULL
|
|
-
| workno
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| name
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| gender
|
char(
1)
| YES
|
|
NULL
|
|
-
| age
| tinyint unsigned
| YES
|
|
NULL
|
|
-
| idcard
|
char(
18)
| YES
|
|
NULL
|
|
-
| entrydate
|
date
| YES
|
|
NULL
|
|
-
| username
|
varchar(
30)
| YES
|
|
NULL
|
|
-
+
-----------+------------------+------+-----+---------+-------+
-
8
rows
in
set (
0.00 sec)
-
-
mysql
>
alter
table emp
drop username;
-
Query OK,
0
rows affected (
0.09 sec)
-
Records:
0 Duplicates:
0 Warnings:
0
-
-
mysql
>
desc emp;
-
+
-----------+------------------+------+-----+---------+-------+
-
| Field
| Type
|
Null
| Key
|
Default
| Extra
|
-
+
-----------+------------------+------+-----+---------+-------+
-
| id
|
int
| YES
|
|
NULL
|
|
-
| workno
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| name
|
varchar(
10)
| YES
|
|
NULL
|
|
-
| gender
|
char(
1)
| YES
|
|
NULL
|
|
-
| age
| tinyint unsigned
| YES
|
|
NULL
|
|
-
| idcard
|
char(
18)
| YES
|
|
NULL
|
|
-
| entrydate
|
date
| YES
|
|
NULL
|
|
-
+
-----------+------------------+------+-----+---------+-------+
-
7
rows
in
set (
0.00 sec)
-
-
mysql
>
alter
table emp rename
to employee;
-
Query OK,
0
rows affected (
0.09 sec)
-
-
mysql
>
desc emp;
-
ERROR
1146 (
42S02):
Table
'itcast.emp' doesn
't exist
-
mysql> show table;
-
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
-
mysql>
-
mysql> show tables;
-
+------------------+
-
| Tables_in_itcast |
-
+------------------+
-
| employee |
-
| tb_user |
-
+------------------+
-
2 rows in set (0.00 sec)
-
-
mysql>
-
mysql> drop table if exists tb_user;
-
Query OK, 0 rows affected (0.09 sec)
-
-
mysql> show tables;
-
+------------------+
-
| Tables_in_itcast |
-
+------------------+
-
| employee |
-
+------------------+
-
1 row in set (0.00 sec)
-
-
mysql> truncate table employee;
-
Query OK, 0 rows affected (0.04 sec)
-
-
mysql>
-
mysql> show tables;
-
+------------------+
-
| Tables_in_itcast |
-
+------------------+
-
| employee |
-
+------------------+
-
1 row in set (0.00 sec)
-
-
mysql>
-
图形化界面工具-DG
DML（数据操作语言）
DML介绍
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。
添加数据（INSERT)
修改数据（UPDATE)删除数据（DELETE)
DML添加数据
指定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
注意:
·插入数据时，指定的字段顺序需要与值的顺序是一一对应的。·字符串和日期型数据应该包含在引号中。
·插入的数据大小，应该在字段的规定范围内。
DML修改数据
修改数据：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例：UPDATE emp SET name = 'Jack' WHERE id = 1;
DML删除数据
删除数据：
DELETE FROM 表名 [ WHERE 条件 ];
DQL（数据查询语言）
语法：
SELECT字段列表FROM表名字段WHERE条件列表GROUP BY分组字段列表HAVING分组后的条件列表ORDER BY排序字段列表LIMIT分页参数
基础查询
查询多个字段：
SELECT 字段1, 字段2, 字段3, ... FROM 表名;SELECT * FROM 表名;
设置别名：
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;
转义：
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符
条件查询
语法：
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件：
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN … AND … | 在某个范围内（含最小、最大值） |
| IN(…) | 在in之后的列表中的值，多选一 |
| LIKE 占位符 | 模糊匹配（_匹配单个字符，%匹配任意个字符） |
| IS NULL | 是NULL |
| 逻辑运算符 | 功能 |
|---|---|
| AND 或 && | 并且（多个条件同时成立） |
| OR 或 || | 或者（多个条件任意一个成立） |
| NOT 或 ! | 非，不是 |
举例：
-
-- 年龄等于30
-
select
*
from employee
where age
=
30;
-
-- 年龄小于30
-
select
*
from employee
where age
<
30;
-
-- 小于等于
-
select
*
from employee
where age
<=
30;
-
-- 没有身份证
-
select
*
from employee
where idcard
is
null
or idcard
=
'';
-
-- 有身份证
-
select
*
from employee
where idcard;
-
select
*
from employee
where idcard
is
not
null;
-
-- 不等于
-
select
*
from employee
where age
!=
30;
-
-- 年龄在20到30之间
-
select
*
from employee
where age
between
20
and
30;
-
select
*
from employee
where age
>=
20
and age
<=
30;
-
-- 下面语句不报错，但查不到任何信息
-
select
*
from employee
where age
between
30
and
20;
-
-- 性别为女且年龄小于30
-
select
*
from employee
where age
<
30
and gender
=
'女';
-
-- 年龄等于25或30或35
-
select
*
from employee
where age
=
25
or age
=
30
or age
=
35;
-
select
*
from employee
where age
in (
25,
30,
35);
-
-- 姓名为两个字
-
select
*
from employee
where name
like
'__';
-
-- 身份证最后为X
-
select
*
from employee
where idcard
like
'%X';
聚合查询（聚合函数）
常见聚合函数：
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
语法：
SELECT 聚合函数(字段列表) FROM 表名;
例：SELECT count(id) from employee where workaddress = "广东省";
分组查询
语法：
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别：
- 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
例子：
-
-- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）
-
select
count(
*)
from employee
group
by gender;
-
-- 根据性别分组，统计男性和女性数量
-
select gender,
count(
*)
from employee
group
by gender;
-
-- 根据性别分组，统计男性和女性的平均年龄
-
select gender,
avg(age)
from employee
group
by gender;
-
-- 年龄小于45，并根据工作地址分组
-
select workaddress,
count(
*)
from employee
where age
<
45
group
by workaddress;
-
-- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
-
select workaddress,
count(
*) address_count
from employee
where age
<
45
group
by workaddress
having address_count
>=
3;
注意事项
- 执行顺序：where > 聚合函数 > having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
排序查询
语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式：
- ASC: 升序（默认）
- DESC: 降序
举例：
-
-- 根据年龄升序排序
-
SELECT
*
FROM employee
ORDER
BY age
ASC;
-
SELECT
*
FROM employee
ORDER
BY age;
-
-- 两字段排序，根据年龄升序排序，入职时间降序排序
-
SELECT
*
FROM employee
ORDER
BY age
ASC, entrydate
DESC;
注意事项
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
分页查询
语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子：
-- 查询第一页数据，展示10条SELECT * FROM employee LIMIT 0, 10;-- 查询第二页SELECT * FROM employee LIMIT 10, 10;
- 起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数
- 分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
- 如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10
DQL执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
DCL（数据控制语言）
管理用户
查询用户：
USER mysql;SELECT * FROM user;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户：
DROP USER '用户名'@'主机名';
-
-- 创建用户test，只能在当前主机localhost访问
-
create
user
'test'@
'localhost' identified
by
'123456';
-
-- 创建用户test，能在任意主机访问
-
create
user
'test'@
'%' identified
by
'123456';
-
create
user
'test' identified
by
'123456';
-
-- 修改密码
-
alter
user
'test'@
'localhost' identified
with mysql_native_password
by
'1234';
-
-- 删除用户
-
drop
user
'test'@
'localhost';
注意事项
- 主机名可以使用 % 通配
权限控制
常用权限：
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
更多权限请看权限一览表
查询权限：
SHOW GRANTS FOR '用户名'@'主机名';
授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项
- 多个权限用逗号分隔
- 授权时，数据库名和表名可以用 * 进行通配，代表所有
例如：
-
show grants
for
'heima'@
'%';
-
-
grant
all
on itcast.
*
to
'heima'@
'%';
-
-
revoke
all
on itcast.
*
from
'heima'@
'%';
转载：https://blog.csdn.net/qq_61430041/article/details/125436488