对mysql平常利用语句的详实计算社会保险

上面总计的知识点全是平时用的,全都是干货,好好珍藏吧。

/* 启动mysql */
net start mysql

/* 连接与断开服务器 */
mysql -h 地址 -p 端口 -u 用户名 -p 密码

/* 跳过权力验证登录mysql */
mysqld –skip-grant-tables
— 修改root密码
密码加密函数password()
update mysql.user set password=password(‘root’);

show processlist — 突显怎么线程正在周转
show variables —

/* 数据库操作 */ ——————
— 查看当前数据库
select database();
— 展现当前时光、用户名、数据库版本
select now(), user(), version();
— 创建库
create database[ if not exists] 数据库名 数据库选项
数据库选项:
character set charset_name
collate collation_name
— 查看已有库
show databases[ like ‘pattern’]
— 查看当前库消息
show create database 数据库名
— 修改库的选项音信
alter database 库名 选项音信
— 删除库
drop database[ if exists] 数据库名
再者删除该数据库相关的目录及其目录内容

/* 表的操作 */ ——————
— 创建表
create [temporary] table[ if not exists] [库名.]表名 (
表的布局定义 )[ 表选项]
种种字段必须有数据类型
末尾一个字段后不可能有逗号
temporary 临时表,会话为止时表自动消失
对于字段的定义:
字段名 数据类型 [not null | null] [default default_value]
[auto_increment] [unique [key] | [primary] key] [comment
‘string’]
— 表选项
— 字符集
charset = charset_name
如若表没有设定,则应用数据库字符集
— 存储引擎
engine = engine_name
表在治本数据时选择的例外的数据结构,结构不一致会导致处理形式、提供的特点操作等分裂
普遍的引擎:innodb myisam memory/heap bdb merge example csv maxdb
archive
今非昔比的引擎在保存表的构造和数量时使用区其余法子
myisam表文件含义:.frm表定义,.myd表数据,.myi表索引
innodb表文件含义:.frm表定义,表空间数据和日志文件
show engines — 突显存储引擎的图景新闻
show engine 引擎名 {logs|status} — 显示存储引擎的日记或气象音信
— 数据文件目录
data directory = ‘目录’
— 索引文件目录
index directory = ‘目录’
— 表注释
comment = ‘string’
— 分区选项
partition by … (详细见手册)
— 查看所有表
show tables[ like ‘pattern’]
show tables from 表名
— 查看表机构
show create table 表名 (音信更详细)
desc 表名 / describe 表名 / explain 表名 / show columns from 表名 [like
‘pattern’]
show table status [from db_name] [like ‘pattern’]
— 修改表
— 修改表本身的取舍
alter table 表名 表的挑三拣四
eg: alter table 表名 engine=myisam;
— 对表进行重命名
rename table 原表名 to 新表名
rename table 原表名 to 库名.表名 (可将表移动到另一个数据库)
— rename可以沟通多少个表名
— 修改表的字段机构
alter table 表名 操作名
— 操作名
add[ column] 字段名 — 增加字段
after 字段名 — 代表增添在该字段名前边
first — 表示增添在率先个
add primary key(字段名) — 创制主键
add unique [索引名] (字段名)– 创立唯一索引
add index [索引名] (字段名) — 成立普通索引
add
drop[ column] 字段名 — 删除字段
modify[ column] 字段名 字段属性 —
援助对字段属性举行修改,不可能改改字段名(所有原有属性也需写上)
change[ column] 原字段名 新字段名 字段属性 — 接济对字段名修改
drop primary key — 删除主键(删除主键前需删除其auto_increment属性)
drop index 索引名 — 删除索引
drop foreign key 外键 — 删除外键

— 删除表
drop table[ if exists] 表名 …
— 清空表数据
truncate [table] 表名
— 复制表结构
create table 表名 like 要复制的表名
— 复制表结构和多少
create table 表名 [as] select * from 要复制的表名
— 检查表是不是有错误
check table tbl_name [, tbl_name] … [option] …
— 优化表
optimize [local | no_write_to_binlog] table tbl_name [,
tbl_name] …
— 修复表
repair [local | no_write_to_binlog] table tbl_name [, tbl_name]
… [quick] [extended] [use_frm]
— 分析表
analyze [local | no_write_to_binlog] table tbl_name [,
tbl_name] …

 

/* 数据操作 */ ——————
— 增
insert [into] 表名 [(字段列表)] values (值列表)[, (值列表), …]
— 假设要插入的值列表包含所有字段并且逐一一致,则足以省略字段列表。
— 可同时插入多条数据记录!
replace 与 insert 完全平等,可调换。
insert [into] 表名 set 字段名=值[, 字段名=值, …]
— 查
select 字段列表 from 表名[ 其余子句]
— 可来自多个表的七个字段
— 其他子句可以不利用
— 字段列表可以用*代替,表示拥有字段
— 删
delete from 表名[ 删除条件子句]
从不条件子句,则会去除所有
— 改
update 表名 set 字段名=新值[, 字段名=新值] [履新标准]

/* 字符集编码 */ ——————
— mysql、数据库、表、字段均可安装编码
— 数据编码与客户端编码不需一致
show variables like ‘character_set_%’ — 查看所有字符集编码项
character_set_client 客户端向服务器发送数据时行使的编码
character_set_results 服务器端将结果再次来到给客户端所利用的编码
character_set_connection 连接层编码
set 变量名 = 变量值
set character_set_client = gbk;
set character_set_results = gbk;
set character_set_connection = gbk;
set names gbk; — 相当于完毕以上多个设置
— 校对集
核查集用以排序
show character set [like ‘pattern’]/show charset [like ‘pattern’]
查看所有字符集
show collation [like ‘pattern’] 查看所有校对集
charset 字符集编码 设置字符集编码
collate 核查集编码 设置核对集编码

/* 数据类型(列类型) */ ——————

  1. 数值类型
    — a. 整型 ———-
    类型 字节 范围(有标志位)
    tinyint 1字节 -128 ~ 127 无标志位:0 ~ 255
    smallint 2字节 -32768 ~ 32767
    mediumint 3字节 -8388608 ~ 8388607
    int 4字节
    bigint 8字节

int(m) m表示总位数

  • 默许存在符号位,unsigned 属性修改
  • 来得涨幅,假诺某个数不够定义字段时设置的位数,则前面以0补填,zerofill
    属性修改
    例:int(5) 插入一个数’123’,补填后为’00123′
  • 在满足要求的气象下,越小越好。

    1意味着bool值真,0意味着bool值假。mysql没有布尔类型,通过整型0和1意味。常用tinyint(1)表示布尔型。

— b. 浮点型 ———-
类型 字节 范围
float(单精度) 4字节
double(双精度) 8字节
浮点型既匡助符号位 unsigned 属性,也支撑显得涨幅 zerofill 属性。
不相同于整型,前后均会补填0.
概念浮点型时,需点名总位数和小数位数。
float(m, d) double(m, d)
m表示总位数,d表示小数位数。
m和d的大小会决定浮点数的范围。不相同于整型的定位范围。
m既代表总位数(不包蕴小数点和正负号),也象征突显涨幅(所有彰显符号均包含)。
支撑科学计数法表示。
浮点数表示近似值。

— c. 定点数 ———-
decimal — 可变长度
decimal(m, d) m也意味总位数,d表示小数位数。
封存一个规范的数值,不会时有发生多少的改动,不相同于浮点数的四舍五入。
将浮点数转换为字符串来保存,每9位数字保存为4个字节。

  1. 字符串类型
    — a. char, varchar ———-
    char 定长字符串,速度快,但浪费空间
    varchar 变长字符串,速度慢,但节省空间
    m表示能积存的最大尺寸,此尺寸是字符数,非字节数。
    不等的编码,所占用的空中分裂。
    char,最多255个字符,与编码毫无干系。
    varchar,最多65535字符,与编码有关。
    一条有效记录最大无法超过65535个字节。
    utf8 最大为21844个字符,gbk 最大为32766个字符,latin1
    最大为65532个字符
    varchar 是变长的,须求动用存储空间保存 varchar
    的尺寸,如若数据低于255个字节,则使用一个字节来保存长度,反之必要多少个字节来保存。
    varchar 的最大实用长度由最大行大小和利用的字符集确定。
    最大使得长度是65532字节,因为在varchar存字符串时,首个字节是空的,不设有其余数据,然后还需多少个字节来存放在字符串的长短,所以有效长度是64432-1-2=65532字节。
    例:若一个表定义为 create table tb(c1 int, c2 char(30), c3 varchar(n))
    charset=utf8; 问n的最大值是稍稍? 答:(65535-1-2-4-30*3)/3

— b. blob, text ———-
blob 二进制字符串(字节字符串)
tinyblob, blob, mediumblob, longblob
text 非二进制字符串(字符字符串)
tinytext, text, mediumtext, longtext
text 在概念时,不要求定义长度,也不会盘算总长度。
text 类型在概念时,不可给default值

— c. binary, varbinary ———-
就像是于char和varchar,用于保存二进制字符串,也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.

  1. 日期时间项目
    一般用整型保存时间戳,因为php可以很有益的将时刻戳进行格式化。
    datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
    date 3字节 日期 1000-01-01 到 9999-12-31
    timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
    time 3字节 时间 -838:59:59 到 838:59:59
    year 1字节 年份 1901 – 2155

datetime “yyyy-mm-dd hh:mm:ss”
timestamp “yy-mm-dd hh:mm:ss”
“yyyymmddhhmmss”
“yymmddhhmmss”
yyyymmddhhmmss
yymmddhhmmss
date “yyyy-mm-dd”
“yy-mm-dd”
“yyyymmdd”
“yymmdd”
yyyymmdd
yymmdd
time “hh:mm:ss”
“hhmmss”
hhmmss
year “yyyy”
“yy”
yyyy
yy

  1. 枚举和汇集
    — 枚举(enum) ———-
    enum(val1, val2, val3…)
    在已知的值中开展单选。最大数量为65535.
    枚举值在保存时,以2个字节的整型(smallint)保存。每个枚举值,按保存的地点顺序,从1伊始挨家挨户递增。
    展现为字符串类型,存储却是整型。
    null值的目录是null。
    空字符串错误值的索引值是0。

— 集合(set) ———-
set(val1, val2, val3…)
create table tab ( gender set(‘男’, ‘女’, ‘无’) );
insert into tab values (‘男, 女’);
最多可以有64个例外的积极分子。以bigint存储,共8个字节。采用位运算的方式。
当创设表时,set成员值的尾巴空格将自行被剔除。

/* 拔取品种 */
— php角度

  1. 成效满意
  2. 积存空间尽量小,处理功能更高
  3. 设想包容问题

— ip存储 ———-

  1. 只需贮存,可用字符串
  2. 若果需计算,查找等,可存储为4个字节的无符号int,即unsigned
    1) php函数转换
    ip2long可更换为整型,但会出现率领符号问题。需格式化为无符号的整型。
    拔取sprintf函数格式化字符串
    sprintf(“%u”, ip2long(‘192.168.3.134’));
    下一场用long2ip将整型转回ip字符串
    2) mysql函数转换(无符号整型,unsigned)
    inet_aton(‘127.0.0.1’) 将ip转为整型
    inet_ntoa(2130706433) 将整型转为ip

 

/* 列属性(列约束) */ ——————

  1. 主键
  • 能唯一标识记录的字段,可以视作主键。
  • 一个表只好有一个主键。
  • 主键具有唯一性。
  • 声称字段时,用 primary key 标识。
    也足以在字段列表之后申明
    例:create table tab ( id int, stu varchar(10), primary key (id));
  • 主键字段的值无法为null。
  • 主键可以由七个字段共同组成。此时急需在字段列表后宣称的主意。
    例:create table tab ( id int, stu varchar(10), age int, primary key
    (stu, age));
  1. unique 唯一索引(唯一约束)
    使得某字段的值也无法再一次。

  2. null 约束
    null不是数据类型,是列的一个性能。
    意味着近期列是不是足以为null,表示什么都尚未。
    null, 允许为空。默许。
    not null, 不一致意为空。
    insert into tab values (null, ‘val’);
    — 此时代表将率先个字段的值设为null, 取决于该字段是还是不是允许为null

  3. default 默许值属性
    近来字段的默许值。
    insert into tab values (default, ‘val’); — 此时表示强制行使默许值。
    create table tab ( add_time timestamp default current_timestamp );
    — 代表将眼前岁月的岁月戳设为默许值。
    current_date, current_time

  4. auto_increment 自动增加约束
    机关拉长必须为索引(主键或unique)
    只能存在一个字段为全自动增进。
    默许为1从头活动增进。可以经过表属性 auto_increment = x进行设置,或
    alter table tbl auto_increment = x;

  5. comment 注释
    例:create table tab ( id int ) comment ‘注释内容’;

  6. foreign key 外键约束
    用以限制主表与从表数据完整性。
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id)
    references t2(id);
    — 将表t1的t1_id外键关联到表t2的id字段。
    — 每个外键都有一个名字,可以透过 constraint 指定

留存外键的表,称之为从表(子表),外键指向的表,称之为主表(父表)。

效用:保持数据一致性,完整性,首要目标是决定存储在外键表(从表)中的数据。

mysql中,可以对innodb引擎使用外键约束:
语法:
foreign key (外键字段) references 主表名 (关联字段)
[主表记录删除时的动作] [主表记录更新时的动作]
那时亟待检测一个从表的外键须求约束为主表的已存在的值。外键在没有涉及的情景下,可以设置为null.前提是该外键列,没有not
null。

可以不点名主表记录更改或更新时的动作,那么此时主表的操作被拒绝。
假若指定了 on update 或 on
delete:在剔除或更新时,有如下几个操作能够接纳:
1.
cascade,级联操作。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被剔除,从表相关记录也被去除。

  1. set
    null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录被剔除,从表相关记录外键被装置成null。但注意,要求该外键列,没有not
    null属性约束。
  2. restrict,拒绝父表删除和立异。

瞩目,外键只被innodb存储引擎所支持。其余发动机是不帮忙的。

/* 建表规范 */ ——————
— normal format, nf

  • 各种表保存一个实体新闻
  • 种种拥有一个id字段作为主键
  • id主键 + 原子表
    — 1nf, 第一范式
    字段无法再分,就知足第一范式。
    — 2nf, 第二范式
    满意第一范式的前提下,无法冒出有的着重。
    废除符合主键就可以防止有些敬爱。增添单列关键字。
    — 3nf, 第三范式
    满意第二范式的前提下,无法冒出传递器重。
    某个字段器重于主键,而有其他字段看重于该字段。这就是传递看重。
    将一个实体信息的数码放在一个表内完结。

/* select */ ——————

select [all|distinct] select_expr from -> where -> group by
[协和函数] -> having -> order by -> limit

a. select_expr
— 可以用 * 表示拥有字段。
select * from tb;
— 可以利用表明式(统计公式、函数调用、字段也是个表明式)
select stu, 29+25, now() from tb;
— 可以为种种列使用别名。适用于简化列标识,幸免多少个列标识符重复。

  • 应用 as 关键字,也可省略 as.
    select stu+10 as add10 from tb;

b. from 子句
用于标识查询来源。
— 能够为表起别名。使用as关键字。
select * from tb1 as tt, tb2 as bb;
— from子句后,可以而且出现多个表。
— 多个表会横向叠加到一道,而数据会形成一个笛卡尔(卡尔(Carl))积。
select * from tb1, tb2;

c. where 子句
— 从from得到的数据源中展开筛选。
— 整型1表示真,0表示假。
— 表达式由运算符和运算数组成。
— 运算数:变量(字段)、值、函数再次回到值
— 运算符:
=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and,
or, not, xor
is/is not 加上ture/false/unknown,检验某个值的真假
<=>与<>功用雷同,<=>可用于null比较

d. group by 子句, 分组子句
group by 字段/别名 [排序格局]
分组后会举行排序。升序:asc,降序:desc

以下[合计函数]需配合 group by 使用:
count 再次回到不相同的非null值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat
重返带有来自一个组的连日的非null值的字符串结果。组内字符串连接。

e. having 子句,条件子句
与 where 成效、用法相同,执行时机不比。
where 在始发时实施检测数据,对原数据举办过滤。
having 对筛选出的结果再度展开过滤。
having 字段必须是询问出来的,where 字段必须是数据表存在的。
where 不可以使用字段的别名,having
可以。因为实施where代码时,可能没有确定列值。
where 不可以运用协议函数。一般需用合计函数才会用 having
sql标准要求having必须引用group by子句中的列或用于合计函数中的列。

f. order by 子句,排序子句
order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]…
升序:asc,降序:desc
援助三个字段的排序。

g. limit 子句,限制结果数量子句
仅对拍卖好的结果开展数据限制。将处理好的结果的当作是一个会聚,依照记录出现的逐条,索引从0开首。
limit 先导地方, 获取条数
简单第四个参数,表示从索引0开端。limit 获取条数

h. distinct, all 选项
distinct 去除重复记录
默许为 all, 全体笔录

/* union */ ——————
将多个select查询的结果组合成一个结实集合。
select … union [all|distinct] select …
默许 distinct 格局,即具备重临的行都是唯一的
提议,对每个select查询加上小括号包裹。
order by 排序时,需加上 limit 进行结合。
亟待各select查询的字段数量同样。
每个select查询的字段列表(数量、类型)应同等,因为结果中的字段名以第一条select语句为准。

/* 子查询 */ ——————

  • 子查询需用括号包裹。
    — from型
    from后需求是一个表,必须给子查询结果取个别名。
  • 简化每个查询内的规范。
  • from型需将结果生成一个暂时表格,可用以原表的锁定的放飞。
  • 子查询再次回到一个表,表型子查询。
    select * from (select * from tb where id>0) as subfrom where
    id>1;
    — where型
  • 子查询重返一个值,标量子查询。
  • 不需要给子查询取别名。
  • where子查询内的表,不可能平素用来更新。
    select * from tb where money = (select max(money) from tb);
    — 列子查询
    若是子查询结果回到的是一列。
    行使 in 或 not in 已毕查询
    exists 和 not exists 条件
    假设子查询重回数据,则赶回1或0。常用于判断标准。
    select column1 from t1 where exists (select * from t2);
    — 行子查询
    询问条件是一个行。
    select * from t1 where (id, gender) in (select id, gender from t2);
    行构造符:(col1, col2, …) 或 row(col1, col2, …)
    行构造符平时用于与对能回去八个或七个以上列的子查询举办相比较。

— 特殊运算符
!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不均等 not in,不等于其中某一个。
all, some 可以合营其余运算符一起行使。

/* 连接查询(join) */ ——————
将三个表的字段举行三番五次,可以指定连接条件。
— 内连接(inner join)

  • 默许就是内接连,可省略inner。
  • 唯有数据存在时才能发送连接。即延续结果不可以出现空行。
    on
    表示连接条件。其基准表明式与where类似。也足以省略条件(表示原则永远为真)
    也可用where表示连接条件。
    还有 using, 但需字段名相同。 using(字段名)

— 交叉连接 cross join
即,没有标准的内延续。
select * from tb1 cross join tb2;
— 外连接(outer join)

  • 设若数额不设有,也会出现在一而再结果中。
    — 左外连接 left join
    假定数据不存在,左表记录会现身,而右表为null填充
    — 右外接连 right join
    万一数额不存在,右表记录会出现,而左表为null填充
    — 自然连接(natural join)
    电动判断连接条件形成连接。
    相当于容易了using,会自行检索相同字段名。
    natural join
    natural left join
    natural right join

select info.id, info.name, info.stu_num, extra_info.hobby,
extra_info.sex from info, extra_info where info.stu_num =
extra_info.stu_id;

/* 导入导出 */ ——————
select * into outfile 文件地方 [控制格式] from 表名; — 导出表数据
load data [local] infile 文件地点 [replace|ignore] into table 表名
[操纵格式]; — 导入数据
转变的数量默认的相间符是制表符
local未指定,则数据文件必须在服务器上
replace 和 ignore 关键词控制对现有的绝无仅有键记录的重复的拍卖
— 控制格式
fields 控制字段格式
默认:fields terminated by ‘\t’ enclosed by ” escaped by ‘\\’
terminated by ‘string’ — 终止
enclosed by ‘char’ — 包裹
escaped by ‘char’ — 转义
— 示例:
select a,b,a+b into outfile ‘/tmp/result.text’
fields terminated by ‘,’ optionally enclosed by ‘”‘
lines terminated by ‘\n’
from test_table;
lines 控制行格式
默认:lines terminated by ‘\n’
terminated by ‘string’ — 终止

/* insert */ ——————
select语句得到的数目足以用insert插入。

能够简不难单对列的指定,要求 values ()
括号内,提要求了遵守列顺序出现的所有字段的值。
或者选择set语法。
insert into tbl_name set field=value,…;

可以两回性使用多少个值,接纳(), (), ();的花样。
insert into tbl_name values (), (), ();

可以在列值指定时,使用表明式。
insert into tbl_name values (field_value, 10+10, now());
可以应用一个新鲜值 default,表示该列使用默许值。
insert into tbl_name values (field_value, default);

可以通过一个查询的结果,作为须要插入的值。
insert into tbl_name select …;

可以指定在插入的值出现主键(或唯一索引)争执时,更新任何非主键列的消息。
insert into tbl_name values/set/select on duplicate key update 字段=值,
…;

/* delete */ ——————
delete from tbl_name [where where_definition] [order by …]
[limit row_count]

依据标准化删除

指定删除的最多记录数。limit

可以因而排序条件删除。order by + limit

支撑多表删除,使用类似连接语法。
delete from 必要删除数据多表1,表2 using 表连接操作 条件。

/* truncate */ ——————
truncate [table] tbl_name
清空数据
剔除重建表

区别:
1,truncate 是删除表再创造,delete 是逐条删除
2,truncate 重置auto_increment的值。而delete不会
3,truncate 不明白删除了几条,而delete知道。
4,当被用于带分区的表时,truncate 会保留分区

/* 备份与还原 */ ——————
备份,将数据的布局与表内数据保存起来。
行使 mysqldump 指令完结。

— 导出

  1. 导出一张表
      mysqldump -u用户名 -p密码 库名 表名 > 文件名(d:/a.sql)
  2. 导出多张表
      mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(d:/a.sql)
  3. 导出所有表
      mysqldump -u用户名 -p密码 库名 > 文件名(d:/a.sql)
  4. 导出一个库
      mysqldump -u用户名 -p密码 -b 库名 > 文件名(d:/a.sql)

可以-w指导备份条件

— 导入

  1. 在登录mysql的景况下:
      source 备份文件
  2. 在不记名的情事下
      mysql -u用户名 -p密码 库名 < 备份文件

/* 视图 */ ——————
何以是视图:
视图是一个虚拟表,其情节由询问定义。同实际的表一样,视图包蕴一连串涵盖名称的列和行数据。可是,视图并不在数据库中以存储的数目值集方式存在。行和列数据出自由定义视图的查询所引用的表,并且在引用视图时动态变化。
视图具有表结构文件,但不存在数据文件。
对中间所引用的基础表来说,视图的成效类似于筛选。定义视图的筛选可以来自当前或其余数据库的一个或五个表,或者别的视图。通过视图举行询问没有其他限制,通过它们举办数据修改时的范围也很少。
视图是储存在数据库中的查询的sql语句,它根本由于三种原因:安全原因,视图可以隐藏一些数据,如:社会有限扶助基金表,能够用视图只显示姓名,地址,而不出示社会保证号和薪金数等,另一缘由是可使复杂的询问易于明白和选择。

— 创造视图
create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)] as select_statement

  • 视图名必须唯一,同时不可能与表重名。
  • 视图可以运用select语句询问到的列名,也得以友善指定相应的列名。
  • 可以指定视图执行的算法,通过algorithm指定。
  • column_list即使存在,则数目必须等于select语句检索的列数

— 查看结构
show create view view_name

— 删除视图

  • 剔除视图后,数据依旧留存。
  • 可同时删除多少个视图。
    drop view [if exists] view_name …

— 修改视图结构

  • 一般不改动视图,因为不是兼具的更新视图都会炫耀到表上。
    alter view view_name [(column_list)] as select_statement

— 视图成效

  1. 简化工作逻辑
  2. 对客户端隐藏真实的表结构

— 视图算法(algorithm)
merge 合并
将视图的查询语句,与外表查询须要先统一再进行!
temptable 临时表
将视图执行完结后,形成临时表,再做外层查询!
undefined 未定义(默许),指的是mysql自主去选择相应的算法。

 

/* 事务(transaction) */ ——————
工作是指逻辑上的一组操作,组成那组操作的逐条单元,要不全成功要不全失利。

  • 支撑接二连三sql的共用成功或公共收回。
  • 业务是数据库在数据晚自习方面的一个功用。
  • 急需选择 innodb 或 bdb 存储引擎,对机关提交的特色协理已毕。
  • innodb被叫做事务安全型引擎。

— 事务开启
start transaction; 或者 begin;
打开事务后,所有被实施的sql语句均被认作当前业务内的sql语句。
— 事务提交
commit;
— 事务回滚
rollback;
只要局地操作爆发问题,映射到事情开启前。

— 事务的表征

  1. 原子性(atomicity)
    作业是一个不可分割的做事单位,事务中的操作依然都发出,要么都不暴发。
  2. 一致性(consistency)
    政工前后数据的完整性必须保持一致。
  • 业务先导和了结时,外部数据一致
  • 在全路事情进度中,操作是连连的
  1. 隔离性(isolation)
    多个用户并发访问数据库时,一个用户的业务无法被其余用户的事物所苦恼,八个冒出事务之间的数额要相互隔离。
  2. 持久性(durability)
    一个事务一旦被提交,它对数据库中的数据变动就是永久性的。

— 事务的落成

  1. 务求是事情帮衬的表类型
  2. 举办一组有关的操作前开启事务
    3.
    整组操作达成后,都成功,则交给;如若存在破产,选取回滚,则会回去工作开端的备份点。

— 事务的规律
利用innodb的机关提交(autocommit)特性成就。
经常的mysql执行语句后,当前的数量交到操作均可被此外客户端可知。
而工作是临时关闭“自动提交”机制,要求commit提交持久化数据操作。

— 注意
1.
数额定义语言(ddl)语句不可能被回滚,比如创立或吊销数据库的讲话,和开创、撤除或更改表或存储的子程序的语句。

  1. 业务不可以被嵌套

— 保存点
savepoint 保存点名称 — 设置一个事情保存点
rollback to savepoint 保存点名称 — 回滚到保存点
release savepoint 保存点名称 — 删除保存点

— innodb自动提交特性设置

set autocommit = 0|1; 0表示关闭自动提交,1代表开启自动提交。

如若关闭了,那平日操作的结果对其它客户端也不可知,需要commit提交后才能持久化数据操作。

  • 也能够关闭自动提交来拉开事务。但与start transaction差其他是,
    set
    autocommit是永恒改变服务器的设置,直到下次再次修改该装置。(针对当前连年)
    而start
    transaction记录开启前的景况,而只要事情提交或回滚后就必要再次开启事务。(针对近期事情)

/* 锁表 */
表锁定只用于防止其余客户端举办不正当地读取和写入
myisam 援救表锁,innodb 襄助行锁
— 锁定
lock tables tbl_name [as alias]
— 解锁
unlock tables

/* 触发器 */ ——————
接触程序是与表有关的命名数据库对象,当该表出现一定事件时,将激活该对象
监听:记录的增多、修改、删除。

— 创设触发器
create trigger trigger_name trigger_time trigger_event on tbl_name
for each row trigger_stmt
参数:
trigger_time是触发程序的动作时间。它可以是 before 或
after,以指明触发程序是在激活它的说话从前或以后触发。
trigger_event指明了激活触发程序的口舌的种类
insert:将新行插入表时激活触发程序
update:更改某一行时激活触发程序
delete:从表中删除某一行时激活触发程序
tbl_name:监听的表,必须是永久性的表,不可能将触发程序与temporary表或视图关联起来。
trigger_stmt:当接触程序激活时实施的言语。执行多个语句,可利用begin…end复合语句结构

— 删除
drop trigger [schema_name.]trigger_name

可以行使old和new代替旧的和新的多少
履新操作,更新前是old,更新后是new.
删除操作,唯有old.
伸张操作,只有new.

— 注意

  1. 对于拥有同样触发程序动作时间和事件的给定表,无法有四个触发程序。

— 字符连接函数
concat(str1[, str2,…])

— 分支语句
if 条件 then
施行语句
elseif 条件 then
实施语句
else
实践语句
end if;

— 修改最外层语句甘休符
delimiter 自定义截至符号
sql语句
自定义停止符号

delimiter ; — 修改回原来的分店

— 语句块包裹
begin
语句块
end

— 特殊的执行

  1. 若是加上记录,就会触发程序。
  2. insert into on duplicate key update 语法会触发:
    一经没有重复记录,会触发 before insert, after insert;
    只要有重复记录并更新,会触发 before insert, before update, after
    update;
    设若有重复记录不过从未爆发更新,则触发 before insert, before update
  3. replace 语法 尽管有记录,则举办 before insert, before delete, after
    delete, after insert

/* sql编程 */ ——————

–// 局地变量 ———-
— 变量注解
declare var_name[,…] type [default value]
本条讲话被用来声称局地变量。要给变量提供一个默许值,请包罗一个default子句。值可以被指定为一个表明式,不须要为一个常数。倘若没有default子句,初叶值为null。

— 赋值
动用 set 和 select into 语句为变量赋值。

– 注意:在函数内是足以应用全局变量(用户自定义的变量)

–// 全局变量 ———-
— 定义、赋值
set 语句可以定义并为变量赋值。
set @var = value;
也足以应用select
into语句为变量开端化并赋值。那样须要select语句只好回到一行,可是足以是多少个字段,就表示同时为多少个变量进行赋值,变量的多寡必要与查询的列数一致。
还是能把赋值语句看作一个表明式,通过select执行到位。此时为了幸免=被看作关系运算符看待,使用:=代替。(set语句可以行使=
和 :=)。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;

select into 可以将表中询问得到的数额赋给变量。
-| select max(height) into @max_height from tb;

— 自定义变量名
为了防止select语句中,用户自定义的变量与系统标识符(平日是字段名)争辨,用户自定义变量在变量名前选择@作为开端符号。
@var=10;

– 变量被定义后,在全方位会话周期都有效(登录到退出)

–// 控制结构 ———-
— if语句
if search_condition then
statement_list
[elseif search_condition then
statement_list]

[else
statement_list]
end if;

— case语句
case value when [compare-value] then result
[when [compare-value] then result …]
[else result]
end

— while循环
[begin_label:] while search_condition do
statement_list
end while [end_label];

– 假设需求在循环内提前终止
while循环,则要求运用标签;标签必要成对出现。

— 退出循环
退出整个循环 leave
脱离当前循环 iterate
透过退出的竹签决定退出哪个循环

社会保险,–// 内置函数 ———-
— 数值函数
abs(x) — 绝对值 abs(-10.9) = 10
format(x, d) — 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) — 向上取整 ceil(10.1) = 11
floor(x) — 向下取整 floor (10.1) = 10
round(x) — 四舍五入去整
mod(m, n) — m%n m mod n 求余 10%3=1
pi() — 得到圆周率
pow(m, n) — m^n
sqrt(x) — 算术平方根
rand() — 随机数
truncate(x, d) — 截取d位小数

— 时间日期函数
now(), current_timestamp(); — 当明天期时间
current_date(); — 当前天期
current_time(); — 当前时间
date(‘yyyy-mm-dd hh:ii:ss’); — 获取日期部分
time(‘yyyy-mm-dd hh:ii:ss’); — 获取时间有些
date_format(‘yyyy-mm-dd hh:ii:ss’, ‘%d %y %a %d %m %b %j’); —
格式化时间
unix_timestamp(); — 获得unix时间戳
from_unixtime(); — 从岁月戳得到时间

— 字符串函数
length(string) — string长度,字节
char_length(string) — string的字符个数
substring(str, position [,length]) —
从str的position开始,取length个字符
replace(str ,search_str ,replace_str) —
在str中用replace_str替换search_str
instr(string ,substring) — 重回substring第一次在string中冒出的岗位
concat(string [,…]) — 连接字串
charset(str) — 再次来到字串字符集
lcase(string) — 转换成小写
left(string, length) — 从string2中的右侧起取length个字符
load_file(file_name) — 从文件读取内容
locate(substring, string [,start_position]) —
同instr,但可指定开首地点
lpad(string, length, pad) —
重复用pad加在string初阶,直到字串长度为length
ltrim(string) — 去除前端空格
repeat(string, count) — 重复count次
rpad(string, length, pad) –在str后用pad补充,直到长度为length
rtrim(string) — 去除后端空格
strcmp(string1 ,string2) — 逐字符相比较两字串大小

— 流程函数
case when [condition] then result [when [condition] then result
…] [else result] end 多分支
if(expr1,expr2,expr3) 双分支。

— 聚合函数
count()
sum();
max();
min();
avg();
group_concat()

— 其余常用函数
md5();
default();

–// 存储函数,自定义函数 ———-
— 新建
create function function_name (参数列表) returns 重回值类型
函数体

– 函数名,应该合法的标识符,并且不应该与已有些根本字争执。

一个函数应该属于某个数据库,可以采用db_name.funciton_name的格局进行当前函数所属数据库,否则为当前数据库。

  • 参数部分,由”参数名”和”参数类型”组成。八个参数用逗号隔开。
  • 函数体由多条可用的mysql语句,流程控制,变量注脚等语句构成。
  • 多条语句应该利用 begin…end 语句块包蕴。
  • 一定要有 return 重返值语句。

— 删除
drop function [if exists] function_name;

— 查看
show function status like ‘partten’
show create function function_name;

— 修改
alter function function_name 函数选项

–// 存储进度,自定义功效 ———-
— 定义
积存存储进度 是一段代码(进程),存储在数据库中的sql组成。
一个囤积进程一般用于完结一段工作逻辑,例如报名,交班费,订单入库等。
而一个函数常常专注与某个意义,视为其余程序服务的,必要在其他语句中调用函数才得以,而存储进度不可能被其余调用,是投机执行
通过call执行。

— 创建
create procedure sp_name (参数列表)
过程体

参数列表:差别于函数的参数列表,要求指明参数类型
in,表示输入型
out,表示输出型
inout,表示混合型

专注,没有重返值。

/* 存储进程 */ ——————
仓储进度是一段可执行性代码的聚合。相比函数,更偏向于业务逻辑。
调用:call 过程名
— 注意

  • 从未再次回到值。
  • 只得单独调用,不可夹杂在其余语句中

— 参数
in|out|inout 参数名 数据类型
in 输入:在调用进程中,将数据输入到进度体内部的参数
out 输出:在调用进程中,将进程体处理完的结果回到到客户端
inout 输入输出:既可输入,也可输出

— 语法
create procedure 进度名 (参数列表)
begin
过程体
end

/* 用户和权力管理 */ ——————
用户音信表:mysql.user
— 刷新权限
flush privileges
— 增加用户
create user 用户名 identified by [password] 密码(字符串)

  • 总得拥有mysql数据库的全局create user权限,或享有insert权限。
  • 只可以创造用户,不可以加之权力。
  • 用户名,注意引号:如 ‘user_name’@’192.168.1.1’
  • 密码也需引号,纯数字密码也要加引号

    要在纯文本中指定密码,需忽略password关键词。要把密码指定为由password()函数重临的混编值,需包含关键字password
    — 重命名用户
    rename user old_user to new_user
    — 设置密码
    set password = password(‘密码’) — 为眼前用户设置密码
    set password for 用户名 = password(‘密码’) — 为指定用户安装密码
    — 删除用户
    drop user 用户名
    — 分配权限/添加用户
    grant 权限列表 on 表名 to 用户名 [identified by [password]
    ‘password’]

  • all privileges 代表所有权限
  • *.* 表示所有库的保有表
  • 库名.表名 表示某库下边的某表
    — 查看权限
    show grants for 用户名
    — 查看当前用户权限
    show grants; 或 show grants for current_user; 或 show grants for
    current_user();
    — 撤销权限
    revoke 权限列表 on 表名 from 用户名
    revoke all privileges, grant option from 用户名 — 撤废所有权力
    — 权限层级
    — 要运用grant或revoke,您必须怀有grant
    option权限,并且您必须用于您正在授予或废除的权力。
    全局层级:全局权限适用于一个加以服务器中的所有数据库,mysql.user
    grant all on *.*和 revoke all on *.*只授予和注销全局权限。
    数据库层级:数据库权限适用于一个加以数据库中的所有目的,mysql.db,
    mysql.host
    grant all on db_name.*和revoke all on
    db_name.*只授予和撤回数据库权限。
    表层级:表权限适用于一个给定表中的所有列,mysql.talbes_priv
    grant all on db_name.tbl_name和revoke all on
    db_name.tbl_name只授予和撤回表权限。
    列层级:列权限适用于一个给定表中的单一列,mysql.columns_priv
    当使用revoke时,您必须指定与被授权列相同的列。
    — 权限列表
    all [privileges] — 设置除grant option之外的装有不难权限
    alter — 允许使用alter table
    alter routine — 更改或吊销已囤积的子程序
    create — 允许使用create table
    create routine — 创制已囤积的子程序
    create temporary tables — 允许采纳create temporary table
    create user — 允许利用create user, drop user, rename user和revoke all
    privileges。
    create view — 允许采用create view
    delete — 允许利用delete
    drop — 允许行使drop table
    execute — 允许用户运行已囤积的子程序
    file — 允许行使select…into outfile和load data infile
    index — 允许使用create index和drop index
    insert — 允许行使insert
    lock tables — 允许对你拥有select权限的表使用lock tables
    process — 允许行使show full processlist
    references — 未被实施
    reload — 允许利用flush
    replication client — 允许用户明白从属服务器或主服务器的地点
    replication slave —
    用于复制型从属服务器(从主服务器中读取二进制日志事件)
    select — 允许利用select
    show databases — 突显所有数据库
    show view — 允许使用show create view
    shutdown — 允许利用mysqladmin shutdown
    super — 允许使用change master, kill, purge master logs和set
    global语句,mysqladmin
    debug命令;允许你连接(一回),即便已落得max_connections。
    update — 允许使用update
    usage — “无权力”的同义词
    grant option — 允许授予权限

/* 表维护 */
— 分析和存储表的根本字分布
analyze [local | no_write_to_binlog] table 表名 …
— 检查一个或七个表是不是有荒唐
check table tbl_name [, tbl_name] … [option] …
option = {quick | fast | medium | extended | changed}
— 整理数据文件的碎片
optimize [local | no_write_to_binlog] table tbl_name [,
tbl_name] …

/* 杂项 */ ——————
1.
可用反引号(`)为标识符(库名、表名、字段名、索引、别名)包裹,以防止与根本字重名!粤语也得以看作标识符!

  1. 种种库目录存在一个封存当前数据库的选项文件db.opt。
  2. 注释:
    单行注释 # 注释内容
    多行注释 /* 注释内容 */
    单行注释 — 注释内容
    (标准sql注释风格,须求双破折号后加一空格符(空格、tab、换行等))
  3. 格局通配符:
    _ 任意单个字符
    % 任意四个字符,甚至包蕴零字符
    单引号需要展开转义 \’
  4. cmd命令行内的口舌截至符可以为 “;”, “\g”,
    “\g”,仅影响突显结果。其余地方或者用分号停止。delimiter
    可修改当前对话的话语为止符。
  5. sql对大小写不灵活
  6. 撤销已有语句:\c

发表评论

电子邮件地址不会被公开。 必填项已用*标注