MySQL、高级SQL操作

学习数据库的目的

岗位需���、大数据时代、被迫需求，存数据

数据库是所有软件体系中最核心的存在

数据库 DB

DB dataBase

数据仓库，软件，安装在window、linux、mac上，可以存储大量数据，500w

作用：存储数据，管理数据

数据库分类

关系型数据库SQL

mysql、Oracle、sql server db2 sqllite

通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库 NoSQL Not only

redis mongdb

非关系型数据库，对象存储，通过对象的自身和属性来决定

DBMS数据库管理系统

数据库的管理软件，科学有效的管理数据，维护和获取数据

MySQL简介

关系型数据库管理系统

之前瑞典mysql ab公司 现在oracle

MySQL是最好的rdbms软件之一

体积小、速度快、成本低

中小型网站、或者大型网站 集群

数据库常用指令

mysql -u root -p 登录

show databases 展示数据库

create database 库名 创建数据库

show create database 库名 查看数据库详细

alter database 库名 charset utf8 修改数据库选项

show database like “匹配模式”

use 数据库名 切换数据库

create table 表名(字段 字段类型,字段 字段类型)

insert into 表名 values(字段1，字段2)

update 表名 set 字段1=新值 修改

delete from 表名 where 限定条件 删除

修改表结构 alter 表名 add 字段名 字段类型

删库 drop database 库名

操作数据库

操作数据库>操作数据库的表>操作数据库表的数据

创建数据库

create DATABASE teacher

删除数据库

drop database if EXISTS teacher

使用数据库

 use student

数据库数据类型

数值

tinyint 十分小的数据 1字节

smallint 较小数据2字节

mediumint中等数据3字节

int整数4字节

bigint较大的数据8字节

float浮点数4字节

double浮点数8字节

decimal字符串形式的浮点数 金融计算使用

字符串

char 字符串固定大小 0~255

varchar 可变字符串 0~65535 常用变量String

tinytext 微型文本 2^8-1

text 文本串 2^16 -1 保存大文本

时间日期

date YYYY-MM-DD 日期格式

time HH:mm:ss 时间格式

datetime YYYY-MM-DD HH:mm:ss

timestamp 时间戳 1970.1.1到现在的毫秒数

tear 年份表示

null

没有值，未知

不要用null进行运算，结果为null

数据库的字段属性

unsigned 无符号的整数，声明了该列不能为负数

zerofill 0填充的，不足的位数，用0填充

自增，自动在上一条记录的基础上+1，自定义设计主键自增的起始值和步长

非空null not null 不能给他赋值，否则报错

默认 设置默认的值

CREATE TABLE IF NOT EXISTS stu(id INT(4),name VARCHAR(20),pwd VARCHAR(20),sex VARCHAR(2),birthday VARCHAR(30),email VARCHAR(20),address VARCHAR(20))ENGINE=INNODB DEFAULT CHARSET=utf8;

列描述

comment是专门给用于开发人维护的注释说明

基本语法：comment 字段描述

查看coment：必须通过查看表的创建语句

show databases; use teacher;use student;show tables;desc stu; -- 展示字段--select * from stu; -- 展示值

主键

主要的键，primary key在一张表中，有且只有一个字段，里面的值具有唯一性，与其他字段没有任何冲突，保证数据的唯一性

创建主键：1.两种方式，

随表创建：2.两种方式：1）、直接在需要当作主键的字段之后，增加primary key属性，来确定主键 2)、在字段后面增加主键属性

2.在所有字段之后增加primary key选项，primary key(字段信息)

创表之后

基本语法：alter table 表名 addprimary key(字段)

查看主键

通过查询表结果desc 表名，pri主键描述，非空且唯一

查看表的创建语句show create table 表名

删除主键

基础语法：alter table 表名 drop primary key

包留了非空条件

create database off;use off;create table student (id int,uname char,age int,phone int,height int)insert into student VALUES(1,'t',12,123456,11) -- 添加数据insert into student VALUES(2,'y',15,123456,21);insert into student VALUES(3,'z',1,123456,31);insert into student VALUES(4,'a',2,123456,14);insert into student VALUES(5,'x',12,123456,91);select * from student -- 查询表rename table student to student_details -- 改表名alter table student_details drop height -- 删字段desc student_details-- 查询字段alter table student_details add sex char(2); -- 添加字段desc student_details

三大范式

保证信息不重复，更新异常、插入异常（无法正常显示），删除异常（丢失有效数据）

第一范式：（1NF）要求数据库表的每一列都是不可分割的原子数据项

表：字段1、字段2(字段2.1，字段2.2)，字段3.。。

第二范式：满足第一范式，每张表只描述一件事情

不符合第二范式的例子

表：学号、课程号、姓名、学分

这个表表示了两个十五：学生信息、课程信息，由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，不符合第二范式，

第三范式：满足第一二范式，需要确保数据表中的每一列数据都和主键直接相关

不符合第三范式的列子

表：学号、姓名、年龄、所在学院、学院联系电话

存在依赖传递学号->所在学院->学院地点、学院电话

解决”取出重复字段、新增学院表

表关系

表与表之间（实体）自然界都有的关系，每种关系如何设计结构

一对一

一张表的记录和另外一张表中的关系只有一条有关系，通常涉及方法保证使用同样的主键

表的使用过程中，常用的信息去查询，不常用的偶尔才会用到

将表拆分，常用的信息放在一个表内 不常用的表在一个表内

一对多

通常也叫多对一，从角度去看问题，通常一对多关系设计方案，再“多”关系的表中去维护一个字段，这个字段是“一”关系的主键

多对多

一张表的一条记录可以匹配到另外一张表中多条记录，多对多的关系如果按照一对一的关系维护，就会出现多个其他表的主键，那么访问会不方便，通过两张表自己增加字段，那么就通过第三张表来解决

中间表满足所有关系

多对多的解决方法：增加中间表，让中间表与对应的其他表形成2个

多对一关系，多对一的解决方法再多表中增加一表中对应的主键字段

SQL高级操作

新增数据

多数据插入

只写一次insert命令，可以插入多条记录

基本语法：insert into 表名 (字段列表) values（值列表），（值列表），（值列表）

主键冲突

主键冲突：在有的表中，使用的时业务主键（字段有具体的含义），但是往往在进行数据插入的时候，又不确定表中书否含有对应的主键

解决方案：

1.主键冲突更新：类似于插入语法，如果插入的过程中主键冲突了，那么久采用更新的方法

语法格式：insert into 表名（字段列表）values on duplicate key update 字段=新值

2.主键冲突更新：当主键冲突之后，替换掉原来的数据，重新插入

蠕虫复制

蠕虫复制：一分为二，成倍增加(指数)，从已有的数据中获取，并且将获取到的数据插入到新的数据表中，

语法给是：insert into 表名(字段列表) select 字段列表 from 表2

可以在短期内快速增加的数量，测试表的压力，测试表的效率（索引）

注意主键冲突

更新数据

1.在更新数据的时候，特别要注意通常一定要跟随条件，不做批量删除更新的

语法格式：update 表名 set 字段名=新值，字段名2=新值2 where 条件

2.如果没有条件，那么就是全表更新，但是可以使用limit语句限制更新的数量

高级删除

1.在删除数据的时候尽量不要删除，应该用where条件进行限制

2.删除数据的时候可以使用limit限制要删除的具体数量

高级查询

data query language 数据查询语句

所有的操作都用 select

简单、复杂的查询

数据库中最核心的语言，最重要的语句

使用频率最高的语句

完整的查询指令

select 字段名 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制

select选项：系统如果对待查询结果

all：默认，表示保存所有记录

distinct：去重，去掉重复记录，值保留一条数据（所有字段相同）

指定查询字段

select * from studentselect * from gradeselect `studentname` as 姓名, `sex` as 性别, `phone` as 电话 from student limit 0,1 select CONCAT('名字',studentname) as 新名字 from student  -- CONCAT('名字',studentname)-- 去重 DISTINCTselect DISTINCT `studentno` as 去重学号 from resultselect VERSION() -- 系统版本select `studentno`,`studentresult`+1 as 学分 from result

数据源

from是为了前面的查询提供数据，数据源只要求是一个符合二维表结构的数据结构就可以

单表数据：from 表名

多表数据：从多张表中获取数据

基本语法：from 表1，表2.。。

得到的结果是：两张表记录数相乘，字段拼接

本质：从第一张表取出一条数据，去拼凑第二张表的所有记录，保留所有记录，得到的几个在数学上叫做笛卡尔积，除了给数据库增加压力，没有任何作用。

动态数据

from后面跟的不是一个实体表，而是一个查询出来的二维表（子查询）

基本语法：from （select 字段列表 from 表）【as】 别名

where字句

用来从数据表中湖区哦数据的时候，进行过滤（条件筛选）。

数据获取原理：针对表去对应的磁盘处获取所有记录（一条条），where的作用就是在拿到一条结果之后开始判断，哦按段是否符合条件，符合保存，否则舍弃（没有存入内存）

where是通过运算符进行结构比较判断数据的。

逻辑运算符 and、or、not

in运算符

in在sql里面用来代替“=”，当你的结果不是一个值，而是一个结果集的时候，

基本语法：in(结果1，结果2)

只要当前条件在结果集中出现，那么就成立

-- 查询成绩大于某个数 select * from result where result.studentresult>=2 and result.subjectno >=4SELECT * from resultwhere studentresult >= 2 && result.subjectno >= 3select * from result where not studentresult = 1

运算符

算数运算符：+、-、*、/

基本算数运算，通常不在条件中使用，而且用于结果元（select字段中）

除法的运算结果用浮点表示，除数为0，系统给出null

拿null进行人恶化算术运算，结果为null

比较运算符 >、=、