MySQL

一、SQL 简介

1、DB：数据库，保存一组有组织的数据的容器

2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据

3、SQL:结构化查询语言，用于和DBMS通信的语言

DQL（Data Query Language）：数据查询语言 select
DML(Data Manipulate Language):数据操作语言 insert 、update、delete
DDL（Data Define Languge）：数据定义语言 create、drop、alter
TCL（Transaction Control Language）：事务控制语言 commit、rollback

-- 1.查看当前所有的数据库
show databases;
-- 2.打开指定的库
use mydb;
-- 3.查看当前库的所有表
show tables;
-- 4.查看其它库的所有表
show tables from mydb;
-- 5.查看表结构
desc mytable;
-- 6.查看服务器的版本
select version();
-- 7.注释
-- 单行注释：#注释文字
-- #单行注释：-- 注释文字
-- 多行注释：/* 注释文字  */

二、DQL 语句

+号的使用

-- mysql中的+号：
-- 仅仅只有一个功能：运算符
select 100+90; -- 两个操作数都为数值型，则做加法运算
select '123'+90;-- 只要其中一方为字符型，试图将字符型数值转换成数值型.
select 'john'+90;-- 如果转换失败，则将字符型数值转换成0
select null+10;-- 只要其中一方为null，则结果肯定为null

2.1 单行函数

2.1.1 字符函数

length获取参数值的字节个数

SELECT LENGTH('a'); -- 1
SELECT LENGTH('中'); -- 3

concat拼接

SELECT CONCAT('a','b'); -- ab
SELECT CONCAT('a','_','b'); -- a_b

concat_ws拼接

select concat_ws(';',orgid,'123')
from kbpos_erp_apply; -- 500382;123

substr截取子串

SELECT SUBSTR('我的世界',3); -- 世界
SELECT SUBSTR('我的世界',1,2); -- 我的

SUBSTRING_INDEX截取子串

-- 从左开始
select SUBSTRING_INDEX('12321313;131;3131',';',1); -- 12321313
-- 从右开始
select SUBSTRING_INDEX('12321313;131;3131',';',-1); -- 3131

upper转换成大写

SELECT UPPER('hello'); -- HELLO

lower转换成小写

SELECT LOWER('hellO'); -- hello

trim去前后指定的空格和字符

SELECT TRIM('    hello    '); -- hello

ltrim去左边空格
rtrim去右边空格
replace替换

SELECT REPLACE('hello worldworld','world','china'); -- hello chinachina

lpad左填充

SELECT LPAD('hello',6,'*'); -- *hello

rpad右填充

SELECT RPAD('hello',6,'*'); -- hello*

instr返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('Hello','el') ; -- 2
SELECT INSTR('Hello','eq') ; -- 0

ifnull 空判断

SELECT ifnull(null,0) -- 0

2.1.2 数学函数

round 四舍五入

SELECT ROUND(-1.55); -- -2
SELECT ROUND(1.567,2); -- 1.57

rand 随机数

SELECT truncate(RAND()*10+1,0); -- 1~10以内的随机数

floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99); -- -10
SELECT FLOOR(9.99); -- 9

ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02); -- -1
SELECT CEIL(1.02); -- 2

mod取余 mod(a,b) ： a-a/b*b

SELECT MOD(10,-3); -- 1
SELECT MOD(-10,3); -- -1
SELECT 10%3; -- 1

truncate截断

SELECT truncate(RAND()*10+1,0); -- 1~10以内的随机数

GREATEST、LEAST：返回集合中最大的值，least返回最小的，注意跟max，min不一样，max里面跟的是col，返回这个列的最大值最小值

select GREATEST(1,2,3,4); -- 4
select LEAST(1,2,3,4); -- 1

power(x,y) 、sqrt(x)、abs(x)

-- power(x,y):返回x的y次方
select power(2,3); -- 8
-- sqrt(x):返回x的平方根
select sqrt(25); -- 5
-- abs(x):返回x的绝对值
select ABS(-1); -- 1

2.1.3 日期函数

序号	格式符	功能
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01,02…11,12）
4	%c	月份（1,2,…11,12）
5	%d	日（01,02,…）
6	%H	小时（24小时制）
7	%h	小时（12小时制）
8	%i	分钟（00,01…59）
9	%s	秒（00,01,…59）

now当前系统日期+时间

SELECT NOW(); -- 2019-01-01 17:35:39
SELECT YEAR(NOW()); -- 2019
SELECT YEAR('1998-1-1'); -- 1998
SELECT MONTH(NOW()) -- 1
SELECT MONTHNAME(NOW()) --January
SELECT DAY(now()); -- 1
SELECT HOUR(now()); -- 17
SELECT MINUTE(now()); -- 38
SELECT SECOND(now()); -- 48
-- 一周中第几天
SELECT DAYOFWEEK(CURDATE());
-- 一个月中第几天
SELECT DAYOFMONTH(CURDATE());
-- 一年中第几天
SELECT DAYOFYEAR(CURDATE());
-- 第几季度
SELECT QUARTER(CURDATE());
-- 第几周
SELECT WEEK(CURDATE());
-- 日期添加
SELECT DATE_ADD(current_date,INTERVAL 5 DAY);
-- 时间差
select DATEDIFF(current_date,DATE_ADD(current_date,INTERVAL 5 DAY)); -- 5
select TIMESTAMPDIFF(day ,20190104,CURRENT_DATE); -- 1
select TIMESTAMPDIFF(second ,20190104,CURRENT_DATE); -- 86400

curdate当前系统日期

SELECT CURDATE(); -- 2019-01-01

curtime当前系统时间

SELECT CURTIME(); -- 17:36:08

str_to_date 将字符转换成日期

SELECT STR_TO_DATE('3-2 1998','%m-%d %Y') -- 1998-03-02

date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') -- 2019年01月01日

2.1.4 流程控制函数

if 处理双分支

SELECT IF(10 < 5, '大', '小'); -- 小

case语句处理多分支

情况1：处理等值判断

SELECT
CASE 50
WHEN 30 THEN 50*1.1
WHEN 40 THEN 50*1.2
WHEN 50 THEN 50*1.3
ELSE 0
END -- 65.0

情况2：处理条件判断

SELECT 
CASE delete_flag
WHEN delete_flag = false THEN 'A'
WHEN delete_flag = true THEN 'B'
ELSE 'C'
END
from A

2.1.5 其他函数

version版本

select version(); -- 5.7.21-log

database当前库

select database() -- test

user当前连接用户

select user() -- root@localhost

2.2 分组函数

又称为聚合函数或统计函数或组函数,分组函数都忽略null值

如果不是数值型，SUM、AVG则返回0

sum 求和

SELECT SUM(salary) FROM employees;

max 最大值

SELECT AVG(salary) FROM employees;

min 最小值

SELECT MIN(salary) FROM employees;

avg 平均值

SELECT MAX(salary) FROM employees;

count 计数

-- 效率：
-- MYISAM存储引擎下，COUNT(*)的效率高
-- INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
SELECT COUNT(DISTINCT salary) FROM employees;

group_concat：返回这个列连接组合的结果,中间有逗号隔开

select group_concat(orgid order by addtime desc separator ';')
from kbpos_erp_apply
group by orgid

2.3 连接查询(SQL99语法)

左连接

A包含B

SELECT <select_list> FROM A LEFT JOIN B ON A.key=B.key

右连接

B包含A

SELECT <select_list>FROM A RIGHT JOIN B ON A.key=B.key

内连接

AB共有

SELECT <select_list>FROM A INNER JOIN B ON A.key=B.key

A不包含B

A独有

SELECT <select_list> FROM A LEFT JOIN B ON A.key=B.key WHERE B.key is null;

B不包含A

B独有

SELECT <select_list>FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key is null;

全连接

全连接

-- Oracle
SELECT <select_list> FROM A FULL JOIN B ON A.key=B.key
-- MySQL
SELECT <select_list> FROM A LEFT JOIN B ON A.key=B.key
UNION
SELECT <select_list>FROM A RIGHT JOIN B ON A.key=B.key

全连接排除交叉

全连接排除交叉

-- Oracle
SELECT <select_list>FROM A FULL JOIN B ON A.key=B.key WHERE A.key is null OR B.key is null;
-- MySQL
SELECT <select_list> FROM A LEFT JOIN B ON A.key=B.key and B.key is null
UNION
SELECT <select_list>FROM A RIGHT JOIN B ON A.key=B.key and A.key is null

2.4 子查询

/* select后面：
		仅仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		行子查询
	exists后面（相关子查询）
		表子查询
*/

2.5 联合查询

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点： 1、要求多条查询语句的查询列数是一致的！取第一列名 2、要求多条查询语句的查询的每一列的类型和顺序一致 3、union关键字默认去重，如果使用union all 可以包含重复项

SELECT id,name FROM test1 WHERE sex='男'
UNION ALL
SELECT tid,tname FROM test2 WHERE sex='male';

三、DML 语句

3.1 INSERT 语句

-- 插入全部列
INSERT INTO person(id,name,sex,phone)
VALUES(1,'张三','女','1388888888');
INSERT INTO person
VALUES(1,'张三','女','1388888888');
-- 插入不可为Null的值
INSERT INTO person(id,name)
VALUES(1,'张三');
-- 插入多行
INSERT INTO person
VALUES(1,'张三','女','1388888888'),(1,'张三','女','1388888888');

-- SET插入 不可插入多行
INSERT INTO person
SET id=1,name='张三',sex='女',phone='1388888888';

-- 查询结果集插入
INSERT INTO person
SELECT id,boyname,sex,phone
FROM boy WHERE id<3;

3.2 UPDATE 语句

-- 修改单表的记录
update person
set sex='男'...
where id > 3;

-- 修改多表
update person p
inner|left|right join nation n
on p.n_id = n.guid
set sex = '男'
where p.id > 5;

3.3 DELETE 语句

-- 单表的删除
delete from person where id < 2;

-- 多表删除
delete a,b
from person a
inner|left|right join nation b on p.n_id = n.guid
where id > 5;

-- 清空表 效率高 【没有返回值、事物不能回滚、自增从1开始】
truncate table person;

四、DDL 语句

4.1 库的管理

-- 创建库db
CREATE DATABASE IF NOT EXISTS db ;
-- 库的修改(programData下文件夹修改，不建议)
-- 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
-- 库的删除
DROP DATABASE IF EXISTS db;

4.2 表的管理

4.2.1 数据类型

类型	字节数	范围
tinyint	1	-128~127
smallint	2	-32768~32767
mediumint	3	-8388608~8388607
int	4	-2147483648~2147483647
bigint	8	-9223372036854775808~9223372036854775807
float	4	±1.75494351E-38~±3.402823466E+38
double	8	±2.2250738585072014E-308~
decimal(M,D)	M+2	±2.2250738585072014E-308~
char(M)	M字符	(不可变长度) M为0~255之间的整数
varchar(M)	M字符	(可变长度) M为0~65535之间的整数
tinytext	256
text	65535	64kb
mediumtext	16777215	16MB
longtext	4294967295	4GB
blob	同text
date	4	2018-10-10
time	3	38:59:59
year	1	2018
datetime	8	1000-9999
timestamp	4	1970-2038

约束:

NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)
FOREIGN KEY 外键
CHECK 检查约束
DEFAULT 默认值

-- 创建表 通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

-- 修改表名
ALTER TABLE book RENAME TO book1;
-- 修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
-- 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
-- 添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE; 
-- 删除列
ALTER TABLE book DROP COLUMN annual;

-- 删除表
DROP TABLE IF EXISTS book;

-- 复制表结构
CREATE TABLE copy_book LIKE book;
-- 复制表的结构+数据
CREATE TABLE copy_book SELECT * FROM book;
-- 复制表的结构+部分数据
CREATE TABLE copy_book SELECT * FROM book where id < 10;
-- 仅仅复制某些字段
CREATE TABLE copy_book SELECT id,name FROM author where id < 10;

五、存储过程及函数

5.1 变量

系统变量

-- 系统变量
-- 查看所有系统变量|会话变量
-- show global|【session】variables;
-- 查看满足条件的部分系统变量|会话变量
-- show global|【session】 variables like '%char%';
-- 查看指定的系统变量|会话变量的值
-- select @@global|【session】系统变量名;
-- 为某个系统变量|会话变量赋值
-- 方式一：
-- set global|【session】系统变量名=值;
-- 方式二：
-- set @@global|【session】系统变量名=值;

自定义变量

-- 用户变量：作用域：针对于当前会话（连接）有效，作用域同于会话变量
SET @num:=1;
SELECT @num;
-- 局部变量：作用域：仅仅在定义它的begin end块中有效
-- DECLARE num INT 【DEFAULT 0】;
SET num:=1;

5.2 函数

有且仅有1 个返回，适合做处理数据后返回一个结果

-- DELIMITER $
-- CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
-- BEGIN
-- 	DECLARE SUM FLOAT DEFAULT 0;
--	SET SUM=num1+num2;
--	RETURN SUM;
-- END $

-- SELECT test_fun1(1,2)$

5.3 存储过程

可以有0个返回，也可以有多个返回值

-- CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
-- BEGIN
--	DECLARE result INT DEFAULT 0;
	
--	SELECT COUNT(*) INTO result
--	FROM admin
--	WHERE admin.username = username
--	AND admin.password = PASSWORD;
	
--	SELECT IF(result>0,'成功','失败');
--END $

-- 调用
-- CALL myp4('张飞','8888')$

5.4 流程控制

5.4.1 case结构

情况1：类似于switch case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end

情况2： case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end

5.4.2 if结构

if 条件1 then 语句1; elseif 条件2 then 语句2; .... else 语句n; end if;

5.4.3 循环结构

iterate类似于 continue，继续，结束本次循环，继续下一次 leave 类似于 break，跳出，结束当前所在的循环

while：先判断再循环【标签:】while 循环条件 do 循环体; end while【标签】;
loop：模拟死循环【标签:】loop 循环体; end loop 【标签】;
repeat：先执行再判断【标签：】repeat 循环体; until 结束循环的条件 end repeat 【标签】;

王海峰 / MySQL

MySQL

一、SQL 简介

1、DB：数据库，保存一组有组织的数据的容器

2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据

3、SQL:结构化查询语言，用于和DBMS通信的语言

二、DQL 语句

+号的使用

2.1 单行函数

2.1.1 字符函数

2.1.2 数学函数

2.1.3 日期函数

2.1.4 流程控制函数

2.1.5 其他函数

2.2 分组函数

2.3 连接查询(SQL99语法)

2.4 子查询

2.5 联合查询

三、DML 语句

3.1 INSERT 语句

3.2 UPDATE 语句

3.3 DELETE 语句

四、DDL 语句

4.1 库的管理

4.2 表的管理

4.2.1 数据类型

五、存储过程及函数

5.1 变量

5.2 函数

5.3 存储过程

5.4 流程控制

5.4.1 case结构

5.4.2 if结构

5.4.3 循环结构

简介

发行版

贡献者

近期动态

王海峰 / MySQL .gitee-modal { width: 500px !important; }

MySQL

一、SQL 简介

1、DB：数据库，保存一组有组织的数据的容器

2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据

3、SQL:结构化查询语言，用于和DBMS通信的语言

二、DQL 语句

+号的使用

2.1 单行函数

2.1.1 字符函数

2.1.2 数学函数

2.1.3 日期函数

2.1.4 流程控制函数

2.1.5 其他函数

2.2 分组函数

2.3 连接查询(SQL99语法)

2.4 子查询

2.5 联合查询

三、DML 语句

3.1 INSERT 语句

3.2 UPDATE 语句

3.3 DELETE 语句

四、DDL 语句

4.1 库的管理

4.2 表的管理

4.2.1 数据类型

五、存储过程及函数

5.1 变量

5.2 函数

5.3 存储过程

5.4 流程控制

5.4.1 case结构

5.4.2 if结构

5.4.3 循环结构

简介

发行版

贡献者

近期动态

搜索帮助

王海峰 / MySQL