![](https://img-blog.csdnimg.cn/20201014180756754.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
数据库使用
金融小白数据分析之路
这个作者很懒,什么都没留下…
展开
-
pandas自定义排序
案例:进行对pandas自定义排序import pandas as pd#数据集df = pd.DataFrame({'word':['a','b','c'], 'num':[2,1,3]})#自定义排序顺序,此顺序对应为升序ascending=Truelist_sorted = ['b', 'a', 'c']#对相关列进行自定义排序df['word'] = df['word'].astype('category').cat.set_categories(list_sorted)#结果df原创 2021-06-04 15:28:29 · 415 阅读 · 0 评论 -
sql报错Duplicate entry ‘...‘ for key ‘PRIMARY
1.原因就是主键字段出现了重复的情况。(主要应该是这个原因)2.可能字段超出了设定范围。原创 2021-06-04 14:00:51 · 6797 阅读 · 0 评论 -
Python ORM框架来操作MySQL (SQLAlchemy)
# Python ORM框架来操作MySQL SQLAlchemy# pip install sqlalchemy# 初始化数据库连接from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,String,creat...原创 2021-04-24 14:37:38 · 224 阅读 · 0 评论 -
SQL存储过程
CREATE PROCEDURE 存储过程名称 ([参数列表])BEGIN 需要执行的语句END CREATE PROCEDURE add_num ( IN n INT ) BEGIN DECLARE i INT; DECLARE sum INT; SET i = 1; SET sum = 0; WHILE i <= n DO SET sum = sum + i; SET i = i + 1; END WHIL.原创 2021-04-23 10:37:25 · 79 阅读 · 0 评论 -
SQL视图
-- 创建视图CREATE VIEW view_name ASSELECT column1, column2FROM tableWHERE condition-- 创建视图CREATE VIEW player_above_avg_height ASSELECT player_id, heightFROM playerWHERE height > (SELECT AVG(height) from player)-- 筛选视图SELECT * FROM player_above...原创 2021-04-23 09:36:50 · 75 阅读 · 0 评论 -
SQL99中是如何使用连接
-- 交叉连接SELECT * FROM player CROSS JOIN team-- SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3-- 自然连接 player 表和 team 表进行等值连接SELECT player_id,team_id,player_name,height,team_name FROM player NATURAL JOIN team-- on连接SELECT player_id, player.team_id, pla.原创 2021-04-22 21:39:57 · 52 阅读 · 0 评论 -
SQL92中是如何使用连接
SELECT * FROM playerSELECT * FROM team-- 笛卡尔积SELECT * FROM team,player-- 等值连接SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_idSELECT player_id, a.team_id, player_name, height, te.原创 2021-04-22 21:23:19 · 64 阅读 · 0 评论 -
SQL子查询
https://github.com/cystanford/sql_nba_dataSELECT player_name,height FROM player WHERE height=(SELECT MAX(height) FROM player)-- 筛选身高大于这个数值的球员姓名、身高和球队IDSELECT player_name,height,team_id FROM player as a WHERE height> (SELECT avg(height) FROM player原创 2021-04-22 21:06:46 · 51 阅读 · 0 评论 -
SQL聚集函数
-- count(*)只统计行数,不管某个字段是否为nullSELECT count(*) from heros WHERE hp_max>6000SELECT count(role_assist) FROM heros WHERE hp_max>6000SELECT max(hp_max) FROM heros WHERE role_main='射手' or role_assist = '射手'SELECT COUNT(*), AVG(hp_max), MAX(mp_max), ...原创 2021-04-22 14:17:53 · 89 阅读 · 0 评论 -
SQL 函数
SELECT ABS(-2)SELECT MOD(101,3)SELECT ROUND(37.25,1)SELECT CONCAT('ABC','123')SELECT LENGTH('你好')SELECT CHAR_LENGTH('你好')SELECT LOWER('ABC')SELECT REPLACE('fabcd','abc',123)SELECT SUBSTRING('fabcd',1,3)SELECT CURRENT_DATE()SELECT CURRE...原创 2021-04-22 13:52:37 · 99 阅读 · 0 评论 -
SQL数据过滤
SELECT name, hp_max FROM heros WHERE hp_max > 6000SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811SELECT name, hp_max FROM heros WHERE hp_max IS NULLSELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max >...原创 2021-04-22 11:31:30 · 83 阅读 · 0 评论 -
SQL SELECT使用
数据https://github.com/cystanford/sql_heros_data--增加一列字段platformSELECT '王者荣耀' as platform, name FROM heros--去除重复行SELECT DISTINCT attack_range FROM heros--对组合范围进行去重SELECT DISTINCT attack_range,name FROM heros--ASC 递增 DESC 递减SELECT name, hp_max FRO原创 2021-04-22 10:52:43 · 176 阅读 · 0 评论 -
sql字段操作(增加、修改、删除)
--增加字段ALTER TABLE player add (age INT(11));--修改字段ALTER TABLE player RENAME COLUMN age to player_age--修改字段类型ALTER TABLE player MODIFY player_age FLOAT(3,1)--删除字段ALTER TABLE player DROP COLUMN player_age;原创 2021-04-22 10:04:12 · 252 阅读 · 0 评论 -
sql进行反向正则匹配
SELECT DISTINCT productnameFROM ( SELECT miulee.ranking, miulee.BSRDate, miuleesku.productname, miuleesku.Bigcategoryname FROM miulee INNER JOIN miuleesku ON miulee.asin = miuleesku.asin ) miuleedeal WHERE productname NOT REGEXP '毛|天鹅绒.原创 2021-04-20 10:45:50 · 540 阅读 · 0 评论 -
sql转pandas最佳方法
improt pandas as pddf = pd.DataFrame(resoverall.fetchall())原创 2021-04-17 16:36:46 · 364 阅读 · 0 评论 -
sql 使用正则表达式实战二
regexp是使用了正则SELECT DISTINCT * FROM ( SELECT miulee.ranking, miulee.BSRDate, miuleesku.productname, miuleesku.Bigcategoryname FROM miulee INNER JOIN miuleesku ON miulee.asin = miuleesku.asin ) miuleedeal WHERE productname REGEXP '毛' A原创 2021-04-17 15:53:50 · 85 阅读 · 0 评论 -
sql进行表连接和去重实战一
INNER JSON 进行内连接SELECT miulee.ranking, miulee.BSRDate, miuleesku.productname, miuleesku.Bigcategoryname FROM miulee INNER JOIN miuleesku ON miulee.asin = miuleesku.asin DISTINCT进行去重,ORDER BY进行排序SELECT DISTINCT * FROM ( SELECT mi原创 2021-04-17 14:29:11 · 657 阅读 · 0 评论 -
pymysql操作mysql数据库
import pymysqldb=pymysql.connect(host="localhost", user="",# 用户名password="*****", # 密码db="", #链接数据库的名port=3306 # 端口)# Create your views here.cur=db.cursor()sql="SELECT * FROM miulee limit 10"try: cur.execute(sql) results=cur.fetchall()e.原创 2021-04-16 15:43:19 · 60 阅读 · 0 评论 -
navicat导入导出数据(mysql)
导出数据导入数据原创 2021-04-14 16:55:15 · 153 阅读 · 0 评论 -
pymysql 导入数据出现错误1062, “Duplicate entry ‘ ‘ for key 错误
比如(1062, "Duplicate entry 'B083LMRXJC-2021-01-18' for key 'asinbsr'")就是自己有重复的数据进行插入了,做了约束。还有不懂的可以跟博主留言交流,感觉有用可以点赞加关注。原创 2021-04-14 15:47:51 · 3127 阅读 · 0 评论 -
sql设置两个字段不唯一(多个字段的约束条件)
如果需要命名 UNIQUE 约束,以及为多个列定义 UNIQUE 约束,请使用下面的 SQL 语法:MySQL / SQL Server / Oracle / MS Access:CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT uc_PersonID原创 2021-04-13 19:44:19 · 1099 阅读 · 0 评论 -
SQL学习第五篇
原创 2021-04-09 10:21:14 · 170 阅读 · 0 评论 -
SQL学习第三篇
CREATE VIEW ViewPractice5_1 (product_name,sale_price,regist_date) AS SELECT product_name,sale_price,regist_date FROMproduct WHERE sale_price>=1000 AND regist_date='2009-09-20';插⼊时将会报错SELECT product_id,product_name,product_type,sale_price,(...原创 2021-04-07 20:37:28 · 99 阅读 · 0 评论 -
SQL学习第二篇
数据的准备--表格创建CREATE TABLE productins(product_id CHAR(4) NOT NULL,product_name VARCHAR(100) NOT NULL,product_type VARCHAR(32) NOT NULL,sale_price INTEGER DEFAULT 0,purchase_price INTEGER ,regist_date DATE ,PRIMARY KEY (prod原创 2021-04-07 17:38:10 · 159 阅读 · 0 评论 -
SQL学习第一篇
CREATE TABLE Addressbook( regist_no INTEGER NOT NULL, name VARCHAR(128) NOT NULL, address VARCHAR(256) NOT NULL, tel_no CHAR(10), mail_address CHAR(20), PRIMARY KEY(regist_no) ) ;以下均使用sqlite数据库假设在创建练习1.1中的 Addre...原创 2021-04-07 10:07:32 · 234 阅读 · 0 评论 -
sqlite在pycharm的使用,pandas转换到sqlite
from sqlalchemy import create_engineimport pandas as pddf=pd.read_excel('资金流向.xlsx')# 新建数据库的名称engine= create_engine('sqlite:///choice.db')# 把pandas数据转化为sql,存入到CaptionFlowb sql表df.to_sql('CaptionFlow', engine)...原创 2021-03-19 11:54:08 · 262 阅读 · 0 评论