mysql常用总结《持续更新......》

mysql常用总结:
==============================================================
1、登录mysql
1) 服务器登录：
mysql -h host -u user -p 
(passwd:)
2)用工具：
常用的有 Navicat Premium(自己百度)
3) 帮助：
\h 
4) 查看警告：
show warnings;
===============================================================
2、查看数据库：
show databases;


3、创建数据库：
create database db_name;
================================================================
4、创建表:
(指定主键后，其他属性建好之后插入)
drop table if exists person;
create table person(
id int(11) primary key auto_increment,
p_name varchar(30),
p_age char(3),
p_sex bool,
p_add varchar(30),
p_iphone char(11),
p_time datetime
);
insert into person(id, p_name, p_age, p_sex, p_add, p_iphone, p_time) values(1, 'faith', 23, 1, 'beijing',13100986734, now());


(也可以这样创建)：
drop table if exists life;
create table life(
id int(11)  primary key auto_increment,
l_name varchar(30),
l_book varchar(30),
l_eat varchar(30),
l_cloth varchar(30),
l_veh varchar(30),
unique key (l_name),//限制l_name 者一列字段不能重复，若有重复，将不能添加进去
constraint v_name foreign key(l_name) references person(p_name)
);




drop table if exists life;
create table life(
id int(11)  primary key auto_increment,
l_name varchar(30),
l_book varchar(30),
l_eat varchar(30),
l_cloth varchar(30),
l_veh varchar(30)
); 
insert into life(id, l_name, l_book, l_eat, l_cloth, l_veh) values(1, 'faith', 'g', 'b', 'n', 'b');


drop table if exists book;
create table book(
id int(11) primary key auto_increment,
b_name varchar(30),
b_author varchar(30),
b_public varchar(30),
b_read varchar(30),
b_read_time date
);
insert into book(id, b_name, b_author, b_public, b_read, b_read_time) values(1, 'g', 'no', 'qinghuachubanshe', 'you', now());
==============================================================================================================================


5、字段类型详解


==============================================================================================================================


6、对表本身的操作：
1) 查看表本身：
desc(describe) table_name;(查看表的结构)
show columns from tb_name;(查看表的结构)
show index from tb_name;(查看索引)
show keys from table_name;

show create table table_name;(查看表的详细信息)

show create table table_name \G;(显示的更美观)

drop table table_name;(删除表)

2)修改表：
alter table old_name rename new_name;(修改表名字)
alter table faith_my_a add en_name varchar(50) not null default 0;(添加字段)
alter table faith_my_a change old_name new_name varchar(80) not null default 0;(修改字段名)
alter table faith_my_a modify column name varchar(100);(修改type)
alter table faithTest change id id int(11) not null auto_increment primary key;(修改id为自增)
alter table faithTest DROP citynum;(删除字段)
alter table faithTest change local local varchar(30) not null default 0;(修改列的类型)
alter table tbsum change s_sum s_sum  int;
alter table tbsum modify s_sum double;
alter table tb_sum modify s_sum double,modify s_are double;(同时修改多个字段的属性)
单独添加外键
alter table parts_two add constraint f_name foreign key (part_name) references books (book_borrower);
删除外键：
alter table parts_two drop foreign key f_name;(或者用show create table  查看表的 外键信息)

3) 修饰字的说明：
NOT NULL(约束字段不能为空)
DEFAULT(默认值)
UNIQUE KEY(约束字段唯一值)
PRIMARY KEY(主键)
AUTO_INCRAEMENT(自增)
FOREIGN KEY(外键)
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；
foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

4) show keys from table_name;字段详解：
| Table     表的名称
| Non_unique  如果索引不能包括重复词，则为0。如果可以，则为1
| Key_name  索引名
| Seq_in_index 索引序列
| Column_name  列名  对应原表的各个字段
| Collation  存储方式  A 为升序  NULL 无分类
| Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。
  基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。
  基数越大，当进行联合时，MySQL使用该索引的机 会就越大。
| Sub_part  如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
| Packed  指示关键字如何被压缩。如果没有被压缩，则为NULL。
| Null  如果列含有NULL，则含有YES。如果没有，则该列含有NO。
| Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
| Comment 
| Index_comment 
================================================================================================================================
7、对表数据的操作：
1)select:
<1> where
select * from tb_name where (condition1  and  condition2 and condition3);
<2> order by
select * from tb_name where (condition1) order by field_name ASC;  (ASC升  DESC降)
<3> group by
select f_name1, f_name2, count(*) from tb_name group by f_name1;
<4> having
having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。
而 having子句在聚合后对组记录进行筛选。
select factory, sum(height) from tb_name group by factory having sum(height)>100;
<5> 内联查询
select a.id,a.bu,b.id,b.height from tb_name1 AS a INNER JOIN tb_name2 AS  b where a.id=b.id;
<6> 左联
select * FROM tb_name1 AS a LEFT OUTER JOIN tb_name2 AS  b on a.id=b.id;
<7> 右联
select * FROM tb_name1 AS a RIGHT OUTER JOIN tb_name2 AS  b on a.id=b.id;
<8> 全联
(左右连查询的结果去掉重复后合并)
select * FROM tb_name1 AS a FULL OUTER JOIN tb_name2 AS  b on a.id=b.id;

2) insert: insert into table_name (   )  values (   )

3) REPLACE语句
　  我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引，当我们再向这个表中使用已经存在的键值
插入一条记录，那将会抛出一 个主键冲突的错误。当然，我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法，
必须先使用DELETE语句删除原先的记录，然后再使用 INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案，
这就是REPLACE语句。使用REPLACE插入一条记录时，如果不重 复，REPLACE就和INSERT的功能一样，如果有重复记录，
REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。
这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。在使用REPLACE时，表中必须有唯一索引，而且这个索引所
在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并
没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了 DELETE删除这条记录，然后再记录用INSERT来插入这条记录。
如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。REPLACE的语法和INSERT非常的相似，如下面的REPLACE语句是插入
或更新一条记录。
　　REPLACE INTO users (id,name,age) VALUES(123, ‘faith’, 50);


4) delete: delete from table_name;
================================================================================================================================


8、mysql常用函数：
见：http://blog.csdn.net/com_ma/article/details/78729171


=================================================================================================================================


9、mysql事件：
CREATE EVENT IF NOT EXISTS ESA
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE
do CALL paaa();

10、linux下操作mysql:


执行存储过程：call fun_name();
导出表和数据：
mysqldump -u user -p db_name > name.sql
导出表：
mysqldump -u user -p  -d db_name > name.sql

导出某张表的结构和数据：
mysqldump -u user -p  db_name tb_name > tb_name.sql
导出某张表的结构：
mysqldump -u user -p -d ab_name tb_name >tb_name.sql

导入数据库：
登录mysql：
mysql -u user -p 
mysql> create database db_name;
mysql> use db_name;
mysql> set names utf8;
mysql> source /path/to/dir/name.sql;

或者：
> mysql -u user -p db_name < name.sql


mysql的备份：
数据目录的位置：(可以通过 --datadir=/path/to/dir来指定)
/var/lib/mysql(rmp包安装)
/usr/local/var(源程序安装)
/usr/local/mysql(二进制包安装)
mysql的文件类型：
格式文件：
.frm ---> 描述表的结构(列  类型  索引等)
.ISD ---> 数据 
.ISD ---> 索引文件



mysql的重定位：


mysql的数据恢复
===============================================================================================================================
11、mysql的存储引擎：
myisam 存储引擎 建议使用固定长度，数据列代替可变长度的数据列。 
memory存储引擎 目前都使用固定数据行存储，因此无论使用char varchar列都没关系， 
innodb 存储引擎 建意使用varchar 类型 

查看数据库有多大：
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='test';
查看存储引擎：
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+