Mysql数据库学习笔记

添加新列

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

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)要求主键列的数据唯一，并且不允许为空，主键能够唯一地标识表中的一条记录

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

单字段主键

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

1. 在定义列的同时指定主键
   列名 数据类型 primary key;
2. 在列定义的后边指定主键
   [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)要求该列唯一，允许为空，唯一约束可以确保一列或者几列不出现重复值。

语法规则如下：

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

列名 数据类型 unique

2. 定义完所有的列之后指定唯一约束:
   [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表示需要同时满足所有内层查询的条件