Mysql database manual (mysql database manual)

2.1 log on to the mysql console [the user name is root, and the password is blank by default]
Switch to the mysql bin directory cd d:/wamp/mysql/bin
Log on to the mysql Console
Syntax: mysql-h hostname-u username-p
Connect to another host
Code: mysql-h 192.168.1.1-u root-p
Password: Press enter.
Connect to Local Machine
Code: mysql-h 127.0.0.1-u root-p
Password: Press enter.
Mysql> indicates that the logon to the mysql console is successful.
2.2 log out of the mysql Console
Mysql> exit;
/*************************************** ********************/
3.1 check the list of all mysql databases;
Syntax: mysql> show databases;
Code: mysql> show databases;

3.2 display the list of all tables in the database
View tables in the current database
Syntax 1: mysql> show tables;
Code 1: mysql> show tables;

3.3 View tables in other databases jxc
Syntax 1: mysql> show tables from databasename;
Code 1: mysql> show tables from jxc;
/*************************************** ********************/
4. Create/delete/select a database
Create a jxc database:
Syntax: mysql> create database databasename;
Code: mysql> create database jxc;
Delete A jxc database:
Syntax: mysql> drop database databasename;
Code: mysql> create database abc;
Code: mysql> drop database abc;
Select jxc database:
Syntax: mysql> use database;
Code: mysql> use jxc;
/*************************************** ********************/

5. view the data structure of a table
5.1 describte View table MERs Structure
Syntax 1: mysql> describe tablename;
Code 1: mysql> describe MERs MERS;
5.2.show columns view the customers table structure
Syntax 1: mysql> show columns from tablename;
Code 1: mysql> show columns from MERs MERS;

. View the data structure of the specified column name in a table
Syntax 1: mysql> show index from tablename column;
Code 1: mysql> show index from MERs name;

5. 4. view the customers index of a table
Syntax 1: mysql> show index from tablename;
Code 1: mysql> show index from MERs MERS;

6. Common Data Operations (select, insert, update, delete)
6.1 select:
Syntax: select * from [Table Name 1, table name 1,] where [condition range]
Code: select * from orders where orderid> 100;

6.2 insert
Syntax: insert into table1 (column1, column,) values (value1, value2 ,,,);
Code: insert into books (isbn, author, title, price) values ('iso-000000', 'jahn. D', 'mysql6. 0', 902126 );

6.3 update:
Syntax: update table1 set [column name] = [new data] where [condition range]
Code: update books set title = "Thinking in Java" where isbn = 'iso-902126 ';

6.4 delete:
Syntax: delete from [Table name] where [condition range]
Code: delete from books where isbn = 'iso-902126 ';

6.5 other methods
Query: select * from table1 where field1 like '% value1 %' --- the like syntax is exquisite.
Sort: select * from table1 order by field1, field2 [desc]
Total: select count as totalcount from table1
Sum: select sum (field1) as sumvalue from table1
Average: select avg (field1) as avgvalue from table1
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1

/*************************************** ********************/

7. Use grant to create database users and permissions
GRANT command syntax:
GRANT [permission List 1], [permission List 2]
ON [database. Table name]
TO [user name @ host name]
Identified by 'Password ';

Code Implementation 1:
Grant select, insert, delete, update
On discuz. * to jake @ localhost
Identified by '20140901 ';
Function Description
Select, insert, delete, and update permissions for all tables in the discuz Database
Add the password to the new user, "201314 ';

Code Implementation 2:
Grant all
On discuz. * to tom @ localhost
Identified by '20140901 ';
Add all the tables in the database discuz permission to the new user tom with the password '123456 ';

[Permission List 1] Options:
Select Table, column
Insert table, column
Udpate Table, column
Delete table
Index Table
Alter TABLE
Create Database, table
Drop DATABASE, table

[Permission List 2] Options:
Create temporary tables allow the use of the temporary keyword
File allows the database to import and export data to files
Lock tables allows the use of the lock talbes command
Reload allows re-loading the authorization table
Show databases allows you to view all database lists
Shutdown allows you to disable MYSQL

All.
Usage allows logon only, but does not allow any operation

The [database. Table name] option is as follows:
Database. Select a table in the database for the XX user
Database. * select all tables in the database to XX users.

/*************************************** ********************/
8. revoke cancels user and User Permissions
Revoke format:
Revoke [permission List 1], [permission List 2] privileges, [columns]
ON [database. Table name]
FROM [user name @ host name]

Code:
Grant permissions to laoliu first (laoliu)
Grant all
On books .*
To laoliu
Identified by 'laoliu11 ';

Revoke some Permissions
Revoke alter, create, drop
On books .*
From laoliu;
All permissions of laoliu
Revoke all
On books .*
From laoliu;

/*************************************** ********************/
9. Add other methods for MYSQL users
Shell> mysql-u root-p1234 mysql
Mysql> insert into user (Host, User, Password) values ('localhost', 'backup ', 'databse ');
Add a mysql user backup from the local machine with the password 1234

Shell> mysql-u root-p
Mysql> grant file on *. * TO backup@192.168.1.200 identified by '20140901 ';
Mysql>/exit
Open an account backup password 1234 to the IP address 192.168.1.200 with the permission to process the file

/*************************************** ********************/
10. Create/modify/delete a table // optimize a table

10.1 create a table
Syntax: create table tablename (columns ,...)
Code:
Create table order_items
(Orderid int unsigned not null,
Isbn char (13) not null,
Quantity tinyint unsigned,
Primary key (orderid, isbn)
);

10.2 modify a table
10.2.1 Add/delete a column
Syntax: alter table [table name] add column [column name] [type];
Add a remark column to the table
Code: alter table order_items add column remark char (50 );
Delete a column
Syntax: alter table [table name] drop column [column name];
Delete A remark column from the table
Alter table order_items drop column remark;

10.2.2 Add/delete a primary key
Add orderid and isbn as the primary key
Syntax: alter table [table name] add primary key [column name 1, column name 1];
Code: alter table order_items add primary key (orderid, isbn );
Delete primary key
Syntax: alter table [table name] drop primary key
Code: Alter table tabname drop primary key

10.2.3 create/delete an index
Create an index
Syntax: create index [index name] on [Table name] (column name );
Code: create index orderid_ix on orders (orderid );
Delete Index
Syntax: drop index [index name] on [Table name] (column name );
Code: drop index orderid_ix on orders;

10.3 delete a table:
Delete table
Syntax: drop table [table name]
Code: drop table orders;

10.4 optimization table:
When there are tens of thousands of rows of data in a table, the access speed is slow and they must be optimized.
The common method is to create an optmize. SQL file,
Import the optimization script file directly to optimize some key tables in batches to speed up access.

Optimize table MERs data (customers)
Syntax: mysql> optmize table tablename;
Code: mysql> optmize table MERs MERS;

10.5 use the command line to load a new_tb. SQL File
This allows MYSQL to execute SQL statements in batches in the * SQL file.
1. Complete the SQL command set in the text file and copy it to the command line for one-by-one execution.
2. If there are too many tables, save them as *. SQL files and load the files with commands.

Format: mysql-h [Host IP address]-u [user name]-D [database name]-p <[name of *. SQL file in this directory]
Run cmd
Cd d:/wamp/mysql/bin
Mysql-h 127.0.0.1-u root-D pubs-p <new_tb. SQL;
Mysql is used to load the d:/wamp/mysql/bin/new_tb. SQL file to the books database,
Note: The database pubs must exist and-D must be capitalized.

New_tb. SQL file content (more than 1000 SQL commands can be saved in this file)
Create table MERs
(Customerid int unsigned not null auto_increment primary key,
Name char (50) not null,
Address char (100) not null,
City char (30) not null
);

Create table orders
(Orderid int unsigned not null auto_increment primary key,
Customerid int unsigned not null,
Amount float (6, 2 ),
Date not null
);

Create table books
(Isbn char (13) not null primary key,
Author char (50 ),
Title char (100 ),
Price float (6, 2)
);

Create table order_items
(Orderid int unsigned not null,
Isbn char (13) not null,
Quantity tinyint unsigned,
Primary key (orderid, isbn)
);

Create table book_reviews
(Isbn char (13) not null primary key,
Review text
);

After running OK, check whether the checklist is automatically created?
C:> mysql-h 127.0.0.1-u root-p
Mysql> show tables from pubs;
The results show that the above five tables have been created with OK;
/*************************************** ********************/

11. Create and delete a table View
Create View
Syntax: create iview [view name] as [select statement );
Code: create view v_orders as select * from orders;
Delete View
Syntax: drop iview [view name]
Code: create view v_orders
/*************************************** ********************/