mysql 必知必会 学习笔记

– Ubuntu Linux mysql 启动 停止
1.启动：/etc/init.d/mysql start
2.停止：/etc/init.d/mysql stop
3.重启：/etc/init.d/mysql restart

– MYSQL远程连接速度慢的解决方法
后来在网上发现解决方法,my.ini里面添加
[mysqld]
skip-name-resolve
skip-name-resolve 禁用DNS解析，连接速度会快很多。不过，这样就不能在授权表中使用主机名,只能用ip格式。

–mysql 客户端
1.MySQL Administrator
2.MySQL Query Browser

–
show columns from _tablename;
7.1

–order 排序方式 & 返回多列
select prod_id, prod_name, prod_price from products order by prod_id, prod_price;
–desc
select prod_id, prod_name, prod_price from products order by prod_id desc, prod_price;
–distinct 返回不相同的值(去重)
select distinct vend_id from products;
–limit 5,6 限制返回 从行5开始的6行
select prod_name from products limit 5,6;
–完全限定的表名
select products.prod_name from crashcourse.products;
–找到 TOP5
select prod_price from products order by prod_price desc limit 5;

–note:如果同时使用order by 和 where时，应该让order by在where之后
select prod_name, prod_price from products where prod_price = 2.5;
select * from products where vend_id > 1002 order by vend_id;
–范围查找
select * from products where prod_price between 5 and 10;
–空值null 查找(null值不能判断是否匹配)
select prod_name from products where prod_price is null;
select * from customers where cust_email is null;
–操作符 and or
select * from products where vend_id = 1003 and prod_price <= 10;
select * from products where vend_id = 1003 or prod_price <= 10;
–在处理 or 操作符前，优先处理and操作符。但最好都用()确认好计算次序
select * from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
select * from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
–操作符 in 与 纯or操作符 功能相同，且in操作符更直观、执行更快。还可包含其它select语句。
select * from products where vend_id in (1002, 1003, 1004) order by prod_name;
–操作符 not (mysql 仅支持对in、between、exists 使用not)
select * from products where vend_id not in (1002, 1003, 1004) order by prod_name;

–第8章 通配符like
–note:尽量少用通配符，因为效率不高
–note:除非必要，不要将通配符放在搜索模式开始处，因为这样耗时最多
–note:正确使用通配符很有必要，也经常用到
–通配符 % 任何字符出现任意次数
select prod_id, prod_name from products where prod_name like ‘jet%’;
select * from products where prod_name like ‘%anvil%’;
select * from products where prod_name like ‘s%e’;
–通配符 _ 任何单个字符
select * from products where prod_name like ‘_ ton anvil’;

–第9章 正则表达式 regexp 表示后面接的是正则表达式
–默认不区分大小写，可使用binary来区分大小写
–包含1000的所有行：
select * from products where prod_name regexp ‘1000’ order by prod_name;
–’.’ 正则表达式中的特殊字符，它表示 匹配任意一个字符。
select * from products where prod_name regexp ‘.000’;
–’|’ 或(or)匹配
select * from products where prod_name regexp ‘1000|2000|3000’;
–’[]’ 匹配几个字符之一(另一种形式的or) ‘1 ton’ ‘2 ton’ ‘3 ton’
select * from products where prod_name regexp ‘[123] ton’;
–’^’ 否定匹配符 （任意非123字符 + ton）
select * from products where prod_name regexp ‘[^123] ton’;
–’[-]’ 匹配范围 例如：[1-6]、[a-z]
select * from products where prod_name regexp ‘[1-5] ton’;
–’\’ 匹配指定字符(转义符)
select * from vendors where vend_name regexp ‘\.’;
–’\’ 也用来引用元字符

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

–9.2.7 匹配多个实例

–元字符
–* 0个或多个匹配
–+ 1个或多个匹配 等于{1,}
–？ 匹配它前面字符的0个或1个匹配 等于{0,1} sticks or stick
select * from products where prod_name regexp ‘\([0-9] sticks?\)’;
–{n} 指定数目的匹配
select * from products where prod_name regexp ‘[[:digit:]]{4}’;
select * from products where prod_name regexp ‘[0-9][0-9][0-9][0-9]’;
–{n,} 不少于指定数目的匹配
–{n,m} 匹配数目的范围 m<=255

–定位元字符
–^ 文本的开始 (找以’.'开始的数)
select * from products where prod_name regexp ‘¹’;
–$ 文本的结尾
select * from products where prod_name regexp ‘[0-9]$’;
–[[:<:]] 词的开始 例：文本 ‘jet 110’ 中有两个词
select ‘jet 1000’ regexp ‘[[:<:]]je’;
–[[:>:]] 词的结尾
select ‘jet 1000’ regexp ‘[0-9][[:>:]]’;

–简单的正则表达式 测试
select ‘hello’ regexp ‘[0-9]’; --没有匹配：0，匹配：1

–10.1 计算字段 是运行时在select语句内创建的
–10.2 拼接字段
–concat 拼接字段 多数dbms使用+或||来拼接
select concat(vend_name, ’ (’, vend_country, ‘)’) from vendors;
–rtrim() 函数去掉右边所有空格 ltrim(左边空格) trim(两边空格)
select concat(rtrim(vend_name), ’ (’, rtrim(vend_country), ‘)’) from vendors;
–as 使用别名(导出列)
select concat(rtrim(vend_name), ’ (’, rtrim(vend_country), ‘)’) as vend_title from vendors;
–10.3 算术操作符 + - * /
select prod_id, quantity, item_price, quantity*item_price as ex_price from orderitems where order_num = 20005;

–11.1 函数
–upper 转换成大写
select vend_name, upper(vend_name) as vend_name_upcase from vendors;
–11.2.1 文本处理函数
–left() 返回串左边的字符
–length() 返回串的长度
–locate() 找出串的一个子串
–lower() 将串转换为小写
–ltrim() 去掉串左边的空格
–rtrim() 去掉串右边的空格
–right() 返回串右边的字符
–soundex() 返回串的soundex值 (‘Y Lie’ 与 ‘Y Lee’ 发音相似)
select * from customers where soundex(cust_contact) = soundex(‘Y Lie’);
–substring() 返回子串的字符
–upper() 将串转换成大写

–11.2.2 日期和时间处理函数
AddDate() --增加一个日期
AddTime() --增加一个时间
CurDate() --返回当前日期
CurTime() --返回当前时间
Date() --增加日期时间的日期部分
DateDiff() --计算两个日期之差
Date_Add() --高度灵活的日期运算函数
Date_Format() --格式化的日期或时间串
Day() --日期的天数部分
DayOfWeek() --返回一个日期是星期几
Hour() --一个时间的小时部分
Minute() --一个时间的分钟部分
Month() --一个日期的月份部分
Now() --当前日期和时间
Second() --一个时间的秒部分
Time() --一个日期时间的时间部分
Year() --日期的年份部分
– 指定某天时间内
select * from orders where date(order_date) = ‘2005-09-01’;
– 指定某段时间内
select * from orders where date(order_date) between ‘2005-09-01’ and ‘2005-09-30’;
– 指定某月时间内
select * from orders where year(order_date) = 2005 and month(order_date) = 9;

–11.2.3 数值处理函数
Abs() --绝对值
Cos() --角度的余弦
Exp() --数的指数值
Mod() --余数
Pi() --圆周率
Rand() --随机数
Sin() --角度的正弦
Sqrt() --平方根
Tan() --角度的正切

–12.1 聚集函数 将数据汇总而不用实际检索出来（高效的，一般比程序中计算更快）
–avg() --平均值
select avg(prod_price) as avg_price from products （where xx = …）;
–count() --行数
select count() as mun_cust from customers;
select count(cust_email) as mun_cust from customers;
–max() --最大值
–min() --最小值
select max(prod_price) as max_price from products;
–sum() --和
select sum(quantity) as timers_ordered from orderitems where order_num = 20005;
select sum(quantityitem_price) as timers_ordered from orderitems where order_num = 20005;
–distinct 【去重】 必须使用列名，不能用于计算或表达式
–如果指定列名，则distinct只能用于count()且不能用于count()
select avg(distinct prod_price) as avg_price from products
–返回（物品数目、最高价格、最低价格、平均价格）
select count() as num_items,
min(prod_price) as price_min,
max(prod_price) as price_max,
avg(prod_price) as price_avg
from products;

–13.1 数据分组
–group by 除聚集计算语句外，select中的每列都必须在group by子句中
–group by 必须在where之后，order by之前
select vend_id, count() as num_prods from products group by vend_id order by num_prods;
–with rollup 会将所有分组再进行汇总
select vend_id, count() as num_prods from products group by vend_id with rollup;
–13.3 过滤分组
–having 分组语法中的where
–where数据分组前进行过滤，having的数据分组后进行过滤
select cust_id, count() as orders from orders group by cust_id having count() >= 2;
–13.4 分组和排序
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(ordertotal) >= 50;
–13.5 select 子句顺序如下
select --1. 要返回的列表或表达式
from --2. 从中检索数据的表
where --3. 行级过滤
group by --4. 分组说明
having --5. 组级过滤
order by --6. 输出排序顺序
limit --7. 要检索的行数

–14.1 子查询 一般与：IN = <> 配合使用
select order_num from orderitems where prod_id = ‘TNT2’;
select cust_id from orders where order_num in(20005, 20007);
– 上面两句等于：
select cust_id from orders where order_num in(
select order_num from orderitems where prod_id = ‘TNT2’
);
– 子查询中，需保证select具有与where子句中相同数目的列。
select * from customers where cust_id in (
select cust_id from orders where order_num in (
select order_num from orderitems where prod_id = ‘TNT2’));

–14.3
select cust_name, cust_state, (select count(*) from orders
where orders.cust_id = customers.cust_id) as orders from customers order bycust_name;

–15.1 join联结 sql最强大最重要的特性
–！！！sql最强大的功能之一就是：在数据检索查询的执行中联结(join)

–15.1.1 关系表 ：将信息分成多个独立的表，以避免相同数据出现多次.各表之间通过常用值互相关联
–note:外键：定义了两表之间的关系(外部主键)
–note:关系数据库 可以有效地存储和方便处理，因此它的可伸缩性远比非关系数据库要好。
–note:笛卡儿积 联结后的行最大数目是两个表的行数积
–note:叉联结
select vend_name, prod_name, prod_price from vendors, products
where vendors.vend_id = products.vend_id

–15.2.2 内部联结 inner join 前后两个表顺序可换
select vend_name, prod_name, prod_price from vendors inner join products
on vendors.vend_id = products.vend_id;

–15.2.3 联结多个表
select prod_name, vend_name, prod_price, quantity from orderitems, products, vendors
where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;

–note:外联结与内联结的区别：内联结需两边都有元素才联结，外联结只需其中一边有元素就可联结，另一边若无则为null
–16 高级联结：自联结、自然联结和外部联结
–16.1 使用表别名 表别名跟列别名不一样，它不返回到客户机
select cust_name, cust_contact from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = ‘TNT2’;
–16.3.1 自联结 有时自联结会比子查询快得多，虽然结果一样
–方式1：子查询
select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id =‘DTNTR’);
–方式2：自联结
select p1.prod_id, p1.prod_name from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR’;
–16.2.2 自然联结 用where实现
select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id and oi.order_num = o.order_num and oi.prod_id = ‘FB’;
–16.2.3 外部联结 left outer join \ right outer join
select customers.cust_id, orders.order_num from customers
left outer join orders on customers.cust_id= orders.cust_id;

–
–16.3 使用带聚集函数的联结
select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord from customers
inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;
select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord from customers
left outer join orders on customers.cust_id = orders.cust_id group by customers.cust_id;
–16.4 使用联结和联结条件

–17.1 组合查询 下面两种情况时需要用到
–（1）在单个查询中从不同的表返回类似结构的数据
–（2）对单个表执行多个查询，按单个查询返回数据

–17.2 创建组合查询 union 可将多条select语句的结果 组合成单个结果集
–17.2.1 union 虽然看起来union比where更繁琐，但处理多表时更简单
select vend_id, prod_id, prod_price from products where prod_price <= 5
union
select prod_id, vend_id, prod_price from products where vend_id in (1001, 1002);
–17.2.2 union规则
–（1）n条select语句就有需要n-1个union分隔
–（2）union和每个查询必须包含相同的列、表达式或聚集函数（次序不必相同）
–（3）列数据类型必须兼容，不必完全相同（可隐式转换的类型）
–17.2.3 包含或取消重复的行 union 默认自动从结果中去重，若相显示所有结果，可使用union all
–17.2.4 对组合查询结果排序 order by 只能出现一次且出现在最后一条select语句之后

–18.1 理解全文本搜索 不是所有引擎都支持全文本搜索，MyISAM(支持)InnoDB(不支持)
–且有几个限制
–（1）性能问题
–（2）明确控制：用通配符和正则表达式，很难明确控制匹配和不匹配什么。
–（3）智能化的结果：同义词等需求
–18.2 使用全文本搜索 索引后，select可与match()和against()一起使用来搜索
–18.2.1 启用全文本搜索支持 fulltext
–note:应先导入数据，再定义fulltext，这样导入数据更快
create table productnoteMy
(
note_id int not null auto_increment,
prod_id char(10) not null,
note_date datetime not null,
note_text text null,
primary key(note_id),
fulltext(note_text)
)engine=MyISAM;
–18.2.2 全文本搜索 因数据是索引的，全文本搜索相当快。
select note_id, note_text from productnotes where match(note_text) against(“rabbit”);
select match(note_text) against(‘rabbit’) as rank, note_text from productnotes order by rank desc;
–18.2.3 查询扩展 返回了有’anvils’所在行上任意单词的所有行
select note_id, note_text from productnotes where match(note_text) against(‘anvils’ with query expansion);
–18.2.4 布尔文本搜索 没有fulltext索引也可以使用。是一种非常缓慢的操作
select note_text from productnotes where match(note_text) against(‘heavy’ in boolean mode);
–包含heavy 但没有rope开始的词(rope*)
select note_text from productnotes where match(note_text) against(‘heavy -rope*’ in boolean mode);
–全文布尔操作符
– + 包含，词必须存在
– - 包含，词必须不出现
– > 包含，且增加等级值
– < 包含，且减少等级值
– () 把词组成子表达式
– ~ 取消一个词的排序值
– * 词尾通配符
– “” 定义一个短语
–18.2.5 全文本搜索的使用说明 若干…

---

1. 0-9\. ↩︎