数据库笔记一

数据库

数据库（DataBase db,dba数据库管理员）

数据库方便存储和管理数据，按照特定的规则存储在磁盘上，就是一个存储数据的仓库。

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

数据库管理软件，用于创建和管理DB。

MySQL数据库

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

关系型数据库

以数据表为单位，表与表之间存在关联关系

例如：

用户表

商品表

订单表 用户编号，商品编号。

非关系型数据库 redis

缓存 key：value；

sql :结构化查询语言简称（SQL）

是一种特殊的编程语言，用于对数据库管理操作和数据的增删改查操作。是一种标准的数据库操作语言，一般的数据库管理系统都支持（一些语法上略有不同）

在sql语言中根据操作不同，又分为不同的类型的sql语句：

1.DDL：数据（结构）定义语言，

创建和删除数据库表结构的语言

常用语句：create ，alter，drop，rename。

-- 创建数据库
CREATE  DATABASE test;
-- 创建数据库，并修改字符集编码
CREATE DATABASE test CHARSET utf8;
-- 创建数据库，并改变数据库的基本字符集，并判断数据库是否存在。
CREATE DATABASE IF NOT EXISTS test CHARSET utf8;

-- 删除数据库
DROP DATABASE test;

-- mysql数据库创建后就无法修改，只能修改字符集编码
ALTER DATABASE test CHARSET utf8;

数据库表

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

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

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

创建表的步骤：

1.确定表名，2.确定列名，

列的数据类型：

字符串型：

char(n):长度为n的定长字符串。

varchar(n):最大长度为n的变长字符串。

日期时间类型：

date 日期--年月日； datetime 时间--年月日 时分秒。

数值类型：

整数：

TINYINF 1；

SMALLINT 2;

MEDIUMINT 3;

INT 4;

BIGINT 8;

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)

列的约束：列的规则。

创建表的语法。

/*
约束条件：
COMMENT'___'，给定一个注释。
PRIMARY KEY 设定为主键
AUTO_INCREMENT 无法输入,自动增加。
NOT NULL  输入不能为空
DEFAULT'___',给定默认值.
CHECK(列名<___)，判断是否满足条件。
UNIQUE  唯一，不能重复
*/
CREATE TABLE student(
ID INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号 主键',
NAME VARCHAR(4) NOT NULL COMMENT'姓名',
age INT CHECK(age>0) NOT NULL COMMENT'年龄',
gender CHAR(1) NOT NULL DEFAULT'男' COMMENT'性别',
hight DECIMAL(3,2) CHECK(hight<2.5),
phone CHAR(11) NOT NULL UNIQUE,
birth DATETIME,
house VARCHAR(25)
)
/*-------------------------------------*/
-- 修改表名
RENAME TABLE student TO stu
-- 将student表改名为stu。
-- 复制表结构
CREATE TABLE student LIKE stu
-- 创建student表，并复制stu表的结构。

2.DML

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

常用语句： insert,delete,update

插入数据

方式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) 查询语句(查询的列数与插入列数匹配)

修改数据

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

删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

查询

单列查询

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，

可以从一个表中查询数据，也可以从多个表中查询数据。

select 查询列表 from 表名;

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

全部列查询: select * from table

算数运算符:+ - * /

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

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

字符函数:

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

逻辑处理

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

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

原本的值

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

学函数

round(数值)：四舍五入

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

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

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

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

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

日期函数

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

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

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

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

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

SECOND(日期列)

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

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

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

分组函数

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

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

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

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

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

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

-- 分组函数
-- count(*)/(1)/(列名) 用于计数
SELECT COUNT(*) FROM student 
SELECT COUNT(1) FROM student
SELECT COUNT(birth) FROM student
-- sum求和
SELECT SUM(hight) FROM student 
-- avg求求平均值
SELECT AVG(hight) FROM student
-- max/min求最大/最小值
SELECT MAX(hight),MIN(birth) FROM student

条件查询

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

语法:select <结果列> from <表名> where <条件>，当<*>时，查询所有的列。

精确查询

比较

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

逻辑运算

and 与 or 或 not 非

-- 条件查询
-- and与,满足所有条件。   or或,满足其中一个条件。     not非,不满足该条件的。 
SELECT * FROM student WHERE hight >=1.80 AND gender ='男' AND age>18
SELECT NAME FROM student WHERE hight = 1.80 OR hight=1.85 OR hight=2.41
SELECT * FROM student WHERE NOT (hight>=2.10)

模糊查询（效率慢）

LIKE

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

或数值型.

通配符： % 任意多个字符

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

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

IN( ，_，)如果想让判断不属于in中的值时，在IN前加上not。

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

-- 条件查询1
-- and与,满足所有条件。   or或,满足其中一个条件。     not非,不满足该条件的。 
SELECT * FROM student WHERE hight >=1.80 AND gender ='男' AND age>18
SELECT NAME FROM student WHERE hight = 1.80 OR hight=1.85 OR hight=2.41
SELECT * FROM student WHERE NOT (hight>=2.10)

-- 模糊查询
-- between __ and ___  两者之间,包含临界值；
SELECT * FROM student WHERE hight BETWEEN 1.40 AND 2.40
-- in 判断某字段的值是否属于in列表中的某一项
SELECT * FROM student WHERE hight IN(1.80,1.85,2.41)
-- IN(    __，_，__)如果想让判断不属于in中的值时，在IN前加上not。
SELECT * FROM student WHERE hight NOT IN(1.80,1.85,2.41)
-- IS NULL（为空的）或 IS NOT NULL（不为空的）
SELECT * FROM student WHERE hight IS NULL
SELECT * FROM student WHERE hight IS NOT NULL
-- LIKE   
-- 通配符：% 任意多个字符
SELECT * FROM student WHERE NAME LIKE('肖%')
SELECT * FROM student WHERE NAME LIKE('%i%')

UNION 的语法：

union合并多个查询的结果，

1、UNION 的语法如下：

（SQL语句的查询条件可以不同，但查询结果必须相同）

[SQL 语句 1]

UNION

[SQL 语句 2]

2、UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

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

而使用union all ，mysql 会把所有的记录返回，且效率高于union

-- union合并
-- UNION 的语法
-- 使用union 时,会把结果集中重复的记录删掉
SELECT * FROM student WHERE age>=19 
UNION
SELECT * FROM student WHERE gender ='男'
-- UNION ALL 的语法
-- 使用union all,会把所有的记录返回，且效率高于union .
SELECT * FROM student WHERE age>=19 
UNION ALL
SELECT * FROM student WHERE gender ='男'