注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

老狗的博客

尽管每一步都很微小,但我确认我在进步

 
 
 

日志

 
 
关于我
sky

认真生活,努力工作 热爱技术,关注DB,存储,分布式,中间层,java,c++,php

mysql语法学习  

2012-05-07 18:07:45|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

首先创建了一张表test4,采用的存储引擎是innodb,表中有8388608条记录,mysql 版本5.0.45

mysql> show create table test4\G
*************************** 1. row ***************************
       Table: test4
Create Table: CREATE TABLE `test4` (
  `c1` int(10) unsigned NOT NULL auto_increment,
  `c2` int(10) unsigned default NULL,
  `c3` int(10) unsigned default NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=8388609 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> select count(*) from test4;
+----------+
| count(*) |
+----------+
|  8388608 | 
+----------+
1 row in set (1.50 sec)

1. 添加一列,并设置默认值

操作:在test4表中添加了新列c4,设置默认值为1
结果:花费1min 40秒,新添加的列在原有的数据中被设置了默认值
论断:创建临时表,将花费较多时间,会改变已有数据
mysql> alter table test4 add column c4 int unsigned default 1;
Query OK, 8388608 rows affected (1 min 40.63 sec)
Records: 8388608  Duplicates: 0  Warnings: 0

mysql> select * from test4 limit 2;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 |    1 |    2 |    1 | 
|  2 |    1 |    2 |    1 | 
+----+------+------+------+
2 rows in set (0.00 sec)

2. 修改已有列默认值

操作:修改已有列t4的默认值为2
结果:花费0.13秒,表中已有数据的值没有改变
论断:修改已有列默认值,不会改变列的数据,也不会创建临时表,速度非常快,几乎没有影响,只会对新插的数据产生影响
mysql> alter table test4 alter column c4 set default 2;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test4 limit 2;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 |    1 |    2 |    1 | 
|  2 |    1 |    2 |    1 | 
+----+------+------+------+
2 rows in set (0.03 sec)

3. 修改表名

操作:修改表名test4为test5
结果:花费0.04s
论断:修改表名,不会创建临时表,速度非常快

mysql> alter table test4 rename to test5;
Query OK, 0 rows affected (0.04 sec)

4. 将表移动到其他库

操作:将表test5从test1库移动到test库,然后test库移回来
结果:花费0.04s
论断:将表在库间移动,不会创建临时表,速度非常快
mysql> alter table test5 rename to test.test100;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table test.test100 rename to test1.test5;
Query OK, 0 rows affected (0.01 sec)

5. 数据库重命名<禁用>

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
这个语句在mysql 5.1.17中被添加,然后又被删除
原因在于,这个语句会带来误会,它不仅仅改名字,而且丢失db中的数据,所以在5.1.23版本中把它废掉了
所以,这个命令一般版本不支持,即使支持也不要随便用

6. 版本升级带来数据库名字的问题

当从5.0 升级到5.1之后,会遇到db名字改变的问题,如a-b-c会变成#mysql50#a-b-c
可以通过alter database dbname upgrade data direcotry name;进行修改

7. 外键语法

外键的语法如下:
constraint [constraint_name] foreign key [index_name] 
(column_name,...) references 
tablename(column_name)
on delete reference_option
on update reference_option;

需要注意的地方:
a. constraint_name 和 index_name不同,在建立外键的同时会默认为这一列建立索引结果,constraint_name指的是外键约束的名字,index_name指的是索引的名字
实例:
mysql> create table testchild(bookid int unsigned not null auto_increment primar
y key,bookname varchar(30),userid int unsigned,constraint fk_userid foreign key
fk_userid(userid) references testparent(userid));

Create Table: CREATE TABLE `testchild` (
  `bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bookname` varchar(30) DEFAULT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`bookid`),
  KEY `fk_userid` (`userid`),
  CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `testparent` (`userid
`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk


b. 表必须是innodb表,而且不是临时表
c. innodb需要在引用表和被引用表的列上建立索引
d. reference option 可以有四个:set null,cascade,no action,restrict,默认为restrict,等同于no action, 在被引用表上删除数据的时候,如果引用表中还有相关数据,则无法主表无法删除

8. 触发器语法
mysql> create table t1(a int,b int)engine=innodb;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON t1
    -> FOR EACH ROW SET @sum = @sum + NEW.a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER update_sum BEFORE update ON t1
    -> FOR EACH ROW SET @sum = @sum + NEW.a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER ins_sum1 BEFORE INSERT ON t1
    -> FOR EACH ROW SET @sum = @sum + NEW.a;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
注意:
1. mysql不支持在同一个表上对同一个触发时机(before/after)和事件(insert/update/delete)建多个触发器,但是如果两个触发器的触发时机或者触发事件不一样的话,是可以建多个触发器的
2. before/after触发器:before可以修改NEW值,而after不可以修改NEW值
3. mysql触发器不支持动态sql

9. 触发器中的控制逻辑以及语句
判断:
if ..then
elseif..then
else
end if;

循环

变量声明/赋值
declare a int;
a:=3;


10. 修改索引
据了解,没有直接修改语句,必须删除重建

11. 修改表的字符集
则要重建表,花费大量时间,不管采用什么语法
mysql> alter table testalter convert to character set utf8;
Query OK, 4194304 rows affected (1 min 16.09 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

mysql> alter table testalter default character set gbk;
Query OK, 4194304 rows affected (1 min 10.34 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

12. 修改表引擎
alter table testalter engine=mysiam;

mysql> alter table testalter engine=innodb;    
Query OK, 4194304 rows affected (1 min 10.04 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

结论:转换表引擎也要进行表的重建

13. 修改某个表的auto_increment值
mysql> alter table testalter auto_increment=100000000;
Query OK, 4194304 rows affected (1 min 10.65 sec)
Records: 4194304  Duplicates: 0  Warnings: 0

结论:修改auto_increment值,表也要重建

14. 修改auto_increment_increment/auto_increment_offset
修改整个数据库的auto_increment_increment和auto_increment_offset

mysql> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     | 
| auto_increment_offset    | 1     | 
+--------------------------+-------+
2 rows in set (0.00 sec)

15. 查看mysql字符集
show character set;

16. 查看某个字符集对应的所有collation
show collation like 'gbk%';

ci: case insensitive
cs: case sensitive
bin: 二进制格式

17. mysql 时间函数
now(),current_timestamp()指这条sql语句执行时候的时间,一旦sql开始执行,时间就不会变化
sysdate(),是改函数运行时候的时间

mysql> select now(),current_timestamp(),sysdate(),sleep(2),now(),current_timestamp(),sysdate()\G
*************************** 1. row ***************************
              now(): 2012-11-10 18:47:55
current_timestamp(): 2012-11-10 18:47:55
          sysdate(): 2012-11-10 18:47:55
           sleep(2): 0
              now(): 2012-11-10 18:47:55
current_timestamp(): 2012-11-10 18:47:55
          sysdate(): 2012-11-10 18:47:57
1 row in set (2.00 sec)

mysql> select date_format(now(),'%Y%m%d');
+-----------------------------+
| date_format(now(),'%Y%m%d') |
+-----------------------------+
| 20121110                    | 
+-----------------------------+
1 row in set (0.00 sec)

18. 计算距离现在10天前的时刻
select date_sub(now(),interval 14 day);

19. 号称最快的行号计算
select id,@a:=@a+1 from user,(select @a:=0) t;
 
分析如下:
1. 首先有user表,如下:
mysql> select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | liud     |
|    5 | zhangsna |
+------+----------+
2 rows in set (0.00 sec)
2. 其次有t表,如下:
mysql> select @a:=0;
+-------+
| @a:=0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)
 
3. 然后两表进行笛卡尔积运算
mysql> select * from user,(select @:=0) t;
+------+----------+------+
| id   | name     | @:=0 |
+------+----------+------+
|    1 | liud     |    0 |
|    5 | zhangsna |    0 |
+------+----------+------+
2 rows in set (0.00 sec)
 
4. 最后,从笛卡尔积中选出
mysql> select b.id,@a:=@a+1 from (select * from user,(select @:=0) t) b;
+------+----------+
| id   | @a:=@a+1 |
+------+----------+
|    1 |        1 |
|    5 |        2 |
+------+----------+
2 rows in set (0.00 sec)
 
这就是整体过程的原理,没有任何神秘之处
20. in/or结果的有序现象

一直很好奇in/or (list)的实现,也一直很好奇,比如
mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(60) default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> select * from user where name in ('a12','a11');
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 100 | a11  |    2 | 
| 200 | a11  |    2 | 
|   2 | a12  |    2 | 
+-----+------+------+
3 rows in set (0.00 sec)


mysql> alter table user drop index idx_name;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> select * from user where name in ('a12','a11');
+-----+------+------+
| id  | name | age  |
+-----+------+------+
|   2 | a12  |    2 | 
| 100 | a11  |    2 | 
| 200 | a11  |    2 | 
+-----+------+------+
3 rows in set (0.01 sec)
虽然我们并没有使用order by name选项,但是最终的结果就是以order by name来进行展现的,
更有意思的是,每个name内部以id主键的顺序进行展现,把name上的索引删除后,则输出没有按照name的顺序

个人猜想的原因是:如果走name索引的话,则会首先对于列表进行排序,然后逐个的在辅助索引中进行,如上,
首先对于('a12','a11')进行排序得到如下('a11','a12'),然后首先用'a11'对于索引进行探查,获取了一组id,然后再用类似于
select *  from user where id in (list)的形式回表获取其他数据

在某些时候我们可以利用这些特性来避免进行排序

21. kill 语句
KILL [CONNECTION | QUERY] thread_id
1. 每一个同mysql建立的连接,mysqld都会分配一个线程来处理,kill connection thread_id,则会将整个线程kill掉,koll query则会保留线程,只是从运行的query中中断出来
2. thread_id可以通过show full processlist查询到

22. 查看授权
用户登录以后可以通过show grants查看

如果要查看他人的则可以通过show grants for user@host查看
mysql> grant select,insert,update,delete on test.* to test4@'127.0.0.1' identified by '123';  
Query OK, 0 rows affected (0.00 sec)

mysql>  revoke insert,delete,update on test.mytest from test4@'127.0.0.1'; 
ERROR 1147 (42000): There is no such grant defined for user 'test4' on host '127.0.0.1' on table 'mytest'
mysql>  revoke insert,delete,update on test.* from test4@'127.0.0.1';  
Query OK, 0 rows affected (0.00 sec)

注意假设以db为单位来进行授权,则不能按照表revoke权限

23. create table if not exists tbname
create table tbname区别

这个语句在某些情况下面还是比较好用的,如想在丛库上建表,后来想在主库上建表,这个时候可以使用create table if not exists语句

24. insert into tbname(xx,xx,xx) values(xx,xx,xx) on duplicate key update ....

举例:如果t1的a字段为主键,b字段有唯一索引,则
INSERT INTO t1(a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

等价于:
1. insert into t1(a,b,c) values(1,2,3)
2. 如果冲突,则使用update t1 set a=1,b=2,c=3 where a= 1 or b=2 limit 1;

注意: 
1. 可以使用values(colname)来提取values值
2. 相对于使用两条sql语句来说,效率更高,避免了一次传输,解析的代价,业务端使用也更方便
3. update的条件是or关系

25. select ...for update 和select ... lock in share mode
1. 对于Innodb,两者没有区别
2. 对于myisam,两者都没有作用


26. 表t(c1,c2)中, 找出c2中既有有'a',‘b',的c1列

c1   c2

1    'a'

1   'b'

2   'a'

2   'c'

 

select a.c1

from (select distinct c1 from t where c2='a') as a inner join (select distinct c1 from t where c2='b') as b on (a.c1 = b.c1);


27. 求两个集合交集的query

表user(id, name), balance(id, userid, save)

问题:找出有账号的用户

方法1:inner join

select * from balance b, user a where a.id =b.userid;

方法2:exists

select * from balance as b where exists (select id from user as a where a.id=b.userid);

方法3:in

select * from balance as b where b.userid in (select id from user);

问题2:找出没有账户的用户

方法1: left join

方法2: not exists

方法3: not in


28. 默认值

如果一个列<不允许为空>,但是又没有给定<显式默认值>
sql_mode在严格模式下,会报错

在非严格模式下,则会给出隐式默认值,隐式默认值规则如下:
1. 对于数字类型(整型/浮点): 隐式默认值为0,例外是auto_increment,默认是下一个自增值
2. 对于date和time, 默认是0000-00-00 和 00:00:00
3. 对于字符串,默认是空串'', 对于enum,默认是第一个枚举值

29. 计算每个分组的最大值
如果有一个成绩表如下:
score(id,  subject, grade, class, userid, score)
则需要求出数学这个科目中,每个班成绩做好的同学的userid以及成绩,年级,班级
1. 首先求出每个每个班数学成绩最高的分数
select class,max(socre)
from score
where subject='math'
group by class;

2. 求出这个谁有这个分数
select userid,score,grade,class
from score inner join
(select class,max(socre) as score
from score
where subject='math'
group by class) as t using(score, class);

30. load data
load data [local] infile 'abc'
[replace | ignore]
into table xxx
fields terminated by 'xx' enclosed by 'xx' escaped by 'xx'
lines terminated by 'xx'
(col1,col2...)

local 关键字 可以将本地的文件载入到服务器端
replace关键字可以对相同的行进行替换

31. trigger中的sql语句不会写入到binlog中

mysql> show create table test7;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------+
| test7 | CREATE TABLE `test7` (
`id` bigint(20) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table test8;
+-------+----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------+
| test8 | CREATE TABLE `test8` (
`id` bigint(20) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


delimiter //
create trigger trig_test7_insert after insert on test.test7
for each row
begin
insert into test.test8 values(new.id);
end;//
delimiter ;


# at 893276
#130424 16:52:05 server id 1 end_log_pos 90 Query thread_id=6230 exec_time=0 error_code=0
SET TIMESTAMP=1366793525/*!*/;
insert into test7 values(1)/*!*/;
# at 893366
#130424 16:52:05 server id 1 end_log_pos 893393 Xid = 3759523
COMMIT/*!*/;
DELIMITER ;
# End of log file


//验证触发器中的insert语句没有写入binlog

32. 如果是事务性表,trigger 和触发trigger的语句在一个事务中
mysql语法学习 - sky - 老狗的博客
 
33. 对于权限和test表,不进行复制

# replication
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%


34. replicate-do-db
语句级别复制
replicate-do-db=sales

use prices;
update sales.january set amount=amount+100;不会被复制
 
33. 索引长度限制

mysql> show create table novel_item;
+------------+------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
------------------------------------------------------------+
| Table | Create Table



|
+------------+------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
------------------------------------------------------------+
| novel_item | CREATE TABLE `novel_item` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_title` varchar(256) DEFAULT NULL,
`item_url` varchar(1024) DEFAULT NULL,
`book_id` int(11) DEFAULT NULL,
`seed_id` int(11) DEFAULT NULL,
`item_status` int(11) DEFAULT NULL,
`item_desc` varchar(256) DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+------------+------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> alter table novel_item add unique index uk_url(item_url);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes


mysql> alter table novel_item engine=innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table novel_item add unique index uk_url(item_url);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


  评论这张
 
阅读(2595)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018