摘要:操作符操作符用来指定范围。聚集函数我们经常需要汇总函数,而不是把它们实际检索出来。这种类型的检索例子确定表中行数获得表中行组的和找出表列所有行某些特定的行的最大值,最小值和平均值聚集函数运行在行组上,计算和返回单个值的函数。
想更一进步的支持我,请扫描下方的二维码,你懂的~
数据库 database: 一个以某种有组织的方式存储的数据集合。理解数据库最简单的方法是想象成一个文件柜。
表 table:某种特定类型数据的结构化清单。表可以保存顾客清单、产品目录或者其他信息清单。
特定类型 表示存储在表中的数据是一种类型的数据或一个清单,功能专一。决不应该把两种类型的表混合在一起。
模式 schema:关于数据库和表的布局及特性的信息。比如表存储什么样的数据,数据如何分解,各部分信息如何命名等等。
列 column:表由列组成。列是表中的一个字段。所有表都是由一个或多个列组成的。
行 row:表中的一个记录。
主键 primary key:一列(或者一组列)其值能够唯一区分表中的每个行
表中任意列都可以作为主键,只要满足如下条件:
任意两行都不具有相同的主键值
每一行都必须有一个主键值(即不许是NULL)
SQL 结构化查询语言
mysql 命令行 进入mysql
或者
mysql -u username -p
然后输入密码
use创建库:
create database dbname;
删除库
drop database dbname;
选择数据库,使用use关键字;如
use kvseg;show
显示可用的数据库列表
SHOW DATABASES
返回可用的数据库的一个列表。
显示一个数据库内的表的列表,使用
SHOW TABLES
显示表列
SHOW COLUMNS FROM customers导入导出
从Linux系统导入
先在mysql创建一个数据表,选择进去,然后导入外部数据库进入表。
create database dbname; use dbname; source dir
dir是.sql文件的路径。如果不清楚路径可以输入pwd指令查看当前路径
从mysql导出到系统
mysqldump -u root -p news > news.sql
select 子句顺序
子句 说明 select 要返回的列或表达式 from 从中检索数据的表 where 行级过滤 group by 分组说明 having 组级过滤 order by 输出排序顺序 limit 要检索的行数
为了使用select检索表数据,必须至少给两条信息 -- 想选择什么(哪一列),以及从什么地方(数据库)选择
检索单个列
比如检索cust_name字段(列),如果没有明确排序查询结果,则得到未排序的数据。
select cust_name from customers;
检索多个列
比如从customers 检索cust_id,cust_name两列;
select cust_id,cust_name from customers;
检索所有列
select * from products;
检索不同的行(去重)
select distinct vend_id from products;
限制结果
select 返回匹配的所有行,也可以返回第一行或者前几行,可使用LIMIT关键字
比如返回不多于5行;
select prod_name from products limit 5;
比如从行5开始的5行
select prod_name from products limit 55;
排序检索数据
使用select语句的order by 子句,根据需要排序检索出数据。
按单个列排序
select * from products order by 列名(keyword for sorting)
order by 子句取一个或多个列的名字,据此对输出进行排序。
按多个列排序
按照多个列排序时,排序完全按照所规定的顺序进行。换句话说,对于上述中的输出,仅仅在多个行具有相同的segk(第一关键字)值时才对按照randnum(第二关键字)排序。
升序?降序?
默认是升序,降序是DESC
还可以实现按照第一个关键字降序,第二个关键字升序
DESC只用到直接位于其前面的列明。如果想在多个列上进行降序排列,必须对每个列指定DESC关键字。
IN A CONCLUSION: order by子句必须是select语句的最后一条子句。
op | descriptions |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< / > | 小于 / 大于 |
<= / >= | 小于等于/ 大于等于 |
Between | 在指定的两个值之间 |
is NULL | 空值检查 |
过滤数据
使用where过滤数据:从大量数据中检索出根据搜索条件/过滤条件过滤出来的数据。
mysqlselect * from products where prod_price = 55;
过滤不匹配
mysqlselect * from products where prod_price <> 55;
范围检测
mysqlselect * from products where prod_price between 30 and 60;
空值
mysqlselect * from products where prod_price is Null;
多条件,组合 and / or
> 组合where子句(使用操作符and/or):更高级的search criteria. MYsql 允许给出多个WHERE子句。 这些子句以两种方式使用:Not and In。
计算次序问题: 使用圆括号明确的分组相应的操作符。
比如选择segk 为118 或者 120的行,并且随机数小于等于2;
因为sql对and进行优先级处理。
任何时候后使用具有and 和 or操作符的where子句,都应该使用圆括号明确的分组操作符,不要过分依赖默认的计算次序。使用圆括号来消除歧义。
IN操作符
IN操作符用来指定范围。(等同于or)
NOT操作符
否定它之后所跟的任何条件
在where 条件中使用REGEXP关键字。
基本字符匹配
检索列prod_name 包含文本1000的所有行:
进行OR匹配
相当于:或操作 “|”
匹配几个字符之一
只想匹配特定的字符。 可以通过指定一组用[和]括起来的字符来完成。
[456]定义了一组字符,他的意思是匹配4或5或6. []是另一种形式的OR语句。[456]是[4|5|6]的缩写。
匹配范围
[1-3] a-z都是合法的范围、
匹配特殊字符
正则表达式语言由特殊含义的特殊字符构成。
. 在正则表达式中表示匹配任何一个字符
比如匹配prod_name中包括on字符串的行:
那如何匹配.,[],|,-?
为了匹配特殊字符,必须用为前导。 比如.表示查找·
匹配字符类
匹配多个实例
再比如 匹配连在一起的4位数字:
sticks? : s后的?使s可选,因为?匹配它前面紧跟的任何字符的0次或者1次出现。
[:digit:]匹配任意数字,因而它为数字的一个集合。{4}确切地要求它前面的字符出现4次。
所以[:digit:]{4}匹配连在一起的任意4位数字。
定位符
目前为止所有例子都是匹配一个串中任意为止的文本。为了匹配特定为止的文本,需要使用定位符。
拼接字段
存储在数据库表中的数据一般不是应用程序所需要的格式。我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
计算字段(字段 = 列,不过数据库列一般称为列,而字段通常用于计算字段中)并不实际存在于数据库表中,计算字段是运行时在select语句内创建的。
拼接 concatenate 将值联结到一起构成单个值
在MySQL的select语句中,可使用Concat()函数来拼接两个列。
如创建由两列组成的标题:生成一个供应商报表,需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。此报表需要单个值,而表中数据存储的两个列vend_name和vend_country中。还需要用括号将vend_country括起来。
新创建的列用AS赋一个别名
去除空白
Ltrim() RTrim() Trim()
执行算术计算
比如物品单单表存储物品的价格和数量,但是不需要存储每个物品的总价格(用价格乘以数量即可)。 为打印发票,需要物品的总价格。即需要增加一列,根据已有的列计算出来。
left() 串左边字符 length() 串长度 locate() 找出串的一个子串 lower() 转为小写 ltrim() 去掉左边空格 right() 返回串右边字符 rtrim() 去掉串右边空格 soundex() 返回字符串soundex值 upper() 大写
将选择的文本转换成大写
select Upper(vend_name) from vendors;
Soundex()函数:将任何文本传转换为描述其语音表示的字母数字模式的算法。(语音匹配?对发音比较而不是对字幕比较)
日期函数日期和时间函数 affffdate() 增加一个日期-天或周 addtime() 增加一个时间 curdate() 返回当前日期 curtime() 返回当前时间 date() 返回日期时间的日期部分 datediff() 计算两个日期差 date_add() 高度灵活的日期运算函数 date_format() 返回一个格式化的日期或时间串 day() 返回一个日期的天数部分 dayofweek() 对于一个日期,返回对应的星期几 hour() minute() month() now() 当前日期和时间 second() time() 当前日期时间的时间部分 year()
一般,应用程序不使用用来存储日期和时间的格式,因此日期和时间函数总是被用来读取,统计和处理这些值。
MySQL的日期格式:yyyy-mm-dd。 比如 2005-09-01
但是这样的where order_date = "2005-09-01"不可靠。因为order_date存储的数据类型是datatime. 这种类型存储日期及时间值。比如存储的order_date值为2005-09-01 11:30:05,则where order_date = "2005-09-01"就会匹配失败。
所以最安全的方法是Date()函数,Date(order_date)指示MySQL提取列的日期部分。
select cust_id, order_num from orders where Date(order_date) = "2005-09-01";
再比如想要检索出2005年9月下的所有订单。
select cust_id, order_num from orders where Year(order_date) = 2005 and Month(order_date) = 9;聚集函数
我们经常需要汇总函数,而不是把它们实际检索出来。
这种类型的检索例子:
1. 确定表中行数
2. 获得表中行组的和
3. 找出表列(or 所有行某些特定的行)的最大值,最小值和平均值
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值的和
求某一列的平均值
select avg(prod_price) as avg_price from products;
- 计数
使用count(*)对表中行的数目进行计数(whether null or not)
使用count(column)对特定列具有值的行进行计数,忽略null
求和
使用sum()返回指定列值的和
目前为止所有计算都是在表的所有数据或匹配特定的where子句的数据上进行的。
group by 子句指示MySQL分组数据,然后对每个组进行聚集(计算),而不是整个结果集进行计算。
where 和 have 的区别:
where在分组前过滤,having在分组后过滤
数据分组
1.group by 可以包含任意数目的列 2.group by 中每个列都必须是检索列或有效的表达式(但不能使聚集函数) 3.除聚集函数外,select语句中的每个列都必须在group by子句中出现 4.如果分组列有Null值,Null将作为一个分组返回 5.group by 子句必须出现在where子句之后, order by 之前
首先看products 这个表
主键是prod_id 产品id,每一个产品都对应一个供应商ID,产品名,产品价格,以及简介。
如果我们查看该产品表的供应商信息:
如果想进一步获知每个供应商提供多少种产品,就应该对供应商进行分组: 比如供应商1001提供3种产品,供应商1002提供2种产品,供应商1003提供7种产品,供应商1005提供2种产品。
分组:
mysqlselect vend_id, count(*) as num_prods from products group by vend_id;
过滤分组
除了能用group by 分组数据外,还允许过滤分组,规定包括哪些分组,排除哪些分组。例如:可能想要列出至少有两个订单的所有顾客。为了得出这种数据,必须给予完整的分组,而不是个别的行进行过滤。
having 非常类where, where能做的having都能做,唯一差别是where过滤行,having过滤分组。
下面列出订单表orders的情况,每个表的主键是订单编号,每行还有订单日期和顾客id.
如果想要统计出订单数目超过2的顾客id
增加的having子句,过滤了count(*)>=2那些分组。
where和having组合使用,可以进行更强功能的操作。 如:列出提供了2个以上,价格为10以上的产品的供应商:
先用wehre子句过滤了所有价格至少为10的行,然后按照vend_id分组数据,having子句过滤计数为2或2以上的分组。
分组和排序
可以涉及数据库多个表,检索数据的语句。
子查询用做过滤 in
订单存储在两个表(orders,orderitems)中。
客户信息存储在customers表中
如果需要列出订购TNT2物品的所有客户:
需要包含如下步骤:
step 1. 检索包含物品TNT2的所有订单的编号。
step 2. 检索具有前一步骤列出的订单编号的所有客户ID
step 3. 检索前一步骤所返回的所有客户ID的信息
可以把一条select语句返回的结果用于另一条select语句的where子句 -- 也可以使用子查询来把3个查询组合成一条语句。
select * from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = "TNT2"));
作为计算字段使用子查询
如果需要显示customers表中每个客户的订单总数。 这需要使用customers,orders两个表。
使用select count(*) 对表中的行进行计数,并通过where 子句过滤行(通过过滤id)
orders是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在这个例子中,该子查询执行了5次,因为检索出了5个客户。
外键 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
可伸缩性: 能够适应不断增加的工作量而不失败。关系型数据库比非关系型数据库的可伸缩性好。
联结: 联结是一种机制,用来在一条select语句中关联表,因此称之为联结。使用特殊的语法,可以连接多个表返回一组输出,联结在运行时关联表中正确的行。
联结的引入是为了解决 为了带来关系数据更大的可伸缩性而分解数据为多个表,但是带来的代价:数据分散存储到多个表,怎么用单条select语句检索出数据。
两个表: 供应商表vendors, 产品表 products
创建(等值)联结
当然也可以按照主键,外键关系联结多个表。但是出于性能的考虑,这种处理可能是非常耗资源的。联接的表越多,性能下降越厉害。
select 嵌套语句实现的返回订购产品TNT2的客户列表的解决方法,可以使用级联:
对比:
select * from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = "TNT2"));
VS
使用不同类型的联结
自联接
> 查询product_id为DTNTR(products表)的供应商,这个供应商生产的其他物品(vendors表)
有两种方法,一种是使用嵌套,另一种是使用自联接。
嵌套:
自联接(同一个表别名为p1,p2);
> 有时候,处理联结要比查理子查询快的多。
外部联结
联结包含了哪些在相关表中没有关联的行。这种类型的连接称为外部链接。
内部连接:
使用outer join来指定联结的类型。而不是在where子句。
使用带聚集函数的连接
检索所有客户及每个客户所下的订单数。
使用联结和联结条件
> 1. 注意所使用的联结类型。一般使用内部联结,但是使用外部联结也是有效的.
> 2. 保证使用正确的联结条件,否则将返回不正确的数据。
> 3. 应该总是提供联结条件,否则会得出笛卡尔积。
> 4. 在一个联结中可以包含多个表,甚至对每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试他们之前,分别测试每个联结。排除故障更简单。
使用Union操作符将多条select语句组合成一个结果集,并将结果作为单个查询结果集返回。这些组合查询称为并或复合查询compound query
有两种情况,需要使用组合查询:
1. 在单个查询中从不同的表返回类似结构的数据;
在单个表执行多个查询,按单个查询返回数据。
Union
假如需要价格小于等于5的所有物品的信息,还想包括供应商1001,1002生产的所有物品(不考虑价格)
使用where:
使用union:
union 从查询结果集中自动去除了重复的。
全文搜索MyISAM 支持全文本搜索
InnoDB 不支持全文本搜索
通配符和正则表达式的缺陷:
性能
通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
明确控制
使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么。
智能化的结果
通配符和正则表达式匹配并非是智能化的选择结果。
一个特殊词的搜索将会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行。
为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引。在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
在索引之后,select可与match()和agianst()一起使用以执行搜索。
match()指定被搜索的列,against()指定要使用的搜索表达式。
创建表时启用全文本搜索。接受fulltext子句,给出被索引列的
CREATE TABLE productnotes( note_id int NOT NULL AUT_INCREMENT, note_text text NULL, FULLTEXT(note_text)
create table 接受 full text子句。
进行全文搜索SELECT note_text FROM tb_name WHERE Match(note_text) Against(‘rabbit’)
全文搜索(compared to like and regexp) 一个重要部分就是对结果排序。具有较高优先级的列先返回(因为这些行很可能就是你真正想要的行):比如先返回第三个词rabbit的行,再返回第20个词rabbit的行。
一个对比:
like是按照出现顺序,先返回第20个词rabbit的行,再返回第三个词rabbit的行。
放宽所返回的全文本搜索结果的范围。
比如:
想找到所有提到anvils的注释,只有一个注释包含了词anvils,但是你还想找出可能与你的搜索有关的所有其他行,即使不包含词anvils.
在使用查询扩展时,mysql对数据和索引两遍扫描完成。利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。
首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
其次,MySQL检查这些匹配行并选择所有有用的词。
再其次,MySQL再次进行全文本搜索们这次不仅使用原来的条件,而且还使用所有有用的词。
布尔文本搜索以布尔方式,可以提供关于如下内容的细节:
要匹配的词;
要排斥的词(即使它包含其他指定的词,但是如果它包括了排斥的词,也不返回该行);
排列提示(指定某些词比其他词更重要,更重要的词等级更高);
表达式分组;
另外一些内容
SELECT note_text FROM productontes WHERE Match(note_text) Against(‘heavy’ IN BOOLEAN MODE)
排除了任何包含rope*的行。
例子:
搜索匹配safe和combination。降低后者的等级。
Insert插入行到数据库表。
可以
插入完整的行
插入行的一部分
插入多行
插入某些查询的结果
insert into customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values("Pep E. LaPew", "100 Main Streat", "Los Angels", "CA", "90046", "USA", null, null );
安全的insert语句,在表名后的括号里明确地给出列名。
当插入多行数据时候,用单条insert语句处理多个插入比使用多条insert语句快。
还可以插入检索出的数据。insert select :假如想从另一个表中合并客户列表到你的customers表,不需要每次读取一行,然后再用insert插入,可以直接 insert select.
形式:
mysqlinsert into TableName(ColomnName1,...) select (ColomnName1,...) from AnotherTableName;
不要求列明匹配,使用的是列的位置。
mysqlupdate TABLENAME set ColomnName1 = NewValue, ColomnName2 = NewValue where ... (过滤条件)更新所有行
如果上面例子,没有where过滤条件,就是更新所有行。
设置为null(if表定义允许为null)
mysqlupdate TABLENAME set ColomnName1 = Null, where ... (过滤条件)删除特定的行
mysqldelete from TABLENAME where ... (过滤条件)删除所有行
如果上面例子,没有where过滤条件,就是删除所有行。
Note: delete 语句是从表中删除行,甚至是删除表中所有行。但是delete不删除表本身。
Note: 更快的删除,如果想从表中删除所有行,不要使用delete, 可使用truncate table语句(完成相同功能,但是速度更快,实际上是删除原来的表并重新创建一个表,而非逐行删除表的数据)。
mysqlCREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL , PRIMARY KEY (cust_id) ) ENGINE=InnoDB;
主键只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引。
MySQL内部具有各自不同的功能和特性的多种引擎,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索;
Memory 在功能等同于MyISAM, 但由于数据存储在内存(而非磁盘)中,速度很快(特别适合于临时表);
MyISAM 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
更新表定义,alter table.
mysqlalter table vendors add vend_phone char(20);
删除表,而非内容
mysqldrop table tableName;
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/46457.html
摘要:必知必会读书笔记关系数据库的三级模式结构如图外模式对应视图和部分表,模式对应基本表,内模式对应于存储文件基本表是本身独立存在的表,在中一个关系就对应一个基本表。由于满足了第二范式,表示每个非主属性都函数依赖于主键。 MySQL 必知必会读书笔记 (1) 关系数据库的三级模式结构 如图:外模式对应视图和部分表,模式对应基本表,内模式对应于存储文件 showImg(https://...
摘要:在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。用户可能没有创建存储过程的安全访问权限。表示进行检查查找。 存储过程 大型系统必须得要存储过程和触发器吗,from 知乎 一般情况下,Web 应用的瓶颈常在 DB 上,所以会尽可能的减少 DB 做的事情,把耗时的服务做成 Scale Out,这种情况下,肯定不会使用存储过程; ...
摘要:编辑大咖说阅读字数用时分钟内容摘要对于真正企业级应用,需要分布式数据库具备什么样的能力相比等分布式数据库,他们条最佳性能优化性能优化索引与优化关于索引与优化的基础知识汇总。 mysql 数据库开发常见问题及优化 这篇文章从库表设计,慢 SQL 问题和误操作、程序 bug 时怎么办这三个问题展开。 一个小时学会 MySQL 数据库 看到了一篇适合新手的 MySQL 入门教程,希望对想学 ...
阅读 1337·2021-11-11 16:53
阅读 3532·2021-10-12 10:11
阅读 1845·2019-08-30 15:53
阅读 1460·2019-08-30 13:15
阅读 2173·2019-08-30 11:25
阅读 1683·2019-08-29 11:24
阅读 3071·2019-08-27 14:19
阅读 1535·2019-08-26 13:53