MySQL数据库基础命令及小案例

MySQL服务启动服务（Linux）：

systemctl start mysqld

MySQL服务启动服务（Windows）：

net start mysql

进入MySQL服务：

mysql -u root -p

效果如下：

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

退出MySQL服务：

exit;

查看MySQL当前状态（进入MySQL之后）：

status

效果如下：

mysql> status
--------------
mysql  Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		17
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.29 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			2 hours 18 min 0 sec

Threads: 2  Questions: 87  Slow queries: 0  Opens: 214  Flush tables: 3  Open tables: 133  Queries per second avg: 0.010
--------------

①：Connection id：链接id
②：Current database：当前数据库，这里没打开数据库，所以空
③：Current user：当前用户，root为超级用户
④：SSL：SSL证书
⑤：Using delimiter：结束符，默认以;结束
⑥：Server version：服务器版本
⑦：Server characterset：服务器字符集

查看所有数据库：

show databases;

效果如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

新建数据库ex：

create database ex;

效果如下：

mysql> create database ex;
Query OK, 1 row affected (0.00 sec)

查看所有数据库，效果如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ex                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

使用该数据库ex：

use ex;

效果如下：

mysql> use ex;
Database changed

删除数据库ex：

drop database ex;

效果如下：

mysql> drop database ex;
Query OK, 0 rows affected (0.01 sec)

提供查看数据库命令可以看到数据库ex删除了：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

下面继续创建和使用该数据库进行操作：

mysql> create database ex;
Query OK, 1 row affected (0.00 sec)

mysql> use ex;
Database changed

创建表curricula、表score_info和表std_info：

mysql> create table curricula
    -> (
    -> code char(2),
    -> Curlum varchar(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table score_info
    -> (
    -> nub char(10) not null,
    -> code char(2),
    -> score double(5,2)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table std_info
    -> (
    -> nub char(10) not null,
    -> name varchar(20),
    -> class char(8),
    -> sex char(2),
    -> primary key(nub)
    -> );
Query OK, 0 rows affected (0.01 sec)

通过show tables命令查看新建表情况：

mysql> show tables;
+--------------+
| Tables_in_ex |
+--------------+
| curricula    |
| score_info   |
| std_info     |
+--------------+
2 rows in set (0.00 sec)

通过desc score_info命令可以查看表结构：

mysql> desc score_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| nub   | char(10)    | NO   |     | NULL    |       |
| code  | char(2)     | YES  |     | NULL    |       |
| score | double(5,2) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

通过insert into curricula values命令来为表插入多个数据：

mysql> insert into curricula values
    -> ('eg', 'english'),
    -> ('mt', 'gaoshu'),
    -> ('vc', 'vc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

通过select * from curricula;命令查看表信息：

mysql> select * from curricula;
+------+---------+
| code | Curlum  |
+------+---------+
| eg   | english |
| mt   | gaoshu  |
| vc   | vc      |
+------+---------+
3 rows in set (0.00 sec)

更新操作：

update std_info set name='zhangjie' where nub = '100001';

效果如下：

mysql> update std_info set name='zhangjie' where nub = '100001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from std_info where nub = '100001';
+--------+----------+-------+------+
| nub    | name     | class | sex  |
+--------+----------+-------+------+
| 100001 | zhangjie | 9701  | m    |
+--------+----------+-------+------+
1 row in set (0.00 sec)

删除操作：

delete from std_info where nub = '100001';

效果如下：

mysql> delete from std_info where nub = '100001';
Query OK, 1 row affected (0.00 sec)

mysql> select * from std_info;
+--------+----------+-------+------+
| nub    | name     | class | sex  |
+--------+----------+-------+------+
| 100002 | lisi     | 9701  | m    |
| 100003 | wangfang | 9701  | f    |
+--------+----------+-------+------+
2 rows in set (0.00 sec)

小案例

新建数据库YGGL

mysql> create database YGGL;
Query OK, 1 row affected (0.01 sec)

mysql> use YGGL;
Database changed

创建表：

mysql> create table Employee
    -> (
    -> Employee_ID char(8) not null primary key,
    -> Name varchar(10) not null,
    -> Sex char(2),
    -> Brith date,
    -> Depart_ID char(3) not null,
    -> Phone char(12)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table Depart
    -> (
    -> Depart_ID char(3) not null primary key,
    -> Depart_Name varchar(10) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table Salary
    -> (
    -> Employee_ID char(8) not null primary key,
    -> InCome double not null,
    -> OutCome double not null
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+----------------+
| Tables_in_YGGL |
+----------------+
| Depart         |
| Employee       |
| Salary         |
+----------------+
3 rows in set (0.01 sec)