MySQL Common operations and exercises

To create a database:
Create DATABASE KALIBOY_GBK DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

Create DATABASE Kaliboy_utf8 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

To display the database:
show databases;

Show databases like '%kaliboy% ';

Connect to display the database:
Use Kaliboy;

To view the connection display database:
Select Database ();

To delete a database:
Drop database Kaliboy;

Select version ();

Select User ();
Select Now ();

Delete the MySQL system surplus account:
Syntax: Drop user "user" @ "host Domain"

Drop user "kali" @ "localhost"
Delete from Mysql.user where= "root" and host= "localhost";

Create a user with the grant command and authorize
The simple syntax for the grant command is as follows:
Grant all privileges the dname.* to [e-mail protected] identified by ' passwd ';
Authorization commands correspond to permissions target: library and table user name and client host user password

Instance:
Grant all privileges the test.* to [e-mail protected] identified by ' kali123 ';
Flush privileges;

To view permissions:
Show grants for [email protected];

Create and Grant mate methods
First create user username and password passwd, authorize host localhost
Create USER ' wiki ' @ ' localhost ' identified by ' passwd ';

Then authorize all permissions on the localhost host through user username to manage the dbname database without a password
Grant all privileges on dbname.* to ' username ' @ ' localhost ';

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

MySQL Interactive parameter-E

To create a database table:

CREATE TABLE Subject_comment_manager (
subject_comment_manager_id bigint () not NULL auto_increment comment ' primary key ',
Subject_type tinyint (2) not NULL COMMENT ' footage type ',
Subject_primary_key varchar (255) not NULL COMMENT ' subject of footage ',
Subject_title varchar (255) Not NULL COMMENT ' The name of the footage ',
Edit_user_nick varchar (+) default NULL COMMENT ' modified person ',
Edit_user_time timestamp null DEFAULT null COMMENT ' modified time ',
Edit_comment varchar (255) Default NULL comment ' Reason for modification ',
State tinyint (1) Not NULL default ' 1 ' COMMENT ' 0 for off, 1 for normal ',
PRIMARY key (' subject_comment_manager_id '), primary key
Key ' Idx_primarykey ' (' Subject_primary_key ' (32)), index
KEY ' Idx_subject_title ' (' Subject_title ' (32)) index
) Engine=innodb auto_increment=1 DEFAULT Charset=utf8;

Database index:
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,
Priary key (ID), PRIMARY key
Key index_name (name) index
);

To delete a primary key:
ALTER TABLE student drop PRIMARY key;

To add a primary key:
ALTER TABLE student change ID ID int primary key auto_increment;

Delete Normal index
ALTER TABLE student DROP INDEX Index_name

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

Specify first n characters to create an index

Create INDEX index_dept on student (Dept (8));

View index:

Show index from Student\g

To create a federated index:

Create INDEX ind_name_dept on student (name,dept);

Create a unique index (non-primary key)

Create UNIQUE index Index_name

Insert data into the table:

CREATE TABLE Test (
ID int (4) not NULL auto_increment,
Name Char (a) is not NULL,
PRIMARY KEY (ID)
) Engine=innodb DEFAULT Charset=utf8;

Inert into Test (id,name) VALUES (1, ' wiki ');

Inert into test values (2, ' Teachar '), (3, ' student ');

Query data:

To query the SELECT query statement execution plan using explain

To modify data in a table:

Update test set name= "High round Circle" where id= "3";

Flush-log Binary bin Log Cutting

Prevent database misuse:
Login Plus-u parameter

Exercises:

Multi-Instance Login-S Specify Mysql.sock path
Mysql-u root-p-s/tmp/mysql.sock

View the database version and what the logged-on user is.
Select version ();

Select User ();

Create a database Kaliboy of the GBK character set and view the complete statement of the built library

Learn to use Help to view help create DATABASE, show character set

Create DATABASE Kaliboy CHARACTER SET GBK COLLATE gbk_chinese_ci;

Show CREATE Database Kaliboy;

Create a user kaliboy so that it can manage the database Kaliboy

Grant all on kaliboy.* to [e-mail protected] "localhost" identified by "kali123";

Flush privileges;

To view the user's authorization

Show grants for [email protected];

See which users are in the current database

Select Host,user from Mysql.user;

Go in Kaliboy Database

Use Kaliboy;

Create InnoDB engine character set for GBK table test, field ID and name varchar (16), view build table structure and SQL statement

CREATE TABLE Test (
ID int (4),
Name varchar (16))
-Engine=innodb default CHARSET=GBK;

CREATE table position (ID int (4), name varchar (ten), limit varchar) engine=innodb default CHARSET=GBK;

Show CREATE TABLE test\g;

Insert a piece of data 1 kaliboy

INSERT into test values (1, ' kaliboy ');

Bulk INSERT Data 2, Playboy. 3,etiantian

INSERT into test values (2, ' Script Boy '), (3, ' Tiantian '), (4, ' Xiaoqian '), (5, "security Engineer");

Query for all inserted records, query records whose name is Kaliboy with a record query ID greater than 1

SELECT * from Test;

SELECT * FROM Test where name= "Kaliboy";

SELECT * FROM Test where id>1;

Change the name of the data ID equal to 1 to Kaliboy to Kaligirl;

Update test set name= "Kaligirl" where id=1;

Insert the Age field before the field name, type int (4)

ALTER TABLE test add age int (4) after ID;

Backing up the Kaliboy library with MySQL Library

Mysqldump-u root-p123456--events-b Kaliboy mysql>kali.sql

Delete all the data in the table and view

Use Kaliboy;

Delete from MySQL;

select * from MySQL;

TRUNCATE TABLE MySQL;

Delete the table test and Kaliboy database and view

drop database test;

Linux command line recovers the data deleted above

Mysql-u Root-p123456<kali.sql

Modify the GBK character set to UTF8

Modify the encoding format of the database: Alter DB user character set GBK;

Echo $LANG

MySQL password lost, how to find the actual combat

/etc/init.d/mysqld stop
Mysqld_safe--defaults-file=/etc/my.cnf--skip-grant-table &

Update Mysql.user Set Password=password ("123456") where user= "root";

The principle of garbled Chinese data in MySQL and how to prevent garbled characters

When setting the ID column as the primary key, create a normal index on the name field

ALTER TABLE tast add primary key (ID);

Create a normal index on the phone field for the first 8 characters

ALTER TABLE Test add index Show (Shou (8));

To view the index and index type information created

Show index from Test\g;

To delete the index of a Name,shouji column

ALTER TABLE test DROP INDEX name;

To build a federated index on the first 6 characters of the Name column and the first 8 characters of the phone column

ALTER TABLE test add index Lianhe (name (6), Shou (8));

Check the phone number starts with 135, the record with the name Kaliboy

SELECT * FROM Test where name= "Kaliboy" and Shou like "135%";

Query the execution plan for the above statement (whether to use a federated index, etc.)

Explain select * FROM Test where name= "Kaliboy" Shou like "135%";

Create a primary key index

ALTER TABLE test change ID ID int primary key auto_increment;

To delete an index:

ALTER TABLE test DROP INDEX index_name;

To add an index:

ALTER TABLE test add index index_name (name);

To create an index on the first few characters:
Grammar

CREATE index index_ (column name) on (table name) (column name (8))

CREATE INDEX index_dept on test (Dept (8));

To view the database size:

To know the size of each database, proceed as follows:

1. Access to the INFORMATION_SCHEMA database (information on other databases)

Use INFORMATION_SCHEMA;

2, query the size of all data:

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as data from tables;

3. View the size of the specified database:

Like looking at the size of the database home

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as data from tables where table_schema= ' home ';

4. View the size of a table in a specified database

For example, view the size of the members table in the database home

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as data from tables where table_schema= ' home ' and Table_name= ' Members ';

MySQL Common operations and exercises