MySQL-数据库基本操作

系统数据库

• information_schema(虚拟库)
    ○ 用户表信息、列信息、权限信息、字符信息等
• performance_schema
    ○ 主要存储数据库服务器的性能参数
• mysql(授权库)
    ○ 主要存储系统用户的权限信息
• sys(优化库)
    ○ 主要存储数据库服务器的性能参数
• bgx(业务库)
    ○ 主要存放业务所需要的库和表

---

一、数据库的连接方式

使用mysql -u root -p可以连接数据库, 但这只是本地连接数据库的方式, 在生产很多情况下都是连接网络中某一个主机上的数据库

-P //指定连接远程数据库端口
-h //指定连接远程数据库地址
-u //指定连接远程数据库账户
-p //指定连接远程数据库密码

[root@liza ~]# mysql -h192.168.112.160 -p3306 -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

---

二、数据库的基本操作

1、查看数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.32    |
+-----------+
1 row in set (0.00 sec)

2、创建数据库

mysql> create database bgx_edu default character set utf8;
Query OK, 1 row affected (0.00 sec)

数据库名称严格区分大小写
数据库名称必须是唯一
数据库名称不允许使用数字
数据库名称不能使用关键字命名create select

3、查看当前的库内容

mysql> SHOW DATABASES;            ---执行命令不区分大小写
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bgx_edu            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

4、删除数据库

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

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

删除库下的表
mysql> drop table Bgx_edu.t1;

5、查询某个库的表

mysql> use bgx_edu;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_bgx_edu |
+-------------------+
| t1                |
+-------------------+
1 row in set (0.00 sec)

6、查看某张表的建表语句

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

---

三、数据库增删查改

在MySQL管理软件中, 可以通过SQL语句中的DML语言来实现数据的操作, 包括如下:

1. INSERT数据插入
2. UPDATE数据更新
3. DELETE数据删除

---

1、准备操作环境数据表

创建数据文件

mysql> create database bgx default character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use bgx;
Database changed
mysql> create table t1 (
    -> id int,
    -> name varchar(10),
    -> sex enum('man','woman'),
    -> age int
    -> );
Query OK, 0 rows affected (0.00 sec)

查看表字段

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| name  | varchar(10)         | YES  |     | NULL    |       |
| sex   | enum('man','woman') | YES  |     | NULL    |       |
| age   | int(11)             | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

2、插入数据INSERT语句

方法一：插入完整数据, 顺序插入: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);

mysql> insert into t1 (id,name,sex,age) values ("1","bgx","man","18");
Query OK, 1 row affected (0.01 sec)

方法二：插入完整数据, 推荐方式: INSERT INTO 表名 VALUES (值1,值2,值n);

mysql> insert into t1 values("2","bgx2","woman","10");
Query OK, 1 row affected (0.00 sec)

方法三：指定字段插入：INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);

mysql> insert into t1 values
    -> ("3","bgx4","man","18"),
    -> ("4","bgx5","man","18"),
    -> ("5","bgx6","woman","26");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看

mysql> select * from t1;
+------+------+-------+------+
| id   | name | sex   | age  |
+------+------+-------+------+
|    1 | bgx  | man   |   18 |
|    2 | bgx2 | woman |   10 |
| NULL | bgx3 | man   |   20 |
|    3 | bgx4 | man   |   18 |
|    4 | bgx5 | man   |   18 |
|    5 | bgx6 | woman |   26 |
+------+------+-------+------+
6 rows in set (0.00 sec)

---

3、更新数据UPDATE语句

语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件;

1. 查看需要修改的表的字段 desc
2. 查询对用的字段 select
3. 更新对应的表字段 update
4. 添加对应的where条件,精准修改

---

示例1: 将t1表中, name字段等于bgx1的改为update_bgx

mysql> update t1 set name="update_bgx" where name="bgx";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------------+-------+------+
| id   | name       | sex   | age  |
+------+------------+-------+------+
|    1 | update_bgx | man   |   18 |
|    2 | bgx2       | woman |   10 |
| NULL | bgx3       | man   |   20 |
|    3 | bgx4       | man   |   18 |
|    4 | bgx5       | man   |   18 |
|    5 | bgx6       | woman |   26 |
+------+------------+-------+------+
6 rows in set (0.00 sec)

示例2: 修改密码示例, 查看表字段内容

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         | *5F2035C0E578A25296968F8A3F33B8D9EFE1C8F0 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

更新字段

mysql> update mysql.user set
    -> authentication_string=password("Bgx123.com")
    -> where user='root' and host='localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

---

4、删除数据DE