数据库 MySQL

DataBase

为了方便数据的存储和管理，将数据按照规则将数据存储在硬盘上。

数据库管理系统（DBMS）:

MySQL数据库：

是一个关系型数据库管理系统，最早由瑞典MySQLAB公司开发，目前被oracle收购

有开源版本，也有付费版本

具有快速，可靠，易于使用的特点

支持标准sql，支持多种操作系统，支持多种编程语言连接他自己

关系数据库

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

非关系型数据库 redis

缓存 key :value

sql()结构化查询语言

一种特殊的编程语言，用于对数据库管理操作和数据库的增删改查操作

是一种标准的数据库操作语言，一般的数据库管理系统都支持（一些语法上略有不同）

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

ddl：数据（结构）定义语言，用于创建和修改数据库

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

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

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

CREATE DATABASE borrow

DROP DATABASE borrow;

CREATE DATABASE  IF NOT EXISTS borrow CHARSET utf8

CREATE TABLE student (sname CHAR(10),sage VARCHAR(2),ssex VARCHAR(2));

DROP TABLE student;

删除数据库

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

DROP DATABASE borrow;

DROP TABLE student;

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

RENAME TABLE student TO stu;
RENAME TABLE stu TO student;

ALTER TABLE student ADD majorid INT 

数据库表的基本概念

1、数据表 表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。 表的最简单形式是由行和列组成，分别都包含着数据。

每个表都有一个表头和表体，表头定 义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

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

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

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

 设 计 表

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

● 表名(表信息)

● 表中的字段

● 字段的数据类型和长度

● 哪些约束

设计表(数据类型)

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

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

date 日期， 包含年月日

datetime 年月日 时分秒

整数

 浮点

decimal 数据类型(M,D) M：精度，数据的总长度； D：标度，小数点后的长度

主键：

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

约束:

PRIMARY KEY 设置主键约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束 设置条件

外键约束 主键自动增长,设置为自动增长时,

只能为整数类型 AUTO_INCREMENT

默认值 DEFAULT default_value 字段注释: comment '注释'

CREATE TABLE student(
   num INT,
   sname VARCHAR(10),
   gender CHAR(1),
   birthday DATE,
   phone CHAR(11),
   adress VARCHAR(30),
   height DECIMAL(3,2),
   reg_time DATETIME
   
)
DROP TABLE student;

CREATE TABLE student(
   num INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(10) NOT NULL COMMENT '学号',
   gender CHAR(1) NOT NULL DEFAULT '男',
   birthday DATE,
   phone CHAR(11) NOT NULL UNIQUE,
   adress VARCHAR(30),
   height DECIMAL(3,2) CHECK(height<2.60),
   reg_time DATETIME
   
)

RENAME TABLE student TO stu;
RENAME TABLE stu TO student;

DROP TABLE student;

CREATE TABLE student(
   num INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(10) NOT NULL COMMENT '学号',
   gender CHAR(1) NOT NULL DEFAULT '男',
   birthday DATE,
   phone CHAR(11) NOT NULL UNIQUE,
   adress VARCHAR(30),
   height DECIMAL(3,2) CHECK(height<2.60),
   reg_time DATETIME
   
)
CREATE TABLE stu LIKE student;

DML

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

● 常用语句： insert,delete,update

INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('李明','2004-09-14','15678941251','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('王五','2004-09-14','15678941252','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('李虎','2004-09-14','15678941253','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('二蛋','2004-09-14','15678941254','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('毛蛋','2004-09-14','15678941255','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('马达','2004-09-14','15678941256','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('马达da','2004-09-14','15678941257','汉中',1.60,NOW());

修改数据 UPDATE 表名 SET 列名 = ‘新值’WHERE 条件 

DELETE FROM 表名 WHERE 条件 TRUNCATE TABLE 表名;清空整张表

DQL-基础查询

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

● 基础查询

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

● 特点： 查询列表可以是：表中的字段、常量、表达式、函数 查询的结果是一个虚拟的表格

-- length()：获取参数值的字节个数
SELECT sname,LENGTH(sname) FROM student
 
 -- char_length()获取参数值的字符个数
SELECT sname,CHAR_LENGTH(sname) FROM student 
 
-- concat(str1,str2,.....)：拼接字符串
SELECT CONCAT(sname,':',gender)AS NAME FROM student

-- upper()/lower()：将字符串变成大写/小写
SELECT UPPER(sname),LOWER(sname) FROM student

-- substring(str,pos,length)：截取字符串 位置从1开始
SELECT SUBSTRING(sname,2,3) FROM student 

-- instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(sname,'明') FROM student

-- trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM(sname) FROM student
SELECT TRIM('李' FROM sname) FROM student

-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(sname,5,'a'), RPAD(sname,5,'a')  FROM student
--  rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度

-- replace(str,old,new)：替换，替换所有的子串
SELECT REPLACE(sname,'马达','马达加大') FROM student


/*
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
原本的值
if函数：if else的 效果 if(条件,结果1,结果2)
*/
SELECT sname,
(CASE WHEN height>=1.80 THEN '高个子'
 WHEN height>=1.60 THEN '正常身高'
 ELSE '低个子' END
)AS height, gender FROM student

SELECT sname,IFNULL(adress,'暂未录入')AS adress FROM student

SELECT sname,IF(height>1.60,'正常','矮个子')AS height FROM student

/*
● 单行函数 
日期函数
now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列) 
SECOND(日期列) 
 str_to_date(字符串格式日期,格式)：将日期格式的字符转换成指定格式的日期
 date_format(日期列,格式)：将日期转换成字符串
 datediff(big,small)：返回两个日期相差的天数
*/

SELECT sname,NOW(),CURDATE(),CURTIME() FROM student


/*
● 分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count（*）用作统计行数
*/

SELECT SUM(height) FROM student;
SELECT AVG(height) FROM student;

SELECT MAX(birthday) FROM student;
SELECT MIN(birthday) FROM student;

SELECT COUNT(*) FROM student;

/*
● 条件查询
使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
 语法:select <结果> from <表名> where <条件>
 比较
 =, != 或<>, >, <, >=, <=
 逻辑运算
 and 与
 or 或
 not 非
*/
SELECT *FROM basketplayer WHERE height>=190 AND weight>100
SELECT * FROM basketplayer WHERE height>200 OR weight>90 OR num=1

-- in 判断某字段的值是否属于in列表中的某一项
SELECT *FROM basketplayer WHERE brithday NOT IN('1982-01-17','1980-09-12')

/*
● 条件查询
 模糊查询
 LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值
 或数值型. 通配符： % 任意多个字符 
 between and 两者之间,包含临界值；
 in 判断某字段的值是否属于in列表中的某一项
 IS NULL（为空的）或 IS NOT NULL（不为空的）*/
 
 SELECT *FROM student WHERE sname LIKE '李%'
  SELECT *FROM student WHERE sname LIKE '%%'
  SELECT * FROM student WHERE height BETWEEN 1.60 AND 1.95
  
SELECT *FROM student WHERE adress IS  NULL
SELECT *FROM student WHERE adress IS NOT NULL
 
 
/*
1、UNION 的语法如下：
 [SQL 语句 1]
 UNION
 [SQL 语句 2]
2、UNION ALL 的语法如下：
 [SQL 语句 1]
 UNION ALL
 [SQL 语句 2]
 当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，
 mysql 会把所有的记录返回，且效率高于union 。
*/ 
 
 SELECT sname,gender,birthday FROM student WHERE height >1.85
 UNION
 SELECT sname,gender,birthday FROM student WHERE adress='汉中'
 
 -- union会删掉重复的元素 ，union all 会将所有记录返回
 SELECT sname,gender,birthday FROM student WHERE height >1.85
 UNION ALL
 SELECT sname,gender,birthday FROM student WHERE adress='汉中'
 
 /*
• 排序
 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
 asc代表的是升序，desc代表的是降序，如果不写，默认是升序
 order by子句中可以支持单个字段、多个字段

 */
 
 SELECT *FROM student ORDER BY height 
 -- 如果不写，默认是升序, desc是降序
 SELECT *FROM student ORDER BY height DESC
 
 
 /*
• 数量限制
 limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
 SELECT * FROM table LIMIT offset rows;
 SELECT * from table LIMIT 0,5;   
 */
 
 SELECT * FROM student LIMIT 0,2;
 SELECT * FROM student LIMIT 2,2;
 SELECT * FROM student LIMIT 4,2;
   
   
   /*
  分组查询
  语法：
  select 分组函数，列（要求出现在group by的后面）
  from 表
  [where 筛选条件]
  group by 分组的列表
  [having 分组后的筛选]
  [order by 子句]
   */
   SELECT COUNT(*),location FROM basketplayer GROUP BY location
   
   SELECT SUM(height),location FROM basketplayer GROUP BY location
   
   SELECT AVG(height),location FROM basketplayer GROUP BY location
   -- 统计每年出生的人数
   SELECT COUNT(*),DATE_FORMAT(brithday,'%Y') FROM basketplayer GROUP BY DATE_FORMAT(brithday,'%Y') 
   
   -- 分组统计名字数量 查询出哪些名字重复了
   -- where 是对原始表中的数据进行过滤
   -- HAVING 在group by 后面使用 为分组的数据添加过滤条件
   
   SELECT COUNT(*) ,sname FROM student GROUP BY sname HAVING COUNT(*)>1