Getting started with day01_mysql database

Why should I learn MySQL database?

MySQL occupies the IT market. Most Internet companies such as Taobao, Netease, Baidu, Sina, and Facebook use MySQL software. In the field of online games, most of the back-end databases use MySQL, such as Audition and World of Warcraft. , And even more powerful, many projects in China Power Grid and China Mobile are also using our mysql. Database :  All the data generated by the software in the process of use must eventually be stored in the database, and the test work often needs to verify the correctness of the data, so it is necessary to learn the database

What is a database?

  • English words for database: DataBase Abbreviation: DB, database is a medium for persistent data, which can be understood as a warehouse for storing and managing data! Persistence : Save data to a power-down storage device for later use . Most of the time of persistence is to store the data in the memory in the database, of course, it can also be stored in a disk file or XML data file.

Why use a database?

  • Data can be persisted to hard disk
  • Can store large amounts of data
  • Easy to search
  • Ensure the consistency and integrity of data
  • Safe and shareable
  • Through combination analysis, new data can be generated

Common classifications of databases

  • Relational database (RDMS: (Relational Database Management System) characteristics of relational database system :  store data in the form of data tables, which is convenient for data query
  • Non-relational database: a database type that does not store data in the form of data tables

The core elements of relational databases

Database related concepts

  • DB: database (database) : "warehouse" for storing data. It saves a series of organized data. The database actually exists as a file on the hard disk.
  • DBMS: Database Management System ( Database Management System). A database is a container created and operated by a DBMS.
  • SQL: Structure Query Language (Structure Query Language): A language specifically used to communicate with databases. When the SQL statement is executed, it will actually be compiled internally, and then sql will be executed. (The compilation of sql statement is done by DBMS.)

The DBMS is responsible for executing sql statements, and manipulate the data in the DB by executing sql statements.

Common relational database management system

Database Management System (DBMS): Refers to a large-scale software for operating and managing databases, used to establish, use, and maintain databases, and perform unified management and control of the databases to ensure the security and integrity of the databases. The user accesses the data in the tables in the database through the database management system. The common ones are as follows:

  • MySQL: Open source and free database, small database, has been acquired by Oracle. MySQL 6.x version also began to charge. Later, Sun company acquired MySQL, and Sun company was acquired by Oracle
  • Oracle: A large-scale database for a fee, a product of Oracle Corporation.
  • DB2: IBM's database product, chargeable. Often used in banking systems.
  • SQL Server: A medium-sized database charged by MicroSoft. Languages ​​such as C# and .net are often used.
  • SQLite: Embedded small database, used on mobile phones, such as Android.

Baidu is recommended for installation and uninstallation of database management system (DBMS). We are installing a MySQL server, and the system will help us automatically register a MySQL service in the background. A service is an application software without an interface. If we want to log in to the MySQL server, we must first enable the MySQL service. The MySQL directory structure is shown in the figure below:

The MySQL data storage directory is the data directory, and each directory under data represents a database.

Start and login of database service

Method 1: Right-click on My Store and select Manage, enter the following interface

Method 2: Enter DOS command mode to start

Connect to the database

MySQL is a database that requires an account name and password to log in. After logging in, it provides a default root account. You can log in using the password set during installation. We can log in through the console or through a graphical tool. For example: SQLyog is a simple, efficient, and powerful graphical MySQL database management tool produced by the well-known Webyog company in the industry. Using SQLyog can quickly and intuitively allow you to maintain a remote MySQL database through the network from any corner of the world

The relationship between database management system, database and table

A database management program (DBMS) can manage multiple databases, and generally developers will create a database for each application. In order to save the data of the entity in the application, multiple tables are generally created in the database to save the data of the entity User in the program. The relationship between database management system, database and table is shown in the figure:

The characteristics of database storage data

  • Put the data in the table, and then put the table in the library
  • There can be multiple tables in a database, and each table has a name to identify itself. The table name is unique.
  • Table has some characteristics, these characteristics define how data is stored in the table, similar to the design of "class" in java.
  • The table is composed of columns, which we also call fields. All tables are composed of one or more columns, and each column is similar to "attributes" in java. Column: called a field (column)
  • The data in the table is stored in rows, and each row is similar to the "object" in java. Row: called data/record (data)

MySQL data types and operators

Numerical type

  • Unsigned means that negative numbers cannot be stored. By default, signed values ​​are used for storage. Specifying int(M) has no effect
  • int(M) specifies the maximum number of digits to be stored, and must be used together with unsigned zerofill (unsigned) to make sense. If the M bit is not satisfied, the left side is filled with 0.

Floating point series : float, double (or real)

double(M,D): indicates that the longest is M digits, of which D digits after the decimal point
For example: the data range represented by double(5,2) [-999.99,999.99], if it exceeds this range, an error will be reported.

The maximum value can be stored, and the maximum 15~16 bits are valid

Fixed-point series: decimal (the bottom layer is actually a string for storage)

decimal(M,D): indicates that the longest is M digits, of which D digits after the decimal point

If decimal does not use width, only integers are retained and decimals are rounded

Bit type: bit

The byte range is: 1-8, the value range is: bit(1)~bit(64), the default bit(1) converts the incoming value to binary and saves it. Used to store binary numbers. For bit fields, using the select command directly will not see the results. You can use the bit() function to insert. When inserting a bit type field, use the bit() function to convert it to a binary value and then insert it, and use the bin() function to read it.

Date and time type

If the value inserted by timestamp is null, the current time is inserted by default, and the time zone changes and timestamp will also change accordingly

String type

MySQL provides a variety of storage types for character data, and different versions may differ. Common ones are: char, varchar, xxtext, binary, varbinary, xxblob, enum, set, etc.

Explanation of common string types:

  • String type char, varchar(M). If char width is not specified, the default is 1 character. varchar(M), the width must be specified
  • Binary and varbinary are similar to char and varchar, except that they contain binary strings and do not support fuzzy queries and the like.
  • Generally, when saving a small number of strings, we will choose char and varchar; when saving larger text, we usually choose to use text or blob series. Blob and text values ​​will cause some performance problems, especially when a large number of delete operations are performed, they will leave a large "hole" in the data table. In order to improve performance, it is recommended to use the optimize table function on such tables regularly. Defragment. You can use a synthetic index to improve the query performance of large text fields. If you need to perform fuzzy queries on large text fields, MySql provides a prefix index. But still avoid retrieving large blobs or text values ​​when unnecessary.

enum enumeration type

  • Its value range needs to be explicitly specified through enumeration when the table is created. Enumeration of 1 to 255 members requires 1 byte of storage; for 255`65535 members, 2 bytes of storage are required. For example: gender enum('男','女'). Only one of the enumerated values ​​can be selected at a time.

set collection type

  • Can contain 0~64 members. You can select multiple members from the set at a time. If a set of 1-8 members is selected, it takes 1 byte, 2 and 3 in turn. . 8 bytes. For example: hoppy set('eat','sleep','play games','travel'), when you select'eat, sleep' or'sleep, play games, travel'

SQL overview

What is SQL

SQL (Structured Query Language) is a "Structured Query Language", which is an operating language for relational databases. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, etc. Although SQL can be used in all relational databases, many databases still have some syntax after the standard, which we can call "dialects". For example, the LIMIT statement in MySQL is a dialect unique to MySQL, which is not supported by other databases! Of course, Oracle or SQL Server has its own dialect.

SQL syntax requirements

  1. SQL statements can be written in single or multiple lines, ending with a semicolon;
  2. Spaces and indentation can be used to enhance the readability of the sentence;
  3. The keywords are not case-sensitive, it is recommended to use uppercase;
  4. It is recommended not to use mysql keywords as table names, field names, etc. If you use it accidentally, please use `(floating sign) in the SQL statement
  5. Do not include spaces between database and table names, field names, and other object names
  6. In the same mysql software, the database cannot have the same name, the table cannot have the same name in the same database, and the field cannot have the same name in the same table.

Annotation

classification

  1. DDL (Data Definition Language): Data Definition Language, used to define database objects: libraries, tables, columns, etc.;
  2. DML (Data Manipulation Language): data manipulation language used to define database records (data);
  3. DCL (Data Control Language): Data Control Language, used to define access rights and security levels;
  4. DQL (Data Query Language): Data query language, used to query records (data).