table of Contents
- Sql (Structured Query Language)
- The concept and characteristics of sql
- DDL (Data Definition Language)
- Create, delete, modify databases.
- The characteristics of database storage data
- Database Table
- The data type of the data in the database table
- Constraints on data in database tables
- Create table
- Set up and modify the structure of the table
- Delete table
- Modify table name
- Copy table structure
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
- It is not case sensitive. It is recommended that keywords be capitalized, and table names and column names are lowercase.
- Use every sentence; end
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
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.
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;
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.
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 (
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
Floating point type
|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||Accuracy, the total length of the data;|
|D||Scale, 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 of||The maximum length|
|TINYBLOB||Maximum length 255 characters (2^8-1)|
|BLOB||Maximum length 65535 (2^16-1)|
|MEDIUMBLOB||Maximum length 16777215 (2^24-1)|
|LONGBLOB||Maximum length 4294967295 (2^32-1)|
|Types of||The maximum length|
|TINYTEXT||Maximum length 255 characters (2^8-1)|
|TEXT||Maximum length 65535 (2^16-1)|
|MEDIUMTEXT||Maximum length 16777215 (2^24-1)|
|LONGTEXT||Maximum length 4294967295 (2^32-1)|
Constraints on data in database tables
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)
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
PRIMARY KEY (Set the primary key constraint)
NOT NULL (setting cannot be null constraint)
UNIQUE (Unique Constraint)
Check constraints CHECK (set constraint conditions
Foreign key constraint FOREIGN KEY (set foreign key constraint)
The primary key automatically grows
The default value
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 '注册时间' )
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;
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
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
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;