mysql速学

#创建一个数据库
CREATE DATABASE db_myw_test;


#使用这个数据库
USE db_myw_test;


#创建表格
CREATE TABLE student1(
sno INT PRIMARY KEY AUTO_INCREMENT NOT NULL ,#primary key 主键  auto_incerment 自增长 not null 非空
sname VARCHAR(4) NOT NULL   DEFAULT '地址', #default 非空
sage DATE NOT NULL ,
ssex VARCHAR(1)  
) COMMENT '学生表';
#查看表
SHOW TABLES;
#查看表的定义
DESC student1;
#删除表
DROP TABLE IF EXISTS student1;  #if exists 可以理解为如果存在     如果存在student1这张表，则删除这张表




#修改表名
ALTER TABLE student RENAME student1;


#添加字段   字段即表示的表的列。算是专业的说法  添加学生telephone列
ALTER TABLE student1 ADD telephone VARCHAR(12) UNIQUE KEY; #唯一约束   UNIQUE KEY 


#修改字段
ALTER TABLE student1 CHANGE telephone tel VARCHAR(11) NOT NULL UNIQUE KEY;


#删除字段
ALTER TABLE student1 DROP tel;


#HELP ALTER TABLE;  这句获取表帮助的文件在sqlyog中无法使用 


#添加主键约束  
#创建新表
CREATE TABLE course(
#并没有 严格使用数据类型 请对这方面自己细化
cno INT(3) COMMENT '课程编号',
cname VARCHAR(10) COMMENT '课程名称',
tno INT(3) COMMENT '授课老师'

);
#未该表添加主键
ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY course(cno);


#查看属性  desc course; 主键已添加


#添加外键约束
#创建表 grade 学生的年级
CREATE TABLE grade(
gradeId INT(2) COMMENT '年级编号',
gradeName VARCHAR(4) COMMENT '年级编号'
);
#为学生表添加字段 gradeId


ALTER TABLE student1 ADD gradeId INT(2); #  select * from student1   这是后面的  测试用


#将student1表中的 gradeId 与 grade表中的gradeId 添加外键关联   这次添加的逻辑是  学生表中的年级编号必须在grade表中出现存在
#alter table student1 add constraint fk_student_grade foreign key (gradeId) references grade (gradeId);    直接执行这句话竟然添加失败   why？
#innodb才支持外键 myisam不支持    可是我的是innodb啊    好吧  这块我不懂   但默认的是innodb  不是这个原因   


#先删除grade 表  上面的代码直接复制下来  方便




DROP TABLE IF EXISTS grade;
#再创建
CREATE TABLE grade(
gradeId INT(2) PRIMARY KEY COMMENT '年级编号',
gradeName VARCHAR(4) COMMENT '年级编号'
);
#再添加
ALTER TABLE student1 ADD CONSTRAINT fk_student_grade FOREIGN KEY (gradeId) REFERENCES grade (gradeId); 
#执行成功  ，经查  设置为主表外键的从表的列，必须为从表的主键   至于唯一键什么别的可以不可以 遇见了再测试
#  这样 第一章就结束了   。以上内容按照北大青鸟 java数据库编程计数为索引。   开始第二章   如果看到的人有什么点评的  ，给我评论哦  ，谢谢
#————————————————————————————————————————————————————————————————————————————
#下面就是重头戏  增删改查了   
#dml插入数据
INSERT INTO grade (gradeId,gradeName) VALUES (1,'语文');
#插入多条
INSERT INTO grade (gradeId,gradeName) VALUES (2,'数学'),(3,'英语'); #最讨厌英语
#快速插入
INSERT INTO grade VALUES (4,'历史'),(5,'化学'),(6,'地理'),(7,'物理');#金刚葫芦娃
#插入新表     
CREATE TABLE grade2 (SELECT gradeId FROM grade);#这货是啥   创建+查询     查询到的数据插入到新表


#dml更新数据
UPDATE grade SET gradeName = '天文' WHERE gradeName='地理';  #把地理换成天文


#删除数据
#这个语句静然出错了   delete * from grade where id=7;#删了物理 
#删除语句不用带* 的  ，默认删除的就是一条数据
#delete  from grade where id=7;   还错  没有id  汗
DELETE  FROM grade WHERE gradeId=7; #看你还错?       删除成功
#强力删除  
#truncate table  grade ;   #呵呵   有外键的删不掉  
TRUNCATE TABLE  grade2 ; #早就看你不顺眼了  死去吧   删除成功 请注意   这不是删除表   这是清空表   用了这个  ，你的表就跟刚创建好的一样
#查询
SELECT * FROM grade;
SELECT * FROM grade WHERE gradeid<5;
SELECT gradeId FROM grade WHERE gradeid<5;
SELECT * FROM grade WHERE gradeid<5 ORDER BY gradeId DESC;  #我降序查
SELECT * FROM grade WHERE gradeid<5 ORDER BY gradeId ASC;  #我升序查
SELECT * FROM grade WHERE gradeid IS NULL;#我查空的
SELECT gradeId AS '编号' ,gradeName AS '名称','一高'AS '学校' FROM grade;  #还可以这样搞  有木有
#函数
SELECT COUNT(gradeId) FROM grade;
SELECT AVG(gradeId) FROM grade;
SELECT MAX(gradeId) FROM grade;
SELECT MIN(gradeId) FROM grade;
SELECT SUM(gradeId) FROM grade;


SELECT CONCAT(gradeId,gradeName) FROM grade;
SELECT INSERT(gradeName,2,4,'新的内容') FROM grade;
INSERT INTO grade (gradeId,gradeName) VALUES (9,'VmyD');


SELECT LOWER(gradeName) FROM grade;
SELECT UPPER(gradeName) FROM grade;
SELECT SUBSTRING(gradeName,2,2) FROM grade WHERE gradeId='8' OR gradeId='9';


SELECT CURDATE();#获取日期
SELECT CURTIME();#获取时间
SELECT NOW();#获取时间日期
SELECT WEEK(NOW());#获取第几周
SELECT YEAR(NOW());#获取年
SELECT HOUR(NOW());#获取小时
SELECT MINUTE(NOW());#获取分钟
SELECT DATEDIFF(NOW(),'2020-10-18');#获取两个时间的天数
SELECT ADDDATE(NOW(),10);#获取10天后的时间


SELECT CEIL(2.3);#向上取整
SELECT FLOOR(2.3);#向下取整
SELECT RAND(2);#随机数
SELECT RAND();#随机数


#上面演示过where orderby 现在加入limit


#添加一些学生的信息  便于演示     引入【【【【【from... where...group by... having.... select ... order by... limit】】】】】


#突然发现一个错误    我嘞个去    
#
# TRUNCATE TABLE  grade ; 还是删不掉


#MD 我把外键给你删了
ALTER TABLE grade DROP FOREIGN KEY yonghuziliao_ibfk_1;


SHOW CREATE TABLE grade;#看看这个表的信息 
SHOW CREATE TABLE student1;
/*
CREATE TABLE `grade` (
  `gradeId` int(2) NOT NULL COMMENT '年级编号',
  `gradeName` varchar(4) DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`gradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `student1` (
  `sno` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(4) NOT NULL DEFAULT '地址',
  `sage` date NOT NULL,
  `ssex` varchar(1) DEFAULT NULL,
  `gradeId` int(2) DEFAULT NULL,
  PRIMARY KEY (`sno`),
  KEY `fk_student_grade` (`gradeId`),
  CONSTRAINT `fk_student_grade` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`gradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'


*/


#删除外键
ALTER TABLE student1 DROP FOREIGN KEY fk_student_grade;
#外键没有了    看明白   从表是主表的外键  删除要从主表删。查看名字也要在主表中找  。
TRUNCATE TABLE  grade ;#终于成功了  
#添外键
ALTER TABLE student1 ADD CONSTRAINT fk_student_grade FOREIGN KEY (gradeId) REFERENCES grade (gradeId); 


INSERT INTO grade (gradeId,gradeName) VALUES (1,'一年级'),(2,'二年级'),(3,'三年级'),(4,'四年级'),(5,'五年级');#呵呵  知错能改
INSERT INTO student1 VALUES(NULL,'张三1','18','男',1);
INSERT INTO student1 VALUES(NULL,'张3','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三2','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三4','18','女',3);
INSERT INTO student1 VALUES(NULL,'张三5','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三6','18','女',2);
INSERT INTO student1 VALUES(NULL,'张三7','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三8','18','女',4);
INSERT INTO student1 VALUES(NULL,'张三9','20','男',1);
INSERT INTO student1 VALUES(NULL,'张三10','18','男',2);
INSERT INTO student1 VALUES(NULL,'张三11','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三12','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三13','18','女',5);
INSERT INTO student1 VALUES(NULL,'张三14','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三15','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三16','18','女',4);
INSERT INTO student1 VALUES(NULL,'张三17','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三18','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三19','18','男',2);
INSERT INTO student1 VALUES(NULL,'张三20','20','女',1);
INSERT INTO student1 VALUES(NULL,'张三21','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三22','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三23','18','男',2);
INSERT INTO student1 VALUES(NULL,'张三24','21','女',1);
INSERT INTO student1 VALUES(NULL,'张三25','18','男',1);
INSERT INTO student1 VALUES(NULL,'张三26','18','男',3);
INSERT INTO student1 VALUES(NULL,'张三27','18','女',1);
INSERT INTO student1 VALUES(NULL,'张三28','19','男',3);
INSERT INTO student1 VALUES(NULL,'张三29','18','男',5);
INSERT INTO student1 VALUES(NULL,'张三30','18','女',2);


#号 插入完了  演示 limit
SELECT * FROM student1 WHERE ssex='男' GROUP BY gradeId ORDER BY sno ASC LIMIT 0,2;
SELECT * FROM student1 WHERE ssex='男' GROUP BY gradeId ORDER BY sno ASC LIMIT 2,2;
#in 与 not in   在不在集合里面
SELECT * FROM student1 WHERE gradeId IN (SELECT gradeId FROM grade);
SELECT * FROM student1 WHERE gradeId NOT IN (SELECT gradeId FROM grade WHERE gradeId<4 );


#存在吗   如果符合条件 则执行  前面的语句  如果不存在  则不执行前面的语句
SELECT * FROM student1 WHERE EXISTS (SELECT * FROM grade WHERE gradeid=7);
SELECT * FROM student1 WHERE NOT EXISTS (SELECT * FROM grade WHERE gradeid=7);
# group by  having
SELECT COUNT(gradeId) '学生数量',gradeId '年级' FROM student1 GROUP BY gradeId HAVING COUNT(gradeId)>2;


#下面的就是灰常灰常关键的内连接查询了   ，内连接就是将两个表共有的一列作为连接条件 将两个表合成一个表 
#查询所有学生 及所在的年纪
SELECT s.sno,s.sname,s.sage,s.ssex,s.gradeId,g.gradename FROM student1 s,grade g WHERE s.gradeId=g.gradeId ORDER BY s.sno  ASC;
#这个主要是什么呢？  select 要查询的内容  from 要查询的表  where 连接条件
SELECT s.sno,s.sname,s.sage,s.ssex,s.gradeId,g.gradename FROM student1 s INNER JOIN grade g ON( s.gradeId=g.gradeId)ORDER BY s.sno  ASC;
#这个关键点是         from 表一 inner join 表二  on（连接条件）    内连接比较容易理解   因为是一一对应的  


#下面是外连接   外连接  外连接分为左外连接查询 与 右外连接查询


#下面做一下表设计


#查看course的创建表的信息；
SHOW CREATE TABLE course;
/*
CREATE TABLE `course` (
  `cno` int(3) NOT NULL DEFAULT '0' COMMENT '课程编号',
  `cname` varchar(10) DEFAULT NULL COMMENT '课程名称',
  `tno` int(3) DEFAULT NULL COMMENT '授课老师',
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
#假定 语文数学是 一年级  英语是二年级 其他是三年级
#修改tno 为 gradeId；
ALTER TABLE course CHANGE tno gradeId VARCHAR(2) ;


INSERT INTO course VALUES (1,'语文',1);
INSERT INTO course VALUES (2,'数学',1);
INSERT INTO course VALUES (3,'英语',2);
INSERT INTO course VALUES (4,'历史',3);
INSERT INTO course VALUES (5,'化学',3);
INSERT INTO course VALUES (6,'生物',4);
INSERT INTO course VALUES (7,'地理',8);
INSERT INTO course VALUES (8,'天理',9);
INSERT INTO course VALUES (9,'地理',9);


#现在 试试左连接把   查询课程
SELECT c.cno,c.cname,c.gradeId,g.gradeName FROM course c LEFT OUTER JOIN grade g ON c.gradeId = g.gradeId;
#   理解   select from 左表 left outer join 右表 on 连接条件    以左表为匹配  左表的项显示完整
#  右表与左表存在对应关系  则显示  不存在的就显示为null


#右连接
SELECT c.cno,c.cname,c.gradeId,g.gradeName FROM course c RIGHT OUTER JOIN grade g ON c.gradeId = g.gradeId;
#   理解   select from 左表 right outer join 右表 on 连接条件    以右表为匹配  右表的项显示完整
# 左表与右表存在对应关系  则显示  不存在的就显示为null 
SELECT c.cno,c.cname,c.gradeId,g.gradeName FROM grade g LEFT OUTER JOIN course c ON c.gradeId = g.gradeId;
#经测试  甲表右连接乙表 乙表左连接甲表 结果是一样的


#好，下面是事物
#创建银行  模拟转账
CREATE TABLE bank(
NAME VARCHAR(10),
money DECIMAL(10,2)
);
INSERT INTO bank VALUES('张三',10000);
INSERT INTO bank VALUES('张三',10000);
#错了  执行下面的


SELECT * FROM bank;
DELETE FROM bank WHERE NAME='张三';
INSERT INTO bank VALUES('张三',10000);
INSERT INTO bank VALUES('李四',10000);
BEGIN;
UPDATE bank SET money=money-500 WHERE NAME='张三';
UPDATE bank SET money=money+500 WHERE NAME='李四';
COMMIT;
#视图
CREATE VIEW stu1 AS(SELECT s.sno,s.sname,s.sage,s.ssex,s.gradeId,g.gradename FROM student1 s,grade g WHERE s.gradeId=g.gradeId);


SELECT * FROM stu1;


DROP VIEW IF EXISTS stu1;