安装配置mysql5.7.24遇到的问题及解决办法

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

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

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

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

C:\Windows\system32>cd D:
D:\
C:\Windows\system32>D:
D:\>cd D:\Mysql5.7.24\mysql-5.7.24-winx64\bin
D:\Mysql5.7.24\mysql-5.7.24-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

登录时出现错误：

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

停止服务

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

输入：

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

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

C:\Windows\system32>D:
D:\>cd D:\Mysql5.7.24\mysql-5.7.24-winx64\bin
D:\Mysql5.7.24\mysql-5.7.24-winx64\bin>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: '\xC4\xD0' 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;