mysql常用操作（一）

在window10系统中使用mysql首先需要启动MySQL服务（以管理员身份运行cmd）：

net start mysql

然后登陆mysql：

mysql -uroot -ppassword

使用数据库：

use 数据库名;

显示数据库：

show databases;

显示表：

show tables;

显示表的列信息

show columns from 表名;
describe 表名;

显示创建数据库的SQL语句：

show create database 数据库名;

显示创建表的SQL语句：

show create table 表名;

检索数据

检索单个列：

select 列名 from 表名;

只返回不同的值：

select distinct 列名 from 表名;

限制结果为行数数量：

select 列名 from 表名 limit 行数;
select 列名 from 表名 limit 开始位置,要检索的行数;
select 列名 from 表名 limit 要检索的行数 offset 开始位置;

使用全限定名：

select 表名.列名 from 数据库名.表名;

排序检索数据

按单个列排序：

select 列名 from 表名 order by 列名;

按多个列排序，仅在行具有相同的列名1值时才对列名2进行排序，如果列名1中所有的值唯一，则不会按列名2排序：

select 列名 from 表名 order by 列名1,列名2;

执行排序方向，如果用多个列进行排序，desc关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定desc关键字：

降序：select 列名 from 表名 order by 列名 desc;
升序（默认）：select 列名 from 表名 order by 列名 asc;

limit子句必须在order by子句之后。

过滤数据

select 列名 from 表名 where 列名=值;

order by子句应该位于where子句之后。

where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between（搭配and使用）	在指定的两个值之间

空值检查：

select 列名 from 表名 where 列名 is null;

组合where子句

and操作符：

select 列名 from 表名 where 列名1 op1 值1 and 列名2 op2 值2;

or操作符：

select 列名 from 表名 where 列名1 op1 值1 or 列名2 op2 值2;

SQL在处理or操作符之前，优先处理and操作符。所以大多情况下需要使用圆括号明确地分组相应的操作符，不依赖默认的计算次序。

in操作符，用来指定条件范围，范围中的每个条件都可以进行匹配：

select 列名 from 表名 where 列名1 in (值1,值2);

使用or操作符可以完成in操作符的相同功能，使用in操作符的优点：

• 语法清楚
• 计算次序易管理
• in操作符一般比or执行更快
• 最大优点是可以包含其他select语句

not操作符，否定它之后所跟的任何条件：

select 列名 from 表名 where 列名 not in (值1,值2);

mysql支持使用not对in、between和exists子句取反。

用通配符进行过滤

通配符：用来匹配值的一部分特殊字符。
搜索模式：由字面值、通配符或两者组合构成的搜索条件。

like操作符，其中%通配符表示任何字符出现任意次数，_通配符匹配单个字符：

select 列名 from 表名 where 列名 like 'jet%';

注意like ‘%’不能匹配NULL。

通配符的使用技巧：

• 不要过度使用通配符。
• 除非有绝对必要，否则不要把它们用在搜索模式的最开始处，这样搜索起来最慢。
• 仔细注意通配符的位置。

用正则表达式进行搜索

正则表达式的作用是匹配文本，将一个模式与一个文本串进行比较。mysql用where子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤select检索出的数据。

regexp关键字，.表示匹配任意一个字符：

select 列名 from 表名 where 列名 regexp '.';

like关键字和regexp关键字的区别：

like匹配整个列，如果被匹配的文本在列值中出现，like将不会找到它，相应的行也不被返回。而regexp在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。

mysql中正则表达式匹配不区分大小写，为区分大小写，可使用binary关键字，regexp binary。

进行or匹配

|为正则表达式的or操作符，它表示匹配其中之一：

select 列名 from 表名 where 列名 regexp '值1|值2';

匹配几个字符之一

匹配特定的字符，可以指定一组用[和]括起来的字符来完成：

select 列名 from 表名 where 列名 regexp '[值1值2]';
select 列名 from 表名 where 列名 regexp '[值1|值2]';

字符集合的否定，即将匹配除指定字符外的任何东西。

select 列名 from 表名 where 列名 regexp '[^值1值2]';

匹配范围

select 列名 from 表名 where 列名 regexp '[字符或数字-字符或数字]';

匹配特殊字符

未匹配特殊字符，必须用\为前导：

select 列名 from 表名 where 列名 regexp '\\.'

元字符	说明
\f	换页
\n	换行
\r	回车
\t	制表
\v	纵向制表

匹配字符类

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

匹配多个实例

元字符	说明
*	0个或多个匹配（等于{1,}）
+	1个或多个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

示例：

select 列名 from 表名 where 列名 regexp '[[:digit:]]{4}'

[:digit:]匹配任意数字，{4}确切地要求它前面的字符出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

定位符

为了匹配特定位置的文本，需要使用定位符：

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

示例：

select 列名 from 表名 where 列名 regexp '^[0-9\\.]'

^匹配串的开始，因此，只在.或任意数字为串中第一个字符时才匹配它们。

创建计算字段

计算字段

很多时候，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。
这就是计算字段发挥作用的所在了。计算字段并不实际存在于数据库表中。计算字段是运行时在select语句内创建的。

拼接字段

将值联结到一起构成单个值。

select concat(列名1,'(',列名2,')') from 表名;

trim函数：mysql除了支持rtrim()（去掉串右边的空格），还支持ltrim()（去掉串左边的空格）以及trim()（去掉串左右两边的空格）。

使用别名

上面拼接得到的新列没有名字，因此不能用于客户机应用中，为解决这个问题，需要使用别名。

select concat(列名1,'(',列名2,')') as 别名 from 表名;

执行算术计算

计算字段的另一个常见用途就是对检索出的数据进行算术计算。

操作符	说明
+	加
-	减
*	乘
/	除

示例：

select 列名1+列名2 as 别名 from 表名;

now()函数：返回当前日期和时间。

使用数据处理函数

函数

函数一般是在数据上执行的，它给数据转换和处理提供了方便。

文本处理函数

函数	说明
left()	返回串左边的字符
length()	返回串的长度
locate()	找出串的一个子串
lower()	将串转换为小写
ltrim()	去掉串左边的空格
right()	返回串右边的字符
rtrim()	去掉串右边的空格
soundex()	返回串的soundex值
substring()	返回子串的字符
upper()	将串转换为大写

其中soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

日期和时间处理函数

函数	说明
adddate()	增加一个日期（天、周等）
addtime()	增加一个时间（时、分等）
curdate()	返回当前日期
curtime()	返回当前时间
date()	返回日期时间的日期部分
datediff()	计算两个日期之差
date_add()	高度灵活的日期运算函数
date_format()	返回一个格式化的日期或时间串
day()	返回一个日期的天数部分
dayofweek()	对于一个日期，返回对应的星期几
hour()	返回一个时间的小时部分
minute()	返回一个时间的分钟部分
month()	返回一个日期的月份部分
now()	返回当前日期和时间
second()	返回一个时间的秒部分
time()	返回一个日期时间的时间部分
year()	返回一个日期的年份部分

示例：

select 列名1 from 表名 where date(数据类型为datetime的列名2) between '2018-03-01' and '2018-03-31'; 
select 列名1 from 表名 where year(数据类型为datetime的列名2)=2018 and month(列名2)=3; 

检索2018年3月的数据。

数值处理函数

函数	说明
abs()	返回一个数的绝对值
cos()	返回一个角度的余弦
exp()	返回一个数的指数值
mod()	返回除操作的余数
pi()	返回圆周率
rand()	返回一个随机数
sin()	返回一个角度的正弦
sqrt()	返回一个数的平方根
tan()	返回一个角度的正切

汇总数据

聚集函数

运行在行组上，计算和返回单个值的函数。

函数	说明
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列值之和

avg

• avg可用来返回所有列的平均值。
• avg也可用来确定特定列或行的平均值。
• avg忽略列值为null的行。

count

• count(*)对表中行的数目进行计数，不管表列中包含的空值还是非空值。
• count(Column)对特定列中具有值的行进行计数，忽略null值。

聚集不同的值

• 对于所有行执行计算，指定all参数或者不给参数，all是默认行为。
• 只包含不同的值，指定distinct参数。

组合聚集函数

select可根据需要包含多个聚集函数。

分组数据

数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

分组是在select语句的group by子句中建立的。group by子句指示mysql分组数据，然后对每个组而不是整个结果集进行聚集。
规定：

• group by子句可以包含任意数目的列。
• 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
• group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。
• 除了聚集语句外，select语句中的每个列都必须在group by子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• group by子句必须出现在where子句之后，order by子句之前。

过滤分组

事实上，目前所学过的所有类型的where子句都可以用having来替代。唯一差别是where过滤行，而having过滤分组。having支持所有where操作符。
having和where的差别：where在数据分组前进行过滤，having在数据分组后进行过滤。

分组和排序

order by	group by
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列，则必须使用

select子句顺序

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

使用子查询

子查询：嵌套在其他查询中的查询。

利用子查询进行过滤

select 列名1 
		from 表名1 
        where 列名2 in (select 列名2
        				from 表名2
        				where 列名3=值);

作为计算字段使用子查询

select 列名1，
		列名2，
		(select count(*) 
		from 表名2
		where 表名2.列名3=表名1.列名3)
		from 表名1;

相关子查询：涉及外部查询的子查询。任何时候只要列名可能有多义性，就要使用全限定列名。

联结表

创建联结

联结的创建规定要联结的所有表以及它们如何关联即可。

select 列名1，列名2 from 表名1,表名2 where 表名1.列名3=表名2.列名3;

在一条select语句中联结几个表时，相应的关系是在运行中构造的。

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

select * from 表名1，表名2;

内部联结

内部联结也称为等值联结，它基于两个表之间的相等测试。

select 列名1,列名2 from 表名1 inner join 表名2 on 表名1.列名3 = 表名2.列名4;

联结多个表

sql对一条select语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

select 列名1, 列名2 from 表名1,表名2,表名3 where 表名1.列名3=表名2.列名3 and 表名2.列名4=表名3.列名4;

select 列名1,列名2
	from 表名1
	where 列名3 in (select 列名3
		from 表名2
		where 列名4 in ( select 列名4
			from 表名3
			where 列名5=值
		)
	);

创建高级联结

使用表别名

select 列名1,列名2
from 表名1 as 别名1,表名2 as 别名2
where 别名1.列名3 = 别名2.列名3;

表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机。

自联结

使用表别名的主要原因之一是能在单条select语句中不止一次引用相同的表。

select 别名1.列名1,别名1.列名2 from 表名 as 别名1,表名 as 别名2 where 别名1.列名3=别名2.列名3 and 别名2.列名4=值;

select 列名1,列名2 from 表名 where 列名3 = (select 列名3 from 表名 where 列名4=值);

自然联结

自然联结排除多次出现，使每个列只返回一次。系统不完成这项工作，由自己完成。

外部联结

联结包含了那些在相关表中没有关联行的行。

select 列名1,列名2 from 表名1 left outer join 表名2 on 表名1.列名3 = 表名2.列名4;

在使用outer join语法时，必须使用right或left关键字指定包括其所有行的表，right指出的是outer join右边的表，而leftt指出的是outer join左边的表。

组合查询

mysql允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询。
有两种基本情况，其中需要使用组合查询：

• 在单个查询中从不同的表返回类似结构的数据。
• 对单个表执行多个查询，按单个查询返回数据。

创建组合查询

可用union操作符来组合数条sql查询。
union使用很简单，所需做的只是给出每条select语句，在各条语句之间放上关键字union。

union规则

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔。
• union中的每个查询必须包含相同的列、表达式或聚集函数。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

包含或取消重复的行

union从查询结果集中自动去除了重复的行，这是union的默认行为，如果想返回所有匹配行，可以使用union all。

对组合查询结果排序

在用union组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条order by子句。