mysql查漏补缺

基本

1.+号的作用

在java中的+号

  • 运算符，两个数都是数值型
  • 连接符，只要其中一个操作数是字符串
    mysql中
    1.两个数都是数值，则作加法运算
    2.只有有一个为字符串，则试图将字符串转为数值型。转换成功，继续加法。
    3.转换失败，则将字符串转成数值0
    4.只要一方为null，则结果为null
select 1+2;//3
select 1+"2";3
select 1+"a";//1
select null+1;null

2.concat的用法

SELECT
    CONCAT(username, `password`)
FROM
    user_info
WHERE
    username = 'admin1'
//admin1123456

可以用在from后面\where\order by 等场景

3.安全等于

<=>可以判断null(还是用来判断等于的)

SELECT
    CONCAT(username, `password`)
FROM
    user_info
WHERE
    expired <=> null

4.常用单行函数

字符函数

  • length(单位字节) CHAR_LENGTH
  • concat
  • upper lower
  • substr substring
  • instr
  • trim() trim( x FROM string)两边去空或指定字符
  • lpad 用指定字符左填充到到指定长度 rpad
  • replace

字符函数

  • round 四舍五入
  • ceil flloor
  • truncate 截断
  • mod取余（规则：余数符合看被除数的符合）

日期函数

  • now 返回当前系统日期+时间
  • curdate 只返回日期
  • curtime 只返回时间
  • 可以获取指定的部分、年、月、日、时、分、秒 。
    STR_TO_DATE（可以随意变换时分秒的位置）/DATE_FORMAT
SELECT STR_TO_DATE('21,5,2018','%d,%m,%Y');//2018-05-21

流程控制函数

  • if-else
select if(10>5,'大','小')
  • case,类型java中的switch-case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...

5.分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

注意事项

  1. sum\avg一般用于数值型
  2. 计算过程中会忽略null，即对avg来说不会算作分母
  3. 可以和distinct配合去重

5.where和having

sql中的筛选分为两类

数据源 位置 关键字
原始表 group by子句前面 where
分组后的结果集 group by 子句后面 having

6.笛卡尔积现象

原因：多表间没有添加有效的连接条件

7.sql92标准连接

  1. 等值连接


    image.png

  2. 非等值连接


    image.png

    工资表和等级标准表连接，每一条工资对应所有的标注，但通过between筛选后。只会剩下一条对应的等级标准

  3. 自连接


    image.png

8.sql99-内连接

  1. 等值连接


    image.png

  2. 非等值连接


    image.png

    3.自连接


    image.png

9.交叉连接

image.png

就是另一种笛卡尔乘积的实现方式

10.各种连接总结

image.png

image.png

全外：mysql不支持
image.png

11. 子查询

 分类：
按子查询出现的位置：
    select后面：
        仅仅支持标量子查询   
    from后面：
        支持表子查询
    where或having后面：★
        标量子查询（单行） √
        列子查询  （多行） √    
        行子查询        
    exists后面（相关子查询）
        表子查询
按结果集的行列数不同：
    标量子查询（结果集只有一行一列）
    列子查询（结果集只有一列多行）
    行子查询（结果集有一行多列）
    表子查询（结果集一般为多行多列）

12.where或having后面

1.标量子查询（单行单列）
一般配合操作符

SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'

);

2.列子查询（多行单列）
一般配合in

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'

)

3.行子查询（结果集一行多列或多行多列）了解

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

用的不多，也可以通过两次查询得出

SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees


)AND salary=(
    SELECT MAX(salary)
    FROM employees

);

13.select后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,(

    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

14.from后面的子查询

将子查询结果充当一张表，要求必须起别名

15 exists和in

in
确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。
因为一旦内表大，查询次数也会扩大很多倍。

exists
参考：https://www.cnblogs.com/emilyyoucan/p/7833769.html

指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
exists()适合内表比外表数据大的情况
为什么？
内表1000w条，外表10条。执行1次exists查1000w条数据。但如果是in，笛卡尔机会有10000w条。相当于：一个是hash相连，一个是2层循环

对子表而言，in不走索引，exists可以走索引

not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

16.联合查询

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

17.两种插入方式

  1. values
insert into 表名(列名,...) values(值1,...);
  1. set
insert into 表名
set 列名=值,列名=值,...

18.表的复制

  1. 仅仅复制表的结构
CREATE TABLE copy LIKE author;
  1. 复制表的结构+数据
CREATE TABLE copy2 
SELECT * FROM author;
  1. 只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author 
WHERE nation='中国';
  1. 仅仅复制某些字段
CREATE TABLE copy4 
SELECT id,au_name
FROM author
WHERE 0;

19.约束类型

  1. NOT NULL：非空，用于保证该字段的值不能为空
    比如姓名、学号等
  2. DEFAULT:默认，用于保证该字段有默认值
    比如性别
  3. PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    比如学号、员工编号等
  4. UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
    比如座位号
  5. CHECK:检查约束【mysql中不支持】
    比如年龄、性别
  6. FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
    在从表添加外键约束，用于引用主表中某列的值
    比如学生表的专业编号，员工表的部门编号，员工表的工种编号

20.添加约束

约束的添加分类：

  1. 列级约束：定义表时，对列进行约束
    六大约束语法上都支持，但外键约束没有效果
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL UNIQUE,#非空
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT  18,#默认约束
    majorId INT REFERENCES major(id)#外键

);
  1. 表级约束：表都定义完了，再添加约束
    除了非空、默认，其他的都支持
/*

语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
    
);

21.修改表时添加约束

  1. 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

示例

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
  1. 添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

示例

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

进阶

1. 查看事务级别

select @@tx_isolation
image.png

2.回滚点

savepoint，设置保存点

#3.演示savepoint 的使用
SET autocommit=0;

START TRANSACTION;

DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点


SELECT * FROM account;

3.试图更新特点

具备以下特点的视图不允许更新

  1. 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
  2. 常量视图
  3. Select中包含子查询
  4. join
  5. from一个不能更新的视图
  6. where子句的子查询引用了from子句中的表
    总结
    试图本质就是一次查询，，数据都是由多张表组合形成。我们要想插入，mysql必须确保能准备的定位到插入的数据对应到哪种表的哪个字段。如果都可以定位到，那就可以更新（增加和删除）

4.变量

  1. 系统变量：

    • 全局变量
    • 会话变量

  2. 自定义变量：

    • 用户变量
    • 局部变量

5.系统变量

.全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

.会话变量

作用域：针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

总结:

  1. 系统变量按作用域划分
  2. 默认session级别，需要再前缀@@session.(@@global.)
  3. 都有两个@

6.自定义变量

说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）

用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量


#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
    SET @变量名=值;
    SET @变量名:=值;
    SELECT @变量名:=值;
#方式二：
    SELECT 字段 INTO @变量名
    FROM 表;
#③使用（查看变量的值）
SELECT @变量名;

局部变量

用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话

#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;


#②赋值（更新变量的值）

#方式一：
    SET 局部变量名=值;
    SET 局部变量名:=值;
    SELECT 局部变量名:=值;
#方式二：
    SELECT 字段 INTO 具备变量名
    FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;

总结

  1. 局部变量没有@
  2. INTO也是赋值
#案例：声明两个变量，求和并打印

#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

7.存储过程

好处：

  1. 提高代码的重用性
  2. 简化操作

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程的格式
1、参数列表包含三部分

参数模式  参数名  参数类型
#举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter重新设置

delimiter 结束标记
案例：
delimiter $

调用语法

CALL 存储过程名(实参列表);

案例

#--------------------------------案例演示-----------------------------------
#1.空参列表
#案例：插入到admin表中五条记录

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $


#调用
CALL myp1()$

#2.创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    WHERE b.name=beautyName;
    

END $

#调用
CALL myp2('柳岩')$

#案例2 ：创建存储过程实现，用户是否登录成功

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 myp3('张飞','8888')$


#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyname INTO boyname
    FROM boys bo
    RIGHT JOIN
    beauty b ON b.boyfriend_id = bo.id
    WHERE b.name=beautyName ;
    
END $


#案例2：根据输入的女神名，返回对应的男神名和魅力值

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
    SELECT boys.boyname ,boys.usercp INTO boyname,usercp
    FROM boys 
    RIGHT JOIN
    beauty b ON b.boyfriend_id = boys.id
    WHERE b.name=beautyName ;
    
END $


#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$



#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$


#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

#四、查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE  myp2;

8.函数

区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

一、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

二、调用语法
SELECT 函数名(参数列表)

#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN

    DECLARE c INT DEFAULT 0;#定义局部变量
    SELECT COUNT(*) INTO c#赋值
    FROM employees;
    RETURN c;
    
END $

SELECT myf1()$


#2.有参有返回
#案例1：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal=0;#定义用户变量 
    SELECT salary INTO @sal   #赋值
    FROM employees
    WHERE last_name = empName;
    
    RETURN @sal;
END $

SELECT myf2('k_ing') $

#案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE ;
    SELECT AVG(salary) INTO sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name=deptName;
    RETURN sal;
END $

SELECT myf3('IT')$

#三、查看函数

SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

#案例
#一、创建函数，实现传入两个float，返回二者之和

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)$

9.触发器

参考：https://www.cnblogs.com/geaozhang/p/6819648.html

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 159,569评论 4赞 363
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,499评论 1赞 294
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,271评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,087评论 0赞 209
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,474评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,670评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,911评论 2赞 313
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,636评论 0赞 202
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,397评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,607评论 2赞 246
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,093评论 1赞 261
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,418评论 2赞 254
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,074评论 3赞 237
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,092评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,865评论 0赞 196
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,726评论 2赞 276
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,627评论 2赞 270

推荐阅读更多精彩内容

  • oracle错误集合
    ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 O...
    我想起个好名字阅读 4,867评论 0赞 9
  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,617评论 0赞 44
  • mysql笔记(转)
    今天看到一位朋友写的mysql笔记总结，觉得写的很详细很用心，这里转载一下，供大家参考下，也希望大家能关注他原文地...
    信仰与初衷阅读 4,699评论 0赞 30
  • MYSQL
    作者：烨竹 数据库的基本操作 登陆MySQL数据库服务：mysql -h服务器地址 -P端口号 -u用户名 ...
    DragonRat阅读 1,317评论 0赞 7
  • 三行情诗
    毕生所愿不过是 一段余生 一个你
    广电1702陈永凡阅读 82评论 0赞 1