一稳懒、mysql配置

1.簡介

用于管理文件的一個軟件

• 服務(wù)端軟件

  • SOCKET 服務(wù)端

  • 本地文件處理

  • 解析指令【SQL語句】

• 客戶端軟件（各種各樣）

  • SOCKET 客戶端
  • 發(fā)送指令
  • 解析指令【SQL語句】

2.用戶管理

默認(rèn)用戶為root

2.1管理用戶

2.1.1 創(chuàng)建用戶

create user "username"@'ip'identified by 'passwd';

ip可使用通用匹配符，即可用%表示

例如：

create user "username"@'10.0.0.1'identified by 'passwd'; #只限該IP登陸用戶

create user "username"@'10.0.0.%'identified by 'passwd'; #只限該IP段登陸用戶

create user "username"@'%'identified by 'passwd'; #任意IP登陸用戶

2.1.2 刪除用戶

drop user '用戶名'@'ip';

2.1.3 修改用戶

rename user '用戶名'@'ip' to '新的用戶名'@'ip'

2.1.4 修改密碼

set password for '用戶名'@'IP地址'= Password('新密碼')

用戶權(quán)限相關(guān)數(shù)據(jù)保存在mysql數(shù)據(jù)庫的user表中慢味，所以也可以直接對其進(jìn)行操作（不建議）

2.2 授權(quán)管理

2.2.1 用戶授權(quán)

語法：grant 權(quán)限條件 on 可訪問對象數(shù)據(jù)庫或數(shù)據(jù)表 to 用戶 @ 允許登陸的條件

grant select ,insert,update on db1.t1 to 'username'@'%' #給允許任意登陸的用戶username賦予查詢场梆、插入、修改數(shù)據(jù)庫db1里的t1表

grant all privileges on to 'username'@'%' #給允許任意登陸的用戶username賦予所有權(quán)限

2.2.2 查看授權(quán)

語法：show grant for '用戶名'@'允許登陸條件'

show grant for 'chancey'@'%' #查看chancey的權(quán)限

2.2.3 刪除權(quán)限

語法：revoke grant on '可訪問對象數(shù)據(jù)庫或數(shù)據(jù)表' from '用戶名'@'允許登陸的條件'

revoke grant on 'db1.t1' from 'chancey'@'%'

2.3 生效設(shè)置

flush privileges

二纯路、基本操作

1. 數(shù)據(jù)庫

show databases; #查看所有數(shù)據(jù)庫
create database dbname engine=innodb default charset=utf8; #創(chuàng)建數(shù)據(jù)庫,指定引擎和編碼
drop database dbname; #刪除數(shù)據(jù)庫
use dbname; #選中數(shù)據(jù)庫或油，只有選中之后才能進(jìn)行庫里面的所有操作

在mysql中，引擎只有innodb支持事物回滾驰唬，myisam速度更快顶岸，但不支持事物回滾

2. 數(shù)據(jù)表

2.1 查看表

show tables; #查看所有表

show create table demo \G; #查看該表如何創(chuàng)建

desc demo; #查看表結(jié)構(gòu)

2.2 創(chuàng)建表

create table 表名(
   列名  類型  約束 是否可以為空,
   列名  類型  約束 是否可以為空
)ENGINE=innodb default CHARSET=utf8; #指定數(shù)據(jù)表編碼及其引擎

2.2.1 數(shù)據(jù)類型

數(shù)值

整數(shù)
int #所有整數(shù)
tinyint #表示范圍，可為負(fù)數(shù)
bigint #相對tinyint范圍大些

浮點數(shù)
float #浮點數(shù)
double #小數(shù)叫编，精度不足
decimal #浮點型拗小，按照字符串存儲时迫，但是精準(zhǔn)
num decimal(10,5) #10表示總位數(shù)栅表，5為小數(shù)點后邊的位數(shù)

字符串

char #固定長度
varchar #自動長度
text #文本

時間

date #日期（YYYY-MM-DD）
time #時間(HH-MM-SS)
year #年份(YYYY)
datetime #時間(YYYY-MM-DD HH:MM:SS)

二進(jìn)制（不常用）

tinyblob
blob
mediumblob
longblob

其他

create table td2(sex enum('man','woman')); #枚舉骗露，就是在該選項選出一個

2.2.2 約束條件

auto_increment #自增長，只能有一個霞篡，如果有自增長世蔗，則必須有索引，即primary key
primary key #主鍵寇损，可約束（不能重復(fù)且不能為空）凸郑，可加速查找（即索引作用）
not null #標(biāo)識該字段不能為空
unique key #標(biāo)識該字段唯一
default #為該字段設(shè)置默認(rèn)值

2.3 插入數(shù)據(jù)

insert into db1(id,name) values(1,'chancey'),(2,'waller') #指定列插入裳食，可一次性插入多條數(shù)據(jù)
insert into db1(id,name) select id,name from db2; #復(fù)制其它表

2.4 刪除數(shù)據(jù)

drop table t1 #刪除整張表（不完全刪除）

truncate from t1 #清空表內(nèi)容（更快速,且ID也隨之清空）

delete from t1 #清空表內(nèi)容

delete from t1 where id>6; #指定刪除數(shù)據(jù)（>,<,=,!=）可以用or和and

2.5 修改數(shù)據(jù)

update t1 set age=18; #批量修改數(shù)據(jù)
update t1 set age=18 where age=17; #指定條件修改

2.5 查詢數(shù)據(jù)

2.5.1 簡單查詢

select * from t1; #查詢整張表所有內(nèi)容
select count(name) from t1; #統(tǒng)計字段出現(xiàn)的次數(shù)

2.5.1 條件查詢

select name from t1 where age=18; #指定單一條件查詢（所有判斷語句皆可用）
例如：
select * from t1 where age=18 and name="chancey";
select * from t1 where age=18 or age=19;

#臨時別名：
select name as "姓名" from t1 where age=18; #只加一個
select name as "姓名" , age as "年齡" from t1; #多個別名

#查詢多個條件
select name from t1 where age in (18,21,25); #查詢枚舉列
select name from t1 where age not in (18,21,25); #查詢除枚舉列

2.5.2 范圍查詢

#查詢范圍區(qū)間
select name from t1 where age between 18 and 22; #查詢范圍內(nèi)

select * from t1 where id in (select id from t2); #條件為查詢的結(jié)果

2.5.3 通配符

select * from t1 where name like "cha%"; #查詢name以cha開頭的所有數(shù)據(jù)
select * from t1 where name like "cha_"; #查詢name以cha開頭且后面只有一個字符

2.5.4 限制查詢

select * from t1 limit 10; #只查看前邊10條記錄

select * from t1 limit 1,16; #從第1條開始取16條矛市，并非以16為結(jié)尾
以上SQL等同于以下
select 8 from t1 limit 16 offset 1; #從1開始，向后取16條

2.5.5 排序查詢

select * from t1 order by id desc; #按照ID從大到小排序
select * from t1 order by id asc; #按照ID從小到大排序

select * from t1 order by id asc,age desc; #以id從小到大排序诲祸，如果id一樣浊吏，則以age從大到小 

3.自增列

一張表只能有一個主鍵而昨，但一個主鍵可以多列。

create table demo(
    id int,
    age int,
    primary key (id,age)
)engine=innodb default charset=utf8;

3.1 自增列初始值

在一張表刪除一些數(shù)據(jù)之后找田。再次插入數(shù)據(jù)歌憨，就會發(fā)現(xiàn)，即使一張表里只有一條數(shù)據(jù)墩衙，它的ID也不為1

alter table demo auto_increment=1 #設(shè)置自增長從1開始

3.2 自增列步長

mysql自增長與sqlserver不同务嫡，SqlServer是基于基礎(chǔ)表設(shè)置，也就是說固定值

3.2.1 基于會話級別

也就是說在當(dāng)前登錄的會話中生效漆改，會話結(jié)束則步長清除

show session variables like 'auto_inc%'; #查看當(dāng)前步長
set session auto_increment=2 #設(shè)置當(dāng)前會話步長為2
set session auto_increment_offset=2 #設(shè)置起始值

3.2.2 基于全局變量

一次修改心铃，永久使用，當(dāng)前會話修改之后挫剑，其他會話也是

show global variables like 'auto_inc%' #查看全局變量
set global auto_increment=200 #設(shè)置步長為200
set global auto_increment_offset=2 #設(shè)置起始值

4. 外鍵

舉例說明：

現(xiàn)公司員工有3人去扣，2個部門，則需要創(chuàng)建3張表關(guān)聯(lián)信息

詳情表樊破、
詳情表(userinfo)

? 部門編號（depart）

外鍵：用來關(guān)聯(lián)兩張或者多張表的字段

create table depart(
    id int auto_increment primary key,
    title char(15)
)engine=innodb charset=utf8;

create table userinfo(
    uid bigint auto_increment primary key,
    name char(10),
    depart_id int,
    constraint fk_user_deapr foreign key (depart_id) references depart(id) #創(chuàng)建外鍵
)engine=innodb charset=utf8;

constraint fk_user_deapr foreign key ("depart_id") references depart("id")

其中constraint聲明外鍵愉棱，fk_user_deapr為外鍵的別名，foreign key為指定兩個不同表里的字段哲戚，references銜接兩個字段

外鍵的意思也就是說奔滑，userinfo中的depart_id只能是depart中的id，類似枚舉顺少，但和枚舉不同档押。

準(zhǔn)備如下5張表

[圖片上傳失敗...(image-7e6894-1562760641749)]

class和student使用class_id和cid關(guān)聯(lián)；

teacher和course使用tid和teach_id關(guān)聯(lián)祈纯；

score 2個外鍵（student.sid-corse.student_id令宿、course.cid-scors_id）

關(guān)系如下：

[圖片上傳失敗...(image-bcdbbf-1562760641749)]

創(chuàng)建這5張表

set names utf8; #設(shè)置編碼問題
set foreign_key_checks = 0; #取消外鍵約束

#創(chuàng)建class表
create table class (
  cid int(11) auto_increment primary key,
  caption varchar(32) not null
) engine=innodb auto_increment=5 default charset=utf8;

#創(chuàng)建teacher表
create table teacher (
  tid int(11) auto_increment primary key,
  tname varchar(32) not null
) engine=innodb auto_increment=6 default charset=utf8;

#創(chuàng)建course表
create table course (
  cid int(11) not null auto_increment primary key,
  cname varchar(32) not null,
  teacher_id int(11) not null,
  key fk_course_teacher (teach_id),
  constraint fk_course_teacher foreign key (teacher_id) references teacher (tid)
) engine=innodb auto_increment=5 default charset=utf8;

#創(chuàng)建student表
create table student (
  sid int(11) not null auto_increment,
  gender char(1) not null,
  class_id int(11) not null,
  sname varchar(32) not null,
  primary key (sid),
  key fk_class (class_id),
  constraint fk_class foreign key (class_id) references class (cid)
) engine=innodb default charset=utf8;

#創(chuàng)建score表
create table score (
  sid int(11) auto_increment primary key,
  student_id int(11) not null,
  course_id int(11) not null,
  number int(11) not null,
  constraint fk_score_course foreign key (course_id) references course (cid),
  constraint fk_score_student foreign key (student_id) references student (sid)
) engine=innodb default charset=utf8;

插入數(shù)據(jù)

insert into `class` values ('1', '三年一班'), ('2', '三年二班'), ('3', '一年三班'), ('4', '三年四班');
insert into `teacher` values ('1', '波多老師'), ('2', '小澤老師'), ('3', '瀧澤老師'), ('4', '蒼井老師'), ('5', '麻生老師');
insert into `course` values ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術(shù)', '2');
insert into `student`(`sid`, `gender`, `class_id`, `sname`) values (1, '男', 1, '素心'),(17, '女', 2, '執(zhí)念如花'),(18, '男', 2, '青燈古酒'),(19, '男', 3, '愚人歡'),(20, '女', 1, '入戲太深'),(21, '女', 3, '一尾流鶯'),(22, '男', 2, '迷離'),(23, '女', 3, '亞澤'),(24, '男', 1, '幽靈'),(25, '男', 2, '歸隱'),(26, '女', 1, '琴女'),(27, '男', 3, '深海處男'),(28, '男', 1, '逍遙散人'),(29, '女', 2, '少女尼斯'),(30, '女', 1, '原味口紅'),(31, '男', 1, '鬼棺');
insert into `score`(`student_id`, `course_id`, `number`) values (16, 2, 57),(5, 2, 13),(14, 1, 46),(15, 3, 57),(4, 2, 60),(1, 3, 68),(9, 1, 83),(7, 4, 97),(6, 4, 72),(12, 2, 14),(10, 4, 85),(2, 4, 25),(8, 1, 70),(13, 4, 27),(11, 2, 93),(3, 3, 28);

set foreign_key_checks = 1;

5. 索引

加速查找，唯一索引

唯一索引

create table demo(
    id int,
    number int,
    li int,
    unique uql (number) #設(shè)置唯一索引
    unique uql (number,li) #設(shè)置聯(lián)合索引
);

以上uql為唯一索引的名稱腕窥，設(shè)置之后不允許重復(fù)的值添加粒没，但可以為空；

聯(lián)合索引為完全相同的時候不能重復(fù)

6. 表關(guān)系

表關(guān)系分三種：

• 一對一
• 一對多
• 多對多

6.1 一對一

[圖片上傳失敗...(image-69aa19-1562760641749)]

create table userinfo(
    id int auto_increment primary key,
    name varchar(32) not null,
    zoo_id int not null
)engine=innodb default charset=utf8;

INSERT INTO `userinfo` VALUES (1, '蒼井空', 1);
INSERT INTO `userinfo` VALUES (2, '波多野結(jié)衣', 10);
INSERT INTO `userinfo` VALUES (3, '小澤瑪利亞', 4);
INSERT INTO `userinfo` VALUES (4, '天海翼', 8);
INSERT INTO `userinfo` VALUES (5, '大橋未久', 2);
INSERT INTO `userinfo` VALUES (6, '麻生希', 7);
INSERT INTO `userinfo` VALUES (7, '美竹玲', 5);
INSERT INTO `userinfo` VALUES (8, '瀧澤蘿拉', 9);
INSERT INTO `userinfo` VALUES (9, '櫻井莉亞', 3);
INSERT INTO `userinfo` VALUES (10, '桃谷繪里香', 6);

create table zoos(
    id int auto_increment primary key,
    zoo varchar(32),
    constraint fk_zoos_id foreign key (id) references userinfo(zoo_id) 
)engine=innodb charset=utf8;

INSERT INTO `zoos` VALUES (1, '鼠');
INSERT INTO `zoos` VALUES (2, '牛');
INSERT INTO `zoos` VALUES (3, '虎');
INSERT INTO `zoos` VALUES (4, '兔');
INSERT INTO `zoos` VALUES (5, '龍');
INSERT INTO `zoos` VALUES (6, '蛇');
INSERT INTO `zoos` VALUES (7, '馬');
INSERT INTO `zoos` VALUES (8, '羊');
INSERT INTO `zoos` VALUES (9, '猴');
INSERT INTO `zoos` VALUES (10, '雞');
INSERT INTO `zoos` VALUES (11, '狗');
INSERT INTO `zoos` VALUES (12, '豬');

6.2 一對多

[圖片上傳失敗...(image-2214c5-1562760641749)]

create table staff(
    id int auto_increment primary key,
    name varchar(32)
)engine=innodb default charset=utf8;

insert into `department` values (1, '教師'),(2, '公交'),(3, '護(hù)士');

create table department(
    id int auto_increment primary key,
    dname varchar(32),
    depart_id int,
    constraint fk_depart_id foreign key (depart_id) references staff(id)
)engine=innodb default charset=utf8;

insert into `staff` values (1, '蒼井空', 26, 1),(2, '波多野結(jié)衣', 25, 2),(3, '小澤瑪利亞', 18, 1),(4, '天海翼', 24, 3),(5, '大橋未久', 30, 3),(6, '麻生希', 27, 1),(7, '美竹玲', 14, 2),(8, '瀧澤蘿拉', 33, 1),(9, '櫻井莉亞', 47, 2),(10, '桃谷繪里香', 56, 1);

6.3 多對多

[圖片上傳失敗...(image-305ea0-1562760641749)]

create table `course` (
  `id` int(11) not null auto_increment,
  `cname` varchar(32) not null,
  primary key (`id`)
) engine=innodb auto_increment=5 default charset=utf8;

create table `teacher` (
  `id` int(11) not null auto_increment,
  `tname` varchar(32) not null,
  `course_id` int(11) not null,
  primary key (`id`),
  key `fk_teacher_id` (`course_id`),
  constraint `fk_teacher_id` foreign key (`course_id`) references `course` (`id`)
) engine=innodb auto_increment=21 default charset=utf8;

INSERT INTO `course` (`id`, `cname`) VALUES (1, '軟件開發(fā)');
INSERT INTO `course` (`id`, `cname`) VALUES (2, '計算機(jī)應(yīng)用');
INSERT INTO `course` (`id`, `cname`) VALUES (3, '移動互聯(lián)');

INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (1, '蒼井空', 1);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (2, '波多野結(jié)衣', 2);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (3, '小澤瑪利亞', 1);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (4, '天海翼', 3);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (5, '大橋未久', 3);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (6, '麻生希', 1);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (7, '美竹玲', 2);
INSERT INTO `teacher` (`id`, `tname`, `course_id`) VALUES (8, '瀧澤蘿拉', 1);

7. 高級查詢

摻雜各種條件限制進(jìn)行查詢

7.1 聯(lián)合查詢

 select * from staff,department where department.id = staff.depart_id; #指定對應(yīng)的外鍵
 
 select * from staff left join department on staff.depart_id = department.id;
 #顯示左邊的全部信息簇爆，即使空也顯示null
 select * from staff right join department on staff.depart_id = department.id;
 #顯示右邊的全部信息癞松，即使空也顯示null
 
  select * from staff inner join department on staff.depart_id = department.id;
  #如果有null的值，則整行隱藏

連表查詢?nèi)绻龅絻蓮埍淼淖侄蜗嗤肭瑒t在select后加上其表名响蓉，即表名.字段名

7.2 分組查詢

分組查詢的關(guān)鍵字是group by

其關(guān)鍵是在查詢到相同的條件時，取指定條件的數(shù)據(jù)（max哨毁、min）

select max(id),name from t1 group by age; #以age聚合枫甲，遇見相同的值則取id最大的那個數(shù)據(jù)
select count(id),name from t1 group by age; #統(tǒng)計相同name出現(xiàn)的ID次數(shù)

如果對于聚合查詢需要進(jìn)行二次篩選，則必須使用having

select count(id),name from t1 group by age having count(id) > 1;

8. 備份

mysqldump -u root 數(shù)據(jù)庫名 > 名稱.sql -p #備份
mysqldump -u root 數(shù)據(jù)庫名 < 名稱.sql -p #恢復(fù)，首先要創(chuàng)建數(shù)據(jù)庫