Mysql 基础 [待整理]

一、基础知识

TINYTEXT 最大长度是 255 (2^8 - 1) 个字符。

TEXT 最大长度是 65535 (2^16 - 1) 个字符。

MEDIUMTEXT 最大长度是 16777215 (2^24 - 1) 个字符。

LONGTEXT 最大长度是 4294967295 (2^32 - 1) 个字符

 

二、基本命令

1.设置初始root密码

初次：mysqladmin -u root -password test

后来：mysqladmin -u root -p test password test1

 

2.创建用户、授权

GRANT all privileges ON test.* TO test@"%" IDENTIFIED BY 'mypwd' WITH GRANT OPTION; 

GRANT FILE ON *.* TO test@localhost; //FILE权限属于管理的权限，要赋予用户时，必须要用*.*。FILE权限关系LOAD DATA INFILE

SET old_passwords = 0; //关闭旧密码模式(历史原因？

UPDATE mysql.user SET Password = PASSWORD('test') WHERE User = 'test' limit 1; //设置新密码模式的密码

FLUSH privileges; //刷新权限

 

3.导入

source /data/database_test.sql; //全库导入

LOAD DATA LOCAL INFILE "/data/mysql.txt" INTO TABLE tablename; //用文本方式将数据装入数据表中：

 

4.导出

1.select * from user into outfile '/data/filename.txt'语句

2.mysqldump

mysqldump -u user_name -p database_name table_name > outfile_name.sql

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql

//-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

 

5.插入

INSERT INTO clients (client_id, client_name, client_type) SELECT 10345, 'IBM', 'advertising'

FROM dual WHERE not exists (select * from clients where clients.client_id = 10345);

//使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。

 

6.重命名数据库表

RENAME TABLE ztemp TO ztemp4

 

7、修改字段属性

ALTER TABLE bbabase CHANGE news_id id VARCHAR(5) NOT NULL;

 

8、在表中的content后增加一字段

ALTER TABLE bbabase ADD leave_time DATETIME NOT NULL AFTER content;

 

9.show一些信息

SHOW DATABASES                                //列出 MySQL Server 数据库。
SHOW TABLES [FROM db_name]                    //列出数据库数据表。

SHOW CREATE TABLE T1
SHOW TABLE STATUS [FROM db_name]              //列出数据表及表状态信息。
SHOW COLUMNS FROM tbl_name [FROM db_name]     //列出资料表字段
SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//列出字段及详情
SHOW FULL FIELDS FROM tbl_name [FROM db_name] //列出字段完整属性
SHOW INDEX FROM tbl_name [FROM db_name]       //列出表索引。
SHOW STATUS                                  //列出 DB Server 状态。
SHOW VARIABLES                               //列出 MySQL 系统环境变量。
SHOW PROCESSLIST                             //列出执行命令。
SHOW GRANTS FOR user                         //列出某用户权限

 10.时间处理

1、将时间转换为时间戳
select unix_timestamp('2009-10-26 10-06-07')
如果参数为空，则处理为当前时间
2、将时间戳转换为时间
select from_unixtime(1256540102)

select date_format(日期字段,’%Y-%m%d’) as ‘日期’ from test

select CURRENT_DATE 返回当前date

CURRENT_DATE

 

11.索引相关

1.添加PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

这样也行：

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

 

查看索引

show index from tblname;
show keys from tblname;

 

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name //功能同上
ALTER TABLE table_name DROP PRIMARY KEY //主键

 

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

12.复制表内容，插入到新表

1.INSERT INTO SELECT语句

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

2.SELECT INTO FROM语句

语句形式为：SELECT vale1, value2 into Table2 from Table1

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

三、搞基小姿势

1.大数据导入

A. 对于Myisam 类型的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭Myisam 表非唯一索引的更新。在导入大量的数据到一个非空的Myisam 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
B. 而对于Innodb 类型的表，这种方式并不能提高导入数据的效率。对于Innodb 类型的表，我们有以下几种方式可以提高导入的效率：
a. 因为Innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
b. 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SETUNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
c. 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2.update 多行

//摘自ci框架，有空自己封装一个
$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);
$this->db->update_batch('mytable', $data, 'title'); 
UPDATE `mytable` SET `name` = CASE
WHEN `title` = 'My title' THEN 'My Name 2'
WHEN `title` = 'Another title' THEN 'Another Name 2'
ELSE `name` END,
`date` = CASE 
WHEN `title` = 'My title' THEN 'My date 2'
WHEN `title` = 'Another title' THEN 'Another date 2'
ELSE `date` END
WHERE `title` IN ('My title','Another title')

C.复制表结构、表数据

1.MySQL复制表结构及数据到新表
CREATE TABLE 新表
SELECT * FROM 旧表

2.只复制表结构到新表
CREATE TABLE 新表
SELECT * FROM 旧表 WHERE 1=2

CREATE TABLE 新表
LIKE 旧表 (mysql>5.0)

3.复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表
SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表

四、常见小问题

1.忘记root密码怎么办？ (吔屎啦！

在启动Mysql服务器时加上参数--skip-grant-tables来跳过授权表的验证 (./safe_mysqld --skip-grant-tables &)，这样我们就可以直接登陆Mysql服务器，然后再修改root用户的口令，重启Mysql就可以用新口令登陆了。

2.解决mysql不用用户名和密码可以直接登陆的问题：
mysql>use mysql;
mysql>delete from user where user='';
mysql>flush privileges; (必须的)

3.php5.2+ 连接mysql 提示old_password错误

Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:554) in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file in path/to/the/file/where/connection/script/is/written/
SET old_passwords =0;
UPDATE mysql.userSET Password= PASSWORD('testpass')WHEREUser='testuser' limit1;
SELECT LENGTH(Password) FROM mysql.user WHERE User ='testuser';

 

五、有用处的函数

mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
        -> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
        -> 'FF', 255

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
        -> 2130706433, 2130706433

SELECT INET_NTOA(INET_ATON('128.0.0.1'))

 
 
INET_NTOA(INET_ATON('128.0.0.1'))
 
128.0.0.1