数据库二（sql语言——DDL/DML）

一、SQL

SQL 结构化查询语言 ：是使用关系型数据库的应用语言，与数据直接打交道 。标准：SQL-86 SQL-89
SQL-92 SQL-99

作为数据库编程语言SQL，有着一个规定的标准SQL规范的标准语言（普通话），而在不同的数据库中又有着不同的语言规范来帮助使用 Mysql特有（方言）、 Oracle特有（方言）。

1.1SQL语言的规则与规范

基本规则：

• SQL语句可以写在一行或者多行 ，为了提供可读性，各子句分行写 ，必要时有缩进
• 每条命令以;或者\g或\G结束
• 关键字不能被缩写也不能分行

关于标点符号

• 必须保证所有的 ()、;、单引号 、双引号都是成对出现，而且必须是英文的半角状态
• 字符串型和日期型的数据可以使用单引号表示
• 列的别名，尽量使用双引号（“”） 而且不建议省略as

大小写规范：

• mysql在windows上大小写不敏感
• MySQL在Linux上是大小写敏感的
• 数据库名 表明 表的别名 变量名 都是严格区分大小写
• 关键字，函数名 列名 列的别名是忽略大小写的

推荐采用统一的书写规范

• 数据库名 表明 表的别名 字段名 字段别名 都小写
• sql关键字 函数名 变量都大写

1.2注释（掌握）

单行注释：#注释文字（mysql特有的）
单行注释： – 注释文字 （sql标准 – 后边必须包含一个空格）
多行注释： /* 注释文字 */

1.3命名规则（掌握）

• 数据库、表名不得超过30个字符
• 必须只能包含A-Z a-z 0-9 _共63个字符
• 数据库名 表名 字段名等名称中间不能包含空格
• 同一个Mysql数据库中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
• 在命名的时候，需要避开保留字，数据库系统所使用的，常用的方法，如果出现了冲突，可以加上 着重号（``）引起来

举例

1.4SQL分类（记忆）

SQL语言在功能上主要分为如下3类：

• DDL（data definition Languages，数据定义语言）。这些语句主要用来定义数据库，表，试图，索引等数据库对象，还可以用来创建，删除，修改数据库和数据表的结构，主要的关键字包括： create 、 drop 、alter等
• DML(data Manipulation Languages,数据操作语言) 用于添加 删除 更新 查询数据库记录，并检查数据的完整性，主要包括的关键字：insert delete update select等。
• DCL（data control Languages，数据控制语言） 用于定义数据库，表，字段，用户的访问权限和安全级别。关键字包括：Grant ，revoke ，commit，rollback，savepoint等

因为查询语句使用频繁，所以很多人将查询语句单独拎出来分成一类（非官方），DQL(数据库查询语
言)

二、DDL(数据定义语言——数据库 表的创建 修改)

2.1数据库的操作

1、查询所有数据库

show databases;

• information_schema：是mysql系统自带的数据库，主要保存mysql数据库服务器的系统信息，比如数据库的名称，数据库中表的名称，字段名称，存取的权限，数据文件所在的目录 ，系统使用的 目录等。
• performance_schema：是mysql系统自带的数据库 可以用来监控mysql的各类性能的指标
• sys：是mysql系统自带的数据库 主要作用是一种更容易理解的方式展示mysql数据库的各类性能指标，帮助系统和开发人员监控mysql的性能。
• mysql：保存mysql数据库服务器运行时所需的系统信息，比如数据文件的目录，当前使用的字符 集，检查约束等等信息。查询当前所使用的数据库
  
  2、创建数据库

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规
则] ;

create database if not exists studydb;

3.删除数据库

drop database [if exists] 数据库名;

2.2表的操作

1.查看当前数据库中的所有表

2.查看指定表的结构

3.查看建表语句

4.创建表结构

create table 表名{
字段1 字段1类型 [Comment 字段1的注释],
字段2 字段2类型 [Comment 字段2的注释],
字段3 字段3类型 [Comment 字段3的注释],
....
字段n 字段n类型 [Comment 字段n的注释]
}[comment 表的注释]

CREATE TABLE tb_user(
id int COMMENT '编号',
`name` VARCHAR(50) COMMENT '姓名',
age int COMMENT '年龄',
gender VARCHAR(1) COMMENT '性别'
) COMMENT '用户表';

5.表中的数据类型

mysql中的数据类型很多，主要分为三类：数值类型 字符串类型 日期时间类型

数值类型：

• tinyint 1byte
• samllint 2byte
• int或者integer 4byte
• bigInt 8byte
• float 4byte
• double 8byte

年龄： tinyint 分数：double(4,1)

字符串类型：

• char(指定字符数) 1byte 定长字符串
• varchar(指定字符数) 变长字符串
• blob 二进制形式的长文本数据
• longblob 二进制形式的极大长文本数据
• text 长文本数据
• longtext 极大长文本数据

用户名 username varchar(30)
性别 gender char(1)
手机号 phone char(11)

日期时间类型

• date 日期值
• time 时间值
• year 年份值
• datatime 时间日期值
• timestamp 混合的日期和时间值 时间戳 格林尼治时间

生日 birthday date
创建时间 creattime timestamp

案例：

• 创建一张员工表：

编号（纯数字）
员工工号（字符串类型 长度不超过10位）
员工的姓名（字符串类型 长度不超过10位）
性别（男/女 存储一个汉字）
年龄
身份证号码：（18位 ）
入职时间（取年月日即可）

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 '员工表';

6.修改表的结构

添加字段

alter table 表名 add 字段名 类型（长度） [comment 注释] [约束];

给emp表增加一个新的字段 昵称：nickname 类型 varchar(20)

-- 给emp表增加一个新的字段 昵称：nickname 类型 varchar(20)
ALTER TABLE emp add nickname VARCHAR(20) COMMENT '昵称';

修改字段数据类

alter table 表名 modify 字段名 新的数据类型（长度）
-- 将日志日期改为datatime类型
alter TABLE emp MODIFY entrydate DATETIME;

修改字段名和字段的类型

alter table 表名 change 旧字段名 新字段名 类型（长度 ）[comment 注释] [约束];

将emp表中的nickname字段修改为username 类型 varchar（30）

-- 将emp表中的nickname字段修改为username 类型 varchar（30）
ALTER TABLE emp CHANGE nickname username VARCHAR(30);

删除字段

alter table 表名 drop 字段;

删除emp表中的username

-- 删除emp表中的username
ALTER TABLE emp DROP username;

修改表名：

alter table 表名 rename to 新表名；

将emp表名修改为employee

-- 将emp表名修改为employee
ALTER TABLE emp RENAME TO employee;

7.删除表

drop table [if exists] 表名

删除tb_user表

DROP TABLE if EXISTS tb_user;

删除指定表并重新创建该表

truncate table tb_user; -- 截断表 清空表中的内容 同时释放所占有的资源

三、DML （数据操作语言——增删改查（DQL））

用来对数据库中的表的记录进行操作：
添加数据（insert）
修改数据（update）
删除数据（delete）

3.1添加数据

1 给指定的字段添加数据

insert into 表名(字段1，字段2，字段3,.....) values(值1，值2，值3，....);

给employee表中的所有字段添加数据

INSERT INTO employee(id,workno,`name`,gender,age,idcard,entrydate)
VALUES(1,'1','admin','男',20,'123456789012345678','2000-01-01 09:00:00');

2.给所有的字段添加数据

insert into 表名 values(值1，值2，值3，....);

INSERT INTO employee
VALUES(2,'2','lucy','女',18,'123456789012345678','2010-01-01 09:00:00');

3.批量插入数据

insert into 表名(字段1，字段2，字段3,.....) values(值1，值2，值3，....)，(值1，值2，值
3，....)，(值1，值2，值3，....)，(值1，值2，值3，....);
insert into 表名 values(值1，值2，值3，....),(值1，值2，值3，....),(值1，值2，值
3，....),(值1，值2，值3，....);

INSERT INTO employee
VALUES(3,'3','lucy','女',18,'123456789012345678','2010-01-01 09:00:00')
,(6,'6','lucy','女',18,'123456789012345678','2010-01-01 09:00:00')
,(4,'4','lucy','女',18,'123456789012345678','2010-01-01 09:00:00')
,(5,'5','lucy','女',18,'123456789012345678','2010-01-01 09:00:00');

注意：

• 如果指定了字段，字段的顺序和值的顺序要一致
• 字符串和日期类型的数据 应该使用引号引起来
• 插入的值的大小，应该在字段规定的范围内。

3.2修改操作

update 表名 set 字段名=值，字段名=值，... [where 条件]

-- 修改id为1 的记录，将name修改为administrator
UPDATE employee SET `name`='admin' where id = 1;
-- 将id为2的员工 name修改为lucy 并且将性别修改为女
update employee SET `name` ='lucy' ,gender='女' where id=2;
-- 将所有员工的入职时间改为2008年12月1日
update employee SET entrydate='2008-12-01';

3.3删除数据

delete from 表名[where 条件];

-- 删除所有性别为男的员工
delete from employee where gender='男';
-- 删除所有员工信息
DELETE FROM employee;

注：在执行删除操作时，条件可以有，也可以没有，但是没有条件则是删除所有数据，尽量是有条件的删除，否则会清空表，这种操作是很危险。

四、DQL 数据查询语言

主要查询数据库表中的记录
查询主要用到的关键字：SELECT

4.1基本语法

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后的条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

主要分为如下几部分：

• 基本查询（不带任何条件）
• 条件查询（WHERE）
• 聚合函数（count，max，min，avg，sum）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

4.2基本查询

-- 查询特定字段
select 字段1，字段2，字段3，... from 表名;
-- 查询所有字段
select * from 表名;

*通配符 表示所有字段，在实际开发中 不建议使用

-- 查询指定字段 name,workno,age
select `name`,workno,age from emp;
-- 查询所有字段
select * from emp;

去除重复记录

select distinct 字段列表 from 表名;

-- 查询所有员工的工作地点
select workaddress from emp;
-- 去除重复记录
select distinct workaddress from emp;

字段设置别名：

select 字段1 [as 别名1]，字段2 [as 别名2] from 表名;
select `name` as '姓名',workno as '工号',age '年龄' from emp;

在给字段设置别名的时候 as 可以省略，但是不建议 而且别名最好都加上单引号

练习：
练习world库中表的结构：

1 查询world数据库中的country表中的所有的记录;
2 查询world数据库中的country表这些国家所处的区域。
3 查询world数据库中的country表中的国家的名称，人口，国土面积，独立年份这几个字段，并设置结果表
头为中文。

1 – 查询world数据库中的country表中的所有的记录;
select * from country;
2 – 查询world数据库中的country表这些国家所处的区域。
select DISTINCT Region from country;
3 – 查询world数据库中的country表中的国家的名称，人口，国土面积，独立年份这几个字段，并设置结果表
头为中文。
select NAME as ‘国家名称’ ,Population as ‘人口’, SurfaceArea as '国土面积 ',IndepYear
as ‘独立年份’ from country;

4.3条件查询

语法：

select 字段列表 from 表名 where 条件列表;

条件可以使用的运算符：

比较运算符： > < >= <=
!=或<>(不等于)
在两个范围之间 between…and…
在列表之中的 in(…)
模糊匹配 like
是null is null
非null is not null

常用的逻辑运算符：

and 或 &&
or 或 ||
not 或 ！

-- 查询年龄等于88的员工
select * from emp where age =88;
-- 查询年龄小于20的员工
select * from emp where age <20 ;
-- 查询年龄小于等于20的员工
select * from emp where age <= 20 ;
-- 查询没有身份证号的员工
select * from emp where idcard is null;
select * from emp where idcard = null; -- 错误 无结果
-- 查询有身份证号的员工
select * from emp where idcard is not null;
-- 查询年龄在15 到20之间的员工
select * from emp where age > 15 and age < 20;
select * from emp where age > 15 && age < 20;
select * from emp where age BETWEEN 15 and 20; -- 包含20 但是不包含15
-- 查询性别为女且年龄小于25的员工信息
select * from emp where gender= '女' and age < 25;
-- 查询年龄等于 18 或者20或者 40的员工信息
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in(18,20,40);
-- 查询姓名为两个字的员工信息
select * from emp where `name` like '__'; -- _表示一个字符
-- 查询姓张的员工信息
select * FROM emp where `name` like '张%';-- % 表示0个或多个字符
-- 查询身份证号最后一位为X的员工信息
select * from emp where idcard like '%X';

练习：
1 查询国家人口少于100000的国家信息;
2 查询国人人口大于 100000000的国家信息;
3 查询有独立年份的国家信息
4 查询没有独立年份的国家信息
5 查询人均寿命介于 40到60之间的国家信息
6 查询国家名称以C开头的国家信息
7 查询国家名称中包含字母A的国家信息
word表

4.4聚合函数

将一列数据作为整体，进行纵向计算
常见的聚合函数：

• count 统计记录数
• max 计算最大值
• min 计算最小值
• avg 计算平均值
• sum 计算求和

语法：

select 聚合函数(字段) from 表名 [where条件];
注意：null值不参与所有的聚合运算

-- 查询该企业员工的数量
select COUNT(*) AS number from emp;
select COUNT(idcard) from emp;
select COUNT(1) from emp;
-- 统计该企业员工的平均年龄
select avg(age) from emp;
-- 统计该企业员工的最大年龄、最小年龄
select max(age),min(age) from emp;
-- 统计西安地区的员工的年龄之和
select sum(age) from emp WHERE workaddress = '西安';

练习：
1 统计国家的数量。
2 统计人口最多的 国家。
3 统计人口最少的国家。
4 统计全球的人口的平均寿命；
5 统计亚洲地区的人口总和。

select COUNT(*) AS number from country;

SELECT Name, SUM(Population) AS Population 
FROM country 
GROUP BY Name 
ORDER BY Population DESC 
LIMIT 1;

SELECT Name, SUM(Population) AS Population 
FROM country 
GROUP BY Name 
ORDER BY Population ASC 
LIMIT 1;

select avg(LifeExpectancy) from country;

select sum(Population) from country WHERE Region='Asia';

4.5分组查询

语法：

• select 字段列表 from 表名 [where 条件] group by 分组字段 [Having 分组后的过滤条件];

where和having的区别：

• 执行的时机不同：where是在分组之前进行过滤，不满足where 条件的不参与分组，having是分组之后对结果进行过滤
• 判断条件不同： where不能对聚合函数进行判断，而having可以。

注意：

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段是无意义的。
• 执行顺序：where > 聚合函数 > having
• 支持多字段分组：group by 多个字段

-- 根据性别，分别统计男性和女性员工的数量
select gender,COUNT(*) FROM emp GROUP BY gender;
-- 根据性别，统计男性和女性员工的平均年龄
select gender ,AVG(age) from emp GROUP BY gender;
-- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 GROUP BY
workaddress HAVING address_count >= 3;
-- 统计各个工作地址上班的男性及女性员工的数量
select workaddress,gender ,count(*) as '数量' FROM emp GROUP BY workaddress,gender;

练习：
1 根据地区分别统计人口的平均数量。

SELECT region, AVG(population) AS avg_population
FROM country
GROUP BY region;

2 根据地区分别统计人口的平均寿命大于70的国家信息。

SELECT region,  AVG(LifeExpectancy) AS avg_LifeExpectancy
FROM country
WHERE LifeExpectancy > 70
GROUP BY region;

4.6排序查询

语法：

select 字段列表 from 表名 order by 排序字段列表 [排序规则 asc/desc]

asc 升序 （默认值）
desc 降序
当排序字段是多个的时候，如果第一字段值相同，则根据后边的字段进行排序，以此类推

-- 根据年龄对公司员工进行升序排序
select * from emp order by age;
select * from emp order by age ASC;
-- 根据入职时间，对员工进行降序排序
select * from emp ORDER BY entrydate desc;
-- 根据年龄对公司员工进行升序排序，年龄相同，在按照入职时间进行降序排序
select * from emp ORDER BY age , entrydate desc;

练习：
1 根据国家的国土面积进行降序排序

select * from country order by SurfaceArea DESC;

2 根据人口数量进行升序排序，如果人口数量相同，则按照国土面积进行降序排序，如果国土面积相同，则按照独立时间进行升序排序。

SELECT name, population, SurfaceArea, IndepYear
FROM country
ORDER BY population ASC, SurfaceArea DESC, IndepYear ASC;

3 对美洲地区的国家按照GNP进行降序排序；

SELECT Name, GNP
FROM Country
WHERE Region = 'North America' OR Region = 'South America'
ORDER BY GNP DESC;

小结

DQL的具体语法：
select
字段列表
from
表名
where
条件列表
group by
分组字段列表
having
分组后的条件列表
order by
排序字段列表
Limit
分页参数

优先级

DQL:数据查询语言

1 基本查询 select … from 表名
2 条件查询 select … from 表名 where 条件
3 聚合函数 count sum avg max min
4 分组查询 select .【聚合函数/分组字段】… from 表名 where 条件 group by 分组字段 having 分组之后的
过滤
5 排序查询 select … from 表名 where 条件 order by 排序字段 （asc/desc）