mysql总结-sql总结
Mysql操作
系统命令
连接MYSQL：
mysql -h主机地址 -u用户名 －p用户密码连接到本机上的MYSQL
mysql -uroot -p超级用户root是没有密码的，故直接回车即可进入到MYSQL中了
连接远程机上的mysql
mysql -h110.110.110.110 -uroot -pabcd123修改密码：
mysqladmin -u用户名 -p旧密码 password 新密码查看链接信息
show status like '%connect%';
Aborted_connects 尝试连接到MySQL服务器失败的次数，
Threads_connected 当前打开的连接的数量
Connections 表示MySQL从启动至今，成功建立连接的连接数，这个值是不断累加的。
max_connect_errors 允许单用户连接错误最大值，超过后在不刷新状态的情况下，禁止该用户新连接
max_connections 实例最大连接数限制
max_user_connections 但用户连接最大限制，默认0表示无限制
connect_timeout 用户连接超时限制，超过10秒，如果依旧无法连接到mysql，则终止连接
查看timeout设置
show variables like '%timeout%';单位是秒
connect_timeout:tcp连接超时是时间（默认10秒），是连接过程中握手的超时时间，是客户端与mysql服务器建立连接时，握手开始到结束的超时时间。
interactive_timeout/wait_timeout:交互/非交互模式下的不活跃线程（sleep状态）的等待时间，默认8小时，连接超过这个时间长度没有过操作，mysql会断开该连接。
修改wait_timeout:
set global wait_timeout=864000;
查看连接线程参数
show variables like 'thread%'; thread_cache_size 设置连接线程缓存的数目。这个缓存相当于MySQL线程的缓存池
thread_handling 默认值是： one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求，直至请求完毕，连接销毁或者存入缓存池。当值是no-threads 时，表示在始终只提供一个线程来处理连接，一般是单机做测试使用的。
thread_stack stack 是堆的意思
thread_concurrency 参数用于向操作系统建议期望的并发线程数，mysql 5.6后不再使用
查看正在执行的sql进程
show processlist
在info列可以查看执行的sql
如果要终止某个操作
kill 指定的id
数据库操作
创建数据库：
CREATE DATABASE 数据库名;CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;删除数据库
drop database <数据库名>;选择数据库
USE database_name;查询当前数据库名称
SELECT DATABASE();添加索引
ALTER TABLE `student` ADD INDEX `idx_age` (`age`);
alter table table_name add unique index uniq_name1_name2 (`column1`,`column2`);删除索引
ALTER TABLE `your_table_name` DROP INDEX `idx_age`;创建表
CREATE TABLE `user` (
`name` varchar(5) NOT NULL,
`age` int(11) NOT NULL,
`createtime` datetime DEFAULT NULL,
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;修改表
添加字段：
ALTER TABLE mp_qualification ADD COLUMN third_code varchar(255) NOT NULL COMMENT '活动币活动编码';
ALTER TABLE mp_qualification ADD COLUMN third_code varchar(255) NOT NULL COMMENT '活动币活动编码' after second_code; -- 指定新字段放在谁后面修改列类型：
ALTER TABLE 表名 MODIFY 列名 列的新类型如果被修改的列中已存在数据，那么列的新类型会影响已存在的数据；
修改列名称：
ALTER TABLE 表名 CHANGE 原列 名新列名 列类型；或者alter table student1 change column 列名 新列名 列类型;删除列：
ALTER TABLE 表名 DROP 列名；修改表名：
ALTER TABLE 表名 RENAME TO新表名（to可以省略）。或RENAME TABLE 表名 TO新表名修改表的字符集：
ALTER TABLE 表名 character set utf8;修改表注释：
alter table test1 comment '修改后的表的注释';修改表的auto_increment
alter table <tableName> auto_increment = <num>;删除表
drop table 表名；为了防止删除一个不存在的表而报错
使用：drop table if exists 表名
查看表的定义信息
SHOW CREATE TABLE mytable;插入数据
向表中插入数据有以下三种方式：
insert into students values(4,'aaaa',1,1);
insert into students set sid=5,sname='ddd',gender=2,dept_id=1;
insert into students select * from students_bak;
insert into student(number,name) values(‘xxx’,’xxx’)
insert into 表名(列名1,列名2, ...) values(列值1, 列值2, ...),(列值1, 列值2, ...)
修改数据
根据条件修改
update student set age=18 where name=’wangwu’ or number=’itcast_0002’;
update student set age=age+1 where age>=15 and age<=35;交换两个字段的值
UPDATE student SET a1 = (@temp := a1), a1 = a2, a2 = @temp;update配合select
UPDATE t_trade td , t_order od SET td.park_id=od.park_id WHERE td.order_code = od.order_code;删除数据
DELETE FROM 表名 [WHERE 条件];
TRUNCATE TABLE 表名;delete和truncate区别
delete可以根据条件进行删除数据
delete会把删除操作作为事务记录在日志中，以便进行回滚
delete不清除自增记录数
truncate直接销毁表并重新新建表，不会记录删除到日志，不能回滚，会清除自增记录数
当表被truncate 后,这个表和索引所占用的空间会恢复到初始大小,delete操作不会减少表或索引所占用的空间。
查询
分页查询
取出sql表中第31开始查询10条
select * from t order by id limit 30,10;distinct查询
查询指定列值不同的记录
select distinct sname from emp；查询多个字段都不重复的记录（也就是多个字段都相同的记录会去除）
select distinct sname,deptno from emp;加减乘除查询
（如果有一列为null，则结果为null;非数值类型的列做运算时，它的值都会被当做0来计算）
select ename,sal,sal*1.5 from emp;
select ename,sal,comm,sal+comm from emp;条件查询
select * from table1 where name=’zhangsan’;
select * from emp where deptno=20;
select * from emp where sal>20000;
#between查询两端的值都在查找范围内
select * from emp where sal between 20000 and 30000;
select * from emp where comm is not null;
select * from emp where job in(‘经理’,’分析师’);
模糊查询
#“%”表示匹配0到N个字符
select * from emp where ename like ‘张%’;#包含指定字符的就会被查到
select * from stu where sname like ‘%刚%’#一个“_”匹配一个字符
select * from emp where ename like ‘__’;使用‘张%’能够用到索引，‘%刚%’和‘%刚’都不能用到索引，当该字段没有建立索引时就没什么关系了
排序查询
不能依赖mysql默认的排序，不一定按照插入的顺序
默认升序asc，如果要倒序desc
select * from emp order by sal先根据一个字段排序，该字段相同时再按照第二个字段排序，依次可以有多个字段
select * from emp order by sal asc ,comm desc;聚合查询
MySQL就为我们提供了一些聚合函数。常用的聚合函数有：MAX（）、MIN（）、COUNT（）、SUM（）、AVG（）
COUNT()函数统计表中包含的记录行的总数，或根据查询结果返回列中包含的数据行数。其使用方法有两种：
①COUNT(*)，计算表中总的行数，不管某列是否有数值或空值
②COUNT(字段名)，计算指定列下总的行数，计算时将忽略空值的行
SUM()是一个求总和的函数，返回指定列值的总和。sum()函数在计算时，忽略列值为null的行。
AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
MAX()函数返回指定列中的最大值
MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，返回包括字符类型的最大值。在堆字符类型数据进行比较时，按照字符的ASCII码值大小进行比较，从a~z，a的ASCII码最小，z的最大。在比较时，先比较第一个字母，如果相等，继续比较下一个字符，一直到两个字符不相等或字符结束为止。
select count(*) from student;
select sum(math) from student;
select avg(math) from student;
select max(math) from student;
select min(math) from student;
分组查询
group by 的特性是分组后会取同一组的第一个值
分组后每个相同分组字段只返回一条数据，可以用GROUP_CONCAT函数把想返回所有值组合成一个字符串返回
SELECT sex,GROUP_CONCAT(name) FROM employee GROUP BY sex;：WHERE是对分组前记录的条件判断
select deptno,count(*) from emp where sal>15000 group by deptno;在分组后再进行的条件判断需要使用另一个关键词：having。
select deptno,count(*) from emp where sal>15000 group by deptno having count(*)>2；
多个字段groupby
SELECT Subject, Semester, Count(*) FROM Subject_Selection GROUP BY Subject, Semester对Subject_Selection表中的数据进行分组，将具有相同Subject和Semester字段值的记录放到同一个分组里去， 然后对每个分组中的数据应用聚合函数（COUNT，SUM， AVG，etc）。
group by 后面的字段顺序 只是影响了结果的顺序，不会影响结果的值。
带排序的group(是对group后的数据进行排序)
SELECT * from `user` GROUP BY age order by age;对分组数量排序
SELECT order_code,count(order_code) c from t_trade WHERE create_time > '2021-06-18 00:00:00' GROUP BY order_code HAVING c>1 order by c desc如何先排序再分组：
方法一，仅适用于低于5.7版本的MySql--
select * from (select * from shop order by price desc) a GROUP BY a.shop_name;5.7版本后mysql优化时会优化掉这个子查询
方法二：使用join查询
这里id是唯一的，如果排序字段不是唯一的就外面在进行一次group by
SELECT a.* FROM USER a
JOIN (SELECT max(id) m FROM USER GROUP BY age ) b ON a.id = b.m
ORDER BY id DESC;
子表查询
字表必须定义别名才可以
SELECT id FROM(SELECT id,NAME FROM t1) b;
SELECT NAME FROM t1,(SELECT tid FROM t2 where t2.age>20) AS b WHERE b.tid=t1.id;
SELECT NAME n,(SELECT age FROM t1 WHERE n=NAME) AS b FROM t1 GROUP BY NAME;
select * from emp where sal>all(select sal from emp where deptno=30);
select * from emp where sal>any(select sal from emp where deptno=30);
select * from emp where (job,deptno,sal) in (select job,deptno,sal from emp where ename=’殷天正’);all是与里面的数据每一个比较都都符合要求才行
any是与里面的数据比较有一个符合条件就行
关联查询和笛卡尔积
关联查询，首先都是进行笛卡尔积，然后根据连接条件和where条件进行条件过滤。
笛卡尔积：
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
到表里就是a表有5条记录，b表有6条记录，他们的笛卡尔积为5*6=30条数据
连接查询处理过程：
生成笛卡尔结果集，使用on条件去掉不符合的数据，添加left join或right join主表符合条件的数据，where条件筛选
如果是inner join没有添加outer join的过程
关联查询缺点：
关联查询会产生笛卡尔积，如果表数据量太多，效率就会下降，可以分别根据索引单表取数据，然后在程序里面合并处理
内连接
select e.ename,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;方言：
select * from emp,dept where emp.deptno=dept.deptno;两个表的结果数据都需要符合连接条件
外连接
外连接代表：主表查询的结果不受on的连接条件限制，连接表受连接条件的限制
左外连接的关键词是：LEFT OUTER JOIN或者 LEFT JOIN
SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.列名=别名2.列名中表1就是左表，又称为主表，表2是右表。
SELECT A.* FROM A LEFT JOIN B ON (A.id = B.id) WHERE b.id IS NULL右连接的关键词是：RIGHT OUTER JOIN或者 RIGHT JOIN
SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.列名=别名2.列名其中表1是左表，表2是右表，又称为主表。
UNION[ALL] 合并结果集
查询的两个结果集的列名称不需要相同，只要满足列的个数和类型一样就可以；右表查询出来的结果集会被合到最左边表中
个数和类型不一样查询会报错
select * from one union all select * from twoUNION，先获取到两个查询的结果集，再去除重复行；重复的行是指结果中每个属性都一样
UNION ALL，不去除重复行。
Exsists和not exsists
exists查询，如果子查询中结果有数据才会执行主查询，note exisists如果没结果才会执行主查询
select * from goods a where EXISTS(select cat_id from ecs_category b where a.cat_id = b.cat_id);
SELECT * FROM A WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.id = A.id );case when
case when语句，用于计算条件列表并返回多个可能结果表达式之一。
CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。
- -搜索Case函数:
Case函数(Case搜索函数): 判断表达式的真假,如果为真,返回结果;如果为假,返回else值;如果未定义else值,则返回空值(使用条件确定返回值);
select name,id,(case when id=34 then salary*2
when id=45 then salary*3
else salary
end) new_salary
from semp;
-- -简单Case函数
简单Case表达式的作用是: 使用表达式确定返回值:
select id,name,(case sex
when '1' then '男'
when '2' then '女'
else '其他' end)
from student;这两种方法可以实现相同的功能. 简单Case函数的写法比较简单,但是和case搜索函数相比,功能方面会有些限制,比如判断式。
还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
比如下面 SQL,将无法得到第二个结果:(差永远获取不到)。
sql如下
case when colum in ('a', 'b') then '优秀'
when colum in ('a') then '差'
else '其他' end case when的常用应用场景：
等值转换
select name as '姓名',(case sex when 0 then '女' else '男' end) as '性别' from test.student;
范围转换
select name as '姓名',
(case when score > 90 then '优' when score >= 80 then '良' when score >= 60 then '及格' else '不及格' end) as '等级'
from test.stu_score;
列转行操作：
select class_id,
(case when grade = 'primary' then rate else 0 end) as 'primary',
(case when grade = 'middle' then rate else 0 end) as 'middle',
(case when grade = 'high' then rate else 0 end) as 'high'
from mst_class;配合聚合做统计：
SELECT
sum(third_real_amount) txAmount,
count(bill_detail_id) txCount,
sum(case when bill_type = 2 then third_real_amount else 0 end) refundAmount ,
count(case when bill_type = 2 then bill_detail_id end) refundCount,
sum(case when bill_type = 1 then third_real_amount else 0 end) amount,
count(case when bill_type = 1 then bill_detail_id end) count,
sum(channel_fee) serviceCharge
from ${table}
WHERE
status=1when中也可以使用and等组合多个条件
SELECT
column1,
column2,
CASE
WHEN condition1 AND condition2 THEN result1
WHEN condition3 AND condition4 THEN result2
ELSE default_result
END AS result_column
FROM
your_table;格式化时间查询
时间转字符串:select date_format(now(), '%Y-%m-%d');
字符串转时间: select str_to_date('2020-11-02', '%Y-%m-%d %H:%i:%s');
字符串转时间戳:
select unix_timestamp(); #无参数，和传入now()都是获取当前时间时间戳
select unix_timestamp('2020-11-01'); #传入时间格式的字符串，转换为与时间参数对应的时间戳
时间戳转时间
select from_unixtime(1604483158); #转换成对应时间戳的时间格式
select from_unixtime(1604483158,’%Y-%m-%d’); #第一个参数是要转换的时间戳，第二个参数是时间格式类型的样式拼接字符串
CONCAT(a.trade_no,'_jc')
查询练习
1、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。
employee:
eid,ename,salary,deptid;
select * from employee order by deptid desc,salary asc;
select eid,ename,salary,deptid from employee order by deptid desc,salary asc;2、列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序
select depatno, count(*) from depat as a,
(select depatno, avg(sal) as avgsal from depat group by depatno)as b,
where a.depatno=b.depatno
and a.sal>b.avgsal
group by a.depatno
order by a.depatno用一条SQL语句 查询出每门课都大于80分的学生姓名
select distinct name from score where name not in (select distinct name from score where score<=80);统计每年每月的信息
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
select sales.year ,
(select t.amount from sales t where t.month='1' and t.year= sales.year) 'm1',
(select t.amount from sales t where t.month='1' and t.year= sales.year) 'm2',
(select t.amount from sales t where t.month='1' and t.year= sales.year) 'm3',
(select t.amount from sales t where t.month='1' and t.year= sales.year) as 'm4'
from sales group by year;
删除除了id号不同,其他都相同的学生冗余信息
2.学生表 如下:
id号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
A:
delete from tablename where id号 not in(select min(id号) from tablename group by 学号,姓名,课程编号,课程名称,分数)求出小于45岁的各个老师所带的大于12岁的学生人数
数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果
1.显示的字段要有老师name, age 每个老师所带的学生人数
2 只列出老师age为40以下，学生age为12以上的记录
SELECT teacher.teaid,
teacher.name,
total
FROM teacher ,
(SELECT tea_stu.tea id,
count(*) total
FROM student,tea_stu
WHERE student.stuid=tea_stu.stuid
AND stu dent.age>12
GROUP BY tea_stu.teaid) AS tea_stu2
WHERE teacher.teaid=tea_stu2.tea id
AND teacher.age<45;
这样也行
SELECT tea. name,
tea.age,
count(u.id)
FROM teacher tea, tea_stu ts, student u
WHERE tea.age < 45
AND tea.id = ts.teaid
AND u.id = ts.stuid
AND u.age > 12
GROUP BY tea.id
求出发帖最多的人：
SELECT authorid,
count(*) total
FROM articles
GROUP BY authorid
HAVING total=
(SELECT max(total2)
FROM
(SELECT count(*) total2
FROM articles
GROUP BY authorid) AS t);思路：要查询最多，就要先查询各用户发帖数量，然后找到最多的数量。
一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。
SELECT count(*) AS num,
tb.id
FROM tb,
(SELECT role
FROM tb
WHERE id=xxx) AS t1
WHERE tb.role = t1.role
AND tb.id != t1.id
GROUP BY tb.id
HAVING num =SELECT count(role)
FROM tb
WHERE id=xxx;思路：首先要知道该用户有哪些角色，select role from tb where id=xxx，然后是查询和该用户有相同角色的所有其他用户，where tb.role = t1.role and tb.id != t1.id，然后就是看角色的数量，相同的就是了。
查询与名称为殷天正的员工的工作，部门以及工资相同的员工信息
select * from emp where (job,deptno,sal) in (select job,deptno,sal from emp where ename=’殷天正’)查询大于30部门所有员工工资的员工信息，包含30部门员工
select * from emp where sal>all(select sal from emp where deptno=30)查询大于30部门其中任意一个员工工资的员工信息，包含30部门员工
select * from emp where sal>any(select sal from emp where deptno=30)
a,b,c三张表分别有id,score,name三个字段，表之间无关系根据score对所有字段排倒序
利用union all联合查询
查看代码
SELECT e.score,
e.name,
e.id from(
(SELECT score,
name,
id
FROM a
ORDER BY score)
UNION
all
(SELECT score,
name,
id
FROM b
ORDER BY score)union all
(SELECT score,
name,
id
FROM c
ORDER BY score)) e
ORDER BY e.score desc查询在一个表中有另一个表中没有的数据
SELECT A.* FROM A LEFT JOIN B ON (A.id = B.id)
WHERE b.id IS NULL select * from B
where (select count(1) as num from A where A.ID = B.ID) = 0
浙公网安备 33010602011771号