自学内容网 自学内容网

Mysql基础

一、数据库概述

为什么学习数据库

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

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

数据库(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:

结果2:

结果3:


原文地址:https://blog.csdn.net/sixudhjj/article/details/140530880

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!