关于MYSQL的应用

1  简单DML应用

create table teacher (
            id int,
        `name` varchar(20),
        salary decimal(18,2)
)ENGINE = innodb default charset = utf8;

更改表名
   alter table teacher rename t_teacher;
更改字段
    alter table t_teacher change name teacher_name varchar(20); 
添加字段
    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 t_teacher drop birthday;
更改字段类型
    alter table t_teacher modify sex2 varchar(20);
    alter table t_teacher modify sex2 varchar(20) comment '该列的注释说明';  
    更改类型的同时,还能添加注释说明
查看建表语句
    show create table t_teacher;

2  主键约束

第一种 : 创建表语句时,添加主键约束
 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);

 删除创建的测试表
    drop table person;
    drop table person1;
    drop table person2;
    drop table person3;
 

3  主键自增

第一种 : 建表时,添加自增
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('测试');

设置自增的起始值
语法 : 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开始

删除测试表
drop table person4;
drop table person5;
drop table person6;
 

4  外键约束

对应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);

5  unique约束

第一种 : 创建表时,添加unique约束
   create table temp (
    id int ,
    `name` varchar(20),
    unique(id)
);
添加一条没有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,'赵六');

6  非空和默认值约束

第一种 : 创建表时,添加约束
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 ('李四','女');

7 基础的DQL

示例代码 : 
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;

8  条件判断

示例代码 : 
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;
只要符合两个条件中的任何一个条件,就可以

关系表达式 
> , >= , <  , <= ,<>,=
> : 大于
< : 小于
>= : 大于等于
<= : 小于等于
= : 相等
<> : 不等于
注意 : = 和 <> 额外留意,和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
between  and
在...之间
语法 : 
select 列限定 from 表限定 where 列名 between 值1 and 值1;
如 : 查询学生表中 成绩在90到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 '__';
 

9  排序和limit

    语法 : 
select 列限定 from 表限定 order by 列名 asc/desc;
Asc : 升序
Desc : 降序
如 : 查询所有学生信息,以成绩降序
select * from student order by score desc;
如 : 查询所有学生信息,按成绩降序,如果成绩相同,按照id升序
select * from  student order by score desc , id asc;
Limit
限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名
语法 : 
select 列限定 from 表限定 limit 条数;
select 列限定 from 表限定 limit 开始值(不包含) ,条数;
如 : 查询学生表,分数前三名的信息
select * from  student order by score desc limit 3;
如 : 查询学生表,分数第二名和第三名
select * from  student order by score desc limit 1,2;

10  组函数和过滤

分组关键字使用 group by
常用组函数有 : 
count(*) : 总条数
max(字段名) : 最大值
min(字段名) : 最小值
avg(字段名) : 平均值
sum(字段名) : 总和
示例数据
create table student (
    id int ,
    name varchar(20),
    teacher_id int,
    score decimal(18,2) ,
    primary key (id)
);
create table teacher(
    id int ,
    name varchar(20),
    primary key (id)
);
insert into teacher (id,name)values(1,'张老师');
insert into teacher (id,name)values(2,'王老师');
insert into student (id,name,teacher_id,score)values(1,'张三',1,90);
insert into student (id,name,teacher_id,score)values(2,'李四',2,88.9);
insert into student (id,name,teacher_id,score)values(3,'王五',1,45.7);
insert into student (id,name,teacher_id,score)values(4,'赵六',1,84);
insert into student (id,name,teacher_id,score)values(5,'小明',2,92.5);
insert into student (id,name,teacher_id,score)values(6,'小红',2,47);
语法 : 
select  count(*),max(字段名),min(字段名)... from 表名 group by 字段名;
如 : 查看学生表共有多少学生
select  count(*)  from   student;
如 : 查看学生表中分数大于90分的有多少学生
select  count(*)  from   student where score > 90;
Group by 
如 : 查询每个老师分别带了多少学生(显示老师id即可)
select teacher_id, count(*) as stu_count  from student group by teacher_id;
如 : 查询每个老师带的学生中的最高分数
select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id;
如 : 查询每个老师所带学生的总成绩与平均分
select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;
Having
刚才我们使用group by 和 组函数,可以对数据进行分组查询,并且也可以查询到平均值等数据
但是有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值 大于60分的平均分数,这时候用where就不行了
select teacher_id, avg(score) as avg from student group by teacher_id where avg > 60;
这个时候就需要使用having进行过滤
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;
 

11· 常用函数

select version() ;显示当前MySQL软件的版本
select database();显示当前所处数据库是哪个
select  char_length('中国');返回字符个数。
select  length('中国');返回字符所占字节数,MySQL中，一个UTF8编码的汉字占3个字节
select  concat(  'a',  'b',  'c',  'd');返回  'abcd'。字符串拼接函数
select  concat_ws(  '=',  'a',  'b',  'c');返回  'a=b=c'。字符串拼接函数,第一个是拼接间隔符
select   upper('abcd');返回ABCD。将参数中所有小写字母转换为大写
select  lower('ABCD');返回abcd。将参数中所有大写字母转换为小写
select  substring(  '系统信息类',  1,  3  );返回  系统信。第2个参数代表从1开始的第几个字符，第3个参数代表截取字符个数
select  trim('  abc  ');返回 abc。用于删去参数左右的所有空格
select  curdate();返回当前日期
select  curtime();返回当前时间
select  now();返回当前日期时间
select  unix_timestamp();返回当前日期时间对应的时间戳（单位秒）
select  unix_timestamp('2018-05-24 20:00:00');返回参数指定的日期时间对应的时间戳（单位秒）
select  from_unixtime(1527163397);返回参数指定时间戳（单位秒）对应的日期时间
select  datediff(  '2018-05-23',  now()  );返回两个参数对应日期相差的天数（用第一个参数减第二个参数）
select  adddate( now(), -2 );返回指定天数前/后的日期时间（第一个参数是日期时间，第二个参数是天数，向后加是正数，向前减是负数）
select year('2019-02-24');返回2019 获得年份
select month('2019-02-24')  返回2 获得月份
select day('2019-02-24')  返回 24 获取日
select  if(  <判断条件>,  <条件为真时的返回值>,  <条件为假时的返回值>  );相当于Java中的三目运算符<判断条件>  ?  <条件为真的返回值>  :  <条件为假的返回值>。
如select  if(1=1,  2,  3);返回2。
select  ifnull(<表达式或者字段>,  <表达式或者字段为NULL时的返回值>);通常用于给有可能有NULL的情况下的提供默认值。
select ifnull(null,'无名氏') ; null这里可以写列名 就会把该列值为null的 以无名氏显示
select ifnull(name,'无名氏') from teacher ;