Learning path (2)

I felt like I was doing well on the first day. I have read some knowledge about MySQL databases. I will continue tomorrow. I guess I can read the contents of the database chapter without knowing what I got, I carefully typed the SQL statement code and made some simple notes. Some SQL statements are directly executed without being attached ~~ Some simple knowledge points of the beginner database:

Some simple knowledge points of the beginner database:
The SQL statement ends with a comma (;). the keywords of the SQL statement are case-insensitive. The SQL identifier must start with a letter and can contain letters, numbers, and three special characters (#_$ ); modify a table using alter table. modifying a table includes adding column definitions, modifying column definitions, deleting columns, and renaming columns. The string value in the SQL statement is not caused by double quotation marks, but by single quotation marks. A primary key constraint is equivalent to a non-empty constraint and a unique constraint. That is, duplicate or null values are not allowed in the columns bound by the primary key. If a primary key constraint is set for multiple columns, each column in multiple columns cannot be empty, but only the combination of these columns cannot be repeated for one-to-many associations. A foreign key column is usually added at one end of multiple columns; for a one-to-one association, you can add a foreign key column in any way. A table that adds a foreign key column is called a slave table, you only need to add a unique constraint to the foreign key column to indicate one-to-one association. For many-to-many relationships, you need to add an additional connection table to record their associations. if you want to delete a master table record, the slave table record will also be deleted, you need to add on Delete cascade or on Delete set null after creating a foreign key constraint; the first is to delete all the records from the master table in cascade mode when deleting the records from the master table. The second is to specify that when deleting the records from the master table, setting the foreign key of the slave table record from the master table to null to create an index is the only function to accelerate the query of the table. The index uses the quick path access method to quickly locate the data. Reduces disk I/O. It cannot exist independently. It must belong to a table but be stored independently in the data dictionary. Most databases use the with check option to force the modification of View data, But Oracle uses the with read only option to force the modification of View data. Aliases can be used for columns. Old and new names are separated by spaces. Alternatively, SQL statements with the as keyword can use two wildcard underscores (_) and percent signs (% ", an underscore represents any character, and a percent sign can represent any number of characters.
 
 
--- Create table test (test_id int, test_price decimal, test_name varchar (255) default 'xxx', test_desc text, test_date datetime );
// Display all records of the table
select * from test ;
// Modify the table structure
Alter table testadd (aaa varchar (255) default 'xxx', bbb varchar (255); alter table testmodify test_id varchar (255); alter table testmodify bbb int; alter table test drop aaa; --- alter table test rename to wawa; -- create table definition constraint create table hehe (hehe_id int not null, hehe_name varchar (255) default 'xyz' not null, hehe_gender varchar (2) null); alter table hehemodify hehe_gender varchar (2) not null; Alter table hehemodify hehe_name varchar (2) null; alter table hehehemodify hehe_name varchar (255) default 'abc' null; // create table unique_test (test_id int not null, test_naem varchar (255) unique); // create a unique constraint when creating a table, use the table-level constraint syntax to create a constraint. Two unique constraints: create table unique_test2 (test_id int not null, test_name varchar (255), test_pass varchar (255), unique (test_name ), constraint test2_uk unique (test_pass); // create a unique constraint when creating a table and use the table-level constraint syntax to create a constraint. These two constraints are in a group and must not be empty at the same time. create table unique_test3 (test_id int not null, test_name varchar (255), test_pass varchar (255), constraint test3_uk unique (test_name, test_pass); // when modifying the table structure, use the add keyword to add the unique constraint alter table unique_test3add unique (test_name, test_pass ); // use the modify keyword when modifying a table and use the column-level constraint syntax for a single column to add the unique constraint alter table hehemodify test_name varchar (255) unique; // drop constraint to delete the constraint // Delete the unique alter t constraint of test3_uk on the unique_test3 table Able unique_test3drop index test3_uk; // create a primary key constraint when creating a table. Use the column-level constraint syntax to create a constraint create table primary_test (test_id int primary key, test_name varchar (255 )); // create a primary key constraint when creating a table. The table-level constraint create table primary_test2 (test_id int not null, test_name varchar (255), test_pass varchar (255) is used ), --- specify the PRIMARY key constraint name test2_pk, which is valid for most databases but not MySql. The primary key constraint name in MySql data is still PRIMARY constraint test2_pk primary key (test_id )); // create a primary key constraint when creating a table and create a composite topic with multiple columns. It can only be used for table-level constraint Syntax: c Reate table primary_test3 (test_name varchar (255), test_pass varchar (255), -- create a primary key constraint for multi-column combination primary key (test_name, test_pass )); // Delete the primary key constraint of the specified table. You can use the drop primary key statement after the alter table statement to alter table primary_test3drop primary key; // modify the column definition through modify to add the primary key constraint, alternatively, add the primary key constraint alter table primary_test3add primary key (test_name, test_pass); alter table primary_test3modify test_name varchar (255) primary key; // set the auto-increment cr Eate table primary_test4 (test_id int auto_increment primary key, test_name varchar (255), test_pass varchar (255); // use the column-level constraint syntax to create a foreign key constraint and directly use the references keyword, references specifies the primary table referenced by the column and the create table teacher_table1 (teacher_id int auto_increment, teacher_name varchar (255), primary key (teacher_id) column referenced by the primary table )); create table student_table1 (student_id int auto_increment primary key, student_name varchar (255), java_te Acher int references teacher_table1 (teacher_id); // It is worth noting that although MySql supports creating foreign key constraints using the column-level constraint syntax, however, the foreign key constraint created by this column-level constraint syntax does not take effect // MySql provides this column-level constraint syntax to maintain good compatibility with standard SQL. therefore, if a foreign key constraint in MySql is required to take effect, use the tag constraint syntax create table teacher_table (teacher_id int auto_increment, teacher_name varchar (255), primary key (teacher_id )); // specify java_teacher to refer to create table student_table (student_id int auto_increment primary key, s Tudent_name varchar (255), struct int, foreign key (java_teacher) references teacher_table (teacher_id); create table partition (teacher_id int auto_increment, teacher_name varchar (255 ), primary key (teacher_id); create table student_table2 (student_id int auto_increment primary key, student_name varchar (255), java_teacher int, constraint foreign key (java_teacher) refe Rences teacher_table2 (teacher_id); create table teacher_table3 (teacher_name varchar (255), teacher_pass varchar (255), primary key (teacher_name, teacher_pass )); create table partition (student_id int auto_increment primary key, student_name varchar (255), java_teacher_name varchar (255), role varchar (255), foreign key (java_teacher_name, role) references limit (Teacher_name, teacher_pass); // Delete the foreign key constraint alter table student_table3drop foreign key student_table3_ibfk_1; // add a foreign key constraint (alter table student_table3add foreign key (java_teacher_name, java_teacher_pass) references teacher_table3 (teacher_name, teacher_pass ); // create a foreign key for the table-level constraint method, and directly refer to the create table foreign_test (foreign_id int auto_increment primary key, foreign_name varchar (255), refer_id I Nt, foreign key (foreign_id) references foreign_test (foreign_id); // if you want to delete a primary table record, the table record will also be deleted, you need to add on delete cascade or add on delete set null after setting a foreign key constraint. The first is to delete the primary table record, deletes all slave table records that reference the master table record. The second option is to specify that when the master table record is deleted, set the foreign key of the slave table record to nullcreate table teacher_table4 (teacher_id int auto_increment primary key, teacher_name varchar (255 )); create table student_table4 (student_id int auto_increment primary key, student_name va Rchar (255), java_teacher int, foreign key (java_teacher) references teacher_table4 (teacher_id) on delete cascade, emp_name varchar (255), emp_salary decimal, primary key (emp_id), check (emp_salary> 0); // insert into teacher_table2 (teacher_name) values ('xyz '); insert into teacher_table2values (null, 'abc'); // insert into stude Nt_table2values (null, 'zhang san', 2); insert into student_table2values (null, "Yeeku", 1), (null, "Sharfly", 3 ); update statement: update teacher_table2set teacher_name = 'Sun wukong'; update teacher_table2set teacher_name = 'pig bag' where teacher_id> 1; delete from student_table2 where teacher_id> 2; select * from student_table2 where 2 between java_teacher and student_id; // select all records whose java_teacher is less than or equal to 2 and whose student_id is greater than or equal to 2. // by default, the Group The function treats all records as a group. To explicitly group records, you can use the group by clause after the select statement. The group by clause is usually followed by one or more column names, it indicates that the query results are grouped by one or more columns. When one or more columns are combined completely, the system treats these records as a group. Select count (*) from student_table2 group by java_teacher;