当前位置: 澳门新濠3559 > 数据库 > 正文

数据类型与数据表的操作,ANY、SOME与ALL关键字用

时间:2019-11-08 05:44来源:数据库
MySQL的数据类型、数据库操作、针对单表的操作以致轻易的笔录操作可参照:MySQL入门笔记(风姿浪漫卡塔 尔(阿拉伯语:قطر‎ Mysql 规范 : 第一字与函数名称全体大写 数据库名称、

MySQL的数据类型、数据库操作、针对单表的操作以致轻易的笔录操作可参照:MySQL入门笔记(风姿浪漫卡塔 尔(阿拉伯语:قطر‎

Mysql

规范

  • 第一字与函数名称全体大写
  • 数据库名称、表名称、字段名称全体大写
  • SQL语句必需以分集团结尾

操作数据库

  • Mysql的登录:mysql -h host -u user -p
  • 数据库暗中同意端口号:3306
  • 体现当前服务器版本:SELECT VERSION();
  • 来伏贴前时光:SELECT NOW();
  • 展现当前客户:SELECT USER();
  • 展现错误:SHOW WARNINGS;
  • 创办数据库:CREATE {DATEBASE | SCHEME} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;
  • 剔除数据库:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
  • 改正数据库:ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

数据类型与数据表的操作

数据类型是指列、存储进程参数、表明式和一些变量的数量特征,它调整了数量的寄存格式,代表了区别的音信连串。
整型

  • TINYINT:1个字节,有符号值:-128~127,无符号值0~255
  • SMALLINT:2个字节,有符号值:-32768~32767,无符号值:0~65535
  • MEDIUMINT:3个字节,有符号值:-8388608~8388607,无符号值:0~16777215
  • INT:4个字节,有符号值:-2147483648~2147483647,无符号值:0~4294967295
  • BIGINT:8个字节

浮点型

  • FLOAT[(M,D)]:M是数字总位数,D是小数前边的位数,要是M和D被轻松,依据硬件允许的约束来保存值。单精度浮点数精确到大要7位小数位。
  • DOUBLE[(M,D)]

日午时间型

  • YEAR
  • TIME
  • DATE
  • DATETIME
  • TIMESTAMP:1970-1-1 0:0:0 ~ 2037

字符型

  • CHAR(M)
  • VARCHAR(M)
  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT
  • ENUM('value1','value2',...):枚举值,最多65535个值
  • SET('value1','value2',...):集合,最多64个成员

数据表是数据库中最入眼的结缘成员之风流倜傥,是其他对象的功底。
1.开荒数据库:USE db_name;
2.创造数据表:CREATE TABLE [IF NOT EXISTS] table_name (column_name data_type,...);

CREATE TABLE IF NOT EXISTS t1(
     username VARCHAR(20) NOT NULL,
     age TINYINT UNSIGNED,
     salary FLOAT(8,2) UNSIGNED
 );

3.翻看数据库列表:SHOW TABLE [FROM db_name] [LINK 'pattern' | WHERE expr];
4.翻看数据表的构造:SHOW COLUMNS FROM tbl_name;
5.插入记录:INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...);
6.记下查找:SELECT expr,... FROM tbl_name;
7:自动编号:AUTO_INCREMENT,且必须与主键组合使用,暗中同意意况下,发轫值为1,每一次增量为1

主键节制:PRubiconIMA宝马X3Y KEY,每张数据表只好存在叁个主键,主键保障记录的唯大器晚成性,主键自动为NOT NULL。

唯意气风发限定:UNIQUE KEY,唯风流浪漫节制能够确定保证记录的唯意气风发性,唯生机勃勃约束的字段可认为空值NULL,每张数据表能够存在八个唯意气风发限定。

暗中同意限定:当插入记录时,若无分明为字段赋值,则自动授予暗中同意值。

CREATE TABLE tb6(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    sex ENUM('1','2','3') DEFAULT '3'
);

约束

  • 封锁有限协理数据的完整性微风流罗曼蒂克致性
  • 自律分为表级节制和列级节制
  • 自律类型包罗:非空节制、主键约束、唯黄金时代节制、暗中认可限制、外键约束

外键限制的准则

  • 父表和子表必得使用相同的存放引擎,并且不许接受有时表
  • 数据表的仓库储存引擎只可以为InnoDB
  • 外键列和参照列必需有所相通的数据类型。当中数字的长度或是还是不是有大户为必需黄金时代致,而字符的尺寸则足以差别
  • 外键列和参照列必得成立索引,假设外键列空头支票索引的话,MySQL将自动创立参照列的目录

外键节制的参照操作

  • CASCADE:从父表删除也许更新且活动删除恐怕更新子表中相配的行
  • SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL,假诺利用了该选项,必须保险子表列未有一点点名NOT NULL
  • RESTEvoqueICT:屏相对父表的删除或更新操作
  • NO ACTION:标准SQL的最主要字,在MySQL中与REST哈弗ICT雷同

8.修正数据表

  • 增多单列:ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
  • 丰富多列:ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...);
  • 删除列:ALTER TABLE tbl_name DROP [COLUMN] col_name;

9.增多主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type (index_col_name,...);

插入
INSERT [INTO] ... VALUES(...);
INSERT [INTO] ... SET col_name = value1,...;
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...;
示例:

INSERT INTO test (username) SEELCT username FROM USERS WHERE age >= 30;

更新
单表更新:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT} [,col_name2={expr1 | DEFAULT}] ... [WHERE where_conditiion];

示例:

UPDATE users SET age = age +5;
UPDATE users SET age = age - id,sex = 0;
UPDATE users SET age = age + 10 WHERE id % 2 = 0;

剔除记录
单表删除

DELETE FROM tbl_name [WHERE where_condition];

示例:

DELETE FROM users WHERE id = 6;

留意:删除后再插入数据,id为前段时间最大id+!实际不是去添补被删除的id。

别名
AS:SELECT userid AS uid FROM users;

分组
GROUP BY:GROUP BY {col_name | position} [ASC | DESC],...
ACS:升序
DESC:降序

分组条件
HAVING:HAVING where_condition,HAVING中若现身字段名,则必得出未来SELECT中

排序
ORDER BY:[ORDER BY {col_name | expr | position} [ASC | DESC],...]

界定查询结果回到的数量
[LIMIT {[offser,] row_count OFFSET offset}]
示例:

SELECT * FROM users LIMIT 2

子查询(Subquery卡塔尔是提议今后任何SQL语句内的SELECT子句。
例如:SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1称为Outer Query / Outer Statement,SELECE col2 FROM t2 称为Subquery。
子查询的外围可以是:SELECT、INSERT、UPDATE、SET或DO。
子查询能够分包八个第一字或标准,如DISTINCT、GROUP BY、OCR-VDER BY、LIMIT、函数等。
示例:

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tbd_goods);

使用 [NOT] IN 的子查询
=ANY 运算符与IN等效
!=ALL恐怕 <>ALL运算符与NOT IN等效

使用 [NOT] EXISTS 的子查询
假如子查询重临任何行,EXISTS将赶回TRUE,不然为FALSE。

用ANY、SOME、ALL修饰的比较运算符

ANY SOME ALL
> 、>= 最小值 最小值 最大值
< 、<= 最大值 最大值 最小值
= 任意值 任意值
<> 、!= 任意值

示例:

SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超极本');

老是类型
在MySQL中,JOIN、CROSS JOIN 和 INNE凯雷德 JOIN是等价的。
使用 ON 关键字来设定连接条件,也足以采纳 WHERE 来代替,通常状态下使用 ON 关键字来设定连接条件,使用 WHERE 关键字展开结果集记录的过滤。
INNER JOIN:内连接,仅突显切合连接条件的笔录

UPDATE tdb_goods INNER JOIN tbd_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

SELECT goods_id,goods_name,cate_name FROM tdb_goods 
INNER JOIN tdb_goods_cates ON 
tdb_goods.cate_id = tdb_goods_cates.cate_id;

LEFT [OUTER] JOIN:左外连接,呈现左表的漫天记录和右表中切合条件的笔录,借使右表中空头支票,则显得为NULL。
RIGHT [OUTER] JOIN:右外连接,展现右表中的全体记下,和左表中相符条件的笔录,假使左表中不设有,则显得为NULL。

多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price 
FROM tdb_goosd AS g 
INNER JOIN tdb_goods_cates AS c 
ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b 
ON g.brand_id = b.brand_id;

A LEFT JOIN B join_condition

  • 数量表B的结果集正视数据表A
  • 数据表A的结果集依照左连接条件重视全数数据表(B表除此而外卡塔 尔(阿拉伯语:قطر‎
  • 左外连接条件决定哪些寻觅数据表B(在未曾点名WHERE条件的情景下卡塔尔
  • 倘若数据表A的某条记下切合WHERE条件,不过在数码表B不设有切合连接条件的记录,将生成三个存有列为空的附加的B行

万大器晚成利用内接二连三查找的笔录在连接数据表中不设有,况且在WHERE子句中品尝以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将要找到切合连接条件的笔录后停止寻找更多的行。

CREATE...SELECT:创设数据表的还要将查询结果写入到数据表,CREATE TABLE [IF NOT EXISTS] [(create_detinition,...)] select_statement;
示例:

CREATE TABLE tdb_goods_brands(
    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(40) NOT NULL
)
SELECT brand_name FROM tdb_goods GROUP BY brand_name;

UPDATE tdb_goods INNER JOIN tdb_goods_brands 
ON tdb_goods.brand_name = tdb_goods_brands.brand_name 
SET tdb_goods.brand_name = tdb_goods_brands.brand_id;

ALTER TABLE tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

最佳级分类表设计
示例:

CREATE TABLE tdb_goods_types(
    type_id SMALLINT UNSIGNED PRIMARY AUTO_INCREMENT,
    type_name VARCHAR(20) NOT NULL,
    parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

有关询问:通过自个儿连接完毕即同三个数据表对其本身实行三番五次。实行自身连接时,必须求取别名。MySQL无法兑现递归查询。

//查询父类型
SELECT s.type_id,s.type_name,p.type_name 
FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types  AS p
ON s.parent_id = p.type_id;

//查询子类型
SELECT p.type_id,p.type_name,s.type_name 
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s 
ON s.parent_id = p.type_id;

//查询父类以及子类的无重复目录
SELECT p.type_id,p.type_name,s.type_name 
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s 
ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id;

//查询父类以及子类的数目
SELECT p.type_id,p.type_name,COUNT(s.type_name) AS child_count 
FROM tdb_goods_types AS p
LEFT JOIN tdb_goods_types AS s 
ON s.parent_id = p.type_id
GROUP BY p.type_name
ORDER BY p.type_id;

多表删除

//模拟单表的多表删除,删除重复记录中id较大的记录
DELETE t1 FROM tdb_goods AS t1 
LEFT JOIN (
    SELECT goods_id,goods_name 
    FROM tdb_goods 
    GROUP BY goods_name 
    HAVING count(goods_name) >= 2
) AS t2
ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;

运算符

字符函数

  • CONCAT():字符连接

    SELECT CONCAT('HELOO','WORLD');

    SELECT CONCAT('HELLO','-','WORLD');

    SELECT CONCAT(firstname,lastname) AS fullname FROM test; //用于姓名的连年

  • CONCAT_WS():使用内定的相间符进行字符连接

    SELECT COMCAT_WS('-','A','B','C'); //结果为A-B-C

    SELECT CONCAT_WS('-','HELLO','WORLD');

  • FORMAT():数字格式化

    SELECT FORMAT(12345.345,2); //结果为12,345.35,四舍五入

  • LOWEENCORE():调换到小写字母

  • UPPEENCORE():转变来大写字母
  • LEFT():获取左边字符

    SELECT LEFT('MySQL',2); //结果为My

    SELECT LOWER(LEFT('MySQL',2)); //结果为my

  • EvoqueIGHT():获取侧面字符

  • LENGTH():获取字符串长度
  • LTLacrosseIM():删除前导空格
  • RT揽胜IM():删除后续空格
  • T宝马7系IM():删除前导和持续空格也许内定字符

    SELECT TRIM(LEADING '?' FROM '??MySQL???'); //结果为MySQL???

    SELECT TRIM(TRAILING '?' FROM '??MySQL???'); //结果为??MySQL

    SELECT TRIM(BOTH '?' FROM '??MySQL???'); //结果为MySQL

  • SUBST奥德赛ING():字符串截取

    SELECT SUNSTHavalING('MySQL',1,2); //结果为My,MySQL中字符串位数从1起来

    SELECT SUNSTRING('MySQL',3); //结果为SQL

    SELECT SUNST逍客ING('MySQL',-1); //结果为L,即从右起头尾数操作

  • [NOT] LIKE:方式相配

    SELECT 'MySQL' LIKE 'M%';

    //查询字符串中包蕴%的字符串,如下,ESCAPE即跳过1后边的%的辨析SELECT * FROM test WHERE name LIKE '%1%%' ESCAPE '1';

  • REPLACE():字符串替换

    SELECT REPLACE('??My??SQL???','?',''); //结果为MySQL

数值运算函数

  • CEIL():进大器晚成取整
  • DIV:整数除法
  • FLOO途达():舍意气风发取整
  • MOD:取余数(取模)
  • POWER():幂运算
  • ROUND():四舍五入
  • TRUNCATE():数字截取

    SELECT CEIL(3.01); //结果为4

    SELECT FLOOR(3.99); //结果为3

    SELECT 3 DIV4; //结果为0

    SELECT 5 MOD 3; //结果为2

    SELECT POWER(3,3); //结果为27

    SELECT ROUND(3.1415,3); //结果为3.142

    SELECT TRUNCATE(1234.567,2); //结果为1234.56,未有四舍五入操作

正如运算符函数

  • [NOT] BETWEEN ... AND ...:[不]在界定以内
  • [NOT] IN():[不]在列出值范围内
  • IS [NOT] NULL:[不]为空

    SELECT 15 BETWEEN 1 AND 22; //结果为1,即为true

    SELECT 10 IN(5,10,15,20); //结果为1,即意味着10在所列数字系列之内

    SELECT 11 IN(5,10,15,20); //结果为0,即表示11不在所列数字种类之内

    SELECT NULL IS NULL; //结果为1

    SELECT '' IS NULL; //结果为0

    SELECT 0 IS NULL; //结果为0

日龙时间函数

  • NOW():当今日子和岁月
  • CUMuranoDATE():超越天期
  • CURTIME():当前不久子
  • DATE_ADD():日期变动
  • DATEDIFF():日期差值
  • DATE_FORMAT():日期格式化

    SELECT NOW(); //再次来到当前不久未时间

    SELECT DATE_ADD('2013-2-11',INTERVAL 365 DAY); //后一年 SELECT DATE_ADD('2013-2-11',INTERVAL -365 DAY); //前一年 SELECT DATE_ADD('2013-2-11',INTERVAL 1 YEAR); //YEAR WEEK DAY

    SELECT DATEDIFF('2011-1-1','二零一二-2-1'); //总计八个日子之间的造化差值

    SELECT DATE_FORMAT('2013-1-2','%m/%d/%Y'); //结果为01/02/2013

音信函数

  • CONNECTION_ID():连接ID
  • DATABASE():当前数据库
  • LAST_INSERT_ID():最后插入记录的ID
  • VEXC60SION():版本音讯

聚合函数

  • AVG():求平均值
  • COUNT():计数
  • MAX():最大值
  • MIN():最小值
  • SUM():求和

    SELECT AVG(goods_price) FROM tbd_goods;

    SELECT COUNT(goods_id) FROM tdb_goods;

    SELECT MAX(goods_price) FROM tdb_goods;

    SELECT MIN(goods_price) FROM tdb_goods;

    SELECT SUM(goods_price) FROM tdb_goods;

加密函数

  • MD5():消息摘要算法
  • PASSWO途胜D():密码算法

自定义函数的操作
客商自定义函数(user-defined function,UDF卡塔 尔(阿拉伯语:قطر‎是风流洒脱种对MySQL扩大的路线,其用法与内置函数相同。

自定义函数的多少个需要条件:

  • 参数
  • 返回值

函数能够再次回到率性档期的顺序的值,同样能够选拔那个品种的参数,不过函数的参数和再次来到值之间没有必要的维系,MySQL规定自定义函数的参数数量不能够抢先10贰15个。

开创自定义函数:

CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body

函数体由官方的SQL语句构成;
函数体能够是差十分的少的SELECT可能INSERT语句;
函数体假设为复合结构则利用BEGIN...END语句;
复合结构能够分包注脚、循环、调整结构;

创设示范:

//创建一个转换了日期时间显示格式的不带参数的自定义函数
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时:%i分:%s秒');

//计算两个数的平均值
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1 + num2)/2;

/* 此时需要自定义设置命令结束符为'//',屏蔽';' */
CREATR FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSET_ID();
END//

//删除已经存在的函数
DROP FUNCTION function_name;

仓库储存进程
SQL命令 -> MySQL引擎深入分析 -> 语法精确的可识别命令 -> 履行回来结果 -> 结果回到给顾客端
仓库储存进度是SQL语句和操纵语句的预编写翻译集合,以一个名号存款和储蓄并作为三个单元管理。

存储进度的帮助和益处

  • 加强SQL语句的作用和灵活性
  • 达成很快的实行进程
  • 裁减互联网流量

创设存款和储蓄进度

CREATE [DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type
  • IN:表示该参数的值必得在调用存款和储蓄进度时内定
  • OUT:表示该参数的值能够被寄存进程改换,况且能够重回
  • INOUT:表示该参数的调用时钦点,而且能够被改造和重返

特性

  • COMMENT:注释
  • CONTAIN SQL:包罗SQL语句,不过不分包读可能写多少的语句
  • NO SQL:不包含SQL语句
  • READS SQL DATA:饱含读数据的言语
  • MODIFIES SQL DATA:包含写多少的讲话
  • SQL SECU揽胜极光ITY {DEFINELacrosse | INVOKE汉兰达}:指明哪个人有权力来奉行

过程体

  • 经过题由法定的SQL语句构成
  • 经过题能够是大肆SQL语句(对于数据的增加和删除改查以至多表的连年卡塔 尔(英语:State of Qatar)
  • 进度体如若为复合结构则应用BEGIN...END语句
  • 复合语句能够包括注解、调控、循环语句

    //制造多个赢得版本的储存进度CREATE PROCEDURE sp1() SELECT VE福特ExplorerSION();

    / 当时亟需自定义设置命令结束符为'//',屏蔽';' / CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) BEGIN DELETE FROM users WHERE id = p_id; END//

    / 那时候内需自定义设置命令停止符为'//',屏蔽';' / CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED) BEGIN DELETE FROM users WHERE id = p_id; SELECT COUNT(id) FROM users INTO userNums; END//

    //

    //调用存储过程CALL sp_name[()] CALL sp_name([parameter[,...]])

MySQL存款和储蓄引擎
MySQL将数据以差异的本领存款和储蓄在文书(内部存款和储蓄器卡塔尔中,这种本领称之为存款和储蓄引擎。每风姿罗曼蒂克种存储引擎使用分歧的仓库储存机制、索引技巧、锁定水平,最后提供布满且不一样的功力。

并发调节:当八个延续记录进行更动时保障数据的生机勃勃致性和完整性。管理并发调节使用锁系统来消除。

  • 分享锁(读锁卡塔 尔(阿拉伯语:قطر‎:在同期段内,七个顾客能够读取同一能源,读取进度中数量不会发送任何变化。
  • 排他锁(写锁卡塔尔:在任曾几何时候只好有三个用户来写入财富,当进行写锁时会堵塞别的的读锁恐怕写锁操作。

锁颗粒

  • 表锁:是意气风发种草费超小的锁计谋
  • 行锁:是生龙活虎种草销最大的锁计谋

事务:事务用于保险数据库的完整性。
政工的特点

  • 原子性
  • 一致性
  • 隔离性
  • 持久性

外键:保险数据大器晚成致性的宗旨。
索引:对数据表中的一列或多列的值实行排序的风姿浪漫种结构。索引是拓宽记录快捷牢固的大器晚成种办法。索引分为平时索引、独一索引、全文索引。

支撑的蕴藏引擎

  • MyISAM
  • InooDB
  • Memory
  • CSV:不扶持索引
  • Archive
  • 布莱克Hole:黑洞引擎,写入的多少都会流失,日常用来做多少复制的过渡
特点 MyISAM InooDB Memory Archive
存储限制 256TB 64TB
事务安全 - 支持 - -
支持索引 支持 支持 支持 -
锁颗粒 表锁 行锁 表锁 行锁
数据压缩 支持 - - 支持
支持外键 - 支持 - -

纠正数据表的寄放引擎

  • 在Mysql的配备文件中,私下认可存款和储蓄引擎设置:default-storage-engine=INNODB
  • 因此创办数量表命令完成,CREATE TABLE table_name(...) ENGINE = engine_name;
  • 改善已创制的表的积攒引擎:ALTEPRADO TABLE table_name ENGINE = engine_name;

重大是为着解决三个select语句,因为语法相比较奇异,没看懂,百度了某个结果

五、子查询

  子查询可回顾地通晓为查询中的查询,即子查询外界自然还应该有黄金时代层查询,何况这里的查询实际不是仅仅指SELECT的查询操作,而是席卷INSERT、DELETE、SET等操作在内的兼具操作。

    select的东西一定于贰个有时表,as就给那有的时候表取个名字。

1. 选择相比运算符的子查询

operand comparison_operator [{ANY | SOME | ALL}] (subquery)

  operand为操作数,即参加相比运算的数;在言语的最终为subquery子查询,子查询必需写在小括号内;ANY、SOME与ALL关键字用于子查询重回结果不唯黄金时代的景况,上面通过例子表达多少个根本字的机能。

  例:存在下表test

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | John    |   20 |
|  2 | Kity    |   24 |
|  3 | Tommy   |   26 |
|  4 | Jackson |   31 |
|  5 | Pat     |   19 |
|  6 | Darling |   33 |
|  7 | Tamzin  |   29 |
|  8 | Hoking  |   21 |
|  9 | Obama   |   22 |
| 10 | Bush    |   25 |
+----+---------+------+

  现要求查询年龄超过平均值的的人,依据常常的办法,则要求先总括出年龄平均值SELECT AVG(age) FROM test;(结果为25.0000卡塔尔,再依照输出的结果开展询问SELECT * FROM test WHERE age > 25;;若使用子查询,则可将两步合为一步:

SELECT * FROM test WHERE age > (SELECT AVG(age) FROM test);

  那正是子查询,而ANY、SOME以致ALL关键字的作用则是,当子查询再次回到的结果存在多条记下时,规定其比较艺术,举个例子,以后要查询比id<=3的记录中随性所欲生机勃勃记录的age小的笔录:

SELECT * FROM test WHERE age < ANY (SELECT age FROM test WHERE id <= 3);

  SOME的效果与利益与ANY雷同,而ALL则是如其意义平日的“全部”,举例在上头的例子中,若将ANY改成ALL,则赶回的结果的age必需高于id<=3的==全体==记录的age。


2. 使用[NOT] IN 的子查询

operand [NOT] IN (subquery)

  [NOT] IN的功用为检验有个别记录是还是不是存在钦赐表中,个中IN等效于= ANY;NOT IN等效于!= ALL或者<> ALL。

  例:以下多少个语句结果完全等同:

SELECT * FROM test WHERE age = ANY (SELECT age FROM test WHERE id <= 3);

SELECT * FROM test WHERE age IN (SELECT age FROM test WHERE id <= 3);

 

3. 使用[NOT] EXISTS的子查询

  若子查询再次回到看肆行,EXISTS再次来到1;不然为0。

  例:以下操作再次回到结果则为0:

SELECT EXISTS (SELECT age FROM test WHERE id < 0);

SELECT语句的风度翩翩体化语法

六、多表操作

SELECT语句的整体语法为: SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} FROM tableexpression[,…][IN externaldatabase] [WHERE…] [GROUP BY…...
SELECT语句的风度翩翩体化语法为:
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
说明:
用中括号([])括起来的一些代表是可选的,用大括号({})括起来的部分是意味必需从当中采用在那之中的叁个。

1. 连接

table_reference {[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN} table_reference ON conditional_expr

  连接,如其字面意思,连接七个数据表,使其爆发物理上的关联(即事实上存在涉嫌,而非仅仅为逻辑上的涉嫌卡塔 尔(英语:State of Qatar)。ON关键字用于设定其后的连天条件;{[INNER|CROSS]JOIN | {LEFT|RIGHT}[OUTER]JOIN}为总是类型,分别是INNEQX56 JOIN内延续、LEFT [OUTER] JOIN左外连接和LacrosseIGHT [OUTER] JOIN右外接连两种:

  ==内接连==为仅重返适合连接条件的笔录,如下图所示,内连接重返的结果为两表的混杂部分。个中JOIN、INNE兰德CRUISERJOIN和CROSS JOIN功能相近,均代表内接连。

澳门新濠3559 1

  例:存在以下两表:

  users:

+----+---------+------+----------+
| id | name    | age  | location |
+----+---------+------+----------+
|  1 | John    |   20 | 3        |
|  2 | Kity    |   24 | 3        |
|  3 | Tommy   |   26 | 4        |
|  4 | Jackson |   31 | 3        |
|  5 | Pat     |   19 | 4        |
|  6 | Darling |   33 | 2        |
|  7 | Tamzin  |   29 | 2        |
|  8 | Hoking  |   21 | 2        |
|  9 | Obama   |   22 | 3        |
| 10 | Bush    |   25 | 1        |
| 11 | Richard |   22 | 12       |
| 12 | Andy    |   22 | 13       |
+----+---------+------+----------+

  lname:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | 北京市    |
|  2 | 上海市    |
|  3 | 广州市    |
|  4 | 深圳市    |
|  5 | 杭州市    |
+----+-----------+

  现进行以下操作:

SELECT * FROM users INNER JOIN lname ON users.location=lname.id;

  结果为:

+----+---------+------+----------+----+-----------+
| id | name    | age  | location | id | name      |
+----+---------+------+----------+----+-----------+
|  1 | John    |   20 | 3        |  3 | 广州市    |
|  2 | Kity    |   24 | 3        |  3 | 广州市    |
|  3 | Tommy   |   26 | 4        |  4 | 深圳市    |
|  4 | Jackson |   31 | 3        |  3 | 广州市    |
|  5 | Pat     |   19 | 4        |  4 | 深圳市    |
|  6 | Darling |   33 | 2        |  2 | 上海市    |
|  7 | Tamzin  |   29 | 2        |  2 | 上海市    |
|  8 | Hoking  |   21 | 2        |  2 | 上海市    |
|  9 | Obama   |   22 | 3        |  3 | 广州市    |
| 10 | Bush    |   25 | 1        |  1 | 北京市    |
+----+---------+------+----------+----+-----------+

  简单看出,重返结果为users表中的第11、12条记下,以至lname表中的第5条记下以外的具备结果,原因正是这3条记下并不设有于两表的混合之中。(若感觉麻烦精通,往下结合左外连接与右外连接相对照,那样比较轻便精通卡塔尔国

  ==左外连接==为回到左表中的全体以至右表中级知识分子足一连条件的记录,==右表官样文章的记录所对应的字段突显为NULL。==

澳门新濠3559 2

  ==右外接连==则为回到右表中的全体以致左表中级知识分子足三番两次条件的笔录,==左表不设有的笔录所对应的字段呈现为NULL。==

澳门新濠3559 3

  例:仍然为上述两表,现将上述查询操作中的内延续改为左外连接:

SELECT * FROM users LEFT JOIN lname ON users.location=lname.id;

  输出结果为:

+----+---------+------+----------+------+-----------+
| id | name    | age  | location | id   | name      |
+----+---------+------+----------+------+-----------+
| 10 | Bush    |   25 | 1        |    1 | 北京市    |
|  6 | Darling |   33 | 2        |    2 | 上海市    |
|  7 | Tamzin  |   29 | 2        |    2 | 上海市    |
|  8 | Hoking  |   21 | 2        |    2 | 上海市    |
|  1 | John    |   20 | 3        |    3 | 广州市    |
|  2 | Kity    |   24 | 3        |    3 | 广州市    |
|  4 | Jackson |   31 | 3        |    3 | 广州市    |
|  9 | Obama   |   22 | 3        |    3 | 广州市    |
|  3 | Tommy   |   26 | 4        |    4 | 深圳市    |
|  5 | Pat     |   19 | 4        |    4 | 深圳市    |
| 11 | Richard |   22 | 12       | NULL | NULL      |
| 12 | Andy    |   22 | 13       | NULL | NULL      |
+----+---------+------+----------+------+-----------+

1 FROM子句

FROM子句内定了SELECT语句中字段的根源。FROM子句前边是满含三个或多少个的表明式(由逗号分开),在那之中的表明式可为单一表名称、已封存的询问或由 INNEEscort JOIN、LEFT JOIN 或 哈弗IGHT JOIN 得到的复合结果。假若表或询问存款和储蓄在表面数据库,在IN 子句之后指明其完整路线。
例:下列SQL语句再次来到全体有定单的顾客:
SELECT OrderID,Customer.customerID
FROM Orders Customers
WHERE Orders.CustomerID=Customers.CustomeersID

2. 多表创立

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition, ...)] select_statement;

  在创设表的还要把询问结果写入该表中。

2 ALL、DISTINCT、DISTINCTROW、TOP谓词

(1) ALL 重临满足SQL语句条件的富有记录。若无指明这些谓词,默感到ALL。
例:SELECT ALL FirstName,LastName
FROM Employees
(2) DISTINCT 假设有三个记录的挑精拣肥字段的数目豆蔻梢头致,只回去三个。
(3) DISTINCTROW 假使有双重的笔录,只回去二个
(4) TOP呈现查询头尾若干记录。也可回到记录的比例,那是要用 TOP N PERCENT子句(个中N 表示百分比卡塔 尔(英语:State of Qatar)
例:再次来到5%定货额最大的定单
SELECT TOP 5 PERCENT*
FROM [ Order Details]
ORDER BY UnitPrice*Quantity*(1-Discount) DESC

3. 多表删除

DELETE tbl_name[.*] [,tbl_name[.*]] ... FROM table_references [WHERE where_condition];

  例:把上述例子中的表users中location字段在表lname中不设有的笔录删掉(即删掉第11、12条记下卡塔尔:

DELETE users.* FROM users INNER JOIN lname ON users.location != ALL(SELECT lname.id FROM lname);

3 用 AS 子句为字段取外号 

如若想为再次回到的列取一个新的标题,也许,经过对字段的总结或总计之后,发生了三个新的值,希望把它内置多少个新的列里显示,则用AS保留。
例:再次来到FirstName字段取别称称为NickName
SELECT FirstName AS NickName ,LastName ,City
FROM Employees
例:重返新的一列展现仓库储存价值
SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock
FROM Products

二 .WHERE 子句内定询问条件

1 相比较运算符
正如运算符 含义
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于
!> 不大于
!< 不小于
例:再次来到96年四月的定单
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate>#1/1/96# AND OrderDate<#1/30/96#
注意:
Mcirosoft JET SQL 中,日期用‘#’定界。日期也能够用Datevalue()函数来替代。在比较字符型的数额时,要抬高单引号’’,尾空格在可比中被忽略。
例:
WHERE OrderDate>#96-1-1#
也足以代表为:
WHERE OrderDate>Datevalue(‘1/1/96’)
动用 NOT 表明式求反。
例:查看96年11月1日之后的定单
WHERE Not OrderDate<=#1/1/96#
2 范围(BETWEEN 和 NOT BETWEEN)
BETWEEN …AND…运算符内定了要索求的叁个闭区间。
例:再次来到96年一月到96年十一月的定单。
WHERE OrderDate Between #1/1/96# And #2/1/96#
3 列表(IN ,NOT IN)
IN 运算符用来相配列表中的其余三个值。IN子句能够取代用OSportage子句连接的多级的尺度。
例:要搜索住在 London、Paris或Berlin的具备客户
SELECT CustomerID, CompanyName, ContactName, City
FROM Customers
WHERE City In(‘London’,’ Paris’,’ Berlin’)

4. 多表更新

UPDATE tables_references SET col_name1={expr1 | DEFAULT} [, col_name2={expr2 | DEFAULT}]... [WHERE where_condition];

  例:存在以下三个数据表:

  users:

+----+---------+------+-----------+
| id | name    | age  | location  |
+----+---------+------+-----------+
|  1 | John    |   20 | 广州市    |
|  2 | Kity    |   24 | 广州市    |
|  3 | Tommy   |   26 | 深圳市    |
|  4 | Jackson |   31 | 广州市    |
|  5 | Pat     |   19 | 深圳市    |
|  6 | Darling |   33 | 北京市    |
|  7 | Tamzin  |   29 | 北京市    |
|  8 | Hoking  |   21 | 北京市    |
|  9 | Obama   |   22 | 广州市    |
| 10 | Bush    |   25 | 上海市    |
+----+---------+------+-----------+

  lname:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | 上海市    |
|  2 | 北京市    |
|  3 | 广州市    |
|  4 | 深圳市    |
+----+-----------+

  现将users表中的location用lname表中相应的id进行取代,操作为:

UPDATE users INNER JOIN lname ON users.location=lname.name SET users.location=lname.id;

4 格局匹配(LIKE)

LIKE运算符核准多个富含字符串数据的字段值是或不是合营一钦赐方式。
LIKE运算符里使用的通配符
通配符 含义
? 任何四个纯净的字符
* 跋扈长度的字符
# 0~9之间的纯粹数字
[字符列表] 在字符列表里的任意气风发值
[!字符列表] 不在字符列表里的任后生可畏值

  • 钦定字符范围,两侧的值分别为其上下限
    例:再次来到寄邮资编在(171卡塔尔国555-0000到(171卡塔 尔(英语:State of Qatar)555-9999里头的客商
    SELECT CustomerID ,CompanyName,City,Phone
    FROM Customers
    WHERE Phone Like ‘(171)555-####’
    LIKE运算符的生龙活虎对体裁及意义
    样式 含义 不符合
    LIKE ‘A*’ A后跟任性长度的字符 Bc,c255
    LIKE’5
    ’ 5*5 555
    LIKE’5?5’ 5与5里头有自由二个字符 55,5wer5
    LIKE’5##5’ 5235,5005 5kd5,5346
    LIKE’[a-z]’ a-z间的即兴三个字符 5,%
    LIKE’[!0-9]’ 非0-9间的妄动一个字符 0,1
    LIKE’[[]’ 1,*
    三 .用OPAJERODE景逸SUV BY子句排序结果
    OEvoqueDE昂Cora子句按一个或三个(最多14个卡塔 尔(英语:State of Qatar)字段排序查询结果,可以是升序(ASC卡塔 尔(英语:State of Qatar)也得以是降序(DESC卡塔 尔(阿拉伯语:قطر‎,缺省是升序。ORAV4DE君越子句普通位于SQL语句的尾声。
    OENCOREDE索罗德子句中定义了多个字段,则依据字段的前后相继顺序排序。
    例:
    SELECT ProductName,UnitPrice, UnitInStock
    FROM Products
    ORDER BY UnitInStock DESC , UnitPrice DESC, ProductName
    OWranglerDE冠道 BY 子句中能够用字段在选用列表中的地点号替代字段名,能够混合字段名和地点号。
    例:下边包车型地铁口舌发生与上列相通的功力。
    SELECT ProductName,UnitPrice, UnitInStock
    FROM Products
    ORDER BY 1 DESC , 2 DESC,3
    四 .运用连接关系贯彻多表查询
    例:寻觅同叁个城市中代理商和客商的名字
    SELECT Customers.CompanyName, Suppliers.ComPany.Name
    FROM Customers, Suppliers
    WHERE Customers.City=Suppliers.City
    例:寻找成品库存量大于同风姿洒脱种产物的定单的数额的出品和定单
    SELECT ProductName,OrderID, UnitInStock, Quantity
    FROM Products, [Order Deails]
    WHERE Product.productID=[Order Details].ProductID
    AND UnitsInStock>Quantity
    另后生可畏种方法是用 Microsof JET SQL 独有的 JNNERubicon JOIN
    语法:
    FROM table1 INNER JOIN table2
    ON table1.field1 comparision table2.field2
    其间comparision 正是眼下WHERE子句用到的相比运算符。
    SELECT FirstName,lastName,OrderID,CustomerID,OrderDate
    FROM Employees
    INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
    注意:
    INNE奇骏 JOIN不可能三翻五次Memo OLE Object Single Double 数据类型字段。
    在三个JOIN语句中连连三个ON子句
    语法:
    SELECT fields
    FROM table1 INNER JOIN table2
    ON table1.field1 compopr table2.field1 AND
    ON table1.field2 compopr table2.field2 OR
    ON table1.field3 compopr table2.field3
    也可以
    SELECT fields
    FROM table1 INNER JOIN
    (table2 INNER JOIN [( ]table3
    [INNER JOER] [( ]tablex[INNER JOIN]
    ON table1.field1 compopr table2.field1
    ON table1.field2 compopr table2.field2
    ON table1.field3 compopr table2.field3
    外界连接重返越来越多记录,在结果中保存不协作的笔录,不管存不设有知足条件的笔录都要再次回到另意气风发侧的有所记录。
    FROM table [LEFT|RIGHT]JOIN table2
    ON table1.field1comparision table.field2
    用左连接来创设外界连接,在表明式的右侧的表会显示其兼具的数据
    例:不管有未有定货量,再次回到全部商品
    SELECT ProductName ,OrderID
    FROM Products
    LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID
    右连接与左连接的歧异在于:不管左侧表里有未有合作的笔录,它都从左侧表中再次来到全部记录。
    例:假诺想精晓客商的音讯,并计算各个区域的客户遍及,这个时候能够用贰个右连接,纵然某些地方未有客户,也要回去客商新闻。
    空值不会互匹同盟,能够经过外接连工夫测验被连接的有些表的字段是不是有空值。
    SELECT *
    FROM talbe1
    LEFT JOIN table2 ON table1.a=table2.c
    1 连接查询中利用Iif函数达成以0值展现空值
    Iif表达式: Iif(IsNull(Amount,0,Amout)
    例:无论定货大于或小于¥50,都要再次来到三个标记。
    Iif([Amount]>50,?Big order?,?Small order?)

七、运算符与函数

  PS:MySQL提供的函数有超级多,这里仅列举部分比较常用的函数,若须要用到别的函数,可查阅MySQL官方文书档案。

五. 分组和总计查询结果

在SQL的语法里,GROUP BY和HAVING子句用来对数码进行聚焦。GROUP BY子句指明了如约哪多少个字段来分组,而将记录分组后,用HAVING子句过滤那些记录。
GROUP BY 子句的语法
SELECT fidldlist
FROM table
WHERE criteria
[GROUP BY groupfieldlist [HAVING groupcriteria]]
注:Microsoft Jet数据库 Jet 不能够对备注或OLE对象字段分组。
GROUP BY字段中的Null值以备分组不过无法被归纳。
在别的SQL合计函数中不划算Null值。
GROUP BY子句后最多能够分包10个字段,排序优先级按从左到右的顺序排列。
例:在‘WA’地区的雇员表中按头衔分组后,寻找富有相通头衔的雇员数目大于1人的兼具头衔。
SELECT Title ,Count(Title) as Total
FROM Employees
WHERE Region = ‘WA’
GROUP BY Title
HAVING Count(Title)>1
JET SQL 中的集合函数
集中函数 意义
SUM ( ) 求和
AVG ( ) 平均值
COUNT ( ) 表明式中记录的数据
COUNT (* ) 总括记录的数目
MAX 最大值
MIN 最小值
VAR 方差
STDEV 标准标称误差
FI劲客ST 第叁个值
LAST 最终一个值

1. 字符函数

六. 用Parameters注解创立参数查询

Parameters申明的语法:
PARAMETERS name datatype[,name datatype[, …]]
中间name 是参数的标识符,能够因而标记符援引参数.
Datatype表明参数的多少类型.
接受时要把PARAMETE奥迪Q7S 注脚置于其余别的语句从前.
例:
PARAMETERS[Low price] Currency,[Beginning date]datatime
SELECT OrderID ,OrderAmount
FROM Orders
WHERE OrderAMount>[low price]
AND OrderDate>=[Beginning date]

1.1 CONCAT(str1, str2, ...)

  效用:连接八个字符串。例:SELECT CONCAT('John','Smith');出口结果为:

+------------------------+
| CONCAT('John','Smith') |
+------------------------+
| JohnSmith              |
+------------------------+

七. 功用查询

所谓功效查询,实际上是大器晚成种操作查询,它能够对数据库实行火速高效的操作.它以选拔查询为目标,筛选出相符条件的数量,再对数码开展批管理.功效查询包罗改革查询,删除查询,增多查询,和生成表查询.
1 更新查询
UPDATE子句能够并且改动三个或八个表中的数据.它也能够同期更正多个字段的值.
更新查询语法:
UPDATE 表名
SET 新值
WHERE 准则
例:英帝国顾客的定货量扩充5%,货物运输量扩张3%
UPDATE OEDERS
SET OrderAmount = OrderAmount *1.1
Freight = Freight*1.03
WHERE ShipCountry = ‘UK’
2 删除查询
DELETE子句能够使顾客删除大批量的老后生可畏套的或冗于的数据.
注:删除查询的靶子是百分之百记录.
DELETE子句的语法:
DELETE [表名.*]
FROM 来源表
WHERE 准则
例: 要删除全部94年前的定单
DELETE *
FROM Orders
WHERE OrderData<#94-1-1#
3 追加查询
INSERT子句能够将贰个或风华正茂组记录追加到三个或多个表的尾巴部分.
INTO 子句内定接纳新记录的表
valueS 关键字钦赐新记录所饱含的多少值.
INSERT 子句的语法:
INSET奥德赛 INTO 目标表或询问(字段1,字段2,…)
valueS(数值1,数值2,…)
例:扩张叁个顾客
INSERT INTO Employees(FirstName,LastName,title)
valueS(‘Harry’,’Washington’,’Trainee’)
4 生成表查询
能够一遍性地把具备满意条件的笔录拷贝到一张新表中.常常制作笔录的备份或别本或作为报表的功底.
SELECT INTO子句用来创立生成表查询语法:
SELECT 字段1,字段2,…
INTO 新表[IN 外界数据库]
FROM 来源数据库
WHERE 准则
例:为定单制作一个存档备份
SELECT *
INTO OrdersArchive
FROM Orders

1.2 CONCAT_WS(connector, str1, str2, ...)

  效率与CONCAT相近,差异之处在于CONCAT_WS的率先个参数为内定连接字符串所选取的连接符。例:存在表username:

+-----------+----------+
| firstname | lastname |
+-----------+----------+
| John      | Smith    |
| Kity      | Chan     |
+-----------+----------+

  输入指令SELECT CONCAT_WS('-', firstname, lastname) AS fullname FROM username;输出结果为:

+------------+
| fullname   |
+------------+
| John-Smith |
| Kity-Chan  |
+------------+

八. 联合查询

UNION运算能够把三个查询的结果合併到叁个结出集里展现.
UNION运算的貌似语法:
[表]查询1 UNION [ALL]查询2 UNION …
例:再次来到巴西联邦共和国具有供给商和客商的名字和都市
SELECT CompanyName,City
FROM Suppliers
WHERE Country = ‘Brazil’
UNION
SELECT CompanyName,City
FROM Customers
WHERE Country = ‘Brazil’
注:
缺省的情况下,UNION子句不回去重新的记录.如若想显示全部记录,能够加ALL选项
UNION运算须要查询全数同等数量的字段.可是,字段数据类型不必相通.
每八个查询参数中能够采纳GROUP BY 子句 或 HAVING 子句实行分组.要想以钦赐的逐条来呈现重临的数量,可以在最终叁个询问的尾部使用OREER BY子句.

1.3 FORMAT(number, dec_place)

  将数字格式化,重临结果为字符型。所谓格式化将在整数每3位用逗号分隔,并依据钦赐保留小数位数进行采纳。函数的第三个参数为举办格式化的数字,第二个参数为保留的小数位数。

  例:SELECT FORMAT(156416486435.13561651,5);的输出结果为:

+----------------------------------+
| FORMAT(156416486435.13561651, 5) |
+----------------------------------+
| 156,416,486,435.13562            |
+----------------------------------+

九. 交叉查询

陆陆续续查询能够对数码进行总额,平均,计数或任何总和总计法的计算,那几个数据通过三种消息举行分组:一个出示在表的左部,另三个突显在表的顶端.
Microsoft Jet SQL 用TRANSFROM语句创设交叉表查询语法:
TRANSFORM aggfunction
SELECT 语句
GROUP BY 子句
PIVOT pivotfield[IN(value1 [,value2[,…]]) ]
Aggfounction指SQL集结函数,
SELECT语句选取作为标题标的字段,
GROUP BY 分组
说明:
Pivot田野(field)在查询结果聚焦创制列标题时用的字段或表明式,用可选的IN子句节制它的取值.
value代表创造列题指标一定值.
例:显示在1999年里每生机勃勃季度每一人职工所接的定单的数码:
TRANSFORM Count(OrderID)
SELECT FirstName&’’&LastName AS FullName
FROM Employees INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE DatePart(“yyyy”,OrderDate)= ‘1996’
GROUP BY FirstName&’’&LastName
ORDER BY FirstName&’’&LastName
POVOT DatePart(“q”,OrderDate)&’季度’

1.4 LOWER(str)、UPPER(str)

  LOWE哈弗的效果与利益是将字符中的字母全体改造为小写,UPPECRUISER则相反。例:SELECT LOWER('TEST大写');出口结果为:

+---------------------+
| LOWER('TEST大写')   |
+---------------------+
| test大写            |
+---------------------+

十 .子查询

子查询能够清楚为 套查询.子查询是一个SELECT语句.
1 表达式的值与子查询重返的十足值做比较
语法:
表达式 comparision [ANY|ALL|SOME](子查询)
说明:
ANY和SOME谓词是同义词,与比较运算符(=,<,>,<>,<=,>=)一齐使用.再次来到一个布尔值True或False.ANY的意趣是,表明式与子查询再次回到的风流倜傥多如牛毛的值逐条相比较,只要个中的叁次比较发生True结果,ANY测验的回到 True值(既WHERE子句的结果),对应于该表明式的近些日子记下将跻身主查询的结果中.ALL测量检验则必要表明式与子查询重回的一文山会海的值的可比都产生True结果,才回再次来到True值.
例:主查询再次回到单价比其它多个折扣大于等于十分六的成品的单价要高的富有产物
SELECT * FROM Products
WHERE UnitPrice>ANY
(SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25)

2 检查表明式的值是还是不是相配子查询再次来到的风流洒脱组值的某部值
语法:
[NOT]IN(子查询)
例:重临仓库储存价值超过等于1000的成品.
SELECT ProductName FROM Products
WHERE ProductID IN
(SELECT PrdoctID FROM [Order DEtails]
WHERE UnitPrice*Quantity>= 1000)

3检查测量检验子查询是还是不是再次来到任何记录
语法:
[NOT]EXISTS (子查询)
例:用EXISTS检索大不列颠及英格兰联合王国的顾客
SELECT ComPanyName,ContactName
FROM Orders
WHERE EXISTS
(SELECT *
FROM Customers
WHERE Country = ‘UK’ AND
Customers.CustomerID= Orders.CustomerID)

1.5 LEFT(str, len)、RIGHT(str, len)

  LEFT的意义是获取字符串从左侧算起钦赐位数的字符,LANDIGHT则相反。例:SELECT LEFT('MyFunction', 5);出口结果为:

+-----------------------+
| LEFT('MyFunction', 5) |
+-----------------------+
| MyFun                 |
+-----------------------+

1.6 LENGTH(str)

  获取字符串长度。

1.7 LTRIM(str)、RTRIM(str)、TRIM()

  LTENVISIONIM的功效是删除字符串的向导空格(即字符串前边的空格卡塔尔国,RTOdysseyIM是去除字符串的一连空格(即字符串前边的空格卡塔 尔(阿拉伯语:قطر‎;

  TEnclaveIM的用法有三种,一是参数仅钦命三个字符串T讴歌ZDXIM(str),则删除该字符串的开头和世襲空格;还应该有意气风发种用法规是可钦赐删除字符串的指引或持续的钦点字符TEscortIM({LEADING | TRAILING | BOTH} del_char FROM str),LEADING为发轫,TRAILING为持续,BOTH则为两个均删除。

  例:SELECT LENGTH(' TEST ');的输出结果为:

+---------------------+
| LENGTH('  TEST   ') |
+---------------------+
|                   9 |
+---------------------+

  加上TRIM函数后:SELECT LENGTH(TRIM(' TEST '));

+---------------------------+
| LENGTH(TRIM('  TEST   ')) |
+---------------------------+
|                         4 |
+---------------------------+

  其它,若输入指令SELECT TRIM(LEADING ',' FROM ',test,');,输出结果为:

+---------------------------------+
| TRIM(LEADING ',' FROM ',test,') |
+---------------------------------+
| test,                           |
+---------------------------------+

1.8 REPLACE(str, ori_str, sub_str)

  将字符串中的某段字符替换到此外生机勃勃段字符。例:输入指令SELECT REPLACE('asdfasdf', 'as', 'df');,结果为:

+---------------------------------+
| REPLACE('asdfasdf', 'as', 'df') |
+---------------------------------+
| dfdfdfdf                        |
+---------------------------------+

1.9 SUBSTRING(str, i[, j])

  功用是截取字符串str从第i个字符开端算起长度为j的字符串,若不钦赐j,则从第i位截取到最终。==i可以为负值,那时候则从字符串的末段往前算,比方-3则为尾数第2个字符算起。==

1.10 [NOT] LIKE

  功用是在多个字符型字段中查找富含钦点子字符串的字符串,须要合作通配符实行应用。MySQL中LIKE语句的通配符有:百分号(%卡塔尔国、下划线(_)和ESCAPE。

  ==%代表任性字符串==,举例'%ab%'表示含有'ab'的大肆字符串,能够是aabc、lab、abo等等;==下划线表示恣意单个字符==,举个例子'ae_a'能够表示aeba、aeoa等等;==若需求查询%或下划线,则经过ESCAPE加转义字符==。

  例:查询表users中name字段为'李'开始的笔录:

SELECT * FROM users WHERE name LIKE '李%';

  查询表users中name字段为'xiao%'最早的记录:

SELECT * FROM users WHERE name LIKE '李/%%' ESCAPE '/';

  ==紧随转义字符前面包车型大巴二个%或八个下划线不作为通配符,其余的如故为通配符。==

2. 数值运算符和函数

2.1 CEIL(num)、FLOOR(num)

  分别为发展取整和向下取整。

2.2 ROUND(num[, m])

  四舍五入。可仅内定实行四舍五入的数字,也能够透过第三个参数同期钦定取舍的小数位数。

2.3 POWER(num, m)

  幂运算,计算num的m次方。

2.4 TRUNCATE(num, m)

  数字截取。m为正值时,将num保留m位小数,m为负值时,则从整数位个位算起m位为0,同一时候保留整数。不作四舍五入,而直白截取。举例TRUNCATE(23.63, 0) = 23、TRUNCATE(459.23, -1) = 450。

2.5 DIV

  整数除法,将要普通除法获得的结果保留整数位,例如5 DIV 4 = 1。

2.6 MOD

  取余运算,举个例子5 MOD 4 = 1。

3. 比较运算符和函数

3.1 [NOT] BETWEEN...AND...

  剖断是或不是在钦点范围内。比方SELECT 10 BETWEEN 0 AND 10,结果为1,即true。

3.2 [NOT] IN()

  判定是或不是在加以的若干个值的限量内。==BETWEEN...AND...决断的是四个连接的间距,而IN则是数轴上的几个点。==比如SELECT 6 IN(1, 2, 3, 4);结果为0,即false。

3.3 IS [NOT] NULL

  判别是不是为空。举例SELECT '' IS NULL,结果为0。

4. 日辰时间函数

4.1 NOW()、CURDATE()、CURTIME()

  分别是日前日子和时间、当前天期、当前时间。举个例子输入指令SELECT NOW();,结果为:

+---------------------+
| NOW()               |
+---------------------+
| 2017-03-06 10:21:17 |
+---------------------+

4.2 DATE_ADD(ori_date, INTERVAL add_date {YEAR | MONTH | WEEK | DAY |...})

  日期更改,add_date可认为负值。比方SELECT DATE_ADD('2017-03-06', INTERVAL -2 MONTH);,结果为:

+-------------------------------------------+
| DATE_ADD('2017-03-06', INTERVAL -2 MONTH) |
+-------------------------------------------+
| 2017-01-06                                |
+-------------------------------------------+

4.3 DATEDIFF(date1, date2)

  总结多少个日子的差值。举个例子SELECT DATEDIFF('2016-01-01', NOW());,结果为(前几日日期为2017-03-06卡塔 尔(英语:State of Qatar):

+-------------------------------+
| DATEDIFF('2016-01-01', NOW()) |
+-------------------------------+
|                          -430 |
+-------------------------------+

4.4 DATE_FORMAT(date, format)

  将日期date根据钦赐格式输出。常用格式有年度的%y、%Y,月份的%m、%M,日期的%D、%d,要细心,==区分朗朗上口写==。

例:SELECT DATE_FORMAT(NOW(), '%d/%m/%y');,结果为:

+--------------------------------+
| DATE_FORMAT(NOW(), '%d/%m/%y') |
+--------------------------------+
| 06/03/17                       |
+--------------------------------+

SELECT DATE_FORMAT(NOW(), '%D/%M/%Y');,结果为:

+--------------------------------+
| DATE_FORMAT(NOW(), '%D/%M/%Y') |
+--------------------------------+
| 6th/March/2017                 |
+--------------------------------+

5. 音信函数

名称 作用
CONNECTION_ID() 返回当前线程的id
DATABASE() 返回当前正在使用的数据库名称
LAST_INSERT_ID() 返回最后插入的记录的ID
USER() 返回当前用户
VERSION() 返回当前数据库的版本信息

  LAST_INSERT_IN()使用注意:使用的靶子数据表==必需包涵一个机关编号的字段==。若最新三遍插入操作同一时间插入多条记下,则赶回多条记下中的==第二个id而非最终贰个id==。比方最终一回操作中插入了id为8和9的两条记下,则使用LAST_INSERT_ID返回的id为8。

6. 聚合函数

名称 作用
AVG() 计算平均值
COUNT() 计数
MAX() 比较得到最大值
MIN() 比较得到最小值
SUM() 求和

  聚合函数使用注意:在MySQL中不容许行使聚合函数直接举办数值运算,其参数须求为数据表中的某字段名称。比方SELECT AVG(1, 2, 3);澳门新濠3559,这一下令则会报错,精确用法应该为SELECT AVG(age) FROM users;

7. 加密函数

7.1 MD5(str)

  对字符串举办散列,日常用于一些平凡的无需解密的数据加密。例:SELECT MD5('MyPassword');,结果为:

+----------------------------------+
| MD5('MyPassword')                |
+----------------------------------+
| 48503dfd58720bd5ff35c102065a52d7 |
+----------------------------------+

7.2 PASSWORD(str)

  用法与MD5()基本黄金年代致,平日用来给客商密码举行加密。

8. 自定义函数

8.1 变量

  (1卡塔尔国客商变量:以"@"起首,定义方法为@var_name,比方定义三个变量temp并让其也正是10:SET @temp = 10;。要求在意,==使用变量时需求连接"@"。==客商变量跟mysql顾客端是绑定的,设置的变量,只对当下客户使用的客商端生效。

  (2卡塔 尔(阿拉伯语:قطر‎全局变量:定义时,以如下二种样式现身,SET GLOBAL 变量名或者SET @@GLOBAL.var_name。对富有顾客端生效,只有具有super权限才方可安装全局变量。

  (3卡塔尔会话变量:只对连年的客户端有效。

  (4卡塔 尔(英语:State of Qatar)局地变量:用DECLARE实行定义,功用范围在begin到end语句块之间。

8.2 简单结构(函数体独有三个实施语句卡塔尔

CREATE FUNCTION function_name([par_definition1] [, par2_definition2] ...)
RETURNS data_type
RETURN function_oper;

  函数名背后钦赐参数列表,可以为空;RETURES前面钦点重临值类型;RETU奇骏N前面跟实际的函数内容,注意再次回到类型需与RETUEscortNS前面钦命的项目风华正茂致。

  例:自定义二个函数,求五个整数的平均值:

CREATE FUNCTION myAvg(a SMALLINT, b SMALLINT)
RETURNS SMALLINT
RETURN (a+b)/2;

  输入指令SELECT myAvg(8,4);,结果为:

+------------+
| myAvg(8,4) |
+------------+
|          6 |
+------------+

8.3 复合结构(函数体有多少个实践语句卡塔尔

DELIMITER new_terminator
CREATE FUNCTION function_name([par_definition1] [, par2_definition2] ...)
RETURNS data_type
BEGIN
function_oper1;
RETURN function_oper2;
END new_terminator

  与轻巧结构不一的地点有二,第一是当现身多个试行语句时,前边的试行语句末尾的';'会被认为是终止符,诱致出错,因此首先必要经过DELIMITE奥迪Q5关键字钦点新的暂息符;第二则是粗略结构中实施语句只有一句,因而该语句必得为能够直接跟在RETUENVISIONN前边的、再次来到值切合须求的话语,而复合结构中设有多少个语句,因而要求以BEGIN伊始,以RETUSportageN再次回到值,以END截至。

  例:定义多少个函数,可向users表(含有id、name多少个字段卡塔尔插入一条记下,并回到该记录的ID:

DELIMITER $$
CREATE FUNCTION addUser(name VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
INSERT users(name) VALUES(name);
RETURN LAST_INSERT_ID();
END$$
DELIMITER ;

  将终止符重新钦命为分行,并步向第一条记下:

mysql> DELIMITER ;
mysql> SELECT addUser('John');
+-----------------+
| addUser('John') |
+-----------------+
| 1               |
+-----------------+

8.4 删除自定义函数

DROP FUNCTION [IF EXISTS] function_name;

八、存款和储蓄进度

CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([{IN | OUT | INOUT} proc_parameter[, ...]]) [characteristic ...] routine_body;

  ==创立存款和储蓄进程==的语法结构与自定义函数特别相同,仅仅是多了[DEFINER = {user | CURRENT_USER}]那风流倜傥选项,DEFINEGL450用于钦定期存款款和储蓄进度的制造客户,可不写。IN表示该参数的值必需在调用存款和储蓄进程时钦赐,用IN修饰的参数也便是函数中的情势参数;OUT表示该参数的值可以被寄放进度校正,並且能够回来,用OUT修饰的参数也正是用于存款和储蓄重回值的变量;INOUT表示该参数的调用时内定,况且能够被退换和重返。

  例1. 创建三个不带参数的储存进度,用于显示数据库版本新闻:

CREATE PROCEDURE showver() SELECT VERSION();

  存款和储蓄进程的==调用方法==是CALL sp_name([proc_parameter[, ...]]);,若无参数,可归纳小括号。

  例2. 开立三个包罗IN类型参数的存款和储蓄进度,用于删除表users中的钦赐id的记录:

DELIMITER $$
CREATE PROCEDURE delUser(IN id SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE users.id = id;
END $$
DELIMITER ;

  例3. 开立二个含有IN类型参数和OUT类型参数的囤积进度,用于删除表users中的钦点id的笔录并赶回剩余记录数据:

DELIMITER $$
CREATE PROCEDURE removeUser(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE users.id = id;
SELECT COUNT(id) FROM users INTO num;
END $$
DELIMITER ;

  调用时选取命令CALL removeUser(8,@num);

  删除存款和储蓄进度的操作是DROP PROCEDURE [IF EXISTS] sp_name;

编辑:数据库 本文来源:数据类型与数据表的操作,ANY、SOME与ALL关键字用

关键词:

  • 上一篇:没有了
  • 下一篇:没有了