MySQL安裝
sudo apt-get install mysql-server mysql-client
管理服務(wù)
sudo service mysql start/stop/restart
允許遠(yuǎn)程連接
修改配置文件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
將bind-address=127.0.0.1注釋
用戶賦權(quán)
grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;
重啟數(shù)據(jù)庫
數(shù)據(jù)庫操作
創(chuàng)建數(shù)據(jù)庫
create database DB_Name charset=utf8;
刪除數(shù)據(jù)庫
drop database DB_Name;
切換數(shù)據(jù)庫
use DB_Name;
顯示當(dāng)前使用數(shù)據(jù)庫
select database();
顯示存在的數(shù)據(jù)庫
show databases;
表操作
創(chuàng)建表
create table Table_Name(
id int auto_increment primary key not null,
name varchar(10),
cls_id int,
foreign key(cls_id) references class(id),
index index_id(id(11)),
.........
);
修改表
alter table Table_Name add|change|motify|drop column
alter table students add stu_name varchar(10) not null;
alter table students motify stu_name char(10);
alter table students change stu_name students_name varchar(10);
alter table students drop stu_name;
alter table students add constraint stu_cls foreign key(cls_id) references class(id);
alter table students drop foreign key stu_cls;
表的約束
- 主鍵primary key
- 非空not null
- 惟一unique
- 默認(rèn)default
- 外鍵foreign key
索引
創(chuàng)建索引
普通索引
create index Index_Name on Table_Name(column【(length)】【desc|asc】);
唯一索引
create unique index Index_Name on Table_Name(column【(length)】【desc|asc】);
全文索引
create fulltext index Index_Name on Table_Name(column【(length)】【desc|asc】); --MySQL5.6之后InnnoDB也可以創(chuàng)建全文索引邢隧，全文索引主要是對char、varchar、text做的查詢優(yōu)化
多列索引
create index Index_Name on Table_Name(
column1【(length)】【desc|asc】,
column2【(length)】【desc|asc】
column3【(length)】【desc|asc】
......);
刪除索引
drop index Index_Name on Table_Name;
視圖
創(chuàng)建視圖
create view View_Name as 查詢語句;
查看視圖
select * from View_Name;
show create view View_Name;
刪除視圖
drop view View_Name 【,View_Name2,.....】;
修改視圖
create or replace view View_Name as 查詢語句;
觸發(fā)器
創(chuàng)建觸發(fā)器
create trigger trigger_Name
BEFORE|AFTER trigger_Event
on TABLE_NAME FOR EACH ROW trigger_STMT;
trigger_Event:
- INSERT
- DELETE
- UPDATE
trigger_STMT:
滿足觸發(fā)條件后執(zhí)行的語句
刪除觸發(fā)器
drop trigger Trigger_Name;
數(shù)據(jù)維護(hù)
插入
insert into Table_Name(name,gender) values ('張三',1),('李四',1),('王五',1);
insert into Table_Name values (0,'張三',1),(0,'李四',1),(0,'王五',1);--不選擇插入的列需要全部的列都要寫颖侄，默認(rèn)的列的值如果寫上會被替換奋渔，如果不寫可以用default代表默認(rèn)
刪除
delete from Table_Name where id=1;
truncate table Tabel_Name;--注意截斷表的數(shù)據(jù)無法恢復(fù)张足，但是刪除速度快
更新
update Table_Name
set field1 = value1,
field2 = value2
where CONDITION;
查詢
查詢語句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count;
執(zhí)行順序
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit star,count
聚合
count()
sum()
avg()
max()
min()
關(guān)系
join
inner join
left join
right join
select * from sutdents inner join class on students.cls_id = class.id;
自關(guān)聯(lián)
create table areas(
aid int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
事務(wù)
begin;
commit;
rollback;
數(shù)據(jù)庫備份
備份
mysqldump -uroot p DatabaseName > ~/Desktop/back.sql
恢復(fù)
mysql -uroot –p DatabaseName < ~/Desktop/back.sql
用戶
創(chuàng)建用戶
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
- username：你將創(chuàng)建的用戶名
- host：指定該用戶在哪個主機(jī)上可以登陸笨枯，如果是本地用戶可用localhost蛙讥，如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸锯蛀，可以使用通配符%
- password：該用戶的登陸密碼，密碼可以為空次慢，如果為空則該用戶可以不需要密碼登陸服務(wù)器
刪除用戶
DROP USER 'username'@'host';
賦權(quán)
GRANT privileges ON databasename.tablename TO 'username'@'host'
- privileges：用戶的操作權(quán)限旁涤，如SELECT，INSERT迫像，UPDATE等劈愚，如果要授予所的權(quán)限則使用ALL
- databasename：數(shù)據(jù)庫名
- tablename：表名，如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用表示闻妓，如.*
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;--賦權(quán)給用戶可以給他們賦權(quán)的權(quán)限
撤銷賦權(quán)
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
- privilege, databasename, tablename：同授權(quán)部分
密碼安全
修改密碼
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是當(dāng)前用戶
SET PASSWORD = PASSWORD("newpassword");
