MySql built-in constraints and functions

MySQL built-in constraints and functions

1. Table constraints

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

The primary key constraint primar 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`)
)

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`);

The 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

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) DEFAULT NULL,
  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 don't want the field to be null You can set the properties of the field to not null，When operating the database, if the data entered in this field is null，Will report an error

AUTO_INCREMENT The attribute that defines the column as self incrementing is generally used for the primary key. The value will be automatically increased by 1 after each data insertion.

PRIMARY KEY Keywords are used to define columns as primary keys. You can use multiple columns to define a primary key, which is called a composite primary key. Column keys are separated by commas

ENGINE Set up the storage engine, CHARSET Set encoding

!be careful: utf-8 And utf8mb4 The difference is utf8mb4 Compatible icon fonts. If the engine and character encoding are set during installation, they can not be specified during table creation.

2. Data type

In order to meet various business needs, mysql provides many data types. The common types are as follows:

[the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-7mndkxl8-1635783879594) (C: \ users \ admin \ appdata \ roaming \ typora \ user images \ image-20211031191909600. PNG)]

!be careful
1.stay DECIMAL(P,D) In the syntax:
P Is the precision of the number of significant digits.
P The range is 1〜65. 
D Is the number of digits after the decimal point. 
D The range of is 0~30. MySQL requirement D Less than or equal to(<=)P. 
 For example: DECIMAL(6,2)It can store up to 6 digits, with 2 decimal places; Therefore, the range of columns is from-9999.99 To 9999.99. 
    
2.char(n) and varchar(n) In square brackets n Represents the number of characters, not bytes,
    	such as CHAR(30) You 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 It is a variable length string. Its length is determined by the stored content. When it is full, it is dominated by the actual stored content.
 
3.BLOB Is a large binary object that can hold a variable amount of binary data.
   There are four kinds BLOB Type: TINYBLOB,BLOB,MEDIUMBLOB and LONGBLOB. The difference is that they can accommodate different storage ranges.
    
4.TEXT There are also 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.

1.Create a table system_user contain int id Auto increment primary key, char(10) name Not empty, varchar(255) password It is not empty and the default value is 123456, blob image Pictures, text information Introduction, tinyint sex Gender, decimal(10,2) money assets, DATETIME birthdays Birthday and use SQLYog Insert 3 test data into the table.
2.Create a table system_classroom contain int id Not empty and self increasing, varchar(50) name Not empty, and insert three classes into the table: JAVA，UI，new media
3.stay system_user Add fields to classroom_id，And add a foreign key to point to the in the class table id，At the same time for all system_user Assign your own class.

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 is the use of a 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 the view is stored in the database, and the data of the view is not stored. 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.

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 with the name auto_save_time，stay user Execution of any row of the table insert The trigger is activated and executed before the statement insert into user_log(log_time) values(now()); sentence. Let's talk about user Table for a insert Operation, you can see that the trigger is activated and the statement is executed.
    View trigger usage commands show triggers；
    There are 6 triggers before and after insertion, before and after deletion, and before and after modification, which are the same as those in the previous case before Corresponding to 
practice:
    
1.Create a table student(id,name) and student_log (id,log_time,method)，Create 3 triggers, complete 	student Records of all modification operations of the 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 in a large database system to complete specific functions. It is stored in the database and called again after the first compilation. It does not need to be compiled again. The user executes the stored procedure by specifying the name of the stored procedure 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 and time processing functions

When querying data, you need 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
BIN(decimal_number )	                    Decimal to binary
CEILING (number2 )	                    Round up
CONV(number2,from_base,to_base)	            Binary conversion
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`,BIN(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,CONV (`english`,10,16) 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), return 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), return 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); ROM_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): add 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.

Equivalent to ADDDATE(date,INTERVAL expr type)=ADDDATE(date,n); SUBDATE(date,INTERVAL expr type) = SUBDATE(date,n)

type values commonly used year, month, day, hour, minute, second, microsecond (milliseconds), week, quarter, and rarely used second_microsecond, minute_microsecond, minute_second, hour_microsecond, hour_second, hour_minute, day_microsecond, day_second, day_minute, day_hour, year_month

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

7. Exercise:

Data: import the following data and complete the corresponding query: Data download

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.The minimum wage of the employee in the query table.

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

2. Database engine

The engine provided by the database is as follows:

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

InnoDB: used for transaction processing applications. It has many features, including ACID transaction support and row level locks.

Memory: saves all data in memory. It can provide extremely fast access in the environment where you need to quickly find references and other similar data.

Two common engines are Innodb and MyIASM.

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, that is, the write operation needs to lock the whole table, so the efficiency will be lower. 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:

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 the re application, 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

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;  DELIMITER $$ USE `mydb`$$ DROP PROCEDURE IF EXISTS `get_count`$$ CREATE DEFINER=`root`@`localhost` 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 ;  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`;

Keywords: Database MySQL

Added by lajkonik86 on Mon, 01 Nov 2021 18:33:16 +0200

Programming VIP

MySql built-in constraints and functions

MySQL built-in constraints and functions

1. Table constraints

2. Data type

3. View

4. Trigger

5. Stored procedure

6. String, number and time processing functions

7. Exercise:

2. Database engine

Popular Keywords