mysql必知必会笔记

show databases; --查询所有数据库
create database jiang charset = utf8; --创建数据库
USE jiang --选择数据库，必须使用use打开数据库才能读取其中数据
show tables --查询数据库内的表
show columns from 表名 --显示表中的列

show status --显示广泛的服务器状态信息
show grants --显示授予用户的安全权限
show errors 和 show warnings --显示服务器错误或警告消息
help show --显示允许使用的show语句

++++++++++++++++++++++++ 创建表 +++++++++++++++++++++++++++++++++++++++++++++++++++

----创建表
CREATE TABLE 表名 (
列名 int(5) primary key,
列名 varchar(10)
);

---- 创建表
create table students(
id int(11) not null auto_increment,
name varchar(20) not null,
primary key(id)
)ENGINE=InnoDB AUTO_INCREMENT=2, DEFAULT CHARSET=utf8;

– 插入数据到表
INSERT INTO 表名(列名,列名)VALUES(数据1，数据2);

alter table 表 add column 列 int(5) not null; --表中添加列

+++++++++++++++++++++++++++ 表查询 ++++++++++++++++++++++++++++++++++++++++++++++

select 列名 from 表名; --查询表中 单列数据
select * from 表名； --查询所有列
SELECT DISTINCT 列名 from 表名 --DISTINCT关键字，只返回不同的值
select 列名 from 表名 LIMIT 3; --LIMIT3表示返回数据不多于3行

select 列 from 表 LIMIT ,2,3; --第一个数为开始位置，第二个数为要检索的行数，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。从行0开始

select 表名.列名 from 数据库.表名 --这是使用完全限定的列名和表名，有些情况需要这种写法

++++++++++++++++++++++++++ 排序检索 ++++++++++++++++++++++++++++++++++++++++++++++++

select 列1 from 表 order by 列3; – order by子句取一个或多个列的名字，据此对输出进行排序。通过非选择列进行排序也是可以的。order by未指定排序规则时默认升序。

select 列1，列2，列3 from 表 order by 列1，列2; – 如果order by 列1中的所有值都是唯一的，则不会按照列2排序。只有列1在多行中具有相同值时，才会按照列2排序。

select 列 from 表 order by 列 desc – dese是降序关键字

select 列 from 表 order by 列1 desc, 列2 desc； – 对多列进行降序必须为每列指定desc，desc只应用到前面的列明，列1重复时才会按照列2降序。asc是升序，但基本不用，因为默认升序。

大小写的排序顺序，这看数据库的设计，一般A和a一样，要是数据库中包含大量的外语字符，order by是搞不定的，需要找数据库管理员的帮助。

使用order by 和 limit的组合，可以找出一列中最高或最低的指定条目数据。
select 列 from 表 order by 列 desc limit 1； --找出列中降序中的第一行

----------------------- 过滤数据 --------------------------------------

where子句在from子句之后给出。

select 列1，列2， from 表 where 列1 = 32； --找出列等于32的数据

where子句操作符：= 等于， <> 不等于， != 不等于， < 小于， <= 小于等于， > 大于，between 在指定两个值之间。

where执行匹配时默认不区分大小写

where 列 between 5 and 10; – 查询5和10之间的数据

select 列 from 表 where 列 is null; --查询具有空值的列

--------------------- 数据过滤，操作符 --------------------------------------

select 列1，列2 from 表 where 列1 = 1003 and 列2 <= 10； --用and操作符给where子句附加条件，还可用and拼接条件过滤数据

or 操作符号表示任意一条件匹配就行。
select 列1，列2 from 表 where 列1 = 10 or 列2 >=50;

whrer 可以包含任意数目的and和or, and 的计算优先级高于 or。
可以用括号明确分组相应的操作符 （id = 10 or id = 32）and age >= 50;

in 操作符，用来指定条件范围，in的合法值用逗号隔开
select 列1，列2 from 表 where 列1(1002,1004);

not操作符，列出指定条件之外的所以数据
select 列 from 表 where lie not in(20,30); --列出除20,30之外的所有数据

mysql中的not支持对in，between和exists子句取反， 和其它DBMS允许使用not对各种条件取反有很大差别。

---------------------- 用通配符进行过滤，like操作 --------------------------

百分号% 通配符： %表示任何字符出现任意次数
where 列 like ‘jet%’; --找出所有以jet起头的产品，%告诉mysql接受jet之后的任意字符，不管多少字符。
可以使用多个通配符，例如： like ‘%jiang%’ --表示匹配任何位置包含jiang的值，而在之后无论出现什么字符。
like ‘s%e’ --这是找出以s开头e结尾的产品
根据mysql的配置方式，搜索是可以区分大小写的，还要注意尾空格，空格会干扰通配符匹配

下划线 _ 通配符：下划线的用途与%一样，不过下划线只匹配单个字符
like ‘_jiang’ --查询的数据是1jiang、2jiang、9jiang，不会是一个字符以上的任意数据，例如：34jiang、3132jiang，下划线总是匹配一个字符，不能多也不能少。

通配符的搜索时间比之前的wher语句要长
使用通配符的技巧：
1、其它操作符能做到的事就不要用通配符
2、用通配符来搜索开始处是最慢的
3、仔细注意通配符的位置，以免查错

------------------- 正则表达式 ------------------
regexp关键字

where 列1 regexp ‘1000’ order by 列1 --regexp后面跟的东西作为正则表达式，找出与1000匹配的数据

where 列1 regexp ‘.000’ – 正则表达式 .000 中 .是一个特殊字符，表示匹配任意一个字符

mysql中的正则表达式不区分大小写

where 列1 regexp ‘1000|2000’ --进行or匹配，| 是正则表达式的or操作符，表示匹配其中之一

where 列1 regexp ‘[123] ton’ – []符号表示匹配几个字符之一，匹配1或2或3，是另一种or形式语句
[^123] --匹配除这些字符外的任何东西
[0-9] --匹配0到9范围内的数
[a-z] --a到z范围内的字符

匹配特殊字符时，如找带点的字符串，这时需要 \转移符为前导，例如：\. 这是查找点的意思
\也可以用来引用元字符， \f 换页，\n 换行， \r 回车，\t 制表，\v纵向制表
匹配\时，写成\\

多数表达式用单斜杠转义，mysql要求两个斜杠，mysql自己解释一个，正则表达式库解释一个

Mysql匹配字符类表：
[: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个匹配，等于{0,1}
  {n} 指定数目的匹配
  {n,} 不少于指定数目的匹配
  {n,m} 匹配数目的范围，m不超过255

例子：‘[[:digit:]]{4}’，digit匹配任意数字，{4}表示出现4次，也就是匹配连在一起的任意4个数字

定位符，匹配特定位置的文本
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾

¹，这是匹配以一个数开始的文本

^的用途有两个，文本开始处 和 否定该集合
通过用^开始每个表达式，用$结束每个表达式，可以使regexp的作用于like一样。

可以在不使用数据库表的情况下用 select测试正则表达式，regexp检查总是返回0或1，如法如下：
select ‘hello’ regexp ‘[0-9]’; 这个例子显然返回0，因为文本hello中没有数字

------------------- 创建计算字段 -------------------------

concat() 函数拼接两个列
select concat(列1,列2) from 表 --拼接列1和列2构成一个单值，各个列用逗号隔开，
select concat(age,’(’,name,’)’) from course; --带括号的写法

rtrim() 函数去掉值右边的所有空格
ltrim() 去掉左边的空格
trim() 去掉左右两边的空格

as关键字表示别名
select concat(列1，列2）as 别名 from 表; --把拼接好的列重新定义一个名字
别名有时也称为导出列

执行算数计算
select 列1列2 as 别名 from 表
mysql算数符有： +，-，,/ 加减乘除

--------------------- 使用数据处理函数 --------------------

sql的函数可移植性很查，尽量用其它方法来实现，用函数一定要写好注释
大多数sql支持的函数有：文本串处理，数值算数，处理日期，返回DBMS的特殊信息

文本处理函数如下：
rtrim() 去掉右边的空格
ltrim() 去掉左边的空格
trim() 去掉左右两边的空格
upper() 转成大写
lower() 转小写
left() 返回串左边的字符
right() 返回右边的字符
leength() 返回串的长度
locate() 找出串的一个子串
soundex() 返回串的soundex值，也就是返回两个字符串的相似性
substring() 返回子串的字符

用soundex()函数可以匹配发音类似子串的数据

日期和时间处理函数：
AddDate() 增加一个日期，天、周等
AddTime() 增加一个时间，时，分等
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 时间的月部分
Now() 返回当前日期和时间
Second() 秒部分
Time() 一个日期时间的时间部分
Year() 日期的年份部分

where Date(日期列) = ‘2009-03-22’, --仅对日期列的日期部分进行匹配
where Date(日期列) between ‘2009-09-01’ and ‘2009-09-30’; --查找9月份的所有数据
where Year(日期列) = 2009 adn Month(日期列) = 9; --这样也是查出09年9月份的数据，用到两个函数

数值处理函数：主要用于代数，三角或几何运算，这些函数在其它DBMS比较统一
Abs() 数的绝对值
Cos() 角度的余弦
Exp() 数的指数值
Mod() 除操作数的余数
Pi() 圆周率
Rand() 随机数
Sin() 角度的正弦
Sqrt() 数的平方根
Tan() 角度的正切

--------------------- 汇总数据 --------------------------

想得到汇总信息而不是汇总数据时，可以使用聚集函数

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

SQL聚集函数：
AVG() 某列平均值
COUNT() 某列的行数
MAX() 最大值
MIN() 最小值
SUM() 某列值之和

还有一系列的标准偏差聚集函数，但这本书没写

select AVG(列) as 别名 from 表 --查平均值的语法

avg只能确定一列的平均值，多个列要用多个avg，avg忽略列值为NULL的行

count(*)，对表中的所有行进行计数，无论NULL与否。
count(列名),对特定列，忽略NULL值

sum(列1 * 列2) – sum函数中可以用算术符

聚集不同值，用 DISTINCT
select sum(DISTINCT 列) from 表; --DISTINCT只会聚集不同的值，然后算平均值，上面的五个聚集函数都可以用DISTINCT

DISTINCT必须使用列名，不能用于计算或表达式

组合聚集函数： select可以包含多个聚集函数，例如：
select count(列1) as 别名, min(列2) as 别名,max(列3) as 别名 from 表

在数据库用聚合函数处理，比在客户端做数据处理效率要高。

----------------------- 分组数据 ------------------------

group by 是分组语句关键字

select 列1,count(*) as 列1的总数 from 表 group by 列1; --group对列1相同的数据进行分组

除聚集函数外，select语句中的每个列必须在 group by 语句中给出，group要出现在order排序之前

select 列1,count(*) as 列1的总数 from 表 group by 列1 with rollup; --with rollup关键字可以得到每个分组汇总级别的值

having 关键字进行过滤分组， having支持所有where操作符，这两个语法都相同，只是关键字不一样。

select 列1,count() as 列1的总数 from 表 group by 列1 having count() >5; --对列1分组过后，每个分组必须大于5进行过滤。

分组和排序，在用group分组后要接着用order排序，这样最稳妥。

from 表 分组 过滤分组 排序； – 这是一般的写法

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

---------------- 重要 ------------------

---

1. 0-9\. ↩︎