Built in constraints and functions of MySQL Foundation

The main contents of this chapter are as follows:

1, Table constraint

in order to ensure that the database is inserted according to the specified rules, table constraints are introduced in the database. Common table constraints are
1. The primary key constraint primary key (cannot be empty or duplicate) if it is an integer type, it is generally defined that the primary key automatically grows by auto_increment is usually added through the visual connection tool.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10),
  `password` char(10),
  PRIMARY KEY (`id`)
)

2. Unique constraint, also known as unique index, is used to set that the field cannot be repeated.

ALTER TABLE `user`.`user` ADD UNIQUE INDEX index_name (`name`);

3. Non NULL constraint not null is used to specify that the field cannot be empty. It is generally specified when creating a table or added by the visual connection tool. It can also be used with DEFAULT to indicate the DEFAULT value of this field when it is not entered.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) NOT NULL DEFAULT 123456,
  PRIMARY KEY (`id`)
)

4. Foreign key constraints. When creating foreign key constraints, ensure that the database engines of the two tables are the same and cannot be MYISAM. They are generally added through visual connection tools.

ALTER TABLE `client` ADD CONSTRAINT `emp_id_fk` FOREIGN KEY (`emp_id`) REFERENCES `emp`(`id`);

The general table creation commands are as follows

CREATE TABLE mytb(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    pwd VARCHAR(40) NOT NULL,
    birth DATE,
    PRIMARY KEY ( id )
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

If you do not want the field to be NULL, you can set the property of the field to NOT NULL. When operating the database, if the data entered in the field is NULL, an error will be reported.
AUTO_INCREMENT defines a column as a self incrementing attribute, which is generally used for the primary key. The value will be automatically incremented by 1 after each data insertion.
PRIMARY KEY keyword is used to define a column as a PRIMARY KEY. You can use multiple columns to define a PRIMARY KEY, which is called a composite PRIMARY KEY. Columns are separated by commas.
ENGINE sets the storage ENGINE. Use the show engines command to view the database engines supported by MySQL. CHARSET sets the encoding.
Note: the difference between utf8 and utf8mb4 is that utf8mb4 is compatible with some icon fonts. If the engine and character encoding are set during installation, they can not be specified during table creation.

2, Data type

MySQL provides many data types to meet various business needs. The common types are as follows:

INT integer 4 bytes
BIGINT long integer 8 bytes
FLOAT single precision decimal single precision floating point value
DOUBLE DOUBLE precision decimal DOUBLE precision floating point value
DECIMAL DECIMAL pair DECIMAL(P,D)
DATE DATE value YYYY-MM-DD
TIME TIME value HH:MM:SS
DATETIME mixed date and time values YYYY-MM-DD HH:MM:SS
CHAR fixed length string 0-255 bytes
VARCHAR variable length string 0-65535 bytes
TEXT long TEXT data 0-65535 bytes
BLOB binary data 0-65535 bytes
be careful:

1. In the syntax of DECIMAL(P,D): P is the precision of the significant number. P ranges from 1 to 65. D is the number of digits after the decimal point. The range of D is 0 ~ 30. MySQL requires d to be less than or equal to (< =) P. For example, DECIMAL(6,2) can store up to 6 digits with 2 decimal places; Therefore, the range of columns is from -9999.99 to 9999.99.

2. N in the brackets of char (n) and varchar(n) represents the number of characters, not the number of bytes. For example, CHAR(30) can store 30 characters. Char is a fixed length string. Once declared, it occupies the corresponding byte length whether the stored value reaches the length or not. Varchar is a variable length string. Its length is determined by the stored content. When it is full, it is mainly based on the actual stored content.

3.BLOB is a large binary object that can accommodate a variable number of binary data. There are four types of blobs: TINYBLOB, BLOB, mediablob and LONGBLOB. The difference is that they can accommodate different storage ranges.

4.TEXT also has four types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. The difference between them is also the maximum length that can be stored. The specific type can be selected according to the actual situation.

Exercise: submitting pictures during operation

1. Create a table system_user contains int id self incrementing primary key, char(10) name is not empty, varchar(255) password is not empty and the default value is 123456, blob image picture, text information introduction, tinyint sex, decimal(10,2) money asset, DATETIME birthdays birthday, and use SQLYog to insert 3 test data into the table.

2. Create a table system_classroom contains int id that is not empty and self increasing, varchar(50) name that is not empty, and three classes are inserted into the table: JAVA, UI and new media

3. In system_ Add the field classroom in user_ id, and add a foreign key to point to the id in the class table, and for all systems_ Users assign their own classes.

Reference answer:

#1. Create a table system_user contains int id, char (10) name, varchar (255) password is not empty, and the default value is 123456, blob image and text information
#tinyint sex,decimal(10,2) money. id is not empty and self incremented. And insert 3 pieces of data
create table system_user(
id int primary key not null auto_increment comment 'number',
name char(10) not null comment 'full name',
password varchar(255) not null default '123456' comment 'password',
image blob comment 'head portrait',
information text comment 'resume',
sex tinyint comment 'Gender',
money decimal(10,2) comment 'balance'
)
#2. Create a table system_classroom contains int id, varchar(50) name is not empty. Insert three classes, JVAV, UI and new media
create table system_classroom(
id int primary key not null  comment 'Class number',
name varchar(50) comment 'Class name'
)
#3. In system_ Add system to user_ User, and add a foreign key to point to the id in the class table, and it is system_ Users assign their own classes
alter table system_user add classroom_id int after id;
alter table system_user add constraint id foreign key (classroom_id) references system_classroom(id)

3, View

a view is actually a virtual table, an incomplete or calculated virtual table exported from other tables.
what's the use of view? When we often query several fields in a table, we can export these fields to a view separately. In future queries, we can directly select *. Only the definition of view is stored in the database, and there is no view data. The view depends on the data in the original table. Once the data in the original table changes, The results of the view query will also change. Let's create a view.

Case: create a view for the Chinese teacher

CREATE VIEW vi_chinese_user 
AS SELECT id,`name`,`chinese` FROM `student`;

The view is exactly the same as the ordinary table query statement. To delete a view, use the following command:

DROP VIEW `vi_chinese_user`;

4, Trigger

triggers trigger an operation by events, including insert, delete and update statements. When the database executes these events, the trigger is activated to perform the corresponding operation.
we need a table. Here we use the user table to add, delete and modify the table. In addition, we need a user_log table. When we operate on the user table, our trigger will be activated_ Log table to perform corresponding operations.

CREATE TRIGGER auto_save_time BEFORE INSERT 
ON `user` FOR EACH ROW 
INSERT INTO user_log(log_time) VALUES(NOW());

this statement means to create a trigger named auto_save_time, before executing the insert statement on any row of the user table, the trigger will be activated and insert into user will be executed_ log(log_time) values(now()); sentence. Next, we perform an insert operation on the user table. It can be seen that the trigger is activated and the statement is executed. Now() indicates that there can be no space before the current time bracket.
to view triggers, use the command show triggers; To delete a trigger, use the command drop trigger trigger trigger name; There are six triggers, before and after insertion, before and after deletion, and before and after modification. There is also after corresponding to before in the previous case.

practice:

1. Create a table student(id,name) and student_log (id,log_time,method), create three triggers to complete the records of all modification operations of the student table.

Reference answer:

CREATE TABLE `student_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `log_time` DATETIME NOT NULL,
  `method` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;


CREATE TRIGGER student_insert BEFORE INSERT 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'add to');

CREATE TRIGGER student_update BEFORE UPDATE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'modify');

CREATE TRIGGER student_delete BEFORE DELETE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'delete');

5, Stored procedure

stored procedure is a set of SQL statements stored in the database to complete specific functions in a large database system. It is called again after the first compilation. It does not need to be compiled again. The user executes the stored procedure by specifying its name and giving parameters (if the stored procedure has parameters), Stored procedure is an important object in database; The stored procedure can contain logic control statements and data manipulation statements. It can accept parameters, output parameters, return single or multiple result sets and return values;

create table p_user(
	id int primary key auto_increment,
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"male");
insert into p_user(name,sex) values('B',"female");
insert into p_user(name,sex) values('C',"male");

create a stored procedure (query to get the number of men or women. If 0 is passed in, get the number of women; otherwise, get the number of men)

DELIMITER $
CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IF sex_id=0 THEN
SELECT COUNT(*) FROM p_user WHERE p_user.sex='female' INTO user_count;
ELSE
SELECT COUNT(*) FROM p_user WHERE p_user.sex='male' INTO user_count;
END IF;
END 
$

Call stored procedure

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

Query the number of men or women. If 0 is passed in, it is a woman, otherwise it is a man.

6, String, number, time processing function

when querying data, it is necessary to process the found fields, such as lowercase to uppercase, cutting, etc. At this time, you need to use the string processing function of the database. The following are the common string processing functions of mysql.

CHARSET(field)	                            Returns the character set of the field
CONCAT(string2  [,... ])	            connection string 
INSTR(string ,substring )	            return substring stay string Location in,0 was not returned
UCASE(string2 )	                            Convert to uppercase
LCASE(string2 )	                            Convert to lowercase
LEFT(string2 ,length )	                    from string2 From left in length Characters
LENGTH(string )	                            return string Number of bytes
REPLACE(str ,search_str ,replace_str )	    stay str of use replace_str replace search_str
STRCMP(string1 ,string2 )	            Compare two string sizes character by character,Returns 1, 0, and-1
SUBSTRING(str , position  [,length ])	    from str of position start,take length Characters
LTRIM(string2 ) RTRIM (string2 )            trim Remove front-end or back-end spaces

Case:

SELECT CHARSET(`name`) FROM `emp`;
SELECT CONCAT(`name`,`pwd`) FROM `emp`;
SELECT INSTR(`pwd`,'2') FROM `emp`;
SELECT UCASE(`name`) FROM `emp`;
SELECT LCASE(`name`) FROM `emp`;
SELECT LEFT(`pwd`,2) FROM `emp`;
SELECT LENGTH(`pwd`) FROM `emp`;
SELECT STRCMP('b' ,'b' ) FROM `emp`;
SELECT SUBSTRING(`pwd`,2,2) FROM `emp`;
SELECT LTRIM(`name`) FROM `emp`;
SELECT RTRIM(LTRIM(`name`)) FROM `emp`;

Digital processing function

The following are the commonly used number processing functions in mysql

ABS(number2 )	                            absolute value
CEILING (number2 )	                    Round up
FLOOR (number2 )	                    Round down
FORMAT (number,Decimal places)	            Keep decimal places
HEX (DecimalNumber )	                    To hexadecimal
LEAST (number , number2  [,..])	            Find the minimum value
MOD (numerator ,denominator )	            Seeking remainder
RAND([seed])	                            random number

Case:

SELECT `name`,ABS(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,FORMAT (`english`,2) FROM `student`;
SELECT `name`,`english`,HEX  (`english`) FROM `student`;
SELECT `name`,RAND() FROM `student`;
SELECT `name`,CEILING(RAND()*10) FROM `student`;

Date time processing function

Get the current date (including specific hours, minutes and seconds)

SELECT NOW(),LOCALTIME(),SYSDATE()

Get the current date (excluding specific hours, minutes and seconds)

SELECT CURRENT_DATE(),CURDATE()

Conversion between date and timestamp

SELECT UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1629882598)

Gets the current time (hours, minutes, seconds)

SELECT CURRENT_TIME(),CURTIME()

Get month

MONTH(date) returns the value of the month; MONTHNAME(date) returns the English name of the month

SELECT MONTH(NOW()),MONTHNAME(NOW())

Function to get days

DAYOFMONTH(date) returns the day of the current month; DAYOFYEAR(date) returns the day of the current year

SELECT DAYOFMONTH(NOW()),DAYOFYEAR(NOW())

Function to get week

DAYNAME(date) returns the English name of the day of the week; DAYOFWEEK(date) returns the value of the day of the week. The range of the returned value is 1-7. 1 means Sunday, and so on; WEEKDAY(date) returns the value of the day of the week. The range of the returned value is 0-6. 0 means Monday, and so on

SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW())

Number of days between two dates

TO_DAYS(date) the number of days between the date and the default date (January 1, 2000); FROM_DAYS(number) the default date is the date after number; DATEDIFF(date1, date2) gets the number of days between the specified two dates, date1-date2

SELECT TO_DAYS(NOW()),FROM_DAYS(738166),DATEDIFF(NOW(),'2020-01-01')

Addition and subtraction of dates

DATE_ADD(date,INTERVAL expr type) adds the date after the specified time interval on the basis of date.

DATE_SUB(date,INTERVAL expr type) is the date after subtracting the specified time interval from the date.

The commonly used type values are: year, month, day, hour, minute, second, microsecond (milliseconds), week and quarter

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY)

Exercise: (dictation for 20 minutes)

Data: import the following data and complete the corresponding query. Download the address

CREATE TABLE `employee` (
  `ID` int(4) NOT NULL,
  `NAME` varchar(10) NOT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MANAGER` int(4) DEFAULT NULL,
  `HIRE_DATE` date DEFAULT NULL,
  `SALARY` double(7,2) DEFAULT NULL,
  `PRIZE_MONEY` double(7,2) DEFAULT NULL,
  `DEPT` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7369,'smith','clerk',7902,'1980-12-17',800.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7499,'allen','salesman',7698,'1981-02-20',1600.00,300.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7521,'ward','salesman',7698,'1981-02-22',1250.00,500.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7566,'jones','manager',7839,'1981-04-02',2975.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7654,'martin','salesman',7698,'1981-09-28',1250.00,1400.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7698,'blake','manager',7839,'1981-05-01',2850.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7782,'clark','manager',7839,'1981-06-09',2450.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7839,'king','president',NULL,'1981-11-17',5000.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7844,'turner','salesman',7698,'1981-09-08',1500.00,0.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7900,'james','clerk',7698,'1981-12-03',950.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7902,'ford','analyst',7566,'1981-12-03',3000.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7934,'miller','clerk',7782,'1982-01-23',1300.00,NULL,'accounting');

Title:

1.Query the number, name and salary of employees without superior leaders
2.The name, position, salary and bonus of the employee with bonus in the query table
3.Query the name, salary and number of the superior leader
4.Name in query table'S'Names of all employees at the beginning
5.The last character of the name in the query table is'S'Name of employee
6.The penultimate character of the query is'E'Name of employee
7.The employee's name in the query table contains'A'Name of employee
8.The name in the query table is not'K'All information of employees at the beginning
9.The name in the query table does not contain'A'Information of all employees
10.Number of clerks( job Contains CLERK (yes)
11.salesman job: SALESMAN Highest paid employee information
12.The number, name, position and salary of the employee in the query table, and the salary is 1000~2000 between.
13.Employees in query table ACCOUNTING Department, including the name, position, superior leader number and Department of the employee of the superior leader
14.The name in the query table contains'E'，And the position is not MANAGER The employee's number, name, position, and salary.
15.Query table ACCOUNTING Department or RESEARCH The number, name and Department of the employee in the Department
16.There is no bonus in the query table or the penultimate letter of the name is not T Number, name, position and bonus of the employee
17.Query whether the salary is higher than 3000 or the Department is SALES Name, position, salary, employment time and Department of the employee
18.Query is not SALES All information about employees in the Department
19.Query all information of employees whose bonus is not empty
20.The number, name and position of all employees in the query table are arranged in descending order according to the number of employees
21.The Department in the query table is ACCOUNTING perhaps RESEARCH In, the names, positions and wages of all employees are arranged in ascending order according to their wages
22.Query all the data in the table, and then arrange them in ascending order according to the Department. If the departments are the same, arrange them in descending order according to the employee number
23.The number, name, salary, department and number of superior leaders of employees whose salary is higher than 1000 or without superior leaders in the query table are arranged in descending order according to the Department. If the departments are consistent, they are arranged in ascending order according to the salary.
24.The name in the query table does not contain S The number, name, salary and bonus of employees are arranged in ascending order according to the salary. If the salary is consistent, they are arranged in descending order according to the number
25.Total number of employees in the statistical table
26.Find out the employee with the highest salary in the table
27.Find out the employee with the largest employee number in the table
28.Minimum wage of employees in the query table
29.Employees on Monday in the query table
30.Query table at the beginning of each month(1 number)New employees

Reference answer:

1.Query the number, name and salary of employees without superior leaders.
SELECT ID,NAME,SALARY FROM employeeloyee WHERE MANAGER IS NULL;

2.query employee The name, position, salary and bonus of the employee with bonus in the table
SELECT NAME,job,SALARY,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

3.Query the name, salary and number of the superior leader
SELECT NAME,SALARY,MANAGER FROM `employee` WHERE MANAGER IS NOT NULL

4.query employee Names in the table begin with'S'Names of all employees at the beginning
SELECT NAME FROM `employee` WHERE NAME LIKE 'S%'

5.query employee The last character of the name in the table is'S'Name of employee
SELECT NAME FROM `employee` WHERE NAME LIKE '%S'


6.The penultimate character of the query is'E'Name of employee
SELECT NAME FROM `employee` WHERE NAME LIKE '%E_'


7.query employee The name of the employee in the table contains'A'Name of employee
SELECT NAME FROM `employee` WHERE NAME LIKE '%A%'

8.query employee The name in the table is not'K'All information of employees at the beginning
SELECT NAME FROM `employee` WHERE NAME NOT LIKE 'K%'


9.query employee The name in the table does not contain'A'Information of all employees
SELECT * FROM `employee` WHERE NAME NOT LIKE '%A%'


10.Number of clerks( job Contains CLERK (yes)
SELECT COUNT(0) FROM `employee` WHERE job='clerk'

11.salesman job: SALESMAN Highest paid employee information
SELECT * FROM `employee` WHERE job='SALESMAN' ORDER BY SALARY DESC LIMIT 1


12.The number, name, position and salary of the employee in the query table, and the salary is 1000~2000 between.
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY BETWEEN 1000 AND 2000
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY >= 1000 AND SALARY<= 2000

13.Employees in query table ACCOUNTING Department, including the name, position, superior leader number and Department of the employee of the superior leader
SELECT NAME,job,MANAGER,dept FROM `employee` WHERE dept ='ACCOUNTING' AND MANAGER IS NOT NULL

14.The name in the query table contains'E'，And the position is not MANAGER The employee's number, name, position, and salary.
SELECT id,NAME,job,SALARY FROM `employee` WHERE NAME LIKE '%E%' AND job != 'MANAGER'

15.Query table ACCOUNTING Department or RESEARCH The number, name and Department of the employee in the Department
SELECT id,NAME,dept FROM `employee` WHERE dept='ACCOUNTING' OR dept ='RESEARCH'


16.There is no bonus in the query table or the penultimate letter of the name is not T Number, name, position and bonus of the employee
SELECT id,NAME,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NULL OR NAME NOT LIKE '%T_'

17.Query whether the salary is higher than 3000 or the Department is SALES Name, position, salary, employment time and Department of the employee
SELECT NAME,JOB,SALARY,HIRE_DATE dept FROM `employee` WHERE SALARY >3000 OR dept ='SALES'


18.Query is not SALES All information about employees in the Department
SELECT * FROM `employee` WHERE dept !='SALES'


19.Query all information of employees whose bonus is not empty
SELECT * FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

20.The number, name and position of all employees in the query table are arranged in descending order according to the number of employees
SELECT id,NAME,job FROM `employee` ORDER BY id DESC


21.The Department in the query table is ACCOUNTING perhaps RESEARCH In, the names, positions and wages of all employees are arranged in ascending order according to their wages
SELECT NAME,JOB,SALARY FROM `employee` WHERE dept ='ACCOUNTING' OR dept ='RESEARCH' ORDER BY SALARY ASC


22.Query all the data in the table, and then arrange them in ascending order according to the Department. If the departments are the same, arrange them in descending order according to the employee number
SELECT * FROM `employee` ORDER BY dept ASC,id DESC 

23.The number, name, salary, department and number of superior leaders of employees whose salary is higher than 1000 or without superior leaders in the query table,
It is arranged in descending order according to the Department. If the departments are consistent, it is arranged in ascending order according to the salary.
SELECT id,NAME,SALARY,DEPT,MANAGER FROM `employee` WHERE SALARY>1000 OR MANAGER IS NULL ORDER BY dept DESC,SALARY ASC


24.The name in the query table does not contain S The number, name, salary and bonus of employees are arranged in ascending order according to the salary. If the salary is consistent, they are arranged in descending order according to the number
SELECT id,NAME,SALARY,PRIZE_MONEY FROM `employee` WHERE NAME NOT LIKE '%S%' ORDER BY SALARY ASC,id DESC

25.Total number of employees in the statistical table
SELECT COUNT(0) FROM `employee`

26.Find out the employee with the highest salary in the table
SELECT * FROM `employee` ORDER BY SALARY DESC LIMIT 1

27.Find out the employee with the largest employee number in the table
SELECT * FROM `employee` ORDER BY id DESC LIMIT 1

28.The minimum wage of the employee in the query table.
SELECT * FROM `employee` ORDER BY SALARY ASC LIMIT 1

29.Query the employees on Monday in the table.
select * from employee where WEEKDAY(HIRE_DATE) = 0

30.Query table at the beginning of each month(1 number)New employees.
select * from employee where DAYOFMONTH(HIRE_DATE) = 1

7, Database engine (self-study)

the engine provided by the database is as follows:

MyISAM: the default MySQL plug-in storage engine, which is one of the most commonly used storage engines in Web, data warehousing and other application environments.

InnoDB: for transaction processing applications, it has many features, including ACID transaction support and row level lock.

Memory: save all data in Memory. It can provide extremely fast access in the environment where reference and other similar data need to be quickly found.

...

Two common engines are Innodb and MyIASM.

Innodb engine

Innodb engine provides support for database ACID transactions and implements four isolation levels of SQL standard. The engine also provides row level locks and foreign key constraints. Its design goal is to deal with large-capacity database systems. In fact, it is a complete database system based on MySQL background. When MySQL runs, Innodb will establish a buffer pool in memory to buffer data and indexes. However, the engine does not support FULLTEXT type indexes, and it does not save the number of rows of the table. When SELECT COUNT(*) FROM TABLE, you need to scan the whole table. When you need to use database transactions, the engine is of course the first choice. Because the granularity of the lock is smaller, the write operation will not lock the whole table. Therefore, when the concurrency is high, using the Innodb engine will improve the efficiency. However, the use of row level locks is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the Innodb table will also lock the entire table.

MyIASM engine

MyIASM is the default engine of MySQL, but it does not support database transactions, row level locks and foreign keys. Therefore, when inserting or updating data, the write operation needs to lock the whole table, which will be less efficient. However, unlike Innodb, MyIASM stores the number of rows in the table. Therefore, when selecting count (*) from table, you only need to directly read the saved values without performing a full table scan. If the table has more read operations than write operations and does not need the support of database transactions, MyIASM is also a good choice.

Main differences:

1. MyIASM is non transaction safe, while InnoDB is transaction safe

2. The granularity of MyIASM locks is table level, while InnoDB supports row level locks

3. MyIASM supports full-text indexing, while InnoDB does not support full-text indexing

4. MyIASM is relatively simple and its efficiency is better than InnoDB. Small applications can consider using MyIASM

5. MyIASM table is saved as a file, which is more convenient for cross platform use

Application scenario:

1. MyIASM manages non transaction tables and provides high-speed storage, retrieval and full-text search capabilities. If a large number of select operations are performed in re applications, MyIASM should be selected

2. InnoDB is used for transaction processing and has the characteristics of ACID transaction support. If a large number of insert and update operations are performed in the application, InnoDB should be selected

Chapter exercise: submitting as a word document

1. Create the table person (id int, auto incremented PK, name varchar(10), sex char(2), age int), and create a log table person for the table_ Log (id int auto increment primary key, method varchar(6) mode, log_date datetime date) is used to record the operation of the table, and create a trigger to complete the monitoring log of relevant addition, deletion and modification.

2. Insert data (1, Zhang San, male, 20), (2, Li Si, male, 30), (3, Wang Wu, male, 25), (4, Zhao Liu, male, 22), (5, Wang Qi, male, 22), (6, Zhu Ba, male, - 22) into the person table and view the person_log data.

3. Create a V_ View of person. The original table is person. v_ The person does not contain an age field.

4. Create a stored procedure get_count ('wang ', @ person_count) passes in the user's last name and returns the number of users with this last name.

5. Query the person table, splice the person's id and age together, and take the alias code.

6. Query all the information in the person table and take the absolute value of age.

Reference answer:

CREATE TABLE `mydb`.`person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (10),
  `sex` CHAR(2),
  `age` INT,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `mydb`.`person_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `method` VARCHAR (6) NOT NULL,
  `log_date` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TRIGGER `person_add` BEFORE INSERT 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('add to', NOW()) ;

CREATE TRIGGER `person_update` BEFORE UPDATE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('modify', NOW()) ;

CREATE TRIGGER `person_delete` BEFORE DELETE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('delete', NOW()) ;


CREATE VIEW v_person AS
SELECT `id`,`name`,`sex` FROM `person`;


SELECT * FROM v_person;



CREATE  PROCEDURE `get_count`(IN first_name VARCHAR(5),OUT person_count INT)
BEGIN
   SELECT COUNT(*) FROM `person` WHERE `name` LIKE CONCAT(first_name,'%') INTO person_count;
    END


DELIMITER ;
SET @user_count = 0;
CALL `get_count`('king', @user_count);
SELECT @user_count;


SELECT CONCAT(id,age) `code`,`name`,sex FROM `person`;

SELECT id,NAME,sex,ABS(age) age FROM `person`;

Tags: Database MySQL

Posted on Mon, 08 Nov 2021 20:56:54 -0500 by bubazoo

Programmer Help

Built in constraints and functions of MySQL Foundation

1, Table constraint

2, Data type

3, View

4, Trigger

5, Stored procedure

6, String, number, time processing function

7, Database engine (self-study)

Hot Tags