01：mysql之基础#

1
mysql> select【涩莱克特】 version【沃申】() ;  //查看数据库软件版本
2
+------------------+
3
| version【沃申】() |
4
+------------------+
5
| 8.0.41           |
6
+------------------+
7
1 row in set (0.00 sec)
8
mysql> select【涩莱克特】 user() ; //查看登陆的用户和客户端地址
9
+----------------+
10
| user()         |
11
+----------------+
12
| root@localhost | 管理员root本机登陆
13
+----------------+
14
1 row in set (0.00 sec)
15
mysql> show【瘦】 databases;【得塔-贝斯】 //查看已有的库
16
+----------------------------------------------+
17
| Database【得塔-贝斯】                         |
18
+----------------------------------------------+
19
| information【因弗梅申】_schema【斯京(白话)磨】  |
20
| mysql                                        |
21
| performance【破佛曼斯】_schema【斯京(白话)磨】  |
22
| sys                                          |
23
+----------------------------------------------+
24
4 rows in set (0.00 sec)

1
mysql> select【涩莱克特】 database();【得塔-贝斯】  //查看当前在那个库里  null【nò】 表示没有在任何库里
2
+------------+
3
| database() |【得塔-贝斯】
4
+------------+
5
| NULL【nò】       |
6
+------------+
7
1 row in set (0.00 sec)
8
mysql> use mysql ; //切换到mysql库
9
mysql> select【涩莱克特】 database();【得塔-贝斯】  // 再次显示所在的库
10
+------------+
11
| database() |【得塔-贝斯】
12
+------------+
13
| mysql      |
14
+------------+
15
1 row in set (0.00 sec)
16
mysql> show【瘦】 tables;【忒-部s】  //显示库里已有的表
17
+------------------------------------------------------+
18
| Tables【忒-部s】_in_mysql                             |
19
+------------------------------------------------------+
20
| columns_priv                                         |
21
| component                                            |
22
| db                                                   |
23
| default_roles                                        |
24
| engine_cost                                          |
25
| func                                                 |
26
| general_log                                          |
27
| global_grants                                        |
28
| gtid_executed                                        |
29
| help_category                                        |
30
| help_keyword                                         |
31
| help_relation                                        |
32
| help_topic                                           |
33
| innodb_index_stats                                   |
34
| innodb_table【忒-部】_stats                           |
35
| password_history                                     |
36
| plugin                                               |
37
| procs_priv                                           |
38
| proxies_priv                                         |
39
| replication_asynchronous_connection【肯耐申】_failover         |
40
| replication_asynchronous_connection【肯耐申】_failover_managed |
41
| replication_group_configuration_version【沃申】       |
42
| replication_group_member_actions                     |
43
| role_edges                                           |
44
| server_cost                                          |
45
| servers                                              |
46
| slave_master_info                                    |
47
| slave_relay_log_info                                 |
48
| slave_worker_info                                    |
49
| slow_log                                             |
50
| tables【忒-部s】_priv                                 |
51
| time_zone                                            |
52
| time_zone_leap_second                                |
53
| time_zone_name                                       |
54
| time_zone_transition                                 |
55
| time_zone_transition_type                            |
56
| user                                                 |
57
+------------------------------------------------------+
58
37 rows in set (0.00 sec)
59
mysql> exit【诶西特】 ;  #断开连接
60
Bye
61
[root@mysql50 ~]#

1
[root@mysql50 ~]# mysql //连接服务
2
Welcome to the MySQL monitor.  Commands end with ; or \g.
3
Your MySQL connection【肯耐申】 id is 7
4
Server version【沃申】: 8.0.26 Source distribution
5
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
6
Oracle is a registered trademark of Oracle Corporation and/or its
7
affiliates. Other names may be trademarks of their respective
8
owners.
9
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10
//查看存放密码的表头名
11
Mysql>
12
Mysql> desc  mysql.user ;
13
//把mysql库下user表中 用户root的密码设置为无；
14
Mysql>
15
mysql> update mysql.user set authentication_string="" where【威尔】 user="root";
16
Query OK, 1 row affected (0.05 sec)
17
Rows matched: 1  Changed: 1  Warnings: 0
18
Mysql>
19
mysql> exit【诶西特】; 断开连接
20
Bye

1
[root@localhost ~]# mysql  无密码登陆
2
Welcome to the MySQL monitor.  Commands end with ; or \g.
3
Your MySQL connection【肯耐申】 id is 8
4
Server version【沃申】: 8.0.26 Source distribution
5
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
6
Oracle is a registered trademark of Oracle Corporation and/or its
7
affiliates. Other names may be trademarks of their respective
8
owners.
9
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10
//设置root用户本机登陆密码
11
Mysql>
12
mysql> alter user root@"localhost" identified by "NSD123456...a";
13
Query OK, 0 rows affected (0.00 sec)
14
Mysql>
15
mysql> exit【诶西特】 断开连接
16
Bye

1
[root@localhost ~]# mysql -uroot -pNSD123456...a  使用破解的密码登陆
2
mysql: [Warning] Using a password on the command line interface can be insecure.
3
Welcome to the MySQL monitor.  Commands end with ; or \g.
4
Your MySQL connection【肯耐申】 id is 10
5
Server version【沃申】: 8.0.26 Source distribution
6
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
7
Oracle is a registered trademark of Oracle Corporation and/or its
8
affiliates. Other names may be trademarks of their respective
9
owners.
10
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
11
mysql>
12
mysql> #登陆成功
13
Mysql>
14
mysql> show【瘦】 databases;【得塔-贝斯】 查看已有的库
15
+----------------------------------------------+
16
| Database【得塔-贝斯】                          |
17
+----------------------------------------------+
18
|  information【因弗梅申】_schema【斯京(白话)磨】 |
19
| mysql                                        |
20
| performance【破佛曼斯】_schema【斯京(白话)磨】  |
21
| sys                                          |
22
+----------------------------------------------+
23
4 rows in set (0.01 sec)

1
//恢复数据
2
[root@mysql50 ~]# mysql -uroot  -pNSD123456...a < /root/moershi.sql
3
mysql: [Warning] Using a password on the command line interface can be insecure.
4
//连接服务
5
[root@mysql50 ~]# mysql -uroot  -pNSD123456...a
6
mysql>
7
mysql> show【瘦】 databases;【得塔-贝斯】 //查看库
8
+----------------------------------------------+
9
| Database【得塔-贝斯】                         |
10
+----------------------------------------------+
11
|  information【因弗梅申】_schema【斯京(白话)磨】 |
12
| mysql                                        |
13
| performance【破佛曼斯】_schema【斯京(白话)磨】  |
14
| sys                                          |
15
| moershi                                      | 恢复的库
16
+----------------------------------------------+
17
5 rows in set (0.00 sec)
18
mysql>
19
mysql> use moershi; //进入库
20
Reading table【忒-部】 information【因弗梅申】 for completion of table【忒-部】 and column names
21
You can turn off this feature to get a quicker startup【s 达 啊普】 with -A
22
Database【得塔-贝斯】 changed
23
mysql>
24
mysql> show【瘦】 tables;【忒-部s】  //查看表
25
+------------------+
26
| Tables【忒-部s】_in_moershi |
27
+------------------+
28
| departments      | 部门表
29
| employees        | 员工表
30
| salary           | 工资表
31
| user             | 用户表
32
+------------------+
33
4 rows in set (0.00 sec)

1
mysql> desc moershi.user;  //查看表头
2
+----------+-------------+------+-----+----------------+----------------+
3
| Field    | Type        | Null【nò】 | Key | Default | Extra          |
4
+----------+-------------+------+-----+----------------+----------------+
5
| id       | int(11)     | NO   | PRI | NULL【nò】    | auto_increment |行号
6
| name     | char(20)    | YES  |     | NULL【nò】    |                |用户名
7
| password | char(1)     | YES  |     | NULL【nò】    |                |密码占位符
8
| uid      | int(11)     | YES  |     | NULL【nò】    |                | uid号
9
| gid      | int(11)     | YES  |     | NULL【nò】    |                | gid号
10
| comment  | varchar(50) | YES  |     | NULL【nò】    |                | 描述信息
11
| homedir  | varchar(80) | YES  |     | NULL【nò】    |                | 家目录
12
| shell    | char(30)    | YES  |     | NULL【nò】    |                | 解释器
13
+----------+-------------+------+-----+----------------+----------------+
14
8 rows in set (0.00 sec)

1
mysql> select【涩莱克特】 name from【弗乱】  moershi.user;        //查看一个表头
2
+-----------------+
3
| name            |
4
+-----------------+
5
| root            |
6
| bin             |
7
| daemon          |
8
| adm             |
9
| lp              |
10
| sync            |
11
| shutdown        |
12
| halt            |
13
| mail            |
14
| operator        |
15
| games           |
16
| ftp             |
17
| nobody          |
18
| systemd-network |
19
| dbus            |
20
| polkitd         |
21
| sshd            |
22
| postfix         |
23
| chrony          |
24
| rpc             |
25
| rpcuser         |
26
| nfsnobody       |
27
| haproxy         |
28
| plj             |
29
| apache          |
30
| mysql           |
31
| bob             |
32
+-----------------+
33
27 rows in set (0.00 sec)
34

35
mysql> select【涩莱克特】 name ,uid from【弗乱】  moershi.user;    //查看多个表头
36
+-----------------+-------+
37
| name            | uid   |
38
+-----------------+-------+
39
| root            |     0 |
40
| bin             |     1 |
41
| daemon          |     2 |
42
| adm             |     3 |
43
| lp              |     4 |
44
| sync            |     5 |
45
| shutdown        |     6 |
46
| halt            |     7 |
47
| mail            |     8 |
48
| operator        |    11 |
49
| games           |    12 |
50
| ftp             |    14 |
51
| nobody          |    99 |
52
| systemd-network |   192 |
53
| dbus            |    81 |
54
| polkitd         |   999 |
55
| sshd            |    74 |
56
| postfix         |    89 |
57
| chrony          |   998 |
58
| rpc             |    32 |
59
| rpcuser         |    29 |
60
| nfsnobody       | 65534 |
61
| haproxy         |   188 |
62
| plj             |  1000 |
63
| apache          |    48 |
64
| mysql           |    27 |
65
| bob             |  NULL【nò】 |
66
+-----------------+-------+
67
27 rows in set (0.00 sec)
68

69
mysql> select【涩莱克特】 *  from【弗乱】  moershi.user;        //查看所有表头
70
+----+-----------------+----------+-------+-------+----------------------------+--------------------+----------------+
71
| id | name            | password | uid   | gid   | comment                    | homedir            | shell          |
72
+----+-----------------+----------+-------+-------+----------------------------+--------------------+----------------+
73
|  1 | root            | x        |     0 |     0 | root                       | /root              | /bin/bash      |
74
|  2 | bin             | x        |     1 |     1 | bin                        | /bin               | /sbin/nologin  |
75
|  3 | daemon          | x        |     2 |     2 | daemon                     | /sbin              | /sbin/nologin  |
76
|  4 | adm             | x        |     3 |     4 | adm                        | /var/adm           | /sbin/nologin  |
77
|  5 | lp              | x        |     4 |     7 | lp                         | /var/spool/lpd     | /sbin/nologin  |
78
|  6 | sync            | x        |     5 |     0 | sync                       | /sbin              | /bin/sync      |
79
|  7 | shutdown        | x        |     6 |     0 | shutdown                   | /sbin              | /sbin/shutdown |
80
|  8 | halt            | x        |     7 |     0 | halt                       | /sbin              | /sbin/halt     |
81
|  9 | mail            | x        |     8 |    12 | mail                       | /var/spool/mail    | /sbin/nologin  |
82
| 10 | operator        | x        |    11 |     0 | operator                   | /root              | /sbin/nologin  |
83
| 11 | games           | x        |    12 |   100 | games                      | /usr/games         | /sbin/nologin  |
84
| 12 | ftp             | x        |    14 |    50 | FTP User                   | /var/ftp           | /sbin/nologin  |
85
| 13 | nobody          | x        |    99 |    99 | Nobody                     | /                  | /sbin/nologin  |
86
| 14 | systemd-network | x        |   192 |   192 | systemd Network Management | /                  | /sbin/nologin  |
87
| 15 | dbus            | x        |    81 |    81 | System message bus         | /                  | /sbin/nologin  |
88
| 16 | polkitd         | x        |   999 |   998 | User for polkitd           | /                  | /sbin/nologin  |
89
| 17 | sshd            | x        |    74 |    74 | Privilege-separated SSH    | /var/empty/sshd    | /sbin/nologin  |
90
| 18 | postfix         | x        |    89 |    89 |                            | /var/spool/postfix | /sbin/nologin  |
91
| 19 | chrony          | x        |   998 |   996 |                            | /var/lib/chrony    | /sbin/nologin  |
92
| 20 | rpc             | x        |    32 |    32 | Rpcbind Daemon             | /var/lib/rpcbind   | /sbin/nologin  |
93
| 21 | rpcuser         | x        |    29 |    29 | RPC Service User           | /var/lib/nfs       | /sbin/nologin  |
94
| 22 | nfsnobody       | x        | 65534 | 65534 | Anonymous NFS User         | /var/lib/nfs       | /sbin/nologin  |
95
| 23 | haproxy         | x        |   188 |   188 | haproxy                    | /var/lib/haproxy   | /sbin/nologin  |
96
| 24 | plj             | x        |  1000 |  1000 |                            | /home/plj          | /bin/bash      |
97
| 25 | apache          | x        |    48 |    48 | Apache                     | /usr/share/httpd   | /sbin/nologin  |
98
| 26 | mysql           | x        |    27 |    27 | MySQL Server               | /var/lib/mysql     | /bin/false     |
99
| 27 | bob             | NUL【nò】L     |  NULL【nò】 |  NULL【nò】 | NULL【nò】                       | NULL【nò】               | NULL【nò】           |
100
+----+-----------------+----------+-------+-------+----------------------------+--------------------+----------------+
101
27 rows in set (0.00 sec)

1
mysql> select【涩莱克特】 *  from【弗乱】  moershi.user where【威尔】  name = “root”;        //查找root用户信息
2
+----+------+----------+------+------+---------+---------+-----------+
3
| id | name | password | uid  | gid  | comment | homedir | shell     |
4
+----+------+----------+------+------+---------+---------+-----------+
5
|  1 | root | x        |    0 |    0 | root    | /root   | /bin/bash |
6
+----+------+----------+------+------+---------+---------+-----------+
7
1 row in set (0.00 sec)
8
mysql>
9
mysql> select【涩莱克特】 *  from【弗乱】  moershi.user where【威尔】  id = 2 ;        //查找第2行用户信息
10
+----+------+----------+------+------+---------+---------+--------------+
11
| id | name | password | uid  | gid  | comment | homedir | shell         |
12
+----+------+----------+------+------+---------+---------+--------------+
13
|  2 | bin   | x        |    1 |   1   | bin      | /bin   | /sbin/nologin |
14
+----+------+----------+------+------+---------+---------+--------------+
15
1 row in set (0.00 sec)

1
//查看第3行的行号、用户名、uid、gid  四个表头的值
2
mysql>
3
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  id = 3;
4
+----+--------+------+------+
5
| id | name   | uid  | gid  |
6
+----+--------+------+------+
7
|  3 | daemon |    2 |    2 |
8
+----+--------+------+------+
9
1 row in set (0.00 sec)
10
//查看前2行的行号用户名、uid、gid 四个表头的值
11
mysql>
12
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  id < 3;
13
+----+------+------+------+
14
| id | name | uid  | gid  |
15
+----+------+------+------+
16
|  1 | root |    0 |    0 |
17
|  2 | bin  |    1 |    1 |
18
+----+------+------+------+
19
2 rows in set (0.00 sec)
20
//查看前3行的行号、用户名、uid、gid  四个表头的值
21
mysql>
22
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  id <= 3;
23
+----+--------+------+------+
24
| id | name   | uid  | gid  |
25
+----+--------+------+------+
26
|  1 | root   |    0 |    0 |
27
|  2 | bin    |    1 |    1 |
28
|  3 | daemon |    2 |    2 |
29
+----+--------+------+------+
30
3 rows in set (0.00 sec)
31
//查看前uid号大于6000的行号、用户名、uid、gid  四个表头的值
32
mysql>
33
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  uid > 6000;
34
+----+-----------+-------+-------+
35
| id | name      | uid   | gid   |
36
+----+-----------+-------+-------+
37
| 22 | nfsnobody | 65534 | 65534 |
38
+----+-----------+-------+-------+
39
1 row in set (0.00 sec)
40
//查看前uid号大于等于1000的行号、用户名、uid、gid  四个表头的值
41
mysql>
42
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  uid >= 1000;
43
+----+-----------+-------+-------+
44
| id | name      | uid   | gid   |
45
+----+-----------+-------+-------+
46
| 22 | nfsnobody | 65534 | 65534 |
47
| 24 | plj       |  1000 |  1000 |
48
+----+-----------+-------+-------+
49
2 rows in set (0.00 sec)
50
//查看uid号和gid号相同的行 仅显示行号、用户名、uid、gid  四个表头的值
51
mysql>
52
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  uid = gid;
53
+----+-----------------+-------+-------+
54
| id | name            | uid   | gid   |
55
+----+-----------------+-------+-------+
56
|  1 | root            |     0 |     0 |
57
|  2 | bin             |     1 |     1 |
58
|  3 | daemon          |     2 |     2 |
59
| 13 | nobody          |    99 |    99 |
60
| 14 | systemd-network |   192 |   192 |
61
| 15 | dbus            |    81 |    81 |
62
| 17 | sshd            |    74 |    74 |
63
| 18 | postfix         |    89 |    89 |
64
| 20 | rpc             |    32 |    32 |
65
| 21 | rpcuser         |    29 |    29 |
66
| 22 | nfsnobody       | 65534 | 65534 |
67
| 23 | haproxy         |   188 |   188 |
68
| 24 | plj             |  1000 |  1000 |
69
| 25 | apache          |    48 |    48 |
70
| 26 | mysql           |    27 |    27 |
71
+----+-----------------+-------+-------+
72
15 rows in set (0.00 sec)
73
//查看uid号和gid号不一样的行 仅显示行号、用户名、uid、gid  四个表头的值
74
mysql>
75
mysql> select【涩莱克特】  id,name,uid,gid  from【弗乱】  moershi.user where【威尔】  uid != gid;
76
+----+----------+------+------+
77
| id | name     | uid  | gid  |
78
+----+----------+------+------+
79
|  4 | adm      |    3 |    4 |
80
|  5 | lp       |    4 |    7 |
81
|  6 | sync     |    5 |    0 |
82
|  7 | shutdown |    6 |    0 |
83
|  8 | halt     |    7 |    0 |
84
|  9 | mail     |    8 |   12 |
85
| 10 | operator |   11 |    0 |
86
| 11 | games    |   12 |  100 |
87
| 12 | ftp      |   14 |   50 |
88
| 16 | polkitd  |  999 |  998 |
89
| 19 | chrony   |  998 |  996 |
90
+----+----------+------+------+
91
11 rows in set (0.00 sec)
92
mysql>