MySQL Common operations

MySQL Common operations

Authorized Superuser:

Grant all privileges on * * to ' tangnanbing ' @ ' percent ' identified by ' [email protected] ' with GRANT option;

To view the library:

show databases;

See which libraries show databases;

View the table of a library use DB; Show Tables \g;

View the table's fields desc TB;

View the Build Table statement show create TABLE TB;

Which user is currently Select users ();

Current Library Select database ();

Creating a library Create database db1;

CREATE TABLE t1 (id int, name char (+) adress varchar (30));

char (Ten) ' AAA '

varchar (TEN) ' AAA '

View database version select version ();

View MySQL status show status;

Modify MySQL parameters show variables like ' max_connect% '; Set global max_connect_errors = 1000;

View MySQL queue show processlist;

SELECT * from Information_schema.processlist where info is not null;

Sleep can be ignored, Qurey query only

Create a regular user and authorize grant all on *. Databases1.user1 identified by ' 123456 ';

Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';

Grant all on db1.* to ' user3 ' @ '% ' identified by ' 231222 '; insert into TB1 (id,name) VALUES (1, ' aming ');

Change Password UPDATE mysql.user SET password=password ("Newpwd") WHERE user= ' username ';

Query select COUNT (*) from Mysql.user; SELECT * from Mysql.db; SELECT * from mysql.db where host like ' 10.0.% ';

Insert Update db1.t1 set name= ' AAA ' where id=1;

Empty tables truncate TABLE db1.t1;

Delete tables drop table db1.t1;

Delete database drop databases db1;

Fix tables Repair table tb1 [use frm];

View Permissions Show grants for [email protected] ' localhost ';

echo "Select User,host,password from Mysql.user" |mysql-uroot-plingxiangxiang

Mysql-uroot-p1234556-e "Select User,host,password into outfile '/home/mysql/1.txt ' from Mysql.user;"

；

Add: INSERT into Test.test (ID, name) VALUES (123, ' Ling ');

INSERT into test.test values (value1_1, value2_2), (value2_1,value2_2), (Value3_1, value3_2);

Delete: Delete from test.test where ID in (123, 456);

ALTER TABLE test drop column dt; Delete field, test table, dt field

Change: Update msyql.user Set password = password (' Lingxiangxiang ')

ALTER TABLE employees add primary key (EMP_NO); Add primary Key

ALTER TABLE employees drop/add column salaries; Delete a field

CREATE TABLE Blog_blogmodel as SELECT * from Book_blogmodel; Create the same new table

The table name:

Check: Select User, host, password from mysql.user where user = "root";

SELECT * from Msyql.user where conditions order by user [DESC];

To adjust the order of fields:

1. ALTER TABLE ' user_movement_log ' Gatewayid ' gatewayid ' int not null default 0 after RegionID

2. ALTER TABLE test2 drop column CJ, drop column Goushi;

Sort: SELECT * from Test.test order by ID Asc/desc;

SELECT * from Users WHERE Email not REGEXP ' ^[a-z0-9._%-][email protected][a-z0-9.-]+. [A-z] {2,4}$ '

Show global variables like '%read_only% ';

alter user [email protected] identified by ';

Export:

SELECT * into outfile '/tmp/test/users.txt ' fields terminated by ' <[!] > ' lines terminated by ' <[end]> ' from users;

Import:

Load data infile '/tmp/test/users.txt ' into table gamedb.users fields terminated by ' <[!] > ' lines terminated by ' <[end]> ';

Create index Idx_name on salaries (emp_no); Index of the Emp_no field that created the salaries table

Show index from Salaries\g; View Index

Date common format: ' Year-month-day '

Complex statements:

SELECT * FROM (SELECT * To Employees ORDER BY emp_no DESC) AA GROUP by Hire_date;

Emp_no is sorted first, and then the hire_date is grouped

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/98/D8/wKiom1lBAvSAkHU9AAEV5uswDR4019.jpg "title=" 001. JPG "alt=" wkiom1lbavsakhu9aaev5uswdr4019.jpg "/>

Internal connection: Join default is inner

SELECT * FROM employees inner joins salaries on employees.emp_no = Salaries.emp_no;

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/98/D8/wKiom1lBAxWCv9pNAACuy2aOE5Q785.jpg "title=" 002. JPG "alt=" wkiom1lbaxwcv9pnaacuy2aoe5q785.jpg "/>

Left outer link: right outer chain:

SELECT * FROM employees left join salaries on employees.emp_no = Salaries.emp_no;

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/98/D8/wKioL1lBAzLA0wXwAAEF46E0xkU235.jpg "title=" 003. JPG "alt=" wkiol1lbazla0wxwaaef46e0xku235.jpg "/>

SELECT * FROM employees right join salaries on employees.emp_no = Salaries.emp_no;

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/98/D8/wKiom1lBA1WiWLrAAADMDzzPFJQ251.jpg "title=" 004. JPG "alt=" wkiom1lba1wiwlraaadmdzzpfjq251.jpg "/>

Enhanced version of update

SELECT * FROM Employees;

650) this.width=650; "src=" https://s4.51cto.com/wyfs02/M01/98/D8/wKioL1lBA3LghPXfAACJAiPvkA4746.jpg "title=" 005. JPG "alt=" wkiol1lba3lghpxfaacjaipvka4746.jpg "/>

UPDATE ' Employees '

SET Emp_name = Case Emp_name

When ' ling ' and ' lingjing '

When ' Xiang ' and ' Lingxiang '

ELSE ' Wang '

END

WHERE gender= ' M ';

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/98/D8/wKiom1lBA4qQDS19AACLnVKUiEI509.jpg "title=" 006. JPG "alt=" wkiom1lba4qqds19aaclnvkuiei509.jpg "/>

Limit 1: Print a line

Max (emp_no): Max

Select Max (emp_no) as emp_no from employees; Max (EMP_NO) changed to name Emp_no

Problem three: Intra-junction + non-correlated subquery

SELECT s1.article, ' dealer ', S1.price

From ' Shop ' as S1

JOIN (SELECT ' article ', MAX (price) as Price

From ' Shop '

GROUP by ' article '

) as S2

On s1.article = S2.article

and S1.price = S2.price

;

This article from "12350027" blog, declined reprint!

MySQL Common operations