MySQL技术内幕第二章（2）

MySQL技术内幕第二章（2）

五、数据库的选择、创建、删除和更改。

1.选择数据库

（1）USE db_name; --使用该数据库
（2）SELECT * FROM db_name.tbl_name; --表前面指定数据库

2.创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET charset] [COLLATE collation];

3.删除数据库

DROP DATABASE db_name;

4.更改数据库

ALTER DATABASE  db_name;

六、表的创建、删除、索引和更改

1.创建表

CREATE TABLE [IF NOT EXISTS] tbl_name ( ... ) [ENGINE=Innodb]; 

(Innodb是服务器默认的存储引擎。)

2.创建临时表

CREATE **TEMPORARY** TABLE tbl_name ...;[ENGINE=Innodb];

(临时表在会话结束时自动删除,但是最好用完临时表后就删除它，这样可以让服务器进来释放出资源。)

3.根据其他表或查询结果来创建表

（1）CREATE TABLE .... LIKE .... --只创建相同的表结构，插入数据可以用INSERT INTO tbl_name SELECT ...
（2）CREATE TABLE .... SELECT .... --建表且插入数据，表结构不一定一样

4.使用分区表（PARTITION BY子句）

分区表的好处（缩短检索时间）：

（1）表存储可以分布在多个设备上，这样可以通过I/O并行机制来缩短访问时间。
（2）优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区。

分区表的方法：

（1）根据范围来分区。 （2）根据列表来分区。 （3）根据散列来分区。

5.删除表：

(1)DROP TABLE IF EXISTS  tbl_name,tbl_name....; --删除实体表
(2)DROP TEMPORARY TABLE tbl_name; --删除临时表

6.索引表：创建索引的三种方法：

ALTER TABLE tbl_name ....； --修改表的时候添加索引
CREATE INDEX index_name ON tbl_name（index_columns）；--创建索引
CREATE TABLE tbl_name(......); --创建表的时候添加索引

删除索引的方法：

DROP INDEX index_name ON tbl_name; 
ALTER TABLE tbl_name DROP INDEX index_name;

7.更改表结构：

ALTER TABLE tbl_name action [, action] ....;

ALTER TABLE语句的某些功能
(1)更改列的数据类型。可以使用CHANGE子句和MODIFY子句，CHANGE字句还可以修改字段名。

ALTER TABLE tbl_name MODIFY ......;
ALTER TABLE tbl_name CHANGE ......; --可修改字段名

(2)让表改用另一种存储引擎。

ALTER TABLE tbl_name ENGINE=engine_name;

(3)重新命名表。

  ALTER TABLE tbl_name RENAME TO new_tbl_name;
  RENAME TABLE tbl_name TO new_tbl_name;

七、获取数据库元数据

（元数据即有关数据库的信息与它里面的各种对象）

1.使用SHOW语句获取元数据

SHOW DATABASES; --列出可以访问的数据库
SHOW CREATE DATABASE  db_name; --显示数据库的CREATE DATABASE语句
SHOW TABLES;  --列出默认数据库
SHOW TABLES FROM db_name; --查看给定数据库里的表
SHOW CREATE TABLE tbl_name; --显示表的CREATE TABLE
SHOW COLUMNS FROM tbl_name; --显示表里的列信息
SHOW INDEX FROM tbl_name; --显示表里的索引信息
SHOW TABLE STATUS; --显示默认数据库的表描述信息
SHOW TABLE STATUS FROM db_name; --显示给定数据库里的表描述信息
有些SHOW语句支持用LIKE子句和WHERE子句,如下：
SHOW COLUMNS FROM tbl_name LIKE ‘c%’;
SHOW COLUMNS FROM tbl_name WHERE KEY =’PRI’;

SHOW TABLES 无法显示TEMPORARY表。

2.借助INFORMATION_SCHEMA获取元数据

INFORMATION_SCHEMA库里面的表

3.从命令行获取元数据**

mysqlshow命令和mysqldump命令

八、使用连接实现多表检索。

SELECT语句的基本语法，如下所示：
SELECT select_list #所选择的列
FROM table_list #要查询的那些表
WHERE row_constraint #行必须满足的条件
GROUP BY grouping_columns # 结果如何分组
ORDER BY sorting_columns #结果如何排序
HAVING group_constraint #分组必须满足的条件
LIMIT count; #限制结果里的行数

1.内连接：

SELECT *FROM t1 JOIN t2 ON t1.i1=t2.i2;
（INNER JOIN、CROSS JOIN等价于JOIN）
SELECT * FROM t1 JOIN t2 USING(b); --使用USING函数

2.左（外）连接和右（外）连接

SELECT  *  FROM t1 LEFT JOIN t2 ON t1.id=t2.id; --左连接
SELECT  *  FROM t1 RIGHT JOIN t2 ON t1.id=t2.id; --右连接

九、使用子查询实现多表检索

子查询指的是用括号括起来的，并嵌入另一条语句里的那条SELECT语句。子查询可以引用和依赖外层查询里的值，子查询的性能不如连接查询。
子查询可用于：

（1）关系比较运算符。
（2）IN和NOT IN
（3）ALL、ANY和SOME子查询,这些运算符与某个关系比较运算符结 合在一起用。
（4）EXISTS 和NOT EXISTS，运算符EXISTS和NOT EXISTS只会 测试某个子查询是否返回了行。
（5）相关子查询，即引用了外层查询里的值，它也依赖于外层查询。
（6）FROM子句里的子查询，将子查询的结果集当作一个表。

十、使用UNION实现多表检索。

 SELECT .....  UNION  SELECT .....

UNION有以下几种特性：

（1）列名和数据类型。UNION结果集里的列名来自第一个SELECT里的列名。UNION里面的SELECT必须选取相同个数的列，但各相应列不需要具备相同列名和数据类型。
（2）重复行处理。在默认情况下，UNION会将结果集里的重复行剔除掉，不想剔除掉就用UNION ALL。
（3）ORDER BY和LIMIT处理。如果想将UNION结果作为一个整体进行处理，那么需要用括号把每一个SELECT语句括起来，并在语句最后加上ORDER BY和LIMIT。当然ORDER BY和LIMIT也可以用在被括号括起来的单个SELECT里。

十一、多表删除和更新

可以使用子查询和连接查询