DML操作
DML : Data Manipulate Language(数据操作语言) ,主要用于向数据库插入,修改,删除数据使用
涉及的关键字有 : insert delete update
Insert
insert into 表名 (列名1,列名2) values (值1,值2);
Delete
delete from 表名 where 列名 = 值;
Update
update 表名 set 列名1=值 , 列名2=值 where 列名 = 值;
Select(属于DQL)
select 列限定 from 表限定 where 行限定
SQL,它的全称叫Structured Query Language,结构化的查询语言。之所以出现这个东西,是为了统一/屏蔽不同数据库厂商生产的数据库产品之间的差异。
SQL定义了一系列标准和规范,数据库厂商也需要按照这个规范来,当然会有一些细小的差别,相比没有规范来说,要好很多
DDL操作
DDL : Data Definition Language 数据库定义语言
涉及的关键字 : create drop alter show desc
比如更改表名
alter table 表名 rename 新表名;
更改字段名
alter table 表名 change 列名 新列名 数据类型;
更改表的列名 和 数据类型 当然数据类型可以不改,但是必须得写,
添加字段
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 '该列的注释说明'; 更改类型的同时,还能添加注释说明
创建表添加注释
create table 表名 (列名 类型 comment ‘注释’);
查看建表语句
show create table 表名;
如果没有再创建,不加if not exists 如果存在,就会报错
create table if not exists t_user (id int);
如果有再删除,不加 if exists ,如果没有 会报错
drop table if exists table
约束分类
主键
建表时增加主键约束
create table person(
id int ,
name varchar(100),
income decimal(18,2),
primary key (id,name)
);
create table person2(
id int primary key,
name varchar(100) ,
income decimal(18,2)
);
建表后添主键
alter table person3 add primary key(id);
自增
建表时设置自增
create table person4(
id int auto_increment ,
name varchar(200),
primary key(id)
);
建表后设置自增
alter table person5 modify id int auto_increment;
设置自增起始值
alter table person6 auto_increment=10000;
外键
一个表中的外键列,需要参照另一个表的主键值生成,并且一个表的外键列的值和另一个表的主键值的数据类型必须一致,
然后就可以通过这个表中的外键 去找另一个表的主键,能找到主键就能根据主键找到对应的一行数据
常用于有关联关系的两个表中
外键列的值,必须是关联表中的已有主键值,也可以为空
建表时添加外键
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)
);
foreign key (当前表的外键列) references 哪个表(对应表中的哪个列)
建表后添加外键
alter table 表名 add foreign key (外键列列名) references 指向的表名 (主键列列名);
alter table student1 add foreign key (teacher_id) references teacher1 (id);
唯一
建表时添加唯一约束
create table temp (
id int ,
`name` varchar(20),
unique(id)
);
或
create table temp (
id int unique ,
`name` varchar(20)
);
建表后设置唯一
alter table temp1 add unique (id);
非空与默认值
建表时设置
create table temp2(
id int not null,
`name` varchar(30) default 'abc',
sex varchar(10) not null default '男'
);
建表后设置
alter table 表名 modify 列名 数据类型 not null default 默认值;
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 '男';
DQL
DQL : Data Query Language,数据查询语言,主要用于查询表。
它通常用来从一张表或者多张表(视图或者子查询等)中按指定的条件筛选出某此记录。涉及到的命令有select。
select 列限定 from 表限定 where 行限定;
条件判断
and
且,和,的意思,一般用于 必须符合两个添加的判断,等同于java中的 &&
语法 :
select 列限定 from 表限定 where A表达式 and B表达式;
or
或的意思,一般用于 符合一个添加判断的情况下,等同于java中的 ||
语法 :
select 列限定 from 表限定 where A表达式 or B表达式;
注意 : 如果 一个语句中,同时出现了and和or的话,and优先级高
select * from student where id = 2 or name = ‘张三’ and score >= 98;
关系表达式
> , >= , < , <= ,<>,=
> : 大于
< : 小于
>= : 大于等于
<= : 小于等于
= : 相等
<> : 不等于
注意 : = 和 <> 额外留意,和java中有所不同,java中判断相等用 == , 这里只用 = , java中判断不相等用 != , 这里使用 <>
错误 判断为空不能使用 = null ,应该使用 is null
select * from student where score is null;
错误 判断不为空 不能使用 <>null,应该使用 is not 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;
In
在指定数据中
语法 :
select 列限定 from 表限定 where 列名 in(值1,值2....);
模糊查询like
我们经常会用到搜索功能,比如百度,搜索功能实现,就是使用like模糊查询技术点
其中 % 匹配任意个数的任意字符
_ 匹配单个任意字符
语法 :
select 列限定 from 表限定 where 列名 like '值' ;
Order by 排序
排序,望文知意,能够让我们查询的数据进行排序展示
语法 :
select 列限定 from 表限定 order by 列名 asc/desc;
Asc : 升序
Desc : 降序
如 : 查询所有学生信息,以成绩降序
select * from student order by score desc;
DISTINCT 去除重复
select DISTINCT score from student order by score desc ;
Limit
限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名
语法 :
select 列限定 from 表限定 limit 条数;
select 列限定 from 表限定 limit 开始值(不包含) ,条数;
单表查询(组函数)
分组关键字使用 group by
常用组函数有 :
count(*) : 总条数
max(字段名) : 最大值
min(字段名) : 最小值
avg(字段名) : 平均值
sum(字段名) : 总和
Group by
如 : 查询每个老师分别带了多少学生(显示老师id即可)
select teacher_id, count(*) as stu_count from student group by teacher_id;
Having
刚才我们使用group by 和 组函数,可以对数据进行分组查询,并且也可以查询到平均值等数据
但是有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值 大于60分的平均分数,这时候用where就不行了
这个时候就需要使用having进行过滤
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;
先执行group by 在执行 having 最后执行 order by
Case
类似于java中的switch
– 不改变表结构的情况下,对分数进行等级分类
– 小于60分就是差 , 大于等于60小于80 就是良, 大于等于80 就是优
select id,name,score ,
CASE
when score < 60 then '差'
when score >=60 and score <80 then '良'
when score >= 80 then '优'
end as '等级'
from test_01;
CASE rank
when '优' then 'A'
when '良' then 'B'
when '差' then 'C'
end as x
子查询
子查询又叫嵌套查询。它通常可以位于SELECT后面 FROM后面 WHERE后面,共三种使用场景。当我们查询一个表没有办法实现功能的时候,就需要使用子查询
上面我们讲到了分组查询,可以查询每个老师所带学生的最低分,
但是我们刚才查出来之后,我们只能看到teacher_id,但是我们并不知道teacher_id对应的是那个老师,这个时候我们最好是显示老师的名字是比较好的,可以用子查询实现
场景一 : select后面
语法 :
select 字段名,(查询语句) from 表名;
如 : 查询所有学生的信息并显示老师的名字
select *,(select name from teacher where id=teacher_id ) as teacher_name from student ;
如 : 查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字
select max(score),min(score),sum(score),avg(score),count(*),(select name from teacher where id=teacher_id ) as teacher_name from student group by teacher_id ;
注意 :
当位于SELECT后面时,要注意
1.一定要在两个表之间找好对应关系(teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的)
2.子查询中只能有一个字段(子查询的结果必须是一行一列)
使用子查询的时候,建议大家养成使用别名的好习惯,这样可以让我们的查询语句更加清晰。别名可以用来命令新字段,也可以用来命名新表.
场景二 : from后面
还是学生表student,我们要将成绩进行分级,并且显示汉字的分级与字母的分级。这里可以使用子查询。相当于给student“新增”了2个字段
如 : 使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,score>=60 且 score<80 评级B并且是良,score>=80 评级是A并且是优
select *,
case rank
when 'A' then '优'
when 'B' then '良'
when 'C' then '差'
end rank_ch
from (
select *,
case
when score < 60 then 'C'
when score >=60 and score <80 then 'B'
when score >=80 then 'A'
end as rank
from student
) a;
注意 :
当位于FROM后面时,要注意
1.我们可以把子查询当成一张表
2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作
场景三 : where后面
如 : 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in
(select id from teacher where name='张老师');
注意 :
当位于WHERE后面时,要注意
1.多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2.如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列
3.子查询中的SELECT后面只能有一个字段(多个字段的话会报错)
Union与 union all
合并查询,合并查询的结果
Union 会去除重复项
Union all 不会去除重复项
union / union all
它俩的作用是把两张表或者更多表合并成一张表
前者会去重(去重的依据是,UNION时SELECT出来的字段如果对应相等则认为是同一条记录,这的逻辑我们可以参考Java equals)
但是or 尽管两行数据每个字段都相等,也不会去重
后者则不会去重,它会保留两张表中的所有记录,但是它性能高(因为去重操作要花时间),
尽量使用union all,把去重这个工作交给代码去完成,这样可以减少MYSQL服务器的压力
使用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 ;