SQL语句学习记录(三)
SELECT TOP number|percent column_name(s) FROM table_name;
SELECT TOP 50 PERCENT * FROM Websites;
SELECT column_name(s) FROM table_name LIMIT number;
select * from websites limit 2
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
SELECT * FROM Persons WHERE ROWNUM <=5;
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
select * from websites where name like 'F%'
SELECT * FROM Websites WHERE name LIKE '%e';
SELECT * FROM Websites WHERE name LIKE '%ov%';
通配符 |
描述 |
% |
替代 0 个或多个字符 |
_ |
替代一个字符 |
[charlist] |
字符列中的任何单一字符 |
[^charlist] 或 [!charlist] |
不在字符列中的任何单一字符 |
SELECT * FROM Websites WHERE url LIKE 'https%';
SELECT * FROM Websites WHERE name LIKE 'G_o_le';
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
SELECT * FROM Websites WHERE name IN ('Google','百度');
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;
SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;
SELECT * FROM Websites WHERE (alexa BETWEEN 100 AND 200) AND country NOT IN ('USA', 'IND');
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';
SELECT * FROM Websites WHERE name NOT BETWEEN 'A' AND 'H'
SELECT column_name AS alias_name FROM table_name;
SELECT name AS n, country AS c FROM Websites;
SELECT column_name(s) FROM table_name AS alias_name;
select emp_info.employee_id,employees.manager_id,emp_info.employee_name from emp_info inner join employees on emp_info.employee_id=employees.employee_id;
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
select emp_info.employee_name,employees.manager_id from emp_info left join employees on emp_info.employee_id=employees.employee_id order by emp_info.employee_id desc;
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;
select emp_info.employee_name,employees.manager_id from emp_info right join employees on emp_info.employee_id=employees.employee_id order by emp_info.employee_id asc;
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
SELECT country FROM Websites UNION SELECT employee_name FROM emp_info ORDER BY country;
SELECT country FROM Websites UNION all SELECT employee_name FROM emp_info ORDER BY country;
SELECT country,url FROM Websites where country='CN' UNION all SELECT employee_name,employee_id FROM emp_info ORDER BY country;