MySQL

Question 1

从user表查询score为100并且country为China的username,password

从user表查询score为100或者country为China的username,password

Answer 1

select username,password from user where score=100 and country=’China;

select username,password from user where score=100 or country=’China;

Question 2

说说having的作用和用法

Answer 2

having用作筛选
SELECT cust_id, COUNT() AS orders FROM orders GROUP BY cust_id HAVING COUNT() > 2

where在分组前过滤，having在分组后过滤

Question 3

查看所有数据库

Answer 3

show databases；

Question 4

创建一个名为MYSQLDATA的数据库

Answer 4

create database MYSQLDATA；

Question 5

从products表中查询price的平均值
从products表中查询price的最大值
从products表中查询price的最小值
从products表中查询price的综合
查询price共有多少行（空也算）
查询price共有多少行（空的不算）

Answer 5

select avg(price) as avgprice from products;
select max(price) as avgprice from products;
select min(price) as avgprice from products;
select sum(price) as avgprice from products;
select count(*) from products;空的也算
select count(price) from products；空的不算

Question 6

在user表中插入一条数据：
username：hijack
passwd：123456

在user表中插入6条数据：
username：hijack1
passwd：1
username：hijack2
passwd：2
username：hijack3
passwd：3
username：hijack4
passwd：4
username：hijack5
passwd：5
username：hijack6
passwd：6

Answer 6

insert into user (username,passwd) values (‘hijack’,’123456’);

insert into user (username,passwd) values (‘hijack1’,’1’), (‘hijack2’,’2’), (‘hijack3’,’3’), (‘hijack4’,’4’), (‘hijack5’,’5’), (‘hijack6’,’6’);

Question 7

从user表中检索name,password列，按照列id降序排列

Answer 7

select name,password from user order by id desc;

Question 8

从score这个表中检索class列

Answer 8

select class from score；

Question 9

union干什么用的

Answer 9

用于合并两个或多个select语句的结果集，并消除重复行
如果要保留重复行，用union all
select column_name from table1
union
select column_name from table2

Question 10

从user表中查询id=8的username，passwd

从user表中查询score不等于100的username，passwd

Answer 10

select username,passwd from user where id=8;

select username,passwd from score where score <>100;

Question 11

从user表中查询username和passwd，结果用username—-passwd形式表示

Answer 11

select concat(username,’—-‘,passwd) from user

Question 12

切换数据库：MYSQLDATA

Answer 12

use MYSQLDATA；

Question 13

登陆mysql
用户名：hijack
主机名：myserver
端口：9999

Answer 13

mysql -u hijack -p -h myserver -P 9999

Question 14

从score这个表中检索所有列

Answer 14

select * from score；

Question 15

select子句的顺序是什么

Answer 15

select
from
where
group by
having
order by
limit

Question 16

列出表score表中所有的列信息

Answer 16

show columns from score；
或
desc score；

Question 17

列出数据库mysqldata的所有表

Answer 17

use mysqldata；

show tables；

Question 18

从score表中检索class列，并按照列age排序

从product表中检索name，price列，并按照country，date排序

Answer 18

select class from scroe order by age;
select name,price from product order by country,date;

Question 19

从score这个表中检索class列和id列

Answer 19

select class,id from score；

Question 20

从products表中查询count和price的乘积

Answer 20

select count*price as total_price from products;

Question 21

取消LILI的存取students表的特权

Answer 21

revoke all on students from LILI CASCADE;

Question 22

使用group by时应注意哪些？

Answer 22

select id,count(*) as num_prods from tb_name group by id;
group by 可以包含任意数目的列
group by 中每个列都必须是检索列或有效的表达式（但不能使聚集函数）
group by 子句必须出现在where子句之后，order by之前。

Question 23

从user表中查询id，条件是name是han开头的

Answer 23

select id from user where name like ‘han%’
%匹配0个或多个任意字符
如果是单个字符，用_,如：
select id from user where name like ‘_an’

Question 24

用正则表达式从products表中查询prod_name,要求prod_id为’1000’或’2000’或‘3000’或’4000’或’5000’

Answer 24

select prod_name from products where prod_id regexp ‘[1-5]000’;

Question 25

从user表中查询username并去除两边的空白字符，然后显示

Answer 25

select trim(username) from user;

Question 26

删除user表中的id为10006的用户数据

Answer 26

delete from user where id=10006;

Question 27

从user表中查询score为空的username，passwd

Answer 27

select username,passwd from user where score is NULL;

Question 28

把user表中的id为10005的用户的passwd改为’123456’

Answer 28

update user set passwd=’123456’ where id=10005;

Question 29

从score表中检索class列，并且去重复

Answer 29

select distinct class from score;

Question 30

从user表中查询score在60和80之间的username，passwd

Answer 30

select username,passwd from user where score between 60 and 80;

Question 31

授予LILI有对表STUDENTS的查询权

Answer 31

grant select on students to LILI with grant option

Question 32

从表score中检索class列，限制返回不多于5行

从表score中检索class列，从第二行开始返回，返回5行数据

Answer 32

select class from score limit 5;
select class from score limit 1,5;
limit初始为0