MySQL Entry basics

About MySQL

1. What is a database?

A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than 60 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just

is 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 a variety of data to large database systems that can store massive amounts of data in every way

has been widely used.

The main databases are: Sqlserver,mysql,oracle, SQLite, Access, MS SQL Server, etc., this article mainly describes the MySQL

2. What is database management used for?

A. Saving data to a file or memory
B. Receive a specific command, and then perform the appropriate action on the file
PS: If you have the above management system, do not need to create files and folders themselves, but directly to the software to pass the command, let it for file operations, they collectively referred to as the database management system (Dbms,database

Management System)

MySQL Installation

MySQL is an open-source relational database management system (RDBMS) that uses the most common database management language – Structured Query Language (SQL) for database management. In terms of WEB application MySQL is

One of the best RDBMS (relational database Management system, relational databases management systems) application software.

Use MySQL must have a condition

A. Installing the MySQL server
B. Installing the MySQL Client
C. "Client" Connection "server Side"
D. "Client" sends a command to the "server-side MySQL" Service to accept the command and perform the appropriate operation (increase and deletion of the search, etc.)
1,: http://dev.mysql.com/downloads/mysql/

2. Installation

For Windows installation Please refer to: http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html
Linux under Installation: http://www.cnblogs.com/chenjunbiao/archive/2011/01/24/1940256.html
Note: The above two links have a complete installation method, after the installation of the MySQL service start

MySQL operation

First, connect the database

Mysql-u user-p
Example: Mysql-u root-p
Common errors are as follows:

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

(Windows) is not running.

To exit a connection:

QUIT or Ctrl+d
Second, view the database, create a database, use the database to view the database:

show databases;
Default database:

MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data
To create a database:

Create database db1 DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # UTF8 Encoding

Create DATABASE db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # GBK encoding
Working with databases:

Use DB1;
Show all tables in the currently used database:

SHOW TABLES;
Third, user management

Create user

Create user ' username ' @ ' IP address ' identified by ' password ';
Delete User

Drop user ' username ' @ ' IP address ';
Modify User

Rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';
Change Password

Set password for ' username ' @ ' IP address ' = password (' new password ');
Note: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)

Iv. Rights Management

MySQL has the following limitations for permissions:

All privileges: All permissions except Grant
Select: Check Permissions only
Select,insert: Check and insert Permissions
...
Usage: No access
ALTER: Use ALTER TABLE
ALTER routine: Use ALTER procedure and drop procedure
Create: Using the CREATE TABLE
Create routine: Use CREATE PROCEDURE
Create temporary tables: Use create temporary tables
Create User: Use Create User, drop user, rename user, and revoke all privileges
CREATE VIEW: Using the CREATE VIEW
Delete: Using the Delete
Drop: Using the 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 the index
Insert: Using Insert
Lock tables: Using the Lock table
process: Using show full Processlist
Select: Using the Select
Show databases: Using show Databases
Show view: Using the Show View
Update: Using update
Reload: Using Flush
Shutdown: Use mysqladmin shutdown (turn off MySQL)
Super: Use change master, kill, logs, purge, master, and set global. Also allows mysqladmin debug to log on to
Replication client: Access to server location
Replication slave: Use by replication slave
for database and other internal permissions as follows:

The database name. all in the database
Database name. table specifies a table in the database
Database name. Stored procedures in the specified database
. * All databases
The permissions for users and IPs are as follows:

User name @ip address user can only be accessed under IP change
User name @192.168.1.% user can only be accessed under the IP segment (wildcard% = 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 the database. Table to ' user ' @ ' IP address '
3. Cancellation of Authorization

Revoke permissions on the database. Table from ' username ' @ ' IP address '
The authorization examples are as follows:

Grant all privileges the DB1.TB1 to ' username ' @ ' IP '

Grant SELECT on db1.* to ' username ' @ ' IP '

Grant Select,insert on . To ' user name ' @ ' IP '

Revoke select on DB1.TB1 from ' username ' @ ' IP '
MySQL table operations

First, view the table

Show tables; # View all tables in the database

SELECT * from table name; # View all the contents of the table
Second, create a table

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
Come up with an example of a good explanation

CREATE TABLE tab1 (
nidInt (one) not NULL auto_increment,
namevarchar (255) DEFAULT Zhangyanlin,
emailvarchar (255),
PRIMARY KEY ( nid )
) Engine=innodb DEFAULT Charset=utf8;
Note:

Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set
Self-increment, if you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table) Note: 1, for self-increment column, must be index (with primary key) 2, for self-increment can set step and start

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

DROP table Name
Iv. emptying the contents of the table

Delete from table name
TRUNCATE TABLE name
V. Modification of the table

To add a column:

ALTER TABLE name add column name type

To delete a column:

ALTER TABLE table name drop column name

To modify a column:

ALTER TABLE name modify column name type; --Type
ALTER TABLE name change original column name new column name type; --Column name, type

To add a primary key:

ALTER TABLE name add primary key (column name);

To delete a primary key:

ALTER TABLE name drop PRIMARY key;
ALTER TABLE name modify column name int, drop primary key;

To add a foreign key:

ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field);

To delete a foreign key:

ALTER TABLE name drop FOREIGN key foreign key name

To modify the default value:

Alter TABLE TESTALTER_TBL ALTER I SET DEFAULT 1000;

Delete default values:

Alter TABLE TESTALTER_TBL ALTER I DROP DEFAULT;

VI. Basic data types

MySQL data types are broadly divided into: numeric, time, and string

bit[(M)]
Bits (101001), m represents the length of the bits (1-64), the default M=1

Tinyint[(m)] [unsigned] [Zerofill]

        小整数，数据类型用于保存一些范围的整数数值范围：        有符号：            -128 ～ 127.        无符号：            0 ～ 255        特别的： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

        整数，数据类型用于保存一些范围的整数数值范围：            有符号：                -2147483648 ～ 2147483647            无符号：                0 ～ 4294967295        特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为：00002

bigint[(m)][unsigned][zerofill]

        大整数，数据类型用于保存一些范围的整数数值范围：            有符号：                -9223372036854775808 ～ 9223372036854775807            无符号：                0  ～  18446744073709551615

decimal[(m[,d]) [unsigned] [Zerofill]

        准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。        特别的：对于精确数值计算时需要用此类型               decaimal能够存储精确值的原因在于其内部按照字符串存储。

float[(m,d)] [UNSIGNED] [Zerofill]

        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。            无符号：                -3.402823466E+38 to -1.175494351E-38,                0                1.175494351E-38 to 3.402823466E+38            有符号：                0                1.175494351E-38 to 3.402823466E+38        **** 数值越大，越不准确 ****

double[(m,d)] [UNSIGNED] [Zerofill]

        双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。            无符号：                -1.7976931348623157E+308 to -2.2250738585072014E-308                0                2.2250738585072014E-308 to 1.7976931348623157E+308            有符号：                0                2.2250738585072014E-308 to 1.7976931348623157E+308        **** 数值越大，越不准确 ****

CHAR (M)

        char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。        PS: 即使数据小于m长度，也会占用m长度

varchar (m)

        varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被

Saved in this data type.

        注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设

All aspects of the database should be considered in order to achieve the best balance

Text

        text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 ? 1)个字符。

Mediumtext

        A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.

Longtext

        A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.

Enum

        枚举类型，        An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)        示例：            CREATE TABLE shirts (                name VARCHAR(40),                size ENUM(‘x-small‘, ‘small‘, ‘medium‘, ‘large‘, ‘x-large‘)            );            INSERT INTO shirts (name, size) VALUES (‘dress shirt‘,‘large‘), (‘t-shirt‘,‘medium‘),(‘polo shirt‘,‘small‘);

Set

        集合类型        A SET column can have a maximum of 64 distinct members.        示例：            CREATE TABLE myset (col SET(‘a‘, ‘b‘, ‘c‘, ‘d‘));            INSERT INTO myset (col) VALUES (‘a,d‘), (‘d,a‘), (‘a,d,a‘), (‘a,d,d‘), (‘d,a,d‘);

DATE

        YYYY-MM-DD（1000-01-01/9999-12-31）

Time

        HH:MM:SS（‘-838:59:59‘/‘838:59:59‘）

Year

        YYYY（1901/2155）

Datetime

        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

MySQL Table content Operations

One, 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
Cases:
Insert into TAB1 (name,email) VALUES (' Zhangyanlin ', ' [email protected] ')
Second, delete

Delete from table # Remove all table data
Delete from table where id=1 and Name= ' Zhangyanlin ' # Delete id = 1 and name= ' Zhangyanlin ' that row of data
Third, change

Update table Set name = ' Zhangyanlin ' where id>1
Iv. Check

Select from table
Select from table where ID > 1
Select Nid,name,gender as GG from table where ID > 1
There's too many conditions to check this, and I'm going to list it. As for the combination, it depends on how well you understand it.

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,33)
Select from table where ID not in (11,22,33)
SELECT * FROM table where ID in (select Nid from Table)
B, wildcard like

Select from table where name like ' zhang% ' # Zhang starts all (multiple strings)
Select from table where name like ' Zhang_ ' # Zhang starts all (one character)
C. Restrict limit

Select from table Limit 5;-First 5 rows
Select from Table limit 4, 5; -5 lines starting from line 4th
SELECT * FROM table Limit 5 offset 4-5 lines starting at line 4th
D, Sort Asc,desc

Select from table order BY column ASC-arranges from small to large according to "column"
Select from table order BY column Desc-arranges from large to small according to "column"
SELECT * FROM Table order BY column 1 desc, column 2 ASC-rank from large to small according to "column 1", if same, sort by column 2 from small to large
E. 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 > 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 have max (ID) > 10

Analysis tools

performance, structure and data analysis tools

anemometer– a SQL slow query monitor.
innodb-ruby– a parser for INOODB format files for the Ruby language.
innotop– a MySQL version ' top ' with multiple features and scalability.
pstop– a class Top program for MySQL that collects, summarizes, 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 Tools

mydumper– logical, Parallel MySQL backup/Dump tool.
mysqldumper– Web-based, open source Backup tool-useful for sharing virtual hosts.
mysqldump-secure– will encrypt, compress, log, blacklist and Nagios monitor the integrated mysqldump security scripts.
Percona xtrabackup– an open source hot Backup Utility for MySQL--your database is not locked during a backup of the server.
Performance testing

Tools to test your server for pressure

Iibench-mysql-Insert Performance Test tool for Java-based mysql/percona/mariadb indexes.
sysbench– a modular, cross-platform and multi-threaded performance testing tool.
Chat app

Script integrated into the chat room

Hubot MySQL Chatops
Configuration

MySQL Configuration examples and guidance

mysql-compatibility-config– makes MySQL more like a 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– is a lightweight, fast MySQL driver for the Go language.
Libattachsql–libattachsql is a lightweight, non-blocking C language API for MySQL servers.
MariaDB Java client– LGPL licensed MariaDB client library for Java applications.
mysql-python– a Python-language MySQL database connector.
PHP mysqlnd– MySQL native drive for MySQL, discarding outdated libmysql base drivers.
Development

Tools to support MySQL-related development

flywaydb– database migration; easily and reliably evolve your database version in any case.
liquibase– the source code control of your database.
propagator– centralized mode and data are deployed on a multidimensional topology.
Gui

Front-end and application GUI

adminer– a PHP-written database management tool.
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 in PHP, intends to manage MySQL on the web.
sequelpro– a database management application that runs MySQL under a Mac.
mycli– a terminal version MySQL client with auto-completion and syntax highlighting
HA

Highly Available Solutions

Galera cluster– A multi-host cluster scheme based on synchronous replication.
mha– High-availability manager and tools for MySQL
MySQL fabric– a extensible framework for managing the MySQL farm (server Farms).
Percona Replication manager– An asynchronous replication Management agent for MySQL. Support for file-and GTID-based replication, geographically distributed clusters implemented using booth.
Agent

MySQL Agent

maxscale– Open Source, a database-centric agent.
Mixer–go implements a MySQL proxy to provide a simple solution for MySQL shards.
MySQL proxy– a simple program between your client and the MySQL server that detects, analyzes, or alters their communication.
proxysql– High-performance MySQL agent.
Copy

Copy the relevant software

orchestrator– tools for managing and visualizing MySQL replication topologies.
Tungsten Replicator–mysql is a high performance, open source, data replication engine.
Mode

Additional mode

The framework of the Common_schema–mysql DBA, which provides an interpreter with function libraries, view libraries, and query scripts.
sys– A collection of views, functions, and procedures to help MySQL administrators gain a deeper understanding of the use of MySQL databases.
Server

MySQL Server Flavors

A community-developed branch of Mariadb–mysql server.
MySQL server & MySQL cluster–oracle official MySQL server and MySQL cluster distribution.
Percona server– A reinforced version of MySQL replacement
webscalesql–webscalesql,5.6 version, based on the MySQL 5.6 Community version.
Sharding

Shard Solution/Framework

vitess– for large-scale Web services, Vitess provides services and tools to facilitate the scaling of MySQL databases.
jetpants– an Automation suite for managing large-scale shard clusters, developed by TUMBLR.
Tool Pack

Toolkit, Generic Script

go-mysql– a pure Go library for handling MySQL network protocols and replication.
MySQL utilities– a collection of command-line utilities, written in Python language, for maintaining and managing single or multi-layered MySQL.
Percona toolkit– An advanced set of command-line tools for performing tasks that are too difficult or complex for MySQL servers and systems.
Openark kit– A set of practical tools to solve routine maintenance tasks, including complex or bare-handed operations, written in the Python language.
undrop– a tool for recovering data from a deleted or corrupted InnoDB table. **

MySQL Entry basics