ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库:Mysql新建表,题目练习

数据库:Mysql新建表,题目练习

原创 Linux操作系统 作者:果果的空间 时间:2013-08-14 10:37:46 0 删除 编辑
mysql> desc Student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Sno   | int(100)     | NO   | PRI | NULL    |       |
| Sname | varchar(100) | YES  |     | NULL    |       |
| Sage  | int(20)      | YES  |     | NULL    |       |
| Ssex  | tinyint(1)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> desc Teacher;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Tno   | char(100)    | NO   | PRI | NULL    |       |
| Tname | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
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)
 
mysql> desc Course;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Cno   | char(100)    | NO   | PRI | NULL    |       |
| Cname | varchar(200) | YES  |     | NULL    |       |
| Tno   | varchar(200) | 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 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 |
+-----+-----+-------+
|   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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-18

  • 博文量
    60
  • 访问量
    117235