mysql简单练习

数据库入门

2.1 引入

数据保存到内存：

优点：

1）读写非常快

缺点：

1）程序关闭导致数据丢失

 

数据保存到文件：

优点：

1）数据可以永久保存

缺点：

1）频繁地IO操作，效率不高！

2）数据管理不方便。例如查询某个数据需要全部读取出来，再匹配。

 

数据保存到数据库软件：

优点：

1）数据永久保存下来

2）数据管理非常方便。（例如查询非常快速和方便）

 

     数据可以说是企业的灵魂！！

2.2 什么是数据库软件

数据库，俗称数据的仓库。方便管理数据的软件（或程序）。

2.3 市面上数据库软件

Oracle，甲骨文公司的产品。 当前最流行应用最广泛的数据库软件。和java语言兼容非常好。

适合中大型，中大应用。

 

SQL Server: 是微软公司的产品。window平台应用非常广泛。和c#，net平台兼容非常好。

 

DB2： IBM公司的产品。IBM服务器--> UNIX -> DB2- > Websphere

 

MySQL: 开源组织的产品。甲骨文公司的产品。免费！！！和java语言兼容非常好！适合中小企业，中小应 用

 

关系型数据库。

 

MongoDB： 非关系型数据库。

 

先学mysql，后面再学oracle

2.4 MySQL入门

1）到mysql官网下载。

2）安装mysql软件

3）使用

验证是否成功

打开cmd  -> 输入 mysql -u root -p  回车   -> 输入密码   回车

C:\Users\APPle>mysql -u root -p

Enter password: ****

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.40 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

3 MySQL数据库

3.1 mysql数据存储结构

先数据库，再表，再有数据

4 数据库管理

4.1 查询所有数据库

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |     -- mysql元数据，基础数据

| mysql              |    --mysql配置数据库，其中包含用户信息。（用户名和密码，权限管理）

| performance_schema |    --mysql数据库软件的运行数据，日志信息，性能数据

| test               |     --测试数据库。空的

+--------------------+

4 rows in set (0.00 sec)

 

4.2 创建数据库

mysql> create database day15       -- 指定默认字符集创建数据库

    -> default character set utf8

    -> ;

Query OK, 1 row affected (0.00 sec)

 

4.3 查看数据库的默认字符集

 

mysql> show create database day15;

+----------+----------------------------------------------------------------+

| Database | Create Database                                                |

+----------+----------------------------------------------------------------+

| day15    | CREATE DATABASE `day15` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

 

4.4 删除数据库

mysql> drop database day15;

Query OK, 0 rows affected (0.01 sec)

 

4.5 修改数据库

 

mysql> alter database day15 default character set gbk;

Query OK, 1 row affected (0.00 sec)

 

5 表管理

选择数据库

 

  5.1 查看所有表

mysql> show tables;

+-----------------+

| Tables_in_day15 |

+-----------------+

| student         |

+-----------------+

1 row in set (0.00 sec)

 

  5.2 创建表

mysql> create table student(

    -> sid int,

    -> sname varchar(20),

    -> sage int

    -> );

Query OK, 0 rows affected (0.01 sec)

 

  5.3 查看表结构

 

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| sid   | int(11)     | YES  |     | NULL    |       |

| sname | varchar(20) | YES  |     | NULL    |       |

| sage  | int(11)     | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

 

5.4 删除表

 

mysql> drop table student;

Query OK, 0 rows affected (0.01 sec)

 

5.5 修改表

1）添加字段

 

mysql> alter table student add column sgender varchar(2);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

   2）删除字段

 

mysql> alter table student drop column sgender;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

3）修改字段类型

 

mysql> alter table student modify column remark varchar(100);

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

4）修改字段名称

 

mysql> alter table student change column sgender gender varchar(2);

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

5）修改表名称

 

mysql> alter table student rename to teacher;

Query OK, 0 rows affected (0.01 sec)

 

6 增删改数据

 

-- ********一、增删改数据********* ---

-- 1.1 增加数据

-- 插入所有字段。一定依次按顺序插入

INSERT INTO student VALUES(1,'张三','男',20);

-- 注意不能少或多字段值

-- INSERT INTO student VALUES(2,'李四','女');

-- 插入部分字段

INSERT INTO student(id,NAME) VALUES(2,'李四');

 

-- 1.2 修改数据

-- 修改所有数据（建议少用）

UPDATE student SET gender='女';

-- 带条件的修改（推荐使用）

UPDATE student SET gender='男' WHERE id=1; -- 修改id为1的学生，修改性别为男

-- 修改多个字段,注意: SET 字段名=值,字段名=值,....

UPDATE student SET gender='男',age=30 WHERE id=2;

 

-- 1.3 删除数据

-- 删除所有数据（建议少用）

DELETE FROM student;

-- 带条件的删除(推荐使用)

DELETE FROM student WHERE id=2;

--  另一种方式

-- delete from: 可以全表删除      1)可以带条件删除  2）只能删除表的数据，不能删除表的约束     3)使用delete from删除的数据可以回滚（事务）

-- truncate table: 可以全表删除   1）不能带条件删除 2）即可以删除表的数据，也可以删除表的约束 3）使用truncate table删除的数据不能回滚

TRUNCATE TABLE student;

 

 

7 查询数据（重点）

7.1 查询所有列

-- 2.1 查询所有列

SELECT * FROM student;

 

7.2 查询指定列

-- 2.2 查询指定列

SELECT id,NAME,gender FROM student;

 

7.3 查询时添加常量列

-- 2.4 查询时添加常量列

-- 需求： 在查询student表时添加一个班级列，内容为“java就业班”

SELECT id,NAME,gender,age,'java就业班' AS '年级'  FROM student;

 

7.4 查询时合并列

-- 2.5 查询时合并列

-- 需求： 查询每个学生的servlet和jsp的总成绩

SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student;

-- 注意：合并列只能合并数值类型的字段

SELECT id,(NAME+servlet) FROM student;

 

7.5 查询时去除重复记录

-- 2.6 查询时去除重复记录(DISTINCT)

-- 需求： 查询学生的性别     男 女

SELECT DISTINCT gender FROM student;

-- 另一种语法

SELECT DISTINCT(gender) FROM student;

-- 需求: 查询学生所在的地区

SELECT DISTINCT address FROM student;

 

7.6 条件查询

-- 2.7 条件查询(where)

-- 2.7.1 逻辑条件： and(与)     or(或)

-- 需求： 查询id为2，且姓名为李四的学生

SELECT * FROM student WHERE id=2 AND NAME='李四'; -- 交集

 

-- 需求： 查询id为2，或姓名为张三的学生

SELECT * FROM student WHERE id=2 OR NAME='张三'; -- 并集

 

-- 2.7.2 比较条件： >   <   >=  <=  =  <>(不等于)     between and (等价于>= 且 <=)

-- 需求： 查询servlet成绩大于70分的学生

SELECT * FROM student WHERE servlet>70;

 

-- 需求： 查询jsp成绩大于等于75，且小于等于90分的学生

SELECT * FROM student WHERE jsp>=75 AND jsp<=90;

-- 另一个语法

SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; -- (包前包后)

 

SELECT * FROM student WHERE gender<>'男';

 

 

-- 2.7.3 判空条件(null 空字符串)：  is null / is not null / =''  / <>''

-- 需求： 查询地址为空的学生（包括null和空字符串）

-- null vs  空字符串

-- null：表示没有值

-- 空字符串：有值的！

-- 判断null

SELECT * FROM student WHERE address IS NULL ;

-- 判断空字符串

SELECT * FROM student WHERE address='';

 

SELECT * FROM student WHERE address IS NULL OR address=''; -- （包括null和空字符串）

 

-- 需求： 查询有地址的学生(不包括null和空字符串)

SELECT * FROM student WHERE address IS NOT NULL AND address<>'';

 

-- 2.7.4 模糊条件： like

-- 通常使用以下替换标记：

-- % : 表示任意个字符

-- _ : 表示一个字符

-- 需求： 查询姓‘张’的学生

SELECT * FROM student WHERE NAME LIKE '李%';

 

-- 需求： 查询姓‘李’，且姓名只有两个字的学生

SELECT * FROM student WHERE NAME LIKE '李_';

 

7.7 聚合查询

-- 2.8 聚合查询（使用聚合函数的查询）

 -- 常用的聚合函数： sum()  avg()  max()  min()  count()

-- 需求：查询学生的servlet的总成绩 (sum() :求和函数)

SELECT SUM(servlet) AS 'servlet的总成绩' FROM student;

 

-- 需求： 查询学生的servlet的平均分

SELECT AVG(servlet) AS 'servlet的平均分' FROM student;

 

-- 需求: 查询当前servlet最高分

SELECT MAX(servlet) AS '最高分' FROM student;

 

-- 需求： 查询最低分

SELECT MIN(servlet) AS '最低分' FROM student;

 

-- 需求： 统计当前有多少学生(count(字段))

SELECT COUNT(*) FROM student;

 

SELECT COUNT(id) FROM student;

 

-- 注意：count（）函数统计的数量不包含null的数据

-- 使用count统计表的记录数，要使用不包含null值的字段

SELECT COUNT(age) FROM student;

 

7.8 分页查询

-- 2.9 分页查询（limit 起始行,查询几行）

-- 起始行从0开始

-- 分页：当前页  每页显示多少条

-- 分页查询当前页的数据的sql: SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;

 

-- 需求： 查询第1,2条记录（第1页的数据）

SELECT * FROM student LIMIT 0,2;

-- 查询第3,4条记录（第2页的数据）

SELECT * FROM student LIMIT 2,2;

-- 查询第5,6条记录（第3页的数据）

SELECT * FROM student LIMIT 4,2;

-- 查询第7,8条记录 (没有记录不显示)

SELECT * FROM student LIMIT 6,2;

 

7.9 查询排序

-- 2.10 查询排序（order by ）

-- 语法 ：order by 字段 asc/desc

-- asc: 顺序，正序。数值：递增，字母：自然顺序（a-z）

-- desc: 倒序，反序。数值：递减，字母：自然反序(z-a)

 

-- 默认情况下，按照插入记录顺序排序

SELECT * FROM student;

 

-- 需求： 按照id顺序排序

SELECT * FROM student ORDER BY id ASC;

SELECT * FROM student ORDER BY id; -- 默认正序

 

SELECT * FROM student ORDER BY id DESC;-- 反序

 

-- 注意：多个排序条件

-- 需求： 按照servlet正序，按照jsp的倒序

SELECT * FROM student ORDER BY servlet ASC,jsp DESC;

 

7.10 分组查询

-- 2.11 分组查询(group by)

-- 需求： 查询男女的人数

-- 预期结果：

  --  男   3

  --- 女   2

  -- 1) 把学生按照性别分组(GROUP BY gender)

  -- 2) 统计每组的人数(COUNT(*))

SELECT gender,COUNT(*) FROM student GROUP BY gender;

 

7.11 分组查询后筛选

-- 2.12 分组查询后筛选

-- 需求： 查询总人数大于2的性别

-- 1) 查询男女的人数

-- 2）筛选出人数大于2的记录(having)

--- 注意： 分组之前条件使用where关键字，分组之前条件使用having关键字

SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2;

 

代码练习：

USE handsomecui

CREATE TABLE studnet(
    id INT,
    NAME VARCHAR(20),
    gender VARCHAR(2),
    age INT
)

ALTER TABLE studnet RENAME TO student;

INSERT INTO student VALUES(1,"张三","男",20)

INSERT INTO student(id, NAME) VALUES(1,"李四");

SELECT * FROM student;

INSERT INTO student(id,NAME,age) VALUES(2,"王五",30);

UPDATE student SET gender="女";

UPDATE student SET gender="男" WHERE id=1;

DELETE FROM student WHERE id=2;

-- delete from student 可以带条件，只能删除表结构，不能删除表约束,可以回滚
-- truncate table student; 不能带条件删除表结构，同时删除表约束，不可以回滚

 

USE handsomecui;

DESC student;

SELECT * FROM student


ALTER TABLE student MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

-- truncate table student

INSERT INTO student(NAME,gender,age) VALUES("张三","男",21);

INSERT INTO student(NAME,gender,age) VALUES("赵六","男",26),("李四","女",22),("王五","男",25)

SELECT NAME AS "姓名", gender AS "性别","软件工程" AS "班级" FROM student

ALTER TABLE student ADD servlet INT, ADD jsp INT;
DESC student

UPDATE student SET servlet=70,jsp=85 WHERE id=1;
UPDATE student SET servlet=65,jsp=90 WHERE id=2;
UPDATE student SET servlet=76,jsp=88 WHERE id=3;
UPDATE student SET servlet=85,jsp=99 WHERE id=4;

SELECT *FROM student

INSERT INTO student VALUES(
    8,"黄六","女",28,78,56
);
SELECT id,NAME,(servlet + jsp)AS "总成绩" FROM student;

SELECT DISTINCT gender FROM student 
SELECT * FROM student WHERE gender<>"男"; -- 不等于
SELECT * FROM student WHERE gender="男" -- 等于

ALTER TABLE student ADD adress VARCHAR(20);

UPDATE student SET adress="河南邓州"
ALTER TABLE student CHANGE adress address VARCHAR(20);

SELECT * FROM student WHERE NAME LIKE "张%"

-- %任意字符
-- _代表一个字符

SELECT SUM(servlet) AS "servlet总成绩",AVG(servlet) AS "servlet平均分", 
MIN(servlet) AS "servlet最低分",MAX(servlet) AS "servlet最高分",COUNT(*) AS "总人数" FROM student

UPDATE student SET servlet=NULL WHERE NAME LIKE "张%"

SELECT * FROM student

SELECT * FROM student LIMIT 0,2; -- 初始行（0开始）,几行


SELECT * FROM student ORDER BY id ASC;
SELECT * FROM student ORDER BY id DESC;


SELECT * FROM student ORDER BY servlet ASC, jsp DESC;

SELECT gender,COUNT(*) AS "人数" FROM student GROUP BY gender;
-- 分组后不能用where，要用having；
SELECT gender ,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*)>2;

 

数据约束

2.1什么数据约束

对用户操作表的数据进行约束

2.2 默认值

作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。

注意：

1）对默认值字段插入null是可以的。

2）对默认值字段可以插入非null

-- 1.1 默认值

CREATE TABLE student(

id INT,

NAME VARCHAR(20),

address VARCHAR(20) DEFAULT '广州天河'  -- 默认值

)

 

DROP TABLE student;

-- 当字段没有插入值的时候，mysql自动给该字段分配默认值

INSERT INTO student(id,NAME) VALUES(1,'张三');

 

-- 注意：默认值的字段允许为null

INSERT INTO student(id,NAME,address) VALUE(2,'李四',NULL);

INSERT INTO student(id,NAME,address) VALUE(3,'王五','广州番禺');

 

2.3 非空

作用： 限制字段必须赋值

注意：

1）非空字符必须赋值

2）非空字符不能赋null

-- 1.2 非空

-- 需求： gender字段必须有值（不为null）

CREATE TABLE student(

id INT,

NAME VARCHAR(20),

gender VARCHAR(2) NOT NULL -- 非空

)

 

-- 非空字段必须赋值

INSERT INTO student(id,NAME) VALUES(1,'李四');

-- 非空字符不能插入null

INSERT INTO student(id,NAME,gender) VALUES(1,'李四',NULL);

 

2.4 唯一

作用： 对字段的值不能重复

注意：

1）唯一字段可以插入null

2）唯一字段可以插入多个null

 

-- 1.3 唯一

CREATE TABLE student(

id INT UNIQUE, -- 唯一

NAME VARCHAR(20)

)

 

INSERT INTO student(id,NAME) VALUES(1,'zs');

INSERT INTO student(id,NAME) VALUES(1,'lisi'); -- ERROR 1062 (23000): Duplicate entry '1' for key 'id'

 

INSERT INTO student(id,NAME) VALUES(2,'lisi');

 

2.5 主键

作用： 非空+唯一

注意：

1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。

2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的 id字段。

-- 1.4 主键（非空+唯一）

DROP TABLE student;

 

CREATE TABLE student(

id INT PRIMARY KEY, -- 主键

NAME VARCHAR(20)

)

 

INSERT INTO student(id,NAME) VALUES(1,'张三');

INSERT INTO student(id,NAME) VALUES(2,'张三');

-- INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 违反唯一约束： Duplicate entry '1' for key 'PRIMARY'

 

-- insert into student(name) value('李四'); -- 违反非空约束： ERROR 1048 (23000): Column 'id' cannot be null

 

2.6 自增长

作用： 自动递增

 

-- 1.5 自增长

CREATE TABLE student(

id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始  ZEROFILL 零填充

NAME VARCHAR(20)

)

 

-- 自增长字段可以不赋值，自动递增

INSERT INTO student(NAME) VALUES('张三');

INSERT INTO student(NAME) VALUES('李四');

INSERT INTO student(NAME) VALUES('王五');

 

SELECT * FROM student;

-- 不能影响自增长约束

DELETE FROM student;

-- 可以影响自增长约束

TRUNCATE TABLE student;

 

USE handsomecui;
CREATE TABLE student1(
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

INSERT INTO student1(NAME) VALUES("张三");
INSERT INTO student1(NAME) VALUES("李四");
SELECT * FROM student1;

 

2.7 外键

作用：约束两种表的数据

 

出现两种表的情况：

解决数据冗余高问题： 独立出一张表

例如： 员工表  和  部门表

问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！

 

使用外键约束：约束插入员工表的部门ID字段值

 

解决办法： 在员工表的部门ID字段添加一个外键约束

 

-- 部门表（主表）

CREATE TABLE dept(

id INT PRIMARY KEY,

deptName VARCHAR(20)

)

 

-- 修改员工表（副表/从表）

CREATE TABLE employee(

id INT PRIMARY KEY,

empName VARCHAR(20),

deptId INT,-- 把部门名称改为部门ID

-- 声明一个外键约束

CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)

--           外键名称                  外键               参考表(参考字段)

)

 

    注意：

1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！

2）主表的参考字段通用为主键！

3）添加数据： 先添加主表，再添加副表

4）修改数据： 先修改副表，再修改主表

5）删除数据： 先删除副表，再删除主表

 

-- 1.6 外键约束

-- 员工表

CREATE TABLE employee(

id INT PRIMARY KEY,

empName VARCHAR(20),

deptName VARCHAR(20) -- 部门名称

)

 

INSERT INTO employee VALUES(1,'张三','软件开发部');

INSERT INTO employee VALUES(2,'李四','软件开发部');

INSERT INTO employee VALUES(3,'王五','应用维护部');

 

SELECT * FROM employee;

 

-- 添加员工，部门名称的数据冗余高

INSERT INTO employee VALUES(4,'陈六','软件开发部');

 

-- 解决数据冗余高的问题：给冗余的字段放到一张独立表中

-- 独立设计一张部门表

CREATE TABLE dept(

id INT PRIMARY KEY,

deptName VARCHAR(20)

)

 

DROP TABLE employee;

 

-- 修改员工表

CREATE TABLE employee(

id INT PRIMARY KEY,

empName VARCHAR(20),

deptId INT,-- 把部门名称改为部门ID

-- 声明一个外键约束

CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：级联修改

--           外键名称                  外键               参考表(参考字段)

)

 

INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');

INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');

INSERT INTO dept(id,deptName) VALUES(3,'秘书部');

 

INSERT INTO employee VALUES(1,'张三',1);

INSERT INTO employee VALUES(2,'李四',1);

INSERT INTO employee VALUES(3,'王五',2);

INSERT INTO employee VALUES(4,'陈六',3);

 

-- 问题: 该记录业务上不合法，员工插入了一个不存在的部门数据

INSERT INTO employee VALUES(5,'陈六',4); -- 违反外键约束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

 

-- 1）当有了外键约束，添加数据的顺序： 先添加主表，再添加副表数据

-- 2）当有了外键约束，修改数据的顺序： 先修改副表，再修改主表数据

-- 3）当有了外键约束，删除数据的顺序： 先删除副表，再删除主表数据

-- 修改部门(不能直接修改主表)

UPDATE dept SET id=4 WHERE id=3;

-- 先修改员工表

UPDATE employee SET deptId=2 WHERE id=4;

 

-- 删除部门

DELETE FROM dept WHERE id=2;

 

-- 先删除员工表

DELETE FROM employee WHERE deptId=2;

 

SELECT * FROM dept;

SELECT * FROM employee;

 

  2.8 级联操作

问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！！！

 

级联修改： ON UPDATE CASCADE

级联删除： ON DELETE CASCADE

 

CREATE TABLE employee(

id INT PRIMARY KEY,

empName VARCHAR(20),

deptId INT,-- 把部门名称改为部门ID

-- 声明一个外键约束

CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：级联修改

--           外键名称                  外键               参考表(参考字段)

)

注意： 级联操作必须在外键基础上使用

 

-- 级联修改（修改）

-- 直接修改部门

UPDATE dept SET id=5 WHERE id=4;

 

-- 级联删除

-- 直接删除部门

DELETE FROM dept WHERE id=1;

 

USE handsomecui;

CREATE TABLE dept(
    id INT PRIMARY KEY,
    deptName VARCHAR(20)
)

DROP TABLE employee1;

CREATE TABLE employee1(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptId INT,
    CONSTRAINT employee1_dept_fk FOREIGN KEY(deptId)REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
)

INSERT INTO dept VALUES(1,'软件开发部');
INSERT INTO dept VALUES(2,'技术开发部');
INSERT INTO dept VALUES(3,'工程开发部');


INSERT INTO employee1 VALUES(1,'张三',1);
INSERT INTO employee1 VALUES(2,'李四',1);
INSERT INTO employee1 VALUES(3,'王五',2);
INSERT INTO employee1 VALUES(4,'陈六',3);

UPDATE dept SET id=5 WHERE id=1;
SELECT * FROM employee1;

UPDATE dept SET deptName="项目开发部"WHERE id=5;


DELETE FROM dept WHERE id=2;

SELECT empName,deptName FROM employee1, dept 
    WHERE employee1.deptId=dept.id;
    
SELECT e.empName,d.deptName
    FROM employee1 AS e
    INNER JOIN dept AS d
    ON e.deptId=d.id;

SELECT d.deptName,e.empName
    FROM dept d
    LEFT OUTER JOIN employee1 e
    ON d.id=e.deptId;
    
SELECT * FROM employee1;

SELECT * FROM dept;

ALTER TABLE employee1 ADD bossid INT;

INSERT INTO employee1 VALUES(3,'王五',5,2);
DESC employee1

UPDATE employee1 SET bossid=3 WHERE id=4;

SELECT    e.empName AS '员工',d.empName AS '老板'
    FROM employee1 e
    LEFT OUTER JOIN employee1 d
    ON e.bossid=d.id;

 

3 数据库设计

3.1 引入

 需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型

图书模型：图书名称，版本号，作者

学生模型： 学号，学生姓名 手机号码

......

角色：学生 老师，图书管理员

《需求说明书》

 需求设计 -  

概要设计：

 抽取实体：业务模型 -> 实体模型（java 类 c++类）内存

class Book{ name, bookNo,author }

 数据库设计:

业务模型/实体模型 - > 数据模型 (硬盘)

 

数据库表设计

问题： 如何设计？

详细设计

类详细，属性和方法

 

    3.2 三大范式

设计原则： 建议设计的表尽量遵守三大范式。

 

第一范式： 要求表的每个字段必须是不可分割的独立单元

student     :   name              -- 违反第一范式

  张小名|狗娃

sutdent    ： name    old_name    --符合第一范式

张小名    狗娃

 

第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

 

employee（员工）: 员工编号  员工姓名 部门名称   订单名称  --违反第二范式

 

员工表：员工编号  员工姓名 部门名称   

 

订单表：  订单编号  订单名称             -- 符合第二范式

      

第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

 

员工表： 员工编号（主键） 员工姓名  部门编号  部门名 --符合第二范式，违反第三范式 （数据冗余高）

 

员工表：员工编号（主键） 员工姓名  部门编号    --符合第三范式（降低数据冗余）

部门表：部门编号  部门名

4 关联查询(多表查询)

--  **************二、关联查询（多表查询）****************----

-- 需求：查询员工及其所在部门(显示员工姓名，部门名称)

-- 2.1 交叉连接查询（不推荐。产生笛卡尔乘积现象：4 * 4=16，有些是重复记录）

SELECT empName,deptName FROM employee,dept;

 

-- 需求：查询员工及其所在部门(显示员工姓名，部门名称)

-- 多表查询规则：1）确定查询哪些表   2）确定哪些哪些字段   3）表与表之间连接条件 (规律：连接条件数量是表数量-1)

-- 2.2 内连接查询：只有满足条件的结果才会显示(使用最频繁)

SELECT empName,deptName       -- 2）确定哪些哪些字段

FROM employee,dept    -- 1）确定查询哪些表

WHERE employee.deptId=dept.id  -- 3）表与表之间连接条件

 

-- 内连接的另一种语法

SELECT empName,deptName

FROM employee

INNER JOIN dept

ON employee.deptId=dept.id;

 

-- 使用别名

SELECT e.empName,d.deptName

FROM employee e

INNER JOIN dept d

ON e.deptId=d.id;

 

-- 需求： 查询每个部门的员工

-- 预期结果：

 --  软件开发部  张三

 --  软件开发部  李四

 --  应用维护部  王五

 --  秘书部      陈六

 --  总经办      null

-- 2.2 左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

 -- （注意： 左外连接：左表的数据一定会完成显示！）

SELECT d.deptName,e.empName

FROM dept d

LEFT OUTER JOIN employee e

ON d.id=e.deptId;

 

-- 2.3 右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

 -- （注意： 右外连接：右表的数据一定会完成显示！）

SELECT d.deptName,e.empName

FROM employee e

RIGHT OUTER JOIN dept d

ON d.id=e.deptId;

 

-- 2.4 自连接查询

-- 需求：查询员工及其上司

-- 预期结果：       

-- 张三    null

-- 李四    张三

-- 王五    李四

-- 陈六    王五

SELECT e.empName,b.empName

FROM employee e

LEFT OUTER JOIN employee b

ON e.bossId=b.id;

 

5 存储过程

5.1 什么是存储过程

  存储过程，带有逻辑的sql语句

之前的sql没有条件判断，没有循环

存储过程带上流程控制语句（if  while）

5.2 存储过程特点

1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！

2）移植性很差！不同数据库的存储过程是不能移植。

 

5.3 存储过程语法

-- 创建存储过程

DELIMITER $       -- 声明存储过程的结束符

CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)

BEGIN             -- 开始

-- 可以写多个sql语句;          -- sql语句+流程控制

SELECT * FROM employee;

END $            -- 结束 结束符

 

-- 执行存储过程

CALL pro_test();          -- CALL 存储过程名称(参数);

 

参数：

IN：   表示输入参数，可以携带数据带存储过程中

OUT： 表示输出参数，可以从存储过程中返回结果

INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能

 

 

-- **************三、存储过程*******************-

-- 声明结束符

-- 创建存储过程

DELIMITER $

CREATE PROCEDURE pro_test()

BEGIN

-- 可以写多个sql语句;

SELECT * FROM employee;

END $

 

-- 执行存储过程

CALL pro_test();

 

-- 3.1 带有输入参数的存储过程

-- 需求：传入一个员工的id，查询员工信息

DELIMITER $

CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数

BEGIN

SELECT * FROM employee WHERE id=eid;

END $

 

-- 调用

CALL pro_findById(4);

 

-- 3.2 带有输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数

BEGIN

        -- 给参数赋值

SET str='helljava';

END $

 

-- 删除存储过程

DROP PROCEDURE pro_testOut;

-- 调用

-- 如何接受返回参数的值？？

-- ***mysql的变量******

--  全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）

        -- 查看所有全局变量： show variables

        -- 查看某个全局变量： select @@变量名

        -- 修改全局变量： set 变量名=新值

        -- character_set_client: mysql服务器的接收数据的编码

        -- character_set_results：mysql服务器输出数据的编码

        

--  会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！

        -- 定义会话变量: set @变量=值

        -- 查看会话变量： select @变量

        

-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

 

-- 1)定义一个会话变量name, 2)使用name会话变量接收存储过程的返回值

CALL pro_testOut(@NAME);

-- 查看变量值

SELECT @NAME;

 

-- 3.3 带有输入输出参数的存储过程

DELIMITER $

CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 输入输出参数

BEGIN

   -- 查看变量

   SELECT n;

   SET n =500;

END $

 

-- 调用

SET @n=10;

 

CALL pro_testInOut(@n);

 

SELECT @n;

 

-- 3.4 带有条件判断的存储过程

-- 需求：输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他数字，返回“错误输入”;

DELIMITER $

CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))

BEGIN

IF num=1 THEN

SET str='星期一';

ELSEIF num=2 THEN

SET str='星期二';

ELSEIF num=3 THEN

SET str='星期三';

ELSE

SET str='输入错误';

END IF;

END $

 

CALL pro_testIf(4,@str);

 

SELECT @str;

 

-- 3.5 带有循环功能的存储过程

-- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和

DELIMITER $

CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)

BEGIN

-- 定义一个局部变量

DECLARE i INT DEFAULT 1;

DECLARE vsum INT DEFAULT 0;

WHILE i<=num DO

      SET vsum = vsum+i;

      SET i=i+1;

END WHILE;

SET result=vsum;

END $

 

DROP PROCEDURE pro_testWhile;

 

 

CALL pro_testWhile(100,@result);

 

SELECT @result;

 

USE day16;

 

-- 3.6 使用查询的结果赋值给变量（INTO）

DELIMITER $

CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )

BEGIN

SELECT empName INTO vname FROM employee WHERE id=eid;

END $

 

CALL pro_findById2(1,@NAME);

 

SELECT @NAME;

 

6 触发器

6.1 触发器作用

当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！

 

例如： 当向员工表插入一条记录时，希望同时往日志表插入数据

 

-- 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据

-- 创建触发器(添加)

CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时

     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

     

-- 插入数据

INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);

INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);

 

-- 创建触发器(修改)

CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时

     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

     

 -- 修改

 UPDATE employee SET empName='eric' WHERE id=7;

 

-- 创建触发器(删除)

CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时

     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

  

 -- 删除

 DELETE FROM employee WHERE id=7;

 

USE handsomecui;

DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)
BEGIN
    SELECT * FROM employee1 WHERE id=eid;
END $

CALL pro_findById(2);


DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))
BEGIN
    SET str="这是一个参数";
END $

DROP PROCEDURE pro_testOut;

CALL pro_testOut(@NAME);
SELECT @NAME;

DELIMITER $
CREATE PROCEDURE protestInOut(INOUT n INT)
BEGIN
    SELECT n;
    SET n=50;
END $

SET @n=10;
CALL protestInOut(@n);
SELECT @n


DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str='星期一';
    ELSEIF num=2 THEN
        SET str='星期二';
    ELSEIF num=3 THEN
        SET str='星期三';

    ELSE
        SET str='输入错误';
    END IF;
END $

CALL pro_testIf(1,@str);

SELECT @str;

-- 循环
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vsum INT DEFAULT 0;
    WHILE i<=num DO
        SET vsum = vsum + i;
        SET i = i + 1;
    END WHILE;
    SET result=vsum;
END $

CALL pro_testWhile(100, @result);
SELECT @result;

SELECT * FROM student1;

CREATE TABLE student3(
    NAME VARCHAR(10),
    english INT
)


INSERT INTO student3 VALUES ("张三", 80),("张三", 60),("张三", 50);

SELECT * FROM student3;
SELECT AVG(english)FROM student3;

DELIMITER $
CREATE PROCEDURE pro_testAvg(OUT str VARCHAR(20)) -- 这一写成declare 了错了半天啊 ！！！
BEGIN 
    DECLARE savg DOUBLE;
    SELECT AVG(english) INTO savg FROM student3;
    IF savg<=70 THEN
        SET str='一般';
    ELSEIF savg>70 AND savg<=90 THEN
        SET str='良好';
    ELSE
        SET str='优秀';
    END IF;
END $

DROP PROCEDURE pro_testAvg;



CALL pro_testAvg(@str);

SELECT @str;

-- ******触发器******
CREATE TABLE testLog(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(100)
)

CREATE TRIGGER triAdd AFTER INSERT ON student1 FOR EACH ROW
    INSERT INTO testLog(content) VALUES ("向student1表插入了一条记录");

CREATE TRIGGER triUp AFTER UPDATE ON student1 FOR EACH ROW
    INSERT INTO testLog(content) VALUES ("向student1表修改了一条记录");


CREATE TRIGGER triDel AFTER DELETE ON student1 FOR EACH ROW
    INSERT INTO testLog(content) VALUES ("向student1表删除了一条记录");


DROP TRIGGER triAdd

SELECT * FROM student1

INSERT INTO student1(NAME) VALUES("王五");
UPDATE student1 SET NAME="毛线" WHERE id=2;

DELETE FROM student1 WHERE id=0001;


SELECT * FROM testLog;

 

7 mysql权限问题

-- ***********五、mysql权限问题****************

 -- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情）

 -- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表

 -- 如何修改mysql的用户密码？

 -- password: md5加密函数(单向加密)

 SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

 

--  mysql数据库，用户配置 : user表

USE mysql;

 

SELECT * FROM USER;

 

-- 修改密码

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

 

-- 分配权限账户

GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

 

USE mysql;

SELECT * FROM USER
UPDATE USER SET PASSWORD=PASSWORD("123456")WHERE USER="root";

GRANT SELECT ON handsomecui.employee TO 'eric'@'localhost'IDENTIFIED BY '123456';
GRANT INSERT ON handsomecui.employee TO 'eric'@'localhost'IDENTIFIED BY '123456';
 -- 备份(和还原都不需要登录mysql)
 
-- mysqldump -u root -p handsomecui > e:/back.sql

-- 还原
-- mysql -u root -p handsomecui <ｅ:/back.sql