数据库语句（练习）

CREATE TABLE student(
				id INT PRIMARY KEY AUTO_INCREMENT,-- 主键自动增长
				NAME VARCHAR(30) UNIQUE, -- 唯一约束
				dender CHAR(1) NOT NULL
);

SHOW TABLES;

DESC student;

ALTER TABLE student Add grade VARCHAR(20) NOT NULL;

ALTER TABLE student MODIFY gender INT NOT NULL DEFAULT 1;

ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;

ALTER TABLE student DROP class;

RENAME TABLE student to teacher;

UPDATE product SET price=5000;

UPDATE product SET price=18000 WHERE pname='苹果手机';

UPDATE product SET price=17000,num=5 WHERE pname="苹果手机";

UPDATE product SET price=price+2 WHERE pname='华为手机';

-- 删除数据
DELETE FROM product WHERE pname='苹果手机';

DELETE FROM product WHERE price >5001;

DELETE FROM product;

TRUNCATE TABLE product;

SELECT * FROM product ;
SELECT pname,price,num,pid FROM product ;
-- 去重查询 
SELECT DISTINCT pname,price  FROM product;


-- 给字段取别名 
SELECT p.pid,pname AS '商品名',price AS '商品价格',num AS '商品库存' FROM product p;

SELECT p.pid,pname AS '商品名',price+10 AS 'price',num AS '商品库存' FROM product p;


SELECT * FROM product where price>10000; 
SELECT * FROM product  where pid=1; 
SELECT * FROM product WHERE pid<>1;-- 不等于
SELECT * FROM product WHERE price BETWEEN 3000 AND 6000;

SELECT * FROM product WHERE pid IN (1,3,4);
SELECT * FROM product WHERE pname LIKE '解%';

SELECT * FROM product WHERE price>3000 AND num>30;
SELECT * FROM product WHERE pid=1 OR price>3000;


SELECT * FROM student ORDER BY score DESC;
SELECT * FROM student WHERE sex='女' ORDER BY score DESC,age DESC;

SELECT MAX(score) FROM student;
SELECT MIN(score) FROM student;
SELECT AVG(score) FROM student;
SELECT AVG(IFNULL(score,0)) FROM student;
SELECT COUNT(sid) FROM student;

SELECT SUM(score) FROM student;

SELECT sex '性别',COUNT(sid) '总人数' FROM student GROUP BY sex;

SELECT sex '性别',AVG(score) '平均分' FROM student GROUP BY sex;

SELECT sex '性别',SUM(score) '总分' FROM student GROUP BY sex;