0.1 MySQL常见问题（Win11）

1. 启动/停止mysql服务

1 2	`net stop mysql80 #停止服务 net start mysql80 #启动服务`

2. MySQL80忘记密码

启动命令行（管理员）
1. win+R 打开运行
2. 输入cmd
3. 按下ctrl + shift + enter 管理员权限启动
停止数据库服务

1	`net stop mysql80 #停止服务`

越过密码认证(如果报错转到步骤7)

1	`mysqld --console --skip-grant-tables --shared-memory`

管理员权限打开另一个命令行，输入

1	`mysql -u root -p`

提示输入密码时直接回车即可登入

设置新密码

1
2
3

update mysql.user set authentication_string='' where user='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

上述'root'@'localhost'，若修改过支持了远程连接，则应该是'root'@'%'，具体可通过user表查看:

1	`select user,host from mysql.user where user='root';`

关闭终端，新开一个终端，启动服务并输入密码登录

1 2	`net start mysql mysql -u root -p`

命令代码无效，两种解决办法

不会保留数据库的数据

在MySQL安装目录下没有data文件夹和my.ini文件
先删除mysql服务

1	`sc delete MySql80`

删除完成之后，Win+R，输入services.msc，手动检查mysql80服务有没有正常删除，如果还在运行，一定要先停止

新建my.ini配置文件

简单版

[mysql]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
basedir=C:/Program Files/MySQL/MySQL Server 8.0 
datadir=C:/Program Files/MySQL/MySQL Server 8.0/data

如果不行那就复杂版

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = C:\Program Files\MySQL\MySQL Server 8.0 
datadir =  C:\Program Files\MySQL\MySQL Server 8.0\data
port = 3306
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

其中basedir和databir记得改成自己对应的mysql目录

在终端执行

1	`mysqld --initialize-insecure --user=mysql`

会生成data文件夹

再输入

1	`mysqld --install "MySql80" --defaults-file="C:\Program Files\MySQL\MySQL Server 8.0\my.ini"`

最后启动MySQL80服务
1
net start mysql80
登录mysql，不需要密码
1
mysql -u root -p

输入更改密码命令

1	`ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';`

就结束啦

保留数据库的数据

概念了解
- Data/data目录：存放着创建的数据库
- my.ini：一些相关配置
- services.msc：可以调出服务程序
- net stop 服务名：可以关闭服务
- net start 服务名：可以启动服务
- -defaults-file：指定服务配置
- –console --skip-grant-tables --shared-memory：跳过密码验证的关键
我的data不在安装目录，而是在C:Server 8.0：
打开my.ini并插入：skip-grant-tables;
运行：services.msc 打开服务后，停止mysql服务

管理员权限打开终端，输入

1
2
3

mysqld --defaults-file=“C:\ProgramData\MySQL\MySQL Server 8.0\my.ini” --console --skip-grant-tables --shared-memory
# 解释
#mysqld --defaults-file=“这里是你的my.ini配置路径” --console --skip-grant-tables --shared-memory

打开新的命令提示符
1
mysql -u root -p
回车进入root

修改密码输入：

1	`ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’;`

若没有出现Query OK，则执行：

1 2	`flush privileges; ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’;`

最后修改完毕注用#释掉my.ini这行语句
关闭所有界面，启动mysql，就可以正常输入密码登录了

-- 所有的建表代码如下：

1、约束代码：

create table user(

	id int  KEY auto_increment COMMENT '主键',

	name varchar(10) not null unique comment '姓名',

	age int check( age > 0 && age <= 120) comment '年龄',

	status char(1) default '1' comment '状态',

 gender char(1) comment '性别'



) comment '用户表';



insert into user(name,age,status,gender) values('TOM1',19,'1','男'),('TOM2',25,'0','男'); 



2、外键约束:

create table dept(

id int auto_increment comment 'ID'  key,

name varchar(50) not null comment '部门名称'

)comment '部门表';

INSERT INTO dept(id,name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');



create table emp

(

id int auto_increment comment 'ID'  key,

name varchar(50) not null comment '姓名',

age int comment '年龄',

job varchar(20) comment '职位',

salary int comment '薪资',

entrydate date comment '入职时间',

managerid int comment '直质领导ID',

dept_id int comment '部门ID'

) comment '员工表1' ;



INSERT INTO emp(id,name,age, job, salary, entrydate, managerid, dept_id) VALUES

(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),

(2,'张无忌', 20, '项目经理', 12500, '2005-12-05',1,1),

(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),

(4,'书一笑',48,'开发',11000,'2002-02-05',2,1),

(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),

(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);



P30:

CREATE table score

(

  id   INT COMMENT '编号',

  name  VARCHAR(20) COMMENT '姓名',

  math  INT COMMENT '数学',

  english INT COMMENT '英语',

  chinese INT COMMENT '语文'

) COMMENT '学员成绩表';



INSERT INTO score

VALUES (1, 'Tom', 67, 88, 95),

    (2, 'Rose', 23, 66, 90),

    (3, 'Jack', 56, 98, 76);



P37-多表查询：

create table student(

   id int auto_increment  key comment '主键ID',

   name varchar(10) comment '姓名',

   no varchar(10) comment '学号'

)comment ' 学生表';



insert into student values (null,'黛绮丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');



create table course(

   id int auto_increment  key comment '主键ID',

   name varchar(10) comment '课程名称'

)comment '课程表';



insert into course values (null,'Java'),(null,'PHP'),(null,'Mysql'),(null,'Hadoop');





create table student_course(

   id int auto_increment comment '主键'  key,

   studentid int not null comment '学生ID',

   courseid int not null comment '课程ID',

   constraint fk_courseid foreign key (courseid) references course(id),

   constraint fk_studentid foreign key (studentid) references student(id)

)comment '学生课程中间表';



insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,3),(null,3,4);



P37:一对一

create table tb_user(

  id int auto_increment  key COMMENT '主键ID',

  name varchar(10) COMMENT '姓名',

  age int COMMENT '年龄',

  gender char(1) COMMENT '1:男， 2：女',

  phone char(11) COMMENT '手机号'

)comment '用户基本信息表';



create table tb_user_edu(

  id int auto_increment primary key COMMENT '主键ID',

  degree varchar(20) COMMENT '学历',

  major varchar(50) COMMENT '专业',

  primaryschool varchar(50) COMMENT '小学',

  middleschool varchar(50) COMMENT '中学',

  university varchar(50) COMMENT '大学',

  userid int unique COMMENT '用户ID',

  constraint fk_userid foreign key (userid) references tb_user(id)

)comment '用户教育信息表';



insert into tb_user(id,name,age,gender,phone) values

(null,'黄渤',45,'1','18800001111'),

(null,'冰冰',35,'2','18800002222'),

(null,'码云',55,'1','18800008888' ),

(null,'李彦宏',50,'1','18800009999');



insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values

(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),

(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),

(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),

(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);



P38:多表查询

create table dept(

  id int primary key auto_increment,

  name varchar(10)

);



insert into dept values

   (null, '研发部'),

   (null, '市场部'),

   (null, '财务部'),

   (null, '销售部'),

   (null, '总经办'),

   (null, '人事部');



create table emp(

  id int primary key auto_increment,

  name varchar(10),

  age int,

  job varchar(10),

  salary int,

  entrydate date,

  managerid int,

  dept_id int,

  constraint fk_dept foreign key (dept_id) references dept(id)

);



insert into emp values

  (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),

  (null, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),

  (null, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),

  (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),

  (null, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),

  (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),

  (null, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),

  (null, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3),

  (null, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),

  (null, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),

  (null, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),

  (null, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),

  (null, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),

  (null, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),

  (null, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),

  (null, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),

  (null, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);



create table salgrade(

  grade int,

  losal int,

  hisal int

);



insert into salgrade

values (1, 0, 3000),

    (2, 3001, 5000),

    (3, 5001, 8000),

    (4, 8001, 10000),

    (5, 10001, 15000),

    (6, 15001, 20000),

    (7, 20001, 25000),

    (8, 25001, 30000);



P74:

CREATE TABLE users (

  id INT PRIMARY KEY,

    name VARCHAR(255),

    phone VARCHAR(20),

    email VARCHAR(255),

     profession VARCHAR(255),

     age INT,

     gender INT,

     status INT,

     createtime DATETIME );

 INSERT INTO users (id, name, phone, email, profession, age, gender, status, createtime) VALUES

  (1, '吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, 1, 6, '2001-02-02 00:00:00'),

  (2, '曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 1, 0, '2001-03-05 00:00:00'),

   (3, '赵云', '17799990002', '177999900@139.com', '英语', 34, 1, 2, '2002-03-02 00:00:00'),

   (4, '孙悟空', '17799990003', '177999900@sina.com', '工程造价', 54, 1, 0, '2001-07-02 00:00:00'),

   (5, '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 2, 1, '2001-04-22 00:00:00'),

   (6, '大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'),

   (7, '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 2, 0, '2001-02-08 00:00:00'),

  (8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 0, '2001-05-23 00:00:00'),

   (9, '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 1, 1, '2001-09-18 00:00:00'),

  (10, '白起', '17799990009', 'baiqi666@163.com', '机械工程及其自动化', 27, 1, 2, '2001-08-16 00:00:00'),

  (11, '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, 1, 0, '2001-06-12 00:00:00'),

   (12, '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, 1, 0, '2001-05-11 00:00:00'),

   (13, '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, 1, 1, '2001-04-09 00:00:00'),

   (14, '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 1, 2, '2001-04-10 00:00:00'),

  (15, '貂蝉', '17799990014', '8495848374@qq.com', '软件工程', 40, 2, 1, '2001-02-12 00:00:00'),

   (16, '妲己', '17799990015', '2782382939@qq.com', '软件工程', 31, 2, 0, '2001-01-30 00:00:00'),

   (17, '半月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 2, 0, '2000-05-03 00:00:00'),

   (18, '赢政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2001-08-08 00:00:00'),

   (19, '狄仁杰', '17799990018', 'jujiamlm816@163.com', '国际贸易', 30, 1, 1, '2007-03-12 00:00:00'),

   (20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市规划', 51, 2, 0, '2001-08-15 00:00:00'),

   (21, '典韦', '17799990020', 'ycauanjian@163.com', '城市规划', 52, 1, 0, '2000-04-12 00:00:00'),

   (22, '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2002-07-18 00:00:00'),

   (23, '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, 1, 0, '2002-03-10 00:00:00'),

   (24, '姜子牙', '17799990023', '3748384@qq.com', '工程造价', 29, 1, 4, '2003-05-26 00:00:00');

后端开发 > MySQL

#MySQL

0.1 MySQL常见问题（Win11）

http://binbo-zappy.github.io/2024/11/25/MySQL/0-1-MySQL常见问题/

作者

Binbo

发布于

2024年11月25日

许可协议

基础篇 1.通用语法及分类 1.1 DDL（数据定义语言）上一篇

5.3 磁盘和固态硬盘下一篇