Mysql学习笔记1123.docx

Mysql学习笔记1123.docx

《Mysql学习笔记1123.docx》由会员分享，可在线阅读，更多相关《Mysql学习笔记1123.docx（12页珍藏版）》请在冰豆网上搜索。

Mysql学习笔记1123

MySQL学习笔记

1查询语句

1.1简单查询

1.1.1distinct删除重复行

SELECT语句中使用ALL或distinct选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。

使用distinct选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。

distinct一般需要处理单一字段时使用，不能用于多个字段。

例如：

select   distinct   ID,AA,BB   from   tName 

select   distinct   ID   from   tName 

区别在于:

第一种情况是把三个字段的内容都相同的记录认为是相同的记录,合并一条，如果有一个字段是不同的，认为不是相同，不合并。

我要消除name字段值重复的记录，同时又要得到id字段的值，其中id是自增字段。

 如何用distinct消除重复记录的同时又能选取多个字段值？

selectdistinctnamefromt1能消除重复记录，但只能取一个字段，现在要同时取id,name这2个字段的值。

selectdistinctid,namefromt1可以取多个字段，但只能消除这2个字段值全部相同的记录。

最后解决方法：

SELECT id,nameFROMt1WHEREidIN（SELECTMAX（id）FROMt1GROUPBYname）orderby id desc

注意开头的 id 的一定要，后面的orderby里有的字段一定要加进select结果，要不然排序无效。

1.1.2限制返回的行数

使用TOPn[PERCENT]选项限制返回的数据行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。

例如:

SELECTTOP2*FROMtesttable

SELECTTOP20PERCENT*FROMtestable

1.1.3WHERE子句设置查询条件

WHERE子句可包括各种条件运算符:

1.比较运算符（大小比较）:

>、>=、=、<、<=、<>、!

>、!

<

2.范围运算符（表达式值是否在指定的范围）:

BETWEEN…AND…

NOTBETWEEN…AND…

3.列表运算符（判断表达式是否为列表中的指定项）:

IN（项1,项2……）

NOTIN（项1,项2……）

4.模式匹配符（判断值是否与指定的字符通配格式相符）:

LIKE

NOTLIKE

常用配字符:

Ø百分号%:

可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%。

Ø下划线_:

匹配单个任意字符，它常用来限制表达式的字符长度。

Ø方括号[]:

指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^]:

其取值也和[]相同，但它要求所匹配对象为指定字符以外的任一个字符。

5.空值判断符（判断表达式是否为空）:

ISNULL

NOTISNULL

6.逻辑运算符（用于多条件的逻辑连接）:

NOT

AND

OR

1.1.4Orderby排序

使用ORDERBY子句对查询返回的结果按一列或多列排序。

ORDERBY子句的语法格式为:

ORDERBY{column_name[ASC|DESC]}[,…n]

其中ASC表示升序，为默认值，DESC为降序。

ORDERBY不能按ntext、text和image数据类型进行排序。

1.1.5COUNT（）函数

count（*）对行的数目进行计算，包含NULL。

count（column）对特定的列的值具有的行数进行计算，不包含NULL值。

count（）还有一种使用方式，count

（1）这个用法和count（*）的结果是一样的。

1.1.6查询字段为NULL

在mysql中，查询某字段为空时，不可用 =null，而是isnull，不为空则是isnotnull。

具体格式如下:

select * from [table name] where [column name] is null;

select * from [table name] where [column name] is not null;

错误的写法：

select * from  [table name]  where  [column name]=null;

select * from  [table name]  where length（[column name]）=0;

1.1.7IFNULL、ISNULL和NULLIF区别

IFNULL用法说明：

IFNULL（expr1,expr2）

如果expr1不是NULL，IFNULL（）返回expr1，否则它返回expr2。

IFNULL（）返回一个数字或字符串值，取决于它被使用的上下文环境。

ISNULL用法说明：

ISNULL（expr）

如expr为null，那么isnull（）的返回值为1，否则返回值为0。

NULLIF用法说明：

NULLIF（expr1,expr2）

如果expr1 =expr2成立，那么返回值为NULL，否则返回值为expr1。

1.1.8casewhen语句

SELECT           

   case           #如果

   whensex='1'then'男'#sex='1'，则返回值'男'

   whensex='2'then'女'#sex='2'，则返回值'女'

   else'其他'       #其他的返回'其他’

   end            #结束

from sys_user           

整体理解：

在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女'否则返回'其他’。

用法一：

SELECT 

   CASE

WHENSTATE='1'THEN'成功' 

   WHENSTATE='2'THEN'失败'

   ELSE'其他'END 

FROM SYS_SCHEDULER

用法二：

SELECT

STATECASE

WHEN'1'THEN'成功' 

    WHEN'2'THEN'失败'

    ELSE'其他'END 

FROM SYS_SCHEDULER

1.2嵌套查询（子查询）

嵌套查询的意思是，一个查询语句（select-from-where）查询语句块可以嵌套在另外一个查询块的where子句中，称为嵌套查询。

其中外层查询也称为父查询，主查询。

内层查询也称子查询，从查询。

嵌套查询的工作方式是：

先处理内查询，由内向外处理，外层查询利用内层查询的结果嵌套查询不仅仅可以用于父查询select语句使用。

还可以用于insert、update、delete语句或其他子查询中。

1.2.1子查询

任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值。

如果某个表只出现在子查询中二不出现在外部查询中，那么该表的列就无法包含在输出中。

selectname,agefromperson

whereage>

（

selectagefromperson

wherename='孙权'

）

1.2.2in嵌套查询

in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。

返回满足in列表中的满足条件的记录。

示例：

selectnamefromperson

wherecountryidin

（

selectcountryidfromcountry

wherecountryname='魏国'

）

　　输出结果为：

1.2.3some嵌套查询

　　some在sql中的逻辑运算符号，如果在一系列比较中，有些值为True，那么结果就为True。

some的语法是：

<表达式>{=|<>|!

=|>|>=|!

>|<|<=|!

<}some（子查询）　　

　　示例：

selectnamefromperson

wherecountryid=some　--用等号和以下查询到的值比较，如果与其中一个相等，就返回

（

selectcountryidfromcountry

wherecountryname='魏国'

）

　　输出结果为：

1.2.4all嵌套查询

all是sql中的逻辑运算符好，如果一系列的比较都为true，那么结果才能为true。

<表达式>{=|<>|!

=|>|>=|!

>|<|<=|!

<}all（子查询）

　　示例：

selectnamefromperson

wherecountryid>all　　--当countryid大于以下返回的所有id，此结果才为True，此结果才返回

（

selectcountryidfromcountry

wherecountryname='魏国'

）

　　输出结果为：

1.2.5exists嵌套查询

exists是sql中的逻辑运算符号。

如果子查询有结果集返回，那么就为True。

exists代表“存在”的意义，它只查找满足条件的那些记录。

一旦找到第一个匹配的记录后，就马上停止查找。

exists　子查询　　　　　　　　

其中子查询是一个首先的select语句，不允许有compute子句和into关键字。

exists的意思是，子查询是否有结果集返回。

　　　例如：

SELECT*FROMPerson

WHEREexists

（

SELECT1

--SELECT0SELECTNULL返回结果都一样，因为这三个子查询都有结果集返回，因此总是TrueSELECT*FROMPerson照常执行

）

　　　但是如果子查询中因为加了条件而没有结果集返回，则主语句就不执行了：

SELECT*FROMPerson

WHEREexists

（

SELECT*FROMPerson

WHEREPerson_Id=100

--如果不存在Person_Id的记录，则子查询没有结果集返回，主语句不执行

）

1.3连接查询

1.3.1左连接（LEFTJOIN）

LEFTJOIN关键字会从左表（table_name1）那里返回所有的行，即使在右表（table_name2）中没有匹配的行。

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在leftjoin下，on和where两者的区别：

on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表（table_name1）的行。

where则是在生成临时表之后使用的条件，此时已经不管是否使用了leftjoin了，只要条件不为真的行，全部过滤掉。

SQL语句：

selecta.id,a.No,b.namefromtable1aleftjointable2bon（a.No=b.Noandb.name='aaa'）;

selecta.id,a.No,b.namefromtable1aleftjointable2bon（a.No=b.No）whereb.name='aaa';

表1：

table2

id

No

1

n1

2

n2

3

n3

表2：

table2

No

name

n1

aaa

n2

bbb

n3

ccc

第一个结果集：

|id|No|name|

|---|---|---|

|1|n1|aaa|

|2|n2|（Null）|

|3|n3|（Null）|

第二个结果集：

|id|No|name|

|---|---|---|

|1|n1|aaa|

第一个sql的执行流程：

首先找到b表的name为aaa的记录行on（a.No=b.Noandb.name=’aaa’）。

然后找到a的数据（即使不符合b表的规则），生成临时表返回用户。

第二个sql的执行流程：

首先生成临时表，然后执行where过滤b.name=’aaa’不为真的结果集，最后返回给用户。

因为on会首先过滤掉不符合条件的行，然后才会进行其它运算，所以按理说on是最快的。

在多表查询时，on比where更早起作用。

系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。

由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，我们就必需把连接条件放在ON后面，而不能放在WHERE后面，如果我们把连接条件放在了WHERE后面，那么所有的LEFT,RIGHT,等这些操作将不起任何作用，对于这种情况，它的效果就完全等同于INNER连接。

对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。

记住：

所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。

selectpj.id,pj.`name`,IFNULL（COUNT（vm1.vmname）,0）as'虚拟机数量'

fromprojectpj

LEFTJOIN（

selectvm.project_id,vm.`name`asvmname

fromvm_hostvm

wherevm.status='A'

）vm1

on（pj.id=vm1.project_id）

wherepj.`status`!

='P'

GROUPBYpj.id

1.3.2右连接（RIGHTJOIN）

1.3.3内连接

内连接，也叫等值连接，innerjoin产生同时符合A和B的一组数据。

mysql>select*fromAinnerjoinBonA.name=B.name;

+----+--------+----+--------+

|id|name|id|name|

+----+--------+----+--------+

|1|Pirate|2|Pirate|

|3|Ninja|4|Ninja|

+----+--------+----+--------+

1.3.4全连接

注意：

mysql不支持Fulljoin，不过可以通过UNION关键字来合并LEFTJOIN与RIGHTJOIN来模拟FULLjoin.

1.4联合查询（UNION）

union对两个结果集进行并集操作，重复数据只显示一次。

unionall对两个结果集进行并集操作，重复数据全部显示。

1.4.1语法

SELECTcolumn_nameFROMtable1

UNION

SELECTcolumn_nameFROMtable2

ØUNION用于合并两个或多个SELECT语句的结果集，并消去表中任何重复行。

ØUNION内部的SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

Ø同时，每条SELECT语句中的列的顺序必须相同。

1.4.2实例

student表数据：

使用union方法返回的结果：

使用unionall方法返回的结果：

2表结构查询

3设置数据库访问权限

mysql-uroot-p密码

grantallprivilegeson*.*to新账号名@'%'identifiedby'新账号的密码'；

flushprivileges;

例如：

#grantallprivilegeson*.*toroot@'%'identifiedby'123456';