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） 时间： DATE：YYYY-MM-DDTIME：HH:MM:SSYEAR：YYYYDATETIME：YYYY-MM-DD HH:MM:SSTIMESTAMP：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;