部分資料來源：http://www.jianshu.com/p/118e1c41e9f0
首先將mysql設置為全局變量，運行命令
~~~
mysql -u root mysql
~~~
登錄mysql,如果遇到ERROR1405錯誤，如
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: YES)
則找到mysql的配置文件my.ini 在mysqld加上skip-grant-tables，保存重啟
登錄成功后進入
mariaDB 命令操作跟mysql操作一樣 ，**每一個操作后面要加分號“；” ，有些操作要分行才有效，**它的性能比mysql 要好
### 1-1.關于數據庫#
//創建數據庫
create database h_test;
//查看數據庫
show databases;
//查看數據庫信息
show create database h_test; ***親測有error***
//修改數據庫的編碼，可使用上一條語句查看是否修改成功
alter database h_test default character set gbk collate gbk_bin;
//刪除數據庫
drop database h_test;
### 1-2.關于數據表
//首先選定操作的數據庫
use h_test;
//創建表student
create table student(
id int(11),
name varchar(20),
age int(11)
);
//查看數據表
show tables;
//查看數據表信息，后面加上參數/G可使結果更加美觀
show create table student;
//查看表的的字段信息
desc student;
//修改表名
alter table student rename [to] h_student;***親測無效***
//修改字段名
alter table h_student change name stu_name varchar(20);
//修改字段的數據類型
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;
//刪除數據表
drop table h_student;
### 1-3表的約束
| 約束條件 | 說明 |
| --- | --- |
| PRIMARY KEY | 主鍵約束，用于唯一標識對應的記錄 |
| FOREIGN KEY | 外鍵約束 |
|NOT NULL | 非空約束 |
|UNIQUE | 唯一性約束 |
| DEFAULT | 默認值約束，用于設置字段的默認值 |
### 1-4索引
作用：提高表中數據的查詢速度
1.普通索引
2.唯一性索引
3.全文索引
4.單列索引
5.多列索引
6.空間索引
//創建索引
//一.創建表的時候創建索引
create table 表名(
字段名 數據類型[完整性約束條件],
...
字段名 數據類型,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
);
//1-1.創建普通索引
create table test1(
id INT,
name VARCHAR(20),
age INT,
INDEX (id)
);
//可以插入一條數據,查看索引是否被使用
explain select * from test1 where id=1 \G;
//1-2.創建唯一性索引
create table test2(
id INT,
name VARCHAR(20),
age INT,
UNIQUE INDEX unique_id(id asc)
);
//1-3.創建全文索引
create table test3(
id INT,
name VARCHAR(20),
age INT,
FULLTEXT INDEX fulltext_name(name)
)ENGINE=MyISAM;
//1-4.創建單列索引
create table test4(
id INT,
name VARCHAR(20),
age INT,
INDEX single_name(name(20))
);
//1-5.創建多列索引
create table test5(
id INT,
name VARCHAR(20),
age INT,
INDEX multi(id,name(20))
);
//1-6.創建空間索引
create table test6(
id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX sp(space)
)ENGINE=MyISAM;
---------------------------------------------------
//二.使用create index語句在已經存在的表上創建索引
//首先新建一個表,這個表沒有索引
create table student(
id int,
age int,
name varchar(20),
intro varchar(40),
g GEOMETRY NOT NULL
)ENGINE=MyISAM;
//2-1.創建普通索引
create index index_id on student(id);
//2-2.創建唯一性索引
create unique index uniqueidx on student(id);
//2-3.創建單列索引
create index singleidx on student(age);
//2-4.創建多列索引
create index mulitidx on student(name(20),intro(40));
//2-5.創建全文索引
create fulltext index fulltextidx on student(name);
//2-6.創建空間索引
create spatial index spatidx on student(g);
//下圖是第二種方法創建索引演示后的所有索引
//三.使用alter table語句在已經存在的表上創建索引
//刪除student表，重新創建
drop table student;
create table student(
id int,
age int,
name varchar(20),
intro varchar(40),
space GEOMETRY NOT NULL
)ENGINE=MyISAM;
//3-1.創建普通索引
alter table student add index index_id(id);
//3-2.創建唯一性索引
alter table student add unique uniqueidx(id);
//3-3.創建單列索引
alter table student add index singleidx (age);
//3-4.創建多列索引
alter table student add index multidx(name(20),intro(40));
//3-5.創建全文索引
alter table student add fulltext index fulltextidx(name);
//3-6.創建空間索引
alter table student add spatial index spatidx(space);
//下圖演示結果
//刪除索引，有下面兩種方式
//1.使用alter table刪除索引fulltextidx
alter table student drop index fulltextidx;
//2.使用drop index刪除索引spatidx
drop index spatidx on student;
//下圖可看到刪除成功
### 1-5.添加數據
//重新建立表student
drop table student;
create table student(
id int,
name varchar(20) not null,
grade float
);
//插入一條數據，也可以少某個字段的同時也少對應的數據
insert into student(id,name,grade) values(1,'howie',70);
//也可以不指定字段名，但要注意順序
insert into student values(2,'howie',80);
//也可以這樣添加數據
insert into student set id=3,name="howie",grade=90;
//同時添加多條數據
insert into student values
(4,'howie',80),
(5,'howie',80),
(6,'howie',80);
### 1-6.更新數據
//更新id=1的數據
update student set name="howie1",grade=60 where id=1;
//批量更新,如果沒有where子句，會更新表中所有對應數據
update student set grade=100 where id<4;
### 1-7.刪除數據
//刪除id=6的數據
delete from student where id=6;
//批量刪除數據
delete from student where id>3;
//刪除所有數據,DDL(數據定義語言)語句 truncate table student也可以刪除表內所有數據
delete from student;
### 二 、單表查詢和多表操作
單表查詢：如何從數據庫中獲取你需要的數據
多表查詢：實際開發中，需要進行2張表以上進行操作
2-1-1.單表查詢
//建立表student
create table student2(
id int not null auto_increment,
name varchar(20) not null,
grade float,
primary key(id)
);
//插入數據
insert into student2 (name,grade) values
("howie1",40),
("howie1",50),
("howie2",50),
("howie3",60),
("howie4",70),
("howie5",80),
("howie6",null);
//查詢全部
select * from student;
//查詢某個字段
select name from student;
//條件查詢,查詢id=2學生的信息
select * from student where id=2;
//in關鍵字查詢,也可以使用not in
select * from student where id IN(1,2,3);
//between and關鍵字查詢
select * from student where id between 2 and 5;
//空值(NULL)查詢，使用IS NULL來判斷
select * from student where grade is null;
//distinct關鍵字查詢
select distinct name from student;
//like關鍵字查詢,查詢以h開頭，e結尾的數據
select * from student where name like "h%e";
//and關鍵字多條件查詢,or關鍵字的使用也是類似
select * from student where id>5 and grade>60;
### 2-1-2.高級查詢
//聚合函數
//count()函數,sum()函數,avg()函數,max()函數,min()函數
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
//對查詢結果進行排序
select * from student order by grade;
//分組查詢
//1.單獨使用group by分組
select * from student group by grade;
//2.和聚合函數一起使用
select count(*),grade from student group by grade;
//3.和having關鍵字一起使用
select sum(grade),name from student group by grade having sum(grade) >100;***不是很懂***
having的用法
having字句可以讓我們篩選成組后的各種數據，where字句在聚合前先篩選記錄，也就是說作用在group by和having字句前。而 having子句在聚合后對組記錄進行篩選。
SQL實例：
一、顯示每個地區的總人口數和總面積．
SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region
先以region把返回記錄分成多個組，這就是GROUP BY的字面含義。分完組后，然后用聚合函數對每組中
的不同字段（一或多條記錄）作運算。
二、 顯示每個地區的總人口數和總面積．僅顯示那些面積超過1000000的地區。
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
在這里，我們不能用where來篩選超過1000000的地區，因為表中不存在這樣一條記錄。
相反，having子句可以讓我們篩選成組后的各組數據
//使用limit限制查詢結果的數量
select * from student limit 5;
select * from student limit 2,2;
select * from student order by grade desc limit 2,2;
//函數,mysql提供了許多函數
select concat(id,':',name,':',grade) from student;
//為表取別名
select * from student as stu where stu.name="howie";
//為字段取別名,as關鍵字也可以不寫
select name as stu_name,grade stu_grade from student;
### 2-2.多表操作
1.了解外鍵
2.了解關聯關系
3.了解各種連接查詢多表的數據
4.了解子查詢，會使用各種關鍵字以及比較運算符查詢多表中的數據
### 2-2-1.外鍵
外鍵是指引用另一個表中的一列或者多列，被引用的列應該具有主鍵約束或者唯一性約束，用于建立和加強兩個數據表之間的連接。
//創建表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 -- 表示班級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;
### 2-2-2.操作關聯表
//數據表有三種關聯關系，多對一、多對多、一對一
//學生(student)和班級(class)是多對一關系，添加數據
//首選添加外鍵約束
alter table student add constraint FK_ID foreign key(cid) references class(id);
//添加數據,這兩個表便有了關聯若插入中文在終端顯示空白，可設置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;
//內連接，該功能也可以使用where語句實現
select student.stu_name,class.classname from student join class on class.id=student.cid;
//外連接
//首先在student,class表中插入數據
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;
//復合條件連接查詢就是添加過濾條件
//子查詢
//in關鍵字子查詢跟上面的in關鍵字查詢類似
select * from student where cid in(select id from class where id=2);
//exists關鍵字查詢,相當于測試，不產生數據，只返回true或者false，只有返回true，外層才會執行，具體看下圖
select * from student where exists(select id from class where id=12); -- 外層不會執行
select * from student where exists(select id from class where id=1); -- 外層會執行
//any關鍵字查詢
select * from student where cid>any(select id from class);
//all關鍵字查詢
select * from student where cid=any(select id from class);
### 三 、事務與存儲過程
start transaction; -- 開啟事務
commit; -- 提交事務
rollback; -- 取消事務(回滾)
//創建表account，插入數據
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);
//利用事務實現轉賬功能，首先開啟事務，然后執行語句，提交事務
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
commit;
//事務的提交，通過這個命令查看mysql提交方式
select @@autocommit; -- 若為1，表示自動提交，為0，就要手動提交
//若事務的提交方式為手動提交
set @@autocommit = 0; -- 設置為手動提交
start transaction;
update account set money=money+100 where name='a';
update account set money=money-100 where name='b';
//現在執行select * from account 可以看到轉賬成功，若此時退出數據庫重新登錄，會看到各賬戶余額沒有改變，所以一定要用commit語句提交事務，否則會失敗
//事務的回滾，別忘記設置為手動提交的模式
start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
//若此時a不想轉賬給b，可以使用事務的回滾
rollback;
//事務的隔離級別
read uncommitted;
read committed;
repeatable read;
serializable;
### 3-2 存儲過程（這部分有待研究，不是很懂）
//創建查看student表的存儲過程
//創建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的結束符設置為//
create procedure Proc()
begin
select * from student;
end //
delimiter ; -- 將mysql的結束符設置為;
call Proc(); -- 這樣就可以調用該存儲過程
//變量的使用,mysql中變量不用事前申明，在用的時候直接用“@變量名”使用就可以
set @number=100; -- 或set @num:=1;
//定義條件和處理程序
//光標的使用
//1.聲明光標
DECLARE * cursor_name* CURSOR FOR select_statement
2. 光標OPEN語句
OPEN cursor_name
3. 光標FETCH語句
FETCH cursor_name INTO var_name [, var_name] ...
4. 光標CLOSE語句
CLOSE cursor_name
//流程控制的使用 不做介紹
### 3-3 調用存儲過程
//定義存儲過程
delimiter //
create procedure proc1(in name varchar(4),out num int)
begin
select count(*) into num from student where name=name;
end//
delimiter ;
//調用存儲過程
call proc1("tom",@num) -- 查找名為tom學生人數
//查看結果
select @num; -- 看下圖
//查看存儲過程
show procedure status like 'p%' \G -- 獲得以p開頭的存儲過程信息
//修改存儲過程
alter {procedure|function} sp_name[characteristic...]
//刪除存儲過程
drop procedure proc1;
### 四、視圖
如何創建視圖
查看、修改、更新、刪除視圖
### 4-1、視圖的基本操作
//在單表上創建視圖,重新創建student表，插入數據
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);
//開始創建視圖
create view stu_view as select math,chinese,math+chinese from student; -- 下圖可看出創建成功
//也可以創建自定義字段名稱的視圖
create view stu_view2(math,chin,sum) as select math,chinese,math+chinese from student;
//在多表上創建視圖，創建表stu_info，插入數據
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');
//創建視圖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;
