MySQL: database entry 2: mysql database entry

MySQL: database entry 2: mysql database entry

# To remove a primary key, you must first unincrement the primary key.

Alter table info modify id int null, drop PRIMARY key

1. User Permissions
1. Create a user
Create user 'hanshe' @ '192. 0.0.1 'IDENTIFIED by '20160901'; -- create a user
2. Remove a user
Drop user 'hanshe' @ '2014. 0.0.1 '; -- remove a user
3. modify a user
RENAME user 'hanshe' @ '192. 0.0.1 'to 'hanxiaoqiang' @ '192. 168.0.1 '-- modify a user
4. View authorization
Show GRANTS for 'hanshe' @ '192. 0.0.1 '; -- view User Permissions
5. Authorization
GRANT select, update ON db1.info to 'hanshe' @ '192. 0.0.1 '; -- authorize
GRANT all PRIVILEGES on *. * to 'hanshe' @ '2014. 0.0.1 '; -- GRANT all Permissions
6. Remove authorization
REVOKE all PRIVILEGES on *. * FROM 'hanshe' @ '2017. 0.0.1 '; -- remove permission
7. Open external access permissions

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

GRANT all PRIVILEGES on *. * to 'test' @ '% ';

Flush privileges; -- refresh Permissions


2. Change User Password
1. Method 1: Use the mysqladmin command
Mysqladmin-u username-p Original password new password;

2. Method 2: Set the password directly
Set password for 'hanshe' @ '%' = password ('20140901 ')

3. Method 3: directly modify
Update mysql. user set password = password ('000000') where user = 'hanshe' and host = '%'

Flush PRIVILEGES;
Version 5.7
Update mysql. user set authentication_string = password ('000000') where user = 'hanshe' and host = '% ';

Flush PRIVILEGES;

3. What should I do if I forget my password (use the database locally)
1. Disable mysql Service
2. Restart the mysql service and skip the permission table.
3. Log On directly through mysql
4. Change the password
5. Refresh

Four single table queries
1. Aggregate functions
Select sum (name), avg (age), max (age), min (age), count (name) FROM person;

2. Group
Select sum (salary), dept_id from person group by dept_id

Select sum (salary) as w, dept_id from person group by dept_id HAVING w> 20000

-- Query the average salary of each department and check who has employees in this department?
Select avg (salary), dept_id, GROUP_CONCAT (name) from person group by dept_id

# Query departments with an average salary of more than 10000 and check who has employees in this department?

Select avg (salary), dept_id, GROUP_CONCAT (name) from person group by dept_id HAVING
Avg (salary) & gt; 10000

3. Paging

Select * from person LIMIT 8, 4
Ps: limit (start number), (Number of queries );


4. execution sequence of SQL statement keywords

Execution sequence: FROM-> WHERE-> group by-> HAVING-> SELECT-> order by-> limit

5. 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 join query
-- Select * from person left join dept on person. dept_id = dept. did;
--
-- Right join query
-- Select * from person right join dept on person. dept_id = dept. did;
--
-- Query internal connections
-- 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;


6. Complex Condition Query
-- 1. Find out the employees who are older than 20 years old and whose salaries are less than 4000 in descending order.
-- (Requirement: Multi-table joint query and intra-join query respectively)

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

Select * from person where age> 20 and
Dept_id = (select did from dept where dname = 'teaching authorization') and salary <10000 ORDER by salary DESC

-- 2. query the maximum wage and minimum wage in each department, and display the Department name

Select MAX (salary), min (salary), dname from person
Left join dept ON person. dept_id = dept. did group by dept_id


VII. substatement Query
1. query using the result set as the table name
Select * from (SELECT * from person) as aaa

-- 2. The name and salary of the person with the maximum salary

Select max (salary) from person;

Select * from person where salary = (select max (salary) from person );

-- 3. personnel whose salaries are higher than the average salaries of all personnel

Select avg (salary) from person;

Select * from person where salary> (select avg (salary) from person)