mySql基本查询语句

1、列别名
给已有表中的列取别名：定义别名的时候“AS”不是必须的，是可以省略的，如下SQL
SELECT BOOKID AS ID ,BOOKNAME AS NAME,BOOKCOUNT AS COUNT ,BOOKADDRESS AS ADRESS FROM T_BOOK;

SELECT BOOKID  ID ,BOOKNAME NAME,BOOKCOUNT  COUNT ,BOOKADDRESS ADRESS FROM T_BOOK;

如果数据库支持中文也可以如下SQL：
SELECT BOOKID 序号 ,BOOKNAME 书名,BOOKCOUNT 数量 ,BOOKADDRESS 地址 FROM T_BOOK;

2、数据汇总
SQL中规定的几种聚合函数：
MAX 计算字段最大值
MIN 计算字段最小值
AVG 计算字段平均值
SUM 计算字段合计值
COUNT 统计数据条数

3、排序之多列排序
对于多个排序规则，数据库系统会按照优先级进行处理。数据库系统首先按照第一个排序
规则进行排序；如果按照第一个排序规则无法区分两条记录的顺序，则按照第二个排序规则进行
排序；如果按照第二个排序规则无法区分两条记录的顺序，则按照第三个排序规则进行排序；……
以此类推,如下SQL：
SELECT * FROM T_BOOK ORDER BY BOOKCOUNT DESC,BOOKID DESC;

4、高级过滤
  1)、通配符过滤
a、单字过滤
进行单字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符
如下SQL：
SELECT * FROM T_BOOK WHERE BOOKCOUNT LIKE '_00' ;
SELECT * FROM T_BOOK WHERE BOOKCOUNT LIKE '_0_' ;
b、多字过滤
进行多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的
任意字符

2)、集合匹配
进行集合匹配的通配符为“[]”，方括号中包含一个字符集，它匹配与字符集中任意一
个字符相匹配的字符。比如通配符表达式“[bt]%”匹配第一个字符为b 或者t、长度不限的字符串，“bed”、“token”、“t ”都能匹配这个表达式，而“at”、“lab”、“lot”等则不能匹配这个表达式。

3)、空值检测
在SQL语句中对空值的处理有些特别，不能使用普通的等于运算符进行判断，而要使用IS NULL关键字，使用方法为“待检测字段名IS NULL”，如果要检测“字段不为空”，则要使用IS NOT NULL，使用方法为“待检测字段名IS NOT NULL”
SELECT * FROM T_BOOK WHERE BOOKADDRESS IS NULL;
SELECT * FROM T_BOOK WHERE BOOKADDRESS IS NOT NULL;

4)、反义运算符
“=”、“<”、“>”等运算符都是用来进行数值判断的，有的时候则会想使用这些运算符
的反义，比如“不等于”、“不小于”或者“不大于”，MSSQLServer、DB2提供了“!”运算
符来对运算符求反义，也就是“!=”表示“不等于”、“!<”表示“不小于”，而“!>”表示
“不大于”。SQL提供了通用的表示“不等于”的运算符“<>”，这样“不等于”、“不大于”和“不小于”就分别可以表示成“<>”、“<=”和“>=”
5)、多值检测

OR语句来连接多个等于判断
SELECT * FROM T_BOOK WHERE BOOKCOUNT =60 OR BOOKCOUNT = 90 OR BOOKCOUNT = 100;
也可，使用IN我们只要指定要匹配的数据集合就可以了，使用方法为“IN (值1,值2,值3……)”
SELECT * FROM T_BOOK WHERE BOOKCOUNT IN (60,90,100);

6)、范围检测
用来检测一个值是否处于某个范围中（包括范围的边界值，也就是闭区间）。使用方法如下“字段名BETTWEEN 左范围值AND 右范围值”，其等价于“字段名>=左范围值AND 字段名<=右范围值”。
SELECT * FROM T_BOOK WHERE BOOKCOUNT BETWEEN 60 AND 100;

7)、数据分组
  a、GROUP BY
SQL语句中使用GROUP BY子句进行分组，使用方式为“GROUP BY 分组字段”。分组语句必须和聚合函数一起使用，GROUP BY子句负责将数据分成逻辑组，而聚合函数则对每一个组进行统计计算

SELECT BOOKNAME FROM T_BOOK GROUP BY BOOKADDRESS;

SELECT * FROM T_BOOK  WHERE BOOKADDRESS ='深圳' GROUP BY BOOKCOUNT;

SELECT BOOKNAME,COUNT(*) AS CountOfBOOK FROM T_BOOK GROUP BY BOOKADDRESS;

SELECT BOOKNAME,COUNT(*) AS CountOfBOOK FROM T_BOOK GROUP BY BOOKADDRESS ORDER BY BOOKID DESC;
b、HAVING
可以在数据库系统中执行下面的SQL的时候，数据库系统会提示语法错误，这是因为聚合函
数不能在WHERE语句中使用，必须使用HAVING子句来代替，比如:
SELECT BOOKNAME  FROM T_BOOK GROUP BY BOOKADDRESS HAVING COUNT(*)>1;

HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件:
SELECT BOOKNAME  FROM T_BOOK GROUP BY BOOKADDRESS HAVING COUNT(*)=1 OR COUNT(*)>=2;
也可以使用IN操作符来实现上面的功能
SELECT BOOKNAME  FROM T_BOOK GROUP BY BOOKADDRESS HAVING COUNT(*) IN (1,3);

注意：HAVING语句能够使用的语法和WHERE几乎是一样的，不过使用WHERE的时候
GROUP BY子句要位于WHERE子句之后，而使用HAVING子句的时候GROUP BY子句要位
于HAVING子句之后（在HAVING语句中不能包含未分组的列名）

 、限制结果集行数
LIMIT关键字用来限制返回的结果集，LIMIT放在SELECT语句的最后位置，语法为“LIMIT 首行行号，要返回的结果集的最大数目”

SELECT * FROM T_BOOK ORDER BY BOOKID DESC LIMIT 1,3;

9)、抑制重复数据
DISTINCT关键字是用来进行重复数据抑制的最简单的功能，而且所有的数据库系统都支持DISTINCT，DISTINCT的使用也非常简单，只要在SELECT之后增加DISTINCT即可。
(DISTINCT是对整个结果集进行抑制的，而不是针对某一个)

SELECT DISTINCT BOOKADDRESS FROM T_BOOK ORDER BY BOOKID ;

10)、字符串的拼接
CONCAT函数支持一个或者多个参数，参数类型可以为字符串类型也可以是非字符串类型，对于非字符串类型的参数MYSQL将尝试将其转化为字符串类型，CONCAT函数会将所有参数按照参数的顺序拼接成一个字符串做为返回值

SELECT CONCAT('书名: ',BOOKNAME,'  销售地址:  ',BOOKADDRESS)FROM T_BOOK

CONCAT_WS可以在待拼接的字符串之间加入指定的分隔符，它的第一个参数值为采用的分隔符，而剩下的参数则为待拼接的字符串值
SELECT CONCAT_WS(',' ,BOOKNAME,BOOKADDRESS) FROM T_BOOK;

11)、结果集联合

UNION运算符要放置在两个查询语句之间，UNION可以连接多个结果集，就像“+”可以连接多个数字一样简单，只要在每个结果集之间加入UNION即可：
使用UNION的原则有两个，如下：
一是每个结果集必须有相同的列数；二是每个结果集的列必须类型相容
SELECT BOOKNAME ,BOOKCOUNT, BOOKADDRESS FROM T_BOOK WHERE BOOKCOUNT > 80
UNION
SELECT BOOKNAME,BOOKCOUNT ,BOOKADDRESS FROM T_BOOK WHERE BOOKADDRESS ='深圳';

打印出5以后的自然数，及自然数的平方，SQl如下
SELECT 1,1 * 1
UNION
SELECT 2,2 * 2
UNION
SELECT 3,3 * 3
UNION
SELECT 4,4 * 4
UNION
SELECT 5,5 * 5

如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在UNION运算符后使用ALL操作符

注意：UNION 操作符选取不同的值，如果允许重复使用UNION ALL，UNION ALL效率要不UNION快一些，如果在查询语句时已使用DISTINCT过滤重复，这是应使用UNION ALL

5、数学函数
  1)、RAND()函数
用来生成随机算法, 返回值是随机的
       SELECT  RAND();

6、索引与约束
1)、索引
a、创建索引的语法
CREATE INDEX 索引名 ON 表名(字段1、字段2，……字段N)，如：
CREATE INDEX INDEX_BOOKNAME_COUNT ON T_BOOK (BOOKNAME,BOOKCOUNT);
b、删除索引
DROP INDEX 索引名 ON 表名
DROP INDEX INDEX_BOOKNAME_COUNT ON T_BOOK
2)、约束
a、非空约束
在定义数据表的时候，默认情况下所有字段都是允许为空值的，如果需要定义字段为空，则可以指定一个字段为空的方式就是在字段定义后增加 NOT NULL
如：
CREATE TABLE T_DEMO (
ID INT NOT NULL COMMENT 'ID',
NAME VARCHAR(25) COMMENT 'name'
);
b、唯一约束
唯一约束又称为UNIQUE 约束，它用于放置一个特定的列中有两个记录具有一致的值。唯一约束分为单字段唯一约束与复合唯一约束两种类型
单字段唯一约束：
如：
CREATE TABLE T_DEMO (
ID INT UNIQUE COMMENT 'ID',
NAME VARCHAR(25) NOT NULL COMMENT 'name',
AGE INT
);
复合唯一约束：
定义复合唯一约束需要定义在所有字段列表之后，语法如下：
CONSTRAINT 约束名 UNIQUE（字段1，字段2，…….字段n）
如：
CREATE TABLE T_DEMO (FNumber VARCHAR(20),
FDepartmentNumber VARCHAR(20),
FName VARCHAR(20),FAge INT,
CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

对已有的表中增加唯一约束，语法
ALTER TBALE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE（字段1,字段2.。。字段N）
如：
ALTER TABLE T_DEMO ADD CONSTRAINT unic_3 UNIQUE(FName, FAge);

删除已经创建好的复合唯一约束，语法
ALTER TABLE 表名 DROP INDEX 唯一约束名
如：
ALTER TABLE T_DEMO DROP INDEX unic_3 ;

c、CHECK约束
CHECK约束会检查输入记录中的值是否满足一个条件，如果不满足这个条件则对数据库的修改不会成功，CHECK约束对于插入、更新等任何对数据进行变化的操作都进行检查
在字段定义后添加CHECK表达式就可以为这个字段添加CHECK约束，一张表中可以存在多个CHECK约束
如：
CREATE TABLE T_DEMO (
FNumber VARCHAR(20),FName VARCHAR(20),
FAge INT CHECK(FAge >0),
FWorkYear INT CHECK(FWorkYear>0));

d、主键约束
主键必须能够唯一标记一条记录，也就是主键字段中的值必须是唯一的，而且不能包含NULL值，。从这一种意义说，主键约束是UNIQUE约束和非空约束的组合，虽然一张表中可以有对个UNIQUE约束和非空约束。但是每个表中却只能有一个主键约束
如：
CREATE TABLE T_DEMO (
ID INT PRIMARY KEY COMMENT 'ID',
NAME VARCHAR(25) NOT NULL COMMENT 'name',
AGE INT
);

除了单一字段组成的组件之外，还可以由多个字段组成主键，这样的主键被称为复合主键或者联合主键
如：
CREATE TABLE T_DEMO (
ID INT NOT NULL  COMMENT 'ID',
NAME VARCHAR(25) NOT NULL COMMENT 'name',
AGE INT NOT NULL,
CONSTRAINT pk_1 PRIMARY KEY(ID,AGE)
);

对已有的表增加复合主键，如
ALTER TABLE T_DEMO ADD CONSTRAINT pk_1 PRIMARY KEY(ID,AGE)

删除复合主键
ALTER TABLE T_DEMO DROP PRIMARY KEY;

e、外键约束
CREATE TABLE T_DEMO(
ID INT PRIMARY KEY,
NAME VARCHAR(20),
AGE INT,
TESTID INT,
FOREIGN KEY (TESTID) REFERENCES t_test(FID)
);

对已有表添加外键约束
ALTER TABLE T_BOOK ADD CONSTRAINT fk_BOOK_DEMO FOREIGN KEY (BOOKID) REFERENCES T_DEMO(Id)

7、表连接
1)、内连接
内连接组合两张表，并且基于两张表中的关联关系来连接它们，使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接，内连接的语法如下
INSERT JOIN 表名 ON CONDITION ，其中CONDITION则为进行连接时的条件
如：
SELECT * FROM t_borrew  br INNER JOIN  t_borrower bw
ON  bw.BORROWID = br.BORROWID WHERE bw.BORROWNAME ='MARK';

2)、不等值连接
在连接的条件中可以使用小于（<）、大于（>）、不等于（<>）等运算符，而且还可以使用LIKE、BETWEEN AND等运算符，甚至还可以使用函数。
如：
SELECT ST.STUNAME,ST.STUAGE FROM t_student ST INNER JOIN student st ON ST.STUAGE<st.sAge+6

3)、交叉连接
交叉连接不存在ON字句，其会将涉及到所有表的所有记录都包含在结果集中，可以采用两种方式来定义交叉连接，分别是隐式的和显式
a、隐式
隐式的连接只要在select语句的from语句后面进行交叉连接的表名列出即可，如：
SELECT ST.STUNAME,ST.STUAGE,st.sName,st.sAge,st.sSex FROM t_student ST,student st
b、显式
交叉连接的显式定义方式为使用CROSS JOIN关键字，其语法与INNER JOIN类似，如：
SELECT ST.STUNAME,ST.STUAGE,st.sName,st.sAge,st.sSex FROM t_student ST
CROSS JOIN student st

4）、自连接
表与其自身连接，称为自连接
5)、外部连接
a、左外部连接
在左外部连接中，左表中所有记录都会放到结果集中，无论是否存在右表中存在匹配的记录
SELECT * FROM t_borrew  br LEFT OUTER JOIN  t_borrower bw  ON  bw.BORROWID = br.BORROWID WHERE bw.BORROWNAME ='MARK';
b、右外部连接
与左外部连接正好相反，在右外部链接中不管是否成功匹配连接条件都会返回右表中的所有记录
SELECT * FROM t_borrew  br RIGHT OUTER JOIN  t_borrower bw ON  bw.BORROWID = br.BORROWID
c、全外部连接（MYSQl不支持全外部连接）

8、子查询
1)、子查询入门
a、单值子查询
单值子查询的语法和普通的SELECT 语句没有什么不同，唯一的限制就是子查询的返回值必须只有一行记录，而且只能有一个列。这样的子查询又被称为标量子查询，标量子查询可以用在SELECT语句的列表中、表达式中、WHERE 语句中等很多场合
SELECT 1 AS f1,2,(SELECT MIN(BOOKCOUNT) FROM T_BOOK),(SELECT MAX(BOOKCOUNT) FROM T_BOOK) AS f4
这个sql中第一列、第二列是数字，第三列则是一个标量子查询，返回的是图书中最少的书数量，第四列也是标量子查询，它返回的是图书中最多数量的书数量
b、列值子查询
与标量子查询不同，列值子查询可以返回一个多行多列的结果集，这样子查询又被称为表子查询，表子查询可以看做一个临时的表，表子查询可以用在select语句的from子句中、insert 语句，连接、in字句等多场合
SELECT ST.STUNAME,ST.STUAGE,st.sName,st.sAge,st.sSex
FROM t_student ST,(SELECT * FROM student WHERE sAge>15 AND sSex ='F' ) AS st

2)、SELECT列表中的标量子查询
SELECT br.BORROWTIME,br.BACKTIME,(SELECT bw.BORROWNAME FROM t_borrower bw WHERE bw.BORROWID = br.BORROWID AND bw.VOCATION ='SINGER') FROM t_borrew br

去掉WHERE字句
SELECT br.BORROWTIME,br.BACKTIME,(SELECT MAX(BORROWID) FROM t_borrower bw)
FROM t_borrew br

3)、WHERE字句中的标量子查询
SELECT BORID FROM t_borrew WHERE BORROWID=(SELECT BORROWID FROM t_borrower
WHERE BORROWNAME='JIM')

4)、集合运算符与子查询
a、IN运算符
SELECT * FROM T_BOOK WHERE BOOKCOUNT IN(80,60,150);

SELECT br.BORROWTIME,br.BACKTIME FROM t_borrew br WHERE BORROWID
IN (SELECT MAX(BORROWID) FROM t_borrower);

b、ANY 和SOME运算符
SOME的用法、功能和ANY一模一样，和IN运算符不同，ANY必须和其他的比较运算符共同使用，而且比较将比较运算符放在ANY关键字之前
SELECT br.BORROWTIME,br.BACKTIME FROM t_borrew br WHERE
BORROWID =ANY (SELECT MAX(BORROWID) FROM t_borrower);

备注：
“=ANY”等价于IN 运算符，而“<>ANY”则等价于NOT IN 运算符。除了等于运算符，ANY 运算符还可以和大于（>）、小于(<)、大于等于（>=）、小于等于（<=）等比较运算符共同使用

c、EXITIS运算符
EXISTS运算符用来检查每一行是否匹配子查询，可以认为EXISTS就是用来测试子查询的结果是否为空，如果结果集为空则匹配结果为false，否则匹配结果为true
SELECT br.BORROWTIME,br.BACKTIME  FROM t_borrew br
WHERE EXISTS (SELECT * FROM t_borrower WHERE BORROWNAME ='MARK');

d、子查询在INSERT语句中的应用
INSERT……VALUES……这种用法外，INSERT 语句还支持另外一种语法，那就是INSERT……SELECT……，采用这种使用方式可以将SELECT语句返回的结果集直接插入到目标表中，因为这一切都是都数据库内部完成的，所以效率非常高
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)
SELECT 1,FId FROM T_Reader

去除重复数据
INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId) SELECT 1,FId FROM T_Reader
WHERE NOT EXISTS
(
SELECT * FROM T_ReaderFavorite
WHERE T_ReaderFavorite. FCategoryId=1
AND T_ReaderFavorite. FReaderId= T_Reader.FId
)
e、子查询在UPDATE语句中的应用
在 UPDATE 语句中可以在更新列表中以及WHERE 语句使用子查询
UPDATE t_borrew SET BOOKID =(SELECT MAX(BOOKID) FROM T_BOOK)
WHERE BORID = 4

f、子查询在DELETE 语句中的应用
子查询在DELETE 中唯一可以应用的位置就是WHERE 子句，使用子查询可以完成复
杂的数据删除控制，如
DELETE FROM t_borrew WHERE BOOKID =(SELECT MAX(BOOKID) FROM T_BOOK)