首页 > 代码库 > 数据库

数据库

数据库操作

登录数据库

mysql -uroot -p123

显示数据库

show databases;

默认数据库:
  mysql - 用户权限相关数据
  test - 用于用户测试数据
  information_schema - MySQL本身架构相关数据

使用数据库,显示该数据库的table

use db;
show tables;

数据表基本操作

建立表

create table 表名(
    列名  类型  是否可以为空,
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

删表

drop table 表名

清空表

delete from 表名
truncate table 表名

修改表

添加列:alter table 表名 add 列名 类型
删除列:alter table 表名 drop column 列名
修改列:
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名,类型
  
添加主键:
        alter table 表名 add primary key(列名);
删除主键:
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
  
添加外键:alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除外键:alter table 表名 drop foreign key 外键名称
  
修改默认值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

表内容操作

1、增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from

2、删

delete from 表
delete from 表 where id=1 and name=alex

3、改

update 表 set name = alex where id>1

4、查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

5、其他

a、条件
    select * from 表 where id > 1 and name != alex and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like ale%  - ale开头的所有(多个字符串)
    select * from 表 where name like ale_  - ale开头的所有(一个字符)
 
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序
 
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的:group by 必须在where之后,order by之前
 
f、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示,如果B中无对应关系,则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示,如果B中无对应关系,则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
g、组合
    组合,自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合,不处理重合
    select nickname
    from A
    union all
    select name
    from B

 

SqlAlchemy ORM 

SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。

一个简单的完整例子

 1 from sqlalchemy.ext.declarative import declarative_base
 2 from sqlalchemy import Column, Integer, String
 3 from sqlalchemy.orm import sessionmaker
 4 from sqlalchemy import create_engine
 5   
 6 engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11?charset=utf8")
 7   
 8 Base = declarative_base()
 9   
10   
11 class User(Base):
12     __tablename__ = users
13     id = Column(Integer, primary_key=True)
14     name = Column(String(50))
15   
16 # 寻找Base的所有子类,按照子类的结构在数据库中生成对应的数据表信息
17 # Base.metadata.create_all(engine)
18   
19 Session = sessionmaker(bind=engine)
20 session = Session()
21   
22   
23 # ########## 增 ##########
24 # u = User(id=2, name=‘sb‘)
25 # session.add(u)
26 # session.add_all([
27 #     User(id=3, name=‘sb‘),
28 #     User(id=4, name=‘sb‘)
29 # ])
30 # session.commit()
31   
32 # ########## 删除 ##########
33 # session.query(User).filter(User.id > 2).delete()
34 # session.commit()
35   
36 # ########## 修改 ##########
37 # session.query(User).filter(User.id > 2).update({‘cluster_id‘ : 0})
38 # session.commit()
39 # ########## 查 ##########
40 # ret = session.query(User).filter_by(name=‘sb‘).first()
41   
42 # ret = session.query(User).filter_by(name=‘sb‘).all()
43 # print ret
44   
45 # ret = session.query(User).filter(User.name.in_([‘sb‘,‘bb‘])).all()
46 # print ret
47   
48 # ret = session.query(User.name.label(‘name_label‘)).all()
49 # print ret,type(ret)
50   
51 # ret = session.query(User).order_by(User.id).all()
52 # print ret
53   
54 # ret = session.query(User).order_by(User.id)[1:3]
55 # print ret
56 # session.commit()

filter()和filter_by()的区别

filter不支持组合查询,只能连续调用filter来变相实现。
而filter_by的参数是**kwargs,直接支持组合查询。
比如:

q = sess.query(IS).filter(IS.node == node and IS.password == password).all()

对应的sql是

SELECT tb_is.id AS tb_is_id, tb_is.node AS tb_is_node, tb_is.password AS tb_is_password, tb_is.email AS tb_is_email, tb_is.`admin` AS tb_is_admin, tb_is.contact AS tb_is_contact, tb_is.is_available AS tb_is_is_available, tb_is.is_url AS tb_is_is_url, tb_is.note AS tb_is_note 
FROM tb_is 
WHERE tb_is.node = %(node_1)s

and后面的条件既不报错,又不生效。

要实现组合查询,要么连续调用filter:

q = sess.query(IS).filter(IS.node == node).filter(IS.password == password).all()

或者直接用filter_by:

q = sess.query(IS).filter_by(node=node, password=password).all()

两者都对应sql:

SELECT tb_is.id AS tb_is_id, tb_is.node AS tb_is_node, tb_is.password AS tb_is_password, tb_is.email AS tb_is_email, tb_is.`admin` AS tb_is_admin, tb_is.contact AS tb_is_contact, tb_is.is_available AS tb_is_is_available, tb_is.is_url AS tb_is_is_url, tb_is.note AS tb_is_note 
FROM tb_is 
WHERE tb_is.password = %(password_1)s AND tb_is.node = %(node_1)s

外键关联

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
 
engine = create_engine("mysql+pymysql://root:alex3714@localhost/testdb",
                                    encoding=utf-8, echo=True)
 
 
Base = declarative_base() #生成orm基类
 
class User(Base):
    __tablename__ = user #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

class Address(Base):
    __tablename__ = addresses
    id = Column(Integer, primary_key=True)
    email_address = Column(String(32), nullable=False)
    user_id = Column(Integer, ForeignKey(user.id))
 
    user = relationship("User", backref="addresses") #这个nb,允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项
 
    def __repr__(self):
        return "<Address(email_address=‘%s‘)>" % self.email_address
 
Base.metadata.create_all(engine) #创建表结构

表创建好后,我们可以这样反查试试

 

1 obj = Session.query(User).first()
2 for i in obj.addresses: #通过user对象反查关联的addresses记录
3     print(i)
4  
5 addr_obj = Session.query(Address).first()
6 print(addr_obj.user.name)  #在addr_obj里直接查关联的user表

多外键关联

 1 from sqlalchemy import Integer, ForeignKey, String, Column
 2 from sqlalchemy.ext.declarative import declarative_base
 3 from sqlalchemy.orm import relationship
 4  
 5 Base = declarative_base()
 6  
 7 class Customer(Base):
 8     __tablename__ = customer
 9     id = Column(Integer, primary_key=True)
10     name = Column(String)
11  
12     billing_address_id = Column(Integer, ForeignKey("address.id"))
13     shipping_address_id = Column(Integer, ForeignKey("address.id"))
14  
15     billing_address = relationship("Address", foreign_keys=[billing_address_id])
16     shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
17  
18 class Address(Base):
19     __tablename__ = address
20     id = Column(Integer, primary_key=True)
21     street = Column(String)
22     city = Column(String)
23     state = Column(String)

多对多关系

http://www.cnblogs.com/alex3714/articles/5978329.html

 

数据库