Mysql回顾

mysql -uroot -p123456 -P3306 -h127.0.0.1;

mysql -uroot -p123456 -P3306 -h127.0.0.1 -e"show databases"; //直接执行SQL语句

select user, host from mysql.user;

create user test@'%' identified by 'password';

create user test@localhost identified by '123456';

drop user test@localhost;

rename user test@localhost to te@loalhost;

update mysql.user set password = password('newpass') where user='test' and host = 'localhost';

grant all privileges on *.* to 'test'@'localhost' identified by 'password';

flush privileges;

create database if not exists test character set utf8;

show databases;

use test;

drop database if exists test;

alter database test character set utf8;

create table if not exists tb (

id char(10) not null primary key,

type varchar(20) not null default 'compute',

publicdata date not null,

num int(5),

price float(5)

) engine=InnoDB;

show tables;

alter table tb add column visit int(10) not null default 0, drop column publicdate;

drop table if exists tb;

describe tb;

delete from tb;

insert into tb (id, type, num, price, visit) values ('12346', 'math', 20, 30.5, 500);

update tb set type = 'compute', visit = 1000 where id = '12346'; //单表修改

update tb, sb set tb.visit = 4, sb.visit = 4 where tb.id = sb.id and tb.id = '123'; //多表修改

delete from tb where id = '12345';

select id as 编号, type as 类型 from tb where visit > 500 and type like 'c_m%' and num between 50 and 100 and price in (30.5, 30.2) and num is not null order by id desc limit 5;

select id, type,

case

when num is null then '卖光'

when num < 20 then '需进货'

when num >=20 and num < 100 then '正常'

when num >= 100 then '积压'

end as '库存'

from tb;

select type, sum(num) from tb group by type having sum(num) > 200;

select book.name, sell.num from book inner join sell on (book.id = sell.id);

select book.name, sell.num from book left outer join sell on (book.id = sell.id);

select book.name, sell.num from book right outer join sell on (book.id = sell.id);

select * from sell where id in (select id from book where visit > 100);

#! /usr/bin/python3
import pymysql


def get_connection():
    conn = pymysql.connect(
        host="xx.xx.xx.xx",
        user="root",
        passwd="password",
        db="test",
        port=3306,
        charset="utf8",
    )
    csr = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return conn, csr


def close_connection(conn, csr):
    if csr:
        csr.close()
    if conn:
        conn.close()


def query_sql(sql, params=None):
    result = None
    conn, csr = get_connection()
    try:
        csr.execute(sql, params)
        result = csr.fetchall()
    except Exception as e:
        print(str(e))
    finally:
        close_connection(conn, csr)
        return result


def update_sql(sql, params=None):
    result = None
    conn, csr = get_connection()
    try:
        result = csr.execute(sql, params)
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(str(e))
    finally:
        close_connection(conn, csr)
        return result


if __name__ == "__main__":
    # ex_sql = "insert into tb (id, type, num, price, visit) values (%s, %s, %s, %s, %s )"
    # res = update_sql(ex_sql, ("12348", "english", 50, 38.5, 1200))
    # print(res)
    ex_sql = "select * from tb where visit > %s"
    res = query_sql(ex_sql, (500,))
    for r in res:
        print(r["id"], r["type"])

#! /usr/bin/python3
import pymysql
from dbutils.pooled_db import PooledDB


class DB(object):
    __pool = None
    def __init__(self):
        self.pool = DB.__get_conn_pool()
    @staticmethod
    def __get_conn_pool():
        if DB.__pool is None:
            DB.__pool = PooledDB(
                creator=pymysql,
                host="xx.xx.xx.xx",
                user="root",
                passwd="password",
                db="test",
                port=3306,
                charset="utf8",
                maxconnections=10240,
            )
        return DB.__pool
    def _get_connection(self):
        conn = self.pool.connection()
        csr = conn.cursor(cursor=pymysql.cursors.DictCursor)
        return conn, csr
    def _close_connection(self, conn, csr):
        if csr:
            csr.close()
        if conn:
            conn.close()
    def query_sql(self, sql, params=None):
        result = None
        conn, csr = self._get_connection()
        try:
            csr.execute(sql, params)
            result = csr.fetchall()
        except Exception as e:
            print(str(e))
        finally:
            self._close_connection(conn, csr)
            return result
    def update_sql(self, sql, params=None):
        result = None
        conn, csr = self._get_connection()
        try:
            result = csr.execute(sql, params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(str(e))
        finally:
            self._close_connection(conn, csr)
            return result


if __name__ == "__main__":
    db = DB()
    # ex_sql = "insert into tb (id, type, num, price, visit) values (%s, %s, %s, %s, %s )"
    # res = db.update_sql(ex_sql, ("12348", "english", 50, 38.5, 1200))
    # print(res)
    ex_sql = "select * from tb where visit > %s"
    res = db.query_sql(ex_sql, (500,))
    for r in res:
        print(r['id'], r['type'])