mysql基本操作

本文基于MySQL8.0.11的版本

一、SQL库操作

1.1、创建数据库

会在磁盘指定存放处生成一个文件夹【mysql8\data\[数据库名]，mysql7之前还会生成一个“db.opt”文件】

mysql的db.opt：

1、文件内容：

[root@root]# cat db.opt
default-character-set=utf8
default-collation=utf8_general_ci

2、作用：
MySQL数据库存放数据目录里的db.opt文件是MySQL建库过程中自动生成的，记录该库的默认字符集编码和字符集排序规则用的。也就是说如果你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。若删除这个db.opt，那么MySQL在建表的时候参照全局的字符设置，default-character-set 。

3、字符集配置

[client] 
default-character-set = charset (charset是mysql支持的字符集）

影响下列系统变量： 
character_set_client 
character_set_connection 
character_set_results

[mysqld} 
default-character-set = latin1 5.1及以前版本 
character-set-server =latin1 5.5

影响下列系统变量：

character_set_server 
character_set_database

规则：

---语法：create database 数据库名 [数据库选项];
---数据库选项：字符集【charset/character set】；校对集【collate】
create database DB_0 charset utf8MB4 collate utf8mb4_general_ci;

数据库名：数字、字母和下划线组成；不区分大小写；数字不能开头。

数据库选项：非必须的规定。字符集，非必须，默认继承DMBS；校对集，非必须，依赖字符集。

1.2、显示数据库

--1.显示所有数据库
show databases;

--2.显示数据库创建指令
show create database DB_0;

1.3、使用数据库

--通过使用数据库使后续指令默认针对具体数据库环境；简化后续SQL指令的复杂度。
--语法：use 数据库名;
use DB_0;

1.4、修改数据库

--修改数据库：修改数据库的相关库选项
---数据库名不可修改（老版本可以）：先新增，后迁移，最后删除。
---修改数据库选项：字符集，校对集
--语法：alter database 数据库名 库选项;
alter database DB_0 charset gbk collate gbk_chinese_ci;

1.5、删除数据库

--删除数据库操作需慎重（删前备份）,不可逆。
--删除数据库后，对应的数据库文件夹会消失。
--语法：drop database 数据库名;
drop database DB_0;

二、SQL表操作

2.1、创建数据表

--创建表需要指定存储的数据库
---明确指定数据库：数据库.表名
---先使用数据库：use 数据库名

--表可以指定表选项
---存储引擎：engine[=]具体存储引擎（‘=’可省略）
----InnoDB：默认存储引擎；支持事务处理和外键；数据统一管理。
----MyIsam：不支持事务和外键；数据、表结构、索引独立管理；MySQL5.6以后不再维护。
---字符集：[default] charset具体字符集（默认继承数据库）
---校对集：collate（默认继承数据库）
----MySQL字段值默认不区分大小写，建表语句加入engine Innodb charset utf8MB4 collate utf8MB4_bin以区分大小写;

--语法：
create table [数据库名.]表名(
    字段0 字段类型,
    字段1 字段类型,
    ...
    字段n 字段类型
)表选项;

use DB_0;
create table T_0(
    id int，
    name varchar(50)
)engine Innodb charset utf8MB4;

--复制表结构
create table 表名 like 数据库名.表名0;
create table 新表 select * from 旧表 where 1=2;
--复制表结构及数据（备份表）
create table 新表 select * from 旧表;
---Statement violates GTID consistency: CREATE TABLE ... SELECT.
---在5.6及以上的版本内，开启了 enforce_gtid_consistency=true 功能导致的，MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。
----1.修改 ：SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;
----配置文件中 ：ENFORCE_GTID_CONSISTENCY = off;
----2.拆分sql
----create table tb_bak like tb;
----insert into tb_bak select * from tb;

2.2、显示数据表

--显示所有的数据表--当前数据库下
show tables;
--显示指定的数据表--指定数据库
show tables from DB_0;
--显示部分关联数据表--匹配
show tables like 'DB_%';
--显示数据表的创建指令
show create table T_0;

--语句结束符
\g：与普通‘;’无区别
\G：纵向显示列数据

2.3、查看表结构

--查看数据表结构
---查看字段信息
---详细的显示字段的各项信息
---查看语法（效果一样）
desc 表名;
describe 表名;
show columns from 表名;

desc T_0;

2.4、更改数据表

--修改表名字和表选项
---修改表名：rename table 表名 to 新表名;
---修改表选项：alter table 表名 表选项;

rename table T_0 to T_1;
--跨数据库修改时使用数据库名.表名

alter table T_1 charset gbk;

2.6、更改字段

--更改字段：表创建好后，表字段的增删改

--字段操作包含字段名、类型和属性的操作
--字段操作的分类：
---新增字段：add [column]
---更改字段名：change
---修改字段：modify
---删除字段：drop
--字段操作还有位置处理
--字段操作通常是在表已经存在数据后进行

--新增字段，默认是追加再所有字段之后
--语法
alter table 表名 add [column] 字段名 字段类型 [字段属性] [字段位置];

--字段位置
--语法
---放到最前面
alter table 表名 add 字段名 字段类型 first;
---放到某个已存在的字段后
alter table 表名 add 字段名 字段类型 after 某个已存在的字段名;

--更改字段名
--语法
alter table 表名 change 原字段名 新字段名 字段类型 [字段属性] [位置];

--修改字段
alter table 表名 modify 字段名 字段类型 [字段属性] [位置];

2.7、删除字段

--删除字段 删除字段会将字段的数据也删除（不可逆）
--语法
alter table 表名 drop 字段名;

三、SQL数据操作

3.1、新增数据

--数据表中的数据以行（row）为单位，实际存储属于字段（column）存储数据
--数据插入方式
全字段插入：insert into 表名 values (字段列表顺序对应的所有值);
部分字段插入：insert into 表名 (字段列表) values (字段列表对应的值顺序列表);

3.2、查看数据

--查到的数据显示出来是一张二维表
--查看所有数据（通配）
select * from 表名 [where条件筛选];
--查看部分字段
select 字段0,字段1 from 表名 [where条件筛选];

3.3、更新数据

--更新数据
update 表名 set 字段=新值[,字段1=新值1] [where条件筛选];

3.4、删除数据

--删除数据（针对的是记录record，不可逆的操作）
delete from 表名 [where筛选条件];

四、字符集和校对集

4.1、字符集

4.4.1、字符集的概念

ASCII码表（单字节存储：1byte=8bits）

4.1.2、MySQL字符集：

--查看MySQL支持的所有字符集
show charset;

1.数据库内部对象字符集原理

服务器数据管理--->DBMS系统，安装配置字符集--->数据库，设定字符集（继承DBMS字符集）--->数据表，设定字符集（继承数据库字符集）--->数据字段，设定字符集（继承数据表字符集）

2.客户端存储数据原理

客户端录入数据（数据自带字符集）--->发送服务端（提前告知服务端数据字符集，若不告知，服务器使用默认设置）--->服务端接收数据--->执行存储操作--->转码为目标字符集--->存储

3.客户端读取数据原理

客户端录入获取指令--->发送服务端（提前告知服务器端客户端数据字符集，若不告知，服务端使用默认设置）--->服务器接收请求--->执行获取指令，按照数据存储本身字符集获取--->转成客户端字符集数据--->返回客户端--->客户端解析，只能按照自己的字符集解析

4.1.3、乱码问题的解决

--客户端告知服务器当前客户端的字符集
set names 客户端字符集;

4.1.4、字符集设置原理

--查看系统内部存储这些记录字符集的信息
show variables like 'character%';

--修改客户端字符集变量，保证服务端正常存储数据
set character_set_client=utf8mb4;

--修改客户端解析字符集变量，保证数据正常被客户端查看
set character_set_result=utf8mb4;

--字符集批量修改，保证客户端被服务端正确理解，同时客户端也能正确解析
--建立连接之后操作数据之前设置，当前会话有效
set names utf8mb4;

--upd character
service mysql stop
--
vim /etc/mysql/my.cnf
--在 [mysqld] 下增加如下两行：
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
--
service mysql restart
---数据库元数据信息generateSimpleParameterMetadata
url：实例名=?generateSimpleParameterMetadata=true&useUnicode=true&characterEncoding=utf8mb4

4.2、校对集

4.2.1、校对集的概念

4.2.2、校对集设置

--校对集设置语法
collate 校对集规则;

--查看MySQL支持的所有校对集
show collation;

--在数据库层设置校对集（常见）
create database 数据库名 charset utf8mb4 collate utf8mb4_bin;

--在数据表层设置校对集
create table T_2(
    id int,
    name varchar(20)
)charset utf8mb4 collate utf8mb4_bin;

--在字段层设置校对集（一般不常用）
create table T_3(
    id int,
    name varchar(20) collate utf8mb4_bin
)charset utf8mb4;

4.2.3、校对集应用

校对集应用：触发校对规则的使用

校对集的应用通常是通过数据比较触发：order by

数据表中数据一旦产生，校对集的修改就无效。

五、字段类型

5.1、字段类型作用

MySQL中用来规定实际存储的数据格式。【规范数据的格式，保证数据的有效性】

字段类型在定义表结构时设定，设定好字段类型后，插入数据时必须与字段类型对应，否则数据错误。

MySQL四大数据类型：整数类型，小数类型，字符串类型，时间日期类型

5.2、整数类型

--有符号和无符号对应的宽度不一样
create table T_9(
    a tinyint, # 有符号，4位宽度
    b tinyint unsigned # 无符号，3位宽度
)charset utf8;

--主动控制显示宽度
alter table T_9 add c tinyint(2) unsigned;

--显示宽度不影响数据的大小
insert into T_9 values(1,1,1); # 小于显示宽度
insert into T_9 values(111,111,111); # 大于显示宽度

--通过zerofill让小于显示宽度的数值前置补充（左补）0到显示宽度
alter table T_9 add d tinyint(2) zerofill; # 0填充只能针对正数
insert into T_9 values(1,1,1,1);

5.3、小数类型

5.3.1、小数（浮点型）

5.3.2、小数（定点型）

5.4、字符串类型

5.4.1、字符串（定长型）

5.4.2、字符串（变长型）

5.4.3、字符串类型（文本字符串）

5.4.4、字符串类型（枚举）

create table T_16(
    type enum('小朋友','少年','青年','中年','老年')
)charset utf8mb4;

insert into T_16 values('少年');
insert into T_16 values('少年0'); # 不存在的数据无法插入
-- ERROR 1265 (01000): Data truncated for column 'type' at row 1

-- enum是建立映射关系，然后实际存储是数字，数值是按照元素顺序从1开始
select type,type+0 from T_16; # 使用字段+0来判定数据具体的效果（字符串转数值为0）
insert into T_16 values(5);

5.4.5、字符串类型（集合）

5.5、时间日期类型

5.5.1、时间日期类型（年）

超出范围（1901-2155）会报错：ERROR 1264 (22003)：Out of range value for column 'v1' at row 1

--记录个人出生年份
create table T_18(
    y1 year,
    y2 year(4)
)charset utf8;
insert into T_18 values(1901,2155);

--year类型允许使用2位数来插入，系统会自动匹配对应的年份
---69以前：系统加上2000
---69以后：系统加上1900
insert into T_18 values(69,70);

--year类型的特殊值是0000，可以使用00或0000插入
insert into T_18 values(00,0000);

5.5.2、时间日期类型（时间戳）

create table T_19(
    goods_name varchar(10),
    goods_inventory int unsigned comment 库存,
    upd_time timestamp
)charset utf8mb4;

insert into T_19 values('HUAWEI P30',100,'1971-01-01 00:00:00');
insert into T_19 values('HUAWEI mate10',100,'19710101000000');


--MySQL8之后，取消了timestamp的默认自动更新，如果需要使用，需额外使用属性：on update current_timestamp

alter T_19 add upd_time timestamp on update current_timestamp;

5.5.3、时间日期类型（日期）

create table T_20(
    name varchar(10),
    birth date
)charset utf8mb4;

insert into T_20 values('张三','2000-12-12');
insert into T_20 values('张零','10011212');

5.5.4、时间日期类型（日期时间）

create table T_21(
    name varchar(10),
    birth datetime
)charset utf8mb4;

insert into T_21 values('zs','2000-12-12 12:12:12');
insert into T_21 values('ls','20001212121212');

5.5.5、时间日期类型（时间）

六、属性

6.1、属性作用

--查看表属性
desc T_0;

# Field：字段名
# Type：数据类型
# Null：是否为空（属性）
# Key：索引类型（属性）
# Default：默认值（属性）

6.2、NULL属性

6.3、Default属性

6.4、主键

--主键管理
---删除主键
alter table T_26 drop primary key;

---追加主键，增加的字段属性不能为null
alter table T_26 add primary key(account,name);

---修改主键（先删除后新增）

6.5、自增长

6.6、唯一键

--用户表：用户名唯一，企鹅经常作为查询条件
create table T_29(
    id int primary key auto_increment,
    username varchar(50) unique coomment '唯一键',
    password char(32) not null
)charset utf8mb4;

insert into T_29 values(null,'username','password');
insert into T_29 values(null,null,'password');
insert into T_29 values(null,null,'password');

# 错误
# ERROR 1062 (23000):Duplicate entry 'username' for key 'username'
insert into T_29 values(null,'username','password');

--删除表中已有的唯一键
alter table T_30 drop index `stu_name`;

--追加唯一键（保证字段里的数据具有唯一性）
alter table T_30 add unique key `stu_course` (stu_name,course);

6.7、comment属性

查看描述信息，使用“show create table 表名;”指令查看。

七、数据库记录长度

八、数据库范式

8.1、第一范式-1NF

8.2、第二范式-1NF

8.3、第三范式-1NF

8.4、逆规范化

九、表关系

9.1、一对一关系

9.2、一对多关系（多对一）

9.3、多对多关系

十、MySQL高级操作

10.1、数据新增

10.1.1、批量插入

【问题】：Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.批量插入数据量过大

数据库客户端和数据库服务器端器均有自己的max_allowed_packet变量，因此，如你打算处理大的信息包，必须增加客户端和服务器上的该变量配置数值。一般情况下，服务器默认max-allowed-packet为1MB，客户端的max-allowed-packet为16MB。

MYSQL端修改max-allowed-packet数值的方法是：在MYSQL的配置文件my.ini中加入：max_allowed_packet=16M

【max_allowed_packet =67108864，67108864=64M，默认大小4194304  也就是4M】修改完成之后要重启mysql服务。

命令修改：（即时生效）

设置为500M
mysql> set global max_allowed_packet = 500*1024*1024; 

或者
mysql> set global max_allowed_packet = 524288000;

查看：

show VARIABLES like '%max_allowed_packet%';

10.1.2、蠕虫复制

--创建一张新表，将T_30表中数据迁移到新表中
create table T_31(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8mb4;
insert into T_31 select * from T_30;

--快速让T_31表中的数据达到超过100条（重复执行，可使用存储过程实现）
insert into T_31(stu_name,course,score) select stu_name,course,score from T_31;

10.1.3、主键冲突

10.2、数据查询

10.2.1、查询选项

10.2.2、字段选择&别名

--不需要数据源的数据获取：select的表达式本身能算出结果
# 获取当前时间戳和版本号
select unix_timestamp() as now,@@version as version,@@version;

--2
select 1+1;
--2
select 1+1 as sum;

10.2.3、数据源

--多表数据源：利用一张表的每一条数据匹配另一张表的所有记录，记录结果为：记录数=表1记录数*表2记录数；字段数=表1字段数+表2字段数（笛卡尔积）
select * from T1,T2;

--子查询数据源：数据来源是一个select对应的查询结果
---查询语句需要使用括号包裹
---查询结果需要知道别名
select * from (select * from T1,T2)t;

--若表字段名较长或者使用不方便，可以利用表别名
---一般情况下别名设置是为了后续条件可以直接使用别名
---若多表操作下，可以使用表别名来明确提取表字段

10.2.4、where子句

10.2.5、运算符

10.2.6、group by子句

10.2.7、回溯统计

10.2.8、分组排序

一般很少用分组排序

10.2.9、having子句

10.2.10、order by子句

10.2.11、limit子句

10.3、数据更新

10.3.1、限制更新

10.4、数据删除

10.4.1、限制删除

10.4.2、清空数据

十一、mysql多表操作

11.1、联合查询

11.1.1、联合查询

11.1.2、联合查询排序

 

11.2、连接查询

 

11.3、子查询