Mysql最全笔记

目录

一、 MySQL的重要性
二、MySQL介绍
三、软件的服务架构
四、 MySQL的安装
五、 SQL语句
六、 数据库相关（DDL）
七、 表相关
八、 DML相关（表中数据）
九、 DQL（重点）
十、 数据完整性
十一、 表与表之间的关系

一、MySQL的重要性

1.1定位
（1）重点在SQL语句 ，需要能够在没有任何提示下，用纸和笔手写SQL。
（2）在面试中95%的概率 手写SQL语句。
（3）通过SQL语句去操作数据库
需要了解SQL、Oracle、SQLserver等数据库的区别：方言

二、MySQL介绍

2.1什么是数据库？
作用：存储数据的，能够长期（断电、关机在开机数据还有）保存数据。
数据存储在哪里：硬盘和内存
我们平时说的数据库：数据管理系统（软件）（Databases Manage System： DBMS）
数据库软件（电脑的Excel软件）中可以创建多个文件夹（数据库（逻辑的））
一个数据库：（一个Excel文件）存储XX电商项目数据
一个数据库：（一个Excel文件）存储XX图书馆管理项目数据
每个数据库可以继续划分（表）
数据库软件–>多个数据库–>多个表–>多条数据（一条一行，一行多列）

2.2数据库的分类
2.2.1关系型数据库（RDBMS：relative database man）
特点：
（1）表与表之间有关系
（2）是通过SQL语句去操作数据库。
（3）有行有列（和Excel类似）
比较有名：
MySQL:免费、开源。
Oracle：甲骨文，收费，大型公司，一年费用9位数。
SQL Server：微软公司，可以安装Windows
DB2
2.2.2非关系型数据库（No-SQL）
特点：
（1）表与表之间没有关系
（2）通过API（java、PHP、Python代码）去操作
（3）充分使用内存，同步到磁盘
比较有名的代表：
Redis：
HBase：
mangoDb：
neo4j：
2.2.3什么是SQL
结构化查询语言(Structured Query Language)简称SQL，用于存取数据以及查询、更新（数据的操作）和管理（数据库、表的创建、修改、删除）关系数据库系统；
通过SQL语句去操作关系型数据库
不同的数据库对SQL语句的支持不完全一样
85%的SQL语句，关系型数据库都支持。
各个数据库在SQL语句上都有自己的扩展（方言）。
结构化：有行有列的数据、Excel数据
非结构化：视频、音乐

三、软件的服务架构

3.1一些场景
刷抖音
抖音APP；短视频通过网络获取，在网络之后有人给你服务。（服务器）
和别人微信聊天
打开APP，通过网络和别人聊天，在网络之外有人提供服务（服务器）
上淘宝购物
打开浏览器，输入淘宝的网址

3.2架构模式

C：Client，客户端
S：server，服务器
B：Browser：浏览器
C/S：客户端/服务器端
抖音APP/微信/手淘
B/S：浏览器/服务器端
淘宝网站
注意：B/S是特殊的C/S架构。
总结：一个项目，肯定不单单只有一个APP那么简单。
3.3MySQL的架构
MySQL其实就一个B/S架构。
要使用MySQL，
（1）先启动MySQL的服务
（2）通过客户端连接MySQL的服务（客户端有很多）
（3）客户端有很多：图形化界面、命令行、代码

四、MySQL的安装

4.1企业的用法
（1）购买云服务（阿里云、腾讯云）然后自行安装MySQL服务。
阿里云：
64核 128GB–>64台（1核 2GB）
（2）直接购买MySQL服务器。（帮你做了很多：定期备份、宕机重启、宕机报警）

4.2命令
启动命令终端： Win + R–>输入cmd–>回车
启动MySQL服务：net start mysql
关闭MySQL服务：net stop mysql
4.3客户端
方式一：命令行 mysql -u root -p
-u：user：用户名 root（超级管理员）
-p：password：密码
-h：hostname主机名（ip）
-P：Port端口
你没有指定-h，则相当于“-h localhost”，此时是以socket方式连接的，而不是以tcp/ip方式，-P参数不起作用。进去以后你可以用show status看一下connection连接方式这一行验证一下。
方式二：图形化界面
（1）Navicat for MySQL 复制到不含中文的目录下
（2）双击“navicat.exe”运行
（3）输入激活码

五、SQL语句

5.1数据库的概念
MySQL：数据软件
数据库：在软件里面创建多个数据库（逻辑、文件夹）
在数据库中可以创建多个表。
数据：数据必须存储在表中。
5.2SQL语句的分类

• 数据查询语言DQL（Data Query Language） 对表中 数据 的查询

• 数据操纵语言DML（Data Manipulation Language） 对 表中 数据 的操作

• 添加数据、删除数据、更新数据、查询数据（DQL） 对Excel中的数据进行处理。

• 数据定义语言DDL（Data Definition Language） 对数据库（database）、表（table）结构的定义。

• 创建数据库、删除、修改 创建表、删除、修改 创建一个Excel文件 数据控制功能DCL（Data Control Language）

  对用户的权限的管理。
  设置用户只能查看数据，不能修改数据。
  设置用户只能对其中的 一个数据库进行操作。
  DBA：数据库管理员
  5.3SQL语法
  （1）代码，用“;”表示结束。
  SQL写完之后，以英文的“;”表示结束。
  （2）SQL语句可能会很长
  在记事本中完成SQL语句的编写，然后复制
  5.4SQL关键字
  Crtate：创建
  Date：日期
  Database：数据库
  Show：展示；查看
  Drop：删除
  Where：在条件；条件
  Alter：修改
  Table：表格
  Modify：修改
  Remove：移除，删除
  Key：钥匙；键
  Row：行；多少行
  Field：字段；列
  Type：类型
  Query：查询
  Update：更新
  Data：数据
  5.5记事本推荐
  在写sql语句时必不可少的是记事本，这对于写语句，校验，存储sql，都有很大有用处，直接在命令行中打一连串的命令有些不现实。
  这里推荐用 Nodepad++
  使用：
  （1）在桌面 创建一个文本文件。
  （2）把文件的后缀名改成sql。
  （3）这个文件用nodepad++打开这个文件
  （4）SQL语句的关键字会有提示。

六、数据库相关（DDL）

6.1创建数据库

#（1）创建数据库
create database + 数据库名字;

   
   

   
   
1
2

6.2查看所有数据库

#（2）查看所有数据库
show databases;

   
   

   
   
1
2

6.3删除数据库

#（3）删除数据库
drop  database 数据库名字;

   
   

   
   
1
2

6.4字符集
字符集：字符的集合。
什么是字符：a,b,c,d,A,B,0,1,2,3,_,し，て，い，る。
GB2312：中文字符集
ASCLL字符集：
Utf-8：统一的字符编码

七、表相关

7.1表的概念

7.2 MySQL的数据类型
7.2.1数值

7.2.2字符串

7.2.3日期类型

7.3创建表

create table 表名(
       字段1名字  字段1类型,
	   字段2名字  字段2类型,
       字段3名字  字段3类型,
)
create table t_user(
     id  int, 
	 name varchar(4),
	 age tinyint,
	 sex varchar(2),
	 phone varchar(11),
	 score double(5,2)

);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

7.4查看表

show  tables;（查看所有表）

 
 

 
 
1

7.4.1查看表所有的数据

select * from  +表名;	

 
 

 
 
1

7.6表的修改

7.6.1添加列：

##（1） 添加一列（添加一个字段）
##给用户表 t_user添加一列 字段名为 balance ，类型是 double;
alter table t_user add balance double(5,2);

 
 

 
 
1
2
3

7.6.2修改列：

##（2） 将性别的数据类型改成 int （0：男，1：女 ， 2：未知）
alter table t_user modify sex int(1);

 
 

 
 
1
2

7.6.3删除列：

##（3） 把 t_user表的 balance字段删除
alter table t_user drop balance;

 
 

 
 
1
2

7.6.4修改列名：

##（4） 把 t_user 表，手机字段改成tel
alter table t_user change phone tel varchar(12);

 
 

 
 
1
2

7.6.5修改表(重命名)的名字

##（5）修改表(重命名)的名字 t_user-->tb_user;
rename table t_user to tb_user;

 
 

 
 
1
2

八、DML相关（表中数据）

8.1DML是什么
通过DML实现表中数据的操作
插入数据： insert
查询数据： select
更新数据： update
删除数据： delete
8.2插入数据
语法：
insert into 表名(字段1,字段2,字段3…) values(字段1的值,字段2的值,字段3的值,…)
注意：
（1）前后字段 个数 类型 必须一样。
（2）数字（整数，小数）、布尔类型 值正常写，其他类型需要加上单引号‘zsf’;
（3）如果插入的 值 是空的 null
8.2.1正常的写法

##（1）插入数据 1、zsf 18岁 、男、1772222、90
insert into  tb_user(id,name,age,sex,tel,score)values(1,'zsf',18,'1',1772222,90);	

 
 

 
 
1
2

8.2.2字段名省略

8.2.3一次性插入多条数据

8.2.4字段缺失

##（4）字段缺失
insert into  tb_user(id,tel) values(5,'1506544');
insert into tb_user(id,name,tel)values(6,null,'1888823');

 
 

 
 
1
2
3

8.3更新数据
8.3.1语法

语法：
update 表名 set 字段1名字 = 字段1的值, 字段2名字 = 字段2的值 where 条件

 
 

 
 
1
2

8.3.2更新所有数据

8.3.3根据条件更新

##(2)将学号为6的学生成绩改为59;
update tb_user set score=59 where id=6;

 
 

 
 
1
2

##(3)将名字为ww的学生，成绩设置为60;
update tb_user set score=60 where name='ww';

 
 

 
 
1
2

8.3.4更新多个字段

 ##(4)将学号是5的学生，成绩设为99，名字设置为“xl”;
update tb_user set  score=99,name='xl' where id=5;

 
 

 
 
1
2

 
##(5)将所有人的成绩减去10分；
update tb_user set score=score-10;

 
 

 
 
1
2
3

8.4删除数据
8.4.1语法

语法：
delete  from  表名 where 条件 

 
 

 
 
1
2

8.4.2删除所有数据

##（1）删除所有数据
delete from tb_user ;

 
 

 
 
1
2

8.4.3根据条件删除

##(2) 删除学号为1的学生数据
delete from tb_user  where id =1;	

 
 

 
 
1
2

##(3)删除姓名为“ww”的学生
delete from tb_user where name = 'ww';

 
 

 
 
1
2

8.4.4truncate删除

##(4)删除所有数据
truncate table tb_user;

 
 

 
 
1
2

8.4.5delete 和 truncate区别

##(5)delete 和 truncate区别
delete :一行一行的把数据删除;
truncate: 先删除表（drop），重新创建（create）表;

 
 

 
 
1
2
3

九、DQL（重点）

DQL介绍
数据库在执行DQL的时候，不会对数据进行改动，仅仅是查看，只要把服务器中的数据获取返回给客户端。
查询的到的结果也是一张表

9.1数据准备

mysql> create  table stu(
    -> sid char(6),
    -> sname varchar(50),
    -> age int ,
    -> gender varchar(50)
-> );

 
 

 
 
1
2
3
4
5
6

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

 
 

 
 
1
2
3
4
5
6
7
8
9
10
11

create table emp  (empno int,
ename varchar(50), 
job varchar(50), 
mgr int, hiredate date,
sal decimal(7,2),
comm decimal(7,2),deptno int);

 
 

 
 
1
2
3
4
5
6

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table dept(
-> deptno int,
-> dname varchar(50),
-> loc varchar(50));
INSERT INTO dept values(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept values(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept values(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept values(40, ‘OPERATIONS’, ‘BOSTON’);
9.2基础查询
9.2.1查询所有的列

##（1.1）查询所有列：*表示所以列
###eg：查询所有的员工信息
select * from emp;       /select * from + 表名;

 
 

 
 
1
2
3

9.2.2查询指定列

##(1.2)查询指定列
select empno,ename,deptno from emp;   /select+要查询的列 from + 表名;

 
 

 
 
1
2

9.3条件查询where
9.3.1常见的运算符
关系运算符：=、！=、<、>、>=、<=
区间：between A and B --> [A,B]
And:并且、和 Not null: 非空
In：在…里面 Is not null:非空
Or：或者 Not：否、非

9.3.2或者Or、并且and

##（2.1）查询性别女，并且 年龄65的记录
select *from stu where  gender = 'female'  and  age =65;

 
 

 
 
1
2

 ##(2.2)查询学号是S_1001或者名字为lisi的记录
 select * from stu where sid ='S_1001' or  age = 65;

 
 

 
 
1
2

9.3.3在什么里面In

##(2.3)查询学号是S_1001,S_1002,S_1003的记录
 写法一：select * from stu  where sid = 'S_1001' or  sid='S_1002'  or sid='S_1003';
 写法二：select * from stu  where sid in ('S_1001','S_1002','S_1003');
##(2.4)查询学号不是S_1001,S_1002,S_1003的记录
 select * from stu  where sid not in ('S_1001','S_1002','S_1003');

 
 

 
 
1
2
3
4
5

9.3.4是否为空is（not）null

##（2.5）查询年龄为null 的记录
select * from stu where age is null;

 
 

 
 
1
2

9.3.5区间between

##（2.6）查询年龄在20~40岁之间的
方式一：select * from stu  where age >=20 and  age <=40;
方式二：select * from stu  where  age between  20 and 40;

 
 

 
 
1
2
3

9.3.6非not

##（2.7）查询性别是非男性的学生记录
select *from stu where gender != 'male';
select *from stu where gender <>'male';
select *from stu where not  gender = 'male';

 
 

 
 
1
2
3
4

9.4模糊查询

9.4.1介绍
（1）当想查询学生姓名中饱含字符a，就需要使用模糊查询，模糊查询的话使用关键字是like（像）
（2）通配符
:任意一个字符
张: 张三，张三丰
张__:张三，张三丰
%：表示0或者多个字符
张%：张三，张三丰

9.4.2通配符：_、%

##(3)通配符
##查询名字由五个字母构成的 学生记录
select * from stu where sname like'_____'; （五个‘_’）

##查询名字由五个字母构成的 并且第五个字符是i的学生记录
select from stu where sname like ‘_____i’;
##查询名字以z开头 的学生记录
select from stu where sname like ‘z%’;

##查询名字第二个字母是i的学生
select * from stu where sname like ‘_i%’;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

注意：
（1）SQL的性能优化
避免使用*，如果表中有100个字段，全部列出。
模糊查询时候，避免使用通配符开头（避免全表扫描）

##查询名字以z开头 的学生记录
select * from stu where sname like 'z%';

##查询名字第二个字母是i的学生
select * from stu where sname like ‘_i%’;

• 1
• 2
• 3
• 4
• 5

9.5字段控制查询

9.5.1去除重复查询

##（1）去除重复数据
select disctinct deptno from emp;

 
 

 
 
1
2

9.5.2 ifnull

##(2)查询员工的薪资加奖金
ifnull (A,B);如果A是空 使用B的值 如果A不是空就使用A
##问题： 任何数据 + null--->null;
 ##1800  +  null  -->1800;
select  sal , comm ,sal+ifnull (comm,0) from emp ;

 
 

 
 
1
2
3
4
5

9.5.3别名as
别名：给这个列 换 一个新名字。

##9.7别名
select  sal , comm ,sal+ifnull (comm,0)as money from emp ;

##as 关键字可以省略
select ename name ,sal , comm ,sal+ifnull (comm,0)as money from emp ;

• 1
• 2
• 3
• 4
• 5

9.5.4排序 order by asc/desc
升序：asc 降序：desc

##排序
##升序order by
##（1）查询所有的学生信息 根据年龄升序排列
select * from stu order by  age asc;

 
 

 
 
1
2
3
4

##（2）查询所有的学生信息 根据年龄降序排列
select * from stu order by  age desc;

 
 

 
 
1
2

##（3）查询所有员工信息 根据工资进行排序  如果工资相同按照员工工号升序
select * from emp order by sal desc ,empno asc;

 
 

 
 
1
2

9.6聚合函数
MySQL系统提供了很多函数：
Count：统计个数，次数，null不统计 Round：四舍五入
Msx：最大值 Sum：求和
Avg：平均值
9.6.1 count函数

##count 函数
##（1）查询emp表中记录数
count(*):不会忽略null的值
select  count(*) from emp;

##（2）统计emp表中 有提成的人
##count（具体字段），null的话会被忽略
select count(comm) from emp;

##（3）工资大于2500的员工人数
select count() from emp where sal>2500;
##（4）薪资 （工资+奖金）大于2500的员工人数
select count() from emp where (sal+ifnull(comm,0) )>2500;

##（5）薪资有提成的人数、有上一级领导的人数；
select count(comm),count(mgr) from emp;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

9.6.2 sum求和，avg平均数，round四舍五入

##sum、avg、round
##（1）查询所有员工工资总和
select sum(sal) from emp;

##（2）查询所有员工工资、提成 总和
select sum(sal) ,sum(comm ) from emp;

##（2）查询所有员工工资、提成 总和、薪资
select sum(sal) ,sum(comm) ,sum(sal+ifnull(comm,0)) money from emp;

##(3)计算员工平均工资
select avg(sal) , avg(comm) from emp;

##(4)平均工资保留两位小数
select round (avg(sal),0),avg(comm ) from emp;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

9.6.3 Max 和 Min

##Max最大值Min最小值
##(1)查询员工表中，工资最高的、最低的
select  max(sal) max_sal,min(sal)  min_sal from emp;

 
 

 
 
1
2
3

9.6.4 分段函数 case
##员工表中，根据薪资不一样，给不一样的评论
>4000,NB666
>3000,NB666
>2000,NB6
<2000,ZZ

select 
       sal,
	   case
	           when sal>4000   then'NB666'
			   when sal>3000   then'NB66'
			   when sal>2000   then'NB6'
			   else 'ZZ'
	   end as pj
from
        emp;

 
 

 
 
1
2
3
4
5
6
7
8
9
10

9.6.5字符串截取
Left(A,4)从左边开始截取4个字符
Right(A,4) 从右边开始截取4个字符
我 爱 你

1987-04-19-->1987
###（1）获取员工的入职年份
select ename,hiredate,left(hiredate,4)from emp;

 
 

 
 
1
2
3

###（2）获取员工的入职工龄
select ename,hiredate,2021-left(hiredate,4)from emp;

 
 

 
 
1
2

输出中间的数04    select right (left('1987-04-19',7),2);

 
 

 
 
1

9.7分组查询 group by

9.7.1场景
统计各个部门的人数情况
按照性别统计人数。
按照班级统计参加比赛的人数。
##(1)统计各个部门的人数
部门ID 人数
10 6
20 7
30 3

部门ID 人数 平均工资
10 6 100
20 3 200
30 5 150
（1）分组之后的结果：和具体一条数据没有关系
（2）分组会和聚合函数一起使用
（3）需求中包含：“每个”，“各个”，这种，group by
##(1)统计各个部门的人数

部门ID 人数 平均工资
10 6 100
20 3 200
30 5 150

select
          deptno,count(*),avg(sal)
from 
        emp
group by
        deptno;

 
 

 
 
1
2
3
4
5
6

##（2）统计各个部门各个岗位 人数。
部门id 平均工资
10 600
20 300
30 400

部门ID 岗位 人数
1 java开发 10
2 前端开发 6
3 出纳 2
2 会计 4

select 
      job, deptno,count(*)
from 
       emp 
group by 
       deptno,job;

 
 

 
 
1
2
3
4
5
6

9.7.3having
需求：统计平均工资超过2000的部门。
算出各个部门的平均工资。
过滤，只留下超过2000的。

select
     job,avg(sal) as avg_sal
   from
   emp
   Group by
   Deptno
Having 
    Sal_avg>2000;

 
 

 
 
1
2
3
4
5
6
7
8

9.7.4 where 与 having的区别
Where ：过滤：分组之前过滤：对数据源进行过滤。
Having过滤：分组之后的过滤
9.8 Limit
Limit限制查询结果的条目数。

##语法： limit start len;从start开始,查询len条
##(1)查询前五条数据
select * from emp limit  5;
##（2）查询第二条到第五条数据
select * from emp limit 2,5;

 
 

 
 
1
2
3
4
5

使用场景：分页查询
如果一个页面显示10条数据，获取数据
第一次 limit 0,10
第一次 limit 10,10
第一次 limit 20,10
9.9执行顺序

select 
          deptno,avg(sal)as sal_avg
from 
          emp
where 
          mgr is not null
group by
          deptno
order by  
          sal_avg desc
limit 2;	

 
 

 
 
1
2
3
4
5
6
7
8
9
10
11

关键字执行顺序：
from -->where -->group by–>select–>having–>order by–> limit

十、数据完整性

10.1什么是数据完整性
作用：保证 用户输入的数据 保存到数据库 是正确的。
比如：
年龄：应该是数字
学号：应该是不能重复的。
成绩：不能为空
怎么去保证数据的完整性：添加约束。
完整性约束分类
实体（记录） 完整性
域完整性
引用完整性
10.2实体完整性（行）
实体（entity）：就是表中一行的（记录）数据。
实体完整性：标识 每一条数据不能重复。
约束类型（通过什么方式解决这个问题）：
主键约束（primary key）
不能为空，不重复
唯一约束（unique）
不能重复，可以为空
自增长列（auto_increment）
序号自己会改变
10.2.1主键约束（primary key）
特点：不能为空，不重复

 ##没有约束
   create  table stu0(
          id  int,
		  name varchar(50)
)
insert into stu0(name)varchar('张三丰');

 
 

 
 
1
2
3
4
5
6

##方式一：创建表，并且添加主键约束
create table stu1(
            id int primary key,
			name varchar(50)
		)
	##方式二：
	   create table stu2(
	           id  int,
			   name varchar(50),
			   primary key(id,name)
			   )
成功： insert into stu1(id,name)value(2,'张三丰');##成功
 测试一：insert into stu1(id,name)value(null,'张三丰');##失败 ，提示不能为空
        ERROR 1048(23000):Column'id'cannot be null
 测试二：
            插入重复值：报错
			     Duplicate entry'2' fro key 'PRIMARY'
select * from stu1;
   自增长列（auto_increment）

 
 

 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

10.2.2唯一约束（unique）
不重复，可以为空
##给名字 添加 唯一的 约束

create table stu3(
    id int primary key,
	name varchar(50)unique
	)
	insert into stu3(id,name)value(1,'张三丰');
	insert into stu3(id,name)value(2,'张三丰');
ERROR <span class="token number">1062</span><span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>:<span class="token keyword">Duplicate</span> entry<span class="token string">'张三丰'</span><span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'name'</span>

<span class="token keyword">insert</span> <span class="token keyword">into</span> stu3<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name <span class="token punctuation">)</span><span class="token keyword">value</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'张三'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

10.2.3自增长列（auto_increment）

##实现，用户的学号，自动增长，
	create table stu4(
	  id int primary key auto_increment,
	  name varchar(50)
	  )
  <span class="token comment">##插入数据</span>
  <span class="token keyword">insert</span> <span class="token keyword">into</span> stu4<span class="token punctuation">(</span>name<span class="token punctuation">)</span><span class="token keyword">value</span><span class="token punctuation">(</span><span class="token string">'张三丰'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token keyword">insert</span> <span class="token keyword">into</span> stu4<span class="token punctuation">(</span>name<span class="token punctuation">)</span><span class="token keyword">value</span><span class="token punctuation">(</span><span class="token string">'ls'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

10.3.1数据类型

10.3.2非空约束（not null）

##创建表，名字name 字段为非空
	create table stu5(
	   id int primary key ,
	   name varchar(50) not null,
	   sex varchar(10)
	   );
   <span class="token comment">##成功：</span>
   <span class="token keyword">insert</span>  <span class="token keyword">into</span> stu5<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>sex<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'zsf'</span><span class="token punctuation">,</span><span class="token string">'n'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token keyword">insert</span>  <span class="token keyword">into</span> stu5<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>sex<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'null'</span><span class="token punctuation">,</span><span class="token string">'n'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
   ERRO <span class="token number">1048</span><span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>:<span class="token keyword">COALESCE</span><span class="token string">'name'</span>cannot be <span class="token boolean">null</span><span class="token punctuation">;</span>
    <span class="token keyword">insert</span>  <span class="token keyword">into</span> stu5<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>sex<span class="token punctuation">)</span> <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'zz'</span><span class="token punctuation">,</span><span class="token string">'null'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	ERROR <span class="token number">1062</span> <span class="token punctuation">(</span><span class="token number">23000</span><span class="token punctuation">)</span>: <span class="token keyword">Duplicate</span> entry <span class="token string">'2'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'PRIMARY'</span>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

10.3.3默认值约束（default）
值没给，就使用默认值。

create table stu6(
   id int primary key ,
   name varchar(50)not null,
   sex varchar(10)Default'N'
   );
查看表结构
<span class="token keyword">desc</span> stu6<span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> stu6<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">,</span>sex<span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'zsf'</span><span class="token punctuation">,</span><span class="token string">'nv'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> stu6<span class="token punctuation">(</span>id<span class="token punctuation">,</span>name<span class="token punctuation">)</span><span class="token keyword">values</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">'zsf'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
查看结构
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> stu6<span class="token punctuation">;</span>

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12

10.4引用完整性
主外键的关系，2个表之间建立管理关系之后，他们删除，更新操作都是受影响的。

##引用完整性
学生表 
        id
       名字
        性别
成绩表
         id
       学生ID
	   成绩
create table stu7(
     id int primary key,
	 name varchar(50)
	);
create table score(
   id int primary key,
   sid int ,
   score double,
   constraint aa foreign key(sid) references stu7(id)
   );

insert into stu7(id,name)values(1,‘zsf’);
insert into score(id,sid,score)values(1,1,90);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (</span>db_0402 <span class="token punctuation">.</span>score<span class="token punctuation">, CONSTRAINT </span>aa<span class="token punctuation"> FOREIGN KEY (</span>sid<span class="token punctuation">) REFERENCES </span>stu7<span class="token punctuation"> (</span>id<span class="token punctuation">))

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24

十一、表与表之间的关系

11.1为什么要拆表？
拆分表的目的：
为了解决数据的冗余的问题，可以减少重复数据的储存，表的结构也更加清晰了。
拆分之后：
在某些业务下，需要查询多个表，速度降低了。
空间 和 时间
11.2表与表之间的关系
11.2.1一对一
人与身份证的关系：
注意：
（1）一对一不存在数据冗余问题 可以不拆分表吗？ 可以！
（2）什么场景需要拆分
用户信息 + 身份证+……>表中信息太多了。
11.2.2一对多
最最常见的关系：
学生和成绩的关系：1个学生有多个成绩
部门和员工的关系：1个部门有多个员工
这种关系怎么去建立，怎么去表达？
在多的一方存储“一”的主键ID。
在成绩表中存储用户ID
在员工表中存储部门ID
建表：使用主外键实现。
11.2.3多对多
老师与学生的关系：一个老师对应多个学生，一个学生对应多位老师。
这种关系怎么去建立，怎么去表达？
用户和商品：订单（谁（用户ID）什么时候买了什么商品（商品ID））
ID 用户ID 商品ID
1001 1 6
1002 1 5
1003 2 6

用户和权限的关系：谁拥有什么权限
ID 用户ID 权限ID
1001 1 1001
1002 1 1003
1003 2 1001
11.3多表查询
多表查询查询有如下：
（1）合并结果集：union，union all
（2）连接查询：（列的合并）
内连接：
外连接：
左外链接：
右外连接：
全连接：
自然连接
（3）子查询
11.4合并结果集（行连接）
2个结果集（表）合并到一起。
要求：
11.4.1union all（全部）
将两个表的结果直接放在一起，不做任何处理。
11.4.2union
将两个表的结果直接放在一起，相同的数据只留下一个（去重）
11.5连接查询（列连接）
问题：学生表有10条数据，成绩有10条数据
连接后有多少条数据（笛卡尔积）：10*10
连接查询会产生笛卡尔积；假设集合A={a，b}，集合B={c，d}，
2个集合的笛卡尔积{（a，c），（a，d），（b，c），（b，d）}

但是：
（1）从数据来讲，有很多无用的数据。
（2）通过 where条件过滤无用数据：通过主外键来清除无用数据

使用主外键清除无用数据
select * from emp,dept where emp.deptno=dept.deptno;

 
 

 
 
1
2

上面的写法就是内连接，但是它不在SQL的标准中
标准的写法：select * from emp e inner join dept d on e.deptno = d.deptno; inner可以省略
（1） Inner 可以省略不写
（2） 内连接特点：查询的条件必须满足条件 不满足条件的数据将会丢失。
部门 是40的数据就没有了，员工emp中没有40这个部门的人。
11.5.2外连接
A left join B ：A左连接 B表；A表是主表 A right join B：A右连接B表；B表是主表
B left join A：B左连接A表； B表是主表 B right join A：B右连接A表；A表是主表
特点：主表的数据全部都在，
然后去匹配 非主表的 数据 匹配补上NULL

# 员工表 emp是主表，去匹配dept
Select * from emp e left join dept d on e.depton= d.depton;
##部门表是主表，然后去匹配员工表
Select * from emp e right join dept  d on e.depton=d.depton;

 
 

 
 
1
2
3
4

11.5.3自然连接
自然连接：是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。

基本这些入门，勤加练习，需要勤能补拙