SQL基础语法大全

介绍

SQL-操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

SQL-操作关系型数据库的编程语言

图形化界面工具

MySQL图形化界面

下面是市面上流行的图形化界面工具

• DG
• Sqlyog
• Navicat
• DataGrip

SQL分类

SQL分类

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

DDL-数据定义语言

数据定义语言(附-MySQL数据类型.xlsx)

数据库操作（database）

操作	说明
查询	查询所有数据库 SHOW DATABASES; 查询当前数据库 SELECT DATABASE;
创建	CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除	DROP DATABASE [IF EXISTS] 数据库名;
使用	USE 数据库名;

---

表操作（table）

创建表

格式:

CREATE TABLE表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
字段n 字段n类型[COMMENT 字段n注释]
) [COMMENT 表注释];

• 存储引擎

格式

ENGINE=InnoDB

表示采用InnoDB储存引擎,同时InnoDB也是默认值

查表

操作	格式
查表	DESC TABLE 表名;

查询建表语句

show create table 表名;

修改/删除（ALTER）

操作	格式
添加字段	ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT注释] [约束];
修改数据类型	ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)；
修改字段名和字段类型	ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
删除字段	ALTER TABLE 表名 DROP 字段名;
修改表名	ALTER TABLE 表名 RENAME TO 新表名;
删除表	DROP TABLE [IF EXISTS] 表名;
删除指定表，并重新创建该表	TRUNCATE TABLE 表名;

DML-数据操作语言(增/删/改)

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

1. 添加(插入)数据（INSERT)
2. 修改数据（UPDATE)
3. 删除数据（DELETE）

添加(插入)(INSERT)

给指定字段添加数据

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

#指定字段添加数据
insert employee (id,workno,name,gender,age,idcard,entrydate)values(1,'1','张三','男',18,'411302200406210132','2023-2-19');

给全部字段添加数据

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

#给全部字段添加数据

insert employee values(2,'2','李四','男',18,'411302200406210132','2023-3-19');

给指定字段批量添加数据

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

#为指定字段批量添加数据
insert employee values(3,'3','王五','男',18,'411302200406210132','2023-3-19'),(4,'4','赵六','男',18,'411302200406210132','2023-3-19');

给全部字段批量添加数据

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

注意：

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

拓展:SQL语句-查询表的内容:Select * from 表名

#查表

select*from employee;

修改(UPDATE)

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

IGNORE：当插入一条违背唯一约束的记录时，MySQL不会去尝试执行该语句

在数据库语言中，IGNORE关键字通常用于在INSERT语句中指示数据库忽略INSERT操作中的重复行，而不是返回错误。这通常用于避免重复的数据并保持数据库的一致性。在MySQL中，INSERT IGNORE语法可以用于在INSERT或REPLACE语句中处理重复数据。

#将id为1的员工的姓名修改为"吴七"
update employee set name='吴七'where id=1;

#将id为1的员工姓名修改为"张三",性别修改为"女"
update employee set name='张三',gender='女'where id=1;

#将所有员工的入职日期修改为2023年1月1日
update employee set entrydate='2023-1-1';

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

在数据库中，为表字段添加属性可以通过ALTER TABLE语句中的ADD COLUMN子语句实现，具体操作步骤如下：

假设我们要在一个名为user的表中添加一个名为email的字段，类型为varchar(50)，并且这个字段是唯一的，可以使用如下ALTER TABLE语句：

ALTER TABLE user ADD COLUMN email varchar(50) UNIQUE;

在该语句中，ADD COLUMN子句表示添加一个新的字段，email是新添加字段的名称，varchar(50)是新添加字段的数据类型，UNIQUE是该字段的属性，表示该字段的值是唯一的。

除了UNIQUE属性之外，还有一些其他的常见属性，如NOT NULL（非空约束）、DEFAULT（默认值约束）、PRIMARY KEY（主键约束）等，这些属性的使用方法类似，只需要在ALTER TABLE语句中添加相应的关键字即可。例如，为user表中的email字段添加非空约束和默认值约束，可以使用如下ALTER TABLE语句：

ALTER TABLE user ADD COLUMN email varchar(50) NOT NULL DEFAULT 'default@xyz.com';

通过为表字段添加属性，可以进一步完善数据库的约束和规范，提高数据的质量和准确性。

删除(DELETE)

DELETE FROM 表名[WHERE 条件]

#删除性别为"女"的员工信息
delete from employee where gender='女';

#删除所有员工
delete from employee;

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

DELETE语句不能删除某一个字段的值(可以使用UPDATE)。

DQL-数据查询语言

DQL-数据查询语言

介绍

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

查询关键字：SELECT

---

语法

# 插入数据
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
       (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
       (3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),
       (4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),
       (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
       (6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
       (7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
       (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
       (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
       (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
       (11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),
       (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
       (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
       (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
       (15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
       (16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');

基本查询(定义、别名、去重)

涉及关键字

SELECT 字段列表

FROM 表名列表

格式:

1.查询多个字段

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

2.设置别名

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

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

-- 基本查询
-- 1.查询指定字段 name,workno,age 返回
select name, workno, age
from emp;

-- 2.查询所有字段返回
select id,
       workno,
       name,
       gender,
       age,
       idcard,
       workaddress,
       entrydate
from emp;

# 通配符 * 也可以查询所有字段,但是不建议,因为不够直观
select *
from emp;

-- 3.查询所有员工的工作地址，起别名
select workaddress
from emp;

# 起别名
select workaddress as '工作地址'
from emp;

# as可以省略
select workaddress '工作地址'
from emp;

-- 4.查询公司员工的上班地址（不要重复）
# distinct关键字
select distinct workaddress as '工作地址'
from emp;

---

条件查询（WHERE）

运算符

涉及关键字

WHERE 条件列表

格式

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

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

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

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

-- 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 in (18, 20, 40);

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

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

---

分组查询（GROUP BY）

涉及关键字

• GROUP BY 分组字段列表
• HAVING 分组后条件列表

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

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意

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

select name,gender,*count*(*)

from emp

group by gender;

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

select name, 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)

涉及关键字

WHERE 条件列表

格式

SELECT 字段列表 FROM 表名 ORDER BY 字段1排序方式1,字段2排序方式2；

排序方式

ASC:升序（默认值）

DESC：降序

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

-- 排序查询
-- 1.根据年龄对公司的员工进行升序排序
# 升序 asc可省略
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）

涉及关键字

LIMIT 分页参数

格式

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

注意

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

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

# 若索引从0开始,则0可省略
select *
from emp
limit 10;

-- 2.查询第2页员工数据，每页展示10条记录(起始索引 = (查询页码-1) * 每页显示记录数)
select *
from emp
limit 10,10;
-- limit参数:起始索引,查询记录数量

聚合函数

1.介绍

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

2.常见聚合函数

常见聚合函数

-- 聚合函数
-- 1.统计该企业员工数量
select count(*)
from emp;
# 注意：null值不参与所有聚合函数运算。
select count(idcard)
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 workaddress = '西安';

DCL-数据控制语言

简介

在数据库语言中，DCL指的是“数据控制语言”（Data Control Language）。DCL允许数据库管理员或用户授予或撤消数据库对象的访问权限，以及执行其他数据控制操作。DCL包含以下几个命令：

1. GRANT：用于授予用户或者角色访问数据库对象（如表、视图、存储过程）的权限。

2. REVOKE：用于撤销用户或者角色对数据库对象访问权限的授权。

3. DENY：用于拒绝某个用户或角色对数据库对象的访问权限。

DCL语言是数据库管理中的重要组成部分，在安全性和数据控制方面扮演了关键的角色。通过使用DCL语言，数据库管理员可以更好地控制数据的访问权限和保护数据库免受非授权的操作和攻击。

管理用户

1. 查询用户

   USE mysql;
   SELECT * FROM user;
   

   

2. 创建用户

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

   # 创建用户"zhangsan",只能在当前主机localhost访问,密码123456
   create user 'zhangsan'@'localhost' identified by '123456';
   
   # 创建用户"zhangsan"，可以在任意主机访问该数据库，密码123456
   create user 'zhangsan'@'%' identified by '123456';
   

3. 修改用户密码

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

   # 修改用户"zhangsan"的访问密码为1234;
   alter user 'zhangsan'@'%' identified with mysql_native_password by '1234';
   

4. 删除用户

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

   # 删除zhangsan@localhost用户
   drop user 'zhangsan'@'localhost';
   

注意：

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

权限控制

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

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

1. 查询权限

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

2. 授予权限

   GRANT 权限列表 ON 数据库名.表名 TO'用户名'@'主机名';
   # *.*代表所有数据库中所有的表
   GRANT 权限列表 ON *.* TO'用户名'@'主机名';
   

3. 撤销权限

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

注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有。