Mysql基本语法

基础命令

show databases;  //查看数据库列表
create databases shop;  //创建一个名为shop的数据库
use shop;//使用shop库

//建一张名为Proudct的表
//包含一个id字段 字段类型为int（整数）not null表示不允许为null，promary key表示该字段为主键，auto-increment表示自动增长
create table Proudct (id int not null primary key auto-increment);

//添加表结构语句，添加一个名为name的，类型为varchar（字符串，长度为100）的属性到proudct表中
alter table Product add column name varchar(100);

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

//查看proudct的表结构
desc Proudct;

//添加一条数据小括号里写我要添加哪些字段，values后按照顺序写值
insert into Product(name,price,type) values ('帽子',99,'服饰');

//查询Product表中全部的数据
select * from Proudct;

//删除Product表中id为1的数据
delete from Product where id=1;

//修改Product表中id为2的记录的name值为拖鞋
update Proudct set name='拖鞋' where id=2;

//条件查询
mysql> select * from Proudct where type='服饰';

//多个条件查询
select * from Proudct where type='服饰'and price>=100;


//查整个库里的商品名和价格
select name,price from Proudct;

//打八折
select name,price,price * 0.8 As price2 from Proudct;

//
select name As proudct_name,price,price * 0.8 As price2 from Proudct;


//价格高于300的打八折
select name,price,price * 0.8 As price2 from Proudct where price >300;


//价格小于300 的打九折
select name,price,price * 0.9 As price2 from Proudct where price <=300;


//最高价格
select max(price) from Proudct;

//最大最小值
select max(price),min(price) from Proudct;

//平均价格
select avg(price) from Proudct;

//新建
insert into Proudct (name,price,type) values ('外套F',99,'衣服');

//
rollback

//
commit;

//衣服小于等于300打九折
select name,price,price * 0.9 As price2 from Proudct where price <=300 and type='衣服';

//查总数
select distinct type from Proudct;

//
select type,count(*) from Proudct group by type;

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