创建表时,最后一个字段不能添加逗号

1
2
3
4
5
6
create table class
(
	cid int primary key,
	caption varchar(15)
)
ENGINE=InnoDB default charset=utf8;

MySQL创建外键方法不同于其他数据库

1
2
3
4
5
6
7
8
9
10
# MySQL
create table student
(
	sid int primary key ,
	sname varchar(10),
	gender varchar(4) check(gender='男' or gender='女'),
	class_id int,
	foreign key (class_id) references class(cid) 
)
engine=InnoDB default charset=utf8;
1
2
3
4
5
6
7
8
9
/* SQL Server */
create table student
(
	sid int primary key ,
	sname varchar(10),
	gender varchar(4) check(gender='男' or gender='女'),
	class_id int foreign key  references class(cid) 
)
engine=InnoDB default charset=utf8;
  1. 不能使用class_id int foreign key references class(cid)
  2. foreign key (class_id) references class(cid) 中的class_id必须添加括号

MySQL使用check的时候不能添加Not null

1
2
3
4
5
# wrong
gender varchar(4) check(gender='男' or gender='女') not null,

# right
gender varchar(4) check(gender='男' or gender='女') ,

使用where sid in (select … )代替join操作

查询没学过“波多”老师课的同学的学号、姓名；

1
2
3
4
5
6
7
select sid,sname from student where sid not in (

	select DISTINCT student_id from score where corse_id in (

		select cid from course join teacher on teacher.tid = course.teach_id where tname = '波多'
		)
)

不要总是使用join,然后再来查询.

可以使用where sid in (select ... )