插入记录
insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
也可以不用指定字段名称，但是values后面的顺序要和字段的排列顺序一致
inset into emp('zzx1','2000-01-01','2000',1);
含可空字段、非空但是含有默认值的字段、自增字段、可以不用再insert后的字段列表里面出现，values后面只写对应字段名称的value，没写的字段可以自动设置为null、默认值、自增的下一个数字
批量增加用逗号隔开
insert into dept values(5,'xxx'),(8,'xxx');
更新记录
update emp set sal=4000 where ename='xxx';
删除记录
delete from emp where ename='doney';
查询记录
select * from emp;
*表示所有记录，也可以用逗号隔开的字段来选择查询
查询不重复的记录
select distinct deptno from emp;
条件查询
用where关键字来实现，可以使用<>!=等多条件可以使用or、and等
排序和限制
desc和asc是排序关键字，desc是降序、asc是升序排列 ORDER BY 排序,默认是升序
select * from emp order by sal;
如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，如果只有一个排序字段，则相同字段将会无序排序
select * from emp order by deptno,sal desc;
限制
select * from emp order by sal limit 3;
//前者是起始偏移量，后者是显示行数
select * from emp order by sal limit 1,3;
limit 和order by 一起使用来做分页
聚合
用户做一下些汇总操作
sum(求和),count(*)(记录数)，max(最大值)，min（最小值）
with rollup 是可选语法，表示是否对分类聚合后的结果进行再汇总
having 表示对分类后的结果在进行条件的过滤。
select deptno,count(1) from emp group by deptno having count(1)>=1;
登入後複製
表格連接
大類上分為外連接和內連接 外部連結又分為左連接和右連接
左連接:包含所以的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄。 右連接：同上
select ename,detname from emp left join dept on emp.deptno=dept.deptno;
左连接和右连接可以相互转换
登入後複製
子查詢
select * from emp where deptno in(select deptno from dept);
如果子查询记录唯一，可以使用=替代in
select * from emp where deptno =(select deptno from dept limit 1);
登入後複製
記錄查詢 #將兩個表的資料依照一定的查詢出來後，將結果合在一起顯示
union all 是將結果集合並在一起，而union是將union all後的結果在進行一次distinct，去除重複
select deptno from emp union all select deptno from dept;
select demtno from emp union select deptno from dept;
登入後複製
? xxx 來查看
#
如果要查看类别 ? data types 具体的 ? int
查看语法 如 ? create table
登入後複製
資料型別
对于整形数据，MySql还支持在类型名称后面的小括号设置宽度，默认设置为int(11),配合zerofill,
当数字位数不够的时候，用字符‘0’填充
alter table t1 modify id1 int zerofill
create table vc (v varchar(4),c char(4))
insert into vc values('ab ','ab ');
selelct length(v),length(c) from vc
//4,2
登入後複製
登入後複製
枚舉
create table vc (v varchar(4),c char(4))
insert into vc values('ab ','ab ');
selelct length(v),length(c) from vc
//4,2
登入後複製
登入後複製
set類型 set類型可以一次選取多個成員
create table t2 (col set('a','b','c','d'));
INSERT into t2 VALUE ('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
对于（a,d，a)这个包含重复成员的集合只取一次 结果为’a,d'
登入後複製
運算子
p==/==除法获取商
MOD==%==除法获取余数
登入後複製
#=和<=>區別
不能用於null比較，後者可以
between 使用格式 a between min and max 等价于 a>=min and a<=max
in的使用格式 a in(value1,value2...);
like 使用格式如 a like %123%,当字符串含有123则返回1 否则返回0
REGEXP 使用格式 str REGEXP str_pat 当str字符串中含有str_pat 相匹配的字符串，则返回1
登入後複製
位元運算
# #運算子
作用
&
and
|
or
^
xor
~
位異或
>
位元右移
#<<
位元左移
常用函数
字符串函数
函数
功能
CONCAT(s1,s2,s3…)
连接s1到sn的字符串（任何字符串和null拼接都是null）
insert(str,x,y,instr)
将字符串str从x位置开始，y字符长的子串替换为字符串instr
lower(str)
将字符串str中所有字符变为小写
UPPER(str)
大写
LEFT(str,x)
返回字符串str最左边x个字符
RIGHT(str,x)
返回字符串str最右边的x个字符
LPAD(str,n,pad)
用字符串pad对str最左边进行填充，直到长度为n个字符串长度
PRPAD(str,n,pad)
用字符串pad对str最右边进行填充，直到长度为n个字符串长度
LTRIM(str)
去掉字符串str左侧的空格
RIGHT(str)
去掉字符串str行尾的空格
REPEAT(str,x)
返回str重复x次的结果
REPLACE(Str,a,b)
用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)
比较字符串s1和s2
TRIM(str)
去掉行尾和行头的空格
SUBSTRING(str,x,y)
返回字符串str x位置起y字符串长度的字串
数字函数
函数
功能
ABS(X)
返回x的绝对值
CEIL(X)
返回大于x的最小整数值
FLOOR(X)
返回小于x的最大整数值
MOD(x,y)
返回x/y的模
RAND()
返回0-1内的随机值
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)
返回数值x截断为y位小树的结果
日期和时间函数
函数
功能
CURDATE()
返回当前日期
CURTIME()
返回当前时间
NOW()
返回当前的日期和时间
UNIX_TIMESTAMP(date)
返回date的unix时间戳
FROM_UNIXTIME
返回UNIX时间戳的日期值
WEEK(date)
返回日期date为一年中的第几周
YEAR(date)
返回日期date的年份
HOUR(time)
返回time的小时值
MINUTE(time)
返回time的分钟值
MONTHNAME(date)
返回date的月份名
DATE_FROMATE(date,fmt)
返回按字符串fmt格式化日期date值
DATE_ADD(date,interval expr type)
返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)
返回起始时间expr和结束时间expr2之间的天数
流程函数
函数
功能
IF(value,t f)
如果value是真 返回 t；否则返回f
IFNULL(value1,value2)
如果value1不为空，返回value1，负责返回value2
CASE WHEN[value1] THEN[value2]…ELSE[default] END
如果value1是真，返回result1否则返回defalut
case [expr] WHEN[value1] THEN[value2]…ELSE[default] END
如果expr等于value1，返回result1否则返回defalut
实例
create table salary(userid int ,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary
select if(salary>2000,'high','low') from salary;
select ifnull(salary,0) from salary;
select case when salary <=2000 then 'low' else 'high' end from salary;
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
create table salary(userid int ,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary
select if(salary>2000,'high','low') from salary;
select ifnull(salary,0) from salary;
select case when salary <=2000 then 'low' else 'high' end from salary;
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
create table autoincre_demo (i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3')
alter table t2 engine=memory;
show TABLE status like 't2'
给memory表创建索引。可以指定hash索引还是btree索引
create index mem_hash using hash on tab_memory(city_id);
登入後複製
在启动MySql服务的时候使用–init-file选项，把INSERT INTO … SELECT或LOAD DATA INFILE这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表 服务器需要足够的内存来维持同一时间使用的MEMORY表，当不需要MEMORY表的内容，要释放MEMORY表的内存，执行DELETE FROM或 TRUNCATE TABLE 或者是DROP TABLE 每个MEMORY表中可以放置的数据量的大小，受max_heap_table_size系统变量的约束，初始值是16mb，可以根据需要加大、 MEMORY类型的存储引擎主要用在那些内容变化不平凡的表，或作为统计操作的中间结果表，便于高效的对中间结果进行分析并得到最终的统计结果。
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
begin
select inventory_id
from inventory
where film_id =p_film_id
and store_id=p_store_id
and inventory_in_stock(inventory_id);
SELECT found_rows() into p_film_count;
end $$
set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr;
登入後複製
定义条件和处理
delimiter $$
create procedure actor_insert()
begin
declare continue handler for sqlstate '23000' set @x2=1;
set @x=1;
insert into actor(actor_id,first_name,last_name) values(201,'test','201');
set @x=2;
insert into actor(actor_id,first_name,last_name) values(1,'test','1');
set @x=3;
end ;
$$
声明光标
declare cursor_name cursor for select_statement
open光标
open cursor_name
fetch光标
fetch cursor_name into var_name[,var_name]...
close光标
close cursor_name
delimiter $$
create procedure payment_stat()
begin
declare i_staff_id int;
declare d_amount decimal(5,2);
declare cur_payment cursor for select staff_id,amount from payment;
declare exit handler for not found close cur_payment;
set @x1=0;
set @x2=0;
open cur_payment;
REPEAT
FETCH cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
close cur_payment;
end;
$$
case
when i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
loop 和leave结合
create procedure actor_insert()
begin
set @x=0;
ins:loop
set @x=@x+1;
if @x=100 then
leave ins;
end if;
insert into actor(first_name,last_name) values('Test','201');
end loop ins;
end;
$$
inerate 语句作用是跳过当前循环的剩下语句，直接进入下一轮循环
create procedure actor_insert()
begin
set @x=0;
ins:loop
set @x=@x+1;
if @x=10 then
leave ins;
elseif mod(@x,2)=0 then
iterate ins;
end if;
insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x);
end loop ins;
end;
$$
repeat 语句 有条件的循环控制语句，当满足条件的时候退出循环
repeat
fetch cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
while
delimiter $$
create procedure loop_demo()
begin
set @x=1,@x1=1;
repeat
set @x=@x+1；
until @x>0 end repeat;
while @x<1 do
set @x=@x+1;
end while;
end;
$$
//创建事件调度器
CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECOND
DO
INSERT INTO dept(deptno,deptname)
VALUES(3,'3');
//查看本地调度器状态
show variables like '%scheduler%';
//打开调度器
set global event_scheduler=1;
//查看后台进程
show processlist;
//创建一个新的定时器 定时清空表，防止表变大，这类触发器非常适合去定期清空临时表或者日志表
create event trunc_test
on schedule every 1 minute
do truncate table test;
禁用调度器或者删除
alter event test_event_1 disable;
drop event test_event_1;
查詢一個actor_id=201,結果為空select * from actor where actor_id=201;
從表裡查詢同理結果為空### #########對表格加上寫入鎖定lock table actor write###############################對錶actor 的讀取操作被阻塞select * from actor where actor_id=201############插入資料insert into actor(actor_id,..)values(201,..)#######等待############回滾記錄rollback######等待#############用start transaction指令重新啟動一個交易######等待#####################開始一個事務，表鎖被釋放，可以查詢；select …where actor_id=201########### ###########查到資料############
在数据库DB1 启动一个分布式的一个分支事务,xid 的gtrid 为 “test”,bqual为”db1”: xa start ‘test’,’db1’;分支事务插入一个数据 insert into actor(…)values(…) 对分支事务1进行第一阶段提交，进入prepare状态 ：xa end ‘test’,’db1’; xa prepare ‘test’,’db1’
在数据库DB2 启动分布式事务 “test”的另外一个分支事务，xid的gtrid为”test”.bqual为”db2”; xa start ‘test’,’db2’: 分支事务2在表film_actor 更新数据 最后 xa end ‘test’,’db2’ xa prepare ‘test’,’db2’
range分区
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(store_id)(
PARTITION p0 VALUES less than (10),
PARTITION p1 VALUES less than (20),
PARTITION p2 VALUES less than (30)
);
//上述的分区方案将storid,1-9分到p0区，10-19分到p1区，等如果插入大于30，会出现错误，因为没有规则保护大于30的
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以
//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);
添加分区
alter table emp add partition(partition p3 values less than maxvalue);
maxvalue表示最大的可能的整数值
mysql 支持在values less than 语句中加入表达式
比如以日期作为分区
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(year(separated ))(
PARTITION p0 VALUES less than (1995),
PARTITION p1 VALUES less than (2000),
PARTITION p2 VALUES less than (2005)
);
MySQl 5.5改进了range分区给你，通过支持非整数分区，创建日期分区就不需要通过函数进行转换
partition by range(separated )(
PARTITION p0 VALUES less than ('1996-01-01'),
PARTITION p1 VALUES less than ('2001-01-01'),
PARTITION p2 VALUES less than ('2006-01-01')
);
create table rc3(a int,b int)
parition by range columns(a,b)(
parition p01 values less than(0,10),
parition p01 values less than(10,10),
parition p01 values less than(10,20),
parition p01 values less than(maxvalue,maxvalue)
)
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by hash(store_id)partitions 4;
登入後複製
这里创建了一个常规的hash 使用 partition by hash（expr）其中expr是某列值或一个整数值的表达式返回值。 partition num 对分区类型，分区键，分区个数进行定义，上述基于store_id列hash分区，表被分为4个分区
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by linear hash(store_id)partitions 4;
计算编号为n的分区
首先找到下一个大于等于num的2的幂，这个值设为v，v的计算公司
v=power(2,ceiling(log(2,num)))
=power(2,ceiling(log(2,4)))
=power(2,ceiling(2))
=power(2,2)
=4
其次设置n=f（column_list)&(v-1),现在计算store_id=234对应的n值
n=f(column_list)&(4-1)
=234&(4-1)
=2
当n》=num设置n=n&（v-1)
对于store_id=234由于n=2《4，所以直接判断这个会被存放到第二分区
key分区 按照key分区进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式，而KEY分区不行使用用户自定义的表达式，需要使用MySQl服务器提供的hash函数；同时hash分区只支持整数分区，而key分区支持除了blob or text类型外其他类型的列作为分区键
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by key (job)partitions 4;
如果不知道分区键，默认为主键，没有主键会选择非空唯一键作为分区键
create table ts(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased))subpartitions 2(partition p0 values less than (1900),partition p0 values less than (2000),partition p0 values less than (maxvalue))
删除分区
alter table emp_date drop partition p2;
增加分区
alter table emp_date add partition(partition p5 value less than(2025))
拆分p3分区，分为p2和p3分区
分区
alter table emp_date reorganize partition p3 into(partition p2 values less than(2005),parition p3 values less than (2015));
合并分区
alter table emp_date reogranize partition p1,p2,p3 into(partition p1 values less than (2015))
重新定义list分区时，只能重新定义相邻的分区，不能跳过list分区进行重新定义
登入後複製
hash&key管理
不能以range和list分区表删除分区的方式，而是跳过alter table coalesce partition 来合并或分区
以原先4个分区为例
alter table emp coalesce partition 2 //减少分区到2个
alter table emp coalesc partition 8 //不能增加分区
要增加分区
alter table emp add partition partitions 8;
