What are the basic operations of mysql?

Added

Create database: create database mpc [charset utf8]; (set database encoding)

Create data table: create table [if not exists] table name (field 1, field 2... [Index 1, Index 2..., Constraint 1, Constraint 2...]) [Table option 1, Table option 2...];

Field setting format:
Field name type [field attribute 1, field attribute 2...]
Type: int, tinyint, float, double, char, varchar, text, datetime, date, time, timestamp...
Field attributes:
auto_increment: Integer auto-increment (must be the primary key)
primary key: Set the primary key, and it is unique. Only one can be set in a table, it cannot be empty (null)
unique key: Set the unique key Index, multiple unique indexes can be set in a data table. Multiple values ​​in this field can be empty (null)
not null: cannot be empty
default: set the default value
comment 'Field description Text': field description;

create table if not exists createtest(
id int auto_increment primary key,
name varchar(40) not null unique key,
password varchar(40) not null,
age tinyint default 18,
email varchar(40) comment '电子邮件');

Index: perform some sorting in advance on all data in a table within the database;
Ordinary index: key (field name);
Unique index: unique key (field name);
Primary key index: primary key (field name);
Joint primary key: primary key (use commas for multiple field names) Separated);
Full text index: fulltext (field name);
Foreign key index: foreign key (field name) references table name (field name); (A field of one table is a field of another table (Must be the primary key), if the entered value is no longer in the field of the external table, it cannot be entered)

create table tab1_key(
id int auto_increment,
name varchar(20) not null,
emai varchar(50) not null,
age int not null default 18,
grades1 int,primary key(id),unique key(name),foreign key(grades1) references tab2_key(grades2)
);create table tab2_key(
stu_id int not null,
teacher varchar(20) not null,floor int not null,
grades2 int auto_increment primary key);

Constraint: A regulation that requires the data to meet certain conditions
Unique constraint: unique key (field name);
Primary key constraint: primary key (field name);
Foreign key constraint: foreign key (field name) references table name (field name); (a table The field is a field in another table (must be the primary key). If the entered value is no longer in the field of the external table, it cannot be entered)
Non-null constraint: not null; when setting a field, it cannot be Empty value
Default constraint: default XX value, set the default value of a field to xx;
Check constraint: check (some kind of judgment statement)

create table tab_check(
age tinyint,
check (age>0 and age<100)
);-- 但是mysql并不能生效，语法能够通过

Table options: When creating a table, overall settings for the table:
charset=Character encoding to be used;
engine=Storage engine to be used (also called table type); (The default is InnoDB )

A relational database table is a data structure used to store and organize information. A table can be understood as a table composed of rows and columns, similar to the form of an Excel spreadsheet. Some tables are simple, some are complex, some tables are not used to store any long-term data at all, and some tables are very fast to read, but very poor when inserting data; in the actual development process, we may need Various tables, different tables, mean that different types of data are stored, and there will be differences in data processing. For MySQL, it provides many types of storage engines. We can choose different storage engines according to our data processing needs, so as to maximize the use of MySQL's powerful functions. This blog post will summarize and analyze the characteristics of each engine and its applicable situations, and will not get entangled in deeper things. My learning method is to first learn to use it, know how to use it, and then find out how to use it. The following is a brief introduction to the storage engines supported by MySQL.

The above is the detailed content of What are the basic operations of mysql?. For more information, please follow other related articles on the PHP Chinese website!