MySQL syntax collation (4)

This blog explains MySQL Some basic insert delete update statements , And explain the connection between inside and outside, left and right .

delete： Delete statements , Records or tables that meet the conditions will be deleted

/*delete： Delete statements ,where Specifies to delete objects , All satisfied records will be deleted */
DELETE FROM instructor
WHERE dept_name='Finance';

/* Common deletion between two tables */
DELETE FROM instructor
WHERE dept_name IN 
	(SELECT dept_name
	FROM department
	WHERE building ='Watson');

insert： The insert .

/* The insert , If it does not indicate that the element is inserted , By default, it is arranged by table elements */
INSERT INTO course
	VALUES('CS-437','Database Systems','Comp. Sci.',4);

/* Will table A Insert results into table B in , Be careful select The type and number of elements should be consistent , Otherwise, it will report a mistake */
INSERT INTO instructor
	SELECT ID,NAME,dept_name,18000 --  the last one 18000 It's salary 
	FROM student
	WHERE dept_name='Physics' AND tot_cred > 10; --  Select physics department with credits greater than 10 Of the students 

update： update operation .

/* update operation , That is, the records in the original table */
UPDATE instructor
	SET salary = salary * 1.05; --  here set It's the assignment symbol , It means multiplying the original salary by 1.05

inner join： Internal connection ,outer join： External connection . First, let's talk about what is inner connection and outer connection . Connect two tables , There will be cases where the primary key of one table does not appear in another table . Such as student list and course selection list , There will be some students who don't choose classes , If we join two tables , There will be some students who cannot match the records in the course selection table , What about those students who don't choose courses ？ For internal connections, you will directly ignore , That is, it will not be placed in the result table ; But if it's an external connection , Although some students don't take courses , But we can also add it to the result table , Make their course information value as null That's all right. . To sum up , Empty values in the table are not allowed , External connections allow .

/* Only internal connections inner join Connect takes and student surface */
SELECT *
FROM student JOIN takes ON student.ID = takes.ID -- on Add connection conditions after 

/* Find out the course selection of all students , Including students who don't choose courses , Use left outer connection 
 The left outer connection is the left. Null values are not allowed , The right table can be null */
SELECT *
FROM student LEFT OUTER JOIN takes ON student.ID=takes.ID;

The left outer connection is introduced above , The right outer connection is the same . So is there a left-right external connection ？ It's real , It's just that we don't call it left-right external connection , It's called total external connection .MySQL Statements with full outer join are not supported ：full outer join, But the whole external connection is a combination of the left external connection and the right external connection , Just one union That's it .

SELECT *
FROM (SELECT id,NAME -- select The key must match the following union The consistency of 
	FROM student
	WHERE dept_name='Comp. Sci.')
	AS Cstudent1 (id , NAME)
	LEFT OUTER JOIN
	(SELECT id
	FROM takes
	WHERE semester='Spring' AND YEAR=2009)
	AS Cspring1 (id)
	ON Cstudent1.id= Cspring1.id
UNION 
SELECT *
FROM (SELECT id,NAME
	FROM student
	WHERE dept_name='Comp. Sci.')
	AS Cstudent2 (id , NAME)
	RIGHT OUTER JOIN
	(SELECT id
	FROM takes
	WHERE semester='Spring' AND YEAR=2009)
	AS Cspring2 (id)
	ON Cstudent2.id= Cspring2.id;

After talking about the internal and external connection , Next, let's talk about views , stored procedure , Functions and triggers , There will be more content , But it's practical ！view： View . The function of view is equivalent to establishing a fixed table , Usually, we will create some temporary tables in the query , Once the query is complete , The temporary table will be deleted by the system . After the view is created , We can use it at any time .

/* adopt view Statement to create a view faculty*/
CREATE VIEW faculty AS 
 SELECT ID,NAME,dept_name 
 FROM instructor;
 
 /* Build a 2009 View of physics class in autumn */
CREATE VIEW physics_fall_2009 
AS
	SELECT course.course_id,sec_id,building,room_number 
	FROM course,section 
	WHERE course.course_id = section.course_id 
	AND course.dept_name='Physics' 
	AND section.semester='Fall' AND section.year='2009';
	
/* Yes physics_fall_2009 View to query , In fact, the view is no different from the ordinary table when querying */
SELECT course_id 
FROM physics_fall_2009 
WHERE building='Watson';

faculty The view is shown below ：

/* Create a view based on the view , No doll ！*/
CREATE VIEW physics_fall_2009_Waston 
AS 
	SELECT course_id,room_number 
	FROM physics_fall_2009 
	WHERE building='Watson';

We just query the newly created view , Can the view support insert and delete operations ？ It can , But there are limitations , That is, the view must come from a table , At the same time, we perform projection operation to establish the view , Other keys without projection must be allowed to be empty .

/* This faculty Because the view is just a simple single table projection selection operation , Therefore, it can support inserting */
INSERT INTO faculty 
VALUES('30765','Green','Music');

/* This demonstrates that the view created under multi table query cannot be inserted , I guess the reason is that the view appears to be a table 
 In fact, the system does not actually build this view table , Instead, the view and the original table are connected by index , Because the view involves two tables 
 So you can't insert */
create view instructor_info 
as 
 select ID, name, building 
 from instructor, department 
 where instructor.dept_name = department.dept_name; 
--  insert record :
insert into instructor_info 
values ('69987','White','Taylor');

The error message is as above . It's also easy to understand why the error is reported , The view is pieced together by multiple tables , If you want to insert , How to allocate the inserted data back to the original table ？ The view we can see is an index table , Not a real watch .

There will also be a situation in the view ： For example, a view shows the information of the history teacher , The view is from instructor Established in the table , When I insert a message about the music teacher into this view , Although we refresh the view and find no records , But look at it. instructor It is found that the record is really inserted .

/* Create a history teacher */
CREATE VIEW history_instructors 
AS
	SELECT * 
	FROM instructor 
	WHERE dept_name= 'History';

--  Insert one that is not History Teacher's record , You won't find it in the view , however instructor The table will be saved 
INSERT INTO history_instructors 
 VALUES ('25566','Brown','Biology',100000)

--  Here you can insert and query , Because the view will find dept_name= 'History' Teacher 
INSERT INTO history_instructors 
 VALUES ('68583','Califieri','History',100000);

--  Then query the view 
SELECT * FROM history_instructors; --  You will find no newly inserted records 

with check option： Check whether it conforms to the definition of the view when inserting .

--  Use with check option Rebuild the view 
CREATE VIEW history_instructors AS 
	SELECT * 
	FROM instructor 
	WHERE dept_name= 'History' 
	WITH CHECK OPTION;

--  Insert Biology Teacher's record , Error will be reported at this time , The reason is that our department is not a history department 
INSERT INTO history_instructors 
VALUES ('25566','Brown','Biology',100000)
/* Added... When defining the view  WITH CHECK OPTION  Clause , Insert the view 、
 Modify and delete operations ,RDBMS  It will automatically add  dept_name= ’History’ Conditions .*/

The error report is shown in the picture ：

Let's first introduce some data types of the table

MySQL Support all standards SQL Numerical data type .

These types include strict numerical data types (INTEGER、SMALLINT、DECIMAL and NUMERIC), And approximate numerical data types (FLOAT、REAL and DOUBLE PRECISION).

keyword INT yes INTEGER A synonym for , keyword DEC yes DECIMAL A synonym for .

BIT Data type save bit field value , And support MyISAM、MEMORY、InnoDB and BDB surface .

As SQL Standard extension ,MySQL Integer types are also supported TINYINT、MEDIUMINT and BIGINT. The following table shows the storage and range required for each integer type .

Date and time type
The date and time type that represent the time value are DATETIME、DATE、TIMESTAMP、TIME and YEAR.

Each time type has a valid value range and a " zero " value , When the designation is illegal MySQL Use... When the value cannot be represented " zero " value .

TIMESTAMP Type has a proprietary automatic update feature

String type
String type means CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM and SET. This section describes how these types work and how to use them in queries .

Next, let's introduce some operations of creating tables ,not null： Non empty constraint , It is not allowed to be null

/* Non empty constraint */
CREATE TABLE instructor 
(ID VARCHAR(5), NAME VARCHAR(20) NOT NULL, /* Non empty constraint */ 
dept_name VARCHAR(20), 
salary NUMERIC(8,2) CHECK (salary > 29000), 
PRIMARY KEY (ID), 
FOREIGN KEY (dept_name) REFERENCES department ON DELETE SET NULL 
);

unique： only , Especially suitable for some primary keys

/*unique bring name Must be unique */
CREATE TABLE instructor 
(ID VARCHAR(5), 
NAME VARCHAR(20) UNIQUE, /*unique  constraint */ 
dept_name VARCHAR(20), 
salary NUMERIC(8,2) CHECK (salary > 29000), 
PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department ON DELETE SET NULL 
);

check： The system will check and limit the filled data , We used to use check Restrict view insertion .

/* Use check To limit the filled data */
CREATE TABLE section
(course_id VARCHAR(8)
, sec_id VARCHAR(8), 
semester VARCHAR(6) CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer')), 
YEAR NUMERIC(4,0) CHECK (YEAR > 1701 AND YEAR < 2100), 
building VARCHAR(15), 
room_number VARCHAR(7), 
time_slot_id VARCHAR(4), 
PRIMARY KEY (course_id, sec_id, semester, YEAR) 
);

on delete set null： When the record of a table is deleted , This record is a foreign key, and other table records are set to null . Sounds awkward , For example, you will understand .

--  You can do it on your own  department  Table execution  delete  operation , After default ,course  The corresponding tuple in the table 
-- det_name  Set to null ;
create table course 
(course_id varchar(8), 
title varchar(50), 
dept_name varchar(20), 
credits numeric(2,0) check (credits > 0), 
primary key (course_id), --  Set primary key 
foreign key (dept_name) references department on delete set null 
);

on delete cascade： Al level to delete .

--  cascading deletion  on delete cascade
create table course 
(course_id varchar(8), 
title varchar(50), 
dept_name varchar(20), 
credits numeric(2,0) check (credits > 0), 
primary key (course_id), 
foreign key (dept_name) references department on delete cascade
);
--  Can be found in  department  Table execution  delete  operation , After default ,course  The corresponding tuples in the table are also automatically deleted 

create user： Create user .

/* Create... On this machine  s1  user , But because of SQLyog Version of the problem , Will not be able to log in */
CREATE USER 's1'@'localhost' IDENTIFIED BY 's1';
--  Replaced IDENTIFIED WITH mysql_native_password BY sentence , So you can log in 
CREATE USER 's1'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
--  Refresh the permissions 
FLUSH privilege;

grant： To give permission .

/* take university Of department Table query is authorized to s1 user , Other modification or deletion operations are not granted */
GRANT SELECT ON university.department TO 's1'@'localhost'; --  hinder @localhost Indicates that the host login has this permission 
/* take university Of department Tabular budget The update permission of the row is given to s1 user 	*/
GRANT UPDATE(budget) ON university.department TO 's1'@'localhost';
/* see s1 User's rights */
SHOW GRANTS FOR [email protected];

drop user： Delete user .

/* Delete user */
DROP USER 's1'@'localhost';

set password for： Set or change the password for the user .

/* Change user password */
SET PASSWORD FOR ' user name '@' host ' = PASSWORD(' New password ');

/* Inquire about root User information , The user's information is USER In the table , If you want to visit select jurisdiction */
SELECT HOST,USER 
FROM USER 
WHERE USER='root';

create role： Create the role . I think the role is equivalent to a permission template , For example, I create a character , Give roles permissions to certain tables in some databases , As long as I add users to this role , You have the permission of the user , It's kind of similar Linux The permissions of the group user .

/*- Create the role */
CREATE role instructor;
/* Delegate to roles */
GRANT SELECT ON university.takes TO instructor; 
/* take  s1  Add to  instructor  in ,s1  You have the permission of this role */
GRANT instructor TO 's1'@'localhost';