Mysql and mysql download

Mysql and mysql download
-- Modify a field

Alter table emp MODIFY dept_id int;

-- Delete a field

Alter table emp drop COLUMN dept_id;

It was a warm-up before, and you will feel that you can learn a lot with this teacher. You have to work hard!

The preceding two statements are highly versatile. Is also supported in oracle syntax.

Exercise today:

Main foreign key, multi-table join, internal connection external link-left outer link right outer link all outer link

And some general syntax rules. It doesn't feel very special. Yes.

Mark a point that is not allowed.

Point 1:

In this example, both varchar and char represent strings, and varchar can be a variable-length string. An unchangeable char length string.

Both have their own advantages. If char is not variable, the query speed can be faster.

Varchar can be variable in length, which saves space, but the query speed decreases accordingly.

Example:

Create table stu (

Name char (10 );

);

Create table stu (

Name varchar (10 );

);

Insert into stu values ('nihao ');

The char type still occupies 10 characters.

However, varchar only occupies 5 characters.

The result is char, which is a waste of space, but the query efficiency is higher, because

In varchar, the bitwise n I h a o is a character and a character for comparison, so the query efficiency is very low.

Modify can only be used to change field attributes.

Change can change the field name or field type and field constraints.

Exercise questions:

I,

1. Create an emp table:

Emp_id int, employee ID

Emp_name varchar (20), employee name

Emp_age int, employee age

Emp_address varchar (50), employee address

Emp_date varchar (20) Start Time

2. Delete the table (because

The standard name is: Database suffix name: db table name suffix name is tb

The first part of the field name is the table name, and the last part is the link between the attribute name with an underscore. Example

Emp_tb_id

Emp_tb_name)

3. Add the table again. The table name is emp_tb.

4. For ease of operation, change the name of the table to emp.

5. Create a dept table:

Dept_id int,

Dept_name varchar (20)

6. Add a dept_id field for the emp table.

7. modify this field to varchar (20 ). Use the modify keyword

8. Change the attribute value of this field to the int type. Use the change keyword

Differences:

Modify can only be used to change field attributes.

Change can change the field name or field type and field constraints.

9. * delete a field to delete the entry time column.

It is best to use alter table emp drop column name. This column can be written or not written in mysql, but it is very important in other Syntax structures. So it is best to remember this keyword.

10. insert data:

11. modify data:

(1) Change the address of an employee with an empty address to Beijing, and (2) set the Department number to 2.

12. delete data:

II,

What are our common constraints? Write at least six. What are row-level constraints and column-level constraints? Please classify these six constraints accordingly. What is the significance of constraints?

III,

What are the similarities and differences between Char and varchar?

4. How do I add constraints?

-- 1

Create table emp (

Emp_id int,

Emp_name varchar (20 ),

Emp_age int,

Emp_address varchar (20 ),

Emp_date date

);

-- 2

Drop table emp;

-- 3

Create table emp_tb (

Emp_id int,

Emp_name varchar (20 ),

Emp_age int,

Emp_address varchar (20 ),

Emp_date date

);

-- 4

Alter table emp_tb rename emp;

-- 5

Create table dept (

Dept_id int,

Dept_name varchar (20)

);

-- 6

Alter table emp add column dept_id int;

-- 7

Alter table emp modify dept_id varchar (20 );

-- 8

Alter table emp change dept_id int;

-- 9

Alter table emp drop column emp_date;

-- 10

SELECT * from emp;

Insert into emp (emp_id, emp_name, emp_age, emp_address) values (1, 'zhang san', 23, 'beijing ');

Insert into emp (emp_id, emp_name, emp_age) values (2, 'wangw', 45)

-- 11

Update emp set emp_address = 'beijing' where emp_address is null;

Update emp set dept_id = 2;

-- 12

Delete from emp;

Database constraint operations: to maintain data integrity:

We know the following keywords:

Not null, primary key, unique foreign key default CHECK (not in this mysql syntax)

They can be divided into row-level and column-level constraints.

The difference between the two is that row-level constraints mean that there is a constraint between the row and the row, that is, the value of one row is a and the value of another row is a, and cannot be a again. This is a row-level constraint.

A column-level constraint is a constraint relative to itself. For example, it cannot be empty, that is, it is a constraint that only has a relationship with itself.

According to this definition: We can classify the above six keywords as follows:

Row-level constraint: primary key; unique

Column-level constraints: not null; foreign key; default; CHECK

There are two ways to add constraints, one of which we are familiar with is to add them directly after the attribute.

Example:

Create table student (

Stuid int primary key,

Stuname varchar (20)

);

Second, add the table in the last row.

Create table student (

Stuid int,

Stuname varchar (20 ),

Constraint pk_student_id primary key (stuid)

);

Four parts are used to modify thousands of brackets!

The constraint specifies the name constraint type (column name) that must be known by the system)

Constraint pk_emp_tb3_address default 'beijing' (emp_address) it is not easy to add the default value constraint at the end of the statement.

Database Error 150? Solution

Constraint unique (emp_address) is also feasible.

How to add foreign key constraints:

Well, you can add them when you are too drunk. You cannot add them in the created file.

More consolidation in this place

Constraint Addition Method

How to add at the end of a table

And the method of adding data outside the table.

Take notes here. I think I will !!!

Emp_age Between A and B

Equivalent to: emp_age> = A and emp_age <= B

Select * from emp where id in (20, 8, 4, 2)

Query all information in emp when the id is 20, 8, 4, or 2.

Reverse query select * from emp where id not in (20, 8, 4, 2)

Query all the information of employees whose id is not equal to 20 8 4 2 in the emp table.

There is a difference Between and in.

Between and is a range

In is a defined range.

Fuzzy search

Select * from emp where name like 'a % ';

In the emp table, find the name starting with name.

Select * from emp where name like '%'

Search for names ending with a in the emp table

Select * from emp where name like '% a %'

Find the name of a in the emp table.

Select * from emp where name like '_ a %'

In the emp table, find the name in the format of "space a blank and cool ".

In other words, "_" is a placeholder.

"%" Is a wildcard

It is easy to get through our understanding:

The query order of Select from where is:

From where select

Order:

Select * from TABLE where COLUMN1 =? Order by COLUMN2 desc, COLUMN3

Asc

Query all the information in the table where column 1 is equal to the question mark in descending order of COLUMN2. When Column 2 is equal, COLUMN3 is used for sorting.

According to this rule, the order of select from where order by four is

From where select order.

The following table is provided:

Create table emp (

Emp_id int,

Emp_name varchar (20 ),

Salary int // monthly Salary

);

Now the question is: sort the emp query results by annual salary:

Select *, salary * 12 annual salary from emp where 1 = 1 order by annual salary;

Aggregate functions:

Select count (*) from emp; queries the total number of records

Select max (emp_age) from emp; query the maximum age of an employee

Select min (emp_age) from emp; query the minimum age of an employee

Select avg (sal) from emp; query the average employee salary

Select sum (sal), avg (sal) max (sal) from emp; query the average monthly salary and maximum salary in the emp table

!!!

If a select statement is followed by a group query, only group fields or grouping functions are displayed.

For example, the following statement:

Select avg (sal), max (sal), dept_id from emp group by dept_id;

Query the average salary and maximum salary as well as the Department number from the employee table and group by the Department number.

It is considered unsafe in other databases. Of course, the person who initially designed this database thinks that I want to show the relationship between an average wage and the maximum wage for a certain person. Maybe this way, he will allow such a situation.

In fact, * can be displayed in mysql. However, a strange data set may appear, and such statements are not allowed in other databases. Therefore, you must remember that if you have a group query, only group fields or grouping functions can be displayed after select.

!!!

Function compute data is grouped data when aggregation and grouping query are used together.

Example:

Select avg (sal), max (sal), dept_id from emp group by dept_id having avg (sal)> 1800

From the emp table, query the maximum wage Department Number of the average wage and group it by Department number. The average wage is greater than 1800 of the data.

When will the Group be made? This requires more exercises for analysis.

Questions about grouping query:

We want to select the highest salary for different departments: Then we find that in the second department, the highest salary is 3300. If we remove the salary column at this time.

You will find that Michael got the highest salary. Such a syntax structure is allowed in mysql. However, it is not allowed in other database systems. Other databases think This is not safe.

Therefore, this is a syntax feature.

So this is the notes we recorded:

If a group query exists, the display conditions of the group query can only be: grouping field or grouping function (aggregate function)

That is, select can only be followed by grouping fields, grouping functions, or aggregate functions.

So the following sentence is very pleasing to the eye and understandable:

The front is the department number followed by the highest salary of the Department.

When Aggregate functions (grouping functions) are used together, function compute data is grouped data.

Aggregate functions are grouping functions.

So we can sort the order.

From where group by having select order

Seeing our sorting, you find having is in front of select, so it is not possible to have the "average wage" entry in having, which is illegal.

That is to say, this is also a feature of mysql. This is not allowed in other databases.

And clearly understand the meaning of this syntax:

Find the average and highest salaries in different departments.

We can improve the corresponding department table to get the complete desired data:

That method does not exist, or an alias should be created. Because the program does not plan to calculate twice since we have obtained the relevant data before.

All the data used later should have already appeared !!!

Okay, my head is a bit messy, mainly because it's too messy. I 've been making a quarrel, or I 've been doing the same thing all morning. It's really possible that I'm physically exhausted.

Let's compile a scenario:

If you want to hold a fraternity, you must find employees of the same age in different departments.

The most complete query order:

Select from where group by having order by limit

Execution sequence of the seven members:

From where group by having select order by limit

The database query and method is absolutely common in compliance with this specification.

Add another

The display part of the grouping query can only contain grouping conditions or grouping functions.

Internal External: