Mysql基础

目录

一、数据库概述

1.1关系型数据库和非关系型数据库

1.2Mysql数据库

二、SQL语句

2.1DDL数据(结构)定义语言

2.1.1数据库存储数据的特点

2.1.3数据库表的基本概念

2.1.4设计表(数据类型)

2.1.5创建表语法：

三、DML数据操纵语言

3.1向表中插入数据

3.2修改数据

3.3删除数据

---

一、数据库概述

数据库（ DataBase ）为了方便数据的存储和管理，它将数据按照特定的规则存储在磁盘上, 就是一个存储数据的仓库。通过数据库管理系统，可以有效的组织和管理存储在数据库中的数据

数据库的相关概念

DB ：数据库（ DataBase ） 存储数据的容器，它保存了一系列有组织的数据。

 DBMS ：数据库管理系统（ DataBase Management System ）

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

根据不同的存储类型可以分为：

关系型数据库和非关系型数据库 

1.1关系型数据库和非关系型数据库

关系数据库

关系型数据库管理系统称为 RDBMS ， R 指 Relation

Oracle: 功能强大 , 收费 .

MySQL 快捷、可靠 开源、免费

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

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

非关系数据库

MongdoDB

Redis

1.2Mysql数据库

MySQL 是一个关系型数据库管理系统 ， 由瑞典 MySQL AB 公司开发，目

前属于 Oracle 旗下产品。 MySQL 流行的关系型数据库管理系统。

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

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

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

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

● mysql 商业版与社区版

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

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

护，可以免费使用

二、SQL语句

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

SQL 优点：

不是某个特定数据库供应商专有的语是言，几乎所有 DBMS 都支持 SQL简单易学，灵活使用可以进行非常复杂和高级的数据库操作

2.1DDL数据(结构)定义语言

是用于创建和修改数据库表结构的语言。

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

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

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

删除数据库

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

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

具体实现如下：

-- 创建数据库
CREATE DATABASE schooldb 
-- 删除数据库
DROP DATABASE schooldb
-- 不存在创建一个数据库
CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8
-- 修改数据库
ALTER DATABASE schooldb CHARSET gbk

2.1.1数据库存储数据的特点

● 将数据放到表中，表再放到库中

● 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有

唯一性。

● 表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的

设计。

● 表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列

类似java中的”属性”

● 表中的数据是按行存储的，每一行类似于Java中的“对象”

2.1.3数据库表的基本概念

1、数据表

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

表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定

义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、字段

字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单

ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。

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

3、记录

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完

整的记录

2.1.4设计表(数据类型)

char （ n ） 长度为 n 的定长字符串

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

date 日期， 包含年月日

datetime 年月日 时分秒

2.1.5创建表语法：

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 约束名 约束规则 ]

)

主键：

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

约束 :

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束 设置条件

外键约束

主键自动增长

AUTO_INCREMENT

默认值

DEFAULT default_value

字段注释 :

comment ' 注释 '

具体实现如下：

-- 添加一个学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR(5) NOT NULL COMMENT '姓名',
gender CHAR(1),
birthday DATE,
address VARCHAR(30),
phone VARCHAR(11) NOT NULL UNIQUE COMMENT '电话号码' ,
height DOUBLE(3,2) CHECK(height<3),
zcrq DATETIME
)

删除表，修改表名

删除表

DROP TABLE [if exists ]表名

修改表名

RENAME TABLE 旧表名 TO 新表名

复制表结构

CREATE TABLE 新表名 LIKE 被复制表名

具体实现如下：

-- 删除学生表
DROP TABLE student
`student`
-- 修该表名，将表名从stu改为student
RENAME  TABLE stu TO student
-- 复制表
CREATE TABLE stu LIKE student

2.2、DML数据操纵语言

2.2.1向表中插入数据

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

方式 2: INSERT INTO 表名 set 列名 1= 值 1,.. 列名 n= 值 n;

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

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

具体实现如下：

-- 向表中插入值
INSERT INTO stu (NAME,gender,birthday,address,phone,height) 
VALUE ("王五",'男','2020-2-2','陕西','000000',1.8)

INSERT INTO stu SET NAME='jim',phone='1111'

2.2.2 修改数据

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

注：条件不能省略，没有条件将改变整个表

-- 修改表中的值
UPDATE stu SET gender = '男',address='陕西' WHERE id=2

2.2.3删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名 ; 清空整张表

-- 删除表中的值
DELETE FROM student WHERE id=2
-- 清空表
TRUNCATE TABLE stu

2.3数据查询语言

DQL （ Data Query Language ）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

2.3.1基础查询

语法：

select 查询列表 from 表名 ;

● 特点：

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

-- 查询生日为1982 1 17 的姓名
SELECT
 NAME
FROM
  player
WHERE birthday = '1982-01-17'

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

2.3.2 查询结果处理

查询结果处理：

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

全部列查询 : select * from table

算数运算符 :+ - * /

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

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

-- 算术运算符表达式 只能用于算术运算 +-*/
SELECT id+height,NAME FROM player WHERE seat='前锋'
-- 使用concat(字符串1,字符串2,字符串...)可以实现字符串连接功能
SELECT id, CONCAT(NAME,':',birthday) FROM player WHERE seat='中锋'
-- distinct 去除查询结果中重复的数据
SELECT DISTINCT NAME,gender FROM student 

字符函数

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) ：替换，替换所有的子串

-- length()：获取参数值的字节个数
-- char_length()获取参数值的字符个数
SELECT LENGTH(NAME),CHAR_LENGTH(NAME),NAME FROM student
-- upper()/lower()：将字符串变成大写/小写
SELECT UPPER(NAME),LOWER(NAME) FROM student
-- substring(str,pos,length)：截取字符串 位置从1开始
SELECT SUBSTRING(NAME,1,2) FROM student
-- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0,不区分大小写
SELECT INSTR(NAME,'B'),id FROM student
-- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM('a' FROM NAME)  FROM student
-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(NAME,10,'a') FROM student
-- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
SELECT RPAD(NAME,10,'a') FROM student
-- replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE ('abcdef','c','C') FROM student

逻辑处理：

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

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

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

-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT id,NAME,
(CASE WHEN height>=1.9 THEN '大高个子'
WHEN height>=1.8 AND height<1.9 THEN '高个子'
ELSE '不是高个子' END) AS height
FROM student
-- ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
-- 原本的值
SELECT id,NAME,IFNULL(address,'没有录入')AS address FROM student
-- if函数：if else的 效果 if(条件,结果1,结果2)
SELECT id,NAME,IF(height>=1.9,'大高个子','不是大高个子')AS height FROM student

数学函数

round( 数值 ) ：四舍五入

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

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

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

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

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

-- round(数值)：四舍五入
SELECT id,NAME,ROUND(height) FROM student
-- ceil(数值)：向上取整，返回>=该参数的最小整数
SELECT id,NAME,CEIL(height) FROM student
-- floor(数值)：向下取整，返回<=该参数的最大整数
SELECT id,NAME,FLOOR(height) FROM student
-- truncate(数值,保留小数的位数)：截断，小数点后截断到几位
SELECT id,NAME,TRUNCATE(height,2) FROM student
-- mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
SELECT MOD(6,4) FROM student
-- rand()：获取随机数，返回0-1之间的小数
SELECT RAND() FROM student

● 单行函数

日期函数

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

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

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

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

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

str_to_date ：将日期格式的字符转换成指定格式的日期

date_format ：将日期转换成字符串

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

-- now()：返回当前系统日期+时间
SELECT NOW()
-- curdate()：返回当前系统日期，不包含时间
SELECT CURDATE()
-- curtime()：返回当前时间，不包含日期
SELECT CURTIME()
-- str_to_date ：将日期格式的字符转换成指定格式的日期
SELECT  STR_TO_DATE('2002-2-2','%Y-%m-%d') 
-- date_format ：将日期转换成字符串
SELECT DATE_FORMAT(birthday,'a') FROM student
-- datediff(big,small)：返回两个日期相差的天数
SELECT DATEDIFF(NOW(),'2003-09-12')

分组函数

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

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

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

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

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

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

-- sum求和、avg平均值、max最大值、min最小值、count计数 不包括空值
SELECT SUM(height) FROM student
SELECT AVG(height) FROM student
SELECT MAX(height) FROM student
SELECT COUNT(height) FROM student

2.3.3条件查询

条件查询

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

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

比较

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

逻辑运算

and 与    or 或       not 非

-- =, != 或<>, >, <, >=, <=
SELECT * FROM student WHERE NAME='王五'
SELECT * FROM student WHERE NAME!='王五'
SELECT * FROM student WHERE gender<>'女'
SELECT * FROM student WHERE height>=1.8
-- 逻辑运算and 与 or 或 not 非
SELECT * FROM student WHERE height>=1.75 AND height<=1.9
SELECT * FROM student WHERE height>1.9 OR height<1.75

模糊查询

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

通配符： % 任意多个字符，包含 0 个字符 _ 任意单个字符

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

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

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

-- LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型
SELECT * FROM student WHERE NAME LIKE '张%'
-- between and 两者之间,包含临界值；
SELECT * FROM student WHERE height BETWEEN 1.75 AND 1.9
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM student WHERE height IN(1.72,1.75,1.8)
-- IS NULL（为空的）或 IS NOT NULL（不为空的）
SELECT * FROM student WHERE birthday IS NOT NULL

排序

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

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

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

• 数量限制

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

-- order by 排序列 ASC/DESC
SELECT * FROM student ORDER BY height DESC
SELECT * FROM student ORDER BY height,birthday
-- limit子句：对查询的显示结果限制数目,跳过一行读取三行
SELECT * FROM student LIMIT 1,3