MySQL common command set MySQL common commands 5

Nine. Cross-query

A cross-query can calculate the sum, average, count, or other sum of the data, which is calculated by

Two kinds of information are grouped: one appears on the left side of the table and the other appears at the top of the table.

Microsoft Jet SQL uses the Transfrom statement to create the crosstab query syntax:

TRANSFORM aggfunction

Select statement

GROUP BY clause

PIVOT pivotfield[in (value1 [, value2[,...])]

Aggfounction refers to the SQL accumulation function,

Select statement selects the field that is the title,

GROUP BY group

Description

Pivotfield the field or expression to use when creating column headings in the query result set, with an optional in clause to restrict

its value.

Value represents a fixed value for creating a column header.

Example: shows the number of orders received by each employee in each quarter in 1996 years:

TRANSFORM Count (OrderID)

Select firstname& "&lastname as FullName

From Employees INNER JOIN orders

On employees.employeeid = orders. EmployeeID

Where DatePart ("yyyy", OrderDate) = ' 1996 '

GROUP by firstname& ' &lastname

OrDER by firstname& ' &lastname

Povot DatePart ("Q", OrderDate) & ' quarter '

10. Sub-query

A subquery can be understood as a set of queries. A subquery is a SELECT statement.

1 The value of the expression is compared to the singleton value returned by the subquery

Grammar:

Expression comparision [any| all| SOME] (sub-query)

Description

The any and some predicates are synonyms, with comparison operators (=,<,>, $amp;

A value of true or false.any means that an expression is compared to a series of values returned by a subquery, as long as its

A comparison in one produces a true result, and the any test returns a true value (both the result of the WHERE clause), corresponding to the

The current record of the expression is entered into the result of the main query. The all test requires that the expression and the subquery return the

The comparison of a series of values yields a true result before returning a true value.

Example: The main query returns all products with a unit price higher than the unit price of any product with a discount greater than or equal to 25%

Select * FROM Products

Where Unitprice>any

(Select UnitPrice from[order Details] Where discount>0.25)

2 checks whether the value of an expression matches a value of a set of values returned by a subquery

Grammar:

[NOT] In (sub-query)

Example: Returns a product with an inventory value greater than or equal to 1000.

Select ProductName from Products

Where ProductID in

(Select prdoctid from [Order DEtails]

Where unitprice*quantity>= 1000)

3 Detect if subqueries return any records

Grammar:

[NOT] EXISTS (sub-query)

Example: Using exists to retrieve British customers

Select Companyname,contactname

From Orders

Where EXISTS

(Select *

From Customers

Where country = ' UK ' and

Customers.customerid= orders. CustomerID)

1: Use the show statement to find out what database currently exists on the server:

Mysql> SHOW DATABASES;

2:2. Create a database Mysqldata

mysql> Create DATABASE Mysqldata;

3: Select the database you created

mysql> use Mysqldata; (press ENTER to appear database changed operation success!)

4: See what tables exist in the current database

Mysql> SHOW TABLES;

5: Create a database table

Mysql> Create TABLE MYTABLE (name VARCHAR), sex CHAR (1));

6: Show the structure of the table:

Mysql> DESCRIBE MYTABLE;

7: Add a record to the table

mysql> INSERT INTO MYTABLE values ("HyQ", "M");

8: Loading data into a database table in text mode (for example, D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;

9: Import. sql File command (for example, D:/mysql.sql)

Mysql>use database;

Mysql>source D:/mysql.sql;

10: Delete Table

Mysql>drop TABLE MYTABLE;

11: Clear the table

Mysql>delete from MYTABLE;

12: Update data in table

Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

Here are some of the management tips that you can inadvertently see on the web using MySQL,

From

In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.

The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:

Use MySQL;

Delete from User where user= "";

Update User set Password=password (' NewPassword ') where user= ' root ';

If you want to restrict the logon terminal used by users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:

Mysql-uroot-p;

Mysql-uroot-pnewpassword;

MySQL mydb-uroot-p;

MySQL Mydb-uroot-pnewpassword;

The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.

In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command. The common usage of grant is as follows:

Grant all on mydb.* to [e-mail protected] identified by "password";

Grant Usage on * * to [e-mail protected] identified by "password";

Grant Select,insert,update on mydb.* to [e-mail protected] identified by "password";

Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";

To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should be given clearance, the permission of the user should be in a timely manner to reclaim permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.

The following gives me the explanation of the common permissions obtained from other materials ():

Global Administrative permissions:

File: Read and write files on the MySQL server.

PROCESS: Displays or kills service threads belonging to other users.

RELOAD: Overloads the Access Control table, refreshes the log, and so on.

SHUTDOWN: Turn off the MySQL service.

Database/data Table/Data column permissions:

Alter: Modifies an existing data table (for example, add/Remove Columns) and index.

Create: Create a new database or data table.

Delete: Deletes the record for the table.

Drop: Deletes a data table or database.

Index: Establish or delete the indexes.

Insert: Adds a table record.

Select: Displays/searches the table's records.

Update: Modifies a record that already exists in the table.

Special permissions:

All: Allow to do anything (as root).

USAGE: Only allow login-nothing else is allowed.

1. mysql Common commands

Create database name; Create a database

Use DatabaseName; Select Database

Drop database name deletes databases directly and does not alert

Show tables; Show Table

Describe TableName; Detailed description of the table

Add distinct in Select to remove duplicate fields

Mysqladmin drop DatabaseName You are prompted before you delete the database.

Show current MySQL version and current date

Select version (), current_date;

2. Change the password of root in MySQL:

Shell>mysql-u root-p

mysql> Update user Set Password=password ("Xueok654123″) where user= ' root ';

Mysql> flush Privileges//Refresh Database

Mysql>use dbname; Open the database:

Mysql>show databases; Show all databases

Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;

Mysql>describe user; Displays column information for the user table in the MySQL database);

3. Grant

Create a full superuser who can connect to the server from anywhere, but must use a password something do this

Mysql> Grant all privileges on * * to [e-mail protected] identified by ' something ' with

Add new users

Format: Grant Select on database. * To User name @ login host identified by "password"

GRANT all privileges on * * to [email protected] identified by ' something ' with GRANT OPTION;

GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;

Remove Authorization:

Mysql> revoke all privileges on * * FROM [email protected] "%";

mysql> Delete from user where user= "root" and host= "%";

mysql> flush Privileges;

Create a user custom login on a specific client it363.com to access a specific database fangchandb

MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd

To rename a table:

mysql > ALTER table t1 rename T2;

4, Mysqldump

Backing Up the database

shell> mysqldump-h host-u root-p dbname >dbname_backup.sql

Recovering a Database

shell> mysqladmin-h myhost-u root-p Create dbname

shell> mysqldump-h host-u root-p dbname < Dbname_backup.sql

If you only want to unload the build instruction, the command is as follows:

Shell> mysqladmin-u root-p-D databasename > A.sql

If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows:

shell> mysqladmin-u root-p-t databasename > A.sql

So what should I do if I only want the data and I don't want any SQL commands?

mysqldump-t./Phptest Driver

Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions.

5. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input. The shell can be used to type the redirection utility to complete this work. For example, if a file is stored in the My_file.sql

These queries can be executed as follows:

For example, if you want to write the build statement in advance in Sql.txt:

MySQL > mysql-h myhost-u root-p Database < Sql.txt

Start the service

Mysqld--console

Stop Service

Mysqladmin-u Root shutdown

Log in after using MySQL database

Mysql-u root-p MySQL

Mysql-u root-p-H 11.11.11.11 Database

Create a database

Create DATABASE db_name [default character SET=GBK]

Set the database default character set

Alter DATABSE db_name default character Set GBK

Replace the database with the DB test after log on

Use test

Creating a table with an image field create a table Mypic to the store picture

CREATE TABLE mypic (picid int, picname varchar (), content blob);

Structure of the Display table describe table Mypic

Desc mypic

Displays the table statement for the current table

Show CREATE TABLE table_name

Change Table Type

ALTER TABLE TABLE_NAME engine innodb|myisam|memory

Insert a record of insert a

INSERT into mypic values (1, ' Chapter II ', 0x2134545);

Show current user Show now users

Select User ();

Show current user password show password

Select password (' root ');

Show current date show today

Select Now ();

Changing the user Password change username user password

Update user set Password=password (' xxx ') where user= ' root ';

Assigning User Rights Grant

Grant all privileges on * * [email protected]

Grant Select,insert,delete,update,alter,create,drop on lybbs.* [email protected] "%" identified by "Lybbs";

Grant Select,insert,delete,update,alter,create,drop on lybbs.* [e-mail protected] by "Lybbs";

Refresh user rights without rebooting flush privileges

Flush Privileges

Add a primary key to a table add primary key

ALTER TABLE Mypic add primary key (PICID)

Modify table structure Add a new field add a new column userid after Picid

ALTER TABLE mypic add column userid int after picid

Change the column type to use the default blob over 100k when the storage image is too large

ALTER TABLE userpic change image Image Longblob;

ALTER TABLE userpic modify image Longblob;

Set the default character set to gb2312

Mysqld--default-character-set=gb2312

Show details, including character set encoding

Show full columns from Userpic;

Change the encoding of a table

Alter TABLE userpic CHARACTER SET gb2312;

MySQL JDBC Connection URL using Chinese

Jdbc:mysql://localhost/test useunicode=true&characterencoding=gb2312

Execute external script

Source

MySQL is the most popular open source SQL database management system, developed, distributed, and supported by MySQL AB. MySQL AB is a MySQL developer-based business company, a second-generation open source company that uses a successful business model to combine open source value and square. MySQL is a registered trademark of MySQL AB.

MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. MySQL server supports mission-critical, heavy-duty production system use, or it can be embedded in a large configuration (mass-deployed) software.

Collect a few, and then do the next finishing. MySQL Common commands

show databases; Display Database

Create database name; Create a database

Use DatabaseName; Select Database

Drop database name deletes databases directly and does not alert

Show tables; Show Table

Describe TableName; Show the specific table structure

Add distinct in Select to remove duplicate fields

Mysqladmin drop DatabaseName You are prompted before you delete the database.

Show current MySQL version and current date

Select version (), current_date;

To modify the root password in MySQL:

Shell>mysql-h localhost-u root-p//Login

mysql> Update user Set Password=password ("xueok654123") where user= ' root ';

Mysql> flush Privileges//Refresh Database

Mysql>use dbname; Open the database:

Mysql>show databases; Show all databases

Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;

Mysql>describe user; Displays column information for the user table in the MySQL database);

Grant

Create user firstdb (password firstdb) and database, and assign permissions to FIRSTDB database

mysql> CREATE DATABASE Firstdb;

Mysql> Grant all on firstdb.* to Firstdb identified by ' Firstdb '

User Firstdb is created automatically

MySQL default is localhost, the corresponding IP address is 127.0.0.1, so you log in with your IP address will be wrong, if you want to use your IP address to log in the first authorization with the grant command.

Mysql>grant all on * * to [e-mail protected] identified by "123456";

Description: Grant and on are various permissions, for example: Insert,select,update, etc.

On after is the database name and table name, the first * represents all databases, the second * represents all tables

Root can be changed to your user name, after the @ can be followed by the domain name or IP address, identified by behind the login password, can be omitted, that is, the default password or empty password.

Drop database Firstdb;

Create a full superuser who can connect to the server from anywhere, but must use a

MySQL common command set MySQL common commands 5