Mysql Learning Notes Data definition table constraints, pagination method summary _mysql

This article describes the MySQL learning Notes data definition table constraints, pagination method. Share to everyone for your reference, specific as follows:

1. Primary KEY Primary

Feature: A primary key is a constraint that uniquely identifies a record, a table can have at most one primary key, cannot be empty, and cannot be duplicated

CREATE TABLE user1 (ID int primary key,name varchar);
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>
inserts into user1 (name) VALUES (' HB ');
ERROR 1364 (HY000): Field ' id ' doesn ' t have a default value

2. Auto_increament Self-growth

Mysql> CREATE TABLE user2 (ID int primary key auto_increment,name varchar);
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 UNIQUE Constraint

Feature: A column value of a table cannot be repeated, you can add duplicate null

CREATE table User3 (ID int primary KEY auto_increment,name varchar unique);
Mysql> CREATE TABLE User3 (ID int primary key auto_increment,name varchar () 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 '

Allows null to be inserted and can be 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

The MySQL table columns can be null by default, and NOT NULL if you do not allow a column to be empty

CREATE TABLE user4 (ID int primary key auto_increment,name varchar () not NULL);
mysql> INSERT into USER4 (name) values (null);
ERROR 1048 (23000): Column ' name ' cannot be null

5. Foreign key foreign keys

In theory, the main table is established first and then the table

Employee table:

CREATE TABLE dept (ID Int primary key, name varchar (32));

Department Table:

CREATE TABLE EMP (
ID int primary KEY,
name varchar (),
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> inserts 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)

Summarize:

① foreign key can only point to the main table or unique
The data type of the ② foreign key should be the same as the column type it points to
Value of the ③ foreign key: NULL or point to a value that exists in the column
The ④ foreign key can point to the primary key column of this table or the unique

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 ();
Query OK, 1 row affected (0.04 sec)
mysql> select * from User99;
+------+
| age |
+------+
|  |
+------+

MySQL Paging

Basic syntax:

SELECT * from indicates where condition limit from the first few, take out several
MySQL is starting with the No. 0 article to fetch the data

Mysql> select * from student;
+------+--------+---------+---------+------+
| id |  name  | chinese | 中文版
| +------+--------+---------+---------+------+
|  1 | Zhang Xiaoming   |   |   About Us |  The |
|  2 | Li Jin    |   About Us |   About Us |  A |
|  3 | Harry    |   |   About Us |  |
|  4 | Li Yi Chan   |   About Us |   About Us |  The |
|  5 | Li Wealth    |   About Us |   |  A |
|  6 | Zhang Jinbao   |   |   About Us |  |
|  7 | Zhang Xiaoming   |   A |   About Us |  |
+------+--------+---------+---------+------+
7 rows in Set (0.05 sec)
mysql> select * FROM student limit 2,2 ;
+------+------+---------+---------+------+
| id |  name | chinese | 中文版
| +------+------+---------+---------+------+
|  3 | Harry   |   |   About Us |  |
|  4 | Li Yi Chan  |   About Us |   About Us |  About Us |
+------+------+---------+---------+------+
2 rows in Set (0.00 sec)

According to the Chinese grade, investigate 3rd to 5th

Mysql> SELECT * FROM student, Chinese desc limit 3,2;
+------+--------+---------+---------+------+
| id |  name  | chinese | 中文版
| +------+--------+---------+---------+------+
|  5 | Li Wealth    |   About Us |   |  A |
|  7 | Zhang Xiaoming   |   A |   About Us |  |
+------+--------+---------+---------+------+
2 rows in Set (0.00 sec)

Extensions, paging: Pagenow, PageSize

SELECT * from indicates where condition [group by ...] ORDER by ...] Limit from the first few, take out a few
SELECT * from indicates where condition [group by ...] ORDER by ...] Limit (pageNow-1) *pagesize, pageSize

More information about MySQL interested readers can view the site topics: "MySQL Index operation skills Summary", "MySQL Log Operation skills Encyclopedia", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and " MySQL common function Big Summary "

I hope this article will help you with the MySQL database meter.