创建数据库
CREATE DATABASE day06
查看所有的数据库
SHOW DATABASES
查看当前创建的数据库
show create database day06
修改数据的字符集
alter database day06 CHARACTER set utf8
删除数据库
drop DATABASE day06*/
使用当前数据库
use day06
查询当数据库
SELECT DATABASE()
创建表
create table student(
sid int PRIMARY KEY,
sname VARCHAR(225),
sex int,
age INT
)
查看当前数据库所有的表
show TABLES
查看表的创建过程
show create table student
查看表的结构
DESC student
添加列
alter table student add study int not NULL
修改列
alter table student modify sex VARCHAR(2)
修改列名
alter table student change sex gender VARCHAR(3)
删除列
alter table student drop study
删除表
DROP table student
show tables
insert into category VALUES(null,'手机数码','电子产品')
insert into category VALUES(null,'鞋靴箱包','江南皮革厂')
insert into category VALUES(null,'香烟酒水','中华、茅台')
insert into category VALUES(null,'酸奶饼干','安慕希、娃哈哈，蒙牛酸酸乳')
insert into category VALUES(null,'馋嘴零食','瓜子、八宝粥、辣条')
select *from category
select cname,cdesc from category
create table product(pid int PRIMARY key auto_increment,pname VARCHAR(10),price DOUBLE,
pdate timestamp , cno int)
insert into product VALUES(null,'小米mix4',998,null,1)
insert into product VALUES(null,'锤子',2888,null,1)
insert into product VALUES(null,'Aj',2000,NULL,2)
insert into product VALUES(null,'阿迪王',99,NULL,2)
insert into product VALUES(null,'老村长',88,NULL,3)
insert into product VALUES(null,'劲酒',35,NULL,3)
insert into product VALUES(null,'小熊饼干',1,NULL,4)
insert into product VALUES(null,'卫龙辣条',1,NULL,5)
insert into product VALUES(null,'旺旺大饼',1,NULL,5)
select *from product
select p.pname,p.price from product as p
select pname 商品名称 ,price 商品价格 from product
select DISTINCT price from product
select * ,price*1.5 as 折后价 from product
select * from product where price>60
/* 价格不是88*/
select * from product where price <> 88
select * from product where price>10 and price<100
/*查询价格 在10 和100之间*/
select * from product where price BETWEEN 10 AND 100
/*liek _ 表是一个字符 % 表示多个字符 模糊查询 查询带有饼的名称*/
select * from product where pname like '%饼%'
/*liek 模糊查询 查询第二名字是熊的所有商品*/
select * from product where pname like '_熊%'
/*查询商品分类con 在 1，4，5里面的所有商品*/
select * from product where cno in (1,2,5)
/* order by asc:ascend 升序（默认排序方式） desc :descend 降序 按价格排序*/
select * from product ORDER BY price
select * from product ORDER BY price DESC
select * from product where pname LIKE '%小%' ORDER BY price
聚合函数
- sun() :求和
select sum(price) from product
- avg () :平均值
select avg (price) from product
- count() : 统计数量
select COUNT(*) from product
max (): 最大值
min() : 最小值
注意 : where 条件后面不能接聚合函数
查出商品价格大于平均值的所有商品
select * from product where price>(select avg (price) from product )
分组统计 group by
/*根据cno 字段分组，分组后统计商品的个数*/
select cno,count(*) from product GROUP BY cno
/*根据cno分组，分组统计每组商品的平均价格 并且商品平均价格大于60*/
select cno,avg(price) from product group by cno having avg(price)>60
