MYSQL基础语法
SQL通用语法
- 单行或多行书写,以分号结尾
- 使用空格和缩进来增强可读性
- 不区分大小写
- 注释
单行: --或#
多行: / *注释内容 */
SQL分类
![在这里插入图片描述](https://img-blog.csdnimg.cn/6bf6ae5eadcf493c94a778ff39ca5fa4.png width=“100” height=“30” align=“middle”/)
DDL–数据库操作
创建数据库
- CREATE DATABASE [IF NOT EXISTS] mydb1;
创建数据库后再插入相同名字的数据库则会创建失败
查询所有数据库
- SHOW DATABASES
(数据库中也有默认带了几个数据库)
查询当前数据库
- SELECT DATABASE();
删除数据库
- DROP DATABASE [IF EXISTS] mydb1;
使用数据库
- USE 数据库名;
查询当前数据库所有表
- SHOW TABLES;(前提是使用USE来进入当前数据库)
表操作-创建
CREATE TABLE 表名(
列名 列类型 [COMMENT 列名注释],
列名 列类型 [COMMENT 列名注释],
......
)[COMMENT 表注释];
use zmt;
create table tb_user(
id int comment '编号',
name varchar(50) comment'姓名',
age int comment'年龄',
gender varchar(1) comment'性别'
)comment '用户表';
查询表结构
- DESC 表名
查询指定表的建表语句
- SHOW CREATE TABLE 表名;
【注:[…]为可选参数,最后一个字段没有逗号】
数据类型
主要分为三类:数值类型、字符串类型、日期时间类型
案例
设计一张员工信息表
列名 | 类型 |
---|---|
编号 | 纯数字 |
员工工号 | 字符串类型,不超过10位 |
员工姓名 | 字符串类型,不超过10位 |
性别 | 男/女 存储一个汉字 |
年龄 | 正常人年龄,没有负数 |
身份证号 | 二代身份证号均为18位,其中有X这样的字符 |
入职时间 | 取年月日即可 |
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 '员工表';
修改表
-
往表中加入字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]例如,给emp表增加一个新的字段“昵称”为nickname,类型为varchar(20)
-
修改表中字段
修改数据类型:
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束]例如:将emp表nickname字段修改为username,类型为varchar(30)
-
删除表中字段
ALTER TABLE 表名 DROP 字段名;例如:将emp表的字段username删除
-
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
删除表
-
删除表
DROP TABLE[IF EXITS] 表名;
-
删除指定表,并重新创建该表
TRUNCATE TABLE 表名;
DML–数据操作语言
- 添加数据(INSERT)
- 修改数据(UPDATE)
- 删除数据(DELETE)
添加数据(INSERT)
1.字段数据与值一一对应
2.字符串和日期数据包含在引号之中
3.插入数据大小应该在字段的规定范围内
-
INSERT INTO 表名(字段1,字段2,…)VALUES(值 1,值2,…);
-
INSERT INTO 表名 VALUES(值 1,值2,…);—给全部字段添加数据
-
INSERT INTO 表名(字段1,字段2,…)VALUES(值 1,值2,…),(值 1,值2,…);
-
INSERT INTO 表名 VALUES(值 1,值2,…),值 1,值2,…);
修改数据(UPDATE)
- UPDATE 表名 SET 字段名1=值1,字段名2=值2,…[WHERE 条件];
(条件可以没有,如果没有条件则会修改整张表的数据)
删除数据(DELETE)
1.DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
2.DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。
- DELETE FROM 表名 [WHERE 条件]
DQL --数据查询语言
查询关键字:SELECT
一条 SQL 查询语句的执行顺序:
from–>where–>grop by–>count()–>select–>having–>order by–>limit
1.查询多个字段:
- SELECT 字段1,字段2 …FROM 表名;
- SELECT * FROM 表名;
2.设置别名
- SELECT 字段1[AS 别名1],字段2[AS 别名2] …FROM 表名;
3.去除重复记录
- SELECT DISTINCT 字段列表 FROM 表名;
运算符
算数运算符
算数运算符 | 说明 |
---|---|
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/或DIV | 除法运算,返回商 |
%或 MOD | 求余运算,返回余数 |
例如:加法 +
比较运算符
比较运算符 | 说明 |
---|---|
= | 等于 |
< 和 <= | 小于和小于等于 |
> 和 >= | 大于和大于等于 |
<=> | 安全的等于,两个操作码均为NULL时,其所得值为1;而当一个操作码为NULL时,其所得值为0 |
<>或 != | 不等于 |
IS NULL 或 ISNULL | 判断一个值是否为 NULL |
ISNOTNULL | 判断一个值是否不为 NULL |
LEAST | 当有两个或多个参数时,返回最小值 |
GREATEST | 当有两个或多个参数时,返回最大值 |
BETWEEN AND | 判断一个值是否落在两个值之间 |
IN | 判断一个值是IN列表中的任意一个值 |
NOT IN | 判断一个值不是IN列表中的任意一个值 |
LIKE | 通配符匹配 |
REGEXP | 正则表达式匹配 |
逻辑运算符
逻辑运算符 | 说明 |
---|---|
NOT 或者 ! | 逻辑非 |
AND 或者 && | 逻辑与 |
OR 或者 || | 逻辑或 |
XOR | 逻辑异或 |
例如:逻辑非 NOT 或者 !
位运算符
位运算符 | 说明 |
---|---|
| | 按位或 |
& | 按位与 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
~ | 按位取反,反转所有比特 |
排序查询
1.ASC代表升序,DESC代表降序,如果不写默认升序
2.ORDER BY用于子句中可以支持单个字段,多个字段,表达式,函数,别名
3.ORDER BY子句,放在查询语句的最后面。LIMIT子句除外
SELECT
字段名1,字段名2,……
FROM表名
ORDER BY 字段名1 [ASC|DESC],字段名2[ASC|DESC]……
聚合查询
聚合函数 | 说明 |
---|---|
COUNT() | 统计指定列不为NULL的记录行数; |
SUM() | 计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0; |
MAX() | 计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算; |
MIN() | 计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算; |
AVG() | 计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0 ; |
-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的总和
select sum(price) from product where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';
分组查询
分组查询是指使用 GROUP BY字句对查询信息进行分组。
- SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段;
- 分组之后的条件筛选-HAVING
- SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING分组条件;
1.分组之后对统计结果进行筛选的话必须使用having,不能使用where
2.where子句用来筛选 FROM 子句中指定的操作所产生的行
3.group by 子句用来分组 WHERE 子句的输出。
4.having 子句用来从分组的结果中筛选行
分页查询
-- 方式1-显示前n条
select 字段1,字段2... from 表名 limit n
-- 方式2-分页显示
select 字段1,字段2... from 表名 limit m,n
m: 整数,表示从第几条索引开始,计算方式 (当前页-1)*每页显示条数
n: 整数,表示查询多少条数据
-- 查询product表的前5条记录
select * from product limit 5
-- 从第4条开始显示,显示5条
select * from product limit 3,5
Insert_into_select语句
- 将一张表的数据导入到另一张表中,可以使用INSERT INTO SELECT语句 。
- 将一张表的数据导入到另一张表中,有两种选择 SELECT INTO 和 INSERT INTO SELECT 。
正则表达式查询介绍
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置。 |
$ | 匹配输入字符串的结束位置。 |
. | 匹配除 “\n” 之外的任何单个字符。 |
[…] | 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。 |
[^…] | 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。 |
p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如,‘z|food’ 能匹配 “z” 或 “food”。‘(z|f)ood’ 则匹配 “zood” 或 “food”。 |
* | 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。 |
+ | 匹配前面的子表达式一次或多次。例如,‘zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}。 |
{n} | n 是一个非负整数。匹配确定的 n 次。例如,‘o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。 |
{n,m} | m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
-- ^ 在字符串开始处进行匹配
SELECT 'abc' REGEXP '^a'; -- 1
-- $ 在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$'; -- 0
SELECT 'abc' REGEXP 'c$'; -- 1
-- . 匹配任意字符
SELECT 'abc' REGEXP '.b'; -- 1
SELECT 'abc' REGEXP '.c'; -- 1
SELECT 'abc' REGEXP 'a.'; -- 1
-- [...] 匹配括号内的任意单个字符,后面括号中的任意字符是否在前边出现
SELECT 'abc' REGEXP '[xyz]'; -- 0
SELECT 'abc' REGEXP '[xaz]'; -- 1
-- [^...] 注意^符合只有在[]内才是取反的意思,在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]'; -- 0
SELECT 'x' REGEXP '[^abc]'; -- 1
SELECT 'abc' REGEXP '[^a]'; -- 1
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b'; -- 1
SELECT 'stb' REGEXP '.ta*b'; -- 1
SELECT '' REGEXP 'a*'; -- 1
-- a+ 匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b'; -- 1
SELECT 'stb' REGEXP '.ta+b'; -- 0
-- a? 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b'; -- 1
SELECT 'stab' REGEXP '.ta?b'; -- 1
SELECT 'staab' REGEXP '.ta?b'; -- 0
-- a1|a2 匹配a1或者a2,
SELECT 'a' REGEXP 'a|b'; -- 1
SELECT 'b' REGEXP 'a|b'; -- 1
SELECT 'b' REGEXP '^(a|b)'; -- 1
SELECT 'a' REGEXP '^(a|b)'; -- 1
SELECT 'c' REGEXP '^(a|b)'; -- 0
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c'; -- 1
SELECT 'auuuuc' REGEXP 'au{3}c'; -- 0
-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c'; -- 1
SELECT 'auuuuc' REGEXP 'au{4,5}c'; -- 1
SELECT 'auuuuc' REGEXP 'au{5,10}c'; -- 0
-- (abc) abc作为一个序列匹配,不用括号括起来都是用单个字符去匹配,如果要把多个字符作为一个整体去匹配就需要用到括号,所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y'; -- 1
SELECT 'xababy' REGEXP 'x(ab)*y'; -- 1
SELECT 'xababy' REGEXP 'x(ab){1,2}y'; -- 1
DCL–管理用户
创建用户
create user '用户名'@'IP地址' identified WITH mysql_native_password by '密码';
flush privileges;
修改用户名
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
修改密码
#切换到mysql库
use mysql;
#更新密码
UPDATE user SET password=password('新密码') WHERE user='用户名' AND host='IP地址';
#刷新权限
FLUSH PRIVILEGES;
删除用户
#注意这里的IP地址,一个用户可能会有多个
drop user '用户名'@'IP地址';
#比如
drop user 'Alian'@'192.168.0.100';
权限设置
关于 数据库名.表名 的说明:
- . 表示任意库的任意表(不建议)
- mysql.* 表示mysql库的任意表
- mysql.user 表示mysql库的user表
关于 用户名@地址 的说明(这里都是英文的单引号):
- ‘alian’@‘localhost’ :表示只允许本机登录
- ‘alian’@‘%’ :表示任意地址登录
- ‘alian’@‘192.168.0.100’:表示只允许ip为192.168.0.100的地址登录
- ‘alian’@‘192.168..’ :表示只允许ip为192.168网段的地址登录
授权:
grant 权限1, 权限2, 权限3,… ,权限n on 数据库名.表名 to 用户名@地址;
#把数据库的所有库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on *.* to 'alian'@'%';
flush privileges;
#把mysql数据库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on mysql.* to 'alian'@'%';
flush privileges;
#把mysql数据库的user表的所有权限都给alian,并且是只能通过192.138.0.10才可以操作
grant all privileges on mysql.user to 'alian'@'192.138.0.10';
flush privileges;
#把mysql数据库的user表的(查询,插入,更新,删除)的权限都给alian,并且是任意ip地址都可以操作
grant SELECT, INSERT, UPDATE, DELETE on mysql.user to 'alian'@'%';
flush privileges;
查看权限:
show grants for 'alian'@'%';
回收权限:
revoke 权限1, 权限2…权限n on 数据库名.表名 from 用户名@地址;
#回收用户的更新和删除mysql(默认的库)数据库的权限
revoke update,delete on mysql.user from 'alian'@'%';
#交互模式设置语言并进入mysql容器(419413b9d276 是mysql的容器id)
docker exec -it 419413b9d276 env LANG=C.UTF-8 /bin/bash