MYSQL数据库

目录

1.数据库总览

2.Myql简介

3.结构化查询语句

4.SQL编写规范

5.注意事项

6.SQL操作数据库

7.SQL创建数据表

8.列类型分类

1》数值类型

2》字符串类型

3》日期和时间类型

 数据字段属性

一，DML（Data Manipulation Language，数据操作语言）

二，DQL（Data Query Language，数据查询语言）

三，从多个表显示数据

四，组函数

五，子查询 

六，事务操作

七，约束、视图

---

1.数据库总览

关系型数据库(SQL）：MYSQL、Oracle、SQLserver、SQlite、DB2

非关系型数据库(NOSQL）：Redis、MongoDB

数据库关系系统：DBMS（database manegement system)

数据管理软件，科学组织存储数据、高效的获取和维护数据

2.Myql简介

概念是现今流行的开源，免费的关系型数据库

特点：免费开源数据库、小巧功能齐全、使用便捷、可运用于windows或linux操作系统、可适用与中小型甚至大型网站应用

SQLyog：可手动操作、管理MySQL数据库的软件工具

数据库：长期存放在计算机内，有组织、可共享的大量数据的集合，是一个数据“仓库”

3.结构化查询语句

DDL(数据定义语言）：定义和管理数据对象如数据库数据表等

命令：CREATE DROP ALTER RENAME TRUNCATE

DML(数据操作语言）操作数据库中所含的数据

命令：INSERT UPDATE DELETE

DQL(数据查询语言):查询数据库语言

命令：SELECT

DCL（数据控制语言）：管理数据库的语言，包括管理权限及数据更改

命令：GRATE   COMMIT  ROLLBACK

4.SQL编写规范

SQL语句里所以表名、字段名全部小写、系统保留字、内置函数名、sql保留字大写

连接符or  in  and 以及=  <=   >=等前后加上一个空格

对较为复杂的sql语句，过程、函数加上注释、说明算法功能

SQL语句的缩进风格

1.一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进

2.where子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。

多表连接时，使用表的别名来引用列。

5.注意事项

SQL 命令是大小写不敏感

SQL 命令可写成一行或多行

一个关键字不能跨多行或缩写

子句通常位于独立行，以便编辑，并易读

6.SQL操作数据库

创建数据库

语法    CREATE  DATEBASE  数据库名

删除数据库

语法    DROP  DATEBASE 数据库名

查看数据库

语法  SHOW DATABASES 数据库名

使用数据库

语法  USE 数据库名

7.SQL创建数据表

创建数据表，属于DDL的一种

语法  CREATE  TABLE 表名（

字段1 列类型  [ 属性 ]  [ 索引 ] [注释] ,

字段2列类型  [ 属性 ]  [ 索引 ] [注释] ,

字段3 列类型  [ 属性 ]  [ 索引 ] [注释] ,

）[  表类型 ] [ 表字符集 ] [注释] ;

8.列类型分类

1》数值类型

tinyint  非常小的数据   取值范围：有符值-27~27-1   无符号值：0 ~ 28-1        1字节

smallint  较小的数据   取值范围：有符值-223~223-1   无符号值：0 ~ 216-1        2字节

mediumint   中等大小的数据   取值范围：有符值-223~223-1   无符号值：0 ~ 224-1     3字节

int          标准整数   取值范围：有符值-231~223-1   无符号值：0 ~ 232-1       4字节

bigint   较大的整数  取值范围：有符值-263~263-1   无符号值：0 ~ 264-1       8字节

float   单精度浮点数  取值范围：±1.1754351e -38      4字节  

double  双精度浮点数  取值范围：±2.2250738585072014e -308        8字节

decimal  字符串形式的浮点数  取值范围:  decimal(m, d)        m个字节

2》字符串类型

char  固定长字符串，检索快但费空间    0 <=  M  <=   255     最大长度:M字符

varchar 可变字符串 0 <=  M <= 65535   最大长度:变长度

tinytext      微型文本串           最大长度 28–1字节

text   文本串            最大长度216–1字节

3》日期和时间类型

 数据字段属性

unsigned  无符号的 、 声明该数据列不允许负数

zerofill   数字0填充的、不足位数的用数字0填充，如：int(3),5则为 005

auto_increment  自动增长的，每添加一条数据，自动在上一个记录数上加1、通常用于设置主键，且为整数类型

NULL 和 NOT NULL 默认为NULL，即没有插入该列的数值  如果设置为NOT NULL，则该列必须有值

DEFAULT   默认的

用于设置默认值  例如，性别字段默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值

数据表列类型注释及表注释

 SQL删除数据表

语法  DROP  TABLE  [ IF  EXISTS ]   表名

一，DML（Data Manipulation Language，数据操作语言）

区别TRUNCATE与DELETE

相同 都能删除数据、不删除表结构，但TRUNCATE 速度更快

不同 使用TRUNCATE TABLE会重新设置AUTO_INCREMENT计数器

二，DQL（Data Query Language，数据查询语言）

1. 查询表中所有的字段结果，采用“*”符号

SELECT   *   FROM student;

2.可指定查询的字段

SELECT     StudentNo, StudentName, Phone  FROM  student;

3.AS子句的作用

可给查询的字段取一个新别名，AS 也可省略不写

SELECT   StudentNo   AS   学号   FROM   student;

SELECT   a.StudentNo   FROM   student  AS  a;

SELECT   Phone+1  AS  Tel    FROM   student;

4.DISTINCT的作用

去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条

SELECT     DISTINCT   字段名1, 字段名2...     FROM   表名；

5.常见运算符 ：+ - * / %

.

 6.比较运算符 ：<   、>   =    <>     !=  （不等于）

7. BETWEEN AND范围查询

SELECT  字段列1，字段2 ,…FROM 表名 WHERE   字段x  BETWEEN  值1 AND  值2;  

查询课程表中课时在110和122之间的所有记录 SELECT  *  FROM  subject  WHERE ClassHour   BETWEEN  110   AND  122;

#等同于：  SELECT  *  FROM  subject  WHERE ClassHour >= 110  AND  ClassHour <=122;

8.like 模糊查询

在WHERE子句中，使用LIKE关键字进行模糊查询

与“%”一起使用，表示匹配0或任意多个字符

与“_”一起使用，表示匹配单个字符

示例#查询包含“数学”的所有课程 SELECT   *  FROM subject WHERE SubjectName  LIKE  "%数学%";

#查询所有姓名为“李**”三个字的学生信息 SELECT  StudentNo,StudentName FROM student   WHERE StudentName LIKE "李__";

9.in范围查询

SELECT  *  FROM  subject   WHERE   ClassHour = 100  OR ClassHour =110 OR ClassHour  = 122;  #普通处理方式

使用IN进行查询方式，更为简洁，效率更高 SELECT  *  FROM  subject  where ClassHour  IN ( 100, 110,122 );

10.null表示该字段属性为空

SELECT  *  from student  WHERE sex is null ;

空代表本字段无数据

SELECT *  from student   WHERE series = '';

11.limit限制行

第一种用法：直接加limit限制，表示显示前n条数据；
SELECT *  from student  LIMIT n;   

第二种用法：形式为limit m,n;    m为从第几行开始显示，m从0开始计数；n表示展示多少行；

SELECT *  from member   LIMIT 2,3; （第3行开始，5行结束）

12 Ordery by 升降序子句

ASC (升序)    DESC(降序) select* from  member ordery by salary asc ;(升序asc可不写）

select* from  member ordery by salary desc  ;

13.alter 使用方法

第一种增加列 （在设计结构中显示）

alter table emp add age int;(增加在最后一列）

alter table emp add age int after name;（增加在前面某列之后）

ALTER TABLE <表名> ADD <需添加的字段名> <字段类型> FIRST;（添加首例）

第二种修改列 change (1.修改列名，2.修改数据类型 3.修改属性）

alter table member change name Name varchar (50)  is null (前面是旧名字后面是新名字)

第三种，删除列drop

ALTER TABLE subject
DROP name003 ;

第四种，对表重命名

ALTER TABLE subject
RENAME subject1;

ALTER TABLE subject
RENAME to subject1;

14.#RENAME TABLE旧表 to 新表

rename TABLE subject1 to subject2;

 1.SELECT * 
FROM emp 
WHERE salary> 2000 and job = 'manager

2.SELECT * FROM emp 
WHERE salary*12 >30000 AND job != 'manager'

3.SELECT * FROM emp 
WHERE salary BETWEEN 1500 and 3000 AND job LIKE '%M%';

4.SELECT * FROM emp 
WHERE salary='' and (depNO =30 or deptNO =30);

5.SELECT * from emp WHERE salary !='' or deptNO =20 ORDER BY salary DESC;

6.SELECT * 
FROM emp 
WHERE (salary*12>30000 and job != 'manager') and(deptNO <> 10and deptNO <> 40) ORDER BY name ;
 

三，从多个表显示数据

1.多表查询概述：

• 指从多张表中查询数据
• 笛卡尔积：笛卡尔积是指在数学中，两个集合A集合和B集合的所有组成情况（在多表查询中需要消除无效的笛卡尔积）

举例：select * from emp , dept where  emp.deptno =dept.deptno;

2.多表查询分类

• 内连接：相当于查询A，B交集部分数据（符合条件数据就匹配，不符合条件数据就过滤掉）
• 外连接：左外连接指查询左表所有数据，以及两张表交集部分数据。  右外连接查询右表所有数据，以及两张表交集部分数据。（发起连接的表中记录全部被匹配，一个也不能少）
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接语法

隐式内连接：select  字段列名 from 表1，表2 where l连接条件

例子：select e.id , e.name, e.deptno,d.deptno,d.name from emp e ,dept d where e.deptno =d.deptno;

显式内连接：select  字段列名 from 表1  inner join  表2 on 连接条件

例子：select e.id , e.name, e.deptno,d.deptno,d.name from emp e inner join dept d on e.deptno =d.deptno;(因为是先显示from后面的语句，所以可以给表起别名，inner可省略）

外连接语法(outer可省略）

左外连接：select  字段列名 from 表1 left outer join 表2 on 连接条件

相当于查询表1（左表）所有数据包含表1和表2交集部分的数据

举例：查询emp表所有数据，和对应的部门信息（左外连接）

select  e.*,d.name from emp e left outer join dept d on e.deptno =d.deptno;

右外连接：select  字段列名 from 表1 right outer join 表2 on 连接条件

相当于查询表2（右表）所有数据包含表1和表2交集部分的数据

举例：查询dept表所有数据，和对应的部门信息（右外连接）

select  d.*,e.name from emp e right outer join dept d on e.deptno =d.deptno;

自连接语法

select  字段列名 from  表A  别名 A  join 表A  别名B on 连接条件

举例：查询员工及其所属领导的名字

SELECT a.name,b.Name FROM emp a ,emp b WHERE a.empNO=b.manager-id;

举例：查询员工emp 及其所属领导的名字emp ，如果员工没有领导也需要查询出来

表结构：emp a （员工），emp  b（领导）

SELECT a.name,b.Name FROM emp a LEFT OUTER JOIN emp b on b.empNO=a.maneger;

联合查询 union ,union all

l联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集。

select  字段列名 from  表A   where l连接条件

union (all)

select  字段列名 from  表B  where l连接条件

对于联合查询的多张表列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据合并在一起，union 会对合并后的数据去重

举例：将薪资低于5000的员工和年龄大于50的员工全部查询出来

select * from emp where salary<5000

union all (all去掉去重）

select * from emp where age>50;

多表连接，非等值连接，在（a,b 表两表内共有相同字段）

SELECT empno,demname,salary
from demp a ,salary b 
WHERE a.salary BETWEEN minsalary AND mixsalary;

四，组函数

1.分组函数类型

AVG ([DISTINCT|ALL]n)    求平均数

SUM ([DISTINCT|ALL]n)    求和

COUNT ({ *|[DISTINCT|ALL]expr})    计数

MAX ([DISTINCT|ALL]expr) 求最大值

MIN ([DISTINCT|ALL]expr)    最小值

STDDEV ([DISTINCT|ALL]x)

VARIANCE ([DISTINCT|ALL]x)

举例：SELECT AVG(age),SUM(age),MAX(age),MIN(age),COUNT(Name),COUNT(DISTINCT Name)
from emp;

2.#GROUP BY 分组
#1,所有用来分组的列在SELECT列表中不能使用分组函数
#2,出现在group by子句中的列，必须出现在select语句中；(不包括组函数所作用的列)

举例

SELECT deptNO ,age, MAX(salary)
from emp 
GROUP BY deptNO,age;

 3.HVING子句

Having子句的作用是对行分组进行过滤筛选

 SELECT deptNO,max(Salary)
FROM emp
WHERE deptNO = 100
GROUP BY deptNO
HAVING max(Salary) > 3000
ORDER BY deptNO
LIMIT N ;

接下来我们了解下sql语句执行的顺序

例：从学生表t_student中筛选出3年级年龄最大的女孩儿们，结果按照学号id升序

select max(age) from t_student where grade = 3 group by sex having sex = girl order by studentID asc

第一步：from 选择表

第二步：where 筛选条件，筛选对象--行

第三步：group by 将筛选出来的数据进行分组

第四步：having 筛选条件，筛选对象--组

第五步：select 选取最后的结果

第六步：order by 将结果按照特定顺序排列

从上面的执行顺序我们又知道了先执行where，后group by分组，分组完之后才能使用分组函数。

所以where后面的筛选条件出现了分组函数那就会报错。还没有进行分组呢，不能使用分组函数。

当我们遇到这种情况时，就要改变思路，用子查询即可，where后面跟一个子查询就迎刃而解了。
————————————————
 

五，子查询 

1.什么是嵌套查询

定义：在一个SELECT语句中的WHERE子句或HAVING子句中嵌套另一个SELECT语句的查询称为嵌套查询。其中，外层的SELECT查询语句叫外层查询或父查询，内层的SELECT查询语句叫内层查询或子查询。

2.使用子查询的规则

• 子查询要用括号括起来
• 将子查询放在比较运算符的右边
• 子查询中不要加 ORDER BY子句，ORDER BY子句只能对最终查询结果即最外层查询的结果集进行排序
• 对单行子查询使用单行运算符
• 对多行子查询使用多行运算符
• 子查询又分为不相关子查询和相关子查询。当子查询的查询条件不依赖于父查询时，这类子查询称为不相关子查询；当子查询的查询条件依赖于父查询时，这类子查询称为相关子查询。

3.子查询种类

• 单行子查询 （单行单列）
• 多行子查询（多行单列）
• 多列子查询   （单行多列）

3.1单行子查询

• 返回一条记录，返回单行单列的数据
• 使用单行记录比较运算符 =  >   <    >=   <=    <>   !=

举例：哪些员工的工资比marry的工资高

select * from emp where salary > (select salary from emp where name  = 'mary');

举例：查询最低工资大于50号部门最低工资的部门和其最低工资

select deptno ,min(salary) from emp group by deptno having min(salary) >(select  min(salary) from where deptno= 50);

举例：返回job与10号员工相同，salary比13号员工多的员工姓名，job和工资

select deptno ,job ,name, salary from emp where job=(select job from emp where deptno =10) and salary > (select salary from where deptno =13);

3.2多行子查询

• 返回多行
• 使用多行比较运算符 in  any   all
• >all指大于最大值
• <all 指小于最小值
• >any 指大于最小值
• <any 指小于最大值
• any 和all都用于where查询的子句，any是只要满足括号里的一个条件就返回true，all是必须满足括号里的全部条件才返回true，

举例：查询出001班级中年龄大于002班级所有人的同学

select * from student where 班级=001 and age >all (select age from student  where 班级=002）

相当于select * from student where 班级=001 and age > (select max(age) from student  where 班级=002）

举例：查询出001班级中,年龄大于002班级任意一个的同学

 select * from student  where 班级=001 and age>any (select age from student where 班级=002）

相当于select * from student where 班级=001 and age > (select min(age) from student  where 班级=002）

3.3多列子查询

多列子查询则是指返回多列数据的子查询语句。当多列子查询返回单行数据时，在where子句中可以使用单行比较符。而返回多行数据时，在where字句中必须使用多行比较符。（in，all，any）。

举例：查询员工编号最小并且工资最高的员工信息

一般思路：SELECT * FROM employees WHERE emp_id=( SELECT MIN(empe_id) from employees ) AND salary=( SELECT MAX(salary) FROM employees );

行子查询思路：SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) from employees );

4.拷贝数据到新表

1.复制表结构及数据到新表

create table student001(新表)

select * from student(旧表);

2.1仅复制表结构到新表

create table student002(新表)

select*from student where 1=2; (where1=2代表错误信息）

2.2.仅复制表结构到新表

create table student100 like student;(like仅支持5.0MySQL版本)

3.新表已存在通过旧表复制数据到新表

insert into student100

select* from student；

4.新表已存在通过旧表复制数据到新表(表结构不一样)

INSERT INTO student009(ID,Name,Sex,Address,Age,Series) SELECT stuID,stuName,Sex,Address,Age,Series from student;

六，事务操作

事务操作分为两种: 自动事务(默认的), 手动事务

默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务。为了让多条SQL语句纳入到一个事物之下，可以手动管理事务。

1.START TRANSACTION;COMMIT ；ROLLBACK；

• 当下列情况发生时事务自动提交：执行一个 DDL 语句、执行一个 DCL 语句 、从 SQL*Plus正常退出
• 当从SQL*PLUS中强行退出或系统失败时,事物自动回滚

2.COMMIT 和ROLLBACK优点

• 保证数据一致性
• 在数据永久性生效前重新查看修改的数据
• 相关逻辑操作单元

3.提交或回滚前数据状态

• 以前的数据可恢复.
• 当前的用户可看到DML操作的结果.
• 其他用户不能看到DML操作的结果.
• 被操作的数据被锁住,其他用户不能修改这些数据.

4.提交后数据状态

• 数据的修改被永久写在数据库中.
• 数据以前的状态永久性丢失.
• 所有的用户都能看到操作后的结果.
• 记录锁被释放,其他用户可操作这些记录.
• 所有的 savepoints 被去掉.

5.回滚后的数据状态

• 语句将使所有的修改失效.
• 修改的数据被回退.
• 恢复数据以前的状态. 
• 行级锁被释放.

6.事务特性

事务的ACID特性:

1、原子性(atomicity): 一个事务中包含的所有sql语句都是一个不可分割的单元。

2、一致性（consistency） 事务必须确保数据库的状态是一致的。

3、隔离性(isolation) 多个事务独立运行，彼此不影响。

4、持久性(durability) 事务一旦提交，数据库的变化就会被永久保留下来。

7.读一致性

• 读一致性保证了查询数据得到一致的结果.
• 不同用户修改的数据不会发生冲突.
• 对相同的数据操作时确保:查询时不用等写完成 、写时不用等查询完成

七，约束、视图

1.什么是约束

• 约束是表级强制执行的规则.
• 当表中数据有相互依赖性时,可保护数据不被删除
• Oracle 有如下类型的约束:NOT NULL、UNIQUE Key  、PRIMARY KEY  、FOREIGN KEY  CHECK

2.外键约束的关键词

• FOREIGN KEY  定义子表的哪一列作为外键约束
• REFERENCES 指示主表和参照的列
• ON DELETE CASCADE 删除主表记录时将子表相关记录删除
• ON DELETE SET NULL 将外键引用置为空值

3.约束的添加、修改。删除

添加主键约束：alter table student  add primary key(id)

修改主键约束：将id修改为name

alter table student  modify name varchar(32)primary key;

删除主键约束：alter table student drop primary key;

4.视图

4.1什么是视图

视图: view, 是一种有结构(有行有列)但是没结果(结构中不真实存放数据)的虚拟表, 虚拟表的结构来源不是自己定义, 而是从对应的基表中产生(视图的数据来源).

4.2视图的意义

• 视图可以节省SQL语句: 将一条复杂的查询语句使用视图进行保存: 以后可以直接对视图进行操作
• 数据安全: 视图操作是主要针对查询的, 如果对视图结构进行处理(删除), 不会影响基表数据(相对安全).
•  视图往往是在大项目中使用, 而且是多系统使用: 可以对外提供有用的数据, 但是隐藏关键(无用)的数据: 数据安全
• 视图可以对外提供友好型: 不同的视图提供不同的数据, 对外好像专门设计
• 视图可以更好(容易)的进行权限控制

4.3可对视图进行DML操作，操作规则

 1在下列情况下不能删除记录: 视图包括组函数 、视图包括GROUP BY 子句 、视 图 包 括 DISTINCT  、Rownum伪列关键词 。

2.在下列情况下不能修改记录 : 前面所提到的情况、 列是由表达式定义的 、包括ROWNUM 虚列

3.在下列情况下不能添加记录 : 前面所提到的情况、 视图的基表有非空列,但在视图中没有此列

4.4创建视图

Create view 视图名字 as select语句; -- select语句可以是普通查询;可以是连接查询; 可以是联合查询; 可以是子查询. 创建单表视图: 基表只有一个  创建多表视图: 基表来源至少两个

例如CREATE VIEW student001as SELECT stuid , name,age  from student;

4.5查看视图

视图是一张虚拟表: 表, 表的所有查看方式都适用于视图: show tables [like]/desc 视图名字/show create table 视图名; 视图一旦创建: 系统会在视图对应的数据库文件夹下创建一个对应的结构文件: frm文件

4.6修改视图

Alter view 视图名字 as 新的select语句;

4.7删除视图

Drop view 视图名字;

4.8新增数据

1.多表视图不能新增数据

2.可以向单表视图插入数据: 但是视图中包含的字段必须有基表中所有不能为空(或者没有默认值)字段

3.视图是可以向基表插入数据的.

4.9删除数据

多表视图不能删除数据  单表视图可以删除数据

5.其它数据对象

5.1什么是序列号

自动生成唯一的数字 、是一个共享的对象 、典型的应用于表的主键 、可替代应用代码、 将序列号值放在缓存中可提高访问速度

create sequence 定义序号