MySQL基础篇 | union、limit、DDL、DML、约束

追求适度,才能走向成功;人在顶峰,迈步就是下坡;身在低谷,抬足既是登高;弦,绷得太紧会断;人,思虑过度会疯;水至清无鱼,人至真无友,山至高无树;适度,不是中庸,而是一种明智的生活态度。

导读:本篇文章讲解 MySQL基础篇 | union、limit、DDL、DML、约束,希望对大家有帮助,欢迎收藏,转发!站点地址:www.bmabk.com,来源:原文

✅作者简介:大家好我是@每天都要敲代码,希望一起努力,一起进步!
📃个人主页:@每天都要敲代码的个人主页
🔥系列专栏:MySQL专栏

目录

一:union的使用

二:limit及通用分页SQL(重要)

三:表结构(DDL)

1. 创建表(creat)

2. 修改表(alter)

3. 删除表(drop)

四:处理表中数据(DML)

1. 插入表中的数据 insert

2. 修改表中的数据 update

3. 删除表中的数据 delete & truncate

五:约束

1. 非空约束 not null

2. 唯一约束 unique

3. 主键约束 primary key 

4.  外键约束  foreign key


一:union的使用

union:可以将查询结果集相加,合并集合!

(1)查询job包含MANAGER和包含SALESMAN的员工

方法一:使用or操作符

 select ename,job from emp where job='MANAGER' or job = 'SALESMAN';

MySQL基础篇 | union、limit、DDL、DML、约束

方法二:使用in操作符

select ename,job from emp where job in ('MANAGER','SALESMAN');

MySQL基础篇 | union、limit、DDL、DML、约束

方法三: 采用union来合并

select ename,job from emp where job='MANAGER' union
select ename,job from emp where job='SALESMAN';

MySQL基础篇 | union、limit、DDL、DML、约束

 (2)注:对于两张不相干的表中的数据拼接在一起显示,只能用unoin!

select ename from emp 
union
select dname from dept;
-- select ename,dname from emp,dept; --笛卡尔现象 

MySQL基础篇 | union、limit、DDL、DML、约束

(3)合并结果集的时候,需要查询字段对应个数相同(列数相等)

在Oracle中更严格,不但要求个数相同,而且还要求类型对应相同

mysql> select ename,sal from emp -- 两列
    -> union
    -> select dname from dept; -- 一列
-- ERROR 1222 (21000): The used SELECT statements have a different number of columns

二:limit及通用分页SQL(重要)

(1)limit是mysql特有的,主要用于分页查询,其他数据库中没有,不通用!

(2)MySQL提供了limit ,主要用于提取前几条或者中间某几行数据

(3)语法:limit startIndex, length
①startIndex表示起始位置,从0开始,0表示第一条数据;
②length表示取几个;

(4)limit是sql语句中最后执行的一个环节:

select          5
...
from            1
...        
where           2
...    
group by        3
...
having          4
...
order by        6
...
limit           7
...;

(5)例如:select * from table limit m,n; 

其中m是指记录开始的index,从0开始,表示第一条记录,n是指从第m+1条开始,取n条!

select * from tablename limit 2,4
-- 即取出第3条至第6条,4条记录
-- 下标是从0开始的,2对应着第3条数据

(1) 取得emp前5条数据

select * from emp limit 0,5;
select * from emp limit 5; --省略了0,默认是从下表为0出开始

MySQL基础篇 | union、limit、DDL、DML、约束

 (2)从emp第二条开始取两条数据

select * from emp limit 1,2;

MySQL基础篇 | union、limit、DDL、DML、约束

 (3)取得薪水最高的前5名

思路:先降序,在使用limit截取前5条

select * from emp order by sal desc limit 5;

MySQL基础篇 | union、limit、DDL、DML、约束

(4)通用的标准分页SQL,例如:

①每页显示pageSize条记录:
②第pageNo页有多少记录:(pageNo – 1) * pageSize, pageSize

注:pageSize是每页显示多少条记录,pageNo是显示第几页!

java代码{
    int pageNo = 2; -- 页码是2
    int pageSize = 10; -- 每页显示10条
    limit (pageNo - 1) * pageSize, pageSize
}

三:表结构(DDL)

1. 创建表(creat)

创建表的时候,表中有字段,每一个字段有:

     * 字段名

     * 字段数据类型

     * 字段长度限制

     * 字段约束,后面会细讲

(1)建表语句的语法格式:

create table 表名(
      字段名1 数据类型,
      字段名2 数据类型,
      字段名3 数据类型,
      ....
);

(2)关于MySQL当中字段的常见的数据类型

int            整数型(java中的int)
bigint         长整型(java中的long)
float          浮点型(java中的float double)
char           定长字符串(String)
varchar        可变长字符串(StringBuffer/StringBuilder)
date           日期类型 (对应Java中的java.sql.Date类型)
BLOB           二进制大对象(存储图片、视频等流媒体信息);Binary Large OBject (对应java中的Object)
CLOB           字符大对象(存储较大文本,比如,可以存储4G的字符串);Character Large OBject(对应java中的Object)

MySQL基础篇 | union、limit、DDL、DML、约束

(3) varchar 和 char 区别?

①长度是否可变

 varchar 类型的长度是可变的,而 char 类型的长度是固定的!

 char 类型是一个定长的字段,以 char(10) 为例,不管真实的存储内容多大或者是占了多少空间,都会消耗掉 10 个字符的空间。

②存储长度

char 长度最大为 255 个字符,varchar 长度最大为 65535 个字符!

③检索效率方面

varchar 类型的查找效率比较低,而 char 类型的查找效率比较高!

(4)char和varchar怎么选择?

①在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的;例如:性别、生日等都是采用char。
②当一个字段的数据长度不确定的;例如:简介、姓名等都是采用varchar。

(5)BLOB和CLOB类型的使用

例如:电影表;包括序号id(int)、电影名name(varchar)、上映时间playtime(date/char)

海报haibao(是一张图片用BLOB)、故事情节history(CLOB)

注意:像int、varchar、date/char可以用insert直接插入;但是BLOB、CLOB需要用IO流才能插入!

(6) 表名在数据库当中一般建议以:t_或者tbl_开始。

例如:创建学生表;学生信息包括:学号、姓名、性别、班级编号、生日
①学号:bigint
②姓名:varchar
③性别:char
④班级编号:varchar
⑤生日:char

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1),
    classno varchar(255),
    birth char(10)
);

MySQL基础篇 | union、limit、DDL、DML、约束

show tables;

 MySQL基础篇 | union、limit、DDL、DML、约束

 (7)表的复制

语法:create table 表名 as select语句; 将查询结果当做表创建出来!

create table t_emp as select * from emp; --as可省略
-- 把emp表中查询的结果,插入到新表中;相当于复制

MySQL基础篇 | union、limit、DDL、DML、约束

 t_emp表中的数据和emp表中数据完全一样,相当于复制!

2. 修改表(alter)

对于表结构的修改,大家使用工具完成即可,因为在实际开发中表一旦设计好之后,对表结构的修改是很少的,修改表结构就是对之前的设计进行了否定,即使需要修改表结构,我们也可以直接使用工具操作;修改表结构的语句不会出现在Java代码当中。

例如:以t_user表作为操作对象

MySQL基础篇 | union、limit、DDL、DML、约束

(1)添加字段,alter table 表名 add 字段名 类型

alter table t_user add paswword varchar(255);
select * from t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

(2)修改字段

alter table 表名 modify 字段名 类型

alter table 表名 change 原字段 新字段 类型;

假如:把paswword的大小设置为100 

alter table t_user modify paswword varchar(100);
desc t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

 假如:把paswword改为password

alter table t_user change paswword password varchar(100);
select * from t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

(3)删除字段alter table 表名 drop 字段名

alter table t_user drop password;
select * from t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

3. 删除表(drop)

drop table if exists t_student; --如果t_student这个表存在就删除它,orcale不支持
drop table t_student; --比较通用

MySQL基础篇 | union、limit、DDL、DML、约束

四:处理表中数据(DML)

插入、修改和删出都属于DML,主要包含的语句:insert、update、delete

增删改查有一个术语:CRUD操作—增删改查
Create(增) Retrieve(检索) Update(修改) Delete(删除)

1. 插入表中的数据 insert

语法格式: insert into 表名(字段名1,字段名2,字段名3,….) values(值1,值2,值3,….)
要求:字段的数量和值的数量相同,并且数据类型要对应相同。

insert into t_student (no,name,sex,classno,birth) values (1,'zhangsan','1','yanyi','1999-1-14);

MySQL基础篇 | union、limit、DDL、DML、约束

 (1)这里顺序可以颠倒,但是一定要把对上,例如:把no放到最后一列

insert into t_student (name,sex,classno,birth,no) values ('wangwu','2','gaozhong','2000-1-14',3);

MySQL基础篇 | union、limit、DDL、DML、约束

(2)也可以只插入一个字段,其它字段会默认为空

insert into t_student (name) values ('zhaoliu');

MySQL基础篇 | union、limit、DDL、DML、约束

(3)插入的字段默认值都是NULL,我们也可以修改它的默认值

desc t_student;
--默认值都是NULL,我们插入时,如果没有指定的数据都是NULL

MySQL基础篇 | union、limit、DDL、DML、约束

修改默认值,我们重新创建一张表t_s,修改sex默认值是1,那么以后在创建表,只要这个字段没有给定值,默认就是1,而不是NULL

create table t_s(
    no bigint,
    name varchar(255),
    sex char(1) default 1, --指定默认值
    classno varchar(255),
    birth char(10)
);

MySQL基础篇 | union、limit、DDL、DML、约束

再次插入一组数据,不指定sex字段的值,默认就是1,而不是NULL 

insert into t_s (name) values ('zl');

MySQL基础篇 | union、limit、DDL、DML、约束

(4)我们也可以把前面的字段省了,但是此时例的数量和顺序都不能错,有几列就写几列

 insert into t_s values (1,'gh','2','dasi','1999-06-04');

MySQL基础篇 | union、limit、DDL、DML、约束

(5)还可以一次插入多个值,中间用逗号分开

insert into t_s (no,name,sex,classno,birth) values 
(2,'zhangsan','1','chuzhong','2000-2-3'),(3,'lisi','2','gaozhong','2001-2-3');

MySQL基础篇 | union、limit、DDL、DML、约束

(6)将查询的结果插入到一张表中,完成批量插入!

 语法:insert into 表名 select语句; 将查询结果插入到一个已经存在的表中

insert into t_student select * from t_s;
-- 将后面查询的结果插入到 t_student表中

MySQL基础篇 | union、limit、DDL、DML、约束

2. 修改表中的数据 update

语法格式:update 表名 set 字段名1=值1 , 字段名2=值2… where 条件;

注意:没有where条件整张表数据全部更新!

(1)将t_dept表中的,部门为10的LOC修改为SHANGHAI,将部门名称修改为RENSHIBU

MySQL基础篇 | union、limit、DDL、DML、约束

update t_dept set DNAME='RENSHIBU',LOC='SHANGHAI' where deptno=10;

MySQL基础篇 | union、limit、DDL、DML、约束

(2)更新dname的所有数据;不加条件就行

update t_dept set dname = 'zl';
-- 没有where条件,表示把所有的dname改成zl

MySQL基础篇 | union、limit、DDL、DML、约束

3. 删除表中的数据 delete & truncate

语法格式:delete from 表名 where 条件;
注意:没有条件全部删除。

(1)删除10部门数据

delete from t_dept where deptno=10;

MySQL基础篇 | union、limit、DDL、DML、约束

 (2)删除所有记录

delete from t_dept;

MySQL基础篇 | union、limit、DDL、DML、约束

 (3)删除大表中的数据 truncate

①使用delete删除表,很慢,但是可以回滚找回数据

②对于大数据表的删除使用truncate删除之后就找不到了

truncate table t_emp; -- 表被截断,不可回滚,永久丢失

MySQL基础篇 | union、limit、DDL、DML、约束

五:约束

什么是约束(Constraint)?常见的约束有哪些呢?

(1)在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
(2)常见的约束有哪些呢?
非空约束(not null):约束的字段不能为NULL。
唯一约束(unique):约束的字段不能重复。
主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)。
外键约束(foreign key):…(简称FK)。

检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。

1. 非空约束 not null

非空约束,表示当前字段不能为NULL!

(1)先创建一个用户表,并添加username非空约束

create table t_user(
    id int,
    username varchar(255) not null, --非空约束
    password varchar(255)
);

在插入数据,我们把username字段不设置,默认为空,就会报错!

insert into t_user (id,password) values (1,'123');

MySQL基础篇 | union、limit、DDL、DML、约束

 username给上初始值,就完全没问题

 insert into t_user (id,username,password) values (1,'zahngsan','123');

MySQL基础篇 | union、limit、DDL、DML、约束

2. 唯一约束 unique

唯一约束修饰的字段具有唯一性,不能重复;但可以为NULL!

唯一约束包括:列级约束(分开写)和表级约束(联合写)!

(1)给name列添加unique,表示姓名不可重复

--删除t_user表
drop table if exists t_user; 
--建表
create table t_user( 
   id int,
   username varchar(255) unique, --名字不能重复
   password varchar(255)
    
);
--插入数据
insert into t_user values (1,'zhangsan','123');
insert into t_user values (2,'zhangsan','456'); --再次插入相同姓名,就会报错
--ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

 MySQL基础篇 | union、limit、DDL、DML、约束

(2)但是可以把名字都使用默认的NULLNULL和NULL之间是不能直接用等号连接的

insert into t_user (id,password) values (2,'123');
insert into t_user (id,password) values (3,'456');

MySQL基础篇 | union、limit、DDL、DML、约束

 (3)给两个列或者多个列添加unique

第一种写法:联合写【表级约束】

例如:unique(username,password) 联合写,表示两个字段连起来不能重复,但是可以重复其中一个字段;属于表级约束! 

--删除t_user表
drop table if exists t_user; 
--建表
create table t_user( 
   id int,
   username varchar(255), 
   password varchar(255),
   unique(username,password) --联合写
);
-- 插入数据
insert into t_user values(1,'zs','123');
insert into t_user values(2,'zs','456');
insert into t_user values(3,'lisi','123');

select * from t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

第二种写法:分开写 【列级约束】

例如:username unique ,password unique 分开写,表示任何一个字段都不能重复;属于列级约束!

--删除t_user表
drop table if exists t_user; 
--建表 
create table t_user( 
   id int,
   username varchar(255) unique, 
   password varchar(255) unique --分开写,任何一个都不能重复
);
-- 插入数据
insert into t_user values(1,'zs','123');
insert into t_user values(2,'zs','456'); --会报错
insert into t_user values(3,'lisi','123'); --会报错

MySQL基础篇 | union、limit、DDL、DML、约束

3. 主键约束 primary key 

每个表应该具有主键,主键可以标识记录的唯一性,主键分为单一主键和复合(联合)主键,单一主键是由一个字段构成的,复合(联合)主键是由多个字段构成的。

主键约束 primary key 约束的字段既不能为NULL(非空约束not null),也不能重复(唯一约束unique)!

主键相关的术语?
①主键约束 : primary key
②主键字段 : id字段添加primary key之后,id叫做主键字段
③主键值 : id字段中的每一个值都是主键值。    
主键有什么作用?

①表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。
主键的作用:主键值是这行记录在这张表当中的唯一标识。(就像一个人的身份证号码一样。)    
主键的分类?
根据主键字段的字段数量来划分:
①单一主键(推荐的,常用的)
②复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式)
根据主键性质来划分:
①自然主键:主键值最好就是一个和业务没有任何关系的自然数;这种方式是推荐的。
②业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。不推荐用;不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。

结论:一张表的主键约束只能有1个!

(1)给username例加上主键约束

列级约束:username varchar(255) primary key, 这样就属于列级约束

表级约束:password varchar(255),primary key(password),这样就属于表级约束

--删除t_user表
drop table if exists t_user; 
--建表 
create table t_user( 
   id int,
   username varchar(255) primary key, --列级约束
   password varchar(255) 
);
-- 插入数据
insert into t_user values(1,'zs','123');
insert into t_user values(2,'zs','234'); --err,username字段不能相等
insert into t_user (id,password) values(2,'234'); --err,username字段不能为NULL

 MySQL基础篇 | union、limit、DDL、DML、约束

MySQL基础篇 | union、limit、DDL、DML、约束

根据以上的测试得出:username是主键,因为添加了主键约束,主键字段中的数据不能为NULL,也不能重复。

 (2)主键值自增 auto_increment

注:Oracle当中也提供了一个自增机制,叫做:序列(sequence)对象。

--删除t_user表
drop table if exists t_user; 
--建表 
create table t_user( 
   id int primary key auto_increment,
   username varchar(255) 
);
-- 插入数据
insert into t_user (username) values ('a'); -- 不用写id,会自增生成
insert into t_user (username) values ('b');
insert into t_user (username) values ('c');

select * from t_user;

MySQL基础篇 | union、limit、DDL、DML、约束

4.  外键约束  foreign key

外键主要是维护表之间的关系的,主要是为了保证参照完整性,如果表中的某个字段为外键字段,那么该字段一般来源于某张表的主键(至少有unique约束,可以为NULL)!

 (1)请设计数据库表,用来维护学生和班级的信息

 第一种方式:写成一张表,数据很冗余

no(pk)			name			classno			classname
--------------------------------------------------------------------------------------
1				zs1				101				 高一(1)班
2				zs2				101				 高一(1)班
3				zs3				102				 高一(2)班
4				zs4				102				 高一(2)班

第二种方式:写成两张表

班级表:t_class

		   cno(pk)			cname
-------------------------------------------------------
			101				 高一(1)班
			102				 高一(2)班

学生表:t_student

sno(pk)			sname			classno	(该字段添加外键约束fk)		
-------------------------------------------
1				zs1				101				
2				zs2				101				 
3				zs3				102				 
4				zs4				102				 

-- 如果classno不添加外键约束fk,可以写随意值;
-- 添加了外检约束fk,只能写外键约束里的值,写其他的会报错

(1)将以上表的建表语句写出来: t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表;t_class表叫做父表。

(2)顺序要求:
删除数据的时候,先删除子表,再删除父表

删除表的时候,先删除子表,在删除父表。
添加数据的时候,先添加父表,在添加子表
创建表的时候,先创建父表,再创建子表。
 

-- 删除表:先删除子表t_student,在删除父表t_calss
drop table if exists t_student; 
drop table if exists t_class; 
-- 创建表:先创建父表t_calss,在创建字表t_student
create table t_class(
    cno int,
    cname varchar(255),
    primary key(cno)    
);

create table t_student(
    sno int ,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno) --添加外键约束
        
);


-- 插入数据:先插入父表t_calss,在插入字表t_student
insert into t_class values (101,'Class 1, senior 1');
insert into t_class values (102,'Class 1, senior 2');

insert into t_student values (1,'zl1','101');
insert into t_student values (2,'zl2','101');
insert into t_student values (3,'zl3','102');
insert into t_student values (4,'zl4','102');

-- insert into t_student values (5,'zl5','103'); 
-- 会报错,因为添加了外键约束,classno只能来自cno的范围

select * from t_student;
select * from t_class;

我们可以把上面的sql语句注释去掉,放到一个文本文件里,然后改成.sql结尾;最后用source就可以执行啦!

MySQL基础篇 | union、limit、DDL、DML、约束

 (2)外键值可为NULL!

insert into t_student (sno,sname) values (5,'zl5');
select * from t_student;

MySQL基础篇 | union、limit、DDL、DML、约束

外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
答:被引用的字段不一定是主键,但至少具有unique约束!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由半码博客整理,本文链接:https://www.bmabk.com/index.php/post/128461.html

(0)

相关推荐

发表回复

登录后才能评论
半码博客——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!