SQL
1.数据库的三大范式
1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线;也就是说:每列的值具有原子性,不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得,满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键,那么主键以外的列必须完全依赖于主键;如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的,即满足第三范式必须要先满足第二范式。第三范式(3NF)要求:表中的非主键列必须和主键直接相关而不能间接相关;也就是说:非主键列之间不能相关依赖。
2.数据类型
2.1整数类型
数据类型 字节数 无符号数的取值范围 有符号数的取值范围
TINYINT 1 0~255 -128~127
SMALLINT 2 0~65535 -32768~32768
MEDIUMINT 3 0~16777215 -8388608~8388608
INT 4 0~4294967295 -2147483648~ 2147483648
BIGINT 8 0~18446744073709551615 -9223372036854775808~9223372036854775808
2.2浮点型
数据类型 字节数 有符号的取值范围 无符号的取值范围
FLOAT 4 -3.402823466E+38~-1.175494351E-38 0和1.175494351E-38~3.402823466E+38
DOUBLE 8 -1.7976931348623157E+308~2.2250738585072014E-308 0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D) M+2 -1.7976931348623157E+308~2.2250738585072014E-308 0和2.2250738585072014E-308~1.7976931348623157E+308
2.3字符串类型
在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是:VARCHAR存储可变长度的字符串。
当数据为CHAR(M)类型时,不管插入值的长度是实际是多少它所占用的存储空间都是M个字节;而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1
2.4文本类型
数据类型 | 储存范围 |
---|---|
TINYTEXT | 0~255字节 |
TEXT | 0~65535字节 |
MEDIUMTEXT | 0~16777215字节 |
LONGTEXT | 0~4294967295字节 |
2.5日期类型
数据类型 字节数 取值范围 日期格式 零值
YEAR 1 1901~2155 YYYY 0000
DATE 4 1000-01-01~9999-12-31 YYYY-MM-DD 0000-00-00
TIME 3 -838:59:59~ 838:59:59 HH:MM:SS 00:00:00
DATETIME 8 1000-01-01 00:00:00~9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 0000-00-00 00:00:00
TIMESTAMP 4 1970-01-01 00:00:01~2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 0000-00-00 00:00:00
3.数据库基本操作
3.1创建数据库
create database 数据库名称;
例:
-- 创建一个叫class的数据库
show create database class;
3.2查看数据库的基本信息
show create database 数据库名称;
例:
show create database class;
3.3删除数据库
drop database 数据库名称;
例:
drop database class;
3.4查看所有数据库
show databases;
3.5切换数据库
use class;
3.6查看当前的数据库
select database();
4.数据表的基本操作
再创建完数据库后需要先使用这个数据库然后再创建数据表
4.1创建数据表
create table 表名(
字段1 字段类型,
字段2 字段类型,
…
字段n 字段类型
);
例:
mysql> create table student(
-> sno char(9) primary key,
-> sname char(10),
-> ssex char(2),
-> sage tinyint,
-> sdept char(20)
-> );
4.2查看数据表
4.2.1查看数据库中所有数据表
show tables;
运行结果:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5fb1ZaEo-1647946198246)(C:\Users\kdmvp\AppData\Roaming\Typora\typora-user-images\image-20220303133941922.png)]
4.2.2查看表的基本信息
show create table student;
4.2.3查看表的字段信息
desc student;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a2Lwr3Ry-1647946198247)(C:\Users\kdmvp\AppData\Roaming\Typora\typora-user-images\image-20220303134525733.png)]
4.3修改数据表
4.3.1修改表名
alter table student rename to stu;
4.3.2修改字段名
alter table stu change 改前名 改后名 char(10);
例:
alter table stu change name sname char(10);
4.3.3修改字段数据类型
alter table stu modify sname int;
4.3.4增加字段
alter table stu add address char(50);
4.3.5删除字段
alter table stu drop address;
4.4删除数据表
drop table stu;
5.数据表的约束
为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;这些规则常称为表的约束。常见约束如下:
约束条件 | 说明 |
---|---|
PRIMARY KEY | 主键约束用于唯一标识对应的记录 |
FOREIGN KEY | 外键约束 |
NOT NULL | 非空约束 |
UNIQUE | 唯一性约束 |
DEFAULT | 默认值约束,用于设置字段的默认值 |
5.1主键约束
主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号,并且这个身份证号是唯一的。
字段名 数据类型 primary key;
例:
create table student(
id int primary key,
name varchar(20)
);
主键约束第二种方式:
create table student01(
id int
name varchar(20),
primary key(id)
);
5.2非空约束
非空约束即 NOT NULL指的是字段的值不能为空,基本的语法格式如下所示:
字段名 数据类型 NOT NULL;
例:
create table student02(
id int
name varchar(20) not null
);
5.3默认值约束
默认值约束即DEFAULT用于给数据表中的字段指定默认值,即当在表中插入一条新记录时若未给该字段赋值,那么,数据库系统会自动为这个字段插人默认值;其基本的语法格式如下所示:
字段名 数据类型 DEFAULT 默认值;
例:
create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);
5.4唯一性约束
字段名 数据类型 UNIQUE;
例:
create table student04(
id int,
name varchar(20) unique
);
5.5外键约束
-- 在创建数据表时语法如下:
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下:
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);
例:
create table student05(
id int primary key,
name varchar(20)
);
create table class(
classid int primary key,
studentid int
);
alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);
建立外键是为了保证数据的完整和统一性,如果主表中的数据被删除或修改从表中对应的数据也应该被删除。
5.5.1删除外键
alter table 从表名 drop foreign key 外键名;
例:
alter table class drop foreign key fk_class_studentid;
6.数据表中插入数据
每个字段与其值是严格一一对应的。也就是说:每个值、值的顺序、值的类型必须与对应的字段相匹配。但是,各字段也无须与其在表中定义的顺序一致,它们只要与 VALUES中值的顺序一致即可。
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值 1,值 2,...);
例:
mysql> insert into stu (sno,sname,ssex,sage,sdept) values(1,'林伟强','男',21,'信息工程学院');
6.1为表中指定字段插入数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES (值 1,值 2,...);
插入数据的方法基本和为表中所有字段插入数据,一样,只是需要插入的字段由你自己指定
6.2同时插入多条数据
INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;
例:
insert into stu (sno,sname,ssex,sage,sdept) values(2,'张三','男',22,'管理学院'),(3,'李四','女',20,'传媒与艺术学院');
5.6 增加约束
在修改表时添加非空约束
如果在创建表时忘记了为字段设置非空约束,也可以通过修改表进行非空约束的添加。修改表时设置非空约束的语法格式如下:
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> 约束条件;
例:
mysql> ALTER TABLE tb_dept4
-> CHANGE COLUMN location
-> location VARCHAR(50) NOT NULL;
5.7在修改表时添加检查约束
如果一个表创建完成,可以通过修改表的方式为表添加检查约束。
修改表时设置检查约束的语法格式如下:
ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
例:
mysql> ALTER TABLE tb_emp7
-> ADD CONSTRAINT check_id
-> CHECK(id>0);
5.8 删除非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
例:
mysql> ALTER TABLE tb_dept4
-> CHANGE COLUMN location
-> location VARCHAR(50) NULL;
7.更新数据
7.1UPDATE基本语法
UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
在该语法中:字段名1、字段名2…用于指定要更新的字段名称;值1、值 2…用于表示字段的新数据;WHERE 条件表达式 是可选的,它用于指定更新数据需要满足的条件
7.2UPDATE更新部分数据
把sname字段设置为王五,ssex字段设置为30 当序号为sno为1时
update stu set sname='王五',ssex=30 where sno=2;
7.3UPDATE更新全部数据
把所有age设置为18
update student set age=18;
8.删除数据
在MySQL通过DELETE语句删除数据表中的数据
8.1DELETE基本语法
DELETE FROM 表名 [WHERE 条件表达式];
8.2DELETE删除部分数据
delete from student where age=14;
8.3DELETE删除全部数据
delete from student;
8.4TRUNCATE和DETELE的区别
TRUNCATE和DETELE都能实现删除表中的所有数据的功能,但两者也是有区别的:
1、DELETE语句后可跟WHERE子句,可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录;但是,TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后,再次向表中添加记录时自动增加字段的默认初始值重新由1开始;使用DELETE语句删除表中所有记录后,再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句,TRUNCATE语句通常被认为是DDL语句
9.MySQL数据表简单查询
9.1查询所有字段
select * from 表名;
例:
select * from stu;
9.2查询指定字段
select 字段名1,字段名2 from 表名;
例:
select sno,sname from stu;
9.3常数的查询
在SELECT中除了书写列名,还可以书写常数。可以用于标记
select 字段名/常数 from 表名;
9.4从查询结果中过滤重复数据
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
select distinct 字段名 from 表名;
例:
select distinct sname from stu;
9.5算数运算符
在SELECT查询语句中还可以使用加减乘除运算符
查询学生10年后的年龄 MySQL命令:
select sname,age+10 from student;
10.函数
10.1 count
统计表中数据的行数或者统计指定列其值不为NULL的数据个数
select count(*) from student;
select count(sname) from student;
10.2 max
计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算
select max(sage) from stu;
10.3 min
计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算
select min(sage) from stu;
10.4 avg
计算指定列的平均值,如果指定列类型不是数值类型则计算结果为
select avg(sage) from stu;
10.5时间函数
SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');
10.6字符串函数
--连接函数
SELECT CONCAT ()
--
SELECT INSTR ();
--统计长度
SELECT LENGTH();
10.7数学函数
-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);
11.条件查询
11.1使用关系运算符查询
在WHERE中可使用关系运算符进行条件查询
关系运算符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
select * from stu where sage=20;
11.2使用in关键字查询
IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中,则将字段所在的记录将査询出来
-- 寻找sdept字段中为集合中的元素(信息工程学院或管理学院)
select * from stu where sdept in('信息工程学院','管理学院');
11.3使用between …and … 关键字查询
BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内,则将所在的记录将查询出来
select * from stu where sage between 20 and 25;
select * from stu where sage not between 20 and 25;
11.4使用空值查询
在MySQL中,使用 IS NULL关键字判断字段的值是否为空值。请注意:空值NULL不同于0,也不同于空字符串
select * from student where sname is not null;
11.5使用AND关键字查询
在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
select * from stu where sage=20 and sdept='信息工程学院';
11.6使用OR关键字查询
在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时,只要记录满足其中任意一个条件就会被查询出来
select * from stu where sage>15 or sdept='信息工程学院';
11.7使用LIKE关键字查询
MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配
11.7.1普通字符串
select * from stu where sname like '林伟强';
11.7.2含有%通配的字符串
%用于匹配任意长度的字符串。例如,字符串“a%”匹配以字符a开始任意长度的字符串
-- 以林开头
select * from stu where sname like '林%';
-- 以强开头
select * from stu where sname like '%强';
-- 包含伟
select * from stu where sname like '%伟%';
11.7.3含有_通配的字符串
下划线通配符只匹配单个字符,如果要匹配多个字符,需要连续使用多个下划线通配符。例如,字符串“ab_”匹配以字符串“ab”开始长度为3的字符串,如abc、abp等等;字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串,如"abcd"、"atud"等等。
select * from stu where sdept like '管理学_';
11.8使用LIMIT限制查询结果的数量
当执行查询数据时可能会返回很多条记录,而用户需要的数据可能只是其中的一条或者几条
查询学生表中年纪最小的3位同学 MySQL命令:
select * from stu order by sage asc limit 3;
11.9使用GROUP BY进行分组查询
GROUP BY 子句可像切蛋糕一样将表中的数据进行分组,再进行查询等操作。换言之,可通俗地理解为:通过GROUP BY将原来的表拆分成了几张小表。
统计各个年龄人数
select count(*), sage from stu group by sage;
统计年龄小于33各个年龄人数
select count(*), sage from stu where sage<33 group by sage;
GROUP BY和聚合函数以及HAVING一起使用
统计工资总和大于8000的部门 MySQL命令:
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;
11.10使用ORDER BY对查询结果排序
从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此,我们可以使用ORDER BY对查询结果进行排序
其语法格式如下所示:
SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];
#asc 升序 desc降序
select * from stu order by sage asc;
12.别名设
12.1为数据表取别名
在查询操作时,假若表名很长使用起来就不太方便,此时可为表取一个別名,用该别名来代替表的名称。语法格式如下所示:
SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;
select * from student as stu;
12.2为字段取别名
在查询操作时,假若字段名很长使用起来就不太方便,此时可该字段取一个別名,用该别名来代替字段的名称。语法格式如下所示:
SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;
select name as '姓名',id from student;
13.表的关联关系
实际开发中数据表之间存在着各种关联关系。
多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如:员工与部门之间的关系,一个部门可以有多个员工;而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中,应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如:学生与老师之间的关系,一个学生可以有多个老师而且一个老师有多个学生。通常情况下,为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
1.关联查询
select * from student where classid=(select cid from class where cname='Java');
2…关于关联关系的删除数据
delete from student where classid=(select cid from class where cname='Java');
delete from class where cname='Java';
14.子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询;该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时,首先会执行子查询中的语句,再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。
1.带比较运算符的子查询
# 查询张三同学所在班级的信息 MySQL命令:
select * from class where cid=(select classid from student where sname='张三');
# 查询比张三同学所在班级编号还大的班级的信息 MySQL命令:
select * from class where cid>(select classid from student where sname='张三');
2.带EXISTS关键字的子查询
EXISTS关键字后面的参数可以是任意一个子查询, 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会 执行
# 假如王五同学在学生表中则从班级表查询所有班级信息 MySQL命令:
select * from class where exists (select * from student where sname='王五');
3.带ANY关键字的子查询
ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。
#查询比任一学生所属班级号还大的班级编号 MySQL命令:
select * from class where cid > any (select classid from student);
4.带ALL关键字的子查询
ALL关键字与ANY有点类似,只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。
#查询比所有学生所属班级号还大的班级编号 MySQL命令:
select * from class where cid > all (select classid from student);