mysql常见操作记录1

向右看齐 2022-04-08 15:21 221阅读 0赞

# mysql常见操作记录1 #

### 文章目录 ###

*  mysql常见操作记录1
 *   *  1.配置
     *   *  1.1mysql下载及安装
         *  1.2mysql常见操作命令
         *  1.3mysql常见配置
         *  附录：

**环境**

> Linux version 3.10.0-862.el7.x86\_64
> 
> mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86\_64)
> 
> navicat mysql

## 1.配置 ##

### 1.1mysql下载及安装 ###

下载参考：

[https://dev.mysql.com/downloads/mysql/5.7.html\#downloads][https_dev.mysql.com_downloads_mysql_5.7.html_downloads]

安装参考：

[https://www.cnblogs.com/fangts/p/8994851.html][https_www.cnblogs.com_fangts_p_8994851.html]

表、字段设计规范参考：

[https://www.cnblogs.com/mjbrian/p/6841226.html][https_www.cnblogs.com_mjbrian_p_6841226.html]

mysql常见配置：

[https://www.cnblogs.com/zhshto/p/6653424.html][https_www.cnblogs.com_zhshto_p_6653424.html]

### 1.2mysql常见操作命令 ###

1)建表语句：在文章末尾

2)简单操作，增删查改单表

>     #SELECT * FROM cas_user where user_name="admin"
>     #INSERT INTO cas_user (user_name,pass_word)values("test1","123456"),("test2","123456");
>     #DELETE FROM cas_user WHERE user_name="test1";
>     #UPDATE cas_user SET user_name="updateName" where id=5;

3)复杂操作:多表操作

关联表的增、删、改一般利用事物控制，关联表的查询根据查询条件不同，查询语句也不同。

>     #内连接
>     #SELECT * FROM sys_user u INNER JOIN sys_user_role ur ON u.user_id=ur.user_id where u.user_id=238
>     #上面的内连接相当于where关联查询
>     #SELECT u.*,ur.id,ur.role_id FROM sys_user u ,sys_user_role ur WHERE u.user_id=ur.user_id 
>     #左连接
>     #SELECT u.* ,ur.id,ur.role_id FROM sys_user u LEFT JOIN sys_user_role ur ON u.user_id=ur.user_id
>     #右连接
>     #SELECT u.user_id ,ur.* FROM sys_user u RIGHT JOIN sys_user_role ur ON u.user_id=ur.user_id

4)用户-角色-权限表查询

>     ####1、查询用户的角色及权限信息-- 
>     ########连接三个数据表的用法：FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
>     -- 
>     --  SELECT u.*,r.*,p.* FROM sys_user u inner join sys_user_role ur on ur.user_id=u.user_id 
>     --  inner join sys_role r on r.role_id=ur.role_id INNER JOIN sys_role_permission rp on rp.role_id=r.role_id
>     --  INNER JOIN sys_permission p on p.perm_id=rp.perm_id WHERE user_name='admin';
>     
>     ####2、查询某用户的角色
>     -- SELECT u.*,r.* FROM sys_user u,sys_role r,sys_user_role ur WHERE u.user_name='admin' AND u.user_id=ur.user_id AND ur.role_id=r.role_id;
>     
>     ####3、查询某用户拥有的权限
>     -- SELECT p.* FROM sys_permission p,sys_role_permission rp,sys_role r WHERE r.role_id=rp.role_id AND rp.perm_id=p.perm_id AND  r.role_id
>     -- IN (SELECT r.role_id FROM sys_user u,sys_role r,sys_user_role ur WHERE u.user_name ='admin' AND u.user_id=ur.user_id AND ur.role_id=r.role_id);
>     ####4、查询拥有某角色的用户信息
>      #SELECT u.* FROM sys_user u,sys_role r,sys_user_role ur WHERE r.role_id=1 AND  r.role_id=ur.role_id AND ur.user_id=u.user_id;
>     ####5、查询拥有某权限的角色
>     --  SELECT p.`perm_name`,r.`role_name` FROM sys_permission p,sys_role_permission rp,sys_role r WHERE p.`perm_name`='query' AND p.perm_id=rp.perm_id AND rp.role_id=r.role_id;
>     ####6、查询某角色拥有的权限
>     -- SELECT p.* FROM sys_permission p,sys_role_permission rp,sys_role r WHERE r.role_id=rp.role_id AND rp.perm_id=p.perm_id AND  r.role_id
>     -- IN (SELECT r.role_id FROM sys_user u,sys_role r,sys_user_role ur WHERE u.user_name ='user1' AND u.user_id=ur.user_id AND ur.role_id=r.role_id);

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

### 1.3mysql常见配置 ###

参考：[https://www.cnblogs.com/zhshto/p/6653424.html][https_www.cnblogs.com_zhshto_p_6653424.html]

[http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh][http_tool.oschina.net_apidocs_apidoc_api_mysql-5.1-zh]

### 附录： ###

文中测试用到的建表：

/*
    Navicat MySQL Data Transfer
    
    Source Server         : local
    Source Server Version : 80000
    Source Host           : localhost:3306
    Source Database       : test
    
    Target Server Type    : MYSQL
    Target Server Version : 80000
    File Encoding         : 65001
    
    Date: 2018-12-18 18:36:51
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for cas_user
    -- ----------------------------
    DROP TABLE IF EXISTS `cas_user`;
    CREATE TABLE `cas_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(50) DEFAULT NULL,
      `pass_word` varchar(21000) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `user_name` (`user_name`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of cas_user
    -- ----------------------------
    INSERT INTO `cas_user` VALUES ('1', 'admin', 'e10adc3949ba59abbe56e057f20f883e');
    INSERT INTO `cas_user` VALUES ('2', 'user1', 'e10adc3949ba59abbe56e057f20f883e');
    INSERT INTO `cas_user` VALUES ('3', 'user2', 'e10adc3949ba59abbe56e057f20f883e');
    INSERT INTO `cas_user` VALUES ('5', 'updateName', '123456');
    
    -- ----------------------------
    -- Table structure for sys_permission
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_permission`;
    CREATE TABLE `sys_permission` (
      `perm_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '权限id',
      `perm_name` varchar(20) NOT NULL COMMENT '权限名',
      PRIMARY KEY (`perm_id`),
      UNIQUE KEY `perm_name` (`perm_name`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='权限表';
    
    -- ----------------------------
    -- Records of sys_permission
    -- ----------------------------
    INSERT INTO `sys_permission` VALUES ('1', 'add');
    INSERT INTO `sys_permission` VALUES ('2', 'delete');
    INSERT INTO `sys_permission` VALUES ('4', 'query');
    INSERT INTO `sys_permission` VALUES ('3', 'update');
    
    -- ----------------------------
    -- Table structure for sys_role
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_role`;
    CREATE TABLE `sys_role` (
      `role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色id',
      `role_name` varchar(255) NOT NULL COMMENT '角色名',
      PRIMARY KEY (`role_id`),
      UNIQUE KEY `role_name` (`role_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='角色表';
    
    -- ----------------------------
    -- Records of sys_role
    -- ----------------------------
    INSERT INTO `sys_role` VALUES ('1', 'admin');
    INSERT INTO `sys_role` VALUES ('2', 'role1');
    INSERT INTO `sys_role` VALUES ('3', 'role2');
    
    -- ----------------------------
    -- Table structure for sys_role_permission
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_role_permission`;
    CREATE TABLE `sys_role_permission` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色-权限关联表的id',
      `role_id` int(11) NOT NULL COMMENT '角色id',
      `perm_id` int(11) NOT NULL COMMENT '权限id',
      PRIMARY KEY (`id`),
      KEY `perm_id` (`perm_id`) USING BTREE,
      KEY `role_id` (`role_id`) USING BTREE,
      CONSTRAINT `sys_role_permission_perm_id` FOREIGN KEY (`perm_id`) REFERENCES `sys_permission` (`perm_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `sys_role_permission_role_id` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='角色-权限表';
    
    -- ----------------------------
    -- Records of sys_role_permission
    -- ----------------------------
    INSERT INTO `sys_role_permission` VALUES ('1', '1', '1');
    INSERT INTO `sys_role_permission` VALUES ('2', '1', '2');
    INSERT INTO `sys_role_permission` VALUES ('3', '1', '3');
    INSERT INTO `sys_role_permission` VALUES ('4', '1', '4');
    INSERT INTO `sys_role_permission` VALUES ('5', '2', '4');
    INSERT INTO `sys_role_permission` VALUES ('6', '3', '4');
    
    -- ----------------------------
    -- Table structure for sys_user
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_user`;
    CREATE TABLE `sys_user` (
      `user_id` int(40) NOT NULL AUTO_INCREMENT COMMENT '用户id',
      `user_name` varchar(255) NOT NULL COMMENT '用户名',
      `real_name` varchar(255) DEFAULT NULL COMMENT '真实名',
      `pass_word` varchar(255) NOT NULL COMMENT '用户密码',
      `telephone` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `photo` varchar(255) DEFAULT NULL,
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
      `update_time` datetime DEFAULT CURRENT_TIMESTAMP,
      `status` int(10) DEFAULT NULL,
      PRIMARY KEY (`user_id`),
      UNIQUE KEY `user_name` (`user_name`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=242 DEFAULT CHARSET=utf8 COMMENT='用户表';
    
    -- ----------------------------
    -- Records of sys_user
    -- ----------------------------
    INSERT INTO `sys_user` VALUES ('238', 'admin', 'user104_RealName', '038bdaf98f2037b31f1e75b5b4c9b26e', '1111111', null, '111', '2018-10-09 18:50:03', '2018-09-28 19:28:06', null);
    INSERT INTO `sys_user` VALUES ('239', 'user1', 'user1_RealName', 'd6e75e048d00e4d0378d904bfdf81870', null, null, null, '2018-10-23 11:35:55', '2018-10-23 11:35:55', null);
    INSERT INTO `sys_user` VALUES ('240', 'user2', 'user2_RealName', '2fec513e98ba7033049be21d92f55fa3', null, null, null, '2018-10-23 14:29:51', '2018-10-23 14:29:51', null);
    INSERT INTO `sys_user` VALUES ('241', 'yhh', 'yhh', 'e22fdab996d9aca5be5ba6ae4f361255', null, null, null, '2018-11-13 22:37:08', '2018-11-13 22:37:08', null);
    
    -- ----------------------------
    -- Table structure for sys_user_role
    -- ----------------------------
    DROP TABLE IF EXISTS `sys_user_role`;
    CREATE TABLE `sys_user_role` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户-角色关联表id',
      `user_id` int(40) NOT NULL COMMENT '用户id',
      `role_id` int(11) NOT NULL COMMENT '角色id',
      PRIMARY KEY (`id`),
      KEY `role_id` (`role_id`) USING BTREE,
      KEY `user_id` (`user_id`) USING BTREE,
      CONSTRAINT `sys_user_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `sys_role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `sys_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `sys_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户-角色表';
    
    -- ----------------------------
    -- Records of sys_user_role
    -- ----------------------------
    INSERT INTO `sys_user_role` VALUES ('6', '238', '1');
    INSERT INTO `sys_user_role` VALUES ('7', '238', '2');
    INSERT INTO `sys_user_role` VALUES ('8', '238', '3');
    INSERT INTO `sys_user_role` VALUES ('9', '239', '2');
    INSERT INTO `sys_user_role` VALUES ('10', '240', '2');

[https_dev.mysql.com_downloads_mysql_5.7.html_downloads]: https://dev.mysql.com/downloads/mysql/5.7.html#downloads
[https_www.cnblogs.com_fangts_p_8994851.html]: https://www.cnblogs.com/fangts/p/8994851.html
[https_www.cnblogs.com_mjbrian_p_6841226.html]: https://www.cnblogs.com/mjbrian/p/6841226.html
[https_www.cnblogs.com_zhshto_p_6653424.html]: https://www.cnblogs.com/zhshto/p/6653424.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70]: /images/20220404/3a88ab6889af4350a23bb6a4970642cc.png
[http_tool.oschina.net_apidocs_apidoc_api_mysql-5.1-zh]: http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh