MySQL Command collation

/* "Data management-additions and deletions" */
Use test;
DROP TABLE Demo;
--pay particular attention to inserting primary table data when primary foreign key relationships are involved
--Main Table: Table with reference Data columns (foundation)
--child table: Data has tables that are referenced to other tables
--PRIMARY key: A column that uniquely identifies a row of data in a table is called a primary key column
--FOREIGN key: This column of data originates from a different column (it can be a unique, non-empty column of its own table or other table)
--Foreign key column values can be duplicated, primary key column values are not allowed to repeat
SHOW TABLES;
SELECT * from student;
SELECT * from course;
SELECT * from score;

/* Error code: 1452
Cannot add or update a child row:
A FOREIGN KEY constraint fails (' Test '. ' Score ',
CONSTRAINT ' Fk_sc_stuid ' FOREIGN KEY (' stuid ') REFERENCES ' student ' (' Sid ')) */\
INSERT into student VALUES (1, ' no Notes ');
/* Error code: 1452
Cannot add or update a child row:
A FOREIGN KEY constraint fails (' Test '. ' Score ',
CONSTRAINT ' Fk_sc_ctuid ' FOREIGN KEY (' cid ') REFERENCES ' Course ' (' CID ') */
INSERT into Course (cid,cname) VALUES (1, ' javase ');
INSERT into score VALUES (1,1,100);

--BULK INSERT multiple data
INSERT into Student VALUES (2, ' Glow '), (3, ' Sprin '),
(4, ' Ink White '), (5, ' Finsk ');
SELECT * from student;

--Duplicate table structure (plus a condition that will never be established)
CREATE TABLE student_copy as SELECT * from student WHERE 1<>1;
SELECT * from Student_copy;
INSERT into Student_copy SELECT * from student;
SELECT * from Student_copy;
/* Error code: 1146
Table ' test.student_bk ' doesn ' t exist*/
INSERT into STUDENT_BK SELECT * from student;

drop table IF EXISTS demo;/* Delete Tables */
CREATE TABLE Demo (
ID INT PRIMARY KEY auto_increment,--Auto-grow, default starting value is 1
Dname VARCHAR (5) Not NULL UNIQUE,
Sex CHAR (1) DEFAULT ' M '
);

SHOW TABLES;
DESC demo;
SHOW CREATE TABLE Demo;
--Forget to set the character encoding when installing the database
SET NAMES UTF8;

--Insert data, note the length of char and varchar stored characters
INSERT into demo VALUES (1, ' Springer ', ' M ');
/* error code: 1406 dname Field inserted value is too large
Data too long for column ' Dname ' at row 1*/
INSERT into demo VALUES (1, ' Sprin ', ' M ');
SELECT * from demo;
INSERT into demo VALUES (1, ' Sprin ', ' Immortals ');
INSERT into demo VALUES (1, ' Sprin ', ' God ');
/* ERROR code: 1062 primary Key repeat
Duplicate entry ' 1 ' for key ' PRIMARY ' */
INSERT into Demo VALUES (2, ' Sprin ', ' God ');
/* ERROR code: 1062 unique UNIQUE constraint works, dname column does not allow duplicates
Duplicate entry ' Sprin ' for key ' dname ' * *
SELECT * from demo;
/* ERROR code: 1048 dname column is not allowed to be empty
Column ' dname ' cannot be null*/
INSERT into demo VALUES (3,null, ' God ');

INSERT into demo (id,dname) VALUES (4, ' laughter of mortals ');
SELECT * from demo;

/* ERROR code: 1136 Number of columns does not match the number of values
Column count doesn ' t match value count at row 1*/
INSERT into demo VALUES (NULL, ' sky ', ' F ');
INSERT into demo VALUES (NULL, ' Glow ', ' M ');
SELECT * from demo;

--primary key changes the starting value of autogrow starting from 17001 (note that auto-grow only works on numeric columns)
ALTER TABLE demo auto_increment=17001;
INSERT into demo VALUES (NULL, ' Mr. Bearded ', ' M ');
SELECT * from demo;

DROP TABLE IF EXISTS demo;
CREATE TABLE Demo (
ID INT PRIMARY KEY auto_increment,--Auto-grow, default starting value is 1
Dname VARCHAR (5) Not NULL UNIQUE,
Sex CHAR (1) DEFAULT ' m ' CHECK (Sex in (' m ', ' F '))
);
-NOTE: Check check constraints do not work in the current MySQL version, only in SQL Server
--if you want to implement check constraints in MySQL, use enum, or use triggers
INSERT into demo VALUES (NULL, ' Finsk ', ' Ape ');
SELECT * from demo;

DROP TABLE IF EXISTS demo;
CREATE TABLE Demo (
ID INT PRIMARY KEY auto_increment,--Auto-grow, default starting value is 1
Dname VARCHAR (5) Not NULL UNIQUE,
Sex ENUM (' M ', ' F ')
);
/* Error code: 1265
Data truncated for column ' sex ' at row 1*/
INSERT into demo VALUES (NULL, ' Finsk ', ' Y ');
INSERT into demo VALUES (NULL, ' Finsk ', ' M ');
SELECT * from demo;

--Supplement: about absolute references and relative references. As Chinese
Use mysql;--switch database to MySQL
/* ERROR code: The demo table in the 1146mysql database does not exist
Table ' Mysql.demo ' doesn ' t exist*/
SELECT * from demo;--relative references
SELECT * from Test.demo;

In enterprise development, it is often necessary to modify the data type of the field of the original table
Alternatively, increase the deletion of a column in a table, or increase the deletion of a constraint
*/
/* Add and delete of "1" column, Modification of column data type, column name modification
ALTER TABLE name the data type of the Add column list;--Append at the end of the last column
ALTER TABLE name the data type of the Add column list after column name 2;--adds a column after column name 2
ALTER TABLE name the data type of the Add column list first;--insert a column before the first column
ALTER TABLE name drop column name;
ALTER TABLE name drop column name 1, column name 2,......;
ALTER TABLE name modify column name new data type;--Modify column name
ALTER TABLE name change column name new column name new data type;--Modify column name and data type
*/
DROP TABLE IF EXISTS student;
CREATE TABLE Student (
Sid INT PRIMARY KEY auto_increment,
Sname VARCHAR (3) not NULL UNIQUE
);
--"1" Add column
ALTER TABLE student ADD sex CHAR (1) not NULL;
ALTER TABLE Student ADD age INT after sname;
ALTER TABLE student ADD Classno VARCHAR (8) first;
SELECT * from student;
--"2" modifies the data type of the column name or column
ALTER TABLE student MODIFY sname CHAR (30);
DESC student;
ALTER TABLE Student Change sname stuname VARCHAR (30);
DESC student;
--"3" Delete column
ALTER TABLE student DROP sex;
DESC student;
ALTER TABLE student DROP classno,drop age;
DESC student;

/* Add and delete of "2" constraint
ALTER TABLE table name ADD constraint constraint name constraint syntax;
"PRIMARY KEY constraint"
ALTER TABLE SCORE add constraint primary key PK_SC (SCID);
"FOREIGN KEY constraint"
ALTER TABLE SCOCE add constraint Fk_sc_st foreign key (Stid) references student (SID);
"Default value Constraint"
ALTER TABLE student ALTER CLASSNO set default ' 20171101 ';
*/
CREATE TABLE Course (--Curriculum
CID INT,
CNAME VARCHAR (30)
);
CREATE TABLE Score (
Stuid INT,
CID INT,
Result NUMERIC (4,1)
);
SHOW TABLES;
--View the primary foreign key relationship between tables, menu "Start"-"new schema Designer", drag the corresponding table into the
--"1" Add and remove primary KEY constraints
----you do not need to specify a column name for the primary key column, because for the same table, there is only one set of primary key columns
ALTER TABLE Course ADD CONSTRAINT PRIMARY KEY (CID);
ALTER TABLE Course Drop PRIMARY key;--Delete primary key
--"2" Add and remove foreign KEY constraints
ALTER TABLE score ADD CONSTRAINT fk_sc_stuid FOREIGN KEY (stuid) REFERENCES student (SID);
ALTER TABLE score DROP FOREIGN KEY fk_sc_stuid;
ALTER TABLE score ADD CONSTRAINT fk_sc_ctuid FOREIGN KEY (CID) REFERENCES course (CID);
ALTER TABLE score DROP FOREIGN KEY fk_sc_ctuid;
--"3" Default value modification
Alter TABLE score alter result SET DEFAULT 0;

SHOW CREATE TABLE Course;
DESC course;
DESC score;--MUL multiple many to 1

MySQL Command collation