数据库day02

MYSQL应用
DDL-基础，扩展
先回忆一下，之前学的DDL基础create ,drop,还有一个 alter。
Alter：
更改表名
DDL-基础，扩展
先回忆一下，之前学的DDL基础create ,drop,还有一个 alter。
Alter：
更改表名
alter table 表名 rename 新表名;
alter table student rename student_info;

更改字段名，类型
alter table 表名 change 列名 新列名 数据类型;
alter table student_info change student_name student_name int;

添加字段
alter table 表名add 列名类型;
alter table student_info add student_age int;

删除字段
alter table 表名 drop 列名;

更改字段类型(尽量不要更改)
alter table 表名 modify 列名 新数据类型;

修改数据库字符集
alter database 数据库名 character set utf8
alter database 111 character set utf8

注意：需要重启mysql服务，才能生效

修改表字符集
Alter table 表名 character set utf8 collate utf8_general_ci
修改列的字符集

批量修改已存在列的字符集
https://blog.csdn.net/vfsdfdsf/article/details/90484891
DDL-增强
1.约束
约束的分类

实体完整性(主键)
我们的java类,对应的就是一张表,成员变量对应一个字段,一个类对象对应一条数据,那么对象都有一定的唯一性
比如判断对象是否相等,我们通常使用equals()方法和hashCode()方法,那么怎么在数据库中表示数据的唯一性呢?主键
主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。
主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）
主键设置可以划分为两种
第一种 : 创建表语句时,添加主键约束
第二种 : 创建表完成之后,通过alter添加主键约束
下面是使用方式
第一种 : 创建表语句时,添加主键约束
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);
演示示例：主键约束

主键自增
上面我们已经对表添加了主键,主键值不能为空且不能重复,那么问题来了…
如果主键的值让客户输入的话,很容易就重复了,比如888,666等数字大家都喜欢使用,导致一直输入不正确,非常不方便
所以又有了自增的概念,所谓自增,望文知意,就是自动增加,不用我们输入值
但是自增的列,必须为主键列,关键字 auto_increment
设置自增的两种方式 :
第一种 : 建表时,添加自增
第二种 : 创建表之后,添加自增
下面是使用方式
第一种 : 建表时,添加自增
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开始
演示示例：主键自增

关联完整性(外键)
对应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);

演示示例：外键约束
唯一约束unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null.
设置unique约束有两种方式 :
第一种 : 创建表时,添加unique约束
第二种 : 创建表之后,添加unique约束
下面是使用方式
第一种 : 创建表时,添加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,‘赵六’);

演示示例：UNIQUE约束

非空约束 not null与 默认值 default
所有的类型的值都可以是null，包括int、float 等数据类型,设置为not null的字段,必须填入数据
经常和default一起使用,当不填写数据的时候,把默认值设置成指定的值
设置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 (‘李四’,‘女’);
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;
如 : 查询学生中,成绩不为空的学生
错误 select * from student where score>90应该使用 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 值2;
In
在指定数据中
语法 :
select 列限定 from 表限定 where 列名 in(值1,值2…);
模糊查询like
我们经常会用到搜索功能,比如百度,搜索功能实现,就是使用like模糊查询技术点

其中 % 匹配任意个数的任意字符
_ 匹配单个任意字符
语法 :
select 列限定 from 表限定 where 列名 like ‘值’ ;

如果想要查询 _ 或者 % 需要转义 % _

演示示例：条件判断

课堂练习2：条件判断
Order by 排序
排序,望文知意,能够让我们查询的数据进行排序展示
语法 :
select 列限定 from 表限定 order by 列名 asc/desc;
Asc : 升序
Desc : 降序
Limit
限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名
语法 :
select 列限定 from 表限定 limit 条数;
select 列限定 from 表限定 limit 开始值(不包含) ,条数;
演示示例：排序和LIMIT
3. 单表查询(组函数)
MYSQL中有一类特殊的函数，用于统计，或者分组统计,
分组关键字使用 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
查询老师所带学生的平均分数大于75分的老师ID和平均分数？

刚才我们使用group by 和 组函数,可以对数据进行分组查询,并且也可以查询到平均值等数据
但是有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值 大于60分的平均分数,这时候用where就不行了
select teacher_id, avg(score) as avg from student where avg > 60 group by teacher_id;
这个时候就需要使用having进行过滤
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;

演示示例：组函数和过滤

5. Union与 union all
   合并查询,合并查询的结果
   Union 会去除重复项
   Union all 不会去除重复项
   如 : 查询出 teacher_id = 1 的所有学生信息
   select * from student where teacher_id=1;
   如 : 查询出 学生分数大于60的所有学生信息
   select * from student where score > 60;
   如 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)
   // 用 or 实现
   select * from student where teacher_id=1 or score > 60;
   // 用 union实现
   select * from student where teacher_id=1
   union
   select * from student where score > 60;
   如 : 查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)
   select * from student where teacher_id=1
   union all
   select * from student where score > 60;

总结和注意 :
union / union all
它俩的作用是把两张表或者更多表合并成一张表
前者会去重（去重的依据是，UNION时SELECT出来的字段如果对应相等则认为是同一条记录，这的逻辑我们可以参考Java equals）
但是or 尽管两行数据每个字段都相等,也不会去重
后者则不会去重，它会保留两张表中的所有记录，但是它性能高（因为去重操作要花时间），
尽量使用union all，把去重这个工作交给代码去完成，这样可以减少MYSQL服务器的压力

使用union / union all的时候要注意：
1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）
2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）
3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）
演示示例：子查询

6. 常用函数
   Select upper() from student
   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 ;

select concat(upper(substring(name,1,1)),lower(substring(name,2,char_length(name)-1)) )from student
演示示例：常用函数