==============新建数据库============ create database if not exists XXX; ==============删除数据库============ drop database if exists XXX; ==============新建表================ create table if not exists z_class( z_id int UNSIGNED auto_increment, z_name VARCHAR(100) NOT NULL, z_age INT NOT NULL, z_date date, PRIMARY KEY(z_id) )ENGINE=INNODB DEFAULT charset=utf8; ==============插入数据============= insert into XXX(,,,)values (,,,), (,,,), (,,,); ==============复制数据============= create table XXX2 like XXX1; 复制表结构 insert into XXX2 select * from XXX1; 复制数据 ==============删除数据============= delete from XXX where id=y; ============删除全部数据=========== delete from XXX; ============排序删除数据=========== delete from XXX order by xxx limit 2; ==============更新数据============= update XXX set xxx = y where id = z; ============多表更新数据=========== update XXX1 set x01 =( select y01 from XXX2 where y02 = 'y' order by rand() limit 1 ) where x02 is null; ===========修改表结构属性========== alter table XXX1 change column xxx xxx int(10) not null; ===========添加表结构属性========== alter table XXX1 add column info varchar(10) null after xxx01; ===========删除表结构属性========== alter table XXX1 drop column xxx01; ===========修改表名称属性========== alter table XXX1 rename to XXX2; 或者 rename table XXX1 to XXX2; ==============查询数据============= select * from XXX1 where xxx01 <= 3; 模糊查询: select * from XXX1 where xxx01 like '%o' 排序: select * from XXX1 order by xxx01 select * from XXX1 order by xxx01 desc 聚合: select sum(xxx01),xxx02 from XXX1 group by xxx02 平均值: select avg(xxx01) from XXX1 合计: select sum(xxx01) from XXX1 最大: select max(xxx01) from XXX1 最小: select min(xxx01) from XXX1 合计总行数: select count(xxx01) from XXX1 分组查询sal字段的最大值:id,sal,num select max(sal),num from XXX1 group by num 查询之间的数: select * from XXX1 where between 1 and 3 查询指定行的数据: select * from XXX1 limit 3,2 不包含第三行,向下查询2行(4,5行) 查询表结构: desc XXX1 创建视图: create view st as select * from XXX1 where xxx01 = 'x' 删除视图: drop view st