数据查询语言(DQL)

SELECT

模糊查询

使用like关键字进行模糊匹配查询

# 查询员工表中姓第一个字符为D的员工信息
SELECT * FROM employees WHERE last_name like 'D%'

Like模糊查询:where <> like <>

通配符：
  %：表示任意字符，可以匹配任意长度的字符串。
  _：表示单个字符，可以匹配任意单个字符。

子查询

将一个查询嵌套在另一个查询语句中实现子查询

select * from student where id in (select student_id from record where student_id<=3) 

匹配关键字：
  IN 
  NOT IN
  EXISTS  
  NOT EXISTS

  • 子查询指将一个查询语句嵌套在另一个查询语句中，

  • 可以在 SELECT、UPDATE 和 DELETE ，where子句中使用。

  • 子查询必须放在圆括号内()。

  • MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

多表连接

使用join关键字实现多表连接查询

select <表A>.<字段>,<表B>.<字段> from A <left|right> join B on <表A>.<字段>=<表B>.<字段>（匹配字段）

JOIN：用于连接多个数据表，可以指定连接类型和连接条件。
  left join（左外连接）:选择左表中的所有数据 和右表中匹配的数据没有的用NULL填充
  right join（右外连接）:选择右表中的所有数据 和左表中匹配的数据没有的用NULL填充
  inner join（内连接）:得到A和B的交集。
  CROSS JOIN（交叉连接）:查询多个表。笛卡尔积

排序查询
# 根据员工工资进行排序
SELECT * FROM employees  ORDER BY salary

ORDER BY：用于对查询结果进行排序，可以指定排序字段和排序方式。
 ASC（默认） 表示升序排序，DESC 表示降序排序。
# 根据员工工资进行降序排序并查看前5名
SELECT * FROM employees ORDER BY salary DESC  LIMIT 5

HAVING：用于对分组后的结果进行筛选，可以使用比较运算符和聚合函数等。

分组查询
GROUP BY：用于对查询结果进行分组，可以指定分组字段。
 select {} group by <>,<>

HAVING：用于对分组后的结果进行筛选，可以使用比较运算符和聚合函数等。

# 查询各个班级的学生人数，并只显示人数大于5人的记录
SELECT class,count(class) FROM students GROUP BY class HAVING count(class) > 5

查询结果记录限制

limit根据限制值限制返回的记录数。

# 返回工资大于50000的前三名员工
SELECT * FROM employees  WHERE salary > 50000 ORDER BY salary LIMIT 3

# offset 关键字允许您偏移 LIMIT限制
# 此查询返回工资大于50000的前三名员工，但略过第一名从第二个开始记录
SELECT * FROM employees  WHERE salary > 50000 ORDER BY salary LIMIT 3 OFFSET 1

查询结果插入新表
create table if not exists user (
  select id,name from player where name like 'tom%'

)

SELECT 查询执行顺序

查询关键字有：

SELECT：查询

FROM：选择数据表

WHERE：条件查询

LIKE：模糊查询

ORDER BY：查询排序

GROUP BY：分组查询

HAVING：分组结果筛选

LIMIT：查询结果限制

JOIN：多表连接

执行顺序为

FROM-WHERE-(LIKE)-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT

索引

CREATE INDEX ：用于创建索引，提高查询效率。
     示例：CREATE INDEX 索引名 ON 表名 (列名);
         MySQL一些特殊的字段类型，如BLOB、TEXT等，是不支持直接创建索引的。
 
 
 #可在创建表时定义索引
 create table book (
   bookid int not null,
   bookname varchar(255) not null,                     
   authors varchar(255) not null,
   info varchar(255) null,
   comment varchar(255) null,
   year_publication year not null,
   index(year_publication)

   unique、fulltext、spatial 为可选参数，分别表示唯一索引、全文索引、空间索引；


   # 删除索引
   DROP INDEX index_name ON tbl_name

聚合函数

常用函数
SUM()：求总和，只适用于数值类型字段，如果是字符串类型不会报错会返回0，会自动过滤空值

AVG()：求平均值，只适用于数值类型字段，字符串类型不会报错会返回0，会自动过滤空值

MAX()：求最大值，适用于数值类型、字符串类型和日期时间类型字段

MIN()：求最小值，适用于数值类型、字符串类型和日期时间类型字段

COUNT()：用于计算查询结果集中的数据共有多少条

MySQL中聚合函数是不能嵌套使用的

数学函数

函数声明

功能描述

ABS(x)

返回x的绝对值

SQRT(x)

返回x的非负2次方根

MOD(x,y)

返回x被y除后的余数

CEILING(x)

返回不小于x的最小整数

FLOOR(x)

返回不大于x的最大整数

ROUND(x,y)

对x进行四舍五入操作，小数点后保留y位

TRUNCATE(x,y)

舍去x中小数点y位后面的的数

SIGN(x)

返回x的符号，x的值为负数、零和正数时依次返回 -1、0和1

字符串函数

函数声明

功能描述

LENGTH(str)

返回字符串str的长度

CONCAT(s1,s2,…)

返回一个或者多个字符串连接产生的新的字符串

TRIM(str)

删除字符串两侧的空格

REPLACE(str,s1,s2)

使用字符串s2替换字符串str中所有的字符串s1

SUBSTRING(str,n,len)

返回字符串str的子串，起始位置为n，长度为len

REVERSE(str)

返回字符串反转后的结果

LOCATE(s1,str)

返回子串s1在字符串str中的起始位置

条件判断函数

函数声明

功能描述

IF(expr,v1,v2)

如果expr表达式为true返回v1，否则返回v2

IFNULL(v1,v2)

如果v1不为NULL返回v1，否则返回v2

数据操作语句（DML）

INSERT ( 添加数据语句 )

INSERT INTO语句用于在表中插入新记录

  • 表的字段是可选的，如果省略，则按顺序依次插入所有的字段。

  • 如果插入的是表中部分列的数据，字段名列表必须填写。

  • 多个字段和多个值之间使用逗号隔开。

  • 值列表必须和字段名列表数量相同且数据类型相符（字符串和日期类型的值要加单引号）。

  • 值列表中的数据必须符合数据完整性的要求。

单行添加
# 单行添加添加数据
INSERT INTO  表名 ( 字段1, 字段2, 字段3, … )
VALUES  ( 值1, 值2, 值3, …);

# 添加全部字段
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

多行添加
# 多行添加数据
INSERT INTO  表名  [( 字段1, 字段2, 字段3, … )  
VALUES  ( 值1, 值2, 值3, …),
( 值1, 值2, 值3, …);

每行数据之间用逗号分隔，最后一行数据使用分号

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');

UPDATE ( 更新数据语句 )

UPDATE语句用于修改表中现有的记录。

WHERE子句指定应更新哪些记录。如果省略该WHERE子句，则将更新表中的所有记录！

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

1.value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果。
2.多个字段之间用逗号隔开。
3.提供的修改数据必须符合数据完整性的要求。
4.condition为筛选条件，如不指定则修改该表的所有列数据。

DELETE ( 删除数据语句 )

DELETE语句用于删除表中现有的记录。

WHERE子句指定应删除哪些记录。如果省略该WHERE子句，则将删除表中的所有记录！

DELETE FROM table_name WHERE condition;

1.DELETE语句删除的是整条记录，不会只删除单个列。
2、condition为筛选条件，如不指定则修改该表的所有列数据

TRUNCATE

使用TRUNCATE语句删除表中记录后，将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句快

TRUNCATE TABLE table_name;

数据定义语言(DDL)

CREATE 创建

CREATE DATABASE语句用于创建一个新的 SQL 数据库

CREATE TABLE语句用于在数据库中创建新表。

创建数据库

  • 在创建任何数据库之前，请确保您具有管理员权限。

  • 创建数据库后，您可以使用以下 SQL 命令在数据库列表中检查它：SHOW DATABASES

CREATE DATABASE databasename;
#我们可以添加一个判断条件，当此数据库不存在时就创建，否则不创建
create database if not exists 数据库名;

#创建数据库，并且指定字符集
create database 数据库名称 character set 字符集名;

创建表

  • 创建表时必须带字段列表，可以只有一个字段，但不能没有，多个字段用逗号隔开

CREATE TABLE table_name(
   column1 datatype constraint Notes,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

table_nane : 表名
column : 字段名
datatype : 字段类型

constraint ：字段约束
Notes ：字段注释

PRIMARY KEY ：主键约束
#我们可以添加一个判断条件，当此表不存在时就创建，否则不创建
create table if not exists table_name({})comment = "表注释";

临时表

临时表只在当前会话中存在，会话结束后自动销毁。

CREATE TEMPORARY TABLE

从现有表
CREATE TABLE NEW_TABLE_NAME AS
SELECT [column1, column2...columnN]
FROM EXISTING_TABLE_NAME
WHERE Condition;

DROP 删除

DROP DATABASE语句用于删除现有的 SQL 数据库。

DROP TABLE语句用于删除数据库中现有的表。

删除数据库
drop database databasename;

# 可以添加一个判断条件，当此数据库存在时就删除，否则不删除
drop database if exists databasename;

删除表
drop table table_name;

#我们可以添加一个判断条件，当此表存在时就删除，否则不删除
drop table if exists table_name;

ALTER 修改

ALTER TABLE语句用于添加、删除或修改现有表中的列。

ALTER TABLE语句还用于在现有表上添加和删除各种约束。

添加表字段
ALTER TABLE table_name
ADD column_name datatype;

删除表字段
ALTER TABLE table_name
DROP COLUMN column_name;

修改表字段
# 重命名
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
# 修改字段名、字段的数据类型以及字段约束
alter table 表名 change  原字段名 新字段名 类型 约束;
#如只需修改类型或约束不需改名可写两个相同字段名（不可省略）

#使用modify：修改字段数据类型和字段约束
alter table modify [COLUMN] 字段名 类型 约束;

添加约束
添加主键约束
alter table 表名 add <constraint 主键名> primary key (主键字段名);
ALTER table player add  PRIMARY KEY (id)
 
 #删除主键约束
 alter table player drop  primary key 
 
 
# 添加外键约束
 alter table 外键表名  
 add constaint <外键名> foreign key (外键字段名) 
 references 主键表名(主键字段名);
     
 
 #设置字段初始值
 ALTER TABLE 表名 ALTER <COLUMN> 字段名 SET DEFAULT 新的初始值;

添加索引
# 添加索引与删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 ADD INDEX 索引名 (字段名);

重命名数据库
ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

数据控制语句（DCL）

GRANT用户授权
GRANT用于授权给用户或用户组访问数据库对象的权限。
     GRANT permission ON object TO user;
 
 #permission表示授权的权限，可以是SELECT、INSERT、UPDATE、DELETE等；object表示授权的数据库对  象，可以是表、视图、存储过程等；user表示被授权的用户或用户组。
 
 授权用户SELECT权限：
 GRANT SELECT ON table_name TO user_name;
 
 授权用户INSERT、UPDATE、DELETE权限：
 GRANT INSERT, UPDATE, DELETE ON table_name TO user_name;
 
 授权用户所有权限：
 GRANT ALL PRIVILEGES ON table_name TO user_name;

REVOKE
REVOKE用于撤销用户或用户组访问数据库对象的权限。
     REVOKE permission ON object FROM user;
 
 #permission表示要撤销的权限，可以是SELECT、INSERT、UPDATE、DELETE等；object表示要撤销权限的数据库对象，可以是表、视图、存储过程等；user表示被撤销权限的用户或用户组。

DENY
DENY关键字用于限制用户或角色对某些数据库对象的访问权限
     DENY permission [, permission] ON object TO {user | role | PUBLIC} [, {user | role | PUBLIC}] [WITH GRANT OPTION]
 
 #permission表示要限制的权限，可以是SELECT、INSERT、UPDATE、DELETE等；object表示要限制访问的对象，可以是表、视图、存储过程等；user或role表示要限制的用户或角色，PUBLIC表示所有用户或角色；WITH GRANT OPTION表示允许被授权的用户或角色再次授权。

其他SQL语句

SHOW
1.查看数据库列表
 show databases
 
 2.查看表列表
 show tables
 
 3.查看表结构
 SHOW CREATE TABLE tbl_name
 
 4.查看可用字符集
 SHOW CHARACTER SET;
 
 5.查看数据库创建信息
 show create database 数据库名；

First Header

Second Header

show status

显示服务器运行状态信息（如正在运行的线程数量）

show character set

显示服务器支持的所有字符集

show collation

显示服务器支持的所有校对规则

show warnings

显示最后一个执行的语句的告警信息（包括：错误、警告和注意信息）

show errors

显示最后一个执行的语句的错误信息

show processlist

显示系统正在运行的所有线程信息

show grants [for user_name]

显示用户权限

how privileges

显示服务器支持的权限列表

show binary logs

显示二进制列表信息

show master logs

显示主库的二进制列表信息（单体数据库中与show binary logs相同）

show [global | session] variables

显示全局（会话）系统变量信息，默认会话

show master status

显示主服务器的状态信息

show slave status

显示从服务器的状态信息

show slave hosts

显示从服务器列表信息

show profiles

显示最近15条SQL性能分析信息

show profile [参数1,参数2] for query [Query_ID]

显示指定Query_ID的SQL性能分析详情

show engines

显示服务器支持的存储引擎列表

show engine engine_name status

显示存储引擎状态

show engine engine_name mutex

显示存储引擎互斥参数

show databases

显示所有数据库名称

show create database db_name

显示数据库的构建语句

show tables [from db_name]

显示数据库中所有表名称

show create table table_name

显示表的构建语句

show triggers [from db_name]

显示指定数据库中的所有触发器信息

show events [from db_name]

显示指定数据库中的所有事件信息

show create trigger trig_name

显示创建触发器语句

show create event event_name

显示创建事件语句

show create function func_name

显示创建函数语句

show create procedure proc_name

显示创建存储函数语句

show create user user_name

显示创建用户语句

show create view view_name

显示创建视图语句

USE
#使用数据库
use 数据库名；

DESC
#查看表结构
 desc 表名;
 
 #查看表结构
 show COLUMNS from 表名

TRUNCATE
#清空表（清空表中的所有数据，但是表结构保留。)
 TRUNCATE TABLE 表名;

RENAME 重命名

RENAME TABLE语句更改 MySQL 表名

RENAME TABLE table_name TO new_table_name;

备份与恢复

BACKUP 备份

BACKUP DATABASE 语句用于创建数据库备份

BACKUP DATABASE database_name
TO DISK = 'filepath'
GO

RESTORE 恢复

RESTORE DATABASE恢复数据库中的备份文件

RESTORE DATABASE database_name
FROM DISK = 'filepath';
GO