Mysql的常用命令

-- DDL

    -- 创建数据库(如果不存在则发生错误)

CREATE DATABASE testdb;

    -- 创建数据库(不存在则创建，存在也不报错)

CREATE DATABASE IF EXISTS testdb;

    -- 删除数据库(如果不存在则发生错误)

DROP DATABASE testdb;

    -- 删除数据库(存在则删除，不存在也不报错)

DROP DATABASE IF EXISTS testdb;

    -- 连接数据库

USE testdb;

    -- 创建表

 CREATE TABLE IF NOT EXISTS sys_user
    (
        id INT NOT NULL AUTO_INCREMENT COMMENT 'id',
        username VARCHAR(255) NOT NULL COMMENT '用户名',
        password VARCHAR(255) NOT NULL COMMENT'密码',
        age INT NOT NULL COMMENT '年龄',
        sex INT DEFAULT '1' COMMENT '状态：1男,0女',
        create_time DATETIME NOT NULL comment'创建时间',
        update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

 -- 查看表详细

SHOW FULL fields FROM sys_user;

 -- 删除表

DROP TABLE IF EXISTS sys_user; 

    -- 修改表名

ALTER TABLE test RENAME TO sys_user;

    -- 修改表注释

ALTER TABLE sys_user COMMENT '用户信息表';

    -- 修改字段类型

ALTER TABLE sys_user MODIFY COLUMN field_name VARCHAR(255) COMMENT '修改字段类型';

    -- 修改字段名(需修改字段类型)

ALTER TABLE sys_user CHANGE name name_new VARCHAR(255) NOT NULL;

    -- 新增字段

ALTER TABLE sys_user ADD field_new VARCHAR(255) NOT NULL COMMENT '新增字段';

   -- 新增字段(指定位置)

ALTER TABLE sys_user ADD field_new INT NOT NULL COMMENT '新增字段，指定位置' AFTER sex;

   -- 删除字段   

ALTER TABLE sys_user DROP field_name;

    -- 指定在某个字段后新增字段(在id字段后)   

 ALTER TABLE sys_user ADD COLUMN name_new INT NOT NULL DEFAULT '1' AFTER id; 

    -- 新增主键  

  ALTER TABLE sys_user ADD id INT(11) NOT NULL , ADD PRIMARY KEY (id);

    -- 新增自增主键    

ALTER TABLE sys_user ADD id INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (id);

    -- 修改为自增主键   

 ALTER TABLE sys_user MODIFY COLUMN id INT(11) AUTO_INCREMENT ;

    -- 调整字段顺序    

ALTER TABLE sys_user CHANGE field_name field_name INT NOT NULL AFTER id;

-- DML 

    -- 插入数据    

INSERT INTO sys_user(username,password,age,sex,create_time) VALUES('KEVIN','1qaz@WSX',23,1,'2019-08-24 00:00:00');

   -- 插入多条数据    

INSERT INTO sys_user(username,password,age,sex,create_time) VALUES
    ('KEVIN','1qaz@WSX',23,1,'2019-08-24 00:00:00'),
    ('MR.CAO','1qaz@WSX',23,1,'2019-08-24 00:00:00');

   -- 查询数据   

 SELECT * FROM sys_user;

    -- 查询前十条数据    

SELECT * FROM sys_user limit 1,10;

    -- 去重

SELECT DISTINCT username,password FROM sys_user;

    -- 统计

SELECT count(*) FROM sys_user;

    -- 删除数据    

SELECT count(*) FROM sys_user;

    -- 清空数据

TRUNCATE FROM sys_user;

    -- 更新数据

UPDATE sys_user SET age = 22 WHERE username = 'KEVIN';

    -- 单表更新多条数据

UPDATE sys_user SET age = 22 and username = 'CAO' WHERE id = 1;

    -- 多表更新数据

UPDATE sys_user u, sys_dept d SET u.age = 23 AND d.dept_name = '开发部' WHERE u.id = 1 AND u.id = d.user_id;

    -- 排序(默认是升序，ASC)

SELECT id,username,password,age,sex,create_time FROM sys_user ORDER BY create_time ;

    -- 排序(降序，DESC)

SELECT id,username,password,age,sex,create_time FROM sys_user ORDER BY create_time DESC;

    -- 分组(查看男女各有多少人)

SELECT count(username),sex FROM sys_user GROUP BY sex;

    -- 判断是男还是女

SELECT username, CASE WHEN sex = 1 THEN '男' ELSE '女' END AS sex FROM sys_user ;

    -- 模糊查询(匹配所有姓曹的名字)

SELECT * FROM sys_user WHERE username LIKE '曹%';

    -- 连接结果集(去除重复的)，列数，类型需要一致

SELECT username,pasword    FROM sys_user WHERE username='KEVIN' 
UNION 
SELECT username,pasword    FROM sys_user WHERE username='Mr.Cao' ;

    -- 连接结果集(包含重复的)   

SELECT username,pasword    FROM sys_user WHERE username='KEVIN' 
UNION ALL
SELECT username,pasword    FROM sys_user WHERE username='Mr.Cao' ;

    -- IS NULL 判断名字为NULL    

SELECT * FROM sys_user WHERE username IS NULL ;

    -- IS NOT NULL 判断名字不为NULL   

 SELECT * FROM sys_user WHERE username IS NOT NULL ;

-- 连接查询

   -- 左连接(获取左表所有记录，即使右表没有对应匹配的记录)    

SELECT u.id,u.username,u.password,d.dept_name FROM sys_user u 
LEFT JOIN sys_dept d
ON u.dept_id = d.id
WHERE u.id = 1;

   -- 右连接(获取右表所有记录，即使左表没有对应匹配的记录)

SELECT u.id,u.username,u.password,d.dept_name FROM sys_user u 
RIGHT JOIN sys_dept d
ON u.dept_id = d.id
WHERE u.id = 1;    

    -- 内连接(获取两个表中字段匹配关系的记录)

SELECT u.id,u.username,u.password,d.dept_name FROM sys_user u 
INNER JOIN sys_dept d
ON u.dept_id = d.id
WHERE u.id = 1;

 -- 临时表    

    -- 创建临时表

CREATE TEMPORARY TABLE sys_temp
    (
        username VARCHAR(255) NOT NULL COMMENT '用户名',
        password VARCHAR(255) NOT NULL COMMENT'密码',
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='临时表';

    -- 删除临时表   

DROP TABLE sys_temp ;

-- 事务

    -- 提交事务    

BEGIN ; -- 开启事务
    INSERT INTO sys_user(username,password,age,sex,create_time) VALUES('KEVIN','1qaz@WSX',23,1,'2019-08-24 00:00:00');
COMMIT ; -- 提交事务

    -- 回滚事务    

BEGIN ; -- 开启事务
    INSERT INTO sys_user(username,password,age,sex,create_time) VALUES('KEVIN','1qaz@WSX',23,1,'2019-08-24 00:00:00');
ROLLBACK ; -- 回滚

    -- 禁止自动提交    

SET AUTOCOMMIT = 0; 

   -- 开启自动提交   

SET AUTOCOMMIT = 1; 

-- 约束(一般在建表时添加)

    -- 添加主键约束(PK_ID：自定义的约束名)(主键不能为空，不能重复，只能有一个主键)   

 ALTER TABLE sys_user ADD CONSTRAINT PK_ID PRIMARY KEY(id);

  -- 删除主键约束(如果主键有自增长，则先删除自增长)    

ALTER TABLE sys_user DROP PRIMARY KEY;

    -- 删除主键的自增长

ALTER TABLE sys_user CHANGE id id INT;

   -- 添加唯一约束(主键不能使用唯一约束，数据可以有null，但不能重复)    

ALTER TABLE sys_user ADD CONSTRAINT un_username UNIQUE(username);

    -- 删除唯一约束    

ALTER TABLE sys_user DROP INDEX un_username;

   -- 检查约束(age必须要大于0)

ALTER TABLE sys_user ADD CONSTRAINT check_age CHECK(age>0);

    -- 默认值约束    

ALTER TABLE sys_user MODIFY sex INT(11) DEFAULT 1;

    -- 外键约束(表可以有多个外键，写的时候，需要在父表找到对应的数据)

ALTER TABLE sys_user ADD CONSTRAINT fk_id FOREIGN KEY(dept_id) REFERENCES sys_dept(id);

    
-- 索引

    -- 普通索引(给name添加普通索引)

ALTER TABLE sys_user ADD INDEX NAME_INDEX(username);

    -- 全文索引(USERNAME_FULL：自定义的索引名)

ALTER TABLE sys_user ADD FULLTEXT USERNAME_FULL(username);

    -- 唯一索引(USERNAME_UNIQUE：自定义的索引名)

 ALTER TABLE sys_user ADD UNIQUE USERNAME_UNIQUE(username)；

    -- 删除索引(NAME_INDEX：自定义的索引名)

 ALTER TABLE sys_user DROP INDEX NAME_INDEX;

-- 其余

    -- 获取表字段的长度、类型、名字    

SELECT DISTINCT column_name,data_type,character_maximum_length FROM information_schema.columns
WHERE table_name = 'fmdes_user';

    -- 获取指定表的字段长度，是否主键，并插入到对应表中    

UPDATE 
        fmdes_test.fmdes_width_table_field a ,
    (
        SELECT 
            table_name,
            field,
            character_maximum_length
        FROM fmdes_test.fmdes_width_table_field f
        LEFT JOIN (
            -- 获取表字段名称，长度
            SELECT DISTINCT column_name,character_maximum_length FROM information_schema.columns
            WHERE table_name = 'dw_app_shopkeeper'
        ) s
        ON f.field = s.column_name
        WHERE f.table_name = 'dw_app_shopkeeper'
    ) b
    SET a.field_length = b.character_maximum_length
    WHERE a.field = b.field AND a.table_name = b.table_name;