Mysql基本语法

基本命令

show databases; //查看数据库列表
create database shop; //创建一个名为shop的数据库
use shop; //使用shop库
//建一张名为Product的表
//包含一个id字段 字段类型为int（整数） not null表示不允许为null,key表示该字段为主键，auto_increment表示自动增长

create table Product(id int not null primary key auto_increment);
//添加表结构语句 添加一个名为name的类型为varcher（字符串，长度为100的属性到Product表中
alter table Product add column name varcher(100);

//删除表结构语句
alter table Product drop column name;

//查看Product的表结构
desc Product;
//添加一条数据小括号里写我要添加哪些字段，values后按照顺序写值
inser into Product (name price type)values("帽子",99,"服饰")；
//查询Product表中全部的数据
select * from Product;
//删除Product表中id为1的数量
delete from Product where id = 1;
//修改Product表中id为2的记录的name值为拖鞋
update Product set name = "拖鞋" where id=2;
//条件查询
select * from Product where type ="服饰";
//多个条件查询
select * from Product where type ="服饰" and price>=100;
//查询指定内容
select type from Product;
//分组查询
select type from Product group by type;
//去掉重复的
select distinct type from Product type;
//计算总数
select type,count(*) from Product group by type;
//查询表里打8折的商品
select name,pricr from Product;
select name AS Product_name ,price,price * 0.8 AS Price2 from Product;
//查询表里打9折且大于150的商品
select name,price,price * 0.9 AS price1 From Product WHERE price>150;

//查询表里价格打九折且小于150的物品
select name ,price,price * 0.9 AS price1 from Product WHERE price<150;
//查询表里价格打九折且小于等于150的物品
select name ,price,price * 0.9 AS price1 from Product WHERE price<=150 AND type = '衣服';
//添加外套C和外套D
insert into Product (name,price,type) values('外套C',1569,''衣服);('外套D',80,'衣服');
//多字段查询
selet name,price from Product;
select sum(price) from Product;//计算总和

select avg(price) from Product;//计算平均价格

select min(price) from Product;//计算最小值

select max(price) from Product;//计算最大值

price int //商品价格
type varcher(20) //商品类型