sql语句学习总结(1)

一、简单语法：

1、sql简单例子

 1）select * from websites    #websites表名，sql语句大小写不敏感

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
+----+--------------+---------------------------+-------+---------+

  2）  SELECT name,country FROM Websites;

2、sql的distinct语句(去重复值)

select distinct country  from websites;

      

3、where子句用于提取那些满足指定标准的记录

select * from websites where id = 1

4、AND &OR语句

select * from websites where country ='CN' AND alexa >50;

select * from websites where country ='CN' OR country ='USA';

5、ORDER BY语句（用于对结果集的排序）与DESC（降序）、ASC（升序）一起搭配

select * from websites order by alexa  DESC;

6、sql之INSERT INTO语法（insert into 语句用于插入新的记录）

INSERT INTO websites(name,url,alexa,country)values("百度","https://www.baidu.com","4","CN");

select  * from websites;

7、UPDATE语句

update websites set alexa = '5000',country = "USA" where name = "菜鸟教程";

8、delete语句

delete from websites where name = "百度" AND country = "CN";

select * from websites;

二、高级语法

1、SQL之like语句

select * from websites where name like "G%";#G开头的名字

select * from websites where name like "%K";#K结尾的名字

select * from websites where name like "_%K";#开头为任意，K结尾的名字

2、SQL之in语句

select * from websites where name in("百度","Google");

3、SQL之between语句

select * from websites where alexa between 1 and 20;  #在1-20之间

select * from websites where alexa not between 1 and 20;#不在1-20之间

4、SQL之top、limit 、rownum

1）以下sql语句从“customers”表中选择前3条country为Germany的语句

 select  top 3 * from customers where country ="Germany";

select  * from customers where country ="Germany"  limit 3 ;

select  * from customers where country = "Germany" and  rowrum <=3;

5、SQL之[charlist]通配符

以下sql语句选择所有以客户city 以“b”、"s"、"p"为开头

select * from customers where city like "[bsp]%";

以下sql语句选择所有不以客户city 以“b”、"s"、"p"为开头

select * from customers where city like "[!bsp]%";

select * from customers where city not like "[bsp]%";

6、SQL之join连接

sql join 用于把来自两个或多个表的行结合起来

1)inner join:如果表中有至少一个匹配，则返回行

2）left join:即使右表中没有匹配，也从左表返回所有行

3）right join:即使左表中没有匹配，也从右表返回所有行

4）full join :只要其中一个表中存在匹配，则返回行

内部连接 inner join

select orders.orederID,Customers.CustomerID,orderDate from orders

inner join Customers  on orders.CustomerID= Customers.CustomerID;

左连接：left join

以下SQL语句选择将所有客户以及他们可能拥有的任何订单

select Customers.CustomerName,orders.orderID from Customers left join orders on 

Customers.CustomerID = orders.CustomerID order by Customers.CustomerName;

注释：left join 关键字返回左表Customers中的所有行，即使在右表orders没有匹配。

右连接：right join

以下SQL语句将返回所有雇员以及他们已经放置的任何订单

select orders.orderID,employees.Lastname,employees.Firstname from orders right join employees on 

orders.employeeID = employees.employeeID order by orders.orderID

 

完整外部连接：full join

以下SQL语句选择所有客户和所有订单

select Customers.CustomerName ,orders.orderID  from Customers full join orders on 

Customers.CustomerID =orders.CustomerID order by Customers.CustomerName;

自连接（self  join）这个有点难理解（自连接就是一个表，看成两个表）

下表表示 找每个人的领导，如果没有领导，显示无领导，eid 对应 leaderid

select  e.ename as "无领导",le.ename as "有领导"  from

emp.e  left join  emp.le

on  e.leaderid = le.eid;