安装配置mysql5.7.24遇到的问题及解决办法操作系统weixin42149273的博客-

安装配置mysql遇到的问题及解决办法

问题1、“MySQL 服务正在启动 .MySQL 服务无法启动。”：删除data文件夹，将my.ini移到bin文件夹下。
问题2、

D:Mysql5.7.24mysql-5.7.24-winx64bin>mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

先打开一个“命令提示符”窗口

C:Windowssystem32>cd D: D: C:Windowssystem32>D: D:>cd D:Mysql5.7.24mysql-5.7.24-winx64bin D:Mysql5.7.24mysql-5.7.24-winx64bin>net start mysql MySQL 服务正在启动 . MySQL 服务已经启动成功。 

登录时出现错误：

D:Mysql5.7.24mysql-5.7.24-winx64bin>mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

停止服务

D:Mysql5.7.24mysql-5.7.24-winx64bin>net stop mysql MySQL 服务正在停止. MySQL 服务已成功停止。 

输入：

D:Mysql5.7.24mysql-5.7.24-winx64bin>mysqld --console --skip-grant-tables --shared-memory 

重新打开一个“命令提示符”窗口

C:Windowssystem32>D: D:>cd D:Mysql5.7.24mysql-5.7.24-winx64bin D:Mysql5.7.24mysql-5.7.24-winx64bin>mysql.exe -u root Welcome to the MySQL monitor.  Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective 

成功进入mysql。
问题3、重新设置密码出现问题

mysql> update mysql.user SET authentication_string='' WHERE user='root' and host='localhost' -> mysql.user SET authentication_string='' WHERE user='root' and host='localhost' -> update mysql.user SET authentication_string='' WHERE user='root' and host='localhost'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql.user SET authentication_string='' WHERE user='root' and host='localhost' u' at line 2 

解决办法：

mysql> use mysql; Database changed mysql> update mysql.user set authentication_string=password("123456") where user="root"; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1  Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) 

问题4、创建学生信息数据库

mysql> create database students; Query OK, 1 row affected (0.00 sec) mysql> use students; Database changed mysql> create table student mysql> create table student     -> ( -> Sno char(8) NOT NULL PRIMARY KEY, -> Sname nchar(5) NOT NULL UNIQUE, -> Ssex char(2) NOT NULL CHECK(Ssex IN('男','女')), -> Sage tinyint NOT NULL, -> Sdept nvarchar(20) NOT NULL -> ); Query OK, 0 rows affected (0.05 sec) mysql> show create table student; mysql> DESC student; mysql> create table Course     -> ( -> Cno char(6) NOT NULL PRIMARY KEY, -> Cname varchar(20) NOT NULL UNIQUE, -> Credit tinyint, -> Semster tinyint -> ); Query OK, 0 rows affected (0.03 sec) mysql> show create table Course; mysql> DESC Course; mysql> create table SC     -> ( -> Sno char(7) NOT NULL, -> Cno char(6) NOT NULL, -> Grade tinyint, -> PRIMARY KEY(Sno,Cno), -> FOREIGN KEY(Sno) REFERENCES student(Sno), -> FOREIGN KEY(Cno) REFERENCES course(Cno) -> ); Query OK, 0 rows affected (0.03 sec) mysql> show create table SC; mysql> DESC SC; 

问题5、数据插入
出现错误：

mysql> insert into student values('0611101','李勇','男',21,'计算机系'),('0611102','刘晨','男',20,'计算机系'),('0611103','王敏','女',20,'计算机系'),('0611104','张晓红','女',19,'计算机系'),('0621101','张力,'男',20,'信息管理系'); ERROR 1366 (HY000): Incorrect string value: 'xC4xD0' for column 'Ssex' at row 1 

解决办法：

mysql> alter table student change Sname Sname varchar(20) character set utf8 collate utf8_unicode_ci not null default ''; Query OK, 0 rows affected (0.09 sec) Records: 0  Duplicates: 0 Warnings: 0 mysql> alter table student change Ssex Ssex varchar(20) character set utf8 collate utf8_unicode_ci not null default ''; Query OK, 0 rows affected (0.08 sec) Records: 0  Duplicates: 0 Warnings: 0 

然后注意：

mysql> insert into student values('0611101','李勇','男',21,'计算机系'); Query OK, 1 row affected (0.01 sec) mysql> insert into student values('0611101','李勇','男',21,'计算机系'),('0611102','刘晨','男',20,'计算机系'),('0611103','王敏','女',20,'计算机系'),('0611104','张晓红','女',19,'计算机系'),('0621101','张力','男',20,'信息管理系'); ERROR 1062 (23000): Duplicate entry '0611101' for key 'PRIMARY' mysql> insert into student values('0611102','刘晨','男',20,'计算机系'),('0611103','王敏','女',20,'计 机系'),('0611104','张晓红','女',19,'计算机系'),('0621101','张力','男',20,'信息管理系'); Query OK, 4 rows affected (0.00 sec) Records: 4  Duplicates: 0 Warnings: 0 

输入mysql> select * from student;即可得到：

问题6、数据操作
（1）查询年龄在20～23岁之间的学生的姓名、所在系和年龄。

mysql> select Sname,Sdept,Sage from student where Sage between 20 and 23; 

（2）查询计算机系和信息管理系学生中年龄在18-20岁的学生的学号、姓名、所在系和年龄。

mysql> SELECT Sno,Sname, Sdept,Sage FROM student WHERE Sdept in ('计算机系' , '信息管理系') and  Sage  between 18 and 20; 

（3）查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。

mysql> SELECT * FROM student order by Sdept,Sage DESC; 

（4）查询每个学生的选课门数和平均成绩。

mysql> SELECT * FROM student order by Sdept,Sage DESC;