计导作业20221220 SQL（创建表时，注意主码、候选码的数据类型 ）

题目：

代码：

CREATE TABLE user (
    uid INT NOT NULL AUTO_INCREMENT,
   usersname VARCHAR(50),
    passwd INT,
    phone VARCHAR(50),
    email VARCHAR(50),
     PRIMARY KEY(uid)
 );

 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(1,"admin",123456,'15373675695','admin@163.com');
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(2,"user2",123456,'13655787767','user2@163.com');
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(3,"user3",123456,'NULL','user3@163.com');
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(4,"user4",123456,'17688909876','user4@163.com');
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(5,"user5",123456,'NULL','user5@163.com');
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(6,"user6",123456,'NULL','user6@163.com');
 
 INSERT INTO user(uid,usersname,passwd,phone,email)
 VALUES(7,"徐清风",88886666,'15839214556','soft@163.com');

SELECT usersname FROM user 
WHERE phone="NULL";

SELECT usersname FROM user
WHERE usersname LIKE "user_";

DELETE FROM user WHERE usersname="user5";

 

 注意点1：存储phone,email用字符串VARCHAR（）

否则会出现phone那一列会出现147483647，应该是INT(整型)可以存储的最大数字

注意点2：候选码存储时是字符串形式存储的，在进行 查询SELECT、删除DELETE等操作时要在加引号

例：

SELECT usersname FROM user 
WHERE phone="NULL";

SELECT usersname FROM user
WHERE usersname LIKE "user_";

DELETE FROM user WHERE usersname="user5";