SQL考试

1.下列sql符合规范的是

A select name, code from table where id = 1

B select user_name, user_code from table where id = 1

C CREATE TABLE table ( `select` int )

D select userName from table where id = 1

2.下面字段定义符合规范的是

A `price` float(20) NOT NULL COMMENT '价格，单位元'

B `price` bigint(20) NOT NULL DEFAULT '0' COMMENT '价格，单位分'

C CREATE TABLE table (col int) ENGINE=InnoDB

D `create_time` datetime COMMENT '创建时间'

3.下面SQL符合规范的是

A select order_id from ORDER where status != 0

B select user_name from table where id = '1'

C select c_id,user_name from table where user_name like '%test%'

D select user_name from table where id > 100000 limit 10

4.下面的操作符合业务要求的是题目

A select * from table where order_id = 1;

B alter table XX drop column name

C update table set status = 1 where id =1

D delete from table where id =1

5.【多选题】下列索引定义不符合规范的是

A uniq_order_id

B idx_user_id

C user_id_order_id

D idx_a_b_c_d_e_f

6.下面的SQL符合规范的是

A select * from table where id in (...........); 有1000个

B select count(id) from table where id > 100

C select * from table where id not in (1,2,3,100,200)

D select id,user_name,address from table where user_name like 'test%'

7.下面哪个SQL的实现较优是

A select user_name,address from table where user_name=xx order by id asc limit 100000, 20;

B select id from table where substring(user_name,1,3)='abc';

C SELECT COUNT(*) FROM table where id < 100;

D select table_name_one.user_name from (table_name_one, table_name_two) join table_name_two on table_name_one.id=table_name_two.id where table_name_one.id=1;

8.如Order表中有a、b、c三个字段，联合索引建立如下 idx_a_b_c 下面SQL 哪些不能命中索引多选

【多选题】【中】（10分）

A select order_id from ORDER where b ='xxx';

B select order_id from ORDER where b ='xxx' and c = 'xxx';

C select order_id from ORDER where a = 'xxx';

D select order_id from ORDER where a = 'xxx' and b ='xxx';

E select order_id from ORDER where b ='xxx' and a = 'xxx';

F select order_id from ORDER where a = 'xxx' and b ='xxx' and c = 'xxx';

考生本题得分：0.0分

9.如Order表中有a、b、c三个字段，联合索引建立如下 idx_a_b_c 对于select order_id from ORDER where a = 'xxx' and b > 'xxx' order by c 下列说法正确的是

【单选题】【中】（10分）

A 不能命中索引

B 可以命中a_b_c

C 可以命中a_b

D 可以命中a

考生本题得分：10.0分

10.如Order表中有a、b、c三个字段，联合索引建立如下 idx_a_b_c 对于select order_id from ORDER where a = 'xxx' and b = 'xxx' order by c 下列说法正确的是

【单选题】【中】（10分）

A 不能命中索引

B 可以命中a_b_c

C 可以命中a_b

D 可以命中a

答案：

1-5：B    B    D    C    CD

6-10：D    C    AB    C     B

2:正例：DATETIME(3)