MySQL的基本操作

MySQL的基本操作

记录学习MySQL过程中记录的学习笔记

1 初始访问及设置

1.1 进入MySQL的用户

1.1.1 本机登陆

1. 打开cmd，输入

mysql -u 用户名 -p

执行命令时可能会遇见无法识别’mysql’这个命令，这是因为在系统环境变量中没有相应路径导致的，添加类似于：‘C:\Program Files\MySQL\MySQL Server 5.7\bin’，默认路径。添加完成重启cmd即可。

2. 按提示输入密码，在-p后直接输入密码也可
3. 登陆成功

1.1.2 联机登陆

与本机登陆相同，要添加上要登陆的账号所存放的IP地址

mysql -h IP -u 用户名 -p

1.2 设置MySQL用户

1. 具体的账号生成可看下面的 “2 用户管理”
2. 完成账号的相关设置后,运行（更新）：

flush privileges;

3. 查看用户是否生成：

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-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;