事物的难度远远低于对事物的恐惧
数据库的内部存储结构主要分为数据库、数据表和数据,之前已经说明了针对数据库和数据表的操作指令,本文将对数据如何操作进行说明。
1.查看所有数据
指令如下:
select * from 数据表名称;
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from student_info;
Empty set (0.00 sec)
此时这个数据表里面还没有任何数据,所有显示为空。
2.插入数据
- 插入完整数据
指令如下:
insert into 数据表名称 (字段1名称,字段2名称,……) values (字段1值,字段2值,……)
mysql> select * from student_info;
Empty set (0.00 sec)
mysql> insert into student_info (_id,name,score,address) values (1,"zhangsan",88.6,"china");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
+------+----------+-------+---------+
1 row in set (0.00 sec)
上面代码中可以看出已经成功插入了一条完整的数据。
还有一种方式:
如果插入的数据的完整的,可以省略字段名,指令如下:
insert into 数据表名称 values (字段值);
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
+------+----------+-------+---------+
1 row in set (0.00 sec)
mysql> insert into student_info values (2,"wanger",66.6,"Beijing");
Query OK, 1 row affected (0.01 sec)
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
| 2 | wanger | 66.6 | Beijing |
+------+----------+-------+---------+
2 rows in set (0.00 sec)
成功插入了一条完整的数据。
- 插入部分数据
指令如下:
insert into 数据表名称 (部分字段名称) values (字段对应的值);
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
+------+----------+-------+---------+
1 row in set (0.00 sec)
mysql> insert into student_info (_id,name) values (2,"lisi");
Query OK, 1 row affected (0.01 sec)
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
| 2 | lisi | NULL | NULL |
+------+----------+-------+---------+
2 rows in set (0.00 sec)
上面的代码成功插入了一条包含部分数据的信息,没有指定值的字段显示null;
- 插入中文
如果按照我之前的文章 MySQL数据库8.0.23 新手详细安装步骤 设置的my.ini配置文件,是可以正常插入中文的数据。
如果插入中文数据出现报错的话,大多数原因是因为编码格式不对应,windows的命令提示符窗口的编码格式是gbk格式的,然而数据库的编码格式是utf8格式,所以会报错,此时把my.ini配置文件中默认的数据库的编码格式改成gbk的就可以了。
3.修改数据,即数据进行更新
指令如下:
update 数据表名称 set 字段名称=新的字段值 where (条件);
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 88.6 | china |
| 2 | wanger | 66.6 | Beijing |
+------+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> update student_info set score=99.8 where _id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 99.8 | china |
| 2 | wanger | 66.6 | Beijing |
+------+----------+-------+---------+
2 rows in set (0.00 sec)
成功的将张三的分数改成了99.8。
4.删除数据
指令如下:
delete from 数据表名称 where (条件);
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 99.8 | china |
| 2 | wanger | 66.6 | Beijing |
+------+----------+-------+---------+
2 rows in set (0.00 sec)
mysql> delete from student_info where _id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_info;
+------+----------+-------+---------+
| _id | name | score | address |
+------+----------+-------+---------+
| 1 | zhangsan | 99.8 | china |
+------+----------+-------+---------+
1 row in set (0.00 sec)
上面的代码将第二行数据删除了。
5.条件
指令如下:
> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 不等于
is null(不可以写成=null) 为空
is not null 不为空
and 逻辑与
or 逻辑或
not 逻辑非
between A and B 在A和B之间
like 模糊查找
6.数据表中数据的详细查找
- 查找全部
指令如下:
select * from 数据表名称;
mysql> select * from student_info;
+------+-----------+-------+-----------+
| _id | name | score | address |
+------+-----------+-------+-----------+
| 1 | zhangsan | 99.8 | china |
| 2 | 张三 | 25 | 西安 |
| 3 | 王二 | 63 | 兰州 |
| 4 | 李四 | 44 | 天津 |
| 5 | 赵云 | 89 | 石家庄 |
| 6 | 火舞 | 57 | 沈阳 |
| 7 | 虞姬 | 89 | 太原 |
| 8 | 霸王 | 88 | 银川 |
| 9 | 艾琳 | 32 | 西宁 |
| 10 | 凯 | 15 | 济南 |
| 11 | 程咬金 | 96 | 郑州 |
| 12 | 兰陵王 | 66 | 成都 |
+------+-----------+-------+-----------+
12 rows in set (0.00 sec)
- 查找符合条件的某一条数据
指令如下:
select * from 数据表名称 where (条件);
mysql> select * from student_info where score=89;
+------+--------+-------+-----------+
| _id | name | score | address |
+------+--------+-------+-----------+
| 5 | 赵云 | 89 | 石家庄 |
| 7 | 虞姬 | 89 | 太原 |
+------+--------+-------+-----------+
2 rows in set (0.00 sec)
- 查找符合条件的某一条数据的部分
指令如下:
select 字段名称,字段名称…… from 数据表名称 where (条件);
mysql> select name,address from student_info where score=89;
+--------+-----------+
| name | address |
+--------+-----------+
| 赵云 | 石家庄 |
| 虞姬 | 太原 |
+--------+-----------+
2 rows in set (0.00 sec)
- 按升序或者降序查看全部或部分数据
指令如下:
select 字段名称,字段名称…… from 数据名称 order by 字段名称 asc(升序)| desc(降序);
mysql> select * from student_info order by score asc;
+------+-----------+-------+-----------+
| _id | name | score | address |
+------+-----------+-------+-----------+
| 10 | 凯 | 15 | 济南 |
| 2 | 张三 | 25 | 西安 |
| 9 | 艾琳 | 32 | 西宁 |
| 4 | 李四 | 44 | 天津 |
| 6 | 火舞 | 57 | 沈阳 |
| 3 | 王二 | 63 | 兰州 |
| 12 | 兰陵王 | 66 | 成都 |
| 8 | 霸王 | 88 | 银川 |
| 5 | 赵云 | 89 | 石家庄 |
| 7 | 虞姬 | 89 | 太原 |
| 11 | 程咬金 | 96 | 郑州 |
| 1 | zhangsan | 99.8 | china |
+------+-----------+-------+-----------+
12 rows in set (0.00 sec)
mysql> select name,address from student_info order by score asc;
+-----------+-----------+
| name | address |
+-----------+-----------+
| 凯 | 济南 |
| 张三 | 西安 |
| 艾琳 | 西宁 |
| 李四 | 天津 |
| 火舞 | 沈阳 |
| 王二 | 兰州 |
| 兰陵王 | 成都 |
| 霸王 | 银川 |
| 赵云 | 石家庄 |
| 虞姬 | 太原 |
| 程咬金 | 郑州 |
| zhangsan | china |
+-----------+-----------+
12 rows in set (0.00 sec)
- 用别名替换字段名称
指令如下:
select 字段名称 as 别名,字段名称 as 别名…… from 数据表名称 where (条件);
mysql> select name as "名字",address as "地址" from student_info where score>60;
+-----------+-----------+
| 名字 | 地址 |
+-----------+-----------+
| zhangsan | china |
| 王二 | 兰州 |
| 赵云 | 石家庄 |
| 虞姬 | 太原 |
| 霸王 | 银川 |
| 程咬金 | 郑州 |
| 兰陵王 | 成都 |
+-----------+-----------+
7 rows in set (0.00 sec)
7.模糊查找 like
指令如下:
select * from 数据表名称 where (条件);
例子1:查找分数为8开头的数据
mysql> select * from student_info where score like "8%";
+------+--------+-------+-----------+
| _id | name | score | address |
+------+--------+-------+-----------+
| 5 | 赵云 | 89 | 石家庄 |
| 7 | 虞姬 | 89 | 太原 |
| 8 | 霸王 | 88 | 银川 |
+------+--------+-------+-----------+
3 rows in set (0.00 sec)
其中百分号%是通配符。
例子2:查找name为3个字的数据。
mysql> select * from student_info where name like "___";
+------+-----------+-------+---------+
| _id | name | score | address |
+------+-----------+-------+---------+
| 11 | 程咬金 | 96 | 郑州 |
| 12 | 兰陵王 | 66 | 成都 |
+------+-----------+-------+---------+
2 rows in set (0.00 sec)
其中下划线_ 是占位符。
8.函数
- count 统计个数
指令如下:
select count(字段名称) from 数据表名称 where (条件);
mysql> select score,count(score) from student_info where score=89;
+-------+--------------+
| score | count(score) |
+-------+--------------+
| 89 | 2 |
+-------+--------------+
1 row in set (0.00 sec)
- sum 求和
指令如下:
select sum(字段名称) from 数据表名称 where (条件);
mysql> select sum(score) from student_info where _id>5;
+------------+
| sum(score) |
+------------+
| 443 |
+------------+
1 row in set (0.00 sec)
- average 求平均值
指令如下:
select avg(字段名称) from 数据表名称 where (条件);
mysql> select avg(score) from student_info where _id>2;
+------------+
| avg(score) |
+------------+
| 63.9 |
+------------+
1 row in set (0.00 sec)
- max min 最大值 最小值
指令如下:
select max(字段名称)| min(字段名称) from 数据表名称 where (条件);
mysql> select max(score) from student_info where _id<10;
+------------+
| max(score) |
+------------+
| 99.8 |
+------------+
1 row in set (0.00 sec)
mysql> select min(score) from student_info where _id<10;
+------------+
| min(score) |
+------------+
| 25 |
+------------+
1 row in set (0.00 sec)
9.数据库的备份和恢复
首先执行指令exit退出数据库,然后进行操作。
- 备份
D:\software\mysql-8.0.23-winx64\bin>mysqldump -hlocalhost -uroot -p student > D:\备份\123.sql
Enter password: ******
- 恢复
D:\software\mysql-8.0.23-winx64\bin>mysql -uroot -p person < D:\备份\123.sql
Enter password: ******
D:\software\mysql-8.0.23-winx64\bin>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| person |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use person;
Database changed
mysql> show tables;
+------------------+
| Tables_in_person |
+------------------+
| student_info |
| temp |
+------------------+
2 rows in set (0.00 sec)
用备份和恢复数据库的方法,可以实现更改数据库名称的目的。
MySQL数据库的基础学习 完结!
可以参考下方列出的我的其他MySQL文章: