数据库 – 第 3 页

#1、在mysql的安装目录中，新建data目录及my.ini#注意事项my.ini文件必须要用ansi的方式编码

#2、编辑my.ini

[mysqld] 
basedir=D:\mysql-5.7.36-winx64
datadir=D:\mysql-5.7.36-winx64\data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8
character_set_filesystem=utf8

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

#3、将D:\mysql-5.7.36-winx64\bin路径添加到path中

#4.初始化数据库D:\mysql-5.7.36-winx64\bin>mysqld –initialize –user=mysql –console

2022-03-30T07:44:01.640992Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

2022-03-30T07:44:01.641071Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2022-03-30T07:44:01.641079Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2022-03-30T07:44:02.890183Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-30T07:44:03.350137Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2022-03-30T07:44:03.496899Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2b9d5b48-affd-11ec-83b7-0250f2000002.
2022-03-30T07:44:03.537576Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-30T07:44:04.828539Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-03-30T07:44:04.828830Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-03-30T07:44:04.829738Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-30T07:44:06.375278Z 1 [Note] A temporary password is generated for root@localhost: V8uZTa!8fk.p

#【注意】记录随机生成密码或初始采用这条命令：mysqld initialize insecure –user=mysql –console

#5.启动服务mysql服务net start mysql

【帮助指南】

1、netstat -ano|findstr 3306
2、Windows下Mysql5.7忘记root密码的解决方法

打开第一个cmd窗口执行 net stop mysql57
在第一个cmd窗口执行 mysqld –defaults-file=”C:\ProgramData\MySQL\MySQL Server 5.7\my.ini” –skip-grant-tables —注意以你的路径为准
打开第二个cmd窗口执行 mysql -uroot -p 提示输入密码，直接回车（不用输入密码）
选择数据库：use mysql;
更新root的密码：update user set authentication_string=password(‘新密码’) where user=’root’ and Host=’localhost’;
刷新权限：flush privileges;
退出：quit
重新登录：mysql -uroot -p 提示输入密码，这时输入密码才能登录。完成！！！

【FQA】mysqld: [ERROR] Found option without preceding group in config file D:\mysql-5.7.36-winx64\my.ini at line 1!
 
没有新建库，需要建立一个库

create table student (
id int(4) not null,
name char(20) not null,
age tinyint(2) not null,
dept varchar(16) not null
);

create database sns; use sns; set names gbk; create table `subject_comment_manager` ( `subject_comment_manager_id` bigint(12) not null auto_increment comment ‘主键’, `subject_type` tinyint(2) not null comment ‘素材类型’, `subject_primary_key` varchar(255) not null comment ‘素材主键’, `subject_title` varchar(255) not null comment ‘素材名称’, `edit_user_nick` varchar(64) not null comment ‘修改人’, `edit_user_time` timestamp null default null comment ‘修改时间’, `edit_comment` varchar(255) default null comment ‘修改的理由’, `state` tinyint(1) not null default ‘1’ comment ‘0 代表关闭，1 代表正常’, primary key (`subject_comment_manager_id`), key `IDX_PRIMARYKEY` (`subject_title`(32)), #括号内的32表示对前32个字符做前缀索引。 key `IDX_SUBJECT_TITLE` (`subject_title`(32)), key `index_nick_type` (`edit_user_nick`(32),`subject_type`) #联合索引，此行为新加的，用于给大家讲解的，实际表语句内没有此行。 ) engine=innodb auto_increment=1 default charset=utf8;

=====================联表查询====================
create table student(
Sno int(10) not null comment ‘学号’,
Sname varchar(16) not null comment ‘姓名’,
Ssex char(6) not null comment ‘性别’,
Sage tinyint(2) not null default ‘0’ comment ‘学生年龄’,
Sdept varchar(16) default null comment ‘学生所在系别’,
primary key (Sno),
key index_Sname(Sname)
) engine=innodb auto_increment=1 default charset=utf8;

create table course(
Cno int(10) not null comment ‘课程号’,
Cname varchar(64) not null comment ‘课程名’,
Ccredit tinyint(2) not null comment ‘学分’,
primary key (Cno)
) engine=innodb auto_increment=1 default charset=utf8;

create table SC(
SCid int(12) not null auto_increment comment ‘主键’,
Cno int(10) not null comment ‘课程号’,
Sno int(10) not null comment ‘学号’,
Grade tinyint(2) not null comment ‘学生成绩’,
primary key (SCid)
) engine=innodb default charset=utf8;

随便插入学生数据

insert into student values(0001,’hognzhi’,’male’,30,’jisuanji’),(0002,’wammg’,’male’,30,’jisuanji’) ,(0003,’oldboy’,’male’,28,’wuliu’) ,(0004,’ts’,’male’,29,’jisuanji’) ,(0005,’oldgirl’,’female’,30,’jisuanji’) ,(0006,’yuany’,’female’,22,’nurse’);

insert into course values(1001,’Linuxyunwei’,3),(1002,’Linuxyunwei’,5),(1003,’Linuxyunwei’,4),(1004,’Linuxyunwei’,4),(1001,’Linuxyunwei’,3);

insert into SC(Sno,Cno,Grade) values(0001,1001,4);
insert into SC(Sno,Cno,Grade) values(0001,1002,3);
insert into SC(Sno,Cno,Grade) values(0001,1003,1);
insert into SC(Sno,Cno,Grade) values(0001,1003,6);

insert into SC(Sno,Cno,Grade) values(0002,1001,3);
insert into SC(Sno,Cno,Grade) values(0002,1002,2);
insert into SC(Sno,Cno,Grade) values(0002,1003,2);
insert into SC(Sno,Cno,Grade) values(0002,1003,8);

insert into SC(Sno,Cno,Grade) values(0003,1001,4);
insert into SC(Sno,Cno,Grade) values(0003,1002,4);
insert into SC(Sno,Cno,Grade) values(0003,1003,2);
insert into SC(Sno,Cno,Grade) values(0003,1003,8);

insert into SC(Sno,Cno,Grade) values(0004,1001,1);
insert into SC(Sno,Cno,Grade) values(0004,1002,1);
insert into SC(Sno,Cno,Grade) values(0004,1003,2);
insert into SC(Sno,Cno,Grade) values(0004,1003,3);

insert into SC(Sno,Cno,Grade) values(0005,1001,5);
insert into SC(Sno,Cno,Grade) values(0005,1002,3);
insert into SC(Sno,Cno,Grade) values(0005,1003,2);
insert into SC(Sno,Cno,Grade) values(0005,1003,9);

联表查询
select student.Sno,student.Sname,course.Cname,SC.Grade from student, course, SC where student.Sno=SC.Sno and course.Cno=SC.Cno;
select student.Sno,student.Sname,course.Cname,SC.Grade from student, course, SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno;
在where后面建索引

explain 查询select 查询语句执行计划
就是查询这个语句，能不能走索引？

use oldboy;
drop table test;
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key (id)
);

insert into test values(1,’oldboy’),(2,’oldgir’),(3,’inca’),(4,’zuma’),(5,’kaka’);

select * from test where name=’oldboy’;

explain select * from test where name=’oldboy’;

explain select * from test where name=’oldboy’\G;

create index index_name on test(name);

建索引和没有建索引的区别
mysql> explain select * from test where name=’oldboy’\G
* 1. row *
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)

mysql> create index index_name on test(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from test where name=’oldboy’\G
* 1. row *
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 60
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)

分类：数据库

windows 安装mysql5.7

mysql 案例