Mysql的基本語法
mysql基本語法
基礎(chǔ)命令
show databses; //查看數(shù)據(jù)庫列表
create databse shop; //創(chuàng)建一個名為shop的數(shù)據(jù)庫
use shop; //使用shop庫
//建一張名為Product的表
//包含一個id字段 字段類型為int（整數(shù)） not null表示不允許為null碳蛋，promary key表示該字段為主建，auto_increment表示自動增長
create table Product(id int not null primary key auto_increment);
//添加表結(jié)構(gòu)語句 添加一個名為name的 類型為varchar(字符串，長度為100)的屬性到product表中
alter table Product add column name varchar(100);//添加表格
//刪除表結(jié)構(gòu)語句
alter table Product drop colum name;
//查看product的表結(jié)構(gòu)
desc Product祭务；
//商品價格
alter table Product add column price int ;
//商品類型
alter table Product add column type varchar(20)闹瞧；
//添加一條數(shù)據(jù)小括號里寫我要添加哪些字段盒让，values后按照順序?qū)懼?insert into Product(name ,price,type) values(“帽子”,99,“服飾”);
//查詢Product表中全部的數(shù)據(jù)
select * from Product;
//刪除Product表中id為1的數(shù)據(jù)
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;
//查詢指定內(nèi)容
SELECT type FROM Product;
//分組查詢
SELECT type FROM Product GROUP BY type;
//分組查詢時去掉重復(fù)值
SELECT DISTINCT type FROM Product type;
//分組查詢并計(jì)算總數(shù)
SELECT type,COUNT(*) FROM Product GROUP BY type;
//多字段查詢
SELECT name,price FROM Product;
//查詢表里價格打8折的物品
SELECT name ,price,price * 0.8 As price2 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;//計(jì)算總和
select avg(price) from Product;//計(jì)算平均價格
select min(price) from Product;//計(jì)算最小值
select max(price) from Product;//計(jì)算最大值
