MySQL的基本操作
记录学习MySQL过程中记录的学习笔记
1 初始访问及设置
1.1 进入MySQL的用户
1.1.1 本机登陆
- 打开cmd,输入
mysql -u 用户名 -p
执行命令时可能会遇见无法识别’mysql’这个命令,这是因为在系统环境变量中没有相应路径导致的,添加类似于:‘C:\Program Files\MySQL\MySQL Server 5.7\bin’,默认路径。添加完成重启cmd即可。
- 按提示输入密码,在-p后直接输入密码也可
- 登陆成功
1.1.2 联机登陆
与本机登陆相同,要添加上要登陆的账号所存放的IP地址
mysql -h IP -u 用户名 -p
1.2 设置MySQL用户
- 具体的账号生成可看下面的 “2 用户管理”
- 完成账号的相关设置后,运行(更新):
flush privileges;
- 查看用户是否生成:
select user,host from user;
2 用户管理
2.1 用户账号、IP、密码创建
create user 'name'@'IP' identified by 'password';
- name:输入你的账号名字
- IP:输入运行登陆的IP地址,如:
‘196.168.1.1’:输入完整的IP地址,则只允许在此IP地址上登陆使用
‘196.168.1.%’,‘196.%’:输入不完整的IP地址,其余用%填充,表示允许在相同前缀的IP下使用
‘%’:仅有%表示允许在任何地址下使用
- password:输入设置的密码
2.2 权限设置
# 授予权限
grant select,insert on db.t to 'name'@"196.168.%"; # grant 权限 on 区域 to 用户
# 取消权限
revoke select,insert on db.t from 'name'@'196.168.%'; # grant 权限 on 区域 from 用户
- grant:授予权限命令
- 权限:
- all privileges:除了grant外的所有权限
- select:查询
- insert:添加
- update:更新
- 区域:允许操作的数据库或表
数据库.表:指定表
数据库.*:数据库下所有表
数据库.存储过程:
*.*:所有数据库所有表
- 用户:被授予权限的用户,格式是’name’@‘IP’,IP的设置同上。表示允许哪个用户在哪可操作的权限
2.3 其他操作
# 删除用户
drop user 'name'@'IP';
# 修改用户名'
rename user 'name1'@'IP' to 'name2'@'IP';
# 修改用户密码'
set password for 'name'@'IP' = new password;
3 数据库及数据表操作
3.1 查询操作
# 显示所有数据库
show databases;
# 使用数据库
ues database_name;
# 显示表
show tables;
# 查询表数据(列)
select col_name from table_name; # col_name 可以是 * 表全部, 逗号隔开,如:user,host
# 查询设置的表的设置(如:自增、非空、数据类型等)
desc table_name;
3.2 基础操作
3.2.1 数据库操作
# 创建数据库
create database name;
create database name default charset=utf8;
# 显示所有数据库
show databases;
# 删除数据库
drop database name;
- name: 创建/删除的数据库名字
- default: 设置
- charset: 文本格式,使用utf8可以使用中文字符,使用默认会报错
3.2.2 表操作
3.2.2.1 基本操作
# 创建表
create table table_name(
# 列名 类别 null/not null,
# 列名 类别 null/not null auto_increment primary key,
id int,
name char(123)
)engine=innodb default charset=utf8;
# 查看所有表
show tables;
# 清空表
delete from table_name;
truncate table table_name;
# 删除表
drop table table_name;
- table_name: 要操作的表名
- 设置表的内容:一个逗号代表一列,最基础设置可以直接就是列名,后面皆是可以选
null/not null: 表示数据可以为空/不可以为空
auto_increment: 表示当前列的数据将会自动增加(1、2、3、4……),一般要与后面primary key一起出现
primary key: 表约束(不能重复、不能为空),还有加速查找的功能(会生产一个文件辅助查找)
- engine:引擎
innodb:支持事务(原子性),减少因为表在数据交换的过程中出现的数据错误
myisam:支持全局索引,存储速度快
- 清空表:
delete: 清空数据但保留数据的顺序,如上面的auto_increment,新增一个数据时会在原来的编号后加1
truncate: 完全清空
3.2.2.2 数据类型
- 数字型:
- 整型:长度由小到大,tinyint(-128~127),int(-2147483648~2147483647),bigint(-9223372036854775808~9223372036854775807)
- 带小数:
- 浮点型:FLOAT,DOUBLE,数字不精确
- decimal:精确的小数,设置格式是:decimal(num1,num2),其中num1是数字的总位数(整数与小数),num2是小数点后的数字位数
- 无符号:在类型后面加unsigned
- 字符型:
- 255字符:
- char:设置格式:char(num),其中num是字符型的长度,如果输入的长度比设置的要短,会自动用空字符补齐,最后此数据的长度是固定的,因此使用这个查找速度快,但较占空间
- varchar:设置格式:varchar(num),其中num是设置长度,这个设置不会自动补齐长度,因此输入的字符长度是多少,数据占的空间就是多少,因此使用的空间少,但在查找时使用的是遍历较耗时
- 更长:
- text:65,535(2^16 - 1)
- mediumtext:166,777,215 (2^16 - 1)
- longtext:4,294,967,295(2^24 - 1)
- 255字符:
- 时间:
- DATE:YYYY-MM-DD
- TIME:HH:MM:SS
- YEAR:YYYY
- DATETIME:YYYY-MM-DD HH:MM:SS
- TIMESTAMP:YYYYMMDD HHMMSS
- 二进制:TinyBlob,Blob,MediumBlob,LongBolb
- 限定选择:
- enum:枚举,输入的数据只能在设置的范围内进行选择其中一个
# 创建 create table table_name( type_name enum('a','b','c') )engine=innodb default charset=utf8; # 使用(行操作,看后面) insert into table_name(type_name) values('b');
- set:集合,输入的数据只能在设置的范围内进行选择任意个
# 创建 create table table_name( type_name set('a','b','c') )engine=innodb default charset=utf8; # 使用(行操作,看后面) insert into table_name(type_name) values('a, b');
- 其他数据:如大数据和图片等,可以将数据存入文件,数据格内填写路径
3.2.2.3 唯一索引
限制值不能重复,单列、多列联合
create table table_name(
id int,
unique uq_name (id)
# unique 添加的唯一索引的名称 (约束列名1(,约束列名2……))
)engine=innodb default charset=utf8;
)
3.2.2.4 外键
可以实现类似枚举的功能,实现对数据的限制输入,与主键不同的是可以为空
create table table_name(
id int,
type_name_id int,
constraint constraint_name foreign key ('type_name_id',) references table_name_2('id')
# constraint 添加的约束的名称 foreign key ('被约束的列名',) references 约束来源的表名('约束来源的列名')
)engine=innodb default charset=utf8;
create table table_name_2(
id int auto_increment primary key,
type_name varchar(15)
)engine=innodb default charset=utf8;
3.2.3 行操作
# 插入数据
# 单组数据的插入
insert into table_name(col1, col2) values(data1, data2);
# 多组数据的插入
insert into table_name(col1, col2) values(data1, data2),(data3,data4);
# 其他表的数据
insert into table_name(col1, col2) select colx,coly from table2_name;
# 删除数据
delete from table_name where col1<10;
# 修改数据
update from table_name set col2=3;
update from table_name set col2=3 where col2<3;
# update from table_name set col2=3(,col3=22……) where col2<3;
# 查看数据
# 全部满足条件的数据查看
select col from table_name;
select col from table_name where col1=10;
select col as other_col from table_name where col1<10; # 可以修改显示出来的列名为other_col
select col,2 from table_name where col1<10; # 在选择列名处填写数字,会在显示的每行后面显示出这个数字
# select col from table_name where col in (10); 等效果,括号内可以逗号隔开多个,否定可以在in前加not,连续区域也可以是between num1 and num2(左右闭区间)
select col from table_name where col like '%2';
# 目标是字符时,可以使用'%'或'_'统配来查找,如:'%2'、'_2'是以2为结尾的所有字符串
# 选择展现数据
select col from table_name limit num1; # 只有一个数字时,第0条开始的num1条数据
select col from table_name limit num1,num2; # 两个数字时,从num1条开始的num2条数据
# 数据显示顺序改变,不改变表本身的顺序
select * from table_name order by col_name desc; # 大到小
select * from table_name order by col_name asc; # 小到大
select * from table_name order by col_name desc, col_name2 asc;
# 分组
select max(col) from table_name group by col_name;
# 在第二个参数处是解决同组的显示问题,使用聚合函数:count(计算同组的个数),max,min,sum,avg。可以逗号隔开一同出现
select max(col) from table_name group by col_name having max(col)>4;
# 对聚合函数的结果进行二次筛选需要用having而不能用where
# 连表
# 左右连表
select * from table1,table2 where table1.col=table2.col;
select * from table1 left join table2 on table1.col=table2.col;
# left:table1全显示,right:table2全显示。显示方无论另一个表有没有对应数据都显示,没有对应数据则显示NULL。inner:只显示有对应数据的信息。
# 上下连表
select * from table1 union select * from table2;
# union会自动将完全重复的数据去除不显示,union all则全部直接显示。
- table_name: 操作的表名
- colx: 操作的列名,可以不按顺序
- datax: 添加的数据,添加顺序与colx相同
- set: 设置相关区域的数据的改变
- select colx:查看的列名,逗号隔开,*为全部
- where: 添加选择范围的条件,类似于if
4 数据库的保存与加载
4.1 保存
mysqldump -u root databases_name > path\name.sql -p; # 有所有数据的数据库(insert)
mysqldump -u root -d databases_name > path\name.sql -p; # 不包含数据的数据库
4.2 加载
create databases databases_name;
mysqldump -u root -d databases_name < path\name.sql -p;