有关MySQL的简单应用

有关MySQL的简单应用

一、数据库管理系统（DBMS）：

- 简介
写程序时，对于数据处理，有以下2种情况：
1、数据储存在本地：
|——找到目录
|——添加数据
2、数据储存在远程
|——socket连接远程机器
|——socket发送命令
那么对于此数据处理时，是不是需要以下功能，如：
a、储存在远程时，需要用户登陆认证
b、对于登陆人员，是不是需要不同人员的授权
c、对于不同等级的人员，是否需要等级的划分，权限的限制等等
这就是一个商机，基本上每个需要处理数据的都需要使用，故数据库管理系统应运而生，各种数据库诞生：
MySql 、Sql server（收费）、Oracle（收费）、SQLlite、access等；
为什么要有数据库？
1.解放代码，不在自己重复操作文件，将命令发给MySQL服务器即可
2.开户授权等做法，保证数据的安全性
3.客户端连接（由mysql提供的）

- 概念
|—数据库 -->文件夹
|—数据表 -->文件
|—数据行 -->文件中的一行

-简单命令
net start mysql / sudo service mysql start#启动mysql服务
net stop mysql / sudo service mysql stop#关闭mysql服务
mysql -u root -p #终端以root方式客户端访问mysql数据库
mysql -u root -h 127.0.0.1 -p#-u 指定用户，-h指定IP，不以localhost登陆，-p指定端口，默认3306
show databases； #查看当前Mysql有哪些数据库，根目录已建立那些文件夹
create database 数据库名； #创建文件夹
use 数据库名； #使用选中数据库，进入目录
show tables； #查看当前数据库下所有数据表
create table 表名（nid int，name varchar（20），pwd varchar（20））； #创建表，包含字段nid数据类型int；name数据类型varchar，20字符长度，超出20字符，只保存前20个
select * from 表名； #从指定表中选取出所有数据
insert into 表名（nid，name，pwd） values（1，‘gao’，‘123456’）; #向表中插入数据
desc 表名#查询指定表字段详情

- 用户授权
用户管理的特殊命令：
create user '用户名'@‘IP地址/localhost’ identified by ‘密码’；#新建一个用户
drop user '用户名‘@’IP地址/localhost‘；#删除一个指定用户
rename user '用户名‘@’IP地址/localhost‘ to '用户名‘@’IP地址/localhost‘;#将指定的用户名（前）更改成指定用户名（后）
set password for '用户名‘@’IP地址/localhost‘ = Password('新密码')；# 修改指定用户密码
用户授权命令：
新建用户默认无任何权限；
grant 权限 on 数据库.表 to '用户名'@‘IP地址/localhost’#把指定表的指定权限给指定的用户
权限：select，查；insert,插入；delete，删除数据行；drop，删除数据表；
数据库.表：
test.tab1,test数据库下的tab1表；
test.*: test数据库下的所有表
*.* : 所有数据库

注意：
对于远程连接时，如果指定IP，那么仅设定的IP可以连接，故存在弊端，所以对于IP设置，MySQL数据库推出IP通配符概念：
用户名@localhost --仅本机
用户名@’IP地址‘ --仅设定IP
用户名@‘192.168.1.%’ --仅192.168.1网段
用户名@‘%’ – 所有IP
-SQL基本数据类型
MySQL基本数据类型分为数值、时间和字符串
1·数值：
bit[m]:二进制位，m表示长度，默认1，范围1-64
tinyint[m]：小整数，有符号范围（-128-127）(默认有符号型)、无符号范围（0-255）（设置时，后面加unsigned）
smallint：与上其他相同，范围不一致
int：与上其他相同，范围不一致
bigint：与上其他相同，范围不一致
decimal:精确的数值，是什么底层内部存的是字符串，故完全一致；decimal（6，2）将保持的数值取总长度为6其中包含2为小数
FLOAT:浮点数，数值越大，越不准确
DOUBLE:双浮点数，也不精确，比float好点
2·字符串：
定长：char（7）当插入数据不足7个字符时，还是以7个位置保存，查找速度快
char：最大时255个字符
变长：varchar（7）当插入数据不足7个字符时，以实际长度保存，节省存储空间
varchar：最大时255个字符
text：最大2**16-1字符
mediumtext：
longtext：

enum:枚举类型，先创建一个枚举，而后字段内的值只可以填枚举中的内容

create table shirts(
	name varchar(20),
	size enum('L','S','M','XL','XXL')
);
insert into shirts (name,size) values('dress shirt','xl');

set：集合类型，先创建一个集合，而后字段内的值只可以填集合中的内容(可多选)

create table shirts(
	name varchar(20),
	colours set('black','gree','blue','red','yellow')
);
insert into shirts (name,colours) values('dress shirt','red,blue');

3·时间：
DATA:YYYY-MM-DD(2020-02-12)传入多于这个格式时，自动剔除
TIME:HH:MM:SS
YEAR:YYYY
DATATIME:YYYY-MM-DD HH:MM:SS
TIMESTAMP:YYYYMMDD HHMMSS
-SQL语句
分级别，如下
1·数据库级别：
SHOW DATABASES; #列举数据库
CREATE DATABASE 数据库名 DEFAULT CHAREST utf8 COLLATE utf8_general_ci; #创建utf8编码类型数据库
CREATE DATABASE 数据库名 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #创建gbk编码类型数据库
USE 数据库名;#使用数据库

2·数据表级别：
SHOW TABLES; #列举数据表
desc 数据表;#列举数据表字段描述
create table 表名（nid int，name varchar（20），pwd varchar（20））； #创建表，包含字段nid数据类型int；name数据类型varchar，20字符长度，超出20字符，只保存前20个
样例：

create table tb(
	nid int not null auto_increment primary key,#数值型、自增、不为空、主键
	name varchar(16)
	)engine=innodb default charset=utf8;
	
create table tb2(
	nid int not null auto_increment primary key,#数值型、自增、不为空、主键
	name varchar(16),
	age int default 19, #默认值19
	part int not null,
	constraint fk_part foreign key (part) references tb(nid)  # 将tb表中的nid作为外键约束tb表中part字段
	)engine=innodb default charset=utf8; # engine为引擎选择 后面为选择utf8编码

项	内容
列名	即字段名
类型	即字段数据类型：int、varchar等
是否为空	数据是否可以为空nul可以l、not null不可以
默认值	当本字段没有进行数据传入时，取此处默认值，设置为define=‘li’
自增	数字，一个数据表中只有一个，此列为自增id列,每次自加1，设置为auto_increment ，加自增必须要加个索引键，如主键等
主键	一张表只存在1个主键，主键是唯一的，不能重复的，不能为空的，可以由多列组成；一般情况下，将自增列设置为主键；主键是一个特殊的唯一列（区别是唯一列可以为空）他们的作用都是：约束、索引，加速查找；设置为 primary key / primary key（）#增加多列一起作为主键 一般直接使用：nid int not null auto_increment primary key， 直接将自增列作为主键
外键	将本表中的数据，与其他表中的唯一列数据做关联，以此来表达对应关系，此做法叫外键：作用：约束、一对多对应，使用：alter table 要添加的数据表 add constraint 外键名 foreign key 要添加约束的数据表（字段）references 由此数据表来（字段）；即由后面的字段中内容约束前面数据表中字段只能填后面的表里的内容

drop table 数据表名;#删除表
delete from 数据表;#清空表内容
truncate table 数据表;#清空表内容，性能高，速度快，自增回到原点
alter table 表名 add 列名 类型;#增加列
alter table 表名 drop colum 列名;#删除列
alter table 表名 modify colum 列名 类型;# 修改列的类型
alter table 表名 change 原列名 新列名 类型;# 修改列
alter table 表名 add primary key（列名）;#增加主键
alter table 表名 drop primary key;#删除主键
alter table 表名 modify 列名 int,drop primary key（列名）;#删除主键
alter table 要添加的数据表 add constraint 外键名 foreign key 要添加约束的数据表（字段）references 由此数据表来（字段）；#添加外键
alter table 表名 drop foregin key 外键名称;#删除指定的外键
alter table 表名 alter 列名 set default 值;#设置指定表的列默认值
alter table 表名 alter 列名 drop default;#删除指定表的列默认值

3·数据行级别：(最重要部分)
select * from 数据表;#查询表中所有数据
增：
insert into 表（列）values（值）,（值）;#插入值，增加多条时，在加括号即可

create table tb(
	nid int auto_increment primary kay,
	name varchar(20),
	age int
)engine=innodb default charset=utf8;

create table tb2(
	nid int auto_increment primary kay,
	caption varchar(20),
	gender varchar(20)
)engine=innodb default charset=utf8;

insert into tb2(caption,genter)values('ka','1'),('ga','2'),('pa','3') # 一次插入多条

insert into tb(name,age) select caption,gender from tb2; # 从别的表中读出，并插入现在表中，注意此处使用的genter字段是verchar类型，但是任可以转换，因为字符串内容是数值，如果不是会报错

insert into tb(name,age) select caption,gender from tb2 where nid > 2; # 支持缩小搜索条件 

删：

create table tb2(
	nid int auto_increment primary kay,
	caption varchar(20),
	gender varchar(20)
)engine=innodb default charset=utf8;

insert into tb2(caption,genter)values('ka','1'),('ga','2'),('pa','3') # 一次插入多条

delete from tb2 where nid=1;# 删除指定部分
delete from tb2 where nid=3 and name='pa'; # 支持指定内容多重
delete from tb2 where nid=3 or name='pa'; # 支持指定内容多重

delete from tb2; # 删除整个表

改：

create table tb2(
	nid int auto_increment primary kay,
	caption varchar(20),
	gender varchar(20)
)engine=innodb default charset=utf8;

insert into tb2(caption,genter)values('ka','1'),('ga','2'),('pa','3') # 一次插入多条

update tb2 set genter='5';# 将表中所有genter字段内容改成‘2’

update tb2 set genter='10' where nid=1; #指定更改
update tb2 set caption='op',genter='10' where nid>2; #指定更改

查：

create table tb2(
	nid int auto_increment primary kay,
	caption varchar(20),
	gender varchar(20)
)engine=innodb default charset=utf8;

insert into tb2(caption,genter)values('ka','1'),('ga','2'),('pa','3') # 一次插入多条

select * from tb2; # 查所有
select * from tb2 where nid>1; # 查指定
select caption as a from tb2 where nid=3; #只返回需要的列 as是美化表头，不以原字段显示，以a显示

其他：

# 条件
select * from tb2 where nid>3 and name!='hu' and num=12; # 组合条件
select * from tb2 where nid between 5 and 10; # 5到16之间满足
select * from tb2 where nid in (5,7,10); # 在其中满足
select * from tb2 where nid not in (5,7,10); # 不在其中满足
select * from tb2 where nid in (select nid in tb); # 在其他表中满足

# 通配符 （模糊搜索）
select * from tb2 where name like 'ga%'; # 以ga开头的所有项（%指多个字符）
select * from tb2 where name like 'ga_'; # 以ga开头的后面有1个字符的所有项（_指1个字符）

# 限制（分页）
select * from tb2 limit 5; # 前5行
select * from tb2 limit 3，5; # 从3开始，一次取5行
select * from tb2 limit 5 offset 3; # 功能与上完全一致

# 排序
select * from tb2 order by nid asc; # 根据nid，从小到大排序
select * from tb2 order by nid desc; # 根据nid，从大到小排序
select * from tb2 order by name asc，nid asc; # 根据name，从小到大排序，name有相同时，按nid从小到大排

# 分组 重要
select num from tb2 group by num; # 根据num分组，num值一致的放到一组，不能返回所有，因为其他字段为分组，会报错
select num,max(nid),min(nid),sum(nid),count(nid) from tb2 group by num; # 除了取本身，取其他值时，需要使用聚合函数，如取最大值，最小值，和,个数等等 
select num,max(nid),min(nid),sum(nid),count(nid) from tb2 group by num having count(nid)>50; # 因为执行顺序的问题，在使用聚合函数时，where不可以使用,使用特有的关键字having

# 组合(联合)
select nid from tb union select nid from tb2 # 将两张表中的nid字段组合 默认去掉重复值 使用union all 不去重

# 连表（重要）
# left join 谁在前面谁重要，后面的按匹配要求填在其后
select * from tb,tb2 where tb.part=tb2.nid;#连表，将tb里的所有数据展示，并且将tb2里的数据，以tb。part中的值对应关系展示在其后
select * from tb left join tb2 on tb.part=tb2.nid;  #将tb表中所有数据展出，在后加入tb2，以tb.part字段匹配
# inner join 互相迁就，出现null时，自动清除，没有主次分
select * from tb inner join tb2 on tb.part=tb2.nid;

# 正则表达式匹配
select * from tb where name regexp '^a'; # 匹配以name字段a开头的数据
select * from tb where name regexp 'a$'; # 匹配以name字段a结尾的数据
select * from tb where name regexp 'a+'; # 匹配以name字段包含1或多个a的数据
select * from tb where name regexp 'ax*'; # 匹配以name字段包含a与0或多个x的数据
select * from tb where name regexp '[0-9]'; # 匹配以name字段存在数字的数据
select * from tb where name regexp '^a|^c|^u'; # 匹配以name字段a开头或c开头或u开头的数据

# 字段拼接
select concat(name,'--',age) from tb; # 查询数据字段name与字段age以--拼接展示（中间的参数是常量连接，有很多字段时，需要写很多次）
select concat_ws('--',name,age) from tb; # 查询数据字段name与字段age以--拼接展示(第一个参数为指定连接符，只需要写一次）

- 补充一个数据的导出与导入：
导出现有数据库数据：

#用于导出现有数据库结构+数据

mysqldump -u用户名 -p密码 数据库名 > 导出文件路径

#用于导出现有数据库结构

mysqldump -u用户名 -p密码 -d 数据库名 > 导出文件路径

导出现有数据库数据：

mysqldump -u用户名 -p密码 -d 数据库名 < 文件路径