1、创建数据库、查看数据库、切换数据库
create database dbname;
show databases;
use dbname;
2、删除数据库
drop database dbname;
3、创建表
create table student (
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
根据已有的表创建新表:
A:create
table
tab_new
like
tab_old (使用旧表创建新表)
B:
create
table
tab_new
as
select
col1,col2…
from
tab_old definition
only
4、删除新表
drop
table
tabname
5、增加一个列、修改列
、删除列
alter
table
tabname
add
column
col type
例:alter table user(表名) add column name(新增字段名) varchar(20) null default null comment '姓名' after age(字段名);
例:alter table user(表名) modify column name(字段名) varchar(50) not NULL default ‘’ comment '姓名' after age(字段名);
例:alter table user(表名) drop column name(字段名) ;
6、添加主键、删除主键
alter
table
tabname
add
primary
key
(col);
alter
table
tabname
drop
primary
key
(col);
7、创建索引、删除索引
create
[
unique
]
index
idxname
on
tabname(col….)
drop
index
idxname
8、创建视图、删除视图
create
view
viewname
as
select
statement
drop
view
viewname
9、基本sql语句
选择:
select
*
from
table1
where
范围
插入:
insert
into
table1(field1,field2)
values
(value1,value2)
删除:
delete
from
table1
where
范围
更新:
update
table1
set
field1=value1
where
范围
查找:
select
*
from
table1
where
field1
like
’%value1%’
排序:
select
*
from
table1
order
by
field1,field2 [
desc
]
总数:
select
count
as
totalcount
from
table1
求和:
select
sum
(field1)
as
sumvalue
from
table1
平均:
select
avg
(field1)
as
avgvalue
from
table1
最大:
select
max
(field1)
as
maxvalue
from
table1
最小:
select
min
(field1)
as
minvalue
from
table1
分页:select * from table limit (pageNo-1)*pageSize, pageSize;
10、使用外连接
A、
left
(
outer
)
join
:
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。
select
a.a, a.b, a.c, b.c, b.d, b.f
from
a
LEFT
JOIN
b
ON
a.a = b.c
B:
right
(
outer
)
join
:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:
full
/
cross
(
outer
)
join
:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
11、数据库去重
删除表中多余的重复记录(多个字段),只留有id最小的记录
DELETE
FROM
user u
WHERE
(u.peopleId, u.seq) IN (
SELECT
peopleId,
seq
FROM
user
GROUP BY
peopleId,
seq
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
user
GROUP BY
peopleId,
seq
HAVING
count(*) > 1
)
12、统计订单近七天的数据
select a.click_date as date,ifnull(b.amount,0) as amount
from (
SELECT curdate() as click_date
union all
SELECT date_sub(curdate(), interval 1 day) as click_date
union all
SELECT date_sub(curdate(), interval 2 day) as click_date
union all
SELECT date_sub(curdate(), interval 3 day) as click_date
union all
SELECT date_sub(curdate(), interval 4 day) as click_date
union all
SELECT date_sub(curdate(), interval 5 day) as click_date
union all
SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date ,sum(amount) as amount
FROM order
GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
) b on a.click_date = b.date ORDER BY a.click_date
13、统计购买数量在一定区间的人数
SELECT
elt( INTERVAL ( t.buyCount, 0, 10, 30, 90 ), '0~10', '10~30', '30~90','90+' ) AS device,
count( t.user_id ) AS count
FROM
( SELECT user_id, sum( goods_count ) AS buyCount FROM order_detail GROUP BY user_id ) AS t
GROUP BY device
14、昨天到目前为止每小时注册人数
SELECT
DATE_FORMAT( create_time, '%Y/%m/%d %H' ) AS hours,
count( * ) AS count
FROM
user
WHERE
create_time < NOW( ) AND create_time > date_sub( NOW( ), INTERVAL 1 DAY )
GROUP BY
hours;