Detailed summary of mysql sql statements to create tables

mysql create table sql statement

Common SQL statements for creating MySQL tables:

Connection: mysql -h host address -u username -p user password (Note: u and root do not need to have spaces, and the same applies to the others)

Disconnect: exit (Enter)

Create authorization: grant select on database.* to username@login host identified by \"password\"

Change password: mysqladmin -u username -p old password password new password

Delete authorization: revoke select,insert,update,delete om *.* from test2@localhost;

Show databases: show databases;

Display data tables: show tables;

Display table structure: describe table name;

Create a library: create database library name;

Delete database: drop database library name;

Use library (select library): use library name;

Create a table: create table table name (field setting list);

Delete table: drop table table name;

Modify the table: alter table t1 rename t2

Query table: select * from table name;

Clear the table: delete from table name;

Backup table: mysqlbinmysqldump -h(ip) -uroot -p(password) databasename tablename > tablename.sql

Restore table: mysqlbinmysql -h(ip) -uroot -p(password) databasename tablename < tablename.sql (delete the original table before the operation)

Add a column: ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

Modify the column: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR(20);

Delete columns: ALTER TABLE t2 DROP COLUMN c;

Back up the database: mysql\bin\mysqldump -h(ip) -uroot -p(password) databasename > database.sql

Restore database: mysql\bin\mysql -h(ip) -uroot -p(password) databasename < database.sql

Copy the database: mysql\bin\mysqldump --all-databases > all-databases.sql

Repair the database: mysqlcheck -A -o -uroot -p54safer

Text data import: load data local infile \"file name\" into table table name;

Data import and export: mysql\bin\mysqlimport database tables.txt

The above is the detailed content of the SQL statement for creating a table in MySQL. The knowledge point is very simple. I hope that the content compiled by 123WORDPRESS.COM can help everyone.