练习Mysql基础语句

龙中舞王

已于 2022-04-01 15:58:55 修改

阅读量801

点赞数

文章标签： mysql

于 2022-04-01 15:58:25 首次发布

本文链接：https://blog.csdn.net/qq_44695769/article/details/123898210

版权

学习目标：练习Mysql基础语句

mysql建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50737
 Source Host           : localhost:3306
 Source Schema         : mysqltest

 Target Server Type    : MySQL
 Target Server Version : 50737
 File Encoding         : 65001

 Date: 01/04/2022 14:58:32
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
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;

-- ----------------------------
-- Records of course
-- ----------------------------
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');

-- ----------------------------
-- Table structure for score
-- ----------------------------
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;

-- ----------------------------
-- Records of score
-- ----------------------------
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');

-- ----------------------------
-- Table structure for student
-- ----------------------------
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;

-- ----------------------------
-- Records of student
-- ----------------------------
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');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
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;

-- ----------------------------
-- Records of teacher
-- ----------------------------
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()