1. 系统查询
1.1 查某库的所有表和列名
SELECT
TABLE_SCHEMA AS '库名',
TABLE_NAME AS '表名',
COLUMN_NAME AS '列名',
ORDINAL_POSITION AS '列的排列顺序',
COLUMN_DEFAULT AS '默认值',
IS_NULLABLE AS '是否为空',
DATA_TYPE AS '数据类型',
CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
NUMERIC_PRECISION AS '数值精度(最大位数)',
NUMERIC_SCALE AS '小数精度',
COLUMN_TYPE AS 列类型,
COLUMN_KEY 'KEY',
EXTRA AS '额外说明',
COLUMN_COMMENT AS '注释'
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = '需要查询的某库名'
ORDER BY
TABLE_NAME,
ORDINAL_POSITION;
1.2 查某库的所有表名称和表说明
SELECT
TABLE_NAME,
TABLE_COMMENT
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA = '需要查询的某库名';
数据查询及操作
列出当前用户可查看的所有数据库
SHOW DATABASES;
新建数据库dbtest
create DATABASE dbtest;
删除数据库dbtest
drop database dbtest;
创建dbtest数据库下的login表,并插入一条数据
use dbtest;
CREATE TABLE `login` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`role` tinyint(255) NULL DEFAULT 0,
`ctime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `login` VALUES (1, 'admin', '202cb962ac123455b964b07152d234b90', 1, '2021-08-07 11:22:09');
分页查询
pageNo(页码)、pageSize(每页条数)
select * from TABLE_NAME where _id > (pageNo-1)*pageSize order by _id limit pageSize;