【小白专用已验证24.6.12】MySQL连接使用-创建数据库和创建数据表

zgscwxd

已于 2024-06-12 09:57:22 修改

阅读量356

点赞数 3

文章标签：数据库 MySQL数据库使用

于 2024-06-12 09:54:34 首次发布

本文链接：https://blog.csdn.net/zgscwxd/article/details/139614054

版权

【小白专用已验证24.6.12】Mysql 8.0的安装配置教程（详细）_mysql安装教程8.2-CSDN博客

1.在Windows开始搜索输入Mysql,并选择第一个打开。

2.输入安装时的密码，再回车，就连接上 MySQL 了

1. MySQLSQL语句通用语法
(1)SQL语句可以单行或多行书写，以分号结尾；
(2)SQL 语句不区分大小写(仅此于win系统)，关键字建议使用大写，库名表名字段别名建议写小写；
(3)注释
单行注释: -- 注释内容(这种方式是通用的) 或 # 注释内容(mysql 特有)
多行注释: /* 注释 */

1.显示所有数据数据库名称的语法是?
show databases;

2. 创建数据库？
create database 数据库名;

create database hkcms_admin;

3. 进入或切换数据库
use 数据库名;

4. 查看当前数据库中的表?
使用 show tables；

5.显示表结构语法?
desc 表名;

6.查看表的索引有那些？
show index from 表名;

7. 创建表语法
注意事项：最后一个字段定义不用写逗号，其余每一行都要写逗号

 
create table 表名(
    字段名 数据类型,
    字段名 数据类型,
    ....
    字段名 数据类型
);
 
create table student(
    id int,
    name varchar(32),
    birthday date,
    money double(5,2)
);

DROP TABLE IF EXISTS `hkcms_admin`;
CREATE TABLE `hkcms_admin` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '登录名称',
  `nickname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '昵称',
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '邮箱',
  `password` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `salt` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码盐',
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '头像',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `logintime` int(11) DEFAULT NULL COMMENT '登录时间',
  `loginip` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '登录IP',
  `status` enum('normal','hidden') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态:normal-正常,hidden-禁用',
  `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
  `update_time` int(11) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniaue_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台管理员';

DROP TABLE IF EXISTS `hkcms_admin_log`;
CREATE TABLE `hkcms_admin_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` int(10) unsigned NOT NULL COMMENT '用户id',
  `username` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名',
  `useragent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'useragent',
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '标题',
  `url` varchar(1500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'URL',
  `ip` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'IP地址',
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '内容',
  `create_time` int(11) NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台操作日志';


DROP TABLE IF EXISTS `hkcms_auth_group`;
CREATE TABLE `hkcms_auth_group` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '父级',
  `rules` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规则',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `status` enum('normal','hidden') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态:normal-正常,hidden-禁用',
  `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
  `update_time` int(11) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色管理';

insert  into `hkcms_auth_group` values (1,'超级管理员',0,'*','','normal',1594794684,1594795080);


DROP TABLE IF EXISTS `hkcms_auth_group_access`;
CREATE TABLE `hkcms_auth_group_access` (
  `admin_id` int(10) unsigned NOT NULL COMMENT '管理员ID',
  `group_id` mediumint(8) unsigned NOT NULL COMMENT '角色组ID',
  `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
  `update_time` int(11) DEFAULT NULL COMMENT '更新时间',
  UNIQUE KEY `uid_group_id` (`admin_id`,`group_id`),
  KEY `uid` (`admin_id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='权限分组表';

insert  into `hkcms_auth_group_access` values (1,1,1595834080,1595834080);


DROP TABLE IF EXISTS `hkcms_auth_rule`;
CREATE TABLE `hkcms_auth_rule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父级',
  `name` char(80) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '规则',
  `title` char(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '标题',
  `route` char(80) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '路由',
  `app` char(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '所属应用',
  `icon` char(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '图标',
  `remark` char(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `weigh` int(11) NOT NULL DEFAULT '0' COMMENT '排序',
  `type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '类型:0-权限规则,1-菜单,2-菜单头',
  `status` enum('normal','hidden') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal' COMMENT '状态:normal-正常,hidden-禁用',
  `is_nav` tinyint(2) NOT NULL DEFAULT '0' COMMENT '快速导航:1-是,0-否',
  `condition` char(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '附加条件',
  `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
  `update_time` int(11) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜单规则';

insert into `hkcms_auth_rule` values (33,0,'index/dashboard','Dashboard','','','fas fa-tachometer-alt','',5,0,'normal',0,'',1594779897,1617268812);
insert into `hkcms_auth_rule` values (34,58,'auth','Auth','','','fas fa-user-alt','',17,1,'normal',0,'',1594779929,1610535401);
insert into `hkcms_auth_rule` values (35,34,'auth/rule','Menu','auth.rule/index','','far fa-circle','',20,1,'normal',1,'',1594780028,1601016617);
insert into `hkcms_auth_rule` values (36,35,'auth/rule/index','View','auth.rule/index','','far fa-circle','',0,0,'normal',0,'',1594780073,1594978319);
insert into `hkcms_auth_rule` values (37,35,'auth/rule/add','Add','auth.rule/add','','far fa-circle','',0,0,'normal',0,'',1594780119,1594780119);
insert into `hkcms_auth_rule` values (38,35,'auth/rule/edit','Edit','auth.rule/edit','','far fa-circle','',0,0,'normal',0,'',1594780154,1594780794);
insert into `hkcms_auth_rule` values (39,35,'auth/rule/del','Delete','auth.rule/del','','far fa-circle','',0,0,'normal',0,'',1594780205,1594780205);
insert into `hkcms_auth_rule` values (40,34,'auth/admin','Admin','auth.admin/index','','far fa-circle','',18,1,'normal',1,'',1594780596,1601016611);
insert into `hkcms_auth_rule` values (41,34,'auth/group','Group','auth.group/index','','far fa-circle','',19,1,'normal',1,'',1594780999,1601016612);
insert into `hkcms_auth_rule` values (42,34,'auth/adminlog','Admin log','auth.adminlog/index','','far fa-circle','',21,1,'normal',1,'',1594781045,1601016622);
insert into `hkcms_auth_rule` values (43,57,'appcenter','Application','','','fas fa-th','',31,1,'normal',0,'',1594781146,1610535417);
insert into `hkcms_auth_rule` values (45,58,'cms/model','Model','cms.model/index','','fas fa-database','',16,1,'normal',1,'',1596080998,1610535376);