SQL:
DDL 数据库操作:
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABSAE();
创建数据库
CREATE DATABASE 数据库名称;
CREATE DATABASE IF NOT EXISTS 数据库名称; //如果数据库不存在就创建
CREATE DATABASE yyds DEFAULT CHARSET utf8mb4;//设置字符集,设置成了utf8mb4,utf8mb4代表utf8四个字节的,utf8在MySQL是3个字节
删除数据库
DROP DATABASE 数据库名称;
DROP DATABASE IF EXISTS 数据库名称;//如果数据库存在就删除
使用数据库
USE 数据库名称;
DDL 表操作-查询:
查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名
DDL 表操作-创建:
创建表
CREATE TABLE 表名(
字符1 字符1 类型 [COMMENT 表注释],[]//表示可写可不写
字符2 字符2 类型 [COMMENT 表注释],
字符2 字符2 类型 [COMMENT 表注释]
)[COMMENT 表注释];
案例:
CREATE TABLE user(
name varchar(50) COMMENT '姓名',
gender varchar(1));
DDL 表操作-修改:
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 表注释];
修改数据类型
ALTER TABLE 表明 MODIFY 字段名 类型;
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 表注释];
删除字段
ALTER TABLE user DROP shengfenzheng;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
删除表
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;如果表在就删除
DML-添加数据:
给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,.......)VALUES(值1,值2,.....);
给全部字段添加数据;
INSERT INTO 表名 VALUES(值1,值2,.....);
批量添加数据
INSERT INTO 表名(字段名1,字段名2,.......)VALUES(值1,值2,.....),(值1,值2,.....),(值1,值2,.....);
INSERT INTO 表名 VALUES(值1,值2,.....),(值1,值2,.....),(值1,值2,.....);
DML-修改数据:
UPDATE 表名 SET 字段名1=值1,字段名2=值2,..... [WHERE 条件] ;//字段名=值(新换的值)
案例:
UPDATE yuangong SET name='王氪思' WHERE id=1;
注意:修改条件可以有,也可以没有,如果没有则会修改整张表的所有数据。
例如:
UPDATE yuangong SET name='王氪思';
DML-删除数据:
DELETE FROM 表名[WHERE 条件] ;
案例
DELETE FROM yuangong WHERE name='王氪思';
注意:DELETE语句的条件可以有,也可以没有,如果没有则会删除整张表的所有数据,
例如:
DELETE FROM yuangong ;
DELETE语句不可以删除某一个字段的值(可以使用UPDATE)。
DQL-基本查询:
查询多个字段
SELECT 字段名1,字段名2,....... FROM 表名;
SELECT * FROM 表名;//查询表所有字段
设置别名
SELECT 字段名1 [AS 别名1], 字段名2 [AS 别名2],FROM usr;
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DDL-条件查询:
1,语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
案例:
SELECT * FROM usr WHERE id=3;
2,条件
比较运算符号:
(大于) >,(大于等于) >=,(小于) <,(小于等于) <=,(等于) =,(不等于)<>,(不等于)!=,
(在某个范围之内(含最小,含最大)) BETWEEN...AND...,(在in之后的列表中的值,多选一) IN(...),(模糊匹配(_匹配单个字符,%匹配任意个字符)) LIKE 占位符,
(是NULL) IS NULL
逻辑运算符
(并且(多个条件同时成立)) AND或&&,(或者(多个条件任意一条成立)) OR或||,(非,不是) NOT或!
DQL-聚合函数:
1,介绍
将一列数据作为一个整体,进行纵向计算。
2,常见聚合函数
COUNT()统计数量;
MAX()最大值
MIX()最小值
AVG()平均值
SUM()求和
3,语法:
SELECT 聚合函数(字段列表) FROM 表名;
DQL-分组查询:
1,语法
SELECT 字段列表 ,[聚合函数] FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件] [聚合函数] ;
2,WHERE和HAVING 区别
执行时机不同:WHERE是分组之前进行过滤,不满足WHERE条件,不参与分组;而HAVING是分组之后对结果进行过滤。
判断条件不同:WHERE不能不对聚合函数进行判断,而HAVING可以。
DQL-排序查询:
1,语法
SELECT 字表列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2 ;
2,排序方式
ASC:升序 (默认值)
DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
DQL-分页查询:
1,语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录;
注意:
起始索引从0开始,起始索引=(查询页码 -1(n-1))*每页显示的记录。
分页查询是数据库的方言,不同的数据有不同的实现,MYSQL是LIMIT。
如果查询的第一页数据,起始索引可以省略,直接简写LIMIT 10(查询记录)。
DCL-管理用户:
查询用户:
USE mysql;
SELECT *FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';//主机名改为'%'可以在任意主机访问数据库
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '密码';
删除用户
DROP USER '用户名'@'主机名';
DCL-权限控制
权限 说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询权限
INSERT 插入权限
UPDATE 修改权限
DELETE 删除权限
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
其他权限描述氪参考官方
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
函数:
字符串函数
函数 功能
CONCAT(s1,s2,...) 字符串拼接,将s1,s2,...sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,len,padstr) 左值充,用字符串padstr对str的左边进行填充,达到len个字符串长度
RPAD(str,len,padstr) 右值充,用字符串padstr对str的右边进行填充,达到len个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start, len) 返回从字符串str从start位置起的len个长度的字符串
案例函数的使用:
SELECT CONCAT('hello','word');//函数前面加个SELECT
数值函数
函数 功能
CEIL(X) 向上取整
FLOOR(X) 向下取整
MOD(N,M) 返回N/M的模,数学公式(n/m=x)
RAND() 返回0~1内的随机数
ROUND(X,D) 求参数X的四舍五入的值,保留D位参数
案例函数的使用:
SELECT ROUND(RAND()*10000,0);
日期函数
函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定data的年份
MONTH(date) 获取指定data的月份
DAY(date) 获取指定data的日期
DATE_ADD(date,INTERVAL expr unit) 返回一个日期/时间值加上一个时间间隔expr后的时间值(就是指定一个时间,往后推多少天或者多月或者多少年)
DATEDIFF(expr1,expr2) 返回起始时间expr1和结束时间expr2之间的天数
案例函数的使用:
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);
流程函数
函数 功能
IF(value,t,f) 如果value为true,则返回t,否则返回f
IFNULL(value1,value2) 如果value不为空,返回value1,否则返回value2
CASE WHEN [val1] THEN [res1] ...ELSE [deault] END 如果val1为true,返回res1,...否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] ...ELSE [deault] END 如果expr的值等于val1,返回res,...否则返回default默认值
约束
1,概念:约束作用于表中字段上的规则,用于限制存储在表中的数据。
2,目的:保证数据库中数据的 正确、有效和完整性。
约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键一行数据的唯一标识,要求非空且唯一 PRIMARY KEY
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT
检查约束 保证字段值满足一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY
自动增长 就比id INT类型,添加数据时,会自增1 AUTO_INCREMENT
演示案例:
CREATE TABLE yh(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL UNIQUE,
age INT CHECK(age>0 && age<120),
start CHAR(1) DEFAULT'1',
gender CHAR(1)
);
外键约束
语法:
CREATE TABLE 表名(
字段名 数据类型,
....
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
);
添加外键(创建表以后,没有添加外键的情况下)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
案例:
CREATE TABLE fk(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL
);
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
age INT,
job VARCHAR(20) COMMENT'职位',
dept_id int,
CONSTRAINT fk_emp FOREIGN KEY(dept_id) REFERENCES fk(id)
);
外键删除行为
行为 说明
NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT 一致)
RESTRCT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION 一致)
CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。
SET NULL 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键的值位null(这就要求该外键允许取null)
SET DEFAULT 父表有变更时,子表将外键列设置成一个默认值(lnnodb不支持)。
语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名) ON UPDATE 行为关键字 ON DELETE行为关键字;
多表查询
内连接:
隐式内连接
SELECT 字段列表 FROM 表名1,表名2 WHERE 连接条件.....;
举例用法:
SELECT 字段列表 FROM 表名1,表名2 WHERE 表名1.字段=表名2.字段;
显示内连接
SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 连接条件.....;
举例用法:
SELECT 字段列表 FROM 表名1 INNER JOIN 表名2 ON 表名1.字段=表名2.字段;
外连接:
左外连接
SELECT 字段列表 FROM 表名1 LEFT OUTER JOIN 表名2 ON 条件...;
相当于查询1(左表)的所有数据包含表1和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表名1 RIGHT OUTER JOIN 表名2 ON 条件...;
自连接
SELECT 字段列表 FROM 表名A 别名1JOIN 表名A 别名2 ON 条件...;
字连接查询,可以是内连接查询,也可以是外连查询
联合查询-UNION,UNION ALL
对于UNION查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表名1
UNION [ALL]//不加ALL的话,就去重数据
SELECT 字段列表 FROM 表名2;
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
SELECT 字段列表 FROM 表名A WHERE 条件(SELECT 字段列表 FROM 表名 [WHERE 条件] );
标量子查询
子查询返回的结果是个单值,最简单的形式,这种查询被称为标量子查询
常用的操作符: = ,<>, >,>=,<,<=
举例:
SELECT 字段列表 FROM 表名A WHERE n=(SELECT 字段列表 FROM 表名 );
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用操作符
操作符 描述
IN 在指定的集合范围之内,多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中,有任意一个满足即可
SOME 与ANY等同,使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足
举例:
SELECT 字段列表 FROM 表名A WHERE n>操作符(SELECT 字段列表 FROM表名 );
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符: = ,<>,IN,NOT IN
举例 :
SELECT 字段列表 FROM 表名A WHERE n=(SELECT 字段列表 FROM表名 );
表子查询
子查询返回结果多行多列,这种查询称表子查询
常用的操作符:IN
SELECT 字段列表 FROM 表名A WHERE (x,y) IN(SELECT 字段列表 FROM 表名 WHERE条件 );
事务
事务操作
<第一种操作方式>
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit=0;//0是换成手动提交事务,一般默认1,自动提交
提交事务
COMMIT;
回滚事务
ROLLBACK;
用法:
SELECT @@autocommit;
SET @@autocommit=0;
UPDATE 表名A SET 字段名1=值1,字段名2=值2,..... [WHERE 条件] ;
UPDATE 表名B SET 字段名1=值1,字段名2=值2,..... [WHERE 条件] ;
...
COMMIT;
ROLLBACK;
<第二种操作方式>
开启事务
START TRANSACTION或BEGIN;
提交事务
COMMIT;
回滚事务
ROLLBACK;
用法:
START TRANSACTION;
UPDATE 表名A SET 字段名1=值1,字段名2=值2,..... [WHERE 条件] ;
UPDATE 表名B SET 字段名1=值1,字段名2=值2,..... [WHERE 条件] ;
...
COMMIT;
ROLLBACK;
事务四大特性(可能面试会考)
原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一直性:事务完成时,必须使所有的数据都保持一致状态。
隔离性:数据库系统提供的隔离机制,保证事务不在受外部并发操作影响的独立环境下运行。
持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的;
并发事务问题
问题 描述
脏读 一个事务读到另外一个事务还没有提交的数据。
不可重复读 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。
事务隔离级别
隔离级别 脏读 不可重复读 幻读
Read uncommitted 未解决 未解决 未解决
Read committed 解决 未解决 未解决
Repeatable Read(默认) 解决 解决 未解决
Serializable 解决 解决 解决
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
举例:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;