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)
+-------+--------------+------+-----+---------+-------+
| 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)
+-------+--------------+------+-----+---------+-------+
| 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)
+-------+-----------+------+-----+---------+-------+
| 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)
+-------+--------------+------+-----+---------+-------+
| 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)
);
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);
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:教师编号
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;
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');
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)
);
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);
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;
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');
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 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)
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)
+-----+------------+
| 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(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 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)
+-----+-------+
| 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)
+-----+-----+-------+
| 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)
+-----+----------+
| 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)
+-----+---------+------+
| 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)
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)
+-----+-------+
| 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)
| 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/