《MySQL必知必会》学习笔记

MySQL必知必会学习笔记


chapter 01


SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。
SQL是一种专门用来与数据库通信的语言。


chapter 02
mysql -u USER -p[PASSWORD]  [-h HOST -P PORT]


chapter 03
show databases;
use DATABASE;
show tables;


desc TABLE = describe TABLE = show columns from TABLE;


show status;
show create database DATABASE;
show create table TABLE;
show grants;
show errors|warnings;
help show;


mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:


SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]


like_or_where:
    LIKE 'pattern'
  | WHERE expr


If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.


Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.


URL: http://dev.mysql.com/doc/refman/5.5/en/show.html


MySQL5 新增库information-schema


chapter 04 检索数据
limit 3,4 = limit 4 offset 3


chapter 13 分组
select
from
where
group by
having
order by
limit


chapter 14 使用子查询
子查询作为计算字段
select cust_name,
     cust_state,
     (select count(*) from orders where orders.cust_id=customer.cust_id) as orders
from customers
order by cust_name;


chapter 15 联结表
select a.x, b.y from a, b where a.xid = b.xid
=
select a.x b.y from a inner join b on a.xid=b.xid 推荐


chapter 16 创建高级联结
自然联结
select a1.id, a1.name
from  a as a1, a as a2
where a1.xid = a2.xid
and a1.name='xx';


外部联结 left|right outer join


chapter 17 组合查询
union 组合，去除重复行
union all 组合全部行，不去除重复
union 排序只要写到最后一个union后面就ok了。


chapter 18 全文搜索
条件：
引擎MyISAM;
接受FULLTEXT


CREATE TABLE productnotes
{
     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;
导入表时先不要指定FULLTEXT，导完数据后再alter table;
执行全文搜索
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');


chapter 19 插入数据
1.insert [LOW_PRIORITY] into TABLE values  (v1,v2....vn);
2.insert [LOW_PRIORITY] into TABLE (f1,f2...fn) values  (v1,v2....vn);
-- 适合插入大量数据
3.insert [LOW_PRIORITY] into TABLE (f1,f2...fn) values
(v1,v2....vn),
(v1,v2....vn),
....
(v1,v2....vn);
4.insert into TABLE (f1, f2...fn) select f1,f2..fn from TABLE1;