MySql操作-1(笔记)

创建数据库

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