mysql命令(command)

Dear 丶 2023-10-06 18:55 1阅读 0赞

#### MySQL命令（command） ####

*  金句
 *  连接命令
 *  Command
 *   *  创建表
     *  Cannot add foreign key constraint 错误解决办法
     *  查询例句
     *   *  Mysql 中的 WITH ROLLUP 用法
     *  插入数据
     *  更新数据
     *  删除数据

## 金句 ##

无论从事什么行业，只要做好两件事就够了，一个是你的专业、一个是你的人品，专业决定了你的存在，人品决定了你的人脉，剩下的就是坚持，用善良专业和真诚赢取更多的信任。

## 连接命令 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]

## Command ##

CREATE DATABASE 数据库名；创建数据库  
  
 SHOW DATABASES；查看所有数据库  
  
 DROP DATABASE 数据库名；删除数据库  
  
 SHOW ENGINES；查看MySQL支持的数据库引擎  
  
 SHOW VARIABLES；查看所有配置  
  
 SHOW VRRIABLES LIKE ‘%xxx%’；模糊匹配查询配置  
  
 SHOW VARIABLES LIKE ‘storage\_engine’；查询默认存储引擎  
  
 SHOW TABLE STATUS LIKE ‘表名或者视图名’；查看表或者视图的数据信息，包括存储引擎，总数据量，大小，时间等…  
  
 SET GLOBAL|SESSION 配置名=配置值；修改配置  
  
 LOCK TABLES ‘表名’ WRITE；锁表  
  
 UNLOCK TABLES；解除锁表

DESC 表名；查看表的定义  
  
 SHOW CREATE TABLE 表名；查看表的详解结构，即SQL语句结构  
  
 ALTER TABLE 旧表名 RENAME 新表名；修改表名称  
  
 ALTER TABLE 表名 MODIFY 属性名 数据类型；修改字段的数据属性  
  
 ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型；修改字段名，如果不想改变数据类型，请填写之前一样的，不能不写哦  
  
 ALTER TABLE 表名 ADD 属性名1 数据类型 \[完整性约束条件\] \[FIRST|AFTER 属性名2\]；增加字段 注意：FIRST指第一个字段，不要当成了BEFORE\[虽然没有\]  
  
 ALTER TABLE 表名 DROP 属性名；删除字段  
  
 ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2；修改字段的排列位置  
  
 ALTER TABLE 表名 ENGINE=存储引擎名；更改表的存储引擎  
  
 ALTER TABLE 表名 DROP FOREIGN KEY 外键别名；删除表的外键约束  
  
 DROP TABLE 表名；删除没有被关联的普通表，如果要删除被外键关联的表，请先删除子表中的外键约束，这样可以保证子表的数据不会丢失

--------------------

`show ENGINES;`  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]

### 创建表 ###

CREATE TABLE 表名(属性名 属性类型 \[完整性约束条件\]…)；创建表

DROP TABLE IF EXISTS `settlement`;
    
    CREATE TABLE `settlement`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uid` INT UNSIGNED DEFAULT 0 COMMENT '用户ID',
    `sid` INT UNSIGNED NOT NULL UNIQUE COMMENT 'XXX',
    `status` ENUM('0','1') DEFAULT '0' COMMENT '状态',
    `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',PRIMARY KEY (`id`,`uid`),
     CONSTRAINT `f_ck` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='用户表';

PRIMARY KEY(属性名1,属性名2,......,属性名N) 　　多字段主键
    
    CONSTRAINT 外键名 FOREIGN KEY (外键字段,...) REFERENCES 父表名 (父表字段)【 ON DELETE CASCADE ON UPDATE CASCADE】 建立外键
    
    NOT NULL 非空约束
    
    UNIQUE 唯一约束
    
    AUTO_INCREMENT 自动增加属性，一个表中只能一个字段使用AUTO_INCREMENT,默认从1开始

### Cannot add foreign key constraint 错误解决办法 ###

1，两张表里要设主键和外键的字段的数据类型或者数据长度不一样 （例如这个是int 另外一个是tinyint，或者都是int，但是设置的长度不同）

2，某个表里已经有记录了

3、两个表的引擎不一样，查看表的引擎语句：

show table status from 数据库名 where name=‘表名’;

4、要设置外键的字段不能为主键

5、改建所参考的字段必须为主键

6、两个字段必须具有相同的数据类型和约束

我遇到的情况就是4、5。后面关联的表的字段必须被设置为主键，才能关联成功。

### 查询例句 ###

SELECT name,age,sex FROM user;
    
    SELECT * FROM user;
    
    SELECT * FROM user WHERE id=1001;
    
    SELECT * FROM user WHERE id NOT IN (1,2,3);
    
    SELECT * FROM user WHERE age NOT BETWEEN 20 AND 25;
    
    SELECT * FROM user WHERE name NOT LIKE "%zong_huai%";
    
    SELECT * FROM user WHERE name IS NULL;
    
    SELECT * FROM user WHERE name IS NOUT NULL;
    
    SELECT * FROM user WHERE id>10 OR age<20 AND name IS NOT NULL;
    
    SELECT DISTINCT age FROM user;
    
    SELECT * FROM user ORDER BY age DESC,name ASC;
    
    SELECT sex,GROUP_CONCAT(name)FROM user GROUP BY sex;
    
    SELECT sex,COUNT(sex) FROM user GROUP BY sex;
    
    SELECT sex,COUNT(sex) FROM user GROUP BY sex HAVING COUNT(sex)>3;
    
    SELECT sex,COUNT(sex) FROM user GROUP BY sex WITH ROLLUP;
    
    SELECT * FROM user GROUP BY sex,name;
    
    SELECT * FROM user LIMIT 2;
    
    SELECT * FROM user LIMIT 12,2;
    
    SELECT COUNT(*) FROM user;
    
    SELECT id,SUM(score) FROM grade WHERE id=1001;
    
    SELECT AVG(age) FROM user;
    SELECT MAX(age) FROM user;
    SELECT MIN(age) FROM user;
    
    内连接只取符合条件的记录，不匹配的将过滤掉，这一点是和左连接是有区别的，特别注意
    SELECT id,name,sex.sname FROM index1,sex WHERE index1.sex = sex.sid
    
    左连接查询可以查询出表1的所有数据，
    表2只能查询出匹配的数据，如果表1的数据在表2中没有匹配的字段将显示为空
    SELECT * FROM index1 LEFT  JOIN sex ON index1.sex=sex.sid;
    
    右连接查询可以查询出表2的所有数据，
    表1只能查询出匹配的数据，如果表2的数据在表1中没有匹配的字段将显示为空
    SELECT * FROM index1 right JOIN sex ON index1.sex=sex.sid;
    
    SELECT * FROM user WHERE id IN (SELECT id FROM grade);
    
    SELECT * FROM user WHERE id>(SELECT uid FROM grade WHERE age=20 LIMIT 1);
    
    SELECT name AS nm,age AS ag FROM user u WHERE u.id=1; 表别名和字段别名
    
    SELECT * FROM user WHERE name REGEXP '^a[0-9]*[a-Z]'; 正则匹配查询

#### Mysql 中的 WITH ROLLUP 用法 ####

`WITH ROLLUP`：在 group 分组字段的基础上再进行统计数据。

例子：首先在 name 字段上进行分组，然后在分组的基础上进行某些字段统计，表结构如下：

CREATE TABLE `test` (
      `Id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(25) DEFAULT NULL COMMENT '标题',
      `uid` int(11) DEFAULT NULL COMMENT 'uid',
      `money` decimal(2,0) DEFAULT '0',
      `name` varchar(25) DEFAULT NULL,
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通');
    INSERT INTO `demo`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋');

分组统计：

`SELECT name, SUM(money) as money FROM test GROUP BY name WITH ROLLUP;`

![在这里插入图片描述][52181dd63c814e5c8aa8d3907dd75473.png]  
可以看到按照name分组后对money求和统计了。  
上面看到 null 358, 如何搞个别名字段比如 总金额：358呢？也可以滴，咱们继续：

`coalesce(a,b,c);`

参数说明：  
如果`a==null`,则选择b；  
如果`b==nul`l,则选择c；  
如果`a!=null`,则选择a；  
如果a b c 都为null ，则返回为null（没意义）。

`SELECT coalesce(name, '总金额'),name, SUM(money) as money FROM test GROUP BY name WITH ROLLUP;`

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_11_color_FFFFFF_t_70_g_se_x_16]  
上面可以看出，在数据汇总方面。用途还是很方便滴。

### 插入数据 ###

INSERT INTO user (name,age)VALUES('zongshuai',24),('lihua',25);
    
    INSERT INTO user (name,age) SELECT name,age FROM user2;

### 更新数据 ###

UPDATE user SET name='zongshuai',age=24 WHERE id=8;

### 删除数据 ###

DELETE FROM user WHERE id=2;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/0c78b5fbe535428082067e1ba1550641.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/77d1ce0a741f4c22b3e7e645b2d09691.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2djaGVufg==,size_20,color_FFFFFF,t_70,g_se,x_16
[52181dd63c814e5c8aa8d3907dd75473.png]: https://img-blog.csdnimg.cn/52181dd63c814e5c8aa8d3907dd75473.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_11_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/f9d9a4bef36949009f0751fd5cabc65d.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2djaGVufg==,size_11,color_FFFFFF,t_70,g_se,x_16