Mysql basic notes

Update:, less than half of the records are recorded, and no code has been completed .... update:, DONEmysql quick query reference: Mysql required, network blog, stackoverflow note; Basic SQL reference mysql required, a good book, beginner level, if needed, you can start searching in the browser.

Update:, less than half of the records are recorded, and no code has been completed .... update: 2013-11-23, DONE mysql Quick Reference: Mysql must know, network blog, stackoverflow note; Basic SQL reference mysql must know, a good book, beginner level, if needed, you can start searching in the browser.

Update:, less than half of the records are recorded, and no code is completed...>

Mysql quick query

Reference: Mysql required, network blog, stackoverflow

Note: For some basic SQL statements, refer to mysql required knowledge. It's a good book. It's a beginner level for cainiao. If you need it, you can start with it.

Search in a browser

Find a good mysql open-source gui tool

Environment Configuration

Install mysql in ubuntu

Sudo apt-get install mysql-server mysql-clientnetstat-nltp | grep mysql configuration file/etc/mysql/my. conf

Basic Concepts

Database basics:

InnoDB is a reliable transaction processing engine that does not support full-text search. MyISAM is a personalized engine that supports full-text search and does not support transaction processing.

Database-database

A container that stores organized data (usually a file or a group of files)

Table

A structured list of specific types of data

Mode-schema

Information on the layout and features of databases and tables

Column-column

A field in the table. All Tables are composed of one or more columns.

Data Type-datatype

The allowed data type. Each table column has a corresponding data type, which limits (or permits) the data stored in this column

Row-row

A record in the table

Primary key-primary key

A column or a group of columns whose values uniquely differentiate each row in the table

Enter the mysql Command Line

Enter mysql or mysql-u ken-p-h myserver-P 9999. [give the user name, host name, and port.] For help: mysql -- help

Command Format and description:

1. The command must be run; or \ g. Only Enter does not run minglin 2. help or \ h to get help. 3. quit or exit

You can use GUI tools

MySQL AdministratorMySQL Query Browser

Use

Create a database:

>CREATE DATABASE MYSQLDATA

Use a library

use db_name

Show

View All databases

show databases;

All tables in the column warehouse picking

use db_name;show tables;

Lists information about all columns in a table.

show columns from table_name;ordesc table_name;

Displays the created SQL statement

show create database db_name;show create table table_name;

Others

Show status server status information show grants show authorized users show errors/show warnings show server errors or warnings

Query

SELECT clause order

SELECTFROMWHEREGROUP BYHAVINGORDER BYLIMIT

Select

Retrieve a single column

>SELECT col FROM tb_name;

Multiple Columns

>SELECT col1, col2 FROM tb_name

Retrieve all columns

> SELECT * FROM tb_name; # unless you are sure to use all columns

Retrieval deduplication

>SELECT DISTINCT col FROM tb_name

Limit the number of results

> SELECT col1 FROM tb_name LIMIT 5; no more than five rows are returned> SELECT col1 FROM tb_name LIMIT 5, the first of 5 is the start position, and the initial value is 0. the second is that the number of displays is equivalent to LIMIT 5 OFFSET 5.

Order

Sort by a Field

>SELECT col1 FROM tb_name ORDER BY col1

Sort by multiple columns

>SELECT col1, col2, col3 FROM tb_name ORDER BY col1, col2

Specify the sorting direction (ascending or descending)

> SELECT col1, col2 FROM tb_name order by col1 DESC; [Default ASC] Note: To sort multiple columns, you must use DESC for each column. Note: order by must be placed before LIMIT.

Where

Filter

>SELECT col1, col2 FROM tb_name WHERE col1 = 2.5;

Filter Mismatch

>SELECT col1, col2 FROM tb_name WHERE col1 <> 1000

Range check

>SELECT col1, col2 FROM tb_name WHERE col1 BETWEEN 5 AND 10

Null check

> SELECT col1 FROM tb_name WHERE col2 is nullnull, with no value. It IS different FROM a field that contains 0, an empty string, or contains only spaces.

Multi-condition, combination and

>SELECT col1 FROM tb_name WHERE col1=100 AND col2 <= 10

Multi-condition, combined or

>SELECT col1 FROM tb_name WHERE col1=100 OR col2 <= 10

Priority and is greater than or, and is processed first, so brackets should be used as appropriate

select prod_id from products where (prod_price < 2.5 or vend_id = 1000) and prod_price > 1;

Query range specified, in Operator

>SELECT col1 FROM tb_name WHERE col1 IN (1001,1002)

Inverse, not operator

>SELECT col1 FROM tb_name WHERE col1 NOT IN (1001,1002)

Operator

=<>!=<<=>>=between A and B

Like

Wildcard

> SELECT col1 FROM tb_name WHERE col1 LIKE 'Jet % '% matches 0 or multiple characters

Single Character

>SELECT col1 FROM tb_name WHERE col1 LIKE ‘_ ton anvil’

Data Filtering regexp

Regular Expression search

> SELECT col1FROM tb_nameWHERE col1 REGEXP '000000' REGEXP '. 000' REGEXP matches the column Value

Or matching

>SELECT col1 FROM tb_name WHERE col1 REGEXP ‘1000|2000’

Several

select prod_id from products where prod_name regexp '[1|2]000';

Matching range

select prod_id from products where prod_name regexp '[1-5]000';

Match special characters and escape them

Must use \ as the leading node. \-> SELECT col1 FROM tb_name WHERE col1 REGEXP '\\.'

Like and regexp

Like full column match regexp column value match

Concat

Concatenated characters

>SELECT Concat(name, ‘ ----‘, age) FROM tb_name

Remove Blank

>SELECT Rtrim(name) FROM tb_nameLtrim() Trim()

Use column name

>SELECT Concat(name, ‘---‘, age) AS info FROM tb_name

Arithmetic computing

> SELECT quantity * item_price AS total_price FROM tb_name support + -*/

Text Functions

Text processing functions

Left () string left character length () String length locate () Find a substring of the string lower () to lowercase ltrim () Remove left space right () returns the right character of the string rtrim (). removes the right space of the string soundex (). returns the string soundex value upper () in upper case.

Eg

>SELECT Upper(name)FROM tb_name

Date Functions

Date and time processing functions

Adddate () Add a date-day or weekly addtime () Add a time curdate () returns the current date curtime () returns the current time date () returns the date part of the date () calculate two date difference date_add () highly flexible date calculation function date_format () returns a formatted date or time string day () returns the number of days of a date part dayofweek () for a date, returns the day of the week (hour () minute () month () now () current date and time second () time () current date and time part year ()

Eg

>SELECT col1 FROM tb_name WHERE Date(order_date) = ‘2005-09-01’

Common Date and Time Functions

Date () returns the Date of the Date part Day () returns the number of days of the Date part

Numeric Functions

Numeric processing functions

Abs () cos () exp () index mod () pi () returns the circumference rate rand () Random Number sin () sqrt () tan ()

Aggregate functions

Avg average

>SELECT AVG(price) AS avg_price FROM tb_name

Count

Select count (*) from products; # whether Null or not, the count select count (prod_id) from products; # count records with values, ignore NULL values

Max

>SELECT MAX(price) AS max_price FROM tb_name

Min

>SELECT MIN(price) AS min_price FROM tb_name

Sum

> Select sum (quantity) AS total FROM tb_name # The sum function ignores rows whose values are NULL.

Group

Group

>SELECT id, COUNT(*) AS num_prods FROM tb_name GROUP BY id

Note:

1. group by can contain any number of columns 2. in group by, each column must be a search column or a valid expression (but not a clustering function. except for the aggregate function, each column in the select statement must appear in the group by clause. if the group column has a Null value, Null is returned as a group. the group by clause must appear after the where clause, before order

Filter groups

>SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) > 2

Difference between where and having

Where is used before grouping, and having Is Used After grouping.

Subquery

1. Used for filtering

>SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num                    FROM orderitems)

2. As a field

>SELECT cust_name,       cust_state,       (SELECT COUNT(*)        FROM orders        WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name

Join table

1. Create a connection

>SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

Multiple tables can be joined.

2. Internal Connections

>SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id

Advanced join table

1. Auto join

>SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products                WHERE prod_id = ‘DTNTR’)

Equivalent

>SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id        AND p2.prod_id = ‘DTNTR’

2. external connections

>SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders      ON customers.cust_id = orders.cust_id

Combined Query

1. UNION

> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 union select vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002) UNION automatically removes duplicate row union all reserved

2. Sorting statement placed after UNION

Effective for all SELECT statements

Full text search

MyISAM supports full text search

InnoDB does not support full text search

1. Enable

>CREATE TABLE productnotes( note_id int NOT NULL AUT_INCREMENT, note_text text NULL, FULLTEXT(note_text)

2. Perform full text search

>SELECT note_text FROM tb_name WHERE Match(note_text) Against(‘rabbit’)

3. boolean text search

>SELECT note_text FROM productontes WHERE Match(note_text) Against(‘heavy’ IN BOOLEAN MODE)

Insert data

1. Basic insert

>INSERT INTO customers(cust_name,                     cust_address) VALUES(‘Pep’, ‘100 main street’)

2. Insert multiple rows

>INSERT INTO customers(cust_name,                     cust_address) VALUES(‘Pep’, ‘100 main street’),       (‘Tim’, ‘200 main Street’);

3. Insert the Retrieved Data

>INSERT INTO customers(cust_name,                     cust_address) SELECT cust_name, custaddress FROM custnew;

Update

1. Update rows

>UPDATE customers SET cust_email = ‘a@fudd.com’ WHERE cust_id = 10005

2. continue without exiting even if an error occurs.

>UPDATE IGNORE customers

Delete

1. delete data

>DELETE FROM customers WHERE cust_id = 10006

Table operations

1. Create a table

>CREATE TABLE customers(   cust_id int NOT NULL AUTO_INCREMENT,   cust_name char(50) NOT NULL,   vend_city char(50) NULL,   quantity int NOT NULL DEFAULT 1,   PRIMARY KEY(cust_id))ENGINE=InnoDB

2. Update a table

Add Field

>ALTER TABLE vendors ADD vend_phone CHAR(20)

Delete A Field

>ALTER TABLE tb1 DROP COLUMN names;

Change column type

>ALTER TABLE infos CHANGE list list tinyint NOT NULL DEFAULT '0'

Add primary key

>ALTER TABLE tb1 ADD primary key(id)

Delete A Field

>ALTER TABLE tb1 DROP field_name

Add auto-increment primary key

alter table customers change id id not null auto_increment primary key;

Add new fields and set them as primary keys

Alter TABLE tablename ADD new_field_id int(5) default 0 not null auto_increment ADD primary key(new_field_id)ALTER TABLE example ADD ID INT NOT NULL;ALTER TABLE example ADD UNIQUE(url)>ALTER TABLE vendors DROP COLUMN vend_phone

Alter table syntax: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

3. delete a table

>DROP TABLE customers2;

4. Clear table data

>DELETE FROM mytable;

5. Rename the table

>RENAME TABLE customers2 TO customers; ALTER TABLE 'oldname' RENAME TO 'newname'

View operations

1. Create a view

>CREATE VIEW productcustomers AS SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id

2. Use the view

>SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = ‘TNT2’

Stored Procedure

1. Create a simple Stored Procedure

>CREATE PROCEDURE productpricing() BEGINSELECT Avg(price) AS priceavgFROM products; END;

CALL: CALL productpricing ()

2. delete a stored procedure

>DROP PROCEDURE productpricing

3. Use Parameters

>CREATE PROCEDURE ordertotal(IN onumber INT,OUT ototal DECIMAL(8,2))BEGINSELECT Sum(item_price*quality)FROM orderitemsWHERE order_num = onumberINTO ototal;END;

Call:

>CALL ordertotal(200005, @total;>SELECT @total;

3. Check the Stored Procedure

>SHOW CREATE PROCEDURE ordertotal;

Cursor

1. Create a cursor

>CREATE PROCEDURE processorders() BEGINDECLARE ordernumbers CURSORFORSELECT order_number FROM orders;BEGIN ordernumbers;FETCH ordernumbers INTO o;CLOSE ordernumbers; END;DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;REPEAT    FETCH ordernumbers INTO o;END;

Trigger

1. Create a trigger

>CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT ‘Product added’

2. delete a trigger

>DROP TRIGGER newproduct;

3. INSERT trigger

>CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num

4. DELETE trigger

>CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW BEGININSERT INTO archive_orders(order_num, order_date, cust_id)VALUES(OLD.order_num, OLD.order_date, OLD.cust_id); END;

5. UPDATE trigger

>CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

Transactions

Basic Concepts

ACIDA, atomicity, food is an atomic operation unit. Its modifications to the data are either fully executed or not executed. consistency: when the transaction starts and completes, the data must be consistent (all relevant data rules and internal data structures) I. isolation, to ensure that the transaction is not affected by external concurrent operations, that is, the state of the intermediate transaction processing process is invisible to the external. D. durability: after the transaction is completed, the data is permanently modified, and system faults can be maintained in a timely manner.

1. Transactions

> Start transaction delete from ordertotals; SELECT * FROM ordertotals;> ROLLBACK> COMMIT

2. Set up retention points

>SAVEPOINT delete1;>ROLLBACK TO delete1;

Import and Export

1. Import

Insert data in text format

>LOAD DATA LOCAL INFILE 'd:/mysql.txt' INTO TABLE mytable;

Import. SQL

>use database;>source d:/mysql.sql

Insert data from another table to this table

INSERT INTO tab1(f1,f2)SELECT a.f1, a.f2FROM a WHERE a.f1='a'

2. Backup

MySQL mysqldump is used for export. The basic usage is as follows:

mysqldump [OPTIONS] database [tables]

Command for backing up MySQL database

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

Backing up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

Directly compress and back up the MySQL database

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

Back up a MySQL database table

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

Back up multiple MySQL databases at the same time

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

Back up database structures only

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

Back up all databases on the server

mysqldump –all-databases > allbackupfile.sql

Restore

Command for restoring MySQL database

mysql -hhostname -uusername -ppassword databasename < backupfile.sqlmysql -hhostname -ppassword databasename tablename < backuptablefile.sql

Restore a compressed MySQL database

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

Transfer database to new server

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

Import query results to external files

SELECT a, B, a + B FROM test_tableINTO OUTFILE '/tmp/result.txt 'fields TERMINATED ', 'optionally enclosed by '"'Lines terminated by' \ n' or mysql-u you-p-e" SELECT... "> file_name

Performance Research

1. Under what circumstances can indexes be used?

Real-time Monitoring

View the current connections of the mysql database

Command: show processlist; or # mysqladmin-uroot-p password processlist

Current Status

Command: show status; or # mysqladmin-uroot-p password status

Original article address: Mysql basic notes, thanks to the original author for sharing.