首页
运维教程
Linux基础
系统服务
系统架构
数据库
shell脚本
虚拟化
大数据
DevOps
企业案例
运维开发
python
go语言
运维安全
行业资讯
网络基础
系统安全
运维面试
学习路线
学习方法
面试题库
职场解惑
软件
运维软件
办公软件
书籍资源
技术陪跑营
重要信息
首页 运维教程数据库【MySQL】MySQL数据库的基本操作一

【MySQL】MySQL数据库的基本操作一

一. 数据库操作

1. 查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zmedu              |
+--------------------+
5 rows in set

注意:
– MySQL数据库指令不区分大小写,操作真正的表和库的时候才区分大小写
– 任何命令都以;结束

2. 创建数据库
mysql> create database zmgaosh;
Query OK, 1 row affected

每创建一个数据库都会在data下创建同名的文件夹,一个数据库说到底就是个文件夹
在这里插入图片描述
查看数据库,发现zmgaosh已经创建好了;
在这里插入图片描述

3. 选择数据库

select database(); 查看当前在哪个库
use zmgaosh ; 切换到zmgaosh

mysql> select database();
+------------+
| database() |
+------------+
| zmedu      |
+------------+
1 row in set

mysql> use zmgaosh;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| zmgaosh    |
+------------+
1 row in set

mysql>
4. 删除数据库

drop 删除数据库命令

mysql> drop database zmgaosh;
Query OK, 0 rows affected

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zmedu              |
+--------------------+
5 rows in set

mysql> 

注意: drop命令将删除指定数据库的所有的表,而且没有任何提示;如果你到data目录下查看,会发现没有zmgaosh这个文件夹了。
#### 二. 数据库表的操作

1. 查看表
mysql> use zmedu;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_zmedu |
+-----------------+
| t               |
+-----------------+
1 row in set

mysql> 

zmedu数据库里我创建了一个t表,所以使用show就可以看到,zmedu库中只有一个表t。

2. 创建表

语法:create table 表名 (字段名 类型, 字段名 类型, 字段名 类型);

mysql> create table hero(id int(20),name char(40),level int(2));
Query OK, 0 rows affected

mysql> show tables;
+-----------------+
| Tables_in_zmedu |
+-----------------+
| hero            |
| t               |
+-----------------+
2 rows in set
3. 查看表结构的表述
mysql> desc hero;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
| level | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set

可以看到有三个字段,id,name 和level,他们的类型

如果想查看创建表时候所执行的命令:

mysql> show create table hero;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| hero  | CREATE TABLE `hero` (
  `id` int DEFAULT NULL,
  `name` char(40) DEFAULT NULL,
  `level` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> 

在MySQL8里,创建表的默认 编码是UTF8

4. 删除表
mysql> create table hero1(id int(20),name char(40),level int(2));
Query OK, 0 rows affected

mysql> show tables;
+-----------------+
| Tables_in_zmedu |
+-----------------+
| hero            |
| hero1           |
| t               |
+-----------------+
3 rows in set

mysql> drop table hero1;
Query OK, 0 rows affected

mysql> show tables;
+-----------------+
| Tables_in_zmedu |
+-----------------+
| hero            |
| t               |
+-----------------+
2 rows in set
5. 插入记录

使用insert into插入数据, insert into ..values

mysql> insert into hero values(1,'zmgaosh',20);
Query OK, 1 row affected

mysql> insert into hero(id,name) values(2,'gaosh
');
Query OK, 1 row affected
6. 查看表记录
mysql> select * from hero;
+----+---------+-------+
| id | name    | level |
+----+---------+-------+
|  1 | zmgaosh |    20 |
|  2 | gaosh   | NULL  |
+----+---------+-------+
2 rows in set

只查某一个字段

mysql> select id from hero;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set

mysql> 

字段的操作

1. 修改字段类型

查看一下原来的字段结构

mysql> desc hero;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
| level | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set

可以看到有id,name ,level 类型

我们现在把name的char类型改为varchar;

mysql> alter table hero modify name varchar(4
0);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hero;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
| level | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set
2. 修改字段名称和类型

语法: alter table 表名 change 原字段名 新字段名 新字段类型

mysql> alter table hero change name heroname char(40);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc hero;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int      | YES  |     | NULL    |       |
| heroname | char(40) | YES  |     | NULL    |       |
| level    | int      | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set
3. 添加字段

语法: alter table 表名 add 字段 类型

mysql> alter table hero add team enum('A','B
');
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc hero;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int           | YES  |     | NULL    |       |
| heroname | char(40)      | YES  |     | NULL    |       |
| level    | int           | YES  |     | NULL    |       |
| team     | enum('A','B') | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set

mysql> 
4. 删除字段

语法:alter table 表名 drop 字段名 ;

mysql> desc hero;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int           | YES  |     | NULL    |       |
| heroname | char(40)      | YES  |     | NULL    |       |
| level    | int           | YES  |     | NULL    |       |
| team     | enum('A','B') | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set

mysql> alter table hero drop team;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc hero;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int      | YES  |     | NULL    |       |
| heroname | char(40) | YES  |     | NULL    |       |
| level    | int      | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set

5. 删除行

删除id=2的行

mysql> select * from hero;
+----+----------+-------+
| id | heroname | level |
+----+----------+-------+
|  1 | zmgaosh  |    20 |
|  2 | gaosh    | NULL  |
+----+----------+-------+
2 rows in set

mysql> delete from hero where id=2;
Query OK, 1 row affected

mysql> select * from hero;
+----+----------+-------+
| id | heroname | level |
+----+----------+-------+
|  1 | zmgaosh  |    20 |
+----+----------+-------+
1 row in set

6. 更新

更新id1 变成22

mysql> select * from hero;
+----+----------+-------+
| id | heroname | level |
+----+----------+-------+
|  1 | zmgaosh  |    20 |
+----+----------+-------+
1 row in set

mysql> update hero set id=22;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from hero;
+----+----------+-------+
| id | heroname | level |
+----+----------+-------+
| 22 | zmgaosh  |    20 |
+----+----------+-------+
1 row in set

总结

数据库和数据库表的增删改查是一个运维必会的,如果连这都不会,就放弃运维了。

本文链接:http://www.geekyunwei.com/1175.html

网友评论comments

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

暂无评论

Copyright © 2021 极客运维 公众号《极客运维之家》
扫二维码
扫二维码
返回顶部