目录
数据库的基本操作主要包括:数据库操作、数据表操作、数据操作;
数据库操作
1、创建数据库
mysql> create database cake;
Query OK, 1 row affected (0.00 sec)
2、使用数据库
在数据库中建立相应的数据表之前,需要使用数据库。
mysql> use cake;
Database changed
3、查看数据库
3.1查看MySQL中的所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| cake |
| information_schema |
| january |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
3.2查看指定的数据库
//以查看新建的数据库cake为例
mysql> show create database cake;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| cake | CREATE DATABASE `cake` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、删除数据库
mysql> drop database cake;
Query OK, 0 rows affected (0.01 sec)
数据表操作
对于数据表的操作是在选择数据库后进行的操作,因此进行数据表操作之前我们需要选择数据库。
1、创建数据表
//创建每一个字段时,需要说明字段名、字段类型、字段约束,对于字段的说明注释可以根据个人的需求进行选择
mysql> create table student(
-> id int primary key not null comment'学号',
-> name char(10) comment'姓名',
-> sex enum('男','女') comment'性别'
-> );
Query OK, 0 rows affected (0.06 sec)
2、查看数据表
2.1查看所有数据表的表内容
mysql> show tables;
+----------------+
| Tables_in_csdn |
+----------------+
| student |
+----------------+
1 row in set (0.04 sec)
2.2查看指定数据表的表内容
mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int NOT NULL,
`name` char(10) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
2.3查看数据表的表结构
mysql> desc student;
+-------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| sex | enum('男','女') | YES | | NULL | |
+-------+-------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
3、修改表数据
3.1 修改数据表名
//修改数据表名的方式1
mysql> alter table student rename information;
Query OK, 0 rows affected (0.06 sec)
//修改数据表名的方式2
mysql> rename table information to student;
Query OK, 0 rows affected (0.04 sec)
3.2 修改字段类型
//修改数据类型的命令
mysql> alter table student modify name char(6);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
//查看修改结果
mysql> desc student;
+-------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | char(6) | YES | | NULL | |
| sex | enum('男','女') | YES | | NULL | |
+-------+-------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
3.3 修改字段名
注意在修改字段名时,即使字段类型并未进行修改,也要说明修改后的字段类型
mysql> alter table student change id number int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.4 修改字段位置
//将sex的位置放在数据表的第一行
mysql> alter table student modify sex enum('男','女') first;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| sex | enum('男','女') | YES | | NULL | |
| number | int | NO | PRI | NULL | |
| name | char(6) | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3.5 新增字段
mysql> alter table student add class int after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| sex | enum('男','女') | YES | | NULL | |
| number | int | NO | PRI | NULL | |
| name | char(6) | YES | | NULL | |
| class | int | YES | | NULL | |
+--------+-------------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
3.6 删除字段
mysql> alter table student drop sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
数据操作
1、添加数据
添加数据可以分为添加一条数据、添加多条数据、导入数据文件,我们先介绍导入单条、多条数据。导入多条数据和导入多条数据相似,只要在各条数据之间添加逗号即可。
mysql> insert into student values(1,'丽丽',1),(2,'愉悦',2);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
2、查询数据
//使用通配符* 查询数据表中的全部数据
mysql> select * from student;
+--------+--------+-------+
| number | name | class |
+--------+--------+-------+
| 1 | 丽丽 | 1 |
| 2 | 愉悦 | 2 |
+--------+--------+-------+
2 rows in set (0.00 sec)
3、简单条件查询
简单条件查询可以使用where实现
mysql> select * from student where number=1;
+--------+--------+-------+
| number | name | class |
+--------+--------+-------+
| 1 | 丽丽 | 1 |
+--------+--------+-------+
1 row in set (0.00 sec)
4、修改数据
修改数据通过更新数据命令update关键字实现
mysql> update student set name='一口吞掉小蛋糕' where number=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student where number=1;
+--------+-----------------------+-------+
| number | name | class |
+--------+-----------------------+-------+
| 1 | 一口吞掉小蛋糕 | 1 |
+--------+-----------------------+-------+
1 row in set (0.00 sec)
5、删除数据
删除数据是对数据表中的数据进行删除操作,并不删除数据表。删除数据的操作通过关键字delete实现。
mysql> delete from student;
Query OK, 2 rows affected (0.04 sec)
mysql> select * from student;
Empty set (0.04 sec)
好啦好啦,关于数据库的基本操作我们就讲到这里,你学废了吗,我们下篇文章见,byebye!!