MySQL SQL操作

来源：互联网发布：iphone6splus精仿淘宝编辑：程序博客网时间：2024/05/16 04:44

一、数据库操作

创建数据库

格式：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [create_specification [, create_specification] ...]

create_specification:

    [DEFAULT] CHARACTER SET charset_name

  | [DEFAULT] COLLATE collation_name

例子：

CREATE DATABASE mydb;

删除数据库

格式：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

例子：

DROP DATABASE mydb;

修改数据库

格式：

ALTER {DATABASE | SCHEMA} [db_name]    alter_specification [, alter_specification] ...alter_specification:    [DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

例子：

使用数据库
格式：USE db_name
例子：
```
USE mydb;
```
显示所有数据库
格式：SHOW DATABASES;
例子：
```
USE mydb;
```
数据库改名
database不能改名.
phpMyAdmin似乎有这功能? 他是建新库,把所有表复制到新库,再删旧库完成。

二、表操作

创建表
格式：详细格式请参照手册
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(
```
col_name type 约束条件,
```
...)]
[{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
为该属性设置默认值

例子：
```
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 [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 属性名
- 修改字段 ALTER TABLE 表名 CHANGE 旧属性名新属性名新数据类型
查看所有表
格式：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，...，值 n)
同时插入多条记录
NSERT INTO 表名 [(属性列表)] VALUES(取值列表 1)，(取值列表 2)...，(取值列表 n)；
更新数据
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) 模糊匹配 like
% 通配任意字符 where goods_name like‘诺基亚%’
_ 通配单一字符 where goods_name like‘诺基亚___’
GROUP BY 分组与统计函数
一般情况下比较消耗资源，因为他在统计之前要先进行排序。

格式： 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;
```

五、连接查询

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

关于连接的方式，可以浏览http://blog.csdn.net/seanb/article/details/51594234

内连接 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。
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;

六、UNION合并查询结果

格式：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表达式的运算。

比较运算：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)

行子查询
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子查询
在SELECT语句的FROM子句中，子查询是合法的。实际的语法是：

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

八、索引

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

索引分类
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 索引名
查看索引
格式：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]

如果给定了OR REPLACE子句，该语句还能替换已有的视图
删除视图
格式：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；
修改视图
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）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

操作语句按照表的格式。

十、触发器

创建触发器
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承载；锁定没有被分开存储在存储器中。在这种情况下，行锁定在撤销中被释放。）在被命名的保存点之后设置的保存点被删除。

阅读全文

0 0

MySQL SQL操作

一、数据库操作

创建数据库

删除数据库

修改数据库

使用数据库

显示所有数据库

数据库改名

二、表操作

创建表

删除表

修改表

查看所有表

查看表结构

三、记录操作

给表的所有字段插入数据

给表的指定字段插入数据

同时插入多条记录

更新数据

删除数据

清空表

四、单表查询

查询所有字段

查询指定字段

WHERE子句

GROUP BY 分组与统计函数

having 帅选

order by 排序

limit 限制取出条目

五、连接查询

内连接 INNER JOIN tbl_name ON exp

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

交叉连接

六、UNION合并查询结果

七、子查询

where子查询

FROM子查询

八、索引

索引分类

创建索引

删除索引

查看索引

九、视图

创建视图

删除视图

修改视图

查看视图

更新视图

十、触发器

创建触发器

删除触发器

查看表结构

十一、事务