部分资料来源：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;
