字符串
left(str,位数)# 截取str左边n个字符
= # 是否相等
转换值
select
年龄 as 'age',
# 修改当前列的值
case 姓名
where '王' then 'wang'
where '李' then 'li'
else 'dogn'
end
as 'name',
# 新增一列
case
where age<20 and age>10 then '青少年'
where arg>30 then '中年'
end
as '状态',
from biao;
查看外键信息
根据该表查该表的外键信息:连同正常的索引一并展示
根据学生表查用了哪些表的哪些字段做外键,如:老师表的老师id,班级表的班级id
# 查看某个表的外键名称,外键字段,关联的表,关联的表的字段
SELECT constraint_name,column_name, referenced_table_name, referenced_column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE constraint_schema = 'db_name' AND table_name = 'biao_name';
根据该表查该表字段关联到哪些表的哪些字段
根据班级表查哪些表使用了班级的字段(如:id),如:老师表,学生表
# 查看关联表名,关联表的字段,关联表字段的外键名,该表的被关联字段(班级id)
select
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_SCHEMA ='db_name' AND
REFERENCED_TABLE_NAME = 'biao_name';
获取数据库中的数据字典
select
t.table_name as '表名',
t.column_name as '字段名',
t.column_type as '字段类型',
IF (t.COLUMN_DEFAULT = '', '空字符串', IFNULL( t.COLUMN_DEFAULT, '' ) ) AS '默认值',
CASE t.IS_NULLABLE
WHEN 'YES' THEN '是'
ELSE '否'
END
AS '是否允许为空',
t.COLUMN_COMMENT AS '字段备注'
FROM
information_schema.COLUMNS t
where table_schema='db_name';
获取数据库中所有表的索引信息
此时是在同一数据库下的所有表
select kcu.table_name '表名',kcu.constraint_name '索引名' ,kcu.column_name '字段',
case c.column_key
when 'PRI' then 'primary'
when 'UNI' then 'unique'
when 'MUL' and left(kcu.table_name,3)='fk_' then 'foreignKey_normal'
when 'MUL' and not left(kcu.table_name,3)='fk_' then 'normal'
end
as '索引类型'
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu left join INFORMATION_SCHEMA.COLUMNS c on kcu.TABLE_NAME=c.table_name and kcu.COLUMN_NAME=c.COLUMN_NAME
where kcu.TABLE_SCHEMA='db_name' #指定某个数据库,可以不指定
order by kcu.table_name;