mysql 常用语句

1、查询表的所有字段

select COLUMN_NAME,data_type, column_comment from information_schema.COLUMNS where table_name = 'roles_user' and table_schema = 'cmdb';

 

 

2、查询没有主键的表

SELECT
    a.TABLE_SCHEMA,
    a.TABLE_NAME 
FROM
    (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME 
    FROM
        information_schema.TABLES 
    WHERE
    TABLE_SCHEMA IN ( 'cmdb', 'mirror', 'deploy_proxy', 'index_proxy' )) AS a
    LEFT JOIN (
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME 
    FROM
        information_schema.TABLE_CONSTRAINTS 
    WHERE
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND TABLE_SCHEMA IN ( 'cmdb', 'mirror', 'deploy_proxy', 'index_proxy' )) AS b 
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA 
    AND a.TABLE_NAME = b.TABLE_NAME 
WHERE
    b.TABLE_NAME IS NULL;

 

 

3、创建联合主键

ALTER TABLE table_name ADD PRIMARY KEY(字段A，字段B，字段C)；

 

 

4、mysql登录

mysql -u root -h 10.12.1.110 -P3306 -p