mysql刷题(不定时更新)
程序员文章站
2022-03-26 22:33:41
面试阶段大家基本都会问一些mysql的题,具体的高深理论以后再慢慢补充,但是刷题是不可避免的,下面直接上货 创建/删除表和索引系列 创建表 sql CREATE TABLE if not exists ( int(11) NOT NULL AUTO_INCREMENT, date DEFAULT N ......
面试阶段大家基本都会问一些mysql的题,具体的高深理论以后再慢慢补充,但是刷题是不可避免的,下面直接上货
创建/删除表和索引系列
- 创建表
create table if not exists `test_date` ( `id` int(11) not null auto_increment, `date` date default null, `temp` int(11) not null, `updatetime` timestamp not null default '0000-00-00 00:00:00' on update current_timestamp comment '更新时间', primary key (`id`) ) engine=innodb default charset=utf8mb4;
- 删除表
drop table if exists person;
- 清空表(delete不重置自增键,truncate重置,truncate不写日志速度更快)
delete from person; truncate table person; truncate person;
- 增加索引
#alter table添加方式 1.添加primary key(主键索引) alter table `table_name` add primary key ( `column` ) 2.添加unique(唯一索引) alter table `table_name` add unique ( `column` ) 3.添加index(普通索引) alter table `table_name` add index index_name ( `column` ) 4.添加fulltext(全文索引) alter table `table_name` add fulltext ( `column`) 5.添加多列索引 alter table `table_name` add index index_name ( `column1`, `column2`, `column3` ) #create方式只能添加这两种索引; create index index_name on table_name (column_list) create unique index index_name on table_name (column_list)
- 删除索引
drop index index_name on table_name ; alter table table_name drop index index_name ; alter table table_name drop primary key ;
账户相关/权限分配
- 查看已经存在的用户
select user,host from mysql.user;
- 创建mysql 用户
格式:create user 'username'@'host' identified by 'password';
create user 'vinter'@'%' identified by '123456'; create user 'jerry'@'localhost' identified by '123456'; create user 'tom'@'126.96.10.26' identified by '123456'; 解析: username 用户名 host 主机 password 密码 localhost 只可以本地登陆 % 本地登陆,远程登陆 126.96.10.26 指定登陆的ip
- 删除mysql 用户:
格式:drop user 'username'@'host';
drop user 'vinter'@'localhost';
- 用户授权:
格式:grant crud on database.tables to 'username'@'host';
grant all on *.* to 'vinter'@'%'; grant select on blog.article to 'vinter'@'%';
- 修改host 可以远程登陆
set sql_safe_updates = 0 update mysql.user set host = '%' where user = 'root'
- 修改密码
set password for 'username'@'host' = password('新密码');
set password for root@localhost = password('123');
或者直接更新表:
use mysql; update user set password=password('123') where user='root' and host='localhost'; flush privileges;
数据查重
- 查询重复数据
编写一个 sql查询 来查找名为 person 的表中的所有重复电子邮件。 示例: +----+---------+ | id | email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ 根据以上输入,您的查询应返回以下结果: +---------+ | email | +---------+ | a@b.com | +---------+
答案及解析:
#重复的也就是数量大于一的(主要考虑group by having的用法,但是题目却不指名分组) select email from person group by email having count( * ) >1
- 删除重复数据
编写一个sql查询来删除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 | +----+------------------+
答案及解析:
#这里还是考虑group by 的用法,但是题目却不指名分组) delete from person where id not in ( select id from ( select min( id ) as id from person st group by email ) temp ); select * from person; #这里解释一下为什么要套双层,不能直接写成 delete from person where id not in ( select min( id ) as id from person st group by email ); 会提示如下错误: you can't specify target table 'person' for update in from clause 这是因为mysql不允许同时删除和查询一个表,这里我们是用一个临时表temp来避免这种问题。
逻辑判断
- 按条件更新数据
给定一个工资表,如下所示,m=男性 和 f=女性 。交换所有的 f 和 m 值 例如,将所有 f 值更改为 m,反之亦然。要求使用一个更新查询,并且没有中间临时表。 | id | name | sex | salary | |----|------|-----|--------| | 1 | a | m | 2500 | | 2 | b | f | 1500 | | 3 | c | m | 5500 | | 4 | d | f | 500 | 运行你所编写的查询语句之后,将会得到以下表: | id | name | sex | salary | |----|------|-----|--------| | 1 | a | f | 2500 | | 2 | b | m | 1500 | | 3 | c | f | 5500 | | 4 | d | m | 500 |
if的用法:
if(字段=值,前面条件为真值,前面条件为假的值)
正解:
update salary set sex = if(sex='m', 'f', 'm')
when case用法
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的,小美想改变相邻俩学生的座位。你能不能帮她写一个 sql query 来输出小美想要的结果呢? 示例: +---------+---------+ | id | student | +---------+---------+ | 1 | abbot | | 2 | doris | | 3 | emerson | | 4 | green | | 5 | jeames | +---------+---------+ 假如数据输入的是上表,则输出结果如下: +---------+---------+ | id | student | +---------+---------+ | 1 | doris | | 2 | abbot | | 3 | green | | 4 | emerson | | 5 | jeames | +---------+---------+ 注意:如果学生人数是奇数,则不需要改变最后一个同学的座位。
正解:
select case when mod ( id, 2 ) = 1 and id != ( select max( id ) from person ) then id + 1 when mod ( id, 2 ) = 0 then id - 1 else id end id, email from person order by id
4.常用函数类型
- 取余函数 mod()
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 led显示板做电影推荐,上面公布着影评和相关电影描述。 作为该电影院的信息部主管,您需要编写一个 sql查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。 例如,下表 cinema: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | war | great 3d | 8.9 | | 2 | science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | ice song | fantacy | 8.6 | | 5 | house card| interesting| 9.1 | +---------+-----------+--------------+-----------+ 对于上面的例子,则正确的输出是为: +---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | house card| interesting| 9.1 | | 1 | war | great 3d | 8.9 | +---------+-----------+--------------+-----------+
正解:
select id, movie, description, rating from cinema where description != 'boring' and mod ( id, 2 ) = 1 order by rating desc
- to_days函数(将日期转换成天数的时间戳)
given a weather table, write a sql query to find all dates' ids with higher temperature compared to its previous (yesterday's) dates. 翻译:给定一个天气表,写一个语句用来找出比前一天气温高的条目的id +---------+------------+------------------+ | id(int) | date(date) | temperature(int) | +---------+------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------+------------------+ for example, return the following ids for the above weather table: +----+ | id | +----+ | 2 | | 4 | +----+
正解:
select w1.id from weather w1, weather w2 where to_days( w1.date ) = to_days( w2.date ) + 1 and w1.temperature > w2.temperature 解析:当你select * from table1,table2 ...的时候会显示出两个表的笛卡尔积 (即查出的记录中每一个table1的条目都对应table2的所有条目)
5 其他
- 笛卡尔积
假设一个网站包含两个表,customers 表和 orders 表。编写一个sql语句找出所有从不订购任何东西的客户。 表名: customers。 +----+-------+ | id | name | +----+-------+ | 1 | joe | | 2 | henry | | 3 | sam | | 4 | max | +----+-------+ table: orders. +----+------------+ | id | customerid | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ 以上述表格为例,返回以下内容: +-----------+ | customers | +-----------+ | henry | | max | +-----------+
正解:
select name from customers where customers.id not in (select customerid from orders)
- 连接的join用法
the employee table holds all employees including their managers. every employee has an id, and there is also a column for the manager id. +----+-------+--------+-----------+ | id | name | salary | managerid | +----+-------+--------+-----------+ | 1 | joe | 70000 | 3 | | 2 | henry | 80000 | 4 | | 3 | sam | 60000 | null | | 4 | max | 90000 | null | +----+-------+--------+-----------+ given the employee table, write a sql query that finds out employees who earn more than their managers. for the above table, joe is the only employee who earns more than his manager. +----------+ | employee | +----------+ | joe | +----------+
正解:
#方法1: select e.name from employee e join employee m on e.managerid = m.id and e.salary > m.salary; #方法2: select e.name from employee e, employee m where e.managerid = m.id and e.salary > m.salary; 解析:一种是显示连接一种是隐式连接