数据库

1.启动和链接MySQL服务

查看MySQL状态：sudo service mysql status

启动/停止/重启：sudo service mysql start/stop/restart

链接数据库：mysql -h 主机地址 -u 用户名 -p

数据库管理

查看已有库

show databases;

创建库

create database 库名 [character set utf8];

e.g. 创建stu数据库，编码为utf8
create database stu character set utf8;
create database stu charset=utf8;

注意：库名的命名

数字、字母、下划线,但不能使用纯数字
库名区分字母大小写
不要使用特殊字符和mysql关键字

切换库

use 库名;

e.g. 使用stu数据库
use stu;

查看当前所在库

select database();

删除库

drop database 库名;

e.g. 删除test数据库
drop database test;

数据表管理

表的基本操作

创建表

create table 表名(字段名数据类型约束,字段名数据类型约束,...字段名数据类型约束);

字段约束

如果你想设置数字为无符号则加上 unsigned
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
DEFAULT 表示设置一个字段的默认值
COMMENT 增加字段说明
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY 关键字用于定义列为主键。主键的值不能重复,且不能为空。

e.g.  创建班级表
create table class_1 (id int primary key auto_increment,name varchar(32) not null,age tinyint unsigned not null,sex enum('w','m'),score float default 0.0);

e.g. 创建兴趣班表
create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),level char not null,price decimal(6,2),remark text);

查看数据表

show tables；

查看表结构

desc 表名;

查看数据表创建信息

show create table 表名；

删除表

drop table 表名;

表数据基本操作

插入(insert)

insert into 表名 values(值1),(值2),...;
insert into 表名(字段1,...) values(值1),...;

e.g. 
insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);

insert into class_1 (name,age,sex,score) values ('Lucy',17,'w',81);

查询(select)

select * from 表名 [where 条件];
select 字段1,字段2 from 表名 [where 条件];

e.g. 
select * from class_1;
select name,age from class_1;

where子句

e.g.
select * from class_1 where age % 2 = 0;

e.g.
select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

e.g.
select * from class_1 where sex='m' and age>9;

更新表记录(update)

update 表名 set 字段1=值1,字段2=值2,... where 条件;

注意:update语句后如果不加where条件,所有记录全部更新

e.g.
update class_1 set age=11 where name='Abby';

删除表记录（delete）

delete from 表名 where 条件;

注意:delete语句后如果不加where条件,所有记录全部清空

e.g.
delete from class_1 where name='Abby';

表字段的操作(alter)

语法 ：alter table 表名 执行动作;

* 添加字段(add)
    alter table 表名 add 字段名 数据类型;
    alter table 表名 add 字段名 数据类型 first;
    alter table 表名 add 字段名 数据类型 after 字段名;
* 删除字段(drop)
    alter table 表名 drop 字段名;
* 修改数据类型(modify)
    alter table 表名 modify 字段名 新数据类型;
* 修改字段名(change)
    alter table 表名 change 旧字段名 新字段名 新数据类型;
* 表重命名(rename)
    alter table 表名 rename 新表名;

e.g. 
alter table hobby add tel char(11) after name;
alter table hobby modify tel char(16);
alter table hobby change tel phone char(16);

时间类型数据

日期： DATE
日期时间： DATETIME，TIMESTAMP
时间： TIME
年份：YEAR

时间格式

date ："YYYY-MM-DD"
time ："HH:MM:SS"
datetime ："YYYY-MM-DD HH:MM:SS"
timestamp ："YYYY-MM-DD HH:MM:SS"

注意:

datetime ：以系统时间存储
timestamp ：以标准时间存储但是查看时转换为系统时区，所以表现形式和datetime相同

e.g.
create table marathon (id int primary key auto_increment,athlete varchar(32),birthday date,registration_time datetime,performance time);

日期时间函数
- now() 返回服务器当前日期时间,格式对应datetime类型
时间操作

时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

  select * from marathon where birthday>='2000-01-01';
  select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

高级查询语句

模糊查询

LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
mysql> select * from class_1 where name like 'A%';

as 用法

在sql语句中as用于给字段或者表重命名

 select name as 姓名,age as 年龄 from class_1;
 select * from class_1 as c where c.age > 17;

排序

ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

默认情况ASC表示升序，DESC表示降序

select * from class_1 where sex='m' order by age desc;

复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

select * from class_1 order by score desc,age;

限制

LIMIT 子句用于限制由 SELECT 语句返回的数据数量或者 UPDATE,DELETE语句的操作数量

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num] [OFFSET num]

查询班级男生第三名
select * from cls where sex='m' order by score desc limit 1 offset 2;

联合查询

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION 操作符语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

默认UNION后卫 DISTINCT表示删除结果集中重复的数据。如果使用ALL则返回所有结果集，包含重复数据。

 select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;

子查询

定义：当一个select语句中包含另一个select 查询语句，则称之为有子查询的语句
子查询出现的位置：

from 之后，此时子查询的内容作为一个新的表内容，再进行外层select查询

select name from (select * from class_1 where sex='m') as s where s.score > 90;

注意：需要将子查询结果集重命名一下，方便where子句中的引用操作

where字句中，此时select查询到的内容作为外层查询的条件值

     select *  from class_1 where age = (select age from class_1 where name='Tom');

注意：

子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。
如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

 select * from cls where name in (select name from hobby);

查询过程

通过之前的学习看到，一个完整的select语句内容是很丰富的。下面看一下select的执行过程：

(5)SELECT DISTINCT <select_list>                     

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

(7)LIMIT <limit_number>

高级查询练习

在stu下创建数据报表 sanguo

字段：id  name  gender  country  attack  defense

create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum('魏','蜀','吴'),
attack smallint,
defense tinyint
);


insert into sanguo
values (1, '曹操', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);

查找练习
1. 查找所有蜀国人信息，按照攻击力排名
2. 将赵云攻击力设置为360，防御设置为70
3. 吴国英雄攻击力超过300的改为300，最多改2个
4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
6. 查找名字为3字的
7. 查找攻击力为魏国最高攻击力的人还要高的蜀国英雄
8. 找到魏国排名2-3名的英雄
9. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的

聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

eg1 : 找出表中的最大攻击力的值？

select max(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(name) as number from sanguo;

eg3 : 蜀国英雄中攻击值大于200的英雄的数量

select count(*) from sanguo where attack > 200;

注意：此时select 后只能写聚合函数，无法查找其他字段。

聚合分组

group by

　　给查询的结果进行分组

e.g. : 计算每个国家的平均攻击力

select country,avg(attack) from sanguo 
group by country;

e.g. : 对多个字段创建分组，此时多个字段都相同时为一组

select age,sex,count(*) from class1 group by age,sex;

e.g. : 所有国家的男英雄中英雄数量最多的前2名的国家名称及英雄数量

select country,count(id) as number from sanguo 
where gender='M' group by country
order by number DESC
limit 2;

聚合筛选

having语句

　　对分组聚合后的结果进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力

select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

注意

having语句必须与group by联合使用。
having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段。

去重语句

distinct语句

　　不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct name,country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意: distinct和from之间所有字段都相同才会去重

聚合运算

　　查询表记录时做数学运算

运算符： + - * / %

eg1: 查询时显示攻击力翻倍
  select name,attack*2 from sanguo;
eg2: 更新蜀国所有英雄攻击力 * 2
  update sanguo set attack=attack*2 where country='蜀国';

索引操作

概述

定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

优缺点
- 优点：加快数据检索速度,提高查找效率
- 缺点：占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率

注意：

通常我们只在经常进行查询操作的字段上创建索引

对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

索引分类

普通(MUL)

普通索引：字段值无约束,KEY标志为 MUL

唯一索引(UNI)

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

主键索引（PRI）

一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

索引创建

创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);

在已有表中创建索引：

create [unique] index 索引名 on 表名(字段名);

e.g.
create unique index name_index on cls(name);

主键索引添加

 alter table 表名 add primary key(id);

查看索引

1、desc 表名;  --> KEY标志为：MUL 、UNI。
2、show index from 表名;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop primary key;  # 删除主键

扩展：借助性能查看选项去查看索引性能

set  profiling = 1； 打开功能 （项目上线一般不打开）

show profiles  查看语句执行信息

外键约束和表关联关系

外键约束

约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性
foreign key 功能 : 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表。
示例

# 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

# 创建人员表
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint DEFAULT 0,
  sex enum('m','w','o') DEFAULT 'o',
  salary decimal(8,2) DEFAULT 250.00,
  hire_date date NOT NULL,
  dept_id int
) ;

上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门，这显然是不合理的。

主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表。

foreign key 外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY（外键字段） 

REFERENCES tbl_name (主表主键)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用

# 创建表时直接简历外键
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint DEFAULT 0,
  sex enum('m','w','o') DEFAULT 'o',
  salary decimal(10,2) DEFAULT 250.00,
  hire_date date NOT NULL,
  dept_id int ,
  constraint dept_fk foreign key(dept_id) references dept(id));

# 建立表后增加外键
alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

注意：

1. 并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。
2. 从表的外键字段数据类型与指定的主表主键应该相同。

通过外键名称解除外键约束

alter table person drop foreign key dept_fk;

# 查看外键名称
show create table person;

注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。

级联动作

restrict(默认) : on delete restrict on update restrict
- 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
- 当主表更改主键字段值时，如果从表有相关记录则不允许更改
cascade ：数据级联更新 on delete cascade on update cascade
- 当主表删除记录或更改被参照字段的值时,从表会级联更新
set null : on delete set null on update set null
- 当主表删除记录时，从表外键字段值变为null
- 当主表更改主键字段值时，从表外键字段值变为null

表关联设计

当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见的数据关系如下：

一对一关系

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

举例 : 学生信息和学籍档案，一个学生对应一个档案，一个档案也只属于一个学生

create table student(id int primary key auto_increment,name varchar(50) not null);

create table record(id int primary key auto_increment,
comment text not null,
st_id int unique,
constraint st_fk foreign key(st_id) references student(id) 
on delete cascade 
on update cascade
);

一对多关系

一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一

举例：一个人可以拥有多辆汽车，每辆车登记的车主只有一人。

create table person(
  id varchar(32) primary key,
  name varchar(30),
  sex char(1),
  age int
);

create table car(
  id varchar(32) primary key,
  name varchar(30),
  price decimal(10,2),
  pid varchar(32),
  constraint car_fk foreign key(pid) references person(id)
);

多对多关系

一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
也能对应A表中的多条记录

举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表。

CREATE TABLE athlete (
  id int primary key AUTO_INCREMENT,
  name varchar(30),
  age tinyint NOT NULL,
  country varchar(30) NOT NULL,
  description varchar(30)
);

CREATE TABLE item (
  id int primary key AUTO_INCREMENT,
  rname varchar(30) NOT NULL
);

CREATE TABLE athlete_item (
   id int primary key auto_increment,
   aid int NOT NULL,
   tid int NOT NULL,
   CONSTRAINT athlete_fk FOREIGN KEY (aid) REFERENCES athlete (id),
   CONSTRAINT item_fk FOREIGN KEY (tid) REFERENCES item (id)
);

表连接

　　如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

简单多表查询

select  字段1,字段2... from 表1,表2... [where 条件]

e.g.
select * from dept,person where dept.id = person.dept_id;

内连接

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person inner join  dept  on  person.dept_id =dept.id;

笛卡尔积

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

select * from person inner join  dept;

左连接 :

　　左表全部显示，显示右表中与左表匹配的项

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person left join  dept  on  person.dept_id =dept.id;

# 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;

右连接：

　　右表全部显示，显示左表中与右表匹配的项

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person right join  dept  on  person.dept_id =dept.id;

综合查询练习

create table class(cid int primary key auto_increment,
                  caption char(4) not null);

create table teacher(tid int primary key auto_increment,
                    tname varchar(32) not null);

create table student(sid int primary key auto_increment,
                    sname varchar(32) not null,
                    gender enum('male','female','others') not null default 'male',
                    class_id int,
                    foreign key(class_id) references class(cid)
                    on update cascade
                    on delete cascade);

create table course(cid int primary key auto_increment,
                   cname varchar(16) not null,
                   teacher_id int,
                   foreign key(teacher_id) references teacher(tid)
                   on update cascade
                   on delete cascade);

create table score(sid int primary key auto_increment,
                  student_id int,
                  course_id int,
                  number int(3) not null,
                  foreign key(student_id) references student(sid)
                   on update cascade
                   on delete cascade,
                   foreign key(course_id) references course(cid)
                   on update cascade
                   on delete cascade);

insert into class(caption) values('三年二班'),('三年三班'),('三年一班');
insert into teacher(tname) values('波多老师'),('苍老师'),('小泽老师');
insert into student(sname,gender,class_id) values('钢蛋','female',1),('铁锤','female',1),('山炮','male',2),('彪哥','male',3);
insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);
insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100),(3,2,78),(4,3,66);

1. 查询每位老师教授的课程数量
2. 查询学生的信息及学生所在班级信息
3. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分
4. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩
5. 查询课程编号为2且课程成绩在80以上的学生学号和姓名
6. 查询各个课程及相应的选修人数

视图

创建视图

语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

e.g.
create view  c1 as select name,age from class_1;

视图表的增删改查操作

视图的增删改查操作与一般表的操作相同，使用insert update delete select即可，但是原数据表的约束条件仍然对视图产生作用。

查看现有视图

show full tables in stu where table_type like 'VIEW';

删除视图

drop view [IF EXISTS] 视图名；

IF EXISTS 表示如果存在，这样即使没有指定视图也不会报错。

drop view if exists c1;

修改视图

参考创建视图，将create关键字改为alter

alter view  c1 as select name,age,score from class_1;

视图作用

是对数据的一种重构，不影响原数据表的使用。
简化高频复杂操作的过程，就像一种对复杂操作的封装。
提高安全性，可以给不同用户提供不同的视图。
让数据更加清晰。

缺点

视图的性能相对较差，从数据库视图查询数据可能会很慢。

Live what we love, do what we do, follow the heart, and do not hesitate.

数据库