SQL Server 2000简单查询
1.查询products表中p_price（商品价格）在800以上的商品详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM products
WHERE m_price > 800
2.查询products表中p_quantity（商品数量）在20和50之间的商品编号、商品名称和商品数量，SQL代码如下所示：
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity >= 20
AND p_quantity <= 50
或
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity BETWEEN 20 AND 50
3.查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总额，SQL
代
码如下所示：
USE eshop
SELECT m_account 会员帐号, o_quantity 商品总额
FROM orders
4.查询members表中家庭地址为“湖南”的会员详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_address LIKE '湖南%'
5.查询members表中年龄大于30且性别为“男”的会员详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE DA TEDIFF(YY,m_birth,GETDATE())>30
AND m_sex = '男'
6.查询orders表各商品销售总量前3名的商品编号和销售总量，SQL代码如下所示：
USE eshop
SELECT TOP 3 p_no, o_quantity
FROM orders
ORDER BY o_quantity DESC
7.查询orders表中购买过商品的会员帐号，要求去掉重复行，SQL代码如下所示：
USE eshop
SELECT DISTINCT m_account
FROM orders
8.查询orders表已确认、已支付和已配送的订单详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM orders
WHERE o_confirm_state = '1'
AND o_pay_state = '1'
AND o_send_state = '1'
SQL Server 2000高级查询
1.查询性别为“男”的会员详细信息，查询结果按月薪降序排列，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_sex = '男'
ORDER BY m_salary DESC
2.查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序
排
列，SQL代码如下所示：
USE eshop
SELECT m_account, m_name, YEAR(GETDATE())-YEAR(m_birth) 年龄
FROM members
ORDER BY m_address, m_birth
或
USE eshop
SELECT m_account, m_name, DATEDIFF(YY, m_birth, GETDATE()) 年龄
FROM members
ORDER BY m_address, m_birth
3.查询会员帐号为’liuzc’所购买的商品号和订购日期，并按订购日期升序排列，SQL代码如下所示：
USE eshop
SELECT p_no, o_date
FROM orders
WHERE m_account = 'liuzc'
ORDER BY o_date
4.查询购买商品号为’0910810004’总人数，SQL代码如下所示：
USE eshop
SELECT COUNT(*)
FROM orders
WHERE p_no = '0910810004'
5.查询2005年6月6日前，所有商品的订购总量，要求输出商品号和订购总量，SQL代码如下所示：
USE eshop
SELECT p_no, SUM(o_quantity)
FROM orders
WHERE o_date < '2005-6-6'
GROUP BY p_no
6.查询所有会员的平均月薪，最高月薪和最低月薪之和，SQL代码如下所示：
USE eshop
SELECT A VG(m_salary)+MAX(m_salary)+MIN(m_salary)
FROM members
7.查询所有会员购买商品的种类和，要求输出会员号和商品种类和，SQL代码如下所示：
USE eshop
SELECT m_account, COUNT(DISTINCT p_no)
FROM orders
GROUP BY m_account
8.查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量，SQL代码如下所示：
USE eshop
SELECT TOP 1 p_no, SUM(o_quantity)
FROM orders
GROUP BY p_no
HA VING SUM(o_quantity) > 10
ORDER BY SUM(o_quantity) DESC
SQL Server 2000联接查询
1.查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，SQL代码如下所示：
USE eshop
SELECT DISTINCT members.m_account 会员号, m_name 姓名
FROM members
JOIN orders
ON members.m_account = orders.m_account
WHERE p_no = '0910810004'
2.查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格，SQL代码如下所示：
USE eshop
SELECT members.m_account, m_name,p_price
FROM members
JOIN orders
ON members.m_account = orders.m_account
JOIN products
ON orders.p_no = products.p_no
AND p_name = '爱国者mp3'
3.查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄，SQL代码如下所示：
USE eshop
SELECT A.m_name,YEAR(GETDATE())-YEAR(A.m_birth)
FROM members A
JOIN members B
ON A.m_account <> B.m_account
AND B.m_name = '张自梁'
AND A.m_salary > B.m_salary
AND A.m_address <> B.m_address
4.使用exists查询购买了“0910810004”商品的会员号和姓名，SQL代码如下所示：
USE eshop
SELECT m_account, m_name
FROM members
WHERE EXISTS ( SELECT *
FROM orders
WHERE members.m_account = orders.m_account
AND p_no = '0910810004')
5.使用in查询与“刘法治”购买至少同一种商品的会员号和商品号，SQL代码如下所示：
USE eshop
SELECT DISTINCT A.m_account, A.p_no
FROM orders A
WHERE p_no IN ( SELECT p_no
FROM orders B
WHERE A.m_account <> B.m_account
AND B.m_account IN (SELECT m_account
FROM members
WHERE B.m_account = members.m_account
AND m_name='刘法治'))
6.使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，SQL代码
如
下所示：
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图7-1所示。
7.使用集合查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，并与步骤6 进行比较，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
UNION
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图7-2所示。
8.将members表和orders表之间的左向外联接包括所有会员的信息，包括没有购买商品的会员，SQL 代码如下所示：
USE eshop
SELECT members.*, orders.*
FROM members
LEFT OUTER JOIN orders
ON members.m_account = orders.m_account
SQL Server 2000的视图操作
1.在members表中创建地址为“湖南株洲”的会员的视图V_addr，SQL代码如下所示：
CREATE VIEW V_addr
AS
SELECT *
FROM members
WHERE m_address = '湖南株洲'
2.在orders表中创建购买了商品号为“0910810004”商品的视图V_buy，SQL代码如下所示：
CREATE VIEW V_buy
AS
SELECT *
FROM orders
WHERE p_no = '0910810004'
3.在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的视图V_addr_buy，SQL代码如下所示：
CREATE VIEW V_addr_buy
AS
SELECT members.*
FROM members
JOIN orders
ON members.m_account = orders.m_account
AND p_no = '0910810004'
AND m_address = '湖南株洲'
4.在视图V_addr上查询性别为“男”的会员信息，SQL代码如下所示：
USE eshop
SELECT *
FROM V_addr
WHERE m_sex = '男'
5.在视图V_addr中增加一条记录（内容如下），并查看members表中记录的改变情况。记录内容如
下：（T-SQL）
‘fengxk’，‘冯向克’，‘男’，‘1978-06-28’，‘北京市’，5000.0，‘fxk0628’
SQL代码如下所示：
USE eshop
INSERT INTO V_addr VALUES('fengxk', '冯向克', '男', '1978-06-28', '北京市',5000.0, ' fxk0628')
6.将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”，并查看members中记
录
的改变情况，SQL代码如下所示：
USE eshop
UPDA TE V_addr
SET m_password = 'liuzc0518'
WHERE m_password = 'liuzc518'
7.在V_addr中删除会员号为“fengxk”的记录，并查看members中记录的改变情况，SQL代码如
下
所示：
USE eshop
DELETE
FROM V_addr
WHERE m_account = 'fengxk'
8.删除视图V_addr_buy、V_buy和V_addr，SQL代码如下所示：
USE eshop
DROP VIEW V_addr_buy, V_buy, V_addr
学生表S，课程表C和学生选课表SC，它们的结构如下，试用SQL完成下列操作。
S（S#，SN，SEX，AGE，DEPT）
C（C#，CN，TEACHER）
SC（S#，C#，GRADE）
1. 建立数据库：
CREATE DATABASE student
ON PRIMARY
(
NAME=student_dat,
FILENAME='E:\mydata\student_dat.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
LOG ON
(
NAME=student_log,
FILENAME='E:\mydata\student_log.ldf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
GO
2. 创建表
USE student
CREATE TABLE S
(
S# V ARCHAR(20),
SN V ARCHAR(20),
SEX CHAR(2),
AGE INT,
DEPT V ARCHAR(50)
)
GO
USE student
CREATE TABLE C
(
C# V ARCHAR(20),
CN V ARCHAR(20),
TEACHER V ARCHAR(50)
)
GO
USE student
CREATE TABLE SC
(
S# V ARCHAR(20),
C# V ARCHAR(20),
GRADE INT
)
GO
USE student
CREATE TABLE SC_C
(
CNO V ARCHAR(20),
CNAME V ARCHAR(20),
A VG_GRADE INT
)
GO
⑴用SQL找出选修了“程军”老师教的所有课程的学生姓名。
SELECT DISTINCT SN FROM S
WHERE NOT EXISTS (SELECT * FROM C
WHERE C.TEACHER='程军' AND NOT EXISTS
(SELECT * FROM SC
WHERE
S.S#=SC.S# AND SC.C#=C.C#))
GO
用SQL找出选修了“程军”老师教的课程的学生姓名。
SELECT DISTINCT SN FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER='程军'
GO
⑵用SQL找出“程序设计”课程成绩在90分以上的学生姓名。
SELECT DISTINCT SN FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND CN='程序设计' AND GRADE>90
GO
⑶检索所有比“王华”年龄大的学生姓名、年龄和性别。
SELECT DISTINCT SN,AGE,SEX FROM S
WHERE AGE>(SELECT AGE FROM S
WHERE SN='王华')
GO
⑷检索选修课程“C2”的学生中成绩最高的学生的学号。
SELECT S# FROM SC
WHERE C#='C2' AND GRADE>=ALL(SELECT GRADE FROM SC
WHERE C#='C2')
GO
或
SELECT S# FROM SC
WHERE C#='C2' AND GRADE=(SELECT MAX(GRADE) FROM SC
WHERE C#='C2')
GO
⑸检索学生姓名及其所选课程的课程号和成绩
SELECT SN,C#,GRADE FROM S,SC
WHERE S.S#=SC.S#
GO
⑹检索选修四门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
SELECT SUM(GRADE) FROM SC
WHERE GRADE>=60
GROUP BY S# HA VING COUNT(*)>=4
ORDER BY SUM(GRADE) DESC
GO
⑺检索全是女同学选修的课程的课程号。
SELECT DISTINCT C# FROM SC,S
WHERE SC.S#=S.S# AND SEX='女'
GO
或
SELECT DISTINCT C# FROM SC
WHERE SC.S# IN (SELECT S.S# FROM S
WHERE SEX='女')
GO
⑻检索不学C6课程的男同学的学号和姓名（S#，SN）。
SELECT S#,SN FROM S
WHERE SEX='男' AND S# NOT IN(SELECT S# FROM SC
WHERE C#='C6')
GO
⑼把SC表中每门课程的平均成绩插入到另一个已存在的表SC_C(CNO,CNAME,A VG_GRADE)中。INSERT INTO SC_C(CNO,CNAME,A VG_GRADE)
SELECT C.C#,CN,A VG(GRADE) FROM SC,C
WHERE SC.C#=C.C#
GROUP BY C.C#,CN
GO
⑽从SC表中把吴老师的女学生选课元组删除。
DELETE FROM SC
WHERE S# IN (SELECT S# FROM S
WHERE SEX='女')
AND C# IN (SELECT C# FROM C
WHERE TEACHER LIKE '吴%')
GO
⑾从SC表中把数学课程中低于数学平均成绩的选课元组删去。
DELETE FROM SC
WHERE GRADE<(SELECT A VG(GRADE) FROM SC,C
WHERE SC.C#=C.C# AND CN='数学'
GROUP BY SC.C#)
GO
⑿把吴老师的女学生选课成绩增加4%。
UPDA TE SC
SET GRADE=GRADE+GRADE*0.04
WHERE S# IN(SELECT S# FROM S WHERE SEX='女')
AND C# IN(SELECT C# FROM C WHERE TEACHER LIKE '吴%')
GO
课本知识点总结
P86 页20题
将下图的教务管理数据库的E-R图，转化为关系模型
P87 页23题
P124 例5—7由学生、课程和选课3个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩
CREATE VIEW 学生成绩（学号，姓名，课程名，成绩）
AS SELECT 学生.学号，学生.姓名，课程.课程名，选课.成绩
FORM 学生，课程，选课
WHERE学生.学号=选课. 学号AND课程.课程号=选课.课程号AND学生.所在系=’计算机系’；
P124 例5—8 将学生的学号、总成绩、平均成绩定义成一个视图
CREATE VIEW 学生成绩统计（学号，总成绩，平均成绩）
AS SELECT 学号，SUM（成绩），A VG(成绩)
FROM 选课
GROUP BY 学号;
P128 例5—11求选修C1课程的学生的学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同，则按学号的升序排列
SELECT 学号，成绩
FROM 选课
WHERE 课程号=‘C1’
ORDER BY 成绩DESC，学号ASC;
P128 例5—13求数学系或计算机系姓张的学生的信息
SELECT *
FROM 学生
WHERE 所在系IN (‘数学系’，‘计算机系’)AND 姓名LIKE ‘张%’；
P212 习题9
P199 例6-30 设在读者表中增加了“借书册数”字段，现要求统计借书者在1998年~1999年间所借书的册数，并将结果送入读者表中的借书册数字段。
UPDATE 读者
SET 借书册数=（SELECT COUNT(*)
FROM 读者，借阅
WHERE 借者.书证号=借阅.读者书证号AND借阅日期
BETWEEN ‘1998-01-01’AND‘1999-12-31’）
P199 例6-31删除单位是计算机系的读者借阅记录。
DELETE 借阅
WHERE 读者书证号IN(SELETE 书证号FROM 读者WHERE 单位=‘计算机系’) 在选课表中插入学号为98011，课程号为c10，成绩为90的记录
insert
into 选课
values ('98011','c10',null);
insert
into 学生
values('10228','shenyim','20','na','jsj')
select distinct *
from 学生where 姓名='shenyim'
update 学生
set 年龄='22'
update 学生
set 姓名='sdf'
where 学号='10228'
删除学生表
delete
from 学生
insert
into
学生
values('10220','sds','20','na','sjuj')
P239 习题9
SQL语句实例 欧阳光明(2021.03. 07) 表操作 例1对于表的教学管理数据库中的表STUDENTS ,可以定义如下： CREATE TABLE STUDENTS (SNO NUMERIC (6, 0) NOT NULL SNAME CHAR (8) NOT NULL AGE NUMERIC(3,0) SEX CHAR(2) BPLACE CHAR(20) PRIMARY KEY(SNO)) 例2对于表的教学管理数据库中的表ENROLLS ,可以定义如下：CREATE TABLE ENROLLS (SNO NUMERIC(6,0) NOT NULL CNO CHAR(4) NOT NULL GRADE INT PRIMARY KEY(SNO,CNO) FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO) FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
*欧阳光明*创编2021.03.07 CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100))) 例3根据表的STUDENTS表，建立一个只包含学号.姓名.年龄的女学生表。 CREATE TABLE GIRL AS SELECT SNO, SNAME, AGE FROM STUDENTS WHERE SEX=女?; 例4删除教师表TEACHER。 DROP TABLE TEACHER 例5在教师表中增加住址列。 ALTER TABLE TEACHERS ADD (ADDR CHAR(50)) 例6把STUDENTS表中的BPLACE列删除，并且把引用 BPLACE列的所有视图和约束也一起删除。 ALTER TABLE STUDENTS DROP BPLACE CASCADE 例7补充定义ENROLLS表的主关键字。 ALTER TABLE ENROLLS ADD PRIMARY KEY (SNO,CNO);
SQL学习 2．1 SQL 简介 当面对一个陌生的数据库是，通常需要一种方式与它进行交换，以完成用户所需要的各种工作，这个时候，就要用到SQL语言了，由于SQL 语言的标准化，所以大多数关系型数据库系统都支持SQL语言，它已经发展成多种平台进行交互操作的底层会话语言。 2．2 SQL 使用入门 2．2．1 SQL分类 SQL 语句主要可以划分为以下3个类别 1）、DDL (Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。 2）、DML(Data Manipulation Languagr)语句：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。 3）、DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别、主要的语句关键字包括grant、revoke等。 2．2．2 DDL 语句 DDL是数据定义语言的缩写，是对数据库内部的对象进行创建（create）、删除（drop）、修改（alter）的操作语言，它和DML语句的最大区别是DML只是对表内部数据的操作，而不涉及表定义，结构的修改，更不会涉及其它的对象，DDL语句更多地被数据库管理员（DBA）所使用，一般开发人员很少使用。 下面通过一些例子来介绍MySQL中常用的DDL语句的使用方法。 1、创建数据库 因为所有的数据都存储在数据库中，因此需要学习的第一个命令是创建数据库，语法如下：
精典的SQL语句ffice ffice" /> 1. 行列转换--普通 假设有张学生成绩表(CJ)如下 Name Subject Result 张三语文80 张三数学90 张三物理85 李四语文85 李四数学92 李四物理82 想变成 姓名语文数学物理 张三80 90 85 李四85 92 82 declare @sql var char(4000) set @sql = 'select Name' select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Res ult end) ['+Subject+']' from (select distinct Subject from CJ) as a select @sql = @sql+' from test group by name' exec(@sql) 2. 行列转换--合并 有表A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表B BR>id pid 1 1,2,3 2 1,2 3 1 创建一个合并的函数 create function fmerg(@id int) returns var char(8000) as begin declare @str var char(8000) set @str=''
select @str=@str+','+cast(pid as var char) from 表A where id=@id se t @str=right(@str,len(@str)-1) return(@str) End go --调用自定义函数得到结果 select distinct id,dbo.fmerg(id) from 表A 3. 如何取得一个数据表的所有列名 方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOL UMN表中取得该数据表的所有列名。 SQL语句如下： declare @objid int,@objname char(40) set @objname = 'tablename' select @objid = id from sysobjects where id = object_id(@objname) select 'Column_name' = name from syscolumns where id = @objid order b y colid 是不是太简单了？呵呵不过经常用阿. 4. 通过SQL语句来更改用户的密码 修改别人的,需要sysadmin role EXEC sp_password NULL, 'newpassword', 'User' 如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa 5. 怎么判断出一个表的哪些字段不允许为空？ select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE= 'NO' and TABLE_NAME=tablename 6. 如何在数据库里找到含有相同字段的表？ a. 查已知列名的情况 SELECT https://www.doczj.com/doc/f23712728.html, as TableName,https://www.doczj.com/doc/f23712728.html, as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U' AND https://www.doczj.com/doc/f23712728.html,='你的字段名字' b. 未知列名查所有在不同表出现过的列名 Select https://www.doczj.com/doc/f23712728.html, As tablename,https://www.doczj.com/doc/f23712728.html, As columnname From syscolumns s1, sysobjects o Where s1.id = o.id And o.type = 'U' And Exists ( Select 1 From syscolumns s2 Where https://www.doczj.com/doc/f23712728.html, = https://www.doczj.com/doc/f23712728.html, And s1.id <> s2.id ) 7. 查询第xxx行数据 假设id是主键： select *
一、基础 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1. dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….)删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname 10、说明：几个简单的基本的sql语句 选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料! 排序：select * from table1 order by field1,field2 [desc] 总数：select count as totalcount from table1 求和：select sum(field1) as sumvalue from table1
1 SQL语句--Group By总结 1. Group By 语句简介： Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。 P.S. 这里真是体会到了一个好的命名的力量，Group By从字面是直接去理解是非常好理解的。恩，以后在命名的环节一定要加把劲:)。话题扯远了。 2. Group By 的使用： 上面已经给出了对Group By语句的理解。基于这个理解和SQL Server 2000的联机帮助，下面对Group By语句的各种典型使用进行依次列举说明。 2.1 Group By [Expressions]: 这个恐怕是Group By语句最常见的用法了，Group By + [分组字段](可以有多个)。在执行了这个操作以后，数据集将根据分组字段的值将一个数据集划分成各个不同的小组。比如有如下数据集，其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键： FruitName ProductPlace Price Apple China $1.1 Apple Japan $2.1 Apple USA $2.5 Orange China $0.8 Banana China
$3.1 Peach USA $3.0 如果我们想知道每个国家有多少种水果，那么我们可以通过如下SQL语句来完成： SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国 FROMT_TEST_FRUITINFO GROUPBYProductPlace 这个SQL语句就是使用了Group By + 分组字段的方式，那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。”很好理解对吧。这里值得注意的是结果集中有两个返回字段，一个是ProductPlace(出产国), 一个是水果种类。如果我们这里水果种类不是用Count(*)，而是类似如下写法的话： SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace 那么SQL在执行此语句的时候会报如下的类似错误： 选择列表中的列'T_TEST_FRUITINFO.FruitName'无效，因为该列没有包含在聚合函数或GROUPBY子句中。 这就是我们需要注意的一点，如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。我们可以将Group By操作想象成如下的一个过程，首先系统根据SELECT 语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了。 2.2 Group By All [expressions] ： Group By All + 分组字段, 这个和前面提到的Group By [Expressions]的形式多了一个 关键字ALL。这个关键字只有在使用了where语句的，且where条件筛选掉了一些组的情况才可以看出效果。在SQL Server 2000的联机帮助中，对于Group By All是这样进行描述的：
SQL查询语句大全集锦 MYSQL查询语句大全集锦 一、简单查询 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的 表或视图、以及搜索条件等。 例如，下面的语句查询testtable表中姓名为“张三”的nickname字段和email字段。 复制内容到剪贴板 代码:SELECT `nickname`,`email`FROM `testtable`WHERE `name`='张三' (一) 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例如，下面语句显示testtable表中所有列的数据： 复制内容到剪贴板 代码:SELECT * FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 例如： 复制内容到剪贴板 代码:SELECT nickname,email FROM testtable 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名 列名列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列 标题：
复制内容到剪贴板 代码:SELECT 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认 为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数 使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是 表示一百分数，指定返回的行数等于总行数的百分之几。 例如： 复制内容到剪贴板 代码:SELECT TOP 2 * FROM `testtable` 复制内容到剪贴板 代码:SELECT TOP 20 PERCENT * FROM `testtable` (二) FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图， 它们之间用逗号分隔。 在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列 所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定： 复制内容到剪贴板 代码:SELECT `username`,citytable.cityid FROM `usertable`,`citytable` WHERE usertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名： 复制内容到剪贴板 代码:表名 as 别名 表名别名例如上面语句可用表的别名格式表示为： 复制内容到剪贴板
常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份 BACKUP DATABASE pubs TO testBack
4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname
根据下面三个关系模式完成下面习题：答案已设为白色需要就全选设为黑色学生表student 第一章课件：编写基本的sql语句。 1．查询所有学生情况。 3．查询所有学生的姓名，性别以及年龄。 5．查询所有学生10年后的年龄。 7．查询所有课程（列名用中文显示）。 9．查看竟有那些学生选课（重复学号显示一次）。 11．显示课程表的边结构。第二章课件：约束和排序数据。 01．查询计算机系的所有学生的姓名和年龄。 02．查询体育课的学分。 03．查询年龄小于18的学生。 04．查询年龄大于20的学生。 05．查询年龄介于18和20之间的学生（包括18和20）。 06．查询年龄不在18和20之间的学生。 07．查询年龄为18，20，22的学生。 08．查询年龄不是18，20，22的学生。 09．查询所有姓张的学生。 10．查询所有没有先行课的课程。 11．查询有先行课的课程。 12．在计算机系中找，姓张的男生。 13．在计算机系中找，姓张的或者姓李的男生并且按照年龄从大到小排序。 14．查询所有学生信息，显示结果先按系从大到小排序，再按年龄排序。 第三章课件：多表查询 1．查询每个学生（学号）选了哪门课（课程）得了多少分 2．查询每个学生（姓名）选了哪门课（课程号）得了多少分 3．查询每个学生（姓名）选了哪门课（课程名）得了多少分 4．查询一下王林选可哪门课得了多少分。 5．查询每个学生的成绩类别（优、良还是及格）。 6．查询哪个学生没有选课（用外查询）。 7．查询哪门课没有人选（用外查询）。 第四章课件：组函数
1．查询一下所有课程的平均分，最高分，最低分和总分数。 2．查询一下有多少个学生参加选课。 3．查询一下计算机系有多少人过20岁。 4．统计一下计算机系的男生多少人。 5．查询一下每个学生考试的最高分和最低分。 6．查询每门课（课程号）的最高分和最底分。 7．查询每门课（课程名）的最高分和最底分。 8．查询计算机系中男生多少人，女生多少人。 9，查询人数在三百人以上的系。 10．查询选修人数在三人（包括三人）的课程（课程名）。 11．查询各科考试成绩最低的同学。 12．查询考试成绩小于所选课程平均分的人。（有能力的同学选做） 第五章课件：子查询 1．查询所有比王林大的同学信息。 2．查询和王林同在一个系的所有学生信息。 3．查询一下谁的成绩（所有成绩）最低。 4．查询一下每门课成绩最底的同学（要姓名，和成绩）。 5．查询一下哪个学生没有选课（用子查询）。 6．查询一下哪门课没有人选（用子查询）。 7．查询一下和王林一个系，但是比他年龄大的同学。 第六章课件：ddl语句 1．创建以上四个表，要求每个表必须有主键，表和表之间必须有外间关联。 3．写出insert语句，给表添加以上数据。 5．提交所有操作。 7．将王林的年龄设置为空。 9．将张大民调到计算机系。 11．将体育课的学分设置成和管理学学分一样（update 中带有子查询）。 13．回滚所有操作。 9．某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的“o和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据
常用经典S Q L语句大 全完整版
常用经典SQL语句大全完整版--详解+实例下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’--- 开始备份
BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2… from tab_old definition only 5、说明： 删除新表：drop table tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col….)
一个项目涉及到的50个Sql语句(整理版) --1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 --3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 --4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ --创建测试数据 create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(C varchar(10),Cname nvarchar(10),T varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(T varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(S varchar(10),C varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80) insert into SC values('03' , '01' , 80) insert into SC values('03' , '02' , 80) insert into SC values('03' , '03' , 80) insert into SC values('04' , '01' , 50) insert into SC values('04' , '02' , 30) insert into SC values('04' , '03' , 20) insert into SC values('05' , '01' , 76) insert into SC values('05' , '02' , 87)
数据库经典SQL语句大全 篇一：经典SQL语句大全 下列语句部分是Mssql语句，不可以在access中使用。 SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先，简要介绍基础语句： 1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的 device USE master EXEC sp_addumpdevice 'disk'，'testBack'， 'c:mssql7backupMyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key]，col2 type2 [not null]，..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1，col2? from tab_old definition only 5、说明： 删除新表： tabname 6、说明： 增加一个列：Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明： 添加主键：Alter table tabname add primary key(col) 说明： 删除主键：Alter table tabname drop primary key(col) 8、说明： 创建索引：create [unique] index idxname on tabname(col?.) 删除索引：drop index idxname 注：索引是不可更改的，想更改必须删除重新建。
一. 数据库简介和创建 1. 系统数据库 在安装好SQL SERVER后，系统会自动安装5个用于维护系统正常运行的系统数据库： （1）master：记录了SQL SERVER实例的所有系统级消息，包括实例范围的元数据（如登录帐号）、端点、链接服务器和系统配置设置。 （2）msdb：供SQL SERVER 代理服务调度报警和作业以及记录操作员的使用，保存关于调度报警、作业、操作员等信息。（备份还原时） （3）model：SQL SERVER 实例上创建的所有数据库的模板。 （4）tempdb：临时数据库，用于保存临时对象或中间结果集，为数据库的排列等操作提供一个临时工作空间。（每次启动都会重新创建） （5）Resource：一个只读数据库，包含了SQL SERVER 的所有系统对象。（隐藏的数据库） 2. 数据库的组成 2.1 数据文件 （1）主要数据文件：扩展名为 .mdf ，每个数据库有且只能有一个。 （2）次要数据文件：扩展名为 .ndf ，可以没有或有多个。 2.2 日志文件 扩展名为 .ldf ，用于存放恢复数据库的所有日志信息。 2.3 数据的存储分配 （1）数据文件和日志文件的默认存放位置为：\Programe Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data文件夹。 （2）数据的存储分配单位是数据页。一页表是一块8KB的连续磁盘空间。（3）页是存储数据的最小空间分配单位，页的大小决定了数据库表中一行数据的最大大小。
3. SQL语句数据库操作 （1）创建数据库 CREATE DATABASE database_name 二. SQL基础 SQL（Structured Query Language，结构化查询语言）是用户操作关系数据库的通用语言。 1. SQL功能概述
2010/7 1. 有4个关系模式如下：出版社（出版社编号，出版社名称）；图书（图书编号，书名，出版社编号，定价）；作者（作者编号，姓名）；著书（图书编号，作者编号，作者排序） 注：作者排序=1表示第一作者，依此类推。用SQL语句，完成第36~39题。 (1).检索所有定价超过20元的书名。 答案：SELECT书名（1分） FROM图书（1分） WHERE定价>20（2分） (2).统计每个出版社图书的平均定价。 答案：SELECT出版社编号，A VG（定价）（2分） FROM图书（1分） GROUP BY出版社编号（1分） (3).将科学出版社的所有图书定价下调5%。 答案：UPDATE图书SET定价=定价*0.95（1分） WHERE出版社编号IN（1分） （SELECT出版社编号FROM出版社（1分） WHERE出版社名称="科学"）（1分） 【说明】WHERE出版社名称LIKE"科学"也正确。 (4).列出所有图书的书名、第一作者姓名和出版社名称。 答案：SELECT书名，姓名，出版社名称（1分） FROM出版社A，图书B，作者C，著书D（1分） WHEREA.出版社编号=B.出版社编号ANDB.图书编号=D.图书编号（1分） ANDC.作者编号=D.作者编号AND作者排序=1。(1分) S(SNO,SNAME,AGE,SEX，SDEPT) SC(SNO,CNO,GRADE) C(CNO,CNAME,CDEPT，TNAME) 1.试用SQL的查询语句表达下列查询： ①检索LIU老师所授课程的课程号和课程名。 ②检索年龄大于23岁的男学生的学号和姓名。 ③检索至少选修LIU老师所授课程中一门课程的女学生姓名。 ④检索W ANG同学不学的课程的课程号。 ⑤检索至少选修两门课程的学生学号。 ⑥检索全部学生都选修的课程的课程号与课程名。 ⑦检索选修课程包含LIU老师所授课程的学生学号。 2．试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询： ①统计有学生选修的课程门数。 ②求选修C4课程的学生的平均年龄。 ③求LIU老师所授课程的每门课程的学生平均成绩。 ④统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 ⑤检索学号比WANG同学大，而年龄比他小的学生姓名。 ⑥检索姓名以WANG打头的所有学生的姓名和年龄。 ⑦在SC中检索成绩为空值的学生学号和课程号。 ⑧求年龄大于女同学平均年龄的男学生姓名和年龄。 ⑨求年龄大于所有女同学年龄的男学生姓名和年龄。 3．试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作： ①往基本表S中插入一个学生元组（'S9'，'WU'，18）。
精妙SQL语句收集 摘要:一、基础 二、提升 三、技巧 正文: SQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作。 一、基础 1、说明：创建数据库 Create DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表) B：create table tab_new as select col1,col2... from tab_old definition only 5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加var char类型的长度。 7、说明：添加主键：Alter table tabname add primary key(col)
/* 标题：一个项目涉及到的50个Sql语句(整理版) 作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开) 时间：2010-05-10 地点：重庆航天职业学院 说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。 问题及描述： --1.学生表 Student(Sid,Sname,Sage,Ssex) --Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 --2.课程表 Course(Cid,Cname,Tid) --Cid --课程编号,Cname 课程名称,Tid 教师编号 --3.教师表 Teacher(Tid,Tname) --Tid 教师编号,Tname 教师姓名 --4.成绩表 SC(Sid,Cid,score) --Sid 学生编号,Cid 课程编号,score 分数 */ --创建测试数据 create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(Tid varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80)