1.w3School SQL简单回顾

一.基础

select
distict
where:=,<>,>,<,>=,<=,between,like
and & or:
order by : desc降序/asc升序(默认)
insert:insert into tableName values(v1,v2,...);
update:update tableName set col=newVal where col=value;
delete:delete from tableName where col=value;

二.高级

limit:(sql server:top,select top 50 percent * from person:查询表中50%的记录数);
like(not like):配合通配符%,_使用;

通配符:配合like使用;
%:替代一个或多个字符
_:仅替代一个字符
[charlist]:字符列中的任意单一字符
[^charlist]/[!charlist]:不在字符列中的单一字符
slect * from person where name like '[!LW]%';
#查询所有名字不以L,W开头的人;

in:
between...and...(not between...and...):
如下:
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter';
以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人

aliases:别名
select name as stu_name
from student
where age<10;

JOIN(INNER JOIN): 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

SELECT INTO用法(mysql当前不支持该语法)

select *
into persons_backup [in internal_database]
from persons;
#将persons表中的数据全部复制到internal_database(省略则表示当前库)数据库的persons_backup表中;

#实例1:将当前数据库的persons复制到backup数据库的persons表
select * 
into persons in 'backup.mdb'
from persons;
#实例2:将person和order表中的数据汇总到数据库person_order_backup.mdb的person_order表中;
select person.lastName,order.orderNo
into person_order in 'person_order_backup.mdb'
from person
inner join order
on person.id=order.id;

#mysql语法
create new_table 
from (
    select * 
    from old_table
    )

创建约束

#mysql语法
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P) #放在后面指定一列
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) #指定多列
)
#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)
)
#修改添加单个约束
ALTER TABLE Persons
ADD UNIQUE (Id_P)
#修改 添加多个约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
#删除约束,mysql语法
ALTER TABLE Persons
DROP INDEX uc_PersonID
#删除约束,SQL Server / Oracle / MS Access语法
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

创建索引

#降序创建索引
create index order_name_index
on table_name(order_name desc);
#删除索引
alter table table_name drop index index_name;

alter语句

#添加列
alter table table_name
add column_name datatype;
#删除列
alter table table_name
drop COLUMN column_name;
#如果不支持alter...drop..语法,则
alter table table_name
alter COLUMN column_name datatype;

date函数(mysql)

now():返回当前日期和时间;
curdate():返回当前日期;
curtime():返回当前时间;
date():提取日期或日期/时间表达式的日期部分;
extract():返回日期/时间按的单独部分
date_add():给日期添加指定的时间间隔;
date_sub():从日期减去指定的时间间隔;
datediff():返回两个日期之间的天数;
date_format():不同的格式显示日期/时间;

DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY

group by:用于结合合计函数,根据一个或多个列对结果集进行分组

#我们拥有下面这个 "Orders" 表：
#O_Id   OrderDate   OrderPrice  Customer
#1      2008/12/29  1000        Bush
#2      2008/11/23  1600        Carter
#3      2008/10/05  700         Bush
#4      2008/09/28  300         Bush
#5      2008/08/06  2000        Adams
#6      2008/07/21  100         Carter
#现在，我们希望查找每个客户的总金额（总订单）。
#我们想要使用 GROUP BY 语句对客户进行组合。

select Customer ,sum(OrderPrice)
from Orders
group by Customer;
#得到结果集如下:
#Customer   SUM(OrderPrice)
#Bush       2000
#Carter     1700
#Adams      2000

having:在sql中增加having子句的原因是,where关键字无法与合计函数一起使用

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

format语法:

SELECT FORMAT(column_name,format) FROM table_name;
#参数 描述
#column_name    必需。要格式化的字段。
#format 必需。规定格式。

TRUNCATE TABLE table_name

删除表中的数据(只是删除数据)

2.mysql手册学习补充

1.基础

mysql>#查看版本和当前日期
mysql>select version(),current_day;
mysql>select version;select now();select user();

1.1.创建数据库/表等

mysql>show databasses;
mysql>create database dbName;
mysql>use dbName;
mysql>show tables;
mysql>create table pet (name varchar(20),owner varchar(20),sex char(10),birth DATE);
mysql>show tables;
mysql>describe pet
mysql>#加载本地问价到mysql中
mysql>load data local infile '/path/pet.txt' into table pet lines terminated by '\r\n';
mysql>insert into pet values('puffball','diane','f','1999-03-30');

1.2.从表检索信息

mysql>select * from pet;
mysql>delete from pet;#只删除元组数据
mysql>drop from pet;#会删除表关系
mysql>update pet set brith='1989-08-31' where name='bowser';

日期计算

要想确定每个宠物有多大，可以计算当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年。以下查询显示了每个宠物的出生日期、当前日期和年龄数值的年数字。

select name,birth,curdate(),
    (year(curdate())-year(brith)-(right(curdate())<right(birth)) as age
from pet
order by name;
#year()提取日期的年份;
#right()在这里提起日期的后5位进行比较,比较的结果为0(false),1(true)

+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

提起时间函数

YEAR( )
MONTH( )
DAYOFMONTH( )
QUARTER(date) :季度
MONTHNAME(date) :返回date是几月(按英文名返回),如February
DAYNAME(date) :返回date是星期几(按英文名返回),如Thursday

找出下个月生日的动物.如果当前月份是12月，就有点复杂了

select name,brith from pet
where month(brith)=month(date_add(month(curdate(),interval 1 month));

#另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果月份当前值是12，则“回滚”到值0)：
#MONTH返回在1和12之间的一个数字，MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD( )以后加1
select name,brith from pet
where month(birth)=mod(month(curdate()),12) +1;                

模式匹配

%:匹配任意数目字符,包括领字符;
_:匹配任何单一字符;

MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。

‘.’匹配任何单个的字符。
“[...]”匹配在方括号内的任何字符;例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字;
“ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符;

#不区分大小写的查找以“b”开头的名字，使用“^”匹配名字的开始：
select * from pet where name regexp '^b';
#区分大小写的查找以b开头的名字
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
#查找以fy结尾的名字
SELECT * FROM pet WHERE name REGEXP 'fy$';
#找出包含一个“w”的名字，使用以下查询：
select * from pet where name regexp 'w';
#为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

2.获取数据库和表信息

#查看当前使用数据库
select database();
#查看表
show tables;
#查看表结构
describe table_name;
#查看表的索引信息
show index from table_name;

3.批处理模式下使用SQL

#批处理基本语句
shell> mysql < batch-file
#Windows下运行mysql，并且文件中有一些可以造成问题的特殊字符，可以这样操作：
C:\> mysql -e "source batch-file"
#命令行上指定连接参数，命令应为 h:主机ip
shell> mysql -h host -u user -p < batch-file
shell> Enter password: ********
#如果你有一个产生多个输出的查询，你可以通过一个分页器而不是盯着它翻屏到屏幕的顶端来运行输出：
mysql < batch-file | more
#你可以捕捉文件中的输出以便进行进一步的处理：
mysql < batch-file > mysql.out
#如果你想要在批模式中得到交互输出格式，使用mysql -t。为了回显以输出被执行的命令，使用mysql -vvv

#你还可以使用源代码或 \.命令从mysql提示符运行脚本：
mysql> source  path/filename.sql;
mysql> \.  path/filename.sql;

4.常用例子

任务：找出最贵物品的编号、销售商和价格

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);
#或者
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
#注:如果有多项最贵的物品( 例如每个的价格为19.95)，LIMIT解决方案仅仅显示其中一个！

任务：每项物品的的最高价格是多少？

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

任务：对每项物品，找出最贵价格的物品的经销商。

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

使用用户变量

例如，要找出价格最高或最低的物品的，其方法是：

mysql>select @min_price := min(price),@max_price := max(price);
mysql>select * from shop where price = @min_price or price = @max_price;

3.sql优化

1.数据类型及尺寸优化

MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引;

如把两个varchar类型的索引长度均设为20;

2.Analyze Table table_name:

MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。
我们可以使用SHOW INDEX语句来查看索引的散列程度
SHOW INDEX FROM PLAYERS;
mysql> show index from t_user_message\G;
*************************** 1. row ***************************
        Table: t_user_message
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 15
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec);

这个时候就可以使用Analyze Table语句修复索引: 
analyze table players;
再次show index from players;
*************************** 1. row ***************************
        Table: t_user_message
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 1000
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec);
#需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。

Optimize Table

经常更新数据的磁盘需要整理碎片，数据库也是这样，Optimize Table语句对MyISAM和InnoDB类型的表都有效。
如果表经常更新，就应当定期运行Optimize Table语句，保证效率。

与Analyze Table一样，Optimize Table也可以使用local来取消写入binlog。！

3.估计查询性能

在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。

在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。

上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB，(假设典型情况下索引缓存区填充率为2/3)，可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。

然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行。

注意，上述讨论并不意味着应用程序的性能将缓慢地以logN 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加 键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制 键高速缓冲区大小。

4.索引合并优化

4.1.索引合并交集

4.2.索引合并并集

4.3.索引合并排序并集

索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。

在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。

注释：索引合并优化算法具有以下几个已知缺陷：

` 如果可以对某些关键字进行范围扫描，则不考虑索引合并。例如，下面的查询：

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

对于该查询，可以有两个方案：
1.    使用(goodkey1 < 10 OR goodkey2 <  20)条件进行索引合并扫描。
2.    使用badkey < 30条件进行范围扫描。
然而，优化器只考虑第2个方案。如果这不是你想要的，你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行：

SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30

SELECT * FROM t1 IGNORE INDEX(badkey)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

5.order by索引优化

在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括：

·对不同的关键字使用ORDER BY：
·   SELECT * FROM t1 ORDER BY key1, key2；
·对关键字的非连续元素使用ORDER BY：
·       SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；
·混合ASC和DESC：
·       SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
·用于查询行的关键字与ORDER BY中所使用的不相同：
·       SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
·你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有 const联接类型的第1个表）。
·有不同的ORDER BY和GROUP BY表达式。
·使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此。
·使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此。

文件排序优化不仅用于记录排序关键字和行的位置，并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样：
1.读行匹配WHERE子句的行，如前面所示。
2.对于每个行，记录构成排序关键字和行位置的一系列值，并且记录查询需要的列。
3.根据排序关键字排序元组
4.按排序的顺序检索行，但直接从排序的元组读取需要的列，而不是再一次访问表。
                                            
如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略：
·增加sort_buffer_size变量的大小。
·增加read_rnd_buffer_size变量的大小。

默认情况下，MySQL排序所有GROUP BY col1，col2，...查询的方法如同在查询中指定ORDER BY col1，col2，...。如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。例如：
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL                                      

6.GROUP BY优化

6.1.松散索引扫面

6.2.紧凑索引扫描

7.如何避免表扫描

EXPLAIN的输出显示了当MySQL使用表扫描来解决查询时使用的所有类型列。这通常在如下条件下发生：
·表很小，扫描表比查找关键字速度快。这对于少于10行并且行较短的表比较普遍。
·在ON或WHERE子句中没有适用的索引列的约束。
·正用常量值比较索引列，并且MySQL已经计算到(基于索引树)常数覆盖了表的很大部分并且表扫描将会比较快。参见7.2.4节，“MySQL怎样优化WHERE子句”。
·你正通过另一个列使用一个低的集的势的关键字(许多行匹配关键字)。在这种情况下，MySQL假设通过使用关键字   它可能会进行许多关键字查找，表扫描将会更快。
  对于小表，表扫描通常合适。对于大表，尝试下面的技巧以避免优化器错选了表扫描：
·使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。参见13.5.2.1节，“ANALYZE TABLE语法”。
·对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。参见13.2.7节，“SELECT语法”。
·SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
·WHERE t1.col_name=t2.col_name；
·用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。参见5.3.3节，“服务器系统变量”。

4.存储过程

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)
当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的转义字符。 

下列是一个例子，一个采用参数的函数使用一个SQL函数执行一个操作，并返回结果： 

mysql> delimiter //
 
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储程序和所有存储函数的信息都被列出。
SHOW FUNCTION STATUS LIKE 'hello'\G

存储过程语法

1.CALL语句
2.BEGIN ... END复合语句;
3.DECLARE语句
4.存储程序中的变量
4.1.DECLARE局部变量:DECLARE var_name[,...] type [DEFAULT value];
4.2.变量SET语句:SET var_name = expr [, var_name = expr] ...
4.3.SELECT ... INTO语句:SELECT col_name[,...] INTO var_name[,...] table_expr
如:SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。
5.条件和处理程序
5.1. DECLARE条件
5.2. DECLARE处理程序
6.光标
6.1.声明光标:DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
6.2. 光标OPEN语句: OPEN cur1;
6.3. 光标FETCH语句:FETCH cur1 INTO a, b;
6.4. 光标CLOSE语句: CLOSE cur1;
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN cur1;
  OPEN cur2;
 
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
 
  CLOSE cur1;
  CLOSE cur2;
END
7.流程控制构造
7.1. IF语句
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

7.2. CASE语句
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
Or: 或者:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

7.3. LOOP语句
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

7.4. LEAVE语句
LEAVE label

7.5. ITERATE语句
ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。” 

7.6. REPEAT语句
[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

7.7. WHILE语句
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]