一、 基本的语法规则
- 所有的sql语法需要以
;
结尾 - 单行注释
-- 杠、杠、空格
# 井号
- 多行注释
/**/
- 书写规范
- 尽量只用大写来写关键词、表名之类的小写(虽然sql是一种大小写不敏感的语言)
- 不要让表面和数据库系统及常用关键字、函数名重复,但是如果坚持使用请添加着重
符号(两个反斜杠)
二、select
1. select语句的基本使用
1.1 select的最简单使用
语法:select 字段1,字段2… from 表名
# 例子
select sname from student;
注意:
- 如果字段使用
*
代替 ,则表示,查询该表中的所有数据 - 另外
字段
就是表中第一行的一系列名字
- 使用的时候注意,如果
from
另起一行一定要保证前面字段留出一个空格
1.2 列的别名
语法:select 字段1 别名1,字段2 别名2… from 表名
select sname name from student;
注意:
- 可以在字段名和别名之间增加
AS
关键字,也可以省略 - 也可以为别名添加
双引号
作为别名,此时的别名可以加空格等特殊字符
1.3 取出重复行
语法:select distinct 字段1 from 表名;
select distinct stu_id from student;
注意:
- distinct后面不要跟两个字段,逻辑比较复杂不建议这么写(将两个字段看做了一组)
- 如果字段中有数据为null,其参与运算的结果也为null
1.4 查询常数
语法:select “常数” 字段1 from 表名
select 'itchen',stu_id from student;
注意:
- 常数会自动添加到每行查询结果的前面
1.5 显示表结构
语法:describe 表名
describe student;
注意:
- 其中的
describe
可以简写为desc
2. select语句设置过滤条件查询
2.1 where的简单使用
语法:select 字段名 from 表明 where 过滤条件【等式或不等式 --> 算数运算符、比较运算符】
select stu_id from student where id = 1;
注意:
在使用的时候数值类型的数据可能会发生隐式转换
在取模的时候
结果的符号
只和被模数有关
比较运算符中
等于符号
为=
比较字符 等价于 比较两者的 ASCLL码 值
2.2 关键字
运算符 | 名称 | 作用 |
---|---|---|
IS NULL | 为空 | 判断是否为空 |
IS NOT NULL | 不为空 | 判断是否不为空 |
LEAST(‘’,‘’,‘’,‘’) | 最小 | 判断其中最小值 |
GREATEST(‘’,‘’,‘’,‘’) | 最大 | 判断其中最大值 |
BETWEEN … AND … | 之间 | 判断在…和…之间的 |
OR | 或者 | 同时满足前后的关系式 |
IN | 在其中 | in前的数据满足in后面的条件 |
NOT IN | 不在其中 | in前的数据不满足in后面的条件 |
LIKE ‘%x%’ | 模糊查询 | 包含某个字符 |
REGEXP | 正则表达式 | ***(独立匹配规则) |
RLIKE | 正则表达式 | ***(独立匹配规则) |
注意:
- %表示前面或后面有任何字符、任意数量的字符占位
- 下划线表示单个字符的占位
2.3逻辑运算符
运算符 | 作用 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或|| | 逻辑或 |
XOR | 逻辑异或 |
注意:
- 优先级
AND
大于OR
2.4 位运算符(了解)
三、排序与分页、多表查询
1. 排序
1.1 使用order by排序
语法:select 字段1 别名 from 表名 order by 表名/列的别名,表名/列的别名;
select stu_id from student order by grade;
注意:
- 默认情况下是按照升序排列的
- 如果需要降序排列,我们可以在语句后面加上
DESC
- 列的别名只能在order by里面使用;不能在where里面使用
- 语法如下:select 字段1 别名 from 表名 where 条件 order by 表名/列的别名;
- 在order by后面可以添加两个字段,其中前一个字段表示一级排序条件;第二个字段表示二级排序条件(类似excel)
2. 分页
2.1 使用limit实现数据的分页显示
语法:select 字段1 别名 from 表名 limit 位置偏移量,显示的条目数;
select stu_id from student limit 0,20;
注意:
- where …… order by …… limit 的声明顺序
- select xxx
from xxx
where xxx
order by xxx
limit xxx; - 如果limit中的位置位置偏移量为0,则可以省略不写
2.2 limit …… offset(mysql8.0新特性)
语法:select 字段1 别名 from 表名 limit 显示的条目数 offset 位置偏移量;
select stu_id from student limit 20 offset 0;
注意:位置偏移量类似起点
3.多表查询
思想:根据关联信息防止查询的冗余、空间的浪费
语法:select 字段1,字段2 from 表名1,表名2 where 连接条件
select sname,cname from student,class where class.cid = student.sid;
注意:
一定要写连接条件,否则容易发生
笛卡尔的错误
上面写法的优化方式:在所有字段前都加上
表名.
为了方便使用、同时便于查看,可以给上述表起别名。此处的别名属于
覆盖
3.1 等值连接和非等值连接
- 通过不同表之间属性的值来关联多个表
3.2 自连接和非自连接
- form里面的表都是一张表,两个别名
- eg. 员工表查员工和他的上级
3.3 内连接和外连接
- 内连接相当于两表求交集
- 外连接相当于两表求并集
- 语法:
select ... from ... join ... on ...;
(如果需要多次加表,每次多加上一个join on)
3.4 union关键字
- 如果场景中union和union all最好使用后者(
union效率低
,因为多执行了一次去重的操作) - 这个关键字可以连接两个select语句
SQL JOIN
的使用场景:
- 内连接
- 左外连接
- 右外连接
- 去重后取左
- 去重后取右
- 外连接(2 + 5)
- 外连接去重(4 + 5)
四、函数/方法
sql语言中不同的管理系统,函数的差异很大
1. 各种单行函数的分类
1.1 数值函数
1.2 字符串函数
ASCAII('')
:返回第一个字符的ascaii码值CHAR_LENGTH('')
:返回字符个数LENGTH('')
:返回存储字符的字节数CONCAT(param,"xxx",param)
:连接字符串INSERT('',idx,len,'')
:插入字符串REPLACE("","aa","bb")
:将aa字符串替换为bb字符串UPPER()
:大写LOWER()
:小写TRIM()
:去掉首尾的空字符串STRCMP('','')
:比较字符串的大小LOCATE('x','xxx')
:x在xxx中首次出现的位置REVERSE('')
:反转字符串
1.3 日期和时间函数**(理解万岁,会查就行!)**
基本的一些函数
CURDATE()
:获取当前年月日CURTIME()
:获取当前时分秒NOW()
:获取当前系统日期和时间DATE_ADD(NOW(),INTERVAL 1 YEAR);
:计算日期和时间,year可以用任意时间类型替代
日期和时间戳的转换
获取年、月、日、时、分、秒、季度、周(参数使用基本函数)
- 转换时间和秒数
- 日期、时间的一些运算
- 日期的格式化与解析
1.4 流程控制函数(类似三目运算符)
IF(value,value1,value2)
:如果value为true则返回value1IFNULL(value1,value2)
:如果value1不为NULL则返回value1SELECT ... CASE WHEN ... THEN ...
WHEN ... THEN ...
WHEN ... THEN ...
END ...
:根据相应的条件显示信息
1.5 加密与解密函数
- 由于大多数数据的加密都在spring的工具中进行,数据库只提供数据的密文存储
- 常见方法:MD5、SHA、PASSWORD(8.0弃用)
- MySQL信息函数
- 其他函数
2. 聚合函数的使用
语法:
select 字段名 别名 聚合函数1,聚合函数2 ...
from 表名
[where 查询条件1,查询条件2 ... ]
[group by 分组条件1,分组条件2 ... ]
[order by 排序条件1,排序条件2 ... ]
执行顺序:
- 找表、过滤、分组、连接
- 修改方式、去重
- order by、limit
# 示例
select AVG(stu_id) from student;
常见的聚合函数:
- AVG()
- SUM()
- MIN()
- MAX()
- COUNT()
2.1 count()函数
参数 | 效果 | 示例 |
---|---|---|
字段名 | 字段下数据的条数(不计算空值) | count(stu_id) |
常数 | 表中的数据的条数 | count(1) |
通配符 | 表中的数据的条数 | count(*) |
3. GROUP BY 的使用
语法:同4.2聚合函数的使用
select中出现的非聚合函数一定要写在group by中,group by中出现的非聚合函数不一定要写在select中
# 示例 通过cid和cname分组并查询平均成绩
select cid,cname,AVG(grade) from student group by cid,cname
注意:其中两个分组条件顺序会造成查找出来的数据的顺序略有不同,但是查找出的数据始终是那些数据
4. HAVING 的使用
语法结论:
- 如果在
where
中使用了聚合函数,必须将where
替换为having
having
必须声明在group by
的后面- 有
having
一定有group by
比较having和where:
where在没有聚合函数的时候使用
having在有聚合函数的时候使用
五、子查询
在一个查询结构中嵌套了另一个查询
基本语法:
select 字段名1
from 表名1
where 字段名 运算符 (
select 字段名2
from 表名2
where 查询条件
)
注意:
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询,多行操作符对应多行子查询
六、数据库操作、表操作(创建和管理表)
1. 创建和管理数据库
1.1 创建数据库
- 方式一 – 使用默认的字符集
CREATE DATABASE mytest1;
- 方式二 – 使用自己定义的字符集
CREATE DATABASE mytest2 CHARACTER SET 'GBK';
- 方式三 – 创建不存在的数据库(推荐)
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'GBK';
1.2 管理数据库
- 查看数据库
SHOW DATABASE;
- 切换数据库
USE mytest2;
- 查看当前数据库中所保存的表
SHOW TABLES;
- 查看当前使用的数据库
SELECT DATABASE() FROM DUAL;
- 查看指定数据库下保存的表
SHOW TABLES FROM mysql;
1.3 修改数据库
- 修改数据库的字符集
ALTER DATABASE mytest2 CHARACTER SET 'utf-8';
- 删除数据库
-- 方式1
DROP DATABASE mytest1;
-- 方式2
DROP DATABASE IF EXISTS mytest1;
2. 创建和管理表
2.1 创建表
- 方式一
-- 需要用户具有创建表的权限
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE;
);
- 方式二
-- 基于现有的表创建表
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
2.2 查看表结构
-- 查看表
DESC myemp1;
-- 查看表的创建信息
SHOW CREATE TABLE myemp1;
2.3 修改表
- 添加字段
-- 默认添加到表中的最后一个字段位置
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);
-- 添加到开始位置
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;
-- 添加到指定位置
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) AFTER emp_name;
- 修改字段
-- 修改字段的类型
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAUT 'aaa';
- 重命名字段
-- 只修改名称
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
-- 修改名称的同时修改其字符串范围
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);
- 删除字段
ALTER TABLE myemp1
DROP COLUMN my_email
2.4 重命名表
- 方式一
RENAME TABLE myemp1
TO myemp11;
- 方式二
ALTER TABLE myemp11
RENAME TO myemp1;
2.5 删除表
- 方式一
DROP TABLE IF EXISTS myemp1;
- 方式二
DROP TABLE myemp1;
2.6 清空表
TRUNCATE myemp1;
2.7 提交与回滚
-- 提交 将数据库数据进行一次保存
COMMIT;
-- 回滚 将数据库数据返回到上一次提交
ROLLBACK;
3. 数据的增删改
3.1 添加数据(记得自己建表建库)
-- 方式1 没有指明添加的字段
INSERT INTO emp1
VALUES (1,'Tom','2024-7-18');
-- 方式2 指明添加的字段
INSERT INTO emp1(id,`name`,hire_date,salary)
VALUES (1,'Tom','2024-7-18');
-- 方式3 添加多条记录
INSERT INTO emp1
VALUES (1,'Tom','2024-7-18'),(1,'Tom','2024-7-18');
3.2 更新数据
-- 修改一条数据中的某个字段
UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;
-- 修改一条数据中的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;
3.3 删除数据
DELETE FROM emp1
WHERE id = 1;