数据分析 NO.2 SQL与MYSQL进阶

内容：SQL与MYSQL进阶

分组计算：
group by
having 是分组后过滤，where是分组前过滤 用法相同

子查询：
嵌套在其他查询中查询
用法：1 子查询在where里面做过滤 （也可以做表直接使用）2 作为计算字段使用
1

select district from world.city where disrtict in
(select countrycode from world.city where population >= 100000)
或
select a.district from (select district ,countrycode from world.city) a;

2

select id,name,countrycode,(select name from world.city where world.country.code=world.city.countrycode) as b;

连接表：
inner join 取交集
left join 左边是主表，共有部分选取出来，然后主表的都选取出来
right join 相反
一次只能针对两张表join，

outjoin : 目前个人理解，交集取1份，然后两边没有的也要选出来。

组合查询：
union 规则：
必须由两条或者两条以上的select语句组成，语句之间用union分隔
union中的每个查询必须包含相同的列，表达式或聚集函数
列数据类型必须兼容
union 会自动去重，不需要使用 union all
工作中： 速度快
EX:

select 	countrycode，sum(population) from world.city group by countrycode  
union 
select code,population from world.county
union

union可以多个使用
order by 只能用在最后的语句
数值可以变成字符，字符不能变成数值。

表的创建和操作：
存表的时候，不允许有相同的列名
EX: create table if not exists world.XXX
AS 下面跟代码

删除表：drop table if exists XXX

创建一个表：（一般就不做修改了，因为删除丢掉是不可逆的）
创建一个表 create table XXX
定义列名数据类型,设置主键（ps.主键可以是多个组合，也可以不用主键）（
name char
primary key (id)
）
插入数据
insert into
select
from

更新和删除表：（建议创建表的时候就考虑好表结构，MYsql 不可逆）
alter 修改
EX：
alter table world.country modify column population bigint 把表中population数据类型变成bigint
alter更改是创建一个新的一行，然后往里面填数据

alter table world.test
add new_name char(80);

alter table world.test
drop id   删除了id这一行

update,delete 不可逆，最好结合where，要不就是对所有的行操作了！

update world.test
set population=0 
where
id=129

update world.test
set new_name=1
where new_name is null

delete  from world.test
where new_name is null

drop 是删除表，delete是删除记录但是表还在

null值既不是空值也不是0 所以使用 is null 和is not null
null会被忽略计算

创建表的时候可以在定义数据类型
id int not null

select * from world where name is null 

SQL随堂练习：

SELECT 性别,sum(历史成功借款金额) 总成功借款金额,sum(历史成功借款次数) 总成功借款次数 
FROM 拍拍贷.LC
group by 性别

结果显示

select 初始评级,sum(历史逾期还款期数) 总逾期次数 from 拍拍贷.LC
group by 初始评级

关于使用Navicat和python进行数据库的创建和插入
首先安装Mysql数据库，使用Navicat进行连接，可以直接在Navicat类似可视化的创建数据库（点点点），当然也可以用mysql进行创建。

sql = "
create database school;#创建school的数据库


use school;#使用数据库


CREATE TABLE IF NOT EXISTS `school_teacher`(  #创建school_teacher的表
   `id` INT UNSIGNED AUTO_INCREMENT,      #定义id为无符号型，自增
   `name` VARCHAR(100) NOT NULL, #定义name为字符型（所允许保存的最大长度为100）（最大长度255）  不允许为空
   `zhuanye` VARCHAR(40) NOT NULL,#定义name为字符型（所允许保存的最大长度为40）（最大长度255）  不允许为空
   `submission_date` DATE, #获取日期
   PRIMARY KEY ( `id` )#设置id为主键
)
ENGINE=InnoDB DEFAULT #设置数据库引擎
CHARSET=utf8;#设置编码方式
"

insert into school_teacher(id,name,zhuanye,date)value("xiaoming","dianxin"，now()) #插入数据,由于id可以自增，因此可以不设置

注：创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键

关于使用python进行数据库(Mysql)的操作主要使用python的pymysql包（如果数据库是SQL Serve则使用户pymssql在这不座介绍）
其中比较常用的方法列出，更全的方法可参考官方文档或者谷歌
.connect(
host =
user =
password =
database =
port =
charset = “utf-8”
)#连接数据库

connection对象支持的方法：
.cursor()使用该连接并返回游标
.commit()提交当前事务
.close()关闭连接

cursor对象支持的方法：
.execute(sql)执行一个数据库的命令
.fetchall()获取结果集中的所有行 （获取的是一个元祖）
.close(）
cursor对象属性
.description 获取列名

desc = cursor.description
print("%s %3s" % (desc[0][0], desc[1][0]))

import pymysql
conn = pymysql.connect(
host = 'lochost',
user = 'sa',
password = 'admin',
database = "school"
port = 3306
charset = "utf-8"
)
cur = conn.cursor()
name = "xiaoming"
age = 18
gender = "male"
project = "math"
sql = 'insert into teacher(name,age.gender,project)value("%s",%s,"%s","%s")'%
(name,age,gender,project)
sql = 'update teacher set gender = "female" where name ='xiaoming''
cur.execute(sql)
conn.commit()
cur.close()
conn.close()

以上sql代码中如果外部传入数据进去，查询需要是字符型的话需要添加转移符 "

a = "20200807"
b = "车载产品线"
# c = (a,b)
sql = """update inform.d_xuqiu
         set 需求来源 = \"%s\"
         where 需求编号 = \"%s\"
            """%(a,b)

将数据库和Web项目部署在服务器Tomcat
首先要在Linux服务器上面装一个MysqlLinux安装mysql的方法，然后数据通过Navicat传输上去，
具体可以参考服务器到部署Web项目到Tomcat

mysql在表的某一位置增加一列、删除一列、修改列名

如果想在一个已经建好的表中添加一列，可以用以下代码：

alter table 表名 add column 列名 varchar(20) not null;

这条语句会向已有的表中加入一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：

alter table 表名 add column 列名 varchar(20) not null after user1;

注意，上面这个命令的意思是说添加addr列到user1这一列后面。如果想添加到第一列的话，可以用：

alter table 表名 add column 列名 varchar(20) not null first;


将表yusheng中，列名def改为unit

alter table yusheng change  def unit char;


将表yusheng中，列名def的列删除

alter table yusheng drop column def ;

复制table表
create table 新表名(
select ID,name,number,numberid
from 要复制的表名);


查看表的各种数据类型
describe `student`


查看表的已存数据
select * from 表名 (select id,name,number,numberid from 表名)


添加数据
insert into 表名 (ID,name,number,numberid) values(1,'Logic',220,1),(2,'HTML',160,1),(3,'Java OOP',230,1);


删除数据
delete from 表名 where 条件;


修改数据
update 表名 set numberid=2 where 条件


降序排列
select id,name,number,numberid from 表名 order by id desc;


添加别名
select id(列名) '别名' ,(列名) as '别名' from 表名;

查询某个数据库里所有的表
1.查询所有数据库
show databases;

2.查询指定数据库中所有表名
select table_name from information_schema.tables where table_name='database_name' and table_type='base table';

3.查询指定表中的所有字段名
select column_name from information_schema.columns where table_schema='database_name' and table_name='table_name';

4.查询指定表中的所有字段名和字段类型
select column_name,data_type from information_schema.columns where table_schema='database_name' and table_name='table_name';