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