SQL快速入门 ( MySQL快速入门, MySQL参考, MySQL快速回顾 )
程序员文章站
2023-08-18 10:34:16
SQL 先说点废话,很久没发文了,整理了下自己当时入门 SQL 的笔记,无论用于入门,回顾,参考查询,应该都是有一定价值的,可以按照目录各取所需。SQL数据库有很多,MySQL是一种,本文基本都是SQL通用标准,有些标准很不统一的地方就用MySQL的写法了。希望本文帮你快速了解SQL的基本操作和概念 ......
sql
先说点废话,很久没发文了,整理了下自己当时入门 sql 的笔记,无论用于入门,回顾,参考查询,应该都是有一定价值的,可以按照目录各取所需。sql数据库有很多,mysql是一种,本文基本都是sql通用标准,有些标准很不统一的地方就用mysql的写法了。希望本文帮你快速了解sql的基本操作和概念。
文章格式上有些问题,可以点击这里获得更加的阅读体验
目录
检索
- 检索某表中单个列:
select 列名
from 表名; - 检索某表中多个列:
select 列名,列名,列名
from 表名;
- 检索某表中所有列:(尽量不用)
select *
from 表名;
- 只检索某表中某列里不重复的项:
select distinct 列名 (如果有两列或以上,需要这些列组合起来是不重复的)
from 表名;
- 检索指定行数:
select 列名
from 表名
limit 5 offset n; (mysql中,选第n行后的五行。 offset n 可不填写默认为0,其它 sql 数据库中有不同写法)
过滤检索结果
- 寻找指定行:(举例)
select prod_name, prod_price from products where prod_price = 3.49;(和字符串比较加单引号,数值不用)
查找列名为prod_name和列名为prod_price的两列,检索其中prod_price = 3.49; 的所有行。 = 可以替换为其它操作符,如下表 | 操作符 | 描述 | | --- | --- | | = | 等于 | | <> | 不等于 | | > | 大于 | | < | 小于 | | >= | 大于等于 | | <= | 小于等于 | | between | 在某个范围内 | | like | 搜索某种模式 |
- 组合where子句:
select prod_id, prod_price, prod_name from products where vend_id = 'dll01' and prod_price <= 4;
and 连接同时需要满足的两个条件,or即满足一个条件即可,not 找到与后边条件不匹配的行。 且not,and和or可以组合使用,用小括号声明逻辑循序。 `where vend_id in ( 'dll01', 'brs01' ) ` in 起到作用类似于or,速度更快,逻辑更清晰。
- 通配符搜索:
select prod_id, prod_name from products where prod_name like '%bean bag%';
%表示任意字符出现任意次数。也可以出现在中间位置。 _ 表示一个字符。 [charlist] 表示包含在里面的任意字符,[^charlist]不包含在里面的任意字符。 少使用通配符,搜索速度较慢。
数据汇总处理
- 算术计算:
select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems where order_num = 20008;
`expanded_price`成为计算出来的新列。
- 字符串拼接:
不同数据库有差异,mysql中:
select concat(vend_name , vend_country) as vend_title from vendors order by vend_name;
concat_ws( ':' , vend_name , vend_country) 形式第一个参数为分隔符。 其他数据库用+或者||拼接字符串。
- 日期时间处理不同数据库差异较大。
- 内置方法:
- 求平均值:
select avg(prod_price) as avg_price
from products;
表products中prod_price的平均值。返回给 avg_price。
可以配合where语句计算指定行的平均值。 - 求最大值:max(prod_price)
- 求最小值:min(prod_price)
- 求和:sum(prod_price)
- 近似的小数点后几位:round(column_name,decimals)
- 当前日期: now()
- 求行数:
select count(*) as num_cust
from customers;
求表customers有几行。返回给num_cust。
*可以换成指定列如:cust_email。计算所得行数不包括该列值为null的行。
distinct 列名,求不重复的列。
- 求平均值:
- 组合:
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;
分组
- 创建分组:
select vend_id from products group by vend_id;
根据 vend_id列中内容对 vend_id分组, 第一行换成 `select vend_id, count(*) as num_prods` 即对每一个组计算行数。 注意:多行null会分为一组,group by子句必须出现在where子句之后,order by子句之前。 可以对一个以上的列进行 group by
- 过滤分组:
having:类似于where。唯一的差别是,where过滤行,而having过滤分组。
select vend_id, count(*) as num_prods from products where prod_price >= 4 group by vend_id having num_prods >= 2;
过滤出有(两个价格大与4的产品)的供应商
给检索结果排序
select company, ordernumber from orders order by company desc, ordernumber asc
可以 order by 列名1,列名2; 先按列名1内容排序,排序结果相同的按列名2内容排序。
列名后接 desc 按该列内容倒序排列,asc 正序(默认)。
order by 命令放在查询、分组等语句的最后。
表操作
- 创建表:
create table newproducts ( prod_id char(10) not null, vend_id char(10) not null, prod_name char(254) not null, prod_price decimal(8,2) not null, prod_desc varchar(1000) null );
not null 非空约束,不允许列中有null值下面介绍其他约束。 列的设置可以加上默认值,如not null后边接 default current_date() ,默认值为当前日期。(每个数据库获取当前日期语句不同。) 后面接 primary key 即设置改列为主键。 后面接 auto_increment 即设置为自增,只有int型可以设置。
- 约束:
每个列可以有一种或几种约束。- not null 非空约束.
- unique 唯一约束,可唯一标识数据库表中的每条记录。
- primary key 主键约束,唯一标识数据库表中的每条记录,唯一且非空。
- foreign key 外键约束,一个表中的 foreign key 指向另一个表中的 primary key。
- check 检查约束,用于限制列中的值的范围。
- default 默认约束,用于向列中插入默认值
每个表可以有多个 unique 约束,但是每个表只能有一个 primary key 约束。
每种约束可以创建表时设置好,也可以后期增删.
- 索引:
在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
create index 索引名 on person (列名[,列名])
- 复制表或表中部分列:
create table custcopy as select * from customers;
创建customers表的复制,custcopy。
- 修改表:
alter table vendors add vend_phone char(20); alter table vendors drop column vend_phone;
各数据库有不兼容现象,复杂表操作列可能要新建表删除旧表。 alter 还可以用来添加删除约束,删除索引等。
- 删除表:
drop table custcopy;
- 重命名表:
rename table oldtable to newtable;
插入数据
- 插入整行或部分行:
insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values('1000000007', 'toy land', '123 any street', 'new york', 'ny', '11111', 'usa', null, null);
插入整行时,可省略 customers 括号内的内容,即按照列的顺序,分别插入数据(不推荐)。省略 customers 括号内的内容时,无内容的列必须用null占位。 插入部分行时,把要插入的列填入 customers 括号内,与values内容一一对应,没有提到的列默认null或其他默认值。
- 插入查询到的值:
insert into customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) select cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_city, cust_state, cust_zip, cust_country from custnew;
把从custnew表中查到的内容插入 customers表中。一次插入多行的方式。
更新和删除数据:
- 更新数据:
update customers set cust_email = 'kim@thetoystore.com' where cust_id = '1000000005';
步骤为,要更新的表,要更新的列,要更新的行。一个set可以跟多个列用逗号隔开。 删除某个值,即设置他为null。
- 删除数据:
delete from customers where cust_id = '1000000008';
删除表中指定整行,删除部分列用update 在update或delete语句使用where子句前,应该先用select进行测试,保证它过滤的是正确的记录,以防编写的where子句不正确。如果不写where会更新或删除所有行内容。
子查询-迭代查询
- 一种形式:
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 = 'rgan01'));
先从第二个括号选择符合条件的order_num,成为第二个括号内容,再向上找到第一个括号,查到符合条件的cust_id返回给第一个括号,最后根据第一个括号内容执行主查询语句。性能问题不要嵌套太多层。 也就是对customers表的查询要用到orders表查询后返回的内容,对orders表的查询要用到orderitems表查询后返回的内容。
- 另一种形式:
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers
根据customers 表中的cust_id,去orders表中取得计算后的数据。
- 同一个表迭代查询:
select cust_id, cust_name, cust_contact from customers where cust_name = (select cust_name from customers where cust_contact = 'jim jones');
联结-关联多个表
- 两个表:
- 内联结
select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id;
根据两个表共同的列vend_id把vendors, products关联起来。 与
select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;
结果相同。都是内联结,前一种是后一种的简写。 inner 可省略。
- 外联结:
select customers.cust_id, orders.order_num from customers left outer join orders on orders.cust_id = customers.cust_id;
left outer join 把customers表中没有被匹配到的 cust_id 也联结进去(会显示在结果里)。 right outer join 是把orders表中没有被匹配到的 cust_id 也联结进去(会显示在结果里)。 full outer join 会把两张表中没有匹配到的列也显示出来(mysql 不支持,可通过 union 实现) outer 可省略。
- 多个表:
select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'rgan01';
作用同子查询中a。同样不要关联太多,有性能问题。其中表名可以使用别名,如:
select cust_name, cust_contact from customers as c, orders as o, orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = 'rgan01';
组合查询
select cust_name, cust_contact, cust_email from customers where cust_state in ('il','in','mi') union all select cust_name, cust_contact, cust_email from customers where cust_name = 'fun4all';
union all 链接两句查询语句,统一返回结果,包含重复结果。
去掉all以后,去掉重复结果。
此处(从同一个表中查询)可以用where , or代替。
常用作从不同表中查询时,只要列数相同就可以拼接到一起,列名按照第一句中查询的列名。
视图
对已存在的表,进行筛选,数据处理,联结等操作后返回的数据,创建的虚拟表。视图是为了重用和简化常用的查询。对视图的查询同表。
视图总是显示最近的数据。每当用户查询视图时,数据库引擎通过使用 sql 语句来重建数据。
- 创建视图:
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;
对orderitems, orders和customers三个表进行联结,联结后结果形成 productcustomers 视图,可以把它当一张表来查询。
-
删除视图:
drop view productcustomers;
其它
- 存储过程:为以后的使用保存一条或多条sql语句,用于简化操作。每个数据库不同,见数据库具体介绍。
- 事务处理:事务处理模块中的语句,或者全部执行,或者全部不执行。可以设立保留点,执行失败时回到保留点。
- 创建数据库:
create database database_name
删除数据库:
drop database 数据库名称