Shulou(Shulou.com)06/01 Report--

Introduction to MySQL

1. What is a database?

Database (Database) is a warehouse that organizes, stores and manages data according to data structure. It came into being more than 60 years ago. With the development of information technology and market, especially after the 1990s, data management is no longer just

It is a way to store and manage data and transform it into a variety of data management methods that users need. There are many types of databases, from the simplest tables that store all kinds of data to large database systems that can store massive data.

It has been widely used.

The mainstream databases are: sqlserver,mysql,Oracle, SQLite, Access, MS SQL Server and so on. This article is mainly about mysql.

2. What is database management for?

a. Save data to a file or memory

b. Receive specific commands, and then take appropriate actions on the file

PS: if you have the above management system, you do not need to create files and folders by yourself, but pass commands directly to the above software to operate the files. They are collectively called database management systems (DBMS,Database).

Management System)

MySQL installation

MySQL is an open source relational database management system (RDBMS). The MySQL database system uses the most commonly used database management language-structured query language (SQL) for database management. In WEB applications, MySQL is

One of the best RDBMS (Relational Database Management System, Relational Database Management system) applications.

The following conditions are required for the use of mysql

a. Install the MySQL server

b. Install the MySQL client

C. [client] connects [server]

D. [client] sends commands to accept commands of [server MySQL] service and performs corresponding operations (additions, deletions, modifications, queries, etc.)

1. Download address: http://dev.mysql.com/downloads/mysql/

2. Installation

For windows installation, please refer to: http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html

Install under linux: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html

Note: the above two links have a complete installation mode. Start the mysql service after installation.

MySQL operation

First, connect to the database

Mysql-u user-p

Example: mysql-u root-p

Common errors are:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service

(Windows) is not running.

Exit the connection:

QUIT or Ctrl+D

Second, view the database, create the database, and use the database to view the database:

Show databases

Default database:

Mysql-data related to user rights

Test-for user test data

Information_schema-MySQL itself constructs relevant data

Create the database:

Create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8 coding

Create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk coding

Use the database:

Use db1

Displays all tables in the database currently in use:

SHOW TABLES

III. User management

Create a user

Create user 'username' @'IP address' identified by 'password'

Delete user

Drop user 'user name' @'IP address'

Modify user

Rename user 'user name' @'IP address'; to 'new user name' @'IP address'

Modify the password

Set password for 'username' @'IP address'= Password ('new password')

Note: user rights-related data is saved in the user table of the mysql database, so it can also be manipulated directly (not recommended)

IV. Authority management

Mysql has the following restrictions on permissions:

All privileges: all permissions except grant

Select: check permissions only

Select,insert: search and insert permissions

...

Usage: no access permission

Alter: using alter table

Alter routine: using alter procedure and drop procedure

Create: using create table

Create routine: using create procedure

Create temporary tables: using create temporary tables

Create user: using create user, drop user, rename user, and revoke all privileges

Create view: using create view

Delete: using delete

Drop: using drop table

Execute: using call and stored procedures

File: using select into outfile and load data infile

Grant option: using grant and revoke

Index: using index

Insert: using insert

Lock tables: using lock table

Process: using show full processlist

Select: using select

Show databases: using show databases

Show view: using show view

Update: using update

Reload: using flush

Shutdown: using mysqladmin shutdown (turn off MySQL)

Super: use change master, kill, logs, purge, master, and set global. It also allows mysqladmin debugging and login.

Replication client: access to the server location

Replication slave: used by replication slaves

For database and other internal permissions are as follows:

Database name. Everything in the database

Database name. Table specifies a table in the database

Database name. Stored procedure specifies the stored procedure in the database

. * all databases

The permissions for users and IP are as follows:

User name @ IP address users can only access it under the changed IP

User name @ 192.168.1% users can only access it under the changed IP segment (wildcard% indicates any)

User name @% user can be accessed under any IP (default IP address is%)

1. View permissions:

Show grants for 'user' @'IP address'

2. Authorization

Grant permissions on database. Table to 'user' @'IP address'

3. Cancel authorization

Revoke permissions on database. Table from 'user name' @'IP address'

Examples of authorization are as follows:

Grant all privileges on db1.tb1 TO 'user name' @ 'IP'

Grant select on db1.* TO 'user name' @ 'IP'

Grant select,insert on. TO 'user name' @ 'IP'

Revoke select on db1.tb1 from 'user name' @ 'IP'

MySQL table operation

First, check the table

Show tables; # View all tables in the database

Select * from table name; # View all table contents

2. Create tables

Create table table name (

Whether the column name type can be empty

Whether the column name type can be empty

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Let's give an example to explain in detail.

CREATE TABLE tab1 (

Nid int (11) NOT NULL auto_increment

Name varchar (255) DEFAULT zhangyanlin

Email varchar (255)

PRIMARY KEY (nid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Note:

Default value, which can be specified when the column is created, and will be added automatically if it is not actively set when inserting data

Self-increment, if you set a self-increment column for a column, you do not need to set this column when inserting data. It will be self-incrementing by default (there can only be one self-increment column in the table) Note: 1. For self-increment column, it must be index (including primary key) 2. For self-increment, step size and start can be set.

Value

The primary key, a special unique index, does not allow null values, if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.

Delete the table

Drop table table name

Fourth, clear the contents of the table

Delete from table name

Truncate table table name

Fifth, modify the table

Add columns:

Alter table table name add column name type

Delete the column:

Alter table table name drop column column name

Modify the column:

Alter table table name modify column column name type;-- type

Alter table table name change original column name new column name type;-- column name, type

Add a primary key:

Alter table table name add primary key (column name)

Delete the primary key:

Alter table table name drop primary key

Alter table table name modify column name int, drop primary key

Add a foreign key:

Alter table slave table add constraint foreign key name (such as: FK_ slave table _ master table) foreign key slave table (foreign key field) references master table (primary key field)

Delete the foreign key:

Alter table table name drop foreign key foreign key name

Modify the default value:

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000

Delete the default value:

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT

VI. Basic data types

The data types of MySQL are roughly divided into: numeric value, time, and string.

Bit [(M)]

Binary bit (101001). M represents the length of the binary bit (1-64). The default is msq1.

Tinyint [(m)] [unsigned] [zerofill]

Small integer, data type is used to hold a range of integer values: signed:-128 ~ 127. Unsigned: 0-255. special: no Boolean value in MySQL, constructed using tinyint (1).

Int [(m)] [unsigned] [zerofill]

Integer, data type is used to hold a range of integer values: signed:-2147483648 ~ 2147483647 unsigned: 0 ~ 4294967295 special: the m in the integer type is used for display only and there is no limit to the storage range. For example: int (5), when inserting data 2, the data is displayed as: 00002 when select

Bigint [(m)] [unsigned] [zerofill]

Large integers, data types used to hold a range of integer values: signed:-9223372036854775808 ~ 9223372036854775807 unsigned: 0 ~ 18446744073709551615

Decimal [(m [, d])] [unsigned] [zerofill]

The exact decimal value, m is the total number of numbers (minus sign is not counted), d is the number after the decimal point. The maximum value of m is 65 and the maximum value of d is 30. Special: for accurate numerical calculations, this type of decaimal is needed to store exact values because it is stored internally as a string.

FLOAT [(MMagar D)] [UNSIGNED] [ZEROFILL]

Single-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point. Unsigned:-3.402823466E+38 to-1.175494351E-38, 0 1.175494351E-38 to 3.402823466E+38 signed: 0 1.175494351E-38 to 3.402823466E+38 * the higher the value, the less accurate it is *

DOUBLE [(MMagar D)] [UNSIGNED] [ZEROFILL]

Double-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point. Unsigned:-1.7976931348623157E+308 to-2.2250738585072014E-308 0 2.2250738585072014E-308 to 1.7976931348623157E+308 signed: 0 2.2250738585072014E-308 to 1.7976931348623157E+308 * the higher the value, the less accurate it is *

Char (m)

The char data type is used to represent a fixed-length string that can contain up to 255characters. Where m represents the length of the string. PS: even if the data is less than m length, it will occupy m length.

Varchar (m)

The varchars data type is used for variable-length strings that can contain up to 255characters. Where m represents the maximum length of the string allowed to be saved by this data type, as long as the length is less than the maximum value, any string can be

Saved in this data type.

Note: although varchar is flexible to use, the processing speed of the char data type is faster from a system-wide performance perspective, sometimes exceeding 50% of the varchar processing speed. Therefore, the user is setting the

All factors should be taken into account when calculating the database in order to achieve the best balance.

Text

The text data type is used to hold long large strings, which can be grouped up to 65535 (2 characters 16 − 1) characters.

Mediumtext

A TEXT column with a maximum length of 16777215 (2 hours 24 − 1) characters.

Longtext

A TEXT column with a maximum length of 4294967295 or 4GB (32 − 1) characters.

Enum

Enumerated types, An ENUM column can have a maximum of 65535 distinct elements. (The practical limit is less than 3000.) Examples: CREATE TABLE shirts (name VARCHAR (40), size ENUM ('x small', 'medium',' large','x dress shirt','large')); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), (' t house shirt), ('polo shirt','small')

Set

Collection type A SET column can have a maximum of 64 distinct members. Example: CREATE TABLE myset (col SET ('averse,' baked, 'cantilevered,' d')); INSERT INTO myset (col) VALUES ('arecalogical d'), (' drecincea'), ('arecalogical'), ('arecalogical dd`'), ('dreceddd')

DATE

YYYY-MM-DD (1000-01-01 Universe 9999-12-31)

TIME

HH:MM:SS ('- 838 lax 59 race 59)

YEAR

YYYY (1901amp 2155)

DATETIME

YYYY-MM-DD HH:MM:SS (1000-01-01 00-00-00-00-12-31 23:59:59 Y)

TIMESTAMP

YYYYMMDD HHMMSS (sometime in 2037). 1970-01-01. 00. 00.

MySQL content operation

I. increase

Insert into table (column name, column name...) Values (value, value,...)

Insert into table (column name, column name...) Values (value, value,...), (value, value, value...)

Insert into table (column name, column name...) Select (column name, column name...) From table

Example:

Insert into tab1 (name,email) values ('zhangyanlin','zhangyanlin8851@163.com')

2. Delete

Delete from table # Delete all data in the table

Delete from table where id=1 and name='zhangyanlin' # deletes the row of ID = 1 and name='zhangyanlin'

III. Reform

Update table set name = 'zhangyanlin' where id > 1

4. Check

Select from table

Select from table where id > 1

Select nid,name,gender as gg from table where id > 1

There are too many conditions to check this area. I will list it out. As for the combination, it depends on your understanding.

A, conditional judgment where

Select from table where id > 1 and name! = 'aylin' and num = 12

Select from table where id between 5 and 16

Select from table where id in (11, 22, 22, 33)

Select from table where id not in (11, 22, 22, 33)

Select * from table where id in (select nid from table)

B, wildcard like

All (multiple strings) at the beginning of the select from table where name like 'zhang%' # zhang

All (one character) at the beginning of the select from table where name like 'zhang_' # zhang

C. Restrict limit

Select from table limit 5;-first 5 rows

Select from table limit 4 5;-5 rows starting at line 4

Select * from table limit 5 offset 4-5 rows starting at line 4

D, sort asc,desc

Select from table order by column asc-arranged from smallest to largest according to "column"

Select from order by column desc-sort from large to small according to "column"

Select * from table order by column 1 desc, column 2 asc-sort from largest to smallest according to "column 1" and, if the same, sort by column 2

E, grouping group by

Select num from table group by num

Select num,nid from table group by num,nid

Select num,nid from table where nid > 10 group by num,nid order nid desc

Select num,nid,count (*), sum (score), max (score), min (score) from table group by num,nid

Select num from table group by num having max (id) > 10

Analysis tool

Performance, structure and data analysis tools

Anemometer-an SQL slow query monitor.

Innodb-ruby-A parser for files in InooDB format for the Ruby language.

Innotop-A MySQL version of the 'top' tool with multiple features and extensibility.

Pstop-A top-like program for MySQL that collects, aggregates, and displays information from performance_schema.

Mysql-statsd-A Python daemon that collects MySQL information and sends it to Graphite via StatsD.

Backup

Backup / Storage / recovery tool

MyDumper-logical, parallel MySQL backup / dump tool.

MySQLDumper-an open source web-based backup tool-is useful for sharing virtual hosts.

Mysqldump-secure-an integrated mysqldump security script that integrates encryption, compression, logging, blacklisting and Nagios monitoring.

Percona Xtrabackup-an open source hot backup utility for MySQL-does not lock your database during server backups.

Performance testing

Tools to perform stress tests on your server

Iibench-mysql-A tool for insert performance testing based on Java's MySQL/Percona/MariaDB index.

Sysbench-A modular, cross-platform and multithreaded performance testing tool.

Chat application

A script integrated into a chat room

Hubot MySQL ChatOps

Configuration

MySQL configuration examples and guidance

Mysql-compatibility-config-makes MySQL configuration more like the new (or previous) version of MySQL.

Connector

MySQL Connector for multiple programming languages

Connector/Python-A standardized database driver for the Python platform and development.

Go-sql-driver-A lightweight, extremely fast MySQL driver for the Go language.

LibAttachSQL-libAttachSQL is a lightweight, non-blocking C language API for MySQL servers.

MariaDB Java Client-MariaDB client libraries that are applied for Java and licensed by LGPL.

MySQL-Python-an MySQL database connector for the Python language.

PHP mysqlnd-MySQL native drivers for MySQL, deprecating obsolete libmysql base drivers.

Development

Tools that support MySQL-related development

Flywaydb-Database migration; easily and reliably evolve your database version in any case.

Liquibase-take source code control over your database.

Propagator-centralized patterns and data are deployed on a multidimensional topology.

GUI

Front-end and application GUI

Adminer-A database management tool written by PHP.

MySQL graphical management tool under HeidiSQL-Windows.

MySQL Workbench-provides database administrators and developers with an integrated tool environment for database design and modeling; SQL development; database management.

PhpMyAdmin-an open source software written by PHP intended to manage MySQL on web.

SequelPro-A database management application that runs MySQL under mac.

Mycli-A terminal MySQL client with automatic completion and syntax highlighting

HA

High availability solution

Galera Cluster-A multi-host cluster scheme based on synchronous replication.

MHA-excellent high-availability managers and tools for MySQL

MySQL Fabric-an extensible framework for managing MySQL server farms (Server Farms).

Percona Replication Manager-Asynchronous replication management agent for MySQL. Support for file-and GTID-based replication, geographically distributed clusters using booth.

Agent

MySQL Agent

MaxScale-Open source, database-centric agent.

A MySQL proxy implemented by Mixer-Go to provide a simple solution for MySQL sharding.

MySQL Proxy-A simple program between your client and MySQL server that can detect, analyze, or change their communications.

ProxySQL-A high-performance MySQL agent.

Copy

Copy related softwar

Orchestrator-A tool for managing and visualizing MySQL replication topologies.

Tungsten Replicator-MySQL is a high-performance, open source, data replication engine.

Pattern

Additional mode

Common_schema-MySQL DBA framework that provides an interpreter with function libraries, view libraries, and query scripts.

Sys-A collection of views, functions, and procedures to help MySQL managers gain a deeper understanding of the use of MySQL databases.

Server

MySQL server flavors

A branch of MariaDB-MySQL server developed by the community.

MySQL Server & MySQL Cluster-the official MySQL server and MySQL cluster distribution of Oracle.

Percona Server-an enhanced alternative to MySQL

WebScaleSQL-WebScaleSQL,5.6 version, based on the MySQL 5.6 community version.

Slice

Sharding solution / framework

Vitess-for large-scale web services, vitess provides services and tools to facilitate scaling of MySQL databases.

Jetpants-an automation suite for managing large sharding clusters, developed by Tumblr.

Tool kit

Toolkit, generic script

Go-mysql-A pure go library that handles network protocols and replication of MySQL.

MySQL Utilities-A collection of command-line utilities written in the Python language to maintain and manage single or multiple tiers of MySQL.

Percona Toolkit-an advanced command-line toolset for performing tasks that are too difficult or complex for MySQL servers and systems.

Openark kit-A set of practical tools to solve daily maintenance tasks, including some complex or manual operations, written in Python.

UnDROP-A tool used to recover data in deleted or corrupted InnoDB tables. **

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.