SQL——表的操作

--1.创建数据库
--create database studentDB 
--2.创建登陆名，并映射为数据库用户
/*
use studentDB 
go
create login zhou1 with password = 'mypassword123'
go
create user zhou1 for login zhou1
*/
--3.创建模式和删除模式
--create schema sch1 authorization zhou1
--drop schema sch1
--4.创建表
/*
--4.1创建用户表
create table uers
(
vusername varchar(18) not null,
vpasswrod varchar(20) not null
)
*/
/*
--4.2创建学生表
create table student
(
Sno char(10) NOT NULL Constraint PK_Stu_No Primary Key,
Sname char(20) NOT NULL,
Ssex char(2) NOT NULL Constraint CK_Stu_Sex check(Ssex in ('男','女')),
Smajor char(20) NOT NULL,
Sdept char(20) NOT NULL,
Sage tinyint  Constraint CK_Stu_Age Check(Sage between 1 and 80),
Tel char(15) NOT NULL,
EMAIL varchar(30) NOT NULL
)
*/
--5.表|列的删除，添加，修改
/*
--删除约束
alter table student drop constraint CK_Stu_Age
--删除列Sage
alter table student drop column Sage
--添加列dBirth为时间格式
alter table student add dBirth datetime
--修改列
alter table student alter column Smajor varchar(20)
--删除表
drop table student
*/
--6.检索表
/*
--显示所有信息
--select * from student
--显示部分信息
select Sno,Sname,Ssex,Smajor from student
--友好列标题
select Sno as 学号,Sname as 姓名,Ssex as 性别,Smajor as 专业,Sage as 年龄 from student
--检索前三行信息
select top 3 *from student
--条件检索学生
select *from student where sage=12
select *from student where Ssex='男' and Sage<21 
select *from student where Ssex='男' or Sage>60
select Sno,Sname,Ssex,Sage from student where Sage between 45 and 65
select Sno,Sname,Ssex,Sage from student where Sage not between 45 and 65
select * from student where Sage in(44,65,12)
--distintc 不重复信息
select distinct Sage from student
--like模糊查询，前缀为1
select * from student where Sage like '1%'
--like模糊查询，只要含有2
select * from student where Sname like '%2%'
--like模糊查询，_表示任意字符，前面必须为两个任意字符后面跟着2
select * from student where Sname like '__2%'
select *from student where Sage is NULL
select *from student where sage is not null
--升序排列
select *from student order by Sage asc
--降序排列
select *from student order by Sage desc
--按Sage升，Tel降
select *from student order by Sage asc,Tel desc
select 3*5,sqrt(2)
select *,Tel*10 as Tel2 from student
--计算Sage=12的数目
select count(*) from student where Sage=12
--计算Tel的列数
select count(Tel) from student
select max(Sage),min(Sage),avg(Sage) from student
select sum(Sage) from student
--group分组
select Sage,count(Sage) as 个数 from student group by Sage
select Sage,Ssex,count(Ssex) from student group by Sage,Ssex order by count(Ssex) asc
--大于三才显示
select Ssex,count(Ssex) from student group by Ssex having count(Ssex)>3

--多表查询，（接上单表查询的表）
drop table sc
create table sc
(
Sno char(10) primary key,
Sname char(30),
Ssex char(2)
)
--内连接,显示两个表的数据，当Sno相等时
--写法一
select student.Sno,student.Sname,sc.Sno,sc.Sname from sc,student
where sc.Sno=student.Sno
--写法二
on sc.Sno=student.Sno
select student.Sno,student.Sname,sc.Sno,sc.Sname from sc join student
--左外连接，左边的表的集合与右边的表的交集
select student.Sno,student.Sname,sc.Sno,sc.Sname from student left outer join sc
on student.Sno=sc.Sno
--右外连接，同上
select student.Sno,student.Sname,sc.Sno,sc.Sname from sc right outer join student
on student.Sno=sc.Sno
--全外连接,两个之和
select student.Sno,student.Sage,sc.Sno,sc.Sname from sc full outer join student
on student.Sno=sc.Sno
--交叉连接，两个表的排列组合
select *from student cross join sc

--集合查询
--union 去重，union all 不去重
use studentDB
select Sno,Sname,Ssex from sc
union
select Sno,Sname,Ssex from student
--intersect 交集
select Sno,Sname,Ssex from student where Ssex='男'
intersect
select Sno,Sname,Ssex from student where Sno=1
--except 差集
select Sno,Sname,Ssex from student where Ssex='男'
except
select Sno,Sname,Ssex from student where Sno=1
--数据更新
insert into sc (Sno,Ssex,Sname) values ('21','21','43')
--创建新表
create table fsc
(
Sno char(12) not null primary key,
Sname char(20) not null,
Ssex char(2),
new char(10)
)
--把子查询结果插入到新表
insert into fsc(Sno,Sname,Ssex,new) select Sno,Sname,Ssex,'new' from sc
--选择数据，插入到一个不存在的新表
select * into wtf from student where Ssex='男'
数据更新
update wtf set Ssex='女' where Tel=12
update sc set Sname=(Select Sname from student where student.Sno=sc.Sno)
--删除数据
delete from wtf where Sno='12'
delete from wtf where Sno in (select Sno from sc)
--建立视图
create view see
as
select * from sc
--通过视图修改数据
update see set Sname='22'
--视图的as前加with encryption 可以给视图加密
create view see
with encryption as
select * from sc
--创建唯一索引
create unique index only on wtf(Sname)
--删除索引
drop index wtf.only
drop index only on wtf