MySql的用法总结-1

本节内容:

 　　一、基本操作

　　　　1.1 登录

　　　　1.2 退出

　　　　1.3 设置密码

　　　　1.4 添加账户

　　　　1.5 授权的完整语法

　　二 、数据库的操作

　　　　2.1  操作数据库(操作文件夹)

　　　　2.2  操作数据表(操作文件)

　　　　2.3 操作记录(数据)

　　三、数据类型

　　　　3.1 整型

　　　　3.2 浮点型

　　　　3.3 字符串

　　　　3.4 数据库内置方法

　　　　3.5 枚举和集合

 　　四、约束

　　　　4.1 unsigned : 无符号

　　　　4.2  not null : 不为空

　　　　4.3 default  : 默认值

　　　　4.4 unique   : 唯一值索引

　　　　4.5 primary key : 主键

　　　　4.6 auto_increment : 自增加一

    　　　4.7 zerofill ： 0填充

　　　　4.8 foreign key :外键

　　　　4.9  额外补充(关于约束的添加和删除)

　　五、联合索引

　　　　5.1 联合唯一索引

　　　　5.2  联合唯一主键

　　　　5.3  联合普通索引

　　六、表之间关系及存储引擎

　　　　6.1  表之间关系

　　　　6.2  存储引擎

一、基本操作

1、登录

　　localhost => 127.0.0.1 本地ip

　　mysql -uroot -p -h ip地址
　　　　-u ：用户
　　　　-p ：密码
　　　　-h ：ip地址
　　　　-P ：端口号，默认为3306

2、 退出 

　　q 或者 exit

3、 设置密码

# 查询当前登录用户
select user();
# 设置密码
set password = password("123");
# 去除密码
set password = password("");

4、添加账号

# 给具体某个ip下设置一个账号 , 用来连接数据库
create user "lianxi100"@"192.168.107.1" identified by "111"
# 给具体192.168.107.% 这个网段下的所有ip设置一个账户
create user "lianxi101"@"192.168.107.%" identified by "222"
# 给所有ip设置账户
create user "lianxi102"@"%" identified by "333"

5、授权完整语法

select user() # 先查询ip用户,然后写在授权for的后面
# 查看当前ip下的用户有什么权限
show grants for "lianxi102"@"%";

grant 权限 on 数据库.表名 to "用户名"@"ip地址" identified by  "密码"

  1. select 查询权限
  2. insert 添加权限
  3. update 修改权限
  4. delete 删除权限
  5. all 所有
  6. * 所有数据库,数据表
  7. % 所有ip
# 授予查询,添加权限
grant select,insert on *.* to "lianxi102"@"%" identified by "444";
# 授予所有权限
grant all on *.* to "lianxi102"@"%" identified by "555";
# 移除删除权限(删除数据库/数据表)
revoke drop on *.* from "lianxi102"@"%"
# 刷新权限 , 立刻生效
flush privileges;

二、数据库的操作

1、操作数据库(操作文件夹)

（1）增加

# 创建数据库
create database db100 charset utf8;

（2） 查看

# 查看所有数据库
show databases;
# 查看建库语句
show create database db100;

（3）改变

#改变字符编码
alter database db100 charset gbk

（4）删除

#删除数据库
drop database db100

2、 操作数据表(操作文件)

(1)、增加

# 选择数据库
use db100;
# 创建表(字段1 类型1 , 字段2 类型2 , 字段3 类型3 ....  )
create table t1(id int , name char );

(2)、查看

# 查看数据表
show tables;
# 查看建表语句
show create table t1;
# 查看表结构
desc t1;

字段     类型   是否为空  键    默认值    备注
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id       | int(11) | YES |      | NULL | |
| name | char(1) | YES |    | NULL | |
+-------+---------+------+-----+---------+-------+

(3)、更改

# modify 改变类型
alter table t1 modify name char(10);
# change 改变类型 + 字段名
alter table t1 change name name999 char(20);
# add  添加字段
alter table t1 add age int;
# drop 删除字段
alter table t1 drop age ;
# rename 更改表名
alter table t1 rename t3123;

（4）删除

drop table t1;

3、操作记录(数据)

（1）增加

# 一次插入一条数据
insert into t1(id , name ) values(1,"张三");
# 一簇插入多条数据
insert into t1(id , name) values(2,"李琦"),(3,"王雨涵"),(4,"石磊"),(5,"菲菲");
# 不指定字段,插入数据
insert into t1 values(6,"赵沈阳");
# 可以具体指定个别字段插入
insert into t1(name) values("王生福");

（2）查找

# 查询所有数据
select * from t1;
# 查询单个字段数据
select name from t1;
# 查询多个字段
select id,name from t1;

（3）更改

# update 表名 set 字段=值 where 字段=值
update t1 set name = "小黄人" where id = 1
# 如果不指定条件,默认更改所有数据
update t1 set name = "小黄人1111" ;

（4） 删除

# delete from 表名 where 条件
delete from t1 where id = 1
# 危险操作,慎用
delete from t1
# 重置表( 重置数据 + 重置id(自增id) )
truncate table t1

三、 数据类型

1、整型

  1. tinyint ： 1个字节 有符号: [-128 ~ 127] 无符号 [0 ~ 255] unsigned 小整型值
  2. int：       4个字节 有符号: [-21亿 ~ 21个亿左右] 无符号 [0 ~ 42亿]unsigned 大整型值
create table t2(id int , sex tinyint ) ;
insert into t2(id,sex) values(2100000000,127);

2、浮点型

float(255,30)  30代表小数保留30个,整数+小数位数总长= 255
默认进行四舍五入

  • float(255,30) 单精度
  • double(255,30) 双精度
  • decimal(255,30) 金钱类型
# 情况一
    create table t3(f1 float(5,3) , d2 double(5,3) , d3 decimal(5,3)  );
    insert into t3(f1,d2,d3) values(2.3888888888888888888 , 2.388888888888888888 , 2.388888888888888);
    insert into t3(f1,d2,d3) values(2345.38888888888888888 , 2345.3888888888888888888 , 2345.3888888888888888);
    insert into t3(f1,d2,d3) values(23.388888888888888888888 , 23.388888888888888888 , 23.388888888888888888);
# 情况二
    """默认: float 保留5位 double 保留16位 decimal只保留整数"""
    create table t4(f1 float , d2 double , d3 decimal  );
    insert into t4(f1,d2,d3) values(2.38888888888888888 , 2.388888888888888888888888 , 2.3888888888888888888888888);

3、字符串

  1. char(11) 定长:固定开辟11个字符长度空间(邮政编码,身份证,手机号,银行卡) [开辟空间的速度上快于varchar,从数据结构的查询上来看,速度不快]
  2. varchar(11) 变长:动态最多开辟11个字符长度的空间( 评论,个性签名,广告 ) [开辟空间的速度上慢于char,从数据结构的查询上来看,速度ok]
  3. text 文本类型: (毕业论文,文章,长篇小说) 
create table t6(c char(11) , v varchar(11) , t text);
insert into t6 values("你好啊好啊","你好啊好啊","你好啊好啊");
insert into t6 values("你好啊好啊","你好啊好啊你好啊好啊你好啊好啊","你好啊好啊"); error
insert into t6 values("你好啊好啊11","你好啊好啊你2","你好啊好啊33");
# concat 可以做字段的拼接
select concat(c , "||" , v , "||" , t) from t6;

4、数据库内置方法

# 显示当前用户
select user()
## 字符串拼接
select concat()
# 显示当前数据库
select database()
# 显示当前时间
select now()

5、枚举和集合

  • enum 枚举 : 从列出来的数据当中选1个 (性别)
  • set 集合 : 从列出来的数据当中选多个 (爱好)
 1 create table t7(
 2     id int ,
 3     name varchar(10),
 4     sex enum("男性","女性","人妖","半兽人","狼人","小黄人") ,
 5     money float(5,3),
 6     hobby set("大保健","吃药","吃大腰子","生命一号")    
 7 );
 8 
 9 # 正常写法
10 insert into t7(id,name,sex,money,hobby) values(1,"李琦","人妖" , 99.5555555 , "大保健,吃大腰子"  );
11 # 自动去重
12 insert into t7(id,name,sex,money,hobby) values(1,"李琦","人妖" , 99.5555555 , "大保健,吃大腰子,吃大腰子,吃大腰子,吃大腰子"  );
13 # 异常报错
14 insert into t7(id,name,sex,money,hobby) values(1,"李琦","人妖" , 99.5555555 , "大保健,黄赌毒"  ); error
View Code

 6、时间类型

  • date  YYYY-MM-DD 年月日 (出生日期,结婚日期,春运,项目启动时间)
  • time  HH:MM:SS 时分秒 (长跑记录,体育竞赛)
  • year  YYYY 年份 (化石,红酒年份)
  • datetime  YYYY-MM-DD HH:MM:SS 年月日 时分秒 (订单流水,日志时间)
create table t1(d date , t time , y year , dt datetime);
insert into t1 values("2021-01-07" , "09:22:40" , "2021" , "2021-01-07 09:22:40");
insert into t1 values(now(),now(),now(),now());
  • timestamp YYYYMMDDHHMMSS (时间戳)  自动更新时间 (系统自动写入,不需要手动更新)  (用来记录修改的时间)
create table t2(dt datetime , ts timestamp);
insert into t2 values(20210107092930 , 20210107092930);
insert into t2 values(null , null); # 插入null时,自动更新时间
insert into t2 values(20400107092930 , 20400107092930); error 时间戳超过2038年错误

四 、约束

约束 : 对数据的一种限制,不满足约束条件的数据会报错

  • unsigned : 无符号
  • not null : 不为空
  • default : 默认值
  • unique : 唯一值 唯一索引
  • primary key : 主键
  • auto_increment : 自增加一
  • zerofill : 0填充
  • foreign key : 外键

（1）unsigned : 无符号

# 顺序  :  字段名  字段类型  字段约束
create table t3(id int unsigned);
create table t3_333(id int);
insert into t3 values(-100); error
insert into t3_333 values(-100);

（2）not null : 不为空

create table t4(id int not null , name varchar(100));
insert into t4 values(1,"卓培峰");
insert into t4 values(null,"卓培峰"); error
insert into t4(name) values("卓培峰"); error

（3）default  : 默认值

create table t5(id int not null , name varchar(255) default "李炯辉");
insert into t5 values(1 , null);
insert into t5(id) values(2);

（4）unique   : 唯一值

默认创建一个唯一的[索引] 作用:加快查询速度,适当的加索引可以加快速度,加的太多适得其反

UNI : 当前这个字段唯一,不能有重复值 , 但是可以为null

create table t6(id int unique , name char(255) default "非非");
insert into t6(id) values(1);
insert into t6(id) values(1); error
insert into t6(id) values(null); success
insert into t6(id) values(null); success

（5）primary key : 主键  [唯一 + 不能空]

PRI : 当前这个字段是主键,标记一条记录的唯一性

create table t7(id int primary key , name varchar(255) default "王永捐");
insert into t7(id) values(1);
insert into t7(id) values(1); error
insert into t7(id) values(null); error
    
# 主键  <=> [unique + not null]
create table t8(id int unique not null , name varchar(255) default "王永捐");
# 主键 + [unique + not null] 同时存在时,会把id2显示成主键
create table t9(id1 int unique not null , id2 int primary key);
# 一个表里面只能有一个字段为单个主键
create table t10(id1 int  primary key , id2 int primary key); error

（6）auto_increment : 自增加一 (特指: 配合主键,和唯一索引使用)

create table t11(id int primary key auto_increment , name varchar(255) default "王永捐");
insert into t11 values(1,"荷叶");
insert into t11 values(2,"孙杰龙");
insert into t11 values(null,"孟凡伟");
insert into t11(id) values(null);
# 自动插入数据
insert into t11 values();

（7）zerofill : 0填充 

create table t12(id int(8) zerofill );
insert into t12 values(123);
insert into t12 values(1234567899);

# 括号中限制字符的长度 (补充)
char(255) max <= 255
varchar(21845) max < 21845 (内部会占用一定空间保留数据的长度)

（8）foreign key  外键

外键:把多张表通过一个关联字段联合在一起,这样的字段可以设置成外键(好处:可以联级更新,联级删除)

  • 语法 : foreign key(字段1) references 表(字段)
  • 条件 : 带有关联关系的字段,套上外键的约束;
  • 要求 : 被关联的字段,必须具有唯一性

student1:
　　id      name       　　age     classid
　　1 　 wangwen  　　18         1
　　2     heye  　　　　  80　　 1
　　3    wangyongjuan   15        2
class1:
　　id      classname
　　1        python33
　　2        python34

# 创建class1
create table class1(id int , classname varchar(255));
# 临时更改id 为unique
alter table class1 add unique(id);
# 插入数据
insert into class1 values(1,"python33");
insert into class1 values(2,"python34");
# 创建student1
create table student1(
    id int primary key  auto_increment,
    name varchar(255) , 
    age int , 
    classid int ,
    foreign key(classid) references class1(id)
    )
# 插入数据
insert into student1 values(null,"wangwen",18,1);
insert into student1 values(null,"heye",80,1);
insert into student1 values(null,"wangyongjuan",15,2);        
delete from class1 where id = 1; 删不掉 (需要先删掉带有关联关系的数据)
delete from student1 where id = 1;
delete from student1 where id = 2;

联级更新和删除 (谨慎使用)

  • 联级更新 : on update cascade
  • 联级删除 : on delete cascade
 1 # 创建 class2
 2 create table class2(id int primary key , classname varchar(255));
 3 # 插入数据
 4 insert into class2 values(1,"python33");
 5 insert into class2 values(2,"python34");
 6     
 7     
 8 # 创建 student2
 9 create table student2(
10     id int primary key  auto_increment,
11     name varchar(255) , 
12     age int , 
13     classid int ,
14     foreign key(classid) references class2(id) on update cascade on delete cascade
15     )
16 # 插入数据
17 insert into student2 values(null,"wangwen",18,1);
18 insert into student2 values(null,"heye",80,1);
19 insert into student2 values(null,"wangyongjuan",15,2);
20     
21 # 可以直接联级删除
22 delete from class2 where id = 1;
23 # 可以连接更新
24 update class2 set id=900 where classname = "python34";
View Code

(9) 额外补充( 关于约束的添加和删除)

 1 # 1 添加/删除 约束 not null
 2 #alter table 表名 modify 字段名 类型
 3 alter table t1 modify id int not null
 4 alter table t1 modify id int
 5 
 6 # 2 添加/删除 unique 唯一索引
 7 # alter table 表名 add unique(id)
 8 alter table t1 add unique(id)
 9 alter table t1 drop index id
10     
11 # 3 添加/删除 primary key
12 # alter table 表名 add primary key(id);
13 alter table t1 add primary key(id);
14 alter table t1 drop primary key;
15     
16 # 4 添加/删除 foreign key 外键 (先通过desc 表 找到外键名字,然后再删)
17 alter table student1 drop foreign key student1_ibfk_1; #删除
18 alter table student1 add foreign key(classid) references class1(id) #添加
View Code

五、联合索引

联合多个字段创建索引:

  1. unique(字段1,字段2,字段3 .... )
  2. primary key(ip,port)
  3. index(ip,port)
  • 主键索引: PRI => primary key
  • 唯一索引: UNI => unique
  • 普通索引: MUL => index

1.联合唯一索引

unique(字段1,字段2,字段3 .... ) 合在一起表达该数据的唯一性

# PRI 
create table t1_server(id int , server_name varchar(255) not null ,ip varchar(15) not null , port int not null , unique(ip,port) )
insert into t1_server values(1,"百度","192.168.107.128",3306);
insert into t1_server values(1,"百度","192.168.107.128",80);
insert into t1_server values(1,"百度","192.168.107.129",80);
insert into t1_server values(1,"百度","192.168.107.129",80); error
insert into t1_server values(2,"阿里云",null,null); # error
# MUL
create table t2_server(id int , server_name varchar(255) not null ,ip varchar(15)  , port int  , unique(ip,port) )
insert into t2_server values(2,"阿里云","192.168.107.128",3306);
insert into t2_server values(2,"阿里云","192.168.107.128",443);
insert into t2_server values(2,"阿里云",null,null); # success
# insert into t1_server values(1,"百度","192.168.107.128",80);
# insert into t1_server values(1,"百度","192.168.107.129",80);
# insert into t1_server values(1,"百度","192.168.107.129",80); error

2.联合唯一主键

primary key(ip,port) 把几个字段合在一起表达数据的唯一性

create table t3_server(id int , server_name varchar(255) not null ,ip varchar(15)  , port int  , primary key(ip,port) )
insert into t3_server values(3,"腾讯云","192.168.107.128",3306);
insert into t3_server values(3,"腾讯云","192.168.107.128",20);

3.联合普通索引

index(ip,port) 把几个字段合在一起,加快查询速度

create table t4_server(id int , server_name varchar(255) not null ,ip varchar(15)  , port int  , index(ip,port) )

六、表之间关系及存储引擎

1、表之间关系

(1) 一对一 : 把表1 id name sex guanlian 表2 id height bloodtype
(2) 一对多(多对一) : 班级和学生之间, 班级是1,学生是多
(3) 多对多 : 学员可以学习很多学科, 一个学科也可以被很多学员学习 (多对多关系表一定要创建第三张关系表)
书和作者也是多对多,一本书可以被多个作者联名出版,一个作者可以出版多本书;

2、存储引擎 : 存储数据的方式

主数据库: 增删改
从数据库: 查询

# show engines 查看存储引擎
表级锁 : 只有一个线程做数据的更改,就会锁表
行级锁 : 只对当前这行修改的数据上锁,其他行数据仍可使用
事务处理 : 在执行sql语句时,必须全部成功,才最后commit提交数据,否则rollback回滚数据;
begin : 开启事务处理
commit : 提交数据
rollback : 回滚

常用存储引擎

  • MyISAM : 表级锁 (mysql5.5之前 默认引擎)
  • InnoDB : 行级锁 , 事务处理 , 外键 (mysql5.5之后 默认引擎)
  • MEMORY : 把数据存储在内存中,应用在缓存;
  • BLACKHOLE : anything you write to it disappears

　　  BLACKHOLE作用: 不存储数据,主要用来同步主从数据库,转发binlog日志;

create table myisam1(id int ,name varchar(255) ) engine=MyISAM;

#myisam1.frm 表结构
#myisam1.MYD 表数据
#myisam1.MYI 表索引
    
create table innodb1(id int ,name varchar(255) ) engine=InnoDB;
#innodb1.frm 表结构
#innodb1.ibd 表数据 + 表索引
    
create table memory1(id int ,name varchar(255) ) engine=MEMORY;
#memory1.frm 表结构
    
create table blockhole1(id int ,name varchar(255) ) engine=BLACKHOLE;
#blockhole1.frm 表结构
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yj0405/p/14256740.html