MySQL核心知识概要（一）

基础

数据库常见概念

DB:Database-数据库
DBMS:Database Manage System-数据库管理系统
SQL:Structured Query Language-结构化查询语言

常用命令

#登录
mysql -h IP -P port -u username -p [password]
mysql -u username -p
#查看数据库版本
mysql -V
mysql --version
select version();
#显示所有数据库
show databases;
#使用指定的库
use [database_name];
#显示库中所有的表
show tables;
show tables from database_name;
#查看建表语句
show create table table_name;
#查看表结构
desc table_name;
#查询当前使用的库
select database();
#查看当前MySQL支持的引擎
show engines;
#查看系统变量
show variables;
show variables like '%keyword%';#_表示一个字符%表示多个字符

SQL分类

  • DQL(Data Query Language)数据查询语言，如select
  • DML(Data Manipulate Language)数据操作语言，如insert、update、delete
  • DDL(Data Define Language)数据定义语言，如create、drop、alter
  • TCL(Transaction Control Language)事务控制语言，如start transaction、savepoint、commit、rollback

数据类型

分类

  • 整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
  • 浮点数类型：float、double、decimal
  • 字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
  • 日期类型：date、time、datetime、timestamp、year

对于int()的说明

  • int类型的长度为4字节不会因为指定的值而改变
  • int()括号中指定的数值表示查询结果的显示宽度，不足时补0；超过时无视长度显示结果，但首先需要在建表时设置unsigned zerofill，如：
create table tbl_integer(
      a int,
      b int(10),
      c int(10) zerofill,
      d int(10) unsigned zerofill
);
  • 使用zerofill时会默认自动添加unsigned属性

浮点类型

  • float、double都是浮点型，decimal是定点型
  • 浮点型和定点型可以用类型名称后加(M, D)表示，M、D分别是精度和标度
  • float、double在不指定精度时按照实际精度显示；decimal在不指定精度时默认小数为0
  • decimal采用四舍五入进行舍入，而float、double采用的是四舍六入五成双

四舍六入五成双：5以下舍弃5以上进位；如果需要处理的数字为5时需要看5后面是否还有不为0的任何数字，如果有则直接进位；如果没有则看5前面的数字如果为奇数则进位，如果为偶数则舍弃

字符串类型

  • char类型占用固定长度，如果存放的数据为固定长度建议使用char类型，如：手机号、身份证号、统一社会信用代码等

数据类型选择建议

  • 选小不选大
  • 尽量将字段设置为非空避免NULL
  • 浮点类型使用decimal避免精度引发的问题
  • 记录时间使用int或bigint可以方便进行索引

MySQL权限控制

MySQL中采用用户名+主机名识别用户身份

MySQL权限验证

步骤

  1. 首先，连接数据库时根据用户名及来源（IP或主机名称）判断是否有权限连接
  2. 当连接成功对MySQL发起请求，如create、select、delete、update等操作时会判断当前用户是否有权限执行

生效时间

  • 权限信息保存在名为mysql的库中，在MySQL启动时被读取到内存；如果直接操作mysql库修改权限需要重启或者执行flush privileges命令之后才能生效
  • 用户登录时MySQL会与当前用户建立连接，并且将权限信息保存到连接会话中；如果此时管理员或其他用户修改了权限则会在下一次登录时生效

常用语句

#查看所有用户
use mysql;
select user, host from user;

#创建用户：主机名默认为%表示任何主机；省略密码时表示可以无密码登录
create user user_name[@host_name] [identified by 'password_string'];
create user 'select_user'@'192.168.5.%' identified by '123456';

#修改密码，password()函数在MySQL8.0版本已经取消
set password for 'username'@'hostname' = password('pswd_str');
set password = password('pswd_str');
use mysql;
update user set authentication_string = password('123456') where user = 'username' and host = '%';
flush privileges;

#授权
grant [privileges:[all | select | update...]] on database.table to 'username'[@'hostname'] [with grant option];
grant all on *.* to 'username'@'hostname';
grant select, update on db.* to 'mysql_user'@'%';
grant select(fieldname1, fieldname2...) on mysql.user to 'username'@'localhost';

grant命令

  • privileges为权限列表，可以是all、select、update等，多个权限之间逗号分隔
  • on用于指定权限用于哪些库中的哪些表，如.表示所有库的所有表
  • to表示将权限赋予某个用户
  • with grant option表示将当前（执行命令的）用户所拥有的权限授权给指定的用户
#查看用户有哪些权限
show grants for 'username'@'hostname';
#查看当前用户的权限
show grants;

#撤销权限
revoke [privileges:[select | update]] on database.table from 'username'[@'hostname'];

#删除用户
drop user 'username'[@'hostname'];
delete from user where user = 'username' and host = 'hostname';

注意：通过操作mysql库中表的方式修改权限等需要调用flush privileges刷新之后重新登录才能生效

DDL

数据库管理

#创建库
create database [if not exists] database_name;
#删除库
drop database [if exists] database_name;

#创建表
create table table_name(
      字段名 类型[(宽度)] [restraints] [comment 'comment_string'],
      ...
      [foreign key(当前表字段名) references 外键表名(外键表字段名)]
)[...];

外键

foreign key(fieldname) references outer_tablename(outer_fieldname)

  • 两张表建立外键首先需要字段类型一致
  • 设置外键的字段不能为主键
  • 被引用的字段需要是主键
  • 被插入的值在外键表必须存在
#标识字段的唯一性，支持一个到多个字段
unique key(fieldname)

#标识字段自动增长
#自增长列的当前值保存在内存中，每次数据库重启会查询最大值作为当前值，如果表中数据被清空数据库重启自增值将被重置
auto_increment

#删除表
drop table [if exists] table_name;
#修改表
alter table table_name rename [to] new_table_name;
#设置备注
alter table table_name comment 'remarks';

#复制表
create table table_name like origin_table_name;
#复制表结构和数据
crreate table table_name [as] select field1, field2... from origin_table_name [where ...];

#添加列
alter table table_name add column column_name type [constraints];
#修改列
alter table table_name modify column column_name type [constraints];
alter table table_name change column column_name new_column_name type [constraints];
#删除列
alter table table_name drop column column_name;

DML

insert

insert into 表名[(字段名...)] values (值);
insert into 表名 set 字段名 = 值;
insert into table_name[(field1, field2)] values (value1, value2), (value3, value4)...;
insert into table_name[(field1, field2)] select ...;

update

update table_name [[as] alias] set [alias.]field1 = value1, [alias.]field2 = value2 [where...];
#不建议使用
update table_name1 [[as] alias1], table_name2 [[as] alias2] set [alias1.]field1 = value1, [alias2.]field2 = value2 [where...];

delete、truncate

delete [alias] from table_name [[as] alias] [where...];
delete [alias1, alias2] from table_name1 [[as] alias1], table_name2 [[as] alias2] [where...];
truncate table_name;

drop、delete、truncate的区别？

  • drop删除整个表和数据并将删除表的结构被依赖的约束（constraint）、触发器（trigger）、索引（index），依赖于该表的存储过程、函数将保留但会变为invalid状态；
  • truncate不能删除具体的行，只能清空表数据保留表结构（包括列、约束、索引等保持不变）；truncate table不能激活触发器，对于foreign key引用的表，不能使用truncate table而应该使用delete语句
  • delete是数据库操作语言DML，操作会放到rollback segment中，在事务提交之后才生效，如果有相应的trigger会被触发；用于删除表中的行，执行过程是每次从表中删除一行并同时将该行的删除操作作为事务记录在日志中以便回滚
  • truncate、drop是数据库定义语言DDL，操作立即生效并且不会进入rollback segment不能回滚，也不会触发trigger
  • truncate方式删除后自增列值会被初始化，delete只有在重启数据库的情况下才会重置自增值
  • 删除速度：drop > truncate > delete
drop truncate delete
条件删除 × × √
删除表结构 √ × ×
事务方式删除 × × √
触发trigger × × √

以上内容源自itsoku的《MySQL笔记》，感谢。

posted @ 2020-10-31 07:40  喂桶饭吃  阅读(84)  评论(0)    收藏  举报