通用SQL语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySOL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
单行注释:--注释内容 或 #注释内容(MySQL特有)
多行注释:/*注释内容*/
SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
DDL-数据库操作
查询
查询所有数据库
show database;
查询当前数据库
select database();
创建
数据库创建语法。
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
创建数据库,以默认方式。
create database my_db;
创建数据库,如果存在就不创建。
create database if not exists my_db;
创建数据库指定数据库默认字符集。
create database my_test default charset utf8mb4;
删除
数据库删除语法。
DROP DATABASE [IF EXISTS] 数据库名;
删除数据库。
drop database my_test;
删除数据库,如果存在就删除。
drop database if exists my_test;
切换
数据库使用语法。
USE 数据库名;
使用数据库。
use my_db;
此时可以通过上述查看选择的数据库命令来查看当前选择的数据库。
select database();
DDL-表操作
查询
查询当前数据库所有表。
show tables;
这里可以在后面的创建表命令后操作。
查询表结构。
DESC 表名;
desc tb_user;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
show create table tb_user;
创建表
基本语法。
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 '用户表';
数据类型
数值类型
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8 bytes | (-263,263-1) | (0,2^64-1) | 极大整数值 |
FLOAT | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
字符串类型
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
案例:设计一张员工信息表,要求如下:
1.编号(纯数字)
2.员工工号(字符串类型,长度不超过10位)
3.员工姓名(字符串类型,长度不超过10位)
4.性别(男/女,存储一个汉字)
5.年龄(正常人年龄,不可能存储负数)
6.身份证号(二代身份证号均为18位,身份证中有X这样的字符)
7.入职时间(取值年月日即可)
创建表SQL
create table emp(
id int comment '编号',
work_no varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
id_card char(18) comment '身份证号',
entry_date date comment '入职时间'
)comment '员工表';
查看表结构
desc emp;
添加字段
添加字段语法格式。
ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释] [约束];
案例:为emp表增加一个新的字段”呢称”为nickname,类型为varchar(20)
alter table emp add nickname varchar(20) comment '昵称';
查看表。
desc emp;
修改字段
修改数据类型
ALTER TABLE 表名 MODIFY 字段名新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
案例:将emp表的nickname字段修改为username,类型为varchar(30)
alter table emp change nickname username varchar(30) comment '用户名';
查看表。
desc emp;
删除字段
删除字段
ALTER TABLE 表名 DROP 字段名;
案例:将emp表的字段username删除
alter table emp drop username;
查看表。
desc emp;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
案例:将emp表的表名修改为employee
alter table emp rename to employee;
查看数据库中表。
show tables;
删除表
删除表
DROP TABLE [IF EXISTS] 表名;
案例:删除用户表。
drop table if exists tb_user;
删除指定表,并重新创建该表。(清空表中数据)
TRUNCATE TABLE 表名;
清空员工表中的数据。
truncate table employee;
DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作。
添加数据
给指定字段添加数据
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,...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
案例:给员工表插入一条记录。
insert into employee(id, work_no, name, gender, age, id_card, entry_date)
values (1,'1','张三','男',23,'510145200011225413','2011-11-26')
或者
insert into employee values(4, '4', '赵六', '男', 24, '510145200011225413', '2011-10-24');
插入多条记录
insert into employee
values (5, '5', '赵六', '男', 24, '510145200011255413', '2015-10-25'),
(6, '6', '赵六', '男', 24, '510145200011265413', '2016-10-26'),
(7, '7', '赵六', '男', 24, '510145200011275413', '2017-10-27');
修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
案例:
修改id为1的数据,将name修改为"张小明"
update employee set name ='张小明' where id = 1;
修改id为1的数据,将name修改为"小昭",gender修改为"女"
update employee set name ='小昭',gender='女' where id = 1;
将所有的员工入职日期修改为"2008-01-01"
update employee set entry_date = '2008-01-01';
注意:若使用DataGrip执行上述SQL报错Unsafe query: 'Update' statement without 'where' updates all table rows at once
,这是DataGrip的安全检查,这时我们点击Execute即可。
若点击后报错You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
,这是由于mysql开启了安全检查。
查看sql_safe_updates
是否开启。
show variables like 'sql_safe_updates';
可以看见当前已启用,这时可以使用以下语句进行修改。
-- 会话级别(0表示关闭,1表示开启)
set sql_safe_updates=0;
-- 全局(0表示关闭,1表示开启)
set global sql_safe_updates=0;
这时修改后再次尝试,就不会报错了。
删除数据
DELETE FROM 表名 [WHERE 条件];
注意:
- DELETE语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
- DELETE语句不能删除某一个字段的值(可以使用UPDATE)。
这里测试操作,建议先备份原始数据。不需要备份,可以直接跳转到案例。
DataGrip备份数据
选择自己需要的,我这里选择insert。
我这里复制存储备份的数据。
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (1, '1', '张小明', '男', 23, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (2, '2', '李四', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (3, '3', '王五', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (4, '4', '赵六', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (5, '5', '赵六', '男', 24, '510145200011255413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (6, '6', '赵六', '男', 24, '510145200011265413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (7, '7', '赵六', '男', 24, '510145200011275413', '2008-01-01');
案例:
删除gender为"女"的员工
delete from employee where gender = '女';
删除所有员工
delete from employee;
此时若出现问题,解决方法同上。
DQL
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
基本语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
基本查询
查询多个字段
-- 查询指定字段
SELECT 字段1,字段2,字段3... FROM 表名;
-- 查询所有字段
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;
去除重复记录
SELECT DISTINCT字段列表FROM表名;
案例:
准备查询数据。
drop table if exists employee;
create table employee
(
id int comment '编号',
work_no varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
id_card char(18) comment '身份证号',
work_address varchar(50) comment '工作地址',
entry_date date comment '入职时间'
) comment '员工表';
INSERT INTO employee (id, work_no, name, gender, age, id_card, work_address, entry_date)
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');
基本查询
1.查询指定字段name,work_no,age
select name,work_no,age from employee;
2.查询所有字段
select * from employee;
3.查询所有员工的工作地址,起别名
-- as关键字可以省略
select work_address as '工作地址' from employee;
4.查询公司员工的上班地址(不要重复)
select distinct work_address from employee;
条件查询(WHERE)
基本语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件–>运算符
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN…AND… | 在某个范围之内(含最小、最大值) |
IN(…) | 在in之后列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
案例:
查询年龄等于88的员工
select * from employee where age = 88;
查询年龄小于20的员工信息
select * from employee where age < 20;
查询年龄小于等于20的员工信息
select * from employee where age <= 20;
查询没有身份证号的员工信息
select * from employee where id_card is null;
查询有身份证号的员工信息
select * from employee where id_card is not null;
查询年龄不等于88的员工信息
select * from employee where age != 88;
或者
select * from employee where age <> 88;
查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from employee where age >= 15 && age <= 20;
或者
select * from employee where age >= 15 and age <= 20;
或者
select * from employee where age between 15 and 20;
查询性别为女且年龄小于25岁的员工信息
select * from employee where gender = '女' && age < 25;
查询年龄等于18或20或40的员工信息
select * from employee where age = 18 or age = 20 or age = 40;
或者
select * from employee where age in(18, 20, 40);
查询姓名为两个字的员工信息
select * from employee where name like '__';
查询身份证号最后一位是X的员工信息
select * from employee where id_card like '_________________X';
或者
select * from employee where id_card like '%X';
聚合函数(count、max、min、avg、sum)
将一列数据作为一个整体,进行纵向计算。
常见聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
基本语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:所有的聚合函数,null不参与计算。
案例:
统计该企业员工数量
select count(id) from employee;
统计该企业员工工作地址总数量
-- 用distinct关键字去除重复的工作地址
select count(distinct work_address) from employee;
统计该企业员工的平均年龄
select avg(age) from employee;
统计该企业员工的最大年龄
select max(age) from employee;
统计该企业员工的最小年龄
select min(age) from employee;
统计西安地区员工的年龄之和
select sum(age) from employee where work_address = '西安';
分组查询(GROUP BY)
基本语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where:条件,不参与分组;而naving是分组之后对结果进行过滤。
- 判断条件不同:where.不能对聚合函数进行判断,而having可以。
注意
- 执行顺序:where>聚合函数>having。
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
案例:
根据性别分组,统计男性员工和女性员工的数量
select gender,count(gender) '数量' from employee group by gender;
根据性别分组,统计男性员工和女性员工的平均年龄
select gender,avg(age) '平均年龄' from employee group by gender;
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select work_address,count(work_address) from employee where age < 45 group by work_address having count(work_address) >= 3 ;
排序查询(ORDER BY)
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2,...;
排序方式
- ASC:升序(默认值)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
案例:
根据年龄对公司的员工进行升序排序
-- asc可以省略,默认就是升序
select * from employee order by age asc ;
根据入职时间,对员工进行降序排序
select * from employee order by entry_date desc ;
根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from employee order by age asc, entry_date desc;
分页查询(LIMIT)
基本语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
- 起始索引从0开始,起始索引 =(查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是 LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
案例:
查询第1页员工数据,每页展示10条记录
select * from employee limit 0, 10;
或者
-- 查询结果是第一页数据,可以省略起始索引
select * from employee limit 10;
查询第2页员工数据,每页展示10条记录
select * from employee limit 10, 10;
DQL综合案例
查询年龄为20,21,22,23岁的女性员工信息。
select * from employee where gender = '女' and age in(20,21,22,23);
查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工。
select * from employee where gender = '男' and age between 20 and 40 and name like '___';
统计员工表中,年龄小于60岁的,男性员工和女性员工的人数。
select gender,count(gender) '员工人数' from employee where age < 60 group by gender;
查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序。
select name,age from employee where age <= 35 order by age,entry_date desc;
查询性别为男,且年龄在20-40岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
select * from employee where gender = '男' and age between 20 and 40 order by age,entry_date limit 5;
DCL
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访问权限。
管理用户
查询用户
USE mysql;
SELECT FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
注意:
- 主机名可以使用
%
通配。 - 这类SQL开发人员操作的比较少,主要是DBA(Database Administrator 数据库管理员)使用。
权限控制
MySQL中定义了很多种权限,但是常用的就以下几种
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号分隔
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有。