---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) ); ---设置联合主键，学号和课程组合唯一；；；；；
1 ---设置联合主键，学号和课程组合唯一；；；；； 2 3 往数据表中添加数据 4 5 mysql> insert into student values(100,"xiaozhan","男","1991-10-05","20110341"); 6 Query OK, 1 row affected (0.00 sec) 7 8 mysql> insert into student values(101,"yibo","男","1997-08-05","20110341"); 9 Query OK, 1 row affected (0.01 sec) 10 11 mysql> insert into student values(102,"dandan","女","1993-09-05","20110341"); 12 Query OK, 1 row affected (0.00 sec) 13 14 mysql> insert into student values(103,"tingting","女","1993-11-15","20110341"); 15 Query OK, 1 row affected (0.00 sec) 16 17 mysql> insert into student values(104,"junjie","男","1994-1-15","20110341"); 18 Query OK, 1 row affected (0.00 sec) 19 20 mysql> insert into student values(105,"peiyu","男","1994-07-15","20110341"); 21 Query OK, 1 row affected (0.00 sec) 22 23 mysql> select * from student; 24 +-----+----------+------+---------------------+----------+ 25 | sno | sname | ssex | sbirthday | class | 26 +-----+----------+------+---------------------+----------+ 27 | 100 | xiaozhan | 男 | 1991-10-05 00:00:00 | 20110341 | 28 | 101 | yibo | 男 | 1997-08-05 00:00:00 | 20110341 | 29 | 102 | dandan | 女 | 1993-09-05 00:00:00 | 20110341 | 30 | 103 | tingting | 女 | 1993-11-15 00:00:00 | 20110341 | 31 | 104 | junjie | 男 | 1994-01-15 00:00:00 | 20110341 | 32 | 105 | peiyu | 男 | 1994-07-15 00:00:00 | 20110341 | 33 +-----+----------+------+---------------------+----------+ 34 6 rows in set (0.00 sec) 35 36 mysql> insert into student values(106,"wangnima","男","1993-07-15","20110341"); 37 Query OK, 1 row affected (0.00 sec) 38 39 mysql> insert into student values(107,"zhaotiezhu","男","1993-07-15","20110341"); 40 Query OK, 1 row affected (0.00 sec) 41 42 mysql> insert into student values(108,"zhaotiezhu","女","1993-07-15","20110341"); 43 Query OK, 1 row affected (0.00 sec) 44 45 mysql> select * from student; 46 +-----+------------+------+---------------------+----------+ 47 | sno | sname | ssex | sbirthday | class | 48 +-----+------------+------+---------------------+----------+ 49 | 100 | xiaozhan | 男 | 1991-10-05 00:00:00 | 20110341 | 50 | 101 | yibo | 男 | 1997-08-05 00:00:00 | 20110341 | 51 | 102 | dandan | 女 | 1993-09-05 00:00:00 | 20110341 | 52 | 103 | tingting | 女 | 1993-11-15 00:00:00 | 20110341 | 53 | 104 | junjie | 男 | 1994-01-15 00:00:00 | 20110341 | 54 | 105 | peiyu | 男 | 1994-07-15 00:00:00 | 20110341 | 55 | 106 | wangnima | 男 | 1993-07-15 00:00:00 | 20110341 | 56 | 107 | zhaotiezhu | 男 | 1993-07-15 00:00:00 | 20110341 | 57 | 108 | zhaotiezhu | 女 | 1993-07-15 00:00:00 | 20110341 | 58 +-----+------------+------+---------------------+----------+ 59 9 rows in set (0.00 sec) 60 61 mysql> insert into student values(109,"xudijian","男","1993-11-15","20110341"); 62 Query OK, 1 row affected (0.00 sec) 63 64 mysql> select * from student; 65 +-----+------------+------+---------------------+----------+ 66 | sno | sname | ssex | sbirthday | class | 67 +-----+------------+------+---------------------+----------+ 68 | 100 | xiaozhan | 男 | 1991-10-05 00:00:00 | 20110341 | 69 | 101 | yibo | 男 | 1997-08-05 00:00:00 | 20110341 | 70 | 102 | dandan | 女 | 1993-09-05 00:00:00 | 20110341 | 71 | 103 | tingting | 女 | 1993-11-15 00:00:00 | 20110341 | 72 | 104 | junjie | 男 | 1994-01-15 00:00:00 | 20110341 | 73 | 105 | peiyu | 男 | 1994-07-15 00:00:00 | 20110341 | 74 | 106 | wangnima | 男 | 1993-07-15 00:00:00 | 20110341 | 75 | 107 | zhaotiezhu | 男 | 1993-07-15 00:00:00 | 20110341 | 76 | 108 | zhaotiezhu | 女 | 1993-07-15 00:00:00 | 20110341 | 77 | 109 | xudijian | 男 | 1993-11-15 00:00:00 | 20110341 | 78 +-----+------------+------+---------------------+----------+ 79 10 rows in set (0.01 sec) 80 81 mysql> inset teacher values(101,"zhangsan","男",“1958-09-11”,"计算机系"); 82 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 83 mysql> insert teacher values(101,"zhangsan","男","1958-09-11","计算机系"); 84 ERROR 1136 (21S01): Column count doesn't match value count at row 1 85 mysql> insert teacher values(101,"zhangsan","男","1958-09-11","教授","计算机系"); 86 Query OK, 1 row affected (0.00 sec) 87 88 mysql> insert teacher values(102,"zhangsi","男","1658-09-11","副教授","计算机系"); 89 Query OK, 1 row affected (0.00 sec) 90 91 mysql> insert teacher values(103,"lisi","女","1678-09-10","讲师","计算机系"); 92 Query OK, 1 row affected (0.00 sec) 93 94 mysql> insert teacher values(104,"liwu","女","1988-09-10","助教","计算机系"); 95 Query OK, 1 row affected (0.00 sec) 96 97 mysql> update teacher set tbirthday="1958-09-11" where tbirthday="1658-09-11"; 98 Query OK, 1 row affected (0.00 sec) 99 Rows matched: 1 Changed: 1 Warnings: 0 100 101 mysql> update teacher set tbirthday="1978-09-10" where tbirthday="1678-09-10"; 102 Query OK, 1 row affected (0.01 sec) 103 Rows matched: 1 Changed: 1 Warnings: 0 104 105 mysql> select * from teacher; 106 +-----+----------+------+---------------------+-----------+--------------+ 107 | tno | tname | tsex | tbirthday | prof | depart | 108 +-----+----------+------+---------------------+-----------+--------------+ 109 | 101 | zhangsan | 男 | 1958-09-11 00:00:00 | 教授 | 计算机系 | 110 | 102 | zhangsi | 男 | 1958-09-11 00:00:00 | 副教授 | 计算机系 | 111 | 103 | lisi | 女 | 1978-09-10 00:00:00 | 讲师 | 计算机系 | 112 | 104 | liwu | 女 | 1988-09-10 00:00:00 | 助教 | 计算机系 | 113 +-----+----------+------+---------------------+-----------+--------------+ 114 4 rows in set (0.00 sec) 115 116 mysql> mysql 117 -> ; 118 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 119 mysql> insert into course values("3-105","计算机导论","104"); 120 Query OK, 1 row affected (0.01 sec) 121 122 mysql> insert into course values("3-106","高等数学","103"); 123 Query OK, 1 row affected (0.01 sec) 124 125 mysql> insert into course values("3-107","JAVA","102"); 126 Query OK, 1 row affected (0.00 sec) 127 128 mysql> insert into course values("3-108","python","101"); 129 Query OK, 1 row affected (0.00 sec) 130 131 mysql> select * from course; 132 +-------+-----------------+-----+ 133 | cno | cname | tno | 134 +-------+-----------------+-----+ 135 | 3-105 | 计算机导论 | 104 | 136 | 3-106 | 高等数学 | 103 | 137 | 3-107 | JAVA | 102 | 138 | 3-108 | python | 101 | 139 +-------+-----------------+-----+ 140 4 rows in set (0.00 sec) 141 142 mysql> mysql> insert into score(101,"3-105","99"); 143 Query OK, 1 row affected (0.00 sec) 144 145 mysql> insert into score values(101,"3-106","100"); 146 ERROR 1062 (23000): Duplicate entry '101' for key 'score.PRIMARY' 147 mysql> insert into score values(102,"3-106","100"); 148 Query OK, 1 row affected (0.00 sec) 149 150 mysql> alter table score drop primary key; 151 ERROR 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint 152 mysql> drop table score; 153 Query OK, 0 rows affected (0.01 sec) 154 155 mysql> create table score( 156 -> sno varchar(20) not null, 157 -> cno varchar(20) not null, 158 -> degree decimal, 159 -> foreign key(sno) references student(sno), 160 -> foreign key(cno) references course(cno), 161 -> primary key(sno,cno) 162 -> 163 -> ); 164 Query OK, 0 rows affected (0.02 sec) 165 166 mysql> select * from score; 167 Empty set (0.00 sec) 168 169 mysql> insert into score values(100,"3-105","99"); 170 Query OK, 1 row affected (0.01 sec) 171 172 mysql> insert into score values(100,"3-106","98"); 173 Query OK, 1 row affected (0.00 sec) 174 175 mysql> insert into score values(100,"3-107","98"); 176 Query OK, 1 row affected (0.00 sec) 177 178 mysql> insert into score values(100,"3-107","90"); 179 ERROR 1062 (23000): Duplicate entry '100-3-107' for key 'score.PRIMARY' 180 mysql> insert into score values(100,"3-108","90"); 181 Query OK, 1 row affected (0.00 sec) 182 183 mysql> insert into score values(101,"3-105","90"); 184 Query OK, 1 row affected (0.00 sec) 185 186 mysql> insert into score values(101,"3-106","90"); 187 Query OK, 1 row affected (0.00 sec) 188 189 mysql> insert into score values(101,"3-107","79"); 190 Query OK, 1 row affected (0.01 sec) 191 192 mysql> insert into score values(101,"3-108","80"); 193 Query OK, 1 row affected (0.00 sec) 194 195 mysql> insert into score values(102,"3-108","80"); 196 Query OK, 1 row affected (0.00 sec) 197 198 mysql> insert into score values(103,"3-108","80"); 199 Query OK, 1 row affected (0.00 sec) 200 201 mysql> insert into score values(104,"3-108","60"); 202 Query OK, 1 row affected (0.01 sec) 203 204 mysql> insert into score values(105,"3-108","66"); 205 Query OK, 1 row affected (0.00 sec) 206 207 mysql> select * from score; 208 +-----+-------+--------+ 209 | sno | cno | degree | 210 +-----+-------+--------+ 211 | 100 | 3-105 | 99 | 212 | 100 | 3-106 | 98 | 213 | 100 | 3-107 | 98 | 214 | 100 | 3-108 | 90 | 215 | 101 | 3-105 | 90 | 216 | 101 | 3-106 | 90 | 217 | 101 | 3-107 | 79 | 218 | 101 | 3-108 | 80 | 219 | 102 | 3-108 | 80 | 220 | 103 | 3-108 | 80 | 221 | 104 | 3-108 | 60 | 222 | 105 | 3-108 | 66 | 223 +-----+-------+--------+ 224 12 rows in set (0.00 sec) 225 226 mysql>