mysql常用命令

作者：zccst

2014-05-18 创建新表各类型模板
CREATE TABLE `aaa` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET utf8;

特殊情况
1，两个主键
primary key (id,name)
2，加唯一键约束
  UNIQUE KEY `name` (`name`)




2014-05-17 给新mysql数据库root用户设置新密码（改默认密码）
use mysql;//少了这个也不行
update user set password=PASSWORD('123456') where user='root';
flush privileges;



一、数据库的基本操作
create database xx
show databases     use dbName;
drop database xx
注：只有use databaseName;才能进行该数据库里的表操作，和表记录操作。

二、对数据库表本身的一些操作
1，操作表（整体）
查看所有表：show tables     
查看所有表状态：show tables  status
查看表结构：desc tbl_name
修改表名：   alter table t_book rename to bbb;
删除表：      drop table xxxx（注：删除多个表时请参考另一篇文章）
【一次删除多个表时：drop table `x_abc1`,`x_abc2`,`x_abc3`】
创建表：      create table ...(最重要，数据库设计)
mysql> create table fail_server_suit(
    -> id INTEGER UNSIGNED auto_increment primary key,
    -> suit_name varchar(30),
    -> cnt int(20) default '0',
    -> percent double(3,2) default null
    -> )engine=InnoDB default charset=utf8;
需要注意的是mysql各种数据类型。
(1)关于时间
datetime  日期和时间的组合。支持‘1000-01-01 00:00:00‘到‘9999-12-31 23:59:59‘
timestamp  时间戳。如果多个timestamp，第一个是时间戳，记录更新时更新。
另外，timestamp以UTC格式保存（ it stores the number of milliseconds）
时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。
(2)关于大数据存储longblob
mysql配置文件my*.cnf默认max_allowed_packet = 1M，但可修改为8M。

2，操作表的某一列
ALTER TABLE：添加，修改，删除表的列，约束等表的定义。
    添加列：alter table 表名 add column 列名 varchar(30);
    删除列：alter table 表名 drop column 列名;
    修改列名： alter table tbl_name change column update_at create_at timestamp;
    修改列属性：alter table t_book modify column_name varchar(22);

3，重要操作
查看已定义的表。如查看已定义表的外键情况。（可能在删除表数据前需要）show create table xxxx;



三、导入导出
1，导入导出sql脚本 
格式：mysqldump -u 用户名 -p 数据库名 > 导出的文件名
例：mysqldump -uroot -p123456 new_rss > new_rss07.sql
导入sql脚本:改变箭头即可。
mysqldump --help
几个比较实用的选项为：
    -c, --complete-insert                                    Use complete insert statements.
    --default-character-set=name                        Set the default character set.
    --fields-terminated-by=name                          Fields in the textfile are terminated by ...
    --ignore-table=name
    -n, --no-create-db
    -t, --no-create-info
    -d, --no-data                                               No row information.
    --tables                                                      Overrides option --databases (-B).
    -w, --where=name                                        Dump only selected records; QUOTES mandatory!

例子，导出数据库中的某个表（不是全部表）


2，导出查询结果到文本
select * from idc into outfile 'E:/sql/idc.txt';
这种情况每次会创建一个新的文件。
还有追加的方式：>。（即select * from idc into > 'E:/sql/idc.txt';）


四、单表的基本操作
1，增删查改
（1）插入记录
a.  insert into

--注：如果是default current timestamp，则时间字段由系统自动填写。如果是datetime，则为now()，不是'now()'；
insert into tbl_name (column1, column2, ..., columnN) values ( value1, value2, ..., valueN);
insert into user(name, age) values ('姚明', 25), ('乔布斯', 50), ('火星人', 250), ('太阳人', 2000);  //插入多条记录【注：这个只适合于mysql】

b.  select insert into

//指定列：
INSERT INTO `suit_type`(suit) SELECT distinct suit FROM aos_data_201112 where date='2011-12-27';
//全部列：
INSERT INTO `a` SELECT * FROM b;  //前提两者数据字段完全一致。

（2）删除记录
delete from MYTABLE where id = xx;
清空表：delete from MYTABLE;
truncate table tablename  其中table是可选的。
如果要删除表中的部分记录，只能使用delete语句。


（3）更新记录
update tbl_name set column1 = xx, column2 = yy where id = zz;

（4）查询记录
select * from tbl_name where id = xx;
关于时间比较
select * from tbl_name where created_at > '2012-01-09';
表面上大于'2012-01-09'，应该只出现'2012-01-10'及以后的结果，实际不是。
批注：由于created_at是timestamp或datetime，所以'2012-01-09'会变为'2012-01-09 00:00:00'，然后再作比较。所以凡是大于'2012-01-09 00:00:00'都是符合条件的，所以类似'2012-01-09 10:00:00'会出现在结果列。

注：DELETE， 删除表的记录。DROP， 删除数据表或数据库。

2，select的常见用法
（1）单表
a，选择若干列
b，选择若干元组
1，消除重复行
2，查询符合条件的元组（比较运算符，逻辑运算符，between and，having，like，in，is null，exist）
3，ORDER BY
4，聚集函数
5，GROUP BY

（2）多表
a，等值与非等值连接查询
b，自身连接
c，外连接
d，符合条件连接

（3）嵌套查询
a，带IN
b，带比较运算符的子查询
c，带ANY(SOME)或ALL的子查询
d，带EXISTS的子查询

（4）集合查询



五、多表操作
1，内连接/外连接/交叉连接
内连接：分等值连接，非等值连接，自然连接
外连接：分左连接、右连接、全连接
交叉连接：

详情可参考：
http://zccst.iteye.com/admin/blogs/1486751


2，嵌套


六、权限管理
1，授权
例如，grant select,insert,update,delete on *.* to root@'%' Identified by "654123";
解释：
（1）*.*  前面的星表示数据库名称，后面的星表示数据库表。
（2）root@'%'  前面表示用户，后面'%'表示允许所有IP访问并重启mysql，此外，还可以是'localhost'等。

再例如，
(1). GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; 
(2). FLUSH   PRIVILEGES;