MySQL 基本操作语句

表基础操作：

CREATE TABLE tabName(ID INT  PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT = 100 ENGINE=MyISAM DEFAULT CHARSET=utf8;SELECT MAX(id) FROM tabNameSELECT 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_tabNameALTER 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 indNameDROP 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') --2POSITION('BC' IN 'ABCDE')  --2INSTR('ABCDE','BC') --2--返回指定某个位置中的字符串ELT(2,'str1','str2','str3')--str2--返回与第一个字符串完全相等的首个字符串的位置FIELD('kk','JK','HAHAKK','KK','KK')-- 3--同 FIELDFIND_IN_SET('kk','JK,HAHAKK,KK,KK')-- 3--整数将转2进制按位从右到左取字符串MAKE_SET(3,'a','b','c');--如果expr为true,返回第一个值v1,否则为v2IF(expr,v1,v2)--如果v1不为null则返回v1,否则v2IFNULL(v1,v2)--如果相同则为null,否则为v1NULLIF(v1,v2)--获取当前时间函数CURDATE() --2015-07-04CURRENT_DATE()--2015-07-04 CURRENT_TIMESTAMP() --2015-07-04 10:39:33LOCALTIME() --2015-07-04 10:39:33NOW() --2015-07-04 10:39:33SYSDATE() --2015-07-04 10:39:33UTC_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 tabFOR EACH ROW BEGIN  {sql_statement;}ENDDELIMITER ;--循环语句(LOOP、REPEAT、WHILE)WHILE 循环：DELIMITER //CREATE PROCEDURE TESTPRO()BEGINDECLARE I,K INT DEFAULT 0 ;WHILE I < 100 DOSET K = I + K; SET I = I + 1; END WHILE; SELECT K;END;// DELIMITER ;REPEAT UNTIL 循环：DELIMITER //CREATE PROCEDURE TESTPRO()BEGINDECLARE I,K INT DEFAULT 0 ;REPEATSET K = I + K; SET I = I + 1; UNTIL I=100END REPEAT;SELECT K;END;// DELIMITER ;LOOP 循环：DELIMITER //CREATE PROCEDURE TESTPRO()BEGINDECLARE 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.sqlmysqldump -u root -p dbname tabname > /root/testbak.sqlmysqldump -u root -p --database db1 db1 > /root/testbak.sql--还原mysql -u root -p dbname </root/testbak.sqluse yourdbsource /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-logsmysqladmin -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=10max_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.logcat /root/binlog.logcat /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学习心得