学习目标:练习Mysql基础语句
mysql建表语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`Cno` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`Cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Tno` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` VALUES ('6-166', '数字电路', '856');
INSERT INTO `course` VALUES ('9-888', '高等数学', '831');
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`Sno` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`Cno` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Degree` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `score` VALUES ('103', '3-105', '92');
INSERT INTO `score` VALUES ('105', '3-105', '88');
INSERT INTO `score` VALUES ('109', '3-105', '76');
INSERT INTO `score` VALUES ('103', '3-105', '92');
INSERT INTO `score` VALUES ('105', '3-105', '88');
INSERT INTO `score` VALUES ('109', '3-105', '76');
INSERT INTO `score` VALUES ('101', '6-166', '85');
INSERT INTO `score` VALUES ('107', '3-105', '91');
INSERT INTO `score` VALUES ('108', '3-105', '78');
INSERT INTO `score` VALUES ('101', '6-166', '85');
INSERT INTO `score` VALUES ('107', '6-166', '79');
INSERT INTO `score` VALUES ('108', '6-166', '81');
INSERT INTO `score` VALUES ('107', '3-245', '77');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`Sno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`Sname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Ssex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Sbirtuday` date NULL DEFAULT NULL,
`Class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES ('101', '小花', '女', '1799-06-12', '95033');
INSERT INTO `student` VALUES ('103', '露露', '女', '1823-12-02', '95031');
INSERT INTO `student` VALUES ('105', '狂魔', '男', '1588-01-01', '95031');
INSERT INTO `student` VALUES ('107', '弟弟', '男', '1776-03-22', '95033');
INSERT INTO `student` VALUES ('108', '曾华', '男', '1977-09-01', '95033');
INSERT INTO `student` VALUES ('109', '王芳', '女', '1799-11-29', '95031');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`Tno` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`Tname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Tsex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Tbirthday` date NULL DEFAULT NULL,
`Prof` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`Depart` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
INSERT INTO `teacher` VALUES ('804', '诚如', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO `teacher` VALUES ('825', '王萍', '女', '1779-02-11', '助教', '计算机系');
INSERT INTO `teacher` VALUES ('831', '刘华', '女', '2033-07-22', '助教', '电子工程系');
INSERT INTO `teacher` VALUES ('856', '张旭', '男', '1899-10-11', '讲师', '电子工程系');
SET FOREIGN_KEY_CHECKS = 1;
mysql常用关键词
- distinct 数据去重 举例 select distinct
- DESC ASC 升序和降序 和 order by搭配使用
- count 计数 count(*) 和where配套使用,返回值为符合where条件的总数
- where sth in 我感觉和 where sth = 效果差不多
- ground by sth 默认排序降序,将sth重名的和为一组,如果没有加以修饰,那么默认为原数据库最先找到的数据,常用avg来计算平均数
- mysql的 like ‘regex’ mysql的正则表达式好像和平常的正则表达式不一样 %类似于*的效果
- 别称 原名称+空格+别称
- 两表连接 举例 select * from where table1 left join table2 on table1.name = table2.name 但是会出现重复的列,可以使用left join on-> left join using()