MySQL learning notes-data definition table constraints, paging method summary, mysql learning notes

MySQL learning notes-data definition table constraints, paging method summary, mysql learning notes

This article describes the data definition table constraints and paging methods of MySQL learning notes. We will share this with you for your reference. The details are as follows:

1. primary key

Feature: a primary key is a constraint used to uniquely identify a record. A table can have only one primary key at most, and cannot be empty or duplicate.

create table user1(id int primary key,name varchar(32));mysql> insert into user1 values(1,'hb');Query OK, 1 row affected (0.10 sec)mysql> insert into user1 values(1,'hb');ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into user1 (name) values('hb');ERROR 1364 (HY000): Field 'id' doesn't have a default value

2. auto_increament auto-Increment

mysql> create table user2(id int primary key auto_increment,name varchar(34));mysql> insert into user2 (name ) values ("name1");Query OK, 1 row affected (0.09 sec)mysql> insert into user2 (name ) values ("name2");Query OK, 1 row affected (0.05 sec)mysql> insert into user2 (name ) values ("name3");Query OK, 1 row affected (0.13 sec)mysql> select * from user2;+----+-------+| id | name |+----+-------+| 1 | name1 || 2 | name2 || 3 | name3 |+----+-------+

3. unique constraint

Feature: A column value in a table cannot be repeated. Duplicate NULL values can be added.

create table user3(id int primary key auto_increment,name varchar(34) unique);mysql> create table user3(id int primary key auto_increment,name varchar(34) unique);Query OK, 0 rows affected (0.39 sec)mysql> insert into user3 (name ) values ("name3");Query OK, 1 row affected (0.11 sec)mysql> insert into user3 (name ) values ("name3");ERROR 1062 (23000): Duplicate entry 'name3' for key 'name'

Null can be inserted and multiple

mysql> insert into user3 (name ) values (null);Query OK, 1 row affected (0.12 sec)mysql> insert into user3 (name ) values (null);Query OK, 1 row affected (0.12 sec)mysql> select * from user3;+----+-------+| id | name |+----+-------+| 3 | NULL || 4 | NULL || 1 | name3 |+----+-------+

4. not null

Mysql table columns can be null by default. If a column is not allowed to be empty, not null can be used.

create table user4 (id int primary key auto_increment,name varchar(32) not null);mysql> insert into user4 (name) values(null);ERROR 1048 (23000): Column 'name' cannot be null

5. foreign key

Theoretically, the primary table is created first, and then the slave table is created.

Employee table:

create table dept(id int primary key , name varchar(32));

Department table:

create table emp(id int primary key ,name varchar(32),deptid int,constraint myforeignkey foreign key(deptid) references dept(id));mysql> select * from dept;+----+-------+| id | name |+----+-------+| 1 | name1 |+----+-------+1 row in set (0.00 sec)mysql> insert into emp values(1,'aaa',1);Query OK, 1 row affected (0.22 sec)mysql> insert into emp values(1,'aaa',2);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into emp values(1,'aaa',null);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into emp values(2,'aaa',null);Query OK, 1 row affected (0.13 sec)mysql> select * from emp;+----+------+--------+| id | name | deptid |+----+------+--------+| 1 | aaa |   1 || 2 | aaa |  NULL |+----+------+--------+2 rows in set (0.00 sec)

Summary:

① Foreign keys can only point to the primary column or unique of the primary table
② The Data Type of the foreign key should be consistent with the column type it points
③ Foreign key value: NULL or a value pointing to a column
④ Foreign keys can point to the primary key column or unique of the table.

Mysql does not support check

create table user99(age int check(age>13));mysql> create table user99(age int check(age>13));Query OK, 0 rows affected (0.19 sec)mysql> insert into user99 values(99);Query OK, 1 row affected (0.04 sec)mysql> select * from user99;+------+| age |+------+|  99 |+------+

Mysql Paging

Basic Syntax:

Select * from indicates that the where condition limit is obtained from the number of items and the number of items retrieved
Mysql extracts data from 0th records.

Mysql> select * from student; + ------ + -------- + --------- + ------ + | id | name | chinese | english | math | + ------ + -------- + --------- + ------ + | 1 | Zhang Xiaoming | 89 | 78 | 90 | 2 | Li Jin | 67 | 98 | 56 | 3 | Wang Wu | 87 | 78 | 77 | 4 | Li Yi | 88 | 98 | 90 | 5 | li laicai | 82 | 84 | 67 | 6 | Zhang jinbao | 55 | 85 | 45 | 7 | Zhang Xiaoming | 75 | 65 | 30 | + ------ + -------- + --------- + ------ + 7 rows in set (0.05 sec) mysql> select * from student limit 2, 2; + ------ + --------- + ------ + | id | name | chinese | english | math | + ------ + --------- + ------ + | 3 | Wang Wu | 87 | 78 | 77 | 4 | Li Yi | 88 | 98 | 90 | + ------ + --------- + ------ + 2 rows in set (0.00 sec)

Sorting by Chinese scores, investigating 3rd to 5th

Mysql> select * from student order by chinese desc limit 3, 2; + ------ + -------- + --------- + ------ + | id | name | chinese | english | math | + ------ + -------- + --------- + ------ + | 5 | Li laicai | 82 | 84 | 67 | 7 | Zhang Xiaoming | 75 | 65 | 30 | + ------ + -------- + --------- + ------ + 2 rows in set (0.00 sec)

Extended, pagination: pageNow, pageSize

Select * from indicates the where condition [group... Having... Order by…] The number of limit entries.
Select * from indicates the where condition [group... Having... Order by…] Limit (pageNow-1) * pageSize, pageSize