yuhehanfeng

yuhehanfeng 查看完整档案

泰安编辑  |  填写毕业院校  |  填写所在公司/组织填写个人主网站
编辑
_ | |__ _ _ __ _ | '_ \| | | |/ _` | | |_) | |_| | (_| | |_.__/ \__,_|\__, | |___/ 该用户太懒什么也没留下

个人动态

yuhehanfeng 提出了问题 · 11月23日

egg-sequelize中的query方法,传参时,用问号替换的变量为什么会带引号?导致查询不正确

eggjs中查询数据库中的user表中的字段,字段动态传入,现在传入'id'。

app.model.query('select ? from user', {
  replacements: ['id'],
  type: 'SELECT'
})

但是生成的sql语句是下面这样,多出了引号,

select 'id' from invoice

我想要的是:

select id from invoice

这是为什么呢?请问引号怎么去掉

关注 1 回答 0

yuhehanfeng 提出了问题 · 11月23日

egg-sequelize中的query方法,传参时,用问号替换的变量为什么会带引号?导致查询不正确

eggjs中查询数据库中的user表中的字段,字段动态传入,现在传入'id'。

app.model.query('select ? from user', {
  replacements: ['id'],
  type: 'SELECT'
})

但是生成的sql语句是下面这样,多出了引号,

select 'id' from invoice

我想要的是:

select id from invoice

这是为什么呢?请问引号怎么去掉

关注 1 回答 0

yuhehanfeng 赞了回答 · 8月3日

解决vue axios暂存多个请求,交换完token后,再依次执行请求,但是成功的回调找不到了?

  1. 基于以下两点:

    1. Promise 实例的状态只能改变一次
    2. Promise 一经创建,内部函数就开始执行
  2. 你不能先发起请求,因为无论成功与否,后面的 then 都不会挂起等待执行
  3. 你只能把请求以某种形式存储起来,先检查 token,如果可以,再依次发起请求。也就是说,所有请求先入队列,而不是有问题的请求入队列

关注 4 回答 3

yuhehanfeng 回答了问题 · 8月3日

解决vue axios暂存多个请求,交换完token后,再依次执行请求,但是成功的回调找不到了?

https://juejin.im/post/684490...
这个文章解决了我的问题,提供了两种方案,一种时请求前交换token,一种时请求后交换token。
解决问题的核心原理:promise的灵活运用。

关注 4 回答 3

yuhehanfeng 赞了文章 · 7月25日

你TM管这玩意儿叫H5编辑器?????

作为一名程序员,产品经理、设计师等身兼数职的我,推荐一款年度最佳的H5编辑器给大家,真心牛逼,业界良心,看的我热血沸腾,回家一脚踢飞正在熟睡的哈士奇!

H5DS编辑器,软件截图:

592956246-5f0edf4a2a0a5.gif

推荐理由1:时间轴

支持动画时间轴,调试动画和音频

image.png

给图层添加动画后可以通过时间轴设置动画,真的很直观和方便。效率提升N倍。其中紫色块表示进入动画,蓝色是离开动画,黄色块是强调动画,绿色块表示音频,另外时间轴开可以设置音乐播放的开始时间。

推荐理由2:批量操作

批量选中后,还可以一键布局,批量粘贴动画,批量设置动画间隔,真是强大的不要不要呀~

image.png

推荐理由3:逐帧动画

逐帧动画哇~ 真的优秀!

image.png

推荐理由4:路径动画

还可以自定义路径动画,贝塞尔曲线,钢笔工具????!!!这就叫专业

image.png

推荐理由5:黑暗模式

黑暗模式,传说中只有IOS才有的黑暗模式,居然在H5DS编辑器中也有,喜欢黑色的编辑器么?那就开启黑暗模式吧!

image.png

推荐理由6:下载代码

尼玛,还可以把做好的作品下载代码到本地。一次只要10个积分,积分不够可以关注官方的公众号,直接赠送500个积分,每天签到可以赠送10个积分。真的很良心~ 积分用完还可以找作者要积分,基本上可以免费使用,之所以为什么不直接免费?因为是怕有人恶意下载导致服务器负载过高挂了大家都没得玩了。

image.png

推荐理由7:强大音频

语音合成,在线录音,上传音频,你需要的都有!

image.png

image.png

推荐理由8:浮动层

唯一一款支持多浮动层的编辑器,给浮动层添加元素后每个页面都会全部加上元素,这个操作比较骚,真的~

image.png

图解浮动层:

微信截图_20200723190133.png

推荐理由9:吸附定位

吸附定位,这个概念真的比较新,通过名字可以了解到这个功能可以让元素吸附到窗口指定的位置,对这个研究了一下发现这个功能真的很酷,特别是解决各种机型兼容性的问题。

推荐理由10:自定义脚本

额,还可以自定义JS脚本,编辑器实现不了的功能,脚本帮你实现。老板:你TM管着玩意儿叫H5编辑器?????可以写代码的编辑器我还是第一次见。

image.png

推荐理由11:丰富的自定义交互功能

编辑器提供了各种交互功能,比如切换页面、拖动、超链接、打电话、隐藏显示图层、弹窗控制、音频控制等丰富的交互功能。真香~

image

推荐理由12:插件可扩展

编辑器自带了很多插件,这款叫H5DS的编辑器更牛逼了,插件还可以自己开发,为了让插件开发者更爽,居然自己开发了一套UI库,哦,你没看错,真的是一套UI库。【h5ds-ui】

woc~ 能不能好好的做编辑器!不得不说真的奥利给呀~

image.png

image.png

推荐理由13:H5DS-JSSDK

额,居然自己开发了一套JSSDK,让React开发的H5DS在vue, angular中大放异彩。传说中的混合开发来了????

image.png

推荐理由14:

推荐H5DS的理由很简单,我TM就是作者!【H5DS网址】

非要逼我跪下来求你才会给我点个赞吗?????老子堂堂七尺男儿!

image.png

大爷!赏个赞吧!

查看原文

赞 51 收藏 14 评论 13

yuhehanfeng 收藏了文章 · 4月30日

为你的 Vue.js 单页应用提速

作者:Matthias Sommer

翻译:疯狂的技术宅

原文:https://dzone.com/articles/sp...

未经允许严禁转载

我有一个项目用了 Vue.js 来构建单页应用程序。随着上线日期的临近,性能优化的工作变得越来越重要。在本文中,我收集了有关在加载时间和渲染性能方面提高 Vue.js 应用性能的所有知识。

使用 Vue.js,你可以快速构建单页应用。 Webpack 会为你将所有内容捆绑到文件(HTML、JavaScript、CSS)中,最后可以用 nginx 来提供。至少,这是我们的设置。但是 Webpack 会警告你某些资源太大。

需要注意的是,一旦用户访问 SPA,这三个文件将会被加载,并且只有在加载完毕之后才会渲染页面。但是最初加载的页面一般不需要太多文件内容,并且不应拖慢用户访问我们的网站的速度。

以下介绍了有关如何缓解此类问题的几种方法,以及在响应性和性能方面进一步改进 Vue.js 应用的其他方法。

功能组件

功能组件是不包含任何状态和实例的组件。将无状态 Vue 组件转换为功能组件可以大大提高渲染性能。

只需在顶层 template 标记中添加 functional 关键字即可:

<template functional>  <div>...</div> </template>

要像以前一样访问 prop 和数据,你必须进行一些小的调整。

<template functional>  
  <div>{{ props.someProp }}</div> 
</template> 
<script> 
  export default {  
    props: {    
      someProp: String  
    } 
  }
</script>

如果你使用 i18n 进行国际化,则必须在 parent 之前加上 $t

{{ parent.$t('app.not-found.message') }}

使用功能组件,我们无权使用方法或计算的 prop。但是,我们仍然可以使用 $options 访问方法。

<template functional>  
  <div>
    {{ $options.username(props.user) }}
  </div> 
</template> 
<script> 
  export default {  
    props: {    
      user: User,  
    },   
    username(user: User): string {    
        return user.name;  
    } 
  }
</script>

延迟加载组件

延迟加载组件可以节省大量的初始下载时间。调用 import() 函数时,将会下载所有延迟加载的资源。对于 Vue 组件,仅在请求渲染时才发生。对话框是注定会这样的。通常仅在用户交互后才显示它们。

<template> 
  <div>     
    ...    
    <app-modal-dialog v-if="showDialog" />  
  </div> 
</template> 
<script> 
  export default {  
    components: {    
      ModalDialog: () => import('./ModalDialog.vue')  
    } 
  }
</script>

Webpack 将为 ModalDialog 组件创建一个单独的块,该块不会在页面加载时立即下载,而是仅在需要时才下载。

注意不要延迟加载应自动显示的组件。例如以下内容(无提示)将无法加载模式对话框。

mounted() {
  this.$bvModal.show('password-check'); 
},

原因是已安装的 hook 是在延迟加载模态组件之前进行评估的。

延迟加载路由

构建 SPA 时,JavaScript 捆绑包可能会变得很大,从而增加页面加载时间。如果我们可以将每个路由的组成部分拆分为一个单独的块,然后仅在访问路由时才加载它们,则效率会更高。

import ProjectList from '@/components/ProjectList.vue'; 
export const routes = [  
  {    
    path: '/projects',    
    name: 'projects',    
    component: ProjectList,  
  }, 
]

定义一个异步组件非常容易,该组件将由 Webpack 自动进行代码拆分。只需更改导入语句:

const ProjectList = () => import('@/components/ProjectList.vue');

除此之外,无需更改路由配置。通过以下方式在生产模式下构建你的应用:

"build": "vue-cli-service build --mode production"

并确认会生成很多块

Vue 和 Webpack 中的代码拆分

你还可以通过在浏览器中打开开发者控制台来验证此功能是否正常。在 Network 标签中,一旦你访问新路由,就会异步加载多个 JavaScript 文件。在开发模式下,每个块都将被赋予一个自动递增的数字。在生产模式下,将使用自动计算的哈希值代替。

延迟加载的块和预取缓存

Vue 有一个很酷的功能就是 Vue 自动添加 Webpack 的魔术注释,以便进一步自动预取其他块(请参阅预取缓存一节) 。但是,预取仅在浏览器完成初始加载并变为空闲之后才开始。

使对象列表不可变

通常,我们将从后端获取对象列表,例如用户、项目、文章等。默认情况下,Vue 使数组中每个对象的每个第一级属性都具有响应性。对于大量对象而言,这代价可能会很大。有时我们只想显示对象时就不需要去修改它们。

所以在这种情况下,如果我们阻止 Vue 使列表具有响应性,那么就可以获得一些性能。我们可以通过使用列表中的 Object.freeze 来做到这一点,例如使其一直不变。

export async function get(url: string): Promise<User[]> {   
  const response = await Object.freeze(axios.get<User[]>(url));   
  return response.data; 
}

评估运行时性能

我们已经讨论了许多改进 Vue SPA 的方法,但是不知道我们实际获得了多少性能。可以通过使用浏览器中开发者工具的 Performance 标签来实现。

为了获得准确的数据,我们必须在 Vue 应用中激活性能模式。让我们在 main.ts 文件中用开发模式激活它

Vue.config.performance = process.env.NODE_ENV !== "production";

这将激活 Vue 内部使用的 User Timing API

打开浏览器,然后按 F12 键打开开发者控制台。切换到 Performance 选项卡,然后单击 Start Profiling。在 Chrome 中,“ Timings” 行显示重要标记,例如 “First Contentful Paint” 和 “First Meanfulful Paint” 时间。你应该尝试减少它们,以便你的用户可以尽快使用该网站。

总结

在本文中,我们了解了如何对路由和组件使用延迟加载以将 SPA 分成多个块,功能组件如何提高性能以及如何衡量这些改进。


本文首发微信公众号:前端先锋

欢迎扫描二维码关注公众号,每天都给你推送新鲜的前端技术文章

欢迎扫描二维码关注公众号,每天都给你推送新鲜的前端技术文章

欢迎继续阅读本专栏其它高赞文章:


查看原文

yuhehanfeng 赞了文章 · 4月30日

一份关于vue-cli3项目常用项配置

  1. 配置全局cdn,包含js、css
  2. 开启Gzip压缩,包含文件js、css
  3. 去掉注释、去掉console.log
  4. 压缩图片
  5. 本地代理
  6. 设置别名,vscode也能识别
  7. 配置环境变量开发模式、测试模式、生产模式
  8. 请求路由动态添加
  9. axios配置
  10. 添加mock数据
  11. 配置全局less
  12. 只打包改变的文件
  13. 开启分析打包日志

vue.config.js

完整的架构配置

const path = require('path');
const UglifyJsPlugin = require('uglifyjs-webpack-plugin') // 去掉注释
const CompressionWebpackPlugin = require('compression-webpack-plugin'); // 开启压缩
const { HashedModuleIdsPlugin } = require('webpack');

function resolve(dir) {
    return path.join(__dirname, dir)
}

const isProduction = process.env.NODE_ENV === 'production';

// cdn预加载使用
const externals = {
    'vue': 'Vue',
    'vue-router': 'VueRouter',
    'vuex': 'Vuex',
    'axios': 'axios',
    "element-ui": "ELEMENT"
}

const cdn = {
    // 开发环境
    dev: {
        css: [
            'https://unpkg.com/element-ui/lib/theme-chalk/index.css'
        ],
        js: []
    },
    // 生产环境
    build: {
        css: [
            'https://unpkg.com/element-ui/lib/theme-chalk/index.css'
        ],
        js: [
            'https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.min.js',
            'https://cdn.jsdelivr.net/npm/vue-router@3.0.1/dist/vue-router.min.js',
            'https://cdn.jsdelivr.net/npm/vuex@3.0.1/dist/vuex.min.js',
            'https://cdn.jsdelivr.net/npm/axios@0.18.0/dist/axios.min.js',
            'https://unpkg.com/element-ui/lib/index.js'
        ]
    }
}

module.exports = {

    lintOnSave: false, // 关闭eslint
    productionSourceMap: false,
    publicPath: './', 
    outputDir: process.env.outputDir, // 生成文件的目录名称
    chainWebpack: config => {

        config.resolve.alias
            .set('@', resolve('src'))

        // 压缩图片
        config.module
            .rule('images')
            .test(/\.(png|jpe?g|gif|svg)(\?.*)?$/)
            .use('image-webpack-loader')
            .loader('image-webpack-loader')
            .options({ bypassOnDebug: true })

        // webpack 会默认给commonChunk打进chunk-vendors,所以需要对webpack的配置进行delete
        config.optimization.delete('splitChunks')

        config.plugin('html').tap(args => {
            if (process.env.NODE_ENV === 'production') {
                args[0].cdn = cdn.build
            }
            if (process.env.NODE_ENV === 'development') {
                args[0].cdn = cdn.dev
            }
            return args
        })
        
        config
            .plugin('webpack-bundle-analyzer')
            .use(require('webpack-bundle-analyzer').BundleAnalyzerPlugin)
    },

    configureWebpack: config => {
        const plugins = [];

        if (isProduction) {
            plugins.push(
                new UglifyJsPlugin({
                    uglifyOptions: {
                        output: {
                            comments: false, // 去掉注释
                        },
                        warnings: false,
                        compress: {
                            drop_console: true,
                            drop_debugger: false,
                            pure_funcs: ['console.log']//移除console
                        }
                    }
                })
            )
            // 服务器也要相应开启gzip
            plugins.push(
                new CompressionWebpackPlugin({
                    algorithm: 'gzip',
                    test: /\.(js|css)$/,// 匹配文件名
                    threshold: 10000, // 对超过10k的数据压缩
                    deleteOriginalAssets: false, // 不删除源文件
                    minRatio: 0.8 // 压缩比
                })
            )

            // 用于根据模块的相对路径生成 hash 作为模块 id, 一般用于生产环境
            plugins.push(
                new HashedModuleIdsPlugin()
            )

            // 开启分离js
            config.optimization = {
                runtimeChunk: 'single',
                splitChunks: {
                    chunks: 'all',
                    maxInitialRequests: Infinity,
                    minSize: 1000 * 60,
                    cacheGroups: {
                        vendor: {
                            test: /[\\/]node_modules[\\/]/,
                            name(module) {
                                // 排除node_modules 然后吧 @ 替换为空 ,考虑到服务器的兼容
                                const packageName = module.context.match(/[\\/]node_modules[\\/](.*?)([\\/]|$)/)[1]
                                return `npm.${packageName.replace('@', '')}`
                            }
                        }
                    }
                }
            };

            // 取消webpack警告的性能提示
            config.performance = {
                hints: 'warning',
                //入口起点的最大体积
                maxEntrypointSize: 1000 * 500,
                //生成文件的最大体积
                maxAssetSize: 1000 * 1000,
                //只给出 js 文件的性能提示
                assetFilter: function (assetFilename) {
                    return assetFilename.endsWith('.js');
                }
            }

            // 打包时npm包转CDN
            config.externals = externals;
        }

        return { plugins }
    },

    pluginOptions: {
        // 配置全局less
        'style-resources-loader': {
            preProcessor: 'less',
            patterns: [resolve('./src/style/theme.less')]
        }
    },
    devServer: {
        open: false, // 自动启动浏览器
        host: '0.0.0.0', // localhost
        port: 6060, // 端口号
        https: false,
        hotOnly: false, // 热更新
        proxy: {
            '^/sso': {
                target: process.env.VUE_APP_SSO, // 重写路径
                ws: true,   //开启WebSocket
                secure: false,      // 如果是https接口,需要配置这个参数
                changeOrigin: true
            }
        }
    }
}

html模板配置cdn

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width,initial-scale=1.0">
    <link rel="icon" href="<%= BASE_URL %>favicon.ico">
    <title><%= htmlWebpackPlugin.options.title %></title>
    <% for (var i in
        htmlWebpackPlugin.options.cdn && htmlWebpackPlugin.options.cdn.css) { %>
    <link href="<%= htmlWebpackPlugin.options.cdn.css[i] %>" rel="preload" as="style" />
    <link href="<%= htmlWebpackPlugin.options.cdn.css[i] %>" rel="stylesheet" />
    <% } %>
</head>

<body>
    <noscript>
        <strong>We're sorry but <%= htmlWebpackPlugin.options.title %> doesn't work properly without JavaScript enabled.
            Please enable it to continue.</strong>
    </noscript>
    <div id="app"></div>
    <!-- built files will be auto injected -->
    <% for (var i in
        htmlWebpackPlugin.options.cdn && htmlWebpackPlugin.options.cdn.js) { %>
    <script data-original="<%= htmlWebpackPlugin.options.cdn.js[i] %>"></script>
    <% } %>
</body>
</html>

开启Gzip压缩,包含文件js、css

new CompressionWebpackPlugin({
      algorithm: 'gzip',
      test: /\.(js|css)$/, // 匹配文件名
      threshold: 10000, // 对超过10k的数据压缩
      deleteOriginalAssets: false, // 不删除源文件
      minRatio: 0.8 // 压缩比
})

去掉注释、去掉console.log

安装cnpm i uglifyjs-webpack-plugin -D

const UglifyJsPlugin = require('uglifyjs-webpack-plugin')
new UglifyJsPlugin({
    uglifyOptions: {
        output: {
            comments: false, // 去掉注释
        },
        warnings: false,
        compress: {
            drop_console: true,
            drop_debugger: false,
            pure_funcs: ['console.log'] //移除console
        }
    }
})

压缩图片

chainWebpack: config => {
    // 压缩图片
    config.module
        .rule('images')
        .test(/\.(png|jpe?g|gif|svg)(\?.*)?$/)
        .use('image-webpack-loader')
        .loader('image-webpack-loader')
        .options({ bypassOnDebug: true })

}

本地代理

devServer: {
    open: false, // 自动启动浏览器
    host: '0.0.0.0', // localhost
    port: 6060, // 端口号
    https: false,
    hotOnly: false, // 热更新
    proxy: {
        '^/sso': {
            target: process.env.VUE_APP_SSO, // 重写路径
            ws: true, //开启WebSocket
            secure: false, // 如果是https接口,需要配置这个参数
            changeOrigin: true
        }
    }
}

设置vscode 识别别名

在vscode中插件安装栏搜索 Path Intellisense 插件,打开settings.json文件添加 以下代码 "@": "${workspaceRoot}/src",安以下添加

{
    "workbench.iconTheme": "material-icon-theme",
    "editor.fontSize": 16,
    "editor.detectIndentation": false,
    "guides.enabled": false,
    "workbench.colorTheme": "Monokai",
    "path-intellisense.mappings": {
        "@": "${workspaceRoot}/src"
    }
}

在项目package.json所在同级目录下创建文件jsconfig.json

{
    "compilerOptions": {
        "target": "ES6",
        "module": "commonjs",
        "allowSyntheticDefaultImports": true,
        "baseUrl": "./",
        "paths": {
          "@/*": ["src/*"]
        }
    },
    "exclude": [
        "node_modules"
    ]
}

如果还没请客官移步在vscode中使用别名@按住ctrl也能跳转对应路径

配置环境变量开发模式、测试模式、生产模式

在根目录新建

.env.development

# 开发环境
NODE_ENV='development'

VUE_APP_SSO='http://http://localhost:9080'

.env.test

NODE_ENV = 'production' # 如果我们在.env.test文件中把NODE_ENV设置为test的话,那么打包出来的目录结构是有差异的
VUE_APP_MODE = 'test'
VUE_APP_SSO='http://http://localhost:9080'
outputDir = test

.env.production

NODE_ENV = 'production'

VUE_APP_SSO='http://http://localhost:9080'

package.json

"scripts": {
    "build": "vue-cli-service build", //生产打包
    "lint": "vue-cli-service lint",
    "dev": "vue-cli-service serve", // 开发模式
    "test": "vue-cli-service build --mode test", // 测试打包
    "publish": "vue-cli-service build && vue-cli-service build --mode test" // 测试和生产一起打包
 }

请求路由动态添加

router/index.js文件

import Vue from 'vue';
import VueRouter from 'vue-router'
Vue.use(VueRouter)

import defaultRouter from './defaultRouter'
import dynamicRouter from './dynamicRouter';

import store from '@/store';

const router = new VueRouter({
    routes: defaultRouter,
    mode: 'hash',
    scrollBehavior(to, from, savedPosition) {
        // keep-alive 返回缓存页面后记录浏览位置
        if (savedPosition && to.meta.keepAlive) {
            return savedPosition;
        }
        // 异步滚动操作
        return new Promise((resolve, reject) => {
            setTimeout(() => {
                resolve({ x: 0, y: 0 })
            }, 200)
        })
    }
})

// 消除路由重复警告
const selfaddRoutes = function (params) {
    router.matcher = new VueRouter().matcher;
    router.addRoutes(params);
}

// 全局路由拦截
router.beforeEach((to, from, next) => {
    const { hasRoute } = store.state; // 防止路由重复添加
    if (hasRoute) {
        next()
    } else {
        dynamicRouter(to, from, next, selfaddRoutes)
    }
})

export default router;

dynamicRouter.js

import http from '@/http/request';
import defaultRouter from './defaultRouter'
import store from '@/store'

// 重新构建路由对象
const menusMap = function (menu) {
    return menu.map(v => {
        const { path, name, component } = v
        const item = {
            path,
            name,
            component: () => import(`@/${component}`)
        }
        return item;
    })
}


// 获取路由
const addPostRouter = function (to, from, next, selfaddRoutes) {
    http.windPost('/mock/menu') // 发起请求获取路由
        .then(menu => {
            defaultRouter[0].children.push(...menusMap(menu));
            selfaddRoutes(defaultRouter);
            store.commit('hasRoute', true);
            next({ ...to, replace: true })
        })
}

export default addPostRouter;

defaultRouter.js 默认路由

const main = r => require.ensure([], () => r(require('@/layout/main.vue')), 'main')
const index = r => require.ensure([], () => r(require('@/view/index/index.vue')), 'index')
const about = r => require.ensure([], () => r(require('@/view/about/about.vue')), 'about')
const detail = r => require.ensure([], () => r(require('@/view/detail/detail.vue')), 'detail')
const error = r => require.ensure([], () => r(require('@/view/404/404.vue')), 'error');
const defaultRouter = [
    {
        path: "/", 
        component: main, // 布局页
        redirect: {
            name: "index"
        },
        children:[
            {
                path: '/index',
                component: index,
                name: 'index',
                meta: {
                    title: 'index'
                }
            },
            {
                path: '/about',
                component: about,
                name: 'about',
                meta: {
                    title: 'about'
                }
            },
            {
                path: '/detail',
                component: detail,
                name: 'detail',
                meta: {
                    title: 'detail'
                }
            }
        ]
    },
    {
        path: '/404',
        component: error,
        name: '404',
        meta: {
            title: '404'
        }
    }
]
export default defaultRouter;

axios配置

import axios from "axios";
import merge from 'lodash/merge'
import qs from 'qs'

/**
 * 实例化
 * config是库的默认值,然后是实例的 defaults 属性,最后是请求设置的 config 参数。后者将优先于前者
 */
const http = axios.create({
    timeout: 1000 * 30,
    withCredentials: true, // 表示跨域请求时是否需要使用凭证
});

/**
 * 请求拦截
 */
http.interceptors.request.use(function (config) {
    return config;
}, function (error) {
    return Promise.reject(error);
});


/**
 * 响应拦截
 */
http.interceptors.response.use(response => {
    // 过期之类的操作
    if (response.data && (response.data.code === 401)) {
        // window.location.href = ''; 重定向
    }
    return response
}, error => {
    return Promise.reject(error)
})


/**
 * 请求地址处理
 */
http.adornUrl = (url) => {
    return url;
}

/**
 * get请求参数处理
 * params 参数对象
 * openDefultParams 是否开启默认参数
 */
http.adornParams = (params = {}, openDefultParams = true) => {
    var defaults = {
        t: new Date().getTime()
    }
    return openDefultParams ? merge(defaults, params) : params
}


/**
 * post请求数据处理
 * @param {*} data 数据对象
 * @param {*} openDefultdata 是否开启默认数据?
 * @param {*} contentType 数据格式
 *  json: 'application/json; charset=utf-8'
 *  form: 'application/x-www-form-urlencoded; charset=utf-8'
 */
http.adornData = (data = {}, openDefultdata = true, contentType = 'json') => {
    var defaults = {
        t: new Date().getTime()
    }
    data = openDefultdata ? merge(defaults, data) : data
    return contentType === 'json' ? JSON.stringify(data) : qs.stringify(data)
}


/**
 * windPost请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windPost = function (url, params) {
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), qs.stringify(params))
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}


/**
 * windJsonPost请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windJsonPost = function (url, params) {
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), http.adornParams(params))
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}


/**
 * windGet请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windGet = function (url, params) {
    return new Promise((resolve, reject) => {
        http.get(http.adornUrl(url), { params: params })
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}

/**
 * 上传图片
 */
http.upLoadPhoto = function (url, params, callback) {
    let config = {}
    if (callback !== null) {
        config = {
            onUploadProgress: function (progressEvent) {
                //属性lengthComputable主要表明总共需要完成的工作量和已经完成的工作是否可以被测量
                //如果lengthComputable为false,就获取不到progressEvent.total和progressEvent.loaded
                callback(progressEvent)
            }
        }
    }
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), http.adornParams(params), config)
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}
export default http;

添加mock数据

const Mock = require('mockjs')

// 获取 mock.Random 对象
const Random = Mock.Random
// mock新闻数据,包括新闻标题title、内容content、创建时间createdTime
const produceNewsData = function () {
    let newsList = []
    for (let i = 0; i < 3; i++) {
        let newNewsObject = {}
        if(i === 0){
            newNewsObject.path = '/add/article';
            newNewsObject.name  = 'add-article';
            newNewsObject.component = 'modules/add/article/article';
        }
        if(i === 1){
            newNewsObject.path = '/detail/article';
            newNewsObject.name  = 'detail-article';
            newNewsObject.component = 'modules/detail/article/article'
        }
        if(i === 2){
            newNewsObject.path = '/edit/article';
            newNewsObject.name  = 'edit-article';
            newNewsObject.component = 'modules/edit/article/article'
        }
        newsList.push(newNewsObject)
    }
    return newsList;
}
Mock.mock('/mock/menu', produceNewsData)

配置全局less

pluginOptions: {
    // 配置全局less
    'style-resources-loader': {
        preProcessor: 'less',
        patterns: [resolve('./src/style/theme.less')]
    }
}

只打包改变的文件

安装cnpm i webpack -D

const { HashedModuleIdsPlugin } = require('webpack');
configureWebpack: config => {    
    const plugins = [];
    plugins.push(
        new HashedModuleIdsPlugin()
    )
}

开启分析打包日志

安装cnpm i webpack-bundle-analyzer -D

chainWebpack: config => {
    config
        .plugin('webpack-bundle-analyzer')
        .use(require('webpack-bundle-analyzer').BundleAnalyzerPlugin)
}

完整代码

image.png

点击获取完整代码github

获取更多相关知识

横版二维码.png

查看原文

赞 56 收藏 42 评论 0

yuhehanfeng 收藏了文章 · 4月30日

一份关于vue-cli3项目常用项配置

  1. 配置全局cdn,包含js、css
  2. 开启Gzip压缩,包含文件js、css
  3. 去掉注释、去掉console.log
  4. 压缩图片
  5. 本地代理
  6. 设置别名,vscode也能识别
  7. 配置环境变量开发模式、测试模式、生产模式
  8. 请求路由动态添加
  9. axios配置
  10. 添加mock数据
  11. 配置全局less
  12. 只打包改变的文件
  13. 开启分析打包日志

vue.config.js

完整的架构配置

const path = require('path');
const UglifyJsPlugin = require('uglifyjs-webpack-plugin') // 去掉注释
const CompressionWebpackPlugin = require('compression-webpack-plugin'); // 开启压缩
const { HashedModuleIdsPlugin } = require('webpack');

function resolve(dir) {
    return path.join(__dirname, dir)
}

const isProduction = process.env.NODE_ENV === 'production';

// cdn预加载使用
const externals = {
    'vue': 'Vue',
    'vue-router': 'VueRouter',
    'vuex': 'Vuex',
    'axios': 'axios',
    "element-ui": "ELEMENT"
}

const cdn = {
    // 开发环境
    dev: {
        css: [
            'https://unpkg.com/element-ui/lib/theme-chalk/index.css'
        ],
        js: []
    },
    // 生产环境
    build: {
        css: [
            'https://unpkg.com/element-ui/lib/theme-chalk/index.css'
        ],
        js: [
            'https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.min.js',
            'https://cdn.jsdelivr.net/npm/vue-router@3.0.1/dist/vue-router.min.js',
            'https://cdn.jsdelivr.net/npm/vuex@3.0.1/dist/vuex.min.js',
            'https://cdn.jsdelivr.net/npm/axios@0.18.0/dist/axios.min.js',
            'https://unpkg.com/element-ui/lib/index.js'
        ]
    }
}

module.exports = {

    lintOnSave: false, // 关闭eslint
    productionSourceMap: false,
    publicPath: './', 
    outputDir: process.env.outputDir, // 生成文件的目录名称
    chainWebpack: config => {

        config.resolve.alias
            .set('@', resolve('src'))

        // 压缩图片
        config.module
            .rule('images')
            .test(/\.(png|jpe?g|gif|svg)(\?.*)?$/)
            .use('image-webpack-loader')
            .loader('image-webpack-loader')
            .options({ bypassOnDebug: true })

        // webpack 会默认给commonChunk打进chunk-vendors,所以需要对webpack的配置进行delete
        config.optimization.delete('splitChunks')

        config.plugin('html').tap(args => {
            if (process.env.NODE_ENV === 'production') {
                args[0].cdn = cdn.build
            }
            if (process.env.NODE_ENV === 'development') {
                args[0].cdn = cdn.dev
            }
            return args
        })
        
        config
            .plugin('webpack-bundle-analyzer')
            .use(require('webpack-bundle-analyzer').BundleAnalyzerPlugin)
    },

    configureWebpack: config => {
        const plugins = [];

        if (isProduction) {
            plugins.push(
                new UglifyJsPlugin({
                    uglifyOptions: {
                        output: {
                            comments: false, // 去掉注释
                        },
                        warnings: false,
                        compress: {
                            drop_console: true,
                            drop_debugger: false,
                            pure_funcs: ['console.log']//移除console
                        }
                    }
                })
            )
            // 服务器也要相应开启gzip
            plugins.push(
                new CompressionWebpackPlugin({
                    algorithm: 'gzip',
                    test: /\.(js|css)$/,// 匹配文件名
                    threshold: 10000, // 对超过10k的数据压缩
                    deleteOriginalAssets: false, // 不删除源文件
                    minRatio: 0.8 // 压缩比
                })
            )

            // 用于根据模块的相对路径生成 hash 作为模块 id, 一般用于生产环境
            plugins.push(
                new HashedModuleIdsPlugin()
            )

            // 开启分离js
            config.optimization = {
                runtimeChunk: 'single',
                splitChunks: {
                    chunks: 'all',
                    maxInitialRequests: Infinity,
                    minSize: 1000 * 60,
                    cacheGroups: {
                        vendor: {
                            test: /[\\/]node_modules[\\/]/,
                            name(module) {
                                // 排除node_modules 然后吧 @ 替换为空 ,考虑到服务器的兼容
                                const packageName = module.context.match(/[\\/]node_modules[\\/](.*?)([\\/]|$)/)[1]
                                return `npm.${packageName.replace('@', '')}`
                            }
                        }
                    }
                }
            };

            // 取消webpack警告的性能提示
            config.performance = {
                hints: 'warning',
                //入口起点的最大体积
                maxEntrypointSize: 1000 * 500,
                //生成文件的最大体积
                maxAssetSize: 1000 * 1000,
                //只给出 js 文件的性能提示
                assetFilter: function (assetFilename) {
                    return assetFilename.endsWith('.js');
                }
            }

            // 打包时npm包转CDN
            config.externals = externals;
        }

        return { plugins }
    },

    pluginOptions: {
        // 配置全局less
        'style-resources-loader': {
            preProcessor: 'less',
            patterns: [resolve('./src/style/theme.less')]
        }
    },
    devServer: {
        open: false, // 自动启动浏览器
        host: '0.0.0.0', // localhost
        port: 6060, // 端口号
        https: false,
        hotOnly: false, // 热更新
        proxy: {
            '^/sso': {
                target: process.env.VUE_APP_SSO, // 重写路径
                ws: true,   //开启WebSocket
                secure: false,      // 如果是https接口,需要配置这个参数
                changeOrigin: true
            }
        }
    }
}

html模板配置cdn

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width,initial-scale=1.0">
    <link rel="icon" href="<%= BASE_URL %>favicon.ico">
    <title><%= htmlWebpackPlugin.options.title %></title>
    <% for (var i in
        htmlWebpackPlugin.options.cdn && htmlWebpackPlugin.options.cdn.css) { %>
    <link href="<%= htmlWebpackPlugin.options.cdn.css[i] %>" rel="preload" as="style" />
    <link href="<%= htmlWebpackPlugin.options.cdn.css[i] %>" rel="stylesheet" />
    <% } %>
</head>

<body>
    <noscript>
        <strong>We're sorry but <%= htmlWebpackPlugin.options.title %> doesn't work properly without JavaScript enabled.
            Please enable it to continue.</strong>
    </noscript>
    <div id="app"></div>
    <!-- built files will be auto injected -->
    <% for (var i in
        htmlWebpackPlugin.options.cdn && htmlWebpackPlugin.options.cdn.js) { %>
    <script data-original="<%= htmlWebpackPlugin.options.cdn.js[i] %>"></script>
    <% } %>
</body>
</html>

开启Gzip压缩,包含文件js、css

new CompressionWebpackPlugin({
      algorithm: 'gzip',
      test: /\.(js|css)$/, // 匹配文件名
      threshold: 10000, // 对超过10k的数据压缩
      deleteOriginalAssets: false, // 不删除源文件
      minRatio: 0.8 // 压缩比
})

去掉注释、去掉console.log

安装cnpm i uglifyjs-webpack-plugin -D

const UglifyJsPlugin = require('uglifyjs-webpack-plugin')
new UglifyJsPlugin({
    uglifyOptions: {
        output: {
            comments: false, // 去掉注释
        },
        warnings: false,
        compress: {
            drop_console: true,
            drop_debugger: false,
            pure_funcs: ['console.log'] //移除console
        }
    }
})

压缩图片

chainWebpack: config => {
    // 压缩图片
    config.module
        .rule('images')
        .test(/\.(png|jpe?g|gif|svg)(\?.*)?$/)
        .use('image-webpack-loader')
        .loader('image-webpack-loader')
        .options({ bypassOnDebug: true })

}

本地代理

devServer: {
    open: false, // 自动启动浏览器
    host: '0.0.0.0', // localhost
    port: 6060, // 端口号
    https: false,
    hotOnly: false, // 热更新
    proxy: {
        '^/sso': {
            target: process.env.VUE_APP_SSO, // 重写路径
            ws: true, //开启WebSocket
            secure: false, // 如果是https接口,需要配置这个参数
            changeOrigin: true
        }
    }
}

设置vscode 识别别名

在vscode中插件安装栏搜索 Path Intellisense 插件,打开settings.json文件添加 以下代码 "@": "${workspaceRoot}/src",安以下添加

{
    "workbench.iconTheme": "material-icon-theme",
    "editor.fontSize": 16,
    "editor.detectIndentation": false,
    "guides.enabled": false,
    "workbench.colorTheme": "Monokai",
    "path-intellisense.mappings": {
        "@": "${workspaceRoot}/src"
    }
}

在项目package.json所在同级目录下创建文件jsconfig.json

{
    "compilerOptions": {
        "target": "ES6",
        "module": "commonjs",
        "allowSyntheticDefaultImports": true,
        "baseUrl": "./",
        "paths": {
          "@/*": ["src/*"]
        }
    },
    "exclude": [
        "node_modules"
    ]
}

如果还没请客官移步在vscode中使用别名@按住ctrl也能跳转对应路径

配置环境变量开发模式、测试模式、生产模式

在根目录新建

.env.development

# 开发环境
NODE_ENV='development'

VUE_APP_SSO='http://http://localhost:9080'

.env.test

NODE_ENV = 'production' # 如果我们在.env.test文件中把NODE_ENV设置为test的话,那么打包出来的目录结构是有差异的
VUE_APP_MODE = 'test'
VUE_APP_SSO='http://http://localhost:9080'
outputDir = test

.env.production

NODE_ENV = 'production'

VUE_APP_SSO='http://http://localhost:9080'

package.json

"scripts": {
    "build": "vue-cli-service build", //生产打包
    "lint": "vue-cli-service lint",
    "dev": "vue-cli-service serve", // 开发模式
    "test": "vue-cli-service build --mode test", // 测试打包
    "publish": "vue-cli-service build && vue-cli-service build --mode test" // 测试和生产一起打包
 }

请求路由动态添加

router/index.js文件

import Vue from 'vue';
import VueRouter from 'vue-router'
Vue.use(VueRouter)

import defaultRouter from './defaultRouter'
import dynamicRouter from './dynamicRouter';

import store from '@/store';

const router = new VueRouter({
    routes: defaultRouter,
    mode: 'hash',
    scrollBehavior(to, from, savedPosition) {
        // keep-alive 返回缓存页面后记录浏览位置
        if (savedPosition && to.meta.keepAlive) {
            return savedPosition;
        }
        // 异步滚动操作
        return new Promise((resolve, reject) => {
            setTimeout(() => {
                resolve({ x: 0, y: 0 })
            }, 200)
        })
    }
})

// 消除路由重复警告
const selfaddRoutes = function (params) {
    router.matcher = new VueRouter().matcher;
    router.addRoutes(params);
}

// 全局路由拦截
router.beforeEach((to, from, next) => {
    const { hasRoute } = store.state; // 防止路由重复添加
    if (hasRoute) {
        next()
    } else {
        dynamicRouter(to, from, next, selfaddRoutes)
    }
})

export default router;

dynamicRouter.js

import http from '@/http/request';
import defaultRouter from './defaultRouter'
import store from '@/store'

// 重新构建路由对象
const menusMap = function (menu) {
    return menu.map(v => {
        const { path, name, component } = v
        const item = {
            path,
            name,
            component: () => import(`@/${component}`)
        }
        return item;
    })
}


// 获取路由
const addPostRouter = function (to, from, next, selfaddRoutes) {
    http.windPost('/mock/menu') // 发起请求获取路由
        .then(menu => {
            defaultRouter[0].children.push(...menusMap(menu));
            selfaddRoutes(defaultRouter);
            store.commit('hasRoute', true);
            next({ ...to, replace: true })
        })
}

export default addPostRouter;

defaultRouter.js 默认路由

const main = r => require.ensure([], () => r(require('@/layout/main.vue')), 'main')
const index = r => require.ensure([], () => r(require('@/view/index/index.vue')), 'index')
const about = r => require.ensure([], () => r(require('@/view/about/about.vue')), 'about')
const detail = r => require.ensure([], () => r(require('@/view/detail/detail.vue')), 'detail')
const error = r => require.ensure([], () => r(require('@/view/404/404.vue')), 'error');
const defaultRouter = [
    {
        path: "/", 
        component: main, // 布局页
        redirect: {
            name: "index"
        },
        children:[
            {
                path: '/index',
                component: index,
                name: 'index',
                meta: {
                    title: 'index'
                }
            },
            {
                path: '/about',
                component: about,
                name: 'about',
                meta: {
                    title: 'about'
                }
            },
            {
                path: '/detail',
                component: detail,
                name: 'detail',
                meta: {
                    title: 'detail'
                }
            }
        ]
    },
    {
        path: '/404',
        component: error,
        name: '404',
        meta: {
            title: '404'
        }
    }
]
export default defaultRouter;

axios配置

import axios from "axios";
import merge from 'lodash/merge'
import qs from 'qs'

/**
 * 实例化
 * config是库的默认值,然后是实例的 defaults 属性,最后是请求设置的 config 参数。后者将优先于前者
 */
const http = axios.create({
    timeout: 1000 * 30,
    withCredentials: true, // 表示跨域请求时是否需要使用凭证
});

/**
 * 请求拦截
 */
http.interceptors.request.use(function (config) {
    return config;
}, function (error) {
    return Promise.reject(error);
});


/**
 * 响应拦截
 */
http.interceptors.response.use(response => {
    // 过期之类的操作
    if (response.data && (response.data.code === 401)) {
        // window.location.href = ''; 重定向
    }
    return response
}, error => {
    return Promise.reject(error)
})


/**
 * 请求地址处理
 */
http.adornUrl = (url) => {
    return url;
}

/**
 * get请求参数处理
 * params 参数对象
 * openDefultParams 是否开启默认参数
 */
http.adornParams = (params = {}, openDefultParams = true) => {
    var defaults = {
        t: new Date().getTime()
    }
    return openDefultParams ? merge(defaults, params) : params
}


/**
 * post请求数据处理
 * @param {*} data 数据对象
 * @param {*} openDefultdata 是否开启默认数据?
 * @param {*} contentType 数据格式
 *  json: 'application/json; charset=utf-8'
 *  form: 'application/x-www-form-urlencoded; charset=utf-8'
 */
http.adornData = (data = {}, openDefultdata = true, contentType = 'json') => {
    var defaults = {
        t: new Date().getTime()
    }
    data = openDefultdata ? merge(defaults, data) : data
    return contentType === 'json' ? JSON.stringify(data) : qs.stringify(data)
}


/**
 * windPost请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windPost = function (url, params) {
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), qs.stringify(params))
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}


/**
 * windJsonPost请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windJsonPost = function (url, params) {
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), http.adornParams(params))
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}


/**
 * windGet请求
 * @param {String} url [请求地址]
 * @param {Object} params [请求携带参数]
 */
http.windGet = function (url, params) {
    return new Promise((resolve, reject) => {
        http.get(http.adornUrl(url), { params: params })
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}

/**
 * 上传图片
 */
http.upLoadPhoto = function (url, params, callback) {
    let config = {}
    if (callback !== null) {
        config = {
            onUploadProgress: function (progressEvent) {
                //属性lengthComputable主要表明总共需要完成的工作量和已经完成的工作是否可以被测量
                //如果lengthComputable为false,就获取不到progressEvent.total和progressEvent.loaded
                callback(progressEvent)
            }
        }
    }
    return new Promise((resolve, reject) => {
        http.post(http.adornUrl(url), http.adornParams(params), config)
            .then(res => {
                resolve(res.data)
            })
            .catch(error => {
                reject(error)
            })
    })
}
export default http;

添加mock数据

const Mock = require('mockjs')

// 获取 mock.Random 对象
const Random = Mock.Random
// mock新闻数据,包括新闻标题title、内容content、创建时间createdTime
const produceNewsData = function () {
    let newsList = []
    for (let i = 0; i < 3; i++) {
        let newNewsObject = {}
        if(i === 0){
            newNewsObject.path = '/add/article';
            newNewsObject.name  = 'add-article';
            newNewsObject.component = 'modules/add/article/article';
        }
        if(i === 1){
            newNewsObject.path = '/detail/article';
            newNewsObject.name  = 'detail-article';
            newNewsObject.component = 'modules/detail/article/article'
        }
        if(i === 2){
            newNewsObject.path = '/edit/article';
            newNewsObject.name  = 'edit-article';
            newNewsObject.component = 'modules/edit/article/article'
        }
        newsList.push(newNewsObject)
    }
    return newsList;
}
Mock.mock('/mock/menu', produceNewsData)

配置全局less

pluginOptions: {
    // 配置全局less
    'style-resources-loader': {
        preProcessor: 'less',
        patterns: [resolve('./src/style/theme.less')]
    }
}

只打包改变的文件

安装cnpm i webpack -D

const { HashedModuleIdsPlugin } = require('webpack');
configureWebpack: config => {    
    const plugins = [];
    plugins.push(
        new HashedModuleIdsPlugin()
    )
}

开启分析打包日志

安装cnpm i webpack-bundle-analyzer -D

chainWebpack: config => {
    config
        .plugin('webpack-bundle-analyzer')
        .use(require('webpack-bundle-analyzer').BundleAnalyzerPlugin)
}

完整代码

image.png

点击获取完整代码github

获取更多相关知识

横版二维码.png

查看原文

yuhehanfeng 收藏了文章 · 4月26日

一些常见必备的MySQL知识

photo-1587613754067-13e9a170b42f.jpeg
最近在整理 sql 的时候发现一份优秀的笔记,是原作者学习 sql 所做的笔记,分享这份总结给大家,对大家对 sql 的可以来一次全方位的检漏和排查,感谢原作者 hjzCy 的付出,原文链接放在文章最下方,如果出现错误,希望大家共同指出!

登录和退出 MySQL 服务器

# 登录MySQL
$ mysql -u root -p12345612

# 退出MySQL数据库服务器
exit;

基本语法

-- 显示所有数据库
show databases;

-- 创建数据库
CREATE DATABASE test;

-- 切换数据库
use test;

-- 显示数据库中的所有表
show tables;

-- 创建数据表
CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

-- 查看数据表结构
-- describe pet;
desc pet;

-- 查询表
SELECT * from pet;

-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 删除数据
DELETE FROM pet where name = 'squirrel';

-- 删除表
DROP TABLE myorder;

建表约束

主键约束

-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 添加主键约束
-- 如果忘记设置主键,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

唯一主键

-- 建表时创建唯一主键
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主键
ALTER TABLE user DROP INDEX name;

非空约束

-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- 移除非空约束
ALTER TABLE user MODIFY age INT;

外键约束

-- 班级
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 这里的 class_id 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的记录被副表引用时,主表不可以被删除。

数据库的三大设计范式

1NF

只要字段值还可以继续拆分,就不满足第一范式。

范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定。

2NF

在满足第一范式的前提下,其他列都必须完全依赖于主键列。如果出现不完全依赖,只可能发生在联合主键的情况下:

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

实际上,在这张订单表中,product_name 只依赖于 product_idcustomer_name 只依赖于 customer_id 。也就是说,product_namecustomer_id 是没用关系的,customer_nameproduct_id 也是没有关系的。

这就不满足第二范式:其他列都必须完全依赖于主键列!

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后,myorder 表中的 product_idcustomer_id 完全依赖于 order_id 主键,而 productcustomer 表中的其他字段又完全依赖于主键。满足了第二范式的设计!

3NF

在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_idcustomer_id 两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计!

查询练习

准备数据

-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;

-- 创建学生表
CREATE TABLE student (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE, -- 生日
    class VARCHAR(20) -- 所在班级
);

-- 创建教师表
CREATE TABLE teacher (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    birthday DATE,
    profession VARCHAR(20) NOT NULL, -- 职称
    department VARCHAR(20) NOT NULL -- 部门
);

-- 创建课程表
CREATE TABLE course (
    no VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    t_no VARCHAR(20) NOT NULL, -- 教师编号
    -- 表示该 tno 来自于 teacher 表中的 no 字段值
    FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- 成绩表
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL, -- 学生编号
    c_no VARCHAR(20) NOT NULL, -- 课程号
    degree DECIMAL,    -- 成绩
    -- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
    FOREIGN KEY(s_no) REFERENCES student(no),
    FOREIGN KEY(c_no) REFERENCES course(no),
    -- 设置 s_no, c_no 为联合主键
    PRIMARY KEY(s_no, c_no)
);

-- 查看所有表
SHOW TABLES;

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

1 到 10

-- 查询 student 表的所有行
SELECT * FROM student;

-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;

-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;

-- 查询 score 表中成绩在60-80之间的所有行(区间查询和运算符查询)
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);

-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';

-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序,从高到低
-- ASC(默认): 升序,从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)。
-- (SELECT MAX(degree) FROM score): 子查询,算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

--  排序查询
-- LIMIT r, n: 表示从第r行开始,查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

分组计算平均成绩

查询每门课的平均成绩。

-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

分组条件与模糊查询

查询 score 表中至少有 2 名学生选修,并以 3 开头的课程的平均分数。

SELECT * FROM score;
-- c_no 课程编号
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

分析表发现,至少有 2 名学生选修的课程是 3-1053-2456-166 ,以 3 开头的课程是 3-1053-245 。也就是说,我们要查询所有 3-1053-245degree 平均分。

-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询,"%" 是一个通配符,匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';

-- 把前面的SQL语句拼接起来,
-- 后面加上一个 COUNT(*),表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

多表查询 - 1

查询所有学生的 name,以及该学生在 score 表中对应的 c_nodegree

SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | 曾华      |
| 102 | 匡明      |
| 103 | 王丽      |
| 104 | 李军      |
| 105 | 王芳      |
| 106 | 陆军      |
| 107 | 王尼玛    |
| 108 | 张全蛋    |
| 109 | 赵铁柱    |
+-----+-----------+

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

通过分析可以发现,只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了,如何做呢?

-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为,只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+

多表查询 - 2

查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。

只有 score 关联学生的 no ,因此只要查询 score 表,就能找出所有和学生相关的 nodegree

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

然后查询 course 表:

+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。

-- 增加一个查询字段 name,分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 计算机导论      |     92 |
| 105  | 计算机导论      |     88 |
| 109  | 计算机导论      |     76 |
| 103  | 操作系统        |     86 |
| 105  | 操作系统        |     75 |
| 109  | 操作系统        |     68 |
| 103  | 数字电路        |     85 |
| 105  | 数字电路        |     79 |
| 109  | 数字电路        |     81 |
+------+-----------------+--------+

三表关联查询

查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree

只有 score 表中关联学生的学号和课堂号,我们只要围绕着 score 这张表查询就好了。

SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

只要把 s_noc_no 替换成 studentsrouse 表中对应的 name 字段值就好了。

首先把 s_no 替换成 student 表中的 name 字段:

SELECT name, c_no, degree FROM student, score WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+

再把 c_no 替换成 course 表中的 name 字段:

-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

-- 由于字段名存在重复,使用 "表名.字段名 as 别名" 代替。
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;

子查询加分组求平均分

查询 95031 班学生每门课程的平均成绩。

score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩:

-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩:

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

子查询 - 1

查询在 3-105 课程中,所有成绩高于 109 号同学的记录。

首先筛选出课堂号为 3-105 ,在找出所有成绩高于 109 号同学的的行。

SELECT * FROM score
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

子查询 - 2

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

-- 不限制课程号,只要成绩大于109号同学的3-105课程成绩就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR 函数与带 IN 关键字查询

查询所有和 101108 号学生同年出生的 nonamebirthday 列。

-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));

多层嵌套子查询

查询 '张旭' 教师任课的学生成绩表。

首先找到教师编号:

SELECT NO FROM teacher WHERE NAME = '张旭'

通过 sourse 表找到该教师课程号:

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '张旭' );

通过筛选出的课程号查询成绩表:

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE t_no = (
        SELECT no FROM teacher WHERE NAME = '张旭'
    )
);

多表查询

查询某选修课程多于 5 个同学的教师姓名。

首先在 teacher 表中,根据 no 字段来判断该教师的同一门课程是否有至少 5 名学员选修:

-- 查询 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李诚   |
| 825 | 王萍   |
| 831 | 刘冰   |
| 856 | 张旭   |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
    -- 在这里找到对应的条件
);

查看和教师编号有有关的表的信息:

SELECT * FROM course;
-- t_no: 教师编号
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+

我们已经找到和教师编号有关的字段就在 course 表中,但是还无法知道哪门课程至少有 5 名学生选修,所以还需要根据 score 表来查询:

-- 在此之前向 score 插入一些数据,以便丰富查询条件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');

-- 查询 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

-- 在 score 表中将 c_no 作为分组,并且限制 c_no 持有至少 5 条数据。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+

根据筛选出来的课程号,找出在某课程中,拥有至少 5 名学员的教师编号:

SELECT t_no FROM course WHERE no IN (
    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825  |
+------+

teacher 表中,根据筛选出来的教师编号找到教师姓名:

SELECT name FROM teacher WHERE no IN (
    -- 最终条件
    SELECT t_no FROM course WHERE no IN (
        SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
    )
);

子查询 - 3

查询 “计算机系” 课程的成绩表。

思路是,先找出 course 表中所有 计算机系 课程的编号,然后根据这个编号查询 score 表。

-- 通过 teacher 表查询所有 `计算机系` 的教师编号
SELECT no, name, department FROM teacher WHERE department = '计算机系'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李诚   | 计算机系     |
| 825 | 王萍   | 计算机系     |
+-----+--------+--------------+

-- 通过 course 表查询该教师的课程编号
SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = '计算机系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根据筛选出来的课程号查询成绩表
SELECT * FROM score WHERE c_no IN (
    SELECT no FROM course WHERE t_no IN (
        SELECT no FROM teacher WHERE department = '计算机系'
    )
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

UNION 和 NOTIN 的使用

查询 计算机系电子工程系 中的不同职称的教师。

-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- 合并两个集
UNION
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
    SELECT profession FROM teacher WHERE department = '计算机系'
);

ANY 表示至少一个 - DESC ( 降序 )

查询课程 3-105 且成绩 <u>至少</u> 高于 3-245score 表。

SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

-- ANY: 符合SQL语句中的任意条件。
-- 也就是说,在 3-105 成绩中,只要有一个大于从 3-245 筛选出来的任意行就符合条件,
-- 最后根据降序查询结果。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
    SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

表示所有的 ALL

查询课程 3-105 且成绩高于 3-245score 表。

-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说,在 3-105 每一行成绩中,都要大于从 3-245 筛选出来全部行才算符合条件。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
    SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+------+-------+--------+

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的 score 表。

-- 查询平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 查询 score 表
SELECT degree FROM score;
+--------+
| degree |
+--------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+--------+

-- 将表 b 作用于表 a 中查询数据
-- score a (b): 将表声明为 a (b),
-- 如此就能用 a.c_no = b.c_no 作为条件执行查询了。
SELECT * FROM score a WHERE degree < (
    (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

子查询 - 4

查询所有任课 ( 在 course 表里有课程 ) 教师的 namedepartment

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+

条件加组筛选

查询 student 表中至少有 2 名男生的 class

-- 查看学生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

-- 只查询性别为男,然后按 class 分组,并限制 class 行大于 1。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

NOTLIKE 模糊查询取反

查询 student 表中不姓 "王" 的同学记录。

-- NOT: 取反
-- LIKE: 模糊查询
mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

YEAR 与 NOW 函数

查询 student 表中每个学生的姓名和年龄。

-- 使用函数 YEAR(NOW()) 计算出当前年份,减去出生年份后得出年龄。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name      | age  |
+-----------+------+
| 曾华      |   42 |
| 匡明      |   44 |
| 王丽      |   43 |
| 李军      |   43 |
| 王芳      |   44 |
| 陆军      |   45 |
| 王尼玛    |   43 |
| 张全蛋    |   44 |
| 赵铁柱    |   45 |
| 张飞      |   45 |
+-----------+------+

MAX 与 MIN 函数

查询 student 表中最大和最小的 birthday 值。

SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+

多段排序

classbirthday 从大到小的顺序查询 student 表。

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 110 | 张飞      | 男  | 1974-06-03 | 95038 |
| 103 | 王丽      | 女  | 1976-01-23 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 | 95033 |
| 107 | 王尼玛    | 男  | 1976-02-20 | 95033 |
| 101 | 曾华      | 男  | 1977-09-01 | 95033 |
| 106 | 陆军      | 男  | 1974-06-03 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 | 95031 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

子查询 - 5

查询 "男" 教师及其所上的课程。

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系统     | 804  |
| 6-166 | 数字电路     | 856  |
+-------+--------------+------+

MAX 函数与子查询

查询最高分同学的 score 表。

-- 找出最高成绩(该查询只能有一个结果)
SELECT MAX(degree) FROM score;

-- 根据上面的条件筛选出所有最高成绩表,
-- 该查询可能有多个结果,假设 degree 值多次符合条件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+

子查询 - 6

查询和 "李军" 同性别的所有同学 name

-- 首先将李军的性别作为条件取出来
SELECT sex FROM student WHERE name = '李军';
+-----+
| sex |
+-----+
| 男  |
+-----+

-- 根据性别查询 name 和 sex
SELECT name, sex FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '李军'
);
+-----------+-----+
| name      | sex |
+-----------+-----+
| 曾华      | 男  |
| 匡明      | 男  |
| 李军      | 男  |
| 陆军      | 男  |
| 王尼玛    | 男  |
| 张全蛋    | 男  |
| 赵铁柱    | 男  |
| 张飞      | 男  |
+-----------+-----+

子查询 - 7

查询和 "李军" 同性别且同班的同学 name

SELECT name, sex, class FROM student WHERE sex = (
    SELECT sex FROM student WHERE name = '李军'
) AND class = (
    SELECT class FROM student WHERE name = '李军'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 曾华      | 男  | 95033 |
| 李军      | 男  | 95033 |
| 王尼玛    | 男  | 95033 |
+-----------+-----+-------+

子查询 - 8

查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。

需要的 "计算机导论" 和性别为 "男" 的编号可以在 coursestudent 表中找到。

SELECT * FROM score WHERE c_no = (
    SELECT no FROM course WHERE name = '计算机导论'
) AND s_no IN (
    SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+

按等级查询

建立一个 grade 表代表学生的成绩等级,并插入数据:

CREATE TABLE grade (
    low INT(3),
    upp INT(3),
    grade char(1)
);

INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

查询所有学生的 s_noc_nograde 列。

思路是,使用区间 ( BETWEEN ) 查询,判断学生的成绩 ( degree ) 在 grade 表的 lowupp 之间。

SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+

连接查询

准备用于测试连接查询的数据:

CREATE DATABASE testJoin;

CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 饭卡      |
|    2 | 建行卡    |
|    3 | 农行卡    |
|    4 | 工商卡    |
|    5 | 邮政卡    |
+------+-----------+

INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id   | name   | cardId |
+------+--------+--------+
|    1 | 张三   |      1 |
|    2 | 李四   |      3 |
|    3 | 王五   |      6 |
+------+--------+--------+

分析两张表发现,person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话,personcardId 字段值为 6 的行就插不进去,因为该 cardId 值在 card 表中并没有。

内连接

要查询这两张表中有关系的数据,可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。

-- INNER JOIN: 表示为内连接,将两张表拼接在一起。
-- on: 表示要执行某个条件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

-- 将 INNER 关键字省略掉,结果也是一样的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;
注意:card 的整张表被连接到了右边。

左外连接

完整显示左边的表 ( person ) ,右边的表如果符合条件就显示,不符合则补 NULL

-- LEFT JOIN 也叫做 LEFT OUTER JOIN,用这两种方式的查询结果是一样的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+

右外链接

完整显示右边的表 ( card ) ,左边的表如果符合条件就显示,不符合则补 NULL

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

全外链接

完整显示两张表的全部数据。

-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误:
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法,使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

事务

在 MySQL 中,事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性

比如我们的银行转账:

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中,假设只有一条 SQL 语句执行成功,而另外一条执行失败了,就会出现数据前后不一致。

因此,在执行多条有关联 SQL 语句时,事务可能会要求这些 SQL 语句要么同时执行成功,要么就都执行失败。

如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中,事务的自动提交状态默认是开启的。

-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自动提交的作用:当我们执行一条 SQL 语句的时候,其产生的效果就会立即体现出来,且不能回滚

什么是回滚?举个例子:

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

可以看到,在执行插入语句后数据立刻生效,原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是,撤销执行过的所有 SQL 语句,使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚:

-- 回滚到最后一次提交
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

由于所有执行过的 SQL 语句都已经被提交过了,所以数据并没有发生回滚。那如何让数据可以发生回滚?

-- 关闭自动提交
SET AUTOCOMMIT = 0;

-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

将自动提交关闭后,测试数据回滚:

INSERT INTO user VALUES (2, 'b', 1000);

-- 关闭 AUTOCOMMIT 后,数据的变化是在一张虚拟的临时数据表中展示,
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

-- 数据表中的真实数据其实还是:
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

-- 由于数据还没有真正提交,可以使用回滚
ROLLBACK;

-- 再次查询
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中?使用 COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
-- 手动提交数据(持久性),
-- 将数据真正提交到数据库中,执行后不能再回滚提交过的数据。
COMMIT;

-- 提交后测试回滚
ROLLBACK;

-- 再次查询(回滚无效了)
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

总结

  1. 自动提交

    • 查看自动提交状态:SELECT @@AUTOCOMMIT
    • 设置自动提交状态:SET AUTOCOMMIT = 0
  2. 手动提交

    @@AUTOCOMMIT = 0 时,使用 COMMIT 命令提交事务。

  3. 事务回滚

    @@AUTOCOMMIT = 0 时,使用 ROLLBACK 命令回滚事务。

事务的实际应用,让我们再回到银行转账项目:

-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';

-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

这时假设在转账时发生了意外,就可以使用 ROLLBACK 回滚到最后一次提交的状态:

-- 假设转账发生了意外,需要回滚。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

这时我们又回到了发生意外之前的状态,也就是说,事务给我们提供了一个可以反悔的机会。假设数据没有发生意外,这时可以手动将数据真正提交到数据表中:COMMIT

手动开启事务 - BEGIN / START TRANSACTION

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后,此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件,使其可以发生回滚:

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交,
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据,提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 提交数据
COMMIT;

-- 测试回滚(无效,因为表的数据已经被提交)
ROLLBACK;

事务的 ACID 特征与使用

事务的四大特征:

  • A 原子性:事务是最小的单位,不可以再分割;
  • C 一致性:要求同一事务中的 SQL 语句,必须保证同时成功或者失败;
  • I 隔离性:事务 1 和 事务 2 之间是具有隔离性的;
  • D 持久性:事务一旦结束 ( COMMIT ) ,就不可以再返回了 ( ROLLBACK ) 。

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 )

  1. READ UNCOMMITTED ( 读取未提交 )

    如果有多个事务,那么任意事务都可以看见其他事务的未提交数据

  2. READ COMMITTED ( 读取已提交 )

    只能读取到其他事务已经提交的数据

  3. REPEATABLE READ ( 可被重复读 )

    如果有多个连接都开启了事务,那么事务之间不能共享数据记录,否则只能共享已提交的记录。

  4. SERIALIZABLE ( 串行化 )

    所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作

查看当前数据库的默认隔离级别:

-- MySQL 8.x, GLOBAL 表示系统级别,不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别,可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别:

-- 设置系统隔离级别,LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别,发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性:

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果,发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的,而该操作的结果是可以被其他事务(另一方的淘宝店)看见的,因此淘宝店的查询结果是正确的,淘宝店确认到账。但就在这时,如果小明在它所处的事务上又执行了 ROLLBACK 命令,会发生什么?

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁,如果再去查询结果就会发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读,一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 READ COMMITTED

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样,再有新的事务连接进来时,它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说,它们看到的还是未提交的数据,例如:

-- 正在操作数据事务(当前事务)
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED,但在当前事务中,
-- 它看到的仍然是数据表中临时改变数据,而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务,连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题,那就是假设一个事务在操作数据时,其他事务干扰了这个事务的数据。例如:

-- 小张在查询数据的时候发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前,小王做了一个操作:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候,就会出现计算不相符合的情况:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据,但还是会出现问题,就是在读取同一个表的数据时,可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED )

幻读

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ,假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后,没提交之前,查询不到,提交后可以被查询到。但是,在提交之前其他事务被开启了,那么在这条事务线上,就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ,在小王这边,都不会查询到小张的事务记录,而是只会查询到自己所处事务的记录:

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ,那么在他的这条新事务的线上,跟其他事务是没有联系的,也就是说,此时如果其他事务正在操作数据,它是不知道的。

然而事实是,在真实的数据表中,小张已经插入了一条数据。但是小王此时并不知道,也插入了同一条数据,会发生什么呢?

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了,操作被告知已存在主键为 6 的字段。这种现象也被称为幻读,一个事务提交的数据,不能被其他事务读取到

串行化

顾名思义,就是所有事务的写入操作全都是串行化的。什么意思?把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例:

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表,准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花,于是插入一条数据:
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢?由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ,串行化的意思就是:假设把所有的事务都放在一个串行的队列中,那么所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释,小王在插入数据时,会出现等待状态,直到小张执行 COMMIT 结束它所处的事务,或者出现等待超时。

推荐

笔者再次墙裂推荐收藏这篇笔记,收录于hjzCy - sql学习笔记,希望对大家有所帮助。

最后如果文章和笔记能带您一丝帮助或者启发,请不要吝啬你的赞和收藏,你的肯定是我前进的最大动力😁

查看原文

yuhehanfeng 收藏了文章 · 2019-10-19

熟悉面试中常见的的 web 安全问题

背景

之前出去面试的时候, 经常会被问到一些安全方面的问题。

安全涉及的领域很大, 我也仅仅是了解一些皮毛, 每次面试前都要找资料复习, 很麻烦。

所以我就根据之前搜集的一些资料和面试的经验,系统的梳理了一下,希望对大家有所帮助。

正文

首先简单介绍几种常见的攻击方式:

  1. SQL注入
  2. XSS
  3. CSRF
  4. 点击劫持
  5. 中间人攻击

1. SQL 注入

这是一种比较简单的攻击方式。

如果后台人员使用用户输入的数据来组装SQL查询语句的时候不做防范, 遇到一些恶意的输入, 最后生成的SQL就会有问题。

image.png

比如地址栏输入的是:

articlrs/index.php?id=1

发送一个get请求, 调用的查询语句是:

sql = "SELECT * FROM articles WHERE id =", $id

正常情况下, 返回 id = 1 的文章。

如果攻击者想获得所有的文章,语句就可以改成:

articlrs/index.php?id=-1 OR 1 = 1

这样就可以了, 为什么呢?

这是因为,id = -1 永远是 false,1=1 永远是true,所有整个where语句永远是ture.

所以 where 条件相当于没有加where条件,那么查询的结果相当于整张表的内容,攻击者就达到了目的。

现在的系统一般都会加入 过滤 和 验证 机制, 可以有效预防SQL注入问题。

2. XSS

XSS 全称是跨站脚本攻击

通过代码注入的方式来达到攻击的目的。

image.png

我们有个社交网站,允许大家相互访问空间,网站可能是这样做的:

<form action="" method="POST">
     <input type="text" name="text">
     <input type="submit" value="submit">input>
 form>
<h2>你输入的内容: {{{text}}}h2>

image.png

如果你用的是Chrome浏览器, 会得到来自浏览器的警告:

image.png

Chrome 这类浏览器能自动帮助用户防御攻击, 很贴心。

但是也不是说, 只要我用Chrome就万事大吉了,该防御, 还得防御。

image.png

对于 XSS 攻击,通常来说,有两种方式可以防御:

  1. 字符转译
  2. CSP(Content Security Policy)

1.字符转译

做法就是转义输入输出的内容,对于引号、尖括号、斜杠等字符进行转义。


& 替换为:&
< 替换为:&lt;
> 替换为:&gt;
” 替换为:"
‘ 替换为:&#x27;
/ 替换为:&#x2f;

通过转义可以将攻击代码 :

<script>alert('1')script>

转译成:


&lt;script&gt;alert(1)&lt;&#x2F;script>

替换了这些字符之后,恶意代码就会失效,XSS 攻击将不会轻易发生。

对于一般的输入, 可以像上面那样粗暴的转译。

有些情况, 光转译也是不够的,比如:


<a href="{{xss}}">点我a>

image.png

链接中如果存在 javacript: 开头的协议,点击链接时浏览器会执行后面的代码。

这个时候光转义是没有用的,需要对 url 协议进行白名控制,只允许 http, https, http, mailto安全协议

包括图片 src 属性 img src="{{xss}}", iframe iframe src="{{xss}}" 都会存在这样的问题,都需要白名单处理

但是别的一些情况, 比如,富文本,这时候就不能这么干了。

如此粗暴的转译会破坏掉原有的格式

这种情况, 比较合适的策略是使用白名单进行过滤标签属性

简单总结一下:

clipboard.png

说完字符转译, 我们再看看CSP。

2.CSP

CSP , Content Security Policy

本质也是白名单,通过设置白名单, 我们可以设置允许浏览器加载哪些外部资源

要开启CSP可以通过两种方式:

  1. 设置 HTTP Header 中的 Content-Security-Policy
  2. 设置 meta 标签的方式

image.png

只要配置了正确的规则,那么即使网站存在漏洞,恶意代码也不会被执行。

CSP的兼容性:

image.png

CSP 文档地址:

https://developer.mozilla.org...

3. CSRF

CSRF 全称是跨站请求伪造( Cross Site Request Forgery)

本质上, 说白了就是借用用户的身份权限偷偷的完成某些操作。

CSRF 的发生其实是借助了 cookie 的特性。

image.png

我们登录了某个 http://tao.com 购物网站之后,cookie 就会有登录过的标记了。

此时请求http://tao.com/pay?id=123&mon..., 是会带着 cookie 的,server 端就知道已经登录了。

如果在http://tao.com去请求其他域名的 API , 例如http://tx.com/api时,是不会带 cookie 的,这是浏览器同源策略的限制。

但是此时在其他域名的页面中,请求http://tao.com/pay?id=123&mon...,就会带着tao.com的 cookie 。

这是发生 CSRF 攻击的理论基础。

如何防御CSRF

防御CSRF 有效的手段就是加入各个层级的权限验证.

例如现在的购物网站,只要涉及现金交易,肯定要输入密码或者扫码验证才行。

除此之外,敏感的接口要使用POST请求而不是GET.

4.点击劫持

click-jacking,也被称为UI-覆盖攻击

这也是比较常问到的一个问题,也是我们最常见的一种情况。

攻击方式就是在某些操作的按钮上加一层透明的iframe

点击一下, 就入坑了。

如何防御点击劫持

常用的两种方式:

1. 使用 HTTP 头防御

通过配置 nginx 发送 X-Frame-Options 响应头.

这个 HTTP 响应头 就是为了防御用 iframe 嵌套的点击劫持攻击。

这样浏览器就会阻止嵌入网页的渲染

该响应头有三个值可选,分别是:

  1. DENY,表示页面不允许通过 iframe 的方式展示。
  2. SAMEORIGIN,表示页面可以在相同域名下通过 iframe 的方式展示。
  3. ALLOW-FROM,表示页面可以在指定来源的 iframe 中展示。

更详细的可以查阅 MDN 上关于 X-Frame-Options 响应头的内容。

2. 使用 Javascript 防御

判断顶层视口的域名是不是和本页面的域名一致,如果不一致就让恶意网页自动跳转到我方的网页。

if (top.location.hostname !== self.location.hostname) {    
    alert("您正在访问不安全的页面,即将跳转到安全页面!")   
    top.location.href = self.location.href;
 }

5. 中间人攻击

中间人攻击(Man-in-the-Middle Attack, MITM)是一种由来已久的网络入侵手段.

如 SMB 会话劫持、DNS 欺骗等攻击都是典型的MITM攻击。

简而言之,所谓的MITM攻击就是通过拦截正常的网络通信数据,并进行数据篡改嗅探来达到攻击的目的,而通信的双方却毫不知情。

如何防御中间人攻击

以下是针对防止中间人攻击的一些建议:

  1. 确保当前你所访问的网站使用了HTTPS
  2. 如果你是一个网站管理员,你应当执行HSTS协议
  3. 不要在公共Wi-Fi上发送敏感数据
  4. 如果你的网站使用了SSL,确保你禁用了不安全的SSL/TLS协议。
  5. 不要点击恶意链接或电子邮件。

还有一个容易被忽略的漏洞

window.opener

带有 target="_blank" 跳转的网页拥有了浏览器 window.opener 对象赋予的对原网页的跳转权限,这可能会被恶意网站利用.

例如一个恶意网站在某 UGC 网站 Po 了其恶意网址,该 UGC 网站用户在新窗口打开页面时,恶意网站利用该漏洞将原 UGC 网站跳转到伪造的钓鱼页面,用户返回到原窗口时可能会忽视浏览器 URL 已发生了变化,伪造页面即可进一步进行钓鱼或其他恶意行为:

代码如下:

<script language="javascript">
  window.opener.location = 'https://example.com'
script> 

想象一下,你在浏览淘宝的时候,点击了网页聊天窗口的一条外链,出去看了一眼,回来之后淘宝网已经变成了另一个域名的高仿网站,而你却未曾发觉,继续买东西把自己的钱直接打到骗子手里。

如何修复

为 target="_blank" 加上 rel="noopener noreferrer" 属性。

<a href="外跳的地址" rel="noopener noreferrer">外跳的地址a>

缺点: 为禁止了跳转带上 referrer,目标网址没办法检测来源地址

还有一种方法是,所有的外部链接都替换为内部的跳转连接服务,点击连接时,先跳到内部地址,再由服务器 redirect 到外部网址。

现在很多站点都是这么做的, 不仅可以规避风险,还可以控制非法站点的打开:

"/redirect?target=http%3A%2F%2Fxxx.yyy.com">
http://xxx.yyy.com

大概就是这么多。

总结

上文介绍了了一些常见的前端安全方面的知识及如何防御这些攻击,应对面试的话,基本上也算够用了。

安全的领域很大,上面我只是粗浅的介绍了一个方面,如果大家对于安全这一块有兴趣的话,可以找一些相关资料继续研究。

文中若有谬误, 还请各位大佬指正。

最后

如果觉得内容有帮助可以关注下我的公众号 「 前端e进阶 」,及时了解最新动态,一起学习!

clipboard.png

查看原文

认证与成就

  • 获得 7 次点赞
  • 获得 8 枚徽章 获得 0 枚金徽章, 获得 2 枚银徽章, 获得 6 枚铜徽章

擅长技能
编辑

(゚∀゚ )
暂时没有

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2017-04-28
个人主页被 397 人浏览