MySQL
数据库基本概念与分类
数据库的定义
- 存储结构化数据的仓库
- 结构化数据一般指存储在数据库中,具有一定逻辑结构和物理结构的数据
数据库的分类
关系型数据库
- 通过外键关联来建立表与表之间的关系
- 例如:Oracle、MySQL、DB2、Informix、SQL Server、SQLite
非关系型数据库
- 数据以对象的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定
- 不是堆关系型数据库的否定,而是补充,主要针对大数据
- 种类
- 键值对(key-value)
- Redis、Memcached
- 按列存储
- Hbase、Scylla、Cassandra
- 面向文档
- MongoDB、CouchDB
- 图形存储
- Neo4J
- 键值对(key-value)
结构化查询语言
数据定义语言DDL
作用:用于对结构的操作(包括数据库、表、索引、视图、触发器等)
数据库
查看show
# tab联想
mysql --no-beep -uroot -proot --auto-rehash
# 显示数据库
show databases;
# 显示当前正在使用的数据库
select database();
元数据:information_schema
核心库:mysql
性能相关:performance_schema
对DBA有好处:sys
数据库在进行注释的时候:#、-- 、/* */
创建create
# 创建数据库
create database DBName;
删除drop
# 删除数据库
drop database DBName;
使用use
# 使用数据库
use DBName;
数据库的复制
-
先把数据库创建出来
create database newdb default character set utf8;
-
进行数据库的复制
mysqldump -u root -p123456 --add-drop-table olddb| mysql -u root -p123456 newdb
表
列column = 域field = 字段
数值类型 int
文本、二进制 char、varchar
日期 date datetime
查看show
# 查看表格
show tables;
创建create
create table tableName (field1 type1 [constraint1],field2 type2 [constraint]… )
-
例子
# 创建表 create table member -> (id int, -> name char(20), -> age int, -> math float);
-
显示表的创建过程
show create table tableName
-
显示表的结构(字段、列、域名)
describe tableName
简写:desc tableName
修改alter
添加列add
alter table tableName add (field1 type1 [constraint1])
alter table member add English float default 60;
修改列modify/change
-
modify
不能改变列(域)的名字
alter table tableName modify field1 type [constraint]
alter table member modify name varchar(20);
-
change
可以改变列(域)名字
alter table tableName change oldName newName type [constraint1])
alter table member change name mname varchar(20);
-
修改表中列的相对顺序
-
修改某字段在为第一列
alter table tableName modify field1 type1 [constraint1] first;
alter table member add chinese float first;
-
修改某字段在某列之后
alter table tableName modify field2 type2 after field1;
alter table member modify chinese float after math;
-
注意
不能直接修改某字段在除了第一列的其他列,比如第二列;不能修改某字段在某列之前
-
删除列drop
alter table tableName drop field
alter table member drop English;
删除drop
drop table test1;
清空truncate
-
清空表的数据,表的结构还是存在的,不能通过事务日志回退
truncate table xxx;
修改表名rename
rename table oldName to newName;
表的复制
-
结构的复制,包括约束条件
# 格式 create table newName like oldName; # 案例 create table customer_tmp_like like customer;
-
数据和结构全部复制,约束条件不会复制
# 格式 create table newName select field1... from oldName; # 案例 create table customer_tmp select * from customer;
数据操作语言DML
作用:用于对数据库表中数据进行操作
插入insert
- 插入指定列
# 格式
insert into tableName [field1,field2...] values (field1Value,field2Value...);
# 案例
insert into member (id,name,age,math) values (1,'qiaofeng',30,60);
insert into member (id,name) values (5,'zsy'); # 未插入的值是NULL
- 插入所有列
# 格式
insert into tableName values (allFieldValue);
# 案例
insert into member values (2,'xuyifeng',21,100);
- 插入多条记录(多行)
# 格式
insert into tableName [field1,field2...] values (field1Value,field2Value...),(field1Value,field2Value...),....;
# 案例
insert into member (id,name,age,math) values (3,'zhoukaige',22,80),(4,'zhongwei',22,70);
修改update
# 格式
update tableName set field1=xxx [where condition]
-
不加where子句,直接更新所有数据
# 案例 update member set e_date = '2023-12-28 14:05:26';
-
加where子句,更新指定数据(注意where可以是范围)
# 格式 update member set e_date='2021-2-22 11:47:00' where id=1; # 案例 update member set age = 24 where id = 5;
-
更新时使用表达式
update member set math=english+age+id*3 where id=1;
删除delete
# 格式
delete from tableName [where condition]
-
如果不加where子句,删除表中所有数据,效果与truncate相同
delete from member;
-
如果加where子句,删除表中符合条件的数据
delete from member where id = 5;
数据查询语言DQL
作用:用于查询数据
0、‘\0’、" "、NULL的区别
在数据库中,0、‘\0’、“”(空字符串)和NULL表示不同的概念:
-
0: 通常表示数值零。在数据库中,这可能是整数或浮点数字段的值。
-
‘\0’: 表示空字符,通常在字符数据中使用。在某些编程语言和数据库中,'\0’是字符串的终止符,表示字符串的结束。
-
“”(空字符串): 表示一个不包含任何字符的字符串。在字符型字段中,空字符串与NULL不同,它是一个有效的字符串对象,只是它的长度为零。
-
NULL: 表示缺少值或未知值。在数据库中,NULL用于表示一个字段没有被赋予具体的值。
总的来说,0是数值零,'\0’是空字符,""是空字符串,而NULL表示缺少值。在不同的上下文和数据库中,它们的行为可能有所不同。
简单查询
查询命令
select column1, column2...
from table
where [ conditions ]
group by column1, column2...
having [ conditions ]
order by column1, column2...
查询所有列
-
不加where条件
# 格式 select * from tableName
-
加where条件
# 格式 select * from tableName where conditions # 案例 select id,name from member where id > 1;
查找指定列
去重distinct
# 格式
select distinct field1 from tableName;
# 案例
select distinct e_date from member;
查询时候使用表达式
select age,age+10 from member;
查询时候可以取别名as
select age,age+10 as total from member;
比较运算符
大于小于等于
< > <= >= = <>(不等于)
范围查询between… and…
闭区间
select age from member where age between 30 and 50;
枚举查询in
# 显示在in列表中的值,而不是一个范围,注意与between...and...的区别
select * from member where age in (30,60);
模糊查询like
Like语句中,% 代表零个或多个任意字符,_ 代表一个字符,例first_name like ‘_a%’;
# 查询name以z开头的数据
select * from member where name like 'z%';
# 查询name以g结尾的数据
select * from member where name like '%g';
# 查询name的第二个字母为h的数据
select * from member where name like '_h%';
判空查询is null
select * from member where name is NULL;
逻辑运算符
- and 多个条件同时成立
- or 多个条件任一成立
- not 不成立
排序order by
- Order by 指定排序的列,排序的列即可是表中的列名,也可以是select 语句后指定的列名。
- Asc 升序(默认情况)、Desc 降序
- ORDER BY 子句应位于SELECT语句的结尾。
# 格式
select field1... from tableName order by field1
select * from member order by math [asc/desc]
# 案例
select age from member order by age asc;
分页查询(定性查询)limit
limit限定行数,offset指定偏移点
# 限制输出数据的条数,此处限制为两条数据
select * from member order by age asc limit 2;
# 先偏移一行,然后再限制输出数据的条数
select * from member order by age asc limit 2 offset 1;
复杂查询
查询命令
# 格式
select field1... from left_table join_type right_table [on conditions1] [where condition2]
连接查询
交叉连接cross join
- 不带on子句,返回连接表中所有数据行的笛卡儿积。
# 格式
select * from left_table cross join right_table;
# 案例
select * from customer cross join order_table;
内连接inner join
-
返回连接表中符合连接条件及查询条件的数据行。
-
内连接如果不指定条件,就与交叉连接效果一样了
-
三种形式
- 等值连接(=应用于连接条件,不会去除重复的列)
- 不等值连接(大于小于不等于等)
- 自然连接(会去除重复的列),特殊的等值连接,要求列属性相同
-
显示形式
-
显示内连接
# 格式 select * from tableName1 inner join tableName2 [on conditions][where 条件] # 案例 select * from customer inner join order_table on customer.id = order_table.user_id; # 可以取别名偷懒!!! select * from customer as c inner join order_table as o on c.id = o.user_id;
-
隐式内连接
# 格式 select * from tableName1,tableName2 [where conditions] # 案例 select * from customer, order_table where customer.id = order_table.user_id;
-
外连接
-
注意:连接的时候,必须要有on子句
-
左外连接left [outer] join
-
特点:不仅包含符合连接条件的数据,还包含左表中的其他数据行,以左表为主表 会显示主表中的多有数据行 右表中复合连接条件的会显示的上面几行 不符合连接条件的直接显示NULL
# 格式 select field1... from left_table left outer join right_table on conditions; # 案例
-
-
右外连接right [outer] join
-
特点:以右表为主表 如果主表中的数据少于副表 副表中不符合连接条件的数据不会显示
# 格式 select field1... from left_table right outer join right_table on conditions1 where conditions2;
-
子查询(嵌套查询)
- where
- from
联合查询(union)
报表查询
group by分组
统计函数
数据完整性
作用:保证插入到数据库中的数据是正确的
三种完整性
实体完整性
保证每条记录(每行数据)是唯一的,通过主键来区分
主键:唯一,非空
域完整性
保证每个字段或者域或列符合某种类型或限制(非空、默认值、自增)
参照完整性
-
特征
-
保证一个表的外键和另一个表的主键对应
-
不能引用不存在的实体
-
-
目的
保证数据的一致性
几种约束
主键约束
-
primary key
-
唯一,非空
-
特殊的唯一约束
-
主键的创建方式
-
定义字段时候直接指定
-
表中字段定义完毕后指定
# 案例 alter table customer_tmp modify id int primary key;
-
-
主键是用来唯一标识表中每一行数据的字段,就像每个人都有唯一的身份证号一样。这有助于确保表中的每一行都有一个独特的标识。
唯一约束
- unique
- 唯一,但是可以为空
非空约束
- not null
自动增长
- auto_increment
mysql> create table customer (
-> id int auto_increment,
-> name varchar(30),
-> age int,
-> primary key(id)
-> );
外键约束
-
foreign key
-
创建方式两种
- 创建表的时候直接创建外键约束
create table order_table (order_id int auto_increment, order_name char(20), price int, usr_id int, PRIMER KEY(order_id), [CONSTRAINT order_table_member] FOREIGN KEY(usr_id) REFERENCES member(ID));
- 修改表的时候创建外键约束
# 格式 alter table tableName add constraint keyName foreign key(fieldName) references tableName2(field1) # 案例 alter table order_table add constraint pk_order_mem foreign key(user_id) references member(id); # 外键名字可省略,默认创建外键名字 alter table order_table_tmp add [CONSTRAINT `order_table_tmp_ibfk_1`] foreign key(user_id) references customer_tmp(id);
-
删除外键
# 格式 alter table tableName drop foreign key keyName # 案例 alter table member drop foreign key fk_order_mem;
-
插入时候,外键必须存在
-
删除的时候,必须考虑外键约束
-
如果想修改表的外键名字,可以先删除外键约束,然后再创建
-
外键用于建立不同表之间的关联。它是另一表的主键,通过在当前表中引用其他表的主键,可以实现表与表之间的连接。
默认约束
检查约束(MySQL不支持)
表与表之间的关系
避免数据的冗余