mysql--查询练习--数据准备

---mysql 查询练习
--学生表
--student
--学号
--姓名
--性别
--出生年月日
--所在班级
create table student(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(20) not null,
    sbirthday datetime,
    class varchar(20)
    );


--教师表
--teacher
--教师编号
--教师名字
--教师性别
--出生年月日
--职称
--所在部门
create table teacher(
    tno varchar(20) primary key,
    tname varchar(20) not null,
    tsex varchar(20) not null,
    tbirthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
);



--课程表
--course
--课程号
--课程名称
--教师编号
create table course(
    cno varchar(20) primary key,
    cname varchar(20) not null,
    tno varchar(20) not null,
    foreign key(tno) references teacher(tno));

--成绩表
--score
--学号
--课程号
--成绩
create table score(
    sno varchar(20) not null,
    cno varchar(20) not null,
    degree decimal,
    foreign key(sno) references student(sno),
    foreign key(cno) references course(cno),
    primary key(sno,cno)

);

---设置联合主键，学号和课程组合唯一；；；；；

---设置联合主键，学号和课程组合唯一；；；；；

往数据表中添加数据

mysql> insert into student values(100,"xiaozhan","男","1991-10-05","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(101,"yibo","男","1997-08-05","20110341");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(102,"dandan","女","1993-09-05","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(103,"tingting","女","1993-11-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(104,"junjie","男","1994-1-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(105,"peiyu","男","1994-07-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+----------+------+---------------------+----------+
| sno | sname    | ssex | sbirthday           | class    |
+-----+----------+------+---------------------+----------+
| 100 | xiaozhan | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo     | 男   | 1997-08-05 00:00:00 | 20110341 |
| 102 | dandan   | 女   | 1993-09-05 00:00:00 | 20110341 |
| 103 | tingting | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie   | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu    | 男   | 1994-07-15 00:00:00 | 20110341 |
+-----+----------+------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> insert into student values(106,"wangnima","男","1993-07-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(107,"zhaotiezhu","男","1993-07-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(108,"zhaotiezhu","女","1993-07-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 20110341 |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 106 | wangnima   | 男   | 1993-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
9 rows in set (0.00 sec)

mysql> insert into student values(109,"xudijian","男","1993-11-15","20110341");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 20110341 |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 106 | wangnima   | 男   | 1993-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
| 109 | xudijian   | 男   | 1993-11-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
10 rows in set (0.01 sec)

mysql> inset teacher values(101,"zhangsan","男",“1958-09-11”,"计算机系");
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 'inset teacher values(101,"zhangsan","男",“1958-09-11”,"计算机系")' at line 1
mysql> insert teacher values(101,"zhangsan","男","1958-09-11","计算机系");
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert teacher values(101,"zhangsan","男","1958-09-11","教授","计算机系");
Query OK, 1 row affected (0.00 sec)

mysql> insert teacher values(102,"zhangsi","男","1658-09-11","副教授","计算机系");
Query OK, 1 row affected (0.00 sec)

mysql> insert teacher values(103,"lisi","女","1678-09-10","讲师","计算机系");
Query OK, 1 row affected (0.00 sec)

mysql> insert teacher values(104,"liwu","女","1988-09-10","助教","计算机系");
Query OK, 1 row affected (0.00 sec)

mysql> update teacher set tbirthday="1958-09-11" where tbirthday="1658-09-11";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update teacher set tbirthday="1978-09-10" where tbirthday="1678-09-10";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+-----+----------+------+---------------------+-----------+--------------+
| tno | tname    | tsex | tbirthday           | prof      | depart       |
+-----+----------+------+---------------------+-----------+--------------+
| 101 | zhangsan | 男   | 1958-09-11 00:00:00 | 教授      | 计算机系     |
| 102 | zhangsi  | 男   | 1958-09-11 00:00:00 | 副教授    | 计算机系     |
| 103 | lisi     | 女   | 1978-09-10 00:00:00 | 讲师      | 计算机系     |
| 104 | liwu     | 女   | 1988-09-10 00:00:00 | 助教      | 计算机系     |
+-----+----------+------+---------------------+-----------+--------------+
4 rows in set (0.00 sec)

mysql> mysql
    -> ;
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 'inser' at line 1
mysql> insert into course values("3-105","计算机导论","104");
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values("3-106","高等数学","103");
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values("3-107","JAVA","102");
Query OK, 1 row affected (0.00 sec)

mysql> insert into course values("3-108","python","101");
Query OK, 1 row affected (0.00 sec)

mysql> select * from course;
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 104 |
| 3-106 | 高等数学        | 103 |
| 3-107 | JAVA            | 102 |
| 3-108 | python          | 101 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql> mysql> insert into score(101,"3-105","99");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(101,"3-106","100");
ERROR 1062 (23000): Duplicate entry '101' for key 'score.PRIMARY'
mysql> insert into score values(102,"3-106","100");
Query OK, 1 row affected (0.00 sec)

mysql> alter table score drop primary key;
ERROR 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint
mysql> drop table score;
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal,
    -> foreign key(sno) references student(sno),
    -> foreign key(cno) references course(cno),
    -> primary key(sno,cno)
    -> 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> select * from score;
Empty set (0.00 sec)

mysql> insert into score values(100,"3-105","99");
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(100,"3-106","98");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(100,"3-107","98");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(100,"3-107","90");
ERROR 1062 (23000): Duplicate entry '100-3-107' for key 'score.PRIMARY'
mysql> insert into score values(100,"3-108","90");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(101,"3-105","90");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(101,"3-106","90");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(101,"3-107","79");
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(101,"3-108","80");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(102,"3-108","80");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(103,"3-108","80");
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(104,"3-108","60");
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values(105,"3-108","66");
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-105 |     99 |
| 100 | 3-106 |     98 |
| 100 | 3-107 |     98 |
| 100 | 3-108 |     90 |
| 101 | 3-105 |     90 |
| 101 | 3-106 |     90 |
| 101 | 3-107 |     79 |
| 101 | 3-108 |     80 |
| 102 | 3-108 |     80 |
| 103 | 3-108 |     80 |
| 104 | 3-108 |     60 |
| 105 | 3-108 |     66 |
+-----+-------+--------+
12 rows in set (0.00 sec)

mysql>