sql2-CSDN博客

本文链接：https://blog.csdn.net/weixin_40387941/article/details/80099985

#创建数据库

create database aa;

show databases;#查看

use aa;#进入数据库

create table ww (id int,age int,sex int);#在aa数据库中创建表,ww是表名

show tables;

insert into ww values (1,19,1)， (2,30,1)，(3,18,2)， (4,19,1);#插入多行数据

select * from ww;#查看表里的全部数据

drop table hy_order_20170410;#删除表

drop database 数据库名;#删除数据库

#定义语言：对对象进行创建、修改、删除create,drop,alter

#操作语言：对表进行操作insert，update，delete

#查询语言：对数据查询select

#控制语言：grant#增加用户权限；revoke#收回权限

第一部分：定义语言部分

#查询表的结构；desc ww;

#alter table对已有表的增删改列；增加，修改需指定类型，更新表名，字段类型。删除不用加类型，直接表名。

alter table ww add test float;#增加test这一列

alter table ww drop test;#删除test这一列

alter table ww change age years int;#修改表age一列为years

alter table ww rename bb;#修改表名，即可以重命名

第一部分：操作语言部分

#增删改数据。insert,delete,update

实例1：

#1.增加数据

插入字段和原字段名顺序一致且全部字段都插入，则不用列名。

插入列的顺序与原表不一致（要一一对应）或者插入部分字段（和后面的值对应着不能多不能少），指定列名或部分列名，在表名后面，添加小括号，里面包括应有的列名，values后面对应着各列指定的值。

#创建订单表#省略有

create table hy_order_20170410(idvarchar(100),order_money float,memeber_id int,create_time datetime,status int);

insert into hy_order_20170410 values('A001',100.15,1,'2017-01-01 13:23:12',2);

insert into hy_order_20170410 values('A002',100.0,2,'2017-04-01 14:23:12',2);

#创建订单明细表

create table hy_orderitem_20170410 (idvarchar(100),order_id varchar(100),item_id int,item_name varchar(200), pricefloat,item_num int);

insert into hy_orderitem_20170410values('AB001','A001',1001,'A',0.15,1);

insert into hy_orderitem_20170410values('AB002','A001',1002,'B',20,1);

insert into hy_orderitem_20170410values('AB003','A001',1003,'C',40,2);

#2.updat更正值

update 表名 set 列名=新值（where 条件）

update hy_order_20170410 set order_money=123 where id='A002';

select * from hy_order_20170410;

#3.删除表中某行或者所有行eg:删除交易金额大于等于999的记录；清空整张表。

#delete from 表名（不加条件，就是删除整张表或者where 列名称=值）

delete from hy_order_20170410 where id='A001';

#为方便，自动补齐，dbeaver

/**

* 如果执行所有脚本，alt+x

*crtl+/单行注释

#4.导出数据

advanced copy 复制粘贴到excel

也可以右键导出结果。

ctrl+f查找；crtl+shift+/快速注释；选中表名，按住ctrl,即快速观看表结构。help里show可找快捷键。

#3.删除数据库

drop database exerci2;

create database exerci2;
show databases;
use exerci2;
create table hy_order_20170410 (id varchar(100),order_money float,memeber_id int,create_time datetime,status int);
show tables ;
select * from hy_order_20170410;
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2);
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2);
insert into hy_order_20170410 values ('A002',100.0,2,'2017-04-01 14:23:12',2);
insert into hy_order_20170410 values ('A003',12.1,3,'2017-01-01 13:23:12',2);
insert into hy_order_20170410 values ('A004',200.15,4,'2017-01-01 13:23:12',2);

/******
* 对查询语法进行练习
*
*
*
******/
--查看表结构

desc hy_order_20170410;
-- 查看前面10行；用来熟悉表结构和内容；
select *
from hy_order_20170410
limit 10;
---找出购买订单金额大于200的顾客
select id
from hy_order_20170410
where order_money>200;
--#6.会员会不会重复 distinct
select distinct id
from hy_order_20170410
where order_money>200;
---找出购买订单金额大于20小于200的顾客
select distinct id
from hy_order_20170410
where order_money>20 and order_money<200;
---按订单金额进行结果降序
select *
from hy_order_20170410
order by order_money desc;
/******
* 练习group by
* group by 必须结合汇总函数
* 汇总函数：max,min,avg,count,sum
*
*
******/
---统计顾客的累计购买金额
use exerci2;
select id
,sum(order_money)
from hy_order_20170410
group by id;
---取别名 as
---字段可以取别名
---给表取别名
select id as member_name
,sum(order_money) as sum_money
from hy_order_20170410
group by id;
select a.member_id
,sum(order_money) as sum_money
from hy_order_20170410 as a
group by member_id;
---统计各个顾客每天的购买金额
select id
,date_format(create_time,'%Y-%m-%d') as day_1
,sum(order_money) as sum_money
from hy_order_20170410
group by id
,date_format(create_time,'%Y-%m-%d');
/********
*
* where 条件练习
*
* like 练习
*
*/**/
---找出购买订单金额大于20小于200的顾客
---区间用 between and
select distinct id
from hy_order_20170410
where order_money>20 and order_money<200;
  select distinct id
from hy_order_20170410
where order_money between 20 and 200;
--查看订单状态为空的记录
select *
from hy_order_20170410
where status is null;
select *
from hy_order_20170410
where status is not null;
---不同订单状态会员数
select status
,count(distinct member_id) as member_number
from hy_order_20170410
group by status;
---以A开始C结尾的记录
select *
from hy_order_20170410
where id like 'A%C';
select *
from hy_order_20170410
where id like 'A_C';
/*********
*
* 进行时间函数必须掌握
* 时间维度是数据分析最为重要的分析维度之一；
* 天：上午，下午，中午
* 周：
* 月：
* 时间：
*
* 节假日，农历
* 很重要趋势分析：每天，每周，每月
*/*/

--- 默认是星期天是用1，按国外习惯

select dayofweek(create_time) as dday
,week(create_time) as wweek
,month(create_time) as mmonth
,dayofmonth(create_time) as monthofday
,dayofyear(create_time) as dayofyear
,create_time
from hy_order_20170410
---日期
select create_time
,date_format(create_time,'%Y-%m-%d') as type1
,date_format(create_time,'%y-%m-%d') as type2
,date_format(create_time,'%H') as type3
,date_format(create_time,'%h') as type4
from hy_order_20170410

/***
*
* hy_order_20170410 订单表包括：订单ID 订单金额会员ID 创建时间订单状态
*
* hy_orderitem_20170410 订单明细表包括：订单明细表的ID 订单ID 商品ID 商品名称商品价格商品数量
*
*
* */
#创建订单表
drop table demo.hy_order_20170410;
create table hy_order_20170410 (id varchar(100),order_money float,member_id int,create_time datetime,status int);
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2);
insert into hy_order_20170410 values ('A001',100.15,1,'2017-01-01 13:23:12',2);

#创建订单明细表

drop table hy_orderitem_20170410;
create table hy_orderitem_20170410 (id varchar(100),order_id varchar(100),item_id int,item_name varchar(200), price float,item_num int);
insert into hy_orderitem_20170410 values('AB001','A001',1001,'A',0.15,1);
insert into hy_orderitem_20170410 values('AB002','A001',1002,'B',20,1);
insert into hy_orderitem_20170410 values('AB003','A001',1003,'C',40,2);
insert into hy_orderitem_20170410 values('AB004','A005',1001,'A',0.15,1);
insert into hy_orderitem_20170410 values('AB005','A005',1004,'D',200,6);
insert into hy_orderitem_20170410 values('AB0015','A015',1005,'E',40.5,1);
#用户表
drop table hy_member_20170410;
create table hy_member_20170410 (id int,name varchar(20),sex int,age int,create_time datetime);
insert into hy_member_20170410 values(1,'小王',1,24,'2016-01-01 09:01:01');
insert into hy_member_20170410 values(2,'小李',1,34,'2016-02-02 14:02:01');
insert into hy_member_20170410 values(3,'小张',1,26,'2016-03-03 10:03:01');
insert into hy_member_20170410 values(4,'小钱',1,27,'2016-04-11 11:04:01');
insert into hy_member_20170410 values(5,'王二',1,28,'2016-05-12 12:05:01');
insert into hy_member_20170410 values(6,'赵四',1,25,'2016-06-13 13:00:01');
insert into hy_member_20170410 values(7,'王小',1,24,'2016-07-14 15:00:01');
select * from hy_member_20170410;
###重要alter database demo character set utf8;

alter table hy_member_20170410 character set utf8;