sql、sql审计

一、sql语言

1、DDL (Data Definition Language) 数据库定义语言

查看帮助

?|help + 类别名称

如 help create table

mysql> ? data types                #查看数据有哪些类型,如：
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL

mysql> help  int;                    #关于数据类型的说明

\s 或者 \status 查看数据库的状态信息

可以获取到更详细的服务器状态报告，包括服务器配置、客户端连接信息、缓冲池使用情况、打开的表数量、查询缓存、查询的次数等等。这对于监视和了解服务器的当前状态非常有用。

mysql> \s
--------------
mysql  Ver 8.0.32 for macos13 on arm64 (MySQL Community Server - GPL)

Connection id:		17
Current database:	oldboy
Current user:		root@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.32 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql.sock
Binary data as:		Hexadecimal
Uptime:			27 min 45 sec

Threads: 6  Questions: 245  Slow queries: 0  Opens: 370  Flush tables: 3  Open tables: 291  Queries per second avg: 0.147
--------------

操作库

-- 创建库
create database db1;

-- 创建库是否存在，不存在则创建
create database  if not exists db1;

-- 查看所有数据库
show databases;

-- 查看某个数据库的定义信息 
show create database db1; 

-- 修改数据库字符信息
alter database db1 character  set utf8; 

-- 删除数据库
drop database db1;

操作表

--创建表
CREATE TABLE student1 (
id INT,
name VARCHAR(32),
age INT,
score DOUBLE(4, 1),
insert_time TIMESTAMP
);

# double(4,1) 是一种数据类型定义，表示一个双精度浮点数（double），具有总长度为 4 个字节的存储空间，并且其中有 1 个小数位。

-- 插入数据
INSERT INTO student1 (id, name, age, score, insert_time)
VALUES
(1, 'Alice', 20, 89.5, NOW()),
(2, 'Bob', 22, 92.3, NOW()),
(3, 'Charlie', 21, 87.1, NOW());

-- 查看表结构
desc 表名;

-- 查看创建表的SQL语句
show create table 表名;
show create table student;
| student | CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(2) NOT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 修改表名
alter table 表名 rename to新的表名;

-- 添加一列
alter table 表名 add 列名 数据类型;

-- 删除列（注意区分删除某一列的一行记录）
alter table 表名 drop 列名;

-- 删除表
drop table 表名;
drop table if exists 表名

2、DML(Data Manipulation Language) 数据库操作语言

增加 insert into

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

删除 delete

-- 删除表中数据
delete  from 表名 where 列名 = 值;

-- 删除表中所有数据
delete  from 表名;

-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate  table 表名;

修改 update

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

-- 带条件的修改
updata 表名 set 列名 = 值 where 列名 = 值;

3、DQL (Data Query Language) 数据库查询语言

查询语句有很多，主要介绍排序查询、聚合函数、模糊查询、分组查询、分页查询、内连接、外连接、子查询

基础关键字：

BETWEEN...AND（在什么之间）和 IN( 集合)

-- 查询年龄大于等于20 小于等于30                
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;             

-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

is null(不为null值) 与 like（模糊查询）、distinct（去除重复值）

-- 查询英语成绩不为null
SELECT* FROM student WHERE english  IS NOT NULL;

 _:单个任意字符
 %：多个任意字符
-- 查询姓马的有哪些？like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的人            
SELECT * FROM student WHERE NAME LIKE "_化%";                
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';                    
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
-- 关键词 DISTINCT 用于返回唯一不同的值。
-- 语法：SELECT DISTINCT 列名称 FROM 表名称
SELECT DISTINCT NAME FROM  student ;

排序查询 order by
语法：order by 子句

　　order by 排序字段1 排序方式1 ，排序字段2 排序方式2...

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

SELECT *FROM person ORDER BY math; --默认升序
SELECT * FROM person ORDER BY math desc; --降序

聚合函数：将一列数据作为一个整体，进行纵向的计算。

count：计算个数
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均数

分组查询 grout by
语法：group by 分组字段;
注意：分组之后查询的字段：分组字段、聚合函数

-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
 
-- 按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
 
--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
 
 --  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

分页查询：
语法：limit 开始的索引，每页查询的条数;
公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
limit 是一个MySQL"方言"

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页

SELECT * FROM student LIMIT 3,3; -- 第2页

SELECT * FROM student LIMIT 6,3; -- 第3页

内连接查询：

从哪些表中查询数据
条件是什么
查询哪些字段

1.隐式内连接：使用where条件消除无用数据

-- 查询员工表的名称，性别。部门表的名称
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`;

2.显式内连接

-- 语法：
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`;

外连接查询
1.左外连接 -- 查询的是左表所有数据以及其交集部分。

-- 语法：
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`;

2.右外连接 -- 查询的是右表所有数据以及其交集部分。

-- 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；

SELECT  * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

子查询：查询中嵌套查询

-- 查询工资最高的员工信息
-- 1 查询最高的工资是多少 9000
SELECT MAX (salary) FROM emp;

-- 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.`salary` = 9000;

-- 一条sql就完成这个操作。这就是子查询
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

1.子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =

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

2. 子查询的结果是多行单列的：

子查询可以作为条件，使用运算符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 = '市场部');

3. 子查询的结果是多行多列的：
子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是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'

4、DCL(Data Control Language) 数据库控制语言

管理用户

添加用户

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

删除用户

语法：DROP USER '用户名'@'主机名';

权限管理

查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

授予权限

-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; 
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限

-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';。

5、TCL(Transaction Control Language) 事务控制语言

二、实例

1、create创库创表，desc查询表结构

>create table department1(id int auto_increment primary key,name varchar(40) not null,dept_sfz int unique);

2、alter配合modify 和 change（modify只能修改属性，change用来修改列名）

> alter table department1 change name dept_name varchar(20) not null;

> alter table student10 modify id int not null;(改变id的自增)

> alter table department1 modify dept_name varchar(10)

 > alter table department1 add dept_sex varchar(10) default "m";

3、 Insert into 插数据

 > insert into department1 (id,name,dept_sfz) values
    -> (2,'we',23),
    -> (4,'ty',56);

4、Drop 删列、删表 delete删除记录 truncate会删除表中所有记录

> alter table department1 drop dept_sex;（删列）

> drop table department1;（删表）

> delete from user where Host='172.24.46.%';（删记录）

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

5、Rename用于重命名对象

> alter table users_user rename users_user01;

6、授权（授权的同时也新建了用户）

> grant all privileges on  *.* to 'userdb'@'localhost' identified by '密码';
> grant select,insert,update,delete on jumpserver.* to zjz@'192.168.0.%';

> update user set host='%' where user='root';（也能达到授权的效果）

> FLUSH PRIVILEGES

7、 Revoke 取消授权

> revoke all on *.* from zjz@localhost;

8、查看用户权限

> show grants\G;(查看当前用户自己的权限）

> show grants for zjz@'localhost';（查看他人权限）

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
......省略
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> desc user;（查看user表的表结构）
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |

.....省略

| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql> select Host,User,authentication_string  from user;（只查询其中三项）
+-----------+---------------+-------------------------------------------+
| Host      | User          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *F14917E81FAE96E012F66F66C219841437552773 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql_monitor | *5C01CCC1CBAF86AAF36B9C35AF94E326204B13F2 |
| %         | grafana       | *702F9ED0D2BE8929F53D852D6D6D03657760940A |
| localhost | admin         | *CC3C9B71332E123B33EF5EF3D816857B5A3EDCE0 |
+-----------+---------------+-------------------------------------------+
6 rows in set (0.01 sec)

9.查看当前的登录用户和数据库

mysql> select user();（等价于mysql> SELECT current_user;）       mysql> select database();
+----------------+                                               +------------+        
| user()         |                                               | database() |
+----------------+                                               +------------+             
| root@localhost |                                               | mysql      |
+----------------+                                               +------------+    
1 row in set (0.01 sec)                                          1 row in set (0.00 sec)

10.数据的导入导出

导出数据库

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p abc > abc.sql
敲回车后会提示输入密码

只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p -d abc > abc.sql

导入数据库
首先建空数据库
mysql>create database abc;

导入数据库
方法一：
（1）选择数据库
mysql>use abc;
（2）设置数据库编码
mysql>set names utf8;
（3）导入数据（注意sql文件的路径）
mysql>source /home/abc/abc.sql;
方法二：
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql

https://www.cnblogs.com/lonmyblog/p/9235136.html

11、设置中文字符集

character-set-server = utf8
#定义服务端所使用的字符集为UTF8

init_connect = SET NAMES utf8
#定义client与server之间传递字符的编码规则为utf8
亲测有效（my.cnf里面改）

https://www.cnblogs.com/lvthinks/p/12502006.html  （有关mysql5.7的配置文件解释）