Fork me on GitHub

JackLin的博客

阿里云ECS训练营_Day02_MeidaWiki的搭建

  • 2020-08-07
  • 440
  • 阿里巴巴
# 阿里云ECS训练营-Day02-MediaWiki搭建 官网地址:https://www.mediawiki.org/wiki/Download ### 1. 环境搭建 服务器:Centos7 PHP版本:PHP7.4 Apache版本:2.4.43 MySql版本:5.5.62 > 说明 由于 MediaWiki 需要Web服务软件,**PHP7.2.9或更高版本**,同时还需要数据库服务器。 ![](http://image.linkaiblog.top/image-20200807180859554.png) 所以为了方便起见,我们这里直接使用宝塔面板进行安装,这是我的宝塔面板已经安装的软件: ![](http://image.linkaiblog.top/image-20200807190129035.png) ### 2. 下载安装 MediaWiki ##### (1)下载压缩包 进入下载页面(https://www.mediawiki.org/wiki/Download),下载压缩包即可,我这里选择的压缩包版本是 `1.3.42` 下载完之后,按照官网的说法,我们需要将 MediaWiki 解压的 Web服务器的根目录。我们可以在宝塔面板的 `网站` 功能中,创建一个站点,并选择目录为 `/data/wwwroot/default`,这样在外部就可以访问了。**(这样更方便,宝塔面板会帮助我们创建目录结构)** ![](http://image.linkaiblog.top/image-20200807181223792.png) ##### (2) 解压至指定目录 用 xftp 将文件将下载好的压缩包上传至 `/data/wwwroot/default` 然后解压即可。 ```bash tar -zxcf mediawiki-1.34.2.tar.gz ``` 之后再对该根目录赋予权限 ```bash chown -R www /data/wwwroot/default/mediawiki-1.34.2 ``` ![](http://image.linkaiblog.top/image-20200807181615912.png) ##### (3)访问测试 完成上面操作之后,我们可以进入 mediawiki 的根目录,可以看到里面有一个 index.php 文件,这个就是我们要在浏览器中访问的文件。 > 但是,第一次访问出现了下面的问题 ![](http://image.linkaiblog.top/image-20200807125748598.png) 网上查了一些,应该是 php 环境没搭好,还要再安装一个 `fileinfo `的扩展。使用宝塔面板安装完该扩展,**然后在重启 php 服务**,如下所示: ![](http://image.linkaiblog.top/image-20200807125835133.png) 然后刷新页面,如下所示。 ![](http://image.linkaiblog.top/image-20200807155706816.png) 这样就表明你的 MediaWiki 安装好了,后面就是进行配置使用了。 ### 3. 配置使用 MediaWiki ##### (1)选择语言 ![](http://image.linkaiblog.top/image-20200807182148767.png) ##### (2)选择数据库 这个按照自己的喜好选,MySql和SQlite都可以,我这里选择的是 MySql ![](http://image.linkaiblog.top/image-20200807184817138.png) ![](http://image.linkaiblog.top/image-20200807184841951.png) ##### (3)安装配置完成 ![](http://image.linkaiblog.top/image-20200807185424763.png) #####(4)按照提示,将 LocalSettings.php 放在 index.php 同级目录即可 ![](http://image.linkaiblog.top/image-20200807185324314.png) ##### (5)安装配置成功 ![](http://image.linkaiblog.top/image-20200807185424763.png) ------ ok,到这里,MeidaWiki就基本搭建好了!

阿里云ECS训练营_Day01_打造专属云笔记

  • 2020-08-06
  • 515
  • 阿里巴巴
# 阿里云ECS训练营-Day01-打造专属云笔记 ### 前言 **今天是关于使用 VuePress 搭建静态云笔记的,之前对 Node.js 和 Vue 都不太了解,还是尝试去做了。官网文档配合视频简介,对 VuePress 有了一个基本的了解,也复习了一些 Linux 的操作,收获不错。** VuePress官网地址:https://www.vuepress.cn/ ------ > 该系列博客时关于阿里云ESC训练营7天进阶路线对应的博客。 ### 1. 设置安全组 VuePress默认使用8080端口,开放即可 ![](http://image.linkaiblog.top/image-20200806164912644.png) ### 2. 安装Node.js 视频中用的 wget 下载安装包,我这里用的本地下载然后上传到服务器上。 支持yum和npm安装 ---- ##### (1)去 Node.js 官网下载安装包,上传至服务器 压缩包放在 `/tmp` ##### (2)解压 解压到该目录:`/usr/local/lib/nodejs` - 先递归创建目录 ``` mkdir -p /usr/local/lib/nodejs ``` > 问题1 ![](http://image.linkaiblog.top/image-20200806170802207.png) 这个文件是以 `.xz` 为后缀的,而 `z` 参数不适用,应该用 `J` 参数 ![](http://image.linkaiblog.top/image-20200806171014840.png) > 问题2 由于疏忽,未指定目录,应该用 `-C` 指定解压到的目录 ![](http://image.linkaiblog.top/image-20200806171040858.png) ----- 最终的命令: ```bash tar -xJvf node-v12.18.3-linux-x64.tar.xz -C /usr/local/lib/nodejs ``` ##### (3)验证安装是否成功 进入解压后的目录里面的 bin 目录,执行命令 `./node -v`,显示版本号即安装成功 ```bash [root@jacklin bin]# pwd /usr/local/lib/nodejs/node-v12.18.3-linux-x64/bin [root@jacklin bin]# ls node npm npx [root@jacklin bin]# ./node -v v12.18.3 ``` ##### (4)配置环境变量 为了方便,在其他目录下也可以使用node命令,在 `/etc/profile` 后面添加一句 ```bash export PATH=$PATH:/usr/local/lib/nodejs/node-v12.18.3-linux-x64/bin ``` 然后执行 `source /etc/profile`立即生效即可 ### 3. 安装 VuePress ##### (1)使用 npm 安装 官网文档:https://www.vuepress.cn/guide/getting-started.html 这里先使用淘宝的镜像仓库并验证。 ```bash [root@jacklin /]# npm config set registry https://registry.npm.taobao.org [root@jacklin /]# npm config get registry https://registry.npm.taobao.org/ [root@jacklin /]# npm install -g vuepress ``` ![](http://image.linkaiblog.top/image-20200806173614539.png) ##### (2)尝试使用 --> 全局安装 跟着官网走,先简单的尝试一下 ```bash [root@jacklin /]# cd home # 随便进入一个文件夹 [root@jacklin home]# mkdir vuepress-starter # 创建一个项目根目录 [root@jacklin home]# cd vuepress-starter/ [root@jacklin vuepress-starter]# echo 'Hello VuePress ---- By JackLin' > README.md # 编辑文件 [root@jacklin vuepress-starter]# ls README.md [root@jacklin vuepress-starter]# vuepress dev . # 开始写作 wait Extracting site metadata... tip Apply theme @vuepress/theme-default ... tip Apply plugin container (i.e. "vuepress-plugin-container") ... tip Apply plugin @vuepress/register-components (i.e. "@vuepress/plugin-register-components") ... tip Apply plugin @vuepress/active-header-links (i.e. "@vuepress/plugin-active-header-links") ... tip Apply plugin @vuepress/search (i.e. "@vuepress/plugin-search") ... tip Apply plugin @vuepress/nprogress (i.e. "@vuepress/plugin-nprogress") ... ✔ Client Compiled successfully in 7.82s ℹ 「wds」: Project is running at http://0.0.0.0:8080/ ℹ 「wds」: webpack output is served from / ℹ 「wds」: Content not from webpack is served from /home/vuepress-starter/.vuepress/public ℹ 「wds」: 404s will fallback to /index.html success [17:52:33] Build 82f839 finished in 7822 ms! > VuePress dev server listening at http://localhost:8080/ ✔ Client Compiled successfully in 224.24ms success [17:52:33] Build a3f338 finished in 225 ms! ( http://localhost:8080/ ) ``` 注意:此时服务正在运行,进入浏览器查看,可以查看到内容如下所示: ![](http://image.linkaiblog.top/image-20200806181952100.png) 此时的文件目录是这样的: ```bash [root@jacklin vuepress-starter]# ls -al total 12 drwxr-xr-x 2 root root 4096 Aug 6 18:12 . drwxr-xr-x. 13 root root 4096 Aug 6 18:12 .. -rw-r--r-- 1 root root 33 Aug 6 18:12 README.md ``` 然后执行 `vuepress build .` 构建静态文件之后的文件目录是这样的;这个 .vuepress目录是隐藏目录 ```bash [root@jacklin vuepress-starter]# ls -al total 16 drwxr-xr-x 3 root root 4096 Aug 6 18:23 . drwxr-xr-x. 13 root root 4096 Aug 6 18:12 .. -rw-r--r-- 1 root root 33 Aug 6 18:12 README.md drwxr-xr-x 3 root root 4096 Aug 6 18:23 .vuepress [root@jacklin vuepress-starter]# cd .vuepress/ [root@jacklin .vuepress]# ls -al total 12 drwxr-xr-x 3 root root 4096 Aug 6 18:23 . drwxr-xr-x 3 root root 4096 Aug 6 18:23 .. drwxr-xr-x 3 root root 4096 Aug 6 18:23 dist [root@jacklin .vuepress]# cd dist [root@jacklin dist]# ls 404.html assets index.html [root@jacklin dist]# cd assets [root@jacklin assets]# ls css img js ``` ##### (3) 现有项目 ---> 深入了解 刚才只是简单的尝试了一下,现在是在项目中使用 VuePress,用来管理文档。 按照官网的说法,应将 VuePress 安装为本地依赖。那就来呗! 从官网截的目录结构图:**约定优于配置**,所有要遵守他的目录结构 ![](http://image.linkaiblog.top/image-20200806184815522.png) 还有一个是视频中的截图,我们就按照这个目录结构来搭建基本结构吧。 ![](http://image.linkaiblog.top/image-20200806180527253.png) - 创建另一个根目录 ```bash mkdir vuepress-starter2 ``` - 初始化项目 一开始对 Node.js 不是很了解,看了群里大佬们的文章才知道用 `npm init ` 来初始化项目 ```bash npm init -y ``` - 之后再 package.json 中加入下面脚本 ```json { "scripts": { "docs:dev": "vuepress dev docs", "docs:build": "vuepress build docs" } } ``` ![](http://image.linkaiblog.top/image-20200806184608250.png) - 接着创建 docs 目录 ```bash mkdir docs ``` - 在 docs 目录下新建 Markdown 文件 ``` echo '# VuePress 现有项目 ---- by JackLin' > README.md ``` - 在 docs 下面创建 .vuepress 目录,再在 .vuepress 里面创建 public 目录和 config.js 文件 - 退到根目录,执行 `vuepress dev docs`,然后在浏览器访问 ![](http://image.linkaiblog.top/image-20200806190117843.png) 至于后面的如何具体配置,官网应该写了,网上也有文章,我就不重复了。

Summer Study Programme

  • 2020-07-26
  • 422
  • 生活
#### This is my Summer Study Programme,from 2020.7.26 to 2020.9.4! # 7.25 ![](http://image.linkaiblog.top/e87465952e67a60e5acf0c181fb1648.jpg) # 7.26 ![](http://image.linkaiblog.top/2ba9e204bc28d9cc6d909387bc577a0.jpg) ![](http://image.linkaiblog.top/a319795184ca1e339c35271548ba9ab.jpg) # 7.27 ------- . ``` Thanks!!! You raise me up so I can stand on mountains You raise me up to walk on stormy seas I am strong when I am on your shoulders You raise me up To more than I can be! ``` ![](http://image.linkaiblog.top/8c162c25b87ab814731c3d75b634e47.jpg) ------ ![](http://image.linkaiblog.top/78e7ca32608e3158c7bf7ba577263d5.jpg) # 7.28 Happy Birthday ![](http://image.linkaiblog.top/a467be38cf67a6b7dd2f4e7c4edd87d.jpg) ------ ![](http://image.linkaiblog.top/80bf8d4b1b152ee84698aa08e7065cb.jpg) # 7.29 ![](http://image.linkaiblog.top/696e25566376ebc875716727e9fd272.jpg) # 7.30 ![](http://image.linkaiblog.top/132b2a4b88766f93fb1fa65a7356b3d.jpg) # 7.31 ![](http://image.linkaiblog.top/d980e71bc2d8e94f5c40607d1ebee5d.jpg) ---- ![](http://image.linkaiblog.top/c8974d085290b99cc2a150c20a1fff3.jpg)

Centos7安装Zookeeper

  • 2020-07-19
  • 178
  • SpringCloud相关
# Cnetos7上Zookeeper 的安装 ##### 环境说明 Centos7 + Zookeeper 3.4.14 经过测试,安装高版本的 Zookeeper 可能是启动失败,所以选择了一个较低版本的安装 ----- ### 官网下载安装包 官网地址:http://apache.communilink.net/zookeeper/ 选择 3.4.14 版本下载即可 ### 将压缩包上传到 Linux 服务器 用 xftp 上传到 Linux 服务器,存放路径为:`/home/zookeeper/zookeeper-3.4.14` 目录可以自己选择。 ### 更改系统配置文件 - 在 /etc/profile 中添加关于 Zookeeper 的配置文件 ```bash vim /etc/profile ``` 按 i 进入编辑模式,在文件末尾加上下面内容 ```bash # 路径要换成自己的路径 export ZOOKEEPER_HOME=/home/zookeeper/zookeeper-3.4.14 export PATH=$PATH:$ZOOKEEPER_HOME/bin:${JAVA_HOME}/bin ``` 按 Esc 退出编辑模式,输入wq保存退出 ![](http://image.linkaiblog.top/image-20200719183314627.png) ### 更改 Zookeeper 的配置文件 - 进入 Zookeeper 解压缩之后的根目录,创建2个文件夹 ```bash mkdir dataDir mkdir dataLogDir ``` ![](http://image.linkaiblog.top/image-20200719183558307.png) - 进入解压缩后面的根目录的 conf 目录,复制一份配置文件名并更改文件名 ```bash cp zoo_sample.cfg zoo.cfg ``` ![](http://image.linkaiblog.top/image-20200719183722484.png) - 编辑拷贝的 zoo.cfg,将里面的 dataDir 的值修改为我们刚才创建的文件夹 dataDir的路径;同时添加 dataLogDir 的值为 刚才创建文件夹 dataLogDir 的路径 ![](http://image.linkaiblog.top/image-20200719183948180.png) - 进入 bin 目录,启动 Zookeeper ```bash ./zkServer.sh start ``` ![](http://image.linkaiblog.top/image-20200719184158910.png) - 停止服务 ```bash ./zkServer.sh stop ```

英语大作业期末视频文稿

  • 2020-07-02
  • 234
  • 实验报告
## 介绍 ​ Hi,My Name is JackLin,I'm from class 1809, software engineering. This is my powerpoint, my team's topic is `Technology in the field of electronic commerce`. ​ All right, let's start with my speech. ## 电子商务领域技术简介 ​ In order to better understand the `Technology in the field of electronic commerce`, I first to introduce what is the electronic commerce. ​ (为了更好的理解电子商务领域技术,我首先介绍一下什么是电子商务) ----- ​ **Electronic Commerce is the use of computer technology, network technology and remote communication technology to achieve the whole business (business) process of electronic, digital and networked. The value of e-commerce is to let consumers buy and pay online through the network, which saves time and space between customers and enterprises, greatly improves transaction efficiency, especially for busy workers, and also saves precious time. In the 21st century when consumer information is diversified, it has become a habit for consumers to know the commodity information of local shopping malls through network channels such as Baidu Weishou, Taobao, New Eggs and so on, and then enjoy on-site shopping. E-commerce is the direct product of the explosive development of the Internet and a new development direction of the application of network technology.** ----- ​ The `Technology in the field of electronic commerce` can not leave the Internet, it has a close relationship with the Internet. ----- ​ **Internet itself has the characteristics of openness, globality, low cost and high efficiency. It has also become the intrinsic feature of e-commerce, and has greatly exceeded the value of e-commerce as a new form of trade. It will not only change the production, operation and management activities of enterprises themselves, but also affect the economic operation and structure of the whole society. The "electronic" technology platform based on the Internet provides a vast space for the development of traditional business activities. Its outstanding advantages are incomparable with traditional media means.** ## 目录 ​ OK, this is the contents of PPT. I will talk about e-commerce technology from the following eight chapters. ---- ## Chapter 01 Defination ​ The Chapter 01, is about the defination of `electronic commerce`. Here, I will give a detailed definition of e-commerce. ​ **E-commerce refers to business activities that use information network technology as the center and commodity exchange as the center; it can also be understood as the activities of conducting transaction activities and related services through electronic transactions on the Internet, intranets and value-added networks, and is traditional business activities. Electronic, network, and informatization of all links; business activities using the Internet as a medium all belong to the category of e-commerce.** ---- ### 1.1 Summary——小结 ​ Well, here, I will make a brief summary of the definition of `electornic-commerce`. ​ **With the rapid development of the Internet, the surging websites have turned to pragmatism one after another after they have fully exploited the concept. One of the distinctive features of pragmatism is that most websites are trying to do real e-commerce. The two most commonly used services in e-commerce are Web services and e-mail services.** ### 1.2 Constute—— 组成 ​ Electronic Commerce mainly consists of the following two parts. ​ First, **The so-called technology of electronic commerce is to use computer technology, network technology and remote communication technology to realize the electronic, digital and network in the whole business process.** ​ And the Second, **People are no longer face-to-face, looking at real goods, trading on paper documents (including cash). But through the network, we can browse a wide range of commodity information, perfect logistics distribution system and convenient and safe fund settlement system for trading (buying and selling).** ---- ## Chapter 02 History ​ The next Chapter, Chapter 02 History. It's about the history about `electronic-commerce` . ​ **Research on e-commerce in the world began in the late 1970s.** **The implementation of e-commerce can be divided into two steps,** **of which EDI commerce started in the mid-1980s and Internet commerce started in the early 1990s.**   **Generally speaking,** **e-commerce has experienced two stages of development:** **(1) EDI-based e-commerce (1960s to 1990s).** **(2) E-commerce based on the Internet (from the 1990s to the present)** ----- ### 2.1 EDI-based e-commerce 2.2 based on the Internet ​ The development of e-commerce has experienced the following two stages. ​ The first stage, is the EDI-based `electronic commerce`. ​ **EDI was produced in the United States in the late 1960s. When traders used computers to process various business documents, they found that 70% of the data manually input into one computer was derived from the output file of another computer. Due to excessive human factors, which affected the accuracy of data and the improvement of work efficiency, people began to try to automatically convert the data on the computers between trading partners, and EDI came into being.** ​ The second stage, is the based on the Internet. ​ **After the mid-1990s, the Internet became rapidly popularized, gradually moving from universities and scientific research institutions to enterprises and people’s families, and its functions have evolved from information sharing to Popular information dissemination.** ## Chapter 03 ---- Characteristic ​ `Electronic-commerce` has the following three characteristics. Wider Enviroment, Broader Market, Circulation and Price. ​ The frist characteristic, Wider Enviroment. ​ **People are not limited by time, space and traditional shopping. They can trade online anytime and anywhere. Through spanning time and space, we can reach more customers in a specific time, and provide us with a broader development environment.** ​ The Second Characteristic, Broader market. ​ **On the Internet, the world will become very small, a business can face the global consumers, and a consumer can shop in any business in the world. A merchant can challenge different regions and different types of buyer customer groups, and collect abundant buyer information on the Internet for data analysis.** ​ The last characteristic of `electronic-commerce`, Circulation and Price. ​ **E-commerce reduces the intermediate link of commodity circulation, saves a lot of expenses, and thus greatly reduces the cost of commodity circulation and transaction. Through e-commerce, enterprises can match buyers more quickly, realize real integration of production, supply and marketing, save resources and reduce unnecessary production waste.** ------ ## Chapter 04 ---Examples ​ Ok, this is the chapter 04, I will give some examples about `electronic commerce`. ​ Online shopping, Mobile phone order electronic newspaper, International tourism and travel service . Web workstations and work outlets, and Manufacturers of computer, network, data communication software and hardware. ​ First, the online shopping. ​ **With the rapid development of the Internet, people's lives and transactions have become simple, and a new dao-style shopping method has entered our lives. This is online shopping. Online shopping is to retrieve product information through the Internet. And send the demand through the electronic order, then fill in the information, the manufacturer sends the goods according to your information. Shopping sites such as Taobao, JD, and Amazon are all types of online shopping.** ​ Second, services. ​ **industries of various countries, such as hotel, restaurant, airport, station booking, rooms booking, information release and other services.** ## Chapter05 Technology involved ​ The next chapter is chapter 05, This chapter is about some of the technologies involved in `electronic-commerce`. ​ Here are six major technologies, which are network technique, Web browsing technology, Safety technology, Database Technology, Electronic Payment Technology, and the Classification. ---- ## Chapter06--- Technical requirements ​ This chapter is about technical requirements of `elecronic-commerce`. ​ The first technical requiremet is safety. ​ **To ensure the security of data transmission is to ensure that data information transmitted on Internet is not monitored and stolen by third parties. Generally, the protection of data information security is achieved by using data encryption technology.** ​ The second technical requriement is data integrity. ​ **To ensure the integrity of data is to ensure that the data information transmitted on Intemet is not tampered with. In e-commerce application environment, data integrity is guaranteed by using secure hash function (Hash function, also known as hash function) and digital signature technology.** ​ Then, the next technical requirement is identy authentication. ​ **In e-commerce activities, the two or more parties often need to exchange some sensitive information (such as credit card number, password, etc.). At this time, they need to confirm the real identity of the other party. If Payment-based e-commerce is involved, it is also necessary to confirm whether the other party's account is genuine and valid. Identity authentication in e-commerce usually uses public key encryption technology, digital signature technology, digital certificate technology and password technology.** ​ The last technical requirement is Non-repudiation. ​ **When transmitting data and information in electronic commerce, each party must carry its own unique information that cannot be duplicated by others, so as to prevent the sender from denying that the message has been sent or the receiver from denying that the information has been received, so as to ensure that the transaction is recorded when it occurs. Non-repudiation of transactions is achieved by digital signature technology and digital certificate technology.** ## Chapter 07 Advantage and Disadvantage ​ In this chapter,I will introduce the advantage and disadvantage of `elecronic-commerce`. ​ First of all, the advantage of `electronic-commerce`. ​ **electronic commerce digitizes and digitizes traditional business processes.On the one hand, electronic flow replaces physical logistics, which can greatly reduce manpower, material resources, and cost. On the other hand, it breaks through the limitations of time and space, making transaction activities possible. It is carried out at any time and any place, which greatly improves efficiency.** ​ 。。。。。。(还有5点,看PPT念就ok) ------ ​ However,there are some disadvantage in `electronic-commerce`. ​ The network itself has limitations: in this mode, only rely on the production of the website and the web designer to grasp the web page better model, to show consumers the goods. ​ 。。。。。。(还有5点,看PPT念就ok) ## Chapter 08 Futuer Development ​ 照着PPT念 ​ Ok, that's all for my powerpoint. Thanks

数据库期末复习

  • 2020-06-22
  • 279
  • 数据库设计
前言:关于数据库的期末复习,侧重点在代码,存储函数,存储过程,触发器等等。 > SQL语句 ``` -- 在语法中:[] 表示可选 <> 表示必选 -- 在进行很多操作时,我们是不知道表名的,所以在书写语句时需要限定一下表名或者列名,而存储函数和存储过程是依赖于数据库的,所以不需要指定表名 -- 所有的代码是伪代码,只是为了方便期末复习查看,并不应能执行成功。 -- 一、第1部分:数据库,表,视图,索引,存储函数,存储过程,约束,触发器 -- 1. 创建删除数据库 create database 数据库名; drop database 数据库名; -- 2. 创建,修改,删除表; 后面 4 行代码是 MySQL 提供的约束,将在第 7 点中详细解释,这是是在创建表的同时增加约束,后面将在创建表结束之后修改约束。 -- 代码都是伪代码,方便记忆,所以将多种情况融在了一起。 create table student ( number varchar(60) primary key, sname varchar(60) not null unique, gender varchar(60), age int(10) default 0, time timestamp default current_timestamp, -- 1. 主键约束 constraint 约束名称 primary key(number, sname), -- 2. 唯一约束 constraint 约束名称 unique(gender), -- 3. 检查约束 constraint 约束名册 check(age > 20 and age < 60), -- 4. 外键约束(创建外键约束时,可以不需要前面的 constraint 约束名称 也可以正常运行) constraint 约束名称 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade ); -- 或者 2.1 create table 表名 select_语句; -- 2.2 修改表结构 add, modify, drop alter table 表名 add 列名 varchar(60) [default 'test']; alter table 表名 modify 列名 varchar(60) [default 'test']; alter table 表名 drop 列名 -- 2.3 截断表 truncate table 表名 -- 2.4 删除表 drop table 表名 -- 3. 创建索引 create index 索引名称 on 表名(属性名); drop index 索引名称 on 表名; -- 4. 创建视图,修改视图,删除视图; -- with check option 表示说插入或修改的数据行必须满足视图所定义的约束条件 create [or replace] view 视图名称 [别名] as sql语句 [with check option]; alter view 视图名称 [别名] as select_语句; drop view 视图名称; -------------------------------------------------- -- 5. 存储函数的创建 create function 函数名([参数名 参数类型]) returns 返回值类型 begin 函数体; return 语句; end; -- 5.1 调用存储函数 select 函数名([参数值]); -- 5.2 删除存储函数,后面不需要带括号 drop function 函数名称; -------------------------------------------------- -- 5. 存储函数举例, 使用 declare 创建变量,使用 set 为变量赋值 -- 5.1 创建存储函数 -- delimiter@@ -- create function name_fn(dno decimal(2), a decimal(5,2), b decimal(5,2)) -- returns varchar(14) -- begin -- declare x decimal(5,2); -- declare y decimal(5,2); -- set x = a, y = b; -- return x+y; -- end@@ delimiter@@ create function name_fn(a decimal(5,2), b decimal(5,2)) returns decimal begin declare x decimal(5,2); declare y decimal(5,2); set x = a, y = b; return x+y; end@@ deleimter; set global log_bin_trust_function_creators=TRUE; -- 5.2 调用存储函数 select name_fno(1, 2); -- 5.3 删除存储函数 drop function name_fn; ----------------------------------------------------- -- 6. 创建存储过程 create procedure 存储过程名( [in | out | inout] 参数名 参数类型, [in | out | inout] 参数名 参数类型 ) begin 过程体; end -- 6.1 调用存储过程 call procedure 存储过程名(); drop procedure 存储过程名; ------------------------------------------------------- -- 6. 存储过程举例, select ... into ... 给变量赋值,结合 out 参数使用 -- 关于 in, out,inout 更详细的例子可以查看课本 P88 delimiter @@ create procedure process_name( in i_1 decimal(2, 0), out o_1 varchar(14), out o_2 varchar(14) ) begin select test_1, test_2 into o_1, o_2 from test_table where test_3 = i_1; end@@ delimiter; call process_name(10, @p_1, @p_2); select @p_1, @p_2; select concat('MySql版本信息:', @@version); --------------------------------------------------------- -- 7. 约束,需要使用 constraint 关键字 -- 这里介绍 4 中约束,在创建表的时候增加约束已经在第 1 点中详细介绍了,这里只介绍在创建表之后如何建立约束 -- 7.1 创建表之后,删除,增加主键约束 alter table 表名 drop primary key; -- 删除主键约束,主键只有一个,所以不需要指定主键名称 alter table 表名 add constraint 主键名称 primary key(列名); -- 增加主键约束 -- 7.2 创建表之后,删除,增加唯一约束 alter table 表名 drop index 约束名称; -- 删除 “唯一约束” , 注意:这里使用的是 index,而且需要指定约束名称; alter table 表名 add constraint 约束名称 unique(列名); -- 增加 “唯一约束” -- 7.3 创建表之后,删除,增加外键约束 alter table 表名 drop foreign kye 外键约束名称; -- 删除外键约束,需要指定约束名称 alter table 表名 add constraint 约束名册 foreign key(本表中的列名) references 依赖的表名(依赖的表中的列名) on update cascade; -- 增加外键约束 --------------------------------------------------------- -- 8. 触发器 create trigger 触发器名称 before | after insert | delete | update on 表名 for each row <触发体>; -- 8.1 删除触发器 drop trigger 触发器名册; -- 触发器是数据库级别的,所以这里不需要指定表名 --------------------------------------------------------- -- 8. 触发器举例,配合 new,old 使用 create trigger test_trigger after update on department for each row update employee set deptno = new.deptno where deptno = old.deptno; -- 二、第2部分:用户,权限管理,数据的备份与恢复 -- 1. 用户管理:创建用户,修改密码,修改用户名 -- 用户 == 用户名@主机名 grant user 用户名@主机名 identified by 'password'; set password for 用户 = '新密码'; rename user 旧用户 to 新用户; drop user 用户; ----------------------------------------------------------- -- 2. 权限管理; 后面可以加上 with grant option -- 2.1 授予 "MySql 字段级别" 的权限:由于是 “字段级别”,所以要指定数据库名,表名,列名 grant 权限名称(列名) on table 数据库名.表名 to 用户 [with grant option] -- 2.1 实例 grant select(ename, sal), update(sal) on table scott1.emp to test_usr@localhost with grant option; ----------------------------------------------------------- -- 2.2 授予 “MySql 表级别” 的权限:由于是 “表级别”,所有只需要指定数据库名,表名 grant 权限名称 on table 数据库名.表名 to 用户 [with grant option] -- 2.2 实例 grant alter, select, insert(empno, ename) on table scott1.emp to test_usr@localhost with grant option; ------------------------------------------------------------ -- 2.3 授予 “MySql 存储程序级别” 的权限“由于是存储过程,存储函数级别的权限,所以不需要指定表名,只需要执行数据库名,存储函数名/存储过程名 grant 权限名称 on function | references 数据库名.存储函数名/存储过程名 to 用户 with grant option; -- 2.3 实例 grant execute on procedure scott1.emp_p to test_usr@localhost with grant option; ----------------------------------------------------------- -- 2.4 授予 “MySql 数据库级别” 的权限:因为是数据库级别,所有范围直接写成 数据库名.* grant 权限名称 on 数据库名.* to 用户 with grant option; -- 2.5 授予 “MySql 服务器管理” -------------------------------------------------------------------- grant 权限名册 on *.* to 用户 with grant option; -- 2.6 撤销所有权限 revoke all privileges, grant option from 用户; -- 2.6.1 撤销指定权限 revoke 权限名称 on *.* | 数据库名.* | 数据库名.表名 from 用户; -- 3. 角色管理; 角色格式:'角色名'@'主机名' ;这个角色的格式和用户的格式十分相似 create role 角色; -- 3.1 授予角色权限:语法和授予用户权限的语法一样 -- 3.2 授予角色给用户 grant 角色 to 用户; -- 3.3 撤销用户角色 drop role 角色; -- 4. 数据的备份与恢复:这些命令不能写到查询里面,而是要在控制台的命令行中输入并执行。 -- 如果通过 -p 指定了密码,则可以直接执行;如果没有指定密码,则后面需要输入密码 -- 4.1 备份单个数据库 mysqldump -u 用户名 -h 主机名 -p 密码 数据库名>备份文件名.sql -- 4.2 备份多个数据库 mysqldump -u 用户名 -h 主机名 -p 密码 --database 数据库名 数据库名>备份文件名.sql -- 4.3 备份所有数据库 mysqldump -u 用户名 -h 主机名 -p 密码 --all -database>备份文件名.sql -- 4.4 恢复shuj1 mysql -u 用户名 -p 数据库名<备份文件名.sql -- 之后会要求输入密码 -- 5. 表数据的导入和导出 mysqldump -u root -p -T '目标路径' 数据库名 表名 -- “目标路径” 可以通过下面语句查询得到 select @@secure_file_priv; ```

JackLin的博客

Talk is cheap, show me the code!

jacklin.it@qq.com

NanChang

文章统计

  • 文章数量

  • 分类数量

  • 标签数量