【MySQL】SQL入门(一)
🚗MySQL学习·起始站~
一.引入
(1) SQL分类
SQL语句,根据其功能,主要分为四类:DDL
、DML
、DQL
、DCL
。
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表, 字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
注:后续SQL示例中 [可选内容] 表示可选内容,即可写可不写.
(2) 书写规则
- SQL语句可以单行或多行书写,以分号结尾。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 在同一个数据库服务器中,不能创建两个名称相同的数据库,否则将会报错。
- 如果删除一个不存在的数据库,将会报错。
- 注释:
- 单行注释:
--
注释内容 或#
注释内容 - 多行注释:
/*
注释内容*/
- 单行注释:
二.DDL语句
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段)
(1) 数据库操作
- 查询所有数据库
show databases;
- 查询当前所在数据库(若之前未选择数据库则为null)
select database();
- 创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ];
- 删除数据库
drop database [ if exists ] 数据库名;
- 切换数据库(当我们要操作某一个数据库下的表时,就需要切换到对应的数据库下,否则是不能操作的)
use 数据库名;
(2) 表操作
(2.1) 创建
- 创建表结构
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT '字段1注释' ],
字段2 字段2类型 [COMMENT '字段2注释' ],
字段3 字段3类型 [COMMENT '字段3注释' ],
......
字段n 字段n类型 [COMMENT '字段n注释' ]
) [ COMMENT '表注释' ] ;
# 注意:最后一个字段后面没有逗号
- 添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
(2.2) 查询
- 查询当前数据库所有表
show tables;
- 查看指定表结构
desc 表名;
# 可以查看到指定表的字段,字段的类型、是否可以为NULL,是否存在默认值等信息。
- 查询指定表的建表语句
show create table 表名;
# 可以用来查看建表语句的sql,其中部分参数我们在创建表的时候,并未指定也会查询到,因为这部分是数据库的默认值,如:存储引擎、字符集等。
(2.3) 修改
- 修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
- 同时修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
(2.4) 删除
注意: 在删除表的时候,表中的全部数据也都会被删除。
- 删除字段
ALTER TABLE 表名 DROP 字段名;
- 删除表
DROP TABLE [ IF EXISTS ] 表名;
- 删除指定表, 并重新创建表
TRUNCATE TABLE 表名;
(2.5) 表字段数据类型
在上述的建表语句中,我们在指定字段的数据类型时,用到了int ,varchar。除此之外,MySQL中还有许多的其他数据类型,它们主要分为三类:数值类型、字符串类型、日期时间类型。
- 数值类型
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|
TINYINT |
1byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT |
2bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT |
3bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT/INTEGER |
4bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT |
8bytes | (-2^63,2^63-1) | (0,2^64-1) | 极大整数值 |
FLOAT |
4bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数 值 |
DOUBLE |
8bytes | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 双精 度浮 点数 值 |
DECIMAL |
依赖于M(精度)和D(标度) 的值 | 依赖于M(精度)和D(标度)的 值 | 小数 值(精 确定 点数) |
注:DECIMAL
中M(精度)表示整个数值长度,D(标度)表示小数位长度。例如:123.45。M=5,D=2。
# 使用示例
1) 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2) 分数 -- 总分100分, 最多出现一位小数
score double(4,1)
- 字符串类型
类型 | 大小 | 描述 |
---|---|---|
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 | 极大文本数据 |
注:使用时通常会在类型后面加上()表示占用空间.
char 与 varchar 都可以描述字符串,char是定长字符串,指定长度多长,就占用多少个字符,和 字段值的长度无关 。而varchar是变长字符串,指定的长度为最大占用长度 。相对来说,char的性能会更高些,而varchar相对更节省存储空间。
# 使用示例
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)
- 日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
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). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime
三.DML语句
Data Manipulation Language,数据操作语言,用来对数据库中表的数据记录进行增、删、改操作
(1) 增加
注意点:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小,应该在字段的规定范围内
- 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
- 给全部字段添加值
INSERT INTO 表名 VALUES (值1, 值2, ...);
- 批量添加数据
# 1.指定字段批量添加
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
# 2.全部字段批量添加
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
(2) 修改
修改语句的where条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
- 修改字段值
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,.... [ WHERE 条件 ]
(3) 删除
注意点:
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数 据
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL)
- 删除记录
DELETE FROM 表名 [ WHERE 条件 ] ;
四.DQL语句
Data Query Language,数据查询语言,用来查询数据库中表的记录。
(1) 基础查询
- 查询指定字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名;
- 查询全部字段
# * 号代表所有字段
SELECT * FROM 表名;
- 查询时为字段设置别名
# 1. 可使用 as 设置别名
SELECT 字段1 [ AS 别名1 ],字段2 [ AS 别名2 ] ... FROM 表名;
# 2. 也可以直接空格设置别名
SELECT 字段1 [ 别名1 ],字段2 [ 别名2 ] ... FROM 表名;
- 去除重复记录查询
SELECT DISTINCT 字段1,字段2... FROM 表名;
(2) 条件查询
(2.1) 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
(2.2) 条件
- 常用的比较运算符如下:
比较运算符 | 功能 |
---|---|
> |
大于 |
>= |
大于等于 |
< |
小于 |
<= |
小于等于 |
= |
等于 |
<> 或 != |
不等于 |
BETWEEN ... AND ... |
在某个范围之内(含最小、最大值) |
LIKE 占位符 |
模糊匹配(_ 匹配单个字符, % 匹配任意个字符) |
IS NULL |
是否为NULL |
IN(...) |
在(…)列表中的值,多选一 |
ANY(...) |
在(…)列表中的值,有任意一个满足即可 |
SOME(...) |
与ANY一致,可相互替换 |
ALL(...) |
必须满足(…)列表中的所有值 |
- 常用的逻辑运算符如下:
逻辑运算符 | 功能 |
---|---|
AND 或 && |
并且 (多个条件同时成立) |
OR 或 || |
或者 (多个条件任意一个成立) |
NOT 或 ! |
非 , 不是 |
(2.3) 使用示例
- 查询年龄等于 88 的员工
select * from emp where age = 88;
- 查询年龄不等于 88 的员工
select * from emp where age != 88;
select * from emp where age <> 88;
- 查询没有身份证号的员工信息
select * from emp where idcard is null;
- 查询有身份证号的员工信息
select * from emp where idcard is not null;
- 查询年龄在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;
- 查询年龄等于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 '范_'; # 名字只可以为范X
- 查询姓范员工信息
select * from emp where name like '范%'; # 名字可以为范X,范XX,范XXX等等
(3) 聚合函数查询
将一列数据作为一个整体,进行纵向计算
(3.1) 语法
SELECT 聚合函数(字段列表) FROM 表名;
(3.2) 常见函数
函数 | 功能 |
---|---|
count |
统计数量 |
max |
最大值 |
min |
最小值 |
avg |
平均值 |
sum |
求和 |
(3.3) 使用示例
NULL值不参与所有聚合函数运算
- 统计该企业员工数量
select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数
- 统计该企业员工的平均年龄
select avg(age) from emp;
- 统计该企业员工的最大年龄
select max(age) from emp;
- 统计该企业员工的最小年龄
select min(age) from emp;
- 统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';
(4) 分组查询
(4.1) 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
注意事项:
where
与having
区别- 执行时机不同:where是分组之前进行过滤;having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。执行顺序: where > 聚合函数 > having。
- 支持多字段分组, 具体语法为 : group by 分组字段名1,分组字段名2
(4.2) 使用示例
- 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) 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(*) '数量' from emp group by gender,workaddress;
(5) 排序查询
(5.1) 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式,字段2 排序方式;
- 排序方式类别
- ASC:升序(默认值)
- DESC: 降序
- 注意事项
- 如果是升序, 可以不指定排序方式ASC ;
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
(5.2) 使用示例
- 根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
select * from emp order by age;
- 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;
(6) 分页查询
(6.1) 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
- 注意事项:
- 起始索引从0开始,计算规则为:(查询页码 - 1)* 每页显示记录数。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
(6.2) 使用示例
- 查询第1页员工数据, 每页展示5条记录
select * from emp limit 0,5;
select * from emp limit 5;
- 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from emp limit 5,5;
(7) 复合使用
(7.1) 书写顺序
同时使用上述多种查询时必须严格遵循以下顺序书写:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
(7.2) 执行顺序
- DQL语句复合使用系统执行顺序为:
from ... where ... group by ... having ... select ... order by ... limit ...
关于having、group by以及聚合函数的执行顺序问题,整个执行顺序可以这么理解:
- 先执行from语句(表之间的笛卡尔积、交并差等),获得一个虚拟表
- 如果where语句存在,从虚拟表中筛出符合where条件的数据,不满足的被剔除
- 如果group by语句存在,则目前存活的数据分组;如果不存在group by,则将这些数据视为一个组
- 如果存在having语句,则将满足having条件的组留下,不满足的组被剔除
- 执行select语句:对存活下来的每个组分别执行聚合函数,形成查询结果
- 执行order by 语句:对剩下的数据进行排序
- 执行limit 语句:限制返回的数据条数
(7.3) 使用示例
- 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender, count(*) from emp where age < 60 group by gender;
- 查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按 入职时间降序排序
select name , age from emp where age <= 35 order by age asc,entrydate desc;
- 查询性别为男,且年龄在20-40 岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序, 年龄相同按入职时间升序排序。
select * from emp where gender = '男' and age between 20 and 40 order by age asc ,entrydate asc limit 5 ;
五.DCL语句
Data Control Language,数据控制语言,用来管理数据库用户、控制数据库的访问权限
(1) 管理用户
- 查询用户
select * from mysql.user
其中 Host代表当前用户访问的主机, 如果为localhost, 代表只能够在当前本机访问,不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一 个用户。
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
(2) 权限控制
常见权限列表:
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES |
所有权限 |
SELECT |
查询数据 |
INSERT |
插入数据 |
UPDATE |
修改数据 |
DELETE |
删除数据 |
ALTER |
修改表 |
DROP |
删除数据库/表/视图 |
CREATE |
创建数据库/表 |
- 查询用户权限
SHOW GRANTS FOR '用户名'@'主机名' ;
- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
- 注意事项:
- 多个权限之间,使用逗号分隔
- 授权时, 数据库名和表名可以使用 * 进行通配,代表所有
六.全文概览
- 点赞
- 收藏
- 关注作者
评论(0)