（一）MySQL基础

 

一.实操教程

基础介绍：https://www.runoob.com/mysql/mysql-tutorial.html

安装部署：https://www.cnblogs.com/Agent9527/p/13194128.html

命名规范：https://cloud.tencent.com/developer/article/1054482

1.创建数据库

create databases 库名;　　# 所有mysql语句小写默认为大写，一定要小写时可加个双引号

普通用户创建

[root@host]# mysqladmin -u root -p create 库名
Enter password:******

2.切换数据库

use 库名;

 3.创建表

create table [if not exists] 表名
(
字段名1 数据类型 [列级完整性约束条件] [默认值],
[字段名2 数据类型 [列级完整性约束条件] [默认值]],
[... ...],
[表级完整性约束条件]
)[engine=引擎类型];

3.1 [ ]

　　解释：根据情况选。

　　" [ ] "为可选框

3.2 字段名

　　解释：根据命名规范给列取名。

　　由编译者自命名

3.3 数据类型

　　解释：插入该列的元素为指定的类型

　　数值类型【只列现学常见，不排除后续更新】

　　　　int[(m)][unsigned][zerofill]：普通大小的整数。带符号范围(-2 147 483 648，2 147 483 647)无符号范围(0，4 294 967 295)。

　　　　float(m,d)：m表示最大长度，d表示小数位数。float(5,2) 浮点数最大长度为5位数，小数部分为2位。范围无符号0.0 ~99999.9有符号-99999.9~99999.9

　　日期和时间类型　　

　　　　date：范围‘1000-01-01’到‘999-12-31’，MySQL以‘YYY-MM-DD’格式显示DATE值。

　　　　datetime：日期和时间的组合。范围‘1000-01-01 00:00:00’到‘9999-12-31 23:59:59’，MySQL以‘YYY-MM-DD HH:SS’格式显示datetime值。

　　　　time：时间类型，范围是‘-838：59：59’到‘838：59：59’。MySQL以‘HH:MM:SS’格式显示time值。

　　字符串类型

　　　　char[(m)]或character[(m)]：固定长度的字符数据类型，m的范围是0~255，用于保存文本信息。

　　　　varchar(m)：可变长的字符数据类型，m的范围是0~65 535。

3.4 列级完整性约束条件

　　解释：制定一系列条件，在插入数据时是否满足该列的制定的条件。这就是约束条件

　　primary key主键：每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL。注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。这用到联合主键：primary key(列1,列2)放表级完整性约束条件位置，所谓的联合主键，就是这个主键是由一张表中多个字段组成的。约束就是多行数据中字段1、字段2的值不能同时一样。

　　unique候选键：能唯一标识表（关系）中行(元组)的列（属性）。AB（行）元素可重复（列）中元素不能重复。

　　foreign key外键：用于多表外连接时的标识列，同时存在表1和表2中，它不是表1的主键，而是表2的主键，就可以说他是表1的外键。

　　unsigned：禁用负值，用此类型可以增加数据长度

　　auto_increment自增属性：每个表只能定义一个auto_increment，并且必须在该列定义主键或候选键约束。默认为1，当插入null数据或数值0时默认1、0+1。

　　null：元素可以为空值。注意这里空串也属于一个有效的值。例：‘’ 中间无字符。

　　not null: 该列元素值不能为空。不设默认null。

　　default默认值:  指在向数据表中插入一行数据时没有给其中默认的一列赋值。那么该列就会默认为default指定的值。default '汉'。

3.5 引擎类型

　　解释：存储引擎就是如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。可参考https://www.cnblogs.com/yinjw/p/11831702.html学习

　　命令：show engines; 可以查看系统支持的引擎类型和默认引擎。其中InnoDB是系统默认的引擎，支持事务处理，是事物型数据库的首选引擎。

4.查看表

4.1查看表名称

show tables [{form | in} 库名];  使用{form | in}可显示非当前数据库中的数据库表名称

4.2查看数据表基本结构

desc 表名;

4.3查看数据表详细结构

show create table 表名;

5.增-插入数据

5.2方法一、不指定数据字段

insert into 表 values('数据1',null,'数据3','数据4'...);

5.3方法二、指定字段

insert into tbs(字段1,字段2,字段3,字段4) values('数据1','数据2','数据3','数据4');  # 指定字段主键必须是排第一

a.同时插入多条数据

insert into 表(字段1,字段2,字段3) values('数据1','数据2','数据3'),('二行数据1','数据2','数据3')...('n行数据1');

b.插入查询数据

insert into 表(字段2,字段2,字段3) select 字段1,字段2,字段3 from 表;

c.使用replace插入数据

replace into 表('主键字段1','字段2','字段3') values('数据1','数据2','数据3');   # 即使有主键约束也可以覆盖原来的数据，某个字段定义外码时会出错。

6.删数据

6.1删除特定数据

delete from 表名 where 字段名='指定数据';

6.2带子查询删除

delete from 库.表1 where 字段=(select 字段1 from 表2 where 字段2='程序设计');   # 当删除数据的表与设置删除条件的表不相同可以使用

6.3删除所有数据

delete from 库.表;    # 表仍存在 

a.使用truncate删除数据

truncate 库.表;    # 直接删除原来的表并重新创建一个新表，速度比delete快

7.改数据

a.修改特定数据记录

update 表 set 字段2='修改数据1',字段3='修改数据2', where 字段1='指定数据1';

b.修改所有数据记录

update 库.表 set 字段1=字段1*数值;    # 修改字段1的全部值；字段1类型必须是数值类型可进行+-*/

c.带子查询的修改

update 库.表1 set 字段1='0' where 字段2=(select 字段2 from 表2 where 列='指定条件');    # 字段1满足字段2的条件时设为 0

8.查数据

总查询结构

select [all | distinct | distinctrow] 字段1 [as] 字段1别名,[字段2]    # 查 [所有、去重、去重] 指定字段1 指定字段2
from 表1或视图1,[表2或视图2]    # 从表1或视图1,表2或视图2
[where<条件表达式>]    # 设置筛选条件
[ group by 字段1 [having<条件表达式>] ]    # 指定字段1分组，having分组结果设置筛选
[ order by 字段2 [asc | desc] ]   # 根据字段2升序降序 
[ limit [m,] n ]    # 从第m+1行开始显示，共显示n行

a.单表查询

a1.select 指定字段

select 字段1,字段2,字段3...n字段 from 表名;

a2.select 去重后的字段

select distinct 字段1,[字段2],...[n字段] from 表名;

a3.select 所有字段

select * from 表名;

a4.select 经过计算的值

select 字段1,字段2,'新增字段3', year(now()-year(1998-11-13))
from 表名;    # 新增字段3中在表中是不存在的，now()获取当前日期+时间减去当前的年月时间。再用()括号按年取得到年龄

a5.select 字段的别名

注意：另外字段不能出现where中

select 字段1 as 1,字段2 2 from 表名;　　# 将字段as取为更短更通俗的名称as可选可不选

a6.where 比较大小的查询

比较：>、<、>=、<=、=、[ !=、<> ]、!>、!<、NOT+含比较运算符的公式例："NOT 字段 = " 与 "字段!=" 是一样的。

select 字段1,字段2
from 表名
where 字段1>=22;    # 查字段1大于等于22的值

a7.where 带between...and关键字的范围查询

select 字段1,字段2
from 表名
where 字段2 [not] between 2 and 10; # 无not参数，值在范围[2,10]闭区间内；有not参数，那么这个值不在[2,10]闭区间内

a8.where 带in关键字的集合查询

select *
from 表名
where 字段2 [not] in('北京','天津','上海');    # 查字段2里边包含北京、天津、上海的数据。用了not那么就是不包含的意思

a9.where 带like关键字的字符串匹配查询

模糊搜索：%：包括0以及任意长度的字符。另外不能匹配空值null  _：代表任意单个字符

要查询的元素本身包含%或_：需要用到escape换码字符。例如：信息系统_分析与设计

select 字段1,字段2
from 表
where 字段1 [not] like '王%';    # 查询字段1中含"王"的数据
where 字段1 [not] like '%#_%' escaps '#';　　# 将#后面的"_"转换为普通字符

a10.where 正则表达式

select 字段1,字段2
from 表名
where 字段1 [not] [regexp|rlike] '正则表达式';    # 匹配字段1含系统等价于 like '%正则表达式%' 

<字符串>：匹配包含指定字符串的文本。例 'fa'，匹配：fan,afa,faad。拓展" | " : 例'fa|fb|fc'，匹配：fadi,sfbxs,dsfca

[ ]：匹配[ ]中的任何一个字符。例 '[ab]'，匹配：bay,big,app

[^]：匹配不在[ ]中的任何一个字符。例 '[^ab]'，匹配：desk,coll,six

^：匹配文本的开始字符。例 '^b'，匹配：bed,bridge,book

$：匹配文本的结尾字符。例 'er $'，匹配：driver,worker,farmer

.：匹配任意单个字符。例 'b.t'，better,bit,bite

*：匹配0个或多个 * 前面指定的字符。例 'f*n'，匹配：fn,fan,begin

+：匹配+前面的字符1次或多次。例'ba+'，匹配：bat,baa,battle,bala

{n}：匹配前面的字符至少n次。例'b{2}'，匹配：bb,bbbb,bbbbbbb

a11.where 带 is null 关键字的空值查询

select * from 表 where 字段 is null;  # 查看字段为NULL的数据

a12.where 带and或or的多条件查询

select 字段1,字段2,字段3
from 表
where (字段1 > 值 or 字段1 < 值) and 字段3==值;

a13.对查询结果进行ASC | DESC 排序

order by 字段1 [ asd | desc ]：对查询结果集中记录，按指定字段1升降序

select *
from 表 order by 字段1 [ ASC |DESC ];  # 升降序

a14.限制查询结果的数量

select 字段1,字段2,字段3
from 表
order 字段1
limit n,m;  # 从第n+1行开始显示；m表示显示的行数

b.聚合函数查询

select 函数 from 表;

函数说明

注意：distinct为去重，all为默认值

count([ distinct | all ] * ) : 统计数据表中的行数

count([ distinct | all ] <列> ) : 统计数据表的一列中值的个数

max([ distinct | all ] <列> ) : 求数据表的一列值中的最大值

min([ distinct | all ] <列> ) : 求数据表的一列值中的最小值

sum([ distinct | all ] <列> ) : 计算数据表的一列值中的总和

avg([ distinct | all ] <列> ) : 计算数据表的一列值中的平均值

c.分组+聚合查询

说明：把指定的A列中相同元素进行分组；求B列count\sum\min\avg...值

select 字段1,函数1(字段2),函数2(字段3)
from 表1
group by 字段1  # 分组
having [ 函数1(字段2) | 函数2(字段3) ] 操作符 值;  # 筛选操作符：>、<、==、!=、<=、>=

d.连接查询

说明：查询涉及到两以上，称之为“连接查询”

d1.交叉查询

交叉查询又称“笛卡儿积”。原理：返回的记录行数等于两张表行数的乘积，其连接结果会产生一些没有意义的记录

[select * from 表1 join 表2]或[select * from 表1,表2];

d2.内连接

内连接[inner join]...[on]：在交叉连接基础上设置条件过滤一些没有意义的数据。

select 表1.字段1,表2.字段4,表3.字段6
from 表1 [inner join] 表2 [inner join] 表3
on 表1.字段1=表2.字段1 and 表2.字段2=表3.字段2
and 字段4="值";

或 用","连接

select 表1.字段1,表2.字段4,表3.字段6
from 表1,表2,表3
where 表1.字段1=表2.字段1 and 表2.字段2=表3.字段2
and 字段4="值";

1.等值连接

根据相同元素的AB列将表1、2相连。当比较运算符“ = ”时表示等值连接，使用其他运算符为非等值连接

select * from 表1 inner jion 表2 on 表1.字段1=表2.字段1；

2.自连接

说明：某个表对自身进行连接称为 “自连接”

注意：使用自连接时需要为表指定多个不同的别名，对所有查询的字段得用引用的表名限定，否则select操作会失败

需求：我要查与这门课程分数相同的其他课程信息。根据表b指定课程作为条件，去查询表a中连接列相同的分数

select a.*
from 表1 a join 表1 b
on a.字段1=b.字段1　　# 连接列 
where b.字段2=课程;　　# 条件必须在b表中指定条件查询

或用 “,”

select a.*
from 表1 a,表1 b
where a.字段1=b.字段1
and b.字段2=课程;

3.自然连接

注意：只有两表列名相同的同时才可以使用，否则返回的是笛卡尔积

select a.字段1,a.字段1
from 表1 a natural join 表1 a;

d3.外连接

说明：外连接分为左外连接、右外连接，也称左连接；右连接

1.左连接

说明：返回左边的基表所有记录，并用这些记录与右边参考表中的记录进行匹配，如果左表在右表中没有匹配的记录，那么就和右边的“万能行”进行连接，即右表对应的字段值为空值NULL

select *
from 左表 a left join 右表 b
on a.字段1=b.字段2；

2.右连接

说明：与左连接相反

select *
from 左表 a right join 右表 b
on a.字段1=b.字段2；

e.子查询

a.带IN的子查询

b.带比较运算符的子查询

c.带EXISTS关键字的子查询

f.联合查询