32 please send the last of all employees in the employees table_ Name and first_name is spliced together as name, separated by a space in the middle

select concat(last_name,' ',first_name) from employees;

Parsing, two string connection methods: mysql CONCAT(str1,str2,...) and CONCAT_WS(separator,str1,str2,…)

33 table building

create table actor(
    actor_id smallint(5) not null ,
    first_name varchar(45) not null comment 'full name',
    last_name varchar(45) not null comment 'surname',
    last_update date not null comment 'date',
    primary key(actor_id)
)engine=innodb,default charset=utf8;

34 batch insert data

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

35 ignore existing data when inserting data

insert ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33')

36 create a new table from the old table

create table actor_name as select first_name,last_name from actor;

37 create index

Yes, first_name create unique index uniq_idx_firstname, yes, last_name creates a common index idx_lastname

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

Resolution:
Index classification:

1. General index
2. unique index
   -Primary key index: primary key
   -Unique index: unique
   The primary key index is only for the primary key column. There is only one column, and the column value is not empty and duplicate. The unique index unique can have multiple columns, and the column value can be empty but cannot be repeated

How to create an index:

1. Create an index when creating a table create table table table name (ID, int, index (ID))
2. Create an index after creating a table create index index index name on table name (field name) or alter table name add index index name (field name)

38 creating views

Create a view for the actor table_ name_ View, only first_name and last_name two columns, and rename the two columns, first_name is first_name_v，last_ Change name to last_name_v

create view actor_name_view 
as select first_name as first_name_v,last_name as last_name_v from actor;

39 query mysql with forced index

select * from salaries
force index (idx_emp_no)
where emp_no=10005;

40 add a new column

Now at last_ A new column named create is added after update_ Date, the type is datetime, NOT NULL, and the default value is' 2020-10-01 00:00:00 '

alter table actor add create_date datetime not null default '2020-10-01 00:00:00';

Skip question 41 here. The topic is trigger

42 delete some records in the table

delete from titles_test
where id not in (select * from (select min(id) from titles_test group by emp_no) as s);

Parsing: mysql does not allow the same table to be queried and deleted at the same time, so here you need to name the table another table, query it as an intermediate table, and then query the original table titles_test to delete

43 all to_ All updates with date 9999-01-01 are NULL

update titles_test set to_date=null ,from_date='2001-01-01';

44 id=5 and EMP_ Replace the row data of no = 10001 with id=5 and emp_no=10005, other data remain unchanged

update titles_test set emp_no = replace(emp_no,10001,10005) where id = 5

45 add titles_ Change the name of the test table to titles_ two thousand and seventeen

alter table titles_test rename to titles_2017

Create foreign key constraints

Create a foreign key constraint on the audit table, and its emp_no corresponds to employees_ Primary key id of test table

alter table audit
add constraint foreign key (emp_no)
references employees_test(id);

For foreign key constraints, see the following two blogs
MySQL foreign key constraints
MySQL database - foreign key constraints
MySQL FOREIGN KEY constraint is used to establish a link between the data of two tables. It can be one or more columns. A table can have one or more foreign keys. The FOREIGN KEY corresponds to referential integrity. The FOREIGN KEY of a table can be null. If it is not null, the value of each FOREIGN KEY must be equal to a value of the primary key in another table. The main function of foreign keys is to maintain the consistency and integrity of data

A foreign key is a field of a table. It is not the primary key of this table, but it corresponds to the primary key of another table. After defining a foreign key, it is not allowed to delete rows with association in another table.

For example, in the main table student, the fields are student id, student name, student gender, and the primary key is student id
From the course table, the fields are subject number, student sid and subject score. The primary key is subject number and the foreign key is student sid. The foreign key here points to the primary key student id of the main table student

The method of specifying foreign keys is specified when creating tables

create table course(
    cid smallint(5) primary key,
    sid varchar(45) not null comment 'student sid',
    score varchar(45) not null comment 'Subject achievement',
    foreign key(sid) references student(id)
)engine=innodb,default charset=utf8;

In this way, a foreign key constraint is created for course, so that sid becomes a foreign key and points to the primary key of the student table - student id

In addition, you can add foreign key constraints to alter as you did in the first question answer

48 increase the current salary of all employees receiving bonus by 10%

update salaries as s inner join emp_bonus as e on s.emp_no = e.emp_no
set salary = salary*1.1
where to_date='9999-01-01'

50 last of all employees in the employees table_ Name and first_name is connected via (')

select concat(RTrim(last_name),"'", LTrim(first_name)) from employees

Here, the spaces on the right and left are removed by RTrim and LTrim respectively

51 find the number of comma occurrences in the string

select id,(length(string)-length(replace(string,',',''))) as cnt
from strings;

Replace the comma with an empty string (not a space), and then subtract the length of the string before and after the replacement to calculate the number of occurrences of the comma

52 according to first_name outputs the last two letters in ascending order

select first_name
from employees
order by right(first_name,2)

Parsing: the right function can return a string of a specified length from the far right, and the left function returns a string of a specified length from the far left

53 according to dept_no to summarize EMP belonging to the same department_ No connect by comma

select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no

https://blog.csdn.net/harry5508/article/details/102481017
group_concat() function can connect the values in the same group generated by group by and return a string result.

Syntax: Group_ Concat ([distinct] field to be connected [order by sort field ASC / desc] [separator 'separator'])
Duplicate values can be excluded by using distinct
You can use the order by clause to sort the values to be connected first and then connect them. If not specified, the default is ascending asc
If a comma is not specified, the default value is a string separator

Note: when the data is too large, group_ If concat exceeds the default value of 1024, it will be truncated. Group_ The data queried by concat will be incomplete

54 average wage

select (sum(salary)-max(salary)-min(salary)) / (count(1)-2) as avg_salary
from salaries
where to_date='9999-01-01'

55 paging query the employees table, one page every five rows, and return the data on page 2

select *
from employees
limit 5,5

Limit x, Y: skip x pieces of data and read y pieces of data

57 use the keyword exists to find all information about employees who are not assigned a specific department

select *
from employees
where not exists
(
    select emp_no
    from dept_emp
    where employees.emp_no=dept_emp.emp_no
)

59 get information about employees with bonuses

select e1.emp_no,
		e1.first_name,
		e1.last_name,
		e2.btype,s1.salary,
		case
			when e2.btype=1 then s1.salary*0.1
			when e2.btype=2 then s1.salary*0.2
			else s1.salary*0.3
		end as bonus
from employees as e1,emp_bonus as e2,salaries as s1
where e1.emp_no=e2.emp_no and e1.emp_no=s1.emp_no and s1.to_date='9999-01-01';

60 statistics of cumulative and running of salary_ total

select emp_no,
        salary,
        sum(salary) over (order by emp_no) as running_total
from salaries    
where to_date='9999-01-01';

The windowing function can only use order by instead of partition by. It is sorted globally by default