一、数据库操作


创建数据库

格式：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name
  1. 例子： 
CREATE DATABASE mydb;

删除数据库

格式：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
  1. 例子： 
DROP DATABASE mydb;

修改数据库

格式：

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name
  1. 例子：

使用数据库

格式：USE db_name

  1. 例子： 
USE mydb;

显示所有数据库

格式：SHOW DATABASES;

  1. 例子： 
USE mydb;

数据库改名

database不能改名.

phpMyAdmin似乎有这功能? 他是建新库,把所有表复制到新库,再删旧库完成。

二、表操作


创建表

格式：详细格式请参照手册

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 [(col_name type 约束条件,
1.  ...)] 
   
 [{ENGINE|TYPE} = engine_name | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]] 
   
 或： 
   

 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
   
 [(] LIKE old_tbl_name [)];

约束条件

说明

PRIMARY KEY

标识该属性为该表的主键，可以唯一的标识对应的记录

FOREIGN KEY

标识该属性为该表的外键，与某表的主键关联

NOT NULL

标识该属性不能为空

UNIQUE

标识该属性的值是唯一的

AUTO_INCREMENT

标识该属性的值自动增加

DEFAULT

为该属性设置默认值




  1. 例子： 
CREATE TABLE IF NOT EXISTS t_book(
id INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
bookname CHAR(10) NOT NULL DEFAULT ""
)ENGINE="MYISAM" CHARSET="UTF8";

删除表

格式：

DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [, tbl_name] ...


例子：

DROP TABLE IF EXISTS t_book;

修改表

格式：

  • 修改字段 ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型 


ALTER [IGNORE] TABLE tbl_name 
   
 alter_specification [, alter_specification] ... 
   

 alter_specification: 
   
 ADD [COLUMN] column_definition [FIRST | AFTER col_name ] 
   
 | ADD [COLUMN] (column_definition,...) 
   
 | ADD INDEX [index_name] [index_type] (index_col_name,...) 
   
 | ADD [CONSTRAINT [symbol]] 
   
 PRIMARY KEY [index_type] (index_col_name,...) 
   
 | ADD [CONSTRAINT [symbol]] 
   
 UNIQUE [index_name] [index_type] (index_col_name,...) 
   
 | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) 
   
 | ADD [CONSTRAINT [symbol]] 
   
 FOREIGN KEY [index_name] (index_col_name,...) 
   
 [reference_definition] 
   
 | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
   
 | CHANGE [COLUMN] old_col_name column_definition 
   
 [FIRST|AFTER col_name] 
   
 | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] 
   
 | DROP [COLUMN] col_name 
   
 | DROP PRIMARY KEY 
   
 | DROP INDEX index_name 
   
 | DROP FOREIGN KEY fk_symbol 
   
 | DISABLE KEYS 
   
 | ENABLE KEYS 
   
 | RENAME [TO] new_tbl_name 
   
 | ORDER BY col_name 
   
 | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] 
   
 | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] 
   
 | DISCARD TABLESPACE 
   
 | IMPORT TABLESPACE 
   
 | table_options 
   
 | partition_options 
   
 | ADD PARTITION partition_definition 
   
 | DROP PARTITION partition_names 
   
 | COALESCE PARTITION number 
   
 | REORGANIZE PARTITION partition_names INTO (partition_definitions) 
   
 | ANALYZE PARTITION partition_names 
   
 | CHECK PARTITION partition_names 
   
 | OPTIMIZE PARTITION partition_names 
   
 | REBUILD PARTITION partition_names 
   
 | REPAIR PARTITION partition_names 
   
• • 修改表名: ALTER TABLE old_tbl_name RENAME [TO] new_tbl_name 新表名 ；或者 
RENAME TABLE tbl_name TO new_tbl_name
•  
    [, tbl_name2 TO new_tbl_name2] ...
•  

• • 增加字段 ALTER TABLE 表名 ADD 属性名 1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名 2] 

• • 删除字段 ALTER TABLE 表名 DROP 属性名

查看所有表

格式：SHOW TABLES;
例子：

SHOW TABLES;

查看表结构

1、查看基本表结构： DESCRIBE(DESC) 表名；

2，查看表详细结构： SHOW CREATE TABLE 表名；

3. 查看表信息 SHOW TABLE STATUS

show table status  \G 列显示

show table status where name = ‘表名’; 只显示指定的表信息


三、记录操作


给表的所有字段插入数据

格式：INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n)；

给表的指定字段插入数据

格式：INSERT  INTO 表名(属性 1，属性 2，...，属性 n)  VALUES(值 1，值 2，值 3，...，值


同时插入多条记录

NSERT INTO 表名 [(属性列表)] VALUES(取值列表 1)，(取值列表 2)...，(取值列表 n)；


更新数据

  1. UPDATE 表名

    SET 属性名 1=取值 1，属性名 2=取值 2，

    ...，

    属性名 n=取值 n WHERE 条件表达式；

删除数据

DELETE FROM 表名 [WHERE 条件表达式]


清空表

格式：TRUNCATE [TABLE] tbl_name
相当于删除指定表之后在按照原来的格式新建一张表；
TRUNCATE t_book;



四、单表查询

select的模型，列是变量可以尽心运算，where后面是表达式，在每一行都会判断真假。

select有5中子句，

  • where条件查询
  • group by 分组
  • having 帅选
  • order by 排序
  • limit 限制结果条数

where是针对磁盘的数据文件，having是针对内存的结果集

五种子句可以选择其中那个的一种或者几种，或者我都不选。但是如果使用了就必须按照上面的先后顺序出现


格式： SELECT [DISTINCT] [*|字段1,字段2,...] FROM tbl_name

WHERE where_definition


查询所有字段

格式：SELECT * FROM tlb_name;
例子：

SELECT * FROM t_book;

查询指定字段

格式：s
例子：

USE mydb;

WHERE子句

expression为真,则该行取出


where表达式的运算符:

1) 比较运算符

运算符

说明

例子

<

小于

where age<10

<=

小于等于

where age<=10

=

等于

where age=10

!= 或 <>

不等于

where age!=10

>=

大于等于

where age>=10

>

大于

where age>10

in

在某集合内

where age in (8,9,10)

not in

不在某集合内

where age not in (8,9,10)

between

在某范围内

where age between 8 and 10

2) 逻辑运算符

运算符

说明

NOt 或 !

逻辑非

OR 或 ||

逻辑或

AND 或 &&

逻辑与

AND的优先级大于OR

3) 模糊匹配

% 通配任意字符  where goods_name like ‘诺基亚%’

_ 通配单一字符  where goods_name like ‘诺基亚___’


GROUP BY 分组与统计函数

  1. 一般情况下比较消耗资源，因为他在统计之前要先进行排序。

    格式： GROUP BY {col_name | expr | position}
    [ASC | DESC], ... [WITH ROLLUP]

    统计函数
    max()
    min()
    count()    count(*)行数
    avg()
    sum()

    与WITH ROLLUP 一起使用(最后加入一个总和行)；

having 帅选

对内存的结果集进行条件判断。


order by 排序

格式： ORDER BY {col_name | expr | position}
[ASC | DESC] , ...


limit 限制取出条目

格式：LIMIT {[offset,] row_count | row_count OFFSET offset}
例子：

USE mydb;

五、连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据；

关于连接的方式，可以浏览

内连接 INNER JOIN tbl_name ON exp

内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。

下面的语句1和语句2的结果是相同的。

语句1：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
 FROM CUSTOMERS C,ORDERS O
 WHERE C.ID=O.CUSTOMER_ID;

 语句2：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
 SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
 FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;

外连接 LEFT | RIGHT | FULL [OUTER] JOIN tlb_name ON e

外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

MySQL是不支持全外的连接的


交叉连接

交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。

例如：下面的语句1和语句2的结果是相同的。
语句1：隐式的交叉连接，没有CROSS JOIN。

六、UNION合并查询结果

SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
 FROM ORDERS O , CUSTOMERS C
 WHERE O.ID=1;

 语句2：显式的交叉连接，使用CROSS JOIN。
 SELECT O.ID,O.ORDER_NUMBER,C.ID,
 C.NAME
 FROM ORDERS O CROSS JOIN CUSTOMERS C
 WHERE O.ID=1;
格式：SELECT ...
 UNION [ALL | DISTINCT]
 SELECT ...
 [UNION [ALL | DISTINCT]
 SELECT ...]


把2条或者多条的SQL的查询结果合并成1个结果集。比如SQL1取得N行记录，SQL2取得M行记录，SQL1 union SQL2就可以取得N+M（不是绝对的）行记录。

使用场景1，可以在where 条件比较复杂的时候，可以分为两条语句。
使用场景，从两张表中查询（在正式用户和还没有激活的用户表中查询）

union的语句必须满足一个条件，可查询语句查询的列数必须相同。列名称未必要一致，第一查询语句的列名称为准。
完全相等的行将会被合并，合并比较耗时，一般实用“union all”不让union进行合并。
union的子句中不用写order by。没有意义。可以在合并之后的结果进行order by。

七、子查询

我们可以说子查询嵌套在外部查询中。实际上，子查询也可以嵌套在其它子查询中，嵌套程度可以很深。子查询必须要位于圆括号中。
有一个限定是，一个子查询的外部语句必须是以下语句之一：SELECT, INSERT, UPDATE, DELETE, SET或DO。还有一个限定是，目前，您不能在一个子查询中修改一个表，又在同一个表中选择。这适用于DELETE, INSERT, REPLACE和UPDATE语句。

where子查询

内层select的结果参加了外层select的where表达式的运算。

  1. 比较运算：non_subquery_operand comparison_operator (subquery)                       comparison_operator是以下操作符之一：= > < >= <= <> ANY, IN和SOME 处理子查询返回集合 
operand comparison_operator ANY (subquery) 
   
 operand [NOT] IN (subquery) 
   
 operand comparison_operator SOME (subquery) 
   
 operand comparison_operator ALL (subquery)
  1. 行子查询 
SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2); 
   
 SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
EXISTS和NOT EXISTS  是判断子查询是否有数据 
   
 如果一个子查询返回任何的行，则EXISTS subquery为FALSE。例如： 
   
 SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

FROM子查询

  1. 在SELECT语句的FROM子句中，子查询是合法的。实际的语法是：

    SELECT ... FROM (subquery) [AS] name ...

八、索引

索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度；类似于图书的目录，方便快速定位，寻找指定的内容。

索引分类

  1. 1)、普通索引（index/key） 这类索引可以创建在任何数据类型中；

    2)、唯一性索引（unique） 使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；

    3)、主键索引(primary key)

    4)、全文索引(fulltext) 使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

    5)、单列索引 在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，也可以是主键索引，还可以是全文索引；

    6)、多列索引 多列索引是在表的多个字段上创建一个索引；

    7)、空间索引 使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空

创建索引

建表时创建索引：

在要创建索引的列的约束条件后面跟上下面的：

CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
 KEY [index_name] [index_type] (index_col_name,...)
 INDEX [index_name] [index_type] (index_col_name,...)
 CONSTRAINT [symbol]] UNIQUE [INDEX]
 [index_name] [index_type] (index_col_name,...)
 [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)

 index_col_name: col_name [(length)] [ASC | DESC]




在已经存在的表上创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [USING index_type]
 ON tbl_name (index_col_name,...)

 index_col_name:
 col_name [(length)] [ASC | DESC]

 或者
 alter table add index (列名);
 alter table add unique (列名);
 alter table add primary key(列名);
 alter table add fulltext (列名);


删除索引

DROP INDEX index_name ON tbl_name
 或者
 删除主键:
 alter table drop primary key
 删除其他索引:
 alter table drop index 索引名

查看索引

  1. 格式：show index from tableName

九、视图

1，视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。

2，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

3，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

创建视图

格式：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
 VIEW view_name [(column_list)]
 AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]1.  如果给定了

OR REPLACE子句，该语句还能替换已有的视图

删除视图

格式：
 DROP VIEW [IF EXISTS]
 view_name [, view_name] ...
 [RESTRICT | CASCADE]
  1. 删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；

修改视图

1 ）CREATE OR REPLACE VIEW 语句修改视图 

 CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }] 

 VIEW 视图名 [( 属性清单 )] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]； 

 2） ALTER 语句修改视图

 ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]

 VIEW 视图名 [( 属性清单 )] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；格式：

查看视图

1) DESCRIBE 语句查看视图基本信息

2) SHOW TABLE STATUS 语句查看视图基本信息

3) SHOW CREATE VIEW 语句查看视图详细信息

4) 在 views 表中查看视图详细信息格式：


更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

  1. 操作语句按照表的格式。

十、触发器


创建触发器

CREATE TRIGGER trigger_name 
 BEFORE | AFTER
 trigger_event
 ON tbl_name FOR EACH ROW 
 trigger_stmt



trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：

· INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

· UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

· DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。

使用别名OLD和NEW，能够引用与触发程序相关的表中的列。OLD.col_name在更新或删除它之前，引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。

创建触发器之间使用DELIMITER 将分割改为别的，创建之后，再修改回来。


删除触发器

格式：DROP TRIGGER [schema_name.]trigger_name


查看表结构

1 SHOW TRIGGERS 语句查看触发器信息

2 在 triggers 表中查看触发器信息

SHOW TRIGGERS LIKE 'acc%';

十一、事务

MySQL通过SET AUTOCOMMIT, START TRANSACTION, COMMIT和ROLLBACK等语句支持本地事务（在给定的客户端连接中).

START TRANSACTION | BEGIN [WORK]
 COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 SET AUTOCOMMIT = {0 | 1}
 START TRANSACTION或BEGIN语句可以开始一项新的事务。COMMIT可以提交当前事务，是变更成为永久变更。ROLLBACK可以 回滚当前事务，取消其变更。SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。


 SAVEPOINT和ROLLBACK TO SAVEPOINT语法
 SAVEPOINT identifier
 ROLLBACK [WORK] TO SAVEPOINT identifier
 RELEASE SAVEPOINT identifier
 InnoDB支持SQL语句SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT和自选的用于ROLLBACK的WORK关键词。



SAVEPOINT语句用于设置一个事务保存点，带一个标识符名称。如果当前事务有一个同样名称的保存点，则旧的保存点被删除，新的保存点被设置。

ROLLBACK TO SAVEPOINT语句会向以命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对行进行了更改，则这些更改会在 回滚中被撤销。但是，InnoDB不会释放被存储在保存点之后的存储器中的行锁定。（注意，对于新插入的行，锁定信息被存储在行中的事务ID承载；锁定没有被分开存储在存储器中。在这种情况下，行锁定在撤销中被释放。）在被命名的保存点之后设置的保存点被删除。