MySQL Storage engine SQL data Import / Export Operation Table record query and matching conditions 01/28 Update SLTechnology News&Howtos

MySQL Storage engine SQL data Import / Export Operation Table record query and matching conditions

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Configuration of MySQL storage engine SQL data import / export operation table record query and matching criteria

1 configuration of the MySQL storage engine

1.1 question

This case requires the use of the MySQL data storage engine to complete the following tasks:

Available storage engine types view the storage engine for the default storage type change table

1.2 steps

To implement this case, you need to follow these steps.

Step 1: view storage engine information

Use the mysql command to connect and log in as root user:

[root@dbsvr1] # mysql-u root-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 9Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Execute the SHOW ENGINES\ G instruction to view the list. There are 9 storage engines available for MySQL 5.6 (except for the last FEDERATED, the other 8 are supported), and the default storage engine is InnoDB:

Mysql > SHOW ENGINES\ gateway * 1. Row * * Engine: InnoDB Support: DEFAULT / / this storage engine is the default Comment: Supports transactions, row-level locking And foreign keysTransactions: YES XA: YES Savepoints: YES** 2. Row * * Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO* * 3. Row * * Engine: MEMORY Support: YES Comment: Hash based Stored in memory Useful for temporary tablesTransactions: NO XA: NO Savepoints: NO** 4. Row * * Engine: BLACKHOLE Support: YES Comment: / dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO**** * * 5. Row * * Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO** 6. Row * * Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO** 7. Row * * Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO** 8. Row * * Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO* * 9. Row * * Engine: FEDERATED Support: NO / / this engine is not supported Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL9 rows in set (0.01 sec)

Or directly check the value of the system variable default_storage_engine, or you can confirm that the default storage engine is InnoDB:

Step 2: modify the default storage engine

In the mysql > environment, you can change the default storage engine directly through the SET instruction (valid only during this connection session, exit reentry is invalid). For example, if you temporarily change it to MyISAM, you can do the following:

If you want to directly modify the default storage engine used by the MySQL service program, you should write the relevant settings to the configuration file / etc/my.cnf and take effect after restarting the service. For example:

[root@dbsvr1 ~] # vim / etc/ my.cnf [mysqld].. .. default_storage_engine=MEMORY / / switch to MEMORY engine [root@dbsvr1 ~] # systemctl restart mysqld.service / / restart the service

[root@dbsvr1] # mysql-u root-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > SHOW VARIABLES LIKE 'default_storage_engine' +-+-+ | Variable_name | Value | +-+-+ | default_storage_engine | MEMORY | / / default engine modified +- -+-+ 1 row in set (0.00 sec) mysql > exitBye

In order to avoid obstacles in subsequent experiments, remember to restore the original state after testing-remove the default engine settings, or change it to InnoDB:

[root@dbsvr1 ~] # vim / etc/ my.cnf [mysqld].. .. default_storage_engine= InnoDB [root @ dbsvr1 ~] # systemctl restart mysqld.service

Confirm the recovery result (option-e returns to the Shell command line after invoking the specified SQL operation):

2 SQL data import / export

2.1 question

Use SQL statements to complete the following export and import operations:

Import the / etc/passwd file into the userdb library userlist table and number each record and export the first 10 records in the userdb library userlist table with UID less than 100. save as / dbak/ulist.txt file

2.2 steps

To implement this case, you need to follow these steps.

Step 1: import the / etc/passwd file into the MySQL database

The structure of the imported table depends on the / etc/passwd configuration file. If you can't remember the meaning of each field for a while, you can also check the man man page of the passwd configuration file for instructions related to the format description, such as:

[root@dbsvr1 ~] # man 5 passwd.. .. Each line of the file describes a single user, and contains seven colon-sep- arated fields: name:password:UID:GID:GECOS:directory:shell / / the order and general use of each field The field are as follows: / / the following detailed explanation of the role of each field name This is the user's login name. It should not contain capital letters. Password This is either the encrypted user password, an asterisk (*), or the letter'xtrees. (See pwconv (8) for an explanation of'x.) UID The privileged root login account (superuser) has the user ID 0. GID This is the numeric primary group ID for this user. (Additional groups for the user are defined in the system group file; see group (5)) GECOS stands for "General Electric Comprehensive Operating Sys- tem", which was renamed to GCOS when GE's large systems division was sold to Honeywell. Dennis Ritchie has reported: "Sometimes we sent printer output or batch jobs to the GCOS machine. The gcos field in the password file was a place to stash the infor- mation for the $IDENTcard. Not elegant." Directory This is the user's home directory: the initial directory where the user is placed after logging in. The value in this field is used to set the HOME environment variable. Shell This is the program to run at login (if empty, use / bin/sh). If set to a nonexistent executable, the user will be unable to login through login (1). The value in this field is used to set the SHELL environment variable. .. ..

1) New userdb library and userlist table

[root@dbsvr1] # mysql-u root-pEnter password:Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 5Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Create a new userdb library and switch to the userdb library:

Mysql > CREATE DATABASE userdb;Query OK, 1 row affected (0.00 sec) mysql > USE userdb;Database changed

Create a new userlist table. For more information on field settings and related operations, please see:

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->); Query OK, 0 rows affected (0.70 sec)

Confirm the structure of the userlist table:

2) an error will be reported if it is imported directly. After MySQL version 5.7.6, import files can only be in the folder specified by secure_file_priv. Execute the show variables like'% secure%' command to display the file directory:

3) perform import operation

Copy the / etc/passwd file to the / var/lib/mysql-files/ directory

Read the contents of the / var/lib/mysql-files/passwd file, separated by ":", and import it into the userlist table:

[root@dbsvr1 ~] # cp / etc/passwd / var/lib/mysql-files/mysql > LOAD DATA INFILE'/ var/lib/mysql-files/passwd'-> INTO TABLE userlist-> FIELDS TERMINATED BY':'; Query OK, 39 rows affected (0.11 sec) Records: 39 Deleted: 0 Skipped: 0 Warnings: 0

The line separation LINES TERMINATED BY'\ nrecords is omitted in the above operation, because this is the default (one original record per line) and is not needed unless you need to split the lines with other characters. For example, the following action specifies that the rows are separated as'\ n', and the contents of the / var/lib/mysql-files/passwd file are imported into another table, userlist2, so that the contents of the userlist2 table are the same as the contents of the userlsit:

Code

4) confirm the import result

Count the number of records in userlist and userlist2 tables respectively:

Mysql > SELECT COUNT (*) FROM userlist;+-+ | COUNT (*) | +-+ | 39 | / / userlist table has 39 records +-+ 1 row in set (0.00 sec) mysql > SELECT COUNT (*) FROM userlist2 +-+ | COUNT (*) | +-+ | 39 | / / userlist table also has 39 records +-+ 1 row in set (0.00 sec)

View the first 10 records of the userlist table, listing the user name, UID, GID, host directory, login Shell:

Mysql > SELECT username,uid,gid,homedir,shell-> FROM userlist LIMIT 10 +-+ | username | uid | gid | homedir | shell | +- -+ | root | 0 | / root | / bin/bash | | bin | 1 | 1 | / bin | / sbin/nologin | | daemon | 2 | 2 | / sbin | / sbin/nologin | | adm | 3 | 4 | / var/adm | / sbin/nologin | lp | 4 | 7 | / Var/spool/lpd | / sbin/nologin | | sync | 5 | 0 | / sbin | / bin/sync | | shutdown | 6 | 0 | / sbin | / sbin/shutdown | | halt | 7 | 0 | / sbin | / sbin/halt | | mail | 8 | 12 | / var/spool/mail | / sbin/nologin | operator | 11 | 0 | / root | / sbin / nologin | +-+ 10 rows in set (0.00 sec)

Check the first 10 records of the userlist2 table, and also list the user name, UID, GID, host directory, login Shell:

Mysql > SELECT username,uid,gid,homedir,shell-> FROM userlist2 LIMIT 10 +-+ | username | uid | gid | homedir | shell | +- -+ | root | 0 | / root | / bin/bash | | bin | 1 | 1 | / bin | / sbin/nologin | | daemon | 2 | 2 | / sbin | / sbin/nologin | | adm | 3 | 4 | / var/adm | / sbin/nologin | lp | 4 | 7 | / Var/spool/lpd | / sbin/nologin | | sync | 5 | 0 | / sbin | / bin/sync | | shutdown | 6 | 0 | / sbin | / sbin/shutdown | | halt | 7 | 0 | / sbin | / sbin/halt | | mail | 8 | 12 | / var/spool/mail | / sbin/nologin | operator | 11 | 0 | / root | / sbin / nologin | +-+ 10 rows in set (0.00 sec)

Step 2: add an automatic number to each record in the userlist table

Just modify the structure of the userlist table and add a self-increasing field.

For example, add an ordinal column named sn as the first field of the userlist table:

1) add a self-increasing primary key field sn

Mysql > ALTER TABLE userlist-> ADD sn int (4) AUTO_INCREMENT PRIMARY KEY FIRST;Query OK, 0 rows affected (0.62sec) Records: 0 Duplicates: 0 Warnings: 0

2) verify the result of automatic numbering

View the first 10 records of the userlist table and list the serial number, user name, UID, GID, and host directory:

Mysql > SELECT sn,username,uid,gid,homedir-> FROM userlist LIMIT 10 +-+ | sn | username | uid | gid | homedir | +-+ | 1 | root | 0 | 0 | / Root | | 2 | bin | 1 | / bin | | 3 | daemon | 2 | 2 | / sbin | 4 | adm | 3 | 4 | / var/adm | | 5 | lp | 4 | 7 | / var/spool/lpd | 6 | sync | 5 | 0 | / sbin | 7 | shutdown | 6 | 0 | / sbin | | 8 | halt | 7 | 0 | / sbin | | 9 | mail | 8 | 12 | / var/spool/mail | | 10 | operator | 11 | 0 | root | +-+ 10 rows in set (0.00 sec) |

Step 3: export the query results from the MySQL database

Take, for example, exporting the first 10 records in the userlist table of the userdb library whose UID is less than 100 to a / var/lib/mysql-files/ulist.txt file.

1) confirm the folder where the exported data is stored

[root@dbsvr1 ~] # ls-ld / var/lib/mysql-files/ drwxr-x---. 2 mysql mysql 19 April 7 11:15 / var/lib/mysql-files/

2) Export the first 10 records with UID less than 100 in the userlsit table

If the default'\ n' is separated by lines, the export operation can also not specify LINES TERMINATED BY:

Mysql > SELECT * FROM userdb.userlist WHERE uid INTO OUTFILE'/ var/lib/mysql-files/ulist.txt'-> FIELDS TERMINATED BY':; Query OK, 24 rows affected (0.00 sec)

3) confirm the export result

Return to the Shell command line and view the number of lines in the / var/lib/mysql-files/ulist.txt file:

[root@dbsvr1 ~] # wc-l / var/lib/mysql-files/ulist.txt24 / var/lib/mysql-files/ulist.txt

View the last 10 lines of the / var/lib/mysql-files/ulist.txt file:

[root@dbsvr1 ~] # tail / var/lib/mysql-files/ulist.txt19:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin24:rpc:x:32:32:Rpcbind Daemon:/var/lib/rpcbind:/sbin/nologin25:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin28:radvd:x:75:75:radvd user:/:/sbin/nologin29:ntp:x: 38:38::/etc/ntp:/sbin/nologin33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin35:postfix:x:89:89::/var/spool/postfix:/sbin/nologin36:sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin37:tcpdump:x:72:72::/:/sbin/nologin39:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false

3 operation table record

3.1 question

Actions recorded in the exercise sheet

Table record insertion table record update table record query table record deletion

3.2 steps

To implement this case, you need to follow these steps.

Step 1: create the stu_info table and make sure that the stu_info table record is empty.

Create the stu_ info table in the userdb library:

[root@dbsvr1] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 19Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > use userdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > CREATE TABLE stu_info (- > name varchar (12) NOT NULL,-> gender enum ('boy','girl') DEFAULT' boy',-> age int (3) NOT NULL->); Query OK, 0 rows affected (0.23 sec)

Delete all records of the stu_ info table:

Mysql > DELETE FROM stu_info;Query OK, 0 rows affected (0.00 sec) / / stu_info table just created and deleted zero records

Confirm the deletion result:

Mysql > SELECT * FROM stu_info;Empty set (0.00 sec)

Step 2: the operation recorded in the exercise table

1) when inserting a record, specify the value of each field of the record

In this case, the fields do not need to be explicitly indicated, but the order and type of values for each record must be consistent with the table structure, otherwise the record may not be inserted correctly.

For example, the following action inserts three table records into the stu_info table:

Mysql > INSERT stu_info VALUES-> ('Jim','girl',24),-> (' Tom','boy',21),-> ('Lily','girl',20); Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0

Confirm the table record after you finish inserting:

Mysql > SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | +-+ 3 rows in set (0.00 sec)

2) when inserting a record, specify only the values of some fields of the record

In this case, the field corresponding to each value must be indicated; moreover, the unassigned field should have a default value or a self-populating property or allow null, otherwise the insert operation will fail.

For example, insert the age information of Jerry into the stu_info table. The gender is the default "boy" and is automatically numbered. The relevant operations are as follows:

Mysql > INSERT INTO stu_info (name,age)-> VALUES ('Jerry',27); Query OK, 1 row affected (0.04 sec)

Similarly, insert the age information of the user Mike:

Mysql > INSERT INTO stu_info (name,age)-> VALUES ('Mike',21); Query OK, 1 row affected (0.05sec)

Confirm all records of the current stu_ info table:

Mysql > SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | Jerry | boy | 27 | Mike | boy | 21 | +-+ 5 rows in set (0.00 sec)

3) when updating table records, if there are no restrictions, it applies to all records

Set the age for all records in the stu_ info table to 10:

Mysql > UPDATE stu_info SET age=10;Query OK, 5 rows affected (0.04 sec) Rows matched: 5 Changed: 5 Warnings: 0

Confirm the update result:

Mysql > SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Jim | girl | 10 | Tom | boy | 10 | Lily | girl | 10 | Jerry | boy | 10 | Mike | boy | 10 | +-+ 5 rows in set (0.00 sec)

4) when updating table records, you can restrict the conditions and be valid only for records that meet the conditions.

Set the age of all records with gender "boy" in the stu_ info table to 20:

Mysql > UPDATE stu_info SET age=20-> WHERE gender='boy';Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0

Confirm the update result:

Mysql > SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Jim | girl | 10 | | Tom | boy | 20 | Lily | girl | 10 | Jerry | boy | 20 | Mike | boy | 20 | +-+ 5 rows in set (0.00 sec)

5) when deleting table records, you can restrict the conditions and delete only those records that meet the criteria.

Delete records under 18 in the stu_info table:

Mysql > DELETE FROM stu_info WHERE age

< 18;Query OK, 2 rows affected (0.03 sec) 确认删除结果： mysql>

SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Tom | boy | 20 | Jerry | boy | 20 | Mike | boy | 20 | +-+ 3 rows in set (0.00 sec)

6) when deleting table records, if there are no restrictions, all table records will be deleted

Delete all records of the stu_ info table:

Mysql > DELETE FROM stu_info;Query OK, 3 rows affected (0.00 sec)

Confirm the deletion result:

Mysql > SELECT * FROM stu_info;Empty set (0.00 sec)

4 query and matching conditions

4.1 question

Practice common SQL queries and condition settings

Create a stu_info table and insert data to practice common SQL queries and condition settings

4.2 steps

To implement this case, you need to follow these steps.

Step 1: set up the employee file table stu_info according to the requirements of the task (if the previous lab has been created, you can continue to use it after clearing the records in the stu_ info table of the previous experiment)

1) create a stu_info table in the userdb library

[root@dbsvr1] # mysql-u root-pEnter password:Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 5Server version: 5.6.15 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Open the test library:

Mysql > USE userdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changed

Create a stu_info table, including name, gender, and age fields:

Mysql > CREATE TABLE stu_info (- > name varchar (12) NOT NULL,-> gender enum ('boy','girl') DEFAULT' boy',-> age int (3) NOT NULL->); Query OK, 0 rows affected (0.03 sec)

Confirm the table structure:

2) prepare the test form

Insert several test records into the established stu_info table

Mysql > INSERT INTO stu_info VALUES-> ('Jim','girl',24),-> (' Tom','boy',21),-> ('Lily','girl',20),-> (' Jerry','boy',27),-> ('Mike','boy',21)->; Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0

Confirm all recorded contents of the stu_info table:

Mysql > SELECT * FROM stu_info;+-+ | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | Jerry | boy | 27 | Mike | boy | 21 | +-+ 5 rows in set (0.00 sec)

Step 2: practice common SQL queries and condition settings

1) commonly used table record statistical functions

Query the total number of records in the stu_ information table (5 in this case):

Mysql > SELECT count (*) FROM stu_info;+-+ | count (*) | +-+ | 5 | +-+ 1 row in set (0.00 sec)

Calculate the average age, maximum age, and minimum age of each student in the stu_ info table:

Mysql > SELECT avg (age), max (age), min (age) FROM stu_info +-+ | avg (age) | max (age) | min (age) | +-+ | 22.6000 | 27 | 20 | +- -+ 1 row in set (0.00 sec)

Calculate the number of male students in the stu_ info table:

Mysql > SELECT count (gender) FROM stu_info WHERE gender='boy';+-+ | count (gender) | +-+ | 3 | +-+ 1 row in set (0.00 sec)

2) numerical comparison of field values

List the student records aged 21 in the stu_info table:

Mysql > SELECT * FROM stu_info WHERE age=21;+-+ | name | gender | age | +-+ | Tom | boy | 21 | Mike | boy | 21 | +-+ 2 rows in set (0.00 sec)

List the student records over the age of 21 in the stu_info table:

Mysql > SELECT * FROM stu_info WHERE age > 21 rows in set + | name | gender | age | +-+ | Jim | girl | 24 | Jerry | boy | 27 | +-+ 2 rows in set (0.00 sec)

List the student records in the stu_info table who are 21 years old or older:

Mysql > SELECT * FROM stu_info WHERE age > = 21 FROM stu_info WHERE age + | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Jerry | boy | 27 | Mike | boy | 21 | +-+ 4 rows in set (0.00 sec)

List the student records in the stu_info table between the ages of 20 and 24:

Mysql > SELECT * FROM stu_info WHERE age BETWEEN 20 and 24 boy + | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | Mike | boy | 21 | +-+ 4 rows in set (0.00 sec)

3) combination of multiple conditions

List the records of female students under the age of 23 in the stu_info table:

Mysql > SELECT * FROM stu_info WHERE age

SELECT * FROM stu_info WHERE age

< 23 OR gender='girl';+------+--------+-----+| name | gender | age |+------+--------+-----+| Jim | girl | 24 || Tom | boy | 21 || Lily | girl | 20 || Mike | boy | 21 |+------+--------+-----+4 rows in set (0.00 sec) 如果某个记录的姓名属于指定范围内的一个，则将其列出： mysql>

SELECT * FROM stu_info WHERE name IN-> ('Jim','Tom','Mickey','Minnie'); +-+ | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | +-+ 2 rows in set (0.00 sec)

4) use SELECT to do mathematical calculation

Calculate the sum of 1234 and 5678:

Mysql > SELECT 1234 / 5678 row in set + | 1234 / 5678 | +-+ | 6912 | +-+ 1 sec)

Calculate the product of 1234 and 5678:

Mysql > SELECT 1234 / 5678 row in set + | 1234 / 5678 | +-+ | 7006652 | +-+ 1 sec)

The result of calculating 1.23456789 divided by 3:

Mysql > SELECT 1.23456789 row in set + | 1.23456789Universe 3 | +-+ | 0.411522630000 | +-+ 1 sec)

Output the names of each student in the stu_info table and their age after 15 years:

Mysql > SELECT name,age+15 FROM stu_info;+-+-+ | name | age+15 | +-+-+ | Jim | 39 | Tom | 36 | Lily | 35 | Jerry | 42 | Mike | 36 | +-+-+ 5 rows in set (0.00 sec)

5) use fuzzy query and lead by LIKE

The underscore _ matches a single character, and% can match any number of characters.

List the student records whose names begin with "J" in the stu_info table:

Mysql > SELECT * FROM stu_info WHERE name LIKE'J% interview + | name | gender | age | +-+ | Jim | girl | 24 | | Jerry | boy | 27 | +-+ 2 rows in set (0.00 sec)

List the student records in the stu_info table whose names begin with "J" and have only 3 letters:

Mysql > SELECT * FROM stu_info WHERE name LIKE 'JockeeBlockedPlus | name | gender | age | +-+ | Jim | girl | 24 | +-+ 1 row in set (0.00 sec)

6) using regular expressions, REGEXP leads

List the student records in the stu_info table whose names begin with "J" and end with "y":

Mysql > SELECT * FROM stu_info WHERE name REGEXP'^ J.According to sec + | name | gender | age | +-+ | Jerry | boy | 27 | +-+ 1 row in set (0.00 sec)

The effect is equivalent to:

Mysql > SELECT * FROM stu_info WHERE name Like'J% row in set + | name | gender | age | +-+ | Jerry | boy | 27 | +-+ 1 row in set (0.00 sec)

List the student records in the stu_info table whose names begin with "J" or end with "y":

Mysql > SELECT * FROM stu_info WHERE name REGEXP'^ J | Yoshengli + | name | gender | age | +-+ | Jim | girl | 24 | Lily | girl | 20 | | Jerry | boy | 27 | +-+ 3 rows in set (0.00 sec)

The effect is equivalent to:

Mysql > SELECT * FROM stu_info WHERE name Like'J%'OR name Like'% name | gender | age | +-+ | Jim | girl | 24 | Lily | girl | 20 | Jerry | boy | 27 | +-+ 3 rows in set (0.00 sec)

7) sort by specified field, ORDER BY

Lists all records in the stu_ info table, sorted by age:

Mysql > SELECT * FROM stu_info GROUP BY age;+-+ | name | gender | age | +-+ | Lily | girl | 20 | | Tom | boy | 21 | Jim | girl | 24 | | Jerry | boy | 27 | +-+ 4 rows in set (0.00 sec)

Since the default is Ascend arrangement, the above operation is equivalent to:

Mysql > SELECT * FROM stu_info GROUP BY age ASC;+-+ | name | gender | age | +-+ | Lily | girl | 20 | | Tom | boy | 21 | Jim | girl | 24 | | Jerry | boy | 27 | +-+ 4 rows in set (0.00 sec)

To sort in descending (Descend) order, change ASC to DESC:

Mysql > SELECT * FROM stu_info GROUP BY age DESC;+-+ | name | gender | age | +-+ | Jerry | boy | 27 | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | +-+ 4 rows in set (0.00 sec)

8) limit the number of query results output, LIMIT

Query all records of the stu_info table, listing only the first three items:

Mysql > SELECT * FROM stu_info LIMIT 3 rows in set + | name | gender | age | +-+ | Jim | girl | 24 | Tom | boy | 21 | Lily | girl | 20 | +-+ 3 rows in set (0.00 sec)

List the 3 oldest student records in the stu_info table:

Mysql > SELECT * FROM stu_info GROUP BY age DESC LIMIT 3 | name | gender | age | +-+ | Jerry | boy | 27 | | Jim | girl | 24 | Tom | boy | 21 | +-+ 3 rows in set (0.00 sec)

9) grouping query results, GROUP BY

According to the stu_info table, the students are divided into groups according to gender, and the numbers of male and female students are calculated respectively:

Mysql > SELECT gender,count (gender) FROM stu_info GROUP BY gender;+-+-+ | gender | count (gender) | +-+-+ | boy | 3 | | girl | 2 | +-+-+ 2 rows in set (0.00 sec)

When listing query fields, you can use the AS keyword to specify the display alias. For example, the above action can be changed to:

Mysql > SELECT gender AS 'gender', count (gender) AS 'number'-> FROM stu_info GROUP BY gender;+-+-+ | gender | number of people | +-+-+ | boy | 3 | girl | 2 | +-+-+ 2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.