编程知识 cdmana.com

Built in constraints and functions of MySQL Foundation (2)


The main content of this article is as follows :

One 、 Table constraints

   In order to ensure that the database is inserted according to the specified rules , Introducing table constraints , Common table constraints are
  1. Primary key constraint primary key( Not allowed to be empty , No repetition ) If it is an integer type, it is generally defined that the primary key will grow automatically auto_increment, It 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 called unique index , Used to set that the field cannot be repeated .

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

  3. Non empty constraint not null It is used to specify that this field cannot be empty. It is usually specified or added by the visual connection tool when creating a table . Can also be combined with DEFAULT In combination with , Indicates 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, you must ensure that the database engines of the two tables are the same and cannot be MYISAM, It is usually added through the visual connection tool .

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 property of the field to NOT NULL, When operating the database, if the data entered in this field is NULL , You're going to report a mistake .
  • AUTO_INCREMENT Define properties that are listed as self incrementing , Generally used for primary key , The value will be automatically incremented after each data insertion 1.
  • 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 , Columns are separated by commas .
  • ENGINE Set up the storage engine , Use show engines Commands can be viewed MySQL Supported database engine ,CHARSET Set encoding .
    Be careful :utf8 And utf8mb4 The difference is that utf8mb4 Compatible with some icon Fonts . If the engine and character encoding are set at the time of installation, you can not specify... When creating a table .

Two 、 data type

  MySQL In order to meet various business needs , Provide many data types , Common types are as follows :

  • INT        Integers        4 byte
  • BIGINT     long integer       8 byte
  • FLOAT     Single decimal        Single precision floating point values
  • DOUBLE    Double decimal        Double precision floating point value
  • DECIMAL    Small value        Yes DECIMAL(P,D)
  • DATE       Date value       YYYY-MM-DD
  • TIME       Time value       HH:MM:SS
  • DATETIME    Mix date and time values      YYYY-MM-DD HH:MM:SS
  • CHAR       Fixed length string       0-255 byte
  • VARCHAR    Variable length string       0-65535 byte
  • TEXT       Long text data       0-65535 byte
  • BLOB        Data in binary form       0-65535 byte
    Be careful :

1. stay DECIMAL(P,D) In the grammar of : P Is the precision of the number of significant digits . P The scope is 1〜65. D It means the number of digits after the decimal point . D The range is 0~30.MySQL requirement D Less than or equal to (<=)P. Such as :DECIMAL(6,2) You can store up to 6 Digit number , The number of decimal places is 2 position ; therefore , The range of columns is from -9999.99 To 9999.99.

2.char(n) and varchar(n) In brackets n Represents the number of characters , It doesn't mean the number of bytes , such as CHAR(30) You can store 30 Characters .char Is a fixed length string . Once it is declared, whether the stored value reaches this length or not , All occupy the corresponding byte length .varchar Is a variable length string , Its length depends on what is stored , When it is full, it is mainly based on the actual stored content .

3.BLOB Is a binary large object , It can accommodate a variable amount of binary data . Yes 4 Kind of BLOB type :TINYBLOB、BLOB、MEDIUMBLOB and LONGBLOB. The difference is that they can hold different storage ranges .

4.TEXT There are also 4 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 .

practice : Submit pictures during operation word

1. Create a table system_user contain int id Since the primary key ,char(10) name Not empty ,varchar(255) password Not null and the default value is 123456,blob image picture ,text information Introduce ,tinyint sex Gender ,decimal(10,2) money assets ,DATETIME birthdays Birthday , And use SQLYog Insert... Into the table 3 Bar test data .

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, The new media

3. stay system_user Add fields to classroom_id, And add a foreign key to point to... In the class table id, At the same time for all system_user Assign your own class .

Refer to the answer :

#1、 Create a table  system_user  contain  int id,char(10) name, varchar (255) password  It is not empty and the default value is  123456,blob image、text information
#tinyint sex,decimal(10,2) money. id  Not empty and self increasing . And insert 3 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  contain int id, varchar(50) name  Not empty . And insert three classes  JVAV ,UI , The new media 
create table system_classroom(
id int primary key not null  comment ' Class number ',
name varchar(50) comment ' Class name '
)
#3、 stay system_user Add  system_user , And add a foreign key to point to... In the class table id, Also for system_user Assign your own class 
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、 ... and 、 View

   A view is actually a virtual table , An incomplete or calculated virtual table exported from another table .
   What's the use of a view , When we often query some fields in a table , We can export these fields to a single view , When querying in the future, you can directly select *, Only the definition of the view is stored in the database , There is no data to store the view , 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 study : 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`;

Four 、 trigger

   A trigger is an action triggered by an event , These events include insert、delete、update sentence . When the database executes these events , The trigger will be activated to perform the corresponding operation .
   We need a watch , Here we use user surface , Used to add, delete, and modify this table , I need another one user_log surface , When the user Table operation , Will activate our trigger , Yes user_log The table does the corresponding operation .

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

   This sentence means , Create a trigger named auto_save_time, stay user Any row of the table executes insert The trigger is activated before the statement , And implement insert into user_log(log_time) values(now()); sentence . Now we're right user The table performs a insert operation , It can be seen that , Trigger activated , And executed that statement .now() Indicates that there can be no space before the current time bracket .
   View trigger commands show triggers; To delete a trigger, use the command drop trigger Trigger Name ; Triggers have 6 individual Before and after insertion , Before and after deleting , Six before and after modification , Same as in the previous case before The counterpart is after.

practice :

1. Create a table student(id,name) and student_log (id,log_time,method), establish 3 Trigger , complete student Records of all modification operations of the table .

Refer to the 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、 ... and 、 stored procedure

   stored procedure (Stored Procedure) In a large database system , A group of SQL Statements set , Stored in a database , Call again after the first compilation without recompilation , The user specifies the name of the stored procedure and gives the parameters ( If the stored procedure has parameters ) To execute it. , Stored procedure is an important object in database ; Stored procedures can contain logical control statements and Data manipulation statement , It can take parameters , Output parameters , Returns a 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'," Woman ");
insert into p_user(name,sex) values('C'," male ");

   Create stored procedure ( Look up the number of men or women , If the incoming is 0 Just 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=' Woman ' INTO user_count;
ELSE
SELECT COUNT(*) FROM p_user WHERE p_user.sex=' male ' INTO user_count;
END IF;
END 
$

Calling stored procedure

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

Look up the number of men or women , If the incoming is 0 It's just women, it's not men .

6、 ... and 、 character string 、 Numbers 、 Time processing functions

   When querying data, you need to process the found fields , Such as lowercase to uppercase , Cutting, etc . In this case, you need to use the string processing function of the database , The following is mysql Common string handling functions .

CHARSET( Field )	                             Returns the character set of the field 
CONCAT(string2  [,... ])	             Connection string 
INSTR(string ,substring )	             return substring stay string Where in , No return 0
UCASE(string2 )	                             Convert to uppercase 
LCASE(string2 )	                             Convert to lowercase 
LEFT(string2 ,length )	                     from string2 From the left side of 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 the size of two strings character by character , return 1、0 and -1
SUBSTRING(str , position  [,length ])	     from str Of position Start , take length Characters 
LTRIM(string2 ) RTRIM (string2 )            trim  Remove front-end spaces or back-end spaces 

Case study :

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 functions

Here are mysql Common digital processing functions

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

Case study :

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 ( Include specific hours, minutes and seconds )

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

Get the current date ( Does not contain 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 ( Minutes and seconds )

SELECT CURRENT_TIME(),CURTIME()

Get month

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

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

Get the function of days

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

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

Get the function of 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 return 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 return value is 0-6,0 For Monday , And so on

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

The number of days between two dates

TO_DAYS(date) date date With the default date (000 year 1 month 1 Japan ) The number of days between ;FROM_DAYS(number) The default date is after number Days after ;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), stay date Add the date after the specified time interval .

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

type Values commonly used are :year、month、day、hour、minute、second、microsecond( millisecond )、week、quarter

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

practice :( Dictation 20 minute )

data : Import the following data and complete the corresponding query , Download 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');

subject :

1. Query the number of employees without superior leaders , full name , Wages 
2. The name of the employee with bonus in the query table , Position , Wages , And bonuses 
3. Query the names of employees with superior leaders , Salary and superior leader's number 
4. The name in the query table starts with ‘S’ The names of all employees at the beginning 
5. The last character of the name in the query table is 'S' The name of the employee 
6. Query the penultimate 2 The characters are ‘E’ The name of the employee 
7. The employee's name in the query table contains ‘A’ The name of the employee 
8. The name in the query table does not start with 'K' All the information of the employee at the beginning 
9. The name in the query table does not contain ‘A’ Information about all employees 
10. Number of clerks (job  in   contain  CLERK  Of )
11. Salesman  job: SALESMAN  The highest paid employee information 
12. The number of the employee in the query table , full name , Position , Wages , And the salary is 1000~2000 Between .
13. Employees in the query table are  ACCOUNTING  department , And contain the name of the employee of the superior leader , Position , Superior leader number and department 
14. The name in the query table contains 'E', And the position is not MANAGER The employee number of , full name , Position , And wages .
15. In the query table ACCOUNTING Department or RESEARCH Number of the employee in the Department , full name , Department 
16. There is no bonus or penultimate name in the query table 2 The first letter is not T The employee number of , full name , Position and bonus 
17. Check that the salary is higher than 3000 Or the department is SALES The name of the employee , Position , Wages , Entry time and department 
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 numbers of all employees in the query table , full name , Position , Sort employees in descending order according to their numbers 
21. The Department in the query table is ACCOUNTING perhaps RESEARCH in , Names of all employees , position , Wages , Sort in ascending order according to salary 
22. Query all the data in the table , Then sort them in ascending order according to the Department , If the departments are consistent , Sort employees in descending order according to their numbers 
23. The salary in the query table is higher than 1000 Or the number of employees without superior leaders , full name , Wages , Department , The number of the superior leader , Sort in descending order according to departments , 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 employee number of , full name , Wages , Bonus , Sort in ascending order according to salary , If the salary is the same , Arrange in descending order according to the number 
25. The total number of employees in the statistical table 
26. Find out the employee with the highest salary in the table 
27. Find the employee with the largest employee number in the table 
28. The minimum wage of employees in the query table 
29. Employees who are employed on Monday in the query table 
30. In the query table, at the beginning of each month (1 Number ) New employees 

Refer to the answer :

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

2. Inquire about employee The name of the employee with bonus in the table , Position , Wages , And bonuses 
SELECT NAME,job,SALARY,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

3. Query the names of employees with superior leaders , Salary and superior leader's number 
SELECT NAME,SALARY,MANAGER FROM `employee` WHERE MANAGER IS NOT NULL

4. Inquire about employee The names in the table are marked with ‘S’ The names of all employees at the beginning 
SELECT NAME FROM `employee` WHERE NAME LIKE 'S%'

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


6. Query the penultimate 2 The characters are ‘E’ The name of the employee 
SELECT NAME FROM `employee` WHERE NAME LIKE '%E_'


7. Inquire about employee The name of the employee in the table contains ‘A’ The name of the employee 
SELECT NAME FROM `employee` WHERE NAME LIKE '%A%'

8. Inquire about employee The names in the table do not begin with 'K' All the information of the employee at the beginning 
SELECT NAME FROM `employee` WHERE NAME NOT LIKE 'K%'


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


10. Number of clerks (job  in   contain  CLERK  Of )
SELECT COUNT(0) FROM `employee` WHERE job='clerk'

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


12. The number of the employee in the query table , full name , Position , Wages , 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 the query table are  ACCOUNTING  department , And contain the name of the employee of the superior leader , Position , Superior leader number and department 
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 number of , full name , Position , And wages .
SELECT id,NAME,job,SALARY FROM `employee` WHERE NAME LIKE '%E%' AND job != 'MANAGER'

15. In the query table ACCOUNTING Department or RESEARCH Number of the employee in the Department , full name , Department 
SELECT id,NAME,dept FROM `employee` WHERE dept='ACCOUNTING' OR dept ='RESEARCH'


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

17. Check that the salary is higher than 3000 Or the department is SALES The name of the employee , Position , Wages , Entry time and department 
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 numbers of all employees in the query table , full name , Position , Sort employees in descending order according to their numbers 
SELECT id,NAME,job FROM `employee` ORDER BY id DESC


21. The Department in the query table is ACCOUNTING perhaps RESEARCH in , Names of all employees , position , Wages , Sort in ascending order according to salary 
SELECT NAME,JOB,SALARY FROM `employee` WHERE dept ='ACCOUNTING' OR dept ='RESEARCH' ORDER BY SALARY ASC


22. Query all the data in the table , Then sort them in ascending order according to the Department , If the departments are consistent , Sort employees in descending order according to their numbers 
SELECT * FROM `employee` ORDER BY dept ASC,id DESC 

23. The salary in the query table is higher than 1000 Or the number of employees without superior leaders , full name , Wages , Department , The number of the superior leader ,
 Sort in descending order according to departments , If the departments are consistent, they are 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 employee number of , full name , Wages , Bonus , Sort in ascending order according to salary , If the salary is the same , Arrange 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. The 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 the employee with the largest employee number in the table 
SELECT * FROM `employee` ORDER BY id DESC LIMIT 1

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

29. Employees who are employed on Monday in the query table .
select * from employee where WEEKDAY(HIRE_DATE) = 0

30. In the query table, at the beginning of each month (1 Number ) New employees .
select * from employee where DAYOFMONTH(HIRE_DATE) = 1

7、 ... and 、 Database engine ( self-taught )

   The engine provided by the database is as follows :

  MyISAM: default MySQL Plug in storage engine , It's in Web、 One of the most commonly used storage engines in data warehousing and other application environments .

  InnoDB: For transactional applications , It has many characteristics , Include ACID Transaction support , Provide row level locks .

  Memory: Keep all data in memory , In an environment where you need to quickly find references and other similar data , Provides extremely fast access to .

There are two common engines Innodb and MyIASM.

Innodb engine

  Innodb The engine provides the database ACID Transaction support , And implemented SQL The standard four isolation levels . The engine also provides row level locks and foreign key constraints , It is designed to handle large capacity database systems , It's actually based on MySQL The complete database system in the background ,MySQL Runtime Innodb Buffer pool will be established in memory , For buffering data and indexes . But the engine doesn't support FULLTEXT Index of type , And it doesn't hold the number of rows in 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 of the smaller granularity of the lock , Write operations do not lock the entire table , So when concurrency is high , Use Innodb The engine will improve efficiency . But using row level locks is not absolute either , If you are executing a SQL When the sentence is MySQL Can't determine the range to scan ,InnoDB The watch also locks the whole watch .

MyIASM engine

  MyIASM yes MySQL Default engine , But it doesn't provide support for database transactions , Row level locks and foreign keys are also not supported , So when INSERT( Insert ) or UPDATE ( to update ) Write on data requires locking the entire table , It's less efficient . But and Innodb Different ,MyIASM The number of rows in the table is stored in , therefore SELECT COUNT(*) FROM TABLE Only the saved values need to be read directly without scanning the whole table . If the read operation of the table is far more than the write operation and does not need the support of database transaction , that MyIASM It's also a good choice .

The main difference :

1、MyIASM It's non transactional security , and InnoDB It's business safety

2、MyIASM The granularity of locks is table level , and InnoDB Row level locking is supported

3、MyIASM Support full text type index , and InnoDB Full text indexing is not supported

4、MyIASM Relatively simple , Better than InnoDB, Small applications can consider using MyIASM

5、MyIASM Save the table as a file , Cross platform use is more convenient

Application scenarios :

  1、MyIASM Manage non transaction tables , Provides high-speed storage and retrieval as well as full-text search capabilities , If a large number of... Are executed in the re application select operation , You should choose MyIASM

  2、InnoDB For transaction processing , have ACID Transaction support and other features , If you execute a lot of insert and update operation , You should choose InnoDB

Chapter exercises : With word Submit in the form of documents

1. Create table person (id int Since the primary key , name varchar(10), sex char(2), age int), And create a log table for this table person_log (id int Since the primary key ,method varchar(6) The way ,log_date datetime Time date ) Used to record the operation of the table , At the same time, a trigger is created to complete the monitoring log of relevant addition, deletion and modification .

2. towards person Insert data into the table (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) And look at person_log The data of .

3. Create a v_person The view of , The original table is person.v_person It doesn't contain age Field .

4. Create a stored procedure get_count(‘ king ’,@person_count) Pass in the user's last name , Returns the number of users with this last name .

5. Inquire about person surface , take person Of id and age Spliced together, alias code.

6. Inquire about person All the information in the table , Also on age Take the absolute value .

Refer to the 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`;

版权声明
本文为[faramita_ of_ mine]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/11/20211109074137153u.html

Scroll to Top