--修改表结构------------------------
--(1)添加列
--alter table 表名 add 新的列名 数据类型
--给员工表添加一列邮箱
ALTER TABLE PEOPLE ADD PEOPKEMAIL VARCHAR(200)
--(2)删除列
--alter table 表名 DROP COLUMN 新的列名
--删除员工表邮箱列
ALTER TABLE PEOPLE DROP COLUMN PEOPKEMAIL
--(3)修改列
----alter table 表名 ALTER COLUMN 列名 数据类型
--修改地址varchar(300)为varchar(200)
alter table PEOPLE ALTER COLUMN PEOPLEADRESS VARCHAR(200)
4、插入数据
--向部门拆入数据
INSERT INTO DEPARTMENT(DEPARTMENTIDNAME,DEPARTMENTREMARK)
VALUES('市场部','.....')
INSERT INTO DEPARTMENT(DEPARTMENTIDNAME,DEPARTMENTREMARK)
VALUES('软件部','.....')
INSERT INTO DEPARTMENT(DEPARTMENTIDNAME,DEPARTMENTREMARK)
VALUES('企划部','.....')
INSERT INTO DEPARTMENT(DEPARTMENTIDNAME,DEPARTMENTREMARK)
VALUES('硬件部','.....')
--简写
INSERT INTO DEPARTMENT VALUES('开发部','.....')
--一次性插入多行数据
INSERT INTO DEPARTMENT(DEPARTMENTIDNAME,DEPARTMENTREMARK)
SELECT '测试部','.....'UNION
SELECT '总经办','.....'UNION
SELECT '产品部','.....'
--向职级表插入数据
INSERT INTO [RANK](RANKNAME,RANKREMARK)
VALUES('初级','.....')
INSERT INTO [RANK](RANKNAME,RANKREMARK)
VALUES('中级','.....')
INSERT INTO [RANK](RANKNAME,RANKREMARK)
VALUES('高级','.....')
--向员工表插入数据
INSERT INTO PEOPLE(DepartmentID,RANKID,PEOPLENAME,PEOPLESEX,PEOPLEBIRTH,PEOPLESALARY,PEOPLEPHONE,PEOPLEADRESS,PEOPLEADDTIME)
VALUES(8,1,'刘备','男','1998-8-8',5000,'13888888888','中国',GETDATE())
5、修改和删除数据
--修改
--语法
--UPDATE 表名 SET 字段1=值1,字段2=值2,字段3=值3
--工资调整,每人加薪1000元
UPDATE PEOPLE SET PEOPLESALARY = PEOPLESALARY + 1000
--将员工编号为7的人加薪500
UPDATE PEOPLE SET PEOPLESALARY = PEOPLESALARY + 500
where PEOPLEID = 7
--将软件部(部门编号为2)的人员工资低于15000的调整为15000
UPDATE PEOPLE SET PEOPLESALARY = 15000
WHERE DepartmentID = 2 AND PEOPLESALARY < 15000
--修改刘备的工资是以前的2倍,并且吧刘备的地址修改成北京(修改多条语句的中间用逗号隔开)
UPDATE PEOPLE SET PEOPLESALARY = PEOPLESALARY * 2,PEOPLEADRESS = '北京'
WHERE PEOPLENAME = '刘备'
SELECT * FROM DEPARTMENT
SELECT * FROM [RANK]
SELECT * FROM PEOPLE
--删除
--语法
--DELETE FROM 表名 WHERE 条件
--删除员工表所有记录
--DELETE FROM PEOPLE
--删除市场部(部门编号为3)中工资大于20000的人
DELETE FROM PEOPLE
WHERE DepartmentID = 3 AND PEOPLESALARY > 20000
--关于删除(DROP,TRUNCATE,DELETE)
--DROP TABLE PEOPLE --删除表对象,直接删除表
--TRUNCATE TABLE PEOPLE --删除数据(清空数据)只能清空数据
--DELETE FROM PEOPLE --删除所有数据,表仍然存在,他可以删除所有,也可以删除部分数据