一 .插入数据
带主键的自增长列不用赋值
语法:insert into 表名(字段名1,字段名2,...) values(数据1,数据2,...)
insert into [Rank]([Name],Info) values('助理工程师','辅助工程师的工作')
insert into [Rank]([Name],Info) values('工程师','设备以及治夹具的设计')
insert into [Rank]([Name],Info) values('主任工程师','负责日常管理工作')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(1,3,'刘备','男','1978-9-10',20000,'13109876543','武汉','2024-1-31','硕士')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(2,1,'张飞','男','1983-9-13',8000,'13109876544','长沙','2024-1-31','小学')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(2,1,'关羽','男','1980-5-16',12000,'13109875543','广东','2024-1-31','本科')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(1,3,'曹操','男','1972-6-10',30000,'13106776543','天津','2024-1-31','博士')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(1,2,'袁绍','男','1968-10-12',15000,'13109786543','上海','2024-1-31','高中')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(2,1,'吕布','男','1979-5-6',18000,'13105876543','北京','2024-1-31','初中')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(3,1,'孙尚香','女','1998-5-18',6000,'13139876543','西藏','2024-1-31','本科')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(3,1,'貂蝉','女','2002-4-10',7000,'13109870543','新疆','2024-1-31','高中')
insert into People(DepartID,RankID,[Name],Sex,Birth,Salary,Phone,[Address],[Time],Degree) values(2,1,'马超','男','1991-8-15',10000,'13102976543','河南','2024-1-31','初中')
insert into Department([Name],Info) values('稽核部','负责工厂的纪律稽查审核')
insert into Department([Name],Info) values('工程部','生产线上治夹具设计,设备的维护及保养')
(简写)语法:insert into 表名 values(数据1,数据2,…) --不推荐
insert into Department values('总经办','总经理办公室日常事务')
二.插入多行数据
语法:insert into 表名(字段名1,字段名2...) select 数据1,数据2,... union select 数据1,数据2...
insert into Department([Name],Info) select '总经办' ,'总经理办公室日常事务' union select '工程部','生产线工艺的制定及设备的维护保养' union select '研发部','产品的设计及研发'
三. 修改数据(指定条件)
语法:update 表名 set 字段名1 = 数据1,字段2 = 数据2,...字段n = 数据n where 条件语句(and ,or)
举例 : 所有人加1000块钱工资
update People set Salary = Salary + 1000
将貂蝉的工资翻倍,并调动至北京吕布住处,异动至工程部
update People set Salary = Salary * 2 , [Address] = '北京',DepartID = 2
where [Name] = '貂蝉'
四. 删除数据
语法: delete from 表名 where 条件语句(and ,or)
删除所有数据 delete from 表名
举例:删除学历为小学的人
delete from People where Degree = '小学'
笔记:
drop truncate delete 比较
drop table People ----删除表对象
truncate table People ----清空数据表
delete from People ----删除表数据
truncate 与 delete 区别
1.truncate : 无条件清空所有数据 delete : 可以无条件删除所有数据 也可以删除指定条件的数据
2.truncate : 删除数据后自动重新编号(删除1,2,3,4,5后 后续添加数据仍是按1,2,3,4,5…顺序) delete : 删除数据后自动编号的ID也随之删除(删除1,2,3,4,5后 后续添加数据按6,7,8,9,10…顺序)
五. 查询数据
1.查询所有列所有行
语法:select * from 表名
select * from Department
select * from [Rank]
select * from People
- 查询指定列
语法:select 字段名1,字段名2,... from 表名
select [Name],Salary,[Address] from People
- 查询指定列并给该列设置中文别名
语法 :select 字段名1 别名1,字段名2 别名2,... from 表名
select [Name] 姓名, Salary 工资, [Address] 地址 from People
- 查询指定列的数据(去除重复显示)
语法 :select distinct 字段名1 from 表名
select distinct [Address] from People ----去除相同的城市
select [Address] from People ----显示所有员工所在城市
select distinct Salary from People
select Salary from People
- 查询某列变动后的信息(调薪20%后的工资)
select [Name] 姓名,Salary 目前工资,Salary * 1.2 加薪后工资 from People