Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of getting started with mysql database, which is very detailed and has a certain reference value. Friends who are interested must finish it!

How do I start the MySQL service?

How to start the MySQL service? In addition to being able to check self-boot during installation, you can also run the

Windows as an example, enter the following:

Net start command name: start a service, such as net start MySQL

Net stop command name: shut down a server, such as net stop MySQL

There are two ways to connect to a MySQL database:

Method 1: enter the MySQL command line and enter the password on the command line

Method 2: in the running window:

Format: mysql-u account-p password-h ip of the host installed by the database server (localhost can be used locally)-P database port

Mysql-uroot-padmin-h227.0.0.1-P3306

It assumes that my account number is root and my password is admin.

If the connected database server is on the local computer and the port is 3306.

Mysql-uroot-padmin can be abbreviated.

Navicat for MySQL

Navicat for MySQL [1] is actually the visualization tool of MySQL. It is a powerful MySQL database management and development tool. It provides a set of powerful and cutting-edge tools for professional developers, but it is still easy for new users to learn. Based on the Windows platform, Navicat for MySQL is tailor-made for MySQL and provides management interface tools similar to MySQL. The emergence of this solution will liberate the minds of programmers such as PHP and J2EE, as well as database designers and managers, reduce development costs and bring higher development efficiency for users.

Database operation and storage engine

Database objects: different structural forms of data are stored, managed, and used, such as tables, views, stored procedures, functions, triggers, events, indexes, etc.

Database: a container that stores database objects.

There are two types of databases:

1): system database (database that comes with the system): cannot be modified

Information_schema: stores database object information, such as user table information, column information, permissions, characters, partitions, etc.

Performance_schema: stores database server performance parameter information.

Mysql: stores database user rights information.

Test: a test database that any user can use.

2): user database (user-defined database): generally, one user database per project.

Common operation commands:

See which databases exist on the database server:

SHOW DATABASES

Use the specified database:

USE database_name

See which data tables are in the specified database:

SHOW TABLES

Create a database with the specified name:

CREATE DATABASE database_name

Delete the database:

DROP DATABASE database_name

Note:; is required, otherwise it will not be displayed correctly

Storage engine of MySQL

Data in MySQL is stored in files (or memory) using a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides different functions and capabilities.

By choosing 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, does not support foreign keys.

InnoDB: supports transactions, foreign keys, row-level locking, and low performance.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with MyISAM, it is less efficient and takes up more disk space to retain data and indexes.

MySQL common column types

The most commonly used integer types:

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 a value is retrieved from the database, it can be lengthened to a specified length.

For example, specifying a field of type INT (6) ensures that values with fewer than six numbers are automatically filled with spaces when retrieved from the database.

It is important to note that using a width indicator does not affect the size of the field and the range of values it can store. Generally, it is not necessary to specify the bit width.

Age int (2) does not mean that age stores up to 99, but that query age uses two zeros to occupy space when it is worth it.

FLOAT [(sQuery p)]:

DOUBLE [(sQuery p)]: decimal type, can store real and integer types, precision (p) and range (s)

Money double (5d2): integers and decimals occupy a total of 5 places. Among them, decimal places account for 2 places, the maximum value: 999.99, the minimum-999.99.

It's not accurate enough.

Fixed-point data type: DECIMAL, high precision type, amount and currency preferred.

MySQL column type Java data type

FLOAT float/Float

DOUBLE double/Double

DECIMAL BigDecimal

Char (size) fixed-length characters, 0-255bytes. Size refers to N characters. If the number of characters inserted exceeds the set length, it will be intercepted and warned.

Varchar (size) variable length characters, 0-255bytes, supporting 65535 bytes from MySQL5. If the number of characters inserted exceeds the set length, it will be intercepted and warned.

Generally store a large number of strings, such as plain text of articles, you can choose the TEXT series type.

Note: in MySQL, characters are enclosed in single quotation marks. Equivalent to a string in Java (String,StringBuilder/StringBuffer)

The date and time types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Note: in MySQL, date-time values are enclosed in single quotation marks. Equivalent to Date,Calender in Java.

BINARY 、 VARBINARY 、 TINYBLOB 、 BLOB 、 MEDIUMBLOB 、 LONGBLOB:

Store graphics, sound and video, binary objects, 0-4GB.

However, in development, we generally store the path where binaries are saved in the database.

BIT: we typically store 0 or 1, which is a value of type boolean/Boolean in Java.

Operation of the table

1. Enter a database first. (use the USE database_name; command)

two。 Enter the command to create the table:

CREATE TABLE table name (

Type of column name 1 column [constraint]

Type of column name 2 column [constraint]

... .

Type constraint of column name N column

);

Note: there is no comma on the last line

If the keyword of the database is used in creating the table.

For example, create a new order table: (order), but order is the keyword in the database (sorting is used).

Table name: t_order, if the cost uses the word order. At this point, enclose it in backquotes (), order`s.

In general, the list name is: t _ name.

Example: create a table

Create a student information table and record the student's id,name,age.CREATE TABLE `t _ student` (`id `bigint, `name `varchar (20), `age `int)

View the table structure:

DESC table_name

View the detailed definition of the table (show the definition SQL statement of the table):

SHOW CREATE TABLE table_name

Delete the table:

DROP TABLE table_name

Constraints of the table (for a column):

1. Non-null constraint: NOT NULL, the content of a column is not allowed to be empty.

two。 Set the default value for the column: DEFAULT.

3. Unique constraint: UNIQUE, in this table, the contents of the column must be unique.

4. Primary key constraint: PRIMARY KEY, non-empty and unique.

5. The primary key grows from: AUTO_INCREMENT, starting from 1, with a step size of 1.

6. Foreign key constraints: foreign key columns in the FOREIGN KEY,A table. The value of the foreign key column in table A must refer to a column in table B (the primary key of table B).

Primary key design that uniquely identifies a row of data:

1: single field primary key, single column as primary key, recommended.

Compound primary key, using multiple columns as primary key, is not recommended.

2: there are two types of primary keys:

1)。 Natural primary key: use a column with business meaning as the primary key (not recommended), such as ID number

2)。 Proxy primary key: use a column with no business meaning as the primary key (recommended)

The above is all the contents of the article "sample Analysis of getting started with mysql Database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.