SQL的基于Qt的编程实践---3--表查询插入

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；