mysql 学习笔记

mysql - study


1) db 
创建数据库   create database if not exists testdb;
删除数据库   drop database if  exists testdb;
数据库更名 导出数据，然后重建，然后在进行导入数据


2) table
create table table_user(
id int(4),
name varchar(16) default 'dd' not null,
primary key (id)
)
alter table table_user rename to user;
drop table if exists table_user;


3) 数据类型
  数值
  字符 其中text（文本），blob （二进制）
  日期：time date timestamp datetime
datetime(2)// 这是秒的精确度。
运算符
select 3+4 
select 3/5
select null + 4 >> null
比较运算
= !=  <>  between is null ,in  like 
逻辑运算符
not/! and /&& or ||  xor


4) 插入数据
insert into tablename values(1,3)
insert into tablename set name = 'username'

5) 更新
update tablename set name ='username'
6) 删除数据库
delete from tablename ;
truncate table ;
7) 函数
平均：avg,min,sum,count,max
round(0.2222,2)保留位数，并且四舍五入,truncate(0.222222,2)截断
字符串函数：lower,upper(),trim(),strcmp(hello,yes) -1, position(yes in 'hyesm')
replace(yes, 'es','hh') insert(yes,3,5,hhh) 替换contact('hello' ,'word')
替换contact_ws(';','hello' ,'word'),right('www',1) left('33333',3); 
lpad('111',11,'2') 左填充，有填充 rpad ,ltrim trim,rtrim
substring(),ascii('A')
时间函数： now,curtime,curdate日期没有时间。year('20130809')
month ,day,dayofyear('20130809');dayofweek('20130809'),hour,second()
date_add(now(),interval 3 month)
date_sub(now(),interval 3 month)


加密函数：password(),encrypt(),aes_encrypt(),encode() aes_decrypt(),MD5(),sha()


if(1>2,2,3)
ifnull(null,2)
case when
if else
nullif(1,2) 相等 返回null，否则第一个

格式化函数：
date_format(now(),'yyy-mm')
time_format('12:12:12','')
inet_aton()
cast('77' as signed) 
convert('220',signed )


连接： categores 2列4行    titles 4*8
交叉连接:两个表 categores ,titles  select * from categores coress join titles; = select * from categores,titles;  
无where 语句
内连接  :select cname ,tname form categores inner join titles where categores.cid=titles.cit and categores.cname = 'Current Affairs'
条件是用where
user, group , user_group
select user.uname as uname ,group.gname as gname from user,group,user_group where user.uid=user_group.uid and user_group.gid= group.gid;
 

外连接  : 条件是用 join on
左链接
select * from user left join user_group on user.uid= user_group.uid; 
uid,uname,uid,gid
保留 id join 上的 右边表的数据。同时追加 左边表的未join 上的数据，其他的数据字段null 可以用 where 过滤。
换言之： 全部保留左表的数据，右边表数据平行按照join 条件插入 列。
总结： 左表保留右表补字段
ON **.ID = **.ID = using(ID)
讲左表的数据全部追加到
左表全部保留，并将另一个表连接上去：作为一张表

右连接
：select * from user right join user_group using(gid)
gid,name,uid
保留右表： 左表插入进去字段。
自连接  :
menu
id label parent

select a.label as parent_label ,b.label as child_label from menu as a,menu as b where a.id=b.parent;
# union 去重复的
# union all 即使重复了，也展示出来
union # select symbole ,price from exchangeA where ***
union  
select symbole ,price from exchangeB where ***
union all;


create table categores(
cid int not null auto_increment,
cname varchar not null;
primary key cid,
unique key cname_UNIQUE (cname)
);

insert into categores (cname) values('sports');
insert into categores (cname) values('Current Affairs');
insert into categores (cname) values('Business');
insert into categores (cname) values('Technology');



子查询：
子查询性能差于上面的连接。
常用方式 (select where haveing)
select cname from user where cid=(select cid ,count(cid) from  *** where ** group by  haveing count(cid) =2)
select sname from services where sid in (select sid from ***)
select * from clients where exists (select bid from branch_service group by bid having count(bid) >=4)
条件满足返回结果。  
select * from user where exists (select name from user having length(name)>0)
select * from (select * from t ) t
select * from (select * from user right join user_group using(gid))
# 子查询 结合连接










事务 默认开启自动commit 
开启
start transaction ; bigin ;begin work;
insert into tablename() values();
commit ; or   rollback;

事务隔离级别：
1) seralizable : 性能最差，安全最高
2) repeatable ：默认的是 可重复读   会有幻读{本事务，中操作了后来其他事务之后新增的数据}
会在开启事务的时候进行不允许读。所以重复进行读取的时候，不会出现问题。
3) read commited ：（就是在读取数据时候不允许别人操作）会有重复读，幻读，没有脏读
开启事务的时候是允许读取的，但是一旦钱减少了，就能够立即读取到最新变化，不会脏读。
4) read uncommited： 性能最高，脏读，幻读，不可重复度
开启事务的时候，允许读取，此时可能已经修改了，但是有可能读取到的是别人未提交的值。
设置mysql 隔离级别
脏读，
幻读，刚开始读取和后来读取不一致。
不可重复度
不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
总结：
1） 所有操作都必须先来后到
2） 别人开启事务的时候不能够读取数据
3） 别人的事务开启的时候，可以操作，但不能读取到别人未提交的事务。
4） 可以读取到别人未提交的事务







distinct ,order by ,group by ,limit ,
explain 看sql计划
create index :   提高查询速度，降低增删该速度 ,在重复率低的加索引比较好，重复度比较高的时候效果不明显;
TYPE:
普通索引，
UNIQUE，这样在值不能重复。
PRIMARY KEY,
全文索引； 中文无效，需要用三方分词spinhx

创建-- 创建无索引的表格     可以在建表的时候进行创建， alter table ,create index 
create table testNoPK (  
 id int not null,  
 name varchar(10)  
);  
-- 创建普通索引  
create index IDX_testNoPK_Name on testNoPK (name);  
多列索引： 就是把两个值绑定作为一个独立的索引。
ALTER TABLE `table_name` ADD INDEX IDX_TABLENAME_Col1_Col2( `column1`, `column2` )
查询：
show index from  php3;

1.WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引
2.类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MySQL将无法使用索引


3.在JOIN操作中（需要从多个数据表提取数据时），mysql只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用


4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；如果条件是LIKE '%abc'，MYSQL将不使用索引。


5.在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。


6.如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。


7.索引有用的情况下就太多了。基本只要建立了索引，除了上面提到的索引不会使用的情况下之外，其他情况只要是使用在WHERE条件里，ORDER BY 字段，联表字段，一般都是有效的。 建立索引要的就是有效果。 不然还用它干吗？ 如果不能确定在某个字段上建立的索引是否有效果，只要实际进行测试下比较下执行时间就知道。


8.如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引


9.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引


10.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
删除：ALTER TABLE table_name DROP INDEX index_name；
drop index name on table_test ;
索引生效条件，
view ： create view viewname as select name ,age from user;
权限控制，创建view的时候，只给有权限的字段，还有就是简化复杂查询。不用嵌套很多了
如果视图是与物理表一一对应，则可以删除，修改，否则不可以。

分区，
=============================
1） 会员信息：常用的会员信息会放到一张表，其他的使用不多，和字段比较大的，放到其他的辅助表。
还有就是，会员名，这个字段可以设置为定长 char(20,可能浪费空间，但是提高速度。
2） 数据库所有的字段都给默认值，应为null 不好比较，并且运算都是null,
效率不高,影响提高索引效果.


因此,我们往往,在建表时 not null default '' 或0





















create table php3 (
name varchar(32),
gender char(1),
com varchar(20),
salary int,
fb int
) charset=utf8 ;