We can usually divide SQL into four kinds: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). DDL is mainly used to create, delete, and modify objects in the database, for example, creating, deleting, and modifying two-dimensional tables. Its core keywords include create, drop, and alter. DML is mainly responsible for inserting, deleting, updating, and querying data. Its keywords include insert, delete, update, and select. DCL is used to grant and take back permissions. Its core keywords are grant and revoke. TCL is usually used for transaction control.
Note: SQL is a language that does not distinguish uppercase and lowercase. In general, we suggest writing keywords in uppercase and the other parts in lowercase. If the company has mandatory SQL coding rules, then follow the company's rules. Personal preference should not be above the company's coding rules. This should be common sense for a professional.
Next, let us build a very simple database for a school course-selection system. We name the database school. The four key entities are colleges, teachers, students, and courses. Among them, students and colleges are in a subordinate relationship. In terms of quantity, this is a many-to-one relationship, because one college can have many students, and one student usually belongs to only one college. In the same way, the subordinate relationship between teachers and colleges is also many-to-one. One teacher can teach many courses. If one course has only one teacher, then the relationship between courses and teachers is also many-to-one. If multiple teachers are allowed to teach one course together, then courses and teachers are many-to-many. To keep it simple, we design courses and teachers as many-to-one. Students and courses are a typical many-to-many relationship, because one student can choose many courses, and one course can also be chosen by many students. In a relational database, a middle table is needed to keep the many-to-many relationship between two entities. In the end, our school course-selection system has five tables in total: college table (tb_college), student table (tb_student), teacher table (tb_teacher), course table (tb_course), and course-selection record table (tb_record). The course-selection record table is the middle table that keeps the many-to-many relationship between students and courses.
-- If there is a database named school, drop it
DROP DATABASE IF EXISTS `school`;
-- Create a database named school and set the default character set and collation
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- Switch to the school database context
USE `school`;
-- Create the college table
CREATE TABLE `tb_college`
(
`col_id` int unsigned AUTO_INCREMENT COMMENT 'ID',
`col_name` varchar(50) NOT NULL COMMENT 'Name',
`col_intro` varchar(500) NOT NULL DEFAULT '' COMMENT 'Introduction',
PRIMARY KEY (`col_id`)
);
-- Create the student table
CREATE TABLE `tb_student`
(
`stu_id` int unsigned NOT NULL COMMENT 'Student ID',
`stu_name` varchar(20) NOT NULL COMMENT 'Name',
`stu_sex` boolean NOT NULL DEFAULT 1 COMMENT 'Sex',
`stu_birth` date NOT NULL COMMENT 'Date of birth',
`stu_addr` varchar(255) DEFAULT '' COMMENT 'Native place',
`col_id` int unsigned NOT NULL COMMENT 'College',
PRIMARY KEY (`stu_id`),
CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);
-- Create the teacher table
CREATE TABLE `tb_teacher`
(
`tea_id` int unsigned NOT NULL COMMENT 'Employee ID',
`tea_name` varchar(20) NOT NULL COMMENT 'Name',
`tea_title` varchar(10) NOT NULL DEFAULT 'Teaching Assistant' COMMENT 'Title',
`col_id` int unsigned NOT NULL COMMENT 'College',
PRIMARY KEY (`tea_id`),
CONSTRAINT `fk_teacher_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);
-- Create the course table
CREATE TABLE `tb_course`
(
`cou_id` int unsigned NOT NULL COMMENT 'ID',
`cou_name` varchar(50) NOT NULL COMMENT 'Name',
`cou_credit` int NOT NULL COMMENT 'Credits',
`tea_id` int unsigned NOT NULL COMMENT 'Teacher',
PRIMARY KEY (`cou_id`),
CONSTRAINT `fk_course_tea_id` FOREIGN KEY (`tea_id`) REFERENCES `tb_teacher` (`tea_id`)
);
-- Create the course selection record table
CREATE TABLE `tb_record`
(
`rec_id` bigint unsigned AUTO_INCREMENT COMMENT 'Course selection record ID',
`stu_id` int unsigned NOT NULL COMMENT 'Student ID',
`cou_id` int unsigned NOT NULL COMMENT 'Course ID',
`sel_date` date NOT NULL COMMENT 'Course selection date',
`score` decimal(4,1) COMMENT 'Exam score',
PRIMARY KEY (`rec_id`),
CONSTRAINT `fk_record_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `tb_student` (`stu_id`),
CONSTRAINT `fk_record_cou_id` FOREIGN KEY (`cou_id`) REFERENCES `tb_course` (`cou_id`),
CONSTRAINT `uk_record_stu_cou` UNIQUE (`stu_id`, `cou_id`)
);There are several places in the DDL above that need to be emphasized:
-
First, in the SQL above, the database names, table names, and field names are all wrapped in backticks (`). Backticks are not required, but they can solve the problem when table names, field names, and so on conflict with SQL keywords, which are words with special meaning in SQL.
-
When creating the database, we used
default character set utf8mb4to specify that the default character set of the database isutf8mb4(utf-8encoding with at most4bytes). We recommend using this character set. It is also the default character set used by MySQL 8.x, because it supports international encoding and can also store Emoji characters. You can use the command below to see the character sets supported by MySQL and the default collations.show character set;+----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)If you want to set the default character set used when the MySQL service starts, you can modify the MySQL configuration and add the following content.
[mysqld] character-set-server=utf8
Tip: If you do not know how to modify the MySQL configuration file, then leave it alone for now.
-
When creating and deleting databases, the keyword
databasecan also be replaced withschema. The two do the same thing. -
In the table creation statement,
not nullis a non-null constraint, which limits the field so it cannot be empty.defaultis used to specify a default value for a field, and we call it a default-value constraint.primary keyis the primary-key constraint. It sets the column that can uniquely determine one record, and it also ensures that each record is unique, because the primary key does not allow duplicates.foreign keyis the foreign-key constraint. It keeps the referential integrity of two tables. For example, because thecol_idfield in the student table has a foreign-key constraint, it must reference (references)col_idin the college table. So the college ID in the student table must come from the college table and cannot be assigned to this field freely. If you need to name primary-key constraints, foreign-key constraints, and so on, you can use theconstraintkeyword and put the constraint name after it. -
The
commentkeyword in the table creation statement is used to add comments to columns and tables, which improves the readability and maintainability of the code. -
When creating a table, you can choose the underlying storage engine yourself. MySQL supports many storage engines, and you can see them with the
show enginescommand. In versions after MySQL 5.5, the default storage engine is InnoDB. This is the storage engine we recommend everyone use, because it is more suitable for the needs of today's internet applications in high concurrency, performance, and transaction support. For downward compatibility of SQL statements, we can specifyengine=innodbafter the right parenthesis at the end of thecreate tablestatement.show engines\G
Note: The
\Gabove is used to change to another output style. In the command-line client, if a table has many fields and one line cannot show them all, the output looks very uncomfortable. Using\Gcan output each column of a record on its own line. This output style looks much better in the command-line client.*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec)The table below makes a simple comparison of several commonly used MySQL storage engines.
Feature InnoDB MRG_MYISAM MEMORY MyISAM Storage limit Yes No Yes Yes Transaction Supported Locking mechanism Row lock Table lock Table lock Table lock B-tree index Supported Supported Supported Supported Hash index Supported Full-text search Supported (5.6+) Supported Clustered index Supported Data cache Supported Supported Index cache Supported Supported Supported Supported Data compression Supported Memory usage High Low Medium Low Storage space usage High Low Low Bulk insert performance Low High High High Supports foreign keys Supported From the comparison above, we can know that InnoDB is the only storage engine that supports foreign keys, transactions, and row locks, so as we said before, it is more suitable for internet applications. In newer versions of MySQL, it is also the default storage engine.
-
When choosing the data type for each field while defining the table structure, if you do not know which data type is more suitable, you can use MySQL's help system to understand the features of each data type, the length and precision of the data, and other related information.
? data types
Note: In MySQL Workbench, you cannot use
?to get help. You need to use the corresponding commandhelp.You asked for help about help category: "Data Types" For more information, type 'help <item>', where <item> is one of the following topics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPEGet help for the
varchartype:? varcharExecution result:
Name: 'VARCHAR' Description: [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. See http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html. MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. *Note*: MySQL follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values. VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set. http://dev.mysql.com/doc/refman/5.7/en/charset-national.html. NVARCHAR is shorthand for NATIONAL VARCHAR. URL: http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.htmlWhen choosing data types, to save string data, we usually use the
VARCHARandCHARtypes. The former is usually called a variable-length string, and the latter is usually called a fixed-length string. For the InnoDB storage engine, the row storage format does not distinguish between fixed-length and variable-length columns, so there is no essential difference betweenVARCHARandCHAR, and the latter is not necessarily faster than the former. If you want to save a very large string, you can use theTEXTtype. If you want to save a very large byte string, you can use theBLOB(Binary Large Object) type. In MySQL,TEXTandBLOBfurther includeTEXT,MEDIUMTEXT,LONGTEXTandBLOB,MEDIUMBLOB,LONGBLOB. Their main difference is the maximum size of data they can store. To save floating-point numbers, you can useFLOATorDOUBLE.FLOATis no longer recommended, and it may be removed in later MySQL versions. To save fixed-point numbers, you should use theDECIMALtype. It can specify the number of valid digits before and after the decimal point. If you want to save date and time, theDATETIMEtype is better than theTIMESTAMPtype, because the former can represent a larger range of date and time. The latter is actually an integer underneath, which records how many milliseconds the specified date and time differs from1970-01-01 00:00:00. This type will overflow after2038-01-19 03:14:07.For the auto-increment field
AUTO_INCREMENT, if you use MySQL 5.x, you need to pay attention to the rollback problem of auto-increment fields. Of course, this problem has been well solved in MySQL 8.x. Also, MySQL 8.x has many other advantages. There are many optimizations and adjustments in both functions and performance, so we strongly recommend everyone use MySQL 8.x. In scenarios with high-concurrency access to the database,AUTO_INCREMENTnot only has performance problems, but may also produce duplicate ID values in a multi-machine structure. In this kind of scenario, using a distributed ID generation algorithm, such as SnowFlake or TinyID, is the best choice. Interested readers can study it by themselves.
Next, we will use the student table as an example to explain how to drop tables and modify tables. To drop a table, you can use drop table, as shown below.
DROP TABLE `tb_student`;or
DROP TABLE IF EXISTS `tb_student`;It should be noted that if data has already been entered into the student table and that data is referenced by other tables, then the student table cannot be dropped, otherwise the operation above will report an error. In the next lesson, we will talk about how to insert data into tables. At that time, you can try it and see whether you can delete the student table smoothly.
If you want to modify the student table, you can use alter table. Specifically, it can be divided into the following cases:
Modify the table and add a new column. For example, add a contact phone number column to the student table.
ALTER TABLE `tb_student` ADD COLUMN `stu_tel` varchar(20) NOT NULL COMMENT 'Contact phone number';Note: If you specify a non-null constraint (
not null) when adding a new column, then the student table cannot already have data. Otherwise, after the original data gets thestu_telcolumn, there is no data in that column, and this violates the non-null constraint. Of course, when we add a column, we can also use a default value constraint to solve this problem.
Modify the table and delete the specified column. For example, delete the contact phone number column added above.
ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;Modify the table and change the data type of a column. For example, change stu_sex in the student table to a character.
ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` char(1) NOT NULL DEFAULT 'M' COMMENT 'Sex';Modify the table and change the column name. For example, change stu_sex in the student table to stu_gender.
ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` boolean DEFAULT 1 COMMENT 'Sex';Modify the table and delete a constraint. For example, delete the foreign key constraint on the col_id column of the student table.
ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;Modify the table and add a constraint. For example, add a foreign key constraint to the col_id column of the student table.
ALTER TABLE `tb_student` ADD FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);or
ALTER TABLE `tb_student` ADD CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);Note: When adding a foreign key constraint, you can also use
on updateandon deleteto specify what should be done when the referenced table is updated or deleted. The default value of both isrestrict, which means if there is a foreign key constraint, the referenced data is not allowed to be updated or deleted. Besidesrestrict, the possible values here arecascadeandset null. Interested readers can study it by themselves.
Modify the table name. For example, change the name of the student table to tb_stu_info.
ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;Tip: In general, please do not easily change the name of a database or table.