SQL学习笔记
SQL(Structured Query Language)学习笔记
Terminal登录数据库
1.登录mysql -u root -p ;
2.显示所有数据库show databases ;
3.使用数据库use “database name” ;
4.显示数据库中所有表 show tables;
5.删除表 drop table ”Customers“;
SQL特性
SQL约束
1.主键
1.
(
order_num int NOT NULL PRIMARY KEY,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL
);```
2.
```ALTER TABLE Orders ADD PRIMARY KEY (order_num);```
2.外键
3.唯一
4.检查
###SQL索引
###触发器
##SQL优化执行计划
###索引唯一扫描(index unique scan)
###索引范围扫描(index range scan)
###索引全扫描(index full scan)
###索引快速扫描(index fast full scan)
##检索数据
1.多条SQL语句必须以分号(;);
2.SQL语句不区分大小写;
3.`select distinct vend_id from products;`返回不同的值
`destinct`作用于所有的列
4.限制结果数量，分页
①Oracle rownum
②MySQL limit
`select * from limit m,n` m是指m+1条开始，取n条
##排序检索数据
1.order by
①`select prod_name from products order by prod_name`
②`select prod_id , prod_price ,prod_name from products order by prod_price,prod_name;`
③`select prod_id , prod_price ,prod_name from products order by 2,3;`
2.order by desc(降序排列)
`select prod_id , prod_price ,prod_name from products order by prod_price desc;`
desc致应用到直接位于其前面的列名
默认是升序排列
##过滤数据(字符需要用'' 数值不需要'')
1.bwtween and
2.is null
3.and,or,in,not
##通配符过滤数据
1.必须是用 like 操作符 ，通配符只能用于文本字段
2.％ **(%不匹配 null)**
3._
4.[]
##拼接字段(||)
1.`select concat(vend_name , '(',vend_country,')') from vendors order by vend_name;`
拼接字段，查询的结果没有列名
2.alias 别名 **Oracle表别名不能用as**
`select concat(vend_name , '(',vend_country,')') as vene_title from vendors order by vend_name;`
3.计算
`select prod_id , quantity , item_price , quantity* item_price as expanded_price from orderitems where order_num=20008;`
## SQL函数
1.聚集函数
|函数 | 说明|
|------- | ----- |
|AVG() | 返回某列的平均值 |
|COUNT() | 返回某列的行数|
|MAX() | 返回某列的最大值|
|MIN() | 返回某列的最小值|
|SUM() | 返回某列值之和|
**count(*) 对表中的行的数目进行计数 不忽略为null的行**
**Count(column)对特定的列中具有值的行进行计数**
## 分组
1.group by
2.having
where 过滤行 having过滤分组
where在数据分组前进行过滤，having在数据分组后进行过滤
## select执行顺序
`select from where---->group by--->having--->order by
`
## 子查询
**作为子查询的select语句只能查询单个列**
`select cust_name ,cust_state ,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders
from customers order by cust_name;
`
## 联结
### 等值联结/内联结
`select vend_name , prod_name ,prod_price from vendors , products where vendors.vend_id=products.vend_id ;`
`select vend_name , prod_name ,prod_price from vendors INNER join products on vendors.vend_id=products.vend_id ;
`
**笛卡尔积 叉联结**
### 自联结
### 自然联结
### 外联结
1.左外联结
`select customers.cust_id ,orders.order_num from customers left outer join orders on customers.cust_id=orders.cust_id;`
2.右外联结
`select customers.cust_id ,orders.order_num from customers right outer join orders on orders.cust_id=customers.cust_id;`
## 组合查询
### UNION
`select cust_name ,cust_contact ,cust_email
from customers
where cust_state in ('IL','IN','MI')
union
select cust_name ,cust_contact ,cust_email
from customers
where cust_name='Fun4A11';`
规则:
1.UNION必须由2条以上的select语句组成
2.UNION的每个查询必须包含相同的列