数据库一些基本操作

索引：

  在表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';