mysq 入门*进阶 （学习笔记）

mysq 入门*进阶 （学习笔记）

数据库的种类：
线性数据库
网状数据库
树状数据库
关系型数据库(由行和列组成)
Mysql(中型数据库 免费 甲骨文公司) 中小企业使用 在较大数据量的时候 处理初速较慢，效率低
Oracle(大型数据库 收费 甲骨文公司) 大型企业使用，在较大数据量的时候 处理初速较快，效率高
Db2(大型数据库 收费 IBM)
Sqlserver（中型数据库 免费 微软）

数据库的结构：
服务器，库，表

一个服务器多个库多张表

MYSQL服务的开关：
第一种方式：
右键我的电脑管理服务和应用程序服务 找到mysql 打开和关闭
services.msc 可以替代找到服务的过程 找到mysql 打开和关闭
第二种方式：
Net start mysql服务的名字
Net stop mysql服务的名字

服务器常用命令：
进入服务器 mysql –u用户名 –p密码 –h空格访问的ip –P端口号(默认3306)
本机: localhost ------127.0.0.1
不写 -h 默认本机 不写-P 默认3306
一般使用mysql –u用户名 –p 回车后 输入密码 这样 安全。
\c 当输入错误时 用\c不会报错
Dos窗口快捷设置本次编码 set names gbk;
常用命令：
查看服务器下有哪些库：show databases 注意information_schema 和mysql不要去动
添加一个库： create database 库名
删除一个库：drop database 库名
重命名一个库:不能
进入一个库:use 库名
查看当前库有哪些表：show tables
创建一个表:create table 表名(字段名1 类型, 字段名2 类型,……. 字段名n 类型)
查看表的详细信息: desc 表名
删除表:drop table 表名
修改表名:rename table 老表名 to 新表名
给表添加一个字段: alter table 表名 add column 字段名 字段类型
更改字段的类型: alter table 表名 modify column 字段名 新类型
最简单的查询表内容: select * from 表名(存在当前库下) 全表全字段查询
往表里添加内容: insert into 表名 values(根据字段的顺序添加值)
修改表数据:update 表名 set columnName1=值1, columnName2=值2 where 条件判断
删除数据：delete from 表名 where 条件判断
告诉服务器编码方式：set names 编码集名.

创建字段的时候遵循:类型正确且合理 节省空间.
数据库的数据类型：
通用属性(极个别不能用):
Not null : 该列不允许不插入值，
Default: 当插入数据没有添加该列的值的时候的默认值.
Not null default 值: 当没有插入的该列的值，可以插入成功，值为默认值。
浮点型：
Float(5,2) 范围 有符号 -999.99 ~999.99 无符号0~999.99

特殊：
当float在定义的时候没有声明(M,D)时，在where 查询直接 用字段= float的值是查不到数据的,而要使用like去找。
(了解)在范围内，但是小数部分超过了标度的长度，标度的后一位会四舍五入。注意对5的处理，不入:0 1 3 5 7 入: 2 4 6 8 9
定点型:

float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）
double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）
decimal:数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位
利用率：
宽度定义 M
实际传入 N
Char N/M N可以等于M 所以利用率可以达到100%
Varchar N/( N+(1+2)字节) N可以等于M 永远不能达到100%
关于选择使用：
四字成语字典，省份证， 朋友圈， 用户昵称
Char(4) Char(18) Varchar(150) char(6)可以，速度快，资源浪费较少
varchar(6)也可以，牺牲速度，节省资源。

常见的约束:
1.主键约束:primary
主键关键字:primary key
已建好的表的主键添加：alter table 表名 primary key(字段名)
将字段设置为主键alter table student add constraint PK_字段名 primaryKey(studentId);
2.非空约束:not null
3.默认值:default
为字段添加默认值:alert table 表名 modify 字段名 set default 默认值
建表的时候为字段添加默认值: 字段名 类型 default 默认值
4.唯一约束:unique
主键和唯一键的区别：
1.主键不能为空 2.唯一键可以有多个，但主键只能有一个

5.自增:：auto_increment
6.外键约束:
外键:foreign key
注意1.外键只能作用在另一个表的主键上 2.创建顺序只能先创建主表
alter table 外键表 add constraint FK_约束名 foreign key ([外键列]) references [主键表] ([主键列])

语法：foreign key(外键所在字段名) references 主表名(主表主键所在字段名)
删除外键:alter table 表名 drop foreign 外键名;
级联操作: on delete[update] cascade
置为null: on delete[update] set null
7.检查约束:
check(检查字段条件)
enum(限制内容) 例：ceaet table student( sex enum( ‘男’ , ‘女’ ) );

数据库三范式：
三范式的作用：
约束数据库建表的规范性。
三范式的最终目标：
不存在冗余数据
第一范式：
不要向表中输入完全重复的数据（设置主键）
下面哪些字段可以作为主键;

第二范式：
存在多对多关系时只有一个字段作为主键是不够的。
部分依赖，会产生冗余数据，需要分解表
第三范式：
班级名称和班级信息这一组值没有必要的重复出现，这些值并不直接依赖于学号（主键），依赖于班级号，也就是时说表中出现了传递依赖 增查小细节：
增：
1.插入全部列
2.插入部分列
3.插入多条
查：
1.查询所有行所有列
2.查询部分列所有行
3.查询部分行所有列
4.表别名(两种方式)
5.字段别名(两种方式)
6.去除重复数据 distinct

查询语法详解

Where
条件比较
＝，！＝,<>，<,>,<=,>=,any,some,all
is null,is not null
between x and y
in（list），not in（list）
like _ ,%

逻辑复合条件
and，or

备份表数据
备份表结构（包含相关键，B预先不存在）
CREATE TABLE B_Table LIKE A_Table
备份表结据（把表A数据备份到表B中，B表结构与A结构一样）
INSERT INTO B_Table SELECT * FROM A_Table;

Order by(排序)

按照单个列排序
order by col

降序和升序：
order by col desc (asc)

按多个列排序(那么排序顺序是什么呢？)
order by col1 desc(asc), col2 desc(asc)

创建计算字段
Sql允许select子句中出现+，-，*，/以及列名和常数的表达式
计算字段并不实际存在于数据库表

函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。

Sql函数可以分为组函数和单行函数。
组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句
单行函数对单个数值进行操作，并返回一个值。
字符串函数
if(条件,值1,值2)
Length()字符串字节长度
Char_length():字符串字符长度
INSERT(str,pos,len,newstr)
SUBSTRING(str FROM pos)

数字函数日期函数
1、NOW
获取当前日期和时间的函数。
语法： NOW()
例如：select NOW();
2、CURDATE
获取当前的日期
语法：CURDATE()
3、CURTIME()
获取当前时间
语法：CURTIME()
4、DATE
获取日期时间或者日期的日期部分
语法：DATE(date)
date 参数是合法的日期表达式。
5、EXTRACT
获取返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
语法：EXTRACT(unit FROM date)
date 参数是合法的日期表达式。
unit 参数可以是下列的值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如获取当前年月：select EXTRACT(YEAR_MONTH FROM now()) ;
6、DATE_ADD
将日期添加指定的时间间隔。
语法：DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如：select DATE_ADD(now(),INTERVAL 2 DAY); //获取两天后的时间
7、DATE_SUB
将日期减去指定的时间间隔。
语法：DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是添加的时间间隔。
type 参数可以是下列值：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
例如：select DATE_SUB(now(),INTERVAL 2 DAY); //获取两天前的时间
8、DATEDIFF
获取两个日期之间的天数。（只有日期部分参与计算）
语法：DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。
例如：SELECT DATEDIFF(NOW(),‘2008-08-08’) AS 北京奥运会多天前;
9、DATEFORMAT
格式化日期时间
语法：DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。
格式：描述
%a：缩写星期名
%b：缩写月名
%c：月，数值
%D：带有英文前缀的月中的天
%d：月的天，数值(00-31)
%e：月的天，数值(0-31)
%f：微秒
%H：小时 (00-23)
%h：小时 (01-12)
%I：小时 (01-12)
%i：分钟，数值(00-59)
%j：年的天 (001-366)
%k：小时 (0-23)
%l：小时 (1-12)
%M：月名
%m：月，数值(00-12)
%p：AM 或 PM
%r：时间，12-小时（hh:mm:ss AM 或 PM）
%S：秒(00-59)
%s：秒(00-59)
%T：时间, 24-小时 (hh:mm:ss)
%U：周 (00-53) 星期日是一周的第一天
%u：周 (00-53) 星期一是一周的第一天
%V：周 (01-53) 星期日是一周的第一天，与 %X 使用
%v：周 (01-53) 星期一是一周的第一天，与 %x 使用
%W：星期名
%w：周的天 （0=星期日, 6=星期六）
%X：年，其中的星期日是周的第一天，4 位，与 %V 使用
%x：年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y：年，4 位
%y：年，2 位

组函数

•组函数基于多行数据返回单个值
•avg()返回某列的平均值
•min()返回某列的最小值
•max()返回某列的最大值
•sum()返回某列值的和
•count()返回某列的行数
•组函数仅在选择列表和Having子句中有效

•组函数除了count()外，都跳过空值而处理非空值
–select count() from emp;
–select count(comm) from emp;

ifnull(参数,值) 如果参数是null 显示出来的值 就是函数第二个参数

组函数一般结合分组使用
–group by 子句
–Group by 子句可以包含任意数目的列。
•除组函数语句外，select语句中的每个列都必须在group by 子句中给出。(oracle特性，mysql不是)
–如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。
–Group by 子句必须出现在where子句之后，order by 子句之前。
•过滤分组（having子句)
–Where过滤行，having过滤分组。
–Having支持所有where操作符(写在group后面)。
where 后面不能直接跟组函数

•SELECT distinct column, group_function
•FROM table
•[WHERE condition]
•[GROUP BY group_by_expression Having ….]
•[ORDER BY column];
•使用GROUP BY子句将表分成小组
•结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句

出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中
练习:查找各个部门的平均工资

不能在 WHERE 子句中使用组函数.不能在 WHERE 子0句中限制组. 使用Having 对分组进行限制
练习:查询部门平均工资大于2000的部门

多表查询

• 语法规则：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
为了连接n个表，至少需要n-1个连接条件。

笛卡尔积
•select count() from emp
•select count() from dept
•select emp.empno,dept.loc
from emp,dept
•检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
•检索出的列的数目将是第一个表中的列数加上第二个表中的列数
•应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据

等值连接
例子：查询所有员工的部门详细信息
查询名叫‘'JAMES’的员工的员工信息和公司信息

非等值链接：
例子：查询所有员工的员工信息和工资等级

多于两个表的连接
查询员工的员工信息，公司信息，工资等级

表连接
通过join连接表，通过on字句限定连接条件

交叉连接CROSS　JOIN
	等同于tab1,tab2
	例子同显示员工及部门完整信息。

自然连接NATURAL JOIN
• NATURAL JOIN子句基于两个表中列名完全相同的列产生连接
–两个表有相同名字的列
–数据类型相同
–从两个表中选出连接列的值相等的所有行
自然连接的结果不保留重复的属性

例子 查询部门为10的员工完整信息

左连接
LEFT JOIN
经典例子：
查询所有部门下的所有员工信息

右连接
RIGHT JOIN
同left join 只是位置上的区别

自连接
	查询每个员工的员工信息和经理名字

全连接  FULL  JOIN
	Mysql没有全连接

Union : 将字段及其结果完全一样的值 融合，不同值时，不融合
Union all: 将字段及其结果 都不融合，添加在一起，作为新的结果
子查询
• SQL允许多层嵌套。子查询，即嵌套在其他查询中的查询。
•SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
•理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。
–子查询要用括号括起来
–将子查询放在比较运算符的右边(增强可读性)
子查询的种类
单行子查询
• 子查询返回一行记录
• 使用单行记录比较运算符
= > < >= <= != <>

例子：查询有哪些人的薪水是在整个雇员的平均薪水之上


**多行子查询**

•子查询返回多行行记录
•使用集合比较运算符
in some all

在多行子查询中使用in

例子：我们要查在雇员中有哪些人是经理人，也就是说，有哪些人的empno号在mgr这个字段中出现过，这个时候，应当首先查询mgr中有哪些号码，然后再看看有哪些人的雇员号码在此出现

在多行子查询中使用some all
例子:
找出部门编号为20的所有员工中收入最高的职员.