Mysql 库表操作

MySQL第二次作业--库表操作 

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

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

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

---

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

         创建数据库school：

mysql> create database school;

         设置字符集为utf8 ：

[root@mysqlserver ~]# vim /etc/my.cnf     //在该配置文件中添加如下信息

[mysqld]
character-set-server=utf8

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

mysql> use school
Database changed
mysql> create table student
    -> 
    -> (id int(10) primary key auto_increment,
    -> name varchar(20)not null,
    -> sex varchar(4),
    -> birth year,
    -> department varchar(20)not null,
    -> address varchar(50)
    -> );
Query OK, 0 rows affected (0.01 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.00 sec)

 

mysql> create table score(
    -> id int(10) primary key auto_increment,
    -> Stu_id int(10) not null,
    -> C_name varchar(20),
    -> Grade int(10)
    -> );
Query OK, 0 rows affected (0.01 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)

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

 出现此问题原因：密码过于简单。

  解决：修改密码策略。

查看 mysql 初始的密码策略，
输入语句 “ SHOW VARIABLES LIKE 'validate_password%'; ” 进行查看，如下图：

mysql> show variables like 'validate_password%';      

处理如下：

[root@mysqlserver ~]# vim /etc/my.cnf      //在配置文件中添加如下：

[mysqld]
validate_password=off
[root@mysqlserver ~]# systemctl restart mysqld    //重启服务即可

再次授权用户，密码等信息。

mysql> create user 'tom'@'%' identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)