数据库

• 数据库解决的问题，E-R模型，三范式
• 图形界面操作数据库、表、数据
• 命令行操作数据库、表、数据

创建数据库； create database 数据库名 charset=utf8;
创建表； create table 表名(字段 类型 约束);
id int auto_increment primary key not null
插入数据； insert into 表名(…) value(…);
修改数据； update 表名 set 字段名=值 …
删除数据； delete from 表名 …
逻辑删除 定义isDelete字段名，对它进行修改

select * from 表名;

• 数据库备份
  1.进入超级管理员
  sudo -s
  2.进入mysql库目录
  3.运行mysqldump命令
  mysqldump -uroot -p 数据库名字 > ~/Desktop/bak.sql
• 数据库恢复
  1.连接mysql，创建数据库
  2.退出数据库，执行如下命令
  mysql -uroot -p 数据库名 < ~/Desktop/bak.sql

实质是生成了 create table 等语句

CRUD

• 查询的基本语法：
  select * from 表名;

• 消除重复行：
  select distinct gender from students;

• 模糊查询
  like
  % 表示任意多个字符
  _ 表示一个任意字符
  如查询姓黄的学生：
  select * from students where sname like '黄%';
  查询姓黄或者叫鑫的学生
  select * from students where like '黄%' or sname like '%鑫'；

• 范围查询
  in 表示在一个非连续的范围内
  查询编号是1或3或8的学生
  select * from students where id in(1,3,8);

  between … and … 表示在一个连续的范围内
  查询学生3至8的男生
  select * from students where id between 3 and 8 and gender=1;

• 空判断
  注意：null与’‘是不同的 (null 不指向内存，’ ‘ 是内存中的一个空字符串)
  判空 is null
  查询没有填写地址的学生
  select * from students where hometown is null;

• 聚合
  为了快速得到统计数据，提供了5个聚合函数
  count() 表示计算总行数，括号中写 或者列名,结果是相同的
  查询学生总数
  select count(*) from students;
  max(列）表示求此列的最小值 min（*）
  select min(id) from students where gender=0;
  sum(列） 表示求此列的和
  查询男生的编号纸盒
  select sum(id) from students where gender=1;
  avg(列）表示求此列的平均值
  select avg(age) from students where gender=0 and isDelete=0;

• 分组
  按照字段分组，表示此字段相同的数据会被放到一个组中
  分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
  可以对分组后的数据进行统计，做聚合运算
  语法：

  select 列1，列2，聚合 ... from 表名 group by 列1，列2，列3 ...
  查询那女生总数
  select gender as 性别，count(*)
  from students
  group by gender;
  查询各城市人数
  select hometown as 家乡，count(*)
  from students group by hometown;
  
  分组后筛选
  用法：
  select 列1，列2，聚合... from 表名
  group by 列1，列2，列3...
  having 列1，...聚合...
  
  查询男生总人数
  方案一：
  select count(*) from students where gender=1;
  方案二：
  select count(*) from students group by gender having gender=1;
  
  对比where和having
     where 是对from后面指定的表进行筛选，属于对原始数据的筛选
     having是对group by 的结果进行筛选

• 排序
  语法：

  select * from 表名 order by 列1 asc|desc，列2 asc|desc，...
  默认升序排列
  asc 从小到大
  desc 从大到小
  查询未删除科目的信息，按名称升序
  select * from students where isDelest=0 order by stitle asc;

• 获取不分行
  当数据量过大时，在一页中查看数据是一件特别麻烦的事情
  语法

      select * from 表名 
      limit start,count
      从start 开始，获取count条数据
      start索引从0开始
      
  示例：分页
      求第N页的数据
      select * from students where isdelete=0
      limit (n-1)*m,m;

• 完整的select语句

  select distinct * from 表名 where ... 
  group by ... having ...
  order by ....
  limit start,count
  执行的顺序为：
  from表名
  where ...
  group by ...
  select distinct *
  having ...
  order by ...
  limit start,count

  实际使用中，只是语句中某些部分的组合，而不是全部

• 建立关系

  foreign key(stuid) references stduents(id)

• 外键的级联操作
  再删除students表的数据时，如果这个id值在scores中已经存在，则会抛出异常
  推荐使用逻辑删除，还可以解决这个问题
  可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
  语法
  alter table scores add constraint stu_sco foreign key(stuid) references students(id);
  级联操作的类型包括：

  restrict（限制）：默认值，抛出异常
  cascade（级联）：如果主表的记录删除，则从表中的记录都将被删除
  set null ：将外键设置为空
  no action： 什么都不做

• 连接查询
  select students.name,subjects.title,scores.score from scores inner join students on scores.stuid = students.id inner join subjects on scores.stuid = subjects.id
  连接查询的类型：
  inner join 两个表都匹配的时候出现在结果集
  left join 左表边匹配就出现
  right join 右表有的数据都会出现

• 自关联

• 视图
  对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
  解决：定义视图
  视图本质就是对查询的一个封装
  定义视图

  create view v_stuscore as 
  select students.*,score.* from students inner join score on students.id=score.stuid;

  使用show tables； 会出现建立的视图，就可以使用查询语句进行查询
  create 换成alter是修改

• 事物
  当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
  使用事物可以完成退回的功能，保证业务逻辑的正确性
  事物四大特性（简称ACID）
  原子性（A) 业务中的全部操作在数据库中是不可分割的，要么全部做完，要么都不执行
  一致性（C) 几个并行执行的业务，其执行结果必须与按某一顺序串行执行的结果一致
  隔离性（I） 事物的执行不受其他事物的干扰，事物执行的中间结果对其他事物必须是透明的
  持久性（D） 对于任意已提交事物，系统必须保证该事物对数据库的修改不被丢失，及时数据库出现故障

  要求： 表的类型必须是innodb 或 bdb 类型，才可以对此表使用事物
  查看创表语句：
  show create table students;
  修改表的类型：
  alter table ’表名‘ engine=innodb;

• 索引
  当数据量很大的时候，查找数据就会变得很慢
  索引能提高数据访问性能
  主键和唯一索引，都是索引，可以提高查询速度
  下面在mysql实现索引

  选择列的数据类型

  越少的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快
  简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂
  尽量避免NULL： 应该制定列为NOT NULL ，除非你想存储NULL。 在MYSQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个字符串代替空值

  操作：

  索引分单列索引和组合索引

  查看索引：

  `show index from  table_name;`

  创建索引：

  create index indexname ON
   mytable(username(length))

  删除索引：

  `drop index [indexName] on mytable;`

缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度

开启时间检测：
set profiling=1;
执行查询语句：
…….
查看执行时间：
show profiles;