• mysql视图


    1 Navicat无法连接数据库的解决办法

    打开【win+r】输入CMD进入命令行界面,然后输入

    alter user 'root'@'localhost' identified by '密码' password expire never;

    alter user 'root'@'localhost' identified with mysql_native_password by '密码';

    flush privileges;

    记得将密码替换为mysql 的密码。

    6.关于mysql数据库视图

    视图:select查询结果的一个虚拟表。

    6-1 创建视图

    代码块
    EATE VIEW stu_salary_view
    
    as
    
    SELECT * from student WHERE salary>5000 with CHECK option;
    
    with CHECK option 表示修改视图时候,salary必须大于5000,否则无法保存。
    

    6-2使用视图

    代码块
    SELECT * from  stu_salary_view  WHERE stu_age=24;
    

    6-3替换原来的视图

    覆盖原来的视图

    代码块
    CREATE OR REPLACE VIEW stu_salary_view
    
    as
    
    (SELECT * from student )
    

    6-4删除视图

    DROP VIEW stu_salary_view;

    7创建一个学生的数据库

    代码块
    -- 创建学院表
    
    CREATE TABLE  college(
    
      id  int(11)  NOT NULL  AUTO_INCREMENT,
    
      name    varchar(30) NOT NULL,
    
      PRIMARY KEY (id)
    
    ) ;
    
    -- 创建表班级
    
    CREATE TABLE class (
    
      id int(11) NOT NULL AUTO_INCREMENT,
    
      name  varchar(30) NOT NULL,
    
      belong_college  int(11) DEFAULT NULL,
    
      PRIMARY KEY (id),
    
      CONSTRAINT class_college_fk  FOREIGN KEY (belong_college) REFERENCES  college(id)
    
    ) ;
    
    -- 创建表班级
    
    CREATE TABLE student(
    
    id int PRIMARY KEY ,
    
    stu_name varchar(20) not null,
    
    age int not null,
    
    gender varchar(6) not null,
    
    number int not null,
    
    birth datetime check(birth > '1990-1-1')
    
    stu_class int,
    
    CONSTRAINT student_class_fk FOREIGN KEY (stu_class) REFERENCES class(id)
    
    );
    
    -- 创建老师表
    
    create table teacher
    
    (
    
    id int  auto_increment primary key,
    
    tea_name varchar(20) not null,
    
    tea_class int,
    
      CONSTRAINT teacher_class_fk FOREIGN KEY (tea_class) REFERENCES class(id)
    
    );
    
    -- 创建课程表
    
    create table course
    
    (
    
    id int auto_increment primary key,
    
    cou_name varchar(50) not null,
    
    cou_time tinyint check(cou_time>0 and cou_time<100),
    
    cou_teacher int not null,
    
      CONSTRAINT course_teacher_fk FOREIGN KEY (cou_teacher) REFERENCES teacher(id)
    
    );
    
    -- 创建成绩表
    
    create table score
    
    (
    
    score_course int,
    
      score_number int,
    
    score int,
    
      CONSTRAINT score_course_fk FOREIGN KEY(score_course) REFERENCES course(id),
    
      CONSTRAINT score_student_fk FOREIGN KEY(score_number) REFERENCES student(id)
    
    );
    

    别跑,点个赞再走

  • 相关阅读:
    jquery实现记住用户名和密码
    从mysql8.0.15升级到8.0.16
    mysql8.0.15二进制安装
    DML、DDL、DCL的分别是什么
    redis3.2.10单实例安装测试
    redis5.0.3单实例简单安装记录
    percona-xtrabackup快速安装及其简单使用
    pt-show-grants的用法
    Centos6安装Percona-tools工具
    sshpass-Linux命令之非交互SSH密码验证
  • 原文地址:https://www.cnblogs.com/hellosiyu/p/12489949.html
Copyright © 2020-2023  润新知