关系型数据库

关系型数据库(MySQL)

mysql 官网

阿里云 SQL 入门

菜鸟教程 python 与 sql

注意：【数据库】在不同的语境下代表的含义是不同的 数据库 = N 个 table table = 表结构+表记录

针对 databases 的增删改查

mysql -u root -p #链接root用户至服务器

create user username identified by 'password'; 增加用户
create user testuser identified by 'test123'; 示例1

use mysql;# 使用mysql 数据库
update user set host='%' where user='testuser';# 指定用户的授权
grant all privileges on *.* to testuser@'%';# 指定用户的授权
show grants for 'testuser';#查看权限

show databases;#查看数据库【databases复数】
create database name; #创建名为name的数据库
drop database name; #删除名为name的数据库

针对 table 的增删改查

#创建
use name #选中name数据库
create table tablename(
id bigint(20),
ip varchar(255)
); #创建名为tablename的表单

#删除
create table tablename #删除一个table

#修改
alter table [tablename] rename [newname]#修改table名

show tables;#显示当前数据库所有表单
describe tablename ;#查询表结构
select * from tablename ;#查询表数据
select colnamel,colname2 from tablename;#查询表的指定列

针对列的增删改查

#增加
alter table [tablename] add [c1] [datatype][no null][default];#增加表的列
alter table tablename add c1 int(11) null; #示例1
alter table tablename add c2 int(11) not null default 1;#示例2

#删除
alter table [tablename] drop [c1];#删除表的列

#修改
alter table [tablename] change [oldcloumname][newcloumname][datatype];#修改列的信息
alter table tablename change c1 c100 int(11);#示例1
alter table tablename change c1 c1 varchar(255);#示例2

#查询
describe tablename ;#查询表结构
select * from tablename ;#查询表数据
select colnamel from tablename;         #查询表的指定单列
select colnamel,colname2 from tablename;#查询表的指定多列

针对行的增改查

#插入
insert into【table name】 values(值1,值2,值3);#插入数据数量必须和列数一致
insert into employee values(1,1,1,1,1);#示例1
insert into【table name】(列1,列2) values(值1,值2）#数据插入指定列

#插入命令insert和查询命令select的组合使用【插入与被插入的数据类型需一致】
insert into【被插入表名】select *from 【table name】[where等子句]..;#常用示例1
insert into【被插入表名】(列名) select (列名) from 【table name】[where等子句]..;#常用示例2

#删除
delete from 【table name】 where 条件;
delete from gc where id = 1; 示例

#修改updata
updata table_ name set colname = xxx[where子句];
updata gc set id = 1 where ip = 3;#示例1
updata gc set id = 1 , account = 1 where ip = 3; #示例2

### 针对行的进阶查找功能【筛选、排序】

#指定查找in
select * from tablename where colname in （value1,value12..）;
select * from gc where id in ('1','2');#示例1

select * from tablename where colname in （select from tablename）;
select * from gc where id in (select id from book where ip <4);示例

#筛选【多重筛选用and和or】
select * from table_ name where colname is null ;
select * from table name where colname is not null ;

select * from table name where colname like abc ;#模糊匹配1
select * from table name where colname not like abc ;#模糊匹配2
注释：abc 固定加单引号匹配格式分为 '%abc'、'abc%'、'%abc%'
select * from table name where colname not like '%abc';#示例1，模糊匹配以abc结尾的数据

select * from tablename where colname 【运算符】值 ;#注意，sql的等于就是单个=
select * from tablename where colname = '1' ;#示例，字符串需要加''
select * from tablename where colname != 1 ;#示例1
select * from tablename where colname between 1 and 3;#示例2，colname值在1和3之间
select * from tablename where colname not between 1 and 3;#示例3，colname值不在1和3之间

#多重筛选
select * from table_ name where colname is null and colname is not null or colname = '1' ;

#去重筛选
select distinct colnamel from tablename;#查询表的指定列【去重】
select distinct colnamel,colname2 from tablename;#查询表的指定列【去重】

#截取【横向】
select * from table_ name [where子句] [order子句] limit [offset,] colcount;
select * from table_ name order by id limit 0,2; #示例1
select * from table_ name order by id limit 2; # 示例2等价于示例1
select * from table_ name order by id limit 10,2; # 示例3，截取11、12行的数据

# 排序【默认asc升序】
select * from table_ name [where 子句] order by col_name [asc/desc]#1.按单一列名排序
select * from table_name [where 子句] order by col_name [asc/desc], col_name2[asc/desc]#2.按多列排序

#筛选+排序
select * from table_ name where colname is null order by col_name [asc/desc],col_name2[asc/desc]

补充：windows 下 python 连接 linux 的 mysql 并且使用

• linux 上成功安装 mysql
• 授权 mysql 可以远程访问 开放防火墙的端口号

GRANT ALL PRIVILEGES ON *.* TO 【账号】@'%'  IDENTIFIED BY ‘【密码】’  WITH GRANT OPTION;
FLUSH   PRIVILEGES;

#开始编写代码测试

import Mysqldb as mysql
conn=mysql.connect(host=’192.168.142.111’,user=’root’,passwd=’1714****’,db=’dg’)

PostgreSQL

基本用法

# 随机抽取指定列N条数据
# 数据库：webs
# 列名：RUN
# 数据量：1条
SELECT * FROM webs WHERE status='RUN' order by random() limit 1;

# 抽取指定条件数据并排序
# 数据库：webs
# 条件：indexs>0
SELECT * FROM webs WHERE indexs>0 ORDER BY indexs DESC;

代码实例

创建配置文件 database.ini，内容如下

[postgresql]
host=192.168.0.1 # 填写自己的数据库公网或内网地址
database=ttt
user=ttt
password=tttttttttttt

[postgresqltest]
host=192.168.0.1 # 填写自己的数据库公网或内网地址
database=webs
user=webs
password=webswebswebs

创建数据库连接文件 database.py，内容如下

import psycopg2
from configparser import ConfigParser

# 读取配置文件信息
def config(filename="database.ini", section="postgresql"):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception(
            "Section {0} not found in the {1} file".format(section, filename)
        )

    return db

# 查询数据库
def connect(sql, section="postgresql"):
    """Connect to the PostgreSQL database server"""
    conn = None
    try:
        params = config(section=section)
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(sql)
        db_version = cur.fetchall()
        cur.close()
        return db_version
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print("Database connection closed.")

# 修改数据库
def connect_update(sql, section="postgresql"):
    """Connect to the PostgreSQL database server"""
    conn = None
    try:
        params = config(section=section)
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()
        cur.close()
        print("sucess")
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print("Database connection closed.")