MySQL (python)

python 连接mysql数据库： pymysql  

import pymysql
#建立连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='asdlyu123',
    db='studentinfo',
    charset='utf8',
)

#创建游标
cur = conn.cursor()

try:
    sql = ""  #要执行语句
    cur.execute(sql)   #执行
except Exception as e:
    print("执行失败:", e)
else:
    print("执行成功;")

cur.close()  #关闭游标

conn.close() #关闭连接

window下使用DOS命令进入MySQL数据库：

1. net start mysql 启动数据库  （net stop mysql, 关闭数据库）
2. 进入安装目录bin文件夹
3. mysql -hlocalhost -uroot -p密码   -h:服务器 - -u:数据库用户名 -p:密码
4. mysqldump -uroot -pasdlyu123 --all-databases >all.db  备份
5. mysql -uroot -pasdlyu123 < all.db   恢复

用户操作：

1. creat user 'newuser' @localhost identified by 'password';   新建用户
2. grant select on database_name.table_name to ‘username’@'localhost’;  赋予操作权限
3. grant select  on database_name.* to ‘username’@'localhost’;         给用户database_name的所有权限
4. show grant for username;     查看用户权限
5. revoke select on database to user;   去除权限
6. drop user ‘username’@‘localhost’;    删除用户

数据库操作：

1. show databases;   显示所有数据库
2. use 163com;         使用163com数据库
3. create database studentinfo;       创建名为studentinfo的数据库
4. drop database studentinfo;  删除数据库 

表操作：

1. show tables;          显示数据库163com中所有表格
2. desc 表名;             显示表结构
3. select * from music;      显示music_id表中的内容
4. select music_name from music;  显示music_id表中music_name列的内容
5. create table student (name varchar(50), age int, score float)  default charset=utf8;  创建student表格  表头为：name age score  (显示中文)
6. create table student1 (id int primary key auto_increment, name varchar(25), age int, score float);  键值
7. insert into student (name,age,score) values('ly ',24,99);     表格中插入内容，和表头内容一致
8. update student set age = 26 where name= 'ly'  and score = 99;   更新  同时限定两个条件
9. update student2 set gender=1 where id in (1,3,5,7);   同时更新多个值
10. create table student2 select * from student;  复制表    create student2 select name,age from student; 部分复制
11. insert into student2 (name,age,score) select name,age,score from student;  插入检索的数据（部分复制）
12. SELECT vale1, value2 into Table2 from Table1
13. alter table student add gender int;   更改表的结构  增加一列
14. delete from student where name='lyu 跃';
15. rename student to stu;      重新命名表
16. drop table if exists student;   删除表格

基本查询语句:

select 属性列表 from 表名 [where 条件] [group by 属性名 [having 条件表达式]] order by 属性名 [ascidesc]

根据group的属性名分组，属性值相等的分为一组，并且只有满足having的条件表达才会输出，然后根据order属性排序

select name,age(now())-sage nianling from student1; 

去重：distinct  select distinct age from student1;  去重（某一属性）

大数据量时禁止用distinct，建议使用group by解决重复问题。

例：distinct： select  distinct salary from salaries where to_date = '9999-01-01'  order by salary desc

       group by :select t salary from salaries where to_date = '9999-01-01'  group by salary order by salary desc

确定范围： where score between A and B   select name,score from student1 where score between 60 and 90; 

带in关键字查询:select name,score from student1 where score in (70,95);   成绩为70  95 

带like匹配查询   '_y%'（名字第二位为y）:select name,score from student1 where age like '2%';  (第一位为2匹配)     

多条件匹配 :select name,age,score from student1 where age >23 and score >60;   

排序： order by 属性  select name,age,score from student1 order by age desc; 根据年龄降序排列  desc 降序  asc 升序

函数：

字符串连接: concat():  select concat(name,'==>',score) from student1; 

统计行数：count()

1. count(*)  统计符合条件的行数    select count(*) from student1 where score=70;
2. count(列名) 统计的是列中非空的个数  select count(age) from student1 ;  

统计某列平均值：avg()     select avg(score) from student1;      计算平均成绩

统计某列值的和：sum()    select sum(age),avg(age) from student1;    age和  平均年龄

某列最大值：max() 

某列最小值：min()  select min(age),max(age) from student1;    最小年龄  最大年龄

取奇数：mod()

group by：将查询的结果根据一列或多列的值进行分组，值相等的为一组

1. 单字段分组： select age,count(*) from student1 group by age; 根据age分组  并统计每个年龄的个数
2. select bj,max(math) from student2 group by bj;   根据年级分组，并统计每个年级数学最高分
3. 多字段分组：select age,score,count(*) from student1 group by age,score;   某年龄某成绩的人数
4. 对组进行筛选：having：select bj,avg(math) ,count(*) from student2 group by bj having avg(math)>80; 数学成绩大于80的组输出  并统计每个组大于80的人数
5. 返回字符串结果，由分组中的值结合而成：group_concat():  select id,group_concat(math,',',eng) from student2 group by id ;  每个人的各科成绩 字符串输出
6. rollup: select bj,max(math),min(math) from student2 group by bj with rollup;  统计每个班的数学最高分和最低分  并统计所有学生最高分和最低分

limit 限制查询结果的数量： 接受一个或两个参数，第一个偏移量，第二个得到的行

1. 查询前三行：select * from student1 limit 3;   
2. 查询从第二行开始的三行数据：select * from student1 limit 1,3; 

连接查询：  连接：前左后右

内连接查询： inner join  on:  组合两个表中的数据，返回关联字段相符的记录。（交集）

• select boy.hid,boy.bname,girl.name from boy inner join girl on girl.hid = boy.hid ;  

左右表是按照句子中的顺序排列

左外连接查询：left join on/ left outer join on   左表全部显示。右表只显示符合条件的记录，其中不足的地方显示NULL

• select boy.hid, boy.bname, girl.gname from boy left join girl on girl.hid = boy.hid

右外连接查询：right join on/ right outer join on 与左连接相反

• select boy.hid, boy.bname, girl.gname from boy right join girl on girl.hid = boy.hid

全连接： union/ union all

• 通过union连接 分别单独取出的列数必须相同
• 不要求合并的列表名称相同时，以第一个sql表列名为准
• union连接时，相同的行将会被合并，但比较耗时。union all 连接时  则会保留
• 被union连接的sql字句，单个句子不用写order by，因为不会有效果，可以对最终的结果进行排序。
• (select id,name form A) union all  (select id,name form B) order by id;

子查询： 将一个查询语句嵌套另一个查询语句。

1. any,some,all
2. in,not in
3. exist,not exist

select m_user.name,count(m_order.id)
from m_user inner join m_order
on m_user.id = m_order.user_id 
group by m_user.id
having count(m_order.id)>1
order by count(m_order.id) desc

//排名倒数第三的员工所有信息 limit 2,1 从2开始取1个  
// distinct 日期去重（并不保证真的是倒数第三  测试用例日期是与用户一一对应的应该是）
select * from employees 
where hire_date = (
    select distinct hire_date from employees order by hire_date desc limit 2,1
)