mysql学习

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;

有疑问找客服

扫描公众号，了解更多实例分享：

参考1
参考2
参考3