Mysql基础

vampire 2020年09月15日 20次浏览

数据库

  1. 使用数据库目的:

    • 数据持久化
    • 有组织/结构存储数据,方便数据增删改查
  2. 定义

    • DB: Database:数据仓库,用于存储

    • DBMS: 数据管理系统(服务端TCP/IP网络应用程序)

      mysql, Oracle, sql server, access, redus, mango db

    • SQL: (Structured Query Language)

  3. 安装与卸载

    • 卸载

      1. 停止mysql服务
      2. 移除即可
      3. 清理遗留文件
      4. 清理注册表
      
    • 安装

  4. mysql属于关系型数据库(二维表格)

  5. 使用

    客户端连接:
    	命令行客户端
    		mysql -h localhost -P 3306 -u root -p
    		说明:
    			(1)-h, -P, -u后面可以有空格也可以没有空格
    			(2)默认连接本机可以省略-h localhost
    			(3)默认端口为3306,可以省略
    	Java程序
    	可视化工具
    
  6. 基本sql语句:

    show databases; 查询当前数据库
    user database; 使用数据库
    show tables; 查看当前数据库有哪些表
    show tables from database;
    select * from user; 查看某张表的数据
    create table stu{
    	id int,
    	name varchar(20),
    	age int
    }; 创建表格
    set name gbk;编码
    insert into stu
    values
    (1, '张三',23);插入数据
    

Mysql数据类型

  1. 整型

    tinyint 1字节
    smallint 2字节
    mediumint 3字节
    int 4字节
    bigint 8字节
    eg: int(M):
    	M是指宽度,单独使用没有意义,必须结合zerofill unsigned
    
  2. 浮点型

    float, double
    double(M, D) 指定精度, 宽度和小数位
    double(M, D) unsigned 范围:0~999.99
    
  3. 定点型

    DECIMAL, NUMERIC
    都可以表示小数
    
  4. 日期时间类型

    Java: java.sql.Date, java.sql.Time, java.sql.Timestamp
    mysql:
    	date: 日期
    	time:时间
    	timestamp, datetime:日期加时间
    	year:年
    	timestamp:底层使用毫秒表示,可以区分时区(同一毫秒值,在不同时区显示适合的时区),范围为:1970年~2038年
    	datetime:用日期和时分秒表示,范围为:1000~9999
    
  5. 字符串类型

    char: 定长字符串,读写字节数
    varchar: 变长字符串(实际存字符和字符的字节数),读写速度慢,节省空间,必须指定varchar(M)
    text: 
    char/char(1)表示存一个字符
    
  6. 其他类型

    xxxbit
    xxxblob:二进制类型,可以存储二维码,小头像
    枚举类型:预定义几个值,从中选一个
    集合类型:预定义几个值,从中选多个
    
  7. 特殊值

    null:所有类型都可以赋值为null
    判空:is null/ is not null
    计算:不会报错,结果均为null
    

SQL的规则规范

  1. 规范
    • mysql对于SQL语句不区分大小写,SQL语句关键字尽量大写
    • 字符串型和日期类型使用’‘单引号
    • 列/字段别名,尽量使用双引号(""), 不建议省略as'
    • 所有括号,引号成对出现
    • # 单行注释 -- 单行 /*多行注释*/
    • 命名规则:A-Z, a-z, 0-9,_共63个字符,不能包含空格,不能过长
    • 不能重名
      • 同一张表,字段不能同名
      • 同一个库,表不能同名
      • 同一个DBMS中,库不能同名
      • 保证没有保留字
      • 字段在不同表中,数据类型必须相同

SQL语句

DDL

  • 数据库定义语言,定义库,表结构用的
  1. 操作数据的语句

    show database; # 显示所有数据库
    create database 数据库名; # 创建数据库
    drop database 数据库名; # 删除数据库
    use 数据库名; # 制定使用数据库
    
  2. 操作表格的语句

    show tables; # 查询数据库中的表
    create table 表名称 (
    	字段名1 数据类型,
        字段名2 数据类型,
        字段名3 数据类型
    );
    
  3. 查看表结构

    desc 表名称;
    
  4. 修改表结构

    # 增加一列
    alter table 表名称 add 字段名 数据类型 [after 字段名/first];
    # 删除一列
    alter table 表名称 drop 字段名
    # 修改表结构:修改列的类型位置等
    alter table 表名称 modify 字段名 数据类型 [after 字段名/first];
    # 修改表结构:修改列的名称
    alter table 表名称 change 旧字段名 新字段名 数据类型 [after 字段名/first];
    # 修改表名称
    alter table 表名称 rename to 新名称;
    # 删除整张表,包括数据和表结构
    drop table 表名称
    

DML

  • 数据库操作语言,增删改查

    # 添加数据
    -- 为表所有字段赋值
    insert into 表名称 values (值列表);
    -- 为表部分字段赋值
    insert into 表名称 (字段列表) values (值列表);
    -- 一次性插入多行数据
    insert into 表名称 values (值列表1), (值列表2);
    insert into 表名称 (字段列表) values (值列表1), (值列表2);
    # 修改数据
    update 表名称 set 字段名1=字段值1, 字段名2=字段值2 where ……
    # 删除数据
    delete from 表名称 where ……
    # 查看数据
    select * from 表名称;
    select 字段列表 from 表名称;
    select 字段列表 from 表名称 where ……;
    # 查看结果时给字段取别名
    select 字段名1 as "别名1", 字段名2 as "别名2" from 表名称 where ……;
    

DCL

  • 数据库控制语言,权限,事务等控制语句

约束

数据完整性:数据的精确性和可靠性。数据完整性从以下方面考虑

  • 实体完整性:不能有完全无法区分的记录
  • 域完整性:范围
  • 引用完整性:
  • 用户自定义完整性

根据约束的特点,分为几种:

  • 键约束:

    • 主键约束

      * 概述
          1. 关键字:primary key
          2. 特点:增加主键约束的列(字段)的值必须非空唯一
          3. 作用:保证表中不会出现无法区分的记录
          4. 要求:每张表必须有主键约束
          5. 分类:单列主键约束、复合主键约束
      * 使用
      	1. 创建指定约束
      		* 建表时指定
      		* 建表后增加,修改表结构
      			alter table 表名 add primary key(字段名);
      	2. 删除主键约束
      		* 修改表结构
      			alter table 表名 drop primary key;
      	3. 复合主键
      		* 需要单独指定
      
    • 唯一键约束

      * 概述
      	1. 关键字:unique key
      	2. 特点:指定了唯一键的列的值必须唯一,不能重复
      	3. 作用:给主键以外的列,限定唯一性
      	4. 分类:单列唯一键、复合唯一键
      * 唯一键和主键的区别
      	1. 主键不能为空,唯一键可以为空
      	2. 主键约束,一个表只能有一个,而唯一键可以有多个
      * 创建/指定唯一键
      	1. 在建表时
      	2. 建表后:修改表结构
      * 删除唯一键:
      	修改表结构
      	alter table 表名称 drop index 索引名;
      	查询索引名:show indexfrom 表名称;
      	索引:index
      	键约束会自动创建索引
      
    • 外键约束

      * 概述:
      	外键约束不是必须的,数据量大时,不建议在数据库层面设计外键,将数据的约束挪到代码层面去判断。
      	1. 关键字:foreign key
      	2. 特点:
      		(1) 约束两张表,需要两张表或者虚拟成两张表
      		(2) 两张表分为主表(父表)和从表(子表)
      			外键的建立和指定时在从表上进行的
      		(3) 被参考的表为主表,主表的被参考列必须是主键或唯一键
      		(4) 一个表可以有多个主键
      * 指定外键
      	1. 建表时指定
      		先建主表,再建从表
      		先删从表,再删主表
      		foreign key(字段名) references 主表(字段名)
      	2. 删除/修改主表记录
      		a. 默认情况下,主表的被参考列的值被引用,不能被轻易删除和修改
      			foreign key(字段名) references 主表(字段名) on update restrict/(no action) on delete restrict
      		b. 如果再建立外键时指定了级联策略,那么可以级联修改和删除
      			foreign key(字段名) references 主表(字段名) on update cascade on delete cascade
      		c. 如果再建立外键时指定了置空策略,那么主表发生变化是,从表对应字段值会置空
      			foreign key(字段名) references 主表(字段名) on update set null on delete set null
      	3. 建表后指定外键
      		alter table 从表名称 add foreign key references 主表(字段名);
      	4. 同一张表自引用
      	5. 查看外键约束名
      	select * from information_schema.table_constraints where table_name = '表名称';
      	5. 删除外键约束
      	alter table 表名 drop foreign key 外键约束名
      
  • 非空约束和默认值约束

    * 指定非空约束
    	1. 建表时, not null default '默认值'
    	2. 建表后, 
    		alter table 表名称 modify 字段名 数据类型 not null default "默认值";
    
  • 自增约束

    1. 关键字:auto_increment
    2. 特点:
    	* 一个表只能有一个自增列
    	* 自增列必须是整型的
    	* 自增键必须是键列,eg.主键,唯一键
    3. 指定自增
    
  • 检查约束(Mysql不支持)

运算符

算术运算符

/*
	加:+
	减:-
	乘:*
	除:/ 或 div (div为整除)
	模:% 或 mod
*/

比较运算符

/*
大于:>
小于:<
等于:=
不等于:!= 或 <>
安全等于:<=> 判断NULL值
*/

逻辑运算符

/*
	与:&& 或 and
	或:|| 或 or
	非:! 或 not
*/

范围和集合运算符

/*
	between xx and yy: [xx, yy]
	not between xx and yy: <xx or >yy
	in (值列表)
	not in (值列表)
*/

模糊查询

/*
	like 'xx'
	占位符:
		_: 确定的一个字符
		%: 任意多个字符,0~n个
*/

关联查询

内连接 inner join

# 形式一
select 字段列表 from A表 inner join B表 on 关联条件 where 筛选条件
# 不写关联条件,会出现笛卡尔积
# 形式二
select 字段列表 from A表 , B表 where 关联条件

外连接

左外连接 left join

第一种结果:A
	select 字段列表
	from A表 left join B表
	on 关联条件;
第二中结果:A - A ∩ B
	select 字段列表
	from A表 left join B表
	on 关联条件
	where 从表的关联字段 is null;

右外连接 right join

第一种结果:B
	select 字段列表
	from A表 right join B表
	on 关联条件;
第二中结果:B - A ∩ B
	select 字段列表
	from A表 right join B表
	on 关联条件
	where 从表的关联字段 is null;
/*
	where xxx is null
	xxx看从表
	例如, 员工表和部门表来说,员工表是从表
*/

全外连接 full join

  • mysql不支持
/*
  第一种结果:A ∪ B
	select 字段列表
	from A表 left join B表
	on 关联条件
	union
	select 字段列表
	from A表 right join B表
	on 关联条件;
  第二种结果:A ∪ B - A ∩ B
	select 字段列表
	from A表 left join B表
	on 关联条件
	where A表的关联字段 is null;
	union
	select 字段列表
	from A表 right join B表
	on 关联条件
	where A表的关联字段 is null;
*/

自连接

/*
特例:自连接
联合查询需要两张表,现在自连接一张表当两张表用
通过给表取别名的方式
表的别名不加双引号,字段别名加双引号
*/
# 查询员工的编号,员工的姓名,领导的编号,领导的姓名
# 员工和领导的信息都在employee表
SELECT emp.eid, emp.ename, emp.mid, mgr.ename
FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.`mid` = mgr.`eid`;

关联查询结果

1. A ∩ B 内连接
2. A
3. A - A ∩ B
// 2, 3左连接
4. B
5. B - A ∩ B
// 4, 5右连接
6. A ∪ B	# 2和4的结果union
7. A ∪ B - A ∩ B # 3和5的结果union
// 6, 7本应用全外连接,mysql用union

select语句的五个子句

/*
分组函数
(1)sum 求和
(2)count
	如果count(字段名), 不会统计NULL值
	如果count(常量值)或count(*)统计行数
(3)avg 求平均值
(4)max 求最大值
(5)min 求最小值
select的五个子句:
(1)where
	where 条件, 筛选符合条件的行记录
(2)group by 分组字段
	分组统计:
	可以按照多个字段分组,用逗号分隔分组字段
	查询不能查询非group by的字段
(3)having
	条件过滤
	统计结果筛选
(4)order by:排序
	DESC:降序
	ASC:升序(默认)
	可以多个字段排序,字段可以是统计结果
(5)limit:分页
	limit m, n
	m = (page-1)*每页记录数
	n = 每页记录数
	
五个子句可以同时出现,也可以出现部分
having必须,前面必须有group by
有group by 不一定有having
五个子句多个同时出现,必须按照1-5的顺序

where和having的区别:
(1)where后面不允许使用分组函数
(2)where用于原表筛选,having用于统计结果的筛选
*/

子查询

/*
某些情况下,当进行一个查询时,需要的条件或数据要用另外一个select语句的结果
此时使用子查询
先于当前查询,嵌套在当前查询中的查询为子查询
分类:
(1)where型
	子查询嵌套在where里面
	条件运算符分为两类:
		=, >, <, >=, != 子查询结果为单值
		in, = ANY, >=ALL, <=ALL 子查询结果可以为多值
(2)from型
(3)exists型查询
*/

# (1)查询全公司最高工资的员工的信息
SELECT * FROM t_employee WHERE salary = (SELECT MAX(salary) FROM t_employee);
# 查询和孙红雷,刘烨, 范冰冰任意一个工资一样的员工
SELECT * FROM t_employee WHERE salary IN (
	SELECT salary FROM t_employee WHERE ename IN ('孙红雷', '刘烨', '范冰冰'));
	
SELECT * FROM t_employee WHERE salary = ANY (
	SELECT salary FROM t_employee WHERE ename IN ('孙红雷', '刘烨', '范冰冰'));
/*
from型
*/
# 查询每个部门编号,部门名称,和平均工资,排除没有部门的员工, 包括没有员工的部门
# 第一步查询每个部门的平均工资,排除没有部门的员工
SELECT did, AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did;
# 第二步用刚才的结果和t_department联合查询
SELECT t_department.*, temp.pingjun
FROM t_department LEFT JOIN (SELECT did, AVG(salary) AS pingjun FROM t_employee WHERE did IS NOT NULL GROUP BY did) AS temp
ON t_department.did = temp.did;
# 查询部门信息,该部门必须有员工
# 使用exists
SELECT * FROM t_department
WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.`did` = t_department.`did`)

函数

常用的函数查询,可以使用API来查询
/*
单行函数和多行函数:

数学函数:
	round(x, y): x小数点后四舍五入有y位的小数的值
	truncate(x, y): x小数点后截取有y位的小数的值
	ceil(x): 向上取整
	floor(x): 向下取整
	abs(x): x的绝对值
	mod(x, y): x/y的模
	sqrt(x): 返回x的平方根
	pow(x, y): x的y次方
	
字符串函数:mysql字符串下表从1开始
		mysql中+只能进行加法运算,有非法值尽力做加法
	left(s, n): 字符串s最左边的n个字符
	right(s, n): 字符串s最右边的n个字符
	substring(s, index, len): 字符串s下表index之后的len个字符
	length(s): 字符串的字节长度,utf-8一个汉字三个字节
	char_length(s): 字符串的长度
	concat(s1, s2, ……, sn): 连接s1, s2, ……, sn为一个字符串
	trim系列函数
		trim()去掉前后空白字符
		ltrim()去掉左边空白字符
		rtrim()去掉右边空白字符
		trim(both c from s):从s两边去掉字符c
		trim(leading c from s):从s头部去掉字符c
		trim(trailing c from s):从s尾部去掉字符c

日期时间函数:
	now(), sysdate():获取系统当前日期时间
	current_date(),current_time():获取当前日期/时间
	year(current_date()):获取年份
	datediff(date1, date2): 日期间隔,天数
	date_add(datetime, interval expr 常量): 返回与给定日期相差interval时间段的日期时间
	date_format(datetime, fmt): 格式化日期
	str_to_date(str, fmt): 按照fmt格式解析字符串为日期


流程控制语句函数
(1)ifnull(x, value):如果x是null用value计算,否则还是x计算
(2)case
     when 条件1 then result1
     when 条件2 then result2
     ……
     [else resultn]
     end
(3)case expr
     when 常量值1 then 值1
     when 常量值2 then 值2
     ……
     [else 值n]
     end
 
加密函数
    password("密码");
    MD5("密码");
*/

事务

/*
事务:表示一组操作(sql),要么同时成功,要么同时失败,那么这种操作就构成了事务。
eg: 
	转账
需要在转账之前开启事务,如果同时成功就提交事务,否则回滚事务

涉及到的语句:
(1)开启事务的语句
start transaction;
(2)提交事务/回滚事务
commit;/rollback;

mysql默认情况是自动提交事务模式,即执行一句,自动提交一句,一旦提交不能回滚。
开启手动提交模式,通过以下设置(JDBC中使用)
	(1) set autocommit = false;
开启手动提交之后,每一组语句都要手动提交或回滚才算结束

(2)自动模式中(只能在命令行中用)
	执行一组事务:
	(1)开启事务的语句
	start transaction;
	(2)提交事务/回滚事务
	commit;/rollback;

事物的特点:ACID
(1)原子性:事务sql语句不能再划分,一组操作是否符合同时成功,同时失败
(2)一致性:保证事务前后数据一致性
(3)隔离性:事务之间是独立的
(4)持久性:一旦提交不可回滚
mysql中只有InnoDB引擎支持事务
事务只对DML语句有效,对DDL语句无效
*/

# 开启手动提交模式, 作用范围是一次连接
SET autocommit = FALSE;

事务的隔离级别

/*
事务的隔离级别:
1. 为什么隔离?
保证事务的独立
两个事物对同一个表的同一个记录的修改,会相互影响
也就是java里面线程安全问题;
表现出来的问题现象:
(1)脏读:
	一个事务读取了另外一个事务还未提交的数据,
	READ-UNCOMMITTED:产生脏读,不可重复读,幻读
(2)不可重复读:
	一个事务读取了另外一个事务已提交的数据,导致一个事务期间对同一个数据的前后两次读取,结果不一致。
	READ-COMMITTED:产生不可重复读,幻读
(3)幻读:
	一个事务读取了一个事物新增/删除,并已经提交的数据,导致一个事务期间记录数不同
	REPEATABLE-READ:幻读
	INNODB使用了MVCC多版本并发控制技术防止幻读,其他事务新增/删除的记录也看不出来
(4)SERIALIZABLE: 序列化
	串行化解决所有问题,但是效率低。

REPEATABLE-READ和SERIALIZABLE的区别:
	REPEATABLE-READ加的是行锁
	SERIALIZABLE假的是表锁
提高事务级别来避免问题


2. mysql的隔离级别:
(1)READ-UNCOMMITTED: 读取未提交的数据
(2)READ-COMMITTED: 读取已提交的数据
(3)REPEATABLE-READ:可重复读(默认级别)
(4)SERIALIZABLE: 序列化读

查看当前连接的隔离级别
	select @@tx_isolation;
修改当前连接的隔离级别
	set tx_isolation = 'READ-UNCOMMITTED';
*/

用户与权限

/*
mysql: 权限验证
(1)是否能够连接
	mysql用户的认证是通过主机地址+用户名+密码
		主机地址为%,任意主机都能访问
		localhost/127.0.0.1,则只能本机访问
(2)验证权限
	A:全局权限,针对所有库,所有表,所有字段的权限
		如果某个权限有全局权限,则不判断是否有对象权限
	B:对象权限
		某个库,某个表,某个字段都可以单独分配权限
保存在user表中

*/