【Mysql】之基础sql语句模板

==============新建数据库============
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