时间：2021-07-01 10:21:17 帮助过：8人阅读
From: http://www.cnblogs.com/edward2013/p/5313724.html
添加mysql源
| 1 |
# rpm -Uvh http://repo.mysql.com//mysql57-community-release-el7-7.noarch.rpm
|
安装mysql
| 1 |
# yum -y install mysql-community-server
|
启动mysql并设置为开机自启动服务
| 1 2 |
# chkconfig mysqld on
# service mysqld start
|
检查mysql服务状态
| 1 |
# service mysqld status
|
第一次启动mysql，会在日志文件中生成root用户的一个随机密码，使用下面命令查看该密码
| 1 |
# grep ‘temporary password‘ /var/log/mysqld.log
|
修改root用户密码
| 1 2 3 4 |
# mysql -u root -p -h localhost
Enter password:
mysql> ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘Mysql-2016‘;
|
创建数据库（数据库名为：crashcourse）
| 1 |
mysql> create database crashcourse;
|
使用crashcourse数据库
| 1 |
mysql> use crashcourse;
|
执行sql脚本（使用source命令）
| 1 2 |
mysql> source /root/MySQLCrashCourse/create.sql;
mysql> source /root/MySQLCrashCourse/populate.sql;
|
查看可用数据库的列表
| 1 |
mysql> show databases;
|
查看当前数据库内可用表的列表
| 1 |
mysql> show tables;
|
显示表列（表名：customers）
| 1 |
mysql> show columns from customers;
|
显示服务器错误或警告消息
| 1 2 |
mysql> show errors;
mysql> show warnings;
|
检索单个列
| 1 |
mysql> select prod_name from products;
|
检索多个列
| 1 |
mysql> select prod_id, prod_name, prod_price from products;
|
检索所有列（除非你确实需要表中的每个列，否则最好别使用*通配符，因为会降低应用程序的性能）
| 1 |
mysql> select * from products;
|
检索不同的行（使用distinct关键字）
| 1 |
mysql> select distinct vend_id from products;
|
限制结果
| 1 2 3 4 |
返回不多于5行
mysql> select prod_name from products limit 5;
检索的开始行和行数
mysql> select prod_name from products limit 5,5;
|
使用完全限定的表名
| 1 |
mysql> select products.prod_name from crashcourse.products;
|
排序数据
| 1 |
mysql> select prod_name from products order by prod_name;
|
按多个列排序
| 1 |
mysql> select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
|
指定排序方向（使用desc关键字，desc为倒序，默认是asc，正序）
| 1 |
mysql> select prod_id, prod_price, prod_name from products order by prod_price desc;
|
使用order by和limit的组合
| 1 |
mysql> select prod_price from products order by prod_price desc limit 1;
|
检索单个值
| 1 |
mysql> select prod_name, prod_price from products where prod_name = ‘fuses‘;
|
不匹配检查
| 1 |
mysql> select vend_id, prod_name from products where vend_id != 1003;
|
范围值检查
| 1 |
mysql> select prod_name, prod_price from products where prod_price between 5 and 10;
|
空值检查
| 1 |
mysql> select prod_name from products where prod_price is null;
|
and操作符
| 1 |
mysql> select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10;
|
or操作符
| 1 |
mysql> select prod_name, prod_price from products where vend_id = 1003 or prod_id = 1003;
|
计算次序
| 1 |
mysql> select prod_name, prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
|
in操作符
| 1 |
mysql> select prod_name, prod_price from products where vend_id in (1002,1003) order by prod_name;
|
not操作符
| 1 |
mysql> select prod_name, prod_price from products where vend_id not in (1002,1003) order by prod_name;
|
百分号（%）通配符（匹配0,1或多个字符）
| 1 |
mysql> select prod_id, prod_name from products where prod_name like ‘jet%‘;
|
下划线（_）通配符（匹配1个字符）
| 1 |
mysql> select prod_id, prod_name from products where prod_name like ‘_ ton anvil‘;
|
基本字符匹配
| 1 |
mysql> select prod_name from products where prod_name regexp ‘.000‘ order by prod_name;
|
进行or匹配
| 1 |
mysql> select prod_name from products where prod_name regexp ‘1000|2000‘ order by prod_name;
|
匹配范围
| 1 |
mysql> select prod_name from products where prod_name regexp ‘[1-5] Ton‘ order by prod_name;
|
匹配特殊字符（使用\\进行转义）
| 1 |
mysql> select vend_name from vendors where vend_name regexp ‘\\.‘ order by vend_name;
|
匹配字符类
| 1 |
mysql> select prod_name from products where prod_name regexp ‘[[:digit:]]{4}‘ order by prod_name;
|
匹配多个实例
| 1 |
mysql> select prod_name from products where prod_name regexp ‘\\([0-9] sticks?\\)‘ order by prod_name;
|
定位符（^, $, [[:<:]], [[:>:]]）
| 1 |
mysql> select prod_name from products where prod_name regexp ‘^[0-9\\.]‘ order by prod_name;
|
拼接字段
| 1 |
mysql> select concat(vend_name, ‘ (‘, vend_country, ‘)‘) from vendors order by vend_name;
|
使用别名
| 1 |
mysql> select concat(rtrim(vend_name), ‘ (‘, rtrim(vend_country), ‘)‘) as vend_title from vendors order by vend_name;
|
执行算术计算
| 1 |
mysql> select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;
|
文本处理函数
| 1 2 |
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‘);
|
日期和时间处理函数
| 1 2 |
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()函数
| 1 |
mysql> select avg(prod_price) as avg_price from products;
|
count()函数
| 1 |
mysql> select count(*) as num_cust from customers;
|
max()函数
| 1 |
mysql> select max(prod_price) as max_price from products;
|
min()函数
| 1 |
mysql> select min(prod_price) as min_price from products;
|
sum()函数
| 1 2 |
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;
|
聚合不同的值
| 1 |
mysql> select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
|
组合聚集函数
| 1 |
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;
|
创建分组
| 1 |
mysql> select vend_id, count(*) as num_prods from products group by vend_id;
|
过滤分组（使用having关键字）
| 1 2 |
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;
|
分组和排序
| 1 2 3 4 5 |
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;
|
利用子查询进行过滤
| 1 2 3 4 5 6 7 |
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‘));
|
作为计算字段使用子查询
| 1 2 3 4 5 6 |
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;
|
创建联结
| 1 2 3 4 |
mysql> select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;
|
内部联结
| 1 2 3 |
mysql> select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;
|
联结多个表
| 1 2 3 4 5 |
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‘;
|
使用别名
| 1 2 3 4 5 |
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‘;
|
自然联结
| 1 2 3 4 5 |
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‘;
|
外部联结
| 1 2 3 |
mysql> select customers.cust_id, orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;
|
使用带聚集函数的联结
| 1 2 3 4 5 |
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
| 1 2 3 4 5 6 7 |
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命令）
| 1 2 3 4 5 6 7 |
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);
|
对组合查询结果排序
| 1 2 3 4 5 6 7 8 |
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数据库引擎中支持全文搜索，一般在创建表时启用全文搜索
| 1 2 3 4 5 6 7 8 9 |
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;
|
进行全文本搜索
| 1 2 3 |
mysql> select note_text
from productnotes
where match(note_text) against(‘rabbit‘);
|
使用查询扩展
| 1 2 3 |
mysql> select note_text
from productnotes
where match(note_text) against(‘anvils‘ with query expansion);
|
布尔文本搜索
| 1 2 3 |
mysql> select note_text
from productnotes
where match(note_text) against(‘heavy‘ in boolean mode);
|
为匹配包含heavy但不包含任意以rope开始的词的行
| 1 2 3 |
mysql> select note_text
from productnotes
where match(note_text) against(‘heavy -rope*‘ in boolean mode);
|
插入完整的行
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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‘);
|
插入多个行
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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‘
);
|
插入检索出的数据
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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;
|
更新数据
| 1 2 3 4 |
mysql> update customers
set cust_name = ‘The Fudds‘,
cust_email = ‘elmer@fudd.com‘
where cust_id = 10005;
|
删除数据
| 1 2 |
mysql> delete from customers
where cust_id = 10006;
|
创建表
| 1 2 3 4 5 6 7 8 9 10 11 12 |
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;
|
指定默认值
| 1 2 3 4 5 6 7 8 |
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;
|
引擎类型
| 1 2 3 |
InnoDB是一个可靠的事务处理引擎。
MEMORY在功能上等同于MyISAM，但是数据存储在内存中，速度很快。
MyISAM是性能极高的引擎，支持全文搜索，但不支持事务处理。
|
更新表
给表添加一列：
| 1 2 |
mysql> alter table vendors
add vend_phone char(20);
|
删除刚刚添加的列：
| 1 2 |
mysql> alter table vendors
drop column vend_phone;
|
ALTER TABLE的一种常见用途是定义外键：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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);
|
删除表
| 1 |
mysql> drop table customers2;
|
重命名表
| 1 |
mysql> rename table customers2 to customers;
|
对多个表重命名
| 1 2 3 |
mysql> rename table backup_customers to customers,
backup_vendors to vendors,
backup_products to products;
|
利用视图简化复杂的联结
| 1 2 3 4 5 |
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;
|
用视图重新格式化检索出的数据
| 1 2 3 4 5 |
mysql> create view vendorlocations as
select concat(rtrim(vend_name), ‘ (‘, rtrim(vend_country), ‘)‘)
as vend_title
from vendors
order by vend_name;
|
用视图过滤不想要的数据
| 1 2 3 4 |
mysql> create view customeremaillist as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;
|
使用视图与计算字段
| 1 2 3 4 5 6 7 |
mysql> create view orderitemsexpanded as
select order_num,
prod_id,
quantity,
item_price,
quantity*item_price as expanded_price
from orderitems;
|
执行存储过程（所有mysql变量都必须以@开始）
| 1 2 3 |
mysql> call productpricing(@pricelow,
@pricehigh,
@priceaverage);
|
创建存储过程
| 1 2 3 4 5 |
mysql> create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
|
删除存储过程
| 1 |
mysql> drop procedure productpricing;
|
使用参数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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;
|
建立智能存储过程