SQL Data Import/Export and Table Operations Tutorial

Section 1 – Problem : Use SQL statements to import the /etc/passwd file into a MySQL database table userlist with a sequential record number, and then export the first 10 rows where UID is less than 100 to /dbbak/ulist.txt .

Solution – Create database and table :

mysql> CREATE DATABASE userdb; mysql> USE userdb;

Define the userlist table:

mysql> CREATE TABLE userlist( username VARCHAR(24) NOT NULL, password VARCHAR(48) DEFAULT 'x', uid INT(5) NOT NULL, gid INT(5) NOT NULL, fullname VARCHAR(48), homedir VARCHAR(64) NOT NULL, shell VARCHAR(24) NOT NULL );

Verify the structure:

mysql> DESC userlist;

(2) Import operation : Load the /etc/passwd file, using ':' as the field delimiter.

mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE userlist FIELDS TERMINATED BY ':';

Rows are separated by the default newline character, so LINES TERMINATED BY '\n' can be omitted.

(3) Verify import result :

mysql> SELECT COUNT(*) FROM userlist;

Show the first 10 records:

mysql> SELECT username, uid, gid, homedir, shell FROM userlist LIMIT 10;

Step 2 – Add automatic numbering :

Add an auto‑increment column sn as the first field.

mysql> ALTER TABLE userlist ADD sn INT(4) AUTO_INCREMENT PRIMARY KEY FIRST;

Verify:

mysql> SELECT sn, username, uid, gid, homedir FROM userlist LIMIT 10;

Step 3 – Export query results :

Ensure the target directory exists and MySQL has write permission:

# mkdir /dbbak # chown mysql /dbbak # ls -ld /dbbak/

Export the first 10 rows with UID < 100:

mysql> SELECT * FROM userdb.userlist WHERE uid < 100 INTO OUTFILE '/dbbak/ulist.txt' FIELDS TERMINATED BY ':';

Two common export failures are a missing directory or insufficient write permissions.

Step 4 – Common SQL queries and conditions (using table stu_info ) :

Count records: SELECT COUNT(*) FROM stu_info;

Aggregate functions: SELECT AVG(age), MAX(age), MIN(age) FROM stu_info;

Conditional count: SELECT COUNT(gender) FROM stu_info WHERE gender='boy';

Exact match: SELECT * FROM stu_info WHERE age=21;

Range query: SELECT * FROM stu_info WHERE age BETWEEN 20 AND 24;

Multiple conditions: SELECT * FROM stu_info WHERE age < 23 AND gender='girl';

Arithmetic expression: SELECT 1234+5678;

Pattern matching with LIKE: SELECT * FROM stu_info WHERE name LIKE 'J%';

Pattern with exact length: SELECT * FROM stu_info WHERE name LIKE 'J__';

Regular expression: SELECT * FROM stu_info WHERE name REGEXP '^J.*y$';

OR condition with REGEXP: SELECT * FROM stu_info WHERE name REGEXP '^J|y$';

Sorting: SELECT * FROM stu_info GROUP BY age ASC; (use DESC for descending).

Limit rows: SELECT * FROM stu_info LIMIT 3;

Group by gender: SELECT gender, COUNT(gender) FROM stu_info GROUP BY gender;

Alias columns: SELECT gender AS '性别', COUNT(gender) AS '人数' FROM stu_info GROUP BY gender;

Step 5 – Table record operations :

Delete all records: DELETE FROM stu_info;

Insert full rows:

INSERT INTO stu_info VALUES ('Jim','girl',24), ('Tom','boy',21), ('Lily','girl',20);

Insert with specific columns:

INSERT INTO stu_info(name, age) VALUES ('Jerry',27);

Update all rows: UPDATE stu_info SET age=10;

Update with condition: UPDATE stu_info SET age=20 WHERE gender='boy';

Delete with condition: DELETE FROM stu_info WHERE age < 18;

Delete without condition removes all rows.