MySQL6天笔记——day01

左手的ㄟ右手 2023-02-22 10:58 1阅读 0赞

### 文章目录 ###

*  MySQL数据库
 *   *  整体大纲
     *  与MySQL的第一次亲密接触
     *   *  为什么学习数据库？
         *  数据库的相关概念
         *  数据库存储数据的特点
         *  MySQL软件
     *  SQL语言的分类
     *  后文需要的数据库数据表文件
     *  欢迎斧正！！！

# MySQL数据库 #

> 这是JavaSE30天笔记的后续笔记，MySQL笔记，总共分为6天。、  
> 全部SQL源文件链接：https://pan.baidu.com/s/1wc51qkVetSRybFzcIYGBIg  
> 提取码：3wpt

## 整体大纲 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]

## 与MySQL的第一次亲密接触 ##

### 为什么学习数据库？ ###

*  实现数据持久化
 *  使用完整的管理系统统一管理，易于查询

### 数据库的相关概念 ###

*  DB：数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
 *  DBMS
    
     *  数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器
     *  常见的数据库管理系统：MySQL、Oracle、DB2、SqlServer等。
 *  SQL
    
     *  结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。
     *  SQL的优点：①简单易学；②不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL；③虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

> 以上三者之间的关系：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 1]

--------------------

### 数据库存储数据的特点 ###

*  1、将数据放到表中，表再放到库中
 *  2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
 *  3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
 *  4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”。
 *  5、表中的数据是按行存储的，每一行类似于java中的“对象”。

### MySQL软件 ###

*  介绍
    
     *  MySQL数据库隶属于MySQLAB公司，总部位于瑞典，后被oracle收购。
     *  优点：①成本低：开放源代码，一般可以免费试用；②简单：很容易安装和使用；③性能高：执行很快。
 *  安装
    
     *  DBMS分为两类：①基于共享文件系统的DBMS （Access）；②基于客户机——服务器的DBMS（MySQL、Oracle、SqlServer）。
     *  版本可分为：社区版（免费），企业版（收费）。
     *  Windows平台下下载：[http://dev.mysql.com/downloads/mysql][http_dev.mysql.com_downloads_mysql]
     *  `完整安装卸载步骤`：[https://blog.csdn.net/Y\_BlueBlack/article/details/81433401][https_blog.csdn.net_Y_BlueBlack_article_details_81433401]
 *  启动和停止MySQL服务
    
     *  方式一：右击此电脑—管理—服务—启动或停止MySQL服务
     *  方式二：

启动：net start mysql服务名
    停止：net stop mysql服务名

*  MySQL服务端的登录和退出

*  MySQL常见命令介绍

进入mysql, 在命令行中输入
    mysql–uroot–p#### (其中：####表示密码）
    例：mysql -uroot -proot
    
    查看mysql中有哪些个数据库
    show databases;
    
    新建一个数据库
    create database 数据库名;
    例：create database book;
    
    选择一个数据库
    use 数据库名称;
    例：use test;
    
    查询数据表
    show tables;
    
    查看指定的数据库中有哪些数据表
    show tables from 数据库名;
    例：show tables from mysql;
    
    查询当前所在数据库
    select database();
    
    新建一个数据表
    create table math(
    id int,
    name varchar(20));
    
    查看表的结构
    desc 表名;
    例：desc math;
    
    查看表中的所有记录
    select * from 表名;
    例： select * from math;
    
    向表中插入记录
    insert into 表名(列名列表) values(列对应的值的列表);
    注意：插入varchar或date 型的数据要用单引号引起来
    例：insert into math (id,name) values(1,"ton");
    
    修改记录
    update 表名set 列1 = 列1的值, 列2 = 列2的值where ..
    例：update math set name="wugang" where id=1;
    
    删除记录
    delete from 表名 where ...
    例：delete from math where id=1;
    
    删除数据表
    drop table 表名;
    例：drop table math;

*  查看MySQL服务端版本

登录到mysql服务端
    select version();
    
    没有登录到mysql服务端
    在cmd命令界面输入：mysql --version

*  MySQL的语法规范
    
     *  不区分大小写,但建议关键字大写，表名、列名小写
     *  每句话用;或\\g结尾，最好用分号结尾
     *  每条命令根据需要，可以进行缩进或换行
     *  注释：①单行注释：\#注释文字；②单行注释：-- 注释文字；③多行注释：/\* 注释文字 \*/
 *  图形化用户界面客户端
    
     *  介绍：主要分为两种——SQLyog 12、Navicat 12 for mysql
     *  安装：[https://blog.csdn.net/lihua5419/article/details/73881837/][https_blog.csdn.net_lihua5419_article_details_73881837]
     *  卸载：方法一：控制面板选中SQLyog直接卸载；方法二：卸载时担心注册表清除不干净,百度geek下载工具，下载免费版即可。下载后，放到桌面，双击后可直接选中要卸载的软件进行卸载,最后可根据选择是否清除注册表。

## SQL语言的分类 ##

> 对于这个网上有三种、四种、五种、六种很多，但综合网上的来说，是5种！！因为大部分用到的是DQL和DML，偶尔会用到DDL，一般的开发人员很少会用到DCL，而且本教程也不会过多赘述，毕竟只有6天。

*  DQL（Data Query Language）：数据查询语言，用于检索数据库中的数据，主要是SELECT语句；
 *  DML（Data Manipulation Language)：数据操纵语言，用于改变数据库中的数据，主要包括INSERT、UPDATE和DELETE语句；
 *  DDL（Data Definition Language)：数据定义语言，用于库和表的创建、修改、删除。主要包括CREATE、DROP、ALTER语句；
 *  DCL（Data Control Language)：数据控制语言，用于定义用户的访问权限和安全级别。主要包括GRANT和REVOKE语句；
 *  TCL（Transaction Control Language)：事务控制语言，用于维护数据的一致性，包括COMMIT、ROLLBACK和SAVEPOINT语句。

## 后文需要的数据库数据表文件 ##

*  myemployees文件

/* SQLyog Ultimate v10.00 Beta1 MySQL - 5.5.15 : Database - myemployees ********************************************************************* */
    
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;
    
    USE `myemployees`;
    
    /*Table structure for table `departments` */
    
    DROP TABLE IF EXISTS `departments`;
    
    CREATE TABLE `departments` (
      `department_id` int(4) NOT NULL AUTO_INCREMENT,
      `department_name` varchar(3) DEFAULT NULL,
      `manager_id` int(6) DEFAULT NULL,
      `location_id` int(4) DEFAULT NULL,
      PRIMARY KEY (`department_id`),
      KEY `loc_id_fk` (`location_id`),
      CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
    
    /*Data for the table `departments` */
    
    insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
    
    /*Table structure for table `employees` */
    
    DROP TABLE IF EXISTS `employees`;
    
    CREATE TABLE `employees` (
      `employee_id` int(6) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(20) DEFAULT NULL,
      `last_name` varchar(25) DEFAULT NULL,
      `email` varchar(25) DEFAULT NULL,
      `phone_number` varchar(20) DEFAULT NULL,
      `job_id` varchar(10) DEFAULT NULL,
      `salary` double(10,2) DEFAULT NULL,
      `commission_pct` double(4,2) DEFAULT NULL,
      `manager_id` int(6) DEFAULT NULL,
      `department_id` int(4) DEFAULT NULL,
      `hiredate` datetime DEFAULT NULL,
      PRIMARY KEY (`employee_id`),
      KEY `dept_id_fk` (`department_id`),
      KEY `job_id_fk` (`job_id`),
      CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
      CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312;
    
    /*Data for the table `employees` */
    
    insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
    
    /*Table structure for table `jobs` */
    
    DROP TABLE IF EXISTS `jobs`;
    
    CREATE TABLE `jobs` (
      `job_id` varchar(10) NOT NULL,
      `job_title` varchar(35) DEFAULT NULL,
      `min_salary` int(6) DEFAULT NULL,
      `max_salary` int(6) DEFAULT NULL,
      PRIMARY KEY (`job_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
    
    /*Data for the table `jobs` */
    
    insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
    
    /*Table structure for table `locations` */
    
    DROP TABLE IF EXISTS `locations`;
    
    CREATE TABLE `locations` (
      `location_id` int(11) NOT NULL AUTO_INCREMENT,
      `street_address` varchar(40) DEFAULT NULL,
      `postal_code` varchar(12) DEFAULT NULL,
      `city` varchar(30) DEFAULT NULL,
      `state_province` varchar(25) DEFAULT NULL,
      `country_id` varchar(2) DEFAULT NULL,
      PRIMARY KEY (`location_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
    
    /*Data for the table `locations` */
    
    insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

*  girls文件

/* SQLyog Ultimate v10.00 Beta1 MySQL - 5.7.18-log : Database - girls ********************************************************************* */
    
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `girls`;
    
    /*Table structure for table `admin` */
    
    DROP TABLE IF EXISTS `admin`;
    
    CREATE TABLE `admin` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(10) NOT NULL,
      `password` VARCHAR(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
    /*Data for the table `admin` */
    
    INSERT  INTO `admin`(`id`,`username`,`password`) VALUES (1,'john','8888'),(2,'lyt','6666');
    
    /*Table structure for table `beauty` */
    
    DROP TABLE IF EXISTS `beauty`;
    
    CREATE TABLE `beauty` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) NOT NULL,
      `sex` CHAR(1) DEFAULT '女',
      `borndate` DATETIME DEFAULT '1987-01-01 00:00:00',
      `phone` VARCHAR(11) NOT NULL,
      `photo` BLOB,
      `boyfriend_id` INT(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
    /*Data for the table `beauty` */
    
    INSERT  INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) VALUES (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
    
    /*Table structure for table `boys` */
    
    DROP TABLE IF EXISTS `boys`;
    
    CREATE TABLE `boys` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `boyName` VARCHAR(20) DEFAULT NULL,
      `userCP` INT(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    /*Data for the table `boys` */
    
    INSERT  INTO `boys`(`id`,`boyName`,`userCP`) VALUES (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

*  工资等级

CREATE TABLE job_grades
    (grade_level VARCHAR(3),
     lowest_sal  INT,
     highest_sal INT);
    
    INSERT INTO job_grades
    VALUES ('A', 1000, 2999);
    
    INSERT INTO job_grades
    VALUES ('B', 3000, 5999);
    
    INSERT INTO job_grades
    VALUES('C', 6000, 9999);
    
    INSERT INTO job_grades
    VALUES('D', 10000, 14999);
    
    INSERT INTO job_grades
    VALUES('E', 15000, 24999);
    
    INSERT INTO job_grades
    VALUES('F', 25000, 40000);

*  学生数据库文件

DROP DATABASE IF EXISTS student;
    CREATE DATABASE student;
    USE student;
    CREATE TABLE student(
    	studentno VARCHAR(10) NOT NULL PRIMARY KEY,
    	studentname VARCHAR(20) NOT NULL,
    	loginpwd VARCHAR(8) NOT NULL,
    	sex CHAR(1) ,
    	majorid INT NOT NULL REFERENCES grade(majorid),
    	phone VARCHAR(11),
    	email VARCHAR(20) ,
    	borndate DATETIME
    )DEFAULT CHARSET=utf8;
    
    CREATE TABLE major(
    	majorid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	majorname VARCHAR(20) NOT  NULL
    
    )DEFAULT CHARSET=utf8;
    CREATE TABLE result(
    	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	studentno VARCHAR(10) NOT NULL REFERENCES student(studentno),
    	score DOUBLE
    )DEFAULT CHARSET=utf8;
    
    
    INSERT INTO major VALUES(NULL,'javaee');
    INSERT INTO major VALUES(NULL,'html5');
    INSERT INTO major VALUES(NULL,'android');
    
    
    INSERT INTO student VALUES('S001','张三封','8888','男',1,'13288886666','zhangsanfeng@126.com','1966-9-1');
    INSERT INTO student VALUES('S002','殷天正','8888','男',1,'13888881234','yintianzheng@qq.com','1976-9-2');
    INSERT INTO student VALUES('S003','周伯通','8888','男',2,'13288886666','zhoubotong@126.com','1986-9-3');
    INSERT INTO student VALUES('S004','张翠山','8888','男',1,'13288886666',NULL,'1995-9-4');
    INSERT INTO student VALUES('S005','小小张','8888','女',3,'13288885678','xiaozhang@126.com','1990-9-5');
    
    INSERT INTO student VALUES('S006','张无忌','8888','男',2,'13288886666','zhangwuji@126.com','1998-8-9');
    INSERT INTO student VALUES('S007','赵敏','0000','女',1,'13288880987','zhaomin@126.com','1998-6-9');
    INSERT INTO student VALUES('S008','周芷若','6666','女',1,'13288883456','zhouzhiruo@126.com','1996-7-9');
    INSERT INTO student VALUES('S009','殷素素','8888','女',1,'13288886666','yinsusu@163.com','1986-1-9');
    INSERT INTO student VALUES('S010','宋远桥','6666','男',3,'1328888890','songyuanqiao@qq.com','1996-2-9');
    
    
    INSERT INTO student VALUES('S011','杨不悔','6666','女',2,'13288882345',NULL,'1995-9-9');
    INSERT INTO student VALUES('S012','杨逍','9999','男',1,'13288885432',NULL,'1976-9-9');
    INSERT INTO student VALUES('S013','纪晓芙','9999','女',3,'13288888765',NULL,'1976-9-9');
    INSERT INTO student VALUES('S014','谢逊','9999','男',1,'13288882211',NULL,'1946-9-9');
    INSERT INTO student VALUES('S015','宋青书','9999','男',3,'13288889900',NULL,'1976-6-8');
    
    
    
    INSERT INTO result VALUES(NULL,'s001',100);
    INSERT INTO result VALUES(NULL,'s002',90);
    INSERT INTO result VALUES(NULL,'s003',80);
    
    INSERT INTO result VALUES(NULL,'s004',70);
    INSERT INTO result VALUES(NULL,'s005',60);
    INSERT INTO result VALUES(NULL,'s006',50);
    INSERT INTO result VALUES(NULL,'s006',40);
    INSERT INTO result VALUES(NULL,'s005',95);
    INSERT INTO result VALUES(NULL,'s006',88);

## 欢迎斧正！！！ ##

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200629175718971.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20200703091454466.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5,size_16,color_FFFFFF,t_70#pic_center
[http_dev.mysql.com_downloads_mysql]: http://dev.mysql.com/downloads/mysql
[https_blog.csdn.net_Y_BlueBlack_article_details_81433401]: https://blog.csdn.net/Y_BlueBlack/article/details/81433401
[https_blog.csdn.net_lihua5419_article_details_73881837]: https://blog.csdn.net/lihua5419/article/details/73881837/