Mysql show command usage details, mysqlshow

Mysql show command usage details, mysqlshow

This document describes how to use the show command in a mysql database.

A. show tables or show tables from database_name; -- displays the names of all tables in the current database.

B. show databases; -- display the names of all databases in mysql.

C. show columns from table_name from database_name; or show columns from database_name.table_name; -- display the column name in the table.

D. show grants for user_name; -- displays the permissions of a user. The result is similar to the grant command.

E. show index from table_name; -- displays the table index.

F. show status; -- displays information about specific system resources, such as the number of running threads.

G. show variables; -- display the name and value of the system variable.

H. show processlist; -- display all processes running in the system, that is, the query being executed.

I. show table status; -- displays information about each table in the currently used or specified database. The information includes the table type and the latest update time of the table.

J. show privileges; -- display different permissions supported by the server.

K. show create database database_name; -- displays whether the create database statement can create a specified database.

L. show create table table_name; -- displays whether the create database statement can create a specified database.

M. show engies; -- displays available storage engines and default engines after installation.

N. show innodb status; -- displays the innoDB Storage engine status.

O. show logs; -- displays the logs of the BDB storage engine.

P. show warnings; -- displays errors, warnings, and notifications generated by the last executed statement.

Q. show errors; -- only displays errors generated by the last statement.

R. show [storage] engines; -- display available storage engines and default engines after installation.

Mysql basic statements

According to their online data, they are all written in the same way. At the same time, many of them are not well written. The following is my summary of mysql usage details, it is also some of my records in the learning process. I hope it will be helpful to you. I have not summarized the stored procedure and other related operations. I will give it to you next time, haha ~~~~~

MySql Study Notes

MySql Overview: MySql is an associated database management system. The so-called associated database stores data in different tables, rather than placing all the data in a large warehouse. This increases the speed and flexibility. MySql is an open source software.

Note: The maximum TimeStamp range supported by MySql is that on 32-bit machines, the supported value range is that the year should not exceed 2030. If the value is on a 64-bit machine, the year can reach 2106, but for date and datetime types, there is no relationship between them. They can all be expressed as 9999-12-31, so pay attention to this. Also, when installing MySql, we generally choose Typical (Typical installation). Of course, if there are other purposes, we 'd better choose Complete (full installation ); during the installation process, you are usually asked to select the Server type. There are three Server types, Developer and Server Machine), Dedicated MySql Server Machine (Dedicated MYSQL Server), select which type of Server will only affect the Configuration Wizard on memory, or other aspects will have no impact; therefore, if we are a developer, you can select the development machine. Then, you can select the database usage dialog box by default;

Connect to and disconnect the server:
Connection: enter a command set similar to the following at a windows Command Prompt: mysql-h host-u user-p
For example, when I use mysql-h localhost-u root-p
Then you will be prompted to enter the user password. At this time, if you have a password, enter the password and press Enter. If there is no password, press enter to access the database client; to connect to mysql on the remote host, run the following command: mysql-h 159.0.45.1-u root-p 123

Disconnect the server: after entering the client, you can directly enter quit and press Enter;

The following describes database commands.
You can enter the following command to perform related operations on the database table or database, which is omitted here and then directly explained in text;

Select version (), current_date; // obtain the current mysql version and date from the server.
Select user (); // obtain all users of the current database
Use databasename; enter the specified database, and then you can operate the tables in the database.
Show databases; // query all databases in the current database and display them;
Create batabase databasename; create a database, for example, Create database manager;
Show tables; // view all tables in the current database;
Create table tablename (colums); create a table and specify related columns for the table, for example, Create table pet (name varchar (20), owner varchar (20 ), species varchar (20), sex char (1), birth date, death date );

Describe tablename; display all the information in the table in detail, for example, descr ...... remaining full text>

Basic MySQL commands

Start: net start mySql;
Enter: mysql-u root-p/mysql-h localhost-u root-p databaseName;
List databases: show databases;
Select Database: use databaseName;
List tables: show tables;
Show columns from tableName;
Create a database: source fileName.txt;
Matching character: wildcard _ can be used to represent any character, and % represents any string;
Add a field: alter table tabelName add column fieldName dateType;
Add multiple fields: alter table tabelName add column fieldName1 dateType, add columns fieldName2 dateType;
Multi-line command input: note that words cannot be broken; when data is inserted or changed, the field strings cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data;
Add an Administrator Account: grant all on *. * to user @ localhost identified by "password ";
After each statement is entered, enter the plus sign ';' At the end, or add '\ G;
Query time: select now ();
Query the current user: select user ();
Query the database version: select version ();
Query the currently used database: select database ();

1. Delete the students data table in the student_course database:
Rm-f student_course/students .*

2. Back up the database: (back up the database test)
Mysqldump-u root-p test> c: \ test.txt
Backup table: (back up the mytable table under the test database)
Mysqldump-u root-p test mytable> c: \ test.txt
Import the backup data to the database: (import back to the test database)
Mysql-u root-p test

3. Create a temporary table: (create a temporary table zengchao)
Create temporary table zengchao (name varchar (10 ));

4. To create a table, first determine whether the table exists.
Create table if not exists students (......);

5. Copy the table structure from an existing table
Create table table2 select * from table1 where 1 <> 1;

6. Copy a table
Create table table2 select * from table1;

7. Rename the table
Alter table table1 rename as table2;

8. Modify the column type
Alter table table1 modify id int unsigned; // modify the column id type to int unsigned
Alter table table1 change id sid int unsigned; // modify the name of the column id ...... the remaining full text>