MySQL

更新时间：2023-12-31

• 在线语法查询及运行MySQL

查看

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM table FROM DatabaseName;
SHOW FIELDS FROM table / DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

数据库

CREATE DATABASE DatabaseName;
CREATE DATABASE DatabaseName CHARACTER SET utf8;
USE DatabaseName;
DROP DATABASE DatabaseName;
ALTER DATABASE DatabaseName CHARACTER SET utf8;

建表

CREATE TABLE table (field1 type1, field2 type2);
CREATE TABLE table (field1 type1, field2 type2, INDEX (field));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1,field2));

CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]

CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
 FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))

CREATE TABLE table IF NOT EXISTS;

CREATE TEMPORARY TABLE table;

删表

DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...

修改表结构

ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER  another_field

查询数据

SELECT * FROM table;
SELECT * FROM table1, table2;
SELECT field1, field2 FROM table1, table2;
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;

# 唯一查询
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

• 日期查询相关操作

select * from table where DATE_FORMAT(time_field,"%Y-%m-%d") = DATE_ADD(put_time,INTERVAL -1 DAY);
SELECT * FROM table WHERE TO_DAYS(time_field)=TO_DAYS(NOW());		#今天
SELECT * FROM table WHERE TO_DAYS(NOW())-TO_DAYS(time_field)=1;		#昨天
SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time_field);			#30天
SELECT * FROM table WHERE DATE_FORMAT(time_field, '%Y%m' = DATE_FORMAT(CURDATE(), '%Y%m';	#本月
SELECT name,time FROM table WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now());	#本周
SELECT name,time FROM table WHERE YEARWEEK(date_format(time,'%Y-%m-%d')) = YEARWEEK(now())-1;	#上周
SELECT name,time FROM table WHERE date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m');	#当月
SELECT name,time FROM table WHERE time between date_sub(now(),interval 6 month) and now();	#6个月前
SELECT name,time FROM table WHERE date_format(time,'%Y-%m')=date_format(DATE_SUB(curdate(), INTER VAL 1 MONTH),'%Y-%m');
SELECT * FROM `user` WHERE DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m'); 		#上个月

关联查询

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

查询条件

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

插入数据

INSERT INTO table1 (field1, field2) VALUES (value1, value2);

更新数据

UPDATE table1 SET field1=new_value1 WHERE condition;
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
  table1.id1 = table2.id2 AND condition;

删除数据

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
DELETE FROM table1, table2 WHERE table1.id1 =  table2.id2 AND condition

导入导出

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql	#导出SQL
mysql -u Username -p dbNameYouWant < databasename_backup.sql;		#导入SQL

修复

mysqlcheck --all-databases;
mysqlcheck --all-databases --fast;

主键和外键

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))

用户和权限

CREATE USER 'user'@'localhost';
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
FLUSH PRIVILEGES;

SET PASSWORD = PASSWORD('new_pass');		#设置密码
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
SET PASSWORD = OLD_PASSWORD('new_pass');

DROP USER 'user'@'host';					#删除用户

重置ROOT密码

pkg sys mysql stop			#停止服务，以安全模式启动
mysqld_safe --skip-grant-tables
mysql						#进入mysql
UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
pkg sys mysql start			#重新启动

字段类型

TINYINT (-128 to +127)
SMALLINT (+-65 000)
MEDIUMINT (+-16 000 000)
INT (+- 2 000 000 000)
BIGINT (+-9.10^18)
FLOAT(M,D)
DOUBLE(M,D)
FLOAT(D=0->53)
TIME (HH:MM)
YEAR (AAAA)
DATE (AAAA-MM-JJ)
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
VARCHAR (single-line; explicit size)
TEXT (multi-lines; max size=65535)
BLOB (binary; max size=65535)
ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)