DDL:数据定义语言
作用
:
用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改
数据库操作
查询数据库
- >
显示当前
mysql
中的数据库列表
> show databases;
- >
显示指定名称的数据的创建的
SQL
指令
> show create database 库名
;
创建数据库
- >
创建数据库
> create database 库名
;
- >
创建数据库，当指定名称的数据库不存在时执行创建
> create database if not exists 库名
;
- >
在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式 utf8
、
gbk
）
> CREATE DATABASE IF NOT EXISTS 库名
CHARSET 'utf8';
修改数据库字符集
- >
修改数据库的字符集
> alter database 库名
CHARSET
字符集
;
删除数据库
- >
删除数据库
> drop database 库名
;
- >
如果数据库存在则删除数据库
> drop database if exists 库名
;
使用/切换数据库
use
库名
;
数据表操作
创建完数据库之后，接下来就是在数据库中创建数据表。在MySQL
中，数据表以二维表格的形式展示，表格中的一行代表一条完整的数据记录，表格中的一列代表数据的某个特定属性。
需要注意：
在数据库中创建表之前，需要查看当前使用的数据库
语法：
select database();
然后切换到你需要操作的数据库
use db_name;
建表
create table 表名
(
字段
1
字段类型
,
字段
2
字段类型
, ...
字段
n
字段类型
);
字段类型 ：
int：整型
double：浮点型，例如
double(5,2)
表示最多
5
位，其中必须有
2
位小数，即最大值为 999.99；
char：固定长度字符串类型；
char(10): 'aaa ' 占
10
位
varchar：可变长度字符串类型；
varchar(10): 'aaa' 占
3
为
text：字符串类型；
blob：字节类型；
date：日期类型，格式为：
yyyy-MM-dd
；
time：时间类型，格式为：
hh:mm:ss
timestamp：时间戳类型
yyyy-MM-dd hh:mm:ss
会自动赋值
datetime:日期时间类型
yyyy-MM-dd hh:mm:ss
boolean:mysql不支持
,oracle
支持
示例 ：
创建一张存储学员信息的表
:stu
表中的字段有
:
s_id 一条数据的唯一标识符
int primary key auto_increment
s_name 学员姓名
varchar(30)
s_sex 学员性别
varchar(10)
s_age 学员年龄
int
s_class 学员所在班级
varchar(50)
s_num 学号
varchar(16)
s_type 学员专业
varchar(50)
sql
语句
create table stu(
s_id int primary key auto_increment,
s_name varchar(30),
s_sex varchar(10),
s_age int,
s_class varchar(50),
s_num varchar(16),
s_type varchar(50)
);
查看所有表
SHOW TABLES;
重命名表
rename table 原表名
to
新表名
;
查看创建表语句信息
SHOW CREATE TABLE 表名
;
修改字符集
ALTER TABLE 表名
CHARACTER SET
修改后的字符集
;
注意:
一般使用
utf8(
就是
utf-8)
查表字段信息
DESC 表名
;
备份表
create table 新表名
as select * from
要备份的表的名称
;
如
:
create table tname2 as select * from tname1;
备份表结构
create table 新表名
like
要备份的表的名称
;
如
:
create table tname2 like tname1;
删除表
DROP TABLE 表名
;
添加列
ALTER TABLE 表名
ADD
添加的列名 填加的列字段类型
;
修改列
alter table 表名 modify 要修改的列名 修改后的字段类型;
删除列
alter table 表名
drop
列名
;
注意:
一次只能删一列
列重命名
ALTER TABLE 表名 CHANGE 原列名 新列名 新字段类型;
DML:数据操作语言
插入数据
作用
:
给表中添加数据
insert into
表名
(
列名
1,
列名
2....)
values
(
值
1,
值
2....);
示例：
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel)
values
(
'20210101'
,
'
张三
'
,
'
男
'
,
21
,
'13030303300'
);
# 数据表名后的字段名列表顺序可以不与表中一致，但是
values
中值的顺序必须与表名 后字段名顺序对应
insert into
stus(stu_num,stu_name,stu_age,stu_tel,stu_gender)
values
(
'20210103'
,
'
王五
'
,
20
,
'13030303302'
,
'
女
'
);
# 当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，
#但是
values
中的值的顺序要与数据表定义的字段保持一致
;
insert into
stus
values
(
'20210105'
,
'
孙 琦'
,
'
男
'
,
21
,
'13030303304'
,
'666666'
);
# 不过在项目开发中，即使要向所有列添加数据
,
也建议将列名的列表显式写出来
(
增强 SQL的稳定性
)
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq)
values
(
'20210105'
,
'
孙琦
'
,
'
男
'
,
21
,
'13030303304'
,
'666666'
);
修改数据
update
表名
set
列名
1=
值
1
，列名
2=
值
2 [
where
条件
];
示例：
# 将学号为
20210105
的学生姓名修改为
“
孙七
”
（只修改一列）
update
stus
set
stu_name=
'
孙七
'
where
stu_num=
'20210105'
;
# 将学号为
20210103
的学生 性别修改为
“
男
”,
同时将
QQ
修改为
777777
（修改多列）
update
stus
set
stu_gender=
'
男
'
,stu_qq=
'777777'
where
stu_num=
'20210103'
;
# 根据主键修改其他所有列
update
stus
set
stu_name=
'
韩梅梅'
,stu_gender=
'
女
'
,stu_age=
18
,stu_tel=
'13131313311'
,stu_qq=
'999999'
where
stu_num=
'20210102'
;
# 如果
update
语句没有
where
子句，则表示修改当前表中所有行（记录）
update
stus
set
stu_name=
'Tom'
;
删除数据
delete from 表名
[where
条件
];
示例：
# 删除学号为
20210102
的学生信息
delete from
stus
where
stu_num=
'20210102'
;
# 删除年龄大于
20
岁的学生信息
(
如果满足
where
子句的记录有多条，则删除多条记录
)
delete from
stus
where
stu_age>
20
;
# 如果删除语句没有
where
子句，则表示删除当前数据表中的所有记录
(
敏感操作
)
delete from
stus;
DQL:数据查询语言
查询全表
select * from 表名
;
查询指定列
select 列名1,列名2,… from 表名;
条件查询
select * from 表名
where
条件;
注意：
条件查询就是在查询时给出
WHERE
子句，在
WHERE
子句中可以使用如下运算符及关键 字：
=、
!=
、
>
、
<
、
<=
、
>
、
>=
BETWEEN…AND (等价
<=
和
>=)
IN(set) (包含
)
IS NULL (非空
)
AND (逻辑与
)
OR (逻辑或
)
NOT (逻辑非
)
XOR (逻辑异或
)
示例：
--
查询成绩小于
80
的学员
select
*
from
stu
where
score <
80
;
--
查询成绩等于
100
的学员
select
*
from
stu
where
score =
100
;
--
查询成绩在
85~100
的学员
select
*
from
stu
where
math
between
80
and
100
;
--
查询姓名叫做
“
张三
”
或者
“
李四
”
的所有学生信息。
select
*
from
stu
where
name beteween
"
张三
"
and
"
李四
"
;
--
查询成绩不小于
80
的学员
select
*
from
stu
where not
score <
80
;
--
查询姓名不叫做
“
张三
”
或者
“
李四
”
的所有学生信息。
select
*
from
stu
where
name
not
beteween
"
张三
"
and
"
李四
"
;
--
查询姓名叫做
“
张三
”
或者
“
李四
”
的所有学生信息。
select
*
from
stu
where
name
in
(
"
张三
"
,
"
李四
"
);
--
查询成绩小于
0
或大于
100
的学员
select
*
from
stu
where
score <
0
or
score >
100
;
--
查询性别为空的学员
select
*
from
stu
where
sex
IS
NULL
;
模糊查询
当想查询姓名中包含
a
字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字 LIKE。
_:任意一个字母
%:任意
0~n
个字母
'
张
%'
示例：
--
查找姓名为
3
个字母的学生信息
select
*
from
stu
where
name
like
'___'
;//
注意是
3
个
_
，表示匹配
3
个字符
--
查找以字母
b
开头的学生信息
select
*
from
stu
where
name
like
'b%'
;
MySQL 同样也支持其他正则表达式的匹配，
MySQL
中使用
REGEXP
操作符来进行正则 表达式匹配。
示例：
--
查找姓名以
l
开头且以
y
结尾的学生信息
select
*
from
stu
where
name regexp
'^l'
and
name regexp
'y$'
;
去重
关键字
:distinct
示例
--
查询
stu
表中
age
字段，剔除重复行
select distinct
age
from
stu;
计算列
对从数据表中查询的记录的列进行一定的运算之后显示出来
+,-,*,/,%
例
--
出生年份
=
当前年份
-
年龄
select
stu_name,
2021
-stu_age
from
stus;
别名
如果在连接查询的多张表中存在相同名字的字段，我们可以使用 表名
.
字段名
来进行区 分，如果表名或字段名太长则不便于SQL
语句的编写，我们可以使用数据表别名
示例
--
字段起别名
select
name
AS
姓名
from
stu;
--
表名起别名
select
s
.name
,s
.sex
from
stu
AS
s;
-- AS
可以省略
select
name
姓名
from
stu;
select
s
.name
,s
.sex
from
stu s;
排序order by
将查询到的满足条件的记录按照指定的列的值升序
/
降序排列
语法：
select * from 表名
where
条件
order by
列名
asc|desc;
order by 列名 表示将查询结果按照指定的列排序
asc 按照指定的列升序（默认）
desc 按照指定的列降序
#
单字段排序
select
*
from
stu
where
age>
15
order by
score
desc
;
#
多字段排序
:
先满足第一个排序规则
,
当第一个排序的列的值相同时再按照第二个列的
规则排序
select
*
from
stus
where
age>
15
order by
score
asc
,age
desc
;
聚合函数
聚合函数是用来做纵向运算的函数：
COUNT()
：统计指定列不为
NULL
的记录行数；
MAX()
：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()
：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()
：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为
0
；
AVG()
：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为
0
；
例：
--
统计年龄大于
20
的学员人数
select count
(*)
as
cnt
from
stu
where
age>
20
;
--
统计学员的总年龄
select
sum(age)
from
stu;
--
统计学员的平均年龄以及总年龄
select
sum(age),avg(age)
from
stu;
--
统计学员的最低年龄以及最高年龄
select
max(age),min(age)
from
stu;
--
计算班级平均分
select
avg(score)
from
stu;
分组查询
分组：就是将数据表中的记录按指定的列进行分组
语法
select
分组字段
/
聚合函数
from
表名
[
where
条件
]
group by
分组列名
[
having
条件
]
[
order by
排序字段
]
注意：
语句执行顺序：
1,
先根据
where
条件从数据库查询记录
2,group by
对查询记录进行分组
3,
执行
having
对分组后的数据进行筛选
4,
排序
例：
-- 先对查询的学生信息按性别进行分组（分成了男、女两组），然后再分别统计每组 学生的个数
select
stu_gender,
count
(stu_num)
from
stus
group by
stu_gender;
-- 先对查询的学生信息按性别进行分组（分成了男、女两组），然后再计算每组的平 均年龄
select
stu_gender,avg(stu_age)
from
stus
group by
stu_gender;
-- 先对学生按年龄进行分组，然后统计各组的学生数量，还可以对最终的结果排序
select
stu_age,
count
(stu_num)
from
stus
group by
stu_age
order by
stu_age;
-- 查询所有学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数
>1 的组，再按年龄升序显示出来
select
stu_age,
count
(stu_num)
from
stus
group by
stu_age
having count
(stu_num)>
1
order by
stu_age;
-- 查询性别为
'
男
'
的学生
,
按年龄进行分组
,
然后分别统计每组的人数
,
再筛选当前组人 数>1
的组
,
再按年龄升序显示出来
mysql>
select
stu_age,
count
(stu_num)
->
from
stus
->
where
stu_gender=
'
男
'
->
group by
stu_age
->
having count
(stu_num)>
1
->
order by
stu_age;
分页查询
语法：
select
查询的字段
from
表名
where
条件
limit
param1,param2;
注意
:
- param1：表示获取查询语句的结果中的第一条数据的索引（索引从
0
开始）
- param2：表示获取的查询记录的条数（如果剩下的数据条数
<param2
，则返回剩下的 所有记录）
注意：
--
示例
--
假如
:
对数据表中的学生信息进行分页显示，总共有
10
条数据，我们每页显示
3
条
--
总记录数：
`count 10`
--
每页显示：
`pageSize 3
--
总页数：
`pageCount=count%pageSize==0?
count/pageSize:count/pageSize+1`
--
查询第一页：
select
*
from
stus [
where
...]
limit
0
,
3
; (
1
-
1
)*
3
--
查询第二页：
select
*
from
stus [
where
...]
limit
3
,
3
; (
2
-
1
)*
3
--
查询第三页：
select
*
from
stus [
where
...]
limit
6
,
3
; (
3
-
1
)*
3
--
查询第四页：
select
*
from
stus [
where
...]
limit
9
,
3
; (
4
-
1
)*
3
--
如果在一张数据表中：
-- pageNum
表示查询的页码
-- pageSize
表示每页显示的条数
--
通用分页语句如下：
select
*
from
stus [
where
...]
limit
(pageNum-
1
)*pageSize,pageSize;
约束
-
保证数据的有效性
-
保证数据的完整性
-
保证数据的正确性
-
非空约束（
not null
）：限制此列的值必须提供，不能为
null
-
唯一约束（
unique
）：在表中的多条数据，此列的值不能重复
-
主键约束（
primary key
）：非空
+
唯一，能够唯一标识数据表中的一条数据
-
自增长约束
(auto_increment):
每次
+1,
从
1
起
-
检查约束（
check
）：保证列中的值满足某一条件
-
默认约束（
default
）：保存数据时
,
未指定值则采用默认值
-
外键约束（
foreign key
）：建立不同表之间的关联关系
多表查询
语法：
select
列名
1,
列名
2,.. from
表
1,
表
2,.. where
判断语句
;
示例
--
查看
stu
和
grade
表中的学生学号、姓名、班级、成绩信息
select
s.*,g.*
from
stu s,grade g
where
s
.id
= g
.stu_id
;
注意：
没有条件会出现笛卡尔积
视图:虚拟表
视图，就是由数据库中一张表或者多张表根据特定的条件查询出得数据构造成得虚拟表
优点：
安全性：
如果我们直接将数据表授权给用户操作，那么用户可以CRUD
数据表中所有数据，加 入我们想要对数据表中的部分数据进行保护，可以将公开的数据生成视图，授权用户访 问视图；用户通过查询视图可以获取数据表中公开的数据，从而达到将数据表中的部分 数据对用户隐藏。
简单性：
如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们 通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图获取多表数 据，操作更便捷。
数据独立：
一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没 有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
创建视图
CREATE VIEW
视图名称
AS <SELECT
语句
>
create view
view_test1
AS
select
*
from
students
查询视图结构
desc
视图名称
;
修改视图
create OR REPLACE view
视图名称
AS
查询语句
#
或
alter view
视图名称
AS
查询语句
例：
#
方式
1
create OR REPLACE view
view_test1
AS
select
*
from
students
where
stu_gender=
'
女
'
;
#
方式
2
alter view
view_test1
AS
select
*
from
students
where
stu_gender=
'
男
'
;
特性
视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进行操作时，对原数据表中
的数据是否由影响呢？
查询操作
：如果在数据表中添加了新的数据，而且这个数据满足创建视图时查询语句的条
件，通过查询视图也可以查询出新增的数据；当删除原表中满足查询条件的数据时，也会
从视图中删除。
新增数据：
如果在视图中添加数据，数据会被添加到原数据表
删除数据：
如果从视图删除数据，数据也将从原表中删除
修改操作：
如果通过修改数据，则也将修改原数据表中的数据
视图的使用建议
:
对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图。
触发器
触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行调用
1、只有每当执行
delete
，
insert
或
update
操作时，才会触发，并执行指定的一 条或多条SQL
语句。
例如
:
可以设置触发器，当删除persons
表中
lucy
的信息时，自动删除
grade
表中与 lucy相关的信息
2、触发器常用于保证数据一致，以及每当更新或删除表时，将记录写入日志表
创建触发器
--
语法
:
--
修改
sql
语句结束符为
||
delimiter ||
--
创建触发器
create
trigger
触发器名
[after|before] [
delete
|update|delete]
on
表名
for each row
begin
语句
;
end ||
delimiter ;
old:
老数据
new:
新数据
查看触发器
语法：
show triggers;
删除触发器
drop trigger
触发器名
;
练习
--
准备工作
,
建库
,
切库
CREATE DATABASE IF NOT EXISTS
lx
CHARSET
'UTF8'
;
USE
lx;
-- 1
、创建
persons
表
,id
设置为主键
,
表的格式
(id integer primary key,name text,addr text,class text)
CREATE TABLE
person(
id
integer
primary key auto_increment
,
name
text
,
addr
text
,
class
text
);
-- 2
、创建
grade
表
,
设置约束条件
(score>0)
，表的格式
(id integer, score integer, year text)
create table
grade(
id
integer
, score
integer
,
year text
);
-- 3
、追加相应的数据
,
见下页
insert into
person (name, addr, class)
value
(
'lucy'
,
'beijing'
,
'class_A'
);
insert into
person (name, addr, class)
value
(
'peter'
,
'tianjing'
,
'class_B'
);
insert into
person (name, addr, class)
value
(
'bob'
,
'hebei'
,
'class_A'
);
insert into
grade (id, score,
year
)
value
(
1
,
78
,
'2013'
);
insert into
grade (id, score,
year
)
value
(
2
,
98
,
'2013'
);
insert into
grade (id, score,
year
)
value
(
3
,
80
,
'2013'
);
insert into
grade (id, score,
year
)
value
(
1
,
88
,
'2014'
);
-- 4
、打印所有学生的信息
:
包括
name,addr,class,score,year
select
p.*,g
.score
,g
.year
from
person p,grade g
where
p
.id
= g
.id
;
-- 5
、将打印所以学生信息的语句
,
创建视图
PersonsGrade,
方便后面使用
create view
persongrade
as select
p.*,g
.score
,g
.year
from
person
p,grade g
where
p
.id
= g
.id
;
-- 6
、查看最高分数是多少？
select max
(score)
from
persongrade;
-- 7
、查看每个班的平均分，以及对应的班级名称和班级人数
select
class
班级
,
count
(
distinct
id)
人数
,
avg
(score)
from
persongrade
group by
class;
-- 8
、在
persons
表中
id
列创建一个索引
persons_id_index
create index
person_id_index
on
person (id);
-- 9
、查看
lucy
在
2013
年的成绩
select
*
from
persongrade
where
name=
'lucy'
and
year
=
'2013'
;
-- 10
、
bob
要退学，必须要删除
bob
所有的信息
(
包括成绩单
),
通过设置触发器，自动删除bob
相关的所有数据，设置触发器
tg_delete
delimiter
||
create trigger
tg_delete
after delete on
person
for each row
begin
delete from
grade
where
grade
.id
= old
.id
;
end
||
delimiter
;
-- 11
、执行删除
bob
的操作
delete from
person
where
name=
'bob'
;
-- 12
、查看所有人的信息，确定是否执行了删除操作
select
*
from
person;
select
*
from
grade;
select
*
from
persongrade;
