1. Constraints
1.1 PrimaryKey(PK): primary key
Features: unique + non empty, a table can only have one primary key constraint It is usually a numeric column Better be meaningless
1.2 NOT NULL
Features: cannot be empty. We recommend that the business key column (index column) be set as non empty as possible
1.3 UNIQUE constraint
Features: no duplicate values are allowed It's like mobile phone number, ID number, qq, email box.
1.4 unsigned numeric columns are unsigned
Features: must be loaded after the number column, indicating that the number has no negative number Generally applicable to age
2. Other attributes
2.1 AUTO_INCREMENT self growth
Features: applicable to ID primary key column
2.2 DEFAULT default
Features: it is used in the NOT NULL column. When the value is not filled in, the default value is automatically generated.
2.3 COMMENT
Features: it is recommended that each column have a comment
Case: 10 tables: --- > add notes to each column of each table
3. DCL data control language (omitted)
grant
revoke
4. DDL application
4.1 Library
4.1.1 add
CREATE DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;
4.1.2 delete (dangerous, does not represent production operation.)
DROP DATABASE oldguo;
4.1.3 modification
ALTER DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;
4.1.4 check (DQL)
mysql> show databases; mysql> show create database oldboy;
4.1.5 specifications
(1) The library name should be related to the business
(2) The library name cannot have uppercase letters, but can have lowercase characters, numbers and special symbols
CREATE DATABASE OLDGUO CHARSET utf8mb4 COLLATE utf8mb4_bin;
(3) The library name cannot start with a number
(4) The library name cannot be a reserved character
(5) The library name should not exceed 18 characters
(6) The character set must be set Try to be utf8mb4
(7) Revoke the DROP permission of all users
4.2 table definition
4.2.1 add
# student form: student
DROP TABLE student;
CREATE TABLE `student` (
`xid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Student number',
`xname` VARCHAR(64) COLLATE utf8mb4_bin NOT NULL COMMENT 'full name',
`xage` TINYINT(3) UNSIGNED NOT NULL DEFAULT '99' COMMENT 'Age',
`xsex` CHAR(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Gender',
`xtel` CHAR(14) COLLATE utf8mb4_bin NOT NULL COMMENT 'cell-phone number',
`xcard` CHAR(18) COLLATE utf8mb4_bin NOT NULL COMMENT 'ID number',
`xaddr` ENUM('Beijing','Shanghai','Shenzhen City','Shandong Province','Gansu Province','Hebei Province','Shanxi Province','Henan Province','Liaoning Province','Jilin Province','Heilongjiang Province','Inner Mongolia Autonomous Region','Xinjiang Uygur Autonomous Region','Sichuan Province','Shaanxi Province','Jiangsu Province','Fujian Province','Hubei province','Guangdong Province','Guangxi Province') COLLATE utf8mb4_bin NOT NULL DEFAULT 'Beijing' COMMENT 'region',
`xdate` DATETIME DEFAULT NULL COMMENT 'Admission time',
PRIMARY KEY (`xid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
INSERT INTO student(xid, xname , xage ,xsex , xtel , xcard ,xaddr ,xdate)
VALUES
(1 , 'Zhang San' , 11 , 'm' , '110' , '660', 'Beijing', '2019-01-01'),
(2 , 'Ma Liu' , 13 , 'm' , '111' , '661', 'Shanghai', '2019-01-01'),
(3 , 'Li Si' , 14 , 'm' , '112' , '662', 'Beijing', '2019-01-01'),
(4 , 'Wang Wu' , 17 , 'm' , '113' , '663', 'Shandong Province', '2019-01-01'),
(5 , 'Hammer' , 18 , 'f' , '114' , '664', 'Henan Province', '2019-01-01'),
(6 , 'Steel egg' , 13 , 'f' , '115' , '665', 'Hebei Province', '2019-01-01'),
(7 , 'Sun WuKong' , 19 , 'm' , '116' , '666', 'Shanxi Province', '2019-01-01'),
(8 , 'Zhu Bajie' , 21 , 'm' , '117' , '667', 'Hebei Province', '2019-01-01'),
(9 , 'Tang Monk' ,23 , 'm' , '118' , '668' , 'Jilin Province', '2019-01-01'),
(10, 'Monk Sha' ,31 , 'm' , '120' , '669' , 'Liaoning Province', '2019-01-01'),
(11, 'White dragon horse' ,26 , 'm' , '119' , '670' , 'Guangxi Province', '2019-01-01') ,
(12, 'Ox demon king' ,19 , 'm' , '121' , '671' , 'Sichuan Province', '2019-01-01'),
(13, 'zhang wuji' ,20 , 'm' , '122' , '672' , 'Fujian Province', '2019-01-01'),
(14, 'Zhao Min' ,28 , 'f' , '123' , '673' , 'Guangdong Province', '2019-01-01'),
(15, 'Guo Jing' ,29 , 'm' , '124' , '674' , 'Gansu Province', '2019-01-01'),
(16, 'Huang Rong' ,17 , 'f' , '125' , '675' , 'Shenzhen City', '2019-01-01'),
(17, 'little dragon maiden' ,22 , 'f' , '126' , '766' , 'Heilongjiang Province', '2019-01-01'),
(18, 'Guo Yang' ,33 , 'm' , '127' , '777' , 'Xinjiang Uygur Autonomous Region', '2019-01-01'),
(19, 'Ouyang Feng' ,25 , 'm' , '128' , '888' , 'Inner Mongolia Autonomous Region', '2019-01-01'),
(20, 'Little Shenyang' ,23 , 'm' , '129' , '999' , 'Shaanxi Province', '2019-01-01');
SELECT * FROM student;
# course schedule: course
DROP TABLE course;
CREATE TABLE course (
cid INT NOT NULL PRIMARY KEY COMMENT 'Course number',
cname VARCHAR(64) NOT NULL COMMENT 'Course name',
tid CHAR(5) NOT NULL COMMENT 'Lecturer name',
cprice INT NOT NULL COMMENT 'Course price'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO course(cid, cname , tid ,cprice )
VALUES
(1001, 'linux' ,'t0001' ,19800),
(1002, 'python' ,'t0002' ,21800),
(1003, 'golang' ,'t0003' ,16000),
(1004, 'DBA' ,'t0004' ,15000),
(1005, 'safe' ,'t0005' ,17800);
SELECT * FROM course;
# teacher table: teacher
DROP TABLE teacher;
CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT 'Teacher number',
tname VARCHAR(64) NOT NULL COMMENT 'Teacher name',
tage TINYINT NOT NULL DEFAULT 99 COMMENT 'Teacher age',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT 'Teacher gender',
tyear TINYINT NOT NULL DEFAULT 3 COMMENT 'Working years',
txl VARCHAR(64) NOT NULL DEFAULT 'undergraduate' COMMENT 'education',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT 'level:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO
teacher(tid,tname,tage,tsex,tyear,txl,tstar)
VALUES
('t0001', 'oldboy' ,48 ,'m', 20 ,'undergraduate', 10),
('t0002', 'oldxu' ,28 ,'m', 6 ,'undergraduate', 6),
('t0003', 'oldq' ,30 ,'m', 6 ,'undergraduate', 6),
('t0004', 'oldguo' ,32 ,'m', 10 ,'undergraduate', 6),
('t0005', 'oldzh' ,32 ,'m', 10 ,'undergraduate', 6);
# Transcript: score
DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT 'Student number',
cid INT NOT NULL COMMENT 'Course number',
score INT NOT NULL DEFAULT 0 COMMENT 'Course score',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT 'Are you absent:1 miss an exam,0 Not absent'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO score(xid , cid , score ,quekao)
VALUES
(1 ,1001 ,80 ,0),
(1 ,1002 ,70 ,0),
(2 ,1001 ,0 ,1),
(2 ,1003 ,90 ,0),
(3 ,1004 ,80 ,0),
(4 ,1004 ,100 ,0),
(5 ,1005 ,60 ,0),
(4 ,1005 ,30 ,0),
(5 ,1002 ,60 ,0),
(6 ,1002 ,45 ,0),
(7 ,1003 ,67 ,0),
(7 ,1004 ,98 ,0),
(8 ,1004 ,76 ,0),
(9 ,1001 ,80 ,0),
(10 ,1002 ,99 ,0),
(11 ,1003 ,40 ,0),
(11 ,1004 ,50 ,0),
(12 ,1005 ,0 ,1),
(12 ,1003 ,90 ,0),
(13 ,1001 ,30 ,0),
(14 ,1002 ,100 ,0),
(15 ,1003 ,60 ,0),
(14 ,1004 ,30 ,0),
(15 ,1005 ,60 ,0),
(16 ,1002 ,45 ,0),
(17 ,1003 ,67 ,0),
(17 ,1004 ,98 ,0),
(18 ,1004 ,76 ,0),
(19 ,1005 ,75 ,0),
(20 ,1002 ,68 ,0);
SELECT * FROM score;
Table building specification:
- Table name: cannot be capitalized, related to business, cannot start with a number, the length is controlled within 18 characters, and cannot have the same name as the keyword
- To set the storage engine type: INNODB, set the character set
- Make the list meaningful
- Appropriate, complete and brief data types (it will affect the performance of the index)
- Each table should have a primary key. I really don't know how to set it. I also need to find an irrelevant self growing column to set it as the primary key
- Try to have not null for each column (especially the columns to be indexed in the future)
- Each column should have annotation information
Small expansion:
Make backup
mysql> create table teacher_bak like teacher;
4.2.2 delete (danger! Careful operation!)
mysql> drop table teacher_bak; mysql> truncate table teacher;
Interview question: could you explain the difference between drop table truncate delete table?
- drop table: table structure + data (physical deletion)
- truncate table: data (clear data page)
- delete from table: clear data rows (delete row by row)
4.2.3 modification
(1) Add column
DESC xuesheng; ALTER TABLE xuesheng ADD xqq BIGINT NOT NULL UNIQUE COMMENT 'qq number'; ALTER TABLE xuesheng ADD wechat BIGINT NOT NULL UNIQUE COMMENT 'wechat number' AFTER xtel; ALTER TABLE xuesheng ADD mail BIGINT NOT NULL UNIQUE COMMENT 'mailbox' FIRST';
(2) Delete column
ALTER TABLE xuesheng DROP mail; ALTER TABLE xuesheng DROP xqq; ALTER TABLE xuesheng DROP wechat; ALTER TABLE xuesheng DROP mail;
(3) Modification
1\. Modify table name: ALTER TABLE xuesheng RENAME TO student;
Interview question: hundreds of millions of lines of data planning?
1\. Monthly filing form 2\. Move or delete useless history tables
Extension: Pt Archiver
- Modify the attribute information of a column
DESC student; ALTER TABLE student MODIFY xname VARCHAR(128) NOT NULL COMMENT 'full name';
- Modify column names and properties
ALTER TABLE student CHANGE xsex xgender CHAR(2) NOT NULL DEFAULT 'm' COMMENT 'Gender';
be careful:
To execute alter statements, you need to lock the table. At this time, you can only query, not modify
We suggest that alter statements should occur during periods when business is not busy If you have to operate online, it is recommended to use Pt OSC tool
4.2.4 check
mysql> show tables; +-----------------+ | Tables_in_neiku | +-----------------+ | course | | score | | student | | teacher | +-----------------+ 4 rows in set (0.00 sec) mysql> desc teacher; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | tid | char(5) | NO | PRI | NULL | | | tname | varchar(64) | NO | | NULL | | | tage | tinyint(4) | NO | | 99 | | | tsex | char(1) | NO | | m | | | tyear | tinyint(4) | NO | | 3 | | | txl | varchar(64) | NO | | undergraduate | | | tstar | tinyint(4) | NO | | 5 | | +-------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) show create table teacher; +---------+--------------------------------------------------------------------------------------------------- | teacher | CREATE TABLE `teacher` ( `tid` char(5) NOT NULL COMMENT 'Teacher number', `tname` varchar(64) NOT NULL COMMENT 'Teacher name', `tage` tinyint(4) NOT NULL DEFAULT '99' COMMENT 'Teacher age', `tsex` char(1) NOT NULL DEFAULT 'm' COMMENT 'Teacher gender', `tyear` tinyint(4) NOT NULL DEFAULT '3' COMMENT 'Working years', `txl` varchar(64) NOT NULL DEFAULT 'undergraduate' COMMENT 'education', `tstar` tinyint(4) NOT NULL DEFAULT '5' COMMENT 'level:1-10', PRIMARY KEY (`tid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
- DML data manipulation language
5.1 insert data
5.2 update modification
SELECT * FROM student; UPDATE student SET xname='Bastard' WHERE xid=6; UPDATE student SET xname='Li tiehammer' WHERE xid=5;
5.3 delete
INSERT INTO student VALUES(21,'Wang Er Ma Zi',22,'f','921','345','Shanghai','2020-01-01'); DELETE FROM student WHERE xid=21;
Extension: pseudo delete
(1) Add status column is_del (1 for deletion, 0 for validity)
ALTER TABLE student ADD is_del TINYINT NOT NULL DEFAULT 0 COMMENT '1 Representative deletion,0 Representative valid'; SELECT * FROM student;
(2) delete —> update
Original statement: delete from student where xid=20; Change to : update student set is_del=1 where xid=20;
(3) Change business query method
Original statement: SELECT * FROM student; Change to: SELECT * FROM student where is_del=0;