MySQL command set

I. Common commands
List databases: Show databases;
Select Database: Use databasename;
List tables: show tables;
Create Database library name;
Create Table Name;
Display the data table structure: DESC table name;
Delete database drop database database name;
Drop table name;
Clear records in the Table: delete from table name;
Displays the records in the Table: Select * from table name;

2. Connect to MySQL.
Format: mysql-H host address-u user name-P User Password
1. Example 1: connect to MySQL on the local machine.
First, open the DOS window, type the mysql-uroot-p command, and press enter to prompt you to enter the password.
2. Example 2: connect to MySQL on the remote host. Assume that the IP address of the remote host is 192.168.1.5, the user name is root, and the password is root. Run the following command: mysql-h192.168.1.5-uroot-proot.
3. Exit MySQL command: exit (Press ENTER)

Iii. Other commands:
Query time: select now ();
Query the database version: Select version ();
Query the current user: Select User ();
Query the currently used database: select database ();
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;
Change Password: mysqladmin-u username-P old Password New Password

Reprinted below:

Add new users:
Format: grant select on database. * To username @ login host identified by "password"

Example 1: Add a user named "test1" with the password "ABC" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:
Grant select, insert, update, delete on *. * To test1 @ "%" identified by "ABC ";
However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants for your data. For the solution, see Example 2.

Example 2: Add a user named "Test2" with the password "ABC" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of Test2, he cannot access the database directly from the Internet, but can only access the database through the web pages on the MySQL host.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified by "ABC ";

Example 3. If you do not want Test2 to have a password, run another command to remove the password.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified "";

Example 4. Add an Administrator Account: grant all on *. * to user @ localhost identified by "password ";

other command sets:
copy the table structure from an existing table. Create Table Table2 select * From Table1 where 1 <> 1;
copy the table create table Table2 select * From Table1;
rename the table alter table Table1 Rename as Table2;
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 column ID name to Sid and the attribute to int unsigned
Create index:
ALTER TABLE Table1 add index ind_id (ID);
Create index ind_id on Table1 (ID);
create unique index ind_id on Table1 (ID ); // create a unique index
delete an index:
drop index idx_id on Table1;
alter table Table1 drop index ind_id;
union character or multiple columns (concatenate the column ID with ":" And column name and "="):
select Concat (ID ,':', name, '=') from students.
limit (10 to 20 records selected) :< br> select * from students order by ID limit 9, 10.
functions not supported by MySQL: transactions, views, foreign keys, and reference integrity, stored procedures, and triggers.