mysql去重分组关键词_mysql 基础学习笔记

安装

SQL规范

不区分大小写，但是建议大写关键词，小写表名、列名

每条SQL建议分号结尾

每条SQL根据需要进行换行缩进

注释：

单行：# --

多行：/* */

类型

数值：

整型：

小数：

定点数

浮点数

字符：

短字符: 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下的tables

SHOW 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'); -- 查找字符串出现的位置 没找到就是0

SELECT TRIM(' A B C D '); -- 去除前后空格

SELECT TRIM('a' FROM 'aaaaA B CaaaDaaaa' ); -- 去除前后的a

SELECT 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,'大','小'); -- if

SELECT `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 * 0

END 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'; -- 字符串会强转成数字非数字转为0

SELECT 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';

# 安全等于 可查 Null

SELECT

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 模糊查询

%：任意多个字符

_: 任意单个字符

\: 转义

# 包含 a

SELECT * 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万的 HAVING

SELECT MAX(`salary`) as `max`, `job_id`

FROM `employees`

WHERE email LIKE '%a%'

GROUP BY `job_id`

HAVING `max` > 10000

ORDER 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 j

WHERE `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 m

WHERE `e`.employee_id = `m`.manager_id

sql99标准

# 等值连接

SELECT `last_name`,`department_name`

FROM employees

INNER JOIN departments

ON employees.`department_id` = departments.`department_id`

# 复杂的等值连接

SELECT department_name,COUNT('*') AS count,MAX(`salary`) AS max,min(`salary`) AS min

FROM employees

INNER JOIN departments

ON employees.department_id = departments.department_id

WHERE last_name LIKE '%o%'

GROUP BY department_name

HAVING `count` BETWEEN 2 AND 10

ORDER BY `count`

# 多表等值连接

SELECT last_name,department_name,job_title

FROM employees

INNER JOIN departments

ON departments.department_id =employees.department_id

INNER JOIN jobs

ON 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 employees

WHERE 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_id

HAVING 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 jobs

SET job_title = '2222'

WHERE job_id = 'ces1'

# 修改多表

UPDATE jobs

INNER JOIN employees

ON employees.job_id = jobs.job_id

SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'

WHERE jobs.job_title = 'Public Accountant12322222'

删除

# 单表删除

DELETE FROM jobs

WHERE job_id = 'ces'

# 清空整表

TRUNCATE TABLE ttt;

# 多表删除

DELETE employees

FROM employees

INNER JOIN jobs

ON jobs.job_id = employees.job_id

WHERE 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; -- 提交事务