MySql statement Daquan: Create, authorize, query, modify, and so on, mysql statement

MySql statement Daquan: Create, authorize, query, modify, and so on, mysql statement
Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka
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.

Example:

Create user 'lin' @ 'localhost' identified by '201312 ';
Create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'pig' @ '%' identified by '000000 ';
Create user 'pig' @ '%' identified '';
Create user 'pig' @ '% ';

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-operation permissions of a user, such as SELECT, INSERT, and UPDATE (for a detailed list, see the end of this Article ). use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *.

Example:

Grant select, insert on school. * TO 'lin' @ '% ';
Grant all on *. * TO 'pig' @ '% ';

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;

4. Set and change the User Password

Command: set password for 'username' @ 'host' = PASSWORD ('newpassword'); if the current login user uses set password = PASSWORD ("newpassword ");

Example: set password for 'lin' @ '%' = PASSWORD ("123456 ");

5. Revoke User Permissions

Command: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';

Note: privilege, databasename, tablename-same as the authorization section.

Example: revoke select on *. * FROM 'pig' @ '% ';

Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'pig' @ '%', use revoke select on *. * FROM 'pig' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on * is used for authorization *. * TO 'pig' @ '%'; then revoke select on test. user FROM 'pig' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.

FOR more information, run the show grants for 'pig' @ '%' command.

6. delete a user

Command: drop user 'username' @ 'host ';

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;
Use Database (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 table: create table Name (field setting list );

CREATE TABLE    USER    (        name VARCHAR(30) NOT NULL,        id INT DEFAULT '0' NOT NULL,        stu_id INT,        phone VARCHAR(20),        address VARCHAR(30) NOT NULL,        age INT(4) NOT NULL,        PRIMARY KEY (name),        CONSTRAINT stu_id UNIQUE (stu_id)    )    ENGINE=InnoDB DEFAULT CHARSET=utf8;

Delete table: drop table name;

Syntax: drop database [if exists] db_name

Function: DROP the DATABASE to delete all tables and databases in the DATABASE. Be careful when using this command!

Drop database returns the number of files deleted from the DATABASE directory. Generally, this is three times the number of tables, because each table corresponds to a ". MYD" file, a ". MYI" file, and a ". frm" file.

In MySQL 3.22 or later versions, you can use the keyword if exists to prevent an error from occurring IF the database does not exist.

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 dataTruncate table name;
5. Back up the databaseFor example, backup library database into Mysql bin directory E: \ mysql-5.6.23-win32 \ bin using "mysqldump -U user name-p Database Name> Backup name "Export Database to fileC: \ Program Files \ MySQL Server 5.5 \ bin> mysqldump-u root-p test> test. sqlEnter password.

E: \ mysql-5.6.23-win32 \ bin directory

6. Restore the database to test as an example. First create database test1 and then enter the source Path under mysql>.
Note the path of test. SQL!

 

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 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

3. Modify the field type of a table and specify it as null or non-empty.

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

> Alter table name modify Field Name field type [whether to allow non-null];

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 ′;

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.

Field.

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
 
6. 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 $ _ 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
 
12. query data in a specified time period
SELECT the field to be searched FROM table name WHERE field name BETWEEN initial value AND termination value
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
 
13. query statistics by month
SELECT * FROM tb_stu WHERE month (date) = '$ _ POST [date] 'order BY date;
Note: The following functions are provided in the SQL language. You can use these functions to conveniently query data by year, month, or day.
Year (data): returns the value corresponding to the year in the data expression.
Month (data): returns the value corresponding to the month in the data expression.
Day (data): returns the value corresponding to the date in the data expression.
 
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.
 
23. Multi-Table grouping statistics
SELECT. name, AVG (. price), B. name, AVG (B. price) FROM tb_demo058 AS a, tb_demo058_1 AS B WHERE. id = B. id group by B. type;