python-study-40

账号授权

# 1、跟权限有关的几张表
权限从高到底
user->db->table_priv->columns_priv
user:所有库都有权限
db:所有表都有权限
table_priv:一张表有权限
columns_priv:一个字段有权限

查看表中的账号，分析权限
一般root账号只有一个，精准权限控制
select * from mysql.user
select * from mysql.db
....

# 2、只创建账号
这种创建的账号没有权限，一般不用这种方式，用创建账号并授权的方式
root只能本地连接，ssh连过去用这个账号登录即可
#create user tom@"客户端的ip" identified by "123";
create user tom@"192.168.15.%" identified by "123";
create user tom@"%" identified by "123";  #% 代表所有客户端都可以连

#客户端：192.168.15.13               服务端：192.168.15.90

create user tom@"192.168.15.13 " identified by "123";
mysql -utom -p"123" -h 192.168.15.90 -P 3306

# 3、创建账号并且授权（只有root账号才能为其他账号授权grant）
一般使用这种创建账号的方式
# *.* ===> mysql.user 权限范围，向下兼容
grant all on *.* to "tom"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.* ===> mysql.db 权限范围，向下兼容
grant all on db1.* to "jack"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.t1 ===> mysql.tables_priv  权限范围，向下兼容
grant all on db1.t1 to "rose"@"192.168.15.90" identified by "123";#all代表除了grant以外的所有权限

# db1.t1(id) ===> mysql.columns_priv  权限范围，向下兼容
grant select(id),update(name) on db1.t1 to "lili"@"192.168.15.90" identified by "123";

# 修改完权限一定要
flush privileges;

# 4、删除账号
drop user "tom"@"192.168.15.90";
drop user "jack"@"192.168.15.90";
drop user "rose"@"192.168.15.90";
drop user "lili"@"192.168.15.90";
flush privileges;

 

单表查询

#1、完整语法(语法级别关键字的排列顺序如下)
select distinct 字段1,字段2,字段3,... from 库名.表名
                    where 约束条件
                    group by 分组依据
                    having 过滤条件
                    order by 排序的字段
                    limit 限制显示的条数
                    ;
# 必须要有的关键字如下：
select * from t1;


# 关键字执行的优先级
from
where
group by
having
distinct
order by
limit


def from(): #每个关键字可以理解为一个功能
    pass

def where():
    pass

def group():
    pass

def having():
    pass

def distinct():
    pass

def order():
    pass

def limit():
    pass

def select(): #select理解为所有功能的集合
    f=from()
    res1=where(f)
    res2=group(res1)
    res3=having(res2)
    res4=distinct(res3)
    res5=order(res4)
    limit(res5)

一个扇区=512B
一个block（没有修改过的）=8个扇区
一般mysql把数据从硬盘读到内存是一个block读进内存，即一次读4096B=4KB


#1、简单查询
select * from t1;
select id,name,sex from t1;
select distinct post from emp; #distinct只能记录对查出来的记录去重，不能指定字段名
select name,salary*12 as annual_salary from emp; #四则运算+别名

#concat字符串拼接
select concat('名字: ',name) as new_name,concat("年龄: ",age) as new_age from emp;
select concat(name,":",age) from emp;
#concat_ws 指定分隔符
select concat(name,":",age,":",sex) from emp;
select concat_ws(":",name,age,sex) as info from emp;

#mysql里的流程控制，变量都可以放在python程序里，公司就不用招专门的sql开发了
 SELECT
       (
           CASE
           WHEN NAME = 'egon' THEN
               NAME
           WHEN NAME = 'alex' THEN
               CONCAT(name,'_BIGSB')
           ELSE
               concat(NAME, 'SB')
           END
       ) as new_name
   FROM
       emp;

#2、where
1、比较运算
2、between
3、in
4、like
5、逻辑运算
#where的原理：对所有的记录用遍历的方法筛选，所以效率很低，后面会有索引优化方案
select * from emp where id >= 10 and id <=15; # 等同于select * from emp where id between 10 and 15;
select * from emp where id = 6 or id = 9 or id = 12; # 等同于select * from emp where id in (6,9,12);

_代表任意单个字符
%代表任意无穷个字符
select * from emp where name like "__";
select * from emp where name like "jin%";
select * from emp where id not in (6,9,12);
select * from emp where id not between 10 and 15;


#3、group by分组
# 什么分组：按照所有记录相同的部分进行归类，一定区分度低的字段
# 为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义


# set global sql_mode="strict_trans_tables,only_full_group_by"; 设置模式写到配置文件里
# 注意:分组之后，只能查到分组的字段以及组内多条记录聚合的成果
select * from emp group by post;

# 聚合函数 用在group by之后 在where里不能用
max
min
avg
sum
count


select post,count(id) from emp group by post;
select post,max(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select sex,count(sex) from emp group by sex;

# 统计出每个部门年龄30以上的员工的平均薪资
select post,avg(salary) from emp where age >= 30 group by post;

# 注意：分组是在where之后发生的
mysql> select * from emp where max(salary) > 3000;
ERROR 1111 (HY000): Invalid use of group function

# group_concat
select post,group_concat(name,':',age) from emp group by post;


#4、having 过滤条件
# 可以用聚合函数
# where是在分组之前的过滤，即在分组之前做了一次整体性的筛选
# having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的筛选

select post,avg(salary) from emp group by post having avg(salary) > 10000;
select post,avg(salary) from emp group by post ;

#5、order by排序

select * from emp order by age asc; # 默认asc升序-》从小到大
select * from emp order by age desc;# desc降序-》从大到小

select * from emp order by age asc,salary desc; # 先按照age升序排列，如果age相同则按照salary降序排

select post,avg(salary) from emp group by post order by avg(salary);

#6、limit 限制显示的条件
select * from emp limit 3;

#薪资最高那个人的详细信息
select * from emp order by salary desc limit 1;

# 分页显示
select * from emp limit 0,5; # 从0开始往后取5条
select * from emp limit 5,5; #从5开始往后取5条

#正则表达式
select * from emp where name regexp "^jin.*(g|n)$";

 

多表查询

#1、笛卡儿积
select * from emp,dep;

select * from emp,dep where emp.dep_id = dep.id;

select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术";

#2、内连接：只取两张表有对应关系的记录
select * from emp inner join dep on emp.dep_id = dep.id;
select * from emp inner join dep on emp.dep_id = dep.id
                            where dep.name = "技术";


#3、左连接: 在内连接的基础上保留左表没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;

#4、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;

#5、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

 

子查询

子查询：就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
以后尽量使用多表连接的查询方式，效率较快
生产中用的都是多表查询 是在单表查询的基础上进行的


select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");


# 每个部门最新入职的员工
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;



select * from emp inner join dep on emp.dep_id = dep.id;

今日内容：

今日内容：
    1、增、删、修改
    2、单表查询（*******）
    3、多表查询（*******）

    http://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5
    作业
    4、pymysql模块=》客户端工具
    5、navicat工具=》图形化界面的mysql客户端工具