CMD操作数据库

登录

  1. mysql -u root -p

查看所有数据库

  1. show databases;

进入数据库

  1. use test

退出数据库

  1. exit;

CRUD

  • 增加 INSERT
  • 删除 DELETE
  • 修改 UPDATA
  • 查找 SELECT

创建数据库

database 不需要增加s。

  1. create database newdb;

表内插入数据

  1. INSERT INTO pet (id,name) VALUES(1,'小明')
  2. mysql> INSERT INTO pet VALUES('小白',11,null);
  3. Query OK, 1 row affected (0.09 sec)

删除数据

更新删除一定要加where.否则就得跑路了~

  1. mysql> delete from pet where age = 121;
  2. Query OK, 1 row affected (0.08 sec)

修改数据

更新删除一定要加where.否则就得跑路了~

  1. mysql> update pet set name="updata花白" where name="话白";
  2. Query OK, 1 row affected (0.10 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0

查找数据

  1. SELECT * FROM log;

按条件查找数据

  1. select * from log where id = 1;
  2. select id,name from log where;
  3. select id,name from log where id = 1;

查看有多少表

  1. show tables;

创建表

  1. CREATE TABLE pet(name VARCHAR(10),age INT(10),sex CHAR(2));

查看数据表结构

  1. mysql> describe pet;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type        | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | name  | varchar(10) | YES  |     | NULL    |       |
  6. | age   | int(10)     | YES  |     | NULL    |       |
  7. | sex   | char(2)     | YES  |     | NULL    |       |
  8. +-------+-------------+------+-----+---------+-------+

建表约束

  • 主键约束
  • 自增约束
  • 外键约束
  • 唯一约束
  • 非空约束
  • 默认约束

主键约束

。我们通过primary key来设置主键约束。能够唯一确定一张标总的一天=条约束。通过给某个字段增加约束，使该字段不重复且不能空。

  1. create table user (
  2.     id int primary key,
  3.     name varchar(20)
  4. );
  5. -- 插入成功
  6. mysql> insert into user value(1,'小明');
  7. Query OK, 1 row affected (0.01 sec)
  8. -- 插入重复id 报错
  9. mysql> insert into user value(1,'小明1');
  10. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'。
  11. -- 插入null 报错
  12. mysql> insert into user value(null,'小明1');
  13. ERROR 1048 (23000): Column 'id' cannot be null

联合主键

primary key(id,name)的方式，插入联合主键。id和name中只要有一个不同就能插入。当然也不能为空!

  1. create table user2 (
  2.     id int,
  3.     name varchar(20),
  4.     primary key(id,name)
  5. );
  6. -- 插入成功
  7. mysql> insert into user2 values(1,'小明');
  8. Query OK, 1 row affected (0.09 sec)
  9. -- 修改一项插入成功
  10. mysql> insert into user2 values(2,'小明');
  11. Query OK, 1 row affected (0.10 sec)
  12. -- 插入重复项会报错。
  13. mysql> insert into user2 values(1,'小明');
  14. ERROR 1062 (23000): Duplicate entry '1-小明' for key 'PRIMARY'
  15. mysql>

修改表结构

  1. mysql> create table user4(id int,name char);
  2. Query OK, 0 rows affected (0.10 sec)
  3. -- add 添加主键约束
  4. mysql> alter table user4 add primary key(id);
  5. Query OK, 0 rows affected (0.09 sec)
  6. Records: 0  Duplicates: 0  Warnings: 0
  7. -- drop 删除主键约束
  8. mysql> alter table user4 drop primary key;
  9. Query OK, 0 rows affected (0.21 sec)
  10. Records: 0  Duplicates: 0  Warnings: 0
  11. -- modify 修改字段 添加约束
  12. mysql> alter table user4 modify id int primary key;
  13. Query OK, 0 rows affected (0.17 sec)
  14. Records: 0  Duplicates: 0  Warnings: 0

自增约束

自增约束，需要使用关键字auto_increment。顾名思义，就是不需要自己设置，自动增加！

  1. create table user3 (
  2.     id int primary key auto_increment,
  3.     name char(10)
  4. );
  5. -- 单独插入name字段
  6. mysql> insert into user3 (name) values('想');
  7. Query OK, 1 row affected (0.10 sec)
  8. -- 自动增加 自增约束值(id)
  9. mysql> select * from user3;
  10. +----+------+
  11. | id | name |
  12. +----+------+
  13. |  1 | 想   |
  14. +----+------+
  15. 1 row in set (0.00 sec)

唯一约束

add unique约束，该字段值不能重复。

  1. -- 创建1
  2. create table user5 (
  3.     id int ,
  4.     name char(20)
  5. );
  6. add unique 添加唯一约束
  7. -- add 添加
  8. alter table user5 add unique(name);
  9. -- 创建2
  10. create table user6 (
  11.     id int ,
  12.     name char(20),
  13.     unique(id,name)
  14. );
  16. -- 插入成功
  17. mysql> insert into user5 values(1,'1');
  18. Query OK, 1 row affected (0.06 sec)
  19. -- 提示name 重复
  20. mysql> insert into user5 values(2,'1');
  21. ERROR 1062 (23000): Duplicate entry '1' for key 'name'
  22. -- drop index 删除约束
  23. mysql> alter table user5 drop index name;
  24. Query OK, 0 rows affected (0.09 sec)
  25. Records: 0  Duplicates: 0  Warnings: 0
  26. -- modify 添加
  27. mysql> alter table user5 modify name char(10) unique;
  28. Query OK, 1 row affected (0.15 sec)
  29. Records: 1  Duplicates: 0  Warnings: 0

非空约束

使用关键字 not null来定义非空约束!

  1. create table user7 (
  2.     id int ,
  3.     name char(20) not null
  4. );
  5. -- Null 下面name字段，显示不能为null(不能为空)!
  6. +-------+----------+------+-----+---------+-------+
  7. | Field | Type     | Null | Key | Default | Extra |
  8. +-------+----------+------+-----+---------+-------+
  9. | id    | int(11)  | YES  |     | NULL    |       |
  10. | name  | char(20) | NO   |     | NULL    |       |
  11. +-------+----------+------+-----+---------+-------+
  12. 2 rows in set (0.01 sec)

默认约束

当我们插入字段的时候，如果没有传值，就会使用默认值！

  1. create table user8 (
  2.     id int ,
  3.     name char(20) default '我是默认值'
  4. );
  5. -- default 如果不输入，会存在默认值~
  6. mysql> desc user8;
  7. +-------+----------+------+-----+-----------------+-------+
  8. | Field | Type     | Null | Key | Default         | Extra |
  9. +-------+----------+------+-----+-----------------+-------+
  10. | id    | int(11)  | YES  |     | NULL            |       |
  11. | name  | char(20) | YES  |     | 我是默认值      |       |
  12. +-------+----------+------+-----+-----------------+-------+
  13. 2 rows in set (0.00 sec)

外键约束

外键约束涉及到两个表： 父表，子表（主表，副标）；副标需要修改的时候需要参照主表，主表有可以修改，如果主表没有，则不可修改！

  1. -- 班级表
  2. create table class(
  3.     id int primary key,
  4.     name varchar(10)
  5. );
  6. -- 学生表
  7. create table students(
  8.     id int primary key,
  9.     name varchar(10),
  10.     class_id int,
  11.     -- 自己的class_id 引用 class表的id
  12.     foreign key(class_id) references class(id)
  13. );
  14. -- 创建班级
  15. insert into  class values(1,'一班');
  16. insert into  class values(2,'二班');
  17. insert into  class values(3,'三班');
  18. -- 创建学生
  19. insert into students value(1,'小明',1);
  20. insert into students value(2,'小红',2);
  21. insert into students value(3,'小花',3);
  22. -- 插入错误的表，因为class里面id值，没有4这一项；
  23. -- 改案例把id设置成class的name会更明显。列如：查找的时候找不到这个4班的小红；所以报错；
  24. mysql> insert into students value(4,'小红',4);
  25. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`newdb`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
  1. import axios from 'axios'
  2. export default function get(url, params) {  
  3.   return new Promise((resolve, reject) => {     
  4. axios.get('/webapi'+url,{
  5.  params:params
  6.    })
  7.    .then(response => {
  8.     resolve(response.data);
  9.    })
  10.    .catch(err => {
  11.     reject(err)
  12.    })
  13.   });
  14. }
  1. // The Vue build version to load with the `import` command
  2. // (runtime-only or standalone) has been set in webpack.base.conf with an alias.
  3. import Vue from 'vue'
  4. import App from './App'
  5. import $ from 'jquery'
  6. import router from './router'
  7. import html2canvas from 'html2canvas'
  8. import axios from 'axios'
  9. import elementui from 'element-ui'
  10. import VueLazyload from 'vue-lazyload'
  11. import store from './store/store'
  12. import 'element-ui/lib/theme-chalk/index.css'
  13. import 'normalize.css'
  14. import '@/assets/scss/index.scss'
  15. import NProgress from 'nprogress'
  16. import 'nprogress/nprogress.css'
  17. import api from '@/utils/api'
  18. import apiGet from '@/utils/api-get'
  19. import apiDelete from '@/utils/api-delete'
  20. import brief from '@/utils/brief'
  21. import esslogan from '@/utils/esslogan'
  22. import user from '@/utils/user'
  23. import apiJson from '@/utils/api-json'
  24. import apiGet2 from '@/utils/api-get2'
  25. import userGet from '@/utils/user-get'
  26. import briefget from '@/utils/briefget'
  27. import analysisget from  '@/utils/analysisget'
  28. import briefdelete from '@/utils/briefdelete'
  29. import holiday from '@/utils/holiday'
  30. import moment from 'moment'
  31. import echarts from 'echarts'
  32. import 'echarts-gl'
  33. import 'echarts-wordcloud'
  34. axios.defaults.headers.common['X-Requested-With'] = 'XMLHttpRequest';
  35. Vue.use(elementui)
  36. Vue.prototype.$http = axios
  37. Vue.prototype.$html2canvas = html2canvas
  38. Vue.prototype.api = api
  39. Vue.prototype.userGet = userGet
  40. Vue.prototype.apiDelete = apiDelete
  41. Vue.prototype.apiGet = apiGet
  42. Vue.prototype.analysisget = analysisget
  43. Vue.prototype.apiJson = apiJson
  44. Vue.prototype.apiGet2 = apiGet2
  45. Vue.prototype.user = user
  46. Vue.prototype.brief = brief
  47. Vue.prototype.briefdelete = briefdelete
  48. Vue.prototype.briefGet = briefget
  49. Vue.prototype.esslogan = esslogan
  50. Vue.prototype.$echarts = echarts
  51. Vue.prototype.holiday = holiday
  52. Vue.use(VueLazyload, {
  53.   preLoad: 10
  54. })
  55. Vue.config.productionTip = false
  56. router.beforeEach((to, from, next) => {
  57.   var mychildren = document.getElementsByTagName('head')[0].children
  58.   for (const item of mychildren) {
  59.     if (item.src == 'http://statistics.bluefocus.com/js/statistics.js') {
  60.       document.getElementsByTagName('head')[0].removeChild(item)
  61.     }
  62.   }
  63.   let script = document.createElement('script')
  64.   script.type = 'text/javascript'
  65.   script.src = 'http://statistics.bluefocus.com/js/statistics.js'
  66.   document.getElementsByTagName('head')[0].appendChild(script)
  67.   NProgress.start()
  68.   next()
  69. })
  70. router.afterEach(() => {
  71.   NProgress.done()
  72. })
  73. Vue.directive('tgi', {
  74.   bind: function (el, binding) {
  75.     if(binding.value.zhi==Math.max.apply(Math, binding.value.shuzu.map(function(o) {return o.tgi}))){
  76.       el.style.backgroundColor = '#4f622a'
  77.     }else{
  78.       el.style.backgroundColor = '#76923b'
  79.     }
  80.   },
  81.   update: function (el, binding) {
  82.     if(binding.value.zhi==Math.max.apply(Math, binding.value.shuzu.map(function(o) {return o.tgi}))){
  83.       el.style.backgroundColor = '#4f622a'
  84.     }else{
  85.       el.style.backgroundColor = '#76923b'
  86.     }
  87.   }
  88. })
  89. Vue.directive('lzs', {
  90.   bind: function (el, binding) {
  91.     if(binding.value>20||binding.value<-20){
  92.       el.style.backgroundColor = '#215968'
  93.     }else{
  94.       el.style.backgroundColor = '#7ec2d5'
  95.     }
  96.   },
  97.   update: function (el, binding) {
  98.     if(binding.value>20||binding.value<-20){
  99.       el.style.backgroundColor = '#215968'
  100.     }else{
  101.       el.style.backgroundColor = '#7ec2d5'
  102.     }
  103.   }
  104. })
  105. Vue.filter('dateformat', function (dataStr, pattern = 'YYYY-MM-DD HH:mm:ss') {
  106.   if (dataStr == null) {
  107.     return ''
  108.   }
  109.   return moment(dataStr).format(pattern)
  110. }) //时间格式过滤器
  111. // axios.interceptors.request.use(    
  112. //   config => {          
  113. //       return config;    
  114. //   },    
  115. //   error => {        
  116. //       return Promise.error(error);    
  117. // })
  118. // 响应拦截器
  119. axios.interceptors.response.use(
  120.   response => {
  121.     // 如果返回的状态码为200，说明接口请求成功，可以正常拿到数据     
  122.     // 否则的话抛出错误
  123.     if (response.status === 200 && response.data.code != undefined) {
  124.       if (response.data.code != 0 && response.data.code != -10) {
  125.         elementui.Message.error(response.data.msg)
  126.       }
  127.       if (response.data.code == -10 && !(router.currentRoute.name == 'login' || router.currentRoute.name == null || router.currentRoute.name == 'null')) {
  128.         if (router.currentRoute.query.id) {
  129.           router.replace({
  130.             path: '/login',
  131.             query: {
  132.               redirect: router.currentRoute.name,
  133.               id: router.currentRoute.query.id,
  134.             }
  135.           });
  136.         } else {
  137.           if (router.currentRoute.query.businessId) {
  138.             router.replace({
  139.               path: '/login',
  140.               query: {
  141.                 redirect: router.currentRoute.name,
  142.                 businessId:router.currentRoute.query.businessId,
  143.                 name: router.currentRoute.query.name,
  144.                 type:router.currentRoute.query.type,
  145.                 formId:router.currentRoute.query.formId||''
  146.               }
  147.             });
  148.           } else {
  149.             router.replace({
  150.               path: '/login',
  151.               query: {
  152.                 redirect: router.currentRoute.name
  153.               }
  154.             });
  155.           }
  156.         }
  157.       }
  158.       return Promise.resolve(response);
  159.     } else {
  160.       return Promise.reject(response);
  161.     }
  162.   },
  163.   // 服务器状态码不是2开头的的情况 
  164.   // 然后根据返回的状态码进行一些操作，例如登录过期提示，错误提示等
  165.   error => {
  166.     console.log(error)
  167.     if (error.response.status) {
  168.       switch (error.response.status) {
  169.         // 401: 未登录
  170.         // 未登录则跳转登录页面，并携带当前页面的路径
  171.         // 在登录成功后返回当前页面，这一步需要在登录页操作。                
  172.         case 401:
  173.           router.replace({
  174.             path: '/login',
  175.             query: {
  176.               redirect: router.currentRoute.fullPath
  177.             }
  178.           });
  179.           break;
  180.         case 302:
  181.           router.replace({
  182.             path: '/login',
  183.             query: {
  184.               redirect: router.currentRoute.fullPath
  185.             }
  186.           });
  187.           break;
  188.           // 403 token过期
  189.           // 登录过期对用户进行提示
  190.           // 清除本地token和清空vuex中token对象
  191.           // 跳转登录页面                
  192.         case 403:
  193.           elementui.Message({
  194.             message: '登录过期，请重新登录',
  195.             duration: 1000,
  197.           });
  198.           // 跳转登录页面，并将要浏览的页面fullPath传过去，登录成功后跳转需要访问的页面 
  199.           setTimeout(() => {
  200.             router.replace({
  201.               path: '/login',
  202.               query: {
  203.                 redirect: router.currentRoute.fullPath
  204.               }
  205.             });
  206.           }, 1000);
  207.           break;
  208.           // 404请求不存在
  209.          case 502:
  210.             elementui.Message.error(error.response.data.msg)
  211.             break;
  212.         case 404:
  213.           elementui.Message({
  214.             message: '网络请求不存在',
  215.             duration: 1500,
  216.           });
  217.           break;
  218.           // 其他错误，直接抛出错误提示
  219.         default:
  220.           elementui.Message({
  221.             message: error.response.data.msg,
  222.             duration: 1500,
  223.           });
  224.       }
  225.       return Promise.reject(error.response);
  226.     }
  227.   }
  228. );
  230. /* eslint-disable no-new */
  231. new Vue({
  232.   el: '#app',
  233.   store,
  234.   router,
  235.   components: {
  236.     App
  237.   },
  238.   template: '<App/>'
  239. })