目录
主要内容
-
数据库基础知识
-
MySQL基础知识
-
SQL语言入门
-
DDL和DML
-
表的约束
一,SQL语言入门
1,SQL作用
-
是所有关系型数据库的统一操作规范,不同关系型数据库都支持SQL
-
所有的关系型数据库都可以使用SQL
-
不同数据库之间的SQL有一些区别(方言)
2,SQL语言规范
-
SQL语句可以单行或者多行书写,以;结尾(Navicat中可以不写;)
-
可以使用空格或者缩进增加语句的可读性
-
MySQL中使用SQL不区分大小写,一般数据库名,表名,列名小写
-
注释方式
3,SQL分类
数据查询语言(Data Query Language,DQL):
DQL主要用于数据的查询,其基本结构是使用SELECT子句,FROM子句和WHERE子句的组合来查询一条或多条数据。
-
select
-
from
-
where
-
order by
-
having
数据操作语言(Data Manipulation Language,DML):
DML主要用于对数据 增加、修改和删除的操作。 主要包括:
-
INSERT: 增加数据
-
UPDATE: 修改数据
-
DELETE: 删除数据
数据定义语言(Data Definition Language,DDL):
DDL主要用针对是数据库对象(数据库、表、索引、视图)进行创建, 修改和删除操作。 主要包括:
-
CREATE: 创建
-
ALTER: 修改
-
DROP: 删除
数据控制语言(Data Control Language,DCL):
DCL用来授予或回收访问数据库的权限。
主要包括:
-
GRANT: 授予用户权限
-
REVOKE: 回收授予的权限 。
事务控制语言(Transaction Control Language,TCL):
TCL用于数据库的事务管理。
主要包括:
-
START TRANSACTION: 开启事务
-
COMMIT: 提交事务
-
ROLLBACK: 回滚事务
二,DDL操作
1,DDL操作数据库
1.1 DDL操作数据库案例
-- 查看所有的数据库
show databases;
-- 创建数据库
create database bjsxt01;
-- 选择数据库
use bjsxt01;
-- 删除数据库
drop database bjsxt01;
2. DDL操作数据库表
类型 | 描述 |
---|---|
int | 整型 |
double | 浮点型 |
varchar | 字符串型 |
date | 日期类型,格式为:yyyy-MM-dd |
2.2 详细数据类型
2.2.1 整数类型
整数类型 | 大小 | 表数范围(有符号) | 表数范围(无符号) | 作用 |
---|---|---|---|---|
TINYINT | 1字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2字节 | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3字节 | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT | 4字节 | (-214748364,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8字节 | (-9233372036854775808,9223372036854775807) | (0,18446744073709551615) | 极大整数值 |
MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。显示宽度并不限制可以在列内保存的值的范围.
2.2.2 浮点数类型
浮点数类型 | 大小 | 作用 |
---|---|---|
FLOAT(m,d) | 4字节 | 单精度浮点数值, m总个数,d小数位 |
DOUBLE(m,d) | 8字节 | 双精度浮点数值, m总个数,d小数位 |
注意事项:
浮点数类型的宽度不会自动扩充,使用时需要指定长度。
如:score double(4,1) 总体长度是4 精度为1位。
2.2.3 字符串类型
字符串类型 | 大小 | 描述 |
---|---|---|
CHAR(M) | 0~255字符 | 允许长度0~M个字符的定长字符串 |
VARCHAR(M) | 0~65535字符 | 允许长度0~M个字符的变长字符串 |
TINYTEXT | 0~255字节 | 短文本数据 |
TEXT | 0~65535字节 | 长文本数据 |
MEDIUMTEXT | 0~16777215字节 | 中等长度文本数据 |
LONGTEXT | 0~4294967295字节 | 极大文本数据 |
BLOB(M) | 最大65K | 以二进制存储图片,文件等数据 |
2.2.4 日期和时间类型
类型 | 格式 | 取值范围 |
---|---|---|
TIME | ‘HH:MM:SS’ | (‘-838:59:59’, ‘838:59:59’) |
DATE | ‘YYYY-MM-DD’ | (‘1000-01-01’, ‘9999-12-31’) |
YEAR | YYYY | (1901, 2155), 0000 |
DATETIME | ‘YYYY-MM-DD HH:MM:SS’ | (‘1000-01-01 00:00:00’, ‘9999-12-31 23:59:59’) |
TIMESTAMP | ‘YYYY-MM-DD HH:MM:SS’ | (‘1970-01-01 00:00:01’ UTC, ‘2038-01-19 03:14:07’ UTC) |
TIMESTEMP类型的数据指定方式与DATETIME基本相同,两者不同之处在于以下几点:
1) 数据的取值范围不同,TIMESTEMP类型的取值范围更小
2) 如果我们对TIMESTAMP类型的字段没有明确赋值,或是被赋与了NULL值,MySQL会自动将该字段赋值为系统当前的日期与时间
2.3 DDL-创建数据库表
create table 表名(
字段1 数据类型,
字段2 数据类型(长度)
);
-- 注意:最后一个字段不要加逗号,可以给字段的数据类型指定长度
2.4 DDL-查看数据库表
-- 显示当前数据库的所有的表
show tables;
-- 显示某个表的结构 desc 表名
desc student;
-- 查看某个表完整的建表语句 show create table 表名
show create table student;
2.5 DDL-删除数据库表
-- 语法格式:
DROP TABLE 表名;
-- 删除当前数据库中的某个表 drop table 表名5
drop table student;
2.6 DDL-修改数据库表
2.6.1 修改表名
-- 语法格式:
rename table 旧表名 to 新表名;
-- 修改student表名为stu
rename table student to stu;
-- 显示所有表
show tables;
2.6.2 向表中添加字段
-- 语法格式:
alter table 表名 add 字段名称 字段类型;
-- 案例:向学生表中添加字段address,长度为50
alter table stu add address varchar(50); -- 默认添加到最后
alter table stu add address2 varchar(50) first; -- 添加到第一个
alter table stu add address3 varchar(50) after age; -- 添加到age字段之后
-- 查看表结构
desc stu;
2.6.3 修改表中字段
-- 语法格式:
alter table 表名 modify 字段名称 字段类型;(修改字段类型和长度)
-- 案例:修改学生表中的字段address,长度改为30
alter table stu modify address varchar(30);
-- 语法格式:
alter table 表名 change 旧字段名 新字段名 类型(长度); (修改字段名 )
-- 案例:修改学生表中的字段address,修改为city,长度为10
alter table stu change address city varchar(10);
2.6.4 删除表中字段
-- 语法格式:
alter table 表名 drop 字段名;
-- 案例:删除学生表中 address2字段
alter table stu drop address2;
三、DML操作
1. 插入数据
-- 语法格式:
-- 1. 插入部分或全部字段
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段
insert into 表名 values(值1,值2,...,值n);
-- 案例:向学生表中插入数据
-- 方式1:插入全部字段,将所有字段名都写出来
insert into stu(sid,sname,sex,age,createdate,clazz,email) values(1,'张飞','男',50,'1990-05-05','1班','zhangfei@qq.com');
-- 方式2:插入全部字段,可以不写字段名
insert into stu values(2,'刘备','男',30,'1995-06-06','2班','liubei@qq.com');
-- 方式3:插入指定字段的值
insert into stu(sid,sname,age,createdate) values(3,'赵云',19,now());
insert into stu(sid,sname,createdate) values(4,'曹操',sysdate());
insert into stu(sid,createdate) values(5,current_date());
-- 一次添加多条数据
insert into 表名 values(值1,值2,...,值n),(值1,值2,...,值n),(值1,值2,...,值n);
注意事项:
-
值与字段必须要对应,个数相同、数据类型相同
-
值的数据大小,必须在字段指定的长度范围内
-
varchar char date类型的值使用单引号或者双引号包裹
-
如果要插入空值,可以忽略不写或者写null
-
如果插入指定字段的值,必须要上写列名
-
字符串日期类型支持 "2000-12-23" "2000/12/23" "2000.12.23"
2. 修改数据
-- 语法格式
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用:不加条件会修改所有的数据
-- 案例1:将sid=1的学生性别改为女
update stu set sex='女' where sid=1;
-- 案例2:将sid=2的学生性别改为女,年龄改为30,班级改为11班
update stu set sex='女',age=30,clazz='11班' where sid=2;
3. 删除数据
-- 语法格式
delete from 表名 where 条件;
delete from 表名; -- 慎用:删除表中的所有数据
truncate table 表名; -- 慎用:删除表中的所有数据
-- 案例1:删除sid=5的学生信息
delete from stu where sid=5;
-- 案例2:删除stu表中所有的数据
-- 方式一:
delete from stu;
-- 方式二:
truncate table stu; -- 推荐使用
truncate 和 delete的对比:
-
这两种操作都可以删除表中的数据
-
delete为数据操作语言DML;truncate 为数据定义语言DDL。
-
delete操作是将表中所有记录一条一条删除直到删除完;truncate 操作则是保留了表的结构,重新创建了这个表,所有的状态都相当于新表。因此,truncate 操作的效率更高。
-
delete操作可以回滚;truncate 操作会导致隐式提交,因此不能回滚(后面会讲解事务的提交和回滚)。
-
delete操作执行成功后会返回已删除的行数(如删除4行记录,则会显示“affected rows:4”);truncate 操作不会返回已删除的行量,结果通常是“affected rows:0”。delete操作删除表中记录后,再次向表中添加新记录时,对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增;truncate 操作会重新从1开始自增。
四、表的约束
1. 约束的作用
可以对表中的数据做一些限制, 从而保证数据的正确性, 有效性, 和完整性。
违反约束的不正确数据是无法插入到表中的。
2. 约束的分类
约束名 | 关键字 | 作用 |
---|---|---|
主键 | primary key | 不可重复, 不能为空,即唯一 + 非空 |
唯一 | unique | 不可重复 |
非空 | not null | 不能为空 |
默认值 | default | 赋予默认值 |
检查(Mysql8提供) | check | 取值范围 |
外键 | foreign key | 表与表之间的关系 |
3. 主键约束
3.1 添加主键约束
-- 语法格式:
字段名 字段类型 primary key
-- 案例1:创建一个带有主键的emp表 字段 eid int ename varchar(10) esex char(1)
-- 方式一
create table emp(
eid int primary key, -- 设置主键 非空唯一
ename varchar(10),
sex char(1)
);
-- 方式二
drop table emp; -- 删除emp表
create table emp(
eid int,
ename varchar(10),
sex char(1),
primary key(eid) -- 指定主键为eid字段
);
-- 方式三
drop table emp; -- 删除emp表
create table emp(
eid int primary key,
ename varchar(10),
sex char(1)
);
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);
3.2 测试主键的唯一和非空
-- 插入正常数据
insert into emp values(1,"宋江","男");
-- 插入一条数据, 主键为空
insert into emp values(null,"李逵","男");
-- Column 'eid' cannot be NULL 主键不能为空
-- 插入一条数据, 主键重复
insert into emp values(1,"孙二娘","女");
-- Duplicate entry '1' for key 'emp.PRIMARY' 主键冲突
3.3 哪些字段可以作为主键?
-
通常针对业务去设计主键, 每张表都设计一个主键id, 并且只能有一个主键.
-
主键是给数据库和程序使用, 所以主键没有意义没有关系, 只要能保证不重复就好, 比如身份证就可以作为主键.
3.4 删除主键约束
-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;
-- 查看表结构
desc emp;
-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束
alter table emp modify eid int;
3.5 主键自增
自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值。
-- 语法格式:
关键字 auto_increment 表示自增长(字段类型必须为整数类型)
-- 案例:为emp表eid字段添加主键约束, 并设置为自增
drop table emp;
create table emp(
eid int primary key auto_increment,
ename varchar(10),
sex char(1)
);
-- 测试主键自增
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');
4. 非空约束
-- 语法格式: 字段名 字段类型 not null
-- 案例:创建emp表,eid为主键约束,自增,ename为非空约束
-- 删除存在的emp表
drop table emp;
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- Column 'ename' cannot be null ename不能为空
5. 唯一约束
-- 语法格式:
字段名 字段类型 unique
-- 案例:emp表 eid 主键约束,自增 ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');
-- Duplicate entry 'n' for key 'emp.esex' esex重复
6. 默认值约束
-- 语法格式: 字段名 字段类型 default '值'
-- 案例:emp表 eid 主键约束,自增 ename 非空约束 esex默认值'男'
-- 删除存在的emp表
drop table emp;
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
sex char(1) default '男'
);
-- 查看emp表结构
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);
-- 错误用法
insert into emp values(default,'zs',null);
7. 检查约束
字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)
案例:emp表 eid 主键约束, 自增 ename 非空约束 esex默认值'男'并检查 男|女
-- 删除存在的emp表
drop table emp;
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
esex char(1) default '男' check(esex='男' or esex='女')
);
-- 查看emp表结构
desc emp;
-- 测试检查约束
insert into emp values(default,'lisi','n');
-- Check constraint 'emp_chk_1' is violated 不符合字段要求
五,语法大总结
1,DDL
/*
查看所有数据库
show databases;
创建数据库
create databases 数据库名称;
选择数据库
use 数据库名称;
创建数据库表
create table 表名(
字段名(列明) 数据类型,
字段名(列明) 数据类型,
...
)
注意:
字段后还有其他字段必须有,
最后一个字段不允许有,
表名 两个单词用_连接。
显示当前数据库所有的表
show tabkes;
显示某个表的结构
desc 表名;
查看某个完整的建表语句
show create table 表名;
删除当前数据库中的某个表
drop table 表名;
向表中添加字段
alter table 表名 add 字段名称 字段属性;
修改表中某个字段
alter table 表名 modify 字段名称 字段类型;(修改字段类型和长度)
alter table 表名 change 旧字段名 新字段名 类型(长度); (修改字段名 )
删除表中字段
alter table 表名 drop 字段名;
*/
2,DML
/*
插入数据
1,一次插一条数据
1.1,插入部分或全部字段
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
1.2,插入全部字段
insert into 表名 values(值1,值2,...,值n);
修改数据
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
删除数据
delete from 表名 where 条件;
delete from 表名; -- 慎用:删除表中的所有数据
truncate table 表名; -- 慎用:删除表中的所有数据
*/
3,约束
/*
主键约束
1,创建表时指定
字段名 字段类型 primary key;
2,创建表时不指定,后来指定
alter table 表名 add primary key(字段名);
主键自增
关键字 auto_increment 表示自增长(字段类型必须为整数类型)
非空约束
字段名 字段类型 not null;
唯一约束
字段名 字段类型 unique;
默认值约束
字段名 字段类型 default '值';
检查约束
字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)
*/
/*
关系型数据库:以二维表的形式存储数据,数据之间有行和列的关系。
本地磁盘存储
非关系型数据库:No SQL 数据库,不适用sql操作数据库,底层使用数据结构存储数据。
内存存储
后续的项目中通常会关系型数据库和非关系型数据库配合使用。
SQL:通用结构化查询语言。使用与关系型数据库。
DDL:数据定义语言。操作库,表,视图,索引,...
create drop alter show
DML:数据操作语言。操作表中的数据,添加,修改,删除
DQL:数据查询语言。查询表中的数据
DCL:数据控制语言。权限控制
TCL:事务控制语言。事务控制
表的约束:为表中的字段添加约束,表中数据的完成性和正确性。
一张表中允许出现多个不同的约束,
一个字段允许使用多个约束。
1.主键约束
非空+唯一(值不允许为空且不能重复)
primary key
注意:
表中都会创建一个例如:id的字段,id字段作为主键,可以和业务需求没有关联,
主键为程序或数据库使用的。
一张表中只能有一个主键。
主键自增 auto_increment
没有指定主键值,指定为null,default,使用自增策略
指定了主键值,使用指定的主键值
2.非空约束
值不允许为空
not null
一个表中可以出现多个非空约束
3.唯一约束
值不允许重复
unique
一个表中可以出现多个唯一约束
4.默认值约束
添加时没有指定值,使用默认值
default '值'
一个表中可以出现多个默认值约束
注意:
1.值为null,默认值不生效,使用null
2.值为default,默认值生效,使用默认值
3.不指定默认值约束的字段,默认值生效,使用默认值
5.检查约束(MySQL8新增)
检查值是够符合要求
check(字段名=值 or 字段名=值)
check(字段名>值 and 字段名<值)
...
*/