sql教程8 NULL ALTER DROP RENAME LI
2020-01-17 本文已影响0人
python测试开发
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: 0
mysql> 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: 0
mysql> 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: 0
mysql> ALTER TABLE members MODIFY fullname char(50) NOT NULL;
Query OK, 14 rows affected (0.66 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> 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)
