索引:
在表Student(学生信息表)上按Sno(学号)降序建唯一索引:
CREATE UNIQUE INDEX Stusno ON Student(Sno desc); |
删除Student表的Stusno索引:
ALTER TABLE Student DROP INDEX Stusno; |
创建索引可以大大提高系统性能。
第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
第二,可以大大加快数据的检索速度,这也是创建索引的最主要原因。
第三,可以加速表和表之间的连接,特别是在实现数据的参照完整性方面特别有意义。
第四,在使用分组(group by)和排序(order by)子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
第五,通过使用索引,可以在查询过程中,使用优化隐藏器,提高系统的性能。
创建索引缺点。
第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间会更大。
第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
索引是建立在数据库表中的某些列的上面。
1、在经常需要搜索的列上创建索引,可以加快搜索的速度。
2、在作为主键的列上创建索引,强制该列的唯一性和组织表中数据的排列结果。
3、在经常用在连接的列上创建索引,这些列主要是一些外键,可以加快连接的速度。
4、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的。
5、在经常使用在WHERE字句中的列上面创建索引,加快条件的判断速度。
视图:
视图是从一个或几个基本表导出的表,它与基本表不同,是一个虚表。数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。所有基本表的数据发生变化,从视图中查询出的数据也就随之改变。视图就像一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化。
eg: 建立一个计算机系学生的视图(在学生表Student的基础上):
CREATE VIEW CS_Student AS SELECT sno, sname FROM Student WHERE Sdept='CS'; |
视图一经定义,就可以和基本表一样被查询、被删除。
若要求进行修改和插入操作时,仍需保证该视图只有计算机系的学生:
CREATE VIEW CS_Student AS SELECT sno, sname FROM Student WHERE Sdept='CS' WITH CHECK OPTION; |
删除视图语句:
DROP view CS_Student; |
数据定义:
1、定义基本表
SQL语言使用CREATE TABLE语句定义基本表,其基本格式如下:
CREATE TABLE <表名> ( <列名> <数据类型> [列级完整性约束条件] [, <列名> <数据类型> [列级完整性约束条件] ] ... [, <表级完整性约束条件>] ); |
建一个“学生信息”表Student:
CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), ); |
2、修改基本表
SQL语言用ALTER TABLE语句修改基本表,一般格式为:
ALTER TABLE <表名> [ADD <新列名> <数据类型> [完整性约束]] [DROP <完整性约束名>] [MODIFY COLUMN <列名> <数据类型>] |
<表名>是要修改的表,ADD子句用于增加新列和新的完整性约束条件,DROP字句用于删除指定的完整性约束条件,MODIFY COLUMN字句用于修改原有列定义,包括修改列名和数据类型。 |
向Student表增加"入学时间"列,其数据类型为日期型。 ALTER TABLE Student ADD S_entrance DATE; 不论表中原来是否已有数据,新增加的列一律为空值。 要求将年龄的数据类型有字符型(假设原来的数据类型是字符型)改为整数。 ALTER TABLE Student MODIFY COLUMN Sage INT; 增加Student表Sname必须取唯一值的约束条件。 ALTER TABLE Student ADD UNIQUE(Sname); |
3、删除基本表
当某个基本表不在需要时,可以使用DROP TABLE语句删除它。其一般格式为:
DROP TABLE <表名> [RESTRICT | CASCADE]; |
若选择RESTRICT,则该表的删除是有限制条件的:欲删除的基本表不能被其他表的约束所引用(如check,foreign key等约束),不能有视图,不能有触发器,不能有存储过程或函数等。如果存在这些依赖该表的对象,则表不能被删除。
如选择CASCADE,则该表的删除没有限制条件。在删除该表的同时,相关的依赖对象,列如视图,都将被一起删除。
删除Student表:
DROP TABLE Student CASCADE; |
数据查询:
数据库查询是数据库的核心操作。
SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] ... FROM <表名或视图> [, <表名或视图名>] ... [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>] ] [ORDER BY <列名2> [ASC | DESC] ]; |
整个SELECT语句的含义是:根据WHERE子句的条件表达式,从FROM子句指定的基本表或视图中找出满足条件的元组,再按SELECT子句中的目标列表达式,选出元组中的属性值形成结果表。 如果有GROUP BY子句,则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY子句带HAVING子句,则只有满足指定条件的组才予以输出。 如果有ORDER BY子句,则结果表还要按<列名2>的值的升序或降序排序。 |
1、选择表中的若干列 假设在表Student中,查询名为Bill Gates的学生信息: SELECT * from Student WHERE Sname='Bill Gates'; 假设在表Student中,查询名字中有Bill的学生信息: SELECT * from Student WHERE Sname like '%Bill%'; %是通配符,代表人物长度的字符串,_代表任意单词字符 假设在表Student中查询年龄在20~23岁之间(包含20,23)的学生信息: SELECT * from Student WHERE Sage BETWEEN 20 AND 23; 与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND... 假设在表Student中查询计算机系、信息系和数学系学生的姓名和性别: SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS','IS','MA'); 与IN相对的谓词是NOT IN,用于查找属性值不属于指定集合的元组。 假设在表Student中查询没有年龄信息的学生: SELECT * FROM Student WHERE Sage IS NULL; IS不能被等号替代 |
2、ORDER BY子句 用户可以用order by子句对查询的结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认值为升序。 在表Student中,按学生的年龄值升序检索出全部学生的信息: SELECT * FROM Student ORDER BY Sage; 在表Student中,先按专业升序排序,然后同一专业的学生再按年龄降序排序,并输出全部学生信息: SELECT * FROM Student ORDER BY Sdept, Sage desc; |
3、LIMIT子句 LIMIT主要是用于查询之后要显示返回的前几条或中间某几行数据。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制SELECT语句返回指定的记录数。LIMIT接收一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是0。 SELECT * FROM Student LIMIT 5,10; //检索记录行6-15 如果只给定一个参数,他表示返回最前面的记录行数目: SELECT * FROM Student LIMIT 5; //检索前5个记录行 SELECT title FROM post order by create_time DESC limit 0, 20; |
4、聚集函数 SQL语句中常用的聚集函数有以下几种:count,sum,avg,max,min. 总数:select count(*) as totalcount from table1; 求和:select sum(field1) as sumvalue from table1; 平均:select avg(field1) as avgvalue from table1; 最大:select max(field1) as maxvalue from table1; 最小:select min(field1) as minvalue from table1; |
5、GROUP BY子句 GROUP BY子句根据一个或多个属性的值来对元组进行分组,值相等的为一组。 对查询结果分组的目的是为了细化聚集函数的作用对象,分组后聚集函数将作用于每一个组,即每一组都有一个函数值,如下列语句为查询Student表中具有相同年龄的每个组的人数: select Sage, count(*) from Student group by Sage; 如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则使用HAVING短语指定筛选条件。通常,HAVING子句只用在GROUP BY子句的SQL语句中,用来选取符合指定条件的分组。 select Sage, count(*) from Student group by Sage having count(*) >1; |
6、连接查询 若一个查询同时涉及两个以上的表,则称之为连接查询。若有表Student(学生信息表)、SC(选课表),要求查询每个学生及其选修课的情况: SELECT Student.*, SC.* FROM Student , SC WHERE Student.Sno=SC.Sno; 以上连接操作中,只有满足条件的元组才能作为结果输出。若表Student中某些学生没有选课,则在SC表中没有相应的元组,造成最终结果中舍弃掉了这些学生信息。 上述连接称为自然连接、内连接。 有时想以Student表为主体列出每个学生的基本情况及选课情况。若某个学生没有选课,依然将其保存到结果中(在SC表的属性上填空值),这时就需要使用外连接。 SELECT Student.*, SC.* FROM Student LEFT JION SC ON(Student.Sno=SC.Sno); 以上是左外连接,左外连接列出左边表(本例为Student表)中的所有元组,右外连接列出右边表关系中所有的元组。 |
数据操纵:
数据操纵有三种:向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。
1、插入元组 插入元组的INSERT语句的格式为: INSERT INTO table1(field1,field2...) VALUES(value1,value2...); 其功能是将新元组插入到指定表中,其中新元组field1的值为value1,field2的值为value2... 如果INTO语句中没有指定任何属性列名,则新插入的元组必须在每个属性列上均有值。 将一个新学生元组(学号:201009013; 姓名:王明; 性别:男; 所在系:CS; 年龄:23)插入到Student表中的语句为: INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUE('201009013','王明','M','CS',23); |
2、修改数据 修改数据又称为更新操作, UPDATE table1 SET field1=value1, field2=value2 WHERE 范围; 其功能就是修改指定表中满足WHERE子句条件的元组。其中SET子句给出的value值用于取代相应的属性列值。如果省略WHERE子句,则表示要修改表中的所有元组。 eg: 将学生201009013的年龄改为22岁: UPDATE Student SET Sage=22 WHERE Sno='201009013'; |
3、删除数据 删除语句的一般格式为: DELETE FROM table1 WHERE 范围; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句,表示删除表中全部元组,但表仍存在。 eg: 删除学号为201009013的学生记录; DELETE FROM Student WHERE Sno='201009013'; |