数据库-表结构的创建 修改 插入 删除

表的创建

CREATE TABLE Student
(
--设置主键
ID int PRIMARY KEY NOT NULL,

NAME VARCHAR(10) NOT NULL,
Age int NOT NULL,
Master VARCHAR(8)  NOT NULL,
Class VARCHAR(10) NOT NULL
)

表结构的修改

--修改大小
ALTER TABLE Student
alter COLUMN Master VARCHAR(10);
--修改表名
exec sp_rename 'Student', 'S_tudent';

--重点 增加主键约束,这里有了主键，所以不能增加了
ALTER TABLE Student
ADD CONSTRAINT KID PRIMARY KEY (Master);
--KID是我们为主键起的名字 ，每一个主键都有一个引用名称，注意这个名字不是他约束键的名称

--修改属性名称
exec sp_rename 'Student.Master','MASTER','COLUMN';

--增加一个属性
ALTER TABLE Student
ADD Grade char(10);

--修改类型
ALTER TABLE Student
ALTER COLUMN  Grade int;

--增加非空约束
ALTER TABLE Student
ALTER COLUMN Grade INT NOT NULL;
--记得带上这个属性的类型
SELECT*FROM Student

--删除表属性列（全删了）
ALTER TABLE Student
DROP COLUMN Grade
--删除写法二
delete from 表名
where 字段 = 值
--修改表记录，一定要用主键指定某一行，不然全改动了
UPDATE Student
set NAME ='AA'
where ID =0

新增表记录

SELECT
	* 
FROM
	Student;
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 3, 'A1', 17, '英语', '软工二' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 2, 'A2', 18, '德语', '软工三' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 4, 'A3', 19, '法语', '软工四' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 5, 'A4', 14, '西班牙语', '软工五' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 6, 'A5', 15, '汉语', '软工六' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 7, 'A6', 18, '俄语', '软工二' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 8, 'A7', 10, '日语', '软工三' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 9, 'A8', 20, '英语', '软工四' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 0, 'A9', 24, '汉语', '软工二' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 11, 'C0', 33, '汉语', '软工三' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 12, 'C1', 22, '英语', '软工四' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 13, 'C2', 11, '法语', '软工三' );
INSERT INTO Student ( ID, NAME, Age, MASTER, Class )
VALUES
	( 1, 'C3', 99, '英语', '软工五' );