MySQL

接上次MySQL,由于有些知识现在用不上,就先不学了,以后有需要再学

where语句

语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

操作符	描述
=	等号，检测两个值是否相等，如果相等返回true
<>, !=	不等于，检测两个值是否相等，如果不相等返回true
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

如:SELECT * from lz2y1 WHERE BINARY first_name='li';

update语句

语法

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

例子:

mysql> select *from lz2y1 ;
+---------+------------+-----------+
| lz2y_id | first_name | last_name |
+---------+------------+-----------+
|       1 | li         | yang      |
|       2 | ye         | qiang     |
+---------+------------+-----------+
2 rows in set (0.00 sec)

mysql> update lz2y1 set first_name="zhang",last_name="long" where lz2y_id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from lz2y1 ;
+---------+------------+-----------+
| lz2y_id | first_name | last_name |
+---------+------------+-----------+
|       1 | li         | yang      |
|       2 | zhang      | long      |
+---------+------------+-----------+
2 rows in set (0.00 sec)

delete语句

语法

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

例子:

select *from lz2y1;

like语句

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

语法

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

like 匹配/模糊匹配，会与 % 和 _ 结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

例子:

select *from lz2y1 where last_name like '%ng';

union操作符

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

语法

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

expression1, expression2, … expression_n**: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

例子:

mysql> select last_name from lz2y1
    -> union [all]
    -> select last_name from lz2y2;

排序

可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
你可以添加 WHERE…LIKE 子句来设置条件。

例子

select last_name , first_name from lz2y1 order by lz2y_id;

分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

function(column_name) 可用来计算数量,相加有数据的列(一定要记得逗号…)
GROUP BY column_name相当于把原来那个表根据这个column_name相同的放一起,形成一个新的表
如果这个时候select的列如果*或者其他的叠在一起的列,则会报错

例子

mysql> select first_name,count(first_name) as "*" from lz2y1 group by first_name;
+------------+---+
| first_name | * |
+------------+---+
| li         | 2 |
| zhang      | 2 |
| ye         | 1 |
+------------+---+
3 rows in set (0.00 sec)

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

例子:

mysql> select coalesce(first_name,'总数'),sum(lz2y_id) from lz2y1 group by first_name WITH ROLLUP;
+-------------------------------+--------------+
| coalesce(first_name,'总数')   | sum(lz2y_id) |
+-------------------------------+--------------+
| li                            |            6 |
| ye                            |            3 |
| zhang                         |            6 |
| 总数                           |           15 |
+-------------------------------+--------------+
4 rows in set (0.00 sec)

连接

可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

在这里我导入了菜鸟教程提供的数据库

mysql> select *from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
|         1 | 学习 PHP      | 菜鸟教程      | 2017-04-12      |
|         2 | 学习 MySQL    | 菜鸟教程      | 2017-04-12      |
|         3 | 学习 Java     | RUNOOB.COM    | 2015-05-01      |
|         4 | 学习 Python   | RUNOOB.COM    | 2016-03-06      |
|         5 | 学习 C        | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.00 sec)

mysql> select *from tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程      |           10 |
| RUNOOB.COM    |           20 |
| Google        |           22 |
+---------------+--------------+
3 rows in set (0.00 sec)

INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
+-----------+---------------+--------------+
4 rows in set (0.00 sec)

LEFT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鸟教程      |           10 |
|         2 | 菜鸟教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|         5 | FK            |         NULL |
+-----------+---------------+--------------+
5 rows in set (0.00 sec)

RIGHT JOIN同理

NULL值的处理

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

假设存在以下数据库

SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| 菜鸟教程      |         NULL |
| Google        |         NULL |
| FK            |           20 |
+---------------+--------------+

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
Empty set (0.00 sec)

可知= 和 != 运算符是不起作用的

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL，如下实例：

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set (0.01 sec)

正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配

语法

SELECT name FROM 数据表 WHERE 列 REGEXP 'XXX';

例子

查找name字段中以'st'为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

其他正则与php相同,代替XXX即可

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)

mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)

mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)

mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

Alter命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

删除，添加表字段

删除使用drop

ALTER TABLE 数据表 DROP 字段;

添加使用add

ALTER TABLE 数据表 add 字段 数据类型;

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 (两个都可完成任务)。

把字段 c 的类型从 CHAR(1) 改为 CHAR(10)

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

ALTER TABLE testalter_tbl CHANGE j j INT;

如果您想要更改列的类型而不是名称， CHANGE语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的

对 Null 值和默认值的影响

以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改表名

可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

 ALTER TABLE testalter_tbl RENAME TO alter_tbl;

前言