MySQL库表练习

准备工作

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
character-set-server=utf8		//永久将数据库的字符集设置为utf-8
validate_password=off		//永久将密码策略关闭（不安全，为了后期允许tom用户的密码可以设置为“mysql”）
#
# 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
#
# 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 = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid 

[root@localhost ~]# systemctl restart mysqld	//重启MySQL服务让配置生效

1、创建数据库school，字符集为utf8

mysql> CREATE DATABASE school DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
| test               |
| test1              |
+--------------------+
8 rows in set (0.00 sec)

mysql> show create database school;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

2、在school数据库中创建Student和Score表

mysql> use school;		//进入school数据库
Database changed

mysql> create table Student(
    -> Id int(10) comment '学号' PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
    -> Name varchar(20) comment '姓名' NOT NULL,
    -> Sex varchar(4) comment '性别',
    -> Birth year comment '出生年份',
    -> Department varchar(20) comment '院系' NOT NULL,
    -> Address varchar(50) comment '家庭住址'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table Score(
    -> Id int(10) comment '编号' PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
    -> Stu_id int(10) comment '学号' NOT NULL,
    -> C_name varchar(20) comment '课程名',
    -> Grade int(10) comment '分数' 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| Score            |
| Student          |
+------------------+
2 rows in set (0.00 sec)

mysql> desc Student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| Id         | int(10)     | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(20) | NO   |     | NULL    |                |
| Sex        | varchar(4)  | YES  |     | NULL    |                |
| Birth      | year(4)     | YES  |     | NULL    |                |
| Department | varchar(20) | NO   |     | NULL    |                |
| Address    | varchar(50) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

mysql> desc Score;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| Id     | int(10)     | NO   | PRI | NULL    | auto_increment |
| Stu_id | int(10)     | NO   |     | NULL    |                |
| C_name | varchar(20) | YES  |     | NULL    |                |
| Grade  | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show create table Student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Student | CREATE TABLE `Student` (
  `Id` int(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `Name` varchar(20) NOT NULL COMMENT '姓名',
  `Sex` varchar(4) DEFAULT NULL COMMENT '性别',
  `Birth` year(4) DEFAULT NULL COMMENT '出生年份',
  `Department` varchar(20) NOT NULL COMMENT '院系',
  `Address` varchar(50) DEFAULT NULL COMMENT '家庭住址',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8                 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table Score;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Score | CREATE TABLE `Score` (
  `Id` int(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `Stu_id` int(10) NOT NULL COMMENT '学号',
  `C_name` varchar(20) DEFAULT NULL COMMENT '课程名',
  `Grade` int(10) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、授权用户tom，密码mysql，能够从任何地方登录并管理数据库school。

mysql> create user 'tom'@'%' identified by 'mysql';		//创建tom用户，设置密码为“mysql”并允许从任何地方登录
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on school.* to 'tom'@'%';		//授权tom用户数据库school的所有权限（允许管理school）
Query OK, 0 rows affected (0.00 sec)

4、使用mysql客户端登录服务器，重置root密码

mysql> grant all on *.* to admin@'%' identified by '123456';	//授权“admin”用户远程登录MySQL的所有权限，且密码为“123456”
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@localhost ~]# systemctl stop firewalld	//关闭防火墙（也可以选择写防火墙规则放行）

使用SQLyog进行客户端登录

• MySQL主机地址填写服务端IP地址“192.168.25.133”
• 用户名填写“admin”
• 密码填写“123456”
• 端口填写3306（服务器如果更改了端口号，需要填写更改后的端口号）
  
  使用客户端登录成功
  
  在客户端上重置root密码
  【注：需要选中需要执行的语句，然后点击下图中红圈框中的执行命令按钮，即可允许命令】
  
  服务端上测试root密码重置效果
  
  可以看到重置成功（原密码为“MySql@123”）