The sixth lesson of MySQl database ——– the continuation of the SQl command —— Come and see

Foreword by the author

Welcome little cuties to come and learn from my gtiee Qin Boss (qin-laoda) – Gitee.com

———————————

SQl statement

Database Operations

Data table operations

SQL additions and deletions

——————————

Interstitial knowledge

1. The primary key is unique, there is only one primary key, and multiple primary keys are nominally a joint primary key, but the value in the primary key is unique and not repeated

2. Combined primary key: multiple fields together as the primary key of a table

3. A table has only one primary key

The author’s small nonsense

In the previous blog, I briefly introduced the simple commands of the database show databases; , use library name;, select database ();, create database library name;, drop database library name; show tables;, desc table name;, select * from table name; I will repeat this blog again, so that everyone can review it again

SQl statement

Structured Query Language, a language used to manipulate
RDBMS
database language. in e.g.
oracle
In addition to individual grammatical differences, current relational databases support the use of SQL
language to operate (
SQL
statement is not case sensitive).

SQL
Statements are mainly divided into:

DQL
: data query language, used to query data

DML
: Data manipulation language, add, modify, delete data

Dedicated to Yixue Classroom (by speed dog)
TPL
: transaction processing language, processing transactions

DDL
: Data definition language, database, table management, etc.

DCL
: Data control language for authorization and permission recycling

CCL
: Pointer control language, through the control pointer to complete the operation of the table

For a programmer, the addition, deletion, modification and query of the database must be mastered. that is
insert
,
delete
,
update
,
select

Operation database

-- view all databases
show databases;

-- create database
CREATE DATABASE library name;
CREATE DATABASE library name charset=utf8;

-- delete the database
drop database library name;

-- select database
use library name;

-- View the currently used database
select database();

Now that we know how to easily operate the database, we need to know that there are data tables in the database, and the operation table is a must for our programmers. I will introduce the commands of the operation table in detail below

Data Table Operations

—
View all tables in the current database

show tables;

— View table structure

desc table name;

Let me briefly explain some of this table structure

There are three fields here (that is, the header)

This represents the data type of the field

This represents whether the field can be left blank. If it is No, the field must have a written value when you write data.

represents the primary key

Represents the default value, if you don’t fill it in, it will be filled in automatically

This will also affect your writing data mainly depends on the situation

Seeing this, there may be some cuties saying what the primary key is, what is the primary key,

The primary key is to uniquely identify a record, there can be no duplication, and it is not allowed to be empty

The primary key is used to maintain data integrity

There is only one primary key

Determine the uniqueness of your data

Simply put, the full name of primary key is “primary key constraint”, which is a combination of one or more columns whose value can uniquely identify each row in the table, through which it can Entity integrity of the table; primary key is used to determine the uniqueness of the data

— create table

lowercase
create table `test` (
`id` int unsigned auto_increment comment 'number',
`title` varchar(100) not null comment 'Title',
`auther` varchar(40) not null comment 'Author',
`cdate` date comment 'date',
primary key ( `id` )
)engine=InnoDB default charset=utf8 comment = 'test form';


capital

CREATE TABLE `test`(
`id` INT UNSIGNED AUTO_INCREMENT COMMENT 'number',
`title` VARCHAR(100) NOT NULL COMMENT 'title',
`author` VARCHAR(40) NOT NULL COMMENT 'author',
`cdate` DATE COMMENT 'date',
-- foreign key(id) references goods(id),
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test form';






--Or you can directly design the primary key
CREATE TABLE IF NOT EXISTS `test_tb`(
`id` INT UNSIGNED AUTO_INCREMENT primary key not null,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`cdate` DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you don’t want the fields to be
NULL,
The properties of the field can be set as
NOT NULL

— AUTO_INCREMENT
Define the column as an auto-increment attribute, generally used for the primary key, and the value will be automatically increased
1

— COMMENT
Comments on fields

—
create foreign keys,
test_tb
middle
id
The type must be the same as the target table (
goods
table) in
id
consistent (can have no foreign keys)

—
Remove foreign keys:
alter table test_tb drop foreign key
foreign key name

— PRIMARY KEY
The keyword is used to define the column as the primary key. You can use multiple columns to define the primary key, separated by commas (`id1`,`id2`)

— ENGINE
set the storage engine,
CHARSET
Set encoding.

Look at the table creation statement (if we forget how to create a table, we can look at this)

show create table table name;

Modify table structure

New field (new header)

alter: change

alter table table name add `field name` type ....;

— Modify the field (used when modifying the field name)

change: change

alter table table name change `field original name` `field new name` type and condition;

Note, the data type: plastic can be converted into a string, but the string can not be converted into a plastic. This method must be renamed to be used normally.

Modify the field type (used without changing the name)

alter table table name modify column name type and constraints;

modify: modify

This method is also to change all the conditions, not just one condition

— delete table

drop table table name;

Here’s a demo

SQLAddition, deletion, modification and query

Write data

··Writing 1
insert into table name(
    field name
)
value(
    'content'
);

··Writing 2

insert into table name(
    `field name`
)
value(
    'content'
);

If the id (that is, the primary key) is written, the data will be inserted in order

Deletion of data

delete from table name where condition;

Summary:

Time flies, and the content of this blog is written here. The next blog will be picked up here to continue the introduction, if you don’t understand, you can chat with me privately;

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge MySQL entry skill tree SQL advanced skillsCTE and recursive query 62574 people are studying systematically

Foreword by the author

Table of Contents

SQl statement

Database Operations

Data table operations

SQL additions and deletions

——————————

Interstitial knowledge

1. The primary key is unique, there is only one primary key, and multiple primary keys are nominally a joint primary key, but the value in the primary key is unique and not repeated

2. Combined primary key: multiple fields together as the primary key of a table

The author’s small nonsense

SQl statement

Operation database

— create table

Modify table structure

This method is also to change all the conditions, not just one condition

— delete table

SQLAddition, deletion, modification and query

Write data

Deletion of data

Summary: