MySQL 资料汇编
作者：佣工7001 注：本文资料及来源于网络
一、MySQL的数据类型：
|
类型
|
大小
|
范围（有符号）
|
范围（无符号）
|
用途
|
|
TINYINT
|
1 字节
|
(-128，127)
|
(0，255)
|
小整数值
|
|
SMALLINT
|
2 字节
|
(-32 768，32 767)
|
(0，65 535)
|
大整数值
|
|
MEDIUMINT
|
3 字节
|
(-8 388 608，8 388 607)
|
(0，16 777 215)
|
大整数值
|
|
INT或INTEGER
|
4 字节
|
(-2 147 483 648，2 147 483 647)
|
(0，4 294 967 295)
|
大整数值
|
|
BIGINT
|
8 字节
|
(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
|
(0，18 446 744 073 709 551 615)
|
极大整数值
|
|
FLOAT
|
4 字节
|
(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
|
0，(1.175 494 351 E-38，3.402 823 466 E+38)
|
单精度
浮点数值 |
|
DOUBLE
|
8 字节
|
(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
|
0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
|
双精度
浮点数值 |
|
DECIMAL
|
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
|
依赖于M和D的值
|
依赖于M和D的值
|
小数值
|
|
CHAR |
0-255字节
|
定长字符串
|
|
VARCHAR
|
0-255字节
|
变长字符串
|
|
TINYBLOB
|
0-255字节
|
不超过 255 个字符的二进制字符串
|
|
TINYTEXT
|
0-255字节
|
短文本字符串
|
|
BLOB
|
0-65 535字节
|
二进制形式的长文本数据
|
|
TEXT
|
0-65 535字节
|
长文本数据
|
|
MEDIUMBLOB
|
0-16 777 215字节
|
二进制形式的中等长度文本数据
|
|
MEDIUMTEXT
|
0-16 777 215字节
|
中等长度文本数据
|
|
LOGNGBLOB
|
0-4 294 967 295字节
|
二进制形式的极大文本数据
|
|
LONGTEXT
|
0-4 294 967 295字节
|
极大文本数据
|
|
ATE
|
3
|
1000-01-01/9999-12-31
|
YYYY-MM-DD
|
日期值
|
|
TIME
|
3
|
'-838:59:59'/'838:59:59'
|
HH:MM:SS
|
时间值或持续时间
|
|
YEAR
|
1
|
1901/2155
|
YYYY
|
年份值
|
|
DATETIME
|
8
|
1000-01-01 00:00:00/9999-12-31 23:59:59
|
YYYY-MM-DD HH:MM:SS
|
混合日期和时间值
|
|
TIMESTAMP
|
8
|
1970-01-01 00:00:00/2037 年某时
|
YYYYMMDD HHMMSS
|
混合日期和时间值，时间戳
|
二、mysql常用语句
(一)数据库的操作：
显示数据库： mysql->show databases;
创建数据库： mysql->create database mydb;
删除数据库： mysql->drop database mydb;
选择数据库： mysql->use mydb;
当前数据库：
mysql->select database();
(二)表操作
1、基本表操作：
创建表： mysql->create table mytable(name varchar(20),sex(char(1),birth date);
删除表： mysql->drop table mytable;
显示所有表： mysql->show tables;
显示表结构： mysql->describe mytable; 或者 desc mytable;
删除多个表： mysql->drop table temp1, temp2, temp3;
多个表之间要用逗号隔开。
为了使不存在的表删除时避免出现提示信息，可以加上if exists关键字。eg: drop tableif exists temp1;
mysql特有的的，指定engine建表：
CREATE TABLE `tt` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 50 ) NOT NULL ) ENGINE = MYISAM ;
2、修改表结构：
增加字段： mysql->alter table yourtable add name varchar(20)not null;
删除字段： mysql->alter table yourtable drop name ;
alter table mytable add myid int (10) default null; alter table mytable drop myid; alter table mytable add uid int (10) not null auto_increment； alter table mytable add primary key (uid);3.重命名表
4、表数据的操作：
插入记录： mysql->insert into mytable values('summer','m','1983-08-24');
删除记录： mysql->delete from mytable where name='summer';
修改记录： mysql->update mytable set sex='vm' where name='summer';
插入多条： mysql->insert into mytable select *from yourtable;
(1)增：
insert into operator (username,password) values ('user1','pass1');
(2)改：
update operator set password='pass123' where username='user1';
(3)删：
delete from operator where username='user1';
(4)查：
select * from operator where username='user1';三、mysql的管理
(一) mysql 备份与恢复
1.全部数据库：
备份：mysqldump --all-databases > all_databases.sql
恢复：mysql < all_databases.sql
2.指定数据库：
导出:
如果[your_password]缺省，然后提示输入密码:有密码则输入,否则直接回车
导入:
如果用mysqldump导出出现了乱码也没有关系，可以运行iconv来转换一下
iconv -c -f UTF-8 -t GB2312 库文件名 > 新的gb2312的库文件名
另，查看MYSQL 数据库编码
mysql> show variables like 'character_set_%'; (二) mysql帐户管理
简单方式：
A、创建本地用户
mysql> grant all privileges on *.* to 'user1'@'localhost' identified by 'pass1';
B、创建可以网络访问的账户
mysql> GRANT SELECT,UPDATE ON *.* TO 'user1'@'localhost' identified by 'pass1';
C、更改已有帐户的密码
mysql> grant all privileges on *.* to 'user1'@'localhost' identified by 'pass123';
mysql> flush privileges;1.新增用户
(1).use mydb;
(2).INSERT INTO `user` VALUES ('localhost', 'newuser', PASSWORD( 'newpassword' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');
(3).FLUSH PRIVILEGES ;
提示：PASSWORD函数的运用需要注意版本，参考函数的调用：OLD_PASSWORD
注意：第三行是插入数据成功后，让他生效的。
2.用 grant 分配
说明: admin 用户(密码为admin)分配所有权限(除grant),对所有数据库和所有表.
说明: chenlb用户有所有权限,只对 db_name的所有表.
说明: clb 用户(密码为空,只有 chenlb.com 主机才有效,
如果改为%.chenlb.com就对所有后缀为chenlb.com的主机都有效)
只对 db_name 中的 tb_name 表中有 select,insert 权限.
权限可以加上(delete,update,drop,create ……)。
3.改用户密码
update user set password=password("new_pass") where user="root";
flush privileges;
4.设远程用户
update user set Host='%' where user='user_name';
flush privileges;
或为特定的IP
注意:否则,登录时出错--Access denied for user 'clb'@'localhost' (using password: YES)
四、其他常用操作：
1、导入并执行.sql文件中的SQL语句(例如c:\crtbl.sql)
mysql>source c:/crtbl.sql;2、导入导出数据到文本.TXT
mysql> load data infile 'TXT文件的完整路径' into table_name;
注意：linux:TXT文件的完整路径=‘/tmp/aaa.txt’
windowns:TXT文件的完整路径='e:\\aaa.txt'
.txt的文件用Tab隔离
表中有auto_increment属性的字段,在.txt文件中使用null
3、.符合条件的从那里开始,在多少以内.
说明:从符合条件的第1条开始,读出30条,如果不够30条,取最大.
4.常用函数
select coalesce(null,3,null)
返回列表的第一个非NULL值
select greatest(3,5)
返回列表的最大值
select least(2,0)
返回列表的最小值
5.安装/删除服务
mysqld --remove
6.改变自增值
ALTER TABLE `table_name` AUTO_INCREMENT =1
7.设主键
ALTER TABLE `table_name` ADD PRIMARY KEY (`id`)
8.搜索
SELECT * FROM `email_list` WHERE relation LIKE '%老%'
9.添加索引(即:唯一)
ALTER TABLE `user` ADD UNIQUE (`username`)
10.删除几条？
delete from text order by id desc limit 2;
11.判断表是否存在？
方法1，使用命令SHOW TABLES：
mysql>show tables like ‘tablename'
方法2，使用系统资料视图INFORMATION_SCHEMA.TABLES，这需要版本5以上的mysql。
mysql>SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DbName' AND TABLE_NAME='tableName'