-- sql语句
-- 创建数据库
CREATE DATABASE mydb1 CHARACTER SET utf8;

-- 使用数据库
USE mydb1;

-- 查看正在使用的数据库
SELECT DATABASE();

-- 查看数据库信息
SHOW CREATE DATABASE mydb1;

-- 删除数据库
DROP DATABASE mydb1;`product`

-- DDL语句
-- 创建表
CREATE TABLE stu (
    cid INT,
    cname VARCHAR(20),
    ctime TIMESTAMP
);

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC stu;

-- 删除表
DROP TABLE stu;

-- 添加新字段
ALTER TABLE stu ADD  `score`VARCHAR(20);

ALTER TABLE stu ADD  `test`VARCHAR(20);

-- 修改字段类型
ALTER TABLE stu MODIFY `score` VARCHAR(50) NOT NULL;-- 修改类型长度并约束非空

-- 修改字段列名
ALTER TABLE stu CHANGE `desc` age INT;

-- 删除字段列
ALTER TABLE stu DROP test;

-- 修改表名
RENAME TABLE stu TO student;

RENAME TABLE student TO stu;

-- 修改表的字符集
ALTER TABLE stu CHARACTER SET gbk;

SHOW CREATE TABLE stu

ALTER TABLE stu CHARACTER SET utf8;

-- DML
-- 添加元素
INSERT INTO stu VALUES(001,'刘能',NULL);
SELECT * FROM stu;

INSERT INTO stu(cname,ctime) VALUES('赵四',NULL);

-- 修改元素
UPDATE stu SET cname ='广坤' WHERE cid = 1;

-- 删除元素
DELETE FROM stu WHERE cname = '赵四';

-- 删除表
DELETE FROM stu;
TRUNCATE stu;

DROP DATABASE mydb1;

-- DQL
#创建商品表：
CREATE TABLE product(
    pid INT PRIMARY KEY,
    pname VARCHAR(20),
    price DOUBLE,
    category_id VARCHAR(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

DELETE FROM product WHERE pid = 18;

-- 查询表内所有元素 
SELECT * FROM product;

-- 条件查询
SELECT pname,price FROM product WHERE category_id ='c003';

DELETE FROM product;

SELECT * FROM product WHERE price = '5000';

SELECT * FROM product WHERE price BETWEEN 500 AND 5000;

SELECT * FROM product WHERE price = 800 OR price = 5000;

SELECT * FROM product WHERE price IN (500,5000);-- 筛选500或者5000

SELECT * FROM product WHERE price LIKE '5%';

SELECT * FROM product WHERE pname LIKE '%霸%';

SELECT * FROM product WHERE pname LIKE '_想%';

-- 排序查询
SELECT * FROM product ORDER BY price ASC;-- 升序

SELECT * FROM product ORDER BY price DESC;-- 降序

SELECT DISTINCT price FROM product ORDER BY price ;-- 默认升序

-- 聚合查询
SELECT COUNT(*) FROM product;

SELECT COUNT(*) FROM product WHERE price = 5000;

SELECT SUM(price) FROM product;

SELECT AVG(price) FROM product WHERE pid %2 = 0;

SELECT MAX(pid),MIN(price) FROM product;

SHOW CREATE TABLE product;

DESC product;

-- 分组查询
SELECT pid, pname,SUM(price) newprice FROM product GROUP BY pname ORDER BY newprice;

SELECT pname, SUM(price) newprice ,COUNT(pid) FROM product GROUP BY  pname HAVING newprice >= 500;

SELECT pname ,SUM(price) newprice FROM product WHERE price >=2000 GROUP BY pname;

SELECT pname ,SUM(price) newprice FROM product GROUP BY pname WHERE newprice >=2000; -- 报错 where必须在gruop by 前面

-- 分页查询
SELECT * FROM product LIMIT 0,3;
SELECT * FROM product LIMIT 3,3;
SELECT * FROM product LIMIT 6,3;