sql常用crud命令

创建表

CREATE TABLE student
( 
	sno VARCHAR(8), 
	sname VARCHAR(14), 
	sex char(4) check(sex='男' or sex='女'),   --性别要不为男或者为女
	age VARCHAR(4),
	college VARCHAR(30),
	partnum INT,
	PRIMARY KEY (sno)
);

修改表名：

alter table student rename to student2

添加列

alter table club add vipnum varchar(5)

修改列

alter table student change sno sno2 varchar(8)

修改列属性

alter table student modify sno varchar(20);

删除列：

alter table stuent drop column  sno

修改字段默认值

alter table student alter ssex set default '女'

删除字段默认值

alter table student alter ssex drop default

查看表中字段的默认值

show ssex from student

查询

SELECT party.sno
FROM party
WHERE  party.club_name='舞蹈协会' and party.club_name='网球协会'

插入

INSERT INTO party(sno,club_name,work)  VALUES ('20180101','围棋协会','副会长')

建立索引

• student表按学号升序建唯一索引
• course表按课程号升序建唯一索引
• sc表按学号升序和课程号降序建唯一索引

create unique index ind1 on student(sno)
create unique index ind1 on course(cno)
create unique index ind1 on sc(sno asc, cno desc)

降序

SELECT sno,sname,sex,age,college,partnum
FROM student 
ORDER BY partnum

所有男生年龄加1

UPDATE student SET age=age+1
WHERE sex='男'

创建视图

CREATE VIEW TYVIEW 
AS
SELECT student.sno,party.club_name
FROM student,party 
WHERE student.college='体育学院';

授权 grant

sp_addlogin '张三'
-------------------分开执行
sp_adduser '张三'

--T12
CREATE TRIGGER Tri1 
ON party FOR DELETE 
AS
UPDATE student 
SET student.partnum = student.partnum-1

--T13
BEGIN TRAN t13 WITH mark 'TJ'
DECLARE @num INT
SET @num=0   
INSERT INTO student(partnum)
 WHERE partnum <'5' 
IF @num>0
	BEGIN 
		ROLLBACK TRANSACTION t13
	END
ELSE
	BEGIN
		COMMIT TRANSACTION t13
	END

触发器
存储过程-课件