MySQL数据库基本操作与简单管理

语句	代表的意思
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等
DML	数据操纵语言，用于对表中的数据进行管理
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限

DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

软件开发：CRUD

DQL：Data Query Language 数据查询语言

SELECT

DCL：Data Control Language 数据控制语言

GRANT，REVOKE

TCL：Transaction Control Language 事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

5.查看帮助信息

#help后面跟上具体命令可以查看帮助
例如：mysql> help create

6.查看、修改字符集

1. #查看支持字符集
show charset;
默认拉丁文字，需要修改为

utf8     | UTF-8 Unicode   
#阉割版的

utf8mb4  | UTF-8 Unicode 
#真实的版本

2. #修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

二.管理MySQL数据库

1.查看数据库结构

1.1 查看当前服务器中的数据库

show databases;					#大小写不区分，分号“;”表示结束

1.2 查看数据库中包含的表

方法一：在库中查看表
use mysql;             #use 数据库名
show tables;           #查看表

法二 ：在库外查看表
show tables from mysql;    #show tables from 数据库名

1.3 查看表的结构（字段）

use 数据库名;
describe [数据库名.]表名;
可缩写成：desc 表名;

1.4 查看mysql版本

mysql -V

2.数据类型

2.1 常用数据类型

类型	说明
int	整型，用于定义整数类型的数据
fload	单精度浮点4字节32位，准确表示到小数点后六位
double	双精度浮点8字节64位
char	固定长度的字符类型，用于定义字符类型数据。
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位。指定长度数组

char

char的长度是不可变。char如果存入数据的实际长度比指定长度要小会补空格至指定长度如果存入的数据的实际长度大于指定长度，低版本会被截取高版本会报错。

varchar

varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

2.2 数据类型参考

参考链接：

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL，包含为NULL的列，对MySQL更难优化

2.3 整数型

tinyint(m) 1个字节范围(-128~127)

smallint(m) 2个字节范围(-32768~32767)

mediumint(m) 3个字节范围(-8388608~8388607)

int(m) 4个字节范围(-2147483648~2147483647)

bigint(m) 8个字节范围(+-9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

2.4 浮点型 (float 和 double) ，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

2.5 定点数

在数据库中存放的是精确值,存为十进制

格式 decimal(m,d) 表示最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时

2.6 字符串

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

3.创建、删除数据库和表

3.1 创建新的数据库（create database）

#建立数据库 
create database 数据库名;

#建立数据库并指定字符集utf8mb4
create database 数据库名 charset=utf8mb4;
后文有详细操作及其演示

3.2 删除指定的数据库（drop database）

drop database 数据库名;

3.3 创建新的表（create table）

use 数据库名;
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

例如：
use zhuzi;
create table students (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );

#unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M

3.4 删除指定的数据表（drop table）

方法一：库内删除
use 数据库名；
drop table 数据表名;

方法二：库外删除
#如不用USE进入库中，则需加上数据库名
drop table 数据库名.数据表名;

4.管理表中的数据记录

4.1 向数据表中插入新的数据记录（insert into）

insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);

例如：
insert into students(id,name,age,passwd) values(2,'lisi',20,'112233' );
insert into students(id,name,age,passwd) values(2,'jiami',22,PASSWORD('123456'));
#PASSWORD('112233')：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示
select * from students;

提前新建数据表（注意长度设置）
use mysql;
create table students (id smallint unsigned primary key auto_increment, name varchar(10),age tinyint unsigned, passwd varchar(48));

此处添加数据为中文，注意字符集（默认为拉丁文）是否更改为utf8和utf8mb4

如果/etc/my.cnf没有更改，建立数据库时要指定字符集utf8mb4：
create database 数据库名 charset=utf8mb4;

4.2 查询数据表内数据记录（select）

select 字段名1,字段名2[,...] from 表名 [where 条件表达式];

基本操作：
1. #查询全部数据
select * from 数据表名;
例如：
select * from students;

2. #查询指定数据
例如：
select id,name from students where id=2;

更多操作：
3. #以列表方式竖向显示
select * from 数据表名\G;			
例如：
select * from students\G;

4. #只显示头2行
select * from 数据表名 limit 2;
例如：	
select * from students limit 2;

5. #显示第3行后的2行
select * from 数据表名 limit 3,2;
例如：
select * from students limit 3,2;

4.3 修改、更新数据表中的数据记录（update）

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];

例如：
update students set age=19 where id=4;
update students set age=20,passwd='111111' where id=2;    #同时修改age和passwd

4.4 在数据表中删除指定的数据记录

delete from 表名 [where 条件表达式];

例如：
delete from students where id=2;

！！！！注意id并不会顶上去！！！！

5.修改表名和表结构（alter table）

5.1 修改表名（rename）

alter table 旧表名 rename 新表名;

例如：
alter table students rename xuesheng;

5.2 扩展表结构，增加字段（add）

alter table 表名 add 字段;

例如：
alter table xuesheng add address varchar(50) default 'wait get';
#default 'wait get'：表示此字段设置默认值 wait get；可与 NOT NULL 配合使用

5.3 修改字段（列）名，添加唯一键（change）

alter table 表名 change 旧列名 新列名 数据类型 [unique key];

例如：
alter table xuesheng change name user_name char(30) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。
验证：
insert into xuesheng values(6,lili,23,234567,NANKING);
失败，因为设置了唯一键，lili不能重复，改成lulu即可
insert into xuesheng values(6,lulu,23,234567,NANKING);

5.4 删除字段（drop）

alter table 表名 drop 字段名;

例如：
#删除表中passwd字段
alter table xuesheng drop passwd;

6.查看、修改字符集（show）

6.1 查看MySQL支持的所有字符集

show charset;

6.2 查看数据库字符集

show create database 数据库名\G

例如：
#查看mysql数据库中mysql库的字符集
show create database mysql\G

6.3 查看数据表的字符集

show table status from 库名 like '表名';

例如：
#查看zhuzi数据库中students表的字符集
show table status from zhuzi like 'students';

6.4 修改数据库字符集（character set）

#建立数据库并指定字符集utf8mb4
create database 数据库名 charset=utf8mb4;

#若数据库已建立，修改字符集
alter database 数据库名 character set 字符集;
例如：
alter database mysql character set utf8mb4;