Mysql基础

最新推荐文章于 2024-07-21 19:06:05 发布

非凡王衍钦

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量333

点赞数 15

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/sixudhjj/article/details/140530880

版权

一、数据库概述

为什么学习数据库

为了实现数据持久的保存起来；

使用完整的管理系统统一管理，可以实现结构化查询，方便管理；

数据库(DataBase)为了方便数据的存储和管理，它将数据按照待定的规则存储在磁盘上，就是一个存储数据的仓库。

数据库的相关概念

DB:数据库(DataBase)

存储数据的容器，它保存了一系列有组织的数据。

DBMS:数据库管理系统(DataBase Management System)

又称为数据库软件或数据库产品，用于创建或管理DB。

二、MySQL数据库

常见的数据库产品：

国外

MySQL 快捷、可靠开源、免费

Oracle:功能强大,收费.

SQL Server(微软): 只能安装在Windows操作系统

DB2 (IBM):适合处理海量数据,收费.

国内

南大通用GBASE: 天津南大通用数据技术股份有限公司

达梦:武汉达梦数据库股份有限公司

人大金仓:北京人大金仓信息技术股份有限公司

神通:神舟通用公司

MySQL是一个关系型数据库管理系统，由瑞典MySQLAB 公司开发，目前属于Oracle旗下产品。MySQL 流行的关系型数据库管理系统。

MySql是一种关系数据库管理系统。

MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。

MySql数据库服务器具有快速、可靠和易于使用的特点。

MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.

mysql商业版与社区版

MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用

MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维

护，可以免费使用

命令行方式连接mysql

登录：mysql [-hlocalhost -P3306]（本机可省略） -uroot -p（可以直接写密码，不能有空格）

-h：主机名

-P：端口号

-u：用户名

-p：密码

退出：exit

MySQL的常用命令

查看当前所有的数据库：show databases;

选择指定的库：use 库名

查看当前的所有表：show tables;

查看其他库的所有表：show tables from 库名;

查看mysql版本 select version();

安装可视化客户端工具

SQLyog / Navicat

三、SQL--DDL

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL优点：

不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL

简单易学，灵活使用可以进行非常复杂和高级的数据库操作

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言。

● 常用的语句：create ,alter,drop,rename

创建,删除数据库

创建数据库并设置编码格式

CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]

删除数据库

DROP DATABASE 数据库名 / [IF EXISTS数据库名];

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

数据库表的基本概念

1、数据表

表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。

表的最简单形式是由行和列组成，分别都包含着数据。每个表都有一个表头和表体，表头定义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、字段

字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。

数据表的一列包含了特定字段的全部信息。

3、记录

记录也被称为一行数据，是表里的一行数据。

设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

1.表名(表信息)

2.表中的字段

3.字段的数据类型和长度

4.哪些约束

设计表(数据类型)

char（n）长度为n的定长字符串,最大长度255个字符

varchar(n）最大长度为n的可变长字符串

date 日期，包含年月日

datetime 年月日+时分秒

整数

signed 有符号默认是有符号

unsigned 无符号

浮点：

decimal

数据类型(M,D)

M：精度，数据的总长度；

D：标度，小数点后的长度

TEXT列字符字符串

4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

它的长度：

TINYTEXT：最大长度255个字符(2^8-1)

TEXT：最大长度65535(2^16-1)

MEDIUMTEXT：最大长度16777215(2^24-1)

LONGTEXT最大长度4294967295(2^32-1)

主键：在一张表中代表唯一的一条记录,不能为空,不能重复

约束:

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束设置条件

外键约束

主键自动增长,设置为自动增长时,只能为整数类型

AUTO_INCREMENT

默认值

DEFAULT default_value

字段注释:comment '注释'

创建表语法:CREATE TABLE 表名(列名数据类型 [约束] [默认值] [ 注释],......)

CREATE TABLE t_user(

id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT],

number INT(5) NOT NULL,

stuname VARCHAR(10) NOT NULL,

age INT(3) CHECK(age>18),

birthday DATE,

weight DOUBLE,

opertime datetime,

[CONSTRAINT 约束名约束规则]

)

删除表

DROP TABLE [if exists ]表名

修改表名

RENAME TABLE 旧表名 TO 新表名

复制表结构

CREATE TABLE 新表名 LIKE 被复制表名;

示例代码：

--  创建表语法
-- char(n)定长的字符串，固定存储n个字符，长度如果不够n，默认补空格
-- VARCHAR(n) 可变的字符串 n是最大长度，如果只存储两个字符，实际占有2个空间位置
CREATE TABLE student(
	number INT,
	NAME VARCHAR(6),
	gender CHAR(1),
	birthday DATE,
	phone VARCHAR(11),
	height DECIMAL(3,2),
	reg_time DATETIME

)

-- 删除表结构
DROP TABLE student

-- 创建表  并添加约束规则
-- PRIMARY KEY 设置主键约束，添加主键约束后，值就不能重复，一个表中只能有一个列是主键
--   主键列可以唯一的表示某一行数据
--    mysql中主键可以设置为自动增长 AUTO_INCREMENT
-- NOT NULL 不能为空 约束
-- UNIQUE 唯一性约束
-- 检查约束 设置条件
CREATE TABLE student(
	number INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
	NAME VARCHAR(6) NOT NULL COMMENT '姓名',
	gender CHAR(1) COMMENT '性别',
	birthday DATE COMMENT '生日',
	phone VARCHAR(11) NOT NULL UNIQUE COMMENT '电话号码',
	height DECIMAL(3,2) CHECK(height<3) COMMENT '体重',
	reg_time DATETIME COMMENT '注册时间'
)
-- 修改表名
RENAME TABLE student TO stu

-- 复制表结构
CREATE TABLE student LIKE stu

运行结果：

在表数据中可看到：

右键点击刚刚创建的表student点击改变表可看到：

里面的注释就是COMMENT‘ ’中的内容

四、SQL--DML

数据操纵语言DML（Data Manipulation Language）

常用语句： insert,delete,update

1.插入数据

INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);

INSERT INTO 表名 set 列名1=值1,..列名n=值n;

INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);

INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

2.修改数据

UPDATE 表名 SET 列名 = ‘新值’WHERE 条件

3.删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

代码示例：

-- DML 数据操作 增 修 删
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)VALUES('科比','男','2020-1-20','1522233235',1.98,NOW()) 

INSERT INTO student SET NAME='库里',gender='男',birthday='1990-3-6',phone='1265554'

-- 插入多条语句
INSERT INTO student(NAME,gender,birthday,phone,height,reg_time)
		    VALUES('欧文','男','1990-1-20','1522233234',1.83,NOW()),
                           ('布朗尼','男','2004-6-20','1513344',1.88,NOW())  
                           
-- 备份student表中数据到stu                                       
INSERT INTO stu(number,NAME,gender,birthday,phone,height,reg_time)SELECT * FROM student

-- 修改记录
UPDATE stu SET NAME='梅西',height=1.70 WHERE number=1

-- 删除记录
DELETE FROM student WHERE number = 2

运行结果：

五、SQL--DQL

函数

类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用

分类：

单行函数：会对查询中的每一行数据进行处理，如concat、length、ifnull等

分组函数：做统计使用，又称为统计函数、聚合函数、组函数

1.单行函数

1.1字符函数

length()：获取参数值的字节个数

char_length()获取参数值的字符个数

concat(str1,str2,.....)：拼接字符串

upper()/lower()：将字符串变成大写/小写

substring(str,pos,length)：截取字符串位置从1开始

instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0

trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)

lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度

rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度

replace(str,old,new)：替换，替换所有的子串

1.2数学函数

round(数值)：四舍五入

ceil(数值)：向上取整，返回>=该参数的最小整数

floor(数值)：向下取整，返回<=该参数的最大整数

truncate(数值,保留小数的位数)：截断，小数点后截断到几位

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()：获取随机数，返回0-1之间的小数

代码示例1：

-- 数学函数
SELECT NAME,gender,ROUND(height),CEIL(1.1),FLOOR(1.9) FROM student

运行结果1：

示例2：

SELECT NAME,gender,TRUNCATE(height,1),height,RAND() FROM student

结果2：

1.3日期函数

now()：返回当前系统日期+时间

curdate()：返回当前系统日期，不包含时间

curtime()：返回当前时间，不包含日期

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)

SECOND(日期列)

str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期

date_format(日期列,格式)：将日期转换成字符串

datediff(big,small)：返回两个日期相差的天数

日期格式

代码示例：

-- 日期函数
-- now()  年月日 时分秒 系统当前时间
-- CURDATE() 年月日
-- CURTIME() 时分秒 

SELECT NOW(),CURDATE(),CURTIME() FROM student
-- year(日期) 将日期格式化为年
SELECT YEAR(birthday),MONTH(birthday) FROM student 

-- STR_TO_DATE('2003-3-3','%Y-%m-%d') 将字符串日期 格式化为 日期类型
SELECT STR_TO_DATE('2003-3-3','%Y-%m-%d') FROM student

-- DATE_FORMAT(birthday,'%Y-%m') 将日期格式化为指定的字符串
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM student

-- DATEDIFF(CURDATE(),birthday) 计算两个日期之间的相差的天数
SELECT DATEDIFF(CURDATE(),birthday) FROM student

结果1：

结果2：

结果3

结果4

结果5

1.4逻辑处理

1.4.1case when

case when条件 then 结果1 else 结果2 end; 可以有多个when

代码示例：

SELECT
  NAME,
  gender,
  (CASE WHEN height>=2 THEN 'A'
        WHEN height>=1.8 THEN 'B'
        ELSE 'C' END)AS height
FROM
  student

运行结果：

1.4.2ifnull

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

代码示例：

-- ifnull(列名,'为空时默认信息')  
SELECT
  NAME,
  gender,
  IFNULL(height,'暂未录入身高信息') AS height
FROM
  student

运行结果：

1.4.3if函数

if else的效果 if(条件,结果1,结果2)

代码示例：

-- if(条件,'结果1','结果2')
 SELECT
  NAME,
  gender,
  IF(height>=1.9,'高个子','正常')AS height
FROM
  student

运行结果：

2.分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

示例代码：

SELECT SUM(height) FROM student  -- 求和
SELECT AVG(height) FROM student  -- 平均值
SELECT MAX(height) FROM student  -- 最大值
SELECT MIN(height) FROM student  -- 最小值
SELECT COUNT(*) FROM student     -- 统计个数

此处代码简单易懂，不做结果展示。

条件查询

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。

语法:select <结果> from <表名> where <条件>

示例代码：

-- 条件查询
-- select * from 表名  where  条件
SELECT * FROM  student  WHERE gender = '男'

运行结果：

比较

=, != 或<>, >, <, >=, <=

代码示例:

SELECT * FROM  student  WHERE gender != '男'
SELECT * FROM  student  WHERE gender <> '女'

结果1：

结果2：

逻辑运算

and 与

or 或

not 非

代码示例：

-- and 并且
SELECT * FROM  student  WHERE gender = '男' AND height > 1.80
-- or 多个条件只需要满足一个即可
SELECT * FROM  student  WHERE gender = '男' OR height > 1.60

结果1：

结果2：

模糊查询

LIKE：是否匹配于一个模式，一般和通配符搭配使用，可以判断字符型数值或数值型.

通配符： % 任意多个字符

between and 两者之间,包含临界值；

in 判断某字段的值是否属于in列表中的某一项

IS NULL（为空的）或 IS NOT NULL（不为空的）

基础查询

语法：select 查询列表 from 表名;

特点：

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

查询结果处理：

特定列查询:select column1,column2 from table

全部列查询: select * from table

算数运算符:+ - * /

排除重复行: select distinct column1,column2 from table

查询函数：select 函数; / 例如version()

1、UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

示例：

-- union 将多个查询结果合并, 合并时,列数必须一致,  去除重复数据
SELECT NAME,gender FROM student WHERE gender   = '男'
  UNION
SELECT NAME,gender FROM student WHERE  number>1

结果：

2、UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

示例：

-- union all 只是将查询的多个结果合并, 不会去除重复数据
SELECT NAME,gender FROM student WHERE gender   = '男'
  UNION ALL
SELECT NAME,gender FROM student WHERE  number>1

结果：

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，

mysql 会把所有的记录返回，且效率高于union 。

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、多个字段

示例：

-- 排序   order by 列   asc(升序) desc(降序)
 SELECT * FROM student WHERE number>0 ORDER BY number ASC
  
 SELECT * FROM student WHERE number>0 ORDER BY number DESC 

 SELECT * FROM student WHERE number>0 ORDER BY height ASC
 
 SELECT * FROM student WHERE number>0 ORDER BY reg_time DESC
 
 SELECT * FROM student WHERE number>0 ORDER BY height ASC,reg_time ASC

结果1：

结果2：

数量限制

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5;

示例：

 -- 数量限制   实现分页查询   limit 开始位置,每次查询的数量
 SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 0,3 -- 查询前三行
 
 SELECT * FROM student WHERE number>0 ORDER BY number ASC LIMIT 3,3

结果1：

结果2：

分组查询

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

示例：

-- 分组查询  
-- 分组--在一些统计场景中,按照某一个条件进行区分(分组)
-- 统计时, 按照性别,  班级, 年级 , 部门 ,年份  月份
-- 把满足某个条件的数据,分到同一个组中, 每个组内单独进行处理

-- 查询 男生,女生各有多少人    每个年级各有多少  每个部门各有多少人
   -- 分组统计 男生组统计一次,  女生组统计一次
  SELECT COUNT(*),gender FROM student  GROUP BY gender
  SELECT MAX(height),gender FROM student  GROUP BY gender
  SELECT COUNT(*),DATE_FORMAT(reg_time,'%Y-%m') FROM student  GROUP BY DATE_FORMAT(reg_time,'%Y-%m')

结果1：

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

分组查询中的筛选条件分为两类:

数据源源位置关键字

分组前筛选 原始表 group by子句的前面 where

分组后筛选 分组后的结果集 group by的后面 having

示例：

  -- 统计男生女生各多少人
   SELECT COUNT(*),gender FROM student  GROUP BY gender
   -- 男生和女生谁的人数大于2
   -- HAVING COUNT(*)>2             having    条件 是对分组后的结果进行条件筛选
   SELECT COUNT(*),gender FROM student GROUP BY gender  HAVING COUNT(*)>2

结果1：

结果2：

六、多表设计_关联查询

数据库设计范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。(联系范式-->邮箱，电话，qq可以拆分)

2．第二范式就是要有主键，要求其他字段都依赖于主键。

没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。

其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。

3.第三范式,确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含已在其它表中包含的非主关键字信息

外键：引用另外一个数据表的某条记录。

外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。

create table student(

id int not null auto_increment primary key, --主键

num int,

name varchar(10)

majorid int, -- 外键

CONSTRAINT 约束名 foreign key(majorid ) references major(id)

);

约束名规则:

例：FK_ForeignTable_PrimaryTable_On_ForeignColumn

1、当主表中没有对应的记录时，不能将记录添加到从表

2、不能更改主表中的值而导致从表中的记录孤立

3、从表存在与主表对应的记录，不能从主表中删除该行

4、删除主表前，先删从表

示例：

*/
   DROP TABLE stu
   -- 新建一个专业表
   CREATE  TABLE major(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20)
   )

 修改表  为表添加一个列  一对多 多对一
   ALTER TABLE student ADD majorid INT

-- 外键  在一个表中外键是用来与另一个表的主键关联的
-- 主键 不能为空  唯一 检查 
-- 外键有两种情况:
-- 1.不加外键约束
--     可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有问题 

-- 2.添加外键约束
--  添加外键约束后,两张表操作时,不能导致外键列和主键列对应关系不成立
  ALTER TABLE student  ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id)

 -- 删除外键约束
 ALTER TABLE student DROP FOREIGN KEY fk_student_major_majorid

添加外键约束结果：

可以看到majorid处上锁了。

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

按功能分类：

内连接

外连接

左外连接

右外连接

内连接(inner join)：

● 把满足了条件的两张表中的交集数据查询出来

语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

示例：

 SELECT 
   s.number,s.name,s.gender,s.phone,m.name  mname 
 FROM student s,major m 
 WHERE s.majorid = m.id   -- 先产生一个笛卡尔乘积  然后在条件筛选

--第二种写法
 SELECT 
   s.number,s.name,s.gender,s.phone,m.name  mname
 FROM student s 
 INNER JOIN major m ON s.majorid = m.id   -- 把满足条件关联在一起

--两种效果是一样的

结果：

左外连接(left join)

select 结果 from

表1 left join 表2 on

表1.column1 = 表2.column2

示例：


 -- 左外连接   特点  即使不满足连接条件,也会把左边表中的所有数据查询出来
 
 SELECT 
   s.number,
   s.name,
   s.gender,
   s.phone,
   m.name  mname
 FROM 
   student s LEFT JOIN major m ON s.majorid = m.id

结果：

右外连接(right join)

select 结果 from

表1 right join 表2 on

表1.column1 = 表2.column2

示例：

 -- 右外连接  即使不满足连接条, 也要把右边表中的所有数据查询出来
 SELECT 
   s.number,
   s.name,
   s.gender,
   s.phone,
   m.name  mname
 FROM 
   student s RIGHT JOIN major m ON s.majorid = m.id

结果：

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询.

分类：

按子查询出现的位置：

from后面：支持表子查询

where：支持标量子查询，列子查询

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

表子查询（结果集一般为多行多列）

示例：

  -- 男生和女生谁的人数大于2  ,首先统计出男生各自的人数
  
  -- 在from后面 使用了表子查询(多行多列), 把子查的结果当做另一个表的数据来源
  SELECT * FROM (SELECT COUNT(*)c,gender FROM student   GROUP BY gender) t
           WHERE t.c>2
   
 -- 在where后面 使用标量子查询(一行一列)
 SELECT * FROM student WHERE height  = (SELECT MAX(height) FROM student)
 
 -- 在where后面 使用列子查询(一列多行)
 SELECT * FROM student WHERE height IN (SELECT height FROM student WHERE height IN(1.80,1.90))

结果1：