实用sql记录

1.mysql建表语句

CREATE TABLE `user` (
	`id` BIGINT (20) NOT NULL AUTO_INCREMENT COMMENT '主键',
	`username` VARCHAR (50) NOT NULL COMMENT '用户名',
	`password` VARCHAR (50) NOT NULL COMMENT '密码',
	PRIMARY KEY (`id`),
	UNIQUE KEY `username` (`username`) COMMENT '用户名索引'
) ENGINE = INNODB DEFAULT CHARSET = utf8;

2.元数据查询

(1)查询索引

SHOW INDEX
FROM
	emp;

(2)查询数据库表名

-- 查询数据库中所有表名 
SELECT
	table_name
FROM
	information_schema. TABLES
WHERE
	table_schema = 'sql_learn'

3.EXISTS的使用

SELECT
	d.deptno
FROM
	dept d
WHERE
	EXISTS (
		SELECT
			NULL
		FROM
			emp e
		WHERE
			d.deptno = e.deptno
	);

4.mysql时区报错解决

show VARIABLES like '%time_zone%';

set GLOBAL time_zone='+8:00';