MySQL 基础语句

USE qy97;/*使用数据库*/
CREATE TABLE book (bid INT PRIMARY KEY AUTO_INCREMENT,bname VARCHAR(50),price DOUBLE);/*创建表格*/
DESC book;/*查看表格的格式*/
ALTER TABLE book MODIFY price INT;/*修改列的类型   MODIFY*/
ALTER TABLE book CHANGE bname bnames VARCHAR(200);/*修改列名  CHANGE*/
SELECT * FROM book;/*查看表格的所有数据*/
INSERT INTO book(bid,bnames,price) VALUES(1,'语文',987);/*  INSERT INTO (列1,列2,列3...) VALUES (数据1,数据2,数据3...);   向表中添加数据*/
INSERT INTO book(bid,bnames,price) VALUES(2,'数学',645);
INSERT INTO book(bid,bnames,price) VALUES(3,'语文',542);
INSERT INTO book(bid,bnames,price) VALUES(4,'数学',364);
INSERT INTO book(bid,bnames,price) VALUES(5,'语文',754);
INSERT INTO book(bid,bnames,price) VALUES(6,'数学',147);
ALTER TABLE book DROP price;/* 关键词 DROP  删除列*/
RENAME TABLE book TO books;/* 关键词 TO   修改表名*/
SELECT * FROM books;
ALTER TABLE books ADD bcount INT;/* 关键词 ADD   添加列*/
UPDATE books SET bcount=2 WHERE bid=1;/* 关键词 UPDATE   SET    根据键值 更新表中bcount列的数据  如果内容相同可以    WHERE 键值 OR 键值*/
UPDATE books SET bcount=4 WHERE bid=2;
UPDATE books SET bcount=6 WHERE bid=3;
UPDATE books SET bcount=8 WHERE bid=4;
UPDATE books SET bcount=10 WHERE bid=5;
UPDATE books SET bcount=12 WHERE bid=6;
SELECT DISTINCT bnames FROM books;/* 关键词 DISTINCT  显示不同的数据*/
SELECT bnames AS 'nn' FROM books;/* 关键词 AS 别名 */
SELECT * FROM books WHERE price BETWEEN 500 AND 700;/*显示price 500——700的数据*/
SELECT * FROM books WHERE price IN (2);/* IN 关键词 在...里   查看2在不在price里*/
SELECT * FROM books ORDER BY price ASC;/* ORDER BY 关键词   ASC 升序*/
SELECT * FROM books ORDER BY price DESC;/* ORDER BY 关键词  DESC 降序*/
DELETE FROM books WHERE bid=1;/* 关键词DELETE  根据键值删除数据*/
SELECT COUNT(*) AS '总数' FROM books;/*关键词COUNT  对表中的数据个数求和(插入几个数据的bid键值) */
SELECT SUM(price) FROM books;/*SUM()  求和*/
SELECT SUM(price) FROM books WHERE bnames LIKE '%语%';/*根据模糊查询  求和*/
SELECT MAX(price) FROM books;/* 关键词 MAX    获取最大值*/
SELECT MIN(price) FROM books;/* 关键词 MIN    获取最小值*/
SELECT AVG(price) FROM books;/* 关键词 AVG    计算一个列的所有数据的平均值*/
SELECT price FROM books;/*查看列的数据*/
SELECT price FROM books WHERE bnames LIKE '%语%';/*  LIKE模糊查询   %通配符*/
SELECT * FROM books WHERE bnames LIKE '__'; /* 查询2个字的  '_'*/
SELECT * FROM books WHERE bnames IS NOT NULL;/*IS NULL   显示bnames列中值为null的数据 */
SELECT SUM(price),bnames FROM books WHERE bnames LIKE '%语%' GROUP BY bnames;/*对bnames分组查询求和  只要模糊查询'%语%*/
SELECT SUM(price)AS 'getprice',bnames FROM books WHERE bnames LIKE '%语%' GROUP BY bnames HAVING getprice>500;/*对bname内容进行分组查询求和   AS别名  但只要模糊查询'%语%'   显示金额大于500*/