获取表的创建语句:
SHOW CREATE TABLE stu //表创建SQL
SHOW CREATE DATABASE websocket //数据库创建SQL
创建表:
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT 'xiaoxiao' COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生'
SHOW TABLE STATUS //详细信息
SHOW TABLES //得到所有的表名
描述表结构
SHOW FULL FIELDS FROM chat_msg //全部结构
DESC chat_msg //基本结构
复制表结构
CREATE TABLE wdgj_order_backup
LIKE wdgj_order
CREATE TABLE b SELECT * FROM users LIMIT 0
得到索引
SHOW INDEX FROM chat_msg
插入查询数据
INSERT INTO wdgj_order_backup2015 SELECT * FROM wdgj_order WHERE (state=-2 OR state=8) AND create_time<='2015-11-09 12:34:34'
INSERT INTO wdgj_order_goods_backup2015 SELECT d.* FROM wdgj_order_goods d ,(SELECT id FROM wdgj_order WHERE (state=-2 OR state=8) AND create_time<='2015-11-09 12:34:34') AS o WHERE o.id=d.order_id
删除查询数据
DELETE FROM wdgj_order WHERE (state=-2 OR state=8) AND create_time<='2015-11-09 12:34:34'
DELETE d FROM wdgj_order_goods d ,(SELECT id FROM wdgj_order WHERE (state=-2 OR state=8) AND create_time<='2015-11-09 12:34:34') AS o WHERE o.id=d.order_id
EXTSTS
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
SELECT * FROM wdgj_original_order_goods d WHERE EXISTS(SELECT id FROM wdgj_original_order o WHERE create_time<='2015-12-19 12:34:34' and d.original_order_id=o.id)
SELECT d.* FROM wdgj_original_order_goods d,(SELECT id FROM wdgj_original_order WHERE create_time<='2015-12-19 12:34:34') AS o WHERE o.id=d.original_order_id
此二者等效,但是后者的效率更高一点。
改变表结构:
1:删除列
ALTER TABLE 【表名字】 DROP 【列名称】
2:增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT '注释说明'
3:修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称(这里可以用和原来列同名即可)】 BIGINT NOT NULL COMMENT '注释说明'
4:重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT '注释说明'
5:重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
6:删除表中主键
Alter TABLE 【表名字】 drop primary key
7:添加主键
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
8:添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
9: 添加唯一限制条件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
10: 删除索引
alter table tablename drop index emp_name;