数据库入门整理-MySQL篇

•  建表语句

create table STUDENT2 (
`ID` int auto_increment key not null comment '自增编号',
unique index `UQ_STUDENT2_ID`(`ID`),
`XH` nvarchar(20) comment '学号',
`XM` nvarchar(30) not null comment '姓名',
`XB` decimal(1) not null comment '性别(1:男, 2:女)',
unique index `UQ_STUDENT2_XB`(`XB`),
`MOBILE` decimal(11) comment '手机号',
`SFZX` TINYINT comment '是否在校(0:离校, 1:在校)',
`RXSJ` date comment '入学时间',
-- primary key(`XH`) -- 添加主键，自增列新增默认为主键，有自增列情况不能这么用
) comment='学生表2';

•  更新语句

-- 修改字段备注
comment on column STUDENT2.XB is '性别(1:男, 2:女)';

-- 表重命名
alter table STUDENT2 rename to STUDENT;

-- 修改字段名
alter table STUDENT rename column XB to SEX;

-- 删除主键
alter table STUDENT drop primary key
-- 添加新的主键
, add primary key(`ID`) USING BTREE
-- 修改表备注
, comment '学生表'
-- 添加唯一索引、指定索引名
, add unique index `UQ_STUDENT_SEX`(`SEX`)
-- 删除索引
, drop index UQ_STUDENT2_XB
-- 修改字段类型、非空以及备注
, modify column `SFZX` TINYINT null comment '是否在校(0:离校,1:在校)'

• 查询当前库表操作

-- 获取当前库存在哪些表
SELECT TABLE_NAME, TABLE_COMMENT COMMENTS
FROM information_schema.`TABLES`
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA=DATABASE()
ORDER BY TABLE_NAME

-- 查询当前库是否存在表
SELECT COUNT(*) hasTable
FROM information_schema.TABLES
WHERE TABLE_NAME=#{tableName} and TABLE_SCHEMA=DATABASE()

• 查询指定表字段信息 

SELECT A.TABLE_NAME, B.TABLE_COMMENT COMMENTS, A.COLUMN_NAME, A.COLUMN_COMMENT COLUMN_COMMENTS,
A.NUMERIC_PRECISION AS DATA_PRECISION, A.NUMERIC_SCALE AS DATA_SCALE,
CASE WHEN LEFT(A.EXTRA, 4)='auto' THEN 'GENERATED' ELSE A.DATA_TYPE END AS DATA_TYPE,
CASE WHEN A.IS_NULLABLE='NO' THEN A.IS_NULLABLE ELSE '' END AS NULLABLE,
CASE WHEN IFNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) > 0 THEN A.CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION
END AS DATA_LENGTH,
-- 查询是否主键
(SELECT CASE WHEN COUNT(1)>0 THEN 1 ELSE '' END FROM information_schema.statistics WHERE
TABLE_SCHEMA=B.TABLE_SCHEMA AND
TABLE_NAME=B.TABLE_NAME AND COLUMN_NAME=A.COLUMN_NAME AND INDEX_NAME = 'PRIMARY') AS PRIMARY_KEY,
-- 查询唯一索引
(SELECT INDEX_NAME FROM information_schema.statistics WHERE TABLE_SCHEMA=B.TABLE_SCHEMA AND
TABLE_NAME=B.TABLE_NAME AND COLUMN_NAME=A.COLUMN_NAME AND INDEX_NAME != 'PRIMARY' LIMIT 1) AS UNIQUE_KEY

FROM information_schema.`COLUMNS` A, information_schema.`TABLES` B
WHERE A.TABLE_NAME=B.TABLE_NAME AND B.TABLE_TYPE='BASE TABLE'
AND A.TABLE_SCHEMA=B.TABLE_SCHEMA AND B.TABLE_SCHEMA=DATABASE()
AND A.TABLE_NAME=#{tableName}
--ORDER BY A.COLUMN_NAME

•  获取表详细信息

SELECT CREATE_TIME,
-- 查询表占用空间大小MB
TRUNCATE((data_length + index_length)/1024/1024, 4) TABLE_SIZE,
-- 统计表记录数
(SELECT COUNT(1) FROM ${tableName}) TABLE_COUNT

FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=#{tableName}

• 注意：

1.  新增自增列默认为主键