SQL结构化数据语言培训讲义

来源:互联网 发布:人工智能的弊端 编辑:程序博客网 时间:2024/03/29 05:49
导读:

  一、SQL语言

  SQL是结构化的查询语言(Structured Query Language),是关系型数据库通讯的标准语言。第一代SQL产品是Oracle,它是当今关系型数据库技术的领导之一。

  1. SQL命令的种类

  DDL,数据定义语言Data Definition Language

  DML,数据操纵语言Data Manipulation Language

  DQL,数据查询语言Data Query Language

  DCL,数据控制语言Data Control Language

  数据管理命令

  事务性控制命令

  1.1数据定义语言DDL

  它是SQL语言的一部分,允许数据库用户创建或重新构件数据库对象。例如,创建或删除一个数据表。主要包括:

  CREATE TABLE

  ALTER TABLE

  DROP TABLE

  CREATE INDEX

  ALTER INDEX

  DROP INDEX

  1.2数据操纵语言DML

  用于在关系型数据库对象中操纵数据。主要包括:

  INSERT

  UPDATE

  DELETE

  1.3数据查询语言DQL

  主要完成对数据库数据的查询,通常就包括一条命令:

  SELECT

  这条命令有很多选项和子句,用于关系型数据库的组合查询。

  1.4数据控制语言DCL

  在SQL中,数据控制命令允许用户在数据库中进行数据的访问控制。通常用于创建与用户访问相关的对象,也控制着用户的权限分配。一些数据控制命令如下:

  ALTER PASSWORD

  GRANT

  REVOKE

  CREATE SYNONYM

  1.5数据管理命令

  数据管理命令能够审计和分析对数据库的操作,还可以用它来帮助分析系统的运行过程。两个主要的数据管理命令:

  START AUDIT

  STOP AUDIT

  1.6事物型控制命令

  BEGIN TRANSACTION 用于开始一个数据库事务

  COMMIT TRANSACTION 用于提交一个数据库事务

  ROLLBACK TRANSACTION用于回退一个数据库事务

  SAVEPOINT创建一组事务中的撤销点

  SET TRANSACTION给事务命名

  2.基本数据类型

  字符型、数字型、日期时间型。

  字符型又分定长型、变长型;

  数值型分整数、小数、位数bit等。

  3.数据库表table

  表是数据的主要存储对象,由多个字段组成,一条纪录也成为一行数据,列是表中基于某个字段的所有数据。在关系型数据库中,主键唯一标识了表中的每一条纪录,通常在创建表的过程中初始化。

  CREATE TABLE命令:

  CREATE TABLE tablename (field1 datatype (NOT NULL),field2 datatype (NOT NULL),field3 datatype (NOT NULL),field4 datatype (NOT NULL),)

  举一个具体的例子:

  CREATE TABLE personal (Pname VARCHAR(40) NOT NULL,Sex CHAR(2) NOT NULL,Age TINYINT NOT NULL,Birthday DATE NULL,Address VARCHAR(100) NULL,Postalcode CHAR(6) NULL)

  ALTER TABLE命令:ALTER TABLE tablename(FIELDNAME ...)

  使用ALTER TABLE命令可以修改列的属性,比如列的数据类型、长度或精度、NULL或NOT NULL等。在不同的数据库系统中,有不同的规定。使用ALTER TABLE还可以给表添加强制列。

  删除表DROP TABLE命令:DROP TABLE tablename

  主键约束:PRIMARY KEY

  例如前面的例子:

  CREATE TABLE personal (Pname VARCHAR(40) NOT NULL PRIMARY KEY,Sex CHAR(2) NOT NULL,Age TINYINT NOT NULL,Birthday DATE NULL,Address VARCHAR(100) NULL,Postalcode CHAR(6) NULL,)

  也可以这样写:

  CREATE TABLE personal (Pname VARCHAR(40) NOT NULL,Sex CHAR(2) NOT NULL,Age TINYINT NOT NULL,Birthday DATE NULL,Address VARCHAR(100) NULL,Postalcode CHAR(6) NULL,PRIMARY KEY(Pname))

  外键约束:FOREIGN KEY

  外键约束是关系型数据库中保证参照完整性的主要机制,定义为外键的列被用作参考在其它表中定义为主键的列。例如:

  另外创建一个表salary:

  CREATE TABLE salary (Pname VARCHAR(40) NOT NULL,Position VARCHAR(20) NULL,Salary_month VARCHAR(20) NOT NULL,Salary NUMBER(5,2) NOT NULL,FOREIGN KEY Pname FK(Pname) REFERENCES personal (Pname))

  在此例中,Pname被定义为salary表的外键,它以personal表中的Pname作为参考。这就确保了salary表中的每一个Pname,在personal表中都有一个相对应的Pname,这就叫父子关系,personal表示父,salary是子。

  NOT NULL约束:此约束将不允许NULL值输入到列中,也就是说在要求NOT NULL的列中,表中每一行数据都必须有值。

  使用Check约束:使用Check约束主要是为了检查特定列的数据有效性,比如在personal表创建时增加一条语句就可以创建CHECK约束:

  CONSTRAINT CHK_Sex CHECK(Sex in ('男','女'))

  4.数据操纵语言DML

  在SQL中,有三种基本的DML命令:

  INSERT

  UPDATE

  DELETE

  INSERT命令:

  使用INSERT命令能够向表中插入新的数据,它带有一些选项,基本语法如下:

  Insert into tablename VALUES('value1','value2','value3',…)

  或 Insert into tablename(col1,col2,…) VALUES('value1','value2',…)

  插入表中的数据,如果是字符型或日期型数据,必须用引号括起来,其它类型不需要。插入的数据项必须和表的列项相对应。例如在salary表中插入一条纪录,应这样写:

  Insert into salary values('李四','经理','2001/4',5000)

  或 Insert into salary(Pname,Position,Salary_month,Salary) values('李四','经理','2001/4',5000)

  另外,用户可以基于另外一张表中的查询结果来往一张表中插入数据,这需要insert 和select 语句的组合。例如有一张与salary表结构完全一样的表salary1,可以直接读取salary1表的数据,然后插入到salary表中:

  Insert into salary [(字段列表)] Select [* | (字段列表)] from salary1 [ where 条件 ]

  (NULL值的插入),说明一下

  UPDATE命令:

  使用UPDATE命令来更新表中已经存在的数据,可以按需要更新一条记录或多条纪录,它的基本语法如下:

  Update tablename SET colname1='value1',colname2='value2',… [ Where 条件 ]

  例如要更新salary表中的一条纪录,可以这样写:

  Update salary SET Salary=6000 where Pname='李四'

  DELETE命令:

  DELETE命令用于删除表中的一行或多行数据,语法如下:

  Delete from tablename [ where 条件 ]

  如果没有条件,就是删除表中所有的数据,例如删除salary表中人名叫"李四"的工资纪录,可以这样写:

  Delete from salary where Pname='李四'

  5.数据查询语言DQL

  就是使用SELECT语句从数据库查询数据,SELECT语句包括一系列的关键字:SELECT、FROM、WHERE、ORDER BY等,简单的语法如下:

  Select [ * | all | distinct column1,column2 ] from table1[,table2];

  用*号表示输出所有的列,distinct用于滤除重复的行,下面有几个简单的例子,分别数出不同的数据行:

  Select * from salary

  Select Pname,Salary_month,Salary from Salary

  Select * from salary where Pname='李四'

  Select Pname,Salary_month,Salary from Salary where Pname='李四'

  Select distinct Pname from Salary

  Select * from salary where Pname='李四' and Salary_month='2001/4'

  分别作解释

  还可以用ORDER BY 子句来对输出结果进行排序,排序需选择列,可以是一列或多列,有升序或降序两种,升序用ASC表示,降序用DESC表示,如:

  Select * from salary ORDER BY Salary_month ASC

  Select * from salary ORDER BY Salary DESC

  利用SELECT语句可以统计表中纪录的条数:

  Select COUNT(*) from salary

  列的别名用于特殊查询下更换表中列的名称,例如:

  Select Pname PersonName,Salary_month Smonth from salary

  6. SQL中的操作符

  在SQL语句中,主要的操作符包括:

  比较操作符

  逻辑操作符

  用于限制条件的操作符

  数学运算符

  比较操作符包括:等于、不等于、大于、小于等操作符,在SQL中,用"="号代表等于,用"<>"表示不等于,用">"表示大于,用"<"。表示小于。举例说明。

  逻辑操作符包括以下:

  IS NULL

  BETWEEN

  IN

  LIKE

  EXISTS

  UNIQUE

  ALL或ANY

  IS NULL:

  用于比较带有NULL值的值,例如搜索salary表中没有Position的纪录:select * from salary where Position IS NULL

  BETWEEN:

  用于在一个值的范围内搜索满足条件的纪录,例如搜索表salary中工资高于5000低于6000的纪录:

  select * from salary where Salary BETWEEN 5000 AND 6000

  IN:

  用于在一个数值的集合里,选择性的匹配条件,用具体的例子说明可能更容易理解:

  select * from salary where Salary_month IN ('2001/4','2001/5')

  查询结果是2001年4月、5月的所有工资纪录。

  LIKE:

  LIKE操作符通过使用匹配符来将一个值同其相似的值比较,用LIKE连接的匹配符有两种:百分号%和下划线 _ 。

  百分号%代表零个、一个或多个字符,下划线代表单个数字或字符,通常有以下一些写法:

  Where Postalcode LIKE '500%' ;查找以500开头,任意长度的值

  Where Postalcode LIKE '%43%';查找任何包含43在任意位置上的值

  Where Postalcode LIKE '_60%';查找任何以60作为第二、第三个字符的值

  Where Postalcode LIKE '%0';查找任何以2结尾的值

  Where Postalcode LIKE '4____0';查找一个六位数的值,它以4开头,0结尾

  EXISTS:

  EXISTS操作符用于查询指定的表中是否存在一行符合某种条件的数据。例如:

  Where EXISTS (Select Pname From salary where Salary>5000)

  搜索salary表中是否存在Salary大于5000的数据纪录。

  在IN、EXISTS、BETWEEN、LIKE之前均可以加NOT 来完成求逆操作,IS NULL的求逆操作为IS NOT NULL。

  UNIQUE:

  UNIQUE操作符在特定的表中搜索每一行使数据唯一(不重复)。

  用法:

  Where UNIQUE (select Salary from salary where Pname='李四')

  该语句测试工资是否重复。

  ALL:

  ALL操作符用于将某个值同另一数据集合的每个值进行比较。用法:Where Salary>ALL(select Salary from salary where Position='经理')测试工资是否比经理的工资都要高。

  ANY:

  ANY操作符用于将某个值同条件中所列的值的任何一个可能的值相比较。

  Where Salary>ANY(select Salary from salary where Position='经理')

  测试工资是否比经理的任何一个工资要高。

  ****注意理解ALL和ANY*****

  连接符

  如果想要在SQL语句中使用多个条件来缩小数据的范围,可以使用连接符来把多个限制条件连接起来,包括:AND 、OR。

  例如:

  Select * from salary Where Position='经理' AND Salary>6000

  Select * from salary Where Pname='李四' OR Pname='张三'

  算术操作符:

  在SQL语句中,通常有四种操作符:

  +(加)、-(减)、*(乘)、/(除)

  例子:

  Select * from salary where Salary+200>5000

  Select * from salary where Salary-200>4500

  Select * from salary where Salary*2>9000

  Select * from salary where Salary/2>2300

  各种操作符也可以进行组合运算,就像一般的数学运算一样。

  二、函数

  1. 统计函数

  在SQL中有很多的函数,统计函数是其中的一种,主要包含以下几个:COUNT、SUM、MAX、MIN、AVG,分别完成返回记录数、累计求和、求最大值、求最小值、求平均值。例子:

  Select COUNT(*) from salary;

  Select SUM(Salary) from salary;

  Select Max(Salary) from salary;

  Select Min(Salary) from salary;

  Select Avg(Salary) from salary;

  2. 数据排序和分组

  对数据分组是通过在Select语句中使用GROUP BY子句来完成的,在使用中,必须位于Where子句之后,ORDER BY子句之前,基本语法:

  SELECT * FROM tablename WHERE condition1 GROUP BY column1,column2… ORDER BY column1,column2…

  Having子句是用来给由GROUP BY子句所创建的组设置条件的,就像Where给Select 设置条件一样,它紧跟在GROUP BY字句的后面,下面有一个完整的例子:

  Select Pname,Avg(Salary) From salary Where Salary_month <>'2001/4' Group by Position Having AVG(Salary)>6000 Order by Salary

  3. 事务处理

  事务就是在逻辑上控制顺序来完成的一组工作序列单元。事务可以由用户以手动的方式来完成,或者可以由数据库程序自动执行。在SQL关系型数据库中,事务是通过使用DML命令来完成的。

  一个事务可以是一条DML语句,也可以是多条DML语句。当管理事务的语句组时,事务组中的每条语句必须作为一个整体全部成功执行,要么就它们都没有成功执行。

  事务的属性描述如下:

  a. 所有的事务都有一个开始和结束。

  b. 所有的事物都可以被保存和撤销。

  c. 如果一个是在执行过程中失败,事务的任何一部分都不会被保存到数据库中。

  用于事务控制的命令有三条:

  COMMIT

  ROLLBACK

  SAVEPOINT

  4. 字符函数

  字符函数是对字符或字符串进行操作的一系列的函数,通常包括以下几种:

  Concatenation:聚集函数,把两个字符串连接合并为一个字符串;

  Substring:从一个字符串中取子串;

  Translate:将一个字符串按字符翻译成另外一个字符串;

  Convert:将一个字符串从一种格式转换成另一种格式的字符串;

  Position:返回一个字符串在另外一个字符串中的实际位置;

  Concatenation函数在不同的数据库系统中有一些差别,比如在Oracle中用"||",而在SQL Server或Sybase中用"+" 。例如:

  Select Pname || 'abc' from salary

  Select Pname+'abc' from salary

  Translate 函数搜索一个字符串中的字符,来检查某个某个特定的字符,并给发现的位置作记号,并且在相同的位置上搜索替代的字符串,用信的字符替换原有的字符。语法如下:

  Translate(String, value1, value2)

  Replace函数功能与Translate函数近似,但是它只能用另外一个字符(串)替换某个特定的字符(串)。

  UPPER函数把字符转换为大写。

  LOWER函数把字符转换为小写。

  SUBSTR函数是从一个字符串中截取一个子串的函数。函数名在不同的数据库中可能有所不同,基本语法是:

  Oracle: SUBSTR(columnname,starting position,length)

  SQL Server:SUBSTRING(column,starting position,length)

  LTRIM和RTRIM,裁剪函数,基本语法是:

  LTRIM(string, ['set']),意为从string左边裁掉以'set'开头的部分;

  RTRIM(string, ['set']),意为从string右边裁掉以'set'结尾的部分;

  Length函数,返回字符串、数字、日期或表达式的字节长度。

  ASCII函数,返回字符串最左边的字符的ASCII码。

  另外还有一些,就不一一讲述。

  5. 日期函数

  在Sybase、Microsoft SQL Server以及Oracle中,获取系统时间的函数稍有不同,Sybase和MS SQL Server 用Getdate(),而Oracle用SYSDATE。

  在已有的日期上加一个数,得到另外一个日期,在SQL Server中用:

  DATEADD(datepart,interval,date)

  datepart表示年、月、日、时、分、秒,比如我们在今天的基础上增加一个月,得到另外一个日期就应该这样写:

  DATEADD(month,1,getdate())

  DATEPART函数返回日期的整数值。

  DATEDIFF函数返回两个日期之间的差值。

  其它就不一一讲了。

  6. 性能调节

  a. 索引

  建立索引的目的主要是为了调节数据检索的性能,索引可以创建,也可以删除,不影响数据。在表上建立的索引和表是分开存储的,而且索引占用的空间通常比表占用的空间要大。那么它是怎么工作的呢?

  当索引建立以后,它记录了索引列的每一个取值在表中的位置,当表中增加新的数据时,索引也增加新的数据项,当在where子句中指定条件时,就首先在索引中进行查找,如果找到,则返回要找的数据在表中的确切位置。

  创建索引的语句:

  Create Index index_name On table_name(column1[,column2])索引可以是唯一的,在索引名前面增加UNIQUE关键字,删除索引的语句:

  Drop Index index_name

  7. 管理数据库

  这部分,根据不同的数据库,进行不同的管理操作,这里就不做介绍了。

  Blog Tags: 我的学习



本文转自

http://dotnet.mblogger.cn/lovecherry/posts/8975.aspx
原创粉丝点击