mysql常用操作

怎么入门数据库编程？需要学习哪些知识点？这是新手们刚接触编程时常见的问题；下面golang学习网就来给大家整理分享一些知识点，希望能够给初学者一些帮助。本篇文章就来介绍《mysql常用操作》，涉及到MySQL，有需要的可以收藏一下

日常使用mysql数据库时,经常需要创建用户、数据库、表以及授权等相关操作,整理在此供大家查询参考使用:

win10 mysql5.7.21免安装版配置

1. 下载mysql-5.7.21-winx64.zip
2. 复制mysql-5.7.21-winx64.zip到D:dev-envirment-setup
3. 解压mysql-5.7.21-winx64.zip至D:dev-envirment-setupmysql-5.7.21-winx64

4. 在D:dev-envirment-setupmysql-5.7.21-winx64目录新建my.ini文件,文件内容如下:

   -- mysql8.0的默认密码验证不再是password,需要加上mysql_native_password
   CREATE USER 'dev'@'%' IDENTIFIED with mysql_native_password BY 'dev';

   修改用户密码

   $ mysql -e "select Host,User,Password from user" mysql -uroot -prootpwd
   +-----------+------+-------------------------------------------+
   | Host      | User | Password                                  |
   +-----------+------+-------------------------------------------+
   | localhost | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
   | hero      | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
   | 127.0.0.1 | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
   | ::1       | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
   +-----------+------+-------------------------------------------+
   
   给root用户设置密码：
   $ mysql -u root -p
   Enter password: (enter root password here)
   mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD(' newpwd');
   mysql> SET PASSWORD FOR ''@' host_name' = PASSWORD(' newpwd');
   密码与操作系统密码无关
   给匿名用户设置密码后或者删除匿名用户，就不能再匿名登录了
   密码不正确报错如下：
   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
   
   任何用户都可以访问test或test_开头的数据库，通过mysql.db表控制
   $ mysql -e "select Host,User,Db from db" mysql -uroot -prootpwd
   +------+------+---------+
   | Host | User | Db      |
   +------+------+---------+
   | %    |      | test    |
   | %    |      | test\_% |
   +------+------+---------+
   $ mysql -u root -p
   Enter password: (enter root password here)
   mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
   mysql> FLUSH PRIVILEGES;

   忘记密码怎么办

   $ ./bin/mysqld_safe --skip-grant-tables
   150407 11:38:25 mysqld_safe Logging to '/mydata/tmnew.err'.
   150407 11:38:25 mysqld_safe Starting mysqld daemon with databases from /mydata
   $ mysql
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 1
   Server version: 5.6.21 MySQL Community Server (GPL)
   
   Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> use mysql
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   mysql> update user set password=password('newrootpwd') where user='root';
   Query OK, 4 rows affected (0.02 sec)
   Rows matched: 4  Changed: 4  Warnings: 0
   
   mysql> flush privileges;
   Query OK, 0 rows affected (0.00 sec)

   2.创建数据库(testdb)

   -- 建库
   CREATE DATABASE IF NOT EXISTS `testdb` CHARACTER SET utf8;
   -- 所有权限
   GRANT ALL PRIVILEGES ON `testdb`.* TO 'dev'@'%';
   -- DDL & DML权限
   GRANT CREATE,DROP,ALTER,INSERT,DELETE,SELECT,UPDATE ON `testdb`.* TO 'dev'@'%'; 
   -- routine & function权限
   GRANT CREATE ROUTINE ON `testdb`.* TO 'dev'@'%'; 
   -- 刷新权限
   FLUSH PRIVILEGES;
   -- 查看权限
   SHOW GRANTS FOR 'dev';

   USAGE权限表示无权限，可以登录mysql数据库，但是不能操作任何数据；注意当mysql.db表中有匿名用户可访问的数据库配置记录时，即使是USAGE权限也可以操作。

   3.准备建表sql文件(testdb.sql)

   建表语句

   -- ----------------------------
   -- 测试表
   -- ----------------------------
   DROP TABLE IF EXISTS `t_test`;
   CREATE TABLE `t_test`  (
     `id`               int(11)       UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
     `code`             varchar(32)   NOT NULL DEFAULT '' COMMENT '代码',
     `name`             varchar(255)  NOT NULL DEFAULT '' COMMENT '名称',
     `remark`           varchar(255)  NOT NULL DEFAULT '' COMMENT '备注信息',
     
     `create_at`        datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间戳',
     `update_at`        datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间戳',
     `del_flag`         tinyint(1)    NOT NULL DEFAULT 0 COMMENT '删除标志:0-正常,1-删除',
     PRIMARY KEY (`id`),
     UNIQUE KEY `idx_uk_test_01` (`code`)
   ) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '测试表';

   修改表字段

   ALTER TABLE t_test MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8 NOT NULL COMMENT '名称';

   4.mysql命令客户端创建表

   D:\testdb\sql>                                       #切换到sql所在目录
   D:\testdb\sql>mysql -h127.0.0.1 -P3306 -udev -pdev   #连接mysql服务
   mysql> use testdb                                    #切换数据库
   mysql> source testdb.sql                             #执行建表sql文件

   5.mysql查看版本与时间

   mysql> select version() -- 查看版本
   mysql> select now() -- 查看当前日期时间yyyy-MM-dd HH:mm:ss
   mysql> select current_date -- 查看当前日期yyyy-MM-dd
   mysql> select current_time -- 查看当前时间HH:mm:ss
   mysql> show variables like '%char%' -- 查看字符集
       character_set_client    utf8mb4
       character_set_connection    utf8mb4
       character_set_database    utf8
       character_set_filesystem    binary
       character_set_results    utf8mb4
       character_set_server    utf8
       character_set_system    utf8
       character_sets_dir    D:\dev-envirment-setup\mysql-5.7.21-winx64\share\charsets\
   mysql> show global variables like '%timeout' -- 查看操作超时时间
       connect_timeout    10
       delayed_insert_timeout    300
       have_statement_timeout    YES
       innodb_flush_log_at_timeout    1
       innodb_lock_wait_timeout    50
       innodb_rollback_on_timeout    OFF
       interactive_timeout    28800
       lock_wait_timeout    31536000
       net_read_timeout    30
       net_write_timeout    60
       rpl_stop_slave_timeout    31536000
       slave_net_timeout    60
       wait_timeout    28800

   6.mysql执行外部sql脚本

   方式一：命令行

   $ mysql –u用户名 –p密码 –D数据库 

   注意：如果在sql脚本中使用了

   mysql> source sql脚本全路径
   mysql> \. sql脚本全路径

   7.查看数据库、表、建表语句

   mysql> select database() -- 查看数据库名称
   mysql> show tables -- 查看当前库下所有表名
   mysql> desc xxx -- 查看表结构,xxx为表名
   mysql> show create table xxx -- 查看表DDL,xxx为表名

   8.查看索引

   DROP INDEX login_name_index ON user; 
   ALTER TABLE user ADD INDEX login_name_index(login_name);
   ALTER TABLE user ADD UNIQUE login_name_index(login_name);
   
   mysql> show index from xxx -- 查看xxx表的索引
   mysql> show keys from xxx -- 查看xxx表的索引

   9.查看表外键

   select 
       CONSTRAINT_NAME,
       TABLE_NAME,
       COLUMN_NAME,
       POSITION_IN_UNIQUE_CONSTRAINT,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME 
   from information_schema.key_column_usage a 
   where 
   a.table_schema='xxxschema' and REFERENCED_TABLE_NAME='xxxtable'

   10.binlog

   show variables like 'log_%'; #查看binlog配置
   show binary logs; #查看binlog文件
   show master status; #查看当前binlog文件
   show binlog events in 'mysql-bin.000039'; #查看binlog文件内容

   11.事件

   # last_executed字段记录最近执行时间
   SELECT * FROM information_schema.EVENTS;

   本篇关于《mysql常用操作》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！