数据库SQL基本语法信息介绍

数据库SQL基本语法信息介绍

使用牛客网进行实战演练

SQL2查找入职员工时间排名倒数第三的员工所有信息

参考书籍：SQL必知必会,人民邮电出版社

检索数据

检索单个列

SELECT prod_name FROM Product;

多条SQL语句必须以分号(;)分开，不区分大小写，所有空格均被忽略。

检索多个列

SELECT prod_id,prod_name,prod_price FROM Products;

选择多个列那么必须要有逗号，但最后一个列名不加逗号。

检索所有列

SELECT * FROM Products;

检索返回不同的值

SELECT DISTINCT vend_id FROM Products;

限制返回多少行

#返回不超过五行的数据
SELECT prod_name FROM Products LIMIT 5;

#从第五行起的五行数据
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

第一个被检索的是第0行。

检索排序数据

按照单列排序

SELECT prod_name FROM Products ORDER BY prod_name;

ORDER BY子句的位置必须是最后一条，不然就会报错。

按照多列排序

SELECT prod_id,prod_proce,prod_name FROM Products ORDER BY prod_price,prod_name;

多列排序时，先按照前面的变量排序，然后按照后面的变量排序。

默认排序方案是升序，可使用下面的语句实现降序排列

SELECT prod_id prod_price prod_name FROM Products ORDER BY prod_price DESC;

多个列进行降序排列

SELECT prod_id prod_price prod_name FROM Products ORDER BY prod_price DESC,prod_id DESC;

过滤数据

检索所需数据需要指定搜索条件，搜索条件也称为过滤条件。

判断条件

SELECT prod_name,prod_price FROM Products WHERE prod_price=3.49;

同时使用ORDER BY和WHERE应该让ORDER BY语句位于WHERE之后，先选择再排序，明显节省了计算量。

检索单个值

SELECT * FROM employees WHERE emp_no=10001;

范围值检查

SELECT * FROM employees WHERE emp_no>10001 AND emp_no<10004;
SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 10004;

检查是否为空

SELECT * FROM employees WHERE emp_no IS NULL;

高级数据过滤

组合多条WHERE语句

#AND 
SELECT * FROM employees WHERE emp_no>10001 AND emp_no<10004;
#OR
SELECT * FROM employees WHERE emp_no<10002 OR emp_no>10004;
#IN,用来指定条件范围
SELECT * FROM employees WHERE emp_no IN (10001,10003);
#NOT,用来表示否定后面的条件
SELECT * FROM employees WHERE NOT emp_no IN (10001,10003);

在WHERE中使用()来表示运算顺序，同时使用AND和OR.

使用通配符进行过滤

通配符表示

# %表示任意多个字符
SELECT * FROM employees WHERE emp_no LIKE '1000%';
SELECT * FROM employees WHERE emp_no LIKE '100%3';
# _匹配单个字符
SELECT * FROM employees WHERE emp_no LIKE '100_3';

创建计算字段

拼接字段，将一个值附加到另一个值构成单个值

SELECT Concat(emp_no, '(' , first_name , ')') FROM employees ORDER BY birth_date;

设置别名

SELECT Concat(emp_no, '(' , first_name , ')') AS emp_no1FROM employees ORDER BY birth_date;

执行代数运算

SELECT emp_no,gender,emp_no*gender AS eg FROM employees;# 处理简单的表达式SELECT 3*2;

使用函数处理数据

与SQL语句不一样，SQL函数不是可移植的，所以使用SQL函数时注意写相对应的注释。

主要使用方面：处理文本字符串，数值算术操作，时间和日期中提取特定成分

汇总数据

聚集函数，返回对应的平均值，返回列数，最大值，最小值，和

#平均值
SELECT AVG(emp_no) FROM  employees; 
#最大值
SELECT MAX(emp_no) FROM  employees; 
#最小值
SELECT MIN(emp_no) FROM  employees; 
#某列的行数，忽略空值
SELECT COUNT(emp_no) FROM  employees; 
#列求和
SELECT SUM(emp_no) FROM  employees; 
#平均数排除相同值
SELECT AVG( DISTINCT emp_no) FROM  employees;

分组数据

分组统计数据

#分组统计
SELECT COUNT(*) FROM  employees GROUP BY gender;
#分组统计并且带有筛选条件
SELECT COUNT(*) FROM  employees GROUP BY gender HAVING COUNT(*) > 5;

WHERE在分组前筛选，HAVING在分组后筛选。

使用子查询

前一步的输出作为后一步的输入

#查找和某个用户相同属性的用户有哪些？
SELECT * FROM  employees WHERE gender IN (SELECT gender FROM employees WHERE emp_no = '10001')

联结表

SQL最强大的功能在于在数据查询的执行中联结(join)表。

关系表的设计就是把信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联，所以成为关系数据库。

#测试1
SELECT salary,dept_no FROM salaries,dept_manager WHERE salaries.emp_no=dept_manager.emp_no;

#测试2，SQL_ERROR_INFO: "Column 'emp_no' in field list is ambiguous"
SELECT salaries.emp_no,salary,dept_no FROM salaries,dept_manager 
WHERE salaries.emp_no=dept_manager.emp_no;

没有联结条件的表返回的结果是笛卡尔积。

组合查询

使用UNION来组合数条SQL查询。

SELECT emp_no,gender FROM employees WHERE gender = 'M'
UNION
SELECT emp_no,gender FROM employees WHERE emp_no='10002';

插入数据

插入完整的行，保证主键唯一

#简单易行，但容易出错
INSERT INTO dept_emp VALUES(20001,'d001','1986-06-26','9999-01-01');
#更加合理的插入
INSERT INTO dept_emp(emp_no,dept_no,from_date,to_date)
VALUES(20001,'d001','1986-06-26','9999-01-01');

插入部分列时意味着某些列可以缺省，但并不代表都可以。

更新和删除数据

更新和修改数据

UPDATE employees SET gender='F' WHERE emp_no='10001'

删除数据

DELETE FROM employees WHERE emp_no='10001'

创建和删除表

新表的名字，列的名字和属性

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

删除表

drop table if exists  'dept_emp';