mariadb.org
Download
Download
MariaDB 5.5.59 Stable
mariadb-5.5.59-linux-x86_64.tar.gz 224.5 MB 下載即可(no,thanks)
centos7
yum源就是本機(jī)的春霍，不用設(shè)置
yum groupinstall mariadb mariadb-client
rpm -ql mariadb-server
rpm -ql mariadb 客戶端
ss -ntl
systemctl start mariadb
systemctl enable mariadb
ss -ntlp 顯示程序
cat /usr/lib/systemd/system/mariadb.service 啟動(dòng)服務(wù)的相關(guān)配置
mysql直接進(jìn)入mysql界面
show databases;
use test; 相當(dāng)于cd命令
show tables;
use mysql 這個(gè)不用分號(hào)董栽；
show tables;
select * from user;
desc user; 列出表的結(jié)構(gòu)恩急，看的清楚
select host,user,password from user;
quit 退出數(shù)據(jù)庫(kù)
mysql
select user(); 以什么身份登陸
不操作：mysql -uroot -h192.168.29.127 -u是以什么身份登陸棕诵，-h連接遠(yuǎn)程主機(jī)的IP地址
quit
su -fgq
mysql
select user();
注意區(qū)別mysql的root和Linux的root不是同一個(gè)，windows里數(shù)據(jù)庫(kù)的root名字叫sa
quit
命令行操作：mysql -uxxx 匿名登陸
select user();
show databases;
use mysql 權(quán)限有限
quit
exit
安全：刪除匿名瓤漏，口令登陸
rpm -ql mariadb-server | grep "secure"
cat /usr/bin/mysql_secure_installation 腳本
命令行操作：/usr/bin/mysql_secure_installation 二進(jìn)制文件
y （=回車(chē)鍵）
y
設(shè)置密碼
再輸1次
y 刪除匿名登陸
n root遠(yuǎn)程登陸不禁止
n test數(shù)據(jù)庫(kù)不刪除
y 表上加載權(quán)限
mysql 登陸不上
mysql -uroot -p
輸入密碼
show databases;
select host,user,password from mysql.user;
格式：數(shù)據(jù)庫(kù)名.表名
quit
iptables -vnL
systemctl disable firewalld
systemctl stop firewalld
iptables -vnL
which mysql
rpm -qf /usr/bin/mysql
yum groupinfo mariadb 服務(wù)器包組里有個(gè)包名：mariadb-server
yum groupinfo mariadb-client 客戶端包組里有個(gè)包名：mariadb
cat /etc/my.cnf
ll /var/log/mariadb/mariadb.log
cat /var/log/mariadb/mariadb.log
cat /etc/my.cnf
cat /var/run/mariadb/mariadb.pid
ps aux
cat /var/run/mariadb/mariadb.pid
pstree -p
which mysql
which mysqld_safe
cat /etc/my.cnf
cat /etc/my.cnf.d/
cd /etc/my.cnf.d/
ls
cat client.cnf
cat mysql-clients.cnf
cat server.cnf
centos7
cat /etc/passwd
cd /var/lib/mysql 數(shù)據(jù)庫(kù)的位置
cd mysql
打開(kāi)另一個(gè)終端
mysql -uroot -p
輸入密碼
create database testdb; 創(chuàng)建數(shù)據(jù)庫(kù)
cd /var/lib/mysql 看是否生成了數(shù)據(jù)庫(kù)testdb
rpm -ql mariadb-server 生成的文件
cat /var/log/mariadb/mariadb.log 生成的日志
centos6.9
yum info mysql
yum list mysql 顯示已經(jīng)安裝
準(zhǔn)備一個(gè)新的機(jī)器centos6版本的
rz上傳下載的文件包
getent passwd 看是否有mysql腾夯，沒(méi)有需要?jiǎng)?chuàng)建用戶mysql
useradd -r -d /app/data -s /sbin/nologin -u 36 mysql
ll /app/data/ -d
chown mysql.mysql /app/data/ 文件的權(quán)限更改
tar xvf mariadb-5.5.59-linux-x86_64.tar.gz -C /usr/local/ 指定文件路徑
cd /usr/local/ ;ll
ln -s mariadb-5.5.59-linux-x86_64/ mysql 把mariadb-...的文件夾名稱改為mysql或者軟連接也可以
ll mysql/
chgrp -R mysql mysql/ 更改權(quán)限mysql屬組
cat /etc/my.cnf 其中的datadir路徑不是我們?cè)O(shè)置的/app/data/，所以自己需要重新設(shè)置蔬充。
cd mysql/support-files/;ls 里面的配置文件根據(jù)自己的需要來(lái)復(fù)制蝶俱，生產(chǎn)環(huán)境用大的配置文件，英文字母的意思來(lái)判斷即可
mkdir /etc/mysql
cp my-huge.cnf /etc/mysql/my.cnf
ll my-huge.cnf /etc/mysql/my.cnf
vim /etc/mysql/my.cnf
[mysqld]下面添加信息
datadir = /app/data
innodb_file_per_table = on 創(chuàng)建的每個(gè)表都是獨(dú)立的數(shù)據(jù)庫(kù)
skip_name_resolve = on 禁止主機(jī)名解析饥漫，速度快
創(chuàng)建數(shù)據(jù)庫(kù)
cd /usr/local/mysql;ls
scripts/mysql_install_db --datadir=/app/data --user=mysql
ll /app/data/ 文件生成
cat support-files/mysql.server 是一個(gè)服務(wù)腳本
cp support-files/mysql.server /etc/init.d/mysqld
ll /etc/init.d/mysqld 看是否有執(zhí)行權(quán)限榨呆，有即可
chkconfig --list mysqld 報(bào)錯(cuò)
chkconfig --add mysqld
chkconfig mysqld on
chkconfig --list mysql 出現(xiàn)
touch /var/log/mysqld.log
chown mysql /var/log/mysqld.log
ll /var/log/mysqld.log
service mysqld start 啟動(dòng)成功
ss -ntl 有3306這個(gè)端口
cd bin/;ls
vim /etc/profile.d/mysql.sh 把mysql放到path變量里
export PATH=/usr/local/mysql/bin:$PATH
. /etc/profile.d/mysql.sh
mysql
show databases;
create database fgqdb;
select user();
select host,user,password from mysql.user;
quit
pwd;ls;有mysql_secure_installation
mysql_secure_installation
回車(chē)（=y）
y
密碼
再次輸入密碼
y
n
n
回車(chē)
mysql 連接不上
mysql -uroot -p
輸入密碼
quit
防火墻
iptables -vnL
iptables -F
cat /etc/my.cnf 里面的socket文件路徑
socket = /var/lib/mysql/mysql.sock
cat /etc/mysql/my.cnf 里面的socket文件路徑，后面覆蓋前面的路徑庸队，本機(jī)通信
socket = /tmp/mysql.sock
ll /tmp/mysql.sock
centos6-3
mysql連接上积蜻；
創(chuàng)建賬號(hào)
create user 'fgq'@'192.168.%.%' identified by '123456'; 用戶名@網(wǎng)段identified by密碼
create user 'testuser'@'%' identified by '123456'; 任何網(wǎng)段的都可以連接
create user 'testuser'@'%';
drop user 'feng'@'192.168.%.%';
更改口令
set password for 'fgq'@'192.168.%.%' = password('1234567'); 用戶名@網(wǎng)段 = password('密碼')
drop
授權(quán)
GRANT priv_type,... ON db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
db_name.tb_name:
*.*: 所有庫(kù)的所表
db_name.*: 指定庫(kù)的所有表
db_name.tb_name: 指定庫(kù)的指定表
db_name.routine_name：指定庫(kù)的存儲(chǔ)過(guò)程和函數(shù)
grant select on fgqdb.* to 'testuser'@'192.168.%.%' identified by '123456';
用戶不存在就會(huì)立刻創(chuàng)建并授予權(quán)限，若存在彻消，則僅僅授予權(quán)限浅侨；
grant delete on fgqdb.s2 to 'testuser'@'192.168.%.%' identified by '123456';
grant all on fgqdb.* to 'testuser'@'192.168.%.%' identified by '123456';
可以創(chuàng)建表，刪除表证膨，不能創(chuàng)建數(shù)據(jù)庫(kù)，原因是授權(quán)時(shí)的限制
取消權(quán)限
REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host'
revoke delete on fgqdb.* from 'testuser'@'192.168.%.%';
show grants for 'feng'@'192.168.%.%';
show grants for 'feng'@'192.168.%.%'\G;
show grants for current_user();
show grants for current_user()\G;
centos7上連接centos6-3的mysql
mysql -ufgq -h192.168.29.132 -p 可以連接上鼓黔，但是權(quán)限限制
show databases; 只能顯示很少的數(shù)據(jù)庫(kù)
mysql -utestuser -h192.168.29.132 -p
use fgqdb;
select * from s1;
delete from s2; 可以刪除
delete from s1; 不能刪除央勒，沒(méi)有權(quán)限
quit
msyql 再次登陸后
delete from s1; 可以刪除
create table s3 (id int unsigned primary key,name varchar(20),age tinyint unsigned,gender enum('m','f'));
drop table s2;
drop 刪除整個(gè)表不见；
delete 刪除表中的記錄，表結(jié)構(gòu)還在崔步；
對(duì)于不能夠或不能及時(shí)重讀授權(quán)表的命令稳吮，可手動(dòng)讓MariaDB的服務(wù)進(jìn) 程重讀授權(quán)表：
mysql> flush privileges;
centos6-3
mysql -uroot -p 輸入密碼，連接數(shù)據(jù)庫(kù)
help或者\(yùn)h mysql的命令查看
status或者\(yùn)s 狀態(tài)查看
select version(); 查看版本
select "haha",1+2;
select "haha" as name,1+2 as result; 只是顯示的時(shí)候更加好看點(diǎn)
SQL語(yǔ)句：
DDL: Data Defination Language 數(shù)據(jù)定義語(yǔ)言——數(shù)據(jù)庫(kù)井濒，表灶似，索引
CREATE, DROP, ALTER
DML: Data Manipulation Language 數(shù)據(jù)操作語(yǔ)言——針對(duì)表的
INSERT, DELETE, UPDATE 增刪改
DCL：Data Control Language 數(shù)據(jù)控制語(yǔ)言——數(shù)據(jù)庫(kù)，表瑞你，用戶——授權(quán)限
GRANT, REVOKE 賦予酪惭，撤銷
DQL：Data Query Language
SELECT
SQL標(biāo)準(zhǔn)：
/*注釋內(nèi)容*/ 多行注釋
-- 注釋內(nèi)容 單行注釋，注意有空格
MySQL注釋： #
create schema fengdb; schema=database
show schemas; = show databases;
help
help create
help create database 查看用法
create table students (id int unsigned not null primary key,name varchar(20) not null,age tinyint unsigned );
注意int和unsigned要放在一起者甲，unsigned修飾前面的int春感；
primary key 就是非空的，所以不用加not null虏缸；
desc students;
create table bjtechan (id int unsigned not null primary key,name varchar(20) not null );
create table zztechan (id int unsigned not null primary key,name varchar(20) not null );
show tables; 進(jìn)入了fgqdb數(shù)據(jù)庫(kù)鲫懒，才這樣操作；
show tables from fgqdb; 未進(jìn)入fgqdb數(shù)據(jù)庫(kù)中
drop table zztechan;
help create table 查看創(chuàng)建表的命令
show table status like "students"\G 查看表的狀態(tài)
show indexes from fgqdb.students;
show indexes from fgqdb.students\G; 進(jìn)入到fgqdb中刽辙，就不用加fgqdb了窥岩；
alter table students rename s1; 改表的名字
alter table s1 add phone varchar(11) after name; 在name后面增加字段phone
alter table s1 add gender enum("m","f"); 增加字段gender，只能取值m或者f宰缤；
alter table s1 change id sid int unsigned not null; 改表中字段的名字：把id更改為sid颂翼，id原本就是primary key，所以此處此處更改不加即可撵溃；
alter table s1 add unique key(name); name變?yōu)槲ㄒ绘I
show indexes from s1;
help create index
quit
cd /app/data;ls ;cd fgqdb/;ls
DML
into 可以省略
insert into s1 (sid,name,age) values (1,"flq",20); 如果只有這三個(gè)字段可以省略
insert into s1 values (1,"flq",20);
insert into s1 (sid,name) values (3,"fyg"); 不知道age疚鲤，缺少一個(gè)字段，要寫(xiě)具體缘挑；
insert into zztechan values (1,"huimian");
insert into zztechan values (2,"helaotiao");
select * from zztechan;
update zztechan set name="luomo" where id=3;
delete from zztechan where id=4; 刪除較慢集歇，一條條刪，有日志语淘；
truncate table fgqdb.bjtechan; 刪除數(shù)據(jù)诲宇，表結(jié)構(gòu)還在，快速刪除惶翻，清空表姑蓝；
select相當(dāng)于print打印
select * from zztechan order by name; 排序按照name
select * from zztechan order by name desc; 倒序
select id as 編號(hào),name as 名稱 from zztechan order by name; as可以省略
select id 編號(hào),name 名稱 from zztechan order by name;
create table fztechan (id int unsigned not null primary key,name varchar(20)); 此處的name可以為空
insert into fztechan (id,name) values (1,"shaobing");
insert into fztechan (id) values (3);
select * from fztechan where name is not null; 如果name在創(chuàng)建表的時(shí)候?yàn)榉强眨筒椴怀鰜?lái)的吕粗；
desc fztechan; 可以看見(jiàn)表的字段是否可以為空纺荧；
select * from fztechan where name is null;
create table s2 select * from s1; 根據(jù)s1表來(lái)創(chuàng)建s2表
快速創(chuàng)建表：
create table s3 select sid,name from s1; 選擇舊表中的一部分字段來(lái)創(chuàng)建新表
desc s3；
select * from s3;
快速創(chuàng)建表：
insert into students select sid,name,age from s1;
注意students表中的數(shù)據(jù)和s1表中的數(shù)據(jù)要互相對(duì)應(yīng)，且類型相同才可以這樣操作宙暇；students表必須存在输枯；
select * from students where name like "%f%"; name中有f字母的記錄
select * from zztechan where id in (1,4,7); id是1，4或者7的記錄占贫，如果沒(méi)有則不顯示桃熄；
select * from zztechan where id>=6 ; id大于等于6
select * from zztechan where id>=2 and id<=6; id大于等于2，小于等于6
select * from zztechan where id between 2 and 6; 與上面的一條相同
select * from zztechan where id != 2; 不等于2
select * from zztechan where id <> 2; 不等于2
select * from zztechan where name != "huimian"; name不是huimian的記錄
select * from zztechan where name != ""; name非空的記錄
insert into students values (4,"lf",20),(8,"zdg",50);
insert into students (id,name) values (6,"ftl"),(9,"ftx");
等值連接
select * from zztechan,bjtechan where zztechan.id=bjtechan.id;
將兩張表的數(shù)據(jù)按指定字段進(jìn)行連接
select z.name as zztcname,b.name as bjtcname from zztechan as z,bjtechan as b where z.id=b.id;
內(nèi)連接
select bj.name from bjtechan as bj,zztechan as zz where bj.name=zz.name;
select bj.name from bjtechan as bj inner join zztechan as zz on bj.name=zz.name;
內(nèi)連接型奥，上面兩個(gè)式子的結(jié)果相同瞳收； 逗號(hào)=inner join，where=on厢汹；
select zz.id,zz.name,bj.name from zztechan as zz inner join bjtechan as bj on zz.id=bj.id ;
select zz.id,zz.name,bj.name from zztechan as zz inner join bjtechan as bj on zz.id=bj.id and bj.id=3;
product表：id螟深，name；
price表：id坑匠，price血崭；
求name與price的對(duì)應(yīng)表；
select p.id,p.name,r.price from product as p inner join price as r on p.id=r.id and r.id=1;
外連接
left
select zz.id,zz.name,bj.name from zztechan as zz left outer join bjtechan as bj on zz.id=bj.id ;
select zz.id,zz.name,bj.name from zztechan as zz left outer join bjtechan as bj on zz.name=bj.name ;
right
select zz.id,zz.name,bj.name from zztechan as zz right outer join bjtechan as bj on zz.id=bj.id ;
select zz.id,zz.name,bj.name from zztechan as zz right outer join bjtechan as bj on zz.name=bj.name ;
縱向合并
select * from zztechan union select * from bjtechan;
求平均年齡
select avg(age) from s1;
求年齡大于平均年齡的人
select name,age from s1 where age > (select avg(age) from s1);
老師年齡=學(xué)生年齡
select name,age from students where age in (select age from teachers);
學(xué)習(xí)課程為第1厘灼，2夹纫，4或者第7門(mén)課的同學(xué)的名字；
select s.name,scores.courseid from students as s left outer join scores on scores.stuid = s.stuid where scores.courseid in (1,2,4,7);
