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_idselect 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 HAVINGavg(salary) >100003.分页select * from person LIMIT 8,4ps: 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.didUNIONselect * from person RIGHT JOIN dept on person.dept_id = dept.did;select * from person LEFT JOIN dept on person.dept_id = dept.didUNION allselect * 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 anddept_id =(select did from dept where dname ='教学部') and salary <10000 ORDER by salary DESC-- 2.查询每个部门中最高工资和最低工资是多少,显示部门名称select MAX(salary),min(salary),dname from personLEFT 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)

#移除主键时需要先解除递增，才能解除主键

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),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)

总结

以上是内存溢出为你收集整理的MySQL:数据库入门篇2全部内容，希望文章能够帮你解决MySQL:数据库入门篇2所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。