建表建库语句

- 查询有多少库show DATABASES;-- 创建库create database mm;-- 删除库drop database mm;-- 使用库use mm;-- 显示表show tables;-- 删除表drop table suser;-- 创建表create TABLE suser (id BIGINT(20) not NULL,name varchar(50) default NULL comment '姓名',sex VARCHAR(5) default '男' comment '性别',primary KEY(id),UNIQUE KEY (name) USING BTREE,KEY (sex) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8 comment '客户';-- 看表结构desc suser;-- 插入语句insert into suser (id,NAME,sex) VALUES (1,'hah','男');-- 修改UPDATE suser set name = '哈' WHERE id=1;-- 查询SELECT * FROM suser;-- 删除列alter TABLE suser DROP id;-- 增加列alter TABLE suser add id BIGINT(20) not null primary key  COMMENT '主键ID';-- 修改列的信息ALTER TABLE suser CHANGE id id BIGINT(2) COMMENT '主键ID' ;-- 重命名表名alter TABLE user RENAME suser;-- 删除主键ALTER TABLE suser drop PRIMARY key;-- 增加主键ALTER TABLE suser add PRIMARY KEY(id);-- 删除索引alter table suser drop INDEX 姓名;-- 增加索引ALTER TABLE suser add index 性别 (sex);-- 增加唯一约束ALTER TABLE suser add unique index 姓名(name);-- 创建联合索引CREATE INDEX aa on suser(sex,name);-- 查询字段长度为6的数据select * from siteinfo where LENGTH(ORG_ID)=6-- 子查询错误UPDATE siteinfo set ORG_ID = CONCAT('0',ORG_ID) WHERE ORG_ID in (select ORG_ID from siteinfo where LENGTH(ORG_ID)=6)-- 正确UPDATE siteinfo a INNER JOIN siteinfo b on  LENGTH(b.ORG_ID)=6 and a.biz_id = b.biz_id set a.ORG_ID = CONCAT('0',a.ORG_ID)//不需子查询UPDATE siteinfo set ORG_ID = CONCAT('0',ORG_ID) WHERE  LENGTH(ORG_ID)=6;-- 第一个表有而第二个表没有SELECT DISTINCT(count_id) FROM barcode t1 left JOIN barcodecount t2 on t1.COUNT_ID = t2.ID   WHERE t2.id is NULL;