数据库操作、表操作、数据操作

数据库操作：

* 创建数据库

create database 数据库名 charset=utf8;

* 修改数据库编码格式

alter database 数据库名 character set utf8;

* 查看所有数据库

show databases;

* 删除数据库

drop database 数据库名;

* 切换数据库

use 数据库名;

* 查看当前选择的数据库

select database();

表操作：

* 查看当前数据库中所有表  show tables;


* 创建表  auto_increment表示自动增长

image.png

* 修改表

alter table 表名 add|change|drop 列名类型;
如： alter table students add birthday datetime;
alter table students change birthday day datetime;

* 删除表

drop table 表名;

* 查看表结构

desc 表名;

* 更改表名称

rename table 原表名 to 新表名;

* 查看表的创建语句

show create table '表名';

数据操作:
* 查
select * from 表名

* 增加

全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
同时插入多条数据：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;

注* 主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

* 修改

update 表名 set 列1=值1,... where 条件

* 删除

delete from 表名 where 条件

逻辑删除，本质就是修改操作update
alter table students add isdelete bit default 0;
如果需要删除则
update students set isdelete=1 where ...;

逻辑删除
* 对于重要数据，并不希望物理删除，一旦删除，数据无法找回
* 一般对于重要数据，会设置一个isDelete的列，类型为bit，表示逻辑删除
* 大于大量增长的非重要数据，可以进行物理删除
* 数据的重要性，要根据实际开发决定

消除重复行
* 在select后面列前使用distinct可以消除重复的行
select distinct 字段名 from 表名;

条件
* 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
* 语法如下：
select * from 表名 where 条件;

比较运算符
* 等于=
* 大于>
* 大于等于>=
* 小于<
* 小于等于<=
* 不等于!=或<>
* 查询编号大于3的学生

select * from 表名 where 条件;

* 查询编号不大于4的科目

select * from 表名 where id<=4;

* 查询姓名不是“黄蓉”的学生

select * from students where sname!='黄蓉';

* 查询没被删除的学生

select * from students where isdelete=0;

逻辑运算符
* and
* or
* not
* 查询编号大于3的女同学
select * from students where id>3 and gender=0 ;

* 查询编号小于4或没被删除的学生

select * from students where id<4 or isdelete=0;

模糊查询
* like
* %表示多个任意字符
* _表示一个任意字符

查询姓黄的学生

select * from students where name like '黄%';

查询姓黄并且名字是一个字的学生

select * from 表名 where 字段名 like '黄_';

 查询姓黄或叫靖的学生

select * from students where sname 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;

* 查询学生是3至8的男生

select * from students where id between 3 and 8 and gender=1;

空判断
* 注意：null与‘’是不同的
* 判空is null
* 查询没有填写地址的学生
select * from students where hometown is null;

* 判非空is not null
* 查询填写了地址的学生

select * from students where hometown is not null;

* 查询填写了地址的女生

select * from students where hometown is not null and gender=0;

优先级
* 小括号，not，比较运算符，逻辑运算符
* and比or先运算，如果同时出现并希望先算or，需要结合()使用

聚合函数：
为了快速得到统计数据，提供了5个聚合函数
count(*)表示计算总行数，括号中写星与列名，结果是相同的
max(列)表示求此列的最大值
min(列)表示求此列的最小值
sum(列)表示求此列的和
avg(列)表示求此列的平均值

排序：
* 默认按照列值从小到大排列
* asc从小到大排列，即升序
* desc从大到小排序，即降序
查询未删除男生学生信息，按学号降序
select * from students where gender=1 and isdelete=0 order by id desc;

分页：
* 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
* 语法：select * from 表名limit start,count
* 从start开始，获取count条数据
* start索引从0开始

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

外键
如果一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键。

一张表中可以有多个外键。

外键的默认作用有两点：

1.对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败。

2.对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。

使用外键的前提：

1. 表储存引擎必须是innodb，否则创建的外键无约束效果。

2. 外键的列类型必须与父表的主键类型完全一致。

3. 外键的名字不能重复。

4. 已经存在数据的字段被设为外键时，必须保证字段中的数据与父表的主键数据对应起来。

连接查询
* 连接查询分类如下：
* 表A inner join 表B：表A与表B匹配的行会出现在结果中
* select * from 表1 inner join 表2 on 外键=主键;
* 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
* select * from 表1 left join 表2 on 外键=主键;
* 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
* 在查询或条件中推荐使用“表名.列名”的语法
* 如果多个表中列名不重复可以省略“表名.”部分
* 如果表的名称太长，可以在表名后面使用' as 简写名'或' 简写名'，为表起个临时的简写名称

子查询(嵌套查询)
select * from 表 where 字段 in (select 字段 from 表 where 条件)

查询支持嵌套使用

日期时间函数
* 获取子值，语法如下
* year(date)返回date的年份(范围在1000到9999)
* month(date)返回date中的月份数值
* day(date)返回date中的日期数值
* hour(time)返回time的小时数(范围是0到23)
* minute(time)返回time的分钟数(范围是0到59)
* second(time)返回time的秒数(范围是0到59)
select date_format('2016-12-21','%Y %m %d');
* 当前日期current_date()
select current_date();

* 当前时间current_time()

select current_time();

* 当前日期时间now()
      select now();

事务
* 当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
* 使用事务可以完成退回的功能，保证业务逻辑的正确性
* 事务四大特性(简称ACID)

    * 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
    * 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
    * 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
    * 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
* 要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务
     * 事务语句
                   开启begin;
                   提交commit;
                   回滚rollback;

数据库操作、表操作 、数据操作

数据库操作、表操作、数据操作