Tutorial on operating tables in mysql

Creation and deletion of database
Start the database service in a black window: net start mysql
Close the database service: net stop mysql

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

View all databases in mysql
show databases;

View the definition information of a database
show create database 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 database name Example: drop database test;

Create a table in the database.

Use the keyword create table
[] It means optional in the database. It can be present or not.
Format:
create table table name (
　Field name data type [length] [constraint],
　Field name data type [length] ] [Constraint],
……
Field name data type [length] [Constraint](The last one cannot have a comma)
);
Example: Create a product classification 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 Example: desc category;

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

Modify table to add columns
alter table table nameadd column name type [length] [constraint];
Example: alter table category add name int;

Modify table to modify column type length and constraints
alter table table name modify column name type [length] [constraint];
Note: If there is data, you must pay attention to the data type varchar--> it is easy to have wrong data
Example: alter table category modify description int;
alter table category modify description varchar(20) not null;

Modify column names, data types and constraints
alter tble table name drop column name;
Note: If there is data in the column, it will be deleted together, so be careful
Example: alter table category drop descr;

Modify table name
rename table table name to new table name
Example:rename table category to student;

Modify the character set of the table
alter table table name character set character set
Note: It is not recommended to execute as it may produce garbled characters
Example: alter table category character set gbk;

Insert data into the database table
Use the keyword insert [into]
Format:
Contains the primary key: insert into table name ( Field 1, field 2,....) values ​​(value 1, value 2,....);
The primary key is incremented, the primary key is omitted: insert into table name (excluding primary key) values ​​(excluding primary key) ;
Notes:
1. Fields and values ​​must correspond one-to-one (number, data type)
2. In addition to numerical types (int, double), other data Types need to be wrapped in quotes
You can use ''. You can also use "", it is recommended to use ''
Contains the 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" ; 100)
);
The primary key is automatically incremented, and the primary key is omitted: insert into table name (excluding primary key) values ​​(excluding primary key);
Example: insert into category (cname) values ​​("Color TV") ;




Batch insert data

Contains primary key: insert into table name (Field 1, Field 2,...) values ​​( Value 1, Value 2,...), (Value 1, Value 2,...), (Value 1, Value 2,...);

insert into category (cid,cname) values ​​(3,'air conditioner') ,(4,'Washing machine');insert into category (cname) values ​​('Microwave oven'),('Induction cooker');


Omit field name format: must be given Out the values ​​of all fields (including primary keys)

Format:

insert into table name values ​​(values ​​of all fields), (all fields value),..;
Example: insert into category values(7,'refrigerator'); insert into category values(8,'laptop'),('desktop');


When adding data, if you cannot remember the primary key, you can use null, and sql will automatically calculate the primary key

Example: insert into category values ​​(null,'Xiaomi 6');

Update table data, use the keyword update (update, modify) set (set)
Format:
Without conditional filtering, modify all the data in the column at one time
update Table name set field name = field value, field name = field value,...;
With conditional filtering, use the keyword where
update table name set field name = field value, field name = field value,. ..where filter condition;
No low condition filtering (use with caution)
Example: update category set cname='all modifications';
With conditional filtering, use the keyword where
update category set cname ='Black and White TV' where cid=4;

To delete table data, use the keyword delete from
Format:
delete from table name[where conditional filtering ];
delete from table name deletes all data in the table, but does not delete the primary key increment
truncate table table name; deletes all data in the table, deletes the primary key increment, and resets the primary key increment Starting from 1
delete from table name[where condition filtering];
Example:delete from category where cid=4;
delete from table name
Example:delete from category;
Use delete When data is inserted after deletion, the primary key will have a broken number and no previous serial number
insert into category (cname) values ​​('mobile phone');
delete from category where cid=12;
insert into category (cid ,cname) values(12,'Manually insert the specified primary key column');

truncate table table name
Example: truncate table category

Primary key constraint
Use the key primary key
Function:
The constraint primary key column cannot be null
cannot be repeated
Each table must have a primary key, and there can only be one primary key
Primary key Business data cannot be used


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');-- non-null
insert into persons(Id_p,LastName) values ​​(1,' Zhang');-- Repeat

The second way to add a primary key
Use the constraint area
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)
);
constraintIf 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)
);

The third way to add a primary key
After creating the table, modify the table structure, the first way to add a primary key
alter table 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-null constraint
Use the keyword not null
Function: Force the constraint that a certain column cannot be null (null values ​​are not accepted)

Create the first step of the non-null constraint A format that creates a representation and gives 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) NOT NULL
);
Add data
INSERT INTO persons(lastname,city) VALUES('Zhang','Xiongxian') ;
INSERT INTO persons(lastname,city) VALUES('李','null');
INSERT INTO persons(lastname,city) VALUES('王','');
INSERT INTO persons (lastname,city) VALUES('Zhao',NULL);-- Column 'City' cannot be null

java Four are empty
String s ="";s ="null" s=null; void

Create non-null constraint method two
Modify table structure
alter table 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-null constraints
alter table persons modify city varchar(255);

Unique constraint
Use the keyword unique
Function: The field with the unique constraint cannot be repeated

The first format to create a unique constraint, create a table When,
CREATE TABLE persons(
Id_P INT PRIMARY KEY AUTO_INCREMENT,
is given directly after the field) 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('王','Mauritius');

Create the second format of unique constraints and create 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)
);

Create the third format of unique constraints, after creating the table ,Modify table data
alter table 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 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 a constraint, if no name is created, the name is a string
alter table persons drop index city;

Default constraint
Add a default value to the field. If the field does not insert a value, use the default value
Use the keyword default value
Create default constraint method 1, create the table, and the column data type is followed by default 'default value '
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');

The above is the detailed content of Tutorial on operating tables in mysql. For more information, please follow other related articles on the PHP Chinese website!