mysql必知必会 day2

男娘i 2023-02-22 12:07 28阅读 0赞

### 文章目录 ###

*  连接
 *  选择数据库：use
 *  显示数据库中的表
 *  显示一个表的列
 *  一些show命令

# 连接 #

我一直是连接的**mysql本地服务器**，即把正在用的主机用作服务器，即服务器和客户端在**一台**计算机上。这种情况下，主机名为localhost。

mysql默认使用的端口是3306，好像也可以要别的端口，这就涉及知识盲区了

连接的方式就是先`net start mysql`服务，然后`mysql -u root -p`再输入密码就好了。

# 选择数据库：use #

连接后，必须要用`use`关键字选择一个数据库。可以先用`show databases;`指令显示出服务器上的所有数据库。  
![2020070310495971.png][]

tysql是之前建立的一个数据库，现在还是用它好啦  
![在这里插入图片描述][20200703110255513.png]

# 显示数据库中的表 #

# 显示一个表的列 #

必须要用from给出表名

可见还返回了每一列的几个属性，数据类型，是否允许null值，键的类型，默认值等

除了`show columns from customers;`，还可以用describe关键字。

![在这里插入图片描述][20200703110732653.png]

# 一些show命令 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]  
显示所有的show命令

mysql> help show
    Name: 'SHOW'
    Description:
    SHOW has many forms that provide information about databases, tables,
    columns, or status information about the server. This section describes
    those following:
    
    SHOW { BINARY | MASTER} LOGS
    SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    SHOW CHARACTER SET [like_or_where]
    SHOW COLLATION [like_or_where]
    SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
    SHOW CREATE DATABASE db_name
    SHOW CREATE EVENT event_name
    SHOW CREATE FUNCTION func_name
    SHOW CREATE PROCEDURE proc_name
    SHOW CREATE TABLE tbl_name
    SHOW CREATE TRIGGER trigger_name
    SHOW CREATE VIEW view_name
    SHOW DATABASES [like_or_where]
    SHOW ENGINE engine_name { STATUS | MUTEX}
    SHOW [STORAGE] ENGINES
    SHOW ERRORS [LIMIT [offset,] row_count]
    SHOW EVENTS
    SHOW FUNCTION CODE func_name
    SHOW FUNCTION STATUS [like_or_where]
    SHOW GRANTS FOR user
    SHOW INDEX FROM tbl_name [FROM db_name]
    SHOW MASTER STATUS
    SHOW OPEN TABLES [FROM db_name] [like_or_where]
    SHOW PLUGINS
    SHOW PROCEDURE CODE proc_name
    SHOW PROCEDURE STATUS [like_or_where]
    SHOW PRIVILEGES
    SHOW [FULL] PROCESSLIST
    SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
    SHOW PROFILES
    SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    SHOW SLAVE HOSTS
    SHOW SLAVE STATUS [FOR CHANNEL channel]
    SHOW [GLOBAL | SESSION] STATUS [like_or_where]
    SHOW TABLE STATUS [FROM db_name] [like_or_where]
    SHOW [FULL] TABLES [FROM db_name] [like_or_where]
    SHOW TRIGGERS [FROM db_name] [like_or_where]
    SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
    SHOW WARNINGS [LIMIT [offset,] row_count]
    
    like_or_where:
        LIKE 'pattern'
      | WHERE expr
    
    If the syntax for a given SHOW statement includes a LIKE 'pattern'
    part, 'pattern' is a string that can contain the SQL % and _ wildcard
    characters. The pattern is useful for restricting statement output to
    matching values.
    
    Several SHOW statements also accept a WHERE clause that provides more
    flexibility in specifying which rows to display. See
    https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.
    
    URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

--------------------

show status给我突突突返回来了470行信息，全是各种状态。。

--------------------

显示出创建表或数据库的代码，这个好有用啊

mysql> show create table customers;
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                            |
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | customers | CREATE TABLE `customers` (
      `cust_id` char(10) NOT NULL,
      `cust_name` char(50) NOT NULL,
      `cust_address` char(50) DEFAULT NULL,
      `cust_city` char(50) DEFAULT NULL,
      `cust_state` char(5) DEFAULT NULL,
      `cust_zip` char(10) DEFAULT NULL,
      `cust_country` char(50) DEFAULT NULL,
      `cust_contact` char(50) DEFAULT NULL,
      `cust_email` char(255) DEFAULT NULL,
      PRIMARY KEY (`cust_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=ascii |
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

mysql> show create database tysql;
    +----------+----------------------------------------------------------------------------------------------------+
    | Database | Create Database                                                                                    |
    +----------+----------------------------------------------------------------------------------------------------+
    | tysql    | CREATE DATABASE `tysql` /*!40100 DEFAULT CHARACTER SET ascii */ /*!80016 DEFAULT ENCRYPTION='N' */ |
    +----------+----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

--------------------

show grants显示用户的权限

`root@localhost`就是 `用户名（登录名）@主机名`的格式

mysql> show grants;
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 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 `root`@`localhost` WITH GRANT OPTION                                                                                                                        |
    | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,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 `root`@`localhost` WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
    +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

可以看到很多语句都是有权限的，包括update，delete

[2020070310495971.png]: https://img-blog.csdnimg.cn/2020070310495971.png
[20200703110255513.png]: https://img-blog.csdnimg.cn/20200703110255513.png
[20200703110732653.png]: https://img-blog.csdnimg.cn/20200703110732653.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200703111012609.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70