Home Database Mysql Tutorial MySQL数据库常用基本命令应用分享02

MySQL数据库常用基本命令应用分享02

Jun 07, 2016 pm 05:41 PM
mysql database Share for free

说明:本文来自老男孩linux运维实战培训-MySQL数据库培训课程系列内容1)视频讲解目录1MySQL服务操作.........................................................

说明:本文来自老男孩linux运维实战培训-MySQL数据库培训课程系列内容
1)视频讲解目录
1 MySQL服务操作............................................................................ 3
1.1 启动与关闭MySQL............................................................. 3
 1.1.1 单实例方法............................................................ 3
 1.1.2 多实例方法............................................................ 4
 1.2 登陆MySQL方法................................................................ 4
 1.2.1 单实例方法............................................................ 4
 1.2.2 多实例方法............................................................ 4
1.3 登陆MySQL后................................................................... 4
1.4 善用MySQL的help........................................................... 5
1.5退出MySQL方法................................................................. 7
1.6设置及修改mysql root用户密码...................................... 7
 1.6.1设置密码方法......................................................... 7
 1.6.2修改root密码法一................................................. 8
 1.6.3修改root密码法二................................................. 8
 1.6.4修改root密码法三................................................. 8
1.7如何找回丢失的mysql root用户密码............................... 8
 1.7.1 单实例启动方法..................................................... 8
 1.7.2 多实例启动方法..................................................... 8
 1.7.3 修改密码的方法..................................................... 8
 1.7.4 重启用新密码登陆................................................. 9
2 数据库操作................................................................................. 9
2.1 创建数据库...................................................................... 9
2.2显示数据库...................................................................... 11
2.3 删除数据库..................................................................... 12
2.4 连接数据库..................................................................... 12
2.5 查看当前连接的数据库................................................... 13
2.6 当前数据库包含的表信息................................................ 14
2.7 删除系统多余帐号.......................................................... 14
2.8 创建用户并赋予权限两法................................................ 15
 2.8.1 查看grant帮助................................................... 15
 2.8.1 create和grant配合法........................................ 16
 2.8.2 直接grant法....................................................... 18
 2.8.3 通过实验获得ALL PRIVILEGES包括哪些权限........ 19
 2.8.4 生产环境授权用户的建议..................................... 20
2.9查看mysql的用户........................................................... 23
3 表操作...................................................................................... 23
3.1准备................................................................................ 23
3.2建表................................................................................ 24
 3.2.1 建立测试表test.................................................. 24
 3.2.2 生产环境标准的UTF8格式表结构语句.................. 25
  3.2.2.1 mysql字段类型............................................. 25
  3.2.2.2 建立几个关联表:....................................... 26
  3.2.2.3 创建关联表语句........................................... 26
  3.2.2.4 某sns产品生产正式建表语句....................... 27
3.3 查表结构及建表的SQL.................................................... 27
 3.3.1 查看表结构.......................................................... 27
 3.3.2 查看建表语句....................................................... 28
 3.3.3 为字段创建索引................................................... 28
3.4插入数据......................................................................... 31
3.5 查询............................................................................... 34
 3.5.1 查询所有行............................................................ 34
 3.5.2 查询前几行数据..................................................... 34
 3.5.3 多表查询............................................................... 37
 3.5.4 使用explain查询select语句使用索引情况.............. 37
3.6 修改表中数据................................................................. 38
3.7 删除表中数据................................................................. 39
3.8 在表中增删改字段(dba操作).......................................... 41
3.9 更改表名........................................................................ 43
3.10 删除表.......................................................................... 45
3.11 mysql字段类型............................................................. 45
4 MySQL数据库的备份与恢复........................................................ 45
4.1 准备工作:首先建表填充数据......................................... 45
4.2 填充中文数据暴漏乱码问题............................................ 46
4.3 MySQL字符集介绍........................................................... 50
4.4 set names gbk在做什么?............................................. 52
4.5备份单个数据库............................................................... 57
4.6 mysqldump在做什么?.................................................... 59
4.7备份多个库...................................................................... 61
4.8备份单个表...................................................................... 61
4.9备份多个表...................................................................... 62
4.10备份数据库结构(不包含数据)........................................ 63
5恢复数据库................................................................................ 64
5.1 source命令恢复............................................................. 64
5.2 mysql命令恢复(标准).................................................... 70
6 mysql带-e参数实现非交互式对话............................................... 72
6.1 恢复mysql数据.............................................................. 72
6.2 查看mysql状态.............................................................. 73
7 mysql生产环境案例.................................................................... 76
7.1 mysql数据库批量插入数据shell脚本实现..................... 76
7.2 不登陆数据库执行mysql命令小结.................................. 77

2)视频实战讲解(全屏观看效果更好)

 


 相关视频内容请博友们多多关注老男孩的博客更新。
最后,美国空间,欢迎大家一起探讨学习和教学,如何让学习者有所收获,让从师者如何最大限度的把知识进行最大限度的传承。

本文出自 “老男孩linux运维” 博客,美国空间,香港服务器,请务必保留此出处

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP development practice: Use PHPMailer to send emails to users in the MySQL database PHP development practice: Use PHPMailer to send emails to users in the MySQL database Aug 05, 2023 pm 06:21 PM

PHP development practice: Use PHPMailer to send emails to users in the MySQL database Introduction: In the construction of the modern Internet, email is an important communication tool. Whether it is user registration, password reset, or order confirmation in e-commerce, sending emails is an essential function. This article will introduce how to use PHPMailer to send emails and save the email information to the user information table in the MySQL database. 1. Install the PHPMailer library PHPMailer is

Tencent video member account free sharing Tencent video member account free sharing Feb 06, 2024 pm 07:40 PM

Tencent Video is one of the most commonly used player software, with rich resources, lightweight interface design and high-definition and smooth playback quality. Tencent Video member users can not only watch all VIP dramas but also enjoy the highest definition picture quality. The editor below will bring you the free membership account of Tencent Video. Come and take a look. Tencent Video’s latest member account is free to share in 2023. Note: You can log in directly and use it. Do not change the password at will. Account: 409886852 Password: gjvg16018 Account: 359108687 Password: 757vyjzq Account: 6769766868 Password: 20378wmdt Account: 2616456320 Password: bkr

Go language and MySQL database: How to separate hot and cold data? Go language and MySQL database: How to separate hot and cold data? Jun 18, 2023 am 08:26 AM

As the amount of data continues to increase, database performance has become an increasingly important issue. Hot and cold data separation processing is an effective solution that can separate hot data and cold data, thereby improving system performance and efficiency. This article will introduce how to use Go language and MySQL database to separate hot and cold data. 1. What is hot and cold data separation processing? Hot and cold data separation processing is a way of classifying hot data and cold data. Hot data refers to data with high access frequency and high performance requirements. Cold data

How to perform incremental data backup of MySQL database using Go language How to perform incremental data backup of MySQL database using Go language Jun 17, 2023 pm 02:28 PM

As the amount of data increases, database backup becomes more and more important. For the MySQL database, we can use the Go language to achieve automated incremental backup. This article will briefly introduce how to use Go language to perform incremental backup of MySQL database data. 1. Install the Go language environment. First, we need to install the Go language environment locally. You can go to the official website to download the corresponding installation package and install it. 2. Install the corresponding library. The Go language provides many third-party libraries for accessing MySQL databases, among which the most commonly used ones are

How to use MySQL database for time series analysis? How to use MySQL database for time series analysis? Jul 12, 2023 am 08:39 AM

How to use MySQL database for time series analysis? Time series data refers to a collection of data arranged in time order, which has temporal continuity and correlation. Time series analysis is an important data analysis method that can be used to predict future trends, discover cyclical changes, detect outliers, etc. In this article, we will introduce how to use a MySQL database for time series analysis, along with code examples. Create a data table First, we need to create a data table to store time series data. Suppose we want to analyze the number

To what extent can I develop MySQL database skills to be successfully employed? To what extent can I develop MySQL database skills to be successfully employed? Sep 12, 2023 pm 06:42 PM

To what extent can I develop MySQL database skills to be successfully employed? With the rapid development of the information age, database management systems have become an indispensable and important component in all walks of life. As a commonly used relational database management system, MySQL has a wide range of application fields and employment opportunities. So, to what extent do MySQL database skills need to be developed to be successfully employed? First of all, mastering the basic principles and basic knowledge of MySQL is the most basic requirement. MySQL is an open source relational database management

How to use MySQL database for image processing? How to use MySQL database for image processing? Jul 14, 2023 pm 12:21 PM

How to use MySQL database for image processing? MySQL is a powerful relational database management system. In addition to storing and managing data, it can also be used for image processing. This article will introduce how to use a MySQL database for image processing and provide some code examples. Before you begin, make sure you have installed a MySQL database and are familiar with basic SQL statements. Create a database table First, create a new database table to store the image data. The structure of the table can be as follows

How to make reliable MySQL database connection using Go language? How to make reliable MySQL database connection using Go language? Jun 17, 2023 pm 07:18 PM

With the large amount of data that needs to be stored and processed, MySQL has become one of the most commonly used relational databases in application development. The Go language is becoming more and more popular among developers due to its efficient concurrency processing and concise syntax. This article will lead readers to implement reliable MySQL database connection through Go language, allowing developers to query and store data more efficiently. 1. Several ways for Go language to connect to MySQL database. There are usually three ways to connect to MySQL database in Go language, which are: 1. Third-party library

See all articles