SQL语句创建表
1.定义基本表语句
语法:
USE 数据库名CREATE TABLE 表名
(列名类型(大小) DEFAULT'默认值',
列名类型(大小) DEFAULT'默认值',
列名类型(大小) DEFAULT'默认值',
... ...);
注:绿色部份是可以省略的。
例:CREATE TABLE S (SNO char(2), SNAME char(8), AGE decimal(2), SEX char(2) DEFAULT'男', DEPT char(2));
创建了一个五列的表,其中第四列的默认值为‘男’。
2.定义完整性约束
语法: USE 数据库名
CREATE TABLE 表名(列名类型(大小) DEFAULT'默认值' CONSTRAINT 约束名约束定义, 列名类型(大小) DEFAULT'默认值' CONSTRAINT 约束名约束定义,
列名类型(大小) DEFAULT'默认值' CONSTRAINT 约束名约束定义, ... ...); 注:(1) 绿色部份是可以省略的。
(2) 一个列是可以有多个约束的。
约束定义:
(1)NULL | NOT NULL 用于定义列的空值约束。(定义列) (下面的蓝色部份是单选其中之一)
语法:CONSTRAINT 约束名NULL | NOT NULL
注意:
a. NOT NULL 约束强制列不接受NULL 值。
b. NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录。
例:下面的SQL 语句强制"Id_P" 列和"LastName" 列不接受NULL 值: CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
(2)UNIQUE 约束唯一标识数据库表中的每条记录。(即可以定义列也可能定义表)
语法:CONSTRAINT 约束名UNIQUE (列名, 列名, ... ...);
说明:用于指定基本表在某一个列或多个列的组合上取值必须唯一。定义了UNIQUE约束的那些列称为唯一键。如果为基本表的革一列或多个列的组合指定了UNIQUE约束,则系统将为这些列建立唯一索引,从而保证在表中的任意两行记录在指定的列或列组合上不能取同样的值。
注意:
a. UNIQUE 约束唯一标识数据库表中的每条记录。
b. UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
c. PRIMARY KEY 拥有自动定义的UNIQUE 约束。
d.请注意,每个表可以有多个UNIQUE 约束,但是每个表只能有一个PRIMARY KEY 约束。
例:下面的SQL 在"Persons" 表创建时在"Id_P" 列创建UNIQUE 约束: SQL Server / Oracle / MS access:
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如果需要命名UNIQUE 约束,以及为多个列定义UNIQUE 约束,请使用下面的SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)
(3)PRIMARY KEY 约束唯一标识数据库表中的每条记录。(即可以定义列也可能定义表)
语法:CONSTRAINT 约束名PRIMARY KEY (列名, 列名, ... ...);
说明:用于定义基本表的主键。与UNIQUE约束类似,PRIMARY KEY 约束也是通过建立唯一索引来保证基本表在主键列(某一个列或多个列的组合)上取值的唯一性。然而它们之间也存在着很大差别:在一个基本表中只能定义一个PRIMARY KEY 约束,却能定义多个UNIQUE约束。如果为基本表的某一个列或多个列的组合指定了PRIMARY KEY 约束,那么其中在任何一个列都不能出现空值;而UNIQUE 约束允许出现空值。
注意:
a. 主键必须包含唯一的值。
b. 主键列不能包含NULL 值。
c. 每个表应该都一个主键,并且每个表只能有一个主键。
例:下面的SQL 在"Persons" 表创建时在"Id_P" 列创建PRIMARY KEY 约束:
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如果需要命名PRIMARY KEY 约束,以及为多个列定义PRIMARY KEY 约束,请使用下面的SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
)
(4)FOREIGN KEY 外键(即可以定义列也可能定义表)
语法:CONSTRAINT 约束名FOREIGN KEY (列名, 列名, ... ...)
REFERENCES (列名, 列名, ... ...) ;
说明:指定某一个列或多个列的组合作为外部键,并在外部键和它所引用的主键或唯一键之间建立联系。在这种联系中,包含外部键的基本表称为从表,包含外部键引用的主键或唯一键的表称为主表。一旦为一列或列的组合定义了FOREIGN KEY 约束,系统将保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值,要么取空值。
注意:
a.在REFERENCES 中引用的列必须和FOREIGN KEY 的外部键列一一对应,即列数目相等并且相应列的数据类型相同。
例:
下面的SQL 在"Orders" 表创建时为"Id_P" 列创建FOREIGN KEY: SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
如果需要命名FOREIGN KEY 约束,以及为多个列定义FOREIGN KEY 约束,请使用下面的SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
(5)CHECK 约束用于限制列中的值的范围。(即可以定义列也可能定义表) 语法:CONSTRAINT 约束名CHECK (约束条件);
说明:用于指定基本表中的每一条记录必须满足的条件,可以对基本表在各个列上的值做进一步的约束,如成绩列的取值既不能大于100,也不能小于0。
注意:
a. 如果对单个列定义CHECK 约束,那么该列只允许特定的值。
b. 如果对一个表定义CHECK 约束,那么此约束会在特定的列中对值进行限制。
例:
下面的SQL 在"Persons" 表创建时为"Id_P" 列创建CHECK 约束。CHECK 约束规定"Id_P" 列必须只包含大于0 的整数。
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如果需要命名CHECK 约束,以及为多个列定义CHECK 约束,请使用下面的SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
例:USE lijintao CREATE TABLE S2
(SNO CHAR(2) CONSTRAINT S_PRIM PRIMARY KEY,
SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,
AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL CONSTRAINT AGE_CHK CHECK(AGE BETWEEN 15 AND 45),
SEX CHAR(2) DEFAULT '男' CONSTRAINT SEX_CHK CHECK(SEX = '男' OR SEX = '女'),
DEPT CHAR(2) CONSTRAINT DEPT_CONS NOT NULL);
SQL数据库数据的查询,汇总,统计和分析
8.数据的查询、汇总、统计和分析 本章将深入剖析SELECT命令。
8.1.SELECT命令 SELECT是一个用来从一个或多个表中获取数据的SQL命令。 8.2.简单的SELECT查询 如: SELECT命令至少包含: ?要出现在查询结果中的字段列表,如:身份证号码,姓名,电话号码 ?字段来自哪些表,如:FROM飞狐工作室 ?字段列表也可以是由字段、常量和函数组成的表达式 ?要列出所有字段,只须用* 号 8.3.设置查询结果的字段名 查询需求 请从数据库pubs的authors表,查询出所有作者的代号、姓名、电话号码及住址,而且请使用中文文字作为查询结果的各字段名。 解答
SELECT 作者代号= au_id, 姓名= au_fname+au_lname, 电话号码= phone, 住址= address FROM authors 或(看看你更喜欢哪一种格式?) /* 脚本文件名: Demo92.sql */ USE pubs SELECT au_id AS 作者代号, au_fname+au_lname AS 姓名, phone AS 电话号码, address AS 住址 FROM authors 注意: ?如果您设置的的字段名包含空格,则须加上单引号 SELECT au_fname+au_lname AS‘Name of Author’ FROM authors 任务:查询teacher表的姓名、性别和生日,列名用中文表示;查询class表的所有班级
信息;查询teacher表的教师姓名和年龄。 8.4.关键字ALL和DISTINCT的使用 1.查询需求 请列出“飞狐工作室”表中所有员工的雇用日期,但是日期相同者只列出一次即可。USE NorthwindSQL SELECT DISTINCT雇用日期FROM飞狐工作室 2.查询需求 请列出“飞狐工作室”表中有哪些部门。 USE NorthwindSQL SELECT DISTINCT部门FROM飞狐工作室 ALL为默认项,显示所有查询到的记录,包括重复项。 DISTINCT,对指定字段的内容相同的,仅显示一项。每个SELECT表达式只能有一个DISTINCT关键字。这意味着,DISTINCT是限制整条数据记录都重复者,只显示其中一条,而不是针对单一字段来处理。 任务:请列出“章立民工作室”表中有哪些部门。列出teacher表中的教师职称。
实验一 SQL语言单表查询
实验一SQL语言单表查询 一、实验目的 熟悉SQLServer2008建立数据库和表的方法;掌握SQL语言单表查询方法二.实验内容 1、建立数据库Student 2、建立该数据库的三张表:stu_info,course_info,stu_grade,三张表分别存放学生的学生信息、课程信息和学生选课信息 3、请写出实现如下功能的SQL语句,并在SQLServer2008中进行测试 (1)查询所有同学的姓名和年龄信息,要求使用别名(姓名和年龄),结果如下: (2)查询所有的院系信息,要求取消重复的行,结果如下: (3)查询前5位同学的学号、姓名和成绩信息。查询结果如下: (4)查询所有的男同学学号、姓名、生日和性别信息。查询结果如下:
(5)查询所有总分大于550的男同学信息。查询结果如下: (6)查询所有信息学院和会计学院并且总分大于550分的同学信息。查询结果如下: (7)查询所有籍贯为空的同学信息。查询结果如下: (8)查询总分在530—560之间的同学信息。 查询结果如下:
(9)查询所有在信息学院或会计学院就读的同学信息。查询结果如下: (10)查询所有姓“张”的同学信息。查询结果如下: (11)查询所有姓“张”,而且姓名是两个字的同学信息。查询结果如下: (12)查询所有不姓“张”也不姓“王”的同学信息。查询结果如下: (13)查询所有同学课程编号701的课程成绩,并按由大到小的顺序输出,如果成绩相同,则按学号由小到大排序。查询结果如下:
(14)统计学生成绩表中每个同学的最高分、最低分、平均分和总分。查询结果如下: (15)统计学生成绩表中每个同学的最高分、最低分、平均分和总分,80分以下的成绩不参与统计。 Select stu_id,MAX(grade),MIN(grade),AVG(grade),SUM(grade) From stu_grade Where grade>=80 Group by stu_id 查询结果如下: (16)统计学生成绩表中每个同学的最高分、最低分、平均分和总分,并输出平均分大于87分的信息。查询结果如下: (17)明细汇总: 使用group by子句对查询数据进行分组汇总,为每一组产生一个汇总结果,每个组只返回一行,无法看到详细信息。使用compute和compute by子句既能够看到统计经营部的结果又能够浏览详细数据。 使用compute子句对所有学生的人数进行明细汇总。查询结果如下:
SQL语句从大到小排序
根据下面三个关系模式完成下面习题:答案已设为白色需要就全选设为黑色学生表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”(哎和一),用户反映说看不清楚,公司决定,把存储在数据
SQL语言命令
SQL语言由命令、子句、运算和集合函数等构成。在SQL中,数据定义语言DDL(用来建立及定义数据表、字段以及索引等数据库结构)包含的命令有CREATE、DROP、ALTER;数据操纵语言DML(用来提供数据的查询、排序以及筛选数据等功能)包含的命令有SELECT、INSERT、UPDATE、DELETE。 一、SQL语句 (1)Select 查询语句 语法:SELECT [ALL|DISTINCT] <目标列表达式> [AS 列名] [,<目标列表达式> [AS 列名] ...] FROM <表名> [,<表名>…] [WHERE <条件表达式> [AND|OR <条件表达式>...] [GROUP BY 列名[HA VING <条件表达式>]] [ORDER BY 列名[ASC | DESC]] 解释:[ALL|DISTINCT] ALL:全部;DISTINCT:不包括重复行 <目标列表达式> 对字段可使用A VG、COUNT、SUM、MIN、MAX、运算符等 <条件表达式> 查询条件谓词 比较=、>,<,>=,<=,!=,<>, 确定范围BETWEEN AND、NOT BETWEEN AND 确定集合IN、NOT IN 字符匹配LIKE(“%”匹配任何长度,“_”匹配一个字符)、NOT LIKE 空值IS NULL、IS NOT NULL 子查询ANY、ALL、EXISTS 集合查询UNION(并)、INTERSECT(交)、MINUS(差) 多重条件AND、OR、NOT
ER图+SQL语句练习题new
下图是企业订单管理系统的E-R图,提供对职工信息、客户信息、供应商信息、产品信息、订单信息、订单明细进行管理。
Phone varchar(20) not null, Address varchar(50) null, PostalCode varchar(15) null, City varchar(20) null, constraint PK__Supplier primary key (SupplierID) ) go /*==============================================================*/ /* Table: 产品信息表Products */ /*==============================================================*/ create table Products ( ProductID char(5) not null, ProductName varchar(50) not null, SupplierID char(5) not null, constraint PK__Products primary key (ProductID), constraint FK_PRODUCTS_SUPPLIER foreign key (SupplierID) references Supplier (SupplierID) ) go /*==============================================================*/ /* Table: 客户信息表Customers */ /*==============================================================*/ create table Customers ( CustomerID char(5) not null, CustomerName varchar(50) not null, Phone varchar(20) not null,
SQLSERVER数据库、表的创建及SQL语句命令
SQLSERVER数据库、表的创建及SQL语句命令 SQLSERVER数据库,安装、备份、还原等问题: 一、存在已安装了sql server 2000,或2005等数据库,再次安装2008,会出现的问题 1、卸载原来的sql server 2000、2005,然后再安装sql server 2008,否则经常sql server服务启动不了 2、sql server服务启动失败,解决方法: 进入sql server configure manager,点开Sql server 网络配置(非sql native client 配置),点sqlzhh(我sqlserver 的名字)协议,将VIA协议禁用。再启动Sql Server服务,成功 如图: 二、在第一次安装SQLSERVER2008结束后,查看安装过程明细,描述中有较多项插件或程度,显示安装失败。 解决方法:
1、重新启动安装程度setup.exe,选择进行修复安装,至完成即可。 三、先创建数据库XXX,再进行还原数据库时,选择好备份文件XXX.bak,确定后进行还原,会报如下图的错误。 解决方法: 选择好备份数据库文件后,再进入“选项”中,勾选“覆盖现在数据库”即可。
四、查看数据库版本的命令:select @@version 在数据库中,点击“新建查询”,然后输入命令,执行结果如下 五、数据库定义及操作命令: 按照数据结构来组织、存储和管理数据的仓库。由表、关系以及操作对象组成,把数据存放在数据表中。 1、修改数据库密码的命令: EXEC sp_password NULL, '你的新密码', 'sa' sp_password Null,'sa','sa'
SQL数据库与表的创建
1.创建数据库 --创建数据库 create database db_NetStore --网上书城 on primary (name='db_NetStore', filename='f:\data\db_NetStore.mdf', size=10MB, maxsize=50MB ) log on (name='db_NetStore_log', filename='f:\data\db_NetStore.ldf, size=5MB, maxsize=25MB ) 2.创建表 ①create table Member --会员信息表 ( MemberID int not null primary key,--会员ID UserName varchar(50)not null,--会员登录名Password varchar(50)not null,--会员登录密码RealName varchar(50)not null,--会员真实姓名Sex bit not null,--会员性别 Phonecode varchar(20)not null,--会员电话号码Email varchar(50)not null,--会员E-mail地址Address varchar(200)not null,--会员详细地址PostCode char(10)not null,--邮编 LoadDate datetime not null,--创建时间 ) ②create table Admin --管理员信息表 ( AdminID int not null primary key,--管理员ID AdminName varchar(50)not null,--管理员登录名Password varchar(50)not null,--管理员登录密码RealName varchar(50)not null,--管理员真实姓名Email varchar(50)not null,--管理员E-mail地址LoadDate datetime not null,--创建时间 )
SQL语句测试
工程中心SQL语句综合测试(200分钟内完成) 1、(10分)要求: 选择受理时间在2008-5-1 到2008-6-1之间的所有申请人姓氏为“刘”的数据,并把“新受理编号” 列表示成当前机器时间的年月和原受理编号年月后的数值组合 格式如下: 受理编号,新受理编号,受理时间,申请人 200801112 201008112 2008-01-13 刘XX SQL: Select regnum 受理编号, SUBSTRING(getdate(),1,6)+ SUBSTRING(regnum,6,9) 新受理编号, REGDA TE 受理时间from I_Optinst t where a. Proposer like ‘刘%’and REGDA TE>=’2008-5-1’and REGDA TE<=’2008-6-1’ 需要的表 I_Optinst 业务实例表 REGDA TE(受理日期) Regnum(受理编号) Proposer(申请人) 2、(15分)要求: 前提:只统计业务小类“存量房买卖” ①按照月份分12月列出2008年每个月份的月份对应月份的交易总面积 ②按照月份分12月列出2008年每个月份的月份对应月份的交易均价(申报价格/建筑面积) 格式 年度月份交易总面积年度月份交易均价(元/平方米) 2008-01 23232 2008-01 2323 2008-02 2323 2008-02 232 2008-03 232323 2008-03 7656 2008-04 232323 2008-03 565 2008-05 232323 2008-03 5656 2008-06 232323 2008-03 565 2008-07 232323 2008-03 67 2008-08 232323 2008-03 676 2008-09 232323 2008-03 6767 2008-10 232323 2008-03 8686 2008-11 232323 2008-03 867 2008-12 232323 2008-03 454 需要的表: Fc_room 房间表 BAREA(建筑面积) I_Optinst业务实例表 regdate(受理时间) fc_owner 产权表
SQL查询语句巩固及练习结果
SQL巩固测试题 1.找出供应商名称,所在城市 select 公司名称,城市from 供应商 2.找出华北地区能够供应海鲜的所有供应商列表。 select 公司名称from 供应商a left join 产品b on a.供应商ID = b.供应商ID left join 类别c on b.类别ID = c.类别ID where a.地区= '华北' and c.类别名称= '海鲜'
3.找出订单销售额前五的订单是经由哪家运货商运送的。 select c.订单ID,a.公司名称,round(c.单价*c.数量*(1-c.折扣),2) as 销售额from 运货商a left join 订单b on a.运货商ID = b.运货商 left join 订单明细c on b.订单ID = c.订单ID order by 销售额desc limit 5 4.找出按箱包装的产品名称。 select 产品名称from 产品 where 单位数量like'%箱%'
5.找出重庆的供应商能够供应的所有产品列表。select 产品名称from 产品 where 供应商ID in(select 供应商ID from 供应商where 城市= '重庆')
6.找出雇员郑建杰所有的订单并根据订单销售额排序。 select a.订单ID,round(sum(a.单价*a.数量*(1-a.折扣)),2) as 销售额from 订单明细a left join 订单b on a.订单ID = b.订单ID left join 雇员c on b.雇员ID = c.雇员ID where c.姓氏= '郑' and c.名字= '建杰' group by 订单ID order by 销售额desc 7.找出订单10284的所有产品以及订单金额,运货商。 select b.订单ID,产品名称,round(a.单价*a.数量*(1-a.折扣),2) as 订单金额,c.公司名称from 订单明细a,订单b,运货商c,产品d
SQL查询语句习题
1.检索“商品信息”类别名称=玩具并且零售价在100和300之间的商品的平均零售价, 最大零售价,最小零售价,以及零售价之和 Select 类别名称,零售价, min(零售价)最小零售价, max(零售价 )最大零售价,avg(零售价)平均零售价,sum(零售价)零售价之和from 商品信息 a inner join 商品类别b on a.类别ID= b.类别ID where 类别名称='玩具' group by 类别名称,零售价 2.检索“商品入出库”中审核日期大于2009年3月1日,并且入库类型编号为“采购入库”的商品的总金额(入出库数量*进价)返回数据:入出库编号、总金额 (商品入出库.入库类型编号=入出库类型.入库类型编号) select a.入出库编号,类型名称,总金额=入出库数量*进价from 商品入出库a inner join 入出库类型b on a.入出库类型编号=b.入出库类型编号 inner join 入出库明细c on c.入出库编号= a.入出库编号where 审核日期>'2009-03-01' and 类型名称='采购入库' 3. 统计每个仓库中库存的除去食品之外的总金额 (【仓库】.仓库ID=【库存.仓库ID select 仓库名称,类别名称, sum(数量*零售价)总金额from 仓库a inner join 库存b on a.仓库ID=b.仓库ID inner join 商品信息c on b.商品ID=c.商品ID inner join 商品类别d on c.类别ID=d.类别ID where 类别名称not like '食品' group by 仓库名称,类别名称 4、统计“商品信息”中商品名称相同的信息,要求商品名称的重复次数大于3,返回数据商品名称,次数 select 商品名称,count(商品名称)次数from 商品信息group by 商品名称having count(商品名称)>3 5、统计“库存”中汇总库存数量小于100的商品编码、商品名称、汇总库存数量 select 商品编码,商品名称,count (数量)汇总库存数量from 库存a inner join 商品信息b on a.商品ID=b.商品ID group by 商品编码,商品名称having count (数量)<100 实验11 SQL Server 2000的存储过程 实验目的 掌握使用向导创建存储过程并更新相应数据 掌握使用T-SQL编程的方法 掌握使用T-SQL语句创建一个存储过程并验证 掌握创建和执行带参数的存储过程 熟练使用系统存储过程、系统函数 掌握用在企业管理器中管理存储过程 实验要求 创建一个不带参数的存储过程。 创建一个带参数的存储过程p_count。
常用SQL语句
1.收缩日志文件 Use 数据库名称 backup log 数据库名称 with no_log dbcc shrinkfile(2) 2.将数据库设置为完整恢复模式: USE master; ALTER DATABASE 数据库名称 SET RECOVERY FULL 3.将数据库设置为大容量日志恢复模式: USE master; ALTER DATABASE 数据库名称 SET RECOVERY BULK_LOGGED; 4.将数据库设置为完整简单模式: USE master; ALTER DATABASE 数据库名称 SET RECOVERY simple (右击数据库—属性—选项中,恢复模式。) 查询重复记录 select * from ID0034_pzgl_d where kjn+kjy+pzh in (select kjn+kjy+pzh from ID0034_pzgl_d group by pzh,kjn,kjy having count(*)>1) order by kjn,kjy,pzh 余额解析 --调整初始化数据 update ID0046_InnerAccount set cshxh=m.lsh from (select m.kmdm,m.pzrq,m.zy,m.je,n.lsh,m.jdbz from ID0046_InnerAccount M inner join ID0046_InnerAccountInitialize n on m.kmdm=n.kmdm and m.pzrq=n.fsrq and m.zy=n.zy and m.je=n.je and m.jdbz=n.jdbz where pzh=0) M where m.kmdm=ID0046_InnerAccount.kmdm and m.pzrq=ID0046_InnerAccount.pzrq and m.zy=ID0046_InnerAccount.zy and m.je=ID0046_InnerAccount.je and m.jdbz=ID0046_InnerAccount.jdbz --调整凭证数据 update ID0046_InnerAccount set pzxh=m.pzxh
SQL数据库 数据的查询、汇总 、统计和分析
8.数据的查询、汇总、统计和分析 本章主题 SELECT语句 简单的SELECT查询 设置查询结果的字段名 关键字ALL和DISTINCT的使用 查询结果的输出目的地 WHERE子句的条件搜索功能 SELECT的通配符 字符和通配符冲突时的解决方法 连接条件设置 统计运算的高手:聚合函数 数据分组小计 HAVING子句的使用 ORDER BY子句的使用 查询名列前茅或落后者 活用子查询(SubQuery) 精彩内容不容错过!这一章是本课程的重点!!★★★★★本章将深入剖析SELECT命令。 8.1.SELECT命令 SELECT是一个用来从一个或多个表中获取数据的SQL命令。
8.2.简单的SELECT查询 如: USE NorthwindSQL SELECT身份证号码,姓名,电话号码-- 这里是字段列表 FROM飞狐工作室 SELECT命令至少包含: 要出现在查询结果中的字段列表,如:身份证号码,姓名,电话号码 字段来自哪些表,如:FROM飞狐工作室 字段列表也可以是由字段、常量和函数组成的表达式 要列出所有字段,只须用 * 号 8.3.设置查询结果的字段名 查询需求 请从数据库pubs的authors表,查询出所有作者的代号、姓名、电话号码及住址,而且请使用中文文字作为查询结果的各字段名。 解答 /* 脚本文件名: */ USE pubs SELECT 作者代号 = au_id, 姓名 = au_fname+au_lname, 电话号码 = phone, 住址 = address FROM authors
或(看看你更喜欢哪一种格式) /* 脚本文件名: */ USE pubs SELECT au_id AS 作者代号, au_fname+au_lname AS 姓名, phone AS 电话号码, address AS 住址 FROM authors 注意: 如果您设置的的字段名包含空格,则须加上单引号 SELECT au_fname+au_lname AS‘Name of Author’ FROM authors 任务:查询teacher表的姓名、性别和生日,列名用中文表示;查询class表的所有班级信息;查询teacher表的教师姓名和年龄。 8.4.关键字ALL和DISTINCT的使用 1.查询需求 请列出“飞狐工作室”表中所有员工的雇用日期,但是日期相同者只列出一次即可。USE NorthwindSQL SELECT DISTINCT雇用日期FROM飞狐工作室 2.查询需求 请列出“飞狐工作室”表中有哪些部门。
运用数据透视表SQL语句实现多工作表合并计算
运用数据透视表SQL语句实现多工作表合并计算 (一)原始方法:将所有工作表先手工汇总到同一张工作表中,再做透视表。 (二)数据》获取外部数据》现有链接》浏览更多选择现有链接》选择表格》确定》导入数据对话框》属性》定义》命令文本》输入SQL语句》确定。 数据》连接》连接》工作簿连接》选中名称》属性》即可重新编辑命令文本 1、Select ? from [] union all select ? from [] Select (首行字段名称1, 首行字段名称2) from [工作表名$] union all select (另首行字段名称1,另首行字段名称2) from [另工作表名$] 如: select 科目编码,实际发生额from [sheet1$] union all select 编码,实际发生额from [sheet2$] 注意:连接多个工作表的时候Select后的字段项数一定要一一相对应 2、SQL语句里添加工作表名为字段项 Select "工作表名" as 字段名,字段1,字段2 from [工作表名$] 如: Select "sheet1" as 部门,科目编码,实际发生额from [sheet1$] union all Select "sheet2" ,科目编码,实际发生额from [sheet2$] union all Select "sheet3" ,科目编码,实际发生额from [sheet3$] 3、获取数据源工作表所有字段 前提:确保每张工作表的字段所在位置顺序等格局完全相同 select "工作表名" as 字段名,* from [工作表名$] union all select * from [工作表名$] 如: select "sheet1" as 部门,* from [sheet1$] union all select "sheet2",* from [sheet2$] union all select "sheet3",* from [sheet3$] 重新打开工作表后,安全警告要启用,如果路径变了的话数据》连接》属性》重新选取即可。 四数据透视表SQL语句实例一将透视表分级字段统计的效果转换为同级的字段项统计 普通方法:添加辅助列,将要同级统计的字段弄在同一列,然后再行透视表统计即可,需要手工添加辅助列,更改了数据源。 SQL语句数据导入方法: Select "部门" as 大分类,单位as 次分类,职位类别,姓名from [数据源$] union all Select "年龄分段",年龄分段,职位类别,姓名from [数据源$] union all Select "学历层次",学历层次,职位类别,姓名from [数据源$] 五数据透视表SQL语句实例二 如何管理日常的数据源格式,如果运用select * 的方法去操作的话数据源首行的规范非常重要。
(财务内部审计)审计模式下常用的SQL查询语句示例
审计模式下常用的SQL查询语句示例随着信息技术的不断发展,被审计对象的财务和业务系统也大多使用了专用软件进行计算机管理,如果暂不考虑计算机软硬件本身的系统审计,那么我们所面对的审计也就分为了以财务账套为基础的账套式审计模式和直接对数据本身(包括财务数据和业务数据)审计的数据式审计模式。但不论是何种审计模式,最根本的还是要使用计算机语句对具体问题进行查询分析,即通过数据库的SQL查询语句对数据库中的数据表进行查询分析。 虽然SQL语句的功能很多,且不同数据库环境下的具体使用也会因为所遵循的官方标准不同而略有差别,但多数情况下的使用是相通的。同时因为审计工作的特点,所使用的SQL语句也多是对数据的查询,一般并不会包括对原始数据的增加、删除、修改等操作,所以在计算机审计中重点需要掌握的内容就是灵活利用SQL语句的查询功能。实际审计工作当中,在数据审计模式下我们所常用的是如下所示的语句,也是从实际审计工作中收集出具有代表性的语句,并且所有语句都基于Access数据库,在AO现场审计实施系统中可正常使用。 一、对单个表的条件查询,用where指定查询条件,也有不使用where的情况,多用于对原始表的统计、分类等 该类情况下SQL语句的语法是“select * from 表名where 条件”。可以指定单个条件或多个条件,并可配合其他函数或关键字使用,常用到的类型有如下几类。
(一)对单个条件的简单查询: select * from 存款表where 金额>=100000; 该情况的使用较为简单,适用于任何单条件查询。 (二)对多个条件的查询,各条件间可能是and或or的关系:select * from 存款表where金额>=100000 and 存款时间>= #2006-1-1#; 该情况常用于对多个条件的查询,根据关键字and或or来决定是同时满足所有条件还是仅满足某些条件。 (三)对多个条件的查询,得到符合条件的某字段的合计值:select sum(金额) from 存款表where金额>=100000 and 存款时间>= #2006-1-1# and 存款时间<= #2006-12-31# order by 金额desc, 存款时间asc; 该情况一般用于统计某种条件下的合计值,常用于对某种情况下的总金额情况进行了解。 (四)用集函数按条件查询: select count(* ) from 凭证表where 金额>=100000; select max(发生金额) from 存款表; select avg(发生金额) from 存款表; 该情况类似于上面求和函数的使用,可根据具体需求进行应用。 (五)对关键字的内容进行查询: select * from 凭证表where 摘要like ‘%招待费%’; 该情况较为常用,查询包含某些关键字的所有记录,也可结合关键字
ER图+SQL语句练习题new
下图是企业订单管理系统的E-R 图,提供对职工信息、客户信息、供应商信息、产品信息、订单信息、订单明细进行管理。
U8 数据库BOM表SQL语句查询
U8 数据库BOM表SQL语句查询 --母件详细信息 select c.InvCode as '母件编码',d.cInvName as '母件名称',d.cInvAddCode as '存货代码',d.cInvStd as '规格型号', https://www.360docs.net/doc/f77089724.html,omUnitName as '计量单位',b.ParentScrap as '母件损耗率', Version as '版本号',VersionDesc as '版本说明',VersionEffDate as '版本生效日',Status as '状态' from bom_bom as a join bom_parent as b on a.BomId=b.BomId join bas_part as c on b.ParentId=c.PartId join Inventory as d on c.InvCode=d.cInvCode join ComputationUnit as e ON https://www.360docs.net/doc/f77089724.html,omUnitCode =https://www.360docs.net/doc/f77089724.html,omUnitCode where c.InvCode='03010001' --根据母件编码查询母件详细信息 --子件详细信息 select b.SortSeq as '序号',c.InvCode as '子件编码',d.cInvName as '子件名称',d.cInvAddCode as '子件代码',d.cInvStd as '子件规格', https://www.360docs.net/doc/f77089724.html,omUnitName as '计量单位' ,b.BaseQtyN as '基本用量',b.BaseQtyD as '基本用量',CompScrap as '子件损耗率',EffBegDate as '子件生效日',EffEndDate as '子件失效日' from bom_bom as a join bom_opcomponent as b on a.BomId=b.BomId
结构化查询语言(SQL)
结构化查询语言(SQL) SQL概述 SQL的内容在笔试和上机考试中均占到大约30%的比例,此外它还是查询和视图的基础,因此是学习的重点也是难点。 SQL是结构化查询语言Structure Query Language的缩写。 SQL包含了查询功能、数据定义、数据操纵和数据控制功能,在VFP中没有提供数据控制功能。 SQL主要特点 1.SQL是一种一体化语言。 2.SQL是一种高度非过程化的语言。 3.SQL语言非常简洁。 4.SQL语言可直接以命令方式交互使用,也可嵌入到程序设计语言中以程序方式使用。
查询功能 SELECT命令的特点: 1.可以自动打开数据库、表文件加以查询,而不需要事先用OPEN DATABASE或USE命令打开。 2.可以直接选取数据表中的数据,而不需要事先用SET RELATION命令建立关联。 3.当需要的索引文件不存在时,会自动建立暂存索引文件,以支持快速搜索技术(Rushmore)来查询。 4.其查询结果可输出到文件、表、屏幕或报表上,还可以转换成统计图表。 命令格式:SELECT ——FROM ——WHERE 可与LIST FIELDS——FOR ——对照学习。 关系操作:投影,选择,联接。 说明:功能强大,语法灵活;要处理的数据表无须事先打开,通过FROM子句指明并打开。 1.SELECT 短语:说明要查询的数据;对应的关系操作为投影,类似于FIELDS子句。 2.FROM 短语:说明要查询的数据来自哪个或哪些表,可对单个表或多个表进行查询; 3.WHERE 短语:说明查询条件;对应的关系操作为选择,类似
于FOR子句。如是多表查询还可能过该子句指明联接条件,进行联接。 4.GROUP BY 短语:用于对查询结果进行分组,可利用它进行分组汇总;类似于TOTAL命令。 5.HA VING 短语:跟随GROUP BY 使用,它用来限定分组必须满足的条件; 6.ORDER BY 短语:用于对查询的结果进行排序;类似于SORT 命令。 示例数据库: 学生管理,包括三个表:学生,课程,选课(通过该表反映“学生”与“课程”之间“多对多”的联系。 问题:请同学们分别指出三个表的主关键字。 一、简单查询 简单查询基于单个表。 例: 1.查询学生信息 SELE * FROM 学生&&“*”号代表所有列。 类似于: USE 学生 LIST