安装
[root@localhost ~]# yum install mariadb-server mariadb -y
# 自动监听3306端口
[root@localhost ~]# systemctl start mariadb.service
[root@localhost ~]# systemctl enable mariadb.service
[root@localhost ~]# netstat -lntup
安全初始化 修改root用户的密码,新版本可能无效
[root@localhost mysql]# mysql_secure_installation
启动客户端
# MySQL安装后默认有一个root用户名,并且没有密码,是数据库管理工具的超级管理员,可以操纵所有的库和表。用户名密码存放在默认数据库mysql的user表中。
[root@localhost ~]# mysql
数据库的基本操作
# 查看数据库
MariaDB [(none)]> show databases;
# 创建数据库
MariaDB [(none)]> create database wordpress charset utf8; # charset utf8 指定字符集
# 删除数据库
MariaDB [(none)]> drop database wordpress;
# 切换数据库
MariaDB [(none)]> use wordpress;
# 查询当前是在哪个库
MariaDB [(none)]> select database();
# 创建表
MariaDB [wordpress]> create table t02_111
-> (
-> id int(10),
-> name varchar(15)
-> );
# 查看表
MariaDB [wordpress]> show tables;
# 查看表结构
MariaDB [wordpress]> desc user; # desc 表名
# 删除表
MariaDB [wordpress]> drop table t02_111;
# 退出root用户
exit
# 本机切换用户登陆
[root@localhost ~]# mysql -u wordpress -p123456
字符集
create table t1 (id int, title char(10)) default charset = utf8mb4;
create database db1 charset utf8mb4;
默认数据文件的从存储位置
/var/lib/mysql
limit
# 从第一条开始,往下查询两条。
select User, Password,Host from user limit 1,2;
数据类型
int float char(定长) text enum(枚举) date boolen varchar(10)(可变长度,最大10个字节,效率低)
float(5,2)浮点类型总共5位,小数点后有两位 999.99
char(10) 十个字节。
create table table1(id int, name char(10), price float(5,2));
约束条件
not null 不能为空
default 默认值,default 100默认值为100
unique 唯一
auto_increment 自增
primary key 主键not null + unique ,自带自增属性
unsigned 只能存正整数
create table tabel_t (id int primary key, name char(10) not null);
插入数据
# 插入全部数据
insert into table1 value (1, '信息', 43.39);
insert into table1 values (2, "信息2", 33.23), (3, "信息3", 33.23);
# 插入指定数据
insert into table1(id, price) value (1, 23.36);
删除数据
delete from t1 where id = 6;
delete from t1 where id = 6 and name = 'lisi';
delete from t1 where id = 6 or name = 'lisi';
修改数据
update t1 set password = '123' where name = "张三";
修改表字段长度
alter table t1 modify name char(20);
查看创表语句
show create table t1;
增加字段
alter table t1 add age int(3);
alter table t1 add idcard char(20) FIRST; # 将idcard字段放到第一个位置
alter table t1 add salary float(4,2) AFTER idcard; # 将salary字段放到idcard字段后面。
删除字段
alter table t1 drop age;
创建用户
create user 'wang@192.168.233.%' identified by '123';
修改用户密码
password()是一个方法,将参数加密
set password for wang@'192.168.233.%' = password('456');
给用户授权
grant all on db1.table1 to wang@'192.168.233.%';
创建用户并授权,mysql和mariadb默认不允许root用户远程登陆
# grant 操作(增删改查,all是所有操作) on 库名.表名(*.*表示所有的库所有的表) to 用户名@'允许远程登陆的IP地址(用户名@'%'表示在任意地址都可以登陆)' indentified by '密码';
MariaDB [mysql]> grant all on wordpress.* to wordpress@'192.168.239.%' identified by '123456';
# to wordpress@'192.168.239.%':wordpress用户可以通过192.168.239网段链接。
# identified by '123456':密码是123456。
# wordpress.*:可以操作wordpress库中的所有表。
# 该方式是授权远程登陆,本机无法登陆,需要再授权一次本机登录
[root@localhost ~]# hostname # 查看本机名
localhost.localdomain
MariaDB [mysql]> grant all on wordpress.* to wordpress@'localhost.localdomain' identified by '123456';
| 192.168.239.% | wordpress | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost.localdomain | wordpress | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
删除用户
drop user wang@'192.168.233.%';
查看用户权限
show grants for wang@'192.168.233.%';
回收用户权限
# 回收wang用户 db1.table1 的 查询权限
revoke select on db1.table1 from wang@'192.168.233.%';
show grants for wang@'192.168.233.%';
查询
导入数据,先将sql文件放到服务器中再导入
[root@localhost ~]# mysql < world.sql
模糊查询
select * from city where district like 'shan';
多条件查询
# 查shanxi 和 hebei 的所有
select * from city where district='shanxi' or district = 'hebei';
# 查询山西河北的城市中人口大于100万的 加括号是因为and的优先级比or高 and并且
select * from city where (district='shanxi' or district = 'hebei') and population > 1000000;
排序
# 按population 升序
select * from city where district='shanxi' order by population ;
# 按population 降序
MariaDB [world]> select * from city where district = 'Jilin' order by population desc;
between
MariaDB [world]> select * from city where CountryCode = "chn" and population between 89000 and 90000;
内置函数
MariaDB [world]> select count(name) as 中国城市总数 from city where countrycode = 'chn';
+--------------------+
| 中国城市总数 |
+--------------------+
| 363 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [world]> select sum(population) from city where countrycode='chn';
+-----------------+
| sum(population) |
+-----------------+
| 175953614 |
+-----------------+
# 把多行合并成一行
MariaDB [world]> select group_concat(name) from city where countrycode = 'chn' and district = 'hebei';
+----------------------------------------------------------------------------------------------------------------+
| group_concat(name) |
+----------------------------------------------------------------------------------------------------------------+
| Shijiazhuang,Tangshan,Handan,Zhangjiakou,Baoding,Qinhuangdao,Xingtai,Chengde,Cangzhou,Langfang,Renqiu,Hengshui |
+----------------------------------------------------------------------------------------------------------------+
# 把多列合并成一列
MariaDB [world]> select concat(name,'#',countrycode,'#',district) from city where countrycode = 'chn' and district = 'hebei';
+-------------------------------------------+
| concat(name,'#',countrycode,'#',district) |
+-------------------------------------------+
| Shijiazhuang#CHN#Hebei |
| Tangshan#CHN#Hebei |
| Handan#CHN#Hebei |
| Zhangjiakou#CHN#Hebei |
| Baoding#CHN#Hebei |
| Qinhuangdao#CHN#Hebei |
| Xingtai#CHN#Hebei |
| Chengde#CHN#Hebei |
| Cangzhou#CHN#Hebei |
| Langfang#CHN#Hebei |
| Renqiu#CHN#Hebei |
| Hengshui#CHN#Hebei |
+-------------------------------------------+