MySQL statement Daquan: Create, authorize, query, modify, etc.

Lin Bingwen Evankaka Original works. Reprint please indicate the source Http://blog.csdn.net/evankaka one, user creation, permission, delete

1. Connect MySQL operation

Connection: Mysql-h host address-u user name-P user Password (note: U and root can be used without spaces, others are the same)
Disconnect: Exit (Enter)

Open cmd, enter

Mysql-h 127.0.0.1-u Root-p then enter the password. You can connect to the local MySQL database.

2. Create User:

Command: CREATE USER ' username ' @ ' host ' identified by ' password ';

Description

Username-the user name that you will create,

Host-Specifies the host on which the user can log on, and if localhost is available to the local user, you can use the wildcard% if you want the user to be able to log on from any remote host.

Password-The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server.

Example:

CREATE USER ' lin ' @ ' localhost ' identified by ' 123456 ';
CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ' 123456 ';
CREATE USER ' pig ' @ '% ' identified by ';
CREATE USER ' pig ' @ '% ';

To log in, first put 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 '

Description

Privileges-user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the last side of the article for a detailed list). Use all if you want to grant the permission. DatabaseName-database name, tablename-table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *.

Example:

GRANT SELECT, INSERT on school.* to ' lin ' @ '% ';
GRANT all on * * to ' pig ' at '% ';

Note: A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:
GRANT privileges on Databasename.tablename to ' username ' @ ' host ' with GRANT OPTION;

4. Setting and changing user passwords

Command: Set PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword '), if current user is logged in with set PASSWORD = PASSWORD ("NewPassword");

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

5. Revoke user Privileges

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-With the authorization section.

Example: REVOKE SELECT on * * from ' pig ' @ '% ';

Note: If you are giving the user ' pig ' @ '% ' authorization (or similar): Grant SELECT on Test.user to ' pig ' @ '% ', then use revoke select On * * from ' pig ' @ '% '; A select operation for the user table in the test database. Conversely, if the authorization is to use the grant SELECT on * * to ' pig ' @ '% ', then the revoke select on Test.user ' pig ' @ '% '; Revoke the user's SELECT permission to the Users table in the test database.

Specific information can be used with the command show GRANTS for ' pig ' @ '% '; View.

6. Delete users

Command: DROP USER ' username ' @ ' host ';

Second, database and table display, create, delete

1, database display, create, delete

Display database: show databases;
Create Library: Name of creation database;
Delete Library: drop database name;
Using libraries (check library): Use library name;

2. Table display, create, delete

Display Data sheet: show tables; (To select a database with the use database name first)

Show Table structure: Describe table name, or DESC table name

Creating Tables: Create table table names (field settings list);

[SQL]View PlainCopy

2. CREATE TABLE
4. USER
6. (
8. name VARCHAR (+) is not NULL,
10. ID INT DEFAULT ' 0 ' not NULL,
12. stu_id INT,
14. Phone VARCHAR (),
16. Address VARCHAR (+) is not NULL,
18. Age INT (4) is not NULL,
20. PRIMARY KEY (name),
22. CONSTRAINT stu_id UNIQUE (stu_id)
24. )
26. Engine=innodb DEFAULT Charset=utf8;

Delete tables: drop table name;

Syntax: DROP DATABASE [IF EXISTS] Db_name

Function: Drop database deletes all tables and databases in the databases. Be careful to use this command!

Drop database Returns the number of files that were deleted from the databases directory. Typically, this is 3 times times the number of tables, because each table corresponds to a ". MYD "file, one". 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.

Third, table replication and backup restore

Suppose you now have table books: 1. Duplicate table Structure1.1 Complete table structure with information such as primary key 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. Pouring data from old tables into a new tableINSERT into new table SELECT * from old table; Note: The new table must already exist 3. Enter the DDL statement that created the tableShow create table table name; 4. Clear the table dataTRUNCATE TABLE name; 5. Backing Up the databaseLike backing up the library database into the MySQL bin directory E:\mysql-5.6.23-win32\bin using "mysqldump-u Username-P database name > Backup name" To export the database to a file C:\Program files\ Mysql\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 Restore test database as an example first create database Test1 and then enter the source path under Mysql>.
pay attention to the path where the Test.sql is located!

Iv. data manipulation in database tables

1. Clear the MySQL table data

Delete from table name;

TRUNCATE TABLE name;

The DELETE statement without the Where parameter removes everything from the MySQL table, and you can also empty all the contents of the MySQL table using TRUNCATE table.

The efficiency of truncate is faster than delete, but the MySQL log is not recorded after Truncate deleted, and data cannot be recovered.

The effect of delete is a bit like removing all records from a MySQL table to the end of a delete,

The truncate is equivalent to preserving the structure of the MySQL table and recreating the table, all of which are equivalent to the new table.

2. Delete some data from the table

Delete from command format: Delete from table name where expression

For example, delete the record numbered 1 in table MyClass:

The code is as follows:

Mysql> Delete from MyClass where id=1;

V. Modifying the table's columns and table names

1. Renaming the column
>alter Table name change field Name field name

For example:
ALTER TABLE pet change weight wei;
2. Renaming the table
>alter table table name rename tables name

For example:

ALTER TABLE Tbl_name Rename NEW_TBL

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

>alter Table name change field Name field Name field type [whether non-null allowed];

>alter Table name Modify field Name field type [allow non-null];

4. Modify the field name of a table and specify null or non-empty

>alter Table name change field original Name field new Name field type [whether non-null allowed];

For example:

Modify the field birth in table Expert_info to allow it to be empty

The code is as follows:

>alter table Expert_info Change birth birth varchar () null;

Vi. modifying data in a table

1. Add a field (one column)

ALTER TABLE table_name ADD COLUMN column_name type default value; Type refers to the field, and value refers to the default value of the field.

For example:

The code is as follows:

ALTER TABLE mybook add column Publish_house varchar (TEN) 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 refers to the original field name, Dest_col_name

Refers to the changed field name

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 column_name type;

For example:

The code is as follows:

ALTER TABLE userinfo change column username 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 (TEN) auto_increment PRIMARY KEY;

6. A backup of a table in the database, entered at the command line:

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. Exporting data

Select_statment into outfile "Dest_file";

For example:

The code is as follows:

Select Cooperatecode,createtime from publish Limit Intooutfile "/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 from the two tables into the other table. The following example shows the concatenation of the values of the COM1 fields in the COM2 and T2 tables in the T1 table into the TX table corresponding to the

field.

For example:

The code is as follows:

Insert INTO TX select T1.com1,concat (T1.COM2,T2.COM1) from T1,t2;

10, delete field

ALTER TABLE Form1 drop column name;

Seven, inquiry form

MySQL query for five seed sentences

where (conditional query), having (filter), GROUP by (grouping), order by (sort), limit (limits the number of results)

1, query numeric data:
SELECT * from Tb_name WHERE sum > 100;
Query predicate:>,=,<,<>,!=,!>,!<,=>,=<

2. Query string
SELECT * from tb_stu WHERE sname = ' Xiao 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 type data
SELECT * from tb_stu WHERE date = ' 2011-04-08 '
Note: Different databases have differences in date type data:
(1) Mysql:select * from tb_name WHERE birthday = ' 2011-04-08 '
(2) SQL Server:select * from tb_name WHERE birthday = ' 2011-04-08 '
(3) Access:select * from tb_name WHERE birthday = #2011 -04-08#

4, query the logical type of data
SELECT * from tb_name WHERE type = ' T '
SELECT * from tb_name WHERE type = ' F '
Logical operators: And OR not

6. Query non-empty data
SELECT * from Tb_name WHERE address <> "ORDER BY addtime Desc
Note:<> is equivalent to! = in PHP

6. Using variables to query numeric data
SELECT * FROM tb_name WHERE id = ' $_post[text] '
Note: When querying data with variables, the variables passed into SQL do not have to be enclosed in quotation marks, because when a string in PHP is connected to a numeric data, the program automatically transforms the numeric data into a string and then connects to the string to be concatenated

7. Query string data with variables
SELECT * from Tb_name WHERE name like '%$_post[name]% '
The exact match method "percent" means that it can appear in any position

8, query the first n records
SELECT * from Tb_name LIMIT 0, $N;
The limit statement is used in conjunction with other statements, such as an order by, and uses a variety of SQL statements to make the program very flexible

9, after the query n records
SELECT * from Tb_stu ORDER by ID ASC LIMIT $n

10. Query n records starting at the specified position
SELECT * from Tb_stu ORDER by ID ASC LIMIT $_post[begin], $n
Note: The ID of the data is starting from 0

11. Query the first n records in the statistic results
SELECT *, (Yw+sx+wy) as total from Tb_score ORDER by (yw+sx+wy) DESC LIMIT 0, $num

12. Querying data for a specified time period
SELECT the field to find from table name WHERE field name between initial value and terminating 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 available in the SQL language, which makes it easy to query by year, month, and day
Year (data): Returns the value corresponding to the A.D. in the data expression
Month (data): Returns the value of the month in the data expression
Day (data): Returns the numeric value of the date in the data expression

14. Query for records larger than the specified criteria
SELECT * from Tb_stu WHERE Age>$_post[age] ORDER by age;

15, the query results do not show duplicate records
SELECT DISTINCT field name from table name WHERE query condition
Note: The distinct in the SQL statement must be used in conjunction with the WHERE clause, otherwise the output information will not change and the field cannot be replaced with *

16, not and predicates to combine the conditions of the query
(1) Not Berween ... And ... Query on rows between start and end values can be changed to < start value and > End value
(2) is not null to query for non-null values
(3) Is null to query for null values
(4) In this style, depending on whether the keyword used is included in the list or excluded from the list, the search expression can be a constant or a column name, and the column name can be a set of constants, but more of a subquery

17. Display the number of duplicate records and records in the data table
SELECT Name,age,count (*), age from tb_stu WHERE age = ' + ' GROUP by date

18, the data in descending/ascending query
SELECT field name from Tb_stu WHERE condition order BY field desc Descending
SELECT field name from Tb_stu WHERE condition ORDER by field ASC Ascending
Note: When sorting a field without specifying a sort order, the default is ASC ascending

19, the data for multi-conditional query
SELECT field name from Tb_stu WHERE condition ORDER by field 1 ASC Field 2 DESC ...
Note: The query information is ordered in order to jointly limit the output of the record, in general, because it is not a single condition limit, so there are some differences in the output effect.

20, the statistical results are sorted
The function sum ([All] field name) or sum ([DISTINCT] field name) can be used to sum the fields, sum all records for all of the fields in the function, and sum the fields for all of the fields that are not repeating records for DISTINCT.
such as: SELECT name,sum (Price) as Sumprice from Tb_price GROUP by name

SELECT * from Tb_name ORDER by Mount Desc,price ASC

21. Single Row data grouping statistics
SELECT Id,name,sum (Price) as title,date from Tb_price GROUP by PID ORDER by title DESC
Note: When the grouping statement group by sort statement order by is present in the SQL statement, the grouping statement is written before the sort statement, otherwise an error occurs

22, multi-column data grouping statistics
Multi-column data grouping statistics are similar to single row data grouping statistics
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 usually followed by a series that is not an aggregate function, that is, a column that is not to be grouped

23, Multi-table grouping statistics
SELECT A.name,avg (A.price), B.name,avg (B.price) from tb_demo058 as a,tb_demo058_1 as B WHERE a.id=b.id GROUP by b.type;

MySQL statement Daquan: Create, authorize, query, modify, etc.