limit

limit action

Extract part of the query result set. It is usually used in paging queries

How does limit work?

Full usage: limit startIndex, length
		startIndex Is the starting subscript, length Is the length.
		The starting subscript starts at 0.

Default usage: limit 5; This is the top five.

Take out the top 5 employees in descending salary order?

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 5; //Top 5

	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 0,5;

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| KING  | 5000.00 |
	| SCOTT | 3000.00 |
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

Note: in mysql, limit is executed after order by!!!!!!

Take out the employees with salary ranking in [3-5]?

select 
		ename,sal
	from
		emp
	order by
		sal desc
	limit
		2, 3;
	
	//2 indicates that the starting position starts from subscript 2, which is the third record.
	//3 indicates the length.

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

Take out the employees with salary ranking in [5-9]?

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit
		4, 5;

	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| BLAKE  | 2850.00 |
	| CLARK  | 2450.00 |
	| ALLEN  | 1600.00 |
	| TURNER | 1500.00 |
	| MILLER | 1300.00 |
	+--------+---------+

paging

3 records per page
	Page 1: limit 0,3		[0 1 2]
	Page 2: limit 3,3		[3 4 5]
	Page 3: limit 6,3		[6 7 8]
	Page 4: limit 9,3		[9 10 11]

Display per page pageSize Records
	The first pageNo Page: limit (pageNo - 1) * pageSize  , pageSize

summary

	select 
		...
	from
		...
	where
		...
	group by
		...
	having
		...
	order by
		...
	limit
		...
	
	Execution order?
		1.from
		2.where
		3.group by
		4.having
		5.select
		6.order by
		7.limit..

Table creation

Syntax format of table creation

create table Table name(
		Field name 1 data type, 
		Field name 2 data type, 
		Field name 3 data type
	);

	Table name: it is suggested to t_ perhaps tbl_At first, it is readable. See the name and know the meaning.
	Field name: see the meaning of the name.
	Both table and field names are identifiers.

About data types in mysql?

For many data types, we only need to master some common data types.

Create a student table

Student number, name, age, gender, email address

create table t_student(
		no int,
		name varchar(32),
		sex char(1),
		age int(3),
		email varchar(255)
	);

Delete table

		drop table t_student; // An error will be reported when this table does not exist!

		// If this table exists, delete it
		drop table if exists t_student;

insert

insert data

Syntax format:
		insert into Table name(Field name 1,Field name 2,Field name 3...) values(Value 1,Value 2,Value 3);

		Note: field names and values should correspond one by one. What is one-to-one correspondence?
			The quantity should correspond. The data type should correspond.
	
	insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
	insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

	insert into t_student(no) values(3);

	+------+----------+------+------+------------------+
	| no   | name     | sex  | age  | email            |
	+------+----------+------+------+------------------+
	|    1 | zhangsan | m    |   20 | zhangsan@123.com |
	|    2 | lisi     | f    |   20 | lisi@123.com     |
	|    3 | NULL     | NULL | NULL | NULL             |
	+------+----------+------+------+------------------+
	
	
	insert into t_student(name) values('wangwu');

	+------+----------+------+------+------------------+
	| no   | name     | sex  | age  | email            |
	+------+----------+------+------+------------------+
	|    1 | zhangsan | m    |   20 | zhangsan@123.com |
	|    2 | lisi     | f    |   20 | lisi@123.com     |
	|    3 | NULL     | NULL | NULL | NULL             |
	| NULL | wangwu   | NULL | NULL | NULL             |
	+------+----------+------+------+------------------+
	
	be careful: insert If the statement is executed successfully, there must be one more record.
	If no value is specified for other fields, the default value is NULL. 

insert statement inserts multiple records at a time

insert into t_user(id,name,birth,create_time) values
		(1,'zs','1980-10-11',now()), 
		(2,'lisi','1981-10-11',now()),
		(3,'wangwu','1982-10-11',now());

		Syntax: insert into t_user(Field name 1,Field name 2) values(),(),(),();

	mysql> select * from t_user;
	+------+--------+------------+---------------------+
	| id   | name   | birth      | create_time         |
	+------+--------+------------+---------------------+
	|    1 | zs     | 1980-10-11 | 2020-03-19 09:37:01 |
	|    2 | lisi   | 1981-10-11 | 2020-03-19 09:37:01 |
	|    3 | wangwu | 1982-10-11 | 2020-03-19 09:37:01 |
	+------+--------+------------+---------------------+

Array formatting

Number formatting: format

select ename,format(sal, '$999,999') as sal from emp;

			+--------+-------+
			| ename  | sal   |
			+--------+-------+
			| SMITH  | 800   |
			| ALLEN  | 1,600 |
			| WARD   | 1,250 |
			| JONES  | 2,975 |
			| MARTIN | 1,250 |
			| BLAKE  | 2,850 |
			| CLARK  | 2,450 |
			| SCOTT  | 3,000 |
			| KING   | 5,000 |
			| TURNER | 1,500 |
			| ADAMS  | 1,100 |
			| JAMES  | 950   |
			| FORD   | 3,000 |
			| MILLER | 1,300 |
			+--------+-------+

Insert date

str_to_date: converts the string varchar type to date type

date_format: converts the date type to a varchar string type with a certain format.

The difference between date and datetime

Date is a short date: only the date information is included.
datetime is a long date: including the information of year, month, day, hour, minute and second.

	drop table if exists t_user;
	create table t_user(
		id int,
		name varchar(32),
		birth date,
		create_time datetime
	);


	id Is an integer
	name Is a string
	birth It's a short date
	create_time Is the creation time of this record: long date type

Default format of mysql short date:% Y-%m-%d
mysql long date default format:% Y -% m -% d% H:% I:% s

stay mysql How to get the current time of the system?
		now() Function, and the obtained time has: hour, minute and second information!!!! yes datetime Type.
	
		insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

Modify update

Syntax format:
	update Table name set Field name 1=Value 1,Field name 2=Value 2,Field name 3=Value 3... where condition;

	Note: no restrictions will cause all data to be updated.

	update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
	+------+----------+------------+---------------------+
	| id   | name     | birth      | create_time         |
	+------+----------+------------+---------------------+
	|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
	|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
	+------+----------+------------+---------------------+

Delete data delete

Syntax format

delete from condition table name;

Note: if there are no conditions, all the data in the whole table will be deleted!

	delete from t_user where id = 2;

	insert into t_user(id) values(2);

	delete from t_user; // Delete all!

Principle of delete statement deleting data

The data in the table has been deleted, but the real storage space of this data on the hard disk will not be released!!!
The disadvantage of this deletion is that the deletion efficiency is relatively low.
The advantage of this deletion is that it supports rollback, and you can recover the data if you regret!!!

Quickly delete data in a table

//Delete Dept_ Data in bak table
delete from dept_bak; //This method of deleting data is relatively slow.

How does the truncate statement delete data?
This deletion efficiency is relatively high. The table is truncated at one time and physically deleted.
Disadvantage of this deletion: rollback is not supported.
The advantage of this deletion: fast.

Usage: truncate table dept_bak; (This operation belongs to DDL Operation.)

The big table is very large, with hundreds of millions of records
When deleting, use delete. It may take 1 hour to delete! Low efficiency.
You can choose to use truncate to delete the data in the table. It only takes less than a second for the deletion to end. High efficiency.
However, before using truncate, you must carefully ask the customer if you really want to delete it, and warn that it cannot be recovered after deletion

Quick create table

mysql> create table emp2 as select * from emp;

Principle:
Create a new query result as a table!!!!!
This can complete the quick copy of the table!!!!
The table is created, and the data in the table also exists!!!

create table mytable as select empno,ename from emp where job = 'MANAGER';

Delete table operation

drop table Table name; // This is not to delete the data in the table, it is to delete the table.

constraint

What are constraints

English word corresponding to constraint: constraint
When creating a table, we can add some constraints to the fields in the table to ensure the integrity and effectiveness of the data in the table!!!

The function of constraints is to ensure that the data in the table is valid!!

What are the constraints

Non NULL constraint: not null
Uniqueness constraint: unique
Primary key constraint: primary key (PK for short)
Foreign key constraint: foreign key (FK for short)
Check constraint: check (not supported by mysql, supported by oracle)

Here we focus on four constraints:
not null
unique
primary key
foreign key

Non NULL constraint: not null

The field of a non NULL constraint not null constraint cannot be null

create table t_vip(
		id int,
		name varchar(255) not null  // not null, only column level constraints, no table level constraints!
	);

Uniqueness constraint: unique

The field of uniqueness constraint unique constraint cannot be duplicate, but it can be NULL

create table t_vip(
		id int,
		name varchar(255) unique,
		email varchar(255)
	);

	insert into t_vip(id) values(4);
	insert into t_vip(id) values(5);
	
	+------+----------+------------------+
	| id   | name     | email            |
	+------+----------+------------------+
	|    1 | zhangsan | zhangsan@123.com |
	|    2 | lisi     | lisi@123.com     |
	|    3 | wangwu   | wangwu@123.com   |
	|    4 | NULL     | NULL             |
	|    5 | NULL     | NULL             |
	+------+----------+------------------+
	
	name Although the field is unique Constraints, but can be NULL. 

Requirement: the name and email fields are unique when combined

			drop table if exists t_vip;
			create table t_vip(
				id int,
				name varchar(255),
				email varchar(255),
				unique(name,email) // The constraint is not added after the column. This constraint is called table level constraint.
			);
			insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
			insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
			select * from t_vip;

			name and email The two fields are unique when combined!!!
			insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
			ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'//report errors

When to use table level constraints?
When you need to combine multiple fields to add a constraint, you need to use table level constraints.

Union of unique and not null

		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255) not null unique
		);

		mysql> desc t_vip;
		+-------+--------------+------+-----+---------+-------+
		| Field | Type         | Null | Key | Default | Extra |
		+-------+--------------+------+-----+---------+-------+
		| id    | int(11)      | YES  |     | NULL    |       |
		| name  | varchar(255) | NO   | PRI | NULL    |       |
		+-------+--------------+------+-----+---------+-------+

		//In mysql, if a field is constrained by not null and unique at the same time,
		//This field automatically becomes the primary key field. (Note: it's different in oracle!)

Primary key constraint

Terms related to primary key constraints

Primary key constraint: a constraint.
Primary key field: a primary key constraint is added to this field. Such a field is called a primary key field
Primary key value: each value in the primary key field is called a primary key value.

What is a primary key

The primary key value is the unique identification of each row of records.
The primary key value is the ID number of each row!!!

Remember: any table should have a primary key. Without a primary key, the table is invalid!!

Characteristics of primary key

not null + unique (the primary key value cannot be NULL and cannot be repeated!)

Add a primary key constraint to a table

create table t_vip(
			id int primary key,  //Column level constraint
			name varchar(255)
		);
		insert into t_vip(id,name) values(1,'zhangsan');
		insert into t_vip(id,name) values(2,'lisi');

		//Error: cannot repeat
		insert into t_vip(id,name) values(2,'wangwu');
		ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

		//Error: cannot be NULL
		insert into t_vip(name) values('zhaoliu');
		ERROR 1364 (HY000): Field 'id' doesn't have a default value

Recommended primary key values:
int, bigint, char and other types.

It is not recommended to use: varchar as the primary key. The primary key value is generally a number and is usually of fixed length!

Primary keys: in addition to single primary keys and composite primary keys, they can also be classified in this way?
Natural primary key: the primary key value is a natural number and has nothing to do with business.
Business primary key: the primary key value is closely related to the business. For example, take the bank card account number as the primary key value. This is the business primary key!

Do you use more business primary keys or more natural primary keys in actual development?
Naturally, primary keys are used more often, because as long as they are not repeated, they do not need to be meaningful.
The business primary key is not good, because once the primary key is linked to the business, when the business changes,
It may affect the primary key value, so the business primary key is not recommended. Try to use natural primary keys.

stay mysql Among them, there is a mechanism that can help us automatically maintain a primary key value?
		drop table if exists t_vip;
		create table t_vip(
			id int primary key auto_increment, //auto_increment means self increment, starting from 1 and increasing by 1!
			name varchar(255)
		);
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		select * from t_vip;

		+----+----------+
		| id | name     |
		+----+----------+
		|  1 | zhangsan |
		|  2 | zhangsan |
		|  3 | zhangsan |
		|  4 | zhangsan |
		|  5 | zhangsan |
		|  6 | zhangsan |
		|  7 | zhangsan |
		|  8 | zhangsan |
		+----+----------+