创建数据库学校-使用表学生stno int not详解.pdfVIP

--创建数据库

createdatabaseschool;

--创建表

useschool;

createtablestudent

(

stnointnotnull,

stnamechar(10)notnull,

stchar(2),

stageint

);

--数据

insertintostudentvalues(1,,M,35);

INSERTINTOstudent

values(2,,M,45),

(3,梅超风,F,35),

(4,欧阳锋,M,56),

(5,,M,25),

(6,小,F,18),

select*fromstudent;

--各种查询操作

--）查询所有的记录

select*fromstudent

--)查询满足条件的所有记录

select*fromstudent

wherestage40;

--3)查询所有记录的某些列

selectstname,stagefromstudent;

--4)查询满足条件的所有记录的某些列

selectstname,stagefromstudent

wherest=F;

--5)更改输出列的列名

selectstnameas,stageas,

fromstudent;

--6)对结果进行去重处理

selectdistinctstfromstudent;

--7)对结果按某一条件排序

select*fromstudentordwebystagedesc;

--where子句进阶

--1)比较表达式，=!===等等

select*fromstudentwherest=Forstage=40

--2)范围表达式，IN,notIN,betweenand,notbeweenand

select*fromstudentwherestage=35orstage=55orstage=60;

fromstudentwherestagenotin(35,45,55,65);

select*fromstudentwherestagenotbetween20and30;

--3)模式匹配like%_

select*fromstudentwherestnamelike张%;

select*fromstudentwherestnamelike张_;

--4)空值的使用

insertintostudentvalues(7,东方不败,null,180),

select*fromstudentwherenotstisnull;

--统计函数，聚合函数，max,min,avg,count,sum,var,stdev

selectAVG(stage)as平均fromstudent;

selectMAX(stage)as最大fromstudent;

selectMIN(stage)as最小fromstudent;

selectCOUNT(*)as总学生数fromstudent;

selectSUM(stage)as总和fromstudent;

selectVAR(stage)as方差fromstudent;

selectAVG(stage)as平均,

STDEV(stage)as标准差,

COUNT(*)as录数fromstudent;

--聚合函数的标配子句,where,groupby,having

--1)聚合函数+where子句

selectCOUNT(*)as