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)