MySQL数据库基本操作

目录

一.mysql的数据类型

1.1.常用的数据库类型

1.2.重点了解：char与varchar的区别 

二.MySQL数据库基本操作

2.1.SQL语言规范

2.2.SQL语句分类

2.3.查看帮助信息

2.4.查看、修改字符集

2.5.数据库对象和命名

三.管理MySQL数据库

3.1.数据类型

1）常用数据类型

2）数据类型参考

3）整数型

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

5）定点数

6）字符串

3.2.查看数据库结构

1）查看库信息 

2）查看表信息 

3）查看表的字段结构 

4）查看MySQL版本

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

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

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

3）创建新的表 （create table）

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

3.4.管理表中的数据记录

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

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

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

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

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

1）修改表名（rename）

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

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

4）删除字段（drop）

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

1）查看MySQL支持的所有字符集

2）查看数据库字符集

3）查看数据表的字符集

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

---

一.mysql的数据类型

1.1.常用的数据库类型

1.2.重点了解：char与varchar的区别 

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

字节大小：

•     char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节。
•     varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

优劣比较：

• varchar比char节省磁盘空间。
• 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片

二.MySQL数据库基本操作

2.1.SQL语言规范

• 在数据库系统中，SQL 语句不区分大小写，建议用大写
• SQL语句可单行或多行书写，默认以 " ; " 结尾
• 关键词不能跨多行或简写
• 用空格和TAB 缩进来提高语句的可读性
• 子句通常位于独立行，便于编辑，提高可读性

2.2.SQL语句分类

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

• DDL: Data Defination Language 数据定义语言，用于创建数据库对象，用于数据库的操作，如库、表、索引等。eg：create、drop、alter。
• DML: Data Manipulation Language 数据操纵语言，用于对表中的数据进行管理，用来插入、删除、修改数据库中的数据。eg：insert、delete、update。
• DQL：数据查询语言，用于从数据表中查找符合条件的数据记录。eg：select。
• DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些 语句定义了数据库、表、字段、用户的访问权限和安全级别，如commit、follback、grant、revoke）

    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

SQL分类:

•     数据库：database
•     表：table，行：row 列：column
•     索引：index
•     视图：view
•     存储过程：procedure
•     存储函数：function
•     触发器：trigger
•     事件调度器：event scheduler，任务计划
•     用户：user
•     权限：privilege

2.3.查看帮助信息

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

2.4.查看、修改字符集

1. #查看支持字符集
show charset;
默认拉丁文字，需要修改为
 
utf8     | UTF-8 Unicode   
#阉割版的
 
utf8mb4  | UTF-8 Unicode 
#真实的版本
 
2. #修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

2.5.数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,如tabble select show databases

三.管理MySQL数据库

3.1.数据类型

1）常用数据类型

char

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

varchar

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

2）数据类型参考

参考链接：

MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types

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

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

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)

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位

5）定点数

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

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

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

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

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

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

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

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

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.2.查看数据库结构

1）查看库信息 

进入mysql的两种方法
    mysql -u root -p
    mysql -u root -pqzz1314

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

2）查看表信息 

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

3）查看表的字段结构 

方法一：（相对路径方式，也可以理解成先切换库，在查看指定表的字段）
use 数据库名;
describe [数据库名.]表名;
可缩写成：desc 表名;
 
方法二：（绝对路径查看表达结构，也是直接查看表结构）
DESCRIBE [数据库名.]表名;
 可缩写成：DESC 数据库名.表名;

方法一：先切换库，在查看指定表的字段

方式二：直接查看表结构 

4）查看MySQL版本

mysql -V

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

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

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

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

drop database 数据库名;

3）创建新的表 （create table）

use 数据库名;
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
 
例如：
use liu;
create table students (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
 
desc students;
 
#unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M

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

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

3.4.管理表中的数据记录

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

insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);
 
提前新建数据表（注意长度设置）
use mysql;
create table students (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
 
 
例如：
insert into students(id,name,age,passwd) values(1,'xianshi',20,'123456');
insert into students(id,name,age,passwd) values(2,'jiami',22,PASSWORD('123123'));
#PASSWORD('123456')：查询数据记录时，密码字串以加密形式显示；若不使用PASSWORD()，查询时以明文显示
select * from students;
 
 
 
    此处添加数据为中文，注意字符集（默认为拉丁文）是否更改为utf8和utf8mb4
 
    如果/etc/my.cnf没有更改，建立数据库时要指定字符集utf8mb4：
    create database 数据库名 charset=utf8mb4;

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 1;
例如：	
select * from students limit 1;

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

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

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
 
例如：
update students set age=19 where id=1;
update students set age=20,passwd='111111' where id=2;    #同时修改age和passwd

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

delete from 表名 [where 条件表达式];
 
例如：
delete from students where id=3;
 
！！！！注意id并不会顶上去！！！！

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

1）修改表名（rename）

alter table 旧表名 rename 新表名;
 
例如：
alter table students rename xuesheng;

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

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

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

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

唯一键验证：

4）删除字段（drop）

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

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

1）查看MySQL支持的所有字符集

show charset;

2）查看数据库字符集

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

3）查看数据表的字符集

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

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

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