MySQL Database QuickStart Basics Learning (Classic tutorial)

This article is a basic primer on learning MySQL database, including common operations commands, learning MySQL database these knowledge points must be mastered, the collection is prepared, first began to accept the start MySQL service, and then connect MySQL database There are two ways 1: Go to MySQL command line , enter the password on the command line, Mode 2: In the Run window, as detailed below.

How do I start the MySQL service?

For how to start the MySQL service? In addition to the installation can be checked with the boot start, you can also run

Window (Windows) For example, enter the following:

Net START command name: Open a service such as: net start MySQL
net stop command name: Close a server, such as: net stop MySQL

There are two ways to connect a MySQL database:

Mode 1: Enter the MySQL command line and enter the password on the command line;
Mode 2: In the Run window:
Format: mysql-u account-p password-H database server installed host IP (if native can use localhost)-p database Port
mysql-uroot-padmin-h127.0.0.1-p3306
The above assumes that my account is the root password is admin
If the connected database server is on this computer, and the port is 3306.
It can be abbreviated: mysql-uroot-padmin

Navicat for MySQL

Navicat for Mysql[1] is a MySQL visualizer, a powerful MySQL database management and development tool that provides professional developers with a powerful and sophisticated set of tools, but is still easy to learn for new users. Navicat for MySQL is based on the Windows platform, tailored for MySQL, and provides a mysql-like management interface tool. The emergence of this solution will liberate PHP, Java EE and other programmers and database designers, managers of the brain, reduce development costs, for users to bring higher development efficiency.

Database operations and Storage engine

Database objects: Stores, manages, and uses different structural forms of data, such as tables, views, stored procedures, functions, triggers, events, indexes, and so on.

Database: A container that stores database objects.

There are two types of databases:
1): System database (the system comes with the database): cannot be modified
INFORMATION_SCHEMA: Store database object information, such as: User table information, column information, permissions, characters, partitions and other information.
Performance_schema: Stores database server performance parameter information.
MySQL: Store database user rights information.
Test: Any user can use the testing database.

2): User database (user-defined database): General, one project one user database.

Common Operations Commands:

To see which databases exist on the database server:
SHOW DATABASES;
Use the specified database:
Use database_name;
View which data tables are in the specified database:
SHOW TABLES;
Create a database with the specified name:
CREATE DATABASE database_name;
To delete a database:
DROP DATABASE database_name;

Note: It is necessary, otherwise it will not be displayed correctly.

MySQL's storage engine

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately providing different capabilities and capabilities.
By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

MyISAM: Has a high insert, query speed, but does not support transactions, foreign keys are not supported.
InnoDB: Supports transactions, supports foreign keys, supports row-level locking, and has low performance.

The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to myisam, processing is inefficient and consumes more disk space to preserve data and indexes.

MySQL Common column types

The most common types of integers:
MySQL column type Java data type
Int/integer:int/integer
Bigint:long/long

MySQL extends the SQL standard in the form of an optional display width indicator, so that when retrieving a value from the database, it can be extended to a specified length.
For example, specifying that a field is of type INT (6) guarantees that values with fewer than 6 contained numbers can be automatically populated with spaces when they are retrieved from a database.
It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store. Generally do not refer to the positioning width.
The age int (2), which does not represent the maximum storage 99, is the time when the query is worth using the two zero placeholder.

float[(s,p)]:
double[(s,p)]: decimal type, can be stored in real and integral type, precision (p) and range (s)
Money double (5,2): Integers and decimals take up 5 of the total. The decimal place is 2 bits, the maximum value is 999.99, and the minimum-999.99.
are not accurate enough.
Fixed-point data type: DECIMAL, high-precision type, money-first choice.

MySQL column type Java data type
FLOAT float/float
DOUBLE double/double
DECIMAL BigDecimal

char (size) fixed length character, 0-255 bytes, size refers to the number of n characters, if the number of characters inserted exceeds the set length, will be intercepted and warned.
varchar (size) variable length character, 0-255 bytes, starting from MySQL5 support 65,535 bytes, if the number of characters inserted more than the set length, will be intercepted and warned.
Generally store a large number of strings, such as plain text of the article, you can choose the text series type.
Note: In MySQL, characters are quoted in single quotes. Equivalent to a string in Java (String,stringbuilder/stringbuffer);

Date and time types are datetime, date, TIMESTAMP, time, and year.
Note: In MySQL, datetime values are quoted in single quotes. Equivalent to Date,calender in Java.

BINARY, VARBINARY, Tinyblob, BLOB, Mediumblob, Longblob:
Store graphics, sounds, and images, binary objects, 0-4GB.
However, in development, we typically store the path where the binary files are saved in the database.
BIT: We typically store 0 or 1, which is the value of the Boolean/boolean type in Java.

Operation of the table

1. Enter a database first. (using use database_name; commands)
2. Enter the command to create the table:
CREATE Table Table name (
Column name 1 column type [constraint],
Column Name 2 column type [constraint],
....
Type constraint for column name n column
);
Note: The last line has no comma

If a database keyword is used in a table.
For example, create a New Order table: (order), but order is a keyword in the database (sort used).
Table name: T_order, if the cost is to use the word order. Use the back quotation mark (order ') at this time. )括起来,
Generally, the table name is: t_ name.

Example: Creating a Table

Create a student information sheet to record the student's id,name,age. CREATE  TABLE   ' t_student ' (         ' id '         bigint,         ' name '  varchar (),         ' age '     int);

To view the table structure:
DESC table_name;
View a detailed definition of the table (the SQL statement that shows the table's definition):
SHOW CREATE TABLE table_name;
To delete a table:
DROP TABLE table_name;

Constraint on a table (for a column):

1. Non-empty constraint: NOT NULL, the contents of a column are not allowed to be empty.
2. Set default values for columns: defaults.
3. Unique constraint: Unique, in which the contents of the column must be unique.
4. PRIMARY KEY constraint: PRIMARY key, non-null and unique.
5. Primary key self-growth: auto_increment, starting from 1, step 1.
6. FOREIGN KEY constraint: FOREIGN The foreign key column in the Key,a table. The value of the Foreign key column in table A must be referenced to a column in table B (table B primary key).

A primary key design that uniquely identifies a row of data:
1: Single-field primary key, as the primary key, recommended to use.
Composite primary key, using multiple columns to act as the primary key, is not recommended.
2: The primary key is divided into two types:
1). Natural PRIMARY Key: Use the business meaning of the column as the primary key (not recommended), such as the identity card number;
2). Proxy primary key: Use a column with no business meaning as the primary key (recommended);

Related articles:

MySQL basic command Getting started learning _mysql

MySQL Database Learning Notes Common Operations Command _mysql

Related videos:

Database MySQL Video tutorial