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

笔记贴代码

导航

mysql 基础学习笔记
删除
单行函数
字符函数
其他函数
between and 范围
表的修改
常见函数
约束
类型
数学函数
常见约束
字符串连接 concat
常用SQL
流程控制函数
库管理
分组查询
like 模糊查询
表的复制
标识列(自增长列)
子查询
添加表时添加约束
SQL规范
统计函数
安装
连接查询
模糊查询
DQL 数据查询语言
修改表时的约束
order by 排序
表的删除
修改
逻辑表达式
分页查询
创建表
sql99标准
TCL 事务控制语言
常量、表达式、函数
日期函数
is null or is not null 是否为Null
DML 数据操作语言
条件表达式
where 和having后面
sql92标准
<=> 安全等于
去重
别名
+号
联合查询
in 属于
条件查询
表的管理
插入
DDL 数据定义语言

mysql 基础学习笔记

安装

SQL规范

类型

常用SQL

常见函数

单行函数

字符函数

数学函数

日期函数

其他函数

流程控制函数

统计函数

常见约束

DQL 数据查询语言

常量、表达式、函数

别名

去重

+号

字符串连接 concat

条件查询

条件表达式

逻辑表达式

模糊查询

like 模糊查询

between and 范围

in 属于

is null or is not null 是否为Null

<=> 安全等于

order by 排序

分组查询

连接查询

sql92标准

sql99标准

子查询

where 和having后面

分页查询

联合查询

DML 数据操作语言

插入

修改

删除

DDL 数据定义语言

库管理

表的管理

创建表

表的修改

表的删除

表的复制

约束

添加表时添加约束

修改表时的约束

标识列(自增长列)

TCL 事务控制语言

字符串连接 `concat`

`like` 模糊查询

`between and` 范围

`in` 属于

`is null` or `is not null` 是否为Null

`<=>` 安全等于

`order by` 排序