MySQL Database and table management

MySQL Database and table management

Yun Zhengjie

Copyright Notice: Original works, declined reprint! Otherwise, the legal liability will be investigated.

the topic we are discussing today is how to use MySQL for development, our main work is not to develop SQL, but nonetheless, we may need to do a performance evaluation of the complex SQL statements written by others, to analyze the SQL statements they write are not efficient enough, if not, We also have the ability to rewrite the SQL statements of each other. So, we do not have to develop SQL on our own, but we must have a look at the ability to read the SQL written by others yo! I. Responsibilities of the DBA1. Development DBA:Responsible for database design (e-r diagram), SQL development, built-in functions, storage history (stored procedures and storage functions), triggers, Time Scheduler (event Scheduler)2. Managing DBAs:responsible for installation, upgrade, backup, recovery, user management, rights management, monitoring, analysis, benchmarking, statement Optimization (SQL statements), data dictionary, configuring servers as needed (server variables: myisam,innodb, cache, log)  two. SQL Language Components1.DDL:Full name Data defination, we call it the definition language, typical command commands have Creat/alter/drop2.DML:the full name of data manipulation, we call it operation language, typical command has insert/delete/select/update3. Integrity definition language, part of the DDL featurePRIMARY KEY constraints, FOREIGN KEY constraints, unique key constraints, conditional constraints, non-null constraints, transaction constraints4. View definition: The virtual table, which is a stored SELECT statement5. Transaction control:For example transactions (perform "Help content" on the MySQL interactive interface to see the relevant information.) )6. Embedded SQL and dynamic sql:7.DCL:We call it the data Control language, such as the typical command that implements authorization and permission recall has grant/revoke.  three. Function of data typeMySQL data type please refer to: http://www.cnblogs.com/yinzhengjie/p/7818092.html1. stored value types;2. The amount of storage space occupied;3. Fixed length, variable length;4. How to be indexed and sorted;5. Whether it can be indexed;  Four. Data dictionary: Catalog of Dependent Systems (System catalog)for a relational database, its data dictionary is also stored in a different place. For the MySQL database, this location is the database named MySQL. When we first started MySQL, it was the first step to initialize the system tables, and the so-called initialize system tables were used to create the MySQL database. We can also call this MySQL database a data dictionary called MySQL. A data dictionary is used to hold metadata on the database server. So what is meta data? I have summed up the following points:1>: The name of the Save Relationship (table)2> Save the names of each field in each relationship (table)3> Save the data type and length of each field4> save a constraint5> Save the name and view definition of the view on each relationship (table)6> Save the name of the authorized user (the user table)7>. Save user's authorization and account information, etc.8> Statistics class data, such as the number of each relationship field, the number of rows per relationship, the storage method for each relationship9> Save the metadata database (i.e.: Information_schema,mysql, Performance_schema) Five. mysql Internal componentsas shown, the connector (connectors) and the connection pool are connected between them. All SQL statements for connection pooling (Connection pool) are sent to the SQL interface (SQL Interface) for reception and then parsed by the parser (Parser), optimized for processing by the optimizer (Optimizer). Eventually we might get the data in the cache (caches&buffers), but it's not done by the storage engine (pluggable Storage Engines) to execute the SQL statement.

Six. Description of the character case in MySQL1.SQL keywords and functions are case-insensitive;2. The case sensitivity of database, table, and view names depends on the underlying OS (operating system) and FS (file system);3. The names of stored procedures, stored functions, and event schedulers are not case-sensitive, but the triggers are case-sensitive;4. Table aliases are case-sensitive;5. For the data in the field, if the field type is binary type, case-insensitive, non-binary case-insensitive; Seven. Detailed SQL instructions-database operations1. Creation of the databasea> viewing help when creating a library

1Mysql>Help CREATE Database2Name:'CREATE DATABASE'3 Description:4 Syntax:5CREATE {DATABASE |SCHEMA} [IF not EXISTS] db_name6 [Create_specification] ...7 8 create_specification:9[DEFAULT] CHARACTER SET [=] Charset_name #设置字符集Ten| [DEFAULT] COLLATE [=] Collation_name #设置排序方式 One  A CREATE Database creates a database with the given name. to use this -statement, you need the CREATE privilege forThe database. CREATE -SCHEMA is a synonym forCREATE DATABASE. the  -Url:http://dev.mysql.com/doc/refman/5.1/en/create-database.html -  -  +Mysql>

b> Create a database that does not exist

1Mysql>show databases;2+--------------------+3| Database |4+--------------------+5| Information_schema |6| MySQL |7| Test |8+--------------------+9 3RowsinchSet (0.00sec)Ten  OneMysql> AMysql>CREATE database Yinzhengjie; -Query OK,1Row affected (0.01sec) -  theMysql>show databases; -+--------------------+ -| Database | -+--------------------+ +| Information_schema | -| MySQL | +| Test | A| Yinzhengjie | at+--------------------+ - 4RowsinchSet (0.00sec) -  -Mysql>

C> How to use the Create command for a database already exists

1Mysql>show databases;2+--------------------+3| Database |4+--------------------+5| Information_schema |6| MySQL |7| Test |8| Yinzhengjie |9+--------------------+Ten 4RowsinchSet (0.00sec) One  AMysql> CREATE DatabaseifNot exists Yinzhengjie; -Query OK,1Row affected,1Warning (0.00sec) -  theMysql>show databases; -+--------------------+ -| Database | -+--------------------+ +| Information_schema | -| MySQL | +| Test | A| Yinzhengjie | at+--------------------+ - 4RowsinchSet (0.00sec) -  -Mysql>

2. Deletion of Databasesa>. Viewing help when deleting a database

MySQL Database and table management