MySQL基础

数据库

• 表格

1. 索引
2. 外键
3. 字段
4. 触发器

• 视图
• 过程
• 函数
• 事件

数据类型

整数类型	字节数	无符号最大值
tinyint	1	255
smallint	2	65535
mediumint	3	16777215
int	4	4294967295
integer	4	4294967295
bigint	8	18445744073709551615

浮点定点类型	字节数	无符号范围
flout	4	0,1.17549351E-38~3.402823466E+38
double	8	0,2.2250738585072014E-38~1.7976931348623157E+38
decimal(m,d)	m+2	同double ( ++m表示总长度，d表示小数位。四舍五入存储。++)

日期类型	字节数	取值
year	1	1910~2155
date	4	1000-01-01~9999-12-31
time	3	-838:59:59~838:59:59
datetime	8	1000-01-01 00:00:00~9999-12-31 23:59:59
timestamp	4	19700101080001~20380119111407

字符串类型	说明
char	固定长度
varchar	可变长度
text	tinytext,text,mediumtext,longtext
enum	只能取一个元素
set	集合，能取多个

二进制类型	说明
binary(m)	字节数为m，允许长度为0~m的定长二进制字符串
varbinary(m)	允许长度为0~m的变长二进制字符串，字节数为值长度加1
bit(m)	m位二进制数据，最多255个字节
tinyblob	可变长二进制数据，最多255个字节
blob	可变长二进制数据，最多（2的16次方-1）个字节
mediumblob	可变长二进制数据，最多（2的24次方-1）个字节
longblob	可变长二进制数据，最多（2的32次方-1）个字节

基本操作

数据库

• show databases;
• create database ++db_name++;
• drop database ++db_name++;

表格

• create table ++t_name++ ( ++name++ type […] , ++name++ type […] , …);

[可选项]	作用
primary key	主键
foreign key	外键，与某表的主键关联
not null	不能空
unique	位移
auto_increment	自动增加
default	设置默认值

• describe(desc) ++t_name++ ; 基本结构
• show create table ++t_name++ ; 详细结构
• alter table ++t_name_old++ rename ++t_name_new++ ;
• alter table ++t_name_old++ change ++name_old++ ++name_new++ type
• alter table ++t_name_old++ add ++name++ type […] [first|after ++name2++]
• alter table ++t_name_old++ drop ++name++
• drop table ++t_name++

查询

• select * from ++t_name++ ;
• select ++name1++,++name2++,++name3++,… from ++t_name++ ;
• select ++name++… from ++t_name++ where … ;
• select ++name++… from ++t_name++ where ++name++ [not] in (…) ;
• select ++name++… from ++t_name++ where ++name++ [not] between ++value1++ and ++value++ ;
• select ++name++… from ++t_name++ where ++name++ [not] like ‘…’; % 表示任意字符，_ 表示单个字符
• select ++name++… from ++t_name++ where ++name++ is [not] null ;
• select ++name++… from ++t_name++ where … and …;
• select ++name++… from ++t_name++ where … or …;
• select distinct ++name++… from ++t_name++ ; 去重复查询
• … order by ++name++ [asc|desc] ; 对查询结果排序
• group by ++name++ [having …][with rollup] ; 分组查询

1. 单独使用无意义
2. 与group_concat()函数一起使用
3. 与聚合函数一起使用
4. 与having一起使用（限制查询结果）
5. 与with rollup一起使用（最后加入一个总和行）

• select ++name++… from ++t_name++ limit ++start_index++,++num++ ; 分页查询

聚合函数

• count()
• sun()
• avg()
• max()
• min()

连接查询

1. 内连接查询两个或者以上的表，符合条件的数据

• select ++name++… from ++t_name1++,++t_name2++,… where … ;

2. 外连接可以查出一张表(根据left|right的选择)的所有信息，附加符合条件的另一个表的数据

• select ++name++… from ++t_name1++ left|right join ++t_name2++ on ++t_name1.name1++ = ++t_name2.name2++ ;

子查询

• in 基于一个条件的结果里查询

• < >= <= 比较查询

• exists 在子层查询有结果的基础上进行查询
• any 满足任一条件
• all 满足所有条件

合并查询结果

• union 合并数据，去掉相同的记录
• union all 不去掉相同的记录

别名

• ++t_name++ ++t_n++
• ++name++ [as] ++n++

插入数据

• insert into ++t_name++ values (value1,value2,value3,…);
• insert into ++t_name++(name1,name2,name3,…) values (value1,value2,value3,…),(value1,value2,value3,…) , … ;
• update ++t_name++ set name1=value1,name2=value2, … where … ;
• delete from ++t_name++ [where …]

索引

索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度。

• pros：提高查询数据的速度
• cons: 创建和维护索引的时间增加

索引种类	说明
普通索引	可以创建在任何数据类型中
唯一性索引	具有唯一值的数据才行
全文索引	设置fulltext，只能char,varchar,text类型，提高查询较大字符串类型的速度，只有MyIsSAM引擎支持
单列索引	单个字段的索引
多列索引	在表的多个字段上创建索引
空间索引	设置spatial参数，只能建立在空间数据类型，提高系统获得空间数据的效率，只有MyISAM引擎可用
聚簇索引	把该索引作为数据存放的物理位置顺序，稀疏索引，数据页上一级的索引页存储的是页指针，提高多行检索的速度
非聚簇索引	非物理顺序，单行检索，密集索引，数据页上一级的索引页存储的是每一个数据行的行指针

• create table ++t_name++ (++name++ type […],++name++ type […], … ,
  [unique|fulltext|spatial] index|key index_name [nickname] (++name1++ [length] [asc|desx]) );
• create [unique|fulltext|spatial] index|key ++index_name++ on ++t_name++ (++name1++ [length] [asc|desx]) );
• alter table ++t_name++ add [unique|fulltext|spatial] index ++index_name++ (++name1++ [length] [asc|desx]);
• drop index ++index_name++ on ++t_name++

视图

视图是虚拟的表，只存放了视图的定义，不存放数据。

• 作用

1. 操作简便化
2. 提高数据的安全性
3. 提高表的逻辑独立性

• create [algorithm = {undefined|merge|temptable}]
  view ++v_name++ [(++name_list++)]
  as select …
  [with [cascaded|local] check option] ;

1. alogrithm 是可选参数，表示视图选择的算法。
2. name_list指定了视图中各种属性的名词，默认情况下与select语句中的查询的属性相同。
3. select语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中。
4. with check option表示更新视图时要保证在该视图的权限范围之内。
5. undefined 表示自动选择算法
6. merge 表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
7. temptable 表示将视图的结果存入临时表，然后使用临时表执行语句。
8. cascaded 表示更新视图时要满足所有相关视图和表的条件，该参数为默认值。
9. local 表示更新视图时，要满足该视图本身的定义条件即可。

• describe ++v_name++
• show table status like ‘…’
• show create view ++v_name++
• create|replace|alter [algorithm = {undefined|merge|temptable}]
  view ++v_name++ [(++name_list++)]
  as select …
  [with [cascaded|local] check option] ;
• insert into v_name values(…);
• update ++v_name++ set ++name1++=value1,++name2++=value2,… where …;
• delete from ++v_name++ where …;
• drop view [if exists] ++v_name++ [restrict|cascade]

触发器trigger

触发器由事件来触发某个操作。事件包括insert、update、delete语句。

• create trigger ++tri_name++ brfore|after ++tri_event++
  on ++t_name++ for each row ++exe_sentence++;
• create trigger ++tri_name++ brfore|after ++tri_event++
  on ++t_name++ for each row begin ++exe_sentence++ end;
• show triggers;
• drop trigger tri_name;

常用函数

date

• curdate() 当前日期
• curtime() 当前事件
• month(d) 日期d的月份值

string

• char_length(s)
• upper(s)
• lower(s)

math

• abs()
• sqrt() 平方根
• mod() 求余数

加密

• password(str) 一般对用户的密码加密，不可逆
• md5(str) 普通MD5加密，不可逆
• encode(str,pawd_str) 加密函数，结果是个二进制数，必须使用blob类型的字段来保存它
• decode(crypt_str,pswd_str) 解密函数

存储过程和函数

是SQL语句的集合。是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

创建存储过程和函数

• create procedure ++p_name++ ([proc_parameter[,…]]) [characteristic…]
  begin routine_body end

1. proc_patameter 中每个参数由3部分组成。
   [in|out|inout] ++param_name++ type
2. characteristic有多个取值

characteristic	说明
language sql	默认取值，说明routine_body由SQL语句组成
[not]deterministic	指明存储过程的执行结果是否是确定，即相同的输入，输出是否一定相同。默认情况下，结果非确定
{contains SQL|no SQL|reads SQL data|modifies SQL data}	指明子程序使用SQL语句的限制
SQL security	指明谁有权限来执行

{contains SQL|no SQL|reads SQL data|modifies SQL data}	说明
contains SQL	包含SQL语句，不包含读或写数据的语句，默认值
no SQL	没有SQL语句
reads SQL data	包含读数据的语句
modifies SQL data	包含写数据的语句

SQL security	说明
definer	定义者自己才能执行，默认值
invoker	调用者能执行

• create function ++p_name++ (func_parameter[,…])
  returns type
  [characteristic…] ++routine_body++;

变量的使用

• declare ++var_name++[…] type [default ++value++];
• set ++var_name++ = expr[,++var_name++=expr]…;
• select ++col_name++[,…] into var_name[,…] from ++t_name++ where conditon ;

游标的使用

查询语句可能查询出多条记录，在存储过程中函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括++申明++游标，++打开++游标，++使用++游标和++关闭++游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

• declare ++cursor_name++ cursor for ++select_statement++;
• open ++cursor_name++;
• fetch ++cursor_name++ into ++var_name++[,++var_name++ …] ;
• close ++cursor_name++;

流程控制

用于存储过程和函数中。MySQL中有if,case,loop,leave,iterate,repeat和while语句。

• delimiter && … && delimiter可以执行SQL语句块
• if 语句

    if search_condition then statement_list
    [elseif search_condition then statement_list]...
    [else statement_list]
end if

-case 语句

case case_value
    when when_value then statement_list
    [when when_value then statement_list]...
    [else statement_list]
end case

• loop,leave 语句

//loop使某些语句重复执行，本身没有停止循环的语句
[begin_label: ]loop
    statement_list
end loop[end_label]

//leave 语句用于 跳出循环控制
leave label

• iterate 语句

//跳出本次循环
iterate label

• repeat 语句

//满足特定条件时，就会跳出循环语句。
[begin_label:] repeat
    statement_list
    until search_condition
end repeat[end_label]

• while 语句

[begin_label:]while search_condition do
    statement_list
end while[end_label]

调用存储过程和函数

• call ++p_name++([parameter[,…]);
• ++fun_name++([parameter[,…)

查看存储过程和函数

• show {procedure|function} status [like ‘pattern’];
• show create {procedure|function} ++sp_name++;

修改存储过程和函数

• alter {procedure|function} ++sp_name++
  [characteristic…]

删除存储过程和函数

• drop {procedure|function} ++sp_name++

数据备份与还原

数据备份

• mysqldump -u ++username++ -p ++db_name++ ++table1++ ++table2++ … > ++BackupName++.sql

数据还原

• Mysql -u root -p [++db_name++] < ++backup++.sql