MySQL Common operation statement

MySQL Common operation statement

1.mysql-u root-p 2.mysql-h localhost-u root-p database_name

2. List the databases:

1.show databases;

3. Select the database:

1.use Databases_name;

4. List the data tables:

1.show tables;

5. Display the properties of the table column:

1.show columns from table_name; 2.describe table_name;

6. Export the entire database:

1.mysqldump-u user_name-p database_name >/tmp/file_name

Example: Mysqldump-u root-p test_db > D:/test_db.sql

7. Export a table:

1.MYSQLDUMP-U USER_NAME-P database_name table_name >/tmp/file_name

Example: Mysqldump-u root-p test_db table1 > D:/table1.sql

8. Export a database structure:

1.mysqldump-u User_name-p-D--add-drop-table database_name > file_name

Example: Mysqldump-u root-p-D--add-drop-table test_db > Test_db.sql

9. Import the database:

1.source file_name; 2. or 3.mysql-u user_name-p database_name < file_name

For example:

Source/tmp/bbs.sql;

SOURCE D:/bbs.sql;

Mysql-u root-p BBS < "D:/bbs.sql"

Mysql-u root-p BBS < "/tmp/bbs.sql"

10. Import the text file into the datasheet (Excel is the same)

1.load data infile "tables.txt" into table table_name;

For example:

Load data infile "/tmp/bbs.txt" into table BBS;

Load data infile "/tmp/bbs.xls" into table BBS;

Load data infile "d:/bbs.txt" into table BBS;

Load data infile "D:/bbs.xls" into table BBS;

11. Export the data table as a text file (Excel is the same)

1.select * into OutFile "path_file_name" from table_name;

For example:

SELECT * into outfile '/tmp/bbs.txt ' from BBS;

SELECT * into outfile '/tmp/bbs.xls ' from BBS where id=1;

SELECT * into outfile ' d:/bbs.txt ' from BBS;

SELECT * into outfile ' D:/bbs.xls ' from BBS where id=1;

12. When creating a database, first determine whether the database exists:

1.create database if not exists database_name;

Example: Create database if not EXISTS BBS

13. Create a database:

1.create database database_name;

For example: Create DATABASE BBS;

14. Delete the database:

1.drop database database_name;

For example: Drop database BBS;

15. Create a data table:

1.mysql> CREATE TABLE <table_name> (<column 1 name> <col. 1 type> <col. 1 details>,<column 2 name> <col. 2 type> <col. 2 Details>,...);

Example: CREATE TABLE (ID int not NULL auto_increment primary key,name char (+) NOT null default ' Jack ', date_year date not NULL) ;

16. Delete data from the data table:

1.delete from TABLE_NAME;

For example:

Delete from BBS;

Delete from BBS where id=2;

17. Delete the data table from the database:

1.drop table table_name;

For example:

drop table test_db;

Rm-f database_name/table_name.* (under Linux)

For example:

RM-RF bbs/accp.*

18. Add data to the database:

1.insert into table_name set column_name1=value1,column_name2=value2;

For example: INSERT INTO BBS set name= "Jack", date_year= "1993-10-01";

1.insert into table_name values (Column1,column2,...);

Example: INSERT INTO BBS ("2", "Jack", "1993-10-02")

1.insert into table_name (column_name1,column_name2,...) VALUES (value1,value2);

For example: INSERT INTO BBS (name,data_year) VALUES ("Jack", "1993-10-01");

19. Query the data in the data table:

1.select * FROM table_name;

For example: SELECT * from BBS where id=1;

20. Modify the data in the data table:

1.update table_name set col_name=new_value where id=1;

For example: Update BBS set name= "Tom" where Name= "Jack";

21. Add a field:

1.alter table table_name Add column FIELD_NAME datatype NOT NULL default "1";

For example: ALTER TABLE BBS add column Tel char (+) not null;

22. Add multiple fields: (column can be omitted and not written)

1.alter table table_name Add column filed_name1 datatype,add column filed_name2 datatype;

For example: ALTER TABLE BBS add column Tel char (+) not null,add column address text;

23. Delete a field:

1.alter table table_name Drop field_name;

For example: ALTER TABLE BBS drop TEL;

24. Modify the data type of the field:

1.alter table table_name Modify ID int unsigned;//Modified column ID is of type int unsigned 2.alter table table_name change ID SID int Unsi gned;//the name of the modified column ID is SID, and the attribute is modified to int unsigned

25. Modify the default value for a field:

1.alter Table table_name Modify COLUMN_NAME datatype NOT null default "";

For example: ALTER TABLE test_db Modify name char (+) default NOT NULL "Yourname";

26. Rename the table:

1.alter table table_name Rename as new_table_name;

For example: ALTER TABLE BBS rename as bbs_table;

1.rename table Old_table_name to New_table_name;

For example: Rename table test_db to ACCP;

27. Copy the structure of the table from an existing table:

1.create table table2 SELECT * FROM table1 where 1<>1;

Example: CREATE TABLE test_db select * from ACCP where 1<>1;

28. Query Time:

1.select now ();

29. Query the current User:

1.select user ();

30. Query the database version:

1.select version ();

31. Create an index:

1.alter Table table1 Add index ind_id (ID);   2.create index ind_id on table1 (ID); 3.create Unique index ind_id on table1 (ID);//establishing a unique index

32. Delete the index:

1.drop index idx_id on table1; 2.alter table table1 Drop index ind_id;

33. Union character or multiple columns (connect ID to ":" and column name and "=")

1.select concat (ID, ': ', name, ' = ') from table;

34.limit (10 to 20 selected)

1.select * FROM BBS ORDER by ID limit 9, 10;

(List the first to several records from the query results)

35. Add an Administrator account:

1.grant all on * * to [e-mail protected] identified by "password";

36. Create a table to determine if the table exists first

1.create table if not exists students (...);

37. Copy the table:

1.create table table2 SELECT * FROM table1;

Example: CREATE TABLE test_db select * from ACCP;

38. Grant user access to MySQL remotely

1.grant all privileges on * * to ' root ' @ '% ' identified by ' password ' with GRANT option;

or modify the Host field in the user table in the MySQL database

1.use MySQL;   2.select user,host from user; 3.update user set host= "%" where user= "user_name";

39. View the current status

1.show status;

40. View the currently connected user

1.show processlist;

(in the case of root, look at all threads and get the same number of user connections as show status; The threads_connected value is the same)

MySQL Common operation statement