MySql数据and高级查询
1.CREATE TABLE grade(
stuID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
stuName VARCHAR(32),
stuAge INT
)
stuID INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
stuName VARCHAR(32),
stuAge INT
)
##修改表名
ALTER TABLE grade RENAME Grade1;
ALTER TABLE grade RENAME Grade1;
##伪造记录
INSERT INTO grade1(stuName,stuAge)
VALUES('你是狗',22)
INSERT INTO grade1(stuName,stuAge)
VALUES('你是狗',22)
##查询
SELECT * FROM grade1;
##添加
ALTER TABLE grade1 ADD stuAddress VARCHAR(32) NOT NULL;
ALTER TABLE grade1 ADD stuAddress VARCHAR(32) NOT NULL;
##修改
ALTER TABLE grade1 CHANGE stuName stuuserName CHAR(10) NOT NULL
ALTER TABLE grade1 CHANGE stuName stuuserName CHAR(10) NOT NULL
##删除
ALTER TABLE grade1 DROP pwd;
ALTER TABLE grade1 DROP pwd;
##删除自增列
ALTER TABLE grade1 MODIFY INT;
ALTER TABLE grade1 MODIFY INT;
##删除主键约束
ALTER TABLE grade1 DROP PRIMARY KEY;
ALTER TABLE grade1 DROP PRIMARY KEY;
##删除外键约束
##添加主键
ALTER TABLE grade1 ADD CONSTRAINT pk_grade PRIMARY KEY (stuID);
##添加唯一约束
##alter table grade1 add constraint stuuserName unique key()
#添加唯一约束
ALTER TABLE grade1
ADD CONSTRAINT UK_GNAME UNIQUE KEY(stuuserName);
##alter table grade1 add constraint stuuserName unique key()
#添加唯一约束
ALTER TABLE grade1
ADD CONSTRAINT UK_GNAME UNIQUE KEY(stuuserName);
#删除唯一约束
ALTER TABLE grade1
DROP INDEX UK_GNAME;
ALTER TABLE grade1
DROP INDEX UK_GNAME;
##添加默认值约束
ALTER TABLE grade1
ALTER COLUMN stuAddress SET DEFAULT '北京'
ALTER TABLE grade1
ALTER COLUMN stuAddress SET DEFAULT '北京'
##删除默认值约束
ALTER TABLE grade1
ALTER COLUMN stuAddress DROP DEFAULT
ALTER TABLE grade1
ALTER COLUMN stuAddress DROP DEFAULT
##添加>=18约束
ALTER TABLE grade1 ADD CONSTRAINT ck_stuAge CHANGE(stuAge>=18)
##3.验证stuage字段的检查约束是否成功
INSERT INTO student2(stuName,stuGradeid,stuage)
VALUES('张三', 1,17)
##4.删除检查约束是不成功的
ALTER TABLE student2
DROP CONSTRAINT ck_stuage
INSERT INTO student2(stuName,stuGradeid,stuage)
VALUES('张三', 1,17)
##4.删除检查约束是不成功的
ALTER TABLE student2
DROP CONSTRAINT ck_stuage
##删除外键约束
ALTER TABLE student2
DROP FOREIGN KEY FK_Grade_Student_GradeId
DROP FOREIGN KEY FK_Grade_Student_GradeId
##
ALTER TABLE student2 ADD CONSTRAINT FK_Grade_Student_GradeId FOREIGN KEY (stuGradeid) REFERENCES `grade`(gid)
ALTER TABLE student2 ADD CONSTRAINT FK_Grade_Student_GradeId FOREIGN KEY (stuGradeid) REFERENCES `grade`(gid)
![](https://images2018.cnblogs.com/blog/1238669/201806/1238669-20180604165624097-1342983340.png)
![](https://images2018.cnblogs.com/blog/1238669/201806/1238669-20180604153711111-130689259.png)
4.Limit:实现分页
分页的目的:为了加快网站对数据的查询(检索)速度,我们引入了分页的概念'
SELECT * FROM result LIMIT 5 ##返回前5条记录
SELECT * FROM result LIMIT 5,10 ##返回6-15条记录
5.聚合函数
6.字符串函数
7.日期函数
示例:
#对象数组 List<Student> #聚合函数 sum() max() min() avg() count() SELECT COUNT(1) AS 总记录数 FROM easybuy_product ##聚合函数一般在什么场景下去使用?? ##计算 统计分析 # 字符串函数 SELECT LOWER('蔡国鑫刚才米粒的眼神并没有俘获我的幼小的心CGX') SELECT CONCAT('Hello','World') ##insert SELECT INSERT('周杰伦是残疾人',5,2,'好') ##郭世超 C# 中SubString(1) string str="郭世超'你坏'"; 结果是 世超'你坏' ## Substring(1,2) 第二个参数:是length,就是截取几个长度 ##dual 伪表 SELECT SUBSTRING('明天会有一场全国运动,高考',7,4) FROM DUAL ##日期函数 SELECT CURDATE() FROM DUAL ## 2018-6-6 19:27:53 SELECT NOW() FROM DUAL ##50多周 22周 SELECT WEEK(NOW()) FROM DUAL ##两个日期做差,看看某个人活了多久??? SELECT DATEDIFF(NOW(),'1998-06-26') FROM DUAL ###人生不过3万天 SELECT ADDDATE(NOW(),INTERVAL 3 MONTH) FROM DUAL ##数学函数 ceil 向上取整 floor 向下取整 SELECT CEIL(1.001) FROM DUAL SELECT FLOOR(1.9999) FROM DUAL
8.子查询:
子查询就是一个查询中又包含了另一个查询,典型的语法结构(),一般来说,子查询会使用()扩起来,并且小括号内的检索结果作为外层查询的条件存在
伪代码如下:
Select * from A表
Where 列名=(select 列名 from B表)
SELECT stuName,stuBirthday FROM student WHERE stuBirthday> ( SELECT stuBirthday FROM student WHERE stuno=3 )
一下为in子查询:
#查询“数学”课程至少一次考试刚好等于60分的学生信息
##学生姓名
SELECT * FROM result
SELECT stuName FROM student
WHERE stuNo IN
(
SELECT Studentno FROM result
WHERE subjectid=
(
SELECT subjectid FROM SUBJECT
WHERE subjectname='数学'
) AND studentresult=60
)
一下为 not in 子查询:
##案例6:查询最近一次未参加 数学 考试的学生名单 ##1.检索到参加了考试的学生学号 ##select * from studentno where studentno not in (1......) SELECT * FROM student WHERE stuno NOT IN ( SELECT studentno FROM result WHERE subjectid= ( SELECT subjectid FROM SUBJECT WHERE subjectname='数学' ) AND examdate= ( SELECT MAX(examdate) FROM result WHERE subjectid= ( SELECT subjectid FROM SUBJECT WHERE subjectname='数学' ) ) ) AND stugradeid= ( SELECT gradeid FROM SUBJECT WHERE subjectname='数学' )
truncate 和delete区别:
##truncate 和delete区别 mdf ldf ndf ##delete from product ---->update ##1.delete 可以删除部分记录 truncate 删除的是全部记录,不能有where ##2.delete 从原则上来说,他是可以恢复部分数据的,记录日志,truncate 不留痕迹 ##3.delete 删除速度慢,truncate 删除速度快 ##4.delete 删除所有记录,再Insert,编号是接着上次的自增编号继续, ##trucate编号从起始的开始编号
Exisit和Not Exisits子查询:
#检测是否有S2的学生记录 #如果存在,从成绩表中查询S2科目的学员成绩信息 SELECT studentno,subjectid,studentresult,examdate FROM result WHERE EXISTS ( SELECT * FROM student WHERE stuGradeid= ( SELECT gid FROM grade WHERE gname='S1' ) ) AND subjectid IN ( SELECT subjectid FROM SUBJECT WHERE gradeid= ( SELECT gradeid FROM grade WHERE gname='S1' ) )
Select * from TableA a where Not Exists (Select * from TableB b where a.id=b.id and a.name=b.name);