SQl语句藏(转)

/* 启动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));
  2. unique 唯一索引(唯一约束)
    让某字段的值吗无能够再次。

  3. null 约束
    null不是数据类型,是排的一个特性。
    代表即排是否好啊null,表示什么都未曾。
    null, 允许为空。默认。
    not null, 不允为空。
    insert into tab values (null, ‘val’);
    — 这意味着将首先独字段的值设为null, 取决于该字段是否允许吗null

  4. default 默认值属性
    此时此刻字段的默认值。
    insert into tab values (default, ‘val’); —
    此时代表强制行使默认值。
    create table tab ( add_time timestamp default current_timestamp
    );
    — 代表以目前岁月的时刻戳设为默认值。
    current_date, current_time

  5. auto_increment 自动增长约
    自动增长必须也索引(主键或unique)
    只得存在一个字段为机关增长。
    默认为1起来活动增长。可以经表属性 auto_increment = x进行设置,或
    alter table tbl auto_increment = x;

  6. comment 注释
    规章:create table tab ( id int ) comment ‘注释内容’;

  7. 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,级联操作。主表数据被更新(主键值更新),从表也于更新(外键值更新)。主表记录被删,从表相关记录也受删去。
    2. set
      null,设置为null。主表数据给更新(主键值更新),从表的外键被安装为null。主表记录被删去,从表相关记录外键被设置成null。但注意,要求该外键列,没有not
      null属性约束。
    3. 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子句后,可以同时起多独说明。
— 基本上个表会横向叠加到一块儿,而数会形成一个笛卡尔积。
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)
    事务前后数据的完整性必须保持一致。
  3. 政工开始和结束时,外部数据一致
  4. 当一切事情进程遭到,操作是连续的
  5. 隔离性(Isolation)
    大多只用户并发访问数据库时,一个用户的政工不能够给外用户之物所干扰,多单冒出事务间的数如果互相隔离。
  6. 持久性(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. 可用反引号(`)为标识符(库名、表名、字段名、索引、别名)包裹,以避免和根本字重名!中文也可以看作标识符!
  2. 每个库目录存在一个保留时数据库的选择项文件db.opt。
  3. 注释:
    单行注释 # 注释内容
    差不多行注释 /* 注释内容 */
    单行注释 — 注释内容
    (标准SQL注释风格,要求对解除折号后加一空格符(空格、TAB、换行等))
  4. 模式通配符:
    _ 任意单个字符
    % 任意多单字符,甚至包括零字符
    单引号需要开展转义 ‘
  5. CMD命令行内的说话结束符可以为 “;”, “\G”,
    “\g”,仅影响著结果。其他地方还是用分号结束。delimiter
    可改时对话之语结束符。
  6. SQL对大小写不灵活
  7. 散已发出报告句:\c

发表评论

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