SQL小结

<转>[size=medium][/size]
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。各种不同的数据库对SQL语言的支持与标准存在着细微的不同，本文主要是在mySQL上测试的。

本文从以下几个方面开始总结

[/color]
一：DDL--数据定义语言(CREATE，ALTER，DROP，DECLARE) ：

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
1，新建数据库和删除数据库
create database SQL_Test;
drop database sql_test;
2，新建表和删除表
create table tabname(col1 type1 [not null] ,col2 type2 [not null],..);
drop table tabname;
注：如果需要根据已有的表创建新表：
A：create table tab_new like tab_old (创建一个与旧表字段完全一样的新表)
B：create table tab_new as select col1,col2… from tab_old definition;(创建只包含就表中选定的部分字段的表)
例：
图片 图片
3，为表增加一列和删除一列
Alter table tabname add col type;（增加多列的话只需要在后面跟上添加的字段以“,”隔开）
Alter table tabname drop column col;（删除col这一列）
增加多列：Alter table tabname add col1 type， add col2 type....;
删除多列的话则：Alter table tabname drop column col1, drop column col2,...;
图片
4,增加与删除主键
主键可以在创建表示增加，也可以
添加主键： Alter table tabname add primary key(col) ;
添加自增主键：Alter table tb add primary key(id);
Alter table tb change id id int(10) not null auto_increment;
删除主键： Alter table tabname drop primary key;
删除自增主键：
先删除自增长在删除主键
Alter table tb change id id int(10);
Alter table tb drop primary key;
5、创建与删除索引
创建索引：
（1）ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
（2）CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
删除索引：
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
注：索引是不可更改的，想更改必须删除重新建。
6、创建与删除试图
创建视图：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)]  AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
例如： CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;
修改视图：ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]
该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。
删除视图：DROP VIEW [IF EXISTS] view_name [, view_name] ...  [RESTRICT | CASCADE]
该语句用户删除视图，可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。若没有该子句，当指定的视图不存在时，将发生错误。
二、DML--数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
1,简单查询
选择：select * from table1 where 范围;选择部分字段以逗号隔开:select COL1,COL2 from table1 where 范围;
插入：insert into table1(field1,field2) values(value1,value2);(自增字段可以不用指定值)
删除：delete from table1 where 范围;(从表中删除符合条件的行)
更新：update table1 set field1=value1 where 范围;更新多个属性为符合条件的行：update table1 set field1=value1，field2=value2 where 范围;
计算：select field*2 from table;将字段乘2
别名：select filed as name from table；将field命名为name选取出来；
去除重复行： select distinct field from table;(去除field字段中重复的行)这种方法只对单表有效
查找：select * from table1 where field1 like ’%value1%’;%表示一个或多个字符
排序：select * from table1 order by field1,field2 [desc];首先按第一个字段排序，若字段1的值相等，则按字段2来排序
总数：select count（*） as totalcount from table1;统计某一字段的元素个数
求和：select sum(field1) as sumvalue from table1;统计某一字段的和（字符型返回0）
平均：select avg(field1) as avgvalue from table1;求平均值（字符型返回0）
最大：select max(field1) as maxvalue from table1;求最大值（字符型，根据acsii来返回大小）
最小：select min(field1) as minvalue from table1;求最小值
2，多表查询
连接查询
(1)内连接
只返回满足连接条件的数据。
SELECT table1.column, table2.column
    FROM table1, table2

    WHERE table1.column1 = table2.column2;
where 条件要有效，两张表间有一个相同的字段，才好进行有效的多表查询。查询时列名前，加表名或表别名前辍（如果字段在两个表中是唯一的可以不加）
例：查询每个员工的工号，姓名，工资，部门名和工作地点*/

    select empno,ename,sal,dname,loc from emp,dept

    where emp.deptno=dept.deptno;

(2)外连接
除了返回满足连接条的行以外，还返回左（右）表中，不满足条件的行，称为左（右）连接。
SELECT table1.column, table2.column   --右外连接

       FROM table1 right outer join table2

       on table1.column = table2.column;（table1中不符合条件的部分也会显示）
SELECT table1.column, table2.column   --左外连接

       FROM table1 left outer join  table2

       on table1.column = table2.column;


(3)自身连接
将自身表的一个镜像当另一个表来对待。
select e.name,d.name from emp e,emp d where ...;(将e和d做成emp的别名，然后将他们当做两个表来操作)
[color=red]
分组:Group by:
一旦分组完成后,查询只能得到组相关的信息。组相关的信息：（统计信息） count,sum,max,min,avg  分组的标准)
对所分的组进行选择使用：HAVING子句。如 select count(*) from client_info group by number having number >5;
而 select count(*) from client_info where id >2 group by number;
两者差别在于：HAVING是先分组后选择（选择的是组）;而WHERE是先选择后分组。
子查询:
(1)使用IN的子查询
用于判断某个属性值是否在子查询的结果中
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
(2)使用比较运算符的子查询
select a from table where a < (select avg（d） from b );
(3)使用ANY或ALL运算符的子查询
ANY表示至少一或者某一，ALL表示所有或每个；
select a,b,c from a where a <ANY(select d from b );
(4)使用EXISTS运算符的子查询
删除主表中已经在副表中没有的信息:delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
(5)组合查询
将多个查询结果的集合并起来，使用关键字UNION，并且会去掉重复的元组，如果需要保留重复的，则使用UNION ALL。
（select a,b,c from a where a=‘xxx’）union（select a,b,c from a where b = 'xxx'）;