目录
1.了解SQL
数据库(database)
表(table)同一数据库表名唯一
模式(schema)
列(column)表由列组成。列存储表中某部分的信息。 字段(field)
数据类型 int,char(),varchar(),datetime...
行(row) == 数据库记录(record)
主键(primary key) 唯一,is not null
子句(clause)
NULL无值(no value) 它与字段包含0、空字符串或仅仅包含空格不同。
确定值是否为NULL,不能简单地检查是否等于NULL。
操作符(operator) 用来联结或改变WHERE子句中的子句的关键字,也称为逻辑操作符(logical operator)。
通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件。
2.检索数据
select 字段名,字段名,... from 表名; select * from 表名;
distinct(去重) 它指示数据库只返回不同的值,作用于所有的列,不仅仅是跟在其后的那一列
select distinct 字段名 from 表名;
limit 限制输出结果 limit offset,length ---> offset代表起始页数,length代表输出行数(默认offset是0)
select 字段名 from 表名 limit length; select 字段名 from 表名 limit offset,length;
3.排序检索数据
order by (默认升序排序)(asc == ascending) 位于子句最后
desc == descending(降序排序) 只应用到直接位于其前面的列名
select * from table order by 字段名,字段名,...; 首先按第一个字段名进行排序,只有第一个字段名的结果有多个值相同时才会按第二个字段名排序 select * from table order by 字段名 desc;
4.过滤数据
where
5.用通配符进行过滤
百分号(%)通配符 在搜索串中,%表示任何字符出现任意次数。
下划线(_)通配符 只匹配单个字符,而不是多个字符。
6.创建计算字段
拼接字段
一、concat()函数
1、功能:将多个字符串连接成一个字符串。
2、语法:concat(str1, str2,...)
说明:返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
3、举例:select concat (id, name, score) as 别名 from 表名;
二、concat_ws()函数
1、功能:和concat()一样,但是可以指定分隔符(concat_ws就是concat with separator)
2、语法:concat_ws(separator, str1, str2, ...)
说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。
3、举例:select concat ('#',id, name, score) as 别名 from 表名;
三、group_concat()函数
1、功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
2、语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator] )
说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator分隔符是一个字符串值,默认为一个逗号。
3、举例:select name,group_concat(id order by id desc separator '#') as 别名 from 表名 group by name;
四、concat_ws()和group_concat()联合使用
题目:查询以name分组的所有组的id和score
举例:select name,group_concat(concat_ws('-',id,score) order by id) as 别名 from 表名 group by name;
trim函数
1、用于返回删除字符串str两侧所有remstr字符串之后的子串,remstr默认为空格。
remstr为可选项,在未指定情况下,可删除空格。
2、返回字符串str,其中所有remstr前缀和/或后缀都已被删除。
语法
trim([{both|leading|trailing} [removed_str]] from str)
实例
select trim(' 123 '); select ltrim(' 123 '); select rtrim(' 123 '); select trim(leading 'jia' from 'jiashanlingjia'); select trim(trailing 'jia' from 'jiashanlingjia');
select curdate();
使用Curdate()函数返回当前日期和时间。
7.函数
文本处理函数(常见)
数值处理函数(常见)
8.汇总数据
聚集函数(aggregate function)对某些行运行的函数,计算并返回一个值。
SQL聚集函数
avg() AVG()函数忽略列值为NULL的行。 count() • 如果指定列名,则COUNT()函数会忽略指定列的值为NULL的行,但如果COUNT()函数中用的是星号(*),则不忽略。 max(),min() 要求指定列名,忽略列值为NULL的行。 sum() 忽略列值为NULL的行。
9.分组数据
group by()
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
WHERE过滤指定的是行而不是分组。
having
HAVING和WHERE的差别 WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。 WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。
SELECT子句及其顺序
11.使用子查询
作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误
12.联结表
笛卡儿积(cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
select vend_name, prod_name, prod_price from Vendors, Products;
内联结(inner join)
select vend_name, prod_name, prod_price from Vendors inner join Products on Vendors.vend_id = Products.vend_id;
表别名只在查询执行中使用。与列别名不一样,表别名不返回到客户端。
自联结(self-join)、自然联结(natural join)和外联结(outer join)
有两种基本的外联结形式:左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说,调整FROM或WHERE子句中表的顺序,左外联结可以转换为右外联结。 另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。(mysql不支持)
13.组合查询
并(union)或复合查询(compound query)
多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是,SQL也允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。 UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)。 使用UNION ALL,DBMS不取消重复的行。 在用UNION组合查询时,只能使用一条ORDER BY子句,它必须位于最后一条SELECT语句之后。
14.插入数据
insert
1.插入完整的行
insert into 表名 列名 值; insert into 表名 值;
2.插入行的一部分
如果表的定义允许,则可以在INSERT操作中省略某些列。 省略的列必须满足以下某个条件。 ❑ 该列定义为允许NULL值(无值或空值)。 ❑ 在表定义中给出默认值。这表示如果不给出值,将使用默认值。(default)
3.插入某些查询的结果
insert into 表名 列名 select 表名 from 列名; 前后列名不必相同(数据类型需要相同)
从一个表复制到另一个表
create table customers_copy as select * from customers; * 可以换成 customers 中的一部分列名
15.更新和删除数据
update
update 表名 set 更新内容,更新内容,... (where 条件);
(空字符串用’’表示,是一个值),而NULL表示没有值。
delete
delete from 表名 (where 条件);
truncate
truncate table; 删除整个表中所有的行比 delete 更快
16.创建和操纵表
创建表
drop table if exists 表名; create table 表名 (列名(唯一) 类型,列名 类型,...,列名 类型,列名 类型);
不要把NULL值与空字符串相混淆。 NULL值是没有值,不是空字符串。 如果指定’'(两个单引号,其间没有字符),这在NOT NULL列中是允许的。 空字符串是一个有效的值,它不是无值。 NULL值用关键字NULL而不是空字符串指定。
指定默认值
default
更新表
alter table Vendors add vend_name char(20);
删除表
drop table 表名; 永久删除整个表
重命名表
alter table 旧表名 rename [to/as] 新表名; rename table 新表名 to 旧表名;
17.使用视图
视图为虚拟的表。
它们包含的不是数据而是根据需要检索数据的查询。
视图提供了一种封装SELECT语句的层次,可用来简化数据处理,重新格式化或保护基础数据。
视图内部不能使用order by子句。
CREATE VIEW CustomersWithOrders AS SELECT Customers.cust_id, Customers.cust_name, Customers.cust_address, Customers.cust_city, Customers.cust_state, Customers.cust_zip, Customers.cust_country, Customers.cust_contact, Customers.cust_email FROM Customers JOIN Orders ON Customers.cust_id = Orders.cust_id; SELECT * FROM CustomersWithOrders;
18.高级SQL特性
约束
主键:唯一且非空
primary key
外键
唯一约束:数据唯一
unique
检查约束:用来保证一列(或一组列)中的数据满足一组指定的条件
check
索引
create index 索引名 on 表名(列名);