@cdmonkey 2014-11-02T13:40:34.000000Z 字数 9693 阅读 949

MySQL基础操作（二）

数据库

六、SQL结构化查询语言

1、什么是SQL

SQL（Structured Query Language），中文意思是“结构化查询语言”，它是一种对关系数据库中的数据进行定义和操作的语言，是大多数关系数据库管理系统所支持的工业标准。
结构化查询语言SQL是一种数据库查询及程序设计语言，用于存取数据以及查询、更新和管理关系型数据库。同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化的编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存取方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同于底层结构的，不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言的语句可以嵌套，这使得它具有极大的灵活性和强大的功能。不同的数据库系统的SQL语句会有一些差别。

2、SQL的分类

SQL结构化查询语言包含六个部分：

2.1 数据查询语言（DQL）

DQL（Data Query Language），也称为数据检索语言，用以从数据表中获取数据，确定数据怎样在应用程序给出。关键字SELETC是DQL（也是所有SQL）中用得最多的语句，其他的DQL常用的关键字有WHERE、ORDER BY，GROUP BY以及HAVING。这些DQL关键字经常与其他类型的SQL语句一起使用。例如：

mysql> select user,host,password from mysql.user order by user asc;
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| oldboy  | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| oldgirl | localhost | *2CADADD54086D5EB4C9F10E0430084D7F179885C |
| root    | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root    | 127.0.0.1 |                                           |
| wiki    | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
+---------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> select user,host,password from mysql.user order by user desc;
+---------+-----------+-------------------------------------------+
| user    | host      | password                                  |
+---------+-----------+-------------------------------------------+
| wiki    | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| root    | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| root    | 127.0.0.1 |                                           |
| oldgirl | localhost | *2CADADD54086D5EB4C9F10E0430084D7F179885C |
| oldboy  | localhost | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
+---------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
//注意：asc为升序排列，desc为倒序排列。

SELETE语句有六个子句：

子句	说明
SELECT	要获取哪几列（字段）的数据
FROM	从哪个数据表中获取数据，FROM子句总是只列出一个表。
WHERE	要获取哪几行（记录）的数据，也就是检索条件，检索符合条件的行。
GROUP BY
HAVING
ORDER BY	使用哪几列来对结果进行归类（排序）

我们使用SELETE语句的目的是从指定的数据表中获取我们需要的那部分数据，SELETE允许我们对表中的一些行和列进行检索。

2.2 数据操作语言（DML）

DML（Data Manipulation Language），其语句包括关键字INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除数据表中的行（记录），也称为动作查询语言。例如：

mysql> delete from mysql.user where user='oldboy';

注意：数据操作语言针对的是数据表中的数据，而不是数据库中的表。

测试

事务处理语言（TPL）

它的语句能够确保被DML语句影响的表中的所有行及时得到更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

数据控制语言（DCL）

DCL（Data Control Language），它的语句通过GRANT或REVOKE获得许可，确定单个用户或用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对数据表中指定列（字段）的访问。

数据定义语言（DDL）

DLL（Data Definition Language），其语句包括CREATE和DROP。在数据库中用来创建新表或者删除表，以及为表加入索引等。它也是动作查询的一部分。

所有的DDL语句的一个必须牢记的特色就是每一条DDL语句都包含了一个隐式提交的事务语句，因此只要这些命令执行，系统就会向数据库提交更改，所有具有访问数据库对象权限的用户马上就可以看到DDL语句的执行效果。

指针控制语言（CCL）

其语句包括DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT，用于对一个或多个表中单独行（记录）的操作。

小结：SQL语句中最为常见的就是以下三类

数据定义语言DDL：CREATE，ALERT，DROP

数据操作语言DML：SELETE，INSERT，DELETE，UPDATE

数据控制语言DCL：GRANT，REVOKE，COMMIT，ROLLBACK

七、MySQL常见管理应用

1、创建数据库

命令语法：CREATE DATABASE database_name;

mysql> create database oldboy;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oldboy             |
| performance_schema |
| wiki               |
+--------------------+
mysql> show create database oldboy\G
*************************** 1. row ***************************
       Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
//数据库字符集被自动设置为拉丁（即数据库默认的字符集为拉丁）。

创建一个名为oldboy_gbk的GBK字符集数据库：

mysql> create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
mysql> show create database oldboy_gbk;
+------------+--------------------------------------------------------------------+
| Database   | Create Database                                                    |
+------------+--------------------------------------------------------------------+
| oldboy_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
//该数据库的字符集为GBK字符集。

创建一个名为oldboy_utf8的UTF8字符集数据库：

mysql> create database oldboy_utf8 DEFAULT CHARACTER SET utf8  COLLATE utf8_general_ci;
mysql> show create database oldboy_utf8\G
*************************** 1. row ***************************
       Database: oldboy_utf8
Create Database: CREATE DATABASE `oldboy_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
//该数据库的字符集为GBK字符集。
//注意：“\G”后面不要加分号，否则会报错。

就是存储数据到表中的时候所用到的字符集，不同字符集编码格式不同，一般编写应用程序或者WEB应用操作数据库遇到的乱码就是字符集不同造成的，像MySQL,Oracle都会有字符集问题。

注意：如果你在编译安装时没有指定字符集，则默认的字符集就是拉丁。例如：

-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
//如果我们在使用cmake编译安装时，通过上面的参数指定了字符集，那么数据库的默认字符集就是UTF8，而不再是拉丁。

那么，问题来了，在实际的生产环境中应该怎样创建数据库？

根据开发的程序来确定所使用的字符集（使用UTF8的多一些）。

在编译的时候指定字符集，如上所示。然后在创建数据库时，直接创建即可，无需再指定字符集及校对规则。

如果在编译安装的时候没有指定字符集，或者是指定了和程序不同的字符集，则在创建数据库时就需要指定字符集及校对规则了。

当然，这里有个大前提，那就是安装的数据库系统要支持你所使用的字符集。所以在编译安装时，要把需要支持的字符集编译进去，即使用下面的参数：

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii

2、显示数据库

命令语法：SHOW DATABASES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oldboy_gbk         |
| oldboy_utf8        |
| performance_schema |
| wordpress          |
+--------------------+
10 rows in set (0.38 sec)
mysql> show databases like '%old%';    //这里的百分号%为通配符。
+--------------------+
| Database (%old%)   |
+--------------------+
| oldboy_gbk         |
| oldboy_utf8        |
+--------------------+
3 rows in set (0.03 sec)
//可以查看帮助，来了解详细的使用规则：
mysql> help show databases;
Name: 'SHOW DATABASES'
Description:
Syntax:
SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]
...

//显示当前的数据库：
mysql> use oldboy_gbk;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| oldboy_gbk |
+------------+

3、删除数据库

命令语法：DROP DATABASE database_name;

mysql> drop database oldboy_utf8;
Query OK, 0 rows affected (0.10 sec)
mysql> show databases like '%utf8';
Empty set (0.00 sec)     //指定的数据库已被删除。
//在DROP的帮助信息中提示，数据库被删除，在这个数据库上的用户权限不会自动删除。

4、连接数据库

命令语法：USE batabase_name;

这有些类似于Linux中的cd切换目录指令，而USE就是用来切换数据库的。

mysql> use wordpress;
Database changed
mysql> select database();   //查看当前连接的数据库。
+------------+
| database() |
+------------+
| wordpress  |
+------------+

5、查看当前的系统信息

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32     |
+------------+
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-11-02 01:23:10 |
+---------------------+
//以上是查看数据库版本、查看当前用户以及查看当前的日期和时间。

6、查看当前数据库中包含的表

命令语法：

mysql> use oldboy_gbk;
Database changed
mysql> show tables;
Empty set (0.00 sec)   //数据库是空的，没有数据表。
mysql> show tables in wordpress;    //查看其它数据库中的数据表。
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| ...                   |
+-----------------------+

八、删除系统中的多余用户

命令语法：DROP USER 'user'@'hostname'

mysql> drop user 'system'@'localhost';
mysql> drop user ''@'192.168.0.102';    //没有的部分就用两个单引号代替即可。
//如果使用DROP删除不了（一般是特殊字符或大写），可以用下面的方式进行删除：
mysql> delete from mysql.user where user='system' and host='localhost';
mysql> flush privileges;

使用drop user与delete from mysql.user删除用户的区别：

DELETE：属于DML语句，删除数据库中指定条件的数据。

DROP：属于DDL语句，作用是删除整个表、删除指定的用户、删除指定的存储空间等等。

DROP USER会将该用户的信息全部删掉，而DELETE只会清除user表中相应的记录。

九、创建MySQL用户及授权

权限管理包括对用户授权以及收回权限。

1、常用方法

1.1、查看GRANT的帮助信息

GRANT语句是添加新用户并授权他们访问MySQL对象的首选方法。

mysql> help grant
//省略部分帮助信息。
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

GRANT语句有三个基本的元素：权限、对象和用户。

1.2、运维人员的常用方法

运维人员比较常见的创建用户的方法是，使用GRANT语句在创建用户的同时进行授权，例如：

mysql> GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

1.3、先创建用户再授权

上述的帮助信息里还提供了一个先用CREATE命令创建用户，然后再用GRANT授权的方法，即创建用户和授权分开进行，例如：

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
//以上两条命令相当于下面一条命令：
mysql> GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

2、通过GRANT语句创建用户并授权

语法结构：

GRANT	ALL PRIVILEGES	ON	db_name.*	TO	username@localhost	IDENTIFIED BY 'passwd';
授权关键字	权限	ON	对象	TO	用户	用户密码

mysql> GRANT ALL PRIVILEGES ON test.* TO 'oldboy'@'localhost' IDENTIFIED BY 'oldboy123'; 
//创建oldboy用户对test库具备所有的权限，允许其从localhost以指定的密码登录。
mysql> flush privileges;
mysql> show grants for oldboy@'localhost' \G     //查看用户权限。
*************************** 1. row ***************************
Grants for oldboy@localhost: GRANT ALL PRIVILEGES ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515'
*************************** 2. row ***************************
Grants for oldboy@localhost: GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost'

3、配合使用CREATE和GRANT

//首先创建用户：
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwd';
//然后进行授权：
mysql> GRANT ALL ON db_name.* TO 'username'@'localhost';

首先使用CREATE语句创建用户：

mysql> create user 'oldgirl'@'localhost' identified by 'oldgirl123';
//先不进行授权，此时查看下该用户的权限。
mysql> show grants for oldgirl@'localhost'\G
*************************** 1. row ***************************
Grants for oldgirl@localhost: GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C'
//只有USAGE权限，也就是没有任何权限。
//我们使用oldgirl用户进行登录：
[root@MySQL-B ~]# mysql -uoldgirl -poldgirl123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
//看不到有效的数据库列表信息，因为没有权限。
mysql> create database oldboy;
ERROR 1044 (42000): Access denied for user 'oldgirl'@'localhost' to database 'oldboy'
//无法创建数据库，仍旧是因为没有权限。

然后使用GRANT语句进行授权：

mysql> grant all privileges on test.* to oldgirl@'localhost';
mysql> show grants for oldgirl@'localhost'\G                 
*************************** 1. row ***************************
Grants for oldgirl@localhost: GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C'
*************************** 2. row ***************************
Grants for oldgirl@localhost: GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost'
//可以看到，该用户有了ALL权限。
mysql> flush privileges;

4、对局域网内的用户进行授权

根据GRANT命令的语法，我们知道username@localhost的位置为授权访问数据库的用户及其所在主机，这里的localhost可以用远程主机的域名、IP地址或地址段代替，因此对局域网内的用户及主机进行授权可以通过如下方式：

//百分号匹配法：
mysql> grant all on *.* to test@'10.0.0.%' identified by 'test123';
//子网掩码匹配法：
mysql> grant all on *.* to test@'10.0.0.0/255.255.255.0' identified by 'test123';

mysql> create user test@'172.16.1.%' identified by 'test';
mysql> grant all on *.* to test@'172.16.1.%';
mysql> flush privileges;