MySQL必知必会
一、概念
模式（schema）关于数据库和表的布局及特性的信息；
列（column）表中的一个字段，所有的表都是由一个或者多个列组成；
数据类型（datatype）所容许的数据的类型，每个表列都有相应的数据类型，它限制列中存储的数据；
行（row）表中的一个记录；
主键（primary key）一列或者一组列，其值能够唯一区分表中的每一行；
SQL（Structured Query Language）的缩写，是专门与数据库通信的语言；
二、使用MySQL
查看用户授权
mysql> show grants for mm@'%'; +---------------------------------------------------------------+ | Grants for mm@% | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mm'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `yuan1`.* TO 'mm'@'%' | +---------------------------------------------------------------+ 2 rows in set (0.00 sec)
三、数据过滤
当 and 和or 同时出现时候，优先执行and，改变优先级用括号
mysql> select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price>=10;
通配符（wildcard）过滤
%表示任何字符出现任何次数；
_匹配任何单个字符；
通配符使用技巧：
不要过渡使用通配符，如果其他操作能达到同样的目的，应该使用其他操作符；
非必要不要使用在搜索模式的开始处；
四、使用正则表达式进行搜索
这则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。
REGXP 是regular expression的缩写，当检索某个文本时候，可以使用一种模式来描述要检索的内容；
mysql> select prod_name from products where prod_name regexp '1000'; +--------------+ | prod_name | +--------------+ | JetPack 1000 | +--------------+ 1 row in set (0.01 sec)
“.”表示正则表达式中一个特殊的字符。
mysql> select prod_name from products where prod_name regexp '.000'; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.03 sec)
进行or匹配
mysql> select prod_name from products where prod_name regexp '1000|2000'; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.01 sec)
匹配几个字符之一
mysql> select prod_name from products where prod_name regexp '[123] Ton'; +-------------+ | prod_name | +-------------+ | 1 ton anvil | | 2 ton anvil | +-------------+ 2 rows in set (0.00 sec)
匹配范围，[0123456789]可以简化为[0-9]
mysql> select prod_name from products where prod_name regexp '[1-5] Ton'; +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | +--------------+ 3 rows in set (0.00 sec)
匹配特殊字符，使用前导符 \\
mysql> select vend_name from vendors where vend_name regexp '\\.'; +--------------+ | vend_name | +--------------+ | Furball Inc. | +--------------+ 1 row in set (0.00 sec)
匹配多个实例：
* 0个或者多个匹配；
+ 一个或者多个匹配
？ 0个或者1个匹配
｛n｝ 指定数据匹配
｛n,｝ 不少于指定数目匹配
｛n,m｝ 匹配数据的范围
mysql> select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)'; +----------------+ | prod_name | +----------------+ | TNT (1 stick) | | TNT (5 sticks) | +----------------+ 2 rows in set (0.00 sec)
匹配连在一起的4位数字
mysql> select prod_name from products where prod_name regexp '[[:digit:]]{4}'; +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 2 rows in set (0.01 sec)
定位符
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
mysql> select prod_name from products where prod_name regexp '^[0-9\\.]'; +--------------+ | prod_name | +--------------+ | .5 ton anvil | | 1 ton anvil | | 2 ton anvil | | 12 Joy | +--------------+ 4 rows in set (0.01 sec)
五、创建计算字段
concat函数用户连接
mysql> select concat(vend_name,'(',vend_country,')') from vendors; +----------------------------------------+ | concat(vend_name,'(',vend_country,')') | +----------------------------------------+ | Anvils R Us(USA) | | LT Supplies(USA) | | ACME(USA) | | Furball Inc.(USA) | | Jet Set(England) | | Jouets Et Ours(France) | +----------------------------------------+ 6 rows in set (0.00 sec)
TRrim（）删除数据右边多余的空格来整理数据：
mysql> select concat(Rtrim(vend_name),'(',Rtrim(vend_country),')') from vendors; +------------------------------------------------------+ | concat(Rtrim(vend_name),'(',Rtrim(vend_country),')') | +------------------------------------------------------+ | Anvils R Us(USA) | | LT Supplies(USA) | | ACME(USA) | | Furball Inc.(USA) | | Jet Set(England) | | Jouets Et Ours(France) | +------------------------------------------------------+ 6 rows in set (0.00 sec)
Ltrim() 去掉左边的空格，Trim()去掉两边的空格。
使用别名：
六、使用数据处理函数
Upper（）将文本转换为大写：
mysql> select vend_name,Upper(vend_country) from vendors; +----------------+---------------------+ | vend_name | Upper(vend_country) | +----------------+---------------------+ | Anvils R Us | USA | | LT Supplies | USA | | ACME | USA | | Furball Inc. | USA | | Jet Set | ENGLAND | | Jouets Et Ours | FRANCE | +----------------+---------------------+ 6 rows in set (0.00 sec)
Date（）函数
mysql> select *,Date(order_date) from orders; +-----------+---------------------+---------+------------------+ | order_num | order_date | cust_id | Date(order_date) | +-----------+---------------------+---------+------------------+ | 20005 | 2005-09-01 00:00:00 | 10001 | 2005-09-01 | | 20006 | 2005-09-12 00:00:00 | 10003 | 2005-09-12 | | 20007 | 2005-09-30 00:00:00 | 10004 | 2005-09-30 | | 20008 | 2005-10-03 00:00:00 | 10005 | 2005-10-03 | | 20009 | 2005-10-08 00:00:00 | 10001 | 2005-10-08 | +-----------+---------------------+---------+------------------+ 5 rows in set (0.00 sec)
Year（）函数从一个日期中返回年份的函数，Month（）从一个日期中返回月份：
mysql> select order_date,Year(order_date),Month(order_date) from orders; +---------------------+------------------+-------------------+ | order_date | Year(order_date) | Month(order_date) | +---------------------+------------------+-------------------+ | 2005-09-01 00:00:00 | 2005 | 9 | | 2005-09-12 00:00:00 | 2005 | 9 | | 2005-09-30 00:00:00 | 2005 | 9 | | 2005-10-03 00:00:00 | 2005 | 10 | | 2005-10-08 00:00:00 | 2005 | 10 | +---------------------+------------------+-------------------+ 5 rows in set (0.00 sec)
七、汇总数据
聚集函数（aggregate function）
mysql> select AVG(prod_price) as avg_price from products; +-----------+ | avg_price | +-----------+ | 15.435333 | +-----------+ 1 row in set (0.00 sec)
COUNT()函数
COUNT（*）对表中行的数据进行技术，不管表中包含的是空值（NULL）还是非空值。
COUNT(column)对表中特定列中具有的行进行行数统计，忽略NULL！！！！！！！！！！！
八、创建联接
mysql> select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id; +-------------+----------------+------------+ | vend_name | prod_name | prod_price | +-------------+----------------+------------+ | Anvils R Us | .5 ton anvil | 5.99 | | Anvils R Us | 1 ton anvil | 9.99 | | Anvils R Us | 2 ton anvil | 14.99 | | LT Supplies | Fuses | 3.42 | | LT Supplies | Oil can | 8.99 | | ACME | Detonator | 13.00 | | ACME | Bird seed | 10.00 | | ACME | Carrots | 2.50 | | ACME | Safe | 50.00 | | ACME | Sling | 4.49 | | ACME | TNT (1 stick) | 2.50 | | ACME | TNT (5 sticks) | 10.00 | | Jet Set | 12 Joy | 5.66 | | Jet Set | JetPack 1000 | 35.00 | | Jet Set | JetPack 2000 | 55.00 | +-------------+----------------+------------+ 15 rows in set (0.00 sec)
九、操纵表
增加外键：
mysql> alter table products add constraint fk_products_vendors foreign key(vend_id) references vendors(vend_id);
十、存储过程
创建存储过程
mysql> DELIMITER // mysql> create procedure productpricing() -> begin -> select avg(prod_price) as priceaverage from products; -> end // Query OK, 0 rows affected (0.06 sec) mysql> DELIMITER ;
其中，DELIMITER // 告诉命令使用//作为语句结束的分隔符，结尾将分隔符调整为;
调用存储过程：
mysql> CALL productpricing(); +--------------+ | priceaverage | +--------------+ | 15.435333 | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
删除存储过程
mysql> drop procedure productpricing; Query OK, 0 rows affected (0.01 sec)
创建带参数的存储过程：
mysql> delimiter // mysql> create procedure productpricing( -> out p1 decimal(8,2), -> out ph decimal(8,2), -> out pa decimal(8,2) -> ) -> begin -> select min(prod_price) -> into p1 -> from products; -> select max(prod_price) -> into ph -> from products; -> select avg(prod_price) -> into pa -> from products; -> end; -> // Query OK, 0 rows affected (0.00 sec)
OUT参数指出相应的参数用来从存储过程传出一个值（返回给调用者）
调用存储过程
mysql> call productpricing(@pricelow,@pricehigh,@priceaverage); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select @pricelow; +-----------+ | @pricelow | +-----------+ | 2.50 | +-----------+ 1 row in set (0.00 sec)
十一、触发器
触发器是某个表发生更改时自动处理，触发器是MySQL相应 DELETE，INSET；UPDATE，语句而自动执行的一条MySQL语句。
创建触发器时候，需要给出4条信息：
唯一的触发器名称；
触发器关联的表；
触发器应该相应的活动（delete、insert或者update）；
触发器合适执行（处理之前或之后）。
创建一个触发器：
mysql> create trigger newproduct after insert on products for each row select 'Product added' into @arg; Query OK, 0 rows affected (0.01 sec)
create TRIGGER创建一个名称为newproduct的触发器，动作在insert 后面。
只有表支持触发器，视图不支持触发器；
触发器按每个事件每次定义；
每个表每个事件每次只允许一个触发器；
每个表最多支持6个触发器（每条insert、update、delete的之前和之后）；
单一触发器不能与多个事件或多个表关联；
创建一个insert触发器
mysql> create trigger neworder after insert on orders for each row select new.order_num into @arg2; Query OK, 0 rows affected (0.01 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+ 5 rows in set (0.01 sec) mysql> insert into orders(order_date,cust_id) values(now(),10001); Query OK, 1 row affected (0.01 sec) mysql> select @arg2; +-------+ | @arg2 | +-------+ | 20011 | +-------+ 1 row in set (0.00 sec) mysql> select * from orders; +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | | 20011 | 2022-09-12 10:10:33 | 10001 | +-----------+---------------------+---------+ 6 rows in set (0.00 sec)
创建一个DELETE触发器：
mysql> create trigger deleteorder before delete on orders for each row begin insert into archive_orders(order_num,order_date,cust_id) values(old.order_num,old.order_date,old.cust_id); -> end; -> // Query OK, 0 rows affected (0.07 sec)
delete 触发器引用了一个名为old的虚拟表，访问被删除的行。
十二、管理事务处理
事务处理（transaction processing）可以维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
控制事务处理：
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from test1; Query OK, 1 row affected (0.01 sec) mysql> select * from test1; Empty set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from test1; +------+ | id | +------+ | 5 | +------+ 1 row in set (0.00 sec)
十三、字符集
查看数据库支持的字符集：
mysql> show character set; mysql> show collation;
查看数据使用的字符集：
mysql> show variables like 'character%'; +--------------------------+------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /application/mysql/share/charsets/ | +--------------------------+------------------------------------+ 8 rows in set (0.02 sec) mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
十四、用户管理
查看用户权限
mysql> show grants for yy; +--------------------------------+ | Grants for yy@% | +--------------------------------+ | GRANT USAGE ON *.* TO 'yy'@'%' | +--------------------------------+ 1 row in set (0.00 sec)
USAGE就是没有任何权限。
mysql> grant select on crashcourse.* to yy; Query OK, 0 rows affected (0.01 sec) mysql> show grants for yy; +---------------------------------------------+ | Grants for yy@% | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'yy'@'%' | | GRANT SELECT ON `crashcourse`.* TO 'yy'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec)
回收权限：
mysql> revoke select on crashcourse.* from yy; Query OK, 0 rows affected (0.01 sec)
十五、数据备份
ANALYZE TABLE用来检查表键是否正确；
mysql> analyze table orders; +--------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+---------+----------+----------+ | crashcourse.orders | analyze | status | OK | +--------------------+---------+----------+----------+ 1 row in set (0.03 sec)
CHECK TABLE 检查表
mysql> check table orders; +--------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+-------+----------+----------+ | crashcourse.orders | check | status | OK | +--------------------+-------+----------+----------+ 1 row in set (0.02 sec)
