数据库的创建与管理

create database Z1p10320
go
use Z1p10320
go
create table student
(
    code int,
    name varchar(20),
    sex char(10),
    age int,
    height decimal(18,2)
)
go
insert into student values(1,'于洋','男',23,180)
insert into student values(2,'徐桐','男',23,175)
insert into student values(3,'丁川','男',28,175)
insert into student values(4,'翟黎明','男',22,175)
insert into student values(5,'荆树坤','男',23,175)
insert into student values(6,'李收','男',25,180)
insert into student values(7,'岳新伟','男',26,170)
insert into student values(8,'周帝','男',23,180)
insert into student values(9,'薛岩','男',21,179.9)
insert into student values(10,'孙鹏','男',27,179)
insert into student values(11,'王学科','男',21,180)
insert into student values(12,'李红艳','女',23,165)
insert into student values(13,'任文财','男',27,160)
insert into student values(14,'魏文鑫','男',21,165)
go
--查询所有信息
select * from student
--查询学号为6的人的所有信息
select * from student where code=6
--查询学号为8的人的姓名
select name from student where code=8
--查询所有人的姓名
select name from student
--查询24岁以上的人的所有信息
select * from student where age>24
--身高超过175的所有人的姓名
select name as 姓名 from student where height>175
--查询总数
select COUNT(name) from student
--查询年龄超过24的人数
select COUNT(*) from student where age>24

--order by  排序   默认从小到大排列
select *from student order by age
--从大到小排列    desc
select *from student order by age desc

--聚合函数
--AVG   平均数
select AVG(age) as 平均年龄 from student
select AVG(height) as 平均身高 from student

--SUM    求和
select SUM(age) from student
select SUM(height) from student

--MAX   最大值
select MAX(age) from student
select MAX(height) from student

--MIN   最小值
select MIN(age) from student
select MIN(height) from student

--COUNT  个数
--查询总数
select COUNT(name) from student
--查询年龄超过24的人数
select COUNT(*) from student where age>24

--分组  group by
select sex from student group by sex
--gre=oup by     having 聚合函数
select sex from student group by sex having COUNT(*)>5

--修改表中的数据
update student set age=18 where name='薛岩'


--删除整个表
drop table student

--删除表中的数据
use Z1p10320
go
delete from student  where code=14

--增加一列
use Z1p10320
go
Alter table student add tizhong decimal(18,1)

--删除一列
alter table student Drop column tizhong

 整理后：

create database z1p10320
go
use z1p10320
go
create table student
(
code int,
name varchar(20),
sex char(2),
age int,
height decimal(10,2),
)
go
insert into student values(1,'于洋','男',23,180)
insert into student values(2,'徐桐','男',23,175)
insert into student values(3,'丁川','男',28,175)
insert into student values(4,'荆树坤','男',23,175)
insert into student values(5,'翟黎明','男',22,175)
insert into student values(6,'李收','男',25,180)
insert into student values(7,'岳新伟','男',26,170)
insert into student values(8,'周帝','男',23,175)
insert into student values(9,'薛岩','男',21,179.9)
insert into student values(10,'孙鹏','男',27,175)
insert into student values(11,'王学科','男',21,175)
insert into student values(12,'李红艳','男',23,165)
insert into student values(13,'任文财','男',27,160)
insert into student values(14,'魏文鑫','男',21,165)
go
--查询所有人的信息
select * from student
--查询学号是六的人的所有信息
select * from student where code=6
--查询学号为8的人的姓名
select name from student where code=8
--查询所有人的姓名
select name from student 
--查询24岁以上的人的信息
select * from student where age>24
--查询身高低于180人的所有信息
select * from student where height<180
--order by（排序）：默认从小到大,针对于所有人、或要排序的这一列的所有信息、进行排序
select * from student order by height 
--在最后 + desc ：倒叙（从大到小）
select * from student order by height desc
--group up  （分组）：针对于具体的一组或一列数据进行分组
select age from student group by age 
--group up ：having函数（针对于要分出的符合条件的组内的所有信息）
select age from student group by age having COUNT(*)>3
--聚合函数
--COUNT    计数
--查询名字的总数
select COUNT(name)from student 
--查询年龄超过24岁的人
select COUNT(*)from student where age>24
-- SUM     求和
--求所有学号的和
select SUM(code)from student 
--求所有人年龄之和
select SUM(age) from student 
--AVG       平均值(为查询的信息命名  as+名字)
--求所有人的平均年龄并未命名
select AVG(age) as 平均年龄 from student 
--求所有人的平均身高
select AVG(height) as 平均身高 from student
--MAX        最大值
select MAX(age) as 最大年龄 from student
select MAX(code) from student 
--MIN         最小值
select MIN(height) as 最低身高 from student
--修改表中数据
update student set height=180.1 where name='薛岩'
select * from student 
--删除表中数据
use z1p10320 
go
delete from student where code=14
--删除整个表
delect table student
--增加一列
use z1p10320 
go
Alter table student add tizhong decimal(18,1)
select * from student  
--删除一列
alter table student drop column tizhong
select * from