Follow the wechat official account: CodingTechWork, learn and progress together.

The introduction

Take and organize notes on the use of common SQL statements.

Create databases and tables

CREATE DATABASE

CREATE DATABASE database_name

Enter the library (USE)

USE database_name

See the table (SHOW)

SHOW TABLES; SHOW TABLES FROM database_name;

CREATE TABLE 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)
Copy the code

Description table (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)
Copy the code

ALTER TABLE ALTER TABLE

Modify COLUMN (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)
Copy the code

ADD columns

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)
Copy the code

Delete the column

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)
Copy the code

INSERT INTO data

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'.Xiangcheng District, Suzhou City, Jiangsu Province.3.080301);
	
mysql> INSERT INTO students 
    -> (name, birthday, address, class_id, student_id)
    -> VALUES('xiaohong'.'1995-09-05'.'Maanshan city, Anhui Province'.3.080310);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO students
    -> (id.name, birthday, address, class_id, student_id)
    -> VALUES(5.'qin Ming'.'1996-03-03'.Nanjing, Jiangsu Province.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'.'Wuxi, Jiangsu Province'.1.080102);
Query OK, 1 row affected (0.00 sec)
Copy the code

View the last record (LAST_INSERT_ID())

SELECT LAST_INSERT_ID(); , the LAST_INSERT_ID() function retrieves the id of the newly entered record from the table.

 SELECT LAST_INSERT_ID(a); +------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)
Copy the code

Modify the data

Modify some columns (UPDATE SET)

UPDATE table_name SET col1_name = ‘new_value’ WHRE col2_name = ‘xxx’; If you need to modify multiple columns, separate the list with commas. Change the age number of Class 3 to 1 and the teacher to Miss Wu

mysql> INSERT INTO class VALUES(1.'one'.'1'.'Miss Wang');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO class VALUES(2.'second class'.'1'.'Miss Xu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO class
    -> (class_id, class_name, grade_id, class_teacher)
    -> VALUES (3.'3'.3.'Miss Chen'); Query OK, 1 row affected (0.01sec) mysql>SELECT * FROM class;
+----------+------------+----------+---------------+
| class_id | class_name | grade_id | class_teacher |
+----------+------------+----------+---------------+Wang | | | | class 1 | | 2 | 1 | | 2 class teacher xu | | 3 | 3 | 3 | teacher Chen | +----------+------------+----------+---------------+
3 rows in set (0.00 sec)

mysql> UPDATE class SET grade_id = 1, class_teacher = 'Miss Wu' 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 |
+----------+------------+----------+---------------+Wang | | | | class 1 | | 2 | 1 | | 2 class teacher xu | | 3 | 3 | 1 | | Mr Wu +----------+------------+----------+---------------+
3 rows in set (0.01 sec)

Copy the code

Replace the data

REPLACE INTO is similar to the INSERT INTO statement, but it can REPLACE existing values if some values are unique.

  1. useUNIQUEMake the field 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)
Copy the code
  1. Replace the data
mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou, jiangsu province | 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | The 1996-05-21 | 1 | 80102 | | in wuxi city, jiangsu province 5 | | qin Ming | 1996-03-03 | | 80205 | nanjing city of jiangsu province | +----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM students WHERE id = 1;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | + xiangcheng district of suzhou in jiangsu province----+----------+------------+----------+------------+-----------------------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO students
    -> (name, birthday, class_id, student_id, address)
    -> VALUES('wang'.'1997-01-02'.3.80303.'Suzhou, Jiangsu Province'), 
    -> ('xiaoming'.'1996-07-01'.3.80301.Xiangcheng District, Suzhou City, Jiangsu Province);
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 | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | 1996-05-21 | | 80102 | 1 | in wuxi city, jiangsu province 5 | | qin Ming | | 1996-03-03 Duringamilitary maneuver at | 2 | 80205 | | 1997-01-02 | | 6 wang | 3 | 80303 | jiangsu province suzhou city | | | 7 xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou in jiangsu province  +----+----------+------------+----------+------------+-----------------------------+
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.Xiangcheng District, Suzhou City, Jiangsu Province);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou, jiangsu province | 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | The 1996-05-21 | 1 | 80102 | | in wuxi city, jiangsu province 5 | | qin Ming 2 duringamilitary maneuver at | | 80205 | | 1996-03-03 | | 6 wang | | | 3 | 80303 | 1997-01-02 Suzhou city, jiangsu province | +----+----------+------------+----------+------------+-----------------------------+
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.Yancheng city, Jiangsu Province);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name     | birthday   | class_id | student_id | address               |
+----+----------+------------+----------+------------+-----------------------+| 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | 1996-05-21 | | 80102 | 1 | in wuxi city, jiangsu province 5 | | qin Ming | | 1996-03-03 Duringamilitary maneuver at | 2 | 80205 | | 1997-01-02 | | 6 wang | 3 | 80303 | jiangsu province suzhou city | | | 8 huangwen 80301 | | 1995-02-04 | 3 | | + in yancheng city in jiangsu province----+----------+------------+----------+------------+-----------------------+
5 rows in set (0.00 sec)
Copy the code

DELETE FROM data

Subquery deletion

mysql> SELECT * FROM class;
+----------+------------+----------+---------------+
| class_id | class_name | grade_id | class_teacher |
+----------+------------+----------+---------------+Wang | | | | class 1 | | 2 | 1 | | 2 class teacher xu | | 3 | 3 | 1 | | Mr Wu +----------+------------+----------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name     | birthday   | class_id | student_id | address               |
+----+----------+------------+----------+------------+-----------------------+| 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | 1996-05-21 | | 80102 | 1 | in wuxi city, jiangsu province 5 | | qin Ming | | 1996-03-03 Duringamilitary maneuver at | 2 | 80205 | | 1997-01-02 | | 6 wang | 3 | 80303 | jiangsu province suzhou city | | | 8 huangwen 80301 | | 1995-02-04 | 3 | | + in yancheng city in jiangsu province----+----------+------------+----------+------------+-----------------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM students
    -> WHERE class_id = 
    -> (SELECT c.class_id FROM class c WHERE class_teacher = 'Miss Xu');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM students;
+----+----------+------------+----------+------------+-----------------------+
| id | name     | birthday   | class_id | student_id | address               |
+----+----------+------------+----------+------------+-----------------------+| 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | 1996-05-21 | | 80102 | 1 | in wuxi city, jiangsu province wang | | 1997-01-02 | | 6 80303 | 3 | jiangsu province suzhou city | | | 8 huangwen 80301 | | 1995-02-04 | 3 | | + in yancheng city in jiangsu province----+----------+------------+----------+------------+-----------------------+
4 rows in set (0.00 sec)
Copy the code

Query setting variables are deleted

mysql> SET @class_id = 
    -> (SELECT class_id FROM class WHERE class_teacher = 'Miss Xu');
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)
Copy the code

SELECT data

Full column query (SELECT *)

SELECT * FROM table_name

mysql> SELECT * FROM students;
+----+----------+-----------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou, jiangsu province | 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 4 huang gai | The 1996-05-21 | 1 | 80102 | | in wuxi city, jiangsu province 5 | | qin Ming | 1996-03-03 | | 80205 | nanjing city of jiangsu province | +----+----------+------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)
Copy the code

SELECT col (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)
Copy the code

Table associative query (JOIN USING)

JOIN … USING, USING is used for querying two tables with the same column value. And use CONCAT(str1, STR2…) String concatenation

mysql> SELECT name, birthday, address, 
    -> CONCAT(grade_id, 'grade', class_name) AS class_info
    -> FROM students 
    -> JOIN class USING(class_id)
    -> WHERE class_teacher = 'Miss Wu';
+----------+------------+-----------------------------+---------------+
| name     | birthday   | address                     | class_info    |
+----------+------------+-----------------------------+---------------+| xiaoming | 1996-07-01 | | xiangcheng district of suzhou in jiangsu province class three, grade 1 | | xiaohong | | 1995-09-05 in anhui province maanshan class three, grade 1 | | +----------+------------+-----------------------------+---------------+
2 rows in set (0.00 sec)
Copy the code

Sorting (ORDER BY)

ascending

Use ascending order by default

mysql> SELECT * FROM students ORDER BY birthday;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | | | 5 qin Ming 2 duringamilitary maneuver at | | 80205 | | 1996-03-03 | | | 4 huang gai | | 1996-05-21 1 | 80102 | | in wuxi city, jiangsu province | 1 | xiaoming 80301 | | 1996-07-01 | 3 | | + xiangcheng district of suzhou in jiangsu province----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)
Copy the code

Descending order

Use DESC for descending processing

mysql> SELECT * FROM students ORDER BY birthday DESC;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou in jiangsu province 4 | | huang gai | 1996-05-21 | | 80102 | 1 | in wuxi city, jiangsu province 5 | | qin Ming | 1996-03-03 Duringamilitary maneuver at | | | 80205 | 2 | 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | +----+----------+------------+----------+------------+-----------------------------+
4 rows in set (0.00 sec)

Copy the code

LIMIT (LIMIT)

Limit the number of

LIMIT n Indicates the maximum number of displays

mysql> SELECT * FROM students LIMIT 2;
+----+----------+------------+----------+------------+-----------------------------+
| id | name     | birthday   | class_id | student_id | address                     |
+----+----------+------------+----------+------------+-----------------------------+| 1 | xiaoming 80301 | | 1996-07-01 | 3 | | xiangcheng district of suzhou in jiangsu province | 3 | xiaohong | | 3 | 80310 | 1995-09-05 in anhui province maanshan | +----+----------+------------+----------+------------+-----------------------------+
2 rows in set (0.01 sec)

Copy the code

Skip limit number

LIMIT(m, n), skip m, show the next n.

mysql> SELECT * FROM students LIMIT 2.1;
+----+--------+------------+----------+------------+--------------------+
| id | name   | birthday   | class_id | student_id | address            |
+----+--------+------------+----------+------------+--------------------+| | 4 huang gai 80102 | | 1996-05-21 | 1 | | + in wuxi city, jiangsu province----+--------+------------+----------+------------+--------------------+
1 row in set (0.00 sec)
Copy the code

Fuzzy query

Use LIKE and % for fuzzy matching

mysql> SELECT `name`, birthday, address
    -> FROM students
    -> WHERE address LIKE '% % in jiangsu province';
+----------+------------+--------------------+
| name     | birthday   | address            |
+----------+------------+--------------------+| huang gai | 1996-05-21 | | in wuxi city, jiangsu province wang | | 1997-01-02 | jiangsu province suzhou city | | huangwen | 1995-02-04 | | + in yancheng city in jiangsu province----------+------------+--------------------+
3 rows in set (0.00 sec)
Copy the code

Intersection union query

  1. useANDPerform intersection query
mysql> SELECT `name`, birthday, address
    -> FROM students 
    -> WHERE address LIKE '% % in jiangsu province' AND class_id = 1;
+--------+------------+--------------------+
| name   | birthday   | address            |
+--------+------------+--------------------+| huang gai | 1996-05-21 | | + in wuxi city, jiangsu province--------+------------+--------------------+
1 row in set (0.00 sec)
Copy the code
  1. useORPerform union query
mysql> SELECT `name`, birthday, address
    -> FROM students 
    -> WHERE `name` LIKE '%xiao%' OR `name` LIKE 'small % %';
+----------+------------+-----------------------+
| name     | birthday   | address               |
+----------+------------+-----------------------+| xiaohong | | 1995-09-05 in anhui province maanshan | | 1997-01-02 | | wang jiangsu province suzhou city | +----------+------------+-----------------------+
2 rows in set (0.00 sec)
Copy the code

Analyze and process data

Number of statistics (COUNT)

COUNT (*) function

mysql> SELECT COUNT(*) - >FROM students
    -> JOIN class USING (class_id)
    -> WHERE class_name = '3';
+----------+
| COUNT(*) |
+----------+| | + 2----------+
1 row in set (0.00 sec)
Copy the code

SUM function

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)
Copy the code

Date processing

Functions: 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 |
+------------------+| 1199 | 6 + out------------------+
1 row in set (0.00 sec)
Copy the code

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)
Copy the code

LOAD DATA INFILE in batches

  1. Mysql file directory
mysql> SHOW VARIABLES LIKE '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
Copy the code
  1. Create TXT file
[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|
Copy the code
  1. The import
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); B. Skipped over: 2 b. Skipped Skipped over: 2 b. Skipped Skipped over: 0 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)
Copy the code