Mysql进阶整理



Mysql基础

数据定义语言DDL(Data Definition Language)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
对象: 数据库和表

关键词: create alter drop truncate(删除当前表再新建一个一模一样的表结构)

创建数据库:create database school;

删除数据库:drop database school;

切换数据库:use school;

创建表:create table student
    id int(4) primary key auto_increment
    name varchar(20)
      score int(3)

  );

查看数据库里存在的表:show tables;

数据操纵语言DML(Data Manipulation Language) 

1.插入 INSERT

1
2
3
4
5
6
7
8
9
10
11
-- 单行插入
INSERT INTO test(id, NAME)
value(16, 'g');

-- 插入且支持子查询
INSERT INTO test(id, NAME)
select 16, 'g';

-- 批量插入
INSERT INTO test values(17, 'g'),
(18, 'h');

2.修改 UPDATE

1
2
3
4
5
6
7
8
9
10
11
-- 单表修改
UPDATE test SET `NAME` = 'coderblue'
WHERE ID = 1;

-- 多表修改
UPDATE c_ins_project p
INNER JOIN c_ins_project_parnet pa
SET p.`PROJECTNAME` = 'coderblue'
WHERE
p.PARENTID = pa.ID
AND p.ID = '202014100130'

3.删除

1
2
3
4
5
-- 删除表数据
DELETE FROM test WHERE `NAME` like '%a%';

-- 删除表数据,再插入数据后自增长列的值从1开始。
TRUNCATE TABLE test;

数据查询语言DQL(Data Query Language)

1.子查询

  • where后面的列子查询

    1
    2
    3
    4
    5
    -- 等值连接更优
    SELECT p.ID FROM c_ins_project p
    WHERE p.PARENTID IN (
    SELECT pa.ID FROM c_ins_project_parnet pa
    );
  • where后面的行子查询

    1
    2
    3
    4
    5
    -- 查工程名称长度最大的工程id
    SELECT p.ID FROM c_ins_project p
    WHERE LENGTH(p.PROJECTNAME) = (
    SELECT MAX(LENGTH(pr.PROJECTNAME)) FROM c_ins_project pr
    );
  • select后面的子查询

    1
    2
    3
    4
    5
    SELECT
    p.ID,
    ( SELECT count( * ) FROM c_ins_project_parnet pa ) 项目表总数
    FROM
    c_ins_project p;
  • from后面的子查询

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    p.ID,
    p.PROJECTNAME,
    pa.ID parentId,
    p.PARENTID
    FROM
    ( SELECT pa.ID FROM c_ins_project_parnet pa ) pa
    INNER JOIN c_ins_project p ON pa.id = p.PARENTID
  • exists后面的子查询

    1
    2
    3
    4
    5
    6
    7
    SELECT
    dep.depid,
    dep.depname
    FROM
    department dep
    WHERE
    EXISTS ( SELECT * FROM employee emp WHERE emp.depid = dep.depid )

count函数

1.count(1) 和 count(*) 和 count(字段)区别

count(*):扫描全表,但不取值,读到的每一行都是1,判断不可能是null,按值累加。(做了专门的优化)

count(1):INNODB存储引擎下,和count(*)的效率差不多,比count(字段)要高一些

​ count(1)执行速度比count(主键 id)快的原因:从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。

count(字段):扫描全表,判断字段可空,拿出该字段所有值,判断每一个值是否为空,不为空则累加

分组函数

1.Group By函数

1
2
3
4
5
6
7
8
9
10
11
-- 全都是函数
select count(*), LENGTH(PROJECTNAME) FROM c_ins_project p
GROUP BY LENGTH(PROJECTNAME);

-- 使用 any_value() 分组查出id信息,单独查询时写 LENGTH(PROJECTNAME), id,然后又不Group by id 就会报错this is incompatible with sql_mode=only_full_group_by
select count(*), LENGTH(PROJECTNAME), any_value(ID) FROM c_ins_project p
GROUP BY LENGTH(PROJECTNAME);

-- 根据PROJECTNAME分组
select count(*),any_value(ID),PROJECTNAME FROM c_ins_project p
GROUP BY PROJECTNAME;

数据控制语言DCL(Data Control Language)

1.事务

查看mysql引擎默认MyIsam,不支持事务,建议在配置文件里更改成InnoDB,如果还是没用就考虑是不是表的引擎没用及时刷新过来。

1
2
3
4
5
6
-- 查看mysql引擎
show ENGINES;
-- 更改表的引擎(可选)
alter table test engine=InnoDB;
-- 查看当前会话是否自动提交
show VARIABLES like 'autocommit';

演示事务 和 事务回滚

1
2
3
4
5
6
7
8
9
10
11
# 关闭当前会话自动提交, set 1 就是自动提交
SET autocommit = 0;
# 开启事务
START TRANSACTION;
# 编写一组事务的语句
UPDATE test SET name = 'coderblue1' WHERE ID = 1;
UPDATE test SET name = 'tom1' WHERE ID = 2;
# 结束事务
COMMIT;
# 或者 事务回滚
ROLLBACK;

回滚到保存点SAVEPOINT

1
2
3
4
5
6
7
8
9
10
11
# 关闭自动提交, set 1 就是自动提交
SET autocommit = 0;
# 开启事务
START TRANSACTION;
# 编写一组事务的语句
UPDATE test SET name = 'coderblue' WHERE ID = 1;
# 设置保存点
SAVEPOINT a;
UPDATE test SET name = 'tom' WHERE ID = 2;
# 回滚到保存点
ROLLBACK TO a;

视图

视图创建语法:可以将经常复用的查询结果集作视图

1
2
3
CREATE VIEW 视图名  
AS
查询语句;

查看视图

1
2
3
4
5
6
7
8
-- 查看视图数据
select * from 视图名;

-- 查看视图结构信息:包括视图名、创建视图语法、视图编码
SHOW CREATE VIEW 视图名;

-- 查看视图的字段信息
DESC 视图名;

视图修改

1
2
3
4
5
6
7
8
9
10
-- 方式一
CREATE
OR REPLACE VIEW 视图名
AS
查询语句;

-- 方式二
ALTER VIEW 视图名
AS
查询语句;

视图删除

1
DROP VIEW 视图名;

视图和表的区别

创建语法的关键字 是否实际占用物理空间
视图 create view 没有
create table 占用

Mysql进阶

变量

1.用户变量

定义和使用的位置可以是会话中的任何地方

  • 声明变量:通过 SET 或 SELECT
1
2
3
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
  • 赋值(更新用户变量的值)
1
2
3
4
5
6
7
8
-- 方式一:通过 SET 或 SELECT
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

-- 方式二:通过SELECT INTO
SELECT 字段 INTO 变量名
FROM 表;
  • 查看用户变量的值
1
SELECT @变量名;

2.局部变量

作用域:仅仅在定义它的begin end中有效

  • 声明
1
2
DECLARE 局部变量名 类型;
DECLARE 局部变量名 类型 DEFAULT 值;
  • 赋值
1
2
3
4
5
6
7
8
-- 方式一:通过 SET 或 SELECT
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;

-- 方式二:通过SELECT INTO
SELECT 字段 INTO 局部变量名
FROM 表;
  • 查看局部变量的值
1
SELECT 局部变量名;

3.对比用户变量和局部变量

作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号,不用限定类型
局部变量 BEGIN END中 只能在BEGIN END中,且为第一句话。 一般不用加@符号,需要限定类型

函数

  • 创建函数
1
2
3
4
5
6
7
8
-- 案例1:统计test表的行数
CREATE DEFINER=`root`@`localhost` FUNCTION `countT`() RETURNS int(11)
BEGIN
DECLARE count INTEGER DEFAULT 0;
SELECT COUNT(*) INTO count
FROM test;
RETURN count;
END
  • 查看函数
1
SHOW CREATE FUNCTION 函数名;
  • 删除函数
1
DROP FUNCTION 函数名;

存储过程

  • 创建语法
1
2
3
4
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END

注意:

1、参数列表包含三部分:参数模式 参数名 参数类型

例如:IN stuName VARCHAR(20)

2、参数模式

IN:该参数可以作为输入,也就是该参数需要调用方传入值

OUT:该参数可以作为输出,也就是该参数可以作为返回值

INOUT:该参数既可以作为输入又可以作为输出

3、如果存储过程体仅仅只有一句话,BEGIN END可以省略存储过程体中的每条SQL语句的结尾要求必须加分号。存储过程的结尾可以使用 DELIMITER 重新设置。

语法:DELIMITER 结束标记

举例:DELIMITER $

  • 空参的存储过程
1
2
3
4
5
6
7
8
CREATE DEFINER=CURRENT_USER PROCEDURE `myp1`()
BEGIN
-- 单行的sql语句结尾必须加分号;
INSERT INTO test(`name`) VALUES('name2'), ('name3'), ('name4');
END

-- 调用
CALL myp1();
  • 创建带in模式参数的存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 案例1:创建存储过程实现根据name,查询对应的信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp2`(IN selectId INT)
BEGIN
SELECT * FROM test WHERE id=selectId;
END

-- 案例二:创建存储过程实现,查看此用户是否存在
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp3`(IN selectId INT, IN selectName VARCHAR(64))
BEGIN
-- 声明并初始化
DECLARE result VARCHAR(20) DEFAULT '';
-- 赋值
SELECT COUNT(*) INTO result
FROM test
WHERE test.id=selectId AND test.`name`=selectName;
-- 使用,回显值
SELECT result;
END
  • 创建带out模式参数的存储过程
1
2
3
4
5
6
7
8
9
10
11
12
-- 案例1:根据id,返回对应的name
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp4`(IN selectId INT, out outName VARCHAR(64))
BEGIN
-- 赋值
SELECT test.`name` INTO outName
FROM test
WHERE test.id=selectId;
END

-- 调用
CALL myp4(2, @name);
-- 查看值 SELECT @name;
  • 创建带inout模式参数的存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 案例1:简单赋值
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp6`(INOUT a INT, INOUT b INT)
BEGIN
SET a = a * 2;
SET b = b * 3;
END

-- 调用
SELECT @a:=1;
SELECT @b:=2;
CALL myp6(@a, @b);

-- 案例2:输入id查询然后返回对应的name
CREATE DEFINER=`root`@`localhost` PROCEDURE `myp5`(INOUT val VARCHAR(64))
BEGIN
SELECT test.name INTO val
FROM test
-- CONVERT(val,SIGNED):将入参转成int类型
WHERE test.id=CONVERT(val,SIGNED);
END
  • 删除存储过程
1
DROP PROCEDURE 存储过程名;
  • 查看存储过程
1
SHOW CREATE PROCEDURE 存储过程名;
  • 条件语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- case语句:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin
declare var int;
set var=parameter+1;
case var
when 0 then
set var=var*4;
when 1 then
set var=var/2;
else
set var=var&&3;
end case;
-- 输入var值
SELECT var;
end

-- if-then-else语句:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin
declare var int;
set var=parameter+1;
if var=0 then
set var=var*4;
else
SELECT now();
end if;
-- 输入var值
SELECT var;
end
  • 循环语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- while ···· end while
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin
declare var int;
SET var := parameter;
WHILE var<6 DO
set var = var * 2;
END WHILE;
SELECT var;
end

-- repeat···· end repeat:它在执行操作后检查结果,而 while 则是执行前进行检查。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin
DECLARE var int;
SET var := parameter;
REPEAT
SET var = var * 2;
UNTIL var > 8
END REPEAT;
SELECT var;
end

-- loop ·····endloop:loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`(in parameter int)
begin
DECLARE var int;
SET var := parameter;
-- LABLES 标号,相当于break,可以结束循环
loop_lable:LOOP
SET var = var * 3;
IF var > 8 then
LEAVE loop_lable;
END IF;
END LOOP;
SELECT var;
end

-- ITERATE 通过引用复合语句的标号,跳出本次循环:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc8`(in v int)
begin
LOOP_LABLE:loop
if v=3 then
set v=v+1;
ITERATE LOOP_LABLE;
end if;
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y');
set v=v+1;
if v>=5 then
-- 相当于continue
leave LOOP_LABLE;
set v=v+100;
end if;
end loop;
SELECT v;
-- 输入5,返回v=6,和DATE_FORMAT(NOW(),'%m-%d-%Y')值
end
  • 批量插入案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
* loopCount:循环次数条件
* inName:拼接的name
*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc20`(in loopCount int, in inName VARCHAR(64))
begin
DECLARE idVal BIGINT;
DECLARE param int DEFAULT 0;
DECLARE nameVal VARCHAR(64);
WHILE param < loopCount DO
-- 当前时间戳 + 秒级别时间戳
SET idVal = CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'), SUBSTRING(UNIX_TIMESTAMP(), 9, 10), CRC32(CONCAT(NOW(),RAND())) % 100000000);
SET nameVal = CONCAT(param*param, inName, param);
INSERT INTO test(ID, `NAME`) VALUES(idVal, nameVal);
SET param=param+1;
END WHILE;
end

错误集

mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by

报错信息:

1
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因:

MySQL版本问题,有的版本默认开启了ONLY_FULL_GROUP_BY,所以导致了一些SQL无法正常执行,其实是因为group by 之后,返回的一些数据是不确定的,所以才会出现这个错误。

解决方法(两种):

  1. 使用聚合函数,例如:ANY_VALUE(column_name)

  2. 设置数据库,关闭ONLY_FULL_GROUP_BY。

    1
    2
    3
    4
    编辑my.cnf / my.ini 文件,找到sql-mode的位置,去掉ONLY_FULL_GROUP_BY,重启MySQL服务;
    如my.cnf中没有sql-mode,需要加入以下:

    sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启MySQL服务,查询值,已经更改

1
select @@global.sql_mode

mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by

报错:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column

Error Code: 1093. You can’t specify target table ‘a’ for update in FROM clause

使用子查询的表进行update操作就报以上错误,因为mysql不支持where里面使用子查询

1
2
3
4
5
6
7
8
update student, 
-- 子查询出来的数据做为 b表
(SELECT s.Ssex,s.SId FROM `student` s, `sc` c
where s.SId = c.CId
and c.SId = '01') b
set student.Ssex = '女'
-- 然后利用b的sid = 学生表的sid找到符合b表的所有数据
where student.SId = b.SId

1064错误,delete语句中 表名不能用别名

1
DELETE FROM tableName  t where t......;

报1064 错误,原因MySQL 中delete 语句不能给表名起别名
另外。如果记录不存在,delete from 不会报错!

打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020 coderblue    湘ICP备20003709号

请我喝杯咖啡吧~

支付宝
微信