MySQL(基础篇)

目录

一.MYSQL特点

二.数据库分类

                 A.关系型数据库(RDBMS)

                 B.非关系型数据库（NOSQL）

三.SQL语言

  一. DDL（Data Definition Language）

           二. 数据类型

           三.DML

          insert：

          update:

          delete：

四.约束

1.什么是约束

2.约束作用

3.约束分类

主键约束

一.单列主键

二.联合主键

三.自动增长

自定增长约束

非空约束

唯一约束

默认约束

零填充约束

---

---

---

一.MYSQL特点

1.基于C和C++编写，保证源码的可移植性

2.支持多个操作系统:windows,Linux,Mac OS等等

3.支持多线程，可充分利用CPU资源

4.为多种编程语言提供API：C，Java，PHP，Python

5.MySQL优化了SQL算法，有效的提高了查询速度

6.开源成本低

二.数据库分类

A.关系型数据库(RDBMS)

1.Oracle数据库 （甲骨文公司） 收费的

2.MySQL数据库（最流行的数据库） 免费版本 源代码开源

3.SQLServer 数据库 （微软开发的数据库）c#（微软公司） windows

4.Sqlite（嵌入式关系数据库） 学习 安卓手机端程序开发

5.db2

B.非关系型数据库（NOSQL）

1.Redis(缓存数据库)

读取数据直接从内存中读取，而不是从硬盘中读取，效率高

redis是将数据存放到内存当中，一旦redis宕机之后，数据都会丢失，需要持久化到硬盘当中，后期根据硬盘中的数据进行快速恢复

2.Mongodb(文档数据库)

MYSQL终端命令

-- 登录mysql 
mysql -u root -h 127.0.0.1 -p

show database; -- 查看数据库

use kaiwen; -- 用kaiwen数据库

show tables;  -- 查看用的数据库下的表

desc customers; -- 查看表结构

三.SQL语言

一. DDL（Data Definition Language）

1.对数据库增删改操作

2.对表结构增删改操作

1.对数据库进行操作

创建数据库:

create database 数据库名称;

创建数据库，判断不存在，再创建

create database if not exists 数据库名称;

创建数据库，并且指定字符集

create database 数据库名称 character set 字符集名;

查询所有数据库的名称:

show databases;

查询当前正在使用的数据库名称:
select database();

使用数据库:

use 数据库名称;

如果存在的话删除数据库:

drop database 数据库名称;

drop database if exists 数据库名称;

2.对表结构进行操作

查看表：

show tables；

创建表:

 create table if not exists 表名(
                列名1 数据类型1[长度] 【字段约束】,
                列名2 数据类型2[长度] 【字段约束】,
                ....
                列名n 数据类型n[长度] 【字段约束】
            );
    * 注意：最后一列，不需要加逗号（,）

示例：

CREATE TABLE league_table(
id int,
name VARCHAR(20),
age int,
address VARCHAR(50)
);

修改表名称：

alter table 表名 rename to 新的表名;

示例:

alter table league_table rename to student_table;

删除表:

drop table 表名;

drop table if exists 表名;

示例:

drop table if exists student_table;

二. 数据类型

日期类

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

字符串类型

三.DML

数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DML与DDL区别？

DDL 对数据库 表结构 增加、修改 删除操作

DML 表结构中的数据 增加（insert）、修改（update） 删除（delete） 查询（select ）

insert：

INSERT INTO 语句用于向表格中插入新的行。

语法格式：

INSERT INTO 表名称 (列名1，列名2 ...) VALUES (值1, 值2,....) // 插入数据列与值的顺序需要一一对应。

INSERT INTO 表名称 VALUES (值1, 值2,....) // 向表中插入所有列

例子：

insert into student_table ()

update:

语法格式：

update 表名称 set 字段=值，字段=值，...； ---直接修改整张表的所有行数据

update 表名称 set 字段=值，字段=值，... where 条件 ---- 根据条件查找到对应行数据 修改

示例:

UPDATE kaiwen_user SET name='裴十' where id=3;

delete：

语法格式：

delete from 表名称 where 条件 根据条件删除表中的数据

TRUNCATE mayikt_user --清空表中所有的数据

示例：

delete FROM kaiwen_user WHERE id=3;

TRUNCATE kaiwen_user;

区别:

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

四.约束

1.什么是约束

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件

例如 用户信息表中 手机号码不允许为空，身份证号码不允许重复。

2.约束作用

用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。

3.约束分类

1.NOT NULL ：非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。

2.DEFAULT：默认值，用于保证该字段有默认值。例如学生表的学生性别

3.PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。

4.UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。

5.CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。

6.FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号

主键约束

1.MySQL 主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可以强制表的实体完整性，同时可以方便根据主键查询该行数据。

2.选取设置主键约束的字段 主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符,主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键,主键应该遵守下面的规则

3.每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值，这是唯一性原则

4.在创建表时设置主键约束 在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的

5.当创建主键的约束时，系统会默认所在的列和列组合 建立对应的索引，方便提高查询效率。

如果使用表中的 一列 主键-------单列主键

使用表中的 多个列（id，手机号码、身份证号码）多列-多列主键（复合主键）

条件：一张表中只能够允许有一个主键、主键值 不允许是为空 主键保证 每行数据完整唯一性,不允许重复的。

mysql 查询高级知识 索引--- 索引 方便提高查询效率

1.添加单列主键

2.添加多列联合主键

使用主键约束 PRIMARY KEY。

一.单列主键

1.定义字段时，指定单列主键

语法格式：

CREATE TABLE 表的名称 (

<字段名> <数据类型> PRIMARY KEY

}

示例：

create table customers(
id int PRIMARY KEY,
tel VARCHAR(11),
sex VARCHAR(1)
);

2.

1. 定义完字段之后，指定主键列（复合主键）

CREATE TABLE 表的名称 (

....

CONSTRAINT 主键名称 PRIMARY key (主键列);

}

示例：

create table customers(

id int,

tel varchar(11),

sex varchar(1)，

primary key(id)

)；

二.联合主键

联合主键（复合主键），由多个列（字段组成）。

注意事项：

1.当主键是有一个列组成时，不能够直接在字段名称后面声明主键约束；

2.一张表只能够允许一个主键

示例：

create table customers(

id int,

tel varchar(11),

sex varchar(1)，

primary key(id,tel)   -- 指定id和手机号码两列组合成一个联合主键

)；

三.自动增长

自定增长约束

设置自动增长

1.在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键自动增加。

2.通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长

3.语法格式如下：

字段名 数据类型 AUTO_INCREMENT

4.默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。

一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。

AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。

AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。

AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

注意：

1.一张表中只能有一个自动增长的字段
2.配合主键一起使用 并且只适用于整数类型
3.自动增长默认的初始值1，每增加一条记录，该字段的值会增加1

示例：

drop table customers;
CREATE TABLE customers(
id int PRIMARY KEY AUTO_INCREMENT,
tel VARCHAR(11),
sex VARCHAR(1)
)

自动增长设置初始值

1.创建表的时候指定:

默认自动增长初始值是从1开始

示例：

mysql 指定自动增长字段初始值 1000

CREATE TABLE customers(
id int PRIMARY KEY AUTO_INCREMENT,
tel VARCHAR(11),
sex VARCHAR(1)
)ATUO_INCREMENT=1000;

2.创建后修改自动增长初始值:

示例:

alter into customer AUTO_INCREMENT 1000;

3. delete与truncate 删除数据区别

delete 删除数据之后，自动增长还是从最后一个删除数据的id基础上做自增;

truncate 清空数据之后 自动增长是从初始值1开始

delete from mayikt_users ---清空表数据

truncate mayikt_users ---清空表数据

TRUNCATE 与delete 不同？

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

自动设定初始值 失效

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

非空约束

1.MySql -- not null 非空约束用于确保当前列的值不为空；在创建表时，如果指定 not null 该字段在插入数据时，不允许为空;

CREATE TABLE customers(
id int NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
phone VARCHAR(11) NOT NULL,
PRIMARY KEY (id)
);

创建表后设定字段为非空

alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;

alter table customers modify name int not null;

唯一约束

MySQL 唯一约束（Unique Key）是指所有记录中该字段的值不能重复出现。例如为 phone 字段加上唯一性约束后，每条记录的 phone 值都是唯一的，不能出现重复的情况。

主键约束 满足唯一性且值不允许为null 而我们的唯一约束 值允许为null

一张表结构中只允许有一个主键约束但是可以有多个唯一约束。

创建表的时候设置唯一约束

CREATE TABLE customers(
id int NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
phone VARCHAR(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_phone (phone)  -- 指定约束名和约束字段
);

修改表时添加唯一约束

alter table customers add CONSTRAINT unique_id UNIQUE (id);

默认约束

默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

1.再创建表的时候设置默认值约束

create table customers(
id int NOT null AUTO_INCREMENT,
name VARCHAR(255) default 'tomcat',  -- name字段默认值为tomcat
phone VARCHAR(11) not null,
PRIMARY KEY (id),
UNIQUE KEY unique_phone (phone)
);

2.修改表的时候添加默认值约束

alter table customers change column name varcha(11) default 'tomcat';

零填充约束

定义了数据类型的长度，如果实际位数小于定义的长度，显示时会在左边用0填充

create table customers(

id int zerofill,

...

);

id(10) 

id=1  0000000001

id=1000  0000001000

五. DQL

1.DQL（Data Query Language）即数据库查询语言，用来查询所需要的信息，在查询的过程中，需要判断所查询的数据与表之间的关，我们可以使用select语句来查询数据。

select * from customers; -- 返回我们customers表的所有列

select name,phone from customers; -- 返回customers表的name字段列和phone字段列

select c.name,c.phone from customers as c;  -- 表的别名称，使用关键字as

select distinct name from customers;    -- 去除name字段列的重复值

select name,age+5 from customers;   -- 查询name和age列，并且返回年龄+5岁

六.运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。例如，学生表中存在一个birth（出生日期）字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。这就是MySQL的运算符，所以熟悉并掌握运算符的应用，我们需要熟悉一下MySQL支持的4种运算符都具备哪些功能。

算术运算符

比较运算符

逻辑运算符

位运算符

算数运算符

算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括：加、减、乘、除、求余。

select name,age+5 as age from customers

比较运算符

select * from customers where name='张三';           -- 查询姓名是张三
select * from customers where name!='张三';        -- 查询姓名不是张三
select * from customers where age<17;           -- 查询年龄小于17岁

select * from customers where age>=18 and age<=40; -- 查询年龄大于=18岁小于=40岁的
select * from customers where age>17 && age<41;     
select * from customers where age between 18 and 40;

select * from customers where age=17 || age=20;    --查询年龄是17岁,20岁的
select * from customers where age=17 or age=20;
select * from customers where age in (17,20);

Like模糊查询

select * from customers where name like '%一%'   -- 查询名字中带'一'的
select * from customers where name like '王%'     -- 查询开头带王的
select * from customers where name like '_小%'   -- 第一个字符是任意字符,第二个是小
select * from customers where name='%李%' and age>30; -- 名字里带李的且年龄大于30

Order by 排序

如果我们需要对读取的数据进行排序，可以利用 order by 根据字段来进行升序或者降序排列 再返回结果。

升序： 从小到大

降序：从大到小

order by 根据字段 数字、字母、汉字

select * from customers order by age; -- 根据年龄从小到大排序(默认)
select * from customers order by age desc; -- 从大到小排序
select * from customers where age>18 order by age; -- 年龄大于18且从小到大排序
select * from customers  order by age desc,id asc;  -- 根据年龄从大到小排序,如果年龄相等,那就根据id从小到大的进行排序


select distinct from customers order by id desc;
-- 根据id去重,然后根据id从大到小排列

七.查询

分页

在mysql中当数据量很大时，显示屏长度有限，我们可以对数据进行分页显示，例如数据总共

100条，每页10条数据，可以分成10页。

-- 方式1：显示前n条
select 字段1，字段2 ... from customers limit n;


-- 方式2：分页显示
-- m表示从mysql数据索引第几条数据开始，n表示取出多少数据
select 字段1，字段2 ... from   limit m,n;


select * from customers order by id desc limit 0,5;  -- 根据id降序后,再分页取出最后5个数据

聚合查询

我们在之前的查询是根据条件一行一行的判断，而使用聚合查询，它是对列的值进行计算，然后返回一个单一的值

select count(*) from customers;     -- 查询表中的总人数

select count(*) from customers where age>18;  -- 查询表中年龄大于18的人数

select sum(age) from customers where class_id='1'; -- 查询class_id=1的年龄的总和

select max(age) from customers;  -- 查询年龄最大的人

select avg(age) from customers;  -- 查询表中所有人的年龄平均值

分组查询

分组查询是指使用 group by 字句对查询信息进行分组。

select count(*) from customers where class class_id='1';  -- 查询class_id=1有多少人
 


分组查询
select count(*) from customers group by class_id; -- 统计每个班级有多少个人

select class_id as 班级,count(*) as 人数 from customers group by class_id;

select class_id as 班级,count(*) as 人数 from customers group by class_id having class_id=1;

select class_id as 班级,count(*) as 人数 from customers group by class_id having count(*)>2 order by count(*) desc;
-- 先查询到数据，然后对数据结果分组，然后统计人数大于2的，再根据人数降序排列

练习

1.查询表中所有学生信息
select * from meite_student;
2.查询表中学生名称和数学成绩
select `name`,maths from meite_student;
3.过滤表中重复数据
select DISTINCT *  from meite_student;
4.统计每个学生的总分
select name 姓名, chinese+english+maths 总分  from meite_student 
5.所有学生总分 加5分
select name 姓名, (chinese+english+maths)+5 总分  from meite_student 
6.查询语文成绩大于80分以上同学
select *  from meite_student  where chinese>80
7.查询总分大于180分以上同学
select name 姓名, (chinese+english+maths) as 总分  from meite_student 
where chinese+english+maths>180
8.查询英语成绩80-90 之间的同学
select  *  from meite_student 
where english >80 and english<90
select  *  from meite_student 
where english  BETWEEN 80 and 90  
9.查询英语成绩不在80-90 之间的同学
select  *  from meite_student 
where english  not BETWEEN 80 and 90  
10.查询英语成绩22,99,33学生 信息
select  *  from meite_student 
where english in(22,99,33)
11.查询所有姓余的学生信息
SELECT * from meite_student where name like  '%余%'
12.查询语文、数学、外语 大于等于72分的学生信息
SELECT * from meite_student where chinese>=72 
and english>=72 and maths>=72
13.查询语文和数学大于等于72分或者总分大于180分以上学生信息
SELECT * from meite_student where
 (english>=72 and maths>=72) or   (chinese+english+maths)>180
14.根据英语成绩字段排序 升序和降序
SELECT * from meite_student ORDER BY english desc  
15.根据总分成绩 从高到低排列
SELECT name as  姓名,(chinese+english+maths) as 总分
 from meite_student order by  (chinese+english+maths) asc  
16.统计学生表中 姓名男和女 分表有多少学生
SELECT sex ,count(*) from meite_student GROUP BY sex 

-- 1. 根据员工的薪资升序或者降序排列
select * from employee order by salary desc ;
select * from employee order by salary asc ;
-- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息
select * from employee where department!='财务部门' order by salary asc 
-- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列
select * from employee where name not  like '_麻%' and salary>=15000
order by salary asc ;
-- 4.查询每位员工综合年薪 根据年薪降序排列
select name as 姓名 ,(salary*12)+bonus as 年薪 from employee
order by (salary*12)+bonus desc 
-- 5.求每个不同部门的平均薪水
select department,avg(salary)  from employee GROUP BY  department
-- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门
select department,avg(salary)  from employee GROUP BY  department 
HAVING avg(salary)>13000
-- 7.求每个部门中最高薪水
select max(salary)   from employee GROUP BY  department
-- 8.求每个部门有多少员工
select  department, count(*)  from employee GROUP BY  department
-- 9.查询员工最高薪资和最低薪资员工信息
select  *  from employee order by  salary desc  limit 1 
select  *  from employee order by  salary asc  limit 1 
-- 10.查询员工最高薪资和最低薪资的差距
select  max(salary)-min(salary)  from employee 

八.concat函数 和 trim 函数 和 replace函数

一.

字符串拼接函数

SELECT CONCAT(`hero_name`,'是T0英雄,他的胜率是',`win_rate`,'出场率是',`appearance_rate`,'ban率是',`ban_rate`) FROM `timi_adc` WHERE `fever`='T0';

输出

后羿是T0英雄,他的胜率是0.4995出场率是0.303ban率是0.0108
鲁班七号是T0英雄,他的胜率是0.4995出场率是0.303ban率是0.0108
伽罗是T0英雄,他的胜率是0.5261出场率是0.115ban率是0.6212
孙尚香是T0英雄,他的胜率是0.4969出场率是0.315ban率是0.0212

二.

去除空格函数

trim()函数可以去掉查询结果中的空格，但是不会修改原数据。

SELECT
  trim(hero_name),
  trim(fever)
FROM
  timi_adc
WHERE
  id = 20;

语法扩展

trim可以精准的去掉前面或者后面的空格

TRIM( BOTH|LEADING|TRAILING removed_str FROM str);

例如去掉id为21这条数据中fever尾部的Q

SELECT
  TRIM(
    TRAILING 'Q'
    FROM
      fever
  )
FROM
  timi_adc
WHERE
  id = 21;

三.

使用replace()函数来修改字符串中间的值

语法：

UPDATE table_name 
SET colunm_name = 
REPLACE(column_name,string_find,string_to_replace) 
WHERE conditions;

九.MySQL多表关系

实际的项目开发中，一个项目通常需要很多张表才能够完成，例如在学生系统中 有学生班级表(stu_class)、学生信息表(stu_info)等多张表。这些表存在一定的关系。

1.一对一 使用的比较少

2.一对多

3.多对多

2.右连接

CREATE TABLE IF NOT EXISTS ykd_student(
id INT NOT NULL PRIMARY KEY,
`name` VARCHAR(10),
birthday VARCHAR(30),
course_id INT,
gender VARCHAR(1),
gmt_created DATETIME,
gmt_modified DATETIME
);

CREATE TABLE IF NOT EXISTS ykd_course(
id INT NOT NULL PRIMARY KEY,
`name` VARCHAR(20),
teacher_id INT,
gmt_created DATETIME,
gmt_modified DATETIME
);


CREATE TABLE IF NOT EXISTS ykd_teacher(
id INT NOT NULL PRIMARY KEY,
`name` VARCHAR(10),
gmt_created DATETIME,
gmt_modified DATETIME
);

INSERT INTO ykd_teacher VALUES(1,'不知火舞',NULL,NULL),(2,'兰陵王',NULL,NULL),(3,'王昭君',NULL,NULL),(4,'蔡文姬',NULL,NULL);

INSERT INTO ykd_student VALUES(1,'孙悟空','公元前578年六月初一',1,'男',NULL,NULL),(2,'刘备','公元161年7月16日',3,'男',NULL,NULL),(3,'妲己','公园1076年七月初三',4,'女',NULL,NULL),(4,'鲁班七号','公园2016年11月26日',2,'男',NULL,NULL);

INSERT INTO ykd_course VALUES(1,'如何击杀脆皮',5,NULL,NULL),(2,'极限一换一',2,NULL,NULL),(3,'蹲草的艺术',3,NULL,NULL),(4,'瑶和明世隐谁才是辅助之王',4,NULL,NULL);


SELECT * FROM ykd_student RIGHT JOIN ykd_course ON
ykd_student.course_id=ykd_course.id;

SELECT * FROM ykd_course 
LEFT JOIN ykd_student ON ykd_course.id=ykd_student.course_id 
LEFT JOIN ykd_teacher ON ykd_course.teacher_id=ykd_teacher.id; 

---