sql教程8 NULL ALTER DROP RENAME LI

作者: python测试开发 | 来源:发表于2020-01-17 06:37 被阅读0次

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)

sql教程8 NULL ALTER DROP RENAME LI
sql快速入门教程1简介 sql教程2数据库和SQL简介 sql教程3 MySQL介绍 sql教程4 MySQL ...
SQL指令操作篇
SQL: Structured Query Language DOL: create / drop /alter ...
Oracle vs PostgreSQL，研发注意事项（2）-D
Oracle执行DDL语句如CREATE, DROP, RENAME, or ALTER时，会隐式提交事务；PG在...
标准SQL
SQL语句及其种类 DDL: Definition CREATE DROP ALTER DML: Manipula...
SQL语句基础
SQL语句 SQL分类: DDL:数据定义语句 create,alter,drop... DML:数据操作语句 i...
mysql---SQL语言说明
SQL语句： DDL 数据定义语言create, drop, alter DML 数据操纵语言insert, ...
MySQL学习笔记---MySQL开发技巧
SQL语句分类 DDL：数据定义语言 --- CREATE、ALTER、DROP、TRUNCATETPL：事务处理...
MySQL
sql 分类 sql 基本语法 DDL（数据定义语言create drop alter）数据表操作数据类型字...
标准SQL知识梳理
SQL动词 SQL功能动词数据定义CREATE,DROP,ALTER数据查询SELECT数据操纵INSERT,UP...
SQL Server 2005常用命令
SQL分类： DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(S...

sql教程8 NULL ALTER DROP RENAME LI

NULL

ALTER DROP RENAME

LIMIT

相关文章

sql教程8 NULL ALTER DROP RENAME LI

SQL指令操作篇

Oracle vs PostgreSQL，研发注意事项（2）-D

标准SQL

SQL语句基础

mysql---SQL语言说明

MySQL学习笔记---MySQL开发技巧

MySQL

标准SQL知识梳理

SQL Server 2005常用命令

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读