一、SQL通用语法
1、SQL语句可以单行或多行书写,以分号结尾。
2、SQL语句可以使用空格/缩进来增强语言的可读性。
3、MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
4、注释:
● 单行注释:- -注释内容 或 # 注释内容
● 多行注释:/* 注释内容 */
二、SQL语句的分类
1、DDL(Data Definition Language):
数据定义语言,用来定义数据库对象(数据库、表、字段)
查询所有数据库 :
SHOW DATABASES
查询当前数据库 :
SELECT DATABASE();
创建数据库 :
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除数据库 :
DROP DATABASE [IF EXISTS] 数据库名;
使用数据库 :
USE 数据库名;
查询当前数据库所有表 :
SHOW TABLES;
查询表结构 :
DESC 表名;
查询指定表的建表语句 :
SHOW CREATE TABLE 表名;
创建表 :
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMEN 表注释];
添加字段 :
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
修改字段的数据类型 :
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型 :
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
删除字段 :
ALTER TABLE 表名 DROP 字段名;
修改表名 :
ALTER TABLE 表名 RENAME TO 新表名;
删除表 :
DROP TABLE [IF EXISTS] 表名;
删除指定表,并重新创建该表 :
TRUNCATE TABLE 表名;
2、DML(Data Manipulation Language):
数据操作语言,用来对数据库表中的数据进行增删改
1、给指定字段添加数据:
INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2,...);
2、给全部字段添加数据:
INSERT INTO 表名 VALUES (值1,值2,...);
3、批量添加数据:
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
4、修改数据:
UPDATE 表名 SET 字段名1=值1,字段名2=值2,... [WHERE 条件];
5、删除数据:
DELETE FROM 表名 [WHERE 条件]
3、DQL(Data Query Language):
数据查询语言,用来查询数据库中表的记录
1、查询多个字段:
SELECT 字段1,字段2,字段3,... FROM 表名;
SELECT * FROM 表名;
2、设置别名:
SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;
3、去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
4、基本查询
条件查询(WHERE)
聚合函数(count、max、min、avg、sum)
分组查询(GROUP BY)
排序查询(ORDER BY)
● ASC 升序(默认)
● DESC 降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段值进行排序
分页查询(LIMIT)
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
● 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
● 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
● 如果查询的是第一页数据,起始索引可以省略,直接简写为LIMIT 10
5、DQL-执行顺序
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
4、DCL(Data Control Language):
数据控制语言,用来创建数据库用户、控制数据库的访问权限
1、查询用户
USE mysql;
SELECT * FROM user;
2、创建用户
CREATE USER '用户名'@‘主机名’ IDENTIFIED BY '密码';
3、修改用户密码
ALTER USER '用户名'@‘主机名’ IDENTIFIED WITH mysql_native_password BY '新密码';
4、删除用户
DROP USER '用户名'@‘主机名’;
5、查询用户权限
SHOW GRANTS FOR '用户名'@‘主机名’;
6、授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
7、撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
三、SQL中的数据类型
数值类型
字符串类型
日期类型
四、函数
函数:是指一段可以直接被另一段程序调用的程序或代码
1、字符串函数
函数 | 功能 |
---|---|
CONCAT(S1,S2,…Sn) | 字符串拼接,将S1,S2…Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
2、数值函数
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
3、日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
4、流程函数
函数 | 功能 |
---|---|
IF(expr,t,f) | 如果expr为true返回t,否则返回f |
IFNULL(val1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [value1] THEN [res1] …ELSE [default] END | 如果val1为true,返回res1,否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1] …ELSE [default] END | 如果expr的值等于val1,返回res1,…否则返回default默认值 |
五、约束
1、概念: 约束是作用于表中字段上的规则,用于限制存储在表中的数据
2、目的: 保证数据库中数据的正确性、有效性和完整性
3、分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束 | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
添加外键:
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
4、删除/更新行为
六、多表查询
1、多表关系
● 一对一(多用于单表拆分)
● 一对多(多对一)(在多的一方建立外键,指向一的一方的主键)
● 多对多(建立第三张中间表,中间表至少包含两个外键,分别关联两个主键)
2、多表查询分类
(1) 连接查询:
● 内连接:相当于查询A、B交集部分的数据
> 隐式内连接:
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
> 显式内连接:
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
● 左外连接:查询左表所有数据,以及两张表交集部分的数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
● 右外连接:查询右表所有数据,以及两张表交集部分的数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
● 自连接:当前表与自身的连接查询,自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
(2) 联合查询-UNION、UNION ALL:
对于UNION查询,就是把多次查询的结果合并起来,形成一个新的查询结果集,联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
(3) 子查询(嵌套查询):
● 标量子查询:(子查询结果为单个值)
常用操作符:=、<>、>、>=、<、<=
● 列子查询:(子查询结果为一列,可以是多行)
常用操作符:
IN:在指定的集合范围之内,多选一
NOT IN:不在指定的集合范围之内
ANY:子查询返回列表中,有任意一个满足即可
SOME:与ANY等同,使用SOME的地方都可以使用ANY
ALL:子查询返回列表的所有值都必须满足
● 行子查询:(子查询结果为一行,可以有多列)
常用操作符:=、<>、IN、NOT IN
● 表子查询:(子查询结果为多行多列)
常用操作符:IN
七、事务
1、事务:
是一组操作的集合,他是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
2、事务操作
(1) 查看/设置事务提交方式
SELECT @@autocommit;--查看事务提交方式(1自动提交,0手动提交)
SET @@aotucommit=0;--设置事务提交方式为手动提交
(2) 开启事务
START TRANSACTION;
BEGIN;
(3) 提交事务
COMMIT;
(4) 回滚事务
ROLLBACK;
3、事务的四大特性(ACID)
● 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
● 一致性(Consistency):事务完成时,必须使所有的数据保持一致状态
● 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
● 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
4、并发事务问题
● 脏读:一个事务读到另一个事务还没有提交的数据
● 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同
● 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在
5、事务的隔离级别
Oracle数据库默认的隔离级别是Read committed(读已提交)
MySQL数据库默认的隔离级别是Repeatable Read(可重复度)
(1) 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
(2) 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}