MySQL连表查询练习题(二)
MySQL连表查询练习题(二)
1.创建oldboy库，并将其字符集定义为utf8。
mysql> create database oldboy charset utf8 collate utf8_general_ci;
2.已知现在在oldboy库中，请创建total表，要求创建name,course,score,sid列，都是非空，其中sid列是唯一且非空和自增长的。注释依次为姓名，学科，成绩，学号。字符集为utf8,引擎为innodb。
mysql> use oldboy
mysql> create table total (
-> sid int not null primary key auto_increment comment '学号',
-> name varchar(20) not null comment '姓名',
-> course varchar(20) not null comment '学科',
-> score tinyint unsigned not null comment '成绩');
mysql> show create table total;
| total | CREATE TABLE `total` (
`sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`course` varchar(20) NOT NULL COMMENT '学科',
`score` tinyint(3) unsigned NOT NULL COMMENT '成绩',
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3.插入数据到total表中。
mysql> insert into total(name,course,score) values
-> ('张小飞','语文',60),('王铁柱','语文',70),('李红','语文',80),('吕凤','语文',90),
-> ('张小飞','数学',80),('王铁柱','数学',90),('李红','数学',60),('吕凤','数学',70),
-> ('张小飞','英语',50),('王铁柱','英语',55),('李红','英语',50),('吕凤','英语',60);
mysql> select * from total;
+-----+-----------+--------+-------+
| sid | name | course | score |
+-----+-----------+--------+-------+
| 1 | 张小飞 | 语文 | 60 |
| 2 | 王铁柱 | 语文 | 70 |
| 3 | 李红 | 语文 | 80 |
| 4 | 吕凤 | 语文 | 90 |
| 5 | 张小飞 | 数学 | 80 |
| 6 | 王铁柱 | 数学 | 90 |
| 7 | 李红 | 数学 | 60 |
| 8 | 吕凤 | 数学 | 70 |
| 9 | 张小飞 | 英语 | 50 |
| 10 | 王铁柱 | 英语 | 55 |
| 11 | 李红 | 英语 | 50 |
| 12 | 吕凤 | 英语 | 60 |
+-----+-----------+--------+-------+
4.分别查询每位同学的最低分和最高分。
mysql> select name,max(score),min(score) from total group by name;
+-----------+------------+------------+
| name | max(score) | min(score) |
+-----------+------------+------------+
| 吕凤 | 90 | 60 |
| 张小飞 | 80 | 50 |
| 李红 | 80 | 50 |
| 王铁柱 | 90 | 55 |
+-----------+------------+------------+
5.查询张小飞同学的语文成绩。
mysql> select * from total where name='张小飞' and course='语文';
+-----+-----------+--------+-------+
| sid | name | course | score |
+-----+-----------+--------+-------+
| 1 | 张小飞 | 语文 | 60 |
+-----+-----------+--------+-------+
6.查询每位同学的总分。
mysql> select name,sum(score) from total group by name;
+-----------+------------+
| name | sum(score) |
+-----------+------------+
| 吕凤 | 220 |
| 张小飞 | 190 |
| 李红 | 190 |
| 王铁柱 | 215 |
+-----------+------------+
7.查询每位同学的平均分且平均分大于70的。
mysql> select name,avg(score),avg(score)>70 from total group by name;
+-----------+------------+---------------+
| name | avg(score) | avg(score)>70 |
+-----------+------------+---------------+
| 吕凤 | 73.3333 | 1 |
| 张小飞 | 63.3333 | 0 |
| 李红 | 63.3333 | 0 |
| 王铁柱 | 71.6667 | 1 |
+-----------+------------+---------------+
mysql> select name,avg(score) from total group by name having avg(score)>70;
+-----------+------------+
| name | avg(score) |
+-----------+------------+
| 吕凤 | 73.3333 |
| 王铁柱 | 71.6667 |
+-----------+------------+
8.统计总共有几名学生。
mysql> select count(distinct(name)) from total;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 4 |
+-----------------------+
9.把王铁柱同学的英语分数修改为80分。
mysql> update total set score='80' where name='王铁柱'and course='英语';
mysql> select * from total;
+-----+-----------+--------+-------+
| sid | name | course | score |
+-----+-----------+--------+-------+
| 1 | 张小飞 | 语文 | 60 |
| 2 | 王铁柱 | 语文 | 70 |
| 3 | 李红 | 语文 | 80 |
| 4 | 吕凤 | 语文 | 90 |
| 5 | 张小飞 | 数学 | 80 |
| 6 | 王铁柱 | 数学 | 90 |
| 7 | 李红 | 数学 | 60 |
| 8 | 吕凤 | 数学 | 70 |
| 9 | 张小飞 | 英语 | 50 |
| 10 | 王铁柱 | 英语 | 80 |
| 11 | 李红 | 英语 | 50 |
| 12 | 吕凤 | 英语 | 60 |
+-----+-----------+--------+-------+
10.查询总分最高的同学。
mysql> select name,sum(score) from total group by name desc limit 1;
+-----------+------------+
| name | sum(score) |
+-----------+------------+
| 王铁柱 | 240 |
+-----------+------------+
11.查看total表的创表语句。
mysql> show create table total;
| total | CREATE TABLE `total` (
`sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`course` varchar(20) NOT NULL COMMENT '学科',
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
12.把total表的表名修改为oldboy。
mysql> alter table total rename oldboy;
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| oldboy |
+------------------+
13.给oldboy表的name列创建一个索引。
mysql> alter table oldboy add index idx_name(name);
mysql> desc oldboy;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | MUL | NULL | |
| course | varchar(20) | NO | | NULL | |
| score | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
14.查看oldboy表的所有索引。
mysql> mysql> show index from oldboy;
+--------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+--------+------------+----------+--------------+-------------+
| oldboy | 0 | PRIMARY | 1 | sid |
| oldboy | 1 | idx_name | 1 | name |
+--------+------------+----------+--------------+-------------+
15.删除oldboy表name列的索引。
mysql> alter table oldboy drop index idx_name;
16.查看数据库的默认的引擎类别。
mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
17.查看共享表空间的参数
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
18.创建用户dev给予此用户所有库下的所有表select update insert delete权限，host为'10.0.0.%' 并设置密码为123456。
mysql> grant select,update,insert,delete on *.* to dev@'10.0.0.%' identified by '123456';
- 查看dev用户的权限。
mysql> show grants for dev@'10.0.0.%'\G;
*************************** 1. row ***************************
Grants for dev@10.0.0.%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'dev'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
- 查看数据库中所有的用户的名称，密码，主机。
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | |
| root | db01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db01 | |
| root | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| msy | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| dev | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
- 显示你当前所在的库
mysql> select database();
浙公网安备 33010602011771号