mysql常用

╰半橙微兮° 2022-10-01 05:43 141阅读 0赞

看过很多mysql的博客, 准备敲一遍基础的,记录一下，方便后期查询, 毕竟不经常用，又忘记了

### 数据库 ###

#### 创建数据库 ####

CREATE DATABASE IF NOT EXISTS eggTest
    复制代码

#### 册除数据库 ####

DROP DATABASE test1
    复制代码

#### 选择数据库 ####

USE eggTest
    复制代码

### 数据类型 ###

#### 数值类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYINT</td> 
   <td>1 字节</td> 
   <td>小整数值</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2 字节</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4 字节</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8 字节</td> 
   <td>极大整数值</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td>4 字节</td> 
   <td>单精度浮点数值</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8 字节</td> 
   <td>双精度浮点数值</td> 
  </tr> 
 </tbody> 
</table>

#### 日期类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>格式</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE</td> 
   <td>YYYY-MM-DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

#### 字符串类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>VARCHAR</td> 
   <td>0-65535 字节</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>CHAR</td> 
   <td>0-255字节</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0-255字节</td> 
   <td>不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0-255字节</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>0-65 535字节</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0-65 535字节</td> 
   <td>长文本数据</td> 
  </tr> 
 </tbody> 
</table>

### 表操作 ###

#### 新建一个表 ####

USE eggTest;
    CREATE TABLE IF NOT EXISTS user(
    userId INT(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
    username VARCHAR(10) NOT NULL,
    password VARCHAR(16) NOT NULL,
    realname VARCHAR(10) NOT NULL,
    updataTime  datetime NOT NULL,
    createTime datetime NOT NULL,
    loginTime  datetime NOT NULL,
    PRIMARY key (userId)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21;
    复制代码

新建说明: AUTO\_INCREMENT --为自增起始值

#### 添加表数据 ####

use eggTest;
    INSERT INTO `user` (createTime,updataTime,username,password) values 
    (NOW(),NOW(),'你好世界',123456),
    (NOW(),NOW(),'BB',123456);
    复制代码

#### 删除数据 ####

user eggTest;
    DROP TABLE eggTest;
    复制代码

#### 修改数据 ####

Use eggTest;
    UPDATE `user` SET STATUS = 0, updataTime = NOW() WHERE userId = 21  
    -- 批量修改
    UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') 
    [WHERE Clause]
    复制代码

#### 查询数据 ####

use eggTest;
    SELECT userId,userName FROM user WHERE userId > 0 LIMIT 10 OFFSET 20;
    复制代码

##### 查询where语句 #####

##### LIMIT查找分页 #####

let result = {
      pageinator: {
        pageLength,
        pageNo
      }
    }
    let start = (pageNo - 1) * pageLength
    let count = pageLength
    let sql = `SELECT article.id article_id, test.id test_id From article, test WHERE article.id = test.id AND artivle.status In(1) LIMIT ${start}, ${count}`
    复制代码

##### ORDER BY排序 #####

// 升序(默认) asc 
    let sql1 = `SELECT * FROM user ORDER BY user_id ASC`
    // 降序 desc
    let sql2 = `SELECT * FROM user ORDER BY user_id DESC`
    复制代码

##### Like模糊查询 #####

let sql = `SELECT article.name, test.title FROM article,test WHERE article.id=test.id AND article.name LIKE '%${title}%' AND status IN(1) LIMIT ${start}, ${count}`
    复制代码

##### GROUP分组查询 #####

[参考链接][Link 1]

// 查询各个部门最高薪资
    let sql = `SELECT dept, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT` 
    //将 WHERE 子句与 GROUP BY 子句一起使用
    //分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句
    let sql = `SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM FROM staff WHERE HIREDATE > '2010-01-01' GROUP BY DEPT, EDLEVEL ORDER BY DEPT, EDLEVEL`
    // 可以在ground by 之后使用having，and，or来限定返回分组
    // 此时返回是以组为单位整组过滤
    let sql = `SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM FROM staff GROUP BY DEPT HAVING COUNT( * ) >2 ORDER BY DEPT`
    复制代码

###### WITH ROLLUP 分组 ######

在分组统计的基础上，在进行统计

复制代码

##### join 连接数据 #####

// 内连接 INER join, 是交集关系
    // left join 左表会去不显示，右表没有的字段值为null
    // right join 右表会去不显示，左表没有的字段值为null
    let sql = `SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP`
    复制代码

##### 左联LEFT JOIN与 WHERE查询 #####

左联的理解是，在没有匹配到值的时候也会放回null where是过滤的意思，在没有匹配到值时会过滤掉 看如下代码

let create_table_user = `
    CREATE TABLE IF NOT EXISTS 'user'(
    'id' int(11) NOT NULL AUTO_INCREMENT COMMENT '用户的id',
    'name' varchar(12)  NOT NULL COMMENT '用户名',
    ‘score’ varchar (3) NOT NULL COMMENT '分数'，
    PRMARY KEY ('id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='用户表 AUTO_INCREMENT=28 ;
    `
    let create_table_city =   `
    CREATE TABLE IF NOT EXISTS 'city'(
    'id' int(11) NOT NULL AUTO_INCREMENT COMMENT '城市记录id',
    'user_id' int(11)  NOT NULL COMMENT '用户id',
    ‘city’ varchar (3) COMMENT '用户城市'，
    PRMARY KEY ('id)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='用户城市表' AUTO_INCREMENT=28 ;
    `
    let insert_user = INSERT INTO user ('id','name','score') VALUES (1,'张三'，89)， (2,'李四'，88)， (3,'王五'，99),(4,'赵六'，99)；
    let insert_city = INSERT INTO city ('id','user_id','city') VALUES (1，2，'湖南’)， (2，2，'上海')， (3,1,null)；
    
    left join 查询（此时赵6也会返回,city为null）
    let select_leftJoin = `SELECT id,name,city from user LEFT JOIN city on(user.id = city.user_id)`
    
    where 查询 （此时赵6 不会会返回）
    let select_where= `SELECT id,name,city from user ，city WHERE user.id=city.user_id`
    
    复制代码

##### Mysql中natural join和inner join的区别 #####

nner join----
    `SELECT * FROM TableA INNER JOIN TableB USING (Column1)`
    `SELECT * FROM TableA INNER JOIN TableB ON TableA.Column1 = TableB.Column1`
    natural join--它会去除重复的列名
    SELECT * FROM TableA NATURAL JOIN Table
    复制代码

#### alter修改表字段 ####

需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令

##### 修改表名 #####

ALTER table test rename to test_1
    复制代码

##### 修改列名change 关键字 #####

alter table test change name username vachar;
    复制代码

##### 修改字段类型 modify 关键字 #####

alter table test modify name char(10)
    复制代码

##### 修改默认值 #####

alter table test modify name char NOT NULL defaut 100，
    复制代码

##### 删除 #####

alter table test drop name;
    复制代码

#### 函数 ####

1.  `count(*) / count(1)` 总数在分页查询用的较多
2.  去重`distinct(name)`
3.  IFNULL() 没有返回空，如下取第二高成绩人名 `select IFNULL((select distinct(Salary) from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary`
4.  sum( SALARY ) 求某一列的总和
5.  MAX( SALARY ) 最大值
6.  MIN( SALARY ) 最小值
7.  NOW() 当前日期/2018-09-19 20:57:43
8.  coalesce(a,b,c); // 如果 a = null,则取b的值以此类推

转载于:https://juejin.im/post/5cad83775188251af5250fda

[Link 1]: https://link.juejin.im?target=https%3A%2F%2Fblog.csdn.net%2Fxxpyeippx%2Farticle%2Fdetails%2F8059910