關(guān)注WX：CodingTechWork，一起學(xué)習(xí)進(jìn)步。
引言
??記錄并整理常用的SQL語句使用筆記。
創(chuàng)建數(shù)據(jù)庫(kù)和表
創(chuàng)建庫(kù)(CREATE DATABASE)
CREATE DATABASE database_name
進(jìn)入庫(kù)(USE)
USE database_name
查看表(SHOW)
SHOW TABLES;
SHOW TABLES FROM database_name;
創(chuàng)建表(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)
插入數(shù)據(jù)(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', '江蘇省蘇州市相城區(qū)', 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()函數(shù)可以從表中取出剛輸入的記錄的標(biāo)識(shí)碼。
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
修改數(shù)據(jù)
修改某些列(UPDATE SET)
UPDATE table_name SET col1_name = 'new_value' WHRE col2_name = 'xxx';，若需要修改多列，則用逗號(hào)分開列表即可。
將三班的年紀(jì)號(hào)改為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)
替換數(shù)據(jù)
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)
- 替換數(shù)據(jù)
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江蘇省蘇州市相城區(qū) |
| 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 | 江蘇省蘇州市相城區(qū) |
+----+----------+------------+----------+------------+-----------------------------+
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, '江蘇省蘇州市相城區(qū)');
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 | 江蘇省蘇州市相城區(qū) |
+----+----------+------------+----------+------------+-----------------------------+
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, '江蘇省蘇州市相城區(qū)');
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 | 江蘇省蘇州市相城區(qū) |
| 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)
刪除數(shù)據(jù)(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)
查詢?cè)O(shè)置變量刪除
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)
選擇數(shù)據(jù)(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 | 江蘇省蘇州市相城區(qū) |
| 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)
表關(guān)聯(lián)查詢（JOIN USING）
JOIN ... USING，USING用于兩個(gè)表相同列值相同查詢。并使用CONCAT(str1, str2, ...)進(jìn)行字符串拼接
mysql> SELECT name, birthday, address,
-> CONCAT(grade_id, '年級(jí)', class_name) AS class_info
-> FROM students
-> JOIN class USING(class_id)
-> WHERE class_teacher = '吳老師';
+----------+------------+-----------------------------+---------------+
| name | birthday | address | class_info |
+----------+------------+-----------------------------+---------------+
| xiaoming | 1996-07-01 | 江蘇省蘇州市相城區(qū) | 1年級(jí)三班 |
| xiaohong | 1995-09-05 | 安徽省馬鞍山市 | 1年級(jí)三班 |
+----------+------------+-----------------------------+---------------+
2 rows in set (0.00 sec)
排序(ORDER BY)
升序
默認(rèn)使用升序
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 | 江蘇省蘇州市相城區(qū) |
+----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)
降序
使用DESC進(jìn)行降序處理
mysql> SELECT * FROM students ORDER BY birthday DESC;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江蘇省蘇州市相城區(qū) |
| 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)
限制個(gè)數(shù)
LIMIT n 限制n個(gè)展示
mysql> SELECT * FROM students LIMIT 2;
+----+----------+------------+----------+------------+-----------------------------+
| id | name | birthday | class_id | student_id | address |
+----+----------+------------+----------+------------+-----------------------------+
| 1 | xiaoming | 1996-07-01 | 3 | 80301 | 江蘇省蘇州市相城區(qū) |
| 3 | xiaohong | 1995-09-05 | 3 | 80310 | 安徽省馬鞍山市 |
+----+----------+------------+----------+------------+-----------------------------+
2 rows in set (0.01 sec)
跳過限制個(gè)數(shù)
LIMIT(m, n)，跳過m個(gè)，展示后面n個(gè)。
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和%進(jìn)行模糊匹配
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進(jìn)行交集查詢
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進(jìn)行并集查詢
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)
分析處理數(shù)據(jù)
統(tǒng)計(jì)個(gè)數(shù)(COUNT)
COUNT(*)函數(shù)
mysql> SELECT COUNT(*)
-> FROM students
-> JOIN class USING (class_id)
-> WHERE class_name = '三班';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
求和函數(shù)(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)
日期處理
函數(shù)：月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)
函數(shù)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)
批量導(dǎo)入數(shù)據(jù)
- 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)
- 創(chuàng)建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|
- 導(dǎo)入
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)
