--創(chuàng)建數(shù)據(jù)庫
create database studentsDB

--創(chuàng)建表
create table student
(
?id int primary key,
?stuid char(10),
?stuname char(20),
?classname char(20),
?addresss char(40),
?cols int,
?sex char(10)
)

--更改數(shù)據(jù)項的數(shù)據(jù)類型
alter table student
alter column cols float

--插入數(shù)據(jù)
insert into student(id,stuid,stuname,classname,addresss,cols,sex)
values(1,'001','張三','1','重慶',100,'男')

insert into student(id,stuid,stuname,classname,addresss,cols,sex)
values(2,'002','李四','2','四川',70,'女')

insert into student(id,stuid,stuname,classname,addresss,cols,sex)
values(3,'003','李麗','1','貴州',99,'男')

insert into student(id,stuid,stuname,classname,addresss,cols,sex)
values(4,'004','王五','3','武漢',55,'女')

insert into student(id,stuid,stuname,classname,addresss,cols,sex)
values(5,'005','陳好','1','香港',67,'男')

--查詢分數(shù)在60-100的信息
select id,stuid,stuname,classname,addresss,cols,sex from student where cols between 60 and 100

--查詢班級里姓李的同學(xué)的信息
select id,stuid,stuname,classname,addresss,cols,sex from student where stuname like '李%'

--查詢分數(shù)等于99和100的同學(xué)的信息
select id,stuid,stuname,classname,addresss,cols,sex from student where cols = 99 or cols =100

--根據(jù)分數(shù)降序排列
select id,stuid,stuname,classname,addresss,cols,sex from student order by cols desc

--根據(jù)性別求出男生和女生分數(shù)的平均值
select avg(cols) as 平均成績,sex from student group by sex

--求出各班的平均成績
select avg(cols) as 平均成績,classname from student group by classname

SQL練習(xí)1關(guān)于插入刪除，修改，單表查詢