整理关于MySQL和MariaDB(安装部署,数据库操作,SQL语句)
2019年8月9日 星期五
15:10
一、MySQL:(实操环境:CentOS6.5安装MySQL5.6)
引用原文地址:
https://www.cnblogs.com/renjidong/p/7047396.html
linux CentOS6.5 yum安装mysql 5.6
1.新开的云服务器,需要检测系统是否自带安装mysql
# yum list installed | grep mysql
2.如果发现有系统自带mysql,果断这么干
# yum -y remove mysql-libs.x86_64
3.随便在你存放文件的目录下执行,这里解释一下,由于这个mysql的yum源服务器在国外,所以下载速度会比较慢,还好mysql5.6只有79M大,而mysql5.7就有182M了,所以这是我不想安装mysql5.7的原因
# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
4.接着执行这句,解释一下,这个rpm还不是mysql的安装文件,只是两个yum源文件,执行后,在/etc/yum.repos.d/ 这个目录下多出mysql-community-source.repo和mysql-community.repo
# rpm -ivh mysql-community-release-el6-5.noarch.rpm
5.这个时候,可以用yum repolist mysql这个命令查看一下是否已经有mysql可安装文件
#yum repolist all | grep mysql
6.安装mysql 服务器命令(一路yes):
# yum install mysql-community-server
7.安装成功后
# service mysqld start
8.由于mysql刚刚安装完的时候,mysql的root用户的密码默认是空的,所以我们需要及时用mysql的root用户登录(第一次回车键,不用输入密码),并修改密码
# mysql -u root
# use mysql;
# update user set password=PASSWORD("这里输入root用户密码") where User='root';
# flush privileges;
9.查看mysql是否自启动,并且设置开启自启动命令
# chkconfig --list | grep mysqld
# chkconfig mysqld on
10.mysql安全设置(系统会一路问你几个问题,看不懂复制之后翻译,基本上一路yes):
# mysql_secure_installation
二、MariaDB:(CentOS7.x试验环境)
1、安装MariaDB
#yum -y group install mariadb mariadb-client
2、开机启动MariaDB
#systemctl enable mariadb
3、查看MariaDB状态
#systemctl status mariadb
#netstat -lant | grep 3306
4、初始化mariadb
#mysql_secure_installation
1)输入root(mariaDB)密码:(默认直接回车)
2)是否设置root密码?[Y/n]
3)移除匿名账户?[Y/n]
4)不允许root远程登陆?[Y/n]
5)移除测试数据库及访问?[Y/n]
6)重新加载特权表?[Y/n]
5、确认root是否可以登陆
#mysel -u root
ERROR 1045 (28000): Access denied for user
'root'@'localhost' (using password: NO)
验证test数据库是否被移除
mysql -u root -p $PassWord
验证root从远程无法登陆至本地
#mysql -u root -p $PassWord -h mariadb_server
6、MariaDB的警告日志位置
/var/log/mariadb/mariadb.log
可以用tail -f 动态查看日志
数据库操作常用SQL指令:
一、数据库类操作:(操作对象是数据库)
1、显示本地数据库
#mysql -u root -p $PassWord
>show databases;
2、创建数据库
>create database $DatabaseName;
>show databases;
3、使用指定数据库
>use $DatabaseName;
查看数据库的表
>show tables;
查看指定表中的信息
>describe customers;
显示广泛的服务器状态信息
>show status
显示创建指定数据库(表)的MariaDB语句
>show create database(table)
显示授予用户的安全权限
>show grants
显示服务器错误或警告信息
>show errors(warnings)
show语句列表
>help show
4、数据库备份与恢复
备份
>mysqldump -u root -p $DataBaseName > ./$DataBaseName.dump
>musqldump -u root -p -all-databases > ./all.dump
恢复
>mysql -u root -p $DataBaseName < ./$DataBaseName.dump
>mysql -u root -p < ./all.dump
5、数据库检查与维护
ANALYZE TABLE用来检查表的键是否正确:
>analyze table $TableName;
CHECK TABLE用来检查表的各种问题:
>check table $TableName;
REPAIR TABLE用来修复表:
>repair table $TableName;
二、用户类操作:(操作对象是用户)
1、新创建数据库用户账户(create)
>create user $UserName $PassWord;
2、删除数据库用户(drop)
>drop user $UserName;
3、重命名数据库用户名(rename)
>rename user $OldUserName to $NewUserName;
4、查询所有数据库用户的列表(从用户表"user"中查询用户列,列名为"user")
>use mysql;
>select user form user;
5、查看用户的权限(show)
>show grants for $UserName;
6、对用户进行数据库访问授权(grant)
>grant select on $DataBaseName.* to $UserName;
多次授权(多个grant可以合并)
grant select insert on $DataBaseName.* to $UserName;
7、撤销权限(revoke)
>revoke select on $DataBaseName .* from $UserName;
权限
权限 | 说明 |
ALL | 除了grant option之外所有权限 |
ALTER | 使用alter table |
ALTER ROUTINE | 使用alter procedure和drop procedure |
CREATE | 使用create table |
CREATE TEMPORAY TABLE | 使用create temporary table |
CREATE ROUTING | 使用create procedure |
CREATE USER | 使用create user,drop user,rename user和 revoke,privileges |
CREATE VIEW | 使用create view |
DELETE | 使用delete |
DROP | 使用drop table |
EXECUTE | 使用call和存储过程 |
FILE | 使用select into outfile和load data infile |
GRANT OPTION | 使用grant和revoke |
INDEX | 使用create index和drop index |
INSERT | 使用insert |
LOCK TABLES | 使用lock tables |
PROCESS | 使用show full processlist |
RELORD | 使用flush |
RELICATION CLIENT | 访问本地服务器 |
RELICATION SLAVE | 有复制从属使用 |
SELECT | 使用select |
SHOW DATABASES | 使用show databases |
SHOW VIEW | 使用show create view |
SHUTDOWN | 使用mysqladmin shutdomn |
SUPER | 使用change master,kill,logs,purge,master和set globle,还允许mysqladmin测试登录 |
UPDATE | 使用Update |
USAGE | 没有访问权限 |
三、表类操作:(操作对象是数据库表)
1、创建表
>create table $TableName ($ColumnName1 $DataType,$ColumnName2 $DataType,PrimaryKey ($ColumnName))
engine=$EngineType(Aria)
2、更新表
>alter table $TableName;
3、重命名表
>rename table $OldTableName to $NewTableName;
4、删除表
>drop table $TableName;
字符串数据类型
数据类型 | 描述 |
char | 定长字符串,长度为1-255 |
enum | 接收一个已经定义的不大于64KB字符串的集合 |
longtext | 同text ,最大尺寸为4G |
mediumtext | 同text,最大尺寸为16KB |
set | 接收0个或多个已经定义的不大于64个字符串的集合 |
text | 可变长字符串,最大尺寸为64KB |
tinytext | 同text ,最大尺寸为255B |
varchar | 同char,不过仅存储文本,最大尺寸 |
数值数据类型
数据类型 | 描述 |
bit | bit字段,从1-64位 |
bigint | 整型值,支持-923372036854775808~923372036854775807 |
boolean(bool) | 布尔标志,0或者1,主要用来打开或者关闭标签 |
decimal(dec) | 不同精度的浮点数值 |
double | 双精度浮点值 |
float | 单精度浮点值 |
int | 整型值,支持-2147483648~2147483647 |
mediumint | 整型值,支持-8388608~8388608 |
smallint | 整值型,支持-32768~32767 |
tinyint | 整值型,支持-128~127 |
时间和日期数据类型
数据类型 | 描述 |
date | 日期从1000-01-01到9999-12-31,格式是YYYY-MM-DD |
datetime | 日期和时间的结合 |
timestamp | 与datetime功能相同(不过变化范围更小) |
time | 时间格式是HH:MM:SS |
year | 2位或者4位的年,2位数支持70(1970)到69(2069)年,4位数支持从1901- 2155年 |
二进制数据类型
数据类型 | 描述 |
blob | 最大长度64KB |
mediumblob | 最大长度16MB |
longblob | 最大长度4GB |
tinyblob | 最大长度255B |
四、数据表类操作
1、insert(插入单独的完整行、部分行、多行、查询结果等)
>insert into $TableName values('abc','def');
#在value子句中指定将要存储到表中每一列,并且需要为每一列提供值的信息,若无值则需要使用NULL。列必须以其在表中定义的次序依次填充
一般情况下依赖于指定列次序的SQL语句并不安全,一般选择在括号中将列名显式表示,当插入新行时,数据库对应value 清单来匹配列清单中的每一项,并且不用按照列在实际表中出现的顺序书写
2、插入多列:(列名及列的次序要一致)
>insert into $TableName ($ColumnName1,$ColumnName2,$ColumnName3...)
values ('$Number1','$Number2','$Nmuber3'...);
或者:
>insert into $TableName ($ColumnName1,$ColumnName2,$ColumnName3...)
values ('$Number1','Number2','$Number3'...),
values ('$Number1','Number2','$Number3'...);
3、插入检索到的数据
将$TableName2检索到的$ColumnName1,$ColumnName2,$ColumnName3等数据插入$TableName1中
>insert into $TableName1 ($ColumnName1,$ColumnName2,$ColumnName3...)
Select $ColumnNmae1,$ColumnName2,$ColumnName3 from $TableName2;
4、修改(更新)和删除数据(update)
>update $TableName set $ColumnName1 = 'ColumnName' where $ColumnName2 = $Number;
5、删除数据(delete)
>delete from $TableName where $ColumnName = 'ColumnName';
五、查询类操作
1、select查询(从一个或多个表中检索信息,检索表中数据需指定检索信息以及检索位置)
检索单独的列
>select $ColumnName from $TableName;
检索多个列
>select $ColumnName1,$ColumnName2,$CloumnName3... from $TableName;
检索所有列
>select * from $TableName;
2、distinct关键字(应用于所有的列,而不仅是其后的一列)
从表中检索所有关键字,忽略重复值
>select distinct $Distinct from $TableName;
3、limit子句(从select语句中所有匹配的行返回特定值)
从表中查询并从第0行开始显示5行
>select $ColumnName from $TableName limit 5;
从表中查询并从第5行开始显示5行
>select $ColumnName from $TableName limit 5,5;
从表中查询并从第3行开始返回4行
>select $CloumnName from $TableName limit 3,4;或者
>select $CloumnName from $TableName limit 4 offset 3;(MariaDB)
4、order by子句(对select语句检索出的数据进行排序,默认正序排序(A-Z))
检索单列并正序排序
>select $ColumnName from $TableName order by $ColumnName;
检索单列并逆序排序
>select $ColumnName from $TableName order by $ColumnName desc;
检索多列并正序排序
>select $ColumnName1,$ColumnName2,ColumnName3...from $TableName order by $ColumnName2,$ColumnName3;
(先以$ColumnName2为准进行正序排序,$ColumnName2数值相同的,以$ColumnName3为准再进行正序排序)
或者
>select $ColumnName1,$ColumnName2,$ColumnName3...from $TableName order by 2,3;
(指定select语句中指定列的编号)
检索多列并逆序排序
>select $ColumnName1,$ColumnName2,$ColumnName3...from $TableName order by $ColumnName2 desc,$ColumnName3;
(此SQL语句的desc只作用于$ColumnName2以逆序排序,对于$ColumnName2数值相同的,以$ColumnName3为准进行正序排序)
检索特定列的最大值
>select $ColumnName from $TableName order by $ColumnName desc limit 1;
#order by子句和limit子句结合使用可以筛选特定列的最值,
#指定order by子句时要确保跟在from子句后;若使用limit, limit必须跟在order by之后
5、where子句(指定select语句的搜索条件并过滤,在from子句之后)
支持的操作符:
操作符 | 说明 |
= | 相等 |
<> | 不等 |
! = | 不等 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between | 在两个特定值之间 |
#同时使用order by和where子句,要确保order by子句跟在where子句之后
列出所有$ColumnName2为特定数值$Number的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName2=$Number;
列出所有$ColumnName2为特定数值$Number的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName = '$Number';
列出所有$ColumnName2低于特定数值$Number的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName < '$Number';
列出所有$ColumnName2不同于特定数值$Number的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName2 <>'$Number';
列出所有$ColumnName2处于特定数值$Number1和$Number2之间的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName2 between $Number1 and $Number2;
检查空值
>select $ColumnName1 from $TableName where $ColumnName is NULL;
6、and和or子句(逻辑操作符,用于在where子句中添加条件,连接或者改变子句的关键字)
#and的执行优先级高于or,and所有条件全部满足,or满足其中一个条件
列出所有$ColumnName2为特定数值且$ColumnName3满足特定条件的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName where $ColumnName2 = $Number and $ColumnName3 <= $Number;
列出所有$ColumnName2为特定数值或$ColumnName3满足特定条件的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName where $ColumnName2 = $Number or $ColumnName3 <= $Number;
and、or和()的巧妙应用
列出所有$ColumnName2为特定数值$Number1或者$Number2,且$COlumnName3为特定条件的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName where ($ColumnName2 = $Number1 or $ColumnName2 = $Number2) and $ColumnName3 >= $Number3;
7、IN操作符(用来指定一组条件,只要匹配其中任何一个条件即可 )
列出所有$ColumnName2为特定数值$Number1或者$Number2的行$ColumnName1的内容并排序
>select $ColumnName1,$ColumnName2 from $TableName where $Number in ($Number1,$Number2) order by $ColumnName1;
或者
>select $CloumnName1,$ColumnName2 from $TableName where $ColumnName2 = $Number1 or $ColumnName2 = $Number2 order by $ColumnName1;
8、NOT操作符(用来否定条件的where子句中的一个关键字)
列出所有$ColumnName2为非特定数值$Number1和$Number2的行$ColumnName1的内容并排序
>select $ColumnName1,$ColumnName2 from $TableName where $Number not in ($Number1,$Number2) order by $ColumnName1;
通配符(用来匹配值的某个部分的特殊字符)
通配符 | 说明 |
% | 任意数量的字符(包括0个字符),无法匹配NULL |
_ | 仅匹配一个字符 |
列出所有$ColumnName2为特殊字段"abc"为开头的行$ColumnName1的内容并排序
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName2 like 'abc%' order by $ColumnName1;
列出所有$ColumnName2为特殊字段仅包含一个字符的"_bc"的字段的行$ColumnName1的内容并排序
>select $ColumnName1,$ColumnName2 from $TableName where $ColumnName2 like '_bc' order by $ColumnName1;
9、子查询(查询中又嵌套的查询,实际应用中,一般不要超过2级,从最内层的select语句开始往外处理)
列出所有$ColumnName2为特殊字段"abc"为开头的行$ColumnName1的内容并排序
>select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName1
where $ColumnName4 in (select $ColumnName4 from $TableName2
where $ColumnName5 in (select $ColumnName5 from $TableName3
where $ColumnName2 = 'abc'))
order by $ColumnName1;
子查询创建计算字段
>select $ColumnName1,$ColumnName2,(select count(*) from $TableName2
where $ColumnName1Nubmer = $ColumnNmae2Number) as orders from $TableName1
order by $ColumnName1;
#count(*) 为聚合函数,表示计算表中行的总数
#as 给列赋予别名
10、UNION操作符(联合查询,也适用于对不同的表进行联合查询)
#一个union必须包含两个或两个以上select语句,且每个查询都必须包含相同的列、表达式或者聚合函数
#查询的列数据类型必须是兼容的,但不必是完全相同的类型
#查询结果自动移除任何重复行,如果要返回所有匹配结果,用union all 代替 union
#当使用union联合查询时只可以使用一个order by子句,并且必须出现在最后一个select语句后面
列出所有$ColumnName2为特定数值且$CloumnName3满足特定条件的行$ColumnName1的内容
>select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName1
where $ColumnName2 in ($Number1,$Number2)
union
select $ColumnName1,$ColumnName2,$ColumnName3 from $TableName1
where $ColumnName3 <= $Number3;
11、全文本搜索(Aria引擎)
在创建表的时候,全文本搜索是开启的,也可以在之后再指定
>create table $TableName(
$ColumnName1 int not NULL atto_increment,
$ColumnName2 char(10) not NULL,
$ColumnName3 text NULL,
primary key($ColumnName1),
fulltext($ColumnName1)
)enging=Aria;
#建立索引后,使用match()指定需要搜索的列, against()指定需要使用的搜索表达式。
>select $ColumnName1 from $TableName where match($ColumnName1) against ('$Number');
#传递给match的值必须与fulltext()定义的值一样,若指定了多列,需要将所有指定列出且以正确的顺序;搜索时不区分大小写,除非使用binary
扩展查询(不仅匹配搜索标准的行,还匹配含有搜索标准返回行中出现过的单词的行)
>select $ColumnName1 from $TableName where match($ColumnName) against ('$Number' with query expansion);
12、布尔文本搜索(在没有定义fulltext索引也能使用,可提供匹配的字段、排位提示和表达式分组)
操作符 | 描述 |
+ | 包含,必须出现的单词 |
| 排除,不能出现的单词 |
> | 包含,增加排位值 |
< | 包含,降低排位值 |
() | 将单词分组为子表达式(允许它们被包含、排位、排除,如此作为一个组 |
~ | 对一个单词的排位值取否 |
* | 单词末尾的通配符 |
" " | 定义短语(相对与个别单词的列表,整个短语要么全部包含,要么全部排除) |
列出所有$ColumnName1列中包含"ab",且不包含以"cd"为开头的行
>select $ColumnName1 from $TableName where match ($ColumnName1) against ('ad -cd*' in boolean mode);
列出所有$ColumnName1列中同时包含"ab"和"cd"的行
>select $ColumnName1 from $TableName where match ($ColumnName1) against ('+ab +cd' in boolean mode);
列出所有$ColumnName1列中至少包含"ab"或"cd"的行
>select $ColumnName1 from $TableName where match ($ColumnName1) against ('ab cd' in boolean mode);
列出所有$ColumnName1列中同时包含"ab"和"cd"的行,并提升"ab"排位,降低"cd"排位
>select $ColumnName1 from $TableName where match ($ColumnName1) against ('>ab <cd' in boolean mode);