03.基础—SQL-优快云博客

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据库操作

查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

删除

DROP DATABASE [IF EXISTS] 数据库名；

使用

USE 数据库名；

查询

查询当前数据库所有表

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 表注释];

注意：[...]为可选参数，最后一个字段后面没有逗号

数据类型

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

修改

添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束];

案例

为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)

修改

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新字段类型(长度);

修改字段名和字段类型

ALTER TABLE 数据表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT注释] [约束];

案例

将emp表的nickname字段修改为username，类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT'昵称';

删除

删除字段

ALTER TABLE 表名 DROP 字段名;

案例：

将emp表的字段username删除

ALTER TABLE emp DROP username;

删除表

DROP TABLE [IF EXISTS] 表名；

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

TRUNCATE TABLE 表名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例：

将emp表的表名修改为employee

ALTER TABLE amp RENAME TO employee;

总结

1.DDL-数据库操作

SHOW DATABASES;                     查询所有数据库
CREATE DATABASE 数据库名;            创建数据库
USE 数据库名;                        使用数据库
SELECT DATABASE();                  选择数据库
DROP DATABASE 数据库名；             删除数据库

2.DDL-表操作

SHOW TABLES;                                             查看表
CREATE TABLE 表名(字段 字段类型，字段 字段类型);              创建表
DESC 表名;                                                查询表结构             
SHOW CREATE TABLE 表名;                                   查询指定表的建表语句
ALTER TABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO...;     
添加字段/修改数据类型/修改字段名/删除字段/修改表名...;
DROP TABLE 表名;                                          删除表名

图形化界面工具DataGrip

DML

介绍

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。

添加数据(INSERT)
修改数据(UPDATE)
删除数据(DELETE)

id	name	age	gender
1	令狐冲	23	男
2	风清扬	68	男
3	东方不败	32	男

添加数据

1.给指定字段添加数据

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);

2.给全部字段添加数据

INSERT INTO 表名 VALUES(值1,值2,...);

3.批量添加数据

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

DELETE FROM 表名 [WHERE 条件]

注意：

Delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
Delete语句不能删除某一个字段的值(可以使用Update)。

DQL

介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

查询关键字：SELECT

语法

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

基本查询

1.查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;

SELECT * FROM 表名;

2.设置别名

SELECT 字段1 [AS 别名1],字段2 [AS 别名2]...FROM 表名;

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询（WHERE）

1.语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2.条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN...AND	在某个范围之内（含最小、最大值）
IN（...）	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND或&&	并且（多个条件同时成立）
OR或\|\|	或者（多个条件任意一个成立）
NOT 或！	非，不是

案例：

--1.查询年龄等于88的员工信息
select * from emp where age =88;

--2.查询年龄小于20的员工信息
select * from emp where age < 20;

--3.查询年龄小于等于20的员工信息
select * from emp where age <= 20;

--4.查询没有身份证号的员工信息
select * from emp where idcard is NULL;

--5.查询有身份证号的员工信息
select * from emp where idcard is not NULL;

--6.查询年龄不等于88的员工信息
select * from emp where age != 88;
select * from emp where age <> 88;

--7.查询年龄在15岁（包含）到20岁（包含）之间的员工信息
select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;

--8.查询性别为女且年龄小于25的员工信息
select * from emp where gender = '女' and age < 25;

--9.查询年龄等于18 或20 或40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age = 18 || age = 20 || age = 40;
select * from emp where age in(18,20,40);

--10.查询姓名为两个字的员工信息 _%
select * from emp where name like '__';

--11.查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

聚合函数（count、max、min、avg、sum）

1.介绍

将一列数据作为一个整体，进行纵向计算。

2.常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

3.语法

SELECT 聚合函数(字段列表) FROM 表名;

注意

null值不参与所有聚合函数运算。

案例

--1.统计该企业员工数量
select count(*) from emp;

--2.统计给企业员工的平均年龄
select avg(age) from emp;

--3.统计该企业员工的最大年龄
select max(age) from emp;

--4.统计该企业员工的最小年龄
select min(age) from emp;

--5.统计西安地区员工的年龄之和
select sum(age) from emp where workadddress = '西安';

分组查询（GROUP BY）

1.语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2.where与having区别

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

注意：

执行顺序：where > 聚合函数 > having.
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

案例：

--1.根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;

--2.根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;

--3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having count(*) >= 3;

排序查询（ORDER BY）

1.语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2，排序方式2;

2.排序方法

ASC:升序（默认值）
DESC：降序

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

案例：

--1.根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
select * from emp order by age desc;


--2.根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;

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

分页查询（LIMIT）

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注意：

起始索引从0开始，起始索引=（查询页码-1）* 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

案例：

--1.查询第一页员工数据，每页展示10条记录
select * from emp limit 0,10;

--2.查询第2页员工数据，每页展示10条记录-------->（页码-1）*页展示记录数
select * from emp limit 10,10;

案例

--1.查询年龄为20，21，22，23岁的女性员工信息
select * from emp where gender = '女' and age in(20,21,22,23);

--2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。
select * from emp where gender = '男' and (age between 20 and 40) and name like '___';

--3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。
select gender, count(*) from emp where age < 60 group by gender;

--4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name, age from emp where age <= 35 order by age asc, entrydate desc; 

--5.查询性别为男，且年龄在20-40岁（内）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate asc limit 5;

执行顺序

总结

DCL

介绍

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

1.查询用户

USE mysql;
SELECT * FROM user;

2.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3.修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4.删除用户

DROP USER '用户名'@'主机名';

注意：

主机名可以使用%通配。
这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据库管理员)使用。

--1.创建用户itcast，只能够在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';


--2.创建用户heima，可以在任意主机访问该数据库，密码123456
create user 'heima'@'%' identified by '123456';


--3.修改用户heima的访问密码为1234；
alter user 'heima'@'%' identified with mysql_native_password by '1234';


--4.删除itcast@localhost用户
drop user itcast@localhost;

权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

1.查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2.授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

多个权限之间，使用逗号分隔。

授权时，数据库名和表名可以使用*进行通配，代表所有。