【自言自语】对于数据库sql语言,之前都是用的时候再查,没有系统的了解,用了一天半把这个书看完了,所以需要总结一下,不过,我没有写游标、存储过程这些,因为我觉得书中讲的过浅了。等下一本书看完,或许有更好的理解。到时候,再写关于那几个方面的我觉得是不错的。这段时间抓紧时间啃书啊,书越借越多。
SQL和Java、C不一样,他只有很少的词。他不是某个特定数据库提供商专有的语言。几乎所有重要的DBMS都支持SQL,但是有些细节地方还是要去看该DBMS的详细文档。
数据库增删改查操作
一、创建数据库
CREATE DATABASE test;
二、查看数据库
SHOW DATABASES;
- 查看当前数据库
SELECT DATABASE();
直接可以看出数据库名称SHOW TABLES;
查看所有表操作也可以显示出数据库名称
三、切换指定数据库
USE test;
四、创建表
CREATE TABLE client( id INT, age INT, name VARCHAR(20), gender VARCHAR(1) );
五、查看表数据
DESC client;
六、增删改查
1. 增
INSERT INTO client VALUES(1,20,'zhangsan','female');
插入所有字段INSERT INTO client(id,name) VALUES(2,'zhansgan');
插入部分字段- 省略的列需要被定义为允许
NULL
值(无值或空值) - 在表中给出默认值。表示如果不给出值,将使用默认值。
- 省略的列需要被定义为允许
INSERT INTO client(id,name) SELECT id,name from oldClient;
- 插入检索出的语句,它可以用一条INSERT插入多行,不管SELECT返回多少行,都将被INSERT插入。
Create TABLE CustCopy AS SELECT * from Customers;
- 创建一个新表,将Customers表的整体内容复制到新表中。
2. 改
UPDATE client SET gender='female';
修改所有数据(使用较少),修改范围较大。UPDATE client SET gender='mael' WHERE id=1;
修改部分数据(使用较多),修改范围较小UPDATE client SET gender='male' , age=30 WHERE id=2;
修改多个字段,中间用英文逗号分隔。
3. 删
DELETE FROM client;
删除该表的所有数据,可以带条件删除。- 若执行该指令,只会删除table的所有数据,但是不能删除表的约束。例如自增长的继续增长。
- 使用该指令,删除的数据可以回滚。
DELETE FROM client WHERE id=2;
删除带条件的数据。TRUNCATE TABLE client;
全表删除,不能带条件删除。
4. 查询数据(基于一张表)
查询出来的顺序未必是按照 存放的顺序。
4.1 查询所有列
SELECT * FROM client
查询所有列 *代表所有
``
4.2 查询制定列
SELECT id,name,FROM client;
SELECT id, AS '编号',name AS '姓名' FROM client AS 's';
查询时指定别名(as),再多表查询时,经常使用!
4.3 查询时添加常量列
SELECT id,name ,'常量列' AS ‘常数列的表头’ FROM cilent;
4.4 查询是合并列
SELECT id,name (a2+a3) AS '新项目' FROM client;
只能合并数值类型
4.5 查询时去除重复记录
SELECT DISTINCT gender FROM client;
SELECT DISTINCT(gender) FROM client;
**PS:**不能部分使用DISTINCT
:
- 该关键字作用于所有的列,不仅仅是跟在其后的那一列。
- 例如你指定
select distinct id,name
,除非指定的两列完全相同,否则所有的行都会被检索出来。
4.6 条件查询
-
逻辑条件 and or
SELECT * FROM client WHERE id=2 AND name='zhangsan';
两个字段以上的之间一定会有逻辑关系! -
比较条件 > 、<、 >=、 <=、 ==、 <>或!=(不等于) between and
SELECT * FROM client WHERE 指定项>=70 AND 指定项2<=90;
SELECT * FROM client WHERE 制定项 BETWEEN 70 AND 95
SELECT * FROM client WHERE gender <> 'male';
寻找性别不为男的数据。SELECT * FROM client WHERE age in (16,18)
IN
:Where子句中用来指定要匹配值的清单的关键字,功能和OR相当。- IN操作符一般比一组OR操作符执行的更快。
-
判空(null / 空字符串) is null /is not null
NULL :没有值
空字符串 :有值。SELECT * FROM client WHERE address IS NULL ;
判断NULLSELECT * FROM client WHERE address='';
判断空字符串SELECT * FROM client WHERE address IS NULL OR address='';
包括NULL和空字符串。SELECT * FROM client WHERE address IS NOT NULL AND address<>'';
-
模糊查询
只适用于字符串类型。
通常使用以下替换标记
%
:表示任意字符出现任意次数(可以是0次)_
:(下划线)表示一个字符。[]
:指定一个字符集,SELECT * FROM client WHERE name LIKE 'zhang%';
SELECT * FROM client WHERE name LIKE 'zhang_' ;
4.7 聚集函数
常用的聚集函数:sum()
avg()
max()
min()
count()
。
这些函数都很高效,它们返回的结果一般比你在自己的客户端应用程序中计算要快得多。
AVG()
返回某列的平均值select avg(prod_price) AS avg_price from products;
- 该函数忽略列值为
NULL
的行。
COUNT()
返回某列的行数- 使用
count(*)
对表中的行数目进行计数。(不忽略NULL
值) - 使用
count(列名)
同样也是对表中该列的行数目进行计数。(忽略NULL
值)
- 使用
MAX()
返回某列的最大值- 用于文本数据时,MAX()返回该列排序后的最后一行。
- 忽略值为NULL的行
MIN()
返回某列的最小值- 用于文本数据时,MAX()返回该列排序后的最前面的行。
- 忽略值为NULL的行
SUM()
返回某列值之和- 忽略值为NULL的行
select sum(item_price*quantity) AS total_price from OrderItems where order_num = 20005;
4.8 分页查询 (limit 起始行,查询几行)
起始行从0开始select * from client limit 0,2;
4.9 查询排序(order by
)
4.9.1 按多个列排序
select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
先按照 prod_price
进行排序,然后按照prod_name
进行排序。
4.9.2 按列位置排序
select prod_id,prod_price,prod_name from products order by 2,3;
select清单中指定的是列的相对位置而不是列名。order by 2.3
表示先按prod_price
排序,再按照prod_name
进行排序。
4.9.3 指定排序方向
**PS:**如果想在多个列上进行降序排序,必须对每一列指定DESC
关键字。asc
:顺序,反序。数值:递增,字母:自然顺序。desc
:反序。
4.11分组查询(group by)
分组筛选后查询
七. 数据处理
1. 创建计算字段
- 拼接字段(例如:拼接两个字符串)
select province + '('+county+')' AS userAddress from address order by county;
- 执行算术计算
select id,count,price,count*price AS money from OrderItems;
- 算术操作符有四种。
+
、-
、*
、/
。
2. 使用函数处理数据
由于每个DBMS都有自己的特性,所以这里就不列出每个DBMS的函数。只举例说明如何运用。
2.1 文本处理函数
UPPER()
将文本转换成大写。select vend_name,UPPER(vend_name) AS vend_name_upcase from vendors order by vend_name
RTRIM()
去掉字符串右边的空格。LTRIM()
去掉字符串左边的空格。TRIM()
去掉字符串中的空格。
2.2数值处理函数
abs()
返回一个数的绝对值cos()
返回一个角度的余弦exp()
返回一个数的指数值PI()
返回圆周率
2.3 日期处理函数
Year()
返回日期的年份。(Mysql)
3 分组数据
3.1 创建分组
select vend_id,count(*) as num_prods from products group by vend_id;
Group By
子句可以包含任意数目的列,因而可以对分组进行嵌套。Group By
子句必须出现在Where子句之后,Order By
子句之前。1
3.2 过滤分组
select vend_id,count(*) AS orders from Orders Group By cust_id HAVING count(*) >=2
用Having
来过滤分组,上一句SQL语句的意思是 对小于两个订单的分组进行过滤。
where
与having
的区别:
- 前者是在数据分组前进行过滤,后者实在数据分组后进行过滤。
- where排除的行不在这些分组中。
- 使用
HAVING
时应该结合GROUP BY
子句,而WHERE
子句用于标准的行级过滤。
3.3 分组和排序
Group by
和ORDER BY
经常完成相同的工作,但他们非常不同。
- 前者对行进行分组,但输出可能不是分组的顺序;后者对产生的输出排序。
- 一般在使用
GROUP BY
子句时,应该也给出ORDER BY
子句,这是保证数据正确排序的唯一方法。
select order_num , count(*) AS items from orderItems group by oredr_num having count(*) >=3 order by items,oredr_num;
3.4 SELECT 子句的顺序
select(必) .....from.....where ....group by .... having .....order by
4. 子查询
sql中允许子查询,即嵌套在其他查询中的查询。
在SELECT语句中,子查询总是从内向外处理。
select cust_id from orders where order_num IN (
select order_num from orderItems where prod_id = 'RGAN01');
在实际的DBMS中,实际上执行了两个操作。
再比如:
select cust_name,cust_contact from customers where cust_id IN(
select cust_id from Orders where order_num IN(
select order_num from OrderItems where prod_id = '45274'
)
);
上述sql代码执行了三个 select子语句。
对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
PS:
- 作为子查询的select语句只能查询单个列。
- 使用子查询并不总是执行这类数据检索的最有效的方法。
select cust_name,cust_state,(
select count(*) from orders where orders.cust_id = Customers.cust_id) As orderNUM
) from customers order by cust_name;
order
是一个计算字段,她是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。
cuwstomers.cust_id
加.
是全限定列名。
5. 联结表
关于什么 是联结,请上百度搜索,百度比我讲的清楚。我只简单的点一下。
数据存储在多个表中,有些业务需要将他们联系起来,检索出来。
联结是一种机制,用来在一条SELECT语句中关联表,因此称为联结。
使用特殊的语法,可以联结多个表返回一组输出。
5.1 创建联结
简单写法:
select vend_name,prod_name,prod_price from vendors,Products where
vendors.vend_id = Products.vend_id;
**PS:**在引用的列名会产生歧义时,必须使用完全限定列名。
上边使用的联结方式称为等值联结,它基于两个表之间的相等测试,这种联结也成为内联结。
另外一种标准写法:
select vend_name,prod_name,prod_price from vendors INNER JOIN Products
on vendors.vend_id = Products.vend_id;
两个表之间的关系是以INNER JOIN
指定的部分FROM
子句。在使用这种语法时,联结条件用特定的ON
子句而不是WHERE
。
至于选用哪种方法就需要看具体的DBMS文档。
5.2 联结多个表
select prod_name,vend_name,prod_price,quantity from OrderItems,Products,Vendors Where
Products.vend_id = Vendors.vend_id
And OrderItems.prod_id = Products.prod_id
And order_num = 20007;
不要联结不必要的表,联结的表越多,性能下降的越厉害。
6 创建高级联结
6.1 使用表别名
-
使用表别名
from 表名 AS 别名
表别名只在查询执行中使用,与列别名不一样,表别名不返回到客户端。
6.2 使用不同类型的联结
-
自联结
通常作为外部语句哟个来替代从相同表中检索数据的子查询语句。虽然结果相同,但是使用联结要比使用子查询效率高很多。
- 使用子查询:
select id, name,address from user where address = (select address from user where age = 15);
- 使用自联结:
select c1.name from user as c1,user as c2 where c1.address = c2.address and c2.age = 15;
- 使用子查询:
-
自然联结
- 实际上,我们建立的每个内联结都是自然联结。
-
外联结
有两种外联结形式,它们之间的唯一差别是所关联的表的顺序。
- 左外联结
LEFT OUTER JOIN
包含其所有行的表在左边 - 右外联结
RIGHT OUTER JOIN
包含其所有行的表在右边
- 左外联结
八、使用视图
1. 视图是什么?
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
他不包含任何列和数据,包含的只是一个查询。
2. 视图有什么作用?
- 简化复杂的SQL操作。在编写查询业务时,直接重用,不需要知道实现的细节。
- 保护数据(可以授权用户访问表的特定部分的权限,而不是整个表的访问权限)
- 更改数据格式和表示。
3. 如何创建视图?
那先说一下 视图的规则和限制。(详细的还是要根据所使用的DBMS来决定)
- 与表一样,视图必须唯一命名。
- 创建视图,必须有足够的访问权限
创建视图:
CREATE VIEW ProductCustomers AS Select cust_name,cust_contact,prod_id from customers,Orders,OrderItems where customers.cust_id = Orders.cust_id and OrderItems.order_num = Orders.order_num;
这个 叫ProductCustomers
的视图,他联结了三个表,返回已订购了任意产品的所有顾客的列表。
比如,我们检索订购了产品id为0001
的纪梵希
的顾客。
select cust_name,cust_contact from ProductCustomers where prod_id = '0001';
实际执行上边的sql语句,视图中的select会先执行。
用视图重更新格式化检索出的数据
create view viewTest AS select RTRIM(province) + '('+RTRIM(county)+')' AS address from User;
该试图将用户的省份和国籍进行格式化输出。
End
SQL基本的会用之后,学习原理就相对来说简单,更有针对性。