目录
- 多进程实现TCP服务端并发
- 互斥锁代码实操
- 线程理论
- 创建线程的两种方式
- 线程的诸多特性
- GIL全局解释器锁
- 验证GIL的存在
- GIL与普通互斥锁
- python多线程是否有用
- 死锁现象
- 信号量
- event事件
- 进程池与线程池
- 协程
- 协程实现并发
- 存取数据的演变史
- 数据库软件应用史
- 数据库的本质
- 数据库的分类
- MySQL简介
- MySQL基本使用
- 系统服务的制作
- 密码相关操作
- SQL与NoSQL
- 数据库重要概念
- 基本SQL语句
- 字符编码与配置文件
- 数据库存储引擎
- 创建表的完整语法
- 字段类型之整型
- 严格模式
- 字段类型之浮点型
- 字段类型之字符类型
- 数字的含义
- 字段类型之枚举与集合
- 字段类型之日期类型
- 无符号、零填充
- 非空
- 默认值
- 唯一值
- 主键
- 自增
- 外键前戏
- 关系的判断
- 一对多关系
- 外键字段的建立
- 多对多关系
- 一对一关系
- 报错及作业讲解
- SQL语句查询关键字
- 前期数据准备
- 编写SQL语句的小技巧
- 查询关键字之where筛选
- 查询关键字之group by分组
- 查询关键字之having过滤
- 查询关键字之distinct去重
- 查询关键字之order by排序
- 查询关键字之limit分页
- 查询关键字之regexp正则表达式
- 多表查询的思路
多进程实现TCP服务端并发
import socket
from multiprocessing import Process
def get_server():
server = socket.socket()
server.bind(('127.0.0.1', 8080))
server.listen(5)
return server
def get_talk(sock):
while True:
data = sock.recv(1024)
print(data.decode('utf8'))
sock.send(data.upper())
if __name__ == '__main__':
server = get_server()
while True:
sock, addr = server.accept()
# 开设多进程去聊天
p = Process(target=get_talk, args=(sock,))
p.start()
互斥锁代码实操
锁:建议只加载操作数据的部分 否则整个程序的效率会极低
from multiprocessing import Process, Lock
import time
import json
import random
def search(name):
with open(r'data.json', 'r', encoding='utf8') as f:
data = json.load(f)
print('%s查看票 目前剩余:%s' % (name, data.get('ticket_num')))
def buy(name):
# 先查询票数
with open(r'data.json', 'r', encoding='utf8') as f:
data = json.load(f)
# 模拟网络延迟
time.sleep(random.randint(1, 3))
# 买票
if data.get('ticket_num') > 0:
with open(r'data.json', 'w', encoding='utf8') as f:
data['ticket_num'] -= 1
json.dump(data, f)
print('%s 买票成功' % name)
else:
print('%s 买票失败 非常可怜 没车回去了!!!' % name)
def run(name, mutex):
search(name)
mutex.acquire() # 抢锁
buy(name)
mutex.release() # 释放锁
if __name__ == '__main__':
mutex = Lock() # 产生一把锁
for i in range(10):
p = Process(target=run, args=('用户%s号' % i, mutex))
p.start()
"""
锁有很多种 但是作用都一样
行锁 表锁 ...
"""
线程理论
进程
进程其实是资源单位 表示一块内存空间
线程
线程才是执行单位 表示真正的代码指令
我们可以将进程比喻是车间 线程是车间里面的流水线
一个进程内部至少含有一个线程
1.一个进程内可以开设多个线程
2.同一个进程下的多个线程数据是共享的
3.创建进程与线程的区别
创建进程的消耗要远远大于线程
创建线程的两种方式
from threading import Thread
import time
def task(name):
print(f'{name} is running')
time.sleep(0.1)
print(f'{name} is over')
t = Thread(target=task, args=('jason',))
t.start()
print('主线程')
# 第二种方式
class MyThread(Thread):
def run(self):
print('run is running')
time.sleep(1)
print('run is over')
obj = MyThread()
obj.start()
print('主线程')
线程的诸多特性
# 线程join方法
from threading import Thread
import time
def task(name):
print(f'{name} is running')
time.sleep(3)
print(f'{name} is over')
t = Thread(target=task, args=('jason',))
t.start()
t.join() # 主线程代码等待子线程代码运行完毕之后再往下执行
print('主线程')
"""
主线程为什么要等着子线程结束才会结束整个进程
因为主线程结束也就标志着整个进程的结束 要确保子线程运行过程中所需的各项资源
"""
# 同进程内多个线程数据共享
from threading import Thread
money = 100
def task():
global money
money = 1
t = Thread(target=task)
t.start()
t.join()
print(money)
# 思考：线程更改进程内数据，数据也会被更改
# 1
线程对象属性和方法
1、同一进程下多个线程的进程号一致
2、如何统计进程下活跃的线程数
active_count()
3、获取线程的名字
1.current_thread().name
MainThread # 主线程
Thread-1、Thread-2 # 子线程
2.self.name # 类对象获取线程名
GIL全局解释器锁
# 官方文档对GIL的解释
In CPython, the global interpreter lock, or GIL, is a mutex that prevents multiple native threads from executing Python bytecodes at once. This lock is necessary mainly because CPython’s memory management is not thread-safe. (However, since the GIL exists, other features have grown to depend on the guarantees that it enforces.
"""
1.在CPython解释器中存在全局解释器锁简称GIL
python解释器有很多类型
CPython JPython PyPython (常用的是CPython解释器)
2.GIL本质也是一把互斥锁 用来阻止同一个进程内多个线程同时执行(重要)
3.GIL的存在是因为CPython解释器中内存管理不是线程安全的(垃圾回收机制)
垃圾回收机制
引用计数、标记清除、分代回收
"""
验证GIL的存在
from threading import Thread
num = 100
def task():
global num
num -= 1
t_list = []
for i in range(100):
t = Thread(target=task)
t.start()
t_list.append(t)
for t in t_list:
t.join()
print(num)
GIL与普通互斥锁
既然CPython解释器中有GIL 那么我们以后写代码是不是就不需要操作锁了!!!
"""
GIL只能够确保同进程内多线程数据不会被垃圾回收机制弄乱
并不能确保程序里面的数据是否安全
"""
import time
from threading import Thread,Lock
num = 100
def task(mutex):
global num
mutex.acquire()
count = num
time.sleep(0.1)
num = count - 1
mutex.release()
mutex = Lock()
t_list = []
for i in range(100):
t = Thread(target=task,args=(mutex,))
t.start()
t_list.append(t)
for t in t_list:
t.join()
print(num)
注意：如果这里没加互斥锁mutex的时候，结果为99，为什么？
分析：因为如果没有互斥锁保证它独立运行完再运行下一个的话，每个线程获取到的money都是100，tmp-1都是99，那么最终结果也就是99
"""
GIL是一个纯理论知识 在实际工作中根本无需考虑它的存在
GIL作用面很窄 仅限于解释器级别
后期我们要想保证数据的安全应该自定义互斥锁(使用别人封装好的工具)
"""
python多线程是否有用
需要分情况
情况1
单个CPU
多个CPU
情况2
IO密集型(代码有IO操作)
计算密集型(代码没有IO)
1.单个CPU
IO密集型
多进程
申请额外的空间 消耗更多的资源
多线程
消耗资源相对较少 通过多道技术
ps:多线程有优势!!!
计算密集型
多进程
申请额外的空间 消耗更多的资源(总耗时+申请空间+拷贝代码+切换)
多线程
消耗资源相对较少 通过多道技术(总耗时+切换)
ps:多线程有优势!!!
2.多个CPU
IO密集型
多进程
总耗时(单个进程的耗时+IO+申请空间+拷贝代码)
多线程
总耗时(单个进程的耗时+IO)
ps:多线程有优势!!!
计算密集型
多进程
总耗时(单个进程的耗时)
多线程
总耗时(多个进程的综合)
ps:多进程完胜!!!
from threading import Thread
from multiprocessing import Process
import os
import time
def work():
# 计算密集型
res = 1
for i in range(1, 100000):
res *= i
if __name__ == '__main__':
# print(os.cpu_count()) # 12 查看当前计算机CPU个数
start_time = time.time()
# p_list = []
# for i in range(12): # 一次性创建12个进程
# p = Process(target=work)
# p.start()
# p_list.append(p)
# for p in p_list: # 确保所有的进程全部运行完毕
# p.join()
t_list = []
for i in range(12):
t = Thread(target=work)
t.start()
t_list.append(t)
for t in t_list:
t.join()
print('总耗时:%s' % (time.time() - start_time)) # 获取总的耗时
"""
计算密集型
多进程:5.665567398071289
多线程:30.233906745910645
"""
def work():
time.sleep(2) # 模拟纯IO操作
if __name__ == '__main__':
start_time = time.time()
# t_list = []
# for i in range(100):
# t = Thread(target=work)
# t.start()
# for t in t_list:
# t.join()
p_list = []
for i in range(100):
p = Process(target=work)
p.start()
for p in p_list:
p.join()
print('总耗时:%s' % (time.time() - start_time))
"""
IO密集型
多线程:0.0149583816528320
多进程:0.6402878761291504
"""
死锁现象
from threading import Thread, Lock
import time
mutexA = Lock()
mutexB = Lock()
class MyThread(Thread):
def run(self):
self.f1()
self.f2()
def f1(self):
mutexA.acquire() # 抢A锁
print(f'{self.name}抢到了A锁')
mutexB.acquire() # 抢B锁
print(f'{self.name}抢到了B锁')
mutexB.release() # 放B锁
mutexA.release() # 放A锁
def f2(self):
mutexB.acquire() # 抢B锁
print(f'{self.name}抢到了B锁')
time.sleep(2)
mutexA.acquire() # 抢A锁
print(f'{self.name}抢到了A锁')
mutexA.release() # 放A锁
mutexB.release() # 放B锁
for i in range(10): # 创建10个线程
t = MyThread()
t.start()
# 结果：
Thread-1抢到了A锁
Thread-1抢到了B锁
Thread-1抢到了B锁
Thread-2抢到了A锁
# 然后就产生阻塞现象了，因为最后线程2抢到A锁然后取抢B锁时，B锁还在线程1手里，然而线程1下面也要抢A锁，两者都进入阻塞
结论：
锁不能轻易使用并且以后我们也不会在自己去处理锁都是用别人封装的工具
ps：锁就算掌握了如何抢 如何放 也会产生死锁现象
信号量
信号量在不同知识体系中，展现出来的功能时不一样的，
eg：
在并发编程中：信号量是把互斥锁
在djando框架中：信号量时达到某条件自动触发特定功能
在python并发编程中信号量相当于多把互斥锁(公共厕所)
"""
如果将自定义互斥锁比喻成是单个厕所(一个坑位)
那么信号量相当于是公共厕所(多个坑位)
"""
from threading import Thread, Lock, Semaphore
import time
import random
sp = Semaphore(5) # 一次性产生五把锁
class MyThread(Thread):
def run(self):
sp.acquire()
print(self.name)
time.sleep(random.randint(1, 3))
sp.release()
for i in range(20):
t = MyThread()
t.start()
分析：上面代码创建带有五个位置的信号量，然后创建30个线程，会发现，代码结果会直接先运行5个线程，然后陆续运行接下来的线程，说明，信号量是设置好的5个位置，前面的位置腾出来后后面的线程再进去，以此类推
event事件
作用：子线程的运行可以由其他子线程决定或者干涉
Event是线程间通信的机制之一：一个线程发送一个event信号，其他的线程则等待这个信号。常用在一个线程需要根据另外一个线程的状态来确定自己的下一步操作的情况。
Event原理：事件对象管理一个内部标志，通过set()方法将其设置为True，并使用clear()方法将其设置为False。wait()方法阻塞，直到标志为True。该标志初始为False。
from threading import Thread, Event
import time
event = Event() # 类似于造了一个红绿灯
def light():
print('红灯亮，原地等待')
time.sleep(3)
print('绿灯行，冲过马路')
event.set() # True
def car(name):
print('%s正在等红灯' % name)
event.wait() # 阻塞，等待为True时退出阻塞
print('%s加油门，开始飙车' % name)
t = Thread(target=light) # 创建1个线程light，模拟红绿灯
t.start()
for i in range(10): # 创建10个线程car，模拟汽车司机
t = Thread(target=car, args=('汽车司机%s' % i,))
t.start()
# 结果：
红灯亮，原地等待
汽车司机0正在等红灯
... # 省略
汽车司机9正在等红灯
绿灯行，冲过马路
汽车司机1加油门，开始飙车
... # 省略
汽车司机7加油门，开始飙车
# 拓展：
event.clear() # 将事件设置为False
is_set() # 当且仅当内部标志为True时返回True
可以看出，wait（）造成阻塞，等待set（）执行，然后结束阻塞
进程池与线程池
引入:
服务端必备的三要素
1.24小时不间断提供服务
2.固定的ip和port
3.支持高并发
回顾:
TCP服务端实现并发
多进程:来一个客户端就开一个进程(临时工)
多线程:来一个客户端就开一个线程(临时工)
问题:
计算机硬件是有物理极限的，我们不可能无限制的创建进程和线程
措施:
池:
保证计算机硬件安全的情况下提升程序的运行效率
进程池:
提前创建好固定数量的进程，后续反复使用这些进程(合同工)
线程池:
提前创建好固定数量的线程，后续反复使用这些线程(合同工)
强调:
如果任务超出了池子里面的最大进程或线程数，则原地等待
进程池和线程池其实降低了程序的运行效率，但是保证了硬件的安全!!!
from concurrent.futures import ProcessPoolExecutor, ThreadPoolExecytor
# 线程池
pool = ThreadPoolExecutor(5) # 线程池线程数默认时CPU个数的5倍，也可以自定义，这个代码执行之后就会立刻创建5个等待工作的线程
def task(n):
time.sleep(2)
print(n)
return '任务的执行结果:%s'%n**2
def func(*args, **kwargs):
# print(args, kwargs)
print(args[0].result())
for i in range(20):
# res = pool.submit(task, i) # 朝线程池中提交任务(异步)及参数
# print(res.result()) # 同步提交(获取任务的返回值)
'''这样效率太慢，不应该自己主动等待结果 应该让异步提交自动提醒>>>:异步回调机制'''
# 异步回调机制：异步提交自动提醒add_done_callback(func)
pool.submint(task, i).add_done_callback(func)
"""add_done_callback只要任务task有结果了,就会自动调用括号内的函数func处理，把task任务当作参数传入func"""
# 进程池
pool = ProcessPoolExecutor(5) # 进程池进程数默认是CPU个数 也可以自定义
'''上面的代码执行之后就会立刻创建五个等待工作的进程'''
pool.submit(task, i).add_done_callback(func)
协程
"""
进程:资源单位
线程:执行单位
协程:单线程下实现并发(效率极高)
在代码层面欺骗CPU 让CPU觉得我们的代码里面没有IO操作
实际上IO操作被我们自己写的代码检测 一旦有 立刻让代码执行别的
(该技术完全是程序员自己弄出来的 名字也是程序员自己起的)
核心:自己写代码完成切换+保存状态
"""
import time
from gevent import monkey;
monkey.patch_all() # 固定编写 用于检测所有的IO操作(猴子补丁)
from gevent import spawn
def func1():
print('func1 running')
time.sleep(3)
print('func1 over')
def func2():
print('func2 running')
time.sleep(5)
print('func2 over')
if __name__ == '__main__':
start_time = time.time()
# func1()
# func2()
s1 = spawn(func1) # 检测代码 一旦有IO自动切换(执行没有io的操作 变向的等待io结束)
s2 = spawn(func2)
s1.join()
s2.join()
print(time.time() - start_time) # 8.01237154006958 协程 5.015487432479858
协程实现并发
import socket
from gevent import monkey;monkey.patch_all() # 固定编写 用于检测所有的IO操作(猴子补丁)
from gevent import spawn
def communication(sock):
while True:
data = sock.recv(1024)
print(data.decode('utf8'))
sock.send(data.upper())
def get_server():
server = socket.socket()
server.bind(('127.0.0.1', 8080))
server.listen(5)
while True:
sock, addr = server.accept() # IO操作
spawn(communication, sock)
s1 = spawn(get_server)
s1.join()
如何不断的提升程序的运行效率
多进程下开多线程 多线程下开协程
存取数据的演变史
1.文本文件
文件路径不固定:C:\aaa.txt D:\bbb.txt E:\ccc.txt
数据格式不统一:jason|123 jason$123 jason 123
2.软件开发目录规范
规定了数据应该保存在db目录下>>>:路径偏向统一
db/user.txt db/userinfo.txt db/jason.json db/jason
数据格式还是没有得到统一
文本 json格式 对象
3.数据库服务(重点)
统一路径 统一操作方式
降低学习成本 提高开发效率
数据库软件应用史
1.单机游戏
数据存储于各个计算机的本地 无法共享
2.网络游戏
数据存储于网络中 可以共享(数据库服务)
"""
数据库服务集群:提升数据的安全性
"""
数据库的本质
1.站在底层原理的角度
数据库指的是操作数据的进程(一堆代码)
2.站在实际应用的角度
数据库指的是可视化操作界面(一些软件)
ps:以后不做特殊说明的情况下讲数据库其实指的是数据库软件
数据库软件本质也是CS架构的程序
意味着所有的程序员其实都有资格编写一款数据库软件
数据库的分类
1.关系型数据库
特征1:拥有固定的表结构(字段名 字段类型)
id name pwd
特征2:数据之间可以建立数据库层面关系
用户表数据
豪车表数据
豪宅表数据
MySQL、Oracle、MariaDB、PostgreSQL、sql server、sqlite、db2、access
1.MySQL:开源免费 使用最广 性价比贼高
2.Oracle:收费 使用成本较高但是安全性也最高
3.PostgreSQL:开源免费 支持二次开发 兼容性极高
4.MariaDB:跟MySQL是一个作者 开源免费
5.sqlite:小型数据库 主要用于本地测试
2.非关系型数据库
特征1:没有固定的表结构 数据存储采用K:V键值对的形式
{'name':'jason'}
{'username':'kevin','pwd':123}
特征2:数据之间无法建立数据库层面的关系
可以自己编写代码建立逻辑层面的关系
redis、mongoDB、memcache
1.redis:目前最火 使用频率最高的非关系型数据库(缓存数据库)
虽然缓存数据库是基于内存做数据存取但是拥有持久化的功能
2.mongoDB:文档型数据库 最像关系型数据库的非关系型数据库
主要用在爬虫以及大数据领域
3.memcache:以及被redis淘汰
"""
虽然数据库软件有很多 但是操作方式大差不差 学会了一个几乎就可以学会所有
其中以MySQL最为典型
"""
MySQL简介
1.版本问题
8.0:最新版
5.7:使用频率较高
5.6:学习推荐使用
ps:站在开发的角度使用哪个版本学习都没有关系
2.下载流程
1.访问官网
2.点击DOWNLOADS并点击GPL
3.点击community server
4.点击Archives
5.选择对应系统的对应版本下载即可(zip压缩包)
3.主要目录介绍
bin目录
存放启动文件
mysqld.exe(服务端) mysql.exe(客户端)
data目录
存放核心数据
my-default.ini
默认的配置文件
readme
软件说明
MySQL基本使用
cmd建议你使用管理员身份打开
1.切换到mysql的bin目录下先启动服务端
mysqld
2.保持窗口不关闭 重新打开一个新的cmd窗口
3.切换到mysql的bin目录下启动客户端
mysql
"""
直接使用mysql命令默认是游客模式 权限和功能都很少
mysql -u用户名 -p密码
管理员默认没有密码 连续回车即可
mysql -uroot -p
"""
'''
有些同学的电脑在启动服务端的时候就会报错 不要慌
拷贝报错信息 然后百度搜索
mysql启动报错粘贴错误信息
'''
系统服务的制作
1.先把bin目录添加到环境变量
清空之前打开的cmd窗口 一定要把之前用cmd启动的服务端关闭(ctrl+c)
2.将mysql添加到系统服务中
1.如何查看系统服务
鼠标右键任务栏选择服务
cmd输入services.msc回车
2.以管理员身份打开cmd窗口
mysqld --install
3.首次添加不会自动启动 需要人为操作一下
1.鼠标右键点击启动
2.命令行启动
net start mysql
"""
如果想卸载重新按照
1.先关闭服务端
net stop mysql
2.移除系统服务
mysqld --remove
"""
密码相关操作
1.修改密码
方式1:mysqladmin
mysqladmin -u用户名 -p原密码 password 新密码
方式2:直接修改存储用户数据的表
方式3:冷门操作 有些版本可能还不支持
set password=password('新密码') # 修改当前登录用户的密码
2.忘记密码
方式1:卸载重新装
方式2:把data目录删除 拷贝同桌的目录
方式3:小把戏操作
1.关闭正常的服务端
2.以跳过授权表的方式重启服务端(不校验密码)
3.以管理员身份进入然后修改mysql.user表数据即可
net stop mysql
mysqld --skip-grant-table
mysql -uroot -p
update mysql.user set password=password('123') where Host='localhost' and User='root';
4.关闭服务端 然后以正常方式启动即可
SQL与NoSQL
数据库服务端是可以服务多种类型的客户端
客户端可以是自己开发的 也可以是python代码编写 也可以是java代码编写
SQL
操作关系型数据库的语言
NoSQL
操作非关系型数据库的语言
ps:要想跟数据库交互就必须使用数据库指定的语言
"""
SQL有时候也指代关系型数据库
NoSQL有时候也指代非关系型数据库
"""
数据库重要概念
"""
强调:小白阶段为了更加方便的理解 做了以下比喻 本质其实有一点点的区别
"""
库 就相当于是 文件夹
表 就相当于是 文件夹里面的文件
记录 就相当于是 文件夹里面的文件中的一行行数据
验证
1.查看所有的库名称
show databases;
2.查看所有的表名称
show tables;
3.查看所有的记录
select * from mysql.user;
基本SQL语句
1.sql语句必须以分号结尾
2.sql语句编写错误之后不用担心 可以直接执行报错即可
'''基于库的增删改查'''
1.创建库
create database 库名;
2.查看库
show databases; 查看所有的库名称
show create database 库名; 查看指定库信息
3.编辑库
alter database 库名 charset='utf8';
4.删除库
drop database 库名;
'''基于表的增删改查'''
操作表之前需要先确定库
create database db1;
切换操作库
use db1;
1.创建表
create table 表名(字段名 字段类型,字段名 字段类型);
2.查看表
show tables; 查看库下所有的表名称
show create table 表名; 查看指定表信息
describe 表名; 查看表结构
desc 表名;
ps:如果想跨库操作其他表 只需要在表名前加库名即可
desc mysql.user;
3.编辑表
alter table 表名 rename 新表名;
4.删除表
drop table 表名;
'''基于记录的增删改查'''
1.插入数据
insert into 表名 values(数据值1,数据值2);
2.查询数据
select * from 表名; 查询表中所有的数据
3.编辑数据
update 表名 set 字段名=新数据 where 筛选条件;
4.删除数据
delete from 表名;
delete from 表名 where id=2;
字符编码与配置文件
1.\s查看MySQL相关信息
当前用户、版本、编码、端口号
MySQL5.6及之前的版本编码需要人为统一 之后的版本已经全部默认统一
如果想要永久修改编码配置 需要操作配置文件
2.默认的配置文件是my-default.ini
拷贝上述文件并重命名为my.ini
直接拷贝字符编码相关配置即可无需记忆
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
ps:
1.utf8mb4能够存储表情 功能更强大
2.utf8与utf-8是有区别的 MySQL中只有utf8
修改了配置文件中关于[mysqld]的配置 需要重启服务端
"""
利用配置文件我们可以偷懒
将管理员登录的账号密码直接写在配置文件中 之后使用mysql登录即可
[mysql]
user='root'
password=123
"""
数据库存储引擎
存储引擎
数据库针对数据采取的多种存取方式
查看常见存储引擎的方式
show engines;
需要了解的四个存储引擎
MyISAM
MySQL5.5之前默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低
InnoDB
MySQL5.5之后默认的存储引擎
支持事务、行锁、外键等操作 存取速度没有MyISAM快 但是安全性更高
Memory
基于内存存取数据 仅用于临时表数据存取
BlackHole
任何写入进去的数据都会立刻丢失
了解不同存储引擎底层文件个数
create database db2;
use db2;
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
'''
1.innodb两个文件
.frm 表结构
.ibd 表数据(表索引)
2.myisam三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory一个文件
.frm 表结构
4.blackhole一个文件
.frm 表结构
'''
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
ps:MySQL默认忽略大小写
创建表的完整语法
create table 表名(
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个 空格隔开即可
4.最后一行结尾不能加逗号
ps:编写SQL语句报错之后不要慌 仔细查看提示 会很快解决
near ')' at line 7
字段类型之整型
tinyint 1bytes 正负号(占1bit)
smallint 2bytes 正负号(占1bit)
int 4bytes 正负号(占1bit)
bigint 8bytes 正负号(占1bit)
验证整型默认是否携带正负号
create table t5(id tinyint);
insert into t5 values(-129),(128);
结果是-128和127 也就意味着默认自带正负号
我们也可以取消正负号
create table t6(id tinyint unsigned);
insert into t6 values(-129),(128),(1000);
严格模式
当我们在使用数据库存储数据的时候 如果数据不符合规范 应该直接报错而不是擅自修改数据 这样会导致数据的失真(没有实际意义)
正常都应该报错 但是我们之前不小心改了配置文件
show variables like '%mode%';
1.临时修改
set session sql_mode='strict_trans_tables';
在当前客户端有效
set global sql_mode='strict_trans_tables';
在当前服务端有效
2.永久修改
直接修改配置文件
字段类型之浮点型
float(20,10)
总共存储20位数 小数点后面占10
double(20,10)
总共存储20位数 小数点后面占10
decimal(20,10)
总共存储20位数 小数点后面占10
create table t7(id float(60,20));
create table t8(id double(60,20));
create table t9(id decimal(60,20));
insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);
三者的核心区别在于精确度不同
float < double < decimal
字段类型之字符类型
char
定长
char(4) 最多存储四个字符 超出就报错 不够四个空格填充至四个
varchar
变长
varchar(4) 最多存储四个字符 超出就报错 不够则有几位存几位
create table t10(id int, name char(4));
create table t11(id int, name varchar(4));
insert into t10 values(1, 'jason1');
insert into t11 values(1, 'jason2');
ps:char_length()获取字段存储的数据长度
默认情况下MySQL针对char的存储会自动填充空格和删除空格
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
char VS varchar
char
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的速度较char慢
jacktonyjasonkevintomjerry
1bytes+jack1bytes+tony1bytes+jason1bytes+kevin1bytes+tom1bytes+jerry
"""
char与varchar的使用需要结合具体应用场景
"""
数字的含义
数字在很多地方都是用来表示限制存储数据的长度
但是在整型中数字却不是用来限制存储长度
create table t12(id int(3)); 不是用来限制长度
insert into t12 values(12345);
create table t13(id int(5) zerofill); 而是用来控制展示的长度
insert into t13 values(123),(123456789);
create table t14(id int);
"""以后写整型无需添加数字"""
字段类型之枚举与集合
枚举
多选一
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into t15 values(1,'tony','猛男');
insert into t15 values(2,'jason','male');
insert into t15 values(3,'kevin','others');
集合
多选多(多选一)
create table t16(
id int,
name varchar(16),
hobbies set('basketabll','football','doublecolorball')
);
insert into t16 values(1,'jason','study');
insert into t16 values(2,'tony','doublecolorball');
insert into t16 values(3,'kevin','doublecolorball,football');
字段类型之日期类型
datetime 年月日时分秒
date 年月日
time 时分秒
year 年
create table t17(
id int,
name varchar(32),
register_time datetime,
birthday date,
study_time time,
work_time year
);
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');
ps:以后涉及到日期相关字段一般都是系统自动回去 无需我们可以操作
无符号、零填充
'''关键字unsigned 无需正负号'''
create table t(id int unsigned); # 无符号的整型，即必须是正整数
'''关键字zerofill'''
create table t(id int(3) zerofill); # 不够展示3位的用0填充
非空
create table t1(
id int,
name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
ps:所有字段类型不加约束条件的情况下默认都可以为空
'''关键字not null'''
create table t2(
id int,
name varchar(16) not null # name为非空字段
);
insert into t2(id) values(1); # 报错，因为name字段未添加值
insert into t2(name) values('jason');
insert into t2 values(1,''); # 不报错！空字符串可以
insert into t2 values(2,null); # 报错，null也属于空
默认值
'''关键字default'''
create table t3(
id int default 666,
name varchar(16) default '匿名'
);
insert into t3(id) values(1); # 不传name,则默认为匿名
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');
唯一值
'''关键字unique'''
'''单列唯一:某个字段下对应的数据不能重复 是唯一的'''
create table t4(
id int unique,
name varchar(32) unique # name字段设置不能重复
);
insert into t4 values(1,'jason'),(2,'jason');
'''联合唯一'''
create table t5(
id int,
ip varchar(32),
port int,
unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080); # 报错
主键
'''关键字primary key'''
1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
create table t6(
id int primary key,
name varchar(32)
);
insert into t6(name) values('jason');
insert into t6 values(1,'kevin');
insert into t6 values(1,'jerry');
2..InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
1.当表中没有主键也没有其他非空切唯一的字段的情况下
InnoDB会采用一个隐藏的字段作为表的主键 隐藏意味着无法使用 基于该表的数据查询只能一行行查找 速度很慢
2.当表中没有主键但是有其他非空且唯一的字段 那么会从上往下将第一个该字段自动升级为主键
create table t7(
id int,
age int not null unique,
phone bigint not null unique,
birth int not null unique,
height int not null unique
);
"""
我们在创建表的时候应该有一个字段用来标识数据的唯一性 并且该字段通常情况下就是'id'(编号)字段
id nid sid pid gid uid
create table userinfo(
uid int primary key,
);
"""
自增
'''关键字auto_increment'''
该约束条件不能单独出现 并且一张表中只能出现一次 主要就是配合主键一起
create table t8(
id int primary key,
name varchar(32)
);
create table t9(
id int primary key auto_increment,
name varchar(32)
);
insert into t9(name) values('kevin'); # 无需传id 会自增
"""
自增特性
自增不会因为数据的删除而回退 永远自增往前
如果自己设置了更大的数 则之后按照更大的往前自增
如果想重置某张表的主键值 可以使用
truncate t9; 清空表数据并重置主键
"""
外键前戏
我们需要一张员工表
id name age dep_name dep_desc
1.表语义不明确(到底是员工还是部门) 无所谓
2.存取数据过于冗余(浪费存储空间) 无所谓
3.数据的扩展性极差 不能忽略
将上述表一分为二
id name age
id dep_name dep_desc
上述的三个问题全部解决 但是员工跟部门之间没有了关系
外键字段:用于标识数据与数据之间关系的字段
关系的判断
表关系、数据关系其实意思是一样的 知识说法上有区分
关系总共有四种
一对多
多对多
一对一
没有关系
关系的判断可以采用'换位思考'原则
一对多关系
以员工表和部门表为例
1.先站在员工表的角度
问:一名员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多名员工
答:可以
结论:一个可以一个不可以 那么关系就是'一对多'
针对'一对多'关系 外键字段建在'多'的一方
外键字段的建立
小技巧:先定义出含有普通字段的表 之后再考虑外键字段的添加
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除
针对3有简化措施>>>:级联更新级联删除
create table emp1(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
"""
外键其实是强耦合 不符合解耦合的特性
所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系
"""
多对多关系
以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以
结论:两个都可以 关系就是'多对多'
针对'多对多'不能在表中直接创建 需要新建第三张关系表
create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint
);
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
一对一关系
以用户表与用户详情表为例
1.先站在用户表的角度
问:一个用户能否对应多个用户详情
答:不可以
2.再站在用户详情表的角度
问:一个用户详情能否对应多个用户
答:不可以
结论:两个都可以 关系就是'一对一'或者没有关系
针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
因为是约束唯一要加上unique
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userdetail(id)
on update cascade
on detele cascade
);
create table userdetail(
id int primary key auto_increment,
phone bigint
);
报错及作业讲解
报错
1.粗心大意 单词拼写错误
2.手忙脚乱 不会看报错思考错误的核心
作业讲解
'''表与表中数据的关系可能会根据业务逻辑的不同发送改变 不是永远固定的'''
服务器表与应用程序表
一台服务器可以运行多个应用程序
一个应用程序也可以运行在多台服务器上
多对多
课程表与班级表
一门课程可以属于多个班级
一个班级可以拥有多门课程
多对多
学生表与班级表
一名学生不能属于多个班级
一个班级可以拥有多名学生
一对多
老师表与课程表
一名老师可以教授多门课程
一门课程可以被多名老师教
多对多
书籍表与出版社表
一本书不可以多个出版社出版
一个出版社可以出版多本书
一对多
ps:上述关系的确认并不是绝对
对于我们而言不单单要学会关系判断还要非常熟练的敲出SQL语句
SQL语句查询关键字
'''关键字select'''
select
指定需要查询的字段信息
select * 查所有字段
select name 查name字段
select char_length(name) 支持对字段做处理
from
指定需要查询的表信息
from mysql.user
from t1
SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
eg:
select id,name from userinfo;
我们先写的select在写的from 但是执行的时候是先执行的from在执行select
对应关键字的编写顺序和执行顺序我们没必要过多的在意 熟练之后会非常自然的编写
我们只需要把注意力放在每个关键字的功能上即可
前期数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
编写SQL语句的小技巧
针对select后面的字段名可以先用*占位往后写 最后再回来修改
在实际应用中select后面很少直接写* 因为*表示所有 当表中字段和数据都特别多的情况下非常浪费数据库资源
"""
SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补
"""
查询关键字之where筛选
# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6; 支持逻辑运算符
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000); 支持成员运算
# 3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
# 4.查询员工姓名中包含字母o的员工姓名与薪资
条件不够精确的查询 称之为 模糊查询
模糊查询的关键字是
like
模糊查询的常用符号
%:匹配任意个数的任意字符
eg:
%o% o jason owen loo wwoww
%o o asdasdo asdo
_:匹配单个个数的任意字符
_o_ aox wob iok
o_ oi ok ol
select * from emp where name like '%o%';
# 5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select * from emp where char_length(name) = 4;
# 6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select * from emp where post_comment=NULL; 不可以
select * from emp where post_comment is NULL; 可以
"""
在MySQL中也有很多内置方法 我们可以通过查看帮助手册学习
help 方法名
"""
查询关键字之group by分组
分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
将班级学生按照性别分组
将全国人民按照民族分组
将全世界的人按照肤色分组
分组的目的是为了更好的统计相关数据
eg:
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资
聚合函数
专门用于分组之后的数据统计
max\min\sum\avg\count
最大值、最小值、求和、平均值、计数
1.将员工数据按照部门分组
select * from emp group by post;
"""
MySQL5.6默认不会报错
set global sql_mode='strict_trans_tables,only_full_group_by'
MySQL5.7及8.0默认都会直接报错
原因是分组之后 select后面默认只能直接填写分组的依据 不能再写其他字段
select post from emp group by post;
select age from emp group by age;
分组之后默认的最小单位就应该是组 而不应该再是组内的单个数据单个字段
"""
2.获取每个部门的最高工资
'''要不要分组我们完全可以从题目的需求中分析出来尤其是出现关键字 每个 平均'''
select post,max(salary) from emp group by post;
针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;
3.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
4.统计每个部门的人数
select post,count(id) from emp group by post;
5.统计每个部门的部门名称以及部门下的员工姓名
'''分组以外的字段无法直接填写 需要借助于方法'''
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;
查询关键字之having过滤
having与where本质是一样的 都是用来对数据做筛选
只不过where用在分组之前(首次筛选)
having用在分组之后(二次筛选)
1.统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''
稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
查询关键字之distinct去重
去重有一个必须的条件也是很容易被忽略的条件
数据必须一模一样才可以去重
select distinct id,age from emp; 关键字针对的是多个字段组合的结果
select distinct age from emp;
select distinct age,post from emp;
查询关键字之order by排序
1.可以是单个字段排序
select * from emp order by age; 默认升序
select * from emp order by age asc; 默认升序(asc可以省略)
select * from emp order by age desc; 默认升序
2.也可以是多个字段排序
select * from emp order by age,salary desc; 先按照年龄升序排 相同的情况下再按照薪资降序排
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
1.先筛选出所有年龄大于10岁的员工
select * from emp where age > 10;
2.再对他们按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""
查询关键字之limit分页
当表中数据特别多的情况下 我们很少会一次性获取所有的数据
很多网站也是做了分页处理 一次性只能看一点点
select * from emp limit 5; 直接限制展示的条数
select * from emp limit 5,5; 从第5条开始往后读取5条
查询工资最高的人的详细信息
'''千万不要关系思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;
查询关键字之regexp正则表达式
SQL语句的模糊匹配如果用不习惯 也可以自己写正则批量查询
select * from emp where name regexp '^j.*?(n|y)$';
多表查询的思路
表数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
select * from emp,dep; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据