MySQL: Getting Started with the database 2

#移除主键时需要先解除递增 to release the primary key

ALTER TABLE info modify ID int null, DROP PRIMARY key

I. User rights
1. Create a user
Create user ' Hanshe ' @ ' 127.0.0.1 ' identified by ' 123 '; --Create User
2. Removing users
Drop user ' hanshe ' @ ' 127.0.0.1 '; --Remove user
3. Modify the user
RENAME user ' hanshe ' @ ' 127.0.0.1 ' to ' Hanxiaoqiang ' @ ' 192.168.0.1 '--Modify users
4. View authorizations
Show GRANTS for ' hanshe ' @ ' 127.0.0.1 ';--View User rights
5. Authorization
Grant Select,update on Db1.info to ' hanshe ' @ ' 127.0.0.1 ';--authorization
GRANT all privileges on * * to ' hanshe ' @ ' 127.0.0.1 '; --Authorize all permissions
6. Remove Authorization
REVOKE all privileges on * * from ' hanshe ' @ ' 127.0.0.1 '; --Remove permissions
7. Open external access rights

Create user ' test ' @ '% ' identified by ' 123 ';

GRANT all privileges on * * to ' test ' @ '% ';

FLUSH privileges; --Refresh Permissions


Two. Modify User password
1. Mode one: Use the mysqladmin command
Mysqladmin-u User name-p original password password new password;

2. Mode two: Set the password directly
Set password for ' hanshe ' @ '% ' = password (' 166 ')

3. Method Three: Direct modification
Update mysql.user Set password = password (' 123 ') where user = ' Hanshe ' and host = '% '

Flush privileges;
Version 5.7
Update mysql.user Set authentication_string = password (' 123 ') where user = ' Hanshe ' and host = '% ';

Flush privileges;

Three. What to do if you forget your password (local use database)
1. Turn off the MySQL service
2. Restart the MySQL service and skip the permissions table
3. Log in directly via MySQL
4. Change the password
5. Refresh

Four single-table query
1. Aggregation functions
Select sum (name), AVG (age), Max (age), min (age), count (name) by person;

2. Grouping
Select sum (Salary), dept_id from person GROUP by dept_id

Select SUM (Salary) as W, dept_id from the person GROUP by DEPT_ID have w >20000

--Check the average salary for each department and see who the employees in this department are?
Select AVG (Salary), Dept_id,group_concat (name) from the person GROUP by dept_id

#查询平均薪资大于10000的部门, and see who the employees in this department are?

Select AVG (Salary), Dept_id,group_concat (name) from the person GROUP by DEPT_ID have
AVG (Salary) >10000

3. Paging

SELECT * FROM person LIMIT 8,4
Ps:limit (number of starting bars), (number of queries);


Order of execution of 4.SQL statement keywords

Execution order: From, WHERE, GROUP by, SELECT by

Five. Multi-table Joint query
SELECT * FROM person p,dept d where p.dept_id = d.did--Cartesian product
--Multi-table joint query
--select * from person p,dept d where p.dept_id = d.did--Cartesian product

----Left connection query
--select * FROM person left JOIN dept on person.dept_id = Dept.did;
--
----Right connection query
--select * FROM person right JOIN dept on person.dept_id = Dept.did;
--
Intra-----connection query
--select * FROM Person INNER JOIN Dept on person.dept_id = Dept.did;

--Full connection
SELECT * FROM person left JOIN dept on person.dept_id = Dept.did
UNION
SELECT * FROM person right JOIN dept on person.dept_id = Dept.did;

SELECT * FROM person left JOIN dept on person.dept_id = Dept.did
UNION All
SELECT * FROM person right JOIN dept on person.dept_id = Dept.did;


Six, complex conditions query
--1. Check out the teaching department is older than 20 years old, and wages less than 4000 of employees, in reverse order of wages.
--(Requirements: Use multiple table union queries and inner JOIN queries respectively)

Select did from dept where dname = ' teaching Department ';

SELECT * from person where age>20 and
dept_id = (Select did from dept where dname = ' teaching Department ') and salary <10000 ORDER by salary DESC

--2. Query the maximum wage and minimum wage in each department, show the department name

Select MAX (Salary), min (Salary), dname from person
Left JOIN dept on person.dept_id = Dept.did GROUP by dept_id


Seven. Sub-statement query
1. Using the result set as the table name query
SELECT * FROM (SELECT * from person) as AAA

--2. The person's name and salary for the maximum wage

Select Max (salary) from person;

select* from person where salary = (select Max (salary) from person);

--3. A person who pays more than the average wage of all people

Select AVG (salary) from person;

SELECT * FROM person where salary > (select AVG (Salary) from person)

MySQL: Getting Started with the database 2