数据库：Mysql新建表，题目练习

最新推荐文章于 2024-07-26 14:20:58 发布

cn2719691

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量603

点赞数

文章标签：数据库

mysql> desc SC;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| Sno   | int(100) | NO   | PRI | NULL    |       |
| Cno   | char(100) | NO   | PRI | NULL    |       |
| score | int(100) | YES |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1.查看数据库：show databases;
2.选择数据库：USE 数据库名；use test; #ERROR 1046 (3D000): No database selected
3.创建表：
Student(S#,Sname,Sage,Ssex) 学生表
Sno：学号；
Sname：学生姓名；
Sage：学生年龄；
Ssex：学生性别

create table Student(
Sno int(100) NOT NULL,
Sname varchar(100),
Sage int(20),
Ssex bool,
PRIMARY KEY (Sno)
);

布尔型：0和1，自己定义，0代表男，1代表女
insert into Student values (1,'TOM',20,0);
insert into Student values (2,'TOM1',21,0);
insert into Student values (3,'TOM2',22,1);
insert into Student values (4,'TOM3',23,1);
insert into Student values (5,'TOM4',24,1);
insert into Student values (6,'TOM5',25,0);
insert into Student values (7,'TOM6',26,0);
insert into Student values (8,'TOM7',27,1);
insert into Student values (9,'TOM8',28,1);
insert into Student values (10,'TOM9',29,0);

Course(Cno,Cname,Tno) 课程表
Cno,课程编号；
Cname：课程名字；
Tno：教师编号

create table Course(
Cno int(100) NOT NULL,
Cname varchar(200),
Tno varchar(200),
PRIMARY KEY (Cno)
);
alter table Course change Cno Cno char(100) not null;

Insert into Course values ('C01','Math', 'T01');
Insert into Course values ('C02','English', 'T02');
Insert into Course values ('C03','History', 'T03');

SC(Sno,Cno,score) 成绩表
Sno：学号；
Cno,课程编号；
score：成绩

create table SC(
Sno int(100) NOT NULL,
Cno int(100) NOT NULL,
score int(100),
PRIMARY KEY (Sno,Cno)
);

alter table SC change Cno Cno char(100) not null;
insert into SC values (1,'C01',99);
insert into SC values (2,'C01',100);
insert into SC values (3,'C01',95);
insert into SC values (4,'C01',90);
insert into SC values (5,'C01',60);
insert into SC values (6,'C01',65);
insert into SC values (7,'C01',70);
insert into SC values (8,'C01',78);
insert into SC values (9,'C01',81);
insert into SC values (10,'C01',59);
insert into SC values (1,'C02',79);
insert into SC values (2,'C02',80);
insert into SC values (3,'C02',95);
insert into SC values (4,'C02',60);
insert into SC values (5,'C02',80);
insert into SC values (6,'C02',65);
insert into SC values (7,'C02',78);
insert into SC values (8,'C02',58);
insert into SC values (9,'C02',62);
insert into SC values (10,'C02',90);
insert into SC values (1,'C03',96);
insert into SC values (2,'C03',85);
insert into SC values (3,'C03',96);
insert into SC values (4,'C03',73);
insert into SC values (5,'C03',82);
insert into SC values (6,'C03',56);
insert into SC values (7,'C03',42);
insert into SC values (8,'C03',63);
insert into SC values (9,'C03',72);
insert into SC values (10,'C03',81);

Teacher(Tno,Tname) 教师表
Tno：教师编号；
Tname：教师名

create table Teacher(
Tno int(100) NOT NULL,
Tname varchar(200),
PRIMARY KEY (Tno)
);
alter table Teacher change Tno Tno char(100) not null;

Insert into Course values ('T01','Teacher1');
Insert into Course values ('T02', Teacher2');
Insert into Course values ('T03', Teacher3');

1、查询“001（C01）”课程比“002（C02）”课程成绩高的所有学生的学号

普通查询：
mysql> select t1.Sno from SC t1 where t1.Cno='C01' and t1.score < (select t2.score from SC t2 where t2.Cno='C02' and t2.Sno =t1.Sno);
+-----+
| Sno |
+-----+
| 5 |
| 7 |
| 10 |
+-----+
3 rows in set (0.00 sec)

优化版：
mysql> select t1.Sno from SC t1 inner join SC t2 on t1.Sno=t2.Sno and t1.score +-----+
| Sno |
+-----+
| 5 |
| 7 |
| 10 |
+-----+
3 rows in set (0.00 sec)

2.查询平均成绩大于60分的同学的学号和平均成绩

mysql> select Sno,AVG(Score) from SC group by Sno having AVG(Score)>60;
+-----+------------+
| Sno | AVG(Score) |
+-----+------------+
|   1 |    91.3333 |
|   2 |    88.3333 |
|   3 |    95.3333 |
|   4 |    74.3333 |
|   5 |    74.0000 |
|   6 |    62.0000 |
|   7 |    63.3333 |
|   8 |    66.3333 |
|   9 |    71.6667 |
| 10 |    76.6667 |
+-----+------------+
10 rows in set (0.00 sec)

3、查询所有同学的学号、姓名、选课数、总成绩；

mysql> select SC.Sno, stu.Sname,count(Cno),sum(score) from SC, Student stu where SC.Sno =stu.Sno group by SC.Sno, stu.Sname;
+-----+-------+------------+------------+
| Sno | Sname | count(Cno) | sum(score) |
+-----+-------+------------+------------+
|   1 | TOM   |          3 |        274 |
|   2 | TOM1 |          3 |        265 |
|   3 | TOM2 |          3 |        286 |
|   4 | TOM3 |          3 |        223 |
|   5 | TOM4 |          3 |        222 |
|   6 | TOM5 |          3 |        186 |
|   7 | TOM6 |          3 |        190 |
|   8 | TOM7 |          3 |        199 |
|   9 | TOM8 |          3 |        215 |
| 10 | TOM9 |          3 |        230 |
+-----+-------+------------+------------+
10 rows in set (0.00 sec)

4、查询姓“李”的老师的个数；

mysql> select count(*) from Teacher where Tname like 'Teacher1%';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from Teacher where Tname='Teacher1';
+----------+
| count(1) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

count(*)和count(1)的区别：

5、查询没学过“叶平”老师课的同学的学号、姓名；
mysql> select stu.Sno,stu.Sname from SC sc,Student stu where stu.Sno=sc.Sno and sc.Cno in (select cno from Course where Tno not in(select Tno from Teacher where Tname='Teacher1'));
+-----+-------+
| Sno | Sname |
+-----+-------+
|   1 | TOM   |
|   1 | TOM   |
|   2 | TOM1 |
|   2 | TOM1 |
|   3 | TOM2 |
|   3 | TOM2 |
|   4 | TOM3 |
|   4 | TOM3 |
|   5 | TOM4 |
|   5 | TOM4 |
|   6 | TOM5 |
|   6 | TOM5 |
|   7 | TOM6 |
|   7 | TOM6 |
|   8 | TOM7 |
|   8 | TOM7 |
|   9 | TOM8 |
|   9 | TOM8 |
| 10 | TOM9 |
+-----+-------+
19 rows in set (0.00 sec)

mysql> select distinct stu.Sno,stu.Sname from SC sc,Student stu where stu.Sno=sc.Sno and sc.Cno in (select cno from Course where Tno not in(select Tno from Teacher where Tname='Teacher1'));
+-----+-------+
| Sno | Sname |
+-----+-------+
|   1 | TOM   |
|   2 | TOM1 |
|   3 | TOM2 |
|   4 | TOM3 |
|   5 | TOM4 |
|   6 | TOM5 |
|   7 | TOM6 |
|   8 | TOM7 |
|   9 | TOM8 |
| 10 | TOM9 |
+-----+-------+
10 rows in set (0.00 sec)

mysql> select * from SC;
+-----+-----+-------+
| Sno | Cno | score |
+-----+-----+-------+
|   1 | C01 |    99 |
|   2 | C01 |   100 |
|   3 | C01 |    95 |
|   4 | C01 |    90 |
|   5 | C01 |    60 |
|   6 | C01 |    65 |
|   7 | C01 |    70 |
|   8 | C01 |    78 |
|   9 | C01 |    81 |
| 10 | C01 |    59 |
|   1 | C02 |    79 |
|   2 | C02 |    80 |
|   3 | C02 |    95 |
|   4 | C02 |    60 |
|   5 | C02 |    80 |
|   6 | C02 |    65 |
|   7 | C02 |    78 |
|   8 | C02 |    58 |
|   9 | C02 |    62 |
|   1 | C03 |    96 |
|   2 | C03 |    85 |
|   3 | C03 |    96 |
|   4 | C03 |    73 |
|   5 | C03 |    82 |
|   6 | C03 |    56 |
|   7 | C03 |    42 |
|   8 | C03 |    63 |
|   9 | C03 |    72 |
| 10 | C03 |    81 |
+-----+-----+-------+
29 rows in set (0.00 sec)

mysql> select * from Teacher;
+-----+----------+
| Tno | Tname |
+-----+----------+
| T01 | Teacher1 |
| T02 | Teacher2 |
| T03 | Teacher3 |
+-----+----------+
3 rows in set (0.00 sec)

mysql> select * from Course;
+-----+---------+------+
| Cno | Cname | Tno |
+-----+---------+------+
| C01 | Math | T01 |
| C02 | English | T02 |
| C03 | History | T03 |
+-----+---------+------+
3 rows in set (0.00 sec)

mysql> 1 | C01 | 99 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 | C01 | 99' at line 1
mysql> delete from SC where Sno='1' and Tno='C01';
ERROR 1054 (42S22): Unknown column 'Tno' in 'where clause'
mysql> delete from SC where Sno='1' and Cno='C01';
Query OK, 1 row affected (0.00 sec)

mysql> select * from SC;                                                        +-----+-----+-------+
| Sno | Cno | score |
+-----+-----+-------+
|   2 | C01 |   100 |
|   3 | C01 |    95 |
|   4 | C01 |    90 |
|   5 | C01 |    60 |
|   6 | C01 |    65 |
|   7 | C01 |    70 |
|   8 | C01 |    78 |
|   9 | C01 |    81 |
| 10 | C01 |    59 |
|   1 | C02 |    79 |
|   2 | C02 |    80 |
|   3 | C02 |    95 |
|   4 | C02 |    60 |
|   5 | C02 |    80 |
|   6 | C02 |    65 |
|   7 | C02 |    78 |
|   8 | C02 |    58 |
|   9 | C02 |    62 |
|   1 | C03 |    96 |
|   2 | C03 |    85 |
|   3 | C03 |    96 |
|   4 | C03 |    73 |
|   5 | C03 |    82 |
|   6 | C03 |    56 |
|   7 | C03 |    42 |
|   8 | C03 |    63 |
|   9 | C03 |    72 |
| 10 | C03 |    81 |
+-----+-----+-------+
28 rows in set (0.00 sec)

mysql>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29056818/viewspace-768455/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29056818/viewspace-768455/

cn2719691

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
数据库：Mysql新建表，题目练习

mysql> desc Student;+-------+--------------+------+-----+---------+-------+| Field | Type | N...
复制链接

扫一扫