Shulou(Shulou.com)06/01 Report--

This article mainly introduces the basics and advanced handouts of MySQL, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

MySQL Basics 2.1 data types

Defining the type of data field in MySQL is very important for optimizing your database.

MySQL supports many types, which can be roughly divided into three types: numeric, date / time, and string (character) types.

2.1.1 Numeric type

2.1.2 date and time type

2.1.3 string type

Integer type

  tinyint, 1 byte, signed:-128x127, unsigned bit: 0255,

  smallint, 2 bytes, signed:-32768mm 32767, unsigned bit: 01365535

  mediumint, 3 bytes, signed:-8388608 "8388607, unsigned bit: 0" 16777215

  int, 4 bytes, signed:-2147483648, unsigned bits: 04284967295

  bigint, accounting for 8 bytes

  bool is equivalent to tinyint (1) Boolean type

Floating point type

  float ([m [, d]]) occupies 4 bytes, 1.17E-38~3.4E+38

  double ([m [, d]]) occupies 8 bytes

Floating point number represented as a string by   decimal ([m [, d]])

Character type

  char ([m]): fixed-length characters that occupy m bytes

  varchar [(m)]: characters of variable length, occupying 1 byte, greater than 255characters

  tinytext,255 characters (2 to the 8th)

  text,65535 characters (2 to the 16th)

  mediumtext,16777215 characters (2 to the 24th power)

  longtext, (2 to the 32)

  enum (value,value,...) There can be up to 65535 members in 1x2 bytes.

  set (value,value,...) Account for 1, 2, 2, 3, 3, and 4, 8 bytes, and a maximum of 64 members

2.2 Common select commands

Use the select command to view mysql database system information:

-- print the current date and time

Select now ()

-- print the current date

Select curdate ()

-- print the current time

Select curtime ()

-- print the current database

Select database ()

-- print the MySQL version

Select version ()

-- print the current user

Select user ()

-- View system information

Show variables; show global variables; show global variables like'% version%'; show variables like'% storage_engine%'; default storage engine

Like fuzzy search can also allow users to use where words, such as

Select * from students where stname like'% 1% 1% 2% 3%'

Besides like, there is also not like.

Show engines; to see which storage engines are supported

-- View system running status information

Show status; show global status like 'Thread%'; 2.3 Export, Import Database 2.3.1 Import Database

An empty database must be created before importing the database

Mysql-e'create database book'-uroot-p123456 or log in to mysql create database book

Import (method 1)

Mysql-uroot-p123456 book

Use book; mysql > show tables; +-+ | Tables_in_book | +-+ | books | | catego +-+

Import (method 2)

Create database book; mysql > use book; mysql > source / root/book.sql # sql script path mysql > show tables; +-+ | Tables_in_book | +-+ | books | | category | +-+ 2.3.2 Export Database

Export database: mysqldump-u user name-p database name > exported file name

Mysqldump-uroot-p123456 book > book2.sql

Expand knowledge

Mysqldump-uroot-p123456-B library name > file. Sql

-B: export the entire library including the statement to build the library

-A: export all databases

How to export the result of a select to text

Select * into outfile'/ tmp/123.txt' from books; there is a file access right problem here. Mysql users can access the / tmp path, so putting select * from books into outfile'/ tmp/456.txt'; under tmp is actually backing up the database.

Test in the book database we just imported

2.4.1 View the contents of the table: mysql > select * from category; mysql > select * from books; mysql > select * from books\ G 2.4.2 View the field type:

Desc table name

Mysql > desc books; 2.4.3 logical operator:

And or not

And and

Or or

Not is not

Select the record for which the book price is (30, 40, 50, 60), and only show the book name, publisher, and price.

Mysql > select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60 +-- +-- +-+ | bName | publishing | price | +- -+-- +-+ | Illustrator 10 complete Manual | Science Press | 50 | | FreeHand 10 basic tutorials | Beijing hopes to publish electronically | 50 | | full course of website design | Science Press | 50 | | ASP Database system Development instance Navigation | people's posts and Telecommunications Publishing House | 60 | | Delphi 5 programming and Control reference | Electronic Industry Press | 60 | | ASP Database system Development instance Navigation | people's posts and Telecommunications Publishing House | 60 | +-- | -+-- + 2.4.4 arithmetic operator:

= equal to

It is not equal to!

Greater than

60

Find out the price is 60

Mysql > select bName,price from books where price=60

Find out if the price is not equal to 60

Mysql > select bName,price from books where price60

Find out the record that the price is 60, 50, 50, 70.

Mysql > select bName,price from books where price in (50, 60, 70)

Find out the record that the price is not 60, 50, 50, 70.

Mysql > select bName,price from books where price not in (50, 60, 70), 2.4.5:

Ascending order: order by "sorted fields" asc default

Descending: oredr by "sorted fields" desc

Mysql > select bName,price from books where price in (50, 60, 70) order by price asc +-+-+ | bName | price | +-- -+-+ | Illustrator 10 complete manual | 50 | | FreeHand 10 basic tutorial | 50 | | website design tutorial | 50 | | ASP database system development instance navigation | 60 | | Delphi 5 programming and control reference | 60 | | ASP database system development instance navigation | 60 | mysql > select bName | Price from books where price in (50, 60, 70) order by price desc +-- +-+ | bName | price | +-- -+ | ASP database system development instance navigation | 60 | | Delphi 5 programming and control reference | 60 | | ASP database system development instance navigation | 60 | | Illustrator 10 complete manual | 50 | | FreeHand 10 basic tutorials | 50 | | website design tutorials | 50 |

Sort multiple fields

Select bName,price from books where price in (50, 60, 60, 70) order by price desc,bName desc; 2.4.6 range operation:

[not] between.... and....

Between and can be replaced by greater than less than, and the meaning of greater than less can be expressed more clearly.

Find the title and price of a book whose price is not between 30 and 60

Mysql > select bName,price from books where price not between 30 and 60 order by price desc

Note:

There are three query conditions here: between. And,or and in

(30 and 60) > 30 and = 30 and select bName from books where bName like'% Program%'; does not contain mysql > select bName from books where bName not like'% Program%'; 2.4.8 MySQL subquery:

Concept: select appears again in the where condition of select

The query is nested in the query

Select a book of type called "Network Technology":

Mysql > select bName,bTypeId from books where bTypeId= (select bTypeId from category where bTypeName=' Network Technology')

Select a book with the type name "*"

Mysql > select bName,bTypeId from books where bTypeId= (select bTypeId from category where bTypeName='***'); 2.4.9 Limit limits the entries displayed: SELECT * FROM table LIMIT [offset,] rows offset lines

The LIMIT clause can be used to force the SELECT statement to return a specified number of records. LIMIT accepts one or two numeric parameters. Parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of record rows returned. The offset of the initial record row is 0 (not 1):

For example, select * from table limit m _ n statement

Indicates where m refers to the index at the beginning of the record, starting from 0, indicating the first record

N means to start with Article m + 1 and take n.

Find the records in rows 2 through 6 of the category table.

First of all, lines 2 to 6 have 2, 3, 4, 5, 6, a total of 5 numbers, starting from 2, the offset is 1.

Mysql > select * from category limit 1pr 5 +-+-+ | bTypeId | bTypeName | +-+-+ | 2 | website | | 3 | 3D Animation | | 4 | linux Learning | | 5 | Delphi Learning | | 6 | * * | +- -+

View the three lowest prices of all books

We sort all the records in ascending order and take out the first three.

Mysql > select bName,price from books order by price asc limit 0Pol 3 +-- +-+ | bName | price | +-- +-+ | website creation through train | 34 | * and network security | | 41 | | Network Program and Design-asp | 43 |

We combine subqueries with restricted entries and arithmetic operations to query.

Display fields bName, price; condition: find books that are cheaper than the cheapest books published by Electronic Industry Press.

In response to this kind of inquiry, we will step by step to find out the cheapest book published by the Electronic Industry Press.

Mysql > select bName,price from books where publishing= Electronic Industry Publishing House order by price asc limit 0Power1; mysql > select bName,price from books where price select bName,price from books where price select sum (price) from books; or select sum (price) as Total Book Price from books; +-+ | sum (price) | +-+ | 10048 | +-+

Average of avg ():

Find the average price of all books whose Id is less than 3

Mysql > select avg (price) from books where bId select bName,max (price) from books; this method is wrong

Let's find out which is the most expensive book.

Select bname,price from books order by price asc limit 0,3

It can be seen that the most expensive books are Javascript and Jscript from entry to proficiency, rather than website production through train.

Select bName,price from books where price= (select max (price) from books) +-- +-+ | bName | price | +-- +-+ | Javascript From beginner to proficient with Jscript | 7500 | +-+-+

Minimum min ():

Ask for cheap books in all the books?

Mysql > select bName,price from books where price= (select min (price) from books) +-- +-+ | bName | price | +-+-+ | website creation through train | 34 | +-+-+

Count () Statistics record number:

Count the number of books with a price greater than 40

Mysql > select count (*) from books where price > 40; +-+ | count (*) | +-+ | 43 | +-+

You can also add what you need in Count (), such as adding distinct to work with it.

Select count (distinct price) from books where price > 40

Arithmetic operation:

/

For all books whose price is less than 40 yuan, the price will be increased by 5 yuan mysql > update books set price=price+5 where price update books set price=price*0.8 where price > 70

String function:

Substr (string, start,len) interception: starting from start, intercepting len length. Start starts from 1.

Mysql > select substr (bTypeName,1,7) from category where bTypeId=10; +-- + | substr (bTypeName,1,7) | +-- + | AutoCAD | originally AutoCAD technology +-+ select substr (bTypeName,8,2) from category where bTypeId=10 +-- + | substr (bTypeName,8,2) | +-+ | Technology | only intercept Chinese characters +-+ 1 row in set (0.00 sec)

Concat (str1,str2,str3.) Splicing. Combine multiple fields into a single field output

Mysql > select concat (bName,publishing) from books; mysql > select concat (bName,-", publishing) from books

Case conversion

Upper () uppercase: convert to uppercase output

Mysql > select upper (bname) from books where bId=9; +-+ | upper (bname) | +-+ | DREAMWEAVER 4 / s / m / m / s | +-+

In this way, garbled codes will appear in the conversion of Chinese.

Lower () lowercase: convert to lowercase output

Mysql > select lower (bName) from books where bId=10; +-+ | lower (bName) | +-+ | 100 creative effects of 3D max 3.0 |

Read the above about MySQL basic and advanced handouts, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.