MySQL数据库——数据库的基本操作

目录

三、数据库的基本操作

1.数据库中库的操作

①创建数据库

②字符集和校验规则

③操纵数据库

④备份与恢复

2.数据库中表的操作

①创建表

②查看表

1> 查看表位于的数据库

2>查看所有表

3>查看表中的数据

4>查看创建表的时候的详细信息

③修改表

1>修改表的名字

2>修改表的内容，插入数据

3>修改表的Type属性

4>修改表的Field属性

5>删除表中某一列

④删除表

---

 

三、数据库的基本操作

1.数据库中库的操作

①创建数据库

CREATE DATABASE database_name; -- 最基本的写法CREATE [IF NOT EXISTS] DATABASE data
CHARACTER SET 'utf8mb4' -- 指定字符集
COLLATE 'utf8mb4_unicode_ci'; -- 指定校验规则[IF NOT EXISTS] -- 可选项，表示如果不存在就创建
说明：当我们创建数据库没有指定字符集和校验规则时
系统使用默认字符集：u8
校验规则是：utf8_general_ci创建一个使用utf8字符集的db2数据库
create database db2 charset=utf8;
创建一个使用utf8字符集，并带校对规则的db3数据库。
create database db3 charset=utf8 collate utf8_general_ci;

②字符集和校验规则

 -- 查看系统默认字符集以及校验规则
show variables like 'character_set_database';
show variables like 'collation_database';-- 查看数据库支持的字符集
show charset;-- 查看数据库支持的字符集校验规则
show collation;

校验规则对数据库的影响：

• 使用utf8_general_ci校验规则

mysql> create database test1 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)mysql> use test1;
Database changed
mysql> create table person(name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql> insert into person values('a');
Query OK, 1 row affected (0.00 sec)mysql> insert into person values('A');
Query OK, 1 row affected (0.00 sec)mysql> insert into person values('b');
Query OK, 1 row affected (0.01 sec)mysql> insert into person values('B');
Query OK, 1 row affected (0.01 sec)

• 使用utf8_bin校验规则

mysql> create database test2 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)mysql> use test2;
Database changed
mysql> create table person(name varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql> insert into person values('a');
Query OK, 1 row affected (0.00 sec)mysql> insert into person values('A');
Query OK, 1 row affected (0.00 sec)mysql> insert into person values('b');
Query OK, 1 row affected (0.01 sec)mysql> insert into person values('B');
Query OK, 1 row affected (0.00 sec)

• 查询

-- utf8_general_ci
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)-- utf8_bin
mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from person where name='a';
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

• 结果排序

-- utf8_general_ci
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from person order by name;
+------+
| name |
+------+
| a    |
| A    |
| b    |
| B    |
+------+
4 rows in set (0.00 sec)-- utf8_bin
mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from person order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| b    |
+------+
4 rows in set (0.00 sec)

-- utf8_general_ci 不区分大小写

-- utf8_bin 区分大小写

不同的校验规则在进行数据库操作的时候会产生不同的结果

③操纵数据库

-- 查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bookstore          |
| helloworld         |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
8 rows in set (0.00 sec)-- 显示创建数据库语句
mysql> show create database test1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)-- 修改数据库，将 mytest 数据库字符集改成 gbk
mysql> alter database test1 charset=gbk;
Query OK, 1 row affected (0.00 sec)mysql> show create database test1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)-- 删除数据库
mysql> DROP database test1;
Query OK, 1 row affected (0.01 sec)

④备份与恢复

1. 备份

mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径-- 示例：将mytest库备份到文件（退出连接）
mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql
-- 这时，可以打开看看 mytest.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句
都装载这个文件中。

2. 还原

source 备份数据库的路径
mysql> source D:/mysql-5.7.22/mytest.sql;

3. 备份一张表

mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

4. 同时备份多个数据库

mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

如果备份一个数据库时，没有带上-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

5. 查看数据库连接情况

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 15 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

2.数据库中表的操作

①创建表

mysql> create database user_db;
Query OK, 1 row affected (0.00 sec)mysql> use user_db;
Database changed-- 两张表使用不同的存储引擎，user1使用MyIsam，user2使用InnoDB
mysql> create table user1(-> id int,-> name varchar(20) comment '用户名',-> password char(32) comment '用户密码',-> birthday date comment '用户生日'-> )character set utf8 collate utf8_general_ci engine MyIsam;
Query OK, 0 rows affected (0.00 sec)mysql> create table user2(-> id int,-> name varchar(20) comment '用户名',-> password char(32) comment '用户密码',-> birthday date comment '用户生日'-> )character set utf8 collate utf8_general_ci engine InnoDB;
Query OK, 0 rows affected (0.01 sec)

 

因为使用不同的存储引擎，所以表结构不同

如果直接创建而不制定存储引擎：

mysql> create table user3(name char(32));
Query OK, 0 rows affected (0.02 sec)

 

因为在配置mysql的时候配置了默认的存储引擎（vim /etc/my.cnf查看配置）

②查看表

1> 查看表位于的数据库

-- 查看当前位于的数据库
mysql> select database();
+------------+
| database() |
+------------+
| user_db    |
+------------+
1 row in set (0.00 sec)

2>查看所有表

-- 查看所有表
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user1             |
| user2             |
| user3             |
+-------------------+
3 rows in set (0.00 sec)

3>查看表中的数据

-- 查看表中的数据
mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> desc user3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(32) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

4>查看创建表的时候的详细信息

-- 查看创建表的时候的详细信息
mysql> show create table user1 \G
*************************** 1. row ***************************Table: user1
Create Table: CREATE TABLE `user1` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL COMMENT '用户名',`password` char(32) DEFAULT NULL COMMENT '用户密码',`birthday` date DEFAULT NULL COMMENT '用户生日'
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

③修改表

1>修改表的名字

-- 显示当前表信息
mysql> desc user1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)-- 更改表的名字
mysql> alter table user1 rename to user;
Query OK, 0 rows affected (0.00 sec)-- 显示更改后的表的名字
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user              |
+-------------------+
1 row in set (0.00 sec)

2>修改表的内容，插入数据

-- 显示表的信息
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)-- 向表中插入第一条数据
mysql> insert into user values(1,'张三','12345','2010-10-1');
Query OK, 1 row affected (0.00 sec)-- 向表中插入第二条数据
mysql> insert into user values(1,'李四','23456','2015-1-1');
Query OK, 1 row affected (0.00 sec)-- 查询表中所有内容
mysql> select * from user;
+------+--------+----------+------------+
| id   | name   | password | birthday   |
+------+--------+----------+------------+
|    1 | 张三   | 12345    | 2010-10-01 |
|    1 | 李四   | 23456    | 2015-01-01 |
+------+--------+----------+------------+
2 rows in set (0.00 sec)-- 新增一列，且在birthday一列之后
mysql> alter table user add image_path varchar(128) comment '用户的头像路径' after birthday;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from user;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    1 | 李四   | 23456    | 2015-01-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)

3>修改表的Type属性

-- 当前表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(20)  | YES  |     | NULL    |       |
| password   | char(32)     | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)-- 修改某一属性，如name的Type
mysql> alter table user modify name varchar(60);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 查看修改后的表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| password   | char(32)     | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4>修改表的Field属性

-- 当前表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)-- 修改某一属性，如name改为xingming
mysql> alter table user change name xingming varchar(60) DEFAULT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0-- 查看修改后的表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| xingming   | varchar(60)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5>删除表中某一列

-- 查看当前表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| password   | char(32)     | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)-- 查看当前表的内容
mysql> select * from user;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 12345    | 2010-10-01 | NULL       |
|    1 | 李四   | 23456    | 2015-01-01 | NULL       |
+------+--------+----------+------------+------------+
2 rows in set (0.00 sec)-- 修改表，删除其中的password列
mysql> alter table user drop password;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0-- 查看修改后的表的属性
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| name       | varchar(60)  | YES  |     | NULL    |       |
| birthday   | date         | YES  |     | NULL    |       |
| image_path | varchar(128) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)-- 查看修改后的表的内容
mysql> select * from user;
+------+--------+------------+------------+
| id   | name   | birthday   | image_path |
+------+--------+------------+------------+
|    1 | 张三   | 2010-10-01 | NULL       |
|    1 | 李四   | 2015-01-01 | NULL       |
+------+--------+------------+------------+
2 rows in set (0.00 sec)

④删除表

-- 显示所有的表
mysql> show tables;
+-------------------+
| Tables_in_user_db |
+-------------------+
| user              |
+-------------------+
1 row in set (0.00 sec)-- 删除表
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)-- 显示更改后所有的表
mysql> show tables;
Empty set (0.00 sec)