sql快速入门教程1简介

sql教程2数据库和SQL简介

sql教程3 MySQL介绍

sql教程4 MySQL SELECT

sql教程5插入和删除及更新

sql教程6分组、转义与模糊匹配、正则表达式

sql教程7函数

sql教程8 NULL ALTER DROP RENAME LIMIT

NULL

image.png

• NULL不是数据类型 -这意味着它不会被识别为"int"，"date"或任何其他定义的数据类型。
• 涉及NULL的 算术运算始终返回NULL ，例如69 + NULL = NULL。
• 所有聚合函数 仅影响没有NULL值的行 。

mysql> SELECT COUNT(contact_number)  FROM members;+-----------------------+| COUNT(contact_number) |+-----------------------+|                    12 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT * FROM members WHERE contact_number IS NOT NULL;+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+| membership_number | full_names          | gender | date_of_birth | physical_address       | postal_address | contact_number | email                 |+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+|                 1 | Janet Jones         | Female | 1980-07-21    | First Street Plot No 4 | Private Bag    | 0759 253 542   | janetjones@yagoo.cm   ||                 3 | Robert Phil         | Male   | 1989-07-12    | 3rd Street 34          | NULL           | 12345          | rm@tstreet.com        ||                 5 | Leonard Hofstadter  | Male   | NULL          | Woodcrest              | NULL           | 845738767      | NULL                  ||                 6 | Sheldon Cooper      | Male   | NULL          | Woodcrest              | NULL           | 0976736763     | NULL                  ||                 7 | Rajesh Koothrappali | Male   | NULL          | Woodcrest              | NULL           | 0938867763     | NULL                  ||                 8 | Leslie Winkle       | Male   | 1984-02-14    | Woodcrest              | NULL           | 0987636553     | NULL                  ||                 9 | Howard Wolowitz     | Male   | 1981-08-24    | SouthPark              | P.O. Box 4563  | 0987786553     | lwolowitz[at]email.me ||                10 | Leonard Hofstadter  | Male   | NULL          | Woodcrest              | NULL           | 845738767      | NULL                  ||                11 | Sheldon Cooper      | Male   | NULL          | Woodcrest              | NULL           | 0976736763     | NULL                  ||                12 | Rajesh Koothrappali | Male   | NULL          | Woodcrest              | NULL           | 0938867763     | NULL                  ||                13 | Leslie Winkle       | Male   | 1984-02-14    | Woodcrest              | NULL           | 0987636553     | NULL                  ||                20 | Howard Wolowitz     | Male   | 1981-08-24    | SouthPark              | P.O. Box 4563  | 0987786553     | lwolowitz[at]email.me |+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+12 rows in set (0.00 sec)mysql> SELECT * FROM members;+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+| membership_number | full_names          | gender | date_of_birth | physical_address       | postal_address | contact_number | email                 |+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+|                 1 | Janet Jones         | Female | 1980-07-21    | First Street Plot No 4 | Private Bag    | 0759 253 542   | janetjones@yagoo.cm   ||                 2 | Janet Smith Jones   | Female | 1980-06-23    | Melrose 123            | NULL           | NULL           | jj@fstreet.com        ||                 3 | Robert Phil         | Male   | 1989-07-12    | 3rd Street 34          | NULL           | 12345          | rm@tstreet.com        ||                 4 | Gloria Williams     | Female | 1984-02-14    | 2nd Street 23          | NULL           | NULL           | NULL                  ||                 5 | Leonard Hofstadter  | Male   | NULL          | Woodcrest              | NULL           | 845738767      | NULL                  ||                 6 | Sheldon Cooper      | Male   | NULL          | Woodcrest              | NULL           | 0976736763     | NULL                  ||                 7 | Rajesh Koothrappali | Male   | NULL          | Woodcrest              | NULL           | 0938867763     | NULL                  ||                 8 | Leslie Winkle       | Male   | 1984-02-14    | Woodcrest              | NULL           | 0987636553     | NULL                  ||                 9 | Howard Wolowitz     | Male   | 1981-08-24    | SouthPark              | P.O. Box 4563  | 0987786553     | lwolowitz[at]email.me ||                10 | Leonard Hofstadter  | Male   | NULL          | Woodcrest              | NULL           | 845738767      | NULL                  ||                11 | Sheldon Cooper      | Male   | NULL          | Woodcrest              | NULL           | 0976736763     | NULL                  ||                12 | Rajesh Koothrappali | Male   | NULL          | Woodcrest              | NULL           | 0938867763     | NULL                  ||                13 | Leslie Winkle       | Male   | 1984-02-14    | Woodcrest              | NULL           | 0987636553     | NULL                  ||                20 | Howard Wolowitz     | Male   | 1981-08-24    | SouthPark              | P.O. Box 4563  | 0987786553     | lwolowitz[at]email.me |+-------------------+---------------------+--------+---------------+------------------------+----------------+----------------+-----------------------+14 rows in set (0.00 sec)mysql> SELECT 5 =5;+------+| 5 =5 |+------+|    1 |+------+1 row in set (0.00 sec)mysql> SELECT NULL = NULL;+-------------+| NULL = NULL |+-------------+|        NULL |+-------------+1 row in set (0.00 sec)mysql> SELECT 5 IS NULL;+-----------+| 5 IS NULL |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> SELECT NULL IS NULL;+--------------+| NULL IS NULL |+--------------+|            1 |+--------------+1 row in set (0.00 sec)

ALTER DROP RENAME

mysql> ALTER TABLE members ADD COLUMN credit_card_number VARCHAR(25);Query OK, 0 rows affected (0.59 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM members;+--------------------+--------------+------+-----+---------+----------------+| Field              | Type         | Null | Key | Default | Extra          |+--------------------+--------------+------+-----+---------+----------------+| membership_number  | int(11)      | NO   | PRI | NULL    | auto_increment || full_names         | varchar(350) | NO   |     | NULL    |                || gender             | varchar(6)   | YES  |     | NULL    |                || date_of_birth      | date         | YES  |     | NULL    |                || physical_address   | varchar(255) | YES  |     | NULL    |                || postal_address     | varchar(255) | YES  |     | NULL    |                || contact_number     | varchar(75)  | YES  |     | NULL    |                || email              | varchar(255) | YES  |     | NULL    |                || credit_card_number | varchar(25)  | YES  |     | NULL    |                |+--------------------+--------------+------+-----+---------+----------------+9 rows in set (0.00 sec)mysql> ALTER TABLE members DROP COLUMN credit_card_number;Query OK, 0 rows affected (0.55 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW COLUMNS FROM members;+-------------------+--------------+------+-----+---------+----------------+| Field             | Type         | Null | Key | Default | Extra          |+-------------------+--------------+------+-----+---------+----------------+| membership_number | int(11)      | NO   | PRI | NULL    | auto_increment || full_names        | varchar(350) | NO   |     | NULL    |                || gender            | varchar(6)   | YES  |     | NULL    |                || date_of_birth     | date         | YES  |     | NULL    |                || physical_address  | varchar(255) | YES  |     | NULL    |                || postal_address    | varchar(255) | YES  |     | NULL    |                || contact_number    | varchar(75)  | YES  |     | NULL    |                || email             | varchar(255) | YES  |     | NULL    |                |+-------------------+--------------+------+-----+---------+----------------+8 rows in set (0.00 sec)mysql> mysql> DROP TABLE categories_archive;Query OK, 0 rows affected (0.16 sec)mysql> RENAME TABLE movierentals TO movie_rentals;Query OK, 0 rows affected (0.12 sec)mysql> RENAME TABLE movie_rentals TO movierentals;Query OK, 0 rows affected (0.10 sec)mysql> ALTER TABLE members CHANGE COLUMN full_names fullname char(250) NOT NULL;Query OK, 14 rows affected (0.71 sec)Records: 14  Duplicates: 0  Warnings: 0mysql> ALTER TABLE members MODIFY fullname char(50) NOT NULL;Query OK, 14 rows affected (0.66 sec)Records: 14  Duplicates: 0  Warnings: 0mysql> ALTER TABLE  members ADD  date_of_registration date NULL AFTER  date_of_birth;Query OK, 0 rows affected (0.61 sec)Records: 0  Duplicates: 0  Warnings: 0

LIMIT

mysql> SELECT *  FROM members LIMIT 3;+-------------------+-------------------+--------+---------------+----------------------+------------------------+----------------+----------------+---------------------+| membership_number | fullname          | gender | date_of_birth | date_of_registration | physical_address       | postal_address | contact_number | email               |+-------------------+-------------------+--------+---------------+----------------------+------------------------+----------------+----------------+---------------------+|                 1 | Janet Jones       | Female | 1980-07-21    | NULL                 | First Street Plot No 4 | Private Bag    | 0759 253 542   | janetjones@yagoo.cm ||                 2 | Janet Smith Jones | Female | 1980-06-23    | NULL                 | Melrose 123            | NULL           | NULL           | jj@fstreet.com      ||                 3 | Robert Phil       | Male   | 1989-07-12    | NULL                 | 3rd Street 34          | NULL           | 12345          | rm@tstreet.com      |+-------------------+-------------------+--------+---------------+----------------------+------------------------+----------------+----------------+---------------------+3 rows in set (0.00 sec)mysql> SELECT * FROM members LIMIT 1, 2;+-------------------+-------------------+--------+---------------+----------------------+------------------+----------------+----------------+----------------+| membership_number | fullname          | gender | date_of_birth | date_of_registration | physical_address | postal_address | contact_number | email          |+-------------------+-------------------+--------+---------------+----------------------+------------------+----------------+----------------+----------------+|                 2 | Janet Smith Jones | Female | 1980-06-23    | NULL                 | Melrose 123      | NULL           | NULL           | jj@fstreet.com ||                 3 | Robert Phil       | Male   | 1989-07-12    | NULL                 | 3rd Street 34    | NULL           | 12345          | rm@tstreet.com |+-------------------+-------------------+--------+---------------+----------------------+------------------+----------------+----------------+----------------+2 rows in set (0.00 sec)

原著是一个有趣的人,若有侵权,请通知删除