MySQL 资料汇编

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.重命名表

  ALTER TABLE `table_name` RENAME `new_table_neme` ;

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.指定数据库：

 导出:      

 mysqldump -u user_name -p [your_password] db_nmae > path\file_name.sql

 如果[your_password]缺省，然后提示输入密码:有密码则输入,否则直接回车
 
 导入:

  mysql -u user -p [your_password] db_name < path\filename.sql

 
 如果用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 分配
 

grant all on *.* to admin@localhost identified by 'admin';


 说明:  admin 用户(密码为admin)分配所有权限(除grant),对所有数据库和所有表.

 grant all on db_name.* to chenlb@% identified by 'chenlbpassword';

 说明: chenlb用户有所有权限,只对 db_name的所有表.

 grant select,insert on db_name.tb_name to clb@chenlb.com identified by '';


 说明: clb 用户(密码为空,只有 chenlb.com 主机才有效,
  如果改为%.chenlb.com就对所有后缀为chenlb.com的主机都有效)
  只对 db_name 中的 tb_name 表中有 select,insert 权限.
  权限可以加上(delete,update,drop,create ……)。
 
3.改用户密码
 

 use mysql;
  update user set password=password("new_pass") where user="root";
  flush  privileges;

 

 mysqladmin -u username -p pw password newpassword

4.设远程用户

 use mysql;
 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

      mysql>select * from table_name into outfile 'path/filename.txt';

     3、.符合条件的从那里开始,在多少以内.

     mysql>select * from talbe_name limit 0,30;

     说明:从符合条件的第1条开始,读出30条,如果不够30条,取最大. 

     4.常用函数

 select coalesce(null,3,null)
 返回列表的第一个非NULL值
 
 select greatest(3,5)
 返回列表的最大值
 
 select least(2,0)
 返回列表的最小值 


      5.安装/删除服务 

 mysqld --install MySQL --defaults-file=C:\my-opts.cnf

 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'


【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/dajianshi/p/2827099.html