MySQL之SQL语句+严格模式

合集 - MySQL数据库炒年糕(16)

1.MySQL之初识数据库2024-03-032.MySQL之MySQL数据库安装2024-03-03

3.MySQL之SQL语句+严格模式2024-03-03

4.MySQL之存储引擎2024-03-035.MySQL之基本数据类型2024-03-036.MySQL之约束条件2024-03-037.MySQL之外键介绍2024-03-038.MySQL之修改表和复制表2024-03-039.MySQL之筛选和过滤条件2024-03-0310.MySQL之多表操作2024-03-0311.MySQL之pymysql模块2024-03-0312.MySQL之视图、触发器、存储过程、函数和流程控制2024-03-0313.MySQL之事务2024-03-0414.MySQL之索引2024-03-0415.MySQL补充：数据库的三大范式2024-03-1816.补充 windows系统安装多个MySQL版本2024-04-11

收起

SQL使用规范

在数据库管理系统中，SQL语句关键字不区分大小写(但建议用大写) ，参数区分大小写。

• 建议命令大写，数据库名、数据表名、字段名统一小写，如数据库名、数据表名、字段名与关键字同名，使用反引号圈起来，避免冲突。

SQL语句可单行或多行书写，默认以英文分号（;）结尾，关键词不能跨多行或简写。

字符串跟日期类型的值都要以 单引号括起来，单词之间需要使用半角的空格隔开。

用空格和缩进来提高SQL语句的可读性。

注释语法

（1）单行注释

采用 "--"(双减号)进行单行注释
注意："--"与注释内容要用空格隔开才会生效

（2）多行注释

采用 
/*
...
*/
进行多行注释

（3）(单行)注释

在mysql数据库中就可使用"#"进行单行注释。
 "#"与注释内容之间有没有空格注释效果都会生效。
 
除此之外，数据库可视化管理工具SQLyog如果连接的是mysql数据库，而mysql数据库支持SQL，那么mysql就支持使用SQL标准注释和"#"注释，那么在SQLyog中也就可以使用SQL标准注释和"#"注释。

SQL类型

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为4种类型：

DDL数据定义语言

DDL（Data Definition Language）是数据定义语言,主要是对数据库和数据表的操作。

（1）操作库（文件夹）

-- 创建库
create database 库名;
-- 创建库时判断库是否存在，不存在则创建
create database if not exists 库名;
-- 查看所有数据库
show databases;
-- 使用指定数据库
use 库名;
-- 查看当前指定数据库包含的数据表
show tables;
-- 查看数据库的结构定义信息
show create database 库名;
-- 删除数据库
drop database 库名;
-- 修改数据库的字符集为utf8
alter database 库名 character set utf8;
-- 查看当前使用的数据库名字
select database(); 

（2）操作表（文件）

-- 创建表
create table 表名(
字段1 类型1,
字段2 类型2,
字段3 类型3,
...........
);
例如：
create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);
 
 
-- 查看表结构
desc 表名;
-- 查看创建表的SQL语句
show create table 表名;
-- 修改表名
alter table 表名 rename to 新的表名;
-- 添加一个新的字段
alter table 表名 add 字段; 字段类型;
-- 修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
-- 修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
-- 删除一个字段
alter table 表名 drop 字段名;
-- 删除表
drop table 表名;
-- 删除表时判断表是否存在,若存在则删除
drop table if exists 表名;
-- 重置表信息
truncate table 表名;

# 补充
1. 修改表名
	alter table 表名 rename 新表名
2. 增加字段
	alter table 表名 add 字段名 字段类型(宽度) 约束条件;
	# 将字段直接添加在标的最前面
	alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
	# 将字段指定跟在谁的后面
	alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名
3. 删除字段
	alter table 表名 drop 字段名;
4. 修改字段
	alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
	alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

DML数据操作语言

DML（Data Manipulation Language）是数据操作语言,主要是对数据表的操作。

（1）插入数据INSERT INTO

-- 写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
-- 不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);
-- 插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);

（2）删除数据DELETE

-- 删除表中指定的数据
delete from 表名 where 列名  = 值;
delete from t1 where id > 1;
delete from t1 where name='dream';
-- 删除表中所有数据
delete from 表名;
-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

（3）更新数据UPDATE

-- 不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;
-- 带条件的修改
update 表名 set 列名 = 值 where 条件;

DQL数据查询语言

DQL（Data Query Language）是数据查询语言，主要就是select配合其他限制条件的关键字进行查询

（1）无条件查询

-- 查询表中所有数据
select * from 表名;

（2）查询在...到...之间(between and / && / and)

-- 查询users表中年龄在18~25岁之间的记录
-- 方式1 between..and..
select * from users where age between 18 and 25;
-- 方式2 &&
select * from users where age>=18 && age<=25;
-- 方式3 and
select * from users where age>=18 and age<=25;

（3）指定条件查询

①单个条件(or / in)

-- 查询users表中年龄为18,20,25岁的记录
-- 方式1 or
select * from users where age=18 or age=20 or age=25;
-- 方式2 in
select * from users where age in(18,20,25);

②多个条件(and)

-- 查询users表中年龄为23，性别为女，名字为小楠的记录
select * from users where age=23 and gender='女' and name='小楠'; 

（4）查询不为NULL值(is not null),为NULL值(is null)

-- 查询users表中序号不为空的记录
select * from users where id is not null;
-- 查询user表中序号为空的记录
select * from users where id is null;

（5）模糊查询（like）

_: 单个任意字符
%: 多个任意个字符
-- 查询users表中姓名第一个字为李的记录
select * from users where name like '李%';
-- 查询users表中姓名第二个字为李的记录
select * from users where name like '_李%';
-- 查询users表中姓名含有李字的记录
select * from users where name like '%李%';
-- 查询users表中姓名是两个字的记录
select * from users where name like '__';

（6）去除重复记录查询（distinct）

-- 查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;
select distinct city from users;

（7）排序查询（order by）

①单个条件

-- 查询users表中记录，并以年龄升序排序
select * from users order by age; --默认升序
 
-- 查询users表中记录，并以年龄降序排序
select * from users order by age desc;--desc降序

②多个条件

注意：多个排序条件时，只有当第一个排序条件值一样，才会执行第二个排序条件，以此类推。

-- 查询users表中记录，并体育成绩降序，年龄降序
select * from users order by PE desc,age desc;

（8）聚合函数

①计算和（sum）

select sum(字段) (as sumvalue) from 表名;

②计算最大值（max）

select max(字段) (as maxvalue) from 表名;

③计算最小值（min）

select min(字段) (as minvalue) from 表名;

④计算平均值（avg）

select avg(字段) (as avgvalue) from 表名;

⑤计算个数（count）

select count(字段) (as totalcount) from 表名;

（9）分组查询（group by）

-- 查询users表中的记录，按照性别分组，查询男，女的体育成绩平均分
select gender,avg(PE) from users group by gender;
-- 查询users表中的记录，按照性别分组,分别查询男、女的体育成绩平均分,人数
select gender, avg(PE),count(id) from users group by gender;
-- 查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组
select gender, avg(PE),count(id) from users where PE > 60 group by gender; 
-- 查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE > 60 group by gender having count(id)>2;

（10）分页查询（limit）

注意：第一条记录的索引是0

-- 查询users表中的前10行条记录
select *from users limit 10;
-- 查询users表中第2~11条记录 (从第2条记录开始累加10条记录)
select *from users limit 1,10;
-- 查询users表中第5~17条记录 (从第5条记录开始累加13条记录)
select *from users limit 4,13;

（11）内连接查询

如果查询数据的来源来自多张表，则必须对这些表进行连接查询，连接是把不同表的记录连到一起的最普遍的方法，通过连接查询可将多个表作为一个表进行处理，连接查询分为内连接和外连接

语法格式

-- 语法1 （隐式内连接）
select 字段1,字段2...
from 表1,表2...
where 过滤条件;
 
-- 语法2 （显式内连接）
select 字段1,字段2...
from 表1 inner join 表2 ...
on 过滤条件;
 
 1. 从哪些表中查询数据
 2.条件是什么
 3. 查询哪些字段

例如这里有两张表: user表和city表

重合的部分就叫做内连接查询,例如下面过滤条件指的就是当两个表的id相等时才符合连接查询的条件

隐式内连接

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;
 
SELECT 
    t1.name, -- 员工表的姓名
    t1.gender,-- 员工表的性别
    t2.name -- 部门表的名称
FROM
    emp t1,
    dept t2
WHERE 
    t1.dept_id = t2.id;

显式内连接

-- 语法： 
select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 例如：
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;   
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id; 

（12）外连接查询

外连接查询分为左外连接查询和右外连接查询

语法

-- 左外连接
select 字段1,字段2..
from 表1 left (outer) join 表2 on 过滤条件;
-- 右外连接
select 字段1,字段2..
from 表1 right (outer) join 表2 on 过滤条件;

区别如下

左外连接：是表1和表2的交集再并上表1的其他数据

右外连接：是表1和表2的交集再并上表2的其他数据

举个例子，例如还是两张表，user表和city表

左外连接

-- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT  t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;

右外连接

-- 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；
-- 例子：
SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

看到结果大家明白左，右外连接的区别了吗，在实际应用中，我们要结合实际的需求来决定使用左外连接还是右外连接

（13）子查询

子查询:其实就是查询语句中嵌套查询语句

子查询的查询结果是可以作为判断条件的

如果子查询结果是单行单列的，可以用运算符做判断条件：< <= > >= =

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

如果子查询结果是多行单列的，可以用运算符in做判断条件

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
 
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

如果子查询结果是多行单列的，可以用子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
 
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date >  '2011-11-11'

DCL数据库控制语言：

DCL（Data Control Language）是数据库控制语言，主要控制用户的访问权限例如COMMIT、ROLLBACK、GRANT、REVOKE

（1）管理用户

①添加用户

create user '用户名'@'主机名' identified by '密码';

②删除用户

drop user '用户名'@'主机名';

（2）权限管理

①查询权限

show grants for '用户名'@'主机名';

②授予权限

-- 语法
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 授予faker用户所有权限，在任意数据库任意表上
grant all on *.* to 'faker'@'localhost';

③撤销权限

-- 语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-- 撤销faker用户对test数据库中city数据表的权限
revoke update on test.city from 'faker'@'localhost';

清空表：
    delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
    truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，
    auto_increment 表示：自增
       primary key 表示：约束（不能重复且不能为空）；加速查找

严格模式

# 如何查看严格模式
show variables like "%mode%";
 
模糊匹配/查询
	关键字 like
		%: 匹配任意多个字符
		_: 匹配任意单个字符
		
# 修改严格模式sql_mode
	set session  只在当前窗口有效
	set global   全局有效
-- 语法
set global sql_mode = 'STRICT_TRANS_TABLES';
修改完成后，重启服务端，即可生效
 
# 5.6版本默认没有开启严格模式，规定只能存一个字符，你给了多个字符，那么我将会自动帮你截取
# 5.7版本及以上版本默认自动开启了严格模式，那么固定只能存几个，就不能超出字符宽度，一旦超出范围，就会立刻报错 ERROR 1406 (22001): Data too long for column 'name' at row 1
 
使用数据库的准则：
能尽量少的让数据库干活就尽量少，不要给数据库增加额外的压力

参考：

https://blog.csdn.net/promsing/article/details/112793260

https://blog.csdn.net/m0_71741835/article/details/127624612