Mysql study note 3-优快云博客

本文链接：https://blog.youkuaiyun.com/xuyunzhang/article/details/7636329

本文介绍如何使用MySQL进行基本操作，包括登录并查看用户表、创建用户、创建数据库及表、插入数据、更新用户密码等核心步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. login and show tables of user:

[root@localhost ctest]# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.06 sec)

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mysql;

Database changed

mysql> select user, host, password from user;

+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *0E6A839B8FF3817A463ED960F7F8E56B1BDC8E4B |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.02 sec)

mysql>

2. create user:

mysql> grant all on *.* to rick@localhost identified by 'nopasswd';

Query OK, 0 rows affected (0.25 sec)

mysql> grant all on *.* to rick@'192.168.0.0/255.255.255.0' identified by 'nopasswd';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to rick@'%.wiley.com' identified by 'nopasswd';

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host, password from user;

+------+---------------------------+-------------------------------------------+
| user | host                      | password                                  |
+------+---------------------------+-------------------------------------------+
| root | localhost                 | *0E6A839B8FF3817A463ED960F7F8E56B1BDC8E4B |
| root | localhost.localdomain     |                                           |
| root | 127.0.0.1                 |                                           |
| root | ::1                       |                                           |
|      | localhost                 |                                           |
|      | localhost.localdomain     |                                           |
| rick | localhost                 | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
| rick | 192.168.0.0/255.255.255.0 | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
| rick | %.wiley.com               | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
+------+---------------------------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql> \q

Bye
[root@localhost ctest]#

3. create database:

[zhang@localhost ch08]$ cat create_children.sql

-- 
-- Create the table chilren
-- 

CREATE TABLE children (
   childno int(11) NOT NULL auto_increment,
   fname varchar(30),
   age int(11),
   PRIMARY KEY (childno)
);

--
-- Populate the table 'children'
--

INSERT INTO children(childno, fname, age) VALUES (2,'Andrew',17);
INSERT INTO children(childno, fname, age) VALUES (3,'Gavin',9);
INSERT INTO children(childno, fname, age) VALUES (4,'Duncan',6);
INSERT INTO children(childno, fname, age) VALUES (5,'Emma',4);
INSERT INTO children(childno, fname, age) VALUES (6,'Alex',15);
INSERT INTO children(childno, fname, age) VALUES (7,'Adrian',9);

[zhang@localhost ch08]$ mysql -u rick --password=nopasswd

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database rick;

Query OK, 1 row affected (0.03 sec)

mysql> create database foo;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| rick               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

mysql> use foo;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| rick               |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

mysql> \. create_children.sql

Query OK, 0 rows affected (0.25 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql>

mysql> show tables;

+---------------+
| Tables_in_foo |
+---------------+
| children      |
+---------------+
1 row in set (0.00 sec)

mysql> select * from children;
+---------+--------+------+
| childno | fname  | age  |
+---------+--------+------+
|       2 | Andrew |   17 |
|       3 | Gavin  |    9 |
|       4 | Duncan |    6 |
|       5 | Emma   |    4 |
|       6 | Alex   |   15 |
|       7 | Adrian |    9 |
+---------+--------+------+
6 rows in set (0.00 sec)

mysql>

elso create tables directly as follow if the database exist already:

[zhang@localhost ch08]$ mysql -u rick --password=nopasswd rick < create_children.sql
[zhang@localhost ch08]$
[zhang@localhost ch08]$ mysql -u rick --password=nopasswd rick

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from children;

+---------+--------+------+
| childno | fname  | age  |
+---------+--------+------+
|       2 | Andrew |   17 |
|       3 | Gavin  |    9 |
|       4 | Duncan |    6 |
|       5 | Emma   |    4 |
|       6 | Alex   |   15 |
|       7 | Adrian |    9 |
+---------+--------+------+
6 rows in set (0.00 sec)

mysql>

4. change user password:

[root@localhost lib]# mysql -u root --password=myrootpasswd mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.06 sec)

mysql> select host, user, password from user;

+---------------------------+------+-------------------------------------------+
| host                      | user | password                                  |
+---------------------------+------+-------------------------------------------+
| localhost                 | root | *0E6A839B8FF3817A463ED960F7F8E56B1BDC8E4B |
| localhost.localdomain     | root |                                           |
| 127.0.0.1                 | root |                                           |
| ::1                       | root |                                           |
| localhost                 |      |                                           |
| localhost.localdomain     |      |                                           |
| localhost                 | rick | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
| 192.168.0.0/255.255.255.0 | rick | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
| %.wiley.com               | rick | *6F2192D95FC8369B70A989445154CD58E7D34C7C |
+---------------------------+------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql>

mysql> update user set password = password('secret') where user = 'rick';

Query OK, 3 rows affected (0.10 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select host, user, password from user;

+---------------------------+------+-------------------------------------------+
| host                      | user | password                                  |
+---------------------------+------+-------------------------------------------+
| localhost                 | root | *0E6A839B8FF3817A463ED960F7F8E56B1BDC8E4B |
| localhost.localdomain     | root |                                           |
| 127.0.0.1                 | root |                                           |
| ::1                       | root |                                           |
| localhost                 |      |                                           |
| localhost.localdomain     |      |                                           |
| localhost                 | rick | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
| 192.168.0.0/255.255.255.0 | rick | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
| %.wiley.com               | rick | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
+---------------------------+------+-------------------------------------------+
9 rows in set (0.00 sec)

mysql>

elso: mysqladmin -u root -p oldpassword newpasswd
[root@localhost lib]# mysqladmin -u root -p nopasswd secret

then you must restart mysql server to make the new settings work:

[root@localhost lib]# /etc/init.d/mysql.server restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@localhost lib]#

[zhang@localhost ch08]$ mysql -u rick --password=secret foo

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye
[zhang@localhost ch08]$

5. try access Mysql via C language API:

//TODO