Common mysql statement operations

1. User creation, permission, and deletion

1. Connect to MySql

Connection: mysql-h host address-u user name-p user password (note: u and root do not need to add spaces, the same is true for others)

Disconnected: exit (press enter)

Open cmd and enter

Mysql-h 127.0.0.1-u root-p and enter the password. You can connect to the local MySql database.

2. Create a user:

Command: create user 'username' @ 'host' identified by 'password ';

Note:

Username-the username you will create,

Host-specifies the host on which the user can log on. If a local user can use localhost, if you want the user to log on from any remote host, you can use the wildcard %.

Password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.

When logging in, first exit the current exit, and then enter the following

Mysql-h 127.0.0.1-u linlin-p password

Mysql-h 127.0.0.1-u pig-p password

3. Authorization:

Command: GRANT privileges ON databasename. tablename TO 'username' @ 'host'

Note:

Privileges-user operation permissions, such as SELECT, INSERT, and UPDATE

ALL.; databasename-database name, tablename-table name. If you want to grant the user the corresponding operation permissions for ALL databases and tables, it can be represented *.*.

Note: The user authorized with the preceding command cannot authorize other users. To authorize the user, run the following command:

GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

2. Display, create, and delete databases and tables

1. Display, create, and delete databases

Display database: show databases;

Database creation: create database name;

Delete database: drop database name;

Usage Library (selected database ):

Use database name;

2. Display, create, and delete tables

Show tables: show tables; (you must first select a database with the use database name)

Display table structure: describe table name; or desc table name

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

Delete table: drop table name;

III. Table copy and backup recovery

Suppose there is a table named books:

1. Copy the table structure

1.1 Complete table structure containing primary key and other information

CREATE table new table name LIKE book;

1.2 only table structure, no primary key and other information

Create table new table name select * from books;

Or

Create table new table name as (select * from book );

Or

Create table new table name select * from books where1 = 2;

2. Add data from the old table to the new table.

Insert into new table SELECT * FROM old table;

Note: The new table must already exist.

3. Enter the DDL statement for table creation.

Show create table name;

4. Clear table data

Truncate table name;

IV. Database table data operations

1. Clear mysql table data

Delete from table name;

Truncate table name;

The delete statement without the where parameter can delete all content in the mysql table. You can also use the truncate table to clear all content in the mysql table.

In terms of efficiency, truncate is faster than delete, but after truncate is deleted, mysql logs are not recorded and data cannot be recovered.

The delete operation is a bit like deleting all records in the mysql table one by one until the deletion is complete,

Truncate is equivalent to retaining the structure of the mysql table and re-creating the table. All the statuses are equivalent to new tables.

2. Delete some data in the table

Delete from command format: delete from table name where expression

For example, to delete a record numbered 1 in MyClass:

The code is as follows:

Mysql> delete from MyClass where id = 1;
5. Modify the column and table name of a table

1. Rename a column

> Alter table name change field name

For example:

Alter table pet change weight wei;

2. Rename a table

> Alter table name rename table name

For example:

Alter table tbl_name rename new_tbl

4. Modify the field name of a table and specify it as null or not

> Alter table name change field original name field new name field type [whether to allow non-null];

For example:

Modify the birth field in table expert_info. It is allowed to be empty.

The code is as follows:

> Alter table expert_info change birth varchar (20) null;
6. Modify table data

1. Add a field (one column)

Alter table table_name add column column_name type default value; type indicates the type of the field, and value indicates the default value of the field.

For example:

The code is as follows:

Alter table mybook add column publish_house varchar (10) default ";
2. Change a field name (you can also change the type and default value)

Alter table table_name change sorce_col_name dest_col_name type defaultvalue; source_col_name indicates the original field name, dest_col_name

Indicates the name of the modified field.

For example:

The code is as follows:

Alter table Board_Info change IsMobile IsTelphone int (3) unsigned default1;
3. Change the default value of a field

Alter table table_name alter column_name set default value;

For example:

The code is as follows:

Alter table book alter flag set default '0 & prime ;;
4. Change the data type of a field

Alter table table_name change column column_name type;

For example:

The code is as follows:

Alter table userinfo change column username varchar (20)
5. Add a column to a table as the primary key.

Alter table table_name add column column_name type auto_increment PRIMARYKEY;

For example:

The code is as follows:

Alter table book add column id int (10) auto_increment primary key;
6. For backup of a table in the database, enter:

Mysqldump-u root-p database_name table_name> bak_file_name

For example:

The code is as follows:

Mysqldump-u root-p f_info user_info> user_info.dat
7. Export data

Select_statment into outfile "dest_file ";

For example:

The code is as follows:

Select cooperatecode, createtime from publish limit 10 bytes outfile "/home/mzc/temp/tempbad.txt ";
8. Import data

Load data infile "file_name" into table table_name;

For example:

The code is as follows:

Load data infile "/home/mzc/temp/tempbad.txt" into table pad;
9. Splice the data in the two tables and insert them into the other table. The following example shows how to splice the values of the com2 and com1 fields in table t1 and insert them into the fields in table tx.

For example:

The code is as follows:

Insert into tx select t1.com1, concat (t1.com2, t2.com1) from t1, t2;
10. Delete a field

Alter table form1 drop column name;

VII. Query a table

Five clauses queried by mysql

Where (conditional query), having (filter), group by (group), order by (SORT), limit (limit the number of results)

1. Query numeric data:

SELECT * FROM tb_name WHERE sum> 100;

Query predicate:>, =, <, <> ,! =,!> ,! <, =>, = <

2. Query strings

SELECT * FROM tb_stu WHERE sname = 'Liu'

SELECT * FROM tb_stu WHERE sname like 'Liu %'

SELECT * FROM tb_stu WHERE sname like '% programmer'

SELECT * FROM tb_stu WHERE sname like '% PHP %'

3. Query date data

SELECT * FROM tb_stu WHERE date = '2017-04-08'

Note: different databases have different types of date data ::

(1) MySQL: SELECT * from tb_name WHERE birthday = '2017-04-08'

(2) SQL Server: SELECT * from tb_name WHERE birthday = '2017-04-08'

(3) Access: SELECT * from tb_name WHERE birthday = #2011-04-08 #

4. Query logical data

SELECT * FROM tb_name WHERE type ='t'

SELECT * FROM tb_name WHERE type = 'F'

Logical Operator: and or not

5. Query non-empty data

SELECT * FROM tb_name WHERE address <> ''Order by addtime desc

Note: <> equivalent to PHP! =

6. Query numeric data using variables

SELECT * FROM tb_name WHERE id = '$ _ POST [text]'

Note: When using variables to query data, the input SQL variables do not need to be enclosed in quotation marks, because when the strings in PHP are connected to the numeric data, the program will automatically convert the numeric data into a string, connect to the string to be connected.

7. Query string data using variables

SELECT * FROM tb_name WHERE name LIKE '% $ _ POST [name] %'

The exact match "%" indicates that the method can appear anywhere.

8. Query the first n Records

SELECT * FROM tb_name LIMIT 0, $ N;

When the limit statement is used together with other statements, such as order by statements, SQL statements are constantly changing, making the program very flexible.

9. n records after Query

SELECT * FROM tb_stu order by id asc limit $ n

10. Query n records starting from the specified position

SELECT * FROM tb_stu order by id ASC LIMIT
P
O
S
T
[
B
E
G
I
N
]
,
POST [begin], n

Note: The data id starts from 0.

11. Query the first n records in the statistical results

SELECT *, (yw + sx + wy) AS total FROM tb_score order by (yw + sx + wy) desc limit 0, $ num

14. Query records larger than the specified conditions

SELECT * FROM tb_stu WHERE age >$ _ POST [age] order by age;

15. Duplicate records are not displayed in the query results

Select distinct field name FROM table name WHERE query condition

Note: the DISTINCT in the SQL statement must be used together with the WHERE clause. Otherwise, the output information will not change and the field cannot be replaced *.

16. Query the combination conditions of NOT and predicates

(1) not berween... AND... When querying data between the start value AND the end value, you can change the row query value to the <start value AND> end value.

(2) is not null queries non-NULL values.

(3) is null queries NULL values.

(4) not in this formula specifies the search expression based on whether the keyword is included IN the list or excluded from the list. The search expression can be a constant or column name, the column name can be a group of constants, but more often it is a subquery.

17. Display repeated records and records in the data table

SELECT name, age, count (*), age FROM tb_stu WHERE age = '19' group by date

18. Query data in descending or ascending order

SELECT field name FROM tb_stu WHERE condition order by field DESC descending ORDER

SELECT field name FROM tb_stu WHERE condition order by field ASC ascending

Note: If you do not specify the sorting method when sorting fields, the default value is ASC ascending.

19. Multi-condition query of data

SELECT field name FROM tb_stu WHERE condition order by field 1 ASC field 2 DESC...

Note: The multi-condition sorting of query information aims to limit the output of records. Generally, the output effect is different because it is not a single condition.

20. Sort the statistical results

The SUM function ([ALL] field name) or SUM ([DISTINCT] field name) can SUM the fields. If the function is ALL, ALL records of the field are summed, if DISTINCT is used, the sum of all fields of this field that do not record repeatedly is used.

For example, SELECT name, SUM (price) AS sumprice FROM tb_price group by name

SELECT * FROM tb_name order by mount DESC, price ASC

21. Grouping statistics for a single column

SELECT id, name, SUM (price) AS title, date FROM tb_price group by pid order by title DESC

Note: When the group statement group by sorting statement order by appears in the SQL statement at the same time, you must write the grouping statement before the sorting statement. Otherwise, an error occurs.

22. Grouping statistics of multiple columns

Grouping statistics for multiple columns is similar to grouping statistics for a single column

SELECT *, SUM (Field 1 * field 2) AS (New Field 1) FROM table name group by field order by new field 1 DESC

SELECT id, name, SUM (price * num) AS sumprice FROM tb_price group by pid order by sumprice DESC

Note: the group by statement is generally followed by a series of non-aggregate functions, that is, it is not the column to be grouped.