关注微信公众号：CodingTechWork，一起学习进步。
引言
记录并整理常用的SQL语句使用笔记。
创建数据库和表
创建库(CREATE DATABASE)
CREATE DATABASE database_name
进入库(USE)
USE database_name
查看表(SHOW)
SHOW TABLES;SHOW TABLES FROM database_name;
创建表(CREATE TABLE)
CREATE TABLE table_name (column1_name INT, column2_name VARCHAR(50), column3_name VARCHAR(50));
mysql> CREATE TABLE students
-> (id INT,
-> name VARCHAR(32),
-> age INT,
-> birthday DATE,
-> class_id INT(11));
Query OK, 0 rows affected (0.34 sec)
mysql> CREATE TABLE class
-> (class_id INT AUTO_INCREMENT PRIMARY KEY,
-> class_name VARCHAR(32),
-> grade_id INT,
-> class_teacher VARCHAR(32));
Query OK, 0 rows affected (0.33 sec)描述表(DESC)
DESCRIBE table_name;
mysql> DESC students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> DESC class;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| class_id | int(11) | NO | PRI | NULL | auto_increment |
| class_name | varchar(32) | YES | | NULL | |
| grade_id | int(11) | YES | | NULL | |
| class_teacher | varchar(32) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)修改列(ALTER TABLE)
修改列(CHANGE COLUMN)
ALTER TABLE table_name CHANGE COLUMN old_col_name new_col_name INT AUTO_INCREMENT PRIMARY KEY
mysql> ALTER TABLE students
-> CHANGE COLUMN id id INT AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)增加列(ADD COLUMN)
ALTER TABLE table_name ADD COLUMN new_col1_name TEXT , ADD COLUMN new_col2_name VARCHAR(255), ... ...
mysql> ALTER TABLE students
-> ADD COLUMN student_id INT,
-> ADD COLUMN address VARCHAR(255);
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
| student_id | int(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)删除列
ALTER TABLE table_name DROP COLUMN col1_name, DROP COLUMN col2_name
mysql> ALTER TABLE students
-> DROP COLUMN age;
Query OK, 0 rows affected (3.73 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
| student_id | int(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)插入数据(INSERT INTO)
INSERT INTO table_name (col1_name, col2_name, col4_name) VALUES (col1_value, col2_value, col4_value);
mysql> INSERT INTO students
-> (name, birthday, address, class_id, student_id)
-> VALUES('xiaoming', '1996-07-01', '江苏省苏州市相城区', 3, 080301);
mysql> INSERT INTO students
-> (name, birthday, address, class_id, student_id)
-> VALUES('xiaohong', '1995-09-05', '安徽省马鞍山市', 3, 080310);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO students
-> (id, name, birthday, address, class_id, student_id)
-> VALUES(5, '秦明', '1996-03-03', '江苏省南京市', 2, 080205);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO students
-> (id, name, birthday, address, class_id, student_id)
-> VALUES(4, '黄盖', '1996-05-21', '江苏省无锡市', 1, 080102);
Query OK, 1 row affected (0.00 sec)查看最后记录(LAST_INSERT_ID())
SELECT LAST_INSERT_ID();，通过LAST_INSERT_ID()函数可以从表中取出刚输入的记录的标识码。
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)修改数据
修改某些列(UPDATE SET)
UPDATE table_name SET col1_name = 'new_value' WHRE col2_name = 'xxx';，若需要修改多列，则用逗号分开列表即可。
将三班的年纪号改为1，老师改为吴老师
mysql> INSERT INTO class VALUES(1, '一班', '1', '王老师');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO class VALUES(2, '二班', '1', '徐老师');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO class
-> (class_id, class_name, grade_id, class_teacher)
-> VALUES (3, '三班', 3, '陈老师');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM class;
+----------+------------+----------+---------------+
| class_id | class_name | grade_id | class_teacher |
+----------+------------+----------+---------------+
| 1 | 一班 | 1 | 王老师 |
| 2 | 二班 | 1 | 徐老师 |
| 3 | 三班 | 3 | 陈老师 |
+----------+------------+----------+---------------+
3 rows in set (0.00 sec)
mysql> UPDATE class SET grade_id = 1, class_teacher = '吴老师' WHERE class_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM class;
+----------+------------+----------+---------------+
| class_id | class_name | grade_id | class_teacher |
+----------+------------+----------+---------------+
| 1 | 一班 | 1 | 王老师 |
| 2 | 二班 | 1 | 徐老师 |
| 3 | 三班 | 1 | 吴老师 |
+----------+------------+----------+---------------+
3 rows in set (0.01 sec)替换数据
REPLACE INTO类似于INSERT INTO语句，但是该语句可以替换已存在的值，若某些值唯一之后。
- 使用
UNIQUE将字段置为唯一性
mysql> ALTER TABLE students
-> CHANGE COLUMN student_id student_id INT UNIQUE;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| class_id | int(11) | YES | | NULL | |
| student_id | int(11) | YES | UNI | NULL | |
| address | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)- 替换数据
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
+----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM students WHERE id = 1;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
+----+----------+------------+----------+------------+-----------------------------+
1 row in set (0.00 sec)
mysql> REPLACE INTO students
-> (name, birthday, class_id, student_id, address)
-> VALUES('小王', '1997-01-02', 3, 80303, '江苏省苏州市'),
-> ('xiaoming', '1996-07-01', 3, 80301, '江苏省苏州市相城区');
Query OK, 3 rows affected (0.05 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 6 | 小王 | 1997-01-02 | 3 | 80303 | 江苏省苏州市 |
| 7 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
+----+----------+------------+----------+------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> REPLACE INTO students
-> (id, name, birthday, class_id, student_id, address)
-> VALUES(1, 'xiaoming', '1996-07-01', 3, 80301, '江苏省苏州市相城区');
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 6 | 小王 | 1997-01-02 | 3 | 80303 | 江苏省苏州市 |
+----+----------+------------+----------+------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> REPLACE INTO students
-> (name, birthday, class_id, student_id, address)
-> VALUES('huangwen', '1995-02-04', 3, 80301, '江苏省盐城市');
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------+
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 6 | 小王 | 1997-01-02 | 3 | 80303 | 江苏省苏州市 |
| 8 | huangwen | 1995-02-04 | 3 | 80301 | 江苏省盐城市 |
+----+----------+------------+----------+------------+-----------------------+
5 rows in set (0.00 sec)删除数据(DELETE FROM)
子查询删除
mysql> SELECT * FROM class;
+----------+------------+----------+---------------+
| class_id | class_name | grade_id | class_teacher |
+----------+------------+----------+---------------+
| 1 | 一班 | 1 | 王老师 |
| 2 | 二班 | 1 | 徐老师 |
| 3 | 三班 | 1 | 吴老师 |
+----------+------------+----------+---------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------+
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 6 | 小王 | 1997-01-02 | 3 | 80303 | 江苏省苏州市 |
| 8 | huangwen | 1995-02-04 | 3 | 80301 | 江苏省盐城市 |
+----+----------+------------+----------+------------+-----------------------+
5 rows in set (0.00 sec)
mysql> DELETE FROM students
-> WHERE class_id =
-> (SELECT c.class_id FROM class c WHERE class_teacher = '徐老师');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------+
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 6 | 小王 | 1997-01-02 | 3 | 80303 | 江苏省苏州市 |
| 8 | huangwen | 1995-02-04 | 3 | 80301 | 江苏省盐城市 |
+----+----------+------------+----------+------------+-----------------------+
4 rows in set (0.00 sec)查询设置变量删除
mysql> SET @class_id =
-> (SELECT class_id FROM class WHERE class_teacher = '徐老师');
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM students
-> WHERE class_id = @class_id;
Query OK, 0 rows affected (0.00 sec)选择数据(SELECT)
全列查询(SELECT *)
SELECT * FROM table_name
mysql> SELECT * FROM students;
+----+----------+-----------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
| 3 | xiaohong |1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
+----+----------+------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)选择列查询(SELECT col)
SELECT col1_name, col4_name FROM table_name WHERE col3_name = 'valuexxx';
mysql> SELECT name, student_id FROM students WHERE class_id = 3;
+----------+------------+
| name | student_id |
+----------+------------+
| xiaoming | 80301 |
| xiaohong | 80310 |
+----------+------------+
2 rows in set (0.00 sec)表关联查询（JOIN USING）
JOIN ... USING，USING用于两个表相同列值相同查询。并使用CONCAT(str1, str2, ...)进行字符串拼接
mysql> SELECT name, birthday, address,
-> CONCAT(grade_id, '年级', class_name) AS class_info
-> FROM students
-> JOIN class USING(class_id)
-> WHERE class_teacher = '吴老师';
+----------+------------+-----------------------------+---------------+
| name | birthday | address | class_info |
+----------+------------+-----------------------------+---------------+
| xiaoming | 1996-07-01 | 江苏省苏州市相城区 | 1年级三班 |
| xiaohong | 1995-09-05 | 安徽省马鞍山市 | 1年级三班 |
+----------+------------+-----------------------------+---------------+
2 rows in set (0.00 sec)排序(ORDER BY)
升序
默认使用升序
mysql> SELECT * FROM students ORDER BY birthday;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
+----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)降序
使用DESC进行降序处理
mysql> SELECT * FROM students ORDER BY birthday DESC;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
| 5 | 秦明 | 1996-03-03 | 2 | 80205 | 江苏省南京市 |
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
+----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)限制(LIMIT)
限制个数
LIMIT n 限制n个展示
mysql> SELECT * FROM students LIMIT 2;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江苏省苏州市相城区 |
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省马鞍山市 |
+----+----------+------------+----------+------------+-----------------------------+
2 rows in set (0.01 sec)跳过限制个数
LIMIT(m, n)，跳过m个，展示后面n个。
mysql> SELECT * FROM students LIMIT 2, 1;
+----+--------+------------+----------+------------+--------------------+
| id | name | birthday | class_id | student_id | address |
+----+--------+------------+----------+------------+--------------------+
| 4 | 黄盖 | 1996-05-21 | 1 | 80102 | 江苏省无锡市 |
+----+--------+------------+----------+------------+--------------------+
1 row in set (0.00 sec)模糊查询
使用LIKE和%进行模糊匹配
mysql> SELECT `name`, birthday, address
-> FROM students
-> WHERE address LIKE '%江苏%';
+----------+------------+--------------------+
| name | birthday | address |
+----------+------------+--------------------+
| 黄盖 | 1996-05-21 | 江苏省无锡市 |
| 小王 | 1997-01-02 | 江苏省苏州市 |
| huangwen | 1995-02-04 | 江苏省盐城市 |
+----------+------------+--------------------+
3 rows in set (0.00 sec)交集并集查询
- 使用
AND进行交集查询
mysql> SELECT `name`, birthday, address
-> FROM students
-> WHERE address LIKE '%江苏%' AND class_id = 1;
+--------+------------+--------------------+
| name | birthday | address |
+--------+------------+--------------------+
| 黄盖 | 1996-05-21 | 江苏省无锡市 |
+--------+------------+--------------------+
1 row in set (0.00 sec)- 使用
OR进行并集查询
mysql> SELECT `name`, birthday, address
-> FROM students
-> WHERE `name` LIKE '%xiao%' OR `name` LIKE '%小%';
+----------+------------+-----------------------+
| name | birthday | address |
+----------+------------+-----------------------+
| xiaohong | 1995-09-05 | 安徽省马鞍山市 |
| 小王 | 1997-01-02 | 江苏省苏州市 |
+----------+------------+-----------------------+
2 rows in set (0.00 sec)分析处理数据
统计个数(COUNT)
COUNT(*)函数
mysql> SELECT COUNT(*)
-> FROM students
-> JOIN class USING (class_id)
-> WHERE class_name = '三班';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)求和函数(SUM)
SUM(col_name)
mysql> SELECT SUM(grade_id) AS 'grade_sum'
-> FROM class
-> JOIN students USING (class_id);
+-----------+
| grade_sum |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)日期处理
函数：月MONTHNAME(col_name), 日DAYOFMONTH(col_name)，年YEAR(col_name)
mysql> SELECT CONCAT(MONTHNAME(birthday), ' ',
-> DAYOFMONTH(birthday), ',',
-> YEAR(birthday)) AS student_birthday
-> FROM students
-> WHERE `name` = 'xiaoming';
+------------------+
| student_birthday |
+------------------+
| July 1,1996 |
+------------------+
1 row in set (0.00 sec)函数DATE_FORMAT(col_name, "%M %d, %Y")
mysql> SELECT DATE_FORMAT(birthday, "%M %d, %Y")
-> AS 'student_birth_date'
-> FROM students
-> WHERE `name` = 'xiaohong';
+--------------------+
| student_birth_date |
+--------------------+
| September 05, 1995 |
+--------------------+
1 row in set (0.00 sec)批量导入数据(LOAD DATA INFILE)
- mysql文件目录
mysql> SHOW VARIABLES LIKE '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)- 创建txt文件
[linux01@t1 /var/lib/mysql-files] touch sql_data.txt
[linux01@t1 /var/lib/mysql-files] vim sql_data.txt
[linux01@t1 /var/lib/mysql-files]# cat sql_data.txt
name | birthday | class_id | student_id |address|
xiaow| 1995-09-05|3|80310|anhui|
xiaoh| 1996-05-01|2|80209|zhejiang|- 导入
mysql> LOAD DATA INFILE '/var/lib/mysql-files/sql_data.txt'
-> REPLACE INTO TABLE students
-> FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES
-> (`name`, birthday, class_id, student_id, address);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM students;
+------+-------+------------+----------+------------+----------+
| id | name | birthday | class_id | student_id | address |
+------+-------+------------+----------+------------+----------+
| 1| xiaow | 1995-09-05 | 3 | 80310 | anhui |
| 2 | xiaoh | 1996-05-01 | 2 | 80209 | zhejiang |
+------+-------+------------+----------+------------+----------+
2 rows in set (0.00 sec)
