MySQL basics--(2) [SQL (Structured Query Language), DDL (Data Definition Language)]

table of Contents


Sql (Structured Query Language)


The distinction between several concepts:

sql: structured query language
MySQL: is the name of the database software
SQLyog: a visualization tool

About SQL syntax and notes

SQL syntax

  • It is not case sensitive. It is recommended that keywords be capitalized, and table names and column names are lowercase.
  • Use every sentence; end

Annotation

Single-line comment: #note text

Single line comment: - comment text (there must be spaces)

Multi-line comment: /* comment text*/


In the visualization tool, after writing the file, you can directly save it to any location with Ctrl+S; in fact, it can be opened as a text file with Notepad


The concept and characteristics of sql


Structured Query Language (Structured Query Language), referred to as SQL, is a special purpose programming language, a database query and programming language, used to access data and query, update and manage relational database systems.

Benefits of SQL :

Not a proprietary language of a particular database vendor, almost all relational databases (RDBMS) support SQL

In fact, a powerful language, flexible use can carry out very complex and advanced database operations

Classification
According to function; SQL language is divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, data control language DCL.


DDL (Data Definition Language)

DDL (Data Definition Language) data definition language; the table structure of the database used to create and modify data.

Commonly used statements create, alter, drop, rename.


Create, delete, modify databases.


Create database

Note: After the name of the database is defined, it cannot be changed anymore

CREATE DATABASE [if not exists] database name
(if not exists here is just to determine whether there is a library to be created; you can omit it)


You can also set character encoding when creating a database; for example, utf8;
CREATE DATABASE [if not exists] database name [CHARSET utf8]

It is recommended to add _db after the database name when creating a database(Represents datebase)

-- 创建数据库;
#这里可以直接选中语句执行查询语句;然后点击连接;再刷新对象浏览器;
#这里加入 if not exits 判断要创建的库是否存在;
create database if not exists studentmessage_db;

Delete database

DROP DATABASE database name/[IF EXISTS database name];
IF EXISTS here determines whether the database exists; it can also be omitted;

Modify the character encoding of the database

ALTER DATABASE database name CHARSET gbk;

The characteristics of database storage data

Put the data in the table, and then put the table in the library

There can be multiple tables in a database, and each table has a name to identify itself. The table name is unique.

The table has some characteristics, these characteristics define how the data is stored in the table, similar to the design of the "class" in Java.

The table is composed of columns, which we also call columns as fields. All tables are composed of one or more columns, each column is similar to "attributes" in java

The data in the table is stored in rows, and each row is similar to an "object" in Java.


Database Table


1. Data table
Table (table) is the most common and simple form of data storage, and it is the basic element that constitutes a relational database. The simplest form of a table is composed of rows and columns, each containing data. Each table has a header and a body. The header defines the table name and column names. The rows in the table are regarded as records in the file, and the columns in the table are regarded as the fields of these records.在一个数据库中,表名不能重复.

2. Record ( 表的行数据)
Record is also called a row of data, which is a row in the table. In a relational database table, a row of data refers to a complete record.

3. Field ( 表的列数据) A
field is a column in the table, which is used to store the specific information of each record. One column of the data table contains all the information of a specific field.


Designing a table
When designing a table, you must first determine some of the components of the
table ; the table name (stored information, a type of information is stored in a table); the fields (columns) in the table; the data type and data length; what constraints are needed; (Cannot be empty/primary key, foreign key);


The data type of the data in the database table


Fixed-length character string and variable fixed-length character string

char(n) a fixed-length character string of length n 这是固定长度的;;
varchar(n) A variable-length character string with a maximum length of n
这是固定一个最大长度,里面可以写入低于这个长度的; the maximum value of n is 65535

Date and time

date Date; the format is: year, month, day,
datetime; the format is: year, month, day, hour, minute, and second

Integer type

Types ofbyteMinimumMax
TINYINT1-128127
SMALLINT2-3276832767
MEDIUMINT3-83886088388607
INT4-21474836482147483647
BIGINT8-92233720368547758089223372036854775807

Floating point type

Types ofDescription
float(m,d)Single-precision floating point 8-bit precision (4 bytes) m total number, d decimal place
double(m,d)Double-precision floating point 16-bit precision (8 bytes) m total number, d decimal place

数据类型(M,D)

Types ofmeaning
MAccuracy, the total length of the data;
DScale, length after decimal point

For example, use double(8,3) as the data type of a certain data attribute; that is, when storing the data, the total length of the data is 8, and only 3 digits are retained after the decimal point.


BLOB is a binary large object that can hold a variable amount of data for storing picture and video information. In a database management system, binary data is stored as a collection of single individuals. The Blob object represents an immutable, original data file-like object.

Types ofThe maximum length
TINYBLOBMaximum length 255 characters (2^8-1)
BLOBMaximum length 65535 (2^16-1)
MEDIUMBLOBMaximum length 16777215 (2^24-1)
LONGBLOBMaximum length 4294967295 (2^32-1)

TEXT string

Types ofThe maximum length
TINYTEXTMaximum length 255 characters (2^8-1)
TEXTMaximum length 65535 (2^16-1)
MEDIUMTEXTMaximum length 16777215 (2^24-1)
LONGTEXTMaximum length 4294967295 (2^32-1)

Constraints on data in database tables


Constraints
Constraints are used to limit the type of data added to the table.
The constraints can be specified when the table is created (via the CREATE TABLE statement), or after the table is created (via the ALTER TABLE statement)


Primary key
There are several attributes in a record in a relational database. If a column can uniquely identify a record, the column is a primary key.不能为空值,不能重复

The primary key is a unique identifier that can determine a record, the primary key is used to maintain data integrity, there is only one primary key

Foreign key A
foreign key is a column in a table that contains the primary key value of another table and defines the relationship between the two tables外键可以是空值,也可以重复

Foreign keys are used to establish connections with other tables; there can be multiple foreign keys

Several constraints

PRIMARY KEY (Set the primary key constraint)

NOT NULL (setting cannot be null constraint)

UNIQUE (Unique Constraint)

Check constraints CHECK (set constraint conditions 注意是:mysql8版本时)

Foreign key constraint FOREIGN KEY (set foreign key constraint)在多个表之间有关系时使用

The primary key automatically grows
AUTO_INCREMENT注意:mysql数据库的数据主键可以设置自动增长,数据类型只能是整数

The default value
DEFAULT default_value设置该列的数据默认值


Field comment
comment'comment'


Create table


CREATE TABLE table name (column name data type [constraint] [default value] [comment],...)

Put a table in the database just now:

It is recommended to add t_ when creating a table(table) represents a table

#创建学生表;
  #列:  学号;姓名,性别,生日,年级,成绩,手机号,注册时间,
  #添加数据类型:
  #设置约束:
CREATE TABLE t_student(
         #学号:int类型,设置主键;
         id INT PRIMARY KEY COMMENT '主键,学号',
         #姓名:最大长度为5的可定长字符串,设置不能为空约束,
         name VARCHAR(5) NOT NULL COMMENT '姓名',
         #性别:长度为1的定长字符串,设置默认值为女;
         sex  CHAR(1)DEFAULT '女' COMMENT '性别' ,
         #生日:日期类型;
         birthday DATE COMMENT '生日',
         #年级:长度为3的定长字符串,
         grade CHAR(3) COMMENT '年级',
         #成绩:double类型,数值总长度为3,保留小数后1位,且成绩在0-120之间;
         score DOUBLE(3,1) CHECK(score>=0 AND score<=120) COMMENT '成绩',
         #手机号:长度为11位定长字符串,设置唯一约束;
         phone CHAR(11) UNIQUE COMMENT '手机号',
         #注册时间:时间类型,
         registertime DATETIME COMMENT '注册时间' 
)
Insert picture description here

The test input data knows:
Primary key: There can only be one primary key in a table, and it cannot be null and cannot be repeated;
Unique constraint: A table can have more than one, and the field can be empty, but it cannot be repeated;
Cannot be empty constraints: A table can have more than one, it can be repeated, but it cannot be empty.


Set up and modify the structure of the table


Add primary key constraint

ALTER TABLE table name ADD PRIMARY KEY (column name)

Delete the primary key constraint

ALTER TABLE table name DROP PRIMARY KEY

Set up automatic growth

ALTER TABLE table name MODIFY column name type AUTO_INCREMENT;

Delete automatic growth

ALTER TABLE table name MODIFY column name type;

Setting cannot be empty

ALTER TABLE table name MODIFY column name type NOT NULL;

Setting can be empty

ALTER TABLE table name MODIFY column name type NULL;

Add unique constraint

ALTER TABLE table name ADD CONSTRAINT constraint name UNIQUE (column name)

Delete unique constraint

ALTER TABLE table name DROP INDEX constraint name;

Add check constraints

ALTER TABLE table name ADD CONSTRAINT constraint name CHECK (condition)

Delete check constraint

ALTER TABLE table name DROP CHECK constraint name;

Add column

ALTER TABLE table name ADD column name data type
ALTER TABLE table name ADD column name data type FIRST 将指定列放在第一列
ALTER TABLE table name ADD column name data type AFTER column name将指定列放在指定的某个列后面

Delete column

ALTER TABLE table name DROP column name

Modify column name

ALTER TABLE table name CHANGE old column name new column name data type

Modify the data type of the column

ALTER TABLE table name MODIFY column name new data type

In front of the modification table areALTER TABLE table name;
Used when deletingDROP, There is no need to indicate the data type afterwards; add withADD;Modify most of the useMODIFY


Delete table


DROP TABLE [if exists] Table name
where if exists determines whether the table exists.

Modify table name


RENAME TABLE old table name TO new table name
or: ALTER TABLE old table name RENAME new table name;

Copy table structure


Just copy the structure of the table (column names, data types, constraints...); not copy the data of the table

CREATE TABLE new table name LIKE is copied table name;