SQL-mysql common operation statements (Entry level), sqlmysql

SQL-mysql common operation statements (Entry level), sqlmysql

1. Enter the database:

Mysql-u root-p

Mysql-h localhost-u root-p database_name

2. List Databases

Show databases;

3. Select a database:

Use databases_name;

4. List data tables:

Show tables;

5. display table column attributes

ShowColumnsFrom table_name;

Describe table_name;

6. Export the entire database:

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:

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:

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:

Source file_name;

Or 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 a text file into a data table (excel is the same) 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 a data table as a text file (excel is the same)

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:

Create database if not exists database_name;

Example: create database if not exists bbs

13. Create a database:

Create database database_name;

Example: create database bbs;

14. delete a database:

Drop database database_name;

Example: drop database bbs;

15. Create a data table:

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 (16) not null default "jack", date_year date not null );

16. delete data from a data table:

Delete from table_name;

For example:

Delete from bbs;

Delete from bbs where id = 2;

17. delete a data table in the database:

Drop table table_name;

For example:

Drop table test_db;

Rm-f database_name/table_name. * (in linux)

For example:

Rm-rf bbs/accp .*

18. add data to the database:

Insert into table_name set column_name1 = value1, column_name2 = value2;

Example: insert into bbs set name = "jack", date_year = "1993-10-01 ";

Insert into table_name values (column1, column2 ,...);

Example: insert into bbs ("2", "jack", "1993-10-02 ")

Insert into table_name (column_name1, column_name2,...) values (value1, value2 );

Example: insert into bbs (name, data_year) values ("jack", "1993-10-01 ");

19. query data in a data table:

Select * from table_name;

Example: select * from bbs where id = 1;

20. modify data in the data table: 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: alter table table_name add column field_name datatype not null default "1 ";

For example, alter table bbs add column tel char (16) not null;

22. add multiple fields: (column can be omitted without writing)

Alter table table_name add column filed_name1 datatype, add column filed_name2 datatype;

For example: alter table bbs add column tel char (16) not null, add column address text;

23. delete a field: alter table table_name drop field_name;

Example: alter table bbs drop tel;

24. Modify the Data Type of a field:

Alter table table_name modify id int unsigned; // modify the column id type to int unsigned alter table table_name change id sid int unsigned; // modify the column id name to sid, modify the attribute to int unsigned.

25. Modify the default value of a field:

Alter table table_name modify column_name datatype not null default "";

Example: alter table test_db modify name char (16) default not null "yourname ";

26. Rename the table:

Alter table table_name rename as new_table_name;

Example: alter table bbs rename as bbs_table;

Rename table old_table_name to new_table_name;

Example: rename table test_db to accp;

27. Copy the table structure from an existing table:

Create table table2 select * from table1 where 1 <> 1;

Example: create table test_db select * from accp where 1 <> 1;

28. query time:

Select now ();

29. query the current user:

Select user ();

30. query the database version:

Select version ();

31. Create an 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

32. Delete the index:

Drop index idx_id on table1;

Alter table table1 drop index ind_id;

33. Combine characters or multiple columns (connect the id with ":" And column name and "=)

Select concat (id, ':', name, '=') from table;

34. limit (10 to 20 items are selected)

Select * from bbs order by id limit 9, 10;

(The number to several records are listed in the query results)

35. Add an Administrator Account:

Grant all on *. * to user @ localhost identified by "password ";

36. To create a table, first determine whether the table exists.

Create table if not exists students (......);

37. Copy a table:

Create table table2 select * from table1;

Example: create table test_db select * from accp;

38. Grant the user the permission to remotely access mysql

Grant all privileges on *. * to "root" @ "%" identified by "password" with grant option;

Or modify the host field in the user table of the mysql database.

Use mysql;

Select user, host from user;

Update user set host = "%" where user = "user_name ";

39. view the current status

Show status;

40. view the current connected user

Show processlist;