SQL 学习笔记

select 基本使用

SELECT employeeNumber FROM employees; -- 简单select 查询
SELECT * FROM employees;  -- 查询全部列
SELECT employeeNumber,lastName,email FROM employees -- 组合查询， 返回结果集

select distinct jobTitle FROM employees  -- 返回 唯一 不同的值

SELECT * from employees WHERE jobTitle = 'Sales Rep'  -- 那些满足指定标准的记录

SELECT * FROM employees WHERE employeeNumber = 1165 -- 如果是数值字段就不用引号了

SELECT * FROM employees WHERE employeeNumber <= 1165  -- 支持的运算符 小于等于
SELECT * FROM employees WHERE employeeNumber >=1165 -- 大于等于
 
SELECT employeeNumber FROM employees WHERE employeeNumber BETWEEN 1165 AND 1286  -- BETWEEN

SELECT lastName FROM employees WHERE lastName LIKE 'F%' -- LIKE

SELECT DISTINCT lastName FROM employees WHERE lastName IN ('Firrelli','Bott') -- In 可以在 where 里查询多个值 和distinct组合使用


SELECT * FROM employees WHERE employeeNumber = 1002 AND extension = 'x5800' 

SELECT employeeNumber FROM employees WHERE employeeNumber = 1002 or employeeNumber = 1165 -- 选取 employeeNumber = 1002 或者 1165

SELECT * FROM employees WHERE employeeNumber > 1056 AND (officeCode = 6 OR officeCode = 5)  -- AND OR 组合

SELECT * FROM employees ORDER BY officeCode DESC  -- 降序
SELECT * FROM employees ORDER BY officeCode ASC   -- 升序

SELECT * FROM employees ORDER BY officeCode, reportsTo ASC; -- 多列 Order By 多列排序 是以第一列为主列 先排序 如果第一列有相同的值 再按第二列 进行排序

INSERT INTO employees (employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitle)
VALUES(3,'xu','ll','x5401','dmurph1y@yiibai.com',3,1443,'sfd');

SELECT * FROM payments 
INSERT INTO payments (customerNumber,checkNumber,paymentDate,amount)
VALUES ('101'	,'HQ336331','2014-10-11','6066.74')
SELECT * FROM employees WHERE lastName = 'xu'

UPDATE employees
SET lastName = 'xu', email = '5645@qq.com' WHERE employeeNumber = 1188   -- 一定要执行 where

SELECT * FROM employees WHERE lastName = 'xu'

DELETE FROM employees 
WHERE  extension = 'x5800'

SELECT * FROM employees WHERE extension = 'x5800'

复制代码

转载于:https://juejin.im/post/5b47037f5188251afa62c075