MySQL (一) 基本概念

一、数据库定义

数据库:是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

二、SQL (Structured Query Language)

SQL 的特点：

• 数据共享
• 减小数据冗余
• 数据独立性
• 数据实现集中控制
• 数据一致性
• 故障恢复

需要掌握技能：

• 增、删、改、查
• 增加
• 删除
• 更新
• 获取

三、一些简单的操作

关于sql 登陆，权限，等。

（1） 登陆

mysql -uroot -p xxxxx;
(base) C:\Users\MR-LI>mysql -uroot -pxxxxx  #xxxxxx表示 你的密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, 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>   #代表进去了

（2） 展示所有库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| user               |
+--------------------+
5 rows in set (0.05 sec)

（3） 展示表

mysql> use user;  # 先选定你使用的库
Database changed
mysql> show tables; #展示所有表
+----------------+
| Tables_in_user |
+----------------+
| dept           |
| emp            |
| id_card        |
| person         |
| salgrade       |
+----------------+
5 rows in set (0.02 sec)

（4） 创建新用户

mysql>  create user 'new' identified by '123456';
Query OK, 0 rows affected (0.25 sec)

可以在mysql->user->User 列下看到 新建的用户 new

（5） 修改密码

刚才新建了用户 new 密码 123456 ，现在修改密码为 1234

mysql> set password for 'new'@'%'='1234';#修改密码
Query OK, 0 rows affected (0.06 sec)(base) C:\Users\MR-LI>mysql -unew -p1234; # 重新用新用户的账号密码登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'new'@'localhost' (using password: YES)(base) C:\Users\MR-LI>mysql -unew -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, 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> # 可以看到登陆成功

（6） 分配权限给新用户

mysql> grant all on *.* to 'new'@'%';# *.*代表了所有权限 
Query OK, 0 rows affected (0.08 sec)

（7） 查看权限

mysql> show grants for 'new'@'%'; # 查看权限
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for new@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `new`@`%`                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `new`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

（8） 删除用户

mysql> drop user new;
Query OK, 0 rows affected (0.35 sec)

可以从图中看到 刚才新建的用户没了，已经被删除了。