MySQL Getting Started note 3

26 Lesson Union Query

is to combine two or more query results into one result.

The where of the two statements is more complex, it is cumbersome to write together, and it is divided into two simple

Mysql> SELECT * from goods where cat_id =3
Union
-select * from goods where cat_id = 4;
+ ———-+ ———————— +--–+ ———-+ ——— –+-
-+ ———— + ———— –+ ————-+
| goods_id | Goods_name | cat_id | brand_id | GOODS_SN |
er | Shop_price | Market_price | Click_count |
+ ———-+ ———————— +--–+ ———-+ ——— –+-
-+ ———— + ———— –+ ————-+
| 8 | philips [email Protected] | 3 | 4 | ecs000008 |
1 | 399.00 | 478.79 | 10 |
| 9 | Nokia E66 | 3 | 1 | ecs000009 |
4 | 2298.00 | 2757.60 | 20 |
| 10 | Sony Ericsson C702c | 3 | 7 | ecs000010 |
7 | 1328.00 | 1593.60 | 11 |
| 11 | Sony Ericsson C702c | 3 | 7 | ecs000011 |
1 | 1300.00 | 0.00 | 0 |
| 12 | Motorola A810 | 3 | 2 | ecs000012 |
8 | 983.00 | 1179.60 | 13 |
| 13 | Nokia 5320 XpressMusic | 3 | 1 | ecs000013 |
8 | 1311.00 | 1573.20 | 13 |
| 15 | Motorola A810 | 3 | 2 | ecs000015 |
3 | 788.00 | 945.60 | 8 |
| 17 | Amoi N7 | 3 | 5 | ecs000017 |
1 | 2300.00 | 2760.00 | 2 |
| 19 | Samsung SGH-F258 | 3 | 6 | ecs000019 |
12 | 858.00 | 1029.60 | 7 |
| 20 | Samsung BC01 | 3 | 6 | ecs000020 |
12 | 280.00 | 336.00 | 14 |
| 21 | Gionee A30 | 3 | 10 | ecs000021 |
40 | 2000.00 | 2400.00 | 4 |
| 22 | Multi-reach Touch HD | 3 | 3 | ecs000022 |
1 | 5999.00 | 7198.80 | 16 |
| 24 | p806 | 3 | 9 | ecs000024 |
00 | 2000.00 | 2400.00 | 35 |
| 31 | Motorola E8 | 3 | 2 | ecs000031 |
1 | 1337.00 | 1604.39 | 5 |
| 32 | Nokia N85 | 3 | 1 | ecs000032 |
4 | 3010.00 | 3612.00 | 9 |
| 1 | kd876 | 4 | 8 | ecs000000 |
1 | 1388.00 | 1665.60 | 9 |
| 14 | Nokia 5800xm | 4 | 1 | ecs000014 |
1 | 2625.00 | 3150.00 | 6 |
| 18 | Amoi T5 | 4 | 5 | ecs000018 |
1 | 2878.00 | 3453.60 | 0 |
+ ———-+ ———————— +--–+ ———-+ ——— –+-
-+ ———— + ———— –+ ————-+
Rows in Set (0.08 sec)

The Union must be the same as the columns to be merged, and can be queried across tables

Column names do not have to be consistent, and column names use the first column name

Note rows that use union exactly equal will be merged
Merging is a time-consuming task and generally does not allow union to merge, and using union ALL can avoid
Merging, you can read a speed with a good lift.

Mysql> SELECT * from a
Union
select * from B;
+--+--+
| ID | num |
+--+--+
| A | 5 |
| B | 10 |
| C | 15 |
| D | 10 |
| B | 5 |
| D | 20 |
| e | 99 |
+--+--+
7 Rows in Set (0.00 sec)

Mysql> SELECT * from a
UNION ALL
select * from B;
+--+--+
| ID | num |
+--+--+
| A | 5 |
| B | 10 |
| C | 15 |
| D | 10 |
| B | 5 |
| C | 15 |
| D | 20 |
| e | 99 |
+--+--+
8 rows in Set (0.00 sec)

The Union clause does not have to write an order by, even if it is written, it has no effect.
Because the total result set that is obtained after the SQL merge can be order, the clause has no meaning to order by
and eventually merge together.

example, the addition and display of the same ID in two tables

mysql> select Id,sum (num) from
(
SELECT * from a
UNION ALL
SELECT * from b
)
As TMP
Group by ID;
+--+ ———-+
| ID | SUM (num) |
+--+ ———-+
| A | 5 |
| B | 15 |
| C | 30 |
| D | 30 |
| e | 99 |
+--+ ———-+
5 rows in Set (0.00 sec)

Don't forget to use what you've learned.

If you do not use the union query to do ....

Learned the operation language of SQL.

Learn the data definition language below

28 Lesson CREATE Table

The process of building a table is the process of declaring a header, which is the process of declaring a column.

What types of columns are declared, what attributes are given, where content is placed, and no space is wasted

The type of the column and its properties

Build table
：
Create Table Table name (
Column 1 Column Type column property default value, can write to column type only
Column 2 column Type column property default value

）；

29 Lesson Integer Columns

Big points.

Numeric ——-integer, floating point, fixed-point

string--char varchar, text

Date Time Type
2012-12-13
14:26:23

Integral type
Int occupies 4 bytes of disk

Bingint 8 bytes
Mediumint 3 bytes

Smallint 2 bytes

Tinyint 1 bytes-128 ~127
There are two possible full positive and positive negative
One byte 8 bit 2 binary

30 Optional parameters for the class integer column

Unsigned unsigned, that is, to make him 0-255 that is all positive is unsigned

Alter table name Add unum tinyint unsigned
To modify a table, adding a column is adding a unnum column, and the type class tinyint, the property is unsigned is unsigned

Zerofill suitable for school numbers, codes, etc., fixed-width, can be filled with 0 of the same length

How wide it fills, M is the meaning of width

Alert table T2 sn tinyint (5) Zerofill

The 5 in that bracket is the length or width.

Fill the bit with 0.

He would not be equal to a negative number, not to write he is also unsigned. The default is unsigned

That M, the type of parentheses, is used in conjunction with Zerofill.

is how much to fill with 0.

31 Lesson Floating-point columns and fixed-point columns

Float, double, decimal

Float (m,d) M is the precision, the total number of digits, and D is the number of digits following the scale decimal point

Double with float, is very big.

Decimal is a fixed-point type, and there are m,d

Mainly decimal is fixed-point, more accurate than the previous two

32 Lesson Character Column

Char varchar

The difference between the two of them

Char (10) No matter how much it gives, it takes 10 characters, and a fixed length

Varchar (10)
Can put up to 10 characters, only one character, he accounted for one more word multibyte 1 to 2 bytes, will use some space to store
How much is the end?

If it is not very big, the space is very big, it will be quicker with Charf.

If Char is not enough, use a space

Concat Stitching Strings

Such as

Select concat (' asdasd ', N1, ' ASD ') from T4

After the char after adding a space in the connection of the other, out of the space after the blank, because it is filled with space connection or, can not distinguish, and then omitted. But varchar doesn't, because a part of it is used to record data.

Text can be saved a lot. Oversized text.

A Blob is a binary type that is used to store binary information such as images, audio, and so on.
The enum enum type is defined as a good value within a certain enumeration range.
Like sex, it could only be male or female.

Create Tables T7 (
Xingbie enum (' Male ', female ')
)；

can only be male or female.

The difference between SET and enumeration is that you can choose several, and more than one enumeration can only be male or female

Date time year can be saved 1901-2,155 1970
Date 1000/01/01--9999/12/12 1998-12-31
Time-838:59:59-–9999:12:31 23:59:59
Datetime 1000, 01, 01 00:00:00--9999:12:31 23:59:59
(is the combination of the above two)

Writing the data is a direct write to the string.

Year is written straight year

and the timestmp type.

If he does not enter it, it will automatically enter the current time.

Not recommended, efficiency is not high.

34 Default values for lesson columns

1, NULL query inconvenience,
2, NULL efficiency is not high
So avoid not default to NULL
Declaration column not NULL default value
The declaration does not default to NULL, but instead defaults

To create a table, add the following after creating each column
Such as:

Id int not NULL default 0,

35 Lesson primary key and self-increment

The primary key can distinguish the columns of each row

Auto_increment

Declaring a primary key

Create Table T11 (
Id int PRIMARY KEY,
.........
)；

or write down the columns and then the primary key.
..........
Primary Key (ID)
);

Auto_increment self-increment, only one column is self-increment
This column must be indexed
Key (that column)
or index
Oracle has no self-increment,

36 Course Comprehensive form case

A table can improve efficiency if it's all fixed length.

Separation of fixed length and variable length, common and non-common columns

37 Delete Additions and modifications to class columns
Renamed: Rename Tablle table name to change table name
Adding a column

ALERTR table table name modification tables
ADD hight tinyint unsigned not null default 0; Add columns
The default is at the end of the table.
Put in the specified position
ALERTR table table name modification tables
ADD hight tinyint unsigned after another column name; After adding columns to another column name

ALERTR table table name modification tables
Drop column name;

Modify

Alert table name change now column name changing column name Type property

It's like declaring a new column and substituting him.

Alert table name Modify column Name Type property

DESC table name, view the type of each column of this table, properties

38 Lesson View

Know the concept of a view,
Will build the view

Some things are often used to check out, so save him.

Create a View

Create view view name as query statement

Next Direct SELECT * from view name
Is the effect of that query.

View is also called a virtual table, and view is a query result of an SQL statement,

What's the use?
1, permission control, a few columns allow users to query, the other is not allowed,
Through the view, open several of these columns,
2, simplify the complex query, put a table to be able to operate.

3, after creating the view, show tables will also come out of the view, can not distinguish
Can it be updated and deleted ~

Change the table, the view will change, directly to the view, if it is the data with the physical table, there is no operation
Can be changed, if it is to perform operations, such as, take the average, directly modified, then changed, the physical table
Data do not know how to change.

Algorithm of the 39 lesson view

Where's the view?

View to build this query and again to view the query statement together, directly to check the physical table
This view stores a statement. This is the land amount algorithm called merge (merge)

It is also possible that the statement of the view itself is more complex and difficult to combine, and MySQL executes the statement that creates the view first, stores it as a temporary table, and then queries the temporary table temptable

You can set the algorithm you use.
Create algorithm = merge view as SQL query statement is spliced.

Do not write is the database of their own judgment.

40 Management statements for timetable/view

DROP table Name
Drop View Name

View the build table process show create table;

View the build view process show create view;

See all tables for more information show table status
Add a \g in the back and it will be displayed for easy viewing.

Don't look so much, look up a table alone

Show table status WHERE name = ' table name ';

To see, there's a coment:view in the back, which is the view.

Truncate emptying data from a table

Delete from table name;

Delete to remove the words, if it is self-increment, delete one, add one more after it will increase, not the original ID.

Delete this table, add 1.

Delete is the deletion of data, which increases again, and the self-increment also increases
Truncate is not, equivalent to deleting the table and rebuilding.

41 Lesson Storage Engine Concept

The myi stored on the disk is an index file.
FRM is a description
MyD is the data

The engine is different, the data information important storage location is different.

Engine engines are different.
5.5 with default InnoDB

Common engine Myisam InnoDb Memory

Non-persistent use Memory

Myisam InnoDb Difference

Myisam speed is not safe.

INNODB Security has logs. Data is not easily lost.

When building a table, write engine at the back = used engines.

Myisam
You can copy the catalogue directly.

InnoDb is not.

42 Lesson character sets and garbled characters

Character set, proofing set, garbled

The literal character set is inconsistent with the displayed character set to the field.

Just choose Utf-8.

If the utf-8 is stored, but the client is GBK
You can make a transition in the middle.

Set names GBK;

is to set the 3 to GBK input, showing the middle

The settings on the page are Utf-8
However, the GBK is set in this client;

Proofing Sets:
Sort by what rules ~

43 Lesson Index Concepts

Quick Find, MVI index file
stored in a data structure, such as a tree
Efficient, like a catalogue of books.
Quick positioning of rows, location of data
Change the data while changing the index.
Index is a cost, reduce the speed of adding and deleting.
It is possible to produce an index file that is larger than the data file.
It is generally better to index on columns with low repetition.
There are also frequent queries when used well.

Normal index key
Unique index Uniquwkey
Primary KEY index PrimaryKey
Full-text Index Fulltest

Normal index key

The name of the key index (which column is indexed) after the column is under construction

The name of the generic index is the same as the column in which the index is indexed.

Unique index Uniquwkey

The name of the Uniquwkeykey index after the column is built (which column is indexed)

Unique indexes do not allow duplicates

Primary key index PrimaryKey the same as before

Full-text Index Fulltest
, in the Chinese environment is invalid, to Word segmentation index, generally with third-party solutions
such as Sphinx

Index length, when indexed, is happy to index only the contents of the previous part of the column
For example, the first 10 characters.

The name of the Uniquwkeykey index after the column is built (the column is indexed (length))
Just like a mailbox ~ ~

You can also build multiple-column indexes
is to think of two or more columns as a monolithic index

The name of the index (which column to index, and the other column)

Look at index show index from table name \g

The query should be written in order to play the role of the index.

44 Lesson Index Operations

Look at index show index from table name \g

Delete Index
Alert table table Name Drop INDEX index name
Drop index name from can also be deleted

Add also use
Alert Table name Add index index name ();

Add primary key index without index name

45 Common functions for lessons

First, mathematical functions
ABS (x) returns the absolute value of X
Bin (x) returns the binary of X (Oct returns octal, HEX returns hex)
Ceiling (x) returns the smallest integer value greater than X
EXP (x) return value E (base of natural logarithm) x Times Square
Floor (x) returns the maximum integer value less than x
Greatest (x1,x2,..., xn) returns the largest value in the collection
least (x1,x2,..., xn) returns the smallest value in the collection
ln (x) returns the natural logarithm of X
Log (x, y) returns the X-base logarithm
MoD (x, y) returns the modulo (remainder) of x/y
Pi () returns the value of Pi (pi)
RAND () returns a random value from 0 to 1, which enables the rand () random number generator to generate a specified value by providing a parameter (seed).
Round (x, y) returns the rounding of parameter x with a value of y decimal place
sign (x) returns the value of the symbol representing the number X
SQRT (x) returns the square root of a number
Truncate (x, y) returns the result of a number x truncated to Y decimal places

Second, aggregation function (commonly used in the GROUP BY clause in the SELECT query)
Avg (COL) returns the average of the specified column
Count (COL) returns the number of non-null values in the specified column
Min (col) returns the minimum value of the specified column
Max (COL) returns the maximum value of the specified column
Sum (COL) returns the sum of all values of the specified column
Group_concat (COL) Returns the result of a combination of column value connections belonging to a group

Three, String function
ASCII (char) returns the ASCII code value of a character
Bit_length (str) returns the bit length of a string
Concat (S1,S2...,SN) to concatenate s1,s2...,sn into a string
Concat_ws (SEP,S1,S2...,SN) joins the S1,S2...,SN into a string and uses the Sep character interval
Insert (STR,X,Y,INSTR) starts the string str from the x position, and the Y-character substring is replaced with a string instr, returning the result
Find_in_set (str,list) parses a comma-delimited list of lists, and if STR is found, returns the position of STR in the list
LCase (str) or lower (str) Returns the result of changing all characters in the string str to lowercase
Left (str,x) returns the leftmost X character in a string str
Length (s) returns the number of characters in the string str
LTrim (str) cuts the opening space from the string str
Position (SUBSTR,STR) returns the position of the substring substr the first occurrence in the string str
QUOTE (str) escapes single quotation marks in Str with a backslash
Repeat (STR,SRCHSTR,RPLCSTR) returns the result of a string str repeating x times
Reverse (STR) Returns the result of reversing the string str
Right (STR,X) returns the rightmost X character in a string str
RTrim (str) returns a space at the trailing end of a string str
strcmp (S1,S2) comparing strings S1 and S2
Trim (str) removes all whitespace from string header and trailing
UCase (str) or upper (str) returns the result of converting all characters in the string str to uppercase

Iv. Date and Time functions
Curdate () or current_date () returns the current date
Curtime () or Current_time () returns the current time
Date_add (date,interval int keyword) returns the date plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_add (Current_date,interval 6 month);
Date_format (DATE,FMT) formats date values according to the specified FMT format
date_sub (date,interval int keyword) returns the date plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_sub (Current_date,interval 6 month);
DayOfWeek (date) returns the day ordinal of the week represented by date (1~7)
DayOfMonth (date) Return date is the day of the one month (1~31)
DayOfYear (date) returns the day ordinal of a year (1~366)
Dayname (date) returns the weekday name of date, such as Select Dayname (current_date);
From_unixtime (TS,FMT) formats UNIX timestamp TS According to the specified FMT format
Hour (time) returns the hour value (0~23)
Minute (time) returns the minute value of time (0~59)
Month (date) returns the months value of date (1~12)
MonthName (date) returns the month name of date, such as: Select MonthName (current_date);
Now () returns the current date and time
Quarter (date) returns a date in the quarter of the year (1~4), such as Select Quarter (current_date);
Week (date) returns a date of the week ordinal of a year (0~53)
Year (date) returns the date of day (1000~9999)
Some examples:
Get current system time: Select From_unixtime (Unix_timestamp ());
Select Extract (Year_month from current_date);
Select Extract (Day_second from current_date);
Select Extract (Hour_minute from current_date);
Returns the difference (number of months) between two date values: Select Period_diff (200302,199802);
Calculate the age in MySQL:
Select Date_format (From_days (To_days (now ())-to_days (birthday)), '%y ') +0 as-from employee;
Thus, if the brithday is a future month or so, the result is 0.
The following SQL statement calculates the absolute age of an employee, that is, when birthday is a future date, a negative value is obtained.
Select Date_format (now (), '%y ')-date_format (Birthday, '%y ')-(Date_format (now (), ' 00-%m-%d ')

MySQL Getting Started note 3