mysql面试题-写sql

创建表

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 third_code varchar(255) NOT NULL COMMENT '活动币活动编码'
修改列类型：
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;

删除表

drop table 表名；为了防止删除一个不存在的表而报错
使用：drop table if exists 表名

插入数据

向表中插入数据有以下三种方式：
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

删除数据

DELETE FROM 表名 [WHERE 条件];
TRUNCATE TABLE 表名；

delete和truncate区别

delete可以根据条件进行删除数据，truncate不可以。
delete会把删除操作做为事务记录在日志中，以便进行回滚。
delete不清除自增记录数
truncate直接销毁表并重新新建表，不会记录删除到日志，不能回滚，会清除自增记录数
因为truncate不用查找要删除的数据和索引及记录日志所以效率比delete高。
当表被truncate 后,这个表和索引所占用的空间会恢复到初始大小,delete操作不会减少表或索引所占用的空间（数据和索引只会标记为删除，新记录过来可以覆盖被标记为删除的数据的空间）。
MySQL的删除操作在底层实现上并不会在数据文件上物理删除数据，会把这些数据的数据及索引标记为无效，之后又新的数据可以重用掉这部分无效数据的空间。
mysql的trunct操作在底层实现上直接设置数据文件和索引文件为无效可重用，并创建新的文件用来存储数据和索引，无效数据的空间以后可以被其他数据覆盖使用。

分页查询

取出sql表中第31开始查询10条
select * from t order by id limit 30,10

limit查询的语法第一个数为偏移量（从0开始），第二个数为查询的条数

查询第一页是

select * from t order by id limit 0,10;
select * from t order by id limit 10;

distinct查询

查询指定列值不同的记录

select distinct name 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

分组查询

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(*) c from emp where sal>15000 group by deptno having c>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 a.*
FROM
	tb a
JOIN (
	SELECT
		max(id) m
	FROM tb
	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是与里面的数据比较有一个符合条件就行

笛卡尔积

笛卡尔积是指从两个或多个表中获取所有可能的组合，如果有一个表A包含m行数据，另一个表B包含n行数据，它们的笛卡尔积将包含m*n行数据。

假设集合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条数据

当你执行一个涉及多个表的查询，并且没有提供过滤条件，就会产生笛卡尔积

SELECT * FROM a, b;

如果对于部分表没有过滤条件也是会产生笛卡尔积

SELECT * FROM a, b, c WHERE a.id = b.a_id;

因为c表没有被任何条件限制，所以它会与a和b的笛卡尔积进行组合。

连接查询

为了避免笛卡尔积，需要使用正确的JOIN语法或WHERE子句来指定表之间的连接条件。可以说JOIN查询是用于避免笛卡尔积的一种手段。

对于join查询要尽量使用连接条件来限制产生的组合数据。

使用join进行查询导致笛卡尔积的原因多是连接条件不正确或缺失。

MySQL 在执行 JOIN 查询时并不会简单地将所有数据加载到内存再进行 WHERE 过滤。相反，它会根据查询的特性和系统的资源来动态地选择最有效的执行策略。这通常包括在查询的不同阶段应用过滤条件，以减少不必要的数据加载和处理，从而优化查询的性能。

关联查询缺点：

MySQL优化器在处理关联查询时，需要考虑多个表之间的关联关系，这增加了优化器的负担。如果关联查询的语句过于复杂，优化器可能难以有效地进行优化，导致查询效率低下。

关联查询需要按照指定的关联条件将多张表中的数据进行匹配，并生成一个新的虚拟表。这个操作需要消耗大量的系统资源，包括CPU、内存和硬盘空间等。

关联查询通常需要访问多个表，这可能导致查询速度变慢，尤其是在处理大量数据时。

内连接

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是右表，又称为主表。

join查询会产生临时表吗

可能存在内存临时表和磁盘临时表都不产生的情况
在某些情况下，MySQL 可能会选择使用内存临时表来存储中间结果。这通常发生在查询的结果集相对较小，且系统有足够的内存来存储这些结果时。使用内存临时表通常比使用磁盘临时表更快
当查询的结果集较大，或者由于某些系统配置（如 tmp_table_size 和 max_heap_table_size 的设置）导致内存临时表无法使用时，MySQL 可能会选择使用磁盘临时表。磁盘临时表通常存储在 MySQL 的临时目录中（由 tmpdir 系统变量指定）。
你可以使用 EXPLAIN 语句来查看 MySQL 如何执行你的 JOIN 查询。在 EXPLAIN 的输出中，如果 Extra 列包含了 Using temporary，那就意味着查询在执行过程中使用了临时表。

UNION[ALL] 合并结果集

查询的两个结果集的列名称不需要相同，只要满足列的个数和类型一样就可以；右表查询出来的结果集会被合到最左边表中

个数和类型不一样查询会报错

select * from one union all select * from two

UNION，先获取到两个查询的结果集，再去除重复行；重复的行是指结果中每个属性都一样

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

查询联系

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;

posted @ 2024-03-27 18:09 星光闪闪阅读(46) 评论(0) 收藏举报

刷新页面返回顶部

星光闪闪