使用工具:MYSQL,MYSQLWorkbench
初窥:
- 关键字大写更规范
- 星号*返回所有列
- USE选择数据库
- WHERE进行筛选
- ORDER进行排序
USE sql_store;
-- 选择数据库
SELECT *
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name;
-- 按字段排序
SELECT语句:
- 查询指定列
SELECT first_name, last_name
FROM sql_store.customers;
返回 两列 first_name, last_name
- 变换查询顺序:
SELECT last_name,first_name
FROM sql_store.customers;
返回列的顺序也随之变化
- 新建列:points+10,对原先列的内容作运算
SELECT last_name,first_name,points,points+10
FROM sql_store.customers;
返回结果:
last_name,first_name,points,points+10
‘Twiddell’,‘Elka’,‘3073’,‘3083’
‘Rumgay’,‘Romola’,‘1486’,‘1496’
‘Roseburgh’,‘Ambur’,‘457’,‘467’
‘Naseby’,‘Thacher’,‘205’,‘215’
- 为新建列起别名:AS
SELECT
last_name,
first_name,
points,
(points+10)*10 AS discount_factor
FROM sql_store.customers;
结果:
last_name,first_name,points,discount_factor
‘MacCaffrey’,‘Babara’,‘2273’,‘22830’
‘Brushfield’,‘Ines’,‘947’,‘9570’
‘Boagey’,‘Freddi’,‘2967’,‘29770’
‘Roseburgh’,‘Ambur’,‘457’,‘4670’
‘Betchley’,‘Clemmie’,‘3675’,‘36850’
- 别名需要空格,使用双引号或单引号
SELECT
last_name,
first_name,
points,
(points+10)*10 AS 'discount factor'
FROM sql_store.customers;
-
可以直接使用Workbench更改表单内数据
可在搜索结果中直接双击更改,更改后单击下方apply -
去除重复结果:DISTINCT
SELECT DISTINCT state
FROM sql_store.customers
WHERE语句:
- 关系运算符:
运算符: >, >=, <, <=, =, !=, <>(另一种不等号)
字符串要加单引号或双引号
SELECT * FROM sql_store.customers WHERE state='VA';
表单中是VA,但查询时va大小写均可
SELECT * FROM sql_store.customers WHERE state<>'va';
日期也可以进行比较,1990-01-01是sql中日期标准写法
SELECT * FROM sql_store.customers WHERE birth_date > '1990-01-01';
参考链接:https://www.bilibili.com/video/BV1iJ411m7Fj?from=search&seid=8712794385598883181