mysql

MYSQL

选择数据库

input：use ggy;
output：Database changed

必须使用USE打开数据库，才能读取其中的数据。

了解数据库和表

input：show databases;
output：
+--------------------+
| Database           |
+--------------------+
| ggy                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

input：show tables;
output：
+---------------+
| Tables_in_ggy |
+---------------+
| temp          |
| user          |
+---------------+
2 rows in set (0.00 sec)

input：show columns from temp;
output：
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int              | NO   | MUL | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     |         |                |
| age   | tinyint unsigned | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+

show columns要求给出一个表名（这个例子中的FROM temp），它对每个字段返回一行，行中包含字段名，数据类型，是否允许为NULL，键信息、默认值以及其他信息（如字段auto_increment）。

这个和desc一样的效果：

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int              | NO   | MUL | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     |         |                |
| age   | tinyint unsigned | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int              | NO   | MUL | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     |         |                |
| age   | tinyint unsigned | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

其他SHOW语句

SHOW STATUS; 用来显示广泛的服务器状态信息。

mysql> show create database ggy;显示创建数据库的SQL语句

mysql> show create table user;显示创建数据库的TABLE语句

show grants；用来显示授权用户（所有用户或特定用户）的安全权限
show errors 和 show warnings；用来显示服务器错误或警告信息。

检索数据

检索单个列

mysql> select sno from temp;
Empty set (0.00 sec)

检索多个列

mysql> select sno,sname from temp;
Empty set (0.00 sec)

检索所有列

mysql> select * from temp;
Empty set (0.00 sec)

一般除非你确实需要表中的每个列，否则最好不要用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低索引和应用程序的性能。

检索不同的行

mysql> select age from user;
+-----+
| age |
+-----+
|  20 |
|  20 |
+-----+
2 rows in set (0.00 sec)

检索出不同的值(distinct)

mysql> select distinct age from user;
+-----+
| age |
+-----+
|  20 |
+-----+
1 row in set (0.00 sec)
========================================================
mysql> select distinct age,name from user;
+-----+------+
| age | name |
+-----+------+
|  20 | 张飞 |
|  20 | 关羽 |
+-----+------+
2 rows in set (0.00 sec)

不能部分使用DISTINCT，如果给出两个列，除非指定的两个列都不同，否则所有列都将被检索处理。

限制结果（limit）

mysql> select name from user limit 1;
+------+
| name |
+------+
| 张飞 |
+------+
1 row in set (0.00 sec)

limit 1 指示MySql返回不多于1行。

mysql> select name from user limit 1,1;
+------+
| name |
+------+
| 关羽 |
+------+

mysql> select name from user limit 1,0;
Empty set (0.00 sec)

limit1，1指示MySql返回从行1开始的1列。第一个数为开始位置，第二个数为要检索的行数。即从x开始的第y行。

使用完全限定的表名

mysql> select user.name from ggy.user;
+------+
| name |
+------+
| 张飞 |
| 关羽 |
+------+
2 rows in set (0.00 sec)

排序检索数据

select语句的order by字句。

mysql> select user.name from user ;
+------+
| name |
+------+
| 张飞 |
| 关羽 |
+------+
2 rows in set (0.00 sec)

mysql> select user.name from user order by user.name;
+------+
| name |
+------+
| 关羽 |
| 张飞 |
+------+
2 rows in set (0.00 sec)

按多个列排序

mysql> select user.name, user.age from user order by  user.age, user.name;
+------+-----+
| name | age |
+------+-----+
| 关羽 |  20 |
| 张飞 |  20 |
| 刘备 |  30 |
+------+-----+
3 rows in set (0.00 sec)

仅在多个行具有相同的age时才对user按name排序。如果age列中的所有值都是唯一的，则不会按name排序。

指定排序方向

mysql> select user.name, user.age from user order by  user.age desc;
+------+-----+
| name | age |
+------+-----+
| 赵云 |  40 |
| 刘备 |  30 |
| 张飞 |  20 |
| 关羽 |  20 |
+------+-----+
4 rows in set (0.00 sec)

如果是多个列：

mysql> select user.name, user.age from user order by  user.age desc ,user.name;
+------+-----+
| name | age |
+------+-----+
| 赵云 |  40 |
| 刘备 |  30 |
| 关羽 |  20 |
| 张飞 |  20 |
+------+-----+
4 rows in set (0.00 sec)

desc只应用到直接位于其前面的列名，上例值对age指定desc，而name依旧按照升序排序。

与desc相反的是asc（默认）。

order by 和 limit组合

mysql> select * from user order by user.age desc limit 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  4 | 赵云 |  40 |
+----+------+-----+
1 row in set (0.00 sec)

在给出order by子句时，应该保证他位于from子句之后。如果使用LIMIT，他必须位于order by之后。使用子句的次序不对将产生错误消息。

过滤数据

使用where子句

mysql> select * from user where user.age = 20;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
+----+------+-----+
2 rows in set (0.00 sec)

where 子句操作符

操作符	说明
=	等于
<>	不等于
！=	不等于
< / <=	小于、小于等于
>/ >=	大于、大于等于
between and	在指定的两个值之间

检查单个值

mysql> select * from user where age = 20;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
+----+------+-----+
2 rows in set (0.00 sec)

使用where=“”语句，他返回的数值默认不区分大小写。

空值查询

mysql> select * from user where age is null;
Empty set (0.00 sec)

数据过滤

组和where子句

and操作符

mysql> select * from user where age < 40 and age > 20;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | 刘备 |  30 |
+----+------+-----+
1 row in set (0.00 sec)

or操作符

mysql> select * from user where age < 40 or age > 20;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
|  4 | 赵云 |  40 |
|  5 | 黄忠 |   0 |
+----+------+-----+
5 rows in set (0.00 sec)

计算次序

AND的优先级比OR高，所以要是用（）；

IN操作符

mysql> select * from user where age in (30,40);
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | 刘备 |  30 |
|  4 | 赵云 |  40 |
+----+------+-----+
2 rows in set (0.00 sec)

IN操作符的优点

1. IN操作符一般比OR操作符清单执行更快。
2. IN最大的有点是可以包含其他的SELECT语句，使得能够更动态地建立WHERE字句。

NOT操作符

mysql> select * from user where age not in (30,40);
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
|  5 | 黄忠 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

使用通配符进行过滤

like操作符

%通配符

mysql> select * from user where name like "张%";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
+----+------+-----+
1 row in set (0.00 sec)

%表示任意字符出现任意次数。

_通配符

mysql> select * from user where name like "张_";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
+----+------+-----+
1 row in set (0.00 sec)

_只匹配单个字符而不是多个字符。

使用通配符的技巧

1. 不要过度使用通配符。如果其他操作符能够达到相同的目的，应该使用其他操作符。
2. 在确实需要通配符时，除非绝对有必要，否则不要把他们用在搜索模式的开始出，搜索起来是最慢的。

用正则表达式进行搜索

基本字符匹配

mysql> select user.name from user where user.age regexp 20 order by user.name;
+------+
| name |
+------+
| 关羽 |
| 张飞 |
+------+
2 rows in set (0.00 sec)

它告诉MySql：REGEXP后跟的东西作为正则表达式处理。

mysql> select * from user where user.age regexp ".0" order by user.name;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
|  4 | 赵云 |  40 |
+----+------+-----+
4 rows in set (0.00 sec)


mysql> select * from user where user.age regexp "0" order by user.name;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
|  4 | 赵云 |  40 |
|  5 | 黄忠 |   0 |
+----+------+-----+
5 rows in set (0.00 sec)

这里使用了正则表达式.0。.是正则表达式中一个特殊字符，用来匹配任意一个字符。

如果被匹配的文本在列值中出现，like将不会找到他，相应的行也不会被返回，但是REGEXP在列值内进行匹配，如果被匹配的文字在列值中出现，REGEXP将会找到他，相应的行也会被返回。

匹配不区分大小写，为区分大小写，可使用BINARY关键字，如... REGEXP BINARY '.0'。

进行or匹配

mysql> select * from user where name regexp "关羽|刘备" order by name;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
+----+------+-----+
2 rows in set (0.00 sec)

|是正则表达式的OR操作符。他标识匹配其中之一，因此刘备，关羽都匹配返回。

匹配几个字符之一

mysql> select * from user where age regexp '[1234]0'order by name desc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  4 | 赵云 |  40 |
|  1 | 张飞 |  20 |
|  3 | 刘备 |  30 |
|  2 | 关羽 |  20 |
+----+------+-----+
4 rows in set (0.00 sec)

[]的有意思是匹配10,20,30,40。

mysql> select * from user where age regexp '1|2|3|4 0'order by name;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
+----+------+-----+
3 rows in set (0.00 sec)

这不是期望的输出，这样的意思是'1'或’2‘或’3‘ 或’4 0 ‘。除非把字符括在一个集合中，否则它将应用于整个串。

字符集合也可以被否定，即匹配除指定字符外的任何东西。[^1 2 3]

mysql> select * from user where age regexp '[^1 2 3]0'order by name;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  4 | 赵云 |  40 |
+----+------+-----+
1 row in set (0.00 sec)

匹配范围

[0123456789]可简化成[0-9]，也有[a-z]。

mysql> select * from user where user.age regexp "[1-3]0" order by user.age desc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
+----+------+-----+
3 rows in set (0.00 sec)

匹配特殊字符

例如\，.，%

mysql> select * from user where user.age regexp "\\." order by user.age desc;
Empty set (0.00 sec)

未匹配特殊字符，必须使用\\为前导。

匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1，}）
？	0个或1个匹配（等于{0,1}）
	指定数目的匹配
	不少于指定数目的匹配
	匹配数目的范围（m不超过225）

mysql> select * from user where user.age regexp "[:digit:]{1}" order by age desc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  4 | 赵云 |  40 |
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
|  5 | 黄忠 |   0 |
+----+------+-----+
5 rows in set (0.00 sec)

定位符

元符号	说明
^	文本的开始
$	文本的结束
[[:<:]]	词的开始
[[:>:]]	词的结束

[.0]和[^0]的区别

mysql> select * from user where user.age regexp ".0" order by age desc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  4 | 赵云 |  40 |
|  3 | 刘备 |  30 |
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
|  5 | 黄忠 |   0 |
+----+------+-----+
5 rows in set (0.00 sec)
=======================================================================
mysql> select * from user where user.age regexp "^0" order by age desc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  5 | 黄忠 |   0 |
+----+------+-----+
1 row in set (0.00 sec)

[.0]：包含0，[^0]限制开头问0；

^在集合中（用[]定义），他用来否定该集合，“”则指串的开始初。

计算字段

拼接(85页)

拼接（concatenate）：将值联合到一切构成单个值。可使用Concat（）函数来拼接两个列。

执行算数技术

mysql> select *, user.id*user.age as ageId from user order by ageId;
+----+------+-----+-------+
| id | name | age | ageId |
+----+------+-----+-------+
|  5 | 黄忠 |   0 |     0 |
|  1 | 张飞 |  20 |    20 |
|  2 | 关羽 |  20 |    40 |
|  3 | 刘备 |  30 |    90 |
|  4 | 赵云 |  40 |   160 |
+----+------+-----+-------+
5 rows in set (0.00 sec)

使用数据处理函数

文本处理函数

concat（）

mysql> select concat(age,name) as aname from user;
+--------+
| aname  |
+--------+
| 20张飞 |
| 20关羽 |
| 30刘备 |
| 40赵云 |
| 0黄忠  |
| 6tom   |
| 6jack  |
+--------+
7 rows in set (0.00 sec)

upper()

mysql> select user.name , Upper(user.name) from user;
+------+------------------+
| name | Upper(user.name) |
+------+------------------+
| 张飞 | 张飞             |
| 关羽 | 关羽             |
| 刘备 | 刘备             |
| 赵云 | 赵云             |
| 黄忠 | 黄忠             |
| tom  | TOM              |
| jack | JACK             |
+------+------------------+
7 rows in set (0.00 sec)

常见文本处理函数

函数	说明
left()	返回串左边的字符
length（）	返回串的长度
locate（）	找出串的一个子串
lower（）	将串转换为小写
ltrim（）	去掉串左边的空格
right（）	去掉串右边的字符
rtrim（）	去掉串右边的空格
soundex（）	返回串的SOUNDEX值
substring（）	返回子串的字符
upper（）	将串转换为大写

soundex是将一个任何文本串穿环卫描述其语音表示的字符数字模式的算法。

日期处理

数值处理函数

汇总数据

聚合函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

分组数据

grop by

mysql> select name,count(*) from user group by name;
+------+----------+
| name | count(*) |
+------+----------+
| 张飞 |        1 |
| 关羽 |        1 |
| 刘备 |        1 |
| 赵云 |        1 |
| 黄忠 |        1 |
| tom  |        1 |
| jack |        1 |
+------+----------+

过滤分组 having

mysql> select user.age,count(*) from user where count(*)>1 group by age;
ERROR 1111 (HY000): Invalid use of group function
mysql> select user.age,count(*) from user where count(*)>1 ;
ERROR 1111 (HY000): Invalid use of group function
mysql> select age,count(*) from user group by age having count(*) > 1;
+-----+----------+
| age | count(*) |
+-----+----------+
|  20 |        2 |
|   6 |        2 |
+-----+----------+
2 rows in set (0.00 sec)

having和where的差异

这里的where不起作用，是因为过滤是基于分组聚集值而不是特定行值的。

另一种理解：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，where排除的行不包括在分组中，这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

having和where连用

mysql> select user.age,count(*) from user where age > 10 group by age;
+-----+----------+
| age | count(*) |
+-----+----------+
|  20 |        2 |
|  30 |        1 |
|  40 |        1 |
+-----+----------+
3 rows in set (0.00 sec)

mysql> select user.age,count(*) from user where age > 10 group by age having count(*) > 1;
+-----+----------+
| age | count(*) |
+-----+----------+
|  20 |        2 |
+-----+----------+
1 row in set (0.00 sec)

select 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
	要检索的行数	否

使用子查询

利用子查询进行过滤

mysql> select * from user where age in(select age from user where age > 10);
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张飞 |  20 |
|  2 | 关羽 |  20 |
|  3 | 刘备 |  30 |
|  4 | 赵云 |  40 |
+----+------+-----+
4 rows in set (0.01 sec)

mysql> select *, (select name from user where name = "张飞") as newname from user order by age;
+----+------+-----+---------+
| id | name | age | newname |
+----+------+-----+---------+
|  5 | 黄忠 |   0 | 张飞    |
|  6 | tom  |   6 | 张飞    |
|  7 | jack |   6 | 张飞    |
|  1 | 张飞 |  20 | 张飞    |
|  2 | 关羽 |  20 | 张飞    |
|  3 | 刘备 |  30 | 张飞    |
|  4 | 赵云 |  40 | 张飞    |
+----+------+-----+---------+
7 rows in set (0.00 sec)

联结表

mysql> select user.name, user.age from user,temp where user.name = temp.name order by user.age asc;
+------+-----+
| name | age |
+------+-----+
| tom  |   6 |
| 张飞 |  20 |
| 刘备 |  30 |
| 赵云 |  40 |
+------+-----+
4 rows in set (0.00 sec)

内部联结

mysql> select user.name, temp.age from temp join user on user.name = temp.name;
+------+-----+
| name | age |
+------+-----+
| 张飞 |  30 |
| 刘备 |  30 |
| 赵云 |  30 |
| tom  |  30 |
+------+-----+
4 rows in set (0.00 sec)

mysql> select user.name, temp.age from temp join user on user.name = temp.name and user.age = temp.age;
+------+-----+
| name | age |
+------+-----+
| 刘备 |  30 |
+------+-----+
1 row in set (0.00 sec)

MySql在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的越多，性能下降越厉害。

创建高级联结

使用表别名

mysql> select u.name,t.age from user AS u,temp AS t where u.name = t.name and u.age = t.age;
+------+-----+
| name | age |
+------+-----+
| 刘备 |  30 |
+------+-----+
1 row in set (0.00 sec)

union

union从查询结果集中自动去除了重复的行。如果想返回所有的匹配行，可使用UNION ALL；

mysql> select name,user.age from user where age > 20 union select temp.name,temp.age from temp where temp.id > 0;
+------+-----+
| name | age |
+------+-----+
| 刘备 |  30 |
| 赵云 |  40 |
| 张飞 |  30 |
| 赵云 |  30 |
| tom  |  30 |
+------+-----+
5 rows in set (0.00 sec)

mysql> select name,user.age from user where age > 20 union all select temp.name,temp.age from temp where temp.id > 0;
+------+-----+
| name | age |
+------+-----+
| 刘备 |  30 |
| 赵云 |  40 |
| 刘备 |  30 |
| 张飞 |  30 |
| 赵云 |  30 |
| tom  |  30 |
+------+-----+
6 rows in set (0.00 sec)

对组合语句查询结果排序

select语句的输出用ORDER BY 子句排序。在UNION组合查询时，只能使用一条ORDER BY 子句，他必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

插入数据

插入完整的行

mysql> insert into temp values(10,"我",100);
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp (id,name,age) values(5,"谁",100);
Query OK, 1 row affected (0.02 sec)

插入多行

mysql> insert into temp (id,name,age) values(6,"华雄",100),(7,"关羽",200);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

插入检索出来的数据

mysql> insert into temp (name, age)  select temp.name, temp.age from temp where temp.id = 10;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

更新数据

mysql> update temp set age = 10 where name = "华雄";
Query OK, 1 row affected (0.01 sec)

UPDATE语句总是以要更新的表的名字开始。set命名用来将新值赋给被更新的列。

UPDATA语句以WHERE子句结束，他告诉MySql更新哪一行。没有where字句，mysql将会用更新表中的所有行。

更新多个列

mysql> update temp set age = 10, id = 9 where name = "华雄";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除数据

mysql> delete from temp where id = 11;
Query OK, 1 row affected (0.01 sec)

delete删除整行而不是删除列。为了删除指定的列，请使用update；

创建表

mysql> create table t (id int not null auto_increment, name varchar(10) not null,primary key (id))engine= innodb;
Query OK, 0 rows affected (0.09 sec)

如果表不存在的时候创建

mysql> create table if not exists t (id int not null auto_increment, name varchar(10) not null,primary key (id))engine= innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> alter table t add sno varchar(10) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t drop column sno;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

mysql> drop table t;

重命名表

mysql> rename table ta to t;
Query OK, 0 rows affected (0.06 sec)

视图

如果视图定义中有以下操作，则不能进行视图的更新：

分组

联结

子查询

并

聚集函数

distinct

导出列