增删改查，掌握基本的数据库基础操作命令
1.创建数据库
create database pems2;
这个是连接数据库的工具查询已有的数据库，也可以在服务器进入数据库命令行查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| pems2 |
| performance_schema |
| sbtest |
| sys |
| test |
| test2 |
| wd_pems |
+--------------------+
9 rows in set (0.00 sec)
mysql>
#####
创建数据库并设置字符集
create database pems3 default character set utf8mb4 collate utf8mb4_general_ci;
也可以用navicat创建数据库并设置字符集，一般情况下会选择用navicat创建数据库并设置字符集
2.建表
建表语法
create table (
<列名><数据类型><该列的约束>，
<列名><数据类型><该列的约束>，
.........
);
详细例子如下
CREATE TABLE `pes_option` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`option_name` varchar(128) NOT NULL DEFAULT ' ',
`name` varchar(128) NOT NULL DEFAULT ' ',
`value` text NOT NULL,
`option_range` varchar(128) NOT NULL DEFAULT ' '，
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
备注：auto_increment 是用于主键自动增长的，从1开始增长，当你把第一条记录删除时，再插入低二条数据时，主键值是2，不是1.
AUTO_INCREMENT=47： id列从47开始自增
NOT NULL: 字段的值不能为空
3.删除表
drop table pes_option;
delete from pes_option; 这个是删除表的内容
4.mysql 表结构更新与修改
一般情况表结构的更新和修改也蛮多的
添加数据列
删除数据列
修改数据列的属性
##新增数据列
语法：
alter table tb_name add (<列名><列属性><列约束>);
例子：
alter table pes_optim add text_1 varchar(200) DEFAULT ' ';
##删除数据列
语法：
alter table tb_name drop 列名；
列子：
alter table pes_option drop text_1;
##修改列的属性
语法：
alter table tb_name modify 列名 列属性定义
例子：
alter table per_opton modify value varchar(128);
##修改表的名字
语法：
alter table tb_name rename new_tb_name新表名；
例子：
alter table pes_option rename pes_option1;
##添加索引
语法：
alter table tb_name add index index_name (column)
例子：添加普通索引
alter table pes_option add index idx_option ( option_name);
也可以在navicat添加索引
语法：添加唯一索引
alter table tb_name add iunique 索引名 (column)
例子：
alter table pes_option1 add unique id_name(name);
语法：添加全文索引
alter table tb_name add index fulltext (column)
例子：
alter TABLE pes_option1 add FULLTEXT id_name(name);
语法：添加复合索引
alter table tb_name add index index_name (column1,column2,column3)
例子：
alter table pes_option1 add index id_c (name,value);
##删除普通索引
语法：
alter table tb_name drop index 索引名；
例子：
alter table pes_option1 drop index idx_option;
#删除唯一索引
语法：alter table tb_name drop index 索引名；
alter table pes_option1 drop index id_name;
其他所以删除方法一样
5.show语法
##列出数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| pems2 |
| pems3 |
| performance_schema |
| sbtest |
| sys |
| test |
| test2 |
| test3 |
| wd_pems |
+--------------------+
11 rows in set (0.00 sec)
mysql>
##列出数据库中的表
show tables [from dbname];
show tables from test3;
##列出库的详细信息
show tables status [from dbname];
show table status from test3;
##列出表的列信息
show columns from pes_option1;
##列出表的索引信息
show index from tbname;
show index from pes_option1;
##列出数据库的参数
A:查看数据库的server-id值
show variables like 'server_id';
B:查看数据库的gtid参数
show variables like '%gtid%';
C:查看logbin是否开启
show variables like '%log_bin';
D:查看mysql的最大连接数
show variables like 'max_connections';
E:查看一个账号能够同事连接到mysql服务的最大连接数。设置为0表示不限制
show variables like 'max_user_connections';
设置为0，表示不限制
F:查看mysql有哪些存储状态以及默认存储状态
show engines;
G:查看缓存大小
show status like 'innodb_buffer_pool_read%'
H:查看系统配置变量
show variables;
I:查看缓存相关参数
show variables like '%cache%';
J:查看缓冲池信息
show variables like 'innodb_buffer_pool%';
K:查看mysql用的编码
SHOW VARIABLES like 'character_set_%';
6.select 相关查询
1）查看某数据的所有表存储引擎类型
SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = '数据库名' ORDER BY table_name DESC;
2）查看所有数据的大小
select concat(round(sum(DATA_LENGTH/1024/1024/1014),2),'G') from information_schema.tables;
3）查询制定数据库的大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='数据库名';
例子：
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='wd_pems';
4）查看所有数据库各表名容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
5）查看制定数据库表名大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;
例子：查看wd_pems数据库各表的容量
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='wd_pems'
order by data_length desc, index_length desc;
6)查询某一个表的所有列内容
SELECT * FROM 表名;
SELECT * FROM pes_attachment;
7）查询某一个表的指定列内容
SELECT * FROM 表名 WHERE 列字段名='制定列内容';
例子：
SELECT * FROM pes_attachment WHERE attachment_name='配件清单.xlsx';
8）查询某一个表的指定列内容并设置返回查询行数
语法：
select coulmn1,column2 from table_name [where ] [LIMIT N] [OFFSET M]
备注：
select 可以读取一条或多条记录；
星号* 表示select语句会返回数据表的所有字段；
where语句是用来设置查询条件的；
limit：用来设置返回的数据条数
offset 用来指定select语句开始查询数据时的偏移量，默认值为0.
例子：
SELECT attachment_id ,attachment_name FROM pes_attachment where attachment_id>4 and attachment_name='配件清单.xlsx';
例子2：
SELECT * FROM pes_attachment where attachment_id>10 limit 5;
例子4：加上偏移量
SELECT * FROM pes_attachment where attachment_id>10 limit 5 OFFSET 1;
例子5.
查询id 10-30的范围
SELECT * from pes_attachment WHERE attachment_id >10 and attachment_id <30;
7.其他数据库命令
1）切换数据库
切换到wd_pems数据库
use wd_pems;
切换到mysql数据库
use mysql;
2）删除数据库
drop database；
drop wd_pems
3）drop/delete/truncate命令间的差异
drop将删除表的结构，以及被依赖的约束（constrain），触发器（triger），索引（index），该表的存储过程/函数将保留，但是变为invalid状态，同时释放空间；
delete 用于删除表的数据，逐行删除，在事务日志中为锁删除行的每一行记录，会生产rokkback，事务提交之后才生效；如果有对应的trigger，执行会被出发，因为是逐行删除，所以大批量删除时候执行效率会很低，同事delete不会删除表结构；
truncate只能操作表，讲表中的数据删除，不能带where的子语句，它是释放存储表数据所用的数据也来删除数据，在事务日志中只记录释放，所以占用系统资源很少，但是删除后没有表空间了，也不能回滚，不触发触发器；
自己主动过，才领悟到某些细节的含义。（情感）
