mysql安装cust_MySQL安装与操作总结

淩亂°似流年 2022-10-28 03:41 132阅读 0赞

安装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 localhost

Enter 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 byvend\_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) asordertotalfromorderitemsgroup byorder\_numhaving sum(quantity\*item\_price) >= 50

order by ordertotal;

使用子查询

利用子查询进行过滤

mysql> selectcust\_name, cust\_contactfromcustomerswhere cust\_id in (selectcust\_idfromorderswhere order\_num in (selectorder\_numfromorderitemswhere prod\_id = 'TNT2'));

作为计算字段使用子查询

mysql> selectcust\_name, cust\_state,

(select count(\*)fromorderswhere orders.cust\_id = customers.cust\_id) asordersfromcustomersorder by cust\_name;

联结表

创建联结

mysql> selectvend\_name, prod\_name, prod\_pricefromvendors, productswhere vendors.vend\_id =products.vend\_idorder by vend\_name, prod\_name;

内部联结

mysql> selectvend\_name, prod\_name, prod\_pricefrom vendors inner joinproductson vendors.vend\_id = products.vend\_id;

联结多个表

mysql> selectcust\_name, cust\_contactfromcustomers, orders, orderitemswhere customers.cust\_id =orders.cust\_idand orderitems.order\_num =orders.order\_numand prod\_id = 'TNT2';

创建高级联结

使用别名

mysql> selectcust\_name, cust\_contactfrom customers as c, orders as o, orderitems asoiwhere c.cust\_id =o.cust\_idand oi.order\_num =o.order\_numand prod\_id = 'TNT2';

自然联结

mysql> select c.\*, o.order\_num, o.order\_date, oi.prod\_id, oi.quantity, oi.item\_pricefrom customer as c, orders as o, orderitems asoiwhere c.cust\_id =o.cust\_idand oi.order\_num =o.order\_numand prod\_id = 'FB';

外部联结

mysql> selectcustomers.cust\_id, orders.order\_numfrom customers left joinorderson customers.cust\_id = orders.cust\_id;

使用带聚集函数的联结

mysql> selectcustomers.cust\_name, customers.cust\_id,count(orders.order\_num) asnum\_ordfrom customers inner joinorderson customers.cust\_id =orders.cust\_idgroup by customers.cust\_id;

组合查询

使用union

mysql> selectvend\_id, prod\_id, prod\_pricefromproductswhere prod\_price <= 5

union

selectvend\_id, prod\_id, prod\_pricefromproductswhere vend\_id in (1001,1002);

包含重复的行(使用union all命令)

mysql> selectvend\_id, prod\_id, prod\_pricefromproductswhere prod\_peice <= 5

union all

selectvend\_id, prod\_id, prod\_pricefromproductswhere vend\_id in (1001,1002);

对组合查询结果排序

mysql> selectvend\_id, prod\_id, prod\_pricefromproductswhere prod\_price <=5

union

selectvend\_id, prod\_id, prod\_pricefromproductswhere vend\_id in (1001,1002)order by vend\_id, prod\_price;

全文搜索

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

CREATE TABLEproductnotes

(

note\_idint NOT NULLAUTO\_INCREMENT,

prod\_idchar(10) NOT NULL,

note\_datedatetime NOT NULL,

note\_texttext NULL,PRIMARY KEY(note\_id),

FULLTEXT(note\_text)

) ENGINE=MyISAM;

进行全文本搜索

mysql> selectnote\_textfromproductnoteswhere match(note\_text) against('rabbit');

使用查询扩展

mysql> selectnote\_textfromproductnoteswhere match(note\_text) against('anvils' with query expansion);

布尔文本搜索

mysql> selectnote\_textfromproductnoteswhere match(note\_text) against('heavy' in boolean mode);

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

mysql> selectnote\_textfromproductnoteswhere match(note\_text) against('heavy -rope\*' in boolean mode);

插入数据

插入完整的行

mysql> insert intocustomers(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 intocustomers(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 intocustomers(cust\_id,

cust\_contact,

cust\_email,

cust\_name,

cust\_address,

cust\_city,

cust\_state,

cust\_zip,

cust\_country)selectcust\_id,

cust\_contact,

cust\_email,

cust\_name,

cust\_address,

cust\_city,

cust\_state,

cust\_zip,

cust\_countryfrom custnew;

更新和删除数据

更新数据

mysql> updatecustomersset cust\_name = 'The Fudds',

cust\_email= 'elmer@fudd.com'

where cust\_id = 10005;

删除数据

mysql> delete fromcustomerswhere cust\_id = 10006;

创建和操纵表

创建表

mysql> create tablecustomers(

cust\_idint NOT NULLAUTO\_INCREMENT,

cust\_namechar(50) NOT NULL,

cust\_addresschar(50) NULL,

cust\_citychar(50) NULL,

cust\_statechar(5) NULL,

char\_zipchar(10) NULL,

char\_countrychar(50) NULL,

char\_contactchar(50) NULL,

char\_emailchar(255) NULL,PRIMARY KEY(cust\_id)

) ENGINE=InnoDB;

指定默认值

mysql> create tableorderitems(

order\_numint NOT NULL,

order\_itemint NOT NULL,

prod\_idchar(10) NOT NULL,

quantityint NOT NULL DEFAULT 1,

item\_pricedecimal(8,2) NOT NULL,PRIMARY KEY(order\_num, order\_item)

) ENGINE=InnoDB;

引擎类型

InnoDB是一个可靠的事务处理引擎。

MEMORY在功能上等同于MyISAM，但是数据存储在内存中，速度很快。

MyISAM是性能极高的引擎，支持全文搜索，但不支持事务处理。

更新表

给表添加一列：

mysql> alter tablevendorsadd vend\_phone char(20);

删除刚刚添加的列：

mysql> alter tablevendorsdrop column vend\_phone;

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

mysql> alter tableorderitemsadd constraintfk\_orderitems\_ordersforeign key (order\_num) referencesorders (order\_num);

mysql> alter tableorderitemsadd constraintfk\_orderitems\_productsforeign key (prod\_id) referencesproducts (prod\_id);

mysql> alter tableordersadd constraintfk\_orders\_customersforeign key (cust\_id) referencescustomers (cust\_id);

mysql> alter tableproductsadd constraintfk\_products\_vendorsforeign key (vend\_id) references vendors (vend\_id);

删除表

mysql> drop table customers2;

重命名表

mysql> rename table customers2 to customers;

对多个表重命名

mysql> rename table backup\_customers tocustomers,

backup\_vendorstovendors,

backup\_productsto products;

使用视图

利用视图简化复杂的联结

mysql> create view productcustomers as

selectcust\_name, cust\_contact, prod\_idfromcustomers, orders, orderitemswhere customers.cust\_id =orders.cust\_idand orderitems.order\_num = orders.order\_num;

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

mysql> create view vendorlocations as

select concat(rtrim(vend\_name), '(', rtrim(vend\_country), ')')asvend\_titlefromvendorsorder by vend\_name;

用视图过滤不想要的数据

mysql> create view customeremaillist as

selectcust\_id, cust\_name, cust\_emailfromcustomerswhere cust\_email is not null;

使用视图与计算字段

mysql> create view orderitemsexpanded as

selectorder\_num,

prod\_id,

quantity,

item\_price,

quantity\*item\_price asexpanded\_pricefrom orderitems;

使用存储过程

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

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

创建存储过程

mysql> create procedureproductpricing()begin

select avg(prod\_price) aspriceaveragefromproducts;end;

删除存储过程

mysql> drop procedure productpricing;

使用参数

mysql> create procedureproductpricing(

out pldecimal(8,2),

out phdecimal(8,2),

out padecimal(8,2)

)begin

select min(prod\_price)intoplfromproducts;select max(prod\_price)intophfromproducts;select avg(prod\_price)intopafromproducts;end;

建立智能存储过程

\--Name: ordertotal--Parameters: onumber = order number--taxable = 0 if not taxable, 1 if taxable--ototal = order total variable

create procedureordertotal(in onumber int,intaxable boolean,

out ototaldecimal(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)fromorderitemswhere order\_num =onumberintototal;--Is this taxable?

if taxable then

\--Yes, so add taxrate to the total

select total+(total/100\*taxrate) intototal;end if;--And finally, save to out variable

select total intoototal;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 procedureprocessorders()begin

declare ordernumbers cursor

for

select order\_num fromorders;end;

打开和关闭游标

\--打开游标

openordernumbers;--关闭游标

close ordernumbers;

使用游标

create procedureprocessorders()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 fromorders;--declare continue handler

declare continue handler for sqlstate '02000' set done=1;--create a table to store the results

create table if not existsordertotals

(order\_numint, total decimal(8,2));--open the cursor

openordernumbers;--loop through all rows

repeat--get order number

fetch ordernumbers intoo;--get the total for this order

call ordertotal(o, 1, t);--insert order and total into ordertotals

insert intoordertotals(order\_num, total)values(o, t);--end of loop

until done endrepeat;--close the cursor

closeordernumbers;end;

使用触发器

创建触发器

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

删除触发器

drop trigger newproduct;

使用触发器

\--insert触发器

create trigger neworder after insert onordersfor each row selectnew.order\_num;--delete触发器

create tigger deleteorder before delete onordersforeach rowbegin

insert intoarchive\_orders(order\_num, order\_date, cust\_id)

value(old.order\_num, old.order\_date, old.cust\_id);end;--update触发器

create trigger updatevendor before update onvendorsfor each row set new.vend\_state = upper(new.vend\_state);

管理实务处理

事务(transaction)指一组SQL语句；

回退(rollback)指撤销指定的SQL语句的过程；

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

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

使用rollback

select \* fromordertotals;

starttransaction;delete fromordertotals;select \* fromordertotals;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 variableslike 'collation%';

给表指定字符集和校对

create tablemytable

(

column1int,

column2varchar(10)

)default character sethebrew

collate hebrew\_general\_ci;

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

create tablemytable

(

column1int,

column2varchar(10),

columm3varchar(10) character setlatin1 collate latin1\_general\_ci

)default character sethebrew

collate hebrew\_general\_ci;

校对顺序排序

select \* fromcustomersorder by lastname, firstname collate latin1\_general\_cs;

安全管理

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

获得所有账号列表

mysql> usemysql;

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。

数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。

最重要的规则就是，每条规则在某些条件下都会被打破。

mysql安装cust_MySQL安装与操作总结

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL总结(一)数据库介绍与安装(卸载)

相关 mysql 安装与连接_MySQL安装与连接

相关 MySQL下载与安装

相关 mysql安装cust_MySQL安装与操作总结

相关 mysql安装与基本操作

相关 ubuntu安装与问题总结

相关 ElasticSearch安装与操作

相关 mysql主从安装配置操作

相关 mysql安装简单易操作

相关 mysql下载与安装