一扒俯、基本操作

對(duì)數(shù)據(jù)庫(kù)以及表的一些基本操作

1-1.關(guān)于數(shù)據(jù)庫(kù)

//創(chuàng)建數(shù)據(jù)庫(kù)
create database h_test;        
//查看數(shù)據(jù)庫(kù)
show databases;  
//查看數(shù)據(jù)庫(kù)信息    
show create database h_test;
//修改數(shù)據(jù)庫(kù)的編碼，可使用上一條語(yǔ)句查看是否修改成功
alter database h_test default character set gbk collate gbk_bin;      
//刪除數(shù)據(jù)庫(kù)
drop database h_test;
//綜上帅刀，可以直接創(chuàng)建數(shù)據(jù)庫(kù)且設(shè)置編碼方式
CREATE DATABASE h_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

1-2.關(guān)于數(shù)據(jù)表

//首先選定操作的數(shù)據(jù)庫(kù)
use h_test;
//創(chuàng)建表student
create table student(
  id  int(11),
  name  varchar(20),
  age int(11)
);
//查看數(shù)據(jù)表
show tables;
//查看數(shù)據(jù)表信息妖异，后面加上參數(shù)/G可使結(jié)果更加美觀(guān)
show create table student;
//查看表的的字段信息
desc student;
//修改表名
alter table student rename [to] h_student;
//修改字段名
alter table h_student change name stu_name varchar(20);
//修改字段的數(shù)據(jù)類(lèi)型
alter table h_student modify id int(20);
//添加字段
alter table h_student add grade float;
//刪除字段
alter table h_student drop grade;
//修改字段的位置
alter table h_student modify stu_name varchar(20) first;
alter table h_student modify id int(11) after age;
//刪除數(shù)據(jù)表
drop table h_student;

1-3表的約束

1-4索引

作用：提高表中數(shù)據(jù)的查詢(xún)速度
1.普通索引
2.唯一性索引
3.全文索引
4.單列索引
5.多列索引
6.空間索引

//創(chuàng)建索引
//一.創(chuàng)建表的時(shí)候創(chuàng)建索引
create table 表名(
        字段名 數(shù)據(jù)類(lèi)型[完整性約束條件],
        ...
        字段名 數(shù)據(jù)類(lèi)型,
        [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
  );
//1-1.創(chuàng)建普通索引
create table test1(
  id  INT,
  name VARCHAR(20),
  age INT,
  INDEX (id)
);
//可以插入一條數(shù)據(jù),查看索引是否被使用
explain select * from test1 where id=1 \G;
//1-2.創(chuàng)建唯一性索引
create table test2(
  id  INT,
  name VARCHAR(20),
  age INT,
  UNIQUE INDEX unique_id(id asc)
);
//1-3.創(chuàng)建全文索引
create table test3(
  id  INT,
  name VARCHAR(20),
  age INT,
  FULLTEXT INDEX fulltext_name(name)
)ENGINE=MyISAM;
//1-4.創(chuàng)建單列索引
create table test4(
  id  INT,
  name VARCHAR(20),
  age INT,
  INDEX single_name(name(20))
);
//1-5.創(chuàng)建多列索引
create table test5(
  id  INT,
  name VARCHAR(20),
  age INT,
  INDEX multi(id,name(20))
);
//1-6.創(chuàng)建空間索引
create table test6(
  id  INT,
  space GEOMETRY NOT NULL,
  SPATIAL INDEX sp(space)
)ENGINE=MyISAM;
---------------------------------------------------
//二.使用create index語(yǔ)句在已經(jīng)存在的表上創(chuàng)建索引
//首先新建一個(gè)表,這個(gè)表沒(méi)有索引
create table student(
  id int,
  age int,
  name varchar(20),
  intro varchar(40),
  g GEOMETRY NOT NULL
)ENGINE=MyISAM;
//2-1.創(chuàng)建普通索引
create index index_id on student(id);
//2-2.創(chuàng)建唯一性索引
create unique index uniqueidx on student(id);
//2-3.創(chuàng)建單列索引
create index singleidx on student(age);
//2-4.創(chuàng)建多列索引
create index mulitidx on student(name(20),intro(40));
//2-5.創(chuàng)建全文索引
create fulltext index fulltextidx on student(name);
//2-6.創(chuàng)建空間索引
create spatial index spatidx on student(g); 
//下圖是第二種方法創(chuàng)建索引演示后的所有索引

index2

//三.使用alter table語(yǔ)句在已經(jīng)存在的表上創(chuàng)建索引
//刪除student表甸各，重新創(chuàng)建
drop table student;
create table student(
  id int,
  age int,
  name varchar(20),
  intro varchar(40),
  space GEOMETRY NOT NULL
)ENGINE=MyISAM;
//3-1.創(chuàng)建普通索引
alter table student add index index_id(id);
//3-2.創(chuàng)建唯一性索引
alter table student add unique uniqueidx(id);
//3-3.創(chuàng)建單列索引
alter table student add index singleidx (age);
//3-4.創(chuàng)建多列索引
alter table student add index multidx(name(20),intro(40));
//3-5.創(chuàng)建全文索引
alter table student add fulltext index fulltextidx(name);
//3-6.創(chuàng)建空間索引
alter table student add spatial index spatidx(space);
//下圖演示結(jié)果

index3

//刪除索引，有下面兩種方式
//1.使用alter table刪除索引fulltextidx
alter table student drop index fulltextidx;
//2.使用drop index刪除索引spatidx
drop index spatidx on student;
//下圖可看到刪除成功

dropIndex

1-5.添加數(shù)據(jù)

//重新建立表student
drop table student;
create table student(
  id int,
  name varchar(20) not null,
  grade float
);
//插入一條數(shù)據(jù)焰坪，也可以少某個(gè)字段的同時(shí)也少對(duì)應(yīng)的數(shù)據(jù)
insert into student(id,name,grade) values(1,'howie',70);
//也可以不指定字段名趣倾，但要注意順序
insert into student values(2,'howie',80);
//也可以這樣添加數(shù)據(jù)
insert into student set id=3,name="howie",grade=90;
//同時(shí)添加多條數(shù)據(jù)
insert into student values
(4,'howie',80),
(5,'howie',80),
(6,'howie',80);

1-6.更新數(shù)據(jù)

//更新id=1的數(shù)據(jù)
update student set name="howie1",grade=60 where id=1;
//批量更新,如果沒(méi)有where子句，會(huì)更新表中所有對(duì)應(yīng)數(shù)據(jù)
update student set grade=100 where id<4;

1-7.刪除數(shù)據(jù)

//刪除id=6的數(shù)據(jù)
delete from student where id=6;
//批量刪除數(shù)據(jù)
delete from student where id>3;
//刪除所有數(shù)據(jù),DDL(數(shù)據(jù)定義語(yǔ)言)語(yǔ)句 truncate table student也可以刪除表內(nèi)所有數(shù)據(jù)
delete from student;

二 某饰、單表查詢(xún)和多表操作

單表查詢(xún)：如何從數(shù)據(jù)庫(kù)中獲取你需要的數(shù)據(jù)
多表查詢(xún)：實(shí)際開(kāi)發(fā)中儒恋，需要進(jìn)行2張表以上進(jìn)行操作

2-1-1.單表查詢(xún)

//建立表student
create table student(
  id int not null auto_increment,
  name varchar(20) not null,
  grade float,
  primary key(id)
);
//插入數(shù)據(jù)
insert into student (name,grade) values
("howie1",40),
("howie1",50),
("howie2",50),
("howie3",60),
("howie4",70),
("howie5",80),
("howie6",null);
//查詢(xún)?nèi)?select * from student;
//查詢(xún)某個(gè)字段
select name from student;
//條件查詢(xún),查詢(xún)id=2學(xué)生的信息
select * from student where id=2;
//in關(guān)鍵字查詢(xún),也可以使用not in
select * from student where id IN(1,2,3);
//between and關(guān)鍵字查詢(xún)
select * from student where id between 2 and 5;
//空值(NULL)查詢(xún)，使用IS NULL來(lái)判斷
select * from student where grade is null;
//distinct關(guān)鍵字查詢(xún)
select distinct name from student;
//like關(guān)鍵字查詢(xún),查詢(xún)以h開(kāi)頭黔漂，e結(jié)尾的數(shù)據(jù)
select * from student where name like "h%e";
//and關(guān)鍵字多條件查詢(xún),or關(guān)鍵字的使用也是類(lèi)似
select * from student where id>5 and grade>60;

2-1-2.高級(jí)查詢(xún)

//聚合函數(shù)
//count()函數(shù),sum()函數(shù),avg()函數(shù),max()函數(shù),min()函數(shù)
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
//對(duì)查詢(xún)結(jié)果進(jìn)行排序
select * from student order by grade;
//分組查詢(xún)
//1.單獨(dú)使用group by分組
select * from student group by grade;
//2.和聚合函數(shù)一起使用
select count(*),grade from student group by grade;
//3.和having關(guān)鍵字一起使用
select sum(grade),name from student group by grade having sum(grade) >100;
//使用limit限制查詢(xún)結(jié)果的數(shù)量
select * from student limit 5;
select * from student limit 2,2;
select * from student order by grade desc limit 2,2;
//函數(shù),mysql提供了許多函數(shù)
select concat(id,':',name,':',grade) from student;
//為表取別名
select * from student as stu where stu.name="howie";
//為字段取別名,as關(guān)鍵字也可以不寫(xiě)
select name as stu_name,grade stu_grade from student;

2-2.多表操作

1.了解外鍵
2.了解關(guān)聯(lián)關(guān)系
3.了解各種連接查詢(xún)多表的數(shù)據(jù)
4.了解子查詢(xún)碧浊，會(huì)使用各種關(guān)鍵字以及比較運(yùn)算符查詢(xún)多表中的數(shù)據(jù)

2-2-1.外鍵

外鍵是指引用另一個(gè)表中的一列或者多列，被引用的列應(yīng)該具有主鍵約束或者唯一性約束瘟仿，用于建立和加強(qiáng)兩個(gè)數(shù)據(jù)表之間的連接箱锐。

//創(chuàng)建表class,student
create table class(
   id int not null primary key,
   classname varchar(20) not null
)ENGINE=InnoDB;
create table student(
   stu_id int not null primary key,
   stu_name varchar(20) not null,
   cid int not null      -- 表示班級(jí)id，它就是class表的外鍵
)ENGINE=InnoDB;
//添加外鍵約束
alter table student add constraint FK_ID foreign key(cid) references class(id);
//刪除外鍵約束
alter table student drop foreign key FK_ID;

看下圖可知外鍵添加成功：

foreign key

2-2-2.操作關(guān)聯(lián)表

//數(shù)據(jù)表有三種關(guān)聯(lián)關(guān)系劳较，多對(duì)一驹止、多對(duì)多浩聋、一對(duì)一
//學(xué)生(student)和班級(jí)(class)是多對(duì)一關(guān)系，添加數(shù)據(jù)
//首選添加外鍵約束
alter table student add constraint FK_ID foreign key(cid) references class(id);
//添加數(shù)據(jù),這兩個(gè)表便有了關(guān)聯(lián)若插入中文在終端顯示空白臊恋，可設(shè)置set names 'gbk';
insert into class values(1,"軟件一班"),(2,"軟件二班");
insert into student values(1,"howie",1),(2,"howie1",2),(3,"howie2",1),(4,"howie3",2);
//交叉連接
select * from student cross join class;
//內(nèi)連接衣洁，該功能也可以使用where語(yǔ)句實(shí)現(xiàn)
select student.stu_name,class.classname from student join class on class.id=student.cid;
//外連接
//首先在student,class表中插入數(shù)據(jù)
insert into class values(3,"軟件三班");
//左連接，右連接
select s.stu_id,s.stu_name,c.classname from student s left join class c on c.id=s.cid;
select s.stu_id,s.stu_name,c.classname from student s right join class c on c.id=s.cid;
//復(fù)合條件連接查詢(xún)就是添加過(guò)濾條件
//子查詢(xún)
//in關(guān)鍵字子查詢(xún)跟上面的in關(guān)鍵字查詢(xún)類(lèi)似
select * from student where cid in(select id from class where id=2);
//exists關(guān)鍵字查詢(xún),相當(dāng)于測(cè)試抖仅，不產(chǎn)生數(shù)據(jù)坊夫，只返回true或者false，只有返回true撤卢，外層才會(huì)執(zhí)行环凿，具體看下圖
select * from student where exists(select id from class where id=12);   -- 外層不會(huì)執(zhí)行
select * from student where exists(select id from class where id=1);    -- 外層會(huì)執(zhí)行
//any關(guān)鍵字查詢(xún)
 select * from student where cid>any(select id from class);
//all關(guān)鍵字查詢(xún)
 select * from student where cid=any(select id from class);

具體結(jié)果請(qǐng)看下圖：

交叉連接

內(nèi)連接

left/right join on

in

exists

any all

三 、事務(wù)與存儲(chǔ)過(guò)程

事務(wù)的概念放吩，會(huì)開(kāi)啟智听、提交和回滾事務(wù)
事務(wù)的四種隔離級(jí)別
創(chuàng)建存儲(chǔ)過(guò)程
調(diào)用、查看渡紫、修改和刪除存儲(chǔ)過(guò)程

3-1 事務(wù)管理

start transaction;  -- 開(kāi)啟事務(wù)
commit;             -- 提交事務(wù)
rollback;           -- 取消事務(wù)(回滾)
//創(chuàng)建表account到推，插入數(shù)據(jù)
create table account(
  id int primary key auto_increment,
  name varchar(40),
  money float
);
insert into account(name,money) values('a',1000),('b',2000),('c',3000);
//利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬功能，首先開(kāi)啟事務(wù)惕澎，然后執(zhí)行語(yǔ)句莉测，提交事務(wù)
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
commit;
//事務(wù)的提交，通過(guò)這個(gè)命令查看mysql提交方式
select @@autocommit; -- 若為1唧喉，表示自動(dòng)提交捣卤，為0，就要手動(dòng)提交
//若事務(wù)的提交方式為手動(dòng)提交
set @@autocommit = 0; -- 設(shè)置為手動(dòng)提交
start transaction;
update account set money=money+100 where name='a';
update account set money=money-100 where name='b';
//現(xiàn)在執(zhí)行select * from account 可以看到轉(zhuǎn)賬成功欣喧，若此時(shí)退出數(shù)據(jù)庫(kù)重新登錄腌零，會(huì)看到各賬戶(hù)余額沒(méi)有改變，所以一定要用commit語(yǔ)句提交事務(wù)唆阿，否則會(huì)失敗
//事務(wù)的回滾益涧，別忘記設(shè)置為手動(dòng)提交的模式
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
//若此時(shí)a不想轉(zhuǎn)賬給b，可以使用事務(wù)的回滾
rollback;
//事務(wù)的隔離級(jí)別
read uncommitted;
read committed;
repeatable read;
serializable;

3-2 存儲(chǔ)過(guò)程

//創(chuàng)建查看student表的存儲(chǔ)過(guò)程
//創(chuàng)建student表
create table student( 
  id int not null primary key auto_increment, 
  name varchar(4), 
  grade float 
)ENGINE=InnoDB default character set utf8;
delimiter //  -- 將mysql的結(jié)束符設(shè)置為//
create procedure Proc()
  begin
  select * from student;
  end //
delimiter ;   -- 將mysql的結(jié)束符設(shè)置為;
call Proc();  -- 這樣就可以調(diào)用該存儲(chǔ)過(guò)程
//變量的使用,mysql中變量不用事前申明驯鳖，在用的時(shí)候直接用“@變量名”使用就可以
set @number=100; -- 或set @num:=1;
//定義條件和處理程序
//光標(biāo)的使用
//1.聲明光標(biāo)
DECLARE * cursor_name* CURSOR FOR select_statement
2. 光標(biāo)OPEN語(yǔ)句
OPEN cursor_name
3. 光標(biāo)FETCH語(yǔ)句
FETCH cursor_name INTO var_name [, var_name] ...
4. 光標(biāo)CLOSE語(yǔ)句
CLOSE cursor_name
//流程控制的使用  不做介紹

3-3 調(diào)用存儲(chǔ)過(guò)程

//定義存儲(chǔ)過(guò)程
delimiter //
create procedure proc1(in name varchar(4),out num int)
begin
select count(*) into num from student where name=name;
end//
delimiter ;
//調(diào)用存儲(chǔ)過(guò)程
call proc1("tom",@num) -- 查找名為tom學(xué)生人數(shù)
//查看結(jié)果
select @num;  -- 看下圖

call proc1

//查看存儲(chǔ)過(guò)程
 show procedure status like 'p%' \G -- 獲得以p開(kāi)頭的存儲(chǔ)過(guò)程信息
//修改存儲(chǔ)過(guò)程
alter {procedure|function} sp_name[characteristic...]
//刪除存儲(chǔ)過(guò)程
drop procedure proc1;

四闲询、視圖

如何創(chuàng)建視圖
查看、修改浅辙、更新扭弧、刪除視圖

4-1、視圖的基本操作

//在單表上創(chuàng)建視圖,重新創(chuàng)建student表记舆，插入數(shù)據(jù)
create table student(
  id int not null primary key auto_increment,
  name varchar(10) not null,
  math float,
  chinese float
);
insert into student(name,math,chinese) values
('howie1',66,77),
('howie2',66,77),
('howie3',66,77);
//開(kāi)始創(chuàng)建視圖
create view stu_view as select math,chinese,math+chinese from student;  -- 下圖可看出創(chuàng)建成功
//也可以創(chuàng)建自定義字段名稱(chēng)的視圖
create view stu_view2(math,chin,sum) as select math,chinese,math+chinese from student;

stu_view

stu_view2

//在多表上創(chuàng)建視圖鸽捻，創(chuàng)建表stu_info，插入數(shù)據(jù)
create table stu_info(
  id int not null primary key auto_increment,
  class varchar(10) not null,
  addr varchar(100)
);
insert into stu_info(class,addr) values
('1','anhui'),
('2','fujian'),
('3','guangdong');
//創(chuàng)建視圖stu_class
create view stu_class(id,name,class) as 
select student.id,student.name,stu_info.class from 
student,stu_info where student.id=stu_info.id;
//查看視圖
desc stu_class;
show table status like 'stu_class'\G
show create view stu_class\G
//修改視圖
create or replace view stu_view as select * from student;
alter view stu_view as select chinese from student;
//更新視圖
update stu_view set chinese=100;
insert into student values(null,'haha',100,100);
delete from stu_view2 where math=100;
//刪除視圖
drop view if exists stu_view2;

stu_class

五、總結(jié)

筆記參考《MySql數(shù)據(jù)庫(kù)入門(mén)》
基本命令就這么多御蒲，仍需多多敲寫(xiě)鞏固
以上命令本人全部敲過(guò)衣赶，若有錯(cuò)誤，敬請(qǐng)指出厚满，希望有幫助府瞄，謝謝。