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

In the Mysql compilation and installation of relational database and database foundation (1) We have a general understanding of the basic application of the database. Let's talk about the daily life of MySQL. In the actual production work, I need to know which way I should choose in the database field. This is the premise of our bright path to databases. What skills do you need to develop DBA and manage DBA, respectively?

Developing DBA: database design (Emurr diagram), SQL development, built-in functions, stored procedures (stored procedures and stored functions), triggers, event investigator (even scheduler)

Manage DBA: install, upgrade, backup, restore, user management, rights management, monitoring, analysis, benchmarking, statement optimization (essential for writing SQL statements), data dictionary, configure server as needed (server variables (must master): MyISAM,InnoDB, cache, log)

Let's step into the world of MySQL step by step:

SQL language components:

(1) DDL: data definition language

(2) DML: data manipulation language

(3) Integrity definition language: some functions of DDL

Such as: (constraint) primary key, foreign key, unique key, condition, non-empty, transaction

(4) View definition: virtual table or temporary table, stored as SELECT statement

(5) transaction control

(6) embedded SQL and dynamic SQL

(DCL) control language

The role of data types:

(1) the type of value stored (for example, data cannot be stored if the type is a field)

(2) Storage space occupied (char (40))

(3) fixed length and variable length (char,varchar)

(4) how to be indexed and sorted

(5) whether it can be indexed; (for example, text, which can only locate the left part)

Data dictionary: system cataloging (system catalog)-like roster

Save metadata on the database server

Metadata:

The name of the relationship

The name of each field of each relationship

Data type and length of each field ()

Constraint

The name and definition of the view on each relationship

First name of the authorized user

User's authorization and account information

Data for the statistical category:

Number of fields per relationship

Number of rows in each relationship

Storage method for each relationship

The database that holds the metadata:

Information_schema

Mysql

Performance_shcema

Data type:

Character type

Char-fixed SPAC

Varchar-changing space, case-insensitive

Tinytext

Text (stored as an object, not directly in the table, but with pointers to other tables)

Mediumtext

Longtext

Binary-fixed SPAC

Varbinary-the space of change

Tinyblob

Blob (binary large object)

Mediumblob

Longblob

Numerical type

Exact numerical type

Tinyint

Smallint

Mediumint

Int

Bigint

Decimal

1 integer type

2 Decimal data: decimal (fixed points, accurate representation)

Approximate numerical type (float,double)

Single precision floating point type

Double precision floating point type

Date and time type

Date

Time

Datetime

Timestamp

Year

Boolean (not actually available in mysql) 0 and 1

Built-in type

Enum enumeration

Set collection

List of data types:

Type

Storage Required

Maximum Length

CHAR (M)

M characters

255 characters

VARCHAR (M)

L characters plus 1 or 2 bytes

65535 characters (subject to limittations)

TINYTEXT

L characters + 1 byte

255 characters

TEXT

L characters + 2 bytes65535 charactersMED IUMTEXT

L characters + 3 bytes16777215 charactersLONGTEXT

L characters + 4 bytes4294967295 characters

Data Type Nmae

SQL Standard

Fixed/Variable Length

Range

Size

Attributes

BINARY

No

Fixed

Length of 0-255 bytes

M bytes

DEFAULT

NOT NULL

NULL

VARBINARY

No

Variable

Length of 0-65532bytes

L*x+1

If L

< 255 L*x+2 if L >

two hundred and fifty five

DEFAULT

NOT NULL

NULLTINYBLOB

No

VariableMax length of 255 bytes

Lang 1 bytes

1 byte stroes

Length

NOT NULL

NULLBLOB

NoVariableMax length of 65535 bytes (64 Kb)

Lhasa 2 bytes

2 byte stroes

Length

NOT NULL

NULLMEDIUMBLOB

NoVariableMax length of 16777215 bytes (64 Kb)

Lao3 bytes

3 byte stroes

Length

NOT NULL

NULLLONGBLOB

NoVariableMax length of 4294967295 bytes (64 Kb)

Lhasa 4 bytes

4 byte stroes

Length

NOT NULL

NULL

Data Type

SIGNED Range

UNSIGNED Range

Size

TINY INT

-128 to 127

0 to 255

1 byte

SMALLINT

-32768 to 32767

0 to 65535

2 bytes

MEDIUMINT

-8388608 to 8388607

0 to 16777215

3 bytesINT

-2147483648 to 2147483647

0 to 4294967295

4 bytesBIGINT

-9223372036854775808 to 9223372036854775807

0 to 18446744073709551615

8 bytes

Commonly used attribute modifiers for character types:

Not_null: non-null constraint

Null: null is allowed

Default 'string': default value, not used for text type

Character set 'character set'

Example: query character set settings and server character variables

Mysql > show character set; mysql > show variables like'% char%'

Collation 'rules': collation

Example: query collation

Mysql > show collation

Auto_increment: automatic growth

Premise: non-empty and unique: support index, non-negative value

UNSIGNED: unsigned

Null

Not null

Default

Floating-point common modifiers

Notnull

Null

Default

Unsigned

Date-time modifier

Notnuall

Null

Default

Modifiers for ENUM and SET (enum enumeration, set collection-not suitable for sorting)

Not null

Null

Default''

MySQL SQL_MODE: SQL mode

TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES

Set the value of the server variable: (only used to support dynamic variables)

Server variables that are supported for modification:

Dynamic variables: can be modified at Mysql runtime

Static variable: modify its value in the configuration file and restart it before it takes effect

There are two types of server variables in terms of their effective scope:

Global variable: server level, valid only for newly established sessions after modification; global

Answer variable: session level, valid only for the current call; session

Inherit variables from the global when the session is established

View server variables:

Mysql > show {global | session} variables like/where clause

Examples are as follows:

Mysql > select @ @ {global | session} .resume _ name

Examples are as follows:

Mysql > select * from information_schema.global_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';mysql > select * from information_schema.session_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME'

Examples are as follows:

Modify variable

Premise: by default, only administrators have permission to modify global variables.

Mysql > SET {GLOBAL | SESSION} VARIABLE_NAME='VALUE'

Note: dynamic variable modifications at both global and session levels will fail after restarting mysqld. To be permanently valid, you can define them in the corresponding section in the configuration file [mysqld].

Character case in MySQL:

1. SQL keywords and function names are not case-sensitive.

2. Whether the database, table and view names differ in size depends on the lower-level OS and FS.

3. The names of stored procedures, stored functions and time schedulers are not case-sensitive, but triggers are case-sensitive

4. Table aliases are case sensitive

5. For the data in the field, if the field type is Binary, it is case-sensitive, while non-binary is not case-sensitive.

There is no better way to change the database name, you can only backup it, and then create a new

Database operations:

Create the database:

Mysql > CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

Examples are as follows:

Delete database

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Examples are as follows:

Modify the database

ALTER {DATABASE | SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

Examples are as follows:

Database table management:

Table creation: the first way

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]

(create_definition,...):

Definition of fields: field names, types, and type modifiers

Key, constraint, or index:

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK

{INDEX | KEY}

[table_options]

ENGINE [=] engine_name

View the engines supported by mysql:

Mysql > SHOW ENGINES

AUTO_INCREMENT [=] value

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name

COMMENT [=] 'string'

DELAY_KEY_WRITE [=] {0 | 1}

ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}

TABLESPACE tablespace_name [STORAGE {DISK | MEMORY | DEFAULT}]

There are two types of tables:

The MyISAM table, with three files per table, is located in the database directory

Tb_name.frm structure definition

Tb_name.MYD data file

Tb_name.MYI index file

InnoDB table, which can be stored in two ways

1. Default: each table has a separate file and a shared file

Tb_name.frm: the definition of the table structure, located in the database directory

Tablespace files shared by ibdata#:, which are located in the data directory by default (the directory pointed to by datadir)

2. Independent tablespace files

Tb_name.frm has one table structure file per table

Tb_name.ibd a unique tablespace file

Configure InnoDB engine independent tablespaces to be enabled:

# vim / etc/my.cnf

Innodb_file_per_table = on

Examples are as follows: view information about a specific table-such as the storage engine used

Table creation: the second way (replicating table data does not replicate table structure)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options]

Select_statement

Examples are as follows:

Table creation: the third way (copy table structure)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{LIKE old_tbl_name | (LIKE old_tbl_name)}

Examples are as follows:

Table deletion:

Mysql > drop table table_name; delete the table with the name xxx

Data in the table is deleted:

Mysql > delete from table_name where field name = 'values'

Such as DELETE FROM Test1 WHERE Name='samlee'

Clear the table data:

Mysql > truncate table_name

Truncate is a SQL syntax that can quickly empty all data in a table. And it can be aimed at the fields with automatic increment.

Table modification:

Syntax format: alter table tab1_name

Modify the field definition: alter

Add a new field (add)

(after) Definiable field collation

Examples are as follows:

Mysql > use mydb;mysql > CREATE TABLE Test5 (ID int (10) UNSIGNED auto_increment NOT NULL,PRIMARY KEY (ID)); mysql > ALTER TABLE Test5 ADD Age TINYINT UNSIGNED NOT NULL;mysql > ALTER TABLE Test5 ADD Gender ENUM ('Maurem') NOT NULL DEFAULT'M' AFTER ID;mysql > ALTER TABLE Test5 ADD Name char (10) NOT NULL

Delete Field (DROP)

Mysql > ALTER TABLE Test5 DROP Age

Examples are as follows:

Modify the field-- >

Modify field name (change)

Mysql > ALTER TABLE Test5 CHANGE Name StuName CHAR (30) NOT NULL

Examples are as follows:

Modify field types and properties (modify)

Mysql > ALTER TABLE Test5 MODIFY Gender ENUM ('Maureen') NOT NULL AFTER ID

Modify a constraint, key, or index

Create an index index (add) on a specific field in the table under alter

Mysql > ALTER TABLE Test5 ADD INDEX (StuName)

View the index index in a table

Mysql > SHOW INDEXES FROM Test5\ G

Delete a specific field index (drop) from the table

Mysql > ALTER TABLE Test5 DROP INDEX StuName

Modify the table name (rename)

The format is as follows:

> RENAME TABLE old_tablename TO new_tablename

Modify the storage engine of the table

The format is as follows:

> alter table table_name engine=MyISAM

Examples are as follows:

Mysql > ALTER TABLE Test5 engine=MyISAM;mysql > SHOW TABLE STATUS LIKE 'Test5'\ G

Extended Tips:

1. Specify the fields for the sorting criteria:

ORDER BY col_name [, col_name]...

two。 Convert character set and collation:

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

3. Table option modification:

[table_options] ENGINE [=] engine_name mysql > SHOW ENGINES; AUTO_INCREMENT [=] value [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name COMMENT [=] 'string' DELAY_KEY_WRITE [=] {0 | 1} ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | REDUNDANT | COMPACT} TABLESPACE tablespace_name [STORAGE {DISK | MEMORY | DEFAULT}]

Phase testing:

Create the following table (including structure and content): ID Name Age Gender Course1 Ling Huchong 24 Male Hamogong2 Huang Rong 19 Female Chilian Shenzhang3 Lu Wushaung 18 Female Jiuyang Shenggong4 Zhu Ziliu 52 Male Pixie Jianfa5 Chen Jialuo 22 Male Xianglong Shiba Zhang6 Ou Yangfeng 70 Male Shenxiang Bannuo Gong- -# # create the above table and insert data mysql > CREATE TABLE student (ID smallint not null primary key Name varchar (40) not null,Age tinyint unsigned not null,Gender ENUM ('Flying Magazine M') not null default' Mouse Magical course varchar (40) not null) Mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value (1Magneto Ling Huchong',24,'M','Hamogong'); mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value (2ZHH Rong',19,'F','Chilian Shenzhang'); mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value (3MIT Lu Wushuang',18,'F','Jiuyang Shenggong') Mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value; mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value; mysql > INSERT INTO student (ID,Name,Age,Gender,Course) value (6mai Yu Yangfeng',52,'M','Shenxiang Bannuo Gong')

(1) add fields: select the Class field definition, and place it after the Name field.

Mysql > alter table student add Class tinyint not null after Name

(2) change the name of ID field to SID

Mysql > ALTER TABLE student change ID SID smallint not null

(3) place the SID field at the end

Mysql > ALTER TABLE student MODIFY SID smallint not null AFTER Class

Query operation of MySQL

Select query selection and projection operation query:

Projection: picking fields to display

Projection: SELECT field 1, field 2,... FROM tb_name; # selected display specified field SELECT * FROM tb_name; # display all fields

Select: pick rows that meet the criteria

Select: SELECT field 1,... FROM tb_name WHERE clause [Boolean conditional expression]

Boolean conditional expression operator:

* #: = equal to * #: abc = bde, abc = NULL*#: is not equal to * #:

< 小于*#: 大于*#: >

= greater than or equal to

Understand the MySQL query through a case:

Create a case environment:

Mysql > CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR (30) NOT NULL, Age TINYINT UNSIGNED NOT NULL,Gender ENUM) NOT NULL,Tutor CHAR (30), ClassID TINYINT UNSIGNED); mysql > INSERT INTO students VALUES (1meme Guo Jing',27,'M','Hong qigong',2), (2meme Li Yang Guo',28,'M','Ou Yangfeng',3), (3meme Qiao feng',21,'M','Ling Huchong',3) Mysql > INSERT INTO students VALUES (4 Baochai',19,'F','Rong Sir',1 Xue Baochai',19,'F','Rong Sir',1), (5 Yuhe',37,'F','Shi Qian',2 Xia), (6 Yong',51,'M','Lin Daiyu',1 Wu); mysql > INSERT INTO students VALUES (7 lemons); 11 (11)) (8) (8)

Query records for which students table Tutor is empty

Mysql > SELECT * FROM students WHERE Tutor IS NULL

two。 Query records for which students table Tutor is not empty

Mysql > SELECT * FROM students WHERE Tutor IS NOT NULL

Fuzzy query:

LIKE: supported wildcard characters

%: any character of any length (fuzzy lookup, low performance)

_: any single character

RLIKE,REGEXP: support for regular expressions (fuzzy lookup, low performance)

3. Query records whose names begin with X

Mysql > SELECT * FROM students WHERE Name LIKE'X%'

Or use RLIKE

Mysql > SELECT * FROM students WHERE Name RLIKE'^ X.clients'

4. Query records with students ages 25, 26, 27, 28 (using IN: to determine whether the specified field is in a given list):

Mysql > SELECT * FROM students WHERE Age IN (25, 26, 26, 27, 28)

5. Query records between the ages of 25 and 40

Mysql > SELECT * FROM students WHERE Age BETWEEN 25 AND 40

Combination condition test:

NOT,!: negate AND, & &: and OR, | |: or

6. Query records with SID of "5" and gender of "F"

Mysql > SELECT * FROM students WHERE SID ='5' AND Gender ='F'

7. Query records with Age of '28' or Gender of' F'

Mysql > SELECT * FROM students WHERE Age = '28' OR Gender =' F'

Query sorting:

ORDER BY

(ASC display in ascending order)

8. Query records with Age of '28' or Gender of' F', and sort them by Age value from smallest to largest

Mysql > SELECT * FROM students WHERE Age = '28' OR Gender =' F' ORDER BY Age

(DESC display in descending order)

9. Query records with Age of '28' or Gender of' F' and sort by Age value from highest to lowest

Mysql > SELECT * FROM students WHERE Age = '28' OR Gender =' F' ORDER BY Age DESC

Aggregate functions sum (), avg (), max (), min ()...

Sum average maximum and minimum

10. Calculate the average age of all students

Mysql > SELECT SUM (Age) FROM students

11. Calculate the maximum age of a student

Mysql > SELECT MAX (Age) FROM students

twelve。 Number of records in statistical tables

Mysql > SELECT COUNT (Age) FROM students

13. Calculate the minimum age of a student

Mysql > SELECT MIN (Age) FROM students

14. Inquire about the sum of the average ages of all people over 30.

Mysql > SELECT SUM (Age) FROM students WHERE Age > 30

Group by-> grouping

Having-- > aggregation

15. The following actions are performed on the students table

Group by ClassID to show the number of students in each class

Mysql > SELECT ClassID,COUNT (Age) FROM students GROUP BY ClassID

16. Grouped by Gender, showing the sum of their ages

Mysql > SELECT Gender,SUM (Age) FROM students GROUP BY Gender

17. Grouped by ClassID, showing the classes with an average age of more than 25

Mysql > SELECT ClassID,AVG (Age) FROM students GROUP BY ClassID HAVING AVG (age) > 25

18. Group by Gender to show the sum of the ages of the students over 25 in each group.

Mysql > SELECT Gender,SUM (Age) FROM students WHERE Age > 25 GROUP BY Gender

19. Use LIMIT to display specified rows (effectively handle big data filtering query operations, reduce system performance consumption)

Mysql > SELECT * FROM students LIMIT 2

Mysql > SELECT * FROM students LIMIT 4

Extended Tips:

(1) Export the database file:

# mysqldump-uroot-hlocalhost-predhat mydb > mydb.sql

(2) Import database files

# mysql-uroot-hlocalhost-prehdat

< mydb.sql SELECT语句的执行流程： FROM clause -->

WHERE clause-- > GROUP BY-- > HAVING clause-- > ORDER BY...-- > SELECT-- > LIMIT

SELECT statement:

DISTINCT: only once if the specified result is the same; SQL_CACHE: cached in the query cache; SQL_NO_CACHE: the query result is not cached

MySQL multi-table query and subquery

Import hellodb.sql the following actions are performed on the students table

# mysql-uroot-hlocalhost-predhat

< hellodb.sql 联结查询：事先将两张或多张表join，根据join的结果进行查询； (1)cross join:交叉联结 #第一张表行数*第二张表行数=总行数(很少用) mysql>

SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID

(2) Natural connection (connection can be established only if the two are equal)-internal connection

Equivalent connection

Condition comparison

(3) external connection

Left outer join: only tuples in relationships that appear before the left outer join operation (left) are retained-- based on the elements on the left, and if there is no null on the right

# left_tb LEFT JOIN right_tb ON connection conditions

Mysql > SELECT s.name _ c. Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID

Right outer join: only tuples in relationships that appear after the right outer join operation (right) are retained

# left_tb RIGHT JOIN right_tb ON connection conditions

Mysql > SELECT s.name _ c. Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID

(4) self-connection

Mysql > SELECT t.namemagrs.Name FROM students AS srecom AS t WHERE s.StuID = t.TeacherID

Tips:

AS field alias:

Mysql > SELECT Name AS SamleeName FROM students

Subqueries: queries nested in queries

For subqueries in where

1. Used to compare subqueries in expressions

There can be only one return value for a subquery

2. Used for subqueries in exists

Judge whether it exists or not

3. Used for subqueries in IN

Determine that it exists in the specified list

Subqueries for from clauses

(1) subquery (1) is used in where

Mysql > SELECT s.NamePersons.AgeJournal s.Gender FROM (SELECT * FROM students WHERE Gender='M') AS s WHERE s.Age > 25

(2) subquery (2) is used in from

Mysql > SELECT Name,Age FROM students WHERE Age > (SELECT AVG (Age) FROM students)

The above are Mysql queries and database management of relational databases (2) all contents

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.