MySQL Basic

数据类型：

• 字符串: CHAR(n)、VARCHAR(n);
• 文本：TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT;
• 整型；TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT;
• 浮点数：FLOAT(4) DOUBLE(8) DECIMAL REAL;
• 二进制(可用来存储图片、音乐等): TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB;
• 日期和时间：DATE/TIME/DATETIME/TIMESTAMP/YEAR
  YEAR：范围1901~2155；尽量用四位数字或字符串表示；二位有歧义；

基本操作命令：

system clear						    #清屏语句，Linux下
sudo service mysql restart     			#重启服务
net start wampmysqld64					#Windows下
net stop wampmysqld64          			
mysql [-h hostname] –u root –p 			#命令行登陆(以root身份登录):
show engines 				   			#存储引擎
show variables like '%storage_engine%'  #默认引擎
Source dir/xx.sql                       #MySQL命令行运行外部脚本文件
ALTER DATABASE mydb2 character set utf8   #修改编码，注意mysql中utf-8编码不带“-”

create database mydb2;					#创建数据库
drop database db_name;  			    #删除数据库
show databases;						    #查看所有数据库
use db_name;						    #选择数据库
show tables;							#显示所有的表

create table tbl_name2				     #设置主键，创建索引
(
id  int(11) PRIMARY KEY,
field1  varchar(25),
field2 float,
field3 int(11)
),INDEX index_name (title(length));

create table tbl_name3
(
id  int(11),
field1  varchar(25),
field2 float,
field3 int(11),
PRIMARY KEY(id)
),UNIQUE indexName (title(length));

create table tbl_name4                #联合主键
(
field1  varchar(25),
field2 float,
field3 int(11),
PRIMARY KEY(field1,field2)
);

create table tbl_name                  #外键
(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_depId foreign key(deptId) references tb_dept1(id)
);

# 建表之后添加外键
# 适用于[父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败]的情况
alter table configure_key_value add foreign key(conf_biz_id) references configure_business(id) 
on update cascade on delete restrict;       

desc tbl_name;                          #显示表结构
describe tbl_name;
show columns from tbl_name;
show create table tbl_name;				#显示建表语句
drop table tbl_name;					#删除表
alter table tbl_name add field_name varchar(20) not null;
alter table wst_orders add(role text ,account text);	    #增加字段
alter table tbl_name drop column field_name;                #删除字段

update test set salary = salary+1 where address='shandong';						   #按条件更新记录
insert into tbl_name(field1,field2) values(value1,value2),(value11,value22);   #插入记录，可多条
delete from emp where ename='lisa' and sal=5000;  #删除记录

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter' #按字母顺序选取 Persons 表中 LastName 介于 Adams 和 Carter 的所有记录

SELECT * FROM from_data  LIMIT  nNumRecord   OFFSET nBaseRow
#表示从第nBaseRow行(基于0的索引)(包括该行)开始,取其后的nNumRecord  条记录

##copy表的数据到另一张表
insert into db1.table1 select * from db2.table2             #完全复制，Query OK
insert into db1.table1 select distinct * from db2.table2    #不复制重复纪录，未测试
insert into db1.table1 select top 5 * from db2.table2       #前五条纪录，测试提示错误
# mysql5.7 change passord
> update mysql.user set authentication_string=password('新密码') where user='用户';
> flush privileges;

create table new_table_name select * from exist_table   # 从select语句创建新的数据表 

存储引擎：

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个存储引擎，这两种引擎类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB更快，而InnoDB提供事务支持以及外部键等高级数据库功能。

可以看到只有InnoDB支持Transactions和Savepoints。

Alias

SQL 语句中，可以为表及字段（列）指定别名（Alias），别名是 SQL 标准语法，几乎所有的数据库系统都支持。通过关键字 AS 来指定。
使用表别名查询：

• 可以使 SQL 变得简洁而更易书写和阅读，尤其在 SQL 比较复杂的情况下；
• 另外除了使用别名来简化 SQL 外，有些时候一个表做自身关联时，必须要使用别名来当作两个表进行关联操作。

该处是thinkPHP的model中引用的模型的一个例子

$sql = 
SELECT shopId,shopSn,shopName,u.userName,shopAtive,shopStatus,gc.catName 
FROM
__PREFIX__shops s,__PREFIX__users u,__PREFIX__goods_cats gc
WHERE
gc.catId=s.goodsCatId1 AND s.userId=u.userId AND shopStatus=1 and shopFlag=1

匹配模式

MySQL中的LIKE关键字与匹配模式联合使用，用于确定所有符合给定匹配模式的记录。匹配模式类似正则表达式，它本身是一个字符串，这个字符串中的字符描述了某种句法规则。匹配模式中可以包括常规字符与通配符。

• % 表示零个或者多个任意字符，例如“s%”表示以s开头的任意字符串，“%s%”表示包含字符s的任意字符
  SELECT * FROM products AS p WHERE p.prod_name LIKE 'jet%';
• 匹配任意单个字符，例如“sm”表示任何以“sm”开头的长度为3的字符串，支持中文，可以对应任意一个中文字符
  如果匹配模式中没有包含通配符，则与使用“=”的运算效果是相同的。
  SELECT * FROM products AS p WHERE p.prod_name LIKE '_ ton anvil%';

正则表达式：REGEXP

SELECT* FROM products WHERE prod_name REGEXP ‘.000’ ORDER BY prod_id DESC;

多表查询

• tbl_a LEFT JOIN tbl_b ON conditon
  包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

select c.cust_id,o.order_num
from customers c left join orders o on c.cust_id=o.cust_id where o.order_num is null; #查询存在于左表而不存在于右表中的数据

• tbl_a RIGHT JOIN tbl_b ON condition
  与左外连接对称
• tbl_a INNER JOIN tbl_b ON condition
  只连接匹配的行，等价于直接查，可以省略inner join

select vend_name, prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
select vend_name, prod_name,prod_price from vendors , products where vendors.vend_id = products.vend_id; #两句效果一样的