MySQL-高级操作

DROP DATABASE zhazhen;
CREATE DATABASE zhazhen;
CREATE TABLE money(
id INT PRIMARY KEY,
username VARCHAR(10) NOT NULL,
money DOUBLE
);
INSERT INTO money VALUES (1,'徐凤年',120000),(2,'张良',2312.36),
(3,'韩非',12937.34),(4,'王翦',2365222),(5,'简不知',23562.32);
SELECT * FROM money;

-- 别名：AS 字段别名、表别名
-- 字段别名

SELECT username,id FROM money;
SELECT username AS '姓名' FROM money;
SELECT username AS '姓名',money '收入' FROM money;
SELECT id "NAME",username "称号" FROM money;
-- 表别名：做子查询时
SELECT m.id FROM money m;
SELECT * FROM money;
INSERT INTO money VALUES (6,'武庚',12000),(7,'武庚',12500);
-- select '表'.id from money AS m;
-- 去重distinct:默认情况查询所有字段数据
SELECT username '姓名' FROM money;
SELECT DISTINCT username '姓名' FROM money;
-- where子句
-- 运算符：> < = !=/<> >= <=
SELECT money FROM money WHERE money>10000;
SELECT * FROM money WHERE id=1;
-- 区间：between...and
SELECT * FROM money WHERE money BETWEEN 2000 AND 20000;
-- 给定值进行选择:in(值1,值2...)
SELECT * FROM money WHERE money IN(2312.36,1,52,7486);
-- 逻辑：与and、或or、非not(取反)
SELECT * FROM money WHERE money>10000 AND id<5;-- 前后条件都满足
SELECT * FROM money WHERE money>10000 OR id<5;-- 前后条件满足其一+
SELECT * FROM money WHERE NOT id=5;-- 给条件取反
-- 字符串匹配:like...% _
SELECT * FROM money WHERE username LIKE '王%';-- 字符后可匹配多个
SELECT * FROM money WHERE username LIKE '王_';-- 字符后只匹配一个
INSERT INTO money VALUES (11,'李斯',2547564);
-- 查询null的数据:is null/is not null
INSERT INTO money VALUE (12,'张仪',NULL);
SELECT * FROM money WHERE money IS NULL;
SELECT * FROM money WHERE money IS NOT NULL;