安装MySQL

添加mysql源

# rpm -Uvh http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm

 

安装mysql

# yum -y install mysql-community-server

 

启动mysql并设置为开机自启动服务

# chkconfig mysqld on# service mysqld start

 

检查mysql服务状态

# service mysqld status

 

第一次启动mysql，会在日志文件中生成root用户的一个随机密码，使用下面命令查看该密码

# grep 'temporary password' /var/log/mysqld.log

 

修改root用户密码

# mysql -u root -p -h localhostEnter password: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql-2016';

 

 创建数据库（数据库名为：crashcourse）

mysql> create database crashcourse;

 

使用crashcourse数据库

mysql> use crashcourse;

 

执行sql脚本（使用source命令）

mysql> source /root/MySQLCrashCourse/create.sql;mysql> source /root/MySQLCrashCourse/populate.sql;

 

查看可用数据库的列表

mysql> show databases;

 

 查看当前数据库内可用表的列表

mysql> show tables;

 

显示表列（表名：customers）

mysql> show columns from customers;

 

 显示服务器错误或警告消息

mysql> show errors;mysql> show warnings;

 

检索数据

检索单个列

mysql> select prod_name from products;

 

检索多个列

mysql> select prod_id, prod_name, prod_price from products;

 

检索所有列（除非你确实需要表中的每个列，否则最好别使用*通配符，因为会降低应用程序的性能）

mysql> select * from products;

 

检索不同的行（使用distinct关键字）

mysql> select distinct vend_id from products;

 

限制结果

返回不多于5行mysql> select prod_name from products limit 5;检索的开始行和行数mysql> select prod_name from products limit 5,5;

 

使用完全限定的表名

mysql> select products.prod_name from crashcourse.products;

 

排序检索数据

排序数据

mysql> select prod_name from products order by prod_name;

 

按多个列排序

mysql> select prod_id, prod_price, prod_name from products order by prod_price, prod_name;

 

指定排序方向（使用desc关键字，desc为倒序，默认是asc，正序）

mysql> select prod_id, prod_price, prod_name from products order by prod_price desc;

 

使用order by和limit的组合

mysql> select prod_price from products order by prod_price desc limit 1;

 

过滤数据

检索单个值

mysql> select prod_name, prod_price from products where prod_name = 'fuses';

 

不匹配检查

mysql> select vend_id, prod_name from products where vend_id != 1003;

 

范围值检查

mysql> select prod_name, prod_price from products where prod_price between 5 and 10;

 

空值检查

mysql> select prod_name from products where prod_price is null;

 

数据过滤

and操作符

mysql> select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;

 

or操作符

mysql> select prod_name, prod_price from products where vend_id = 1003 or prod_id = 1003;

 

计算次序

mysql> select prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

 

in操作符

mysql> select prod_name, prod_price from products where vend_id in (1002,1003) order by prod_name;

 

not操作符

mysql> select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;

 

用通配符进行过滤

百分号（%）通配符（匹配0,1或多个字符）

mysql> select prod_id, prod_name from products where prod_name like 'jet%';

 

下划线（_）通配符（匹配1个字符）

mysql> select prod_id, prod_name from products where prod_name like '_ ton anvil';

 

用正则表达式进行搜索

基本字符匹配

mysql> select prod_name from products where prod_name regexp '.000' order by prod_name;

 

进行or匹配

mysql> select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

 

匹配范围

mysql> select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;

 

匹配特殊字符（使用\\进行转义）

mysql> select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

 

匹配字符类

mysql> select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;

 

匹配多个实例

mysql> select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;

 

定位符（^, $, [[:<:]], [[:>:]]）

mysql> select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;

 

创建计算字段

拼接字段

mysql> select concat(vend_name, ' (', vend_country, ')') from vendors order by vend_name;

 

使用别名

mysql> select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')') as vend_title from vendors order by vend_name;

 

执行算术计算

mysql> select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;

 

使用数据处理函数

文本处理函数

mysql> select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;mysql> select cust_name, cust_contact from customers where soundex(cust_contact) = soundex('Y Lie');

 

日期和时间处理函数

mysql> select cust_id, order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';mysql> select cust_id, order_num from orders where year(order_date) = 2005 and month(order_date) = 9;

 

汇总数据

avg()函数

mysql> select avg(prod_price) as avg_price from products;

 

count()函数

mysql> select count(*) as num_cust from customers;

 

max()函数

mysql> select max(prod_price) as max_price from products;

 

min()函数

mysql> select min(prod_price) as min_price from products;

 

sum()函数

mysql> select sum(quantity) as items_ordered from orderitems where order_num = 20005;mysql> select sum(item_price*quantity) as total_price from orderitems where order_num = 20005;

 

聚合不同的值

mysql> select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

 

组合聚集函数

mysql> 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;

 

 分组数据

创建分组

mysql> select vend_id, count(*) as num_prods from products group by vend_id;

 

过滤分组（使用having关键字）

mysql> select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;mysql> select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;

 

分组和排序

mysql> select order_num, sum(quantity*item_price) as ordertotal    from orderitems    group by order_num    having sum(quantity*item_price) >= 50    order by ordertotal;

 

使用子查询

利用子查询进行过滤

mysql> select cust_name, cust_contact        from customers        where cust_id in (select cust_id        from orders        where order_num in (select order_num        from orderitems        where prod_id = 'TNT2'));

 

作为计算字段使用子查询

mysql> select cust_name, cust_state,        (select count(*)        from orders        where orders.cust_id = customers.cust_id) as orders        from customers        order by cust_name;

 

联结表

创建联结

mysql> select vend_name, prod_name, prod_price        from vendors, products        where vendors.vend_id = products.vend_id        order by vend_name, prod_name;

 

内部联结

mysql> select vend_name, prod_name, prod_price        from vendors inner join products        on vendors.vend_id = products.vend_id;

 

联结多个表

mysql> select cust_name, cust_contact        from customers, orders, orderitems        where customers.cust_id = orders.cust_id        and orderitems.order_num = orders.order_num        and prod_id = 'TNT2';

 

创建高级联结

使用别名

mysql> 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';

 

自然联结

mysql> select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price        from customer 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 = 'FB';

 

外部联结

mysql> select customers.cust_id, orders.order_num        from customers left join orders        on customers.cust_id = orders.cust_id;

 

使用带聚集函数的联结

mysql> 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;

 

组合查询

使用union

mysql> select vend_id, prod_id, prod_price        from products        where prod_price <= 5        union        select vend_id, prod_id, prod_price        from products        where vend_id in (1001,1002);

 

包含重复的行（使用union all命令）

mysql> select vend_id, prod_id, prod_price        from products        where prod_peice <= 5        union all        select vend_id, prod_id, prod_price        from products        where vend_id in (1001,1002);

 

对组合查询结果排序

mysql> select vend_id, prod_id, prod_price        from products        where prod_price <=5        union        select vend_id, prod_id, prod_price        from products        where vend_id in (1001,1002)        order by vend_id, prod_price;

 

全文搜索

仅在MyISAM数据库引擎中支持全文搜索，一般在创建表时启用全文搜索

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;

 

进行全文本搜索

mysql> select note_text        from productnotes        where match(note_text) against('rabbit');

 

使用查询扩展

mysql> select note_text        from productnotes        where match(note_text) against('anvils' with query expansion);

 

布尔文本搜索

mysql> select note_text        from productnotes        where match(note_text) against('heavy' in boolean mode);

 

为匹配包含heavy但不包含任意以rope开始的词的行

mysql> select note_text        from productnotes        where match(note_text) against('heavy -rope*' in boolean mode);

 

插入数据

插入完整的行

mysql> insert into customers(cust_name,        cust_contact,        cust_email,        cust_address,        cust_city,        cust_state,        cust_zip,        cust_country)    values('Pep E. LaPew',        Null,        Null,        '100 Main Street',        'Los Angeles',        'CA',        '90046',        'USA');

 

插入多个行

mysql> insert into customers(cust_name,        cust_address,        cust_city,        cust_state,        cust_zip,        cust_country)    values(        'Pep E. LaPew',        '100 Main Street',        'Logs Angeles',        'CA',        '90046',        'USA'    ),    (        'M. Martian',        '42 Galaxy Way',        'New York',        'NY',        '11213',        'USA'    );

 

插入检索出的数据

mysql> insert into customers(cust_id,        cust_contact,        cust_email,        cust_name,        cust_address,        cust_city,        cust_state,        cust_zip,        cust_country)    select cust_id,        cust_contact,        cust_email,        cust_name,        cust_address,        cust_city,        cust_state,        cust_zip,        cust_country    from custnew;

 

更新和删除数据

更新数据

mysql> update customers        set cust_name = 'The Fudds',        cust_email = 'elmer@fudd.com'        where cust_id = 10005;

 

删除数据

mysql> delete from customers        where cust_id = 10006;

 

创建和操纵表

创建表

mysql> create table customers(        cust_id            int        NOT NULL AUTO_INCREMENT,        cust_name     char(50)      NOT NULL,        cust_address   char(50)     NULL,        cust_city         char(50)     NULL,        cust_state       char(5)       NULL,        char_zip          char(10)     NULL,        char_country   char(50)    NULL,        char_contact    char(50)    NULL,        char_email      char(255)   NULL,        PRIMARY KEY (cust_id)    ) ENGINE=InnoDB;

 

指定默认值

mysql> create table orderitems(        order_num    int                NOT NULL,        order_item    int                NOT NULL,        prod_id         char(10)        NOT NULL,        quantity        int                 NOT NULL   DEFAULT  1,        item_price    decimal(8,2)   NOT NULL,        PRIMARY KEY (order_num, order_item)    ) ENGINE=InnoDB;

 

引擎类型

InnoDB是一个可靠的事务处理引擎。MEMORY在功能上等同于MyISAM，但是数据存储在内存中，速度很快。MyISAM是性能极高的引擎，支持全文搜索，但不支持事务处理。

 

更新表

给表添加一列：

mysql> alter table vendors        add vend_phone char(20);

 

删除刚刚添加的列：

mysql> alter table vendors        drop column vend_phone;

 

ALTER TABLE的一种常见用途是定义外键：

mysql> alter table orderitems        add constraint fk_orderitems_orders        foreign key (order_num) references orders (order_num); mysql> alter table orderitems        add constraint fk_orderitems_products        foreign key (prod_id) references products (prod_id); mysql> alter table orders        add constraint fk_orders_customers        foreign key (cust_id) references customers (cust_id); mysql> alter table products        add constraint fk_products_vendors        foreign key (vend_id) references vendors (vend_id);

 

删除表

mysql> drop table customers2;

 

重命名表

mysql> rename table customers2 to customers;

 

对多个表重命名

mysql> rename table backup_customers to customers,        backup_vendors to vendors,        backup_products to products;

 

使用视图

利用视图简化复杂的联结

mysql> create view productcustomers as        select cust_name, cust_contact, prod_id        from customers, orders, orderitems        where customers.cust_id = orders.cust_id        and orderitems.order_num = orders.order_num;

 

用视图重新格式化检索出的数据

mysql> create view vendorlocations as        select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')')        as vend_title        from vendors        order by vend_name;

 

用视图过滤不想要的数据

mysql> create view customeremaillist as        select cust_id, cust_name, cust_email        from customers        where cust_email is not null;

 

使用视图与计算字段

mysql> create view orderitemsexpanded as        select order_num,        prod_id,        quantity,        item_price,        quantity*item_price as expanded_price        from orderitems;

 

使用存储过程

执行存储过程（所有mysql变量都必须以@开始）

mysql> call productpricing(@pricelow,        @pricehigh,        @priceaverage);

 

创建存储过程

mysql> create procedure productpricing()        begin        select avg(prod_price) as priceaverage        from products;        end;

 

删除存储过程

mysql> drop procedure productpricing;

 

使用参数

mysql> create procedure productpricing(        out pl decimal(8,2),        out ph decimal(8,2),        out pa decimal(8,2)    )    begin        select min(prod_price)        into pl        from products;        select max(prod_price)        into ph        from products;        select avg(prod_price)        into pa        from products;    end;

 

建立智能存储过程

-- Name: ordertotal-- Parameters: onumber = order number--                     taxable = 0 if not taxable, 1 if taxable--                     ototal = order total variable create procedure ordertotal(    in onumber int,    in taxable boolean,    out ototal decimal(8,2)) comment 'Obtain order total, optionally adding tax'begin     -- Declare variable for total    declare total decimal(8,2);    -- Declare tax percentage    declare taxrate int default 6;     -- Get the order total    select sum(item_price*quantity)    from orderitems    where order_num = onumber    into total;     -- Is this taxable?    if taxable then        -- Yes, so add taxrate to the total        select total+(total/100*taxrate) into total;    end if;     -- And finally, save to out variable    select total into ototal; end;

 

taxable=0测试结果

call ordertotal(20005, 0, @total);select @total;

 

taxable=1测试结果

call ordertotal(20005, 1, @total);select @total;

 

检查存储过程

mysql> show create procedure ordertotal;

 

限制过程状态结果

mysql> show procedure status like 'ordertotal';

 

使用游标

创建游标

create procedure processorders()begin    declare ordernumbers cursor    for    select order_num from orders;end;

 

打开和关闭游标

-- 打开游标open ordernumbers;-- 关闭游标close ordernumbers;

 

使用游标

create procedure processorders()begin     -- declare local variables    declare done boolean default 0;    declare o int;    declare t decimal(8,2);     -- declare the cursor    declare ordernumbers cursor    for    select order_num from orders;    -- declare continue handler    declare continue handler for sqlstate '02000' set done=1;     -- create a table to store the results    create table if not exists ordertotals        (order_num int, total decimal(8,2));     -- open the cursor    open ordernumbers;     -- loop through all rows    repeat         -- get order number        fetch ordernumbers into o;         -- get the total for this order        call ordertotal(o, 1, t);         -- insert order and total into ordertotals        insert into ordertotals(order_num, total)        values(o, t);     -- end of loop    until done end repeat;     -- close the cursor    close ordernumbers; end;

 

使用触发器

创建触发器

create trigger newproduct after insert on productsfor each row select 'Product added';

 

删除触发器

drop trigger newproduct;

 

使用触发器

-- insert触发器create trigger neworder after insert on ordersfor each row select new.order_num; -- delete触发器create tigger deleteorder before delete on ordersfor each rowbegin　　insert into archive_orders(order_num, order_date, cust_id)　　value(old.order_num, old.order_date, old.cust_id);end; -- update触发器create trigger updatevendor before update on vendorsfor each row set new.vend_state = upper(new.vend_state);

 

管理实务处理

事务（transaction）指一组SQL语句；

回退（rollback）指撤销指定的SQL语句的过程；

提交（commit）指将未存储的SQL语句结果写入数据库表；

保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退。

使用rollback

select * from ordertotals;start transaction;delete from ordertotals;select * from ordertotals;rollback;select * from ordertotals;

 

使用commit

start transaction;delete from orderitems where order_num = 20010;delete from orders where order_item = 20010;commit;

 

使用保留点

savepoint delete1; rollback to delete1;

 

更改默认的提交行为（设置autocommit为0，mysql不自动提交更改，直到autocommit为设置为1为止）

set autocommit=0;

 

全球化和本地化

字符集为字母和符号的集合；

编码为某个字符集成员的内部表示；

校对为规定字符如何比较的指令。

查看所支持的字符集完整列表

show character set;

 

查看所支持校对的完整列表

show collation;

 

显示数据库所用的字符集和校对

show variables like 'character%';show variables like 'collation%';

 

给表指定字符集和校对

create table mytable(    column1　　int,    column2　　varchar(10)) default character set hebrew    collate hebrew_general_ci;

 

对每个列设置字符集和校对

create table mytable(    column1　　int,    column2　　varchar(10),    columm3　  varchar(10) character set latin1 collate latin1_general_ci) default character set hebrew    collate hebrew_general_ci;

 

校对顺序排序

select * from customersorder by lastname, firstname collate latin1_general_cs;

 

安全管理

不应该在日常的MySQL操作中使用root

获得所有账号列表

mysql> use mysql;mysql> select user from user;

 

创建用户账号

mysql> create user ben identified by 'p@$$w0rd';

 

重命名用户账号

mysql> rename user ben to bforta;

 

删除用户账号

mysql> drop user bforta;

 

查看用户账号权限

mysql> show grants for bforta;

 

给用户账号授予权限

mysql> grant select on crashcourse.* to bforta;

 

撤销用户权限

mysql> revoke select on crashcourse.* from bforta;

 

更改用户口令

set password for bforta = password('n3w p@$$w0rd');

 

设置自己的口令

set password = password('n3w p@$$w0rd');

 

数据库维护

备份数据

• 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件
• 使用命令行实用程序mysqlhotcoy从一个数据库复制所有数据
• 使用backup table或select into outfile转储所有数据到某个外部文件

数据库维护

• analyze table用来检查表键是否正确

mysql> analyze table orders;

 

• check table用来针对许多问题对表进行检查

mysql> check table orders, orderitems;

 

• 如果从一个表中删除大量数据，应该使用optimize table来回收所用的空间，从而优化表的性能

查看日志文件

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。通常名为hostname.err
• 查询日志。它记录所有mysql活动，在诊断问题时非常有用。通常名为hostname.log
• 二进制日志。它记录更新过数据的所有语句。通常名为hostname.bin
• 缓慢查询日志。它记录执行缓慢的任何查询。通常名为hostname-slow.log

改善性能

查看当前设置

mysql> show variables;mysql> show status;

 

显示所有活动进程

mysql> show processlist;

 

• 如果某个任务执行缓慢，则所有的请求都会执行缓慢，可以用kill命令终结该进程。
• 总是有不止一种方法编写同一条select语句，应该试验联结、并、子查询等，找出最佳的方法。
• 使用explain语句让mysql解释它如何执行一条select语句。
• 一般来说，存储过程执行比一条条地执行其中的各条mysql语句快。
• 应该总是使用正确的数据类型。
• 决不要检索比需求还要多的数据。换言之，不要用select *（除非你真的需要每个列）。
• 有的操作（包括insert）支持一个可选的delayed关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
• 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括fulltext索引），然后在导入完成后再重建它们。
• 必须索引数据库以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的select语句以找出重复的where和order by子句。如果一个简单的where子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
• 你的select语句中有一系列复杂的or条件吗？通过使用多条select语句和连接它们的union语句，你能看到极大的性能改进。
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
• like很慢。一般来说，最好是使用fulltext而不是like。
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
• 最重要的规则就是，每条规则在某些条件下都会被打破。