mysql使用

1.MYSQL数据导出与导入，secure_file_priv参数设置

执行：vim /etc/my.cnf

然后重启mysql：service mysqld restart

https://blog.csdn.net/HHTNAN/article/details/78520511

2.进入mysql

mysql -u root -p

3.查看数据库有哪些

show databases

4查看表有哪些

show tables

5进入数据库

use "数据库”

6.导入一条csv文件数据

load data infile "/data/roaming_hour_data/df_feat_20190107_0.csv" INTO TABLE roaminglog.roaming_hour_features FIELDS TERMINATED BY ',' ignore 1 lines
ignor 1 line表示忽略第一行

采用该语句导入mysql时，第一行的列名也会被当成数据，所以要忽略第一行。

7.将pandas中dataframe数据导入数据库

import pandas as pd 
from sqlalchemy import create_engine
import warnings
warnings.filterwarnings("ignore")
if __name__ == '__main__':

    engine = create_engine("mysql+pymysql://root:password@host:port/database")
    df = pd.DataFrame({'id': ["v"],
                   'pred_proba': ["a"],
                    'day': [10],
                    'hour':[3]})
    df.to_sql(name = tablename,con = engine,if_exists = 'append',index = False,index_label = False)

https://blog.csdn.net/qq_23953717/article/details/80067917

8.将mysql上的数据读入dataframe

import pymysql
import pandas as pd
conn = pymysql.connect(host = "localhost",user = username,passwd = password,db = database_name,charset="utf8")
sql_query = 'SELECT * FROM database_name.table_name'
df = pd.read_sql(sql_query, con=conn)
conn.close()  #使用完后记得关掉

https://www.cnblogs.com/cymwill/articles/7576600.html

9.mysql删除表中重复元素

https://www.cnblogs.com/jiangxiaobo/p/6589541.html

10.mysql中in的用法

https://blog.csdn.net/yihanzhi/article/details/81065573

11.SqlServer四种排序：ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

https://blog.csdn.net/a11112244444/article/details/80453397

https://blog.csdn.net/qq_25221835/article/details/82762416

12.sql中with as使用 

https://www.cnblogs.com/Niko12230/p/5945133.html