MySQLS数据库账户管理及权限

通过数据库账户管理及权限的相关知识点我们可以完成以下案例：

一，安装mysql8.0之前的环境准备：

1.检查本机主机名是否为英文，不能为中文
2.若已经安装了mysql，则：
    （1）.检查是否安装了历史mysql，要求在系统中卸载
    （2）.删除磁盘中所有mysql命名文件夹
    （2）.清除注册表
3.关闭所有安全类软件：360全家桶   杀毒（火绒）  管家
4.准备好运行库（可选）
5.找到运行服务界面
6.找到环境变量设置界面
7.找到命令控制台或powershell （注意管理员）

二，新建产品库mydb6_product

PS C:\Users\26874> mysql -uroot -p /*先登录数据库*/
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; /*查看当前所有存在的数据库*/
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb_temp1         |
| mydbl_test         |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
11 rows in set (0.01 sec)
mysql> select database();  /*查看当前正在使用的数据库*/
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> create database mydb6_product;  /*创建mydb6_product数据库*/
Query OK, 1 row affected (0.01 sec)

mysql> use mydb6_product;  /*！！！一定要记得使用该数据库*/
Database changed
mysql> select database();  /*检测mydb6_product是否被使用*/
+---------------+
| database()    |
+---------------+
| mydb6_product |
+---------------+
1 row in set (0.00 sec)  /*使用成功*/

二（1），创建employees表且每列满足每列相关需求

mysql> create table employees(id int primary key , /*列1：id ，整型 ， 主键*/
mysql> name varchar(50) not null ,   /*列2:name ，字符串，最大长度50，不能为空*/
mysql> age int not null ,   /*列3:age，整型*/
mysql> gender varchar(10) default'unknown' ,   
/*列4:gender，字符串，最大长度10，不能为空，默认值“unknown”*/
mysql> salary float not null);  /*列5：salary，浮点型*/
Query OK, 0 rows affected (0.03 sec)

mysql> desc employees;  /*检测结果*/
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | NO   |     | NULL    |       |
| age    | int         | NO   |     | NULL    |       |
| gender | varchar(10) | YES  |     | unknown |       |
| salary | float       | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

二（2），创建orders表且每列满足每列相关需求

mysql> create table orders(id int primary key ,   /*列1:id，整型，主键*/
mysql> name varchar(100) not null ,   /*列2:name，字符串，最大长度100，不能为空*/
mysql> price float not null ,   /*列3:price，浮点型*/
mysql> quantity int not null ,   /*列4:quantity，整型*/
mysql> category varchar(50) not null);  /*列5:category，字符串，最大长度50*/
Query OK, 0 rows affected (0.03 sec)

mysql> desc orders;  /*检测结果*/
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
| price    | float        | NO   |     | NULL    |       |
| quantity | int          | NO   |     | NULL    |       |
| category | varchar(50)  | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show tables;  /*查看当前数据库已创建的表*/
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees               |
| orders                  |
+-------------------------+
2 rows in set (0.00 sec)

二（3），创建invoices表且每列满足每列相关需求

mysql> create table invoices(number int primary key auto_increment , 
/*列1:number，整型，主键自增长*/
mysql> order_id int ,  foreign key(order_id) references orders(id) ,   
/*列2:order_id，整型外键关联到orders表的id列*/
/*列3:in date: 日期型*/
mysql> in_date date not null ,  /*列3:in date: 日期型*/ 
mysql> total_amount float , check(total_amount>0));
/*列4:total amount:浮点型，要求数据大于0*/
Query OK, 0 rows affected (0.04 sec)

mysql> desc invoices;   /*检测结果*/
+--------------+-------+------+-----+---------+----------------+
| Field        | Type  | Null | Key | Default | Extra          |
+--------------+-------+------+-----+---------+----------------+
| number       | int   | NO   | PRI | NULL    | auto_increment |
| order_id     | int   | YES  | MUL | NULL    |                |
| in_date      | date  | NO   |     | NULL    |                |
| total_amount | float | YES  |     | NULL    |                |
+--------------+-------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into invoices values(3,'666','2025-1-16','-100');  /*检测最后一列*/
ERROR 3819 (HY000): Check constraint 'invoices_chk_1' is violated. /*显示不符约束范围*/
mysql> show tables;  /*显示当前存在三个表，例子完成*/
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees               |
| invoices                |
| orders                  |
+-------------------------+
3 rows in set (0.00 sec)

三，最终完成实验