今天突然遇到有人要数据库表及表字段说明，数据库表太多又不能一个个表去找，就想想SQL是否能直接查询出来。
经过查询资料，加上一些自己的一些调整写了一个sql语句，在此记录一下，以方便日后查找使用。
SELECT  ( CASE WHEN a.colorder = 1 THEN d.name
ELSE ‘‘
END ) N‘表名‘ ,
( CASE WHEN a.colorder = 1
THEN ISNULL(( SELECT TOP 1 value FROM   sys.extended_properties WHERE  major_id = OBJECT_ID(d.name) ), ‘‘)
ELSE ‘‘
END ) N‘表说明‘ ,
a.colorder N‘字段序号‘ ,
a.name N‘字段名‘ ,
( CASE WHEN COLUMNPROPERTY(a.id, a.name, ‘IsIdentity‘) = 1 THEN ‘√‘
ELSE ‘‘
END ) N‘标识‘ ,
( CASE WHEN ( SELECT    COUNT(*)
FROM      sysobjects
WHERE     name IN (
SELECT  name
FROM    sysindexes
WHERE   id = a.id
AND indid IN (
SELECT  indid
FROM    sysindexkeys
WHERE   id = a.id
AND colid IN ( SELECT  colid FROM syscolumns WHERE id = a.id AND name = a.name )
)
)
AND xtype = ‘PK‘
) > 0 THEN ‘√‘
ELSE ‘‘
END ) N‘主键‘ ,
b.name N‘类型‘ ,
a.length N‘占用字节数‘ ,
COLUMNPROPERTY(a.id, a.name, ‘PRECISION‘) AS N‘长度‘ ,
ISNULL(COLUMNPROPERTY(a.id, a.name, ‘Scale‘), 0) AS N‘小数位数‘ ,
( CASE WHEN a.isnullable = 1 THEN ‘√‘
ELSE ‘‘
END ) N‘允许空‘ ,
ISNULL(e.text, ‘‘) N‘默认值‘ ,
ISNULL(g.[value], ‘‘) AS N‘字段说明‘
FROM    sys.syscolumns a
LEFT JOIN sys.systypes b ON a.xtype = b.xusertype
INNER JOIN sys.sysobjects d ON a.id = d.id
AND d.xtype = ‘U‘
AND d.name <> ‘dtproperties‘
LEFT JOIN sys.syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
ORDER BY OBJECT_NAME(a.id) ,
a.colorder;

场景：
1. 要查询数据库 "mammothcode" 下所有表名以及表注释
/* 查询数据库 ‘mammothcode’ 所有表注释 */
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema=‘mammothcode‘;
2. 要查询表字段的注释
/* 查询数据库 ‘mammothcode’ 下表 ‘t_adminuser’ 所有字段注释 */
SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name=‘t_adminuser‘ AND table_schema=‘mammothcode‘
3. 一次性查询数据库 "mammothcode" 下表注释以及对应表字段注释
SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`=‘mammothcode‘
扩展：使用存储过程，对上面sql语句进行存储，sql如下：
DELIMITER//
DROP PROCEDURE IF EXISTS findComment//
CREATE PROCEDURE findComment (dbName VARCHAR(50))
BEGIN
DECLARE stmt VARCHAR(65535);
#如果用户名长度大于0
IF LENGTH(dbName)>0 THEN
BEGIN
SET @sqlstr=CONCAT(‘SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`=‘,‘‘‘‘,dbName,‘‘‘‘);
END;
ELSE
BEGIN
SET @sqlstr=CONCAT(‘SELECT ‘,‘‘‘‘,‘数据库名不能为空‘,‘‘‘‘, ‘AS ‘,‘‘‘‘,‘提示‘,‘‘‘‘);
END;
END IF;
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
END//
DELIMITER ;
调用存储过程：
CALL findComment(‘xiyinli_test‘);
上面的存储过程还可以简化：在存储过程中直接查询当前使用的数据库，如下：
DELIMITER//
DROP PROCEDURE IF EXISTS findComment//
CREATE PROCEDURE findComment ()
BEGIN
DECLARE stmt VARCHAR(65535);
#查询当前的 use-->database
SET @dbName=(SELECT DATABASE());
BEGIN
SET @sqlstr=CONCAT(‘SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`=‘,‘‘‘‘,@dbName,‘‘‘‘);
END;
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
END//
DELIMITER ;
调用：
CALL findComment();

根据java类中的属性建Hibernate映射文件映射到mysql数据库中，有些属性、映射类型拿不准，就顺便查查资料总结了一下有关知识：
                                                        Hibernate-Java-MySql
Hibernate映射类型
Java字段(属性)类型
MySql字段类型
integer 或者 int
或者 java.lang.Integer
INTEGER
long
long  Long
BIGINT
shortt
short  Short
SMALLINT
byte
byte  Byte
TINYINT
float
float  Float
FLOAT
double
double  Double
DOUBLE
big_decimal
java.math.BigDecimal
NUMERIC
character
char  Character  String
CHAR(1)
string
String
VARCHAR
boolean
boolean  Boolean
BIT
yes_no
boolean  Boolean
CHAR(1) (Y-N)
true_false
boolean  Boolean
CHAR(1) (T-F)
timestamp
Date
Date
date
util.Date 或者 sql.Date
Date
time
util.Date/java.sql.TIme
TIME
如下代码以及映射：
/**
 * java代码

 */
 public class Test
{

   private Long id;


    private Integer state;

    

    private String startMemberId;


    private Date startDate;


    private String approveMemberId;
    private Date approveDate;


    private Integer finishedflag;


    private Integer ratifyflag;
    private String ratifyMemberId;


    private Date ratifyDate;


    private Integer sort;


    private String modifyMemberId;


    private Date modifyDate;
    /**
     *省略get和set方法

      *//

}

<id name="id" type="long" column="id" length="20">

            <generator class="assigned" /><!-- 用户自定义id -->

        </id>

        <property name="state" column="state" type="integer"></property>

        <property name="startMemberId" column="" type="string"></property>

        <property name="startDate" type="timestamp">

            <column name="start_member_id"/>

        </property>

        <property name="approveMemberId" column="approve_member_id" type="string"></property>

        <property name="approveDate" type="timestamp">

            <column name="approve_date"></column>

        </property>

        <property name="finishedflag" column="finishedflag" type="integer"></property>

        <property name="ratifyflag" column="ratifyflag" type="integer"></property>

        <property name="ratifyMemberId" column="ratifyMemberId" type="string"></property>

        <property name="ratifyDate" type="timestamp">

            <column name="ratify_date"></column>

        </property>

        <property name="sort" column="sort" type="integer"></property>

        <property name="modifyMemberId" column="modify_member_id" type="string"></property>

        <property name="modifyDate" type="timestamp">

            <column name="modify_date"></column>

        </property>

1.数据类型
数据类型

使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
常用数据类型如下：

    整数：int，bit
    小数：decimal
    字符串：varchar,char
    日期时间: date, time, datetime
    枚举类型(enum)


特别说明的类型如下：

decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位

char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '

varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'

字符串text表示存储大文本，当字符大于4000时推荐使用

对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

2.约束

主键primary key：物理上存储的顺序
自增auto_increment：表示自动增长（一般主键使用）
非空not null：此字段不允许填写空值
惟一unique：此字段的值不允许重复
默认default：当不填写此值时会使用默认值，如果填写时以填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制
2.1 数值类型(常用)

2.2 字符串

2.3 日期时间类型

3.数据库里面数据表操作（前提：use 数据库名）

3.1查看当前数据库中所有表

show tables;


3.2 查看表结构

desc 表名;


3.3 创建表

auto_increment表示自动增长

CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
);

例：创建班级表
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(10)
);

例：创建学生表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','人妖','保密'),
    cls_id int unsigned default 0
)


3.4 修改表-添加字段

alter table 表名 add 列名 类型;

例：
alter table students add birthday datetime;


3.5 修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;

例：
alter table students change birthday birth datetime not null;


3.6 修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;

例：
alter table students modify birth date not null;


3.7 修改表-删除字段

alter table 表名 drop 列名;

例：
alter table students drop birthday;


3.8 删除表

drop table 表名;

例：
drop table students;


3.9 查看表的创建语句

show create table 表名;

例：
show create table classes;

4. 数据表增删改查操作
curd的解释: 代表创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）
4.1 查询基本使用

查询所有列

select * from 表名;
# 例：
select * from student;


查询指定列,可以使用as为列或表指定别名(支持中文)

select 列1,列2 from 表名;
例：
select id,name from student; 
select id as 编号,name as 姓名 from student; 

4.2 增加
说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准

全列插入：值的顺序与表中字段的顺序对应

insert into 表名 values(字段1对应value,字段2对应value,...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');


部分列插入：值的顺序与给出的列顺序对应

insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');

上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信

全列多行插入：值的顺序与给出的列顺序对应

insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('杨康'),('杨过'),('小龙女');

4.3 修改
update 表名 set 列1=值1,列2=值2... where 条件
例：
update students set gender=0,hometown='北京' where id=5;

4.4 删除
delete from 表名 where 条件
例：
delete from students where id=5;


逻辑删除，本质就是修改操作,大部分场合都考虑逻辑删除而非完全删除数据记录。

update students set isdelete=1 where id=1;