Mysql数据库学习笔记

最新推荐文章于 2023-04-21 16:51:17 发布

YaruCode

最新推荐文章于 2023-04-21 16:51:17 发布

阅读量320

点赞数

分类专栏：其他文章标签： Mysql SQL sql Mysql笔记 sql笔记

本文链接：https://blog.csdn.net/yasenRK/article/details/109037519

版权

其他专栏收录该内容

16 篇文章 0 订阅

订阅专栏

文章目录

添加新列

在已经存在的表中添加新的列

alter table_name add new_column_name data_type [约束条件] [first][after 已存在的列名]

修改列名

alter table <表名> change <旧表名><新表名><新数据类型>
alter table reader change email2 email4 varchar(30);

修改列的数据类型

修改列的数据类型

alter table <表名> modify <列名> <数据类型>

修改列的排列位置

alter table 来改变表中列的相对位置

alter table <表名> modify <列1><数据类型> first|after <列2>

删除列

删除列是将数据表中的某个列从表中移除

alter table <表名> drop <列名>;

修改表名

mysql 是通过alter table 语句来实现表名的修改

alter table <旧表名> rename [TO]<新的表名>;

删除数据库表

mysql中，使用drop table 可以一次删除一个或多个没有被其他表关联的数据库表

drop table [if exists] 表1,表2...表n;

表分区

当我们创建的表，需要承载的数据量很大的时候，就要注意他的行呢个问题，比如图书馆信息表，有1000万个图书信息，那么我们就得考虑如何优化它，其中一种方式就是表分区。
表分区就是把一张表的数据分成多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，但所有的的数据还在一个表中。
数据库的表分区一般有两种方式：垂直分区和水平分区。
- 水平分区：将表中的数据按行分割成不同的数据文件
- 垂直分区：将表中的数据案列分割成不同的数据文件

创建表分区

在创建表的时候使用：partition by 类型(字段)
Range 分区：
- 根据指定某列的范围值进行分区
- 使用values less than操作符定义分区

create table bookinfo(
    book_id int,
    book_name varchar(20)
)
partition by range(book_id)(
    partition p1 values less than(20109999),
    partition p2 values less than(20159999),
    partition p3 values less than MAXVALUE
);

约束

约束是一种限制，它通过表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

Mysql中常用的几种约束类型：

约束类型	非空约束	主键约束	唯一约束	默认约束	外键约束
关键字	not null	primary key	uniqueue	default	foreign key

非空约束：被约束为非空的列是不能为空的
主键约束：用于约束表中的一行，一张表中可以通过主键来准确的定位到一行数据
唯一约束：被规定为唯一约束的一列中的数据不能有重复
默认约束：当插入数据时如果没有给字段赋值时默认填入的值
外键约束：是用来在两个表之间建立联系，可以通过外键约束保证数据的一致性和完整性

空值与非空

NULL:字段值可以为空
NOT NULL:字段值禁止为空
非空约束(not null constraint)指字段的值不能为空。对于使用了非空字段的约束如果用在添加数据时，没有指定值，数据库系统会报错。
语法规则

列名 数据类型 not null

创建表时添加非空约束

create table bookinfo(
    book_id int,
    book_name varchar(20) not null
);

通过修改表为指定列添加非空约束

alter table bookinfo modify book_name varchar(20) not null;

删除非空约束

alter table bookinfo modify book_name varchar(20) null;

主键约束

主键约束(primary key constraint)要求主键列的数据唯一，并且不允许为空，主键能够唯一地标识表中的一条记录

注意：一张表中最多只能有一个主键约束

单字段主键

单字段主键是由一个字段(列)组成

在定义列的同时指定主键
列名数据类型 primary key;
在列定义的后边指定主键
[constraint<约束名>] primary key(列名)

多个字段联合主键(或者交复合主键)

主键有多个字段(列)联合组成
primary key(字段1，字段2，…字段n)
例

create table borrowinfo(
    book_id int,
    card_id char(18),
    primary key(book_id,card_id)
);

通过修改表为列添加主键

---首先创建数据表
create table bookinfo(
    book_id int,
    book_name varchar(20) not null;
);
---修改表来添加主键
alter table bookinfo modify book_id int primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint pk_id primary key(book_id);

删除主键

alter table bookinfo drop primary key;

唯一约束

唯一约束(Unique Constraint)要求该列唯一，允许为空，唯一约束可以确保一列或者几列不出现重复值。

语法规则如下：

定义完列之后直接指定唯一约束：

列名 数据类型 unique

定义完所有的列之后指定唯一约束:
[constraint <约束名>] unique(<列名>)

添加表时添加唯一约束

---第一种
create table bookinfo(
    book_id primary key,
    book_name varchar(20) unique not null
);

---第二种
create table bookinfo(
    book_id int primary key,
    book_name varchar(20) not null,
    constraint uk_bname unique(book_name)
);

修改表时添加唯一约束

alter table bookinfo modify book_name varchar(20) unique;
alter table bookinfo add unique(book_name);
alter table bookinfo add constraint uk_bname unique(book_name);

删除唯一约束

alter table bookinfo drop index uk_bname;
alter table bookinfo drop key uk_bname;

主键约束和唯一约束的区别：

一个表中可以有多个unique声明，但只能有一个primary key声明
声明为primary key的列不允许有空值
但是声明为unique的列允许有空值

默认约束

默认约束(Default Constraint)指定某列的默认值
语法规则
列名数据类型 defalut 默认值

创建表时添加约束

create table bookinfo(
    boo_id int primary key,
    press varchar(20) default '机械工业出版社'
);

修改表的时候添加约束

alter table bookinfo modify press varchar(20) default '机械工业出版社';
alter table bookinfo alter column press set default'机械工业出版社';

删除默认约束

alter table bookinfo modify press varchar(20);
alter table bookinfo alter column press drop default;

外键约束

外键用来在两个表之间的数据库之间的建立链接，它可以是一列或者多列。一个表可以有一个或多个外键
外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键的作用是保持数据的一致性和完整性

使用外键约束

语法格式：
[Constraint <外键约束>] foreign key (列名)
references<主表名>(主键)

创建表时添加外键约束

---子表
create table bookcategory(
    category_id int primary key,
    category varchar(20),
    parent_id int
);

---父表
create table booinfo(
    book_id primary key,
    book_id int,
    constraint fk_id foreign key(book_category_id) references bookcategory(category_id)
);

修改表时添加外键约束

alter table bookinfo add foreign key(book_category_id) references bookcategory(category_id);

外键约束的参照操作

cascade：从父表删除或者更新且自动删除或更新子表匹配的行

create table bookinfo(
    book_id int primary key,
    book_category_id int,
    constraint fk_cid foreign key(book_category_id) references bookcategory(category_id) on delete cascade
);

单表数据记录的更新

Mysql中使用update语句更新表中的记录
更新特定的行
更新所有的行

语法格式

update table_name set column_name1=value1,
column_name2=value2,
column_namen=valuen
where(condition);

单表记录的删除

使用delete语句删除满足条件的记录，语法格式如下：

delete from table_name [where<condition>]

如果想删除表中的所有记录，还可以使用truncate table语句，truncate将直接删除原来的表，并重新创建一个表，其语法格式如下：

truncate table table_name

基本查询语句

Mysql从数据表中查询数据的基本语句为slect语句。

语法格式

select select_expr[,select_expr]
[
    from table_references
    [where where_condition]
    [group by{col_name | position}[ASC |DESC,]……]
    [HAVING where_condition]
    [OERER BY{col_name | expr |position}[ASC | DESC],……]
    [LIMIT {[offset,] row_count | row_count Offset}]
]

查询所有的列

在select语句中使用"*"通配符查询所有的列
如:select * from bookcategory
在select 语句中指定所有的列
如: select category_id,category,parent_id from bookcategory

查询指定列

查询单个列
如：select category from bookcategory
查询多个列
如:select category_id,category from bookcategory

查询指定记录

在select 语句中通过where子句，对数据进行过滤，语法格式为:

select 列名1，列名2，……,列名n
from 表名
where 查询条件

如：

select book_id,book_name,price from bookinfo where press='机械工业出版社';

查询结果不重复

在select 语句中可以使用distinct关键字指示mysql消除重复的记录值
select distinct 列名 from 表名

查询空值

在select语句中使用is null子句，可以查询某列内容为空记录
如:select * from readerinfo where age is null;

分组查询

分组查询是对数据按照某个或多个列进行分组
[group by 列名][HAVING<条件表达式>]

创建分组

查询结果分组
group by 列名
group by 通常和聚合函数在一起使用，例如:MAX(),
MIN(),SUM(),AVG(),COUNT()
select count(*) from readerinfo where sex=‘男’;
select sex,count(*) from readerinfo group by sex;

使用having过滤分组

[HAVING<条件表达式>]
HAVING 限定显示记录所需要满足的条件，只有满足条件的分组才会被显示
例
select sex from readerinfo group by sex having coun(sex)>2;

对查询结果进行排序

Mysql中可以通过order by子句对查询的结果进行排序
order by 列名[ASC| DESC]

单列排序
多列排序
指定排序方向

单列排序

按照指定的某列进行排序，默认升序
例:select * from bookinfo order by price

多列排序

按照多个列排序，默认升序
例:select * from bookinfo order by price,store;

指定排序方向

排序分为升序和降序，默认为升序
升序 ASC
降序 DESC

select * from bookinfo order by price asc,store desc;

用limit语句限制查询结果的数量

limit关键字可以返回指定位置的记录

语法格式

limit [位置便宜量,] 行数
说明:第一条记录的位置偏移量为0，第二条为1，……

显示图书比信息表的前三行数据

select * from bookinfo limit 3;
显示从第三条数据开始的2条数据
select * from bookinfo limit 2,2;

运算符与函数

运算符

运算符概述

运算符是告诉我们Mysql执行特定的算术或逻辑操作的符号，运算的连接表达式中各个操作数，其作用是用来指明对操作数所进行的运算

比较运算符

运算符	作用
>、<	大于、小于
>=、=<	大于等于，小于等于
=	等于
is(not) null	判断一个值是否为空(或不为空)
between…and	判断一个值是否在两个值之间
(not) In	判断一个值(或不是)IN列表中的值
LIKE	通配符匹配

逻辑运算符

运算符	作用
AND	逻辑与
OR	逻辑或
NOT	逻辑非

数值函数

函数表示对输入参数值返回一个具有特定关系的值，Mysql提供了大量丰富的函数，在进行数据库管理以及数据查询和操作时将会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，更加灵活的满足不同用户的需求。

常用的函数

数值函数(数学函数)
字符函数
日期时间函数
聚合函数
信息函数
加密函数

数值函数:主要是用来处理数值数据的

CEIL(x):返回大于x的最小整数值
例:select ceil(28.55)
结果:29
floor(x):返回小于x的最大整数值
floor(28.5)的结果是28

字符函数

contact(s1,s2……)返回结果为连接参数产生的字符串，如果任何一个参数为null，则返回值为null;
例：select (‘hello’,‘world’);

字母转换大小写函数

LOWER(str)可以将字符串str中的字母字符串全部转换成小写字母。
例:select lower(‘HEllo’,‘WOrld’);
结果:hello world

Upper(str)可以将字符串中的字母字符串全部转换成大写字母

例如:select upper(‘hellO WolRd’);
结果:HELLO WORLD

求字符串长度的函数

length(str)返回值为字符串的字节长度

删除空格的函数

ltrim(s)返回字符串s,字符左侧空格字符被删除
rtrim(s)返回字符串s,字符右侧空格被删除

截取字符串

substrim(s,n,len)带有len参数的格式，从字符串s返回一个长度同len字符串相同的子字符串，起始位置n。n如果是负数，则子字符串的位置起始于字符串结尾的n个字符。

获取指定长度的字符串函数

left(s,n)返回字符串s从开始位置最左边的n个字符
right(s,n)返回字符串s从右边开始的n个字符

replace(str,from_str,to_str)在字符串str中所有出现的字符串from_str被to_str替换，然后返回整个字符串

select replace(‘hello world’,‘world’,‘mysql’);
结果:hello mysql(world 被替换成了mysql)

格式化函数

format(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不含小数
select fromat(1234.567789,2)

日期时间函数

日期和时间函数主要用来处理日期和时间值

获取当前日期的函数

curdate()和current_day()函数作用相同，将当前日期按照’YYYY-MM–DD’或’YYYYMMDD’格式的返回，具体格式根据函数在字符串或是数字语境中而定
select curdate();

获取当前时间的函数

curtime()和current_time()函数作用相同，将当前时间以’HH:MM:SS’或HHMMSS的格式返回，具体格式根据函数在字符串或字符语境中而定
select curtime();

获取当前的日期和时间

now()和sysdate()两个函数的作用相同,均返回当前日期时间值。格式为’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS。

执行日期的加运算

date_add(date,interval expr type)其中，date是一个datetime或date值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔。type为关键字，它指示了表达式被解释的方式,如:YEAR,MONTH,DAY,WEEK,HOUR等。
select date_add(‘2017-01-01’,invertval 5 month);
结果:2017-06-01

计算两个日期之间的间隔天数

datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数
select datediff(‘2017-02-12’,‘2020-03-02’);

日期格式化

date_format(date,format)根据format指定的格式显示date值
date_format时间日期格式
%b:月份的缩写名称(jan,dec)
%c:月份,数字形式(0,……12)
%m:月份，数字形式(00,……12)

聚合函数

有时候并不需要返回实际表中的数据，而只是对获取的数据进行分析和总结。这个时候就需要使用聚合函数

常用的聚合函数

名称	描述
AVG	返回某个列的平均值
COUNT	返回某个列的行数
MAX	返回某个列的最大值
MIN	返回某个列的最小值
SUM	返回某个列的和

系统信息函数

version()->返回当前Mysql服务器的版本号
connection_id()->返回Mysql服务器当前连接的次数，每个连接都有各自的唯一ID
DataBase()和Schema()->返回当前数据库名称
user()->获取用户的名称函数,返回当前函数的用户名称

加密函数

加密函数主要用来对数据进行加密和界面处理，以保证某些重要的数据不被别人获取

加密函数

MD5()：信息摘要算法
MD5(str)：函数可以加密字符串，加密后的值以32位的十六进制数字的二进制字符串形式返回，若参数为null，则返回null
password()：密码算法
Password(str)从原明文密码str计算并返回加密后的密码字符串，当参数位null时，返回null

子查询

子查询是嵌套在其他sql语句内的查询语句
子查询指嵌套在查询内部，且必须始终出现在圆括号内
子查询的结果将作为另一层查询的过滤条件
子查询可以添加到select、update和delete等语句中

例如:

select * from table1 where col1=(select col2 from table2)

子查询的使用场景

使用比较运算引发的子查询
使用[NOT]IN或EXIST操作符引发的子查询
插入记录时使用的子查询

使用比较运算符的子查询

比较运算符
=、>,<,>=,<=,<>,!=

用ANY,ALL,SOME关键字修饰子查询

ANY,ALL,SOME等关键字放在比较运算符的后面
ANY和SOME是同义词，表示满足内层子查询中的任何一个条件
ALL表示需要同时满足所有内层查询的条件

YaruCode

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库学习笔记

文章目录添加新列修改列名修改列的数据类型修改列的排列位置删除列修改表名删除数据库表表分区创建表分区约束空值与非空语法规则创建表时添加非空约束通过修改表为指定列添加非空约束删除非空约束主键约束单字段主键多个字段联合主键(或者交复合主键)通过修改表为列添加主键删除主键唯一约束语法规则如下：添加表时添加唯一约束修改表时添加唯一约束删除唯一约束主键约束和唯一约束的区别：默认约束创建表时添加约束修改表的时候添加约束删除默认约束外键约束使用外键约束创建表时添加外键约束修改表时添加外键约束外键约束的参照操作单表数据记录的
复制链接

扫一扫

专栏目录