vlambda博客
学习文章列表

Last login: Wed Oct 14 10:24:46 on ttys000
The default interactive shell is now zsh.To update your account to use zsh, please run `chsh -s /bin/zsh`.For more details, please visit https://support.apple.com/kb/HT208050.reindeMacBook-Pro:~ reinse$ /usr/local/MySQL/bin/mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database dbname;Query OK, 1 row affected (0.00 sec)
mysql> create database wddb;Query OK, 1 row affected (0.00 sec)
mysql> use wddb;Database changed
mysql> show databases;+--------------------+| Database |+--------------------+| dbname || information_schema || mysql || performance_schema || sys || wddb |+--------------------+6 rows in set (0.00 sec)
mysql> show tables;Empty set (0.00 sec)
mysql> drop database dbname;Query OK, 0 rows affected (0.01 sec)
mysql> use wddb;Database changedmysql> create table job(name varchar(20),sex varchar(2),age int(2),hiredate date,wage decimal(10,2));Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc job;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| name | varchar(20) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | || wage | decimal(10,2) | YES | | NULL | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.01 sec)
mysql> show create table job \g;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| job | CREATE TABLE `job` ( `name` varchar(20) DEFAULT NULL, `sex` varchar(2) DEFAULT NULL, `age` int DEFAULT NULL, `hiredate` date DEFAULT NULL, `wage` decimal(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
ERROR: No query specified
mysql> alter table job modify name varchar(25);Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table job add home varchar(30);Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table job drop column home;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table job change wage salary decimal(10,2);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc job;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| name | varchar(25) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | || salary | decimal(10,2) | YES | | NULL | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.00 sec)
mysql> select *from job;Empty set (0.00 sec)
mysql> alter table job rename worker;Query OK, 0 rows affected (0.01 sec)
mysql> desc worker;+----------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+---------------+------+-----+---------+-------+| name | varchar(25) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | || salary | decimal(10,2) | YES | | NULL | |+----------+---------------+------+-----+---------+-------+5 rows in set (0.01 sec)
mysql> alter table worker drop column salary;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc worker;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| name | varchar(25) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)
mysql> alter table worker add birthday date;Query OK, 0 rows affected (0.00 sec)
mysql> desc worker;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| name | varchar(25) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | || birthday | date | YES | | NULL | |+----------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)
mysql> insert into worker values("wd01","男",23,"2020-01-01","1997-12-01");Query OK, 1 row affected (0.01 sec)
mysql> insert into worker values("wd02","男",24,"2020-02-01","1997-01-01"), -> ("wd03","女",22,"2020-03-01","1997-01-03"), -> ("wd04","女",26,"2020-03-05","1997-01-04"), -> ("wd05","男",27,"2020-05-05","1997-04-04");Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from worker;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+5 rows in set (0.00 sec)
mysql> update worker set age=26 where name="wd02";Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from worker where name="wd02";Query OK, 1 row affected (0.00 sec)
mysql> select *from worker;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+4 rows in set (0.00 sec)
mysql> select name,sex,age,hiredate,birthday from worker;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+4 rows in set (0.00 sec)
mysql> insert into worker values ("wd02","男",24,"2020-02-01","1997-01-01"), ("wd06","女",22,"2020-03-01","1997-01-03"), ("wd07","女",26,"2020-03-05","1997-01-04"), ("wd08","男",27,"2020-05-05","1997-04-04");Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0
mysql> select *from worker;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> desc worker;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| name | varchar(25) | YES | | NULL | || sex | varchar(2) | YES | | NULL | || age | int | YES | | NULL | || hiredate | date | YES | | NULL | || birthday | date | YES | | NULL | |+----------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)
mysql> select distinct age from worker;+------+| age |+------+| 23 || 22 || 26 || 27 || 24 |+------+5 rows in set (0.00 sec)
mysql> select *from worker where age=24;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 |+------+------+------+------------+------------+1 row in set (0.00 sec)
mysql> select *from worker where age >24;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+4 rows in set (0.00 sec)
mysql> select *from worker where age >=24;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+5 rows in set (0.00 sec)
mysql> select *from worker where age>=24;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+5 rows in set (0.00 sec)
mysql> select *from worker order by sex desc;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> select *from worker order by age;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> select *from worker order by age desc;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 |+------+------+------+------------+------------+8 rows in set (0.01 sec)
mysql> select *from worker limit 3;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 |+------+------+------+------------+------------+3 rows in set (0.00 sec)
mysql> select *from worker order by age desc limit 2,3;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 |+------+------+------+------------+------------+3 rows in set (0.00 sec)
mysql> select *from worker order by age desc;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> select sum(age) from worker;+----------+| sum(age) |+----------+| 197 |+----------+1 row in set (0.00 sec)
mysql> select max(age),min(age) from worker;+----------+----------+| max(age) | min(age) |+----------+----------+| 27 | 22 |+----------+----------+1 row in set (0.00 sec)
mysql> select count(1) from worker;+----------+| count(1) |+----------+| 8 |+----------+1 row in set (0.00 sec)
mysql> select age,count(1) from worker group by age;+------+----------+| age | count(1) |+------+----------+| 23 | 1 || 22 | 2 || 26 | 2 || 27 | 2 || 24 | 1 |+------+----------+5 rows in set (0.00 sec)
mysql> select age,count(1) -> from worker -> group by age with rollup;+------+----------+| age | count(1) |+------+----------+| 22 | 2 || 23 | 1 || 24 | 1 || 26 | 2 || 27 | 2 || NULL | 8 |+------+----------+6 rows in set (0.00 sec)
mysql> select age,count(1) from worker group by age with rollup having count(1)>1;+------+----------+| age | count(1) |+------+----------+| 22 | 2 || 26 | 2 || 27 | 2 || NULL | 8 |+------+----------+4 rows in set (0.00 sec)
mysql> select *from worker;+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> create table worker_type(name varchar(20),work varchar(20));Query OK, 0 rows affected (0.01 sec)

mysql> insert into worker_type values ("wd02","teacher"), ("wd03","writer"), ("wd04","programmer"), ("wd05","programmer"), ("wd06","teacher"), ("wd07","doctor"), ("wd08","writer"), ("wd01","manager");Query OK, 8 rows affected (0.01 sec)Records: 8 Duplicates: 0 Warnings: 0
mysql> select *from worker_type;+------+------------+| name | work |+------+------------+| wd02 | teacher || wd03 | writer || wd04 | programmer || wd05 | programmer || wd06 | teacher || wd07 | doctor || wd08 | writer || wd01 | manager |+------+------------+8 rows in set (0.00 sec)
mysql> select worker.name,worker_type.name from worker,worker_type where worker.name=worker_type.name;+------+------+| name | name |+------+------+| wd02 | wd02 || wd03 | wd03 || wd04 | wd04 || wd05 | wd05 || wd06 | wd06 || wd07 | wd07 || wd08 | wd08 || wd01 | wd01 |+------+------+8 rows in set (0.00 sec)
mysql> select worker.name,worker_type.* from worker left join worker_type on worker.name=worker_type.name;+------+------+------------+| name | name | work |+------+------+------------+| wd01 | wd01 | manager || wd03 | wd03 | writer || wd04 | wd04 | programmer || wd05 | wd05 | programmer || wd02 | wd02 | teacher || wd06 | wd06 | teacher || wd07 | wd07 | doctor || wd08 | wd08 | writer |+------+------+------------+8 rows in set (0.00 sec)
mysql> select worker.name,worker_type.work from worker left join worker_type on worker.name=worker_type.name;+------+------------+| name | work |+------+------------+| wd01 | manager || wd03 | writer || wd04 | programmer || wd05 | programmer || wd02 | teacher || wd06 | teacher || wd07 | doctor || wd08 | writer |+------+------------+8 rows in set (0.00 sec)
mysql> select worker.* from worker where name in(select name from worker_type);+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd01 | 男 | 23 | 2020-01-01 | 1997-12-01 || wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd04 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd05 | 男 | 27 | 2020-05-05 | 1997-04-04 || wd02 | 男 | 24 | 2020-02-01 | 1997-01-01 || wd06 | 女 | 22 | 2020-03-01 | 1997-01-03 || wd07 | 女 | 26 | 2020-03-05 | 1997-01-04 || wd08 | 男 | 27 | 2020-05-05 | 1997-04-04 |+------+------+------+------------+------------+8 rows in set (0.00 sec)
mysql> select *from worker where name =(select name from worker_type limit 1,1);+------+------+------+------------+------------+| name | sex | age | hiredate | birthday |+------+------+------+------------+------------+| wd03 | 女 | 22 | 2020-03-01 | 1997-01-03 |+------+------+------+------------+------------+1 row in set (0.00 sec)
mysql> select name from worker union all select name from worker_type;+------+| name |+------+| wd01 || wd03 || wd04 || wd05 || wd02 || wd06 || wd07 || wd08 || wd02 || wd03 || wd04 || wd05 || wd06 || wd07 || wd08 || wd01 |+------+16 rows in set (0.00 sec)
mysql> select name from worker union select name from worker_type;+------+| name |+------+| wd01 || wd03 || wd04 || wd05 || wd02 || wd06 || wd07 || wd08 |+------+8 rows in set (0.00 sec)
You asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories: Account Management Administration Components Compound Statements Contents Data Definition Data Manipulation Data Types Functions Geographic Features Help Metadata Language Structure Plugins Storage Engines Table Maintenance Transactions User-Defined Functions Utility

mysql> ? account manager;
Nothing foundPlease try to run 'help contents' for a list of all accessible topics
mysql> ? account management;You asked for help about help category: "Account Management"For more information, type 'help <item>', where <item> is one of the followingtopics: ALTER RESOURCE GROUP ALTER USER CREATE RESOURCE GROUP CREATE ROLE CREATE USER DROP RESOURCE GROUP DROP ROLE DROP USER GRANT RENAME USER REVOKE SET DEFAULT ROLE SET PASSWORD SET RESOURCE GROUP SET ROLE
mysql> ? data types;You asked for help about help category: "Data Types"For more information, type 'help <item>', where <item> is one of the followingtopics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPE
mysql> ? varchar;Name: 'VARCHAR'Description:[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATEcollation_name]
A variable-length string. M represents the maximum column length incharacters. The range of M is 0 to 65,535. The effective maximum lengthof a VARCHAR is subject to the maximum row size (65,535 bytes, which isshared among all columns) and the character set used. For example, utf8characters can require up to three bytes per character, so a VARCHARcolumn that uses the utf8 character set can be declared to be a maximumof 21,844 characters. Seehttps://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html.
MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plusdata. The length prefix indicates the number of bytes in the value. AVARCHAR column uses one length byte if values require no more than 255bytes, two length bytes if values may require more than 255 bytes.
*Note*:
MySQL follows the standard SQL specification, and does not removetrailing spaces from VARCHAR values.
VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is thestandard SQL way to define that a VARCHAR column should use somepredefined character set. MySQL uses utf8 as this predefined characterset. https://dev.mysql.com/doc/refman/8.0/en/charset-national.html.NVARCHAR is shorthand for NATIONAL VARCHAR.
URL: https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html

mysql> ? show;Name: 'SHOW'Description:SHOW has many forms that provide information about databases, tables,columns, or status information about the server. This section describesthose following:
SHOW {BINARY | MASTER} LOGSSHOW 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_nameSHOW CREATE EVENT event_nameSHOW CREATE FUNCTION func_nameSHOW CREATE PROCEDURE proc_nameSHOW CREATE TABLE tbl_nameSHOW CREATE TRIGGER trigger_nameSHOW CREATE VIEW view_nameSHOW DATABASES [like_or_where]SHOW ENGINE engine_name {STATUS | MUTEX}SHOW [STORAGE] ENGINESSHOW ERRORS [LIMIT [offset,] row_count]SHOW EVENTSSHOW FUNCTION CODE func_nameSHOW FUNCTION STATUS [like_or_where]SHOW GRANTS FOR userSHOW INDEX FROM tbl_name [FROM db_name]SHOW MASTER STATUSSHOW OPEN TABLES [FROM db_name] [like_or_where]SHOW PLUGINSSHOW PROCEDURE CODE proc_nameSHOW PROCEDURE STATUS [like_or_where]SHOW PRIVILEGESSHOW [FULL] PROCESSLISTSHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]SHOW PROFILESSHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]SHOW SLAVE HOSTSSHOW 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 _ wildcardcharacters. The pattern is useful for restricting statement output tomatching values.
Several SHOW statements also accept a WHERE clause that provides moreflexibility in specifying which rows to display. Seehttps://dev.mysql.com/doc/refman/8.0/en/extended-show.html.
URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

mysql> show create database wddb;+----------+--------------------------------------------------------------------------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------------------------------------------------------------------------+| wddb | CREATE DATABASE `wddb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |+----------+--------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> create table test1(ald int, bld int(5));Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc test1;+-------+------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------+------+-----+---------+-------+| ald | int | YES | | NULL | || bld | int | YES | | NULL | |+-------+------+------+-----+---------+-------+2 rows in set (0.00 sec)
mysql> alter table test1 modify ald int zerofill;Query OK, 0 rows affected, 1 warning (0.02 sec)Records: 0 Duplicates: 0 Warnings: 1
mysql> alter table test1 modify bld int(5) zerofill;Query OK, 0 rows affected, 2 warnings (0.02 sec)Records: 0 Duplicates: 0 Warnings: 2
mysql> select *from test1;Empty set (0.00 sec)
mysql> insert into test1 values(1234567890,1234567);Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values -> (000000001,00011), -> (000000002,00022), -> (000000003,00033), -> (000000004,00044), -> (000000005,00055);Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0
mysql> select *from test1;+------------+---------+| ald | bld |+------------+---------+| 1234567890 | 1234567 || 0000000001 | 00011 || 0000000002 | 00022 || 0000000003 | 00033 || 0000000004 | 00044 || 0000000005 | 00055 |+------------+---------+6 rows in set (0.00 sec)
mysql> create table test2(ald float(6,2) default NULL,bld double(6,2) default NULL,cld decimal(6,2) default NULL);Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> insert into test2 values(1234.12,1234.12,1234.12);Query OK, 1 row affected (0.00 sec)
mysql> select *from test2;+---------+---------+---------+| ald | bld | cld |+---------+---------+---------+| 1234.12 | 1234.12 | 1234.12 |+---------+---------+---------+1 row in set (0.00 sec)
mysql> alter table test2 modify ald float;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test2 modify bld double;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test2 modify cld decimal;Query OK, 1 row affected, 1 warning (0.02 sec)Records: 1 Duplicates: 0 Warnings: 1
mysql> select *from test2;+---------+---------+------+| ald | bld | cld |+---------+---------+------+| 1234.12 | 1234.12 | 1234 |+---------+---------+------+1 row in set (0.00 sec)
mysql> insert into test2 values(1.23,1.23,1.23);Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> create table test3(id bit(1));Query OK, 0 rows affected (0.01 sec)
mysql> insert into test3 values(1);Query OK, 1 row affected (0.00 sec)
mysql> select *from test3;+------------+| id |+------------+| 0x01 |+------------+1 row in set (0.00 sec)
mysql> select hex(id),bin(id) from test3;+---------+---------+| hex(id) | bin(id) |+---------+---------+| 1 | 1 |+---------+---------+1 row in set (0.00 sec)
mysql> alter table test3 modify id bit(2);Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into test3 values(2);Query OK, 1 row affected (0.00 sec)
mysql> create table test4(id year);Query OK, 0 rows affected (0.01 sec)
mysql> desc test4;+-------+------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------+------+-----+---------+-------+| id | year | YES | | NULL | |+-------+------+------+-----+---------+-------+1 row in set (0.01 sec)
mysql> insert into test4 values(2020),('2020');Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0
mysql> select *from test4;+------+| id |+------+| 2020 || 2020 |+------+2 rows in set (0.00 sec)
mysql> delete from test4;Query OK, 2 rows affected (0.00 sec)
mysql> insert into test4 values('0'),('00'),('11');Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test4 values('88'),('20'),('21');Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from test4;+------+| id |+------+| 2000 || 2000 || 2011 || 1988 || 2020 || 2021 |+------+6 rows in set (0.00 sec)
mysql> delete from test4;Query OK, 6 rows affected (0.00 sec)
mysql> alter table test4 modify id TIME;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test4 modify id TIME;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into test4 values ('15:11:23'), ('20:13'), ('2 11:11'), ('3 05'), ('33');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0
mysql> select *from test4;+----------+| id |+----------+| 15:11:23 || 20:13:00 || 59:11:00 || 77:00:00 || 00:00:33 |+----------+5 rows in set (0.00 sec)
mysql> create table test5(id date);Query OK, 0 rows affected (0.01 sec)
mysql> insert into test5 values ('2020-06-13'),('20200613'),(20200613);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from test5;+------------+| id |+------------+| 2020-06-13 || 2020-06-13 || 2020-06-13 |+------------+3 rows in set (0.00 sec)
mysql> create table test6(id datetime);Query OK, 0 rows affected (0.00 sec)
mysql> insert into test4 values -> ('2020-06-13 11:11:11'), -> (20200613111111), -> ('20200613111111');Query OK, 3 rows affected, 3 warnings (0.01 sec)Records: 3 Duplicates: 0 Warnings: 3
mysql> insert into test4 values -> (20200613080808);Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select *from test6;Empty set (0.00 sec)
mysql> select *from test4;+----------+| id |+----------+| 15:11:23 || 20:13:00 || 59:11:00 || 77:00:00 || 00:00:33 || 11:11:11 || 11:11:11 || 11:11:11 || 08:08:08 |+----------+9 rows in set (0.00 sec)
mysql> select *from test4;+----------+| id |+----------+| 15:11:23 || 20:13:00 || 59:11:00 || 77:00:00 || 00:00:33 || 11:11:11 || 11:11:11 || 11:11:11 || 08:08:08 |+----------+9 rows in set (0.00 sec)
mysql> insert into test6 values('2020-06-13 11:11:11'),(20200613111111),('20200613111111');Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from test6;+---------------------+| id |+---------------------+| 2020-06-13 11:11:11 || 2020-06-13 11:11:11 || 2020-06-13 11:11:11 |+---------------------+3 rows in set (0.00 sec)
mysql> select @@sql_mode;+-----------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+-----------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> create table vctest1 (vc varchar(6),ch char(6));Query OK, 0 rows affected (0.01 sec)
mysql> insert into vctest1 values("abc","abc");Query OK, 1 row affected (0.00 sec)
mysql> insert into vctest1 values("ab c ","abc ");Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select length(vc),length(ch) from vctest1;+------------+------------+| length(vc) | length(ch) |+------------+------------+| 3 | 3 || 6 | 3 |+------------+------------+2 rows in set (0.00 sec)
mysql> select 3+4,5-2,5*3,8/3,8%3;+-----+-----+-----+--------+------+| 3+4 | 5-2 | 5*3 | 8/3 | 8%3 |+-----+-----+-----+--------+------+| 7 | 3 | 15 | 2.6667 | 2 |+-----+-----+-----+--------+------+1 row in set (0.00 sec)
mysql> select 1=1,1=0,null=null,null=null;+-----+-----+-----------+-----------+| 1=1 | 1=0 | null=null | null=null |+-----+-----+-----------+-----------+| 1 | 0 | NULL | NULL |+-----+-----+-----------+-----------+1 row in set (0.00 sec)
mysql> select 1!=1,1!=0;+------+------+| 1!=1 | 1!=0 |+------+------+| 0 | 1 |+------+------+1 row in set (0.00 sec)
mysql> select null<=>null,null<=>null,null<=>null,1<=>2,1<=>1;+-------------+-------------+-------------+-------+-------+| null<=>null | null<=>null | null<=>null | 1<=>2 | 1<=>1 |+-------------+-------------+-------------+-------+-------+| 1 | 1 | 1 | 0 | 1 |+-------------+-------------+-------------+-------+-------+1 row in set (0.00 sec)
mysql> select 'a'>'b';+---------+| 'a'>'b' |+---------+| 0 |+---------+1 row in set (0.00 sec)
mysql> select 20 between 10 and 30,9 between 8 and 9;+----------------------+-------------------+| 20 between 10 and 30 | 9 between 8 and 9 |+----------------------+-------------------+| 1 | 1 |+----------------------+-------------------+1 row in set (0.00 sec)
mysql> select 1 is null, 2 is not null,null is null, null is not null;+-----------+---------------+--------------+------------------+| 1 is null | 2 is not null | null is null | null is not null |+-----------+---------------+--------------+------------------+| 0 | 1 | 1 | 0 |+-----------+---------------+--------------+------------------+1 row in set (0.00 sec)
mysql> select 'aaa' in ('aaa','bbb','ccc');+------------------------------+| 'aaa' in ('aaa','bbb','ccc') |+------------------------------+| 1 |+------------------------------+1 row in set (0.00 sec)
mysql> select 1234 like '123%';+------------------+| 1234 like '123%' |+------------------+| 1 |+------------------+1 row in set (0.00 sec)
mysql> select 1 like '123%';+---------------+| 1 like '123%' |+---------------+| 0 |+---------------+1 row in set (0.00 sec)
mysql> select 'abcd' regexp 'ab';+--------------------+| 'abcd' regexp 'ab' |+--------------------+| 1 |+--------------------+1 row in set (0.01 sec)
mysql> select 'abcd' regexp 'ddd';+---------------------+| 'abcd' regexp 'ddd' |+---------------------+| 0 |+---------------------+1 row in set (0.00 sec)
mysql> select not 0,!1,not null,!null;+-------+----+----------+-------+| not 0 | !1 | not null | !null |+-------+----+----------+-------+| 1 | 0 | NULL | NULL |+-------+----+----------+-------+1 row in set, 2 warnings (0.00 sec)
mysql> select not 0,!1,not null,!null;+-------+----+----------+-------+| not 0 | !1 | not null | !null |+-------+----+----------+-------+| 1 | 0 | NULL | NULL |+-------+----+----------+-------+1 row in set, 2 warnings (0.00 sec)
mysql> select 0 AND 1,1&&2,1&&3,NULL&&NULL;+---------+------+------+------------+| 0 AND 1 | 1&&2 | 1&&3 | NULL&&NULL |+---------+------+------+------------+| 0 | 1 | 1 | NULL |+---------+------+------+------------+1 row in set, 3 warnings (0.00 sec)
mysql> select (1||0),(0||0),(1||1),(1||2),(null||null),(null||1);+--------+--------+--------+--------+--------------+-----------+| (1||0) | (0||0) | (1||1) | (1||2) | (null||null) | (null||1) |+--------+--------+--------+--------+--------------+-----------+| 1 | 0 | 1 | 1 | NULL | 1 |+--------+--------+--------+--------+--------------+-----------+1 row in set, 6 warnings (0.00 sec)
mysql> select 1 xor 0,0 xor 0,null xor 1,null xor 0;+---------+---------+------------+------------+| 1 xor 0 | 0 xor 0 | null xor 1 | null xor 0 |+---------+---------+------------+------------+| 1 | 0 | NULL | NULL |+---------+---------+------------+------------+1 row in set (0.00 sec)
mysql> select 2&5,3&6;+-----+-----+| 2&5 | 3&6 |+-----+-----+| 0 | 2 |+-----+-----+1 row in set (0.00 sec)
mysql> select 3|6;+-----+| 3|6 |+-----+| 7 |+-----+1 row in set (0.00 sec)
mysql> select 4^5;+-----+| 4^5 |+-----+| 1 |+-----+1 row in set (0.00 sec)
mysql> select ~1,~18446744073709551614;+----------------------+-----------------------+| ~1 | ~18446744073709551614 |+----------------------+-----------------------+| 18446744073709551614 | 1 |+----------------------+-----------------------+1 row in set (0.00 sec)
mysql> select bin(~1);+------------------------------------------------------------------+| bin(~1) |+------------------------------------------------------------------+| 1111111111111111111111111111111111111111111111111111111111111110 |+------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> select 50>>3;+-------+| 50>>3 |+-------+| 6 |+-------+1 row in set (0.00 sec)
mysql> select 20<<2;+-------+| 20<<2 |+-------+| 80 |+-------+1 row in set (0.00 sec)
mysql> select upper('wd001'),lower('wd002');+----------------+----------------+| upper('wd001') | lower('wd002') |+----------------+----------------+| WD001 | wd002 |+----------------+----------------+1 row in set (0.00 sec)
mysql> select concat('w','d');+-----------------+| concat('w','d') |+-----------------+| wd |+-----------------+1 row in set (0.00 sec)
mysql> select concat('wd',null);+-------------------+| concat('wd',null) |+-------------------+| NULL |+-------------------+1 row in set (0.00 sec)
mysql> select left('wd12345',5),right('wd12345',5);+-------------------+--------------------+| left('wd12345',5) | right('wd12345',5) |+-------------------+--------------------+| wd123 | 12345 |+-------------------+--------------------+1 row in set (0.00 sec)
mysql> select insert ('wd12345',1,5,'jianshe');+----------------------------------+| insert ('wd12345',1,5,'jianshe') |+----------------------------------+| jianshe45 |+----------------------------------+1 row in set (0.00 sec)
mysql> select ltrim(' wd'),rtrim('wd ');+------------------+------------------+| ltrim(' wd') | rtrim('wd ') |+------------------+------------------+| wd | wd |+------------------+------------------+1 row in set (0.00 sec)
mysql> select repeat('javajianshe ',5);+------------------------------------------------------------------------+| repeat('javajianshe ',5) |+------------------------------------------------------------------------+| javajianshe javajianshe javajianshe javajianshe javajianshe |+------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> select repeat('wd最帅 ',20);+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| repeat('wd最帅 ',20) |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 wd最帅 |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
mysql> select trim(' wd ');+------------------+| trim(' wd ') |+------------------+| wd |+------------------+1 row in set (0.00 sec)
mysql> select substring('javajianshe',1,4);+------------------------------+| substring('javajianshe',1,4) |+------------------------------+| java |+------------------------------+1 row in set (0.00 sec)
mysql> select lpad('wd',20,'jianshe'),rpad('wd',20,'jianshe');+-------------------------+-------------------------+| lpad('wd',20,'jianshe') | rpad('wd',20,'jianshe') |+-------------------------+-------------------------+| jianshejianshejianwd | wdjianshejianshejian |+-------------------------+-------------------------+1 row in set (0.01 sec)
mysql> select strcmp('wd','javajianshe'),strcmp('javajianshe','wd');+----------------------------+----------------------------+| strcmp('wd','javajianshe') | strcmp('javajianshe','wd') |+----------------------------+----------------------------+| 1 | -1 |+----------------------------+----------------------------+1 row in set (0.00 sec)
mysql> select replace('wd','wd','javajianshe');+----------------------------------+| replace('wd','wd','javajianshe') |+----------------------------------+| javajianshe |+----------------------------------+1 row in set (0.00 sec)
mysql> select abs(-0.8),abs(1.0);+-----------+----------+| abs(-0.8) | abs(1.0) |+-----------+----------+| 0.8 | 1.0 |+-----------+----------+1 row in set (0.00 sec)
mysql> select ceil(-0.8),ceil(0.8);+------------+-----------+| ceil(-0.8) | ceil(0.8) |+------------+-----------+| 0 | 1 |+------------+-----------+1 row in set (0.00 sec)
mysql> select mod(7,2),mod(14,5);+----------+-----------+| mod(7,2) | mod(14,5) |+----------+-----------+| 1 | 4 |+----------+-----------+1 row in set (0.00 sec)
mysql> select round(3.1415926,3);+--------------------+| round(3.1415926,3) |+--------------------+| 3.142 |+--------------------+1 row in set (0.00 sec)
mysql> select floor(-0.8),floor(0.8);+-------------+------------+| floor(-0.8) | floor(0.8) |+-------------+------------+| -1 | 0 |+-------------+------------+1 row in set (0.00 sec)
mysql> select truncate(3.145,2);+-------------------+| truncate(3.145,2) |+-------------------+| 3.14 |+-------------------+1 row in set (0.00 sec)
mysql> select rand();+-------------------+| rand() |+-------------------+| 0.611663494828775 |+-------------------+1 row in set (0.00 sec)
mysql> select now();+---------------------+| now() |+---------------------+| 2020-10-14 12:30:38 |+---------------------+1 row in set (0.00 sec)
mysql> select week(now()),year(now());+-------------+-------------+| week(now()) | year(now()) |+-------------+-------------+| 41 | 2020 |+-------------+-------------+1 row in set (0.00 sec)
mysql> select hour(now()),minute(now());+-------------+---------------+| hour(now()) | minute(now()) |+-------------+---------------+| 12 | 31 |+-------------+---------------+1 row in set (0.00 sec)
mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| October |+------------------+1 row in set (0.00 sec)
mysql> select curdate();+------------+| curdate() |+------------+| 2020-10-14 |+------------+1 row in set (0.00 sec)
mysql> select unix_timestamp();+------------------+| unix_timestamp() |+------------------+| 1602649931 |+------------------+1 row in set (0.00 sec)
mysql> select from_unixtime(1592548441);+---------------------------+| from_unixtime(1592548441) |+---------------------------+| 2020-06-19 14:34:01 |+---------------------------+1 row in set (0.00 sec)
mysql> select date_format(now(),'%M,%D,%Y');+-------------------------------+| date_format(now(),'%M,%D,%Y') |+-------------------------------+| October,14th,2020 |+-------------------------------+1 row in set (0.00 sec)
mysql> select datediff('2021-01-01',now());+------------------------------+| datediff('2021-01-01',now()) |+------------------------------+| 79 |+------------------------------+1 row in set (0.00 sec)
mysql> select version();+-----------+| version() |+-----------+| 8.0.21 |+-----------+1 row in set (0.00 sec)
mysql> select database();+------------+| database() |+------------+| wddb |+------------+1 row in set (0.00 sec)
mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)
mysql> select INET_ATON('192.168.1.1');+--------------------------+| INET_ATON('192.168.1.1') |+--------------------------+| 3232235777 |+--------------------------+1 row in set (0.00 sec)
mysql> select INET_NTOA(3232235777);+-----------------------+| INET_NTOA(3232235777) |+-----------------------+| 192.168.1.1 |+-----------------------+1 row in set (0.00 sec)
mysql> ```