MySQL 的 常用语句介绍（重点 SELECT 语句）_mysql语句介绍

1、安装 MySQL

[root@Sybil ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
[root@Sybil ~]# yum install mariadb.server -y
Updated:
  mariadb-server.x86_64 1:5.5.65-1.el7           

Dependency Updated:
  mariadb.x86_64 1:5.5.65-1.el7               mariadb-libs.x86_64 1:5.5.65-1.el7

Complete!
[root@Sybil ~]# systemctl start mariadb
[root@Sybil ~]# ss -tnl
State       Recv-Q Send-Q         Local Address:Port                Peer Address:Port              
LISTEN      0      50                         *:3306                           *:*                  
LISTEN      0      128                        *:22                             *:*                  
LISTEN      0      100                127.0.0.1:25                             *:*                  
LISTEN      0      128                       :::22                            :::*                  
LISTEN      0      100                      ::1:25                            :::*                  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2、登陆 MySQL，创建数据库 和 表

# 登陆 MySQL
[root@Sybil ~]# mysql -uroot -p
Enter password: root

# 查看现有数据
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
5 rows in set (0.01 sec)

# 创建数据库 JIAOLEE_FAMILY
MariaDB [(none)]> CREATE DATABASE JIAOLEE_FAMILY;
Query OK, 1 row affected (0.00 sec)

# 查看现有数据库 JIAOLEE_FAMILY 已经创建成功
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| JIAOLEE_FAMILY     |
| mysql              |
+--------------------+
6 rows in set (0.00 sec)

# 使用数据库 JIAOLEE_FAMILY 
MariaDB [(none)]> USE JIAOLEE_FAMILY;
Database changed

# 创建新表
MariaDB [JIAOLEE_FAMILY]> CREATE TABLE `FAMILY_MEMBERS` (
    -> `ID` int(11) NOT NULL AUTO_INCREMENT ,
    -> `Name` varchar(30) NOT NULL ,
    -> `Age` int(11) DEFAULT NULL ,
    -> `Gender` enum('M', 'F') NOT NULL ,
    -> `Birth_Date` date NOT NULL ,
    -> PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

# 查看新创建的表
MariaDB [JIAOLEE_FAMILY]> SHOW TABLES;
+--------------------------+
| Tables_in_JIAOLEE_FAMILY |
+--------------------------+
| FAMILY_MEMBERS           |
+--------------------------+
1 row in set (0.00 sec)

# 查看新创建的表的列信息
MariaDB [JIAOLEE_FAMILY]> DESC FAMILY_MEMBERS;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| ID         | int(11)       | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(30)   | NO   |     | NULL    |                |
| Age        | int(11)       | YES  |     | NULL    |                |
| Gender     | enum('M','F') | NO   |     | NULL    |                |
| Birth_Date | date          | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

3、在表中添加内容

MariaDB [JIAOLEE_FAMILY]> INSERT INTO FAMILY_MEMBERS (Name, Age, Gender, Birth_Date) VALUES ('Sybil', 1, 'F', '2020-02-02');
Query OK, 1 row affected (0.00 sec)

MariaDB [JIAOLEE_FAMILY]> INSERT INTO FAMILY_MEMBERS (Name, Age, Gender, Birth_Date) VALUES ('Tang', 6, 'F', '2014-05-05');
Query OK, 1 row affected (0.07 sec)

MariaDB [JIAOLEE_FAMILY]> INSERT INTO FAMILY_MEMBERS (Name, Age, Gender, Birth_Date) VALUES ('Tang', 6, 'F', '2014-05-05');
Query OK, 1 row affected (0.01 sec)

MariaDB [JIAOLEE_FAMILY]> INSERT INTO FAMILY_MEMBERS (Name, Age, Gender, Birth_Date) VALUES ('Tang', 6, 'F', '2014-05-05');
Query OK, 1 row affected (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

4、查看表中内容

• 注意：在庞大的数据库中进行表内容查看时，一定要带 WHERE 条件，不然查询数据过于庞大，会对服务器造成较大压力。

# 查看数据库表中的所有内容， FROM 后面跟 表 的名称
MariaDB [JIAOLEE_FAMILY]> SELECT * FROM FAMILY_MEMBERS;
+----+--------+------+--------+------------+
| ID | Name   | Age  | Gender | Birth_Date |
+----+--------+------+--------+------------+
|  1 | Jiao   |   31 | M      | 1989-10-01 |
|  2 | Lee    |   18 | F      | 1999-10-01 |
|  3 | Tang   |    6 | F      | 2014-05-05 |
|  5 | Neo    |    6 | M      | 2014-10-01 |
|  6 | Sybil  |    1 | F      | 2020-02-02 |
|  7 | Luna   |    1 | F      | 2020-10-01 |
|  8 | Pang   |    1 | F      | 2020-10-01 |
|  9 | Fufu   |   65 | M      | 1955-06-06 |
| 10 | Lanlan |   55 | F      | 1965-06-06 |
| 11 | Huahua |   55 | F      | 1962-10-12 |
| 12 | Jinjin |   55 | M      | 1963-10-12 |
| 13 | Sybil  |    1 | F      | 2020-02-02 |
| 14 | Sybil  |    1 | F      | 2020-02-02 |
| 15 | Sybil  |    1 | F      | 2020-02-02 |
| 16 | Tang   |    6 | F      | 2014-05-05 |
| 17 | Tang   |    6 | F      | 2014-05-05 |
| 18 | Tang   |    6 | F      | 2014-05-05 |
+----+--------+------+--------+------------+
17 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4、SELECT 语句

• 1、 列名称 要使用 反引号（TAB键上面的符号） 进行标注
• 2、表中的 列内容 用 单引号 或者 双引号 进行标注

4.1 查询 Name 列为 Tang 的所有行信息

MariaDB [JIAOLEE_FAMILY]> SELECT * FROM FAMILY_MEMBERS WHERE `Name` = 'Tang';
+----+------+------+--------+------------+
| ID | Name | Age  | Gender | Birth_Date |
+----+------+------+--------+------------+
|  3 | Tang |    6 | F      | 2014-05-05 |
| 16 | Tang |    6 | F      | 2014-05-05 |
| 17 | Tang |    6 | F      | 2014-05-05 |
| 18 | Tang |    6 | F      | 2014-05-05 |
+----+------+------+--------+------------+
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10

4.2 查询 Name 列为 Tang 的所有行信息（列信息只显示 ID 、Name 和 Age）

MariaDB [JIAOLEE_FAMILY]> SELECT `ID`, `Name`, `Age` FROM FAMILY_MEMBERS WHERE `Name` = 'Tang';
+----+------+------+
| ID | Name | Age  |
+----+------+------+
|  3 | Tang |    6 |
| 16 | Tang |    
1
2
3
4
5