sql数据库第二课

use student

select * from students

--连接值的方式插入多行数据
insert into students (ID,SName,Age,Sex)
select 18,'王1',19,0 union
select 19,'王2',22,1 union
select 20,'王3',24,0 union
select 21,'王4',25,1

--更新语句
update students set Sex=0
update students set Address='浙江金华'

--更新语句（限定条件）
update students set Address='浙江温州'
where SName='肖云斌'

--更新语句（多列数据更新）
update students set SEmail='33@232.com',IdentityCard='737373737737373723',
Address='浙江义乌'
where ID=5

--更新语句（数值运算后更新）
update students set Age=Age+3
where ID=6

update students set Sex=1
where ID<=10

update students set Address='北京奥运村'
where Sex=0

update students set Sex=1
where ID%2=0

select * from students

--删除语句（整个表）
delete from students

--删除语句（限定条件）
delete from students where ID%2=0

select * from students
select * from studentMarks
--向子表中插入外键列数据，在主表中必须存在
--否则将违反外键约束
insert into StudentMarks
values(4,1,2,1004,97)

--如果在子表中存放在关联主表中的数据，那么删除主表
--会违反外键约束，无法删除，如果一定要删除主表中的
--数据，应先删除子表中的数据后再删除主表中的数据
delete from students

delete from studentMarks
delete from students

--删除行
select * from sbk
insert into sbk values ('aa','aa')
--delete 语句删除表标识列不会重新编号
delete from sbk
--truncate语句删除表会重新表好似列
--注：不能用于有外键约束的表
truncate table sbk

 

select * from students

--指定列名查询表的信息
select SName,Age,Address from students

-- 取别名查询标的信息
select SName as 姓名,Age as 年龄,Address as 地址 from students
where ID%2=0

select 姓名=SName,年龄=Age,地址=Address from students

select '姓名'=SName,'年龄'=Age,'地址'=Address from students

select * from students

--排序数据（倒序）
select * from students order by Age desc
--正序
select * from students order by Age asc
--如果是正序asc关键字可以省略
select * from students order by Sex

insert into Employees
select '张','三'union
select '李','四'union
select '王','五'

--查询：将多列合并成一列
select ID,LastName+''+FirstName as EmpName
from Employees

update students set Address=null
where ID%2=0

select * from students
--查询：指定列为空的数据
select * from students where Address is null
--查询：指定列不为空的数据
select * from students where Address is not null
--查询：指定列为空字符的数据
select * from students where Address=''

--查询：在查询中加入常量列
select *,'金华NIIT' as SchoolName
from students

--查询：top关键字的使用
select top 5 * from students order by Age desc
--以百分比查询表中的数据
select top 20 percent * from students

--查询：排序
select stuID as 学员编号,(chengji*0.9+5)as 综合成绩
from studentmarks
where (chengji*0.9+5)>60
order by chengji

select * from employees
select * from authors

insert into authors
select '刘','1'union
select '刘','2'

--使用union 来合并多表中的行为一个结果集
select LastName+''+FirstName as Emp
from employees union
select A_LastName+''+A_FirstName as Emp
from authors