Mysql基本语法

基本命令

SHOW databases; -- 查看数据仓库

USE shop;-- 使用仓库

SHOW TABLES;-- 查看表

-- 创建

CREATE TABLE proudct(id INT NOT Null PRIMARY KEY auto_increment);

DESC proudct; -- 查看表结构

ALTER TABLE proudct add COLUMN name VARCHAR(20);

ALTER TABLE proudct add COLUMN price int;

ALTER TABLE proudct add COLUMN type VARCHAR(20);

INSERT INTO proudct VALUES (1,'外套A',300,'衣服');

SELECT * FROM proudct;-- 查询表中的所有列和所有记录

INSERT INTO proudct VALUES (2,'外套B',350,'衣服');

INSERT INTO proudct VALUES (3,'鞋子A',100,'鞋');

SELECT * FROM proudct WHERE type='衣服';-- 加条件查询(where)

SELECT * FROM proudct WHERE type='鞋';-- 加条件

SELECT name,price FROM proudct;-- 查询指定字段

SELECT type from proudct;-- 查询有几种类型

SELECT type from proudct GROUP BY type; -- 分组

SELECT DISTINCT type from proudct;-- 去重关键字DISTINCT

SELECT type,COUNT(*) FROM proudct GROUP BY type; -- count统计

-- 所有商品名和商品价格

SELECT name,price from product;

SELECT name,price,price * 0.8 AS price2 FROM Proudct;

-- 打八折

SELECT name AS product_name,price,price * 0.8 AS price2 FROM Proudct;

-- 价格超过三百额的打八折

SELECT name,price,price * 0.9 AS price2 from Proudct where price <=300;

SELECT name,price,price * 0.9 AS price2 from Proudct where price <=300 AND type='衣服';

INSERT INTO proudct VALUES (4,'外套D',800,'衣服');

INSERT INTO proudct VALUES (5,'外套E',10000,'衣服');

INSERT INTO proudct VALUES (6,'外套C',300,'衣服');

-- insert into proudct (name,price,type) VALUES ()

SELECT * FROM proudct;

-- 总计

SELECT sum(price) from proudct;

-- 整个商品的平均售价

SELECT AVG(price) from proudct;

-- 最贵的售价

SELECT MAX(price),MIN(price) from product;