内容: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';