Mysql database quick start basic learning (classic tutorial)

This article is the basic introductory knowledge for learning mysql database, including commonly used operating commands. These knowledge points must be mastered to learn mysql database. Save them to be prepared. First, start the MySQL service, and then connect to the MySQL database. There are two ways. Method 1: Enter the MySQL command line and enter the password in the command line; Method 2: In the run window; details are explained below.

How to start the MySQL service?

How to start the MySQL service? In addition to checking the box to automatically start when booting during installation, you can also run

window (windows) as an example, enter the following:

net start command name: open 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 the MySQL database :

Method 1: Enter the MySQL command line and enter the password in the command line;
Method 2: In the running window:
Format: mysql -u account -p password -h The ip of the host where the database server is installed (if it is the local machine, you can use localhost) -P database port
mysql -uroot -padmin -h127.0.0.1 -P3306
The above assumes that my account is root and the password is admin
If the connected database server is on this machine and the port is 3306.
can be abbreviated as: mysql -uroot -padmin

Navicat for MySQL

Navicat for MySQL[1] is actually a visualization tool for MySQL and is a powerful MySQL database management and development tool that provides a powerful enough set of tools for professional developers, yet still easy to learn for new users. Navicat for MySQL is based on the Windows platform and is tailor-made for MySQL, providing a management interface tool similar to MySQL. The emergence of this solution will liberate the brains of PHP, J2EE and other programmers as well as database designers and managers, reduce development costs, and bring higher development efficiency to users.

Database operation and storage engine

Database objects: different structural forms for storing, managing and using data, 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 (the system’s own database): cannot be modified
Information_schema: stores 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: stores database user permission information.
test: A test database that can be used by any user.

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

Commonly used operating commands:

Check which databases exist on the database server:
SHOW DATABASES;
Use the specified database:
USE database_name;
Check 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

MySQL storage engine

Data in MySQL is stored using various technologies file (or memory). Each of these technologies uses different storage mechanisms, indexing techniques, locking levels and ultimately provides different functionality and capabilities.
By choosing different technologies, you can gain additional speed or functionality, thereby improving the overall functionality of your application.

MyISAM: has high insertion and query speed, but does not support transactions and foreign keys.
InnoDB: supports transactions, supports foreign keys, supports row-level locking, and has low performance.

The InnoDB storage engine provides transaction safety with commit, rollback, and crash recovery capabilities. However, compared with MyISAM, the processing efficiency is poor and it takes up more disk space to retain data and indexes.

MySQL commonly used column types

The most commonly used integer types:
MySQL column types                                                                                                                                                                                                                      

MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。
例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。
需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。一般不用指定位宽。
age  int(2),并不是代表age最多存储99,而是指查询age值得时候使用两个0来占位.

FLOAT[(s,p)]   ：
DOUBLE[(s,p)]  ： 小数类型，可存放实型和整型 ,精度(p)和范围(s)
                 money double(5,2): 整数和小数一共占5位.其中小数占2位,最大值:999.99,最小-999.99.
                 都不够精确。
定点数据类型： DECIMAL，高精度类型，金额货币优先选择。

MySQL列类型               Java数据类型
FLOAT                        float/Float
DOUBLE                    double/Double
DECIMAL                   BigDecimal

char(size)    定长字符，0 - 255字节，size指N个字符数，若插入字符数超过设定长度，会被截取并警告。
varchar(size)        变长字符，0 - 255字节，从MySQL5开始支持65535个字节，若插入字符数超过设定长度，会被截取并警告。
一般存储大量的字符串，比如文章的纯文本，可以选用TEXT系列类型。
注意:在MySQL中，字符使用单引号引起来。 相当于Java中字符串(String,StringBuilder/StringBuffer);

日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
注意:在MySQL中，日期时间值使用单引号引起来。 相当于Java中Date，Calender。

BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB：
存放图形、声音和影像，二进制对象，0-4GB。
但是,在开发中，我们一般存储二进制文件保存路径的路径存储在数据库中。
BIT：我们一般存储0或1，存储是Java中的boolean/Boolean类型的值。

表的操作

1.先进入某一个数据库.（使用USE database_name;命令）
2.输入建表的命令:
CREATE TABLE 表名(
  列名1    列的类型   [约束],
  列名2    列的类型   [约束],
  ….
  列名N    列的类型   约束
);
注意:最后一行没有逗号

若在建表中使用到了数据库的关键字.
比如新建一张订单表:(order),但是order是数据库中的关键字(排序使用).
表名:t_order,若费用使用order这个单词.此时使用反引号()括起来,order`.
一般，起表名为：t_名字。

例子:创建一张表

创建一张学生信息表,记录学生的id,name,age.CREATE  TABLE   `t_student`(         `id `         bigint,         `name `  varchar(20),         ` age `     int);

查看表结构：
DESC table_name;
查看表的详细定义(显示表的定义SQL语句)：
SHOW CREATE TABLE table_name;
删除表：
DROP TABLE table_name;

表的约束(针对于某一列):

1.非空约束：NOT NULL，不允许某列的内容为空。
2.设置列的默认值：DEFAULT。
3.唯一约束：UNIQUE，在该表中，该列的内容必须唯一。
4.主键约束：PRIMARY KEY， 非空且唯一。
5.主键自增长：AUTO_INCREMENT，从1开始，步长为1。
6.外键约束：FOREIGN KEY，A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)。

Primary key design, uniquely identifies a certain row of data:
1: Single field primary key, single column as the primary key, recommended.
Composite primary key, using multiple columns as the 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). Surrogate primary key: use a column with no business meaning as the primary key (recommended);

Related articles:

Mysql basic command introduction Learning_MySQL

Common operating commands for mysql database study notes_MySQL

Related videos:

Database mysql video tutorial

The above is the detailed content of Mysql database quick start basic learning (classic tutorial). For more information, please follow other related articles on the PHP Chinese website!