数据库2
用户权限
1，创建用户
create user 'ergou'@'127.0.0.1' identified by '123';
2移除用户
drop user 'ergou'@'127.0.0.1';
3修改用户
rename user 'ergou'@'127.0.0.1' to 'er'@'127.0.0.1'
4 查看授权
show grants for 'er'@'127.0.0.1';
5，授权
手动逐个授权
GRANT SELECT ,UPDATE on a1.info1 to 'er'@'127.0.0.1';
授权所有
grant all privileges on *.* to 'er'@'127.0.0.1';
6 移除授权
revoke all PRIVILEGES on *.* from 'er'@'127.0.0.1';
7开放外部访问权限
CREATE user 'ergou'@'%' IDENTIFIED by '123'; GRANT all PRIVILEGES on *.* to 'ergou'@'%'; FLUSH PRIVILEGES;#刷新权限
修改密码
第一种方式
mysqladmin -u用户名 -p原密码 password 新密码;
mysqladmin -uergou -p123 password 111
第二种方式
set password for 'ergou'@'%'=password('44');
第三种方式
update mysql.user set password = password('123') where user ='ergou' 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 info1;
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
5 多表联合查询
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; 6. 复杂条件查询 -- 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 7.子语句查询 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)
临时表查询
SELECT * FROM person as p1 , (SELECT dept_id,avg(salary) AS m FROM person GROUP BY dept_id) as p2 where p1.dept_id=p2.dept_id and p1.salary>p2.m;
将得出的结果作为新的表进行联合查询
判断查询
SELECT p.name,p.age,p.sex,p.salary, if(p.salary>10000,'高端人群','低端人群')as '级别' FROM person p;
SELECT dname , SUM(case when salary>10000 then 1 else 0 END) as '富人', SUM(case when salary BETWEEN 5000 and 10000 then 1 else 0 end ) as '小资', SUM(case when salary BETWEEN 3000 and 5000 then 1 else 0 end ) as '平民', SUM(case when salary BETWEEN 1000 and 3000 then 1 else 0 end ) as '屌丝' FROM person,dept WHERE dept.did=person.dept_id GROUP BY dept_id;
SQL逻辑查询语句执行顺序(重点***)
SELECT DISTINCT <select_list>
FROM <left_table><join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>ORDER BY <order_by_condition>LIMIT <limit_number>
外键约束
约束是一种限制，它通过对表的行或列的数据做出限制来确保表的数据的完整性，唯一性
外键约束一般是在创建表的同时创建的，
