装置

MacOS

Windows 10

Centos 7

SQL标准

  1. 不辨别大小写，然而倡议大写关键词，小写表名、列名
  2. 每条SQL倡议分号结尾
  3. 每条SQL依据须要进行换行缩进
  4. 正文：
    单行：# --
    多行：/* */

类型

数值：    整型：    小数：        定点数        浮点数字符：    短字符: char varcahr     长文本: text blob日期：    date        2020-02-03    datetime    2020-02-02 02:02:02    timesiamp   1594279093389    time        02:02:02    year        2020

罕用SQL

use test; -- 选中 数据库show tables; -- 事实以后选中的库的所有表show tables from mysql; # 查问mysql下的tablesSHOW INDEX FROM stuinfo; # 显示以后的索引select database(); # 查看以后库/* create table table1(    id int,    name varchar(24)); */desc table1; -- 查看表构造select * from table1;insert into table1 (id,name) values(1,'测试'); -- 插入update table1 set name='我靠' where name='ces'; -- 批改update table1 set id=0 where name='我靠'; -- 批改delete from table1 where name='我靠'; -- 删除

常见函数

单行函数

解决

字符函数

SELECT LENGTH('我是谁'); -- 依据以后字符集 失去以后字节长度SELECT CONCAT('我','是','谁呀'); -- 拼接字符串SELECT UPPER('Abc'); -- 转换成大写字符SELECT LOWER('Abc'); -- 转换成小写SELECT SUBSTR('abc123一二三',4,3); -- 从4开始截取3个 蕴含4 索引从1开始SELECT SUBSTRING('abc123一二三',4,3); -- 从4开始截取3个 蕴含4 索引从1开始SELECT INSTR('01234556','234'); -- 查找字符串呈现的地位 没找到就是0SELECT TRIM(' A B C D '); -- 去除前后空格SELECT TRIM('a' FROM 'aaaaA B  CaaaDaaaa' ); -- 去除前后的aSELECT LPAD('abc123一二三',20,'*'); -- 左填充/保留右边的SELECT RPAD('abc123一二三',20,'*'); -- 右填充/保留右边的

数学函数

SELECT ROUND(0.4); -- 四舍五入SELECT ROUND(0.5);  -- 四舍五入SELECT ROUND(-0.4);  -- 四舍五入SELECT ROUND(-0.5);  -- 四舍五入SELECT CEIL(0.2); -- 向上取整SELECT FLOOR(0.9); -- 向下取整SELECT RAND(); -- 随机数SELECT TRUNCATE(0.2345,3); -- 保留多少位小数 不进行解决SELECT MOD(10,3); -- 取余

日期函数

SELECT NOW(); -- 返回以后的日期工夫SELECT CURDATE(); -- 返回以后的日期SELECT CURTIME(); -- 返回以后工夫SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- 年/月/日SELECT STR_TO_DATE('2020-03-23 22:32:12','%Y-%m-%d %H:%i:%s'); -- 将字符串解析成工夫SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 格式化工夫

其余函数

SELECT VERSION(); -- 查看版本号SELECT DATABASE(); -- 查看以后的库 SELECT USER(); -- 以后用户

流程管制函数

SELECT IF(10<5,'大','小'); -- ifSELECT `last_name`, IF(`commission_pct` IS NULL,TRUE,FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; -- if 例子# case SELECT `salary`,`department_id`,CASE department_id    WHEN 80 THEN        salary * 1.2        WHEN 40 THEN        salary * 1.9    ELSE        salary * 0END AS newMoney FROM `employees`ORDER BY department_id DESC;

统计函数

统计
SELECT COUNT(*) FROM `employees`; -- 数量统计SELECT SUM(`salary`) FROM `employees`; -- 相加和SELECT AVG(`salary`) FROM `employees`; -- 平均值SELECT MAX(`salary`) FROM `employees`; -- 最大值SELECT MIN(`salary`) FROM `employees`; -- 最小值SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`FROM `employees`;# 留神/**/

常见束缚

一种限度，用于限度表中的数据，用来保障表中数据的精确和可靠性
分类：    六大束缚：        NOT NULL: 非空,用于保障该字段的值不能为空        DEFAULT: 默认值        PRIMARY KEY: 主键，用于保障该字段具备唯一性（非空）        UNIQUE: 惟一（可空）        CHECK: 查看 （mysql 不反对）        FOREIGN KEY: 外键，用于限度两个表的关系，用于保障该字段必须来自关联表的主键    增加束缚的机会：        1. 创立表        2. 批改表    束缚的增加分类：        列级束缚：            六大束缚语法上都反对，外键束缚有效        表级束缚：            除了非空和默认其余都反对            主键和惟一的区别：    主键：惟一、非空、只能一个    惟一：惟一、可空、多个外键：    1. 从表设置外键关系    2. 主从表类型统一/兼容    3. 主表关联键个别为主键或惟一    4. 必须对应主表数据，删除先删除从表再删除主表

DQL 数据查询语言

常量、表达式、函数

SELECT 1; -- 常量值SELECT 10*20; -- 表达式SELECT VERSION(); -- 函数

别名

SELECT 1+2 as number;

去重

SELECT DISTINCT     `name`FROM     `table`

+号

SELECT 1+2; -- 数字相加SELECT 1+'123'; -- 字符串会强转成数字非数字转为0SELECT 1 + Null; -- 与Null返回Null

字符串连贯 concat

SELECT CONCAT('a','b','c'); -- 字符串拼接SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- 拼接字段

条件查问

条件表达式
< > >= <= != <> <=>
# 等于SELECT     CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE     `first_name`='Bruce'; # 平安等于 可查 NullSELECT     CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE     `first_name`<=>'Bruce'; # 大于SELECT     *FROM `employees` WHERE     `department_id` > 60;# 小于SELECT     *FROM `employees` WHERE     `department_id` <= 60;    # 不等于 # != 不倡议SELECT     *FROM `employees` WHERE     `department_id` <> 60;
逻辑表达式
&& || !
AND OR NOT
# 且查问# 不倡议 &&SELECT CONCAT(`first_name`,`last_name`) as `name`FROM `employees` WHERE     `first_name`='Bruce'     AND     `last_name`='Ernst';# 或SELECT     CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE     `first_name`='Bruce'     OR     `last_name`='K_ing';    # 非SELECT     CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE     NOT    `first_name`='Bruce'
含糊查问
like 含糊查问

%：任意多个字符
_: 任意单个字符
\: 本义

# 蕴含 aSELECT * FROM `employees` WHERE     `first_name` like '%a%'; # 定义转义字符SELECT * FROM `employees` WHERE     `last_name` like '%$_%' ESCAPE '$';
between and 范畴
在什么到什么之间，蕴含前后
# 查找100 到200 之间的数据SELECT * FROM `employees` WHERE     `employee_id` BETWEEN 100 AND 200;
in 属于
查问是否属于某些列表中的某一个
# 查问是否属于某些列表中的某一个SELECT * FROM `employees` WHERE     `job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
is null or is not null 是否为Null
# 查问字段为空的SELECT * FROM `employees`WHERE    `commission_pct` IS NULL;# 查问不为空的SELECT * FROM `employees`WHERE    NOT `commission_pct` IS NULL;SELECT * FROM `employees`WHERE    `commission_pct` IS NOT NULL;
<=> 平安等于
既能够判断NULL 又能够判断数值
SELECT * FROM `employees`WHERE    `commission_pct` <=> NULL;
order by 排序

ASC升序 DESC降序 默认 ASC

SELECT * FROM `employees` ORDER BY `salary` ASC; -- 升序SELECT * FROM `employees` ORDER BY `salary` DESC; -- 降序SELECT `salary` * IFNULL(`commission_pct`,0) + IFNULL(manager_id,0) as money,`salary`  FROM `employees` ORDER BY `money`; -- 表达式别名降序SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; -- 按函数SELECT * FROM `employees` ORDER BY `salary` DESC, `employee_id` ASC; -- 多个排序条件

分组查问

# 每个工种的总工资SELECT SUM(`salary`) AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;# 每个工种的最高工资SELECT MAX(`salary`) as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;# 查问邮箱蕴含a的工种的最高工资SELECT MAX(`salary`) as `max`, `job_id`FROM `employees`WHERE email LIKE '%a%' GROUP BY `job_id` ORDER BY `max`;# 查问邮箱蕴含a的工种的最高工资大于1万的   HAVINGSELECT MAX(`salary`) as `max`, `job_id` FROM `employees`WHERE email LIKE '%a%' GROUP BY `job_id`HAVING `max` > 10000ORDER BY `max`;# 查问名称长度一样的大于5集体的数据SELECT LENGTH(`first_name`) AS `name`, COUNT(1) AS `count`FROM `employees`GROUP BY `name`HAVING `count` > 5;# 多字段分组SELECT AVG(`salary`) AS `avg`,`department_id`,`job_id`FROM `employees`GROUP BY `department_id`,`job_id`ORDER BY `department_id`,`job_id`

连贯查问

分类:    按年代划分：        sql92规范：只反对内连贯        sql99规范【举荐】：反对内连贯+外来连贯（左外、右外）+穿插连贯    按性能划分：        内连贯：            等值连贯            非等值连贯            自连贯        外连贯：            左外连贯            右外连贯            全外连贯        穿插连贯

sql92规范

# 等值连贯SELECT e.`first_name`,j.`job_title`,`j`.`job_id`FROM `employees` as e,`jobs` as jWHERE `e`.`job_id` = `j`.`job_id`;# 非等值连贯# 自连贯SELECT e.last_name,m.employee_id,e.manager_id,m.last_name FROM `employees` AS e, `employees` AS mWHERE `e`.employee_id = `m`.manager_id

sql99规范

# 等值连贯SELECT `last_name`,`department_name`FROM employeesINNER JOIN departmentsON employees.`department_id` = departments.`department_id`# 简单的等值连贯SELECT department_name,COUNT('*') AS count,MAX(`salary`) AS max,min(`salary`) AS minFROM employeesINNER JOIN departmentsON employees.department_id = departments.department_idWHERE last_name LIKE '%o%'GROUP BY department_nameHAVING `count` BETWEEN 2 AND 10ORDER BY `count`# 多表等值连贯SELECT last_name,department_name,job_titleFROM employeesINNER JOIN departmentsON departments.department_id =employees.department_idINNER JOIN jobsON employees.job_id = jobs.job_id;

子查问

分类：    按呈现地位：        select 前面：            只反对标量子查问        form 前面            表子查问        where 和having前面 *            标量子查问  *            列子查问    *            行子查问        exists前面            表子查问            按后果集的行列数不同：        标量子查问(后果集只有一行一列)        列子查问(后果集只有一列多行)        行子查问(后果集只有多列一行)        表子查问(后果集个别多行多列)

where 和having前面

特点: 1. 放在小括号内 2. 放在条件右侧 3. 标量子查问：配合单行操作符。列子查问：配合多行操作符。

# 查问工资比Abel高的人SELECT * FROM employees WHERE salary > (    SELECT salary FROM employees     WHERE last_name = 'Abel');# job_id与141号员工一样且工资大于143号员工的员工SELECT last_name,salary,employee_id FROM employeesWHERE job_id = (    SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (    SELECT salary FROM employees WHERE employee_id = 143)# 查问比50号部门最低工资高的部门的最低工资SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees GROUP BY department_idHAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)ORDER BY minsalary DESC;

分页查问

SELECT *FROM `employees` LIMIT (page-1)*size,size;SELECT *FROM `employees` LIMIT 10,10;

联结查问

# 无

DML 数据操作语言

插入

INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','捡垃圾1',200,6000);INSERT INTO jobs VALUES('p_a1','捡垃圾1',200,6000),('p_a2','捡垃圾2',200,6000);INSERT INTO jobs SET job_id = 'ces',job_title="123"INSERT INTO jobs SELECT 'ces1','444',200,6000

批改

# 简略批改UPDATE jobsSET job_title = '2222'WHERE job_id = 'ces1'# 批改多表UPDATE jobsINNER JOIN employeesON employees.job_id = jobs.job_idSET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'WHERE jobs.job_title = 'Public Accountant12322222'

删除

# 单表删除DELETE FROM jobsWHERE job_id = 'ces'# 清空整表TRUNCATE TABLE ttt;# 多表删除DELETE employeesFROM employeesINNER JOIN jobsON jobs.job_id = employees.job_idWHERE jobs.job_id = 'SA_MAN'

DDL 数据定义语言

1. 库的治理    创立\批改\删除2. 表的治理    创立\批改\删除创立: create批改: alter删除: drop

库治理

# 创立 IF NOT EXISTS 进行容错 CHARACTER SET  字符集CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;# 批改 不能该了RENAME DATABASE books TO newbook;# 更改字符集ALTER DATABASE books CHARACTER SET gbk;# 库的删除DROP DATABASE IF EXISTS books;

表的治理

创立表

USE books;# 表的创立/*CREATE TABLE 表名(    列名 列类型([长度]) [束缚],    列名 列类型([长度]) [束缚],    ...)*/CREATE TABLE book (    id INT,    b_name VARCHAR(20),    price DOUBLE,    author_id INT,    publish_date DATETIME);DESC book;CREATE TABLE author(    id INT,    au_name VARCHAR(20),    nation VARCHAR(10));DESC author;

表的批改

/*批改列：ALTER TABLE 表名 [CHANGE|MODIFY|ADD|DROP] COLUMN 列名 类型｜束缚;批改表名：ALTER TABLE 表名 RENAME TO 新表名;*/# 批改列名ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;# 批改列类型束缚ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;# 增加新列ALTER TABLE author ADD COLUMN annual DOUBLE;# 删除ALTER TABLE author DROP COLUMN annual;# 批改表名ALTER TABLE author RENAME TO authers;

表的删除

DROP TABLE IF EXISTS authers;

表的复制

# 复制表构造CREATE TABLE copy LIKE author;# 复制整个表CREATE TABLE copy2 SELECT * FROM author;

束缚

增加表时增加束缚

# 列级束缚CREATE TABLE stuinfo(    id INT PRIMARY KEY, # 主键    stu_name VARCHAR(20) NOT NULL, # 非空    gender CHAR(1) DEFAULT '男', # 默认    seat INT UNIQUE # 惟一);# 表级束缚CREATE TABLE stuinfo(    id INT, # 主键    stu_name VARCHAR(20), # 非空    gender CHAR(1) , # 默认    seat INT, # 惟一    majorid INT,        CONSTRAINT pk PRIMARY KEY(id),# 主键    CONSTRAINT uq UNIQUE(seat),# 惟一    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键);# 通用倡议束缚DROP TABLE  IF EXISTS stuinfo;CREATE TABLE stuinfo(    id INT PRIMARY KEY, # 主键    stu_name VARCHAR(20) NOT NULL, # 非空    gender CHAR(1) DEFAULT '男', # 默认    seat INT UNIQUE, # 惟一    majorid INT,    -- 表级束缚    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键);

批改表时的束缚

# 批改列束缚ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL; # 表级束缚ALTER TABLE stuinfo ADD PRIMARY KEY(id);ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);# 删除外键# ALTER TABLE stuinfo DROP PRIMARY KEY;

标识列(自增长列)

AUTO_INCREMENT
CREATE TABLE stuinfo(    id INT PRIMARY KEY AUTO_INCREMENT, # 主键    stu_name VARCHAR(20) NOT NULL, # 非空    gender CHAR(1) DEFAULT '男', # 默认    seat INT UNIQUE, # 惟一    majorid INT,    -- 表级束缚    CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键);

TCL 事务管制语言

一个或一组sql语句组成一个执行单位，要么全副执行，要么全副失败。

ACID: A: 原子性 C: 一致性 I: 隔离性 D: 持久性

# 提交示意实现，回滚代表异样set autocommit = 0; -- 敞开主动事务START TRANSACTION; -- 开始事务UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;SAVEPOINT a; -- 保留节点UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;ROLLBACK; -- 回滚事务ROLLBACK TO a; -- 回滚事务到指定节点COMMIT;    -- 提交事务