1.创建表
1).创建表
use xs
create table xsb
( 学号 char(6) not null primary key,
姓名 nchar(8) not null,
性别 nchar not null,
出生时间 datetime not null,
专业 nchar(20) not null,
总学分 tinyint not null,
备注 nvarchar(1000) not null
)
2)在表中插入记录的的语法
insert into 表名 (属性1 属性2 ..属性n)
values(属性1值,属性2值,…属性n值)
// 指定插入数据的表
insert into xsb (学号, 姓名,专业,总学分,备注)
values(‘081101’,‘王林’,‘计算机’,‘50’,‘null’)
insert into 学生 (学号, 姓名, 性别, 出生时间,专业,总学分,备注)
values (‘081101’,‘王林’,’男’,’20161112082312’,‘计算机’,‘50’,‘null’)
insert into 学生
values (‘081101’,‘王林’,’男’,’20161112082312’,‘计算机’,‘50’,‘null’)
//当给出全部属性的属性值时,两者等价。
2.1)在表中插入记录
insert [top<数值> 或 percent ]
into[表名\视图名]
有效的select 查询结果
//Top/percent:查询结果中前N条或前百分之N条记录
插入记录的另外一种情况:从已经存在的表中到处数据到另一个表中。
示例:
设已存在某表(学号、姓名、专业),将“学生:表中计算机专业的学生记录的前5行导入到新表“计算机”(学号 姓名 专业)中
create table 计算机
(
学号 char(10) not null primary key,
姓名 nchar(10) not null,
专业 nvarchar(20) not null
)
Insert top(5) into 计算机
select 学号,姓名,专业
from 学生
where 专业=‘计算机’
3)在表中删除记录的的语法
/* 使用delete语句删除*/
delete [top<数值>或percent]
from 表名/视图名 //从指定的表或视图中删除
where / /指定删除的条件
/* 若省略where,则表示删除指定表中所有的记录*/
/将XS数据库学生表中(学号 姓名 专业 总学分 备注),总学分大于52的记录删除/
ues XS
delete
from 学生
where 总学分>52
4)更新表中数据记录的语法
/修改记录/
update [top<数值>或percent]
表名/视图名 //对指定的表或视图进行修改
set //进行新的赋值
列名1=新值,(表达式/null/default)
列名2=新值 /一次可修改同一条记录的多个属性值
[from] //指定用表为更新操作提供数据
where //指定修改条件
/将XS数据库“08101中学生表中学号为”0801“的学生的备注值改为”三好学生“。3.18/
update 学生
set 备注=‘三好学生’
where 学号=‘0801’
2.查询表
1).查询表语法
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
SELECT子句的<目标列表达式>可以为:
字符串常量
算术表达式
列别名
函数
1.1)字符串常量
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
1.2)算术表达式
SELECT Sname,2004-Sage /假定当年的年份为2004年/
FROM Student
输出结果:
Sname 2004-Sage
李勇 1984
刘晨 1985
王敏 1986
张立 1985
1.3)使用列别名改变查询结果的列标题
SELECT Sname NAME,’Year of Birth: ’ BIRTH,
2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
输出结果:
NAME BIRTH BIRTHDAY DEPARTMENT
——- —————- ————- ——————
李勇 Year of Birth: 1984 cs
刘晨 Year of Birth: 1985 is
王敏 Year of Birth: 1986 ma
张立 Year of Birth: 1985 is
1.3.1)消除取值重复的行 如果没有指定DISTINCT关键词,则缺省为ALL
SELECT Sno FROM SC;
等价于:
SELECT ALL Sno FROM SC;
执行上面的SELECT语句后,结果为:
Sno
200215121
200215121
200215121
200215122
200215122
指定DISTINCT关键词,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
执行结果:
Sno
200215121
200215122
1.4函数
聚集函数:
计数
COUNT([DISTINCT|ALL] *)
COUNT([DISTINCT|ALL] <列名>)
计算总和
SUM([DISTINCT|ALL] <列名>)
计算平均值
AVG([DISTINCT|ALL] <列名>)
最大最小值
MAX([DISTINCT|ALL] <列名>)
MIN([DISTINCT|ALL] <列名>
1.4.1 count
查询学生总人数。
SELECT COUNT(*) //所有元祖的个数
FROM Student;
查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;
1.4.2SUM
查询学生200215012选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC, Course
WHER Sno='200215012' AND SC.Cno=Course.Cno;
1.4.3AVG
计算1号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 1 ';
1.4.4Max和MIN
查询选修1号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHER Cno= ‘ 1 ’;
2.where语句
2.1比较:
查询计算机科学系全体学生的名单。
SELECT Sname
FROM Student
WHERE Sdept=‘CS’;
查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;
查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
2.2确定范围:
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23
2.3确定集合:
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( ‘IS’,’MA’,’CS’ );
查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( ‘IS’,’MA’,’CS’ );
2.4字符匹配:[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
匹配串为固定字符串
查询学号为200215121的学生的详细情况。
SELECT *
FROM Student
WHERE Sno LIKE ‘200215121’;
等价于:
SELECT *
FROM Student
WHERE Sno = ’ 200215121 ‘;
匹配串为含通配符的字符串
查询所有姓刘学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE ‘刘%’;
查询姓”欧阳”且全名为三个汉字的学生的姓名。
SELECT Sname
FROM Student
WHERE Sname LIKE ‘欧阳__’;
查询名字中第2个字为”阳”字的学生的姓名和学号。
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE ‘__阳%’;
查询所有不姓刘的学生姓名。
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE ‘刘%’;
使用换码字符将通配符转义为普通字符
查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE ‘DB_Design’ ESCAPE ‘\‘;
查询以”DB_”开头,且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM Course
WHERE Cname LIKE ‘DB_%i_ _’ ESCAPE ’ \ ‘;
ESCAPE ‘\’ 表示“ \” 为换码字符
2.4空值:IS NULL 或 IS NOT NULL
某些学生选修课程后没有参加考试,所以有选课记录,但没
有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL
查所有有成绩的学生学号和课程号。
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL
2.5多重条件查询
查询计算机系年龄在20岁以下的学生姓名。
SELECT Sname
FROM Student
WHERE Sdept= ‘CS’ AND Sage<20;
3.Group By 子句
GROUP BY子句分组:
1 细化聚集函数的作用对象
2 未对查询结果分组,聚集函数将作用于整个查询结果
3 对查询结果分组后,聚集函数将分别作用于每个组
4 作用对象是查询的中间结果表
按指定的一列或多列值分组,值相等的为一组
求各个课程号及相应的选课人数。
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno
HAVING count(*) >3 ;
查询结果:
Cno COUNT(Sno)
1 22
2 34
3 44
4 33
5 48
/*
系统执行顺序:
1)按SNO分组
2)执行聚函数COUNT
3)having筛选出满足条件的组
*/
HAVING短语与WHERE子句的区别:
作用对象不同
WHERE子句作用于基表或视图,从中选择满足条件的元组
HAVING短语作用于组,从中选择满足条件的组。
4.Order By 子句
ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
当排序列含空值时
ASC:排序列为空值的元组最后显示
DESC:排序列为空值的元组最先显示
查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECT Sno,Grade
FROM SC
WHERE Cno= ’ 3 ’
ORDER BY Grade DESC;