MySQL 基本操作语句

表基础操作：

CREATE TABLE tabName(
ID INT  PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100 ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT MAX(id) FROM tabName
SELECT LAST_INSERT_ID()
SELECT @@identity
--查看表结构
EXEC sp_help 'tabName'
DESC tabName
--更改表名
ALTER TABLE tabName RENAME newTabName 
--更改字段类型
ALTER TABLE tabName MODIFY id BIGINT
--更改字段名
ALTER TABLE tabName CHANGE id no BIGINT
--添加字段
ALTER TABLE tabName ADD NAME NVARCHAR(200) NULL
--删除字段
ALTER TABLE tabName DROP NAME
--添加主键/外键
ALTER TABLE tabName ADD CONSTRAINT pk_tabName PRIMARY KEY tabName(id); 
ALTER TABLE subTabName ADD CONSTRAINT fk_subTabName_tabName FOREIGN KEY subTabName(fid) REFERENCES tabName(id); 
--删除主键/外键
ALTER TABLE tabName DROP PRIMARY KEY pk_tabName
ALTER TABLE subTabName DROP FOREIGN KEY fk_subTabName_tabName 
--删除约束
ALTER TABLE tabName DROP CONSTRAINT constaintName
--删除表
DROP TABLE IF EXISTS tabName,subTabName;
--视图
DESC view_name;
SHOW CREATE VIEW view_name;

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

DROP VIEW IF EXISTS view_name;

索引：

SHOW INDEX FROM tabName
--添加索引
CREATE TABLE tabName ( id INT NOT NULL,KEY indName(id))
ALTER TABLE tabName ADD INDEX indName(col)
CREATE UNIQUE INDEX indName ON tabName(col)
--删除索引
ALTER TABLE tabName DROP INDEX indName
DROP INDEX indName ON tabName
--重建索引
REPAIR TABLE tabName QUICK;

运算符：

--逻辑运算符
AND或者&&
OR或者||
XOR--异或
--位运算符
与(&)，或(|)，取反(~)，异或(^)，左移(<<)，右移(>>)
--正则表达式
SELECT 'char' REGEXP '^..a$'

函数：

--多个值中返回最小值
LEAST（值1,值2,...值n）
--多个值中返回最大值
GREATEST（值1,值2,...值n）
--求余(X%Y)
MOD(X,Y)
--截取小数(Y为小数位数)
TRUNCATE(X,Y)
--四舍五入(Y为小数位数)
ROUND(X,Y)
--ascii字符转16进制
HEX('A') --41
--16进制转ascii字符
UNHEX(41) --A
--字符个数
CHAR_LENGTH('kk') --2
--按首个字符将后面的字符连接
CONCAT_WS('*','A',NULL,'B');--A*B 
--字符替换.从第2个开始的3个字符替换为"*"
INSERT('ABCDE',2,3,'*') --A*E
--左填充（设置字符串长为4，多则截取；不足则左侧填充符号"*"）
LPAD('KK',4,'*')--**KK 
--右填充
RPAD('KK',10,'*')--KK********
--消去左右空格
TRIM(' kk ') --kk
--消除左右包含的字符"kk"
TRIM('kk' FROM 'k12kk35kk')--k12kk35
--重复生成字符串
REPEAT('1',5) --11111
--字符串比较
STRCMP('A1','A1') ,STRCMP('A2','A1'),STRCMP('A1','A2')-- 0;1;-1
--字符串定位，第一个字符串在第二个字符串中的起始位置
LOCATE('BC','ABCDE') --2
POSITION('BC' IN 'ABCDE')  --2
INSTR('ABCDE','BC') --2
--返回指定某个位置中的字符串
ELT(2,'str1','str2','str3')--str2
--返回与第一个字符串完全相等的首个字符串的位置
FIELD('kk','JK','HAHAKK','KK','KK')-- 3
--同 FIELD
FIND_IN_SET('kk','JK,HAHAKK,KK,KK')-- 3
--整数将转2进制按位从右到左取字符串
MAKE_SET(3,'a','b','c');
--如果expr为true,返回第一个值v1,否则为v2
IF(expr,v1,v2)
--如果v1不为null则返回v1,否则v2
IFNULL(v1,v2)
--如果相同则为null,否则为v1
NULLIF(v1,v2)
--获取当前时间函数
CURDATE() --2015-07-04
CURRENT_DATE()--2015-07-04 
CURRENT_TIMESTAMP() --2015-07-04 10:39:33
LOCALTIME() --2015-07-04 10:39:33
NOW() --2015-07-04 10:39:33
SYSDATE() --2015-07-04 10:39:33
UTC_DATE(),UTC_TIME()
--返回英文月份(QUARTER/HOUR/MINUTE/SECOND…)
MONTHNAME(NOW())
--返回日期/时间的单独部分，比如年/月/日/小时/分钟等
EXTRACT(YEAR FROM NOW());
时间转为秒数：TIME_TO_SEC(NOW())
秒数转时间：SEC_TO_TIME(10000)
增加日期：DATE_ADD(date,interval  expr type)
减去日期：DATE_SUB(date,interval  expr type)
增加时间：ADD_TIME(date,expr)
减去时间：SUBTIME(date,expr)
时间差：DATEDIFF()
日期格式化：DATE_FORMAT(date,format)
时间格式化：TIME_FORMAT(time,format)
日期时间格式：GET_FORMAT(val_type,format_type)

存储过程，函数，触发器，循环，游标：

SHOW CREATE { PROCEDURE | FUNCTION } name ; 
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE 'pattern']; 

SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME=' sp_name ';

--存储过程
DELIMITER //
CREATE PROCEDURE sp_name(IN param1 INT,OUT param2 INT) 
BEGIN
  {sql_statement}
END//
DELIMITER ;

CALL sp_name(); 
DROP PROCEDURE IF EXISTS sp_name; 

--函数
DELIMITER //
CREATE FUNCTION fn_name()
RETURNS Decimal(10,2)
RETURN 3.14;
//
DELIMITER ;

SELECT fn_name();
DROP PROCEDURE IF EXISTS fn_name;
--触发器
DROP TRIGGER IF EXISTS tr_name;

DELIMITER //
CREATE TRIGGER tr_name
{ AFTER | BEFORE } { INSERT | UPDATE |DELETE }
ON tab
FOR EACH ROW 
BEGIN
  {sql_statement;}
END
DELIMITER ;

--循环语句(LOOP、REPEAT、WHILE)
WHILE 循环：
DELIMITER //
CREATE PROCEDURE TESTPRO()
BEGIN
DECLARE I,K INT DEFAULT 0 ;
WHILE I < 100 DO
SET K = I + K; 
SET I = I + 1; 
END WHILE; 
SELECT K;
END;// 
DELIMITER ;

REPEAT UNTIL 循环：
DELIMITER //
CREATE PROCEDURE TESTPRO()
BEGIN
DECLARE I,K INT DEFAULT 0 ;
REPEAT
SET K = I + K; 
SET I = I + 1; 
UNTIL I=100
END REPEAT;
SELECT K;
END;// 
DELIMITER ;

LOOP 循环：
DELIMITER //
CREATE PROCEDURE TESTPRO()
BEGIN
DECLARE I,K INT DEFAULT 0 ;
LABEL: LOOP 
SET K = I + K; 
SET I = I + 1; 
IF I=100 THEN 
LEAVE LABEL;
END IF; 
END LOOP; 
SELECT K;
END;// 
DELIMITER ;


CALL TESTPRO();
DROP PROCEDURE IF EXISTS TESTPRO;


--游标
DECLARE cursor_name CURSOR FOR select_statement ; 
OPEN cursor_name ; 
FETCH cursor_name INTO var_name[,var_name…] ;(循环内) 
CLOSE cursor_name ;

权限脚本：

select Host,User,Password from mysql.user;
show grants for 'username'@'localhost'; 

--添加用户(insert into mysql.user 默认禁止,去掉 my.init\sql-mode\STRICT_TRANS_TABLES)
create user 'username'@'localhost' identified by 'password';  
insert into mysql.user(Host,User,Password)  
values ('localhost','username',password('password')) ; 
--当前用户设置密码  
set password=password("kk");  
flush privileges  
--授权和收权  
grant select on *.* to 'username'@'localhost' identified by 'password';
revoke all privileges, grant option from 'username'@'localhost';
--删除用户  
drop user 'username'@'localhost';  
delete from mysql.user where user='username';

备份还原：

--备份
mysqldump -u root -p -h localhost --all-database > /root/testbak.sql
mysqldump -u root -p dbname tabname > /root/testbak.sql
mysqldump -u root -p --database db1 db1 > /root/testbak.sql
--还原
mysql -u root -p dbname </root/testbak.sql
use yourdb
source /root/testbak.sql;
--导出
select * from test.tab into outfile "/root/mysqltab";
mysqldump -u root -p -T /root/tab dbname tabname
--导入
load data low_priority infile "/root/mysqltab" into table test.tab;mysqlimport -u root -p dbname /root/mysqltab

日志相关：

1、错误日志：记录mysql服务的启动、运行、停止mysql服务时出现的问题
2、查询日志：记录建立的客户端连接和执行的语句
3、二进制日志：记录所有更改数据的语句，可以用于数据复制
4、慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询

show binary logs;
show variables like '%log_%';

--日志刷新
mysql>flush logs;
mysqladmin -u root -p flush-logs
mysqladmin -u root -p refresh

--启用日志
vi /etc/my.cnf

[mysqld]
#查询日志
log=/var/run/mysqld/mysqld.log
#查询慢日志
log-slow-queries=/var/run/mysqld/mysqld-slow.log
#二进制日志
log-bin[=/var/run/mysqld/binlog.log]
expire_logs_days=10
max_binlog_size=100M

[mysqld_safe]
#错误日志
log-error=/var/log/mysqld.log

--暂停恢复二进制日志
set sql_log_bin={0|1}

--导出二进制日志内存
mysqlbinlog  /var/run/mysqld/binlog.000001 > /root/binlog.log
cat /root/binlog.log

cat /var/run/mysqld/mysqld.log

--清除二进制日志（之前的）
mysql> reset master;
mysql>purge master logs to "binlog.000002";
mysql> purge master logs before '20150910';

--查看二进制日志信息
show binlog events;
show binlog events in "binlog.000004";

下一篇 SqlServer 与 MySQL 基本操作语句对比 做了更具体的一些操作语句。

主要参考 桦仔大侠的 我的MYSQL学习心得