MYSQL入门（个人学习 持续更新..）

基础-sql-通用语法和分类

SQL通用语法：

1.分号结尾

2.可使用空格 增加可读性

3.不区分大小写！！

4.注释：

  单行注释：-- 或#（MySQL特有）

  多行注释：/*  */

SQL分类：

DDL:数据定义语言。定义数据库对象：数据库、表、字段

DML:数据操作语言。增删改

DQL:数据查询语言。查询记录

DCL:数据控制语言。创建数据库用户、控制数据库的访问权限

DDL:

数据库操作：

查询所有数据库：SHOW DATABASES;

查询当前数据库：SELECT DATABASE();

创建：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则];

删除：DROP DATABASE[IF EXIST]数据库名

使用：USE 数据库名

表操作-查询

查询当前数据库所有表：SHOW TABLES; (需要先使用数据库)

查询表结构：DESC 表名;

查询指定表的建表语句：SHOW CREATE TABLE 表名;

表结构-创建

CREATE TABLE 表名（

        字段1 字段1类型[ COMMENT 字段1注释 ],

        ....

）[ COMMENT 表注释 ]；

注意：最后一个字段末尾没有分号

表操作-数据类型

数值类型、字符串类型、日期时间类型

数据类型：TINYINT(1),SMALLINT(2),MEDIUMINT(3),INT OR INTEGER(4),BIGINT(8),FLOAT(4),DOUBLE(8),DECIMAL(依赖精度和标度的值)

字符串类型：CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT,MEDIUMBLOB,MEDIUMTEXT,LONGBLOB,LONGTEXT

日期时间类型：

DATE,TIME,YEAR,DATETIME,TIMESTAMP(有时间戳)

表操作-修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束];

修改字段：

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型:

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释] [约束];

删除字段：

ALTER TABLE 表名 DROP 字段名;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

删除表：

DROP TABLE [IF EXSITS] 表名;

删除指定表，并重新创建该表：

TRUNCATE TABLE 表名;

MySQL图形化界面：

DataGrip!!

DML:

数据操作语言。对数据库中表的数据记录进行增删改操作。

添加：INSERT

修改：UPDATE

删除：DROP

DML-添加数据

1.给指定字段添加数据：

INSERT INTO 表名（字段名1,字段名2，...）VALUES(值1,值2...);

2.给全部字段添加数据

INSERT INTO 表名 VALUES(值1,值2，...)；

3.批量添加数据

INSERT INTO 表名（字段名1，字段名2...）VALUES(值1,值2，...)，(值1,值2，...)，(值1,值2，...);

INSERT INTO 表名  VALUES（值1,值2,..）,（值1,值2,..）,(值1,值2,...);

注意：

        插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

        字符串和日期型数据应该包含在引号中。

        插入的数据大小，应该在字段的规定范围内。

DML-修改数据

更新：

UPDATE 表名 SET 字段1 = 值1,字段2 = 值2,...[ WHRER 条件]；条件可有可无，没有条件的话一般指所有。

删除：

DELETE FROM 表名 [WHERE 条件];

无条件：删整张表。

不能删除某一字段的值（想实现这个功能可以使用UPDATE）。

DQL:

Data Query Language (数据查询语言)

SELECT 字段、FROM 表名；

WHRER 条件；

        聚合函数！

GROUP BY 分组字段列表、HAVING分组后条件列表；

ORDER BY 排序字段列表；

LIMIT 分页参数。

DQL-基本查询：

1.查多个字段

SELECT 字段1,字段2,字段3,...FROM表名;

SELECT * FROM 表名;  注：*的功能是查询并返回所有字段。

2.设置别名

SELECT 字段1 [AS 别名1]，字段2 [AS 别名2]... FROM 表名;

3.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询：

1.语法

SELECT 字段列表 FROM 表名 WHRER 条件列表;

2.条件

有个大表格。分为比较运算符（大于、小于、之间...）和逻辑运算符（是、与、非）。

模糊匹配：

_:单个字符                %：任意多个字符

例一、查询名字是两个的：select * from emp where name like '__';

例二、查询身份证号末尾一位是X的：select * from emp idcard like '%X';

DQL-聚合函数：

1.将一列数据作为一个整体，进行纵向计算。

2.常见聚合函数

count        统计数量 

max        最大值

min        最小值

avg        平均值

sum        求和

3.用法

SELECT 聚合函数（字段列表） FROM 表名;

例：统计在西安工作的员工年龄之和

select sum(age) from emp where workaddress = '西安'; 

DQL-分组查询：

GROUP BY

1.语法

SELECT 字段列表 FROM 表名 [WHERE 条件 ] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where 和 having 的差别：

where 是分组前进行筛选，having 是分组后进行筛选。

where 不能对聚合函数进行判断，而 having 可以。

例：

1.根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender ;

2.查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址。

select workaddress,count(*) address_count from emp where age<45  group by workaddress having  count(*) (address_count) >= 3;

注意：

执行顺序：where > 聚合函数 >having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序查询：

ORDER BY

1.语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

（多字段排序）

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

2.排序方式

ASC：升序（默认值）

DESC：降序

例：根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序。

select * from emp order by age asc,entrydate desc;

DQL-分页查询：

LIMIT

1.SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；

注意：

1.起始索引从0开始，起始索引= （查询页码 -1）*每页显示记录数。

2.分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是LIMIT。

3.如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

例：查询第2页员工数据，每页展示10条记录。

select * from emp limit 10,10;

DQL-执行顺序

我们先来看看DQL语句的编写顺序：

select ...from ...where... group by ...having.. order by... limit...

而他的执行顺序是：

from...where...group by...having...select...order by...limit..

DCL:

data control language(数据控制语言)，用来管理数据库用户，控制数据库的访问权限。

DCL-用户管理

1.查询用户

USE mysql;

SELECT * FROM user;

2.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3.修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4.删除用户

DROP USER '用户名'@'主机名';

注意：

1.主机名可以使用%通配。

2.这类SQL开发人员比较少，主要是由数据库管理员使用。

DCL-权限控制

权限                                                                          说明

ALL,ALL PRIVILEGES                                               所有权限

SELECT                                                                     查询数据

INSERT                                                                      插入数据

UPDATE                                                                     修改数据

DELETE                                                                     删除数据

ALTER                                                                        修改表

DROP                                                                         删除数据库/表/试图

CREATE                                                                     创建数据库/表

语法：

1.查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2.授予权限

GRANT 权限列表 ON 数据库名、表名 TO '用户名'@'主机名';

3.撤销权限

REMOVE 权限列表 ON 数据库名、表名 FROM  '用户名'@'主机名';

注意：

多个权限之间，使用逗号分隔。

授权时，数据库名和表名可以使用*进行通配，代表所有。

函数：

字符串函数：

函数                                                                              功能

CONCAT(S1,S2,S3...,SN)                                        字符串拼接，将S1,...SN拼成一个字符串

LOWER(str)                                                               将字符串str全部转为小写

UPPER(str)                                                                将字符串str全部转为大写

LPAD(str,n,pad)                               左填充，用字符串pad对str左边进行填充，达到n个字符串长度

RPAD(str,n,pad)                              右填充，用字符串pad对str右边进行填充，达到n个字符串长度

TRIM(str)                                         去掉字符串头部和尾部的空格

SUBSTRING(str,start,len)               返回从字符串str从start位置起的len个长度的字符串

用法：

SELECT 函数（参数）;

例子：企业员工的工号，统一为5位数，目前不足5位数的全部在前边补0.

update emp set workno = lpad(workno,5,'0');

数值函数：

函数                                                                   功能

CEIL(x)                                                        向上取整

FLOOR(x)                                                    向下取整

MOD(x,y)                                                     返回x/y的值

RAND()                                                        返回0~1内的随机数

ROUND(x,y)                                                求参数x的四舍五入的值，保留y位小数

用法：

select 函数（参数）;

例子：通过数据库的函数，生成一个六位数的随机验证码。

select lpad（round(rand()*1000000,0）,6,'0');          不过把round改成ceil比较好

日期函数：

函数                                                                        功能

CURDATE()                                                           返回当前日期                                                   

CURTIME()                                                           返回当前时间

NOW()                                                                   返回当前日期和时间

YEAR(date)                                                           获取指定date的年份

MONTH(date)                                                        获取指定date的月份

DAY(date)                                                              获取指定date的日期

DATE_ADD(date,INTERVAL expr type)         返回一个日期/时间加上一个时间间隔expr后的时间值

DATEDIEF(date1,date2)                       返回起始时间date1和结束时间date2之间的天数(d1减d2)

例子：查询所有员工的入职天数，并根据入职天数倒序排序。

select name,datedief(curdate(),entrydate) as 'entrydays' from emp oder by entrydays desc  ;

流程函数;

常用，用来进行条件筛选，提高语句的效率

函数                                                                         功能

IF(value,t,f)                                       如果value为true,则返回t,否则返回f

IFNULL(value1,value2)                   如果value不为空（不是Null），返回value1,否则返回value2

CASE WHEN [val1] THEN [res1]...ELSE [ default ] END   

如果val1为true,返回res1,...否则返回default默认值

例：查询emp表的员工姓名和工作地址（北京/上海--》一线城市，其他--》二线城市）

select name,

     (case when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)as '工作地址'

from emp;

CASE [expr] WHEN [val1] THEN [res1]....ELSE [ default ] END

如果expr的值等于val1,返回res1,...否则返回default默认值

例：做一个分级，80以上优秀，60以上合格

select 

id,

name,

(case when math >=80 then '优秀' case when math >=60 then '合格' else '不及格' end) '数学',

from score;

约束：

概述：

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2.目的：保证数据库中数据的正确，有效性和完整性。

3.分类： 

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识、要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证一个字段值满足某一条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意：约束是作用于字段上的，可以在创建表/修改表的时候添加约束。

演示：

create table user(

        id int primary key auto_increment comment '主键',

        name varchar(10) not null unique comment '姓名',

        age int check (age > 0 && age <=120 )comment '年龄',

        status char(1) default '1' comment '状态',

        gender char(1) comment '性别',

)comment '用户表';

外键约束：

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

之下有个父表子表的关系，或者主表从表的关系。

外键关联后，可以保证数据的一致性和完整性。

语法：

--添加外键：

CREATE TABLE 表名（

        字段名 数据类型

        ...

        [CONSTRANT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 （主表列表）

）;

或者

ALTER TABLE 表名 ADD CONSTRANT 外键名称 FOREIGN KEY(外键字段名) REFERECES 主表 (主表列名);

例：

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

--删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键删除更新行为：

行为	说明
NO ACTION	当父表存在删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
RESTRICT	当父表存在删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
CASCADE	当父表存在删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键的值为null
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值

语法：

ALTER TABLE 表名 ADD CONTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名（主字段表名） ON UPDATE CASCADE ON DELETE CASCADE;

多表查询：

多表关系：

一对多：

案例：部门（1）和员工（N）的关系

关系：一个部门对应多个员工，一个员工对应多个部门

实现：在多的一方建立外键，指向一的一方的主键。

多对多：

案例：学生（N）选课(N)

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

一对一：

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

多表查询概述：

在多表查询中，需要消除多余的笛卡尔积，比如，在查人和部门对应关系的这张表中，用两个单表查询的方式来操作的话，就会多出许多没必要的数据，而多表查询就是用来解决这一问题的。