MySQL

1.简单的DDL

DDL : Data Definition Language 数据库定义语言

比如更改表名

alter table 表名 rename 新表名;

更改字段名

alter table 表名 change 列名 新列名 数据类型;

更改表的列名 和 数据类型 当然数据类型可以不改,但是必须得写,

如 alter table t_teacher change name teacher_name varchar(20);

添加字段

alter table 表名add 列名类型;

如 alter table t_teacher add birthday datetime; 默认添加到尾部

alter table t_teacher add birthday datetime after teacher_name; 把列添加到指定列的后面

alter table t_teacher add sex2 char(2) first; 添加到第一列,需要把表关掉,刷新一下,再打开才能显示出来

删除字段

alter table 表名 drop 列名;

如 alter table t_teacher drop birthday;

更改字段类型(尽量不要更改)

alter table 表名 modify 列名 新数据类型;

如 alter table t_teacher modify sex2 varchar(20);

alter table 表名 modify 列名 数据类型 comment '该列的注释说明';  更改类型的同时,还能添加注释说明

查看建表语句

show create table 表名;

2DDL增强

约束分类

2.1主键 :我们的java类,对应的就是一张表,成员变量对应一个字段,一个类对象对应一条数据,那么对象都有一定的唯一性.主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）

使用方式:

第一种 : 创建表语句时,添加主键约束

 create table person(

        id int ,

        name varchar(100),

        income decimal(18,2),

        primary key (id,name)

    );

上面代码设置了两个主键

 create table person1(

        id int ,

        name varchar(100),

        income decimal(18,2),

        primary key (id)

    );

上面代码设置了一个主键

如果只有一列主键,也可以直接写在字段后面

 create table person2(

        id int primary key,

        name varchar(100) ,

        income decimal(18,2)

      );

第二种 : 创建表完成之后,通过alter添加主键约束

语法 :  alter table 表名 add primary key(列名,列名...);

create table person3(

        id int ,

        name varchar(100),

        income decimal(18,2)

      );

比如要对person3表添加id列主键

 alter table person3 add primary key(id);

2.2主键自增:主键值自己增长

使用方式:

第一种 : 建表时,添加自增

create table person4(

id int auto_increment ,

name varchar(200),

 primary key(id)

);

测试语句 :

insert into person4(name)values('测试');

并未输入id的值,但是可以自动填充

第二种 : 创建表之后,添加自增

语法 : alter table 表名modify 主键列名 类型 auto_increment;

create table person5(

id int ,

name varchar(200),

 primary key(id)

);

alter table person5 modify id int auto_increment;

测试语句 :

insert into person5 (name)values('测试');

并未输入id的值,但是可以自动填充

设置自增的起始值

语法 : alter table 表名auto_increment=值;

create table person6(

id int auto_increment ,

name varchar(200),

 primary key(id)

);

alter table person6 auto_increment=10000;

测试语句 :

insert into person6 (name)values('测试');

Id值从10000开始

2.3外键 

概述:对应java代码来说,外键就是类的关联关系(一个类的成员变量是另外一个类的对象引用)

一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,然后就可以通过这个表中的外键 去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据

外键列的值,必须是关联表中的已有主键值,也可以为空

具体外键中的查询,现在不考虑,到DQL的时候咱们再说,现在子查询都还没讲,所以先了解什么是外键

使用方式:

第一种 : 创建表时添加外键约束

create table teacher(

    id int ,

    name varchar(20),

    primary key (id)

);

create table student (

    id int ,

    name varchar(20),

    teacher_id int ,

    primary key (id),

    foreign key (teacher_id) references teacher(id)

);

注意 : 引用student中添加外键列,指向teacher表,所以必须先创建teacher表才行

测试语句

添加一个讲师

insert into  teacher (id,name) values(1,'张老师');

添加一个学生小明,学生通过teacher_id可以指向张老师

insert into  student (id,name,teacher_id) values(1,'小明',1);

添加一个学生小红,teacher_id没有设置值

insert into  student (id,name) values(2,'小红');

添加一个小黑,teacher_id指向一个不存在的讲师,报错

insert into  student (id,name,teacher_id) values(3,'小黑',2);

第二种 : 创建完表之后,添加外键约束

create table student1 (

    id int ,

    name varchar(20),

    teacher_id int,

    primary key (id)

);

create table teacher1(

    id int ,

    name varchar(20),

    primary key (id)

);

语法 : alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名);

alter table student1 add foreign key (teacher_id) references teacher1 (id);

测试语句

添加一个讲师

insert into  teacher1 (id,name) values(1,'张老师');

添加一个学生小明,学生通过teacher_id可以指向张老师

insert into  student1 (id,name,teacher_id) values(1,'小明',1);

添加一个学生小红,teacher_id没有设置值

insert into  student1 (id,name) values(2,'小红');

添加一个小黑,teacher_id指向一个不存在的讲师,报错

insert into  student1 (id,name,teacher_id) values(3,'小黑',2);

2.4唯一约束unique

概述:

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

唯一约束不允许出现重复的值，但是可以为多个null.

使用方式:

第一种 : 创建表时,添加unique约束

   create table temp (

    id int ,

    `name` varchar(20),

    unique(id)

);

或

   create table temp (

    id int  unique ,

    `name` varchar(20)

);

添加一条没有id的数据

insert into temp (name)values('张三');

再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束)

insert into temp (name)values('李四');

添加一条id为1 的数据

insert into temp (id,name)values(1,'王五');

再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复

insert into temp (id,name)values(1,'赵六');

第二种 : 创建表之后,添加unique约束

   create table temp1 (

    id int ,

    `name` varchar(20)

);

alter table temp1 add unique (id);

添加一条没有id的数据

insert into temp1 (name)values('张三');

再添加一条没有id的数据,可以添加(唯一约束,又不是不为空约束)

insert into temp1 (name)values('李四');

添加一条id为1 的数据

insert into temp1 (id,name)values(1,'王五');

再添加一条id为1的数据,报错,因为已经有了id为1了,不可重复

insert into temp1 (id,name)values(1,'赵六');

2.5  非空约束 not null与 默认值 default 

概述:

所有的类型的值都可以是null，包括int、float 等数据类型,设置为not null的字段,必须填入数据

经常和default一起使用,当不填写数据的时候,把默认值设置成指定的值

使用方式:

第一种 : 创建表时,添加约束

create table temp2(

    id int not null,

    `name` varchar(30) default  'abc',

sex varchar(10) not null default '男'

);

测试语句 :

只添加id值,可以,因为name和sex都有默认值

insert into temp2 (id) values (1);

如果设置了值,默认值就不再设置

insert into temp2 (id,name,sex) values (2,'张三','女');

注意 : 没有添加id的值,而id又设置不能为空,并且也没有默认值,所以报错

insert into temp2 (name,sex) values ('李四','女');

第二种 : 创建表之后,添加约束

语法 : alter table 表名 modify 列名 数据类型 not null  default 默认值;

create table temp3(

    id int,

    `name` varchar(30) ,

sex varchar(10)

);

alter table temp3 modify id int not null ;

alter table temp3 modify name  varchar(30)   default  'abc';

alter table temp3 modify sex varchar(10) not null  default '男';

测试语句 :

只添加id值,可以,因为name和sex都有默认值

insert into temp3 (id) values (1);

如果设置了值,默认值就不再设置

insert into temp3 (id,name,sex) values (2,'张三','女');

没有添加id的值,而id又设置不能为空,并且也没有默认值,所以报错

insert into temp3 (name,sex) values ('李四','女');

3.1基础Dql

DQL : Data Query Language，数据查询语言,主要用于查询表。

它通常用来从一张表或者多张表（视图或者子查询等）中按指定的条件筛选出某此记录。涉及到的命令有select。

语法 :

select 列限定 from 表限定 where 行限定;

示例代码 :

create table teacher(

id int,

`name` varchar(30)

);

insert into teacher (id,name) values (1,'张老师');

insert into teacher (id,name) values (2,'王老师');

最简单粗暴的一个DQL :

select * from teacher;

会查询到teacher表中所有的数据

如果想查看所有行的name

select name from teacher;

如果想查看id为1的讲师姓名

select name from teacher where id = 1;

3.2  条件判断

上面一个简单的DQL查询时,我们使用到了where行限定,后面写的是id=1

但是如果这一个条件不满足我们判断的条件呢?比如 id=1 且 name=’张老师’ 就怎么怎么样

所以我们的条件判断,是用于where后面的

示例代码 : 

create table student(

id int,

`name` varchar(30),

score decimal(18,2)

);

insert into student(id,name,score) values (1,'张三',99.2);

insert into student(id,name,score) values (2,'李四',97.9);

insert into student(id,name,score) values (3,'王五',98);

insert into student(id,name) values (4,'赵六');

insert into student(id,name,score) values (5,'小明',98);

and

且,和,的意思,一般用于 必须符合两个添加的判断,等同于java中的 &&

语法 :

select 列限定 from 表限定 where A表达式 and B表达式;

如 : 查询学生表中,name是张三且成绩大于90分

select * from student where name='张三' and score > 90;

只会查询出符合两个条件的学生

or

或的意思,一般用于 符合一个添加判断的情况下,等同于java中的 ||

语法 :

select 列限定 from 表限定 where A表达式 or B表达式;

如 : 查询学生表中,name是张三 或 成绩大于90分

select * from student where name='张三' or score > 90;

只要符合两个条件中的任何一个条件,就可以

注意 : 如果 一个语句中,同时出现了and和or的话,and优先级高

关系表达式

> , >= , <  , <= ,<>,=

> : 大于

< : 小于

>= : 大于等于

<= : 小于等于

= : 相等

<> : 不等于

注意 : = 和 <> 额外留意,和java中有所不同,java中判断相等用 == , 这里只用 = , java中判断不相等用 != , 这里使用 <>

如 : 查询学生表中,成绩大于90分的

select * from student where  score > 90;

如 : 查询学生中,成绩为空的学生

错误 判断为空不能使用 = null ,应该使用 is null

select * from student where score = null;

select * from student where  score is null;

如 : 查询学生中,成绩不为空的学生

错误 判断不为空 不能使用 <>null,应该使用 is not null

select * from student where  score  <> null;

select * from student where  score is not  null;

注意 : 判断是否为空,应该使用is null,而不是 = null , 同理,判断不为空应该使用 is not null ,而不是 <>null,并且and和or同时出现的话,and优先级比or要高

between  and

在...之间

语法 :

select 列限定 from 表限定 where 列名 between 值1 and 值1;

如 : 查询学生表中 成绩在98到100之间 (包含98和100)

select * from student where score >= 98 and score<=100;  

等价于

select * from student where score between 98 and 100;

In

在指定数据中

语法 :

select 列限定 from 表限定 where 列名 in(值1,值2....);

如 : 给出一个数据集合(1,3,10,20),获取学生id在这个数据集合中的学生信息

select * from student where id in (1,3,10,20);

模糊查询like

我们经常会用到搜索功能,比如百度,搜索功能实现,就是使用like模糊查询技术点

其中 % 匹配任意个数的任意字符

 _ 匹配单个任意字符

语法 :

select 列限定 from 表限定 where 列名 like  '值' ;

如 : 把name中,把姓张的查询出来

select * from student where  name like '张%';

如 : 把 name中,姓名有两个字的查询出来

select * from student where  name like '__';

4Order by 排序

语法 :

select 列限定 from 表限定 order by 列名 asc/desc;

Asc : 升序

Desc : 降序

Limit

语法 :

select 列限定 from 表限定 limit 条数;

select 列限定 from 表限定 limit 开始值(不包含) ,条数;

单表查询(组函数)

MYSQL中有一类特殊的函数，用于统计，或者分组统计,

分组关键字使用 group by

常用组函数有 :

count(*) : 总条数

max(字段名) : 最大值

min(字段名) : 最小值

avg(字段名) : 平均值

sum(字段名) : 总和

语法 :

select  count(*),max(字段名),min(字段名)... from 表名 group by 字段名;

子查询

场景一 : select后面

语法 :

select 字段名,(查询语句) from 表名;

当位于SELECT后面时，要注意

1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）

2.子查询中只能有一个字段（子查询的结果必须是一行一列）

使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.

场景二 : from后面

当位于FROM后面时，要注意

1.我们可以把子查询当成一张表

2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

场景三 : where后面

当位于WHERE后面时，要注意

多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）