MySQL(DDL DML DQL DCL)

服务器

服务器:
	服务器是硬件和软件的结合体 能够对外提供某种服务 我们就叫做服务器

数据库服务器:
	一台装有MySQL服务软件的电脑 能够对外提供数据增删改查的服务 我们就叫做数据库服务器

web服务器:
	一台装有web服务软件(例如，Tomcat)的电脑 能够对外提供上网(网络资源的访问，比如图片，音乐，视频等等，文字，等等)服务 那么这台电脑就可以称作web服务器
1
2
3
4
5
6
7
8

数据库

概念
	数据存储的仓库，用于存储和管理数据
	
常见的数据库产品
	Oracle: oracle公司 大型的关系型数据库 收费的
	DB2: IBM公司 大型的关系型数据库 收费的
	Mysql: mysql公司 中小型关系型数据库 免费的。后来被oracle收购 mysql6之后收费(社区版(免费的) 旗舰版)
	Sql Server: 微软公司 中小型关系型数据库 收费的
	Sqllite: 安卓系统内置数据库 微型关系型数据库	免费的
	
关系型数据库: 
	数据的存储结构 有行和列组成的这种二维表 并且表和表之间存在对应关系
	
非关系型数据库:
	比如用 键值存储数据这种数据库 类似JSON 例如:Redis	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MySQL: 数据库软件

卸载:
    1.关闭mysql服务
        a.手动关闭：我的电脑-->右键-->管理
        b.cmd-->services.msc,打开服务面板
        c.cmd-->net stop mysql(注意获取cmd管理员权限)
    2.卸载
    3.删除数据存储文件
		找到mysql的安装目录。中my.ini文件 datadir目录,删除

使用:
    登陆数据库:
    	cmd-->mysql -uroot -p密码
    服务: 系统后台进程
    启动服务: net start mysql
    停止服务: net stop mysql
    删除服务: sc delete mysql 

配置:
修改mysql的密码步骤:
    1.停止mysql服务 运行输入services.msc 停止mysql服务
    	或者 cmd -- net stop mysql
    2.在cmd下输入 mysqld -- skip-grant-tables 启动服务器 光标不动(不要关闭该窗口)
    3.新打开cmd 输入mysql -u root -p 旧密码 
        use mysql;
        update user set password=password('123456') WHERE User='root';
    4.关闭两个cmd窗口 在任务管理器结束mysqld进程 
    5.在服务管理页面 重启mysql 服务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

SQL: 结构化查询语句

概念
	SQL是Structured Query Language的缩写,中文译为“结构化查询语言”。结构化查询语言,是针对关系型数据库,指定的一套操作数据库的语法规范,只要是关系型数据库,都要遵循此规范,但是允许各家数据库存在差异,这些差异我们称之为方言

SQL分类
	DDL(Data Definition Language): 
		数据定义语言,用来定义数据库对象: 库、表、列等
    DML(Data Manipulation Language): 
    	数据操作语言,用来定义数据库记录(增、删、改)
    DCL(Data Control Language): 
    	数据控制语言,用来定义访问权限和安全级别
    DQL(Data Query Language): 
    	数据查询语言,用来查询记录(数据)
1
2
3
4
5
6
7
8
9
10
11
12

DDL: 操作数据库和表

操作数据库

-- ``反引号在创建数据库,表时 用来规避关键字	

-- 创建数据库
create database `数据库名称`;

-- 删除数据库
drop database `数据库名称`;

-- 修改数据库字符集
ALTER DATABASE 数据库名称 
	CHARACTER SET='utf8';
	
-- 查看当前数据库服务器中的所有数据库
SHOW DATABASES;

-- 查看前面创建的db01数据库的定义信息
SHOW CREATE DATABASE `数据库名称`;
	
-- 备份数据库(在DOS执行)命令行
-- mysqldummp -u 用户名 -p -B 数据库1 数据库2 >文件名.sql
mysqldump -u root -p -B db01>d:\\bak.sql;

-- 备份数据库的表(在DOS执行)命令行
-- mysqldummp -u 用户名 -p 数据库 表1 表2>文件名.sql
mysqldump -u root -p db01 users>d:\\bak.sql;

-- 恢复数据库(进入Mysql命令行在执行)
-- source 文件名.sql
mysql -u root -p
source d:\\bak.sql;

-- 恢复数据库(复制方式)
-- 新建新查询编辑器->复制bak.sql内容->粘贴到新查询编辑器->运行	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

操作表

列数据类型

表，有表头（或者叫字段）

字段或者说表头，是有数据类型的

可变长度字符串 最大65532字节  【utf8编码最大21844字符 1-3个字节用于记录大小】
如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766

CREATE TABLE `time`{
	login_time TIMESTAMP
	--如果希望login_time列自动更新 需要进行下面的配置
	NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
}
1
2
3
4
5
6
7
8
9

操作表的语法

-- 建表之前，先选择库
use `数据库名称`;

-- 创建表
-- 建表时 可以加上字段说明
create table `emp`(
	`id` INT COMMENT '员工编号',
	`name` VARCHAR(32) COMMENT '员工姓名',
	`sex` CHAR(1),
	`birthday` DATE,
	`entry_date` DATE,
	`job` VARCHAR(32),
	`salary` DOUBLE,
	`resume` TEXT)
	character set utf8 collate utf8_bin engine innodb;
	
character set字符集  	
collate校对规则
engine innodb引擎

-- 添加语句
insert into `表名` values(-128);

-- 删除表
drop table `表名`;

-- 修改表名	
rename table emp to employee
alter table emp rename to employee;

-- 查询表
select * from `表名`;

-- 查看该库中所有的表
show tables;

-- 显示表的结构(查看表的所有列)	
desc 表名;


-- 对表头的操作 alter
-- 添加列	
alter table `emp`
	add image varchar(32) 
	after resume;

-- 删除列	
alter table `emp`
	drop sex;
	
-- 修改列的数据类型
alter table `emp`
	modify job varchar(60);
	
-- 可以在修改列名时 可以修改数据类型
alter table employee change change `name` `name` varchar(60);	

-- 修改列名	
alter table employee 
	change `name` `user_name` varchar(32) 
	not NULL default '';

-- 修改字符集
alter table employee 
	character set utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

DML: 操作表中的数据

where子句运算符

增-input

-- 向表中插入数据
insert into table_name (column...)
	values(value...)
-- column是列名 如果不写,默认添加全部的值
-- value是需要往column中添加的值

-- 向employee表中添加数据
-- 	id 字段添加 1 
-- 	salary 字段添加 3000 
insert into employee (id,`salary`)
	values(1,3000)

1.插入的数据应于数据类型相同
2.数据的长度应该在列的规定范围内
3.在VALUES中列出的数据位置 必须与被加入的列的排列位置相对应
4.字符和日期类型数据应包含在单引号中''
5.列可以插入空值
	insert into table values(null)
6.添加多条记录
	insert into table (column...)
		values(value...),(value...),(value...)
7.如果给表中的所有列添加数据 可以不写前面的列名
    insert into table_name values(value...)
8.不给某个字段值时 如果有默认值就添加默认值 否则报错
  如果某个字段没有指定NOT NULL 那么添加数据时没有给定值 则默认给NULL
9.timestamp 日期类型 你不给值或者给null值 他就会取系统的时间 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

删-delete

-- 删除表中的数据
-- 逐行删 如果数据多 删的慢
delete from table_name 
	where where_definition
-- where_definition判断删除那些行
-- 不写WHERE表示删除表中的所有数据

-- 删除所有记录
-- 先删除表 再创建表 效率高一些
truncate table 表名;

-- 删除employee表中数据 
-- 	删除 id字段为1 的数据	
delete from employee 
	where id = 1	
	
1.如果不使用WHERE子句 将删除表中的所有数据
2.DELETE不能删除某一列的值(可以使用UPDATE将某列的值设为null或'')
3.使用DELETE只能删除表中数据 不能删除表本身
--  如果要删除表 可以使用 drop table 表名;
--  如果要删除列 可以使用 alter table 表名 drop 列名;

★truncate 清空表
	格式:
		truncate 表名; 干掉表,重新创建一张空表
	和delete from 区别:
		delete属于DML语句  truncate属于DDL语句
		delete逐条删除	truncate干掉表,重新创建一张空表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

改-update

-- 修改表中数据
update table_name
	set col_name1 = expr1, col_name2 = expr2 ...
	where where_definition
-- col_name1要修改的列名 expr1要修改后的值
-- where_definition判断修改那些行 
-- 不写WHERE表示修改所有行

-- 修改employee表中的数据
-- 	只修改id字段为2的行中的salary值
update employee
	set salary = 9000
	where id = 2

1.update语法可以用新值更新原有表中的各列
2.set子句指示要修改那些列和要给予那些值
3.where子句指定应更新那些行
  若不写WHERE子句 则更新所有的行
4.如果要修改多个字段 可以通过
  set 字段1 = 值1, 字段2 = 值2 ...  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

DQL: 查询

查询表中数据

●查询表中数据
    SELECT [DISTINCT] *|{column1, column2 ...}
        FROM table_name;
    DISTINCT可选 在显示结果时 每一列都相同,则去掉重复的数据
    *代表查询所有的列
    column查询指定的列名
    FROM指定查询哪张表

-- 查询student表中所有学生信息
SELECT * FROM student;
-- 查询student表中学生的姓名和学生的英语成绩
SELECT `name`,english FROM student 
-- 过滤表中重复的数据
SELECT DISTINCT * FROM student
1
2
3
4
5
6
7
8
9
10
11
12
13
14

表达式对查询的列进行运算

●使用表达式对查询的列进行运算
    SELECT *|{column1|expression, column2|expression ...}
        FROM table_name;
    expression表达式
    |代表或
    
-- 查询student表中学生的姓名和学生的总成绩
SELECT `name`,(chinese+english+math) 
    	FROM student;
1
2
3
4
5
6
7
8
9

添加别名(as)

●使用as语句 添加别名
	SELECT column_name as 别名 FROM 表名;
	
#使用别名来表示学生的总成绩
SELECT `name`,(chinese+english+math) AS totoal_score 
		FROM student;
#简写别名 
SELECT `name`,(chinese+english+math) totoal_score 
		FROM student;		
1
2
3
4
5
6
7
8
9

select查询语句顺序

DQL: 查询
书写顺序:
    SELECT 
        selection_list /*要查询的列名称*/
    FROM 
        table_list /*要查询的表名称*/
    WHERE 
        condition /*行条件*/
    GROUP BY 
        grouping_columns /*对结果分组*/
    HAVING 
        condition /*分组后的行条件*/
    ORDER BY 
        sorting_columns /*对结果排序*/
    LIMIT 
        offset_start(起始索引), row_count(展示数量) /*结果分页*/
        
执行顺序:       
(7)  SELECT 
(8)  DISTINCT <select_list>
(1)  FROM <left_table>
(3)  <join_type> JOIN <right_table>
(2)  ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  HAVING <having_condition>
(9)  ORDER BY <order_by_condition>
(10) LIMIT <limit_number>        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

where子句查询

-- 查询姓名为赵云的学生成绩
SELECT * FROM student
	WHERE `name` = '赵云';	
	
-- 查询英语分数在80-90之间的学生
SELECT * FROM student
	WHERE english BETWEEN 80 AND 90;
	
-- 查询数学分数为89,90,91的学生
SELECT * FROM student
	WHERE math IN(89, 90, 91);

-- 模块查询 like 
-- 模糊查询时要用到两个通配符
-- % 匹配多个任意字符
-- _ 匹配单个任意字符
-- 查询所有姓韩的学生成绩
SELECT * FROM student
	WHERE `name` LIKE '韩%';
	
-- 我要查询姓名第二个字母是D
SELECT * FROM emp WHERE ename LIKE '_D%';	
	
-- 查询奖金为null的
SELECT * FROM emp 
	WHERE comm IS NULL; 

-- 查询奖金不为null的
SELECT * FROM emp 
	WHERE comm IS NOT NULL;	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

分组统计(group by–having)

●group by子句对列进行分组
    SELECT column1, column2 ... 
        FROM table_name
        GROUP BY column 
        
●having子句对分组后的结果进行过滤
	SELECT column1, column2 ... 
        FROM table_name
        GROUP BY column having ...
        
注意这里的where是对分组前的条件限定 也就是说不满足条件的 不参与分组   
where和having区别
    where:在分组之前对条件进行限定。不满足条件，就不会参与分组
    having:在分组之后 对结果集的筛选
        
#显示每个部门的平均工资和最高工资
SELECT deptno , AVG(sal) ,MAX(sal)
	FROM emp GROUP BY deptno;
	
#显示每个部门的每种岗位的平均工资和最低工资
SELECT deptno, job, AVG(sal), MIN(sal) 
	FROM emp GROUP BY deptno, job;
	
#显示平均工资低于2000的部门号和它的平均工资
#HAVING 是对分组之后的结果 进行再次筛选
SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING avg_sal < 2000;

#每个部门的部门编号 以及每个部门工资大于1500的人数
#where 在分组之前进行条件的筛选。
SELECT deptno AS '部门编号',COUNT(*) AS '部门人数' 
	FROM emp 
	WHERE sal>1500 
	GROUP BY deptno;
	
#查询各个部门 员工工资大于1500 的平均工资 
#并且平均工资 大于3000的部门
SELECT deptno AS '部门编号',AVG(sal) AS '部门平均工资' 
    FROM emp 
    WHERE sal>1500 
    GROUP BY deptno 
    HAVING 部门平均工资>3000;	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

排序(order by)

●使用order by语句 排序
	SELECT column1, column2 ...
		FROM table
		ORDER BY column asc(默认 升序)|desc(降序)
	
#对数学成绩排序输出(升序)
SELECT * FROM student
	ORDER BY math;

#对总分按从高到低的顺序输出
SELECT `name`,(math+chinese+english) AS total_score 
	FROM student
	ORDER BY total_score DESC;	

#对姓韩的学生总分排序输出(升序)
SELECT `name`,(math+chinese+english) AS total_score 
	FROM student
	WHERE `name` LIKE '韩%'
	ORDER BY total_score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

分页展示(limit)

LIMIT 起始索引, 展示数量 /*结果限定*/

#分页展示
#起始索引=(页码-1)*每页条数

#一页展示 3条 
#第一页
SELECT * FROM emp LIMIT 0,3;

#第二页
SELECT * FROM emp LIMIT 3,3;

#第三页
SELECT * FROM emp LIMIT 6,3;

#既有条件，又有排序，又有分页
SELECT * FROM emp 
    WHERE comm IS NOT NULL 
    ORDER BY comm DESC 
    LIMIT 0,3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

合并查询(union)

-- union all 就是将两个查询结果合并 不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union  就是将两个查询结果合并 会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3
1
2
3
4
5
6
7
8
9

DCL:数据库权限

★mysql数据库权限问题
	root:拥有所有权限
	权限账户:只拥有部分权限(CURD)
		例如，只能操作某个数据库的某张表

★mysql数据库 用户配置:user表
-- 使用数据库
USE mysql; 
-- 查询用户密码
Select password('root'); 
-- 查询数据库用户
SELECT * FROM USER;  
  
★修改mysql用户密码
-- MySQL5.5修改密码
UPDATE USER 
    SET PASSWORD=PASSWORD('123456') 
    WHERE USER='root';
-- MySQL5.7修改密码 
UPDATE USER 
    SET authentication_string=PASSWORD('123456') 
    WHERE USER='root';
    
★分配权限账户
方式一
    语法:
    GRANT 权限 
        ON 数据库名.某张表名 TO 
        '用户名'@'localhost' IDENTIFIED BY '123456';
    #@后面可以是localhost也可以是ip 也可以给% 
    #(%代表任意一台计算机都可以连接上来)
    例子:	
    GRANT SELECT 
        ON my_database.`users` TO 
        'root'@'localhost' IDENTIFIED BY '123456';
    #注意分配多个权限用逗号隔开
    例子:
    GRANT DELETE,SELECT,UPDATE 
        ON my_database.`users` TO 
        'root'@'localhost' IDENTIFIED BY '123456';
方式二        
    INSERT INTO USER(HOST, USER, PASSWORD, 
    select_priv, insert_priv, update_priv) 
        VALUES ('localhost', 'guest',
                PASSWORD('guest123'),
                'Y', 'Y', 'Y');	

★删除用户
Delete FROM user 
	Where User='用户名' 
	and Host='localhost';

★备份数据库
-- 备份数据库(在DOS执行)命令行
-- mysqldummp -u 用户名 -p -B 数据库1 数据库2 >文件名.sql
mysqldump -u root -p -B db01>d:\\bak.sql;

-- 备份数据库的表(在DOS执行)命令行
-- mysqldummp -u 用户名 -p 数据库 表1 表2>文件名.sql
mysqldump -u root -p db01 users>d:\\bak.sql;

-- 恢复数据库(进入Mysql命令行在执行)
-- source 文件名.sql
mysql -u root -p
source d:\\bak.sql;

-- 恢复数据库(复制方式)
-- 新建新查询编辑器->复制bak.sql内容->粘贴到新查询编辑器->运行	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

远程登录数据库

1.切换库
use mysql; 

 2.授权远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 

3.刷新
flush privileges; 

4.然后退出mysql
exit;
  
5.启动服务
service mysqld start

6.查看状态
service mysqld status

7.远程登录
mysql -h IP地址 -P 3306 -u root -p123456
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

数据库表的设计

数据库设计
	引入
		需求分析 -> 需求分析师 -> 原始需求 -> 抽取业务模型
	 
     	图书模型: 图书名称 版本号 作者 出版社
     	学生模型: 学号 学生姓名 手机号码
	 	......
	 	角色: 学生 老师 图书管理员
	 
		《需求说明书》
	 	需求设计 
			概要设计		
				抽取实体: 业务模型 -> 实体模型(java类 c++类)内存
						 class Book{ name, bookNo, author }
					 数据库设计:
						 业务模型/实体模型 - > 数据模型(硬盘)
				
					 数据库表设计
					 问题: 如何设计？
				详细设计
					类详细 属性和方法
					
三大范式
	设计原则: 建议设计的表尽量遵守三大范式

第一范式: 要求表的每个字段必须是不可分割的独立单元
	student : name              -- 违反第一范式  
			  张小名|狗娃					
	sutdent : name   old_name   --符合第一范式
			  张小名    狗娃

第二范式: 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。			
	employee(员工): 员工编号 员工姓名 部门名称 订单名称  --违反第二范式

	员工表: 员工编号 员工姓名 部门名称   
	订单表: 订单编号 订单名称            -- 符合第二范式
					      
第三范式: 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
	员工表: 员工编号(主键) 员工姓名 部门编号  部门名 --符合第二范式，违反第三范式(数据冗余高)
	员工表: 员工编号(主键) 员工姓名 部门编号    --符合第三范式(降低数据冗余)
    部门表: 部门编号  部门名 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41