MySQL第二章

CREATE TABLE student1(
   studentNo INT(4) PRIMARY KEY COMMENT'学号',
   loginPwd VARCHAR(20) NOT NULL COMMENT'密码',
   studentName VARCHAR(50) NOT NULL COMMENT'姓名',
   sex CHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',
   gradeID INT(4) UNSIGNED COMMENT'年级编号',
   phone VARCHAR(50) COMMENT'电话',
   address VARCHAR(255) DEFAULT '地址不详' COMMENT'地址',
   bornDate DATETIME COMMENT'出生日期',
   email VARCHAR(60) COMMENT'邮箱',
   identityCard VARCHAR(18) NOT NULL UNIQUE KEY COMMENT'身份证号码'
) COMMENT='学生表'
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10000','123','郭靖','男','1','13645667783','天津市河西区','1990-09-08 00:00:00',NULL,'11');
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10001','123','李文才','男','1','13645667890','地址不详','1994-04-12 00:00:00',NULL,'22');
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10002','123','李斯文','男','1','13645556793','河南洛阳','1993-07-23 00:00:00',NULL,'44');
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10003','123','张萍','女','1','13642345112','地址不详','1995-06-10 00:00:00',NULL,'77');
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10004','123','韩秋洁','女','1','13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,'55');
INSERT INTO `student1` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10005','123','张秋丽','女','1','13567893246','北京市东城区','1994-01-17 00:00:00',NULL,'99');
#新表存在将会报错
CREATE TABLE phoneList(
SELECT studentName,phone
  FROM student1
)
SELECT * FROM 'phoneList'
#新表phoneList必须存在 新表数据类型和原表一样
INSERT INTO phoneList
SELECT
studentName,phone
FROM student1
#更新数据记录
UPDATE student1 SET sex='女';
UPDATE student1 SET gradeID=2,loginPwd='123456'
WHERE studentNo>10003
#删除数据记录  有条件限制
DELETE FROM `phoneList`
WHERE studentName='郭靖'
#truncate  重置  删除整张表
DELETE FROM student1 WHERE studentName='张萍';
TRUNCATE TABLE student1 
CREATE TABLE new_subject(
      subjectNo INT(4) PRIMARY KEY COMMENT'科目编号',
      subjectName VARCHAR(50) COMMENT'科目名',
      subjectcount INT UNSIGNED COMMENT'课时数',
      subjectgrade INT COMMENT'学期编号'
)COMMENT='科目表'
INSERT INTO `new_subject`(`subjectNo`,`subjectName`,`subjectcount`,`subjectgrade`) VALUES('1','logic Java','220','1'),('2','HTML','160','1'),('3','Java OOP','230','2')

CREATE TABLE student(
   studentNo INT(4) PRIMARY KEY COMMENT'学生编号',
   studentName VARCHAR(50) NOT NULL COMMENT'姓名',
   address VARCHAR(255) DEFAULT '地址不详' COMMENT'地址',
   grade INT(4) UNSIGNED COMMENT'班号'
) COMMENT='学生表'
INSERT INTO `student` (`studentNo`, `studentName`, `address`,`grade`) VALUES('001','张明全','湖南长沙','201'),('002','李菲','湖北宜昌','202'),('003','于寄谦','甘肃天水','204'),
                                                                            ('004','刘国正','山东菏泽','201'),('005','周接轮','台湾新竹','204'),('006','巩小妹','香港龙湾','203'),
                                                                            ('007','巩大妹','香港龙湾','203'),('008','张明敏','北京顺义','202'),('009','茅十八','四川棉阳','204'),
                                                                            ('010','罗林光','陕西临潼','202'),('011','司马坡','新疆碦什','201')
                                                                           
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`
FROM `student1`
WHERE `gradeId` = 1
ORDER BY `bornDate` DESC
#order by 升序  order by   desc  降序

SELECT * FROM `student1`;

SELECT `studentNo` AS '学号',`studentName`AS '姓名',`address`+`bornDate`
FROM `student1`
WHERE address<>'地址不详' AND `gradeId`=1
ORDER BY `bornDate` DESC

SELECT *,'北京信息中心' AS '学校名称' FROM `student1`
WHERE email IS NULL