MySQL Database Basic Learning Notes

First, the installation and initialization of MySQL:

To install the MySQL command:

Yum install-y mysql-server MySQL mysql-devel

Initialization

Service mysqld Start //The first start of the MYSQLD services will be initialized automatically:

Create a user and initialize the password:

mysqladmin-u root passwd ' password '

Log in to MySQL:

mysql-u root-p

Exit MySQL:

quit, Exit

Set mysqld service self-boot:

chkconfig mysqld on

Related files for MySQL:

/etc/my.cnf mysql master configuration file;

/var/lib/mysql/:mysql the database directory storage location;

Log file for/var/log/mysqld.log:mysqld;

MySQL default TCP port: 3306

To connect to a remote MySQL server:

mysql-h ip-u id-p //-h parameter Specify connection address, default connection local localhost

The SQL statement default command is in uppercase (case insensitive),

SELECT version (); View database version;

SELECT current--date; View current time,

Select supports querying multiple parameters with, separate;

Select can also be used for calculation (example: select 4*4;)

The database name cannot be changed after it is created.

Second, SQL statements commonly used commands:

To create a database:

CREATE DATABASE database_name;

To delete a database:

DROP DATABASE database_name;

To view the current database:

SHOW DATABASES;

Enter the database:

Use database_name;

To view the current table:

SHOW TABLES;

To create a table:

CREATE TABLE table_name (* *); * * Indicates the columns and data types created

To view the columns and data types of a table:

DESCRIBE table_name; Describe can be abbreviated DESC

Add data to the table:

INSERT into table_name values (value 1, value 2 ...). ）；

INSERT into table_name (column 1, column 2 ...) Values (value 1, value 2 ...). ）;

Character values need to be enclosed in single quotation marks, preceded by commas, and the last one without commas

To delete a table:

DROP TABLE table_name;

Rename Table name:

ALTER TABLE table_name RENAME new_tabnle_name;

Add a column to the table:

ALTER TABLE table_name ADD * *; * * Column name and data type

Delete a column:

ALTER TABLE table_name DROP column * *;//** columns name

To modify a data type:

ALTER TABLE table_name MODIFY * *; * * Column name + new data type

Rename a column:

ALTER TABLE table_name Change Column # # * *;//# #旧列名称, * * NEW column name + data type

Query records by criteria from the table:

SELECT column name from table_name WHERE column operator value;//Column name: * denotes all columns,

Where the operator is supported:

= equals,

<> not equal to,

> Greater Than,

< less than,

>= greater than or equal,

<= less than Equals,

Between in between,

LIKW Searching for a pattern

The query returns the result of deleting duplicates:

SELECT DISTINCT column name from table_name;

A logical combination is used in the Where condition:

SELECT * FROM table_name WHERE condition one and\or condition 2;

And logic and: must conform to two conditions, or logic or: meet a condition can

To sort the results of a query by a specified column:

SELECT column name from the table_name ORDER by column name;

SELECT * FROM table_name ORDER by table name DESC;

Third, MySQL database user management

MySQL default only root one user;

MySQL saves the user information in the MySQL database user table;

To create a new user:

CREATE USER user_name identified by ' password ';

After the new user is created, there is no login permission because no permissions are set

To delete a user:

DROP user_name;

Rename User name:

RENAME user_name to New_user_name;

To modify a user password:

SET PASSWORD =password (' New_password ');

SET PASSWORD for user_name =password (' new_password ')//Modify user_name Password

Iv. MySQL Rights Management

Controls whether a user is connected and can operate on those objects after the connection

MySQL privilege control consists of two stages:

1. Check if the user is able to connect,

2. Check whether the user has permission to perform the action;

MySQL Authorization permission level:

1. Global level,

2. Database Hierarchy,

3. Surface level,

4. Column level,

5. Sub-program level;

MySQL grants permissions through GRANT, REVOKE revoke permissions

Authorizing a User:

GRANT all privileges the on level to [email protected] host identified by password;

Example: GRANT all privileges the ' user ' @ '% ' identified by ' password ';

*. *: First * database, second * table,% = ALL hosts

Remove a user right:

REVOKE all privileges from user_name;

MySQL connection authentication, host address type:

LocalHost local connection,% all remote hosts, exact hostname or IP, network segment, etc.;

V. Simple backup and Recovery of MySQL

To back up a specified database:

MYSQLDUMP-U root-p database_name > Backup name. sql

The MySQL backup file is a plain text file that can be modified slightly for use by other databases;

To restore a specified database:

mysql-u root-p Database < backup name. SQL

VI. MySQL database character encoding settings

Two aspects of the main impact of coding:

1. The amount of space the database holds for the same content,

2. Communication between the database and the client;//client and database character encoding must be consistent!

Common Database code:

Latin (Latin)//big5 (Traditional Chinese) gb2312 (Simplified Chinese), General UTF8;

To view MySQL-supported encodings:

SHOW CHARACTER SET;

MySQL default encoding:

CHARACTER Set:latin 1,

Collation:latin 1_swedish_ci;

To view the encoding currently being used by MySQL:

SHOW VARIABLES like ' character_set% ';

SHOW VARIABLES like ' collation& ';

Show VARIABLES View the current MySQL parameters.

Specify character encoding when creating a database:

>create DATABASE database_name

>default_character_set UTF8

>default_collate Utf8_general_ci;

To modify the character encoding of a database that has been created:

ALTER DATABASE database_name CHARACTERSET UTF8 COLLATE utf8_general_ci;

There is already data in the database, if modifying character encoding may cause data not to display properly;

To modify the MySQL default character encoding:

To modify the main configuration file my.cnf, add the following parameters to the corresponding location:

[Client]

Default-character-set=utf8

[MySQL]

Default-character-set=utf8

[Mysqld]

Default-character-set=utf8

Collation-server=utf8_unicode_ci

init-connect= ' SET NAMES UTF8 '

Character-set-server = UTF8

After modifying, restart the MySQL service and view the default encoding with the following command:

SHOW VARIABLES like ' character_set% ';

This article is from "An empty cup" blog, make sure to keep this source http://luchuanjia.blog.51cto.com/6640392/1439471