MySQL基础

MySQL

MySQL

大约 30 分钟

一、SQL查询语句执行顺序

(1)from 
(3)join 
(2)on 
(4)where 
(5)group by(只有group的分组字段或者聚合函数，才能出现在select后面)
(6)having(having一般在分组后配合聚合函数使用) -- 对分组后的数据进行过滤，可使用 select 后的查询出的字段
(7)select 
(8)distinct 
(9)order by 
(10)limit

二、基本函数的使用

1、字符相关处理

1)concat连接函数

注意：oracle的cancat函数只能连接两个字符串

concat('字符串1','字符串2',变量3,...)

#张三你好3
SELECT CONCAT("张三","你","好",3) as demo;

通过我们使用模糊查询采用

select * from employee where name like concat('%',"张三",'%')

2) upper/lower/ucase/lcase 大小写转换

SELECT upper('chINese') as 大写,lower('chINese') as 小写;    # CHINESE   chinese
SELECT ucase('chINese') as 大写,lcase('chINese') as 小写;    # CHINESE   chinese

3)length 字符的字节数

# 5
SELECT LENGTH('hello')
# 6
SELECT LENGTH('你好')

4)substr 字符串截取

索引从1开始

#从索引为3位置的开始截取到结尾
# hello world
SELECT substr('你好hello world',3)

#从索引为3的位置截取5个字符
# hello
SELECT substr('你好hello world',3,5)

5)instr判断子串出现的索引

索引从1开始

# 5
SELECT INSTR('你好hello world','l')

6)trim去除前后空格

#去除前后空格
SELECT TRIM('       你好       ');

#从字符串中取出前后为a的字符
# 你aa好
SELECT TRIM('a' from 'aaaaa你aa好aaaaa');

7)lpad/rpad填充字符

# 向左填充字符，填充的字符和原来的字符相加的字符长度为8
#  ++++++你好
SELECT lpad('你好',8,'++')

# 向右填充字符，填充的字符和原来的字符相加的字符长度为4
#  你好##
SELECT rpad('你好',4,'--')

8)replace 替换字符串

replace(T1,T2,T3) 将T1中的T2字符串替换为T3

# hello，hello，hello，世界!!
SELECT replace('你好，你好，你好，世界!!','你好','hello')

9)substring_index 按索引指定字符切割字符串

SUBSTRING_INDEX('aaa-bbb-ccc','-','1')
===> aaa

2、convert小数格式化

1)decimal(P,D)函数的使用

P是表示有效数字数(从左边第一个非0开始)的精度。 P范围为1〜65。
D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。
当直接使用decimal时，不包含小数，并且只保留整数，且有效数字P为10
保留小数是四舍五入的形式

例如:

amount DECIMAL(6,2);  
amount列最多可以存储6位数字，小数位数为2位; 因此，amount列的范围是从-9999.99到9999.99。

测试：

#2312
SELECT convert(2312.335,DECIMAL) as 结果

#2312.34
SELECT convert(2312.335,DECIMAL(6,2)) as 测试

3、ifnull 判断空并赋值

ifnull(T1,T2)
- 若T1为null则输出T2，否则输出T1

#若emp_name为空，则输出第二个参数的值，否则输出emp_name列的值
SELECT id,IFNULL(emp_name,'无') as 姓名 from employee

4、日期相关处理

1)日期格式化参数缩写

%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

2)日期名称解释

HOUR	小时
MINUTE	分
SECOND	秒
MICROSECOND	毫秒
YEAR	年
MONTH	月
DAY	日
WEEK	周
QUARTER	季
YEAR_MONTH	年和月
DAY_HOUR	日和小时
DAY_MINUTE	日和分钟
DAY_ SECOND	日和秒
HOUR_MINUTE	小时和分
HOUR_SECOND	小时和秒
MINUTE_SECOND	分钟和秒

3)常见时间获取及处理

--  2020-07-25 23:15:31 返回当前系统的 日期+时间
SELECT now();

--  2020-07-25 返回当前系统的 日期
SELECT CURDATE();

--  23:16:05 返回当前系统的 时间
SELECT CURTIME();

--  获取 年 
SELECT year(NOW());
SELECT year('2011-1-1');
--  获取 月
SELECT MONTH(NOW());
--  July 获取月 对应的英文
SELECT MONTHNAME(NOW());
-- 获取 日
select DAY(NOW())
-- 获取 时
select HOUR(NOW())
-- 获取 分
select minute(NOW())
-- 获取 秒
select second(NOW())

-- 查询当前日期和时间 2019-05-28 13:42:29
select sysdate()

-- 20190528141522
SELECT NOW()+0;

-- 查询指定日期属于一年中的第几周  21
SELECT week("2019-05-28 13:42:29")

-- 查询指定日期的 年份 2019
SELECT YEAR("2019-05-28 13:42:29"); 

-- 查询指定日期的 月份 5
SELECT MONTH("2019-05-28 13:42:29");
SELECT MONTHNAME("2019-05-28 13:42:29");  #月份 May

-- 查询指定日期的 号 28
SELECT day("2019-05-28 13:42:29"); 
SELECT HOUR("2019-05-28 13:42:29");  #时 13
SELECT minute("2019-05-28 13:42:29"); #分 42
SELECT SECOND("2019-05-28 13:42:29") #秒 29

-- 格式化日期  2019/05/28 14:05:25
SELECT DATE_FORMAT(now(),"%Y/%m/%d %H:%i:%s")

-- 日期相隔的天数  第一个日期减第二个日期 (格式必须相同) 符合年月日规范
SELECT DATEDIFF("2019-08-01","1997-08-06")  #8030

-- 时间相隔 第一个时间减第二个时间 (格式必须相同) 符合时分秒规范
SELECT TIMEDIFF("15:44:44","13:04:44")

-- 给日期加上指定间隔 INTERVAL 10 YEAR 加十年
-- 给日期减去指定间隔 INTERVAL -10 YEAR 减十年
SELECT ADDDATE("2019-05-28 13:42:29",INTERVAL -10 SECOND)

-- 给日期加上指定天数 只能加天数 负数表示减
SELECT ADDDATE("2019-05-28 13:42:29",2);

-- 给指定日期时间
SELECT ADDTIME("2017-03-03 13:42:29","2 1:11:11"); #2017-03-05 14:53:40

-- 加秒
SELECT ADDTIME('23:59:59', 55);

-- 直接对日期任何部分进行操作  加+10小时 -10表示减10小时
SELECT "2019-05-28 13:42:29" + INTERVAL 10 SECOND

-- 减10年
SELECT "2019-05-28 13:42:29" + INTERVAL -10 year

4)格式化日期

①字符格式化为日期

查询日期为2019-9-9，但是需要将不规范的日期格式转换为对应的时间日期格式

SELECT * from employee where birthday = str_to_date('9-9 2019','%m-%d %Y')

②日期格式化为字符

# 将2019-09-09 转换为 2020年09月09日
SELECT id,DATE_FORMAT(birthday,'%Y年%m月%d日') from employee

5)日期间隔差值计算

日期相减 DATEDIFF(T1,T2) T1减去T2，得到相隔的天数

# 1822
SELECT DATEDIFF("2020-08-01","2015-08-06")

时间相减 TIMEDIFF(T1,T2) T1减去T2,得到相隔时分秒

# 02:40:00
SELECT TIMEDIFF("15:44:44","13:04:44")

6)加减天数/时间

adddate() 增加指定天数

subdate() 减去指定天数

adddate(T1,T2) 给T1日期加上指定T2天数

#2019-05-30 13:42:29
SELECT adddate("2019-05-28 13:42:29",2);

ADDTIME(T1,T2) 给T1日期加上指定T2的天数和时间

#2017-03-05 14:53:40
SELECT ADDTIME("2017-03-03 13:42:29","2 1:11:11");

5、流程控制函数

1)if

if(T1,T2,T3) 若T1为真，输出T2，否则输出T3

-- 嘻嘻
SELECT if(10<3,'哈哈','嘻嘻');
-- 嘻嘻
SELECT if(10=3,'哈哈','嘻嘻');

2)case...when...then...else... end

if - else 结构： select case 列名 when 列值 then 列名/值 else 列名/值 end from 表名
等同于
select case when 列名='列值' then 列名/值 else 列名/值 end from 表名

SELECT case isbn when 'ISBN-001' then '001' else 'other' end as 备注 from book; 
SELECT case when isbn='ISBN-001' then '001' else 'other' end as 备注 from book;

swith-case 结构：着重强调判断相等 即 case T1 when T2 then ，判断T1变量或表达式是否等于常量T2

  SELECT 
	case isbn 
		when 'ISBN-001' then '001'
		when 'ISBN-002' then '002'
		when 'ISBN-003' then '003'
		when 'ISBN-004' then '004'
		when 'ISBN-005' then '005'
		else 'other'
  end as 备注 from book;

if-else if-else 结构 : case when 条件 then ，条件可以是随意的，例如 > < = 等判断

SELECT case 
     when price>100 then 'good'
	 when price=100 then 'normal'
	 when price<100 then 'bad' 
	 end as 备注
from book;

6、聚合函数

sum 求和，avg平均值，max最大值，min最小值，count计算个数

sum,avg 一般处理数值型 max,min,count可以处理任何类型
所有分组函数进行处理的时候都忽略null值

在聚合函数中，只能为空或者为null或者只能为数字。不能出现汉字和字符否则会出错

sum(null) ==> null
sum(0) ==> 0

7、多行合并 GROUP_CONCAT 函数

多行合并为一行
GROUP_CONCAT(列名,SEPARATOR 分隔符) 具体用法：https://www.cnblogs.com/coder-wzr/p/10102792.htmlopen in new window
将表中所有的Name名合并为一行一列，并以逗号分隔
SELECT
	GROUP_CONCAT(name SEPARATOR ',') AS all_name 
	FROM employee
根据Salary分组，将Name合并为一行
SELECT
	GROUP_CONCAT(name SEPARATOR ',') AS all_name 
	FROM employee GROUP BY Salary
根据Salary分组，将 Name1:Salary1|Name2:Salary2 的形式
SELECT
	GROUP_CONCAT( CONCAT_WS( ":", NAME, salary ) SEPARATOR '|' ) AS all_name 
FROM
	employee 
GROUP BY
	Salary
concat_ws(separator, str1, str2, ...)
以指定字符连接多个字符串

8、行转列

详见行转列：https://www.cnblogs.com/xiaoxi/p/7151433.htmlopen in new window

三、基本关键字的使用

1、between ... and ...

#查询hrid在3到5直接的id，包括3和5
SELECT * FROM `hr` where hrid BETWEEN 3 and 5;

等价于

SELECT * from hr where hrid>=3 and hrId<=5

2、distinct去重

#去除price重复的值
SELECT DISTINCT price from book

#去除isbn，price，book_name都不重复的值，即同时满足每一行的这三个值都不相同
SELECT DISTINCT isbn,price,book_name from book

单独的distinct，必须放在开头，否则报错，语法错误；但是与其它的函数一起使用的时候，没有位置限制。
可以去重null值和空值，当有多个null值和空值时，去重后只有一个null值和空值

3、group by多条件分组

#按照每一行的price，book_name都相同时分组，若只一个不同则视为不同行
SELECT book_name,price from book GROUP BY price,book_name

4、case when ... then ... else ... end行转列

SELECT
	student.sno,
	sname,
	sc.grade,
	course.cname
FROM
	student
LEFT JOIN sc ON student.sno = sc.sno
LEFT JOIN course ON sc.cno = course.cno

将学生按照学生的id和姓名进行分组，查询成绩的时候有选择的输出到列，将分组的成绩转换为对应列的成绩

SELECT student.sno,sname,
    #若当前查询cname列为数据库，则输出成绩列grade,否则输出为空
	max(case when cname='数据库' then grade else null end) as '数据库',
	max(case when cname='数学' then grade else null end) as '数学',
	max(case when cname='信息系统' then grade else null end) as '信息系统',
	max(case when cname='数据结构' then grade else null end) as '数据结构',
	max(case when cname='操作系统' then grade else null end) as '操作系统',
	#转换小数，将avg(grade)得到的平均成绩，保留5位有效数字，并且保留2位小数。
	convert(avg(grade),DECIMAL(5,2)) '平均成绩'
FROM
	student
LEFT JOIN sc ON student.sno = sc.sno
LEFT JOIN course ON sc.cno = course.cno
GROUP BY
	student.sno,
	sname

5、exists和not exists用法

exists 后面的查询若查的不为空值则返回该次查询，否则该条查询无效
not exists 后面的查询若为空，则返回此次查询。若不为空，则该条查询无效

#每次执行一次外部查询 都会执行子查询（会引用外部查询的值）是否返回结果集
SELECT * FROM b WHERE not EXISTS(SELECT * from a where a.id=b.id);

#查询选修了 1006课程的学生
SELECT sname from s where EXISTS(SELECT * from sc where s.sid=sc.sid and cid='1006')

#查询选修了课程的学生 
SELECT sname from s where EXISTS(select * from sc WHERE s.sid=sc.sid)
1  查询到 退出
5  没查询到  

#查询选修了全部课程的学生
SELECT sname from s where not EXISTS(select * from c where not exists(SELECT * from sc where s.sid=sc.sid and c.cid=sc.cid))	

#相当于三个for循环嵌套，都匹配返回，不匹配往下循环查找
1  1001  空 
1  1002  不为空
1  1003  不为空 
....
1  1006  空
#遍历完二层后结果集 整体有值则不会返回 1

4  1001  空
4  1002  空
4  1003  空
4  1004  空
4  1005  空
4  1006  空
#遍历完二层后结果集 整体为空则会返回 1

6、limit,offset使用

一个参数：等价于 limit 0,n

#取两条数据，即1,2条
SELECT * from employee LIMIT 2

两个参数：

第一个参数：从哪个索引位置开始取，limit 的索引从0开始，即对应第一条数据
第二个参数：取几条数据

#从索引为2位位置即第三行开始，取3条数据。即 3,4,5
SELECT * from employee LIMIT 2,3

offset的使用：

offset：后面表示偏移的位置，即索引开始的位置

limit: 后面表示取几条数据

#从索引为2的位置开始取三条数据，即 3,4,5
SELECT * from employee LIMIT 3 OFFSET 2

等价于==>SELECT * from employee LIMIT 2,3

7、like模糊查询

_ 匹配单个字符
% 匹配多个字符

查询带_下划线的字符串, 需要使用反斜杠来转义下划线

SELECT * from employee where emp_name like '张三\_'

或者使用ESCAPE关键字指定对应的字符来转义

#使用美元符号指定转义字符
SELECT * from employee where emp_name like '张三$_' ESCAPE '$'

8、not null/is not null 判断空

空值不等于 null值
- 若某一列的约束设置为null，则可以为 null和空字符串
- 若某一列的约束设置为not null，则可以为空字符串，但是不能为null值
判断null只能用 is null/not null判断，不能用 =、>、< ，!= ,<> 等判断

#找出emp_name为null值的数据，空字符串的数据可以被查出
SELECT * from employee where emp_name is NULL

9、order by 排序

使用 ASC(升序) 或 DESC(降序) 关键字来设置查询结果是按升序或降序排列。
默认为ASC升序
order by 后可加2个（或多个）字段，字段之间用英文逗号隔开。
若A用升序，B用降序，SQL该这样写：order by A ASC, B DESC;默认同理，也可以这样写：order by A, B DESC;
若A、B都用降序，必须用两个order by A DESC, B DESC;
若A、B都用升序，则order by A,B ;
多个字段时，优先级按先后顺序而定。

#先按照sno升序排列，再按照cno降序排列
SELECT * FROM `sc` ORDER by sno,cno desc;

9、some/all/any

any/some:

any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据即返回。

只要有sage小于其中sdept为IS条件的任何一个数据即返回

select *from student where sage< any(select sage from student where sdept='IS') and sdept<>'IS'

all:

all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

只要sgae 小于select sage from student中的所有数据即返回

select *from student where sage<=all(select sage from student)

10、natural join 自然连接

无需手动建立等值连接，不能写on关键字，自动连接等值的列，并且自动去掉重复的列

SELECT * from course NATURAL join sc WHERE cno=1

11、union/union all

使用 or 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or

⚠️联合起来列名和顺序必须相同，否则会联立错乱

select * from Table1  
union   #去除重复的行
select * from Table2

select * from Table1  
union all  #不会去除重复
select * from Table2

1.对重复结果的处理：UNION会去掉重复记录，UNION ALL不会；
2.对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合并；
3.效率方面的区别：因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多；

12、delete多表联合删除及注意事项

1. 删除单表中的数据

delete from course where c_id=12;

2. 删除联合查询的指定一个表的数据

即删除联合查询后delete关键字后面的表course

DELETE course from course,score where course.c_id = score.c_id and c_name='语文';

3. 删除联合查询中两个表的数据

即删除联合查询中delete关键字后面的表course和score

DELETE course,score from course,score where course.c_id = score.c_id and c_name='语文';

4.别名的使用

当联合多表删除时，必须在DELETE后面指定表名，当表名指定别名是，DELETE也必须指定表的别名

例如：删除 person表中id为1的数据

DELETE p from person p,employee e where p.id=1

5. 在同一表中select和delete的错误

mysql中You can't specify target table for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。：

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into Person (Id, Email) values ('1', 'john@example.com')
insert into Person (Id, Email) values ('2', 'bob@example.com')
insert into Person (Id, Email) values ('3', 'john@example.com')

来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

如下这种方式会出现：You can't specify target table 'person' for update in FROM clause

DELETE from person where id not in (select min(id) id from person group by email)

修改如下：再用select嵌套一层

DELETE from person where id not in (select id from (select min(id) id from person group by email) t)

-- 或者
DELETE p1
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email 
AND p1.Id > p2.Id

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。

13、not 非

表示成绩不在10-20之间的数据

SELECT * FROM sc where not(grade >=10 and grade <=20)

14、replace into 有则更新，无则新增

replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

1.如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

https://www.jb51.net/article/158125.htmopen in new window

replace into tbl_name(col_name, ...) values(...)

相当于：

if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1；

关于执行影响的行数：

①：假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。 返回影响行数2

②：假如不存在即没有与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，即直接插入，此时相当于一个普通插入操作。 返回影响行数1

15、in 的多条件匹配

来源LeetCode ，查询每个部门的最高工资

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)

16、关于聚合函数在mybatis中返回0和null的问题

当没有查询到符合的数据时，count(*)会被赋值为0，而sum(salary)会被赋值为null

所以，在对mybatit作返回值时，统计个数可以用int来接收，而求和等其他统计用Integer来接收并且需要判断对象是否为null

17、聚合函数中的多条件查询

注意：
sum(0) ==> 0
sum(1) ==> 1
sum(null) ==> null
count(0) ==> 总数
count(1) ==> 总数
count(null) ==> 0

                    count( CASE WHEN sheet_status = '2' AND repair_result = '0' THEN 1 ELSE NULL END ) c1,
                    count( CASE WHEN sheet_status = '2' THEN 1 ELSE NULL END ) c2,
                    count( CASE WHEN sheet_status = '1' THEN 1 ELSE NULL END ) c3,
                    count( CASE WHEN assessment_result = '1' THEN 1 ELSE NULL END ) p1,
                    count( CASE WHEN assessment_result = '2' THEN 1 ELSE NULL END ) p2,
                    count( CASE WHEN assessment_result = '3' THEN 1 ELSE NULL END ) p3
                    
                    sum( CASE WHEN sheet_status = '2' AND repair_result = '0' THEN 1 ELSE 0 END ) c1,
        			sum( CASE WHEN sheet_status = '2' THEN 1 ELSE 0 END ) c2,
        			sum( CASE WHEN sheet_status = '1' THEN 1 ELSE 0 END ) c3,
        			sum( CASE WHEN assessment_result = '1' THEN 1 ELSE 0 END ) p1,
        			sum( CASE WHEN assessment_result = '2' THEN 1 ELSE 0 END ) p2,
        			sum( CASE WHEN assessment_result = '3' THEN 1 ELSE 0 END ) p3

https://blog.csdn.net/qq_32112175/article/details/89707327open in new window

https://www.cnblogs.com/licunzhi/p/10824201.htmlopen in new window

https://blog.csdn.net/weixin_40558287/article/details/100881885open in new window

18、小表驱动大表

https://blog.csdn.net/weixin_39634876/article/details/111698250open in new window

为什么要用小表驱动大表？

例如：现有两个表A与B ，表A有200条数据，表B有20万条数据 ; 按照循环的概念举个例子

小表驱动大表 > A驱动表，B被驱动表
```
for(200条){for(20万条){...}}
```
大表驱动小表 > B驱动表，A被驱动表
```
 for(20万){for(200条){...}}
```
- 如果小的循环在外层，对于表连接来说就只连接200次 ;
- 如果大的循环在外层，则需要进行20万次表连接，从而浪费资源，增加消耗 ;
小表驱动大表的主要目的是通过减少表连接创建的次数，加快查询速度

在JOIN查询中经常用到的 inner join、left join、right join 问题解答： 1.当使用left join时，左表是驱动表，右表是被驱动表 ; 2.当使用right join时，右表时驱动表，左表是被驱动表 ; 3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;

19、truncate,drop,delete

drop
- drop table 表名
1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
truncate
- truncate table 表名
1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
delete
- delete from 表名 where 条件
1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

总结：

在速度上，一般来说，drop> truncate > delete。
在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
如果想删除表，当然用drop；
如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
如果和事务有关，或者想触发trigger，还是用delete；
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

20、批量插入 insert into 表名 select * from 表名

批量插入时，字段的顺序必须一致

21、多表联合更新

https://www.cnblogs.com/willingtolove/p/10792713.htmlopen in new window

Oracle

语法

UPDATE updatedtable 
SET (col_name1[,col_name2...])= (SELECT col_name1,[,col_name2...] 
FROM srctable [WHERE where_definition])

范例

update test1 
set (test1.name,test1.age)=
(select test2.name,test2.age from test2 where test2.id=test1.id)

MySQL

语法

UPDATE table_references 
SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

范例

update test1,test2 
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id

通用写法

update test1 
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)

22、表的备份

-- 将 student 表备份为 student_bak
-- 备份表结构和数据
create table student_bak as select * from student;
-- 只备份表结构
create table student_bak as select * from student where 1=2;

23、MySQL和Java的类型对应关系

MySQL	JDBCType	JavaType	备注
char	CHAR	String	定长字符
varchar	VARCHAR	String	变长字符串
tinyint	TINYINT	byte	1字节
smallint	SMALLINT	short	2字节
int	INTEGER	int	4字节
float	FLOAT	float	4字节
bigint	BIGINT	long	8字节
double	DOUBLE	double	8字节
bit	BOOLEAN	boolean	布尔类型
date	Date	util.Date、sql.Date	YYYY-MM-DD
time	TIME	util.Date、sql.Date	HH:MM:SS
timestamp	TIMESTAMP	util.Date、sql.Date	YYYY-MM-DD HH:MM:SS
text	VARCHAR	String	文本类型【2^16-1字节】
longtext	LONGVARCHAR	String	文本类型【2^32-1字节】

四、存储过程及函数

1、:=和=的区别

=
- 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
:=
- 不只在set和update时时赋值的作用，在select也是赋值的作用。

@num:=@num+1,:=是赋值的作用，所以，先执行@num+1,然后再赋值给@num，所以能正确实现行号的作用。

@num=@num+1,此时=是等于的作用，@num不等于@num+1，所以始终返回0，如果改为@num=@num,始终返回1了。mysql数据库中，用1表示真，0表示假。

五、DDL操作

1、数据库管理

#创建数据库
create database test1;

#查看所有数据库
show databases;

#删除数据库
drop database test1
#如果存在test1数据库则删除
drop database if EXISTS test1

#使用test数据库
use test;

2、表的管理

alter table 表名 change|modify|add|drop column 列名【约束名约束】

#如果person表存在则删除该表
drop table if exists person;

#查看所有表
show tables; 

#创建表
create table person(
		id int primary key,
		name varchar(50) not null,
	  height int,
    birthday date,
		gender char(2)
);

#修改列名及其类型和约束
#修改列名gender为sex并为其重新指定类型为char(1)
alter table person change column gender sex char(1) not null

#修改列的类型及约束
alter table person modify column birthday datetime not null

#增加列
alter table person add column address varchar(100)

#删除列
alter table person drop column height

#修改表名person为person_table
alter table person rename to person_table

#删除表
drop table person;

#查看表的结构 `desc 表名`
desc person

#查看表的所有索引 `show index from 表名`
show index from person

4、数据类型

1)枚举的创建和使用

枚举值不区分大小写

create table table_char(
        # 设置主键自增
		id int PRIMARY key auto_increment,
		gender enum('男','女')
)
INSERT into table_char(gender) values('男');
INSERT into table_char(gender) values('女');

# 插入 男
INSERT into table_char(gender) values('1');
INSERT into table_char(gender) values(1);

#插入 女
INSERT into table_char(gender) values('2');
INSERT into table_char(gender) values(2);

#插入 空值
INSERT into table_char(gender) values('0');

2)日期类型

①datetime与timestamp区别

日期显示的格式一致

也就是说，对于timestamp来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于datetime来说，存什么拿到的就是什么。

使用场景：

如果在时间上要超过Linux时间的，或者服务器时区不一样的就建议选择 datetime。
如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。
如果只是想表示年、日期、时间的还可以使用 year、 date、 time，它们分别占据 1、3、3 字节，而datetime就是它们的集合。
一般我们在创建表时，如果希望得到表的更新时间，则创建一个更新字段，勾选timestamp/datetime类型为时间戳类型，当修改表的数据时会自动更新该字段为当前系统时间，但首次插入时不会更新该字段

3)整型

4)浮点型

5)字符型

text：与char和varchar不同的是，text不可以有默认值，其最大长度是2的16次方-1

5、约束的操作

not null 非空约束
default 默认约束
primary key 唯一非空约束
unique 唯一约束，可为null，可以有多个null值。也可以为空值，空值也算一种值，但是不能有多个空值。
check 检查约束【mysql不支持，但是写不报错】
foreign key 外键约束(不常用)

1)约束添加的时机

列级约束
- 六大约束语法上都支持，但外键约束没有效果
标记约束
- 除了非空，默认，其他的都支持

create table 表名(
	字段名 字段类型 列级约束,
    字段名 字段类型 列级约束
    表级约束
);

2)创建表时添加列级约束

添加列级索引时，不允许取索引名称
创建表时，不允许添加列级的普通索引

create table class_info(
	id int primary key auto_increment,
	name VARCHAR(20)
);
drop table if EXISTS stu_info;
create table stu_info(
	id int primary key auto_increment,
	username varchar(20) unique not null,
	name varchar(30) not null 
	# 检查约束在mysql中无效，但写不报错
	gender char(1) check(gender='男' or gender='女'),
	age int default 18,
    # 在列级添加外键约束无效
	classid int REFERENCES class_info(id)
);

3)创建表时添加表级约束

添加普通索引
- index 索引名(列名)
- index(列名) --默认列名为索引名
添加唯一索引
- unique 索引名(列名)
- index(列名) --默认列名为索引名

create table class_info(
	id int primary key auto_increment,
	name VARCHAR(20)
);
drop table if EXISTS stu_info;
create table stu_info(
	id int auto_increment,
	username varchar(20) not null,
	name varchar(30) not null,
	gender char(1) default '男',
	age int default 18,
    address varchar(50),
	classid int,
    #主键约束
	primary key(id),
    #唯一约束
    unique(username),
    #check在mysql中不生效
    check(gender='男' or gender='女'),
    #外键的命名规则一般为 fk_本表字段_外键字段
    constraint fk_calssid_id foreign key(classid) references class_info(id),
    #添加普通索引
    index inxadd(address)
);

#查看表的所有索引信息
show index from stu_info

4)创建表时常规写法

drop table if EXISTS stu_info;
create table stu_info(
	id int primary key auto_increment,
	username varchar(20) unique not null,
	name varchar(30) not null,
	gender char(1) default '男',
	age int default 18,
	classid int,
	constraint fk_calssid_id foreign key(classid) references class_info(id)
);

5)主键约束和非空约束的区别

主键
- 不允许为null，若主键为字符型，那么只能有一个空的字符作为主键
非空约束
- 不允许为null值，可以为空值

6)唯一约束(索引)

允许为null，且可以为多个null值。允许为空值，但是最多只能有一个空值
一个表可以有多个唯一约束

7)联合主键

当只有一个字段作为主键时，字段不为null，且唯一
当有两个字段作为联合主键时，只要两个字段在多行只要有一个不相等则可以插入。
一个表只能有一个主键

9)修改表添加约束

#添加列级约束
#		添加主键约束
alter table stu_info MODIFY column id int PRIMARY key auto_increment;
#添加表级约束
#		添加主键约束
alter table stu_info add PRIMARY key(id);

#添加列级约束
#		添加非空唯一约束
alter table stu_info modify column username varchar(20) not null unique;
#添加表级约束
#		添加唯一约束		
alter table stu_info add unique(username);

#添加外键约束
alter table stu_info add CONSTRAINT fk_calssid_id FOREIGN key(classid) REFERENCES class_info(id)

#查看表的结构
desc stu_info;
#查看表的索引信息
show index from stu_info

10)修改表时删除约束

#删除字段上的其他约束(默认约束，非空约束)。直接重新修改字段即可
alter table stu_info modify column username varchar(20);

#删除唯一约束 `alter table 表名 drop index 约束名`
alter table stu_info drop index username

#删除主键
alter table stu_info drop PRIMARY key

#删除外键  `alter table 表名 drop FOREIGN key 外键名称`
alter table stu_info drop FOREIGN key fk_calssid_id

6、视图的操作

视图的好处
- 重用sql语句
- 简化复杂的sql操作，不必知道它的查询细节
- 保护数据，提高安全性

1)视图创建

#创建视图 `create view 视图名 as 查询语句`
CREATE VIEW v1 AS SELECT
	hr.*, d.depName,
	d.enabled
FROM
	hr
LEFT JOIN department AS d ON hr.depId = d.depId;

#视图使用 `select * from 视图名 where ...`
SELECT * FROM v1 WHERE hrTelephone LIKE '029%';

2)视图的修改

#若v2存在则修改视图，若不存在则创建视图v2  `create or replace view 视图名 as 查询语句`
create or replace view v2 as select hr.hrId from hr;

#修改视图  `alter view 视图名 as 查询语句`
alter view v1 as select * from hr;

SELECT * from v1

SELECT * from v2

3)删除视图

#删除视图 `drop view 视图名1,视图名2`
drop view v1,v2

4)查看视图结构

#查看视图结构 `desc 视图名`
desc v2

5)视图和表的区别

视图没有保存实际的数据，只是一张虚拟表，只是保存的sql逻辑。一般适用于查询操作
表保存了实际的数据

7.数据及表的导出与导入

mysqldump完整参数详解 https://www.cnblogs.com/qq78292959/p/3637135.htmlopen in new window

7.1 导出结构

容器：docker exec -it 容器名 mysqldump --opt -d -u用户名 -p密码数据库名 >./文件名.sql

非容器： mysqldump --opt -d -u用户名 -p密码数据库名 >./文件名.sql

7.2 导出数据

容器：docker exec -it 容器名 mysqldump -t -u用户名 -p密码数据库名 >./文件名.sql

非容器：mysqldump -t -u用户名 -p密码数据库名 >./文件名.sql

7.3 导出数据库表结构及数据

容器：docker exec -it 容器名 mysqldump -u用户名 -p密码数据库名 >./文件名.sql

非容器： mysqldump -u用户名 -p密码数据库名 >./文件名.sql

7.4 导出特定的表结构

非容器：mysqldump　-uroot　-p密码　-B　数据库名　--table　表名　>　xxx.sql

容器：docker exec -it 容器名 mysqldump　-uroot　-p密码　-B　数据库名　--table　表名　>　xxx.sql

7.5 导出特定数据库中的特定多张表

非容器：mysqldump -u用户名 -p密码数据库表1 表2 > xxx.sql

例子：mysqldump -uroot -p'Ainms@!^*168' ai-adapter s_cmd_model s_data_type > ./bak20220520.sql

⚠️ -p后面如果有特殊字符一律使用''引用

7.6 导入sql数据文件

第一种方式：

进入数据库
mysql -uroot -p123456
选择数据库
use db;
导入数据
注意文件位置
source /export/home/c3.sql;

第二种方式：

注意数据库位置

mysql -u用户名 -p密码数据库名 < 数据库名.sql

8.添加用户及权限限制

添加root用户，并给root用户设置密码为Ainms@!^*168，并且访问限制ip为172.16.33.116。并赋予所有权限.

若172.%则为172开头的网段可访问

mysql系统数据库下的user表可查看详细信息
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.33.116' IDENTIFIED BY 'Ainms@!^*168' WITH GRANT OPTION;
更新完后刷新配置
flush privileges;

添加root任意地址访问

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
刷新配置
flush privileges;

六. 其他

4.1 查看时区和修改时区

查看当前系统的时区

SHOW VARIABLES LIKE "%time_zone%";

设置系统时区

#全局设置
SET GLOBAL time_zone = '+8:00';
#只在当前会话有效
SET time_zone = '+8:00';
#刷新
FLUSH PRIVILEGES;

4.2 MySQL5.7默认打开ONLY_FULL_GROUP_BY模式问题与解决方案

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SELECT @@sql_mode;

修改/etc/init.d/my.cnf配置文件，在配置文件最后添加如下配置信息 sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

4.3 Mysql导出列，数据类型，注释信息等

SELECT
	COLUMN_NAME 列名,
	COLUMN_TYPE 数据类型,
	DATA_TYPE 字段类型,
	CHARACTER_MAXIMUM_LENGTH 长度,
	IS_NULLABLE 是否为空,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注
FROM
	information_schema. COLUMNS
WHERE
	TABLE_NAME = 'bz_class_ti_info'//表名
AND TABLE_SCHEMA = 'aly';//数据库名称

4.4 Navicat导入excel数据

直接使用Navicat中的导入向导即可

https://blog.csdn.net/wzmde007/article/details/105988388open in new window

MySQL基础

# 一、SQL查询语句执行顺序

# 二、基本函数的使用

# 1、字符相关处理

# 1)concat连接函数

# 2) upper/lower/ucase/lcase 大小写转换

# 3)length 字符的字节数

# 4)substr 字符串截取

# 5)instr判断子串出现的索引

# 6)trim去除前后空格

# 7)lpad/rpad填充字符

# 8)replace 替换字符串

# 9)substring_index 按索引指定字符切割字符串

# 2、convert小数格式化

# 1)decimal(P,D)函数的使用

# 3、ifnull 判断空并赋值

# 4、日期相关处理

# 1)日期格式化参数缩写

# 2)日期名称解释

# 3)常见时间获取及处理

# 4)格式化日期

# ①字符格式化为日期

# ②日期格式化为字符

# 5)日期间隔差值计算

# 6)加减天数/时间

# 5、流程控制函数

# 1)if

# 2)case...when...then...else... end

# 6、聚合函数

# 7、多行合并 GROUP_CONCAT 函数

# 8、行转列

# 三、基本关键字的使用

# 1、between ... and ...

# 2、distinct去重

# 3、group by多条件分组

# 4、case when ... then ... else ... end行转列

# 5、exists和not exists用法

# 6、limit,offset使用

# 7、like模糊查询

# 8、not null/is not null 判断空

# 9、order by 排序

# 9、some/all/any

# 10、natural join 自然连接

# 11、union/union all

# 12、delete多表联合删除及注意事项

# 1. 删除单表中的数据

# 2. 删除联合查询的指定一个表的数据

# 3. 删除联合查询中两个表的数据

# 4.别名的使用

# 5. 在同一表中select和delete的错误

# 13、not 非

# 14、replace into 有则更新，无则新增

# 15、in 的多条件匹配

# 16、关于聚合函数在mybatis中返回0和null的问题

# 17、聚合函数中的多条件查询

# 18、小表驱动大表

# 19、truncate,drop,delete

# 20、批量插入 insert into 表名 select * from 表名

# 21、多表联合更新

# 22、表的备份

# 23、MySQL和Java的类型对应关系

# 四、存储过程及函数

# 1、:=和=的区别

# 五、DDL操作

# 1、数据库管理

# 2、表的管理

# 4、数据类型

# 1)枚举的创建和使用

# 2)日期类型

# ①datetime与timestamp区别

# 3)整型

# 4)浮点型

# 5)字符型

# 5、约束的操作

# 1)约束添加的时机

# 2)创建表时添加列级约束

# 3)创建表时添加表级约束

# 4)创建表时常规写法

# 5)主键约束和非空约束的区别

# 6)唯一约束(索引)