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;
//商品价格
alter table Product add column price int ;
//商品类型
alter table Product add column type varchar(20);
//添加一条数据小括号里写我要添加哪些字段，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 Prouduct GROUP BY type;
//分组查询时去掉重复值
SELECT DISTINCT type FROM Product type;
//分组查询并计算总数
SELECT type,COUNT(*) FROM Product GROUP BY type;
//多字段查询
SELECT name,price FROM Product;
//查询表里价格打8折的物品
SELECT name,price,price * 0.8 As pirce2 FROM Product;
//查询表里价格打8折的物品
SELECT name AS Product_name,price,price * 0.8 As price2 FROM Product;
//查询表里价格打8折且大于150的物品
SELECT name,price,price * 0.8 AS price2 FROM Product WHERE price>150;
        //查询表里价格打九折且小于150的物品
        SELECT name ,price,price * 0.9 AS price2 FROM Product WHERE price<150;
        //查询表里价格打九折且小于等于150的物品
        SELECT name ,price,price * 0.9 AS price2 FROM Product WHERE price<=150 AND type = '衣服';
//添加外套C和外套D
INSERT INTO Product (name,price,type) VALUES('外套C',1569,''衣服);('外套D',80,'衣服');
select sum(price) from Product;//计算总和

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

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

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