问题：sqlserver如何查看表注释？

方法：

SELECT
A.name AS 表名,
B.name AS 字段名,
C.value AS 字段说明
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
WHERE A.name = '表名'

查询所有字段，可查视图

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='表名/视图名'

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_OCTET_LENGTH
FROM
    information_schema.COLUMNS
WHERE
    TABLE_NAME = '表名/视图名'

阅读全文

问题：如何查看oracle表结构、视图结构？

方法：

表及字段信息

SELECT * FROM cols WHERE TABLE_NAME = 'xxx';

字段注释

SELECT * FROM user_col_comments WHERE TABLE_NAME = 'xxx';

SELECT * FROM all_col_comments WHERE TABLE_NAME = 'xxx';

表/视图名称及说明

SELECT * FROM user_tab_comments WHERE TABLE_NAME = 'xxx';

SELECT * FROM all_tab_cols WHERE table_name ='LBORGANIZATION' and owner='USR_RSXT';

阅读全文

问题：如何通过jdbc连接oracle？

解决：jdbc连接方式有三种，使用正确方式才能连上

方法：
1、jdbc:oracle:thin:@host:port:SID

jdbc:oracle:thin:@localhost:1521:orcl

2、jdbc:oracle:thin:@//host:port/service_name 推荐

jdbc:oracle:thin:@//localhost:1521/orcl.city.com

3、jdbc:oracle:thin:@TNSName

jdbc:oracle:thin:@TNS_ALIAS_NAME

阅读全文

问题：sqlalchemy的outerjoin外表时需要添加条件，使用filter过滤会到where中，而不是在on语句中

解决：使用and_

方法：

offset_num = (page - 1) * pagesize
q = select(
    User.uid, User.username, User.fullname, User.national_id, User.phone,
    User.gender, User.last_login, User.status, User.create_time,
    func.group_concat(UserRole.role_id).label('role_ids')).outerjoin(
    UserRole, and_(User.uid == UserRole.user_uid,
    UserRole.is_delete == obj_get['is_delete'])).filter(
    User.is_delete == obj_get['is_delete']).group_by(
    User.uid).offset(offset_num).limit(pagesize)
r = await db.execute(q)
return r.all()

阅读全文

问题：sqlalchemy.exc.ArgumentError: Mapper mapped class UserTotal->user_total could not assemble any primary key columns for mapped

解决：sqlalchemy的model不能没有primary_key

方法：

class UserTotal(Base):
    __tablename__ = "user_total"

    dt = Column(Date, nullable=False)
    recent_days = Column(Integer, nullable=False)
    login_num_count = Column(BigInteger)
    page_count = Column(BigInteger)

    __mapper_args__ = { 
        'primary_key': [dt, recent_days]
    }