SQL--基本语句

1 建表，主键约束，外键约束。

CREATE TABLE course ( cno int(11) NOT NULL AUTO_INCREMENT, cname char(20) DEFAULT NULL, cteachername char(20) DEFAULT NULL, PRIMARY KEY (cno) ) CREATE TABLE student ( sno int(11) NOT NULL AUTO_INCREMENT, sname char(20) DEFAULT NULL, sxe char(2) DEFAULT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (sno) ) CREATE TABLE sc ( sno int(20) NOT NULL, cno int(20) NOT NULL, PRIMARY KEY (sno,cno), CONSTRAINT sc_ibfk_1 FOREIGN KEY (sno) REFERENCES student (sno), CONSTRAINT sc_ibfk_2 FOREIGN KEY (cno) REFERENCES course(cno) )

说明：进行外键约束时，当主表中记录删除时，从表中的记录将级联删除，在外键约束的最后添加on delete cascade，例子如下:

FOREIGN KEY (sno) REFERENCES s (sno) on delete cascade

另外一种当主表中的记录删除时，从表中记录的外键将会设置为null，在外键约束后添加on delete set null，例子如下：

FOREIGN KEY (sno) REFERENCES s (sno) on delete set null

2 批量删除与插入： 批量删除方法一：

DELETE FROM MyTable WHERE ID IN (1,2);

批量插入方法一：

INSERT INTO MyTable(ID,NAME) VALUES(1,'123'); INSERT INTO MyTable(ID,NAME) VALUES(2,'456'); INSERT INTO MyTable(ID,NAME) VALUES(3,'789');

批量插入方法二：使用UNION ALL来进行插入操作：

INSERT INTO MyTable(ID,NAME) SELECT 4,'000' UNION ALL SELECT 5,'001' UNION ALL SELECT 6,'002' ;

据说要比第一种要快！ 批量删除方法三：

INSERT INTO MyTable(ID,NAME) VALUES(7,'003'),(8,'004'),(9,'005');

3 增删改查

INSERT INTO user(name,password,count) VALUE('123','123',1) UPDATE user SET count=count+1 WHERE id=1 DELETE FROM user WHERE id=1 SELECT name,password, CASE WHEN flag=0 THEN '已删除' WHEN flag=1 THEN '存在' ELSE '未知状态' END AS flag FROM user ORDER By createtime DESC LIMIT 1,1 第二条记录