MYSQL应用day02

一 DDL（数据定义语言） 基础拓展

DDL 基础有 create,drop alter

Alter：

更改表明：

alter table 表明 rename 新表名

更改字段名 类型：

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

添加字段

alter table 表名 add 列名类型；

删除字段

alter table 表名 drop 列名

更改字段类型（尽量不要更改）

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

修改数据库字符集

alter database 数据库名 character set utf8;

！！！需要重启mysql 服务，才能生效

修改表字符集

alter table 表名 character set utf8 collate utf8_general_ci

二   DDL 增强

约束分类

 1主键

主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。

主键设置可以划分为两种

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

create table person(
    id int ,
    name varchar(223),
    primary key(id,name)
)

create table person2(

    id int primary key,
    age int
)

第二种 : 创建表完成之后,通过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主键自增

所谓自增,望文知意,就是自动增加,不用我们输入值但是自增的列,必须为主键列,关键字 auto_increment

键列,关键字 auto_increment

设置自增的两种方式 :

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

create table person7(

  id int auto increment,
    name varchar(222),
    
)

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

create table person5(
	id int ,
	name varchar(200),
	 primary key(id)
);
alter able person5 modify id int auto increment;

3 设置自增的起始值

 语法： alter table  表名 auto_increment = 值；

4外键

常用于有关联关系的两个表中，外键的值，必须是关联表中的已有主键值，也可以为空

设置外键有两种方式 :

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

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 表才行

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

​
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 student1 add foreign key (teacher_id) references teacher1 (id);

5 唯一约束 unique

一约束是指定table的列或列组合不能重复，保证数据的唯一性,唯一约束不允许出现重复的值，但是可以为多个null.

设置unique约束有两种方式 :

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

create table temp (
    id int ,
    `name` varchar(20),
    unique(id)
);
或者
   create table temp (
    id int  unique ,
    `name` varchar(20)
);

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

  create table temp1 (
    id int ,
    `name` varchar(20)
);
alter table temp1 add unique (id);

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

设置not null 与 default有两种方式 :

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

create table temp2(
    id int not null,
    `name` varchar(30) default  'abc',
	sex varchar(10) 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 '男';

二   条件判断 

  条件判断，用于where 后面的

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

语法： 

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

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

语法：

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

注意： 如果一个语句中 同时出现了 and  和or ， and 优先级更高

3关系表达式

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

> : 大于

< : 小于

>= : 大于等于

<= : 小于等于

= : 相等

<> : 不等于

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

4 between   and 

在...之间

语法 :

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

In 

在指定数据中

语法 :

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

5 模糊查询 like 

语法 :

select 列限定 from 表限定 where 列名 like  '值' ;     %匹配任意个数的任意字符

6 Order by 排序

语法：

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

Asc : 升序

Desc : 降序

7 Limit： 限制条数,通常和order by一起使用,

语法 :

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

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

三 单表查询

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

分组关键字使用 group by

常用函数：

count(*) : 总条数

max(字段名) : 最大值

min(字段名) : 最小值

avg(字段名) : 平均值

sum(字段名) : 总和

1语法：

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

如 : 查看学生表共有多少学生

select  count(*)  from   student;

如 : 查看学生表中分数大于90分的有多少学生

select  count(*)  from   student where score > 90;

2Group 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;

3 Having    过滤

select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60

Union与 union all        

        

合并查询,合并查询的结果

        Union 会去除重复项

        Union all 不会去除重复项

1        如 : 查询出 学生分数大于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的时候要注意：

1.参与合并的表，它们SELECT出来的字段数量必须一致（强制规则）

2.参与合并的表，它们SELECT出来的字段的类型建议一一对应（非强制，但是最好遵循这条规则）

3.参与合并的表，它们SELECT出来的字段的顺序建议一致（非强制，但是最好遵循这条规则）

常用函数（红色为常用函数）

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 ;