Mysql Single Table, mysql Single Table maximum number of rows

Mysql Single Table, mysql Single Table maximum number of rows

Create and delete Databases
Black window to start the Database Service: net start mysql
Disable Database Service: net stop mysql

Create a database
Use the keyword create database
Format:
Create database name;
Create database name character set;

View All databases in mysql
Show databases;

View the definition information of a Database
Show create database name example: show create database mybase;

Switch Database
Use Database Name example: use test;

View the database in use
Select database;

Delete Database
Drop database name example: drop database test;

Create a table in the database.

Use the keyword create table
[] It is optional in the database.
Format:
Create table Name (
Field Name Data Type [length] [constraints],
Field Name Data Type [length] [constraints],
......
Field Name Data Type [length] [constraints] (the last one cannot contain commas)
);
For example, create a product category table category.
Create table category (
Cid int primary key,
Cname varchar (100)
);

View all tables in the current database
Show tables;

View table structure
Desc table name: desc category;

Delete table
Format: drop table Name
Example: drop table category;

Modify a table and add columns
Alter table name add column name type [length] [constraint];
Example: alter table category add name int;

Modify the type length and constraints of columns in a table
Alter table name modify column name type [length] [constraint];
NOTE: If data exists, you must note that the data type varchar --> error data is easy to exist.
Example: alter table category modify description int;
Alter table category modify description varchar (20) not null;

Modify column names and data types and constraints
Alter tble Table Name drop column name;
Note: You must be cautious when deleting data in the column.
Example: alter table category drop descr;

Modify Table Name
Rename table name to new table name
Example: rename table category to student;

Modify the character set of a table
Alter table name character set
Note: It is not recommended that the execution may produce garbled characters.
Example: alter table category character set gbk;

Insert data to a database table
Insert [into]
Format:
Primary Key: insert into Table Name (Field 1, Field 2,...) values (value 1, value 2 ,....);
Primary Key auto-increment, omitted primary key: insert into Table Name (excluding primary key) values (excluding primary key );
Note:
1. The field and value must correspond one to one (number, data type)
2. Except for the numeric type (int, double), quotation marks must be used to enclose other data types.
You can use ''. Or" ". We recommend that you use''
Primary Key: insert into Table Name (Field 1, Field 2,...) values (value 1, value 2 ,....);
Example: insert into category (cid, cname) values (1, "clothing ");
Insert into category (cid, cname) values (1, "Color TV ");


Create a category Table Primary Key auto-incrementing auto_increment (primary key self-added, self-increased)
Example: create table category (
Cid int primary key auto_increment,
Cname varchar (100)
);
Primary Key auto-increment, omitted primary key: insert into Table Name (excluding primary key) values (excluding primary key );
Example: insert into category (cname) values ("Color TV ");

 

Batch insert data
Format:
Primary Key: insert into Table Name (Field 1, Field 2 ,...) values (value 1, value 2 ,...), (value 1, value 2 ,...), (value 1, value 2 ,...);
Primary Key auto-increment, omitted primary key: insert into Table Name (excluding primary key) values (value 1, value 2 ,...), (value 1, value 2 ,...)..;
Insert into category (cid, cname) values (3, 'airconditioner '), (4, 'washer ');
Insert into category (cname) values ('microwave oven '), ('electric cooker ');

Field name format omitted: All field values (including primary keys) must be given)
Format:
Insert into table name values (values of all fields );
Insert into table name values (values of all fields), (values of all fields ),..;
Example: insert into category values (7, 'refrigerator ');
Insert into category values (8, 'laptop '), ('desktops ');


If you cannot remember the primary key when adding data, you can use null. SQL automatically calculates the primary key.
Example: insert into category values (null, 'xiaomi 6 ');

Update table data, use the keyword update (update, modify) set (set)
Format:
Modify all data in the column at a time without filtering conditions.
Update table name set field name = field value, field name = field value ,...;
Use the where keyword for conditional filtering.
Update table name set field name = field value, field name = field value,... where filter condition;
Low condition filtering (use with caution)
For example, update category set cname = 'modify all ';
Use the where keyword for conditional filtering.
Update category set cname = 'black and white TV set' where cid = 4;

Delete from
Format:
Delete from table name [where condition filtering];
The delete from table name deletes all data in the table, but does not delete the primary key auto-increment.
Truncate table name; deleting all data in the table will delete the primary key auto-increment, so that the primary key auto-increment is reset from 1
Delete from table name [where condition filtering];
Example: delete from category where cid = 4;
Delete from Table Name
Example: delete from category;
Insert data after deletion using delete, and the primary key will have a break number without the previous sequence number.
Insert into category (cname) values ('cell phone ');
Delete from category where cid = 12;
Insert into category (cid, cname) values (12, 'manually insert the specified Primary Key column ');

Truncate table name
Example: truncate table category

Primary key constraint
Use key primary key
Purpose:
The constraint primary key column cannot be null.
It cannot be repeated.
Each table must have one primary key and only one primary key.
The primary key cannot use business data.


The first way to add a primary key
Add directly after the column name
Create table persons (
Id_p int primary key,
LastName varchar (255 ),
FirstName varchar (255 ),
Address varchar (255 ),
City varchar (255)
);
Insert into persons (Id_p, LastName) values (1, 'zhang ');
Insert into persons (Id_p, LastName) values (null, 'zhang'); -- not empty
Insert into persons (Id_p, LastName) values (1, 'zhang'); -- duplicate

The second method for adding a primary key
Use the constraint Region
Format:
[Constraint name] primary key (Field List)
Create table persons (
Id_P INT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255 ),
CONSTRAINT pk_id_p primary key (Id_P)
);
If the name of the primary key is not given, the keyword constraint can be omitted.
Create table persons (
Id_P INT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255 ),
Primary key (Id_P)
);

Method 3 for adding a primary key
After creating a table, modify the table structure and add the first primary key.
Alter table name add [constraint name] primary key (Field List );
Create table persons (
Id_P INT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255)
);
Alter table persons add primary key (Id_P );

Delete primary key
Alter table persons drop primary key;

Joint primary key
Use more than two fields as the primary key
Create table persons (
Id_P INT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255 ),
Primary key (LastName, FirstName)
);

Non-empty Constraint
Use the keyword not null
Purpose: force a column to be null (null value is not accepted)

The first format for creating a non-empty constraint. The expression is created, and then the field is directly given.
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255) NOT NULL
);
Add data
Insert into persons (lastname, city) VALUES ('zhang', 'xiongxian ');
Insert into persons (lastname, city) VALUES ('lil', 'null ');
Insert into persons (lastname, city) VALUES ('King ','');
Insert into persons (lastname, city) VALUES ('zhao ', NULL); -- Column 'city' cannot be null

Java is empty
String s = ""; s = "null" s = null; void

Method 2
Modify Table Structure
Alter table name modify column name type [length] [constraint];
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255)
);
Alter table persons MODIFY city VARCHAR (255) not null;

Delete non-empty Constraints
Alter table persons modify city varchar (255 );

 

Unique Constraint
Use the keyword unique
Purpose: add fields with unique constraints. They cannot be repeated.

The first format for creating a unique constraint. When creating a table, it is provided directly after the field
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255) UNIQUE
);
Insert into persons (lastname, city) VALUES ('zhang ', 'mauritius ');
-- Duplicate entry 'mauritius 'for key 'city'
Insert into persons (lastname, city) VALUES ('King', 'mauritius ');

The second format for creating unique constraints. When creating a table, use [constraint name] unique (Field List)
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255 ),
Constraint unique (City)
);

The third format for creating unique constraints. After creating a table, modify the table data.
Alter table name modify column name type [length] [constraint];
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255)
);
Alter table persons MODIFY city VARCHAR (255) UNIQUE;
Alter table name add [constraint name] unique (Field List)
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255)
);
Alter table persons add unique (City );

Delete unique constraint
Alert table persons drop index name
When defining constraints, if no name is created, it is called a string
Alter table persons drop index city;

Default Constraint
Add a default value to the field. If the field does not have a inserted value, use the default value.
Use the default keyword Value
Create default Constraint Method 1: Create a table. The column data type is followed by default 'default'
Create table persons (
Id_P int primary key AUTO_INCREMENT,
LastName VARCHAR (255 ),
FirstName VARCHAR (255 ),
Address VARCHAR (255 ),
City VARCHAR (255) DEFAULT 'China'
);
Insert into persons (lastname) VALUES ('zhang ');
Insert into persons (lastname, city) VALUES ('zhang ', 'Canada ');