SQL语句

数据库语句

appledeMacBook-Pro:mysql apple$ cd
appledeMacBook-Pro:~ apple$ cd ~/.bash
.bash_history   .bash_profile   .bash_sessions/ .bashrc
appledeMacBook-Pro:~ apple$ cd ~/.bash
.bash_history   .bash_profile   .bash_sessions/ .bashrc
appledeMacBook-Pro:~ apple$ cd ~/.bash_profile
-bash: cd: /Users/apple/.bash_profile: Not a directory
appledeMacBook-Pro:~ apple$ vim ~/.bash_profile
appledeMacBook-Pro:~ apple$ source ~/.bash_profile
appledeMacBook-Pro:~ apple$ mysql   -u root  -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1. 创建、删除数据表

mysql> CREATE DATABASE Test
    -> CREATE DATABASE RUNOOB
    -> q
    -> mysqladmin -u root -p create RUNOOB
    -> use RUNOOB
    -> use RUNOOB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE RUNOOB
q
mysqladmin -u root -p create RUNOOB
use RUNOOB
use RUNO' at line 2

mysql> use RUNOOB;                                                                               Database changed

mysql> CREATE TABLE runoob_tbl(
    -> runoob_id INT NOT NULL AUTO_INCREMENT,
    -> runoob_title VARCHAR(100) NOT NULL,
    -> runoob_author VARCHAR(40) NOT NULL,
    -> submission_date DATE,
    -> PRIMARY KEY ( runoob_id )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> DROP TABLE runoob_tbl
    -> use RUNOOB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'use RUNOOB' at line 2

mysql> use RUNOOB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> DROP TABLE runoob_tbl
    -> show tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show tables' at line 2
mysql> show tables;
+------------------+
| Tables_in_runoob |
+------------------+
| runoob_tbl       |
+------------------+
1 row in set (0.00 sec)

2. 插入数据

mysql> create DATABASE RUNOOB;
ERROR 1007 (HY000): Can't create database 'RUNOOB'; database exists
mysql> INSERT INTO runoob_tbl
    -> (runoob_title,runoob_author,submission_date)
    -> VALUES
    -> ("学习PHP","菜鸟教程",NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT INTO runoob_tbl
    -> (runoob_title,runoob_author,submission_date)
    -> VALUES
    -> ("学习MySQL","菜鸟教程",NOW());                                                           
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO runoob_tbl
    -> (runoob_title,runoob_author,submission_date)
    -> VALUES
    -> ("Java 教程","RUNOOB.COM",'2019-03-30');
Query OK, 1 row affected (0.01 sec)

3. 查询数据
select * from runoob_tbl;
select runoob_title from runoob_tbl;

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习PHP      | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-30      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

 
mysql> select runoob_title from runoob_tbl;
+--------------+
| runoob_title |
+--------------+
| 学习PHP      |
| 学习MySQL    |
| Java 教程    |
+--------------+
3 rows in set (0.00 sec)

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

mysql> select * from runoob_tbl WHERE runoob_author = '菜鸟教程';
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习PHP      | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
2 rows in set (0.00 sec)

5. update

mysql> update runoob_tbl set submission_date = '2019-03-29' where runoob_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from runoob_tbl
    -> ;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习PHP      | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 C++     | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

6. 删除数据
delete from runoob_tbl where runoob_id = 4;

delete，drop，truncate 都有删除表的作用，区别在于：
1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

mysql> insert into runoob_tbl 
    -> (runoob_title,runoob_author,submission_date)
    -> values
    -> ("待删除","无",'2019-03-31');
Query OK, 1 row affected (0.01 sec)

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 C++     | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
|         4 | 待删除       | 无            | 2019-03-31      |
+-----------+--------------+---------------+-----------------+
4 rows in set (0.00 sec)

mysql> delete from runoob_tbl where runoob_id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 C++     | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

7. like

mysql> select * from runoob_tbl where runoob_author like '%教程';
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 C++     | 菜鸟教程      | 2019-03-30      |
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
+-----------+--------------+---------------+-----------------+
2 rows in set (0.00 sec)

8.UNION 语句

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
使用形式如下：
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

9 .order by 排序

mysql> select * from runoob_tbl order by submission_date;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         2 | 学习MySQL    | 菜鸟教程      | 2019-03-29      |
|         1 | 学习 C++     | 菜鸟教程      | 2019-03-30      |
|         3 | Java 教程    | RUNOOB.COM    | 2019-03-30      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

10.分组

1、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：
SELECT name FROM employee_tbl GROUP BY name;
返回的结果集就是所有员工的名字。
2、分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：
SELECT name ,sum(*)  FROM employee_tbl WHERE id<>1 GROUP BY name  HAVING sum(*)>5 ORDER BY sum(*) DESC;

11. 连接的使用
JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

mysql> CREATE TABLE tcount_tbl( runoob_author VARCHAR(40) NOT NULL, runoob_count INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tcount_tbl;
Empty set (0.00 sec)

mysql> INSERT INTO `tcount_tbl` VALUES('菜鸟教程','10'),('RUNOOB.COM','20'),('Google','20');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

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


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 |
+-----------+---------------+--------------+
3 rows in set (0.00 sec)

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

INNER JOIN.png

11.1 LEFT JOIN
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL LEFT JOIN 的应用
以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

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 |
+-----------+---------------+--------------+
3 rows in set (0.00 sec)

LEFT JOIN.png

11.2 RIGHT JOIN
MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT 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 |
|      NULL | NULL          |           20 |
+-----------+---------------+--------------+
4 rows in set (0.00 sec)

RIGHT JOIN.png

12. NULL 处理
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

mysql> create table runoob_test_tbl
    -> (runoob_author varchar(40) NOT NULL,runoob_count  INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20),('菜鸟教程', NULL),('Google', NULL),('FK', 20);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| 菜鸟教程      |         NULL |
| Google        |         NULL |
| FK            |           20 |
+---------------+--------------+
4 rows in set (0.00 sec)

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

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

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

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        |           20 |
| FK            |           20 |
+---------------+--------------+
2 rows in set (0.00 sec)

13. MySQL 正则表达
MySQL 正则表达

---

MySQL教程