MySQL db manual (MySQL database manual) _mysql

2.1 Login to MySQL console [username is root, password defaults to NULL]
Switch to MySQL's Bin directory cd D:/wamp/mysql/bin
Login to MySQL Console
Syntax: mysql-h hostname-u username-p
Connecting to another host
Code: Mysql-h 192.168.1.1-u root-p
Password: Press ENTER directly
Connecting to this machine
Code: Mysql-h 127.0.0.1-u root-p
Password: Press ENTER directly
appear mysql> to login to MySQL console success
2.2 Exit MySQL Console
mysql>exit;
/***********************************************************/
3.1 Check all MySQL database list;
Grammar:mysql> show databases;
Code:mysql> show Databases;

3.2 Display a list of all the tables in the database
View tables in the current database
Grammar 1:mysql> show tables;
Code 1:mysql> show Tables;

3.3 View tables in other database Jxc
Grammar 1:mysql> show tables from DatabaseName;
Code 1:mysql> show tables from JXC;
/***********************************************************/
4. Create/delete/select database
To create a JXC database:
Syntax:mysql> CREATE database databasename;
Code:mysql> CREATE DATABASE Jxc;
To 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 Customers structure
Grammar 1:mysql> describe TableName;
Code 1:mysql> describe customers;
5.2.show Columns View Table customers structure
Grammar 1:mysql> show columns from TableName;
Code 1:mysql> show columns from customers;

5.3. View the data structure of a specified column name for a table
Syntax 1:mysql> Show index from tablename column;
Code 1:mysql> Show index from customers name;

5.4. View the index of a table customers
Syntax 1:mysql> Show index from TableName;
Code 1:mysql> show index from customers;

6. Data common operation (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 Insertion
Syntax: INSERT INTO table1 (column1,column,,,) VALUES (value1,value2,,,);
Code: INSERT INTO Books (Isbn,author,title,price) VALUES (' iso-902126 ', ' Jahn. D ', ' mysql6.0 ', 99.0);

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 the books where isbn= ' iso-902126 ';

6.5 Other methods
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle
Sort: SELECT * from table1 ordered 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. Users and permissions to create a database with Grant
Grant Command syntax:
GRANT [License list 1],[license 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 ' 201314 ';
Function description
Put all the tables in the database Discuz select,insert,delete,update these 4 permissions
Add to new user Jake, password is ' 201314 ';

Code Implementation 2:
Grant All
On discuz.* to Tom@localhost
Identified by ' 123456 ';
Add all the tables in the database Discuz to the new user Tom, the password is ' 123456 ';

The License right list 1 option is as follows
Select Table, Column
Insert Table, column
Udpate table, column
Delete Table
Index Table
ALTER TABLE
Create DATABASE, table
Drop DATABASE, table


The License right List 2 option is as follows
Create temporary tables allow the use of temporary keywords
File allows databases to be imported and exported to files
Lock tables allows you to use the Lock Talbes command
Reload allows the authorization table to be loaded again
Show databases allows you to view all database listings
Shutdown allows you to use shutdown MySQL

All above so permission
Usage allows you to log on only but does not allow any actions

[Database. Table name] options are as follows
Database. Table name Select a table in the database for XX users
Database. * Select all tables in the database to XX users

/***********************************************************/
8.revoke Remove user and user permissions
Revoke format:
Revoke [License list 1],[license List 2] privileges,[columns]
on [Database. Table name]
From [user name @ host name]

Code:
First authorize to Laoliu (Lao Liu)
Grant All
On books.*
To Laoliu
Identified by ' Laoliu11 ';

To scatter a part of the authority
Revoke Alter,create,drop
On books.*
From Laoliu;
All permission to scatter Laoliu.
Revoke all
On books.*
From Laoliu;

/***********************************************************/
9. Add MySQL User other methods
shell> mysql-u root-p1234 MySQL
mysql> INSERT INTO User (Host,user,password) values (' localhost ', ' backup ', ' databse ');
Add a MySQL user backup from this computer with a password of: 1234

Shell>mysql–u root–p
Mysql>grant FILE on *.* to backup@192.168.1.200 identified by ' 1234 ';
Mysql>/exit
Open an account backup password 1234 gives access to file processing from ip:192.168.1.200


/***********************************************************/
10. CREATE Table/modify table/delete table//optimize table

10.1 Creating a Table
Syntax: CREATE TABLE tablename (columns,...)
Code:
CREATE TABLE Order_items
(orderid int unsigned NOT NULL,
ISBN char (not null),
Quantity tinyint unsigned,
Primary KEY (ORDERID,ISBN)
);

10.2 Modifying the table
10.2.1 Add/Remove a column
Syntax: ALTER TABLE [table name] Add column [column name] [type];
Add a column to a table remark
Code: ALTER TABLE order_items add column remark char (50);
Delete a column
Syntax: ALTER TABLE [table name] Drop column [column name];
Deletes a column from a table remark
ALTER TABLE order_items drop column remark;

10.2.2 Add/Remove primary key
Add ORDERID,ISBN as 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 Establish/Delete index
Establish 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 Table:
Delete Table
Syntax: drop table [table name]
Code: drop table orders;

10.4 Optimization Table:
When a table has tens of thousands of rows of data, and access slows down, it must be optimized for them
The usual way is to make a optmize.sql file,
Import this optimization script file directly to bulk optimize some key tables to improve access speed

Optimize table Customers data (customers)
Syntax: Mysql>optmize table tablename;
Code: Mysql>optmize table Customers;

10.5 Loading a New_tb.sql file with the command line
So that MySQL can execute a batch of SQL statements in the *sql file at a time
1. The text file completes the SQL command collection, and then copies it to the command line.
2. If the table is too much, save it as a *.sql file, and then load the file with the command

Format: mysql-h [Host IP]-u [user name]-d [Database name]-p < [*.sql file name in this directory]
Run cmd
CD D:/wamp/mysql/bin
Mysql-h 127.0.0.1-u root-d pubs-p <new_tb.sql;
The top is to use MySQL to load the D:/wamp/mysql/bin/new_tb.sql file into the books database,
Note: Database pubs must exist and-D to capitalize

New_tb.sql file contents (SQL commands for this file can be stored in more than 1000 items)
CREATE TABLE Customers
(customerid int unsigned NOT NULL Auto_increment primary key,
Name char (not NULL),
Address char (m) NOT NULL,
City Char is not NULL
);

CREATE TABLE Orders
(orderid int unsigned NOT NULL Auto_increment primary key,
CustomerID int unsigned NOT NULL,
Amount float (6,2),
Date date NOT NULL
);

CREATE TABLE Books
(ISBN char () 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 (not null),
Quantity tinyint unsigned,
Primary KEY (ORDERID,ISBN)
);

CREATE TABLE Book_reviews
(ISBN char () NOT NULL primary key,
Review text
);

Does the check table automatically create OK after the OK is performed?
C:>mysql-h 127.0.0.1-u Root-p
Mysql>show tables from pubs;
The results show that all the above 5 tables have been set up OK;
/***********************************************************/

11. Table View establishment and deletion
Building a 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
/***********************************************************/