//创建表
mysql> create table <table_name>(field[,field2,...]) comment='';
//eg:
mysql> create table user(
> id int(4) not null primary key auto_increment,
> name varchar(20) not null,
> sex int(1) not null default '0',
> weight double(10,2)) comment='用户表';

//删除表
mysql> drop table <table_name>;

//修改表名
mysql> rename table <old_table_name> to <new_table_name>;

SHOW DATABASES                                //列出 MySQL Server 数据库。
SHOW TABLES [FROM db_name]                    //列出数据库数据表。
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]                  //可以简写为： DESC 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                         //列出某用户权限

mysql> alter table <table_name> add <field_name> <field_type> comment "备注";
//eg:在表user添加了一个字段update_time，类型为timestamp，设置创建时间和默认时间
mysql> alter table user add update_time timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment='更新时间';
// 在log表中 添加一个state字段并且放在current_count后
mysql> ALTER TABLE log ADD `state` TINYINT (1) UNSIGNED NOT NULL DEFAULT 0 COMMENT "状态，0 待审核，1已审核，2拒绝" AFTER current_count;

mysql> alter table <table_name> change <old_table> <new_table> <field_type>;
//eg:
mysql> alter table user change weight height double(8,2);

删除字段：
mysql> alter table <table_name> drop <field_name>;

//一般索引
mysql> alter table <table_name> add index <index_name> (field_name[,field_name2,...]);
//eg:
mysql> alter table user add index u_name (name,sex);

//加主关键字的索引
mysql> alter table <table_name> add primary key <field_name>;
//eg:
mysql> alter table user add primary key(id);

//加唯一限制条件的索引
mysql> alter table <table_name> add unique <index_name> (field_name);
//eg:
mysql> alter table user add unique u_name(cardnumber);

mysql> alter table <table_name> drop index <index_name>;
//eg:
mysql> alter table user drop index u_name;

INSERT [ LOW_PRIORITY | DELAYED | HIGH_PRIORITY ] [ IGNORE ] [ INTO ] tbl_name [ PARTITION (
	partition_name ,...) ] [ (
		col_name ,...) ] {
		VALUES
			|
		VALUE
			} ({ expr | DEFAULT },...),(...) ,...[ ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr ]...]

//eg1 单条插入:
mysql> INSERT INTO user(name, age) VALUE ("张三",18);
//eg2 多条插入:
mysql> INSERT INTO user(name, age) VALUES ("张三",18),("李四",19),("王五",20);

//只能插入一条
INSERT [ LOW_PRIORITY | DELAYED | HIGH_PRIORITY ] [ IGNORE ] [ INTO ] tbl_name [ PARTITION (
	partition_name ,...) ]
	SET col_name ={ expr | DEFAULT },...[ ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr ]...]

//eg:
mysql> INSERT INTO user set name="张三", age=18;

//用于表复制
INSERT [ LOW_PRIORITY | HIGH_PRIORITY ] [ IGNORE ] [ INTO ] tbl_name [ PARTITION (
	partition_name ,...) ] [ (
		col_name ,...) ] SELECT
			...[ ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr ]...]
			
//eg1 两个表数据互通:
mysql> INSERT INTO a(a_field1,a_field2) SELECT b_field1,b_field2 FROM b;
//eg2 多张表进行复制,需要先使用关联查询查出数据后再插入,嵌套部分需要设置别名！！:
mysql> INSERT INTO a(a_field1,a_field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb;

UPDATE [ LOW_PRIORITY ] [ IGNORE ] table_reference
SET col_name1 ={ expr1 | DEFAULT } [, col_name2 ={ expr2 | DEFAULT }]...[
WHERE
	where_condition ] [
ORDER BY
	...] [
LIMIT row_count ]

# eg1 单表更新:
UPDATE user SET name="李四",age=19 WHERE id=1;

# eg2 多表更新:
UPDATE USER u
INNER JOIN user_info ui ON u.user_id = ui_user_id
SET ui_age = 20
WHERE
	u.sex = 1;

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ]
FROM
	tbl_name [ PARTITION (
		partition_name ,...) ] [
		WHERE
			where_condition ] [
		ORDER BY
			...] [
		LIMIT row_count ]

//eg1 删除单条:
mysql> DELETE FROM user where user_id = 1;
//eg2 删除多条:
mysql> DELETE FROM user where user_id in (1,2,3,4);

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] tbl_name [.*] [, tbl_name [.*]]...
FROM
	table_references [
WHERE
	where_condition ]

//eg:
DELETE t1,t2
FROM t1
INNER JOIN t2
INNER JOIN t3
WHERE
	t1.id = t2.id
AND t2.id = t3.id;

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ]
FROM
	tbl_name [.*] [, tbl_name [.*]]... USING table_references [
WHERE
	where_condition ]
	
# eg：
DELETE
FROM
	t1,
	t2 USING t1
INNER JOIN t2
INNER JOIN t3
WHERE
	t1.id = t2.id
AND t2.id = t3.id;

mysql> truncate table <table_name>;
/*
截断表可以用于删除表中的所有数据。截断表命令还会回收所有索引的分配页。截断表的执行速度与不带where子句的delete（删除）命令相同，甚至比它还要快。delete（删除）一次删除一行数据，并且将每一行被删除的数据都作为一个事务记录日志；而truncate （截断）表则回收整个数据页，只记录很少的日志项。delete（删除）和truncate（截断）都会回收被数据占用的空间，以及相关的索引。只有表的拥有者可以截断表。
另外，truncate表之后，如果有自动主键的话，会恢复成默认值。
*/

SELECT
	[ ALL | DISTINCT | DISTINCTROW ] [ HIGH_PRIORITY ] [ STRAIGHT_JOIN ] [ SQL_SMALL_RESULT ] [ SQL_BIG_RESULT ] [ SQL_BUFFER_RESULT ] [ SQL_CACHE | SQL_NO_CACHE ] [ SQL_CALC_FOUND_ROWS ] select_expr [, select_expr...] [
FROM
	table_references [ PARTITION partition_list ] [
WHERE
	where_condition ] [
GROUP BY
	{ col_name | expr | position } [ ASC | DESC ],...[ WITH ROLLUP ]] [
HAVING
	where_condition ] [
ORDER BY
	{ col_name | expr | position } [ ASC | DESC ],...] [
LIMIT {[ OFFSET ,] row_count | row_count OFFSET OFFSET }] [ PROCEDURE procedure_name (argument_list) ] [ INTO OUTFILE 'file_name' [ CHARACTER
SET charset_name ] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name ]] [ FOR UPDATE | LOCK IN SHARE MODE ]]

#eg:
#查询总条数
mysql> SELECT COUNT(*) FROM test_table WHERE tf_b = 1;
#倒序分页查询
mysql> SELECT tf_a, tf_b FROM test_table WHERE tf_b = 1 ORDER BY tf_a DESC LIMIT 100,10;

INSERT INTO 表名1(字段列表1) SELECT 字段列表2 FROM 表名2 WHERE 条件表达式;

# eg 用一条语句查询出student1表中学生总成绩并且降序插入student2表中
INSERT INTO student2 (
	STUDENT_NAME,
	STUDENT_TOTAL_SCORE
) SELECT
	STUDENT_NAME,
	SUM(STUDENT_SCORE) AS STUDENT_TOTAL_SCORE
FROM
	student1
GROUP BY
	STUDENT_NAME

UPDATE table1 a INNER JOIN table2 b on a.id = b.id set ... where ...

# eg: 用一条语句将所有人的语文成绩改成95
UPDATE student1 a
INNER JOIN (
	SELECT
		STUDENT_ID,
		STUDENT_NAME,
		STUDENT_COURSE,
		STUDENT_SCORE
	FROM
		student1
	WHERE
		STUDENT_COURSE LIKE '%英语%'
) b ON a.STUDENT_ID = b.STUDENT_ID
SET a.STUDENT_SCORE = 95

方法一(推荐)：
SELECT * FROM
	table a
WHERE
	[(a.field1,a.field2,...)] IN (
		SELECT
			[field1,field2,...]
		FROM
			table
		GROUP BY
			[field1 ,field2,...]
		HAVING
			Count(*) > 1
	) 
方法二：
SELECT * FROM
	table a
WHERE
((  SELECT
    	COUNT(*)
    FROM
    	table
    WHERE
    	field1 = a.field1
        AND field2 = a.field2
        AND ...
)> 1)

# 根据多个字段查询student1
SELECT
	STUDENT_ID,
	STUDENT_NAME,
	STUDENT_COURSE,
	STUDENT_SCORE
FROM
	student1 a
WHERE
	(a.STUDENT_NAME, a.STUDENT_COURSE) IN (
	# (a.STUDENT_NAME, a.STUDENT_COURSE,a.STUDENT_ID) IN (
		SELECT
		    # MAX(STUDENT_ID)
			STUDENT_NAME,
			STUDENT_COURSE
		FROM
			student1
		GROUP BY
			STUDENT_NAME,
			STUDENT_COURSE
		HAVING
			count(*) > 1
	)
ORDER BY
	STUDENT_COURSE DESC

DELETE FROM student1
WHERE
    # 将最后插入的数据保留 其他数据删除
	STUDENT_ID  NOT IN (
 		# 查询最后插入的数据
		SELECT
			MAX(STUDENT_ID)
		FROM
			(
				#  查询出重复数据
				SELECT
					STUDENT_ID,
					STUDENT_NAME,
					STUDENT_COURSE,
					STUDENT_SCORE
				FROM
					student1 a
				WHERE
					(
						a.STUDENT_NAME,
						a.STUDENT_COURSE
					) IN (
						SELECT
							STUDENT_NAME,
							STUDENT_COURSE
						FROM
							student1
						GROUP BY
							STUDENT_NAME,
							STUDENT_COURSE
						HAVING
							count(*) > 1
					)
			) AS tmp
		GROUP BY
			STUDENT_NAME,
			STUDENT_COURSE
		HAVING
			count(*) > 1
	)

DELETE
FROM
	student1
WHERE
    #查出重复数据
	(
		STUDENT_NAME,
		STUDENT_COURSE
	) IN (
		SELECT
			STUDENT_NAME,
			STUDENT_COURSE
		FROM
			(
				SELECT
					STUDENT_NAME,
					STUDENT_COURSE
				FROM
					student1
				GROUP BY
					STUDENT_NAME,
					STUDENT_COURSE
				HAVING
					COUNT(*) > 1
			) s1
	)
# 排除最后插入的数据保留
AND STUDENT_ID NOT IN (
	SELECT
		STUDENT_ID
	FROM
		(
			SELECT
				MAX(STUDENT_ID)
			FROM
				student1
			GROUP BY
				STUDENT_NAME,
				STUDENT_COURSE
			HAVING
				COUNT(*) > 1
		) s2
);