MySQL:資料庫入門篇2

#移除主鍵時需要先解除遞增，才能解除主鍵

alter table info modify id int null , drop PRIMARY key

一.用戶許可權
1.創建用戶
create user 'hanshe'@'127.0.0.1' IDENTIFIED by '123'; -- 創建用戶
2.移除用戶
drop user 'hanshe'@'127.0.0.1' ; -- 移除用戶
3.修改用戶
RENAME user 'hanshe'@'127.0.0.1' to 'hanxiaoqiang'@'192.168.0.1' -- 修改用戶
4.查看授權
show GRANTS for 'hanshe'@'127.0.0.1';-- 查看用戶 許可權
5.授權
GRANT select,update ON db1.info to 'hanshe'@'127.0.0.1';-- 授權
GRANT all PRIVILEGES on *.* to 'hanshe'@'127.0.0.1'; -- 授權所有許可權
6.移除授權
REVOKE all PRIVILEGES on *.* FROM 'hanshe'@'127.0.0.1'; -- 移除許可權
7.開放外部訪問許可權

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

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

FLUSH PRIVILEGES; -- 刷新許可權


二.修改用戶密碼
1.方式一:使用 mysqladmin 命令
mysqladmin -u用戶名 -p原密碼 password 新密碼;

2.方式二:直接設置密碼
set password for 'hanshe'@'%' = password('166')

3.方式三: 直接修改
update mysql.user set password = password('123') where user ='hanshe' and host ='%'

flush PRIVILEGES;
5.7 版本
update mysql.user set authentication_string = password('123') where user ='hanshe' and host ='%';

flush PRIVILEGES;

三.忘記密碼怎麼辦（本地使用資料庫）
1.關閉mysql服務
2.重新啟動mysql服務並跳過許可權表
3.直接通過mysql登錄
4.修改密碼
5.刷新

四單表查詢
1.聚合函數
select sum(name),avg(age),max(age),min(age),count(name) FROM person;

2.分組
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

-- 查詢每個部門的平均薪資 並且看看這個部門的員工都有誰?
select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id

#查詢平均薪資大於10000的部門, 並且看看這個部門的員工都有誰?

select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id HAVING
avg(salary) >10000

3.分頁

select * from person LIMIT 8,4
ps: limit (起始條數),(查詢多少條數);


4.SQL 語句關鍵字的執行順序

執行順序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit　

五. 多表聯合查詢
select * from person p,dept d where p.dept_id = d.did -- 笛卡爾乘積
-- 多表聯合查詢
-- select * from person p,dept d where p.dept_id = d.did -- 笛卡爾乘積

-- -- 左連接查詢
-- select * from person LEFT JOIN dept on person.dept_id = dept.did;
--
-- -- 右連接查詢
-- select * from person RIGHT JOIN dept on person.dept_id = dept.did;
--
-- -- 內連接查詢
-- select * from person INNER JOIN dept on person.dept_id = dept.did;

-- 全連接
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;


六、 複雜條件查詢
-- 1. 查詢出 教學部 年齡大於20歲,並且工資小於4000的員工,按工資倒序排列.
-- (要求:分別使用多表聯合查詢和內連接查詢)

select did from dept where dname ='教學部';

select * from person where age>20 and
dept_id =(select did from dept where dname ='教學部') and salary <10000 ORDER by salary DESC

-- 2.查詢每個部門中最高工資和最低工資是多少,顯示部門名稱

select MAX(salary),min(salary),dname from person
LEFT JOIN dept ON person.dept_id = dept.did GROUP BY dept_id


七.子語句查詢
1.使用結果集作為表名查詢
select * from (SELECT * from person) as aaa

-- 2.求最大工資那個人的姓名和薪水

select max(salary) from person;

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

-- 3. 求工資高於所有人員平均工資的人員

select avg(salary) from person;

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