MySQL installation and basic use;

Installation of MySQL:

Download mysql-5.5.54-linux2.6-x86_64.tar.gz

# TAR-XF Mysql-5.5.54-linux2.6-x86_64.tar.gz

# LN-SV Mysql-5.5.54-linux2.6-x86_64/usr/local/mysql

For security, create a MySQL-only user without logging in to bash

# Useradd Mysql-b/sbin/nologin

Initialize MySQL using the package's own initialization script

# scripts/mysql_install_db--datadir=/mysql-date/mydate/--user=mysql

Copy your own service script to the system

# CP Support-files/mysql.server/etc/rc.d/init.d/mysqld

In the package has 3 kinds of profile templates: my-large.cnf,my-medium.cnf,my-small.cnf, corresponding to different levels of MySQL, according to their own needs to replicate;

# CP MY-LARGE.CNF/ETC/MY.CNF

Because the location of the DataDir is re-specified at initialization time, this item needs to be redefined in the configuration file;

# VIM/ETC/MY.CNF

<< datadir=/mysql-date/mydate

# vim/etc/profile.d/mysqld.sh

<< Export Path=/usr/local/mysql/bin: $PATH

Remember to change the owners and genera of the MySQL-related directory before starting the service;

# Chown-r Root:mysql/usr/local/mysql

# Chowm-r Mysql:mysql/mysql-date/mydate//Note the problem with the data directory, if you do not change this, you will be prompted to create a PID file, resulting in the inability to start the service;

Start and test;

Basic operations:

DCL: Data Control language for granting and recovering permissions

Grant,revoke

DDL: A data definition language, a statement used to create basic components such as tables, indexes, etc.

Create,drop,alter

DML: Data manipulation language, responsible for adding, deleting and modifying operations

Insert,delete,select,update

The MySQL client uses:

In MySQL, the keyword to use uppercase, to distinguish between keywords and content, and each line to, at the end, sometimes, especially when the beginning of contact with MySQL will often forget to play, and enter the battery life mode, this time can be entered;

Configure the startup password to modify MySQL:

> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' password ');

Basic Operations Command:

SHOW DATABASES; View the library that has permissions, that is, the directory;

Use library name; Select the default library to be operated on;

SHOW TABLES; View the forms in the default action library;

DESC table sole name;//View the structure of the form;

Select: Select the operation, first make a selection, then follow-up operation;

Select User,host,password from user;//Choose the User,host,password field in the user form and view it;

Can be used in conjunction with where to perform conditional operations;

Create: Creates a command that can create database and table;

Create database name;//creating library;

CREATE Table table name (field type, field 2 Type 2, field 3 Type 3 ...) ）；

Common types:

Character type:

char (): fixed length, case insensitive;

varchar (): Non-fixed length, case insensitive;

Binary (): fixed length, case sensitive;

varbinary (): Non-fixed length, case-sensitive;

Text (): For large paragraphs of text, not case-sensitive;

Digital type:

Integral type:

tinyint: micro-shaping, one byte, range: -128--+127;

smallint: Small integer, two bytes, range: -32768--+32767;

Mediumint: Medium integer, three bytes;

int: integral type, four bytes;

bigint: Large integer, eight bytes;

Floating point type:

FLOAT: single-precision floating-point type;

Double: Dual precision floating point type;

Time Type:

Date: Dates;

Time:;

DateTime: Date time;

Timestamp: time stamp;

Other:

Enum: Enumeration type, a variety of select one, a single radio;

Set: Set, multi-choice, multi-select;

Field Modifiers:

Null: The expression can be empty;

Not NULL: cannot be empty;

UNSIGNED: positive;

Default: Defaults;

Auto_increment: Auto-grow, cannot be used with default values;

PRIMARY key: Primary key;

Unique key: the only keys;

Example:mysql> CREATE TABLE tb_name (Id int not NULL auto_increment PRIMARY key,name char (a) not null,age tinyint not null,g Ender ENUM (' F ', ' M ') DEFAULT ' F ' not NULL);

Insert: Inserts, edits table data;

Format: INSERT into table name (Field 1, Field 2, Field 3, ...). ) Value (value 1, value 2, value 3, ...) ), (value 1, value 2, value 3, ...). ）.... ；

Example:mysql> INSERT into Tb_name (name,age,gender) VALUE (' Apple ', +, ' m '), (' Bider ', +, ' m '), (' Cat ', +, ' F ');

WHERE: Used for conditional matching operations, matching a field for conditional testing;

Where can be logically tested;

SELECT age from Tb_name WHERE age < 18;

Like: a string comparison test;

SELECT name from Tb_name WHERE Name is like '%t ';

%: matches any character of any length;

_: matches any single character;

UPDATE: Modify the operation;

UPDATE table name SET field = value WHERE field condition;//The conditions here vary depending on the type of field to be changed if the character type is used like, other types are not;

mysql> SHOW CREATE TABLE text2; Displays the statement used when creating the table Text2;

Mysql> SHOW ENGINES; View the storage engines supported by the database;

Mysql> Show Table Status\g//View status information for each table;

Mysql> Show Table status like '%s '//View status information for tables ending with S;

Character set: The mapping of byte encoding to Chinese characters;

GBK

GB2312

GB18030

UTF-8

mysql> SHOW CHARACTER SET; view character set;

There are a number of different collations that can exist for each character set:

Mysql> SHOW COLLATION; View sorting rules;

GRANT all privileges the db_name.tb_name to ' username ' @ ' hostname ' identified by ' your_password ';//configure permissions;

MySQL installation and basic use;