MySQL database permissions, index basic operations

How to close the database:

1. Graceful way to close the database:
mysqladmin-uroot-p123456 shutdown

2. Script Close:
/etc/init.d/mysqld stop

3. Method of using kill signal (preferably not used)
KILL-USR2 ' Cat path/pid '

History does not record historical commands

Histcontrol=ignorespace (when the command is hit with a space, can not be recorded)

Good use of MySQL Help

How to set the password for MySQL:
Mysqladmin-uroot password ' 123456 '

Mysqladmin-uroot password ' 123456 '-s/data/3306/mysql.sock (multi-instance password setting)

MySQL Change Password:

mysqladmin-uroot-p123456 password ' template '

mysqladmin-uroot-p123456 password ' template '-s/data/3306/mysql.sock (multi-instance Change password)

To modify a password using an SQL statement:

Mysql>update mysql.user SET Password=password ("123456") WHERE user= ' root ';

Set Method:
Set Password=password (' 123456 ')

MySQL password forgot to retrieve steps:

1. Stop the database First
/etc/init.d/mysqld stop

2. Use--skip-grant-tables to start MySQL, ignoring authorization login verification

[Email protected] ~]# mysqld_safe--skip-grant-tables--user=mysql &

Blank Password Login:
[[email protected] ~]# MySQL

Change Password:
Mysql>update Mysql.user Set Password=password ("123456") where user= ' root ' and host= ' localhost ';

Refresh permissions:
Mysql>flush privileges;

Turn off MySQL
[Email protected] ~]# mysqladmin-uroot-p123456 shutdown

Restart MySQL
[[email protected] ~]#/etc/init.d/mysqld start

[Email protected] ~]# mysql-uroot-p123456

SQL Structured Query Language SQL structured queries language

Simple database operation:

Specify a character set when creating a database:
Mysql> CREATE DATABASE TEMPLATE_GBK default Character set GBK

Mysql> Show CREATE Database TEMPLATE_GBK

mysql> show databases;

Mysql> Select Database (); View the currently connected database

Mysql> Select Now (); View current Time

Mysql> Select User (); View Current User

Mysql> select version (); View current version

Mysql> show tables; View table

Delete MySQL System surplus account

Drop user ' template ' @ ' localhost '; Delete User
If the drop is not deleted (typically special characters or uppercase), you can delete it using the following method
Mysql> Delete from mysql.user where user= ' root ' and host= ' localhost ';
mysql> flush Privileges;

Create MySQL users and empower users
Mysql> Grant all on test.* to ' template ' @ ' localhost ' identified by ' 123456 ';

mysql> flush Privileges;

Mysql> show grants for ' template ' @ ' localhost '; View Permissions

Create and Grant mate methods

mysql> create user ' template ' @ ' localhost ' identefied by ' 123456 ';

Mysql> Grant all on dbname.* to ' username ' @ ' localhost '; #赋予权限

#授权局域网内主机远程连接数据库

% matching method
Mysql> Grant All on * * to ' someuser ' @ ' percent ' identified by ' 123456 ';

Subnet Mask Matching method
Mysql> Grant All on * * to ' someuser ' @ ' 10.0.0.0/255.255.255.0 ' identified by ' 123456 ';

Mysql-uroot-p123456-h 10.0.0.7-p 3306 Remote Connection

Revoke insert on test.* from ' template ' @ ' localhost '; Revoke permissions

Mysql> Show grants for [email protected] ' localhost '; View Permissions

1 SELECT
2 INSERT
3 UPDATE
4 DELETE
5 CREATE
6 DROP
7 REFERENCES
8 INDEX
9 ALTER
Ten CREATE temporary TABLES
One LOCK TABLES
EXECUTE
CREATE VIEW
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
+ EVENT
TRIGGER

Mysql> select * from Mysql.user;

Insert (increment), delete (delete), update (change), select (check)

To reclaim the Create and drop authorizations after the database table is generated

mysql> CREATE database template; Create a database

Mysql> show create database template; View the build library information
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| Template | CREATE DATABASE ' template '/*!40100 DEFAULT CHARACTER SET UTF8 */|
+----------+-------------------------------------------------------------------+
1 row in Set (0.00 sec)

Build table:
CREATE table < table name > (
< field name 1>< type 1>
...
< field name n>< type n>);

To build a table statement:

CREATE TABLE Student (

ID Int (4) is not NULL,

Name Char (a) is not NULL,

Age tinyint (2) is not null default ' 0 ',

Dept varchar (+) default null

);

Mysql> Show CREATE TABLE Student\g
1. Row ***************************
Table:student
Create table:create Table ' student ' (
' ID ' int (4) is not NULL,
' Name ' char (a) is not NULL,
' Age ' tinyint (2) is not NULL DEFAULT ' 0 ',
' Dept ' varchar (+) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)

mysql> desc Student; View table Structure
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID | Int (4) | NO | | NULL | |
| name | char (20) | NO | | NULL | |
| Age | tinyint (2) | NO | | 0 | |
| Dept | varchar (16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

Mysql> show columns from student; View table Structure

An index is like a directory of books, and if indexed on a field, it can speed up querying data when indexed as a query condition.

Create a primary key index

Querying the database, by primary key index, is the quickest, and each table can have only one primary key column, but there may be more than one normal index column. The primary key column requires that all the contents of the column must be unique, and the indexed column does not require that the content be unique

Mysql> CREATE TABLE Student (
ID int (4) NOT NULL auto_increment, #自增
Name Char (a) is not NULL,
Age tinyint (2) is not null default ' 0 ',
Dept varchar (+) default NULL,
Primary key (ID),
KEY index_name (name) #创建索引
);

Increase the primary key index by the ALTER command after the table is built (do not recommend doing so)
mysql> ALTER TABLE student change ID ID int primary key auto_increment;

mysql> ALTER TABLE student DROP INDEX index_name; Delete Index

Mysql> DROP index index_dept on student; Delete Index

Mysql> ALTER TABLE student Add index index_name (name); Add a normal index

Mysql> CREATE index index_dept on student (Dept (8)); Specify first n characters to create an index

Mysql> Show index from STUDENT\G view indexes

Mysql> CREATE index idn_name_dept on student (name,dept); Create a federated index that allows its own index on the column

Mysql> CREATE index ind_name_dept on student (name (8), Dept (10)); Create a federated index based on the first n characters of multiple columns

Mysql> Create unique index uni_ind_name on student (name); Create a unique index

Question 1: Since the index can speed up the query, then index all the columns?

Answer: Because the index not only occupies space, update the database also need to maintain the index data, so the index to try a double-edged sword, not the more the better, for example: Dozens of to hundreds of rows of small tables do not need to index, write frequently, read less business to build less index

2: What columns do you create indexes on?

Answer: Select User,host from Mysql.user where host= ..., the index must be created in the where after the condition column, rather than select the column of the selection data, in addition, to try to choose a large number of unique values on the index.

Basic conditions:

1. To create an index on a table column

2, the index will speed up the query, but will affect the speed of the update, because to maintain the index

3, the index is not as much as possible, to create an index on the condition column after the frequently queried where

4. Do not index on columns with small tables or unique values, to create indexes on large tables and columns with different contents

Help ALTER TABLE

MySQL database permissions, index basic operations