参考文章1:https://www.bilibili.com/video/BV1Kr4y1i7ru/
参考文章2:https://dhc.pythonanywhere.com/article/public/1/
文章目录
- 基础篇
- 数据库相关概念
- (数据库DataBase(DB)、数据库管理系统DataBase Management System(DBMS)、结构化查询语言SQL(Structured Query Language))
- 主流的关系型数据库管理系统(SQL统一语言标准)
- mysql安装及卸载
- 安装
- 卸载
- 数据模型
- 关系型数据库RDBMS(建立在关系模型基础上,由多张相互连接的二维表组成的数据库)
- SQL通用语法及分类
- 通用语法
- DDL(Data Definition Language): 数据定义语言,用来定义数据库对象(数据库,表,字段)
- 数据库操作
- 查询所有数据库:show databases
- 查询当前数据库:select
- 创建数据库:create
- 删除数据库:drop
- 使用数据库:use
- 注意事项
- 表操作
- mysql数据类型
- 数值类型
- 字符串类型
- 日期时间类型
- 查询当前数据库所有表:show tables
- 查询表结构:desc
- 查询指定表的建表语句:show create table(可展示表的详细信息,包括创建时的comment)
- 创建表:create table
- 添加字段:`ALTER TABLE ttt ADD fff ttt COMMENT 'xxx';`。MySQL中的字段可以在创建表时创建,也可以在创建表后单独添加
- 修改字段数据类型(`ALTER TABLE 表名 MODIFY`)(主要用于修改列的数据类型或更改默认值等。它只能用来修改已经存在的列的属性,不能修改列的名称)
- 修改字段名和字段数据类型(`ALTER TABLE 表名 CHANGE`)(不仅可以修改列的数据类型或更改默认值,还可以更改列的名称)
- 删除字段(`ALTER TABLE 表名 DROP 字段名;`)
- 修改表名(`ALTER TABLE 表名 RENAME TO 新表名`)
- 删除表(`DROP TABLE [IF EXISTS] 表名;`)
- 删除表,并重新创建该表(`TRUNCATE TABLE 表名`)(可用于清理表数据)
- DML(Data Manipulation Language): 数据操作语言,用来对数据库`表中`的数据进行增删改
- 添加数据`INSERT`
- 指定字段:`INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);`
- 全部字段:`INSERT INTO 表名 VALUES (值1, 值2, ...);`
- 批量添加数据
- 注意事项
- 更新数据(修改数据):`UPDATE`
- `UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];`(注意:如果不带where条件修改的是整张表的数据)
- 删除(某条)数据:`DELETE`
- `DELETE FROM 表名 [ WHERE 条件 ];`(不带where条件将会删除整张表数据)
- DQL(Data Query Language): 数据查询语言,用来查询数据库中表的记录
- 基础查询
- 查询指定(单个或多个)字段:`SELECT 字段1, 字段2, 字段3, ... FROM 表名;`
- 查询所有字段:``SELECT * FROM 表名;``(建议开发中不要直接写`*`)
- 设置别名:`SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;`或者`SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;`
- 去除重复记录:`SELECT DISTINCT 字段列表 FROM 表名;`
- 转义:`ESCAPE`关键字定义转义字符
- 条件查询:`WHERE`关键字
- 语法
- 条件运算符
- 逻辑运算符
- 例子
- 聚合查询(聚合函数)(count、max、min、avg、sum)(将一列数据作为一个整体,进行纵向计算 )
- 常见聚合函数
- 语法
- 注意:所有的null值不参与聚合函数计算
- 示例
- 分组查询:`GROUP BY`关键字、`HAVING`关键字(可用`AS`关键字设置别名)
- 语法
- where 和 having 的区别
- 例子
- 注意事项
- 排序查询:ORDER BY关键字、ASC: 升序(默认)、DESC: 降序
- 语法
- 排序方式
- 例子
- 注意事项
- 分页查询:LIMIT关键字
- 语法
- 例子
- 注意事项
- DQL查询语句小结练习
- `DQL`语句编写顺序和执行顺序
- `DQL`编写顺序:`SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT`
- DQL执行顺序:`FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT`
- 示例:验证DQL执行顺序
- DCL(Data Control Language): 数据控制语言,用来管理数据库用户、控制数据库的访问权限
- 管理用户
- 查询用户
- 创建用户
- 修改用户密码
- 删除用户
- 例子
- 注意事项
- 权限控制
- 常用权限
- 查询权限:`SHOW GRANTS FOR`
- 授予权限:`GRANT `关键字
- 撤销权限:`REVOKE`关键字
- 注意事项
- 示例
基础篇
数据库相关概念
(数据库DataBase(DB)、数据库管理系统DataBase Management System(DBMS)、结构化查询语言SQL(Structured Query Language))
主流的关系型数据库管理系统(SQL统一语言标准)
mysql安装及卸载
安装
教程在windows上安装mysql社区版8.0.26版本
windows上安装方法参考视频:https://www.bilibili.com/video/BV1Kr4y1i7ru?p=3
我用的是linux,我就介绍Ubuntu20.04上的安装mysql服务的方法:
-
首先,打开一个终端窗口。
-
更新包列表以确保获取最新的软件版本。可以通过运行以下命令来完成这一步:
sudo apt update
-
安装MySQL服务器,运行以下命令:
sudo apt install mysql-server
-
在安装完毕后,MySQL服务应该会自动启动。可以使用以下命令来确认MySQL正在运行:
sudo systemctl status mysql
-
对于初次安装MySQL,需要运行mysql_secure_installation脚本来提高MySQL的安全性。运行以下命令:
sudo mysql_secure_installation
这个脚本将会引导你设置root用户的密码、删除匿名用户、禁止root远程登录,并删除测试数据库。
这个我们就不设置了,反正是调试用,设置太多影响效率
卸载
在Ubuntu 20.04中完全卸载MySQL,可以按照以下步骤操作:
-
首先,需要停止正在运行的MySQL服务。打开终端并输入以下命令:
sudo systemctl stop mysql
-
然后,使用apt来卸载MySQL。输入以下命令:
sudo apt-get remove --purge mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean
-
最后,为了确保所有相关的文件和配置都被删除,可以手动删除数据目录。默认情况下,这个目录通常是/var/lib/mysql。但是,应该检查系统以确认正确的位置。删除之前,请确保不再需要其中的任何数据。可以使用以下命令来删除这个目录:
sudo rm -rf /var/lib/mysql
注意:这将永久删除所有MySQL数据库和相关数据,所以在执行此操作前请确保已备份重要数据。
数据模型
关系型数据库RDBMS(建立在关系模型基础上,由多张相互连接的二维表组成的数据库)
同理,不通过表结构存储的数据库称为非关系型数据库
。
SQL通用语法及分类
通用语法
在学习具体的SQL语句之前,先来了解一下SQL语言的通用语法。
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
单行注释:--
注释内容 或#
注释内容
多行注释:/*
注释内容*/
DDL(Data Definition Language): 数据定义语言,用来定义数据库对象(数据库,表,字段)
数据库操作
- 先登录数据库:
mysql -u root -p
查询所有数据库:show databases
SHOW DATABASES;
-
示例:
show databases;
查询当前数据库:select
SELECT DATABASE();
-
示例:
select database();
创建数据库:create
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
-
示例:
create database itcast;
-
不能创建名称已经存在的数据库:
-
可以加
if not exists
判断选项,这样数据库存在不会创建,同时也不会报错:create database if not exists itcast;
-
创建时指定默认字符集(建议使用utf8mb4,占4个字节;而utf8只占3个字节):
create database if not exists itcast3 dafault charset utf8mb4;
当创建数据库时,选择
utf8mb4
字符集的原因主要有以下几点:-
完整的Unicode支持:
utf8mb4
(UTF-8 Multibyte 4)是一个在MySQL中用于表示UTF-8字符的字符集。与早期的utf8
实现相比,它提供了对4字节长的Unicode字符的全面支持。这意味着它可以存储任何标准的Unicode字符。而原始的utf8
实现只支持最多3字节长的字符,无法处理某些特殊字符,例如Emoji表情符号或者某些特殊的汉字。 -
数据安全性:由于
utf8
不能正确处理4字节长的字符,如果试图插入这样的字符,可能会导致数据截断和丢失。使用utf8mb4
可以避免这种情况,确保数据的完整性。 -
未来兼容性:随着更多的新的Unicode字符和Emoji表情符号的出现,
utf8mb4
能够保证未来的兼容性。
因此,为了确保你的数据库能够处理所有类型的Unicode字符,一般建议在创建数据库时使用
utf8mb4
字符集。 -
注意:字符集主要影响文本类型数据,例如 CHAR, VARCHAR, TEXT 等的存储和处理。通常它不会影响非字符串数据类型,如整型、浮点型、日期时间类型等的存储和处理。
删除数据库:drop
DROP DATABASE [ IF EXISTS ] 数据库名;
-
示例
drop database itcast;
使用数据库:use
USE 数据库名;
-
示例
use myDatabase;
-
可以使用select查询当前正在使用的数据库:
注意事项
- UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
表操作
mysql数据类型
数值类型
- 示例:
年龄:age tinyint unsigned
分数:score double(4,1)
,表示最长四位有效数字(100.0),然后1表示有1位小数。
字符串类型
定长字符串性能较好,变长字符串性能较差。
- 示例:
用户名:username varchar(50)
性别:gender char(1)
日期时间类型
- 示例:
生日:birthday date
查询当前数据库所有表:show tables
SHOW TABLES;
-
示例
show tables;
查询表结构:desc
DESC 表名;
-
示例
desc tb_user;
查询指定表的建表语句:show create table(可展示表的详细信息,包括创建时的comment)
SHOW CREATE TABLE 表名;
-
示例
show create table tb_user;
上面代码显示的是
tb_user
表的创建语句。这个语句包含了表的名称、字段定义、存储引擎和字符集等信息。具体解释如下:
-
CREATE TABLE tb_user
:创建名为tb_user
的表。 -
下面是四个字段的定义:
id
int DEFAULT NULL COMMENT ‘编号’:创建一个名为id
的字段,数据类型为整型(int),默认值为NULL,注释为’编号’。name
varchar(50) DEFAULT NULL COMMENT ‘姓名’:创建一个名为name
的字段,数据类型为可变长字符串(varchar),最大长度为50个字符, 默认值为NULL,注释为’姓名’。age
int DEFAULT NULL COMMENT ‘年龄’:创建一个名为age
的字段,数据类型为整型(int),默认值为NULL,注释为’年龄’。gender
varchar(1) DEFAULT NULL COMMENT ‘性别’:创建一个名为gender
的字段,数据类型为可变长字符串(varchar),最大长度为1个字符, 默认值为NULL,注释为’性别’。
-
ENGINE=InnoDB
:指定表的存储引擎为InnoDB。MySQL支持多种存储引擎,每种存储引擎都有自己的优点和特性,InnoDB是最常用的一种,支持事务处理和行级锁定。 -
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
:指定表的默认字符集为utf8mb4,排序规则为utf8mb4_0900_ai_ci。字符集决定了表中文本数据的存储和处理方式,而排序规则决定了如何比较字符。 -
COMMENT='用户表'
:给表添加注释,说明这是一个’用户表’。
-
创建表:create table
CREATE TABLE 表名(字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释],字段3 字段3类型 [COMMENT 字段3注释],...字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];
注意:最后一个字段后面没有逗号
-
示例1
create table tb_user(id int comment '编号',name varchar(50) comment '姓名',age int comment '年龄',gender varchar(1) comment '性别') comment '用户表';
https://www.bilibili.com/video/BV1Kr4y1i7ru?p=8
-
示例2
设计一张员工信息表,要求如下:- 编号(纯数字)
- 员工工号 (字符串类型,长度不超过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 '员工表';
把这个粘贴到终端就能添加了。
root@ubuntu:~#
root@ubuntu:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.34-0ubuntu0.20.04.1 (Ubuntu)Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myDatabase |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)mysql>
mysql>
mysql> use myDatabase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql>
mysql>
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| myDatabase |
+------------+
1 row in set (0.00 sec)mysql>
mysql>
mysql> show tables;
+----------------------+
| Tables_in_myDatabase |
+----------------------+
| tb_user |
+----------------------+
1 row in set (0.00 sec)mysql>
mysql>
mysql> 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 '员工表';
Query OK, 0 rows affected (0.10 sec)mysql>
mysql> show tables;
+----------------------+
| Tables_in_myDatabase |
+----------------------+
| emp |
| tb_user |
+----------------------+
2 rows in set (0.01 sec)mysql>
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)mysql>
https://www.bilibili.com/video/BV1Kr4y1i7ru?p=8
添加字段:ALTER TABLE ttt ADD fff ttt COMMENT 'xxx';
。MySQL中的字段可以在创建表时创建,也可以在创建表后单独添加
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例:ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
- 示例:给表添加email字段
# 列出所有数据库
show databases;
# 切换到指定数据库
use myDatabase;
# 查看当前使用的数据库
select database();
# 列出当前数据库所有表
show tables;
# 查看指定表结构
desc tb_user;
# 为指定表增加指定字段
alter table tb_user add email varchar(255);
# 查看指定表结构
desc tb_user;
修改字段数据类型(ALTER TABLE 表名 MODIFY
)(主要用于修改列的数据类型或更改默认值等。它只能用来修改已经存在的列的属性,不能修改列的名称)
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段数据类型(ALTER TABLE 表名 CHANGE
)(不仅可以修改列的数据类型或更改默认值,还可以更改列的名称)
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
- 例:将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
删除字段(ALTER TABLE 表名 DROP 字段名;
)
ALTER TABLE 表名 DROP 字段名;
示例:
修改表名(ALTER TABLE 表名 RENAME TO 新表名
)
ALTER TABLE 表名 RENAME TO 新表名
- 示例:
删除表(DROP TABLE [IF EXISTS] 表名;
)
DROP TABLE [IF EXISTS] 表名;
- 示例:
删除表,并重新创建该表(TRUNCATE TABLE 表名
)(可用于清理表数据)
TRUNCATE TABLE 表名;
- 示例:
DML(Data Manipulation Language): 数据操作语言,用来对数据库表中
的数据进行增删改
添加数据INSERT
指定字段:INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
- 示例
给employee表所有的字段添加数据:
insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');
-
用DataGrip:
点击表右键 --> New --> Query Console:
将上面的sql语句粘贴进去,全选sql语句(不然软件不知道你要执行哪一句),然后点击运行:
查询表数据,双击表即可查看,或者执行select * from xxx表
查询表数据:
执行:select * from emp;
全部字段:INSERT INTO 表名 VALUES (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...);
- 示例:
insert into emp values(3,'3','Itcast','男',10,'12345','2000-01-01');
批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
-
示例:
insert into emp values(4,'4','Itcast','男',10,'123456789012345681','2000-01-01'),(5,'5','Itcast','男',10,'123456789012345682','2000-01-01');
注意用逗号,
分隔不同条(行)的数据。
注意事项
- 字符串和日期类型数据应该包含在引号中
- 插入的数据大小应该在字段的规定范围内
更新数据(修改数据):UPDATE
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
(注意:如果不带where条件修改的是整张表的数据)
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
- 例:
UPDATE emp SET name = 'Jack' WHERE id = 1;
删除(某条)数据:DELETE
DELETE FROM 表名 [ WHERE 条件 ];
(不带where条件将会删除整张表数据)
DELETE FROM 表名 [ WHERE 条件 ];
-
示例1
DELETE FROM emp WHERE name = 'Jack';
-
示例2
DELETE FROM emp WHERE name = 'Jack';
上面语句将会删除整张表数据:
DQL(Data Query Language): 数据查询语言,用来查询数据库中表的记录
-
语法:
SELECT字段列表 FROM表名字段 WHERE条件列表 GROUP BY分组字段列表 HAVING分组后的条件列表 ORDER BY排序字段列表 LIMIT分页参数
-
数据准备
表结构: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 '身份证号',workaddress varchar(50) comment '工作地址',entrydate date comment '入职时间' ) comment '员工表';
表数据:
INSERT INTO emp (id, workno, name, gender, age, idcard,workaddress, entrydate) VALUES (1,'1','柳岩','女',20,'123456789012345678' ,'北京','2000-01-01'), (2,'2','张无忌','男',18,'123456789012345670' ,'北京' ,'2005-09-01'), (3,'3','韦一笑','男',38,'123456789712345670' ,'上海' ,'2005-08-01'), (4,'4','赵敏','女',18,'123456757123845670' ,'北京' ,'2009-12-01'), (5,'5','小昭','女',16,'123456769012345678' ,'上海' ,'2007-07-01'), (6,'6','杨道','男',28,'12345678931234567X' ,'北京' ,'2006-01-01'), (7,'7','范瑶','男',40,'123456789212345670' ,'北京' ,'2005-05-01'), (8,'8','黛绮丝','女',38,'123456157123645670' ,'天津' ,'2015-05-01'), (9,'9','范凉凉','女',45,'123156789012345678' ,'北京' ,'2010-04-01'), (10,'10','陈友谅','男',53,'123456789012345670' ,'上海' ,'2011-01-01'), (11,'11','张士诚','男',55,'123567897123465670' ,'江苏' ,'2015-05-01'), (12,'12','常遇春','男',32,'123446757152345670' ,'北京' ,'2004-02-01'), (13,'13','张三丰','男',88,'123656789012345678' ,'江苏' ,'2020-11-01'), (14,'14','灭绝','女',65,'123456719012345670' ,'西安' ,'2019-05-01'), (15,'15','胡青牛','男',70,'12345674971234567X' ,'西安' ,'2018-04-01'), (16,'16','周芷若','女',18,null ,'北京' ,'2012-06-01');
基础查询
查询指定(单个或多个)字段:SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
-
示例
查询以下指定字段select name,workno,age from emp;
查询所有字段:SELECT * FROM 表名;
(建议开发中不要直接写*
)
- 示例:
SELECT * FROM emp;
设置别名:SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
或者SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
-
示例:
SELECT workaddress as '工作地址' FROM emp;
或者:
SELECT workaddress '工作地址' FROM emp;
去除重复记录:SELECT DISTINCT 字段列表 FROM 表名;
SELECT DISTINCT 字段列表 FROM 表名;
-
示例:
select distinct workaddress from emp;
转义:ESCAPE
关键字定义转义字符
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/
之后的_
不作为通配符
在SQL查询中,LIKE操作符用于搜索某列的模式。其中,百分号(%)和下划线(_)是特殊字符。百分号表示任意数量的字符,包括零字符;而下划线代表一个单独的任意字符。
有时,我们可能需要搜索包含这些特殅字符的数据。为了使这些特殊字符被识别为普通字符,我们需要使用ESCAPE关键字。它定义了一个转义字符,让你能够在LIKE模式中搜索百分号或下划线。
在你给出的例子中,
ESCAPE '/'
声明了斜杠(/
)作为转义字符。然后,在WHERE子句中,name LIKE '/_张三'
表示搜索以“张三”开头且前面有任意一个字符的所有名字。因此,/_张三
匹配到的可能是“1张三”,“a张三”等等。总的来说,加上
ESCAPE '/'
是为了明确指定在该查询中,我们希望把斜杠(/
)视为转义字符,使得在LIKE模式中可以正确解析其后的下划线。
条件查询:WHERE
关键字
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件运算符
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN … AND … | 在某个范围内(含最小、最大值) |
IN(…) | 在in之后的列表中的值,多选 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
例子
# 查找年龄等于20的员工信息
select * from emp where age = 20;# 查找年龄等于20的员工信息
select * from emp where age < 20;# 查找身份证号为null的员工信息
select * from emp where idcard is null;# 查找身份证号不是null的员工信息
select * from emp where idcard is not null;# 查找年龄不等于88的员工信息
select * from emp where age != 88;
select * from emp where age <> 88;# 查询年龄在15到20岁之间的员工信息
select * from emp where age > 15 and age < 20;
select * from emp where age > 15 && age < 20;
# 注意between为闭区间
select * from emp where age between 15 and 20;# 查询性别为女且年龄小于25岁的员工
select * from emp where gender = '女' and age < 25;
select * from emp where age in(18,19,40);# 查询姓名为两个字的员工信息
select * from emp where name like '__';# 查询身份证号最后一位为X的员工信息
select * from emp where emp.idcard like '%X';
select * from emp where emp.idcard like '_________________X';
聚合查询(聚合函数)(count、max、min、avg、sum)(将一列数据作为一个整体,进行纵向计算 )
常见聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:所有的null值不参与聚合函数计算
示例
-- 统计企业员工数量(注意:不会统计字段值为null的行,所以不要把可能为null的字段来用于统计)
SELECT count(*) FROM emp;
SELECT count(id) FROM emp;-- 统计员工的平均年龄
SELECT avg(age) FROM emp;-- 统计员工的最大年龄
SELECT max(age) FROM emp;-- 统计员工的最小年龄
SELECT min(age) FROM emp;-- 统计西安地区的年龄之和
SELECT sum(age) FROM emp where workaddress = '西安';
分组查询:GROUP BY
关键字、HAVING
关键字(可用AS
关键字设置别名)
语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件不参与分组;having是分组后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
例子
-- 根据性别分组,统计男性和女性数量(只显示分组数量,不显示哪个是男哪个是女)
select count(*) from emp group by gender;-- 根据性别分组,统计男性和女性数量(会显示哪个是男,哪个是女)
select gender, count(*) from emp group by gender;-- 根据性别分组,统计男性和女性的平均年龄
select gender, avg(age) from emp group by gender;-- 统计根据工作地址分组的人数
select workaddress, count(*) from emp group by workaddress;-- 统计年龄小于45,根据工作地址分组的人数
select workaddress, count(*) from emp where age < 45 group by workaddress;-- 统计年龄小于45,根据工作地址分组的人数,输出员工数量大于等于3的组(可设置别名as,as关键字可省略)
select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) >= 3;
select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
select workaddress, count(*) as address_count from emp where age < 45 group by workaddress having address_count >= 3;
注意事项
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
排序查询:ORDER BY关键字、ASC: 升序(默认)、DESC: 降序
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式
- ASC: 升序(默认)
- DESC: 降序
例子
-- 根据年龄升序排序
SELECT * FROM emp ORDER BY age ASC;
SELECT * FROM emp ORDER BY age;-- 根据入职时间降序排序
SELECT * FROM emp ORDER BY entrydate desc ;-- 两字段排序,根据年龄升序排序,入职时间降序排序
SELECT * FROM emp ORDER BY age ASC, entrydate DESC;
注意事项
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
分页查询:LIMIT关键字
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子
-- 查询第一页数据,展示10条
SELECT * FROM emp LIMIT 0, 10;
-- 查询第一页时,索引可省略
SELECT * FROM emp LIMIT 10;-- 查询第二页
SELECT * FROM emp LIMIT 10, 10;
注意事项
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
- 分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10
DQL查询语句小结练习
注意,第五个问题可能有歧义:
它可能有以下两种理解:
1、查询emp表中性别为男,且年龄在20到40岁(含20和40)的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
2、1、查询emp表中性别为男,且年龄在20到40岁(含20和40),年龄升序排序,年龄相同按入职时间升序排序,获取前5个员工信息。
对于第一种理解,“查询emp表中性别为男,且年龄在20到40岁(含20和40)的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。” 因为SQL的限制,不能直接先取前5条然后再排序,但可以通过子查询实现,如下:
SELECT *
FROM (SELECT *FROM empWHERE sex = '男' AND age BETWEEN 20 AND 40LIMIT 5
) AS subquery
ORDER BY age ASC, join_time ASC;
对于第二种理解,“查询emp表中性别为男,且年龄在20到40岁(含20和40),年龄升序排序,年龄相同按入职时间升序排序,获取前5个员工信息。” 这个问题是先排序,然后取前五个记录。对应的SQL语句为:
SELECT *
FROM emp
WHERE sex = '男' AND age BETWEEN 20 AND 40
ORDER BY age ASC, join_time ASC
LIMIT 5;
DQL
语句编写顺序和执行顺序
DQL
编写顺序:SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
DQL执行顺序:FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
示例:验证DQL执行顺序
-- 查询年龄大于15的员工的姓名、年龄,并根据年龄进行升序排序
select name,age from emp where age > 15 order by age asc ;
-- 验证执行顺序
select e.name e_name,e.age e_age from emp e where e.age > 15 order by e_age asc ;
DCL(Data Control Language): 数据控制语言,用来管理数据库用户、控制数据库的访问权限
管理用户
查询用户
USE mysql;
SELECT * FROM user;
localhost,root的含义是这个root只能通过本机访问数据库,无法通过远程方式访问数据库
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
例子
-- 创建用户arnoldtest1,只能在当前主机localhost访问,密码123456# create user 'arnoldtest1'@'localhost' identified by 'Reaishenghuo';
-- 提示:[2023-10-02 02:07:26] [HY000][1819] Your password does not satisfy the current policy requirements
-- 用:SHOW VARIABLES LIKE 'validate_password%';查看当前密码规则
SHOW VARIABLES LIKE 'validate_password%';
# validate_password.changed_characters_percentage,0
# validate_password.check_user_name,ON
# validate_password.dictionary_file,""
# validate_password.length,8
# validate_password.mixed_case_count,1
# validate_password.number_count,1
# validate_password.policy,MEDIUM
# validate_password.special_char_count,1
CREATE USER 'arnoldTest1'@'localhost' IDENTIFIED BY 'Reai#2023shenghuo';-- 创建用户arnoldTest1,能在任意主机访问
create user 'arnoldTest1'@'%' identified by 'Reai#2023shenghuo';-- 修改密码
alter user 'arnoldTest1'@'localhost' identified with mysql_native_password by 'Reai#2024shenghuo';-- 删除用户
drop user 'arnoldTest1'@'localhost';
drop user 'arnoldTest1'@'%';
注意事项
- 主机名可以使用 % 通配,此时创建的账号可从任意主机访问数据库
- 这类
SQL
开发人员操作的比较少,主要是DBA(Database Administrator数据库管理员)
使用
权限控制
常用权限
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
更多权限请看权限一览表
查询权限:SHOW GRANTS FOR
SHOW GRANTS FOR '用户名'@'主机名';
授予权限:GRANT
关键字
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:REVOKE
关键字
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项
- 多个权限用逗号分隔
- 授权时,数据库名和表名可以用
*
进行通配,代表所有
示例
-- 创建用户
CREATE USER 'arnoldTest'@'localhost' IDENTIFIED BY 'Reai#2023shenghuo';-- 查询权限
SHOW GRANTS FOR 'arnoldTest'@'localhost';-- 授予权限(授予'arnoldTest'@'localhost'用户对myDatabase所有表的所有权限)
grant all on myDatabase.* to 'arnoldTest'@'localhost' ;-- 撤销权限
revoke all on myDatabase.* from 'arnoldTest'@'localhost' ;-- 删除用户
drop user 'arnoldTest'@'localhost';
不知道为啥授予和撤销权限的时候,老提示:
myDatabase> grant all on myDatabase.* to 'arnoldTest'@'localhost'
[2023-10-02 02:44:57] [42000][1044] Access denied for user 'root'@'%' to database 'myDatabase'
[2023-10-02 02:44:57] [42000][1044] Access denied for user 'root'@'%' to database 'myDatabase'
myDatabase> revoke all on myDatabase.* from 'arnoldTest'@'localhost'
[2023-10-02 02:44:59] [42000][1044] Access denied for user 'root'@'%' to database 'myDatabase'
[2023-10-02 02:44:59] [42000][1044] Access denied for user 'root'@'%' to database 'myDatabase'
莫名其妙的问题,以后遇到再说了!。。。