1.建库建表
-- 1. 创建一个school 数据库
-- 2. 创建学生表（列，字段）使用SQL 创建
-- 3. 创建 学生登录密码varchar(20) 姓名，性别varchar(2) 出生日期（datatime）,家庭地址，email
-- 注意， 使用英文() ，表的名称 和字段尽量用 `` 括起来
-- AUTO INCREMENT 自增
-- 字符串用单引号括起来‘
-- 所有的语句后面加 （，） 英文逗号， 最后一个不用加
-- PRARMARY KEY 主键，一般一张表只有一个唯一的主键；
CREATE TABLE
IF
NOT EXISTS `student` (
`id` INT ( 5 ) AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` date DEFAULT NULL COMMENT '生日',
`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8
select * from student
创建表的格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...........
`字段名` 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
常用命令
show create database -- 数据库名 查看创建数据库的语句
show create table -- 表名 查看创建表的语句
DESC 表名 -- 显示创建表的结构
2.数据库类型
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大 约为MYISAM的两倍
常规操作:
- MYISAM 节约空间,速度快
- INNODB 安全性高,事务的处置,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下
每个数据库对应data下的每个文件夹, 本质还是要以文件的方式进行存储
MySQL 引擎在物理文夹你上的区别
- INNODB
在数据库表中只有一个*.frm ,以及上级目录下的ibdata文件 - MYISAM
-
*.frm 存放表结构的定义文件 -
*.MYD 数据文件(data) -
*.MYI 索引文件(index)
-
设置数据库表的字符集编码
charset = utf8
不设置的话回事mysql 默认的字符集编码 - 不支持中文
- MySQL 默认的编码是latin1 ,不支持中文
- 在my.ini 中配置默认的编码
character-sest-server=utf8
– 此种方式不推荐使用, 推荐在创建表的时候设定好 字符集 这样的话建表语句拿到任何地方去执行会指定字符集,减少错误率
3.修改和删除表
修改表
-
ALTER TABLE student RENAME AS student1
– ALERT TABLE 原表名 RENAME AS 新表明
-
添加表的字段,
–ALTER TABLE 表名 ADD 字段 INT(类型)(长度)
-
修改表的字段(重命名,修改约束!)
-
modify 不能用来重命名, 只能修改字段类型和约束;
-
change 用来字段重命名, 不能修改字段那类型和约束;
– ALTER TABLE 表名 MODIFY 字段名 字段类型(长度) – 修改约束
– ALTER TABLE 表名 CHANGE 原字段名 新字段名 类型(长度) – 字段重命名
-
-
删除表的字段
– ALTER TABLE 表名 DROP 字段名
-
删除表
– 删除表(如果表存在则删除)
–DROP TABLE IF EXISTS 表名
所有的创建和删除操作尽量加上判断避免报错
4.MYSQL 数据管理
- 外键(了解)
A表的 Bid 要去引用 B表的Bid
定义外键key -> Bid
给这个外键添加约束(执行引用) ,
方式一 在创建表的时候,增加索引(比较复杂)
删除有外键关系的表需要先删除从表在删除主表
方式二 , 创建完成表之后在添加外键
-- ALERT TABLE 表 ADD CONSTRAINT 约束名FOREIGN KEY (作为外键的列) REFERENCES 哪个表(那个字段)
上面这种添加外键的操作为数据库级别的, 如果表数量过多的话,结构会非常复杂
最佳实践 :
-
数据库就是单纯的表只有行跟列,
-
我们想使用多多张表的数据, 使用逻辑外键
-
DDL 建库建表删库删表
4.0 ,DML 语言 (增删改)
4.1插入
-- 插入语句
-- insert into 表名(字段一, 字段二, 字段三) values (值1, 值2 ,值3);
--INSERT INTO 表名 values(值1,值2) ; 这种方式会与表的列一一匹配 (类型)
--INSERT INTO 表名 (字段) values ('值1'),('值2'); 插入多条
4.2修改
-- UPDATE 表名 SET 字段名= 字段值 , 字段名1 = 字段值1 ,字段名2 = 字段值2 ... where 条件1,条件2...
WHERE 操作符
操作符 含义 范围 结果
= 等于
<> | != 不等于
<
<=
=
BETWEEN … AND … 闭合区间在某个范围内2-4 包含2 和4
AND & 与
OR | 或者
CURRENT_TIME当前时间
4.3删除
-- DELETE FROM 表名 where 条件
TRUNCATE 命令
-- TRUNCATE TABLE 表名
完全清空一张表,表的结构和约束不会变
TRUNCATE 和 DELETE 区别
--相同点:
-- 都能删除数据不会删除表结构;
--不同点:
-- TRUNCATE 重新设置自增列,计数器归零, 不会影响事务
-- DELETE 删除的问题,重启数据库,现象
INNODB 自增列从一开始(存在内存中,断电即失)
MYISAM 继续从上一个自增量开始(存在文件中,不会丢失)
5 DQL 查询数据
5.1 DQL 数据查询语言
- 所有的查询语句都用他
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言
- 使用频率最高的语言
5.2 指定查询字段
-- 查询所有的学生信息
select * from student
-- 查询学生的姓名跟学生学号并且 利用 as 起别名 表起别名
select studentno as '学生名字' , studentname as '学生姓名' from student as s
-- 利用 mysql concat 函数进行拼接字符串 查询学生的名字
select concat('姓名:',studentname) from student
语法 : select 字段,字段,… from 表名
有时候,可以给指定的列起别名 利用 AS 关键字 进行起别名
去重distinct
-- 查询成绩表里面所有的学号 利用 distinct 函数进行去重
select distinct(studentno) from result
-- 查询数据库的版本
select version()
-- 用来计算
select 100*3-1 AS '计算结果'
-- 变量
select @@auto_increment_increment -- 用来查询每自增的个数
-- 学员考试成绩加一分 查看
select studentno ,studentresult+1 from result
数据库中的表达式: 文本值, 列,Null , 函数, 计算表达式 ,系统变量
select 表达式 from 表名
5.3 where 条件子句
作用: 检索数据中符合条件的结果
逻辑运算符
运算符 语法 描述
and && a &b / a and b 与
or || a | b / a or b 或
Not ! not a /!a 非
sql 中尽量使用关键字少用符号
-- 查询区间范围的sql
select * from result where studentresult >=68 and studentresult <=85
select * from result where studentresult between 68 and 85
select * from result where studentresult >=68 && studentresult<= 85
-- 查询条件为两个 or
select * from result where studentresult =85 || studentresult =70
select * from result where studentresult = 85 or studentresult =70
-- 条件为 非! not
select * from result where studentresult != 85
select * from result where not studentresult = 85
模糊查询 : 比较运算符
运算符 语法 描述
is null a is null a如果为 null
is not null a is not null a 如果不为null
between a between b and c 在 b 和 c 之间
like a like b a 匹配b 结果为真
in a in(12,3,4,5) a 在(…) 结果为真
5.4 连表查询
左连接右连接
-- 查询参加了考试的同学信息 ;学号 学生姓名 科目名 分数
-- 查询参加考试的同学
SELECT
s.studentno AS '学号',
studentname AS '姓名',
r.studentresult AS '学生分数',
s1.subjectname AS '学科'
FROM
student s
right JOIN result r ON s.studentno = r.studentno
LEFT JOIN `SUBJECT` s1 ON r.subjectno = s1.subjectno
操作 描述
inner join 两边表里面的有值才会返回
left join 返回左边表里面的全部值,即使右表没有值
right join 返回右表所有值,即使左表没有匹配
自连接
5.5分页和排序
排序 group by ; ASC 升序 DESC 降序
SELECT
s.studentno AS '学号',
studentname AS '姓名',
r.studentresult AS '学生分数',
s1.subjectname AS '学科'
FROM
student s
right JOIN result r ON s.studentno = r.studentno
LEFT JOIN `SUBJECT` s1 ON r.subjectno = s1.subjectno
group by r.studentresult desc
分页 limit 参数1 起始值, 参数二 每页的大小
select * from student limit 6,2
5.6子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
select * from tableName where 列 = | in ( select ... from table)
5.7 分组和过滤
--查询不同课程的平均分, 最高分,最低分,平均分大于80 , 根据不同课程分组
select 科目名称, AVG(成绩) ,MAX (成绩) , MIN(成绩) from 成绩表 ,inner join 科目表 on 成绩表.no = 科目表.no GROUP by 科目名称 having AVG(成绩) >80;
6 MySql函数
6.1 常用函数
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.6) --向上取整
SELECT FLOOR (9.4) -- 向下取整
SELECT RAND() -- 返回一个0-1之间的随机数
SELECT SIGN() -- 判断一个数的符号 0 返回0 整数 返回1 负数 返回-1
-- 字符串函数
SELECT CHAR_LENGTH ('****') -- 字符的长度
SELECT CONCAT('S' ,'S' , 'S') -- 拼接字符串
SELECT INSERT
SELECT LOWER ('ADF') -- 小写字母
SELECT UPPER('sdfds') --大写字母
SELECT INSTR('DFDS','F') -- 返回第一次出现则字符索引
SELECT REPLACE ('我爱中国' ,'我爱' '我们爱') -- 替换出现的指定字符串
SELECT SUBSTR ('我爱中国' ,2 ,2) --返回指定的字符串 (源字符串 ,截取的位置, 截取的长度)
-- 时间和日期函数 (*)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() --本地时间
SLEECT SYSDATE() -- 返回系统时间
SELECT YEAR(NOW()) -- 年
SELECT MONTH(NOW()) --月
SELECT DAY(NOW()) -- 日
SELECT HOUR(NOW()) -- 时
SELECT MINUTE(NOW()) --分
SELECT SECOND(NOW()) --秒
--系统
SELECT SYSTEM_USER() -- 用户
SELECT USER()
SELECT VERSION() --版本
6.2 聚合函数
函数名称 描述
SELECT COUNT(指定列) FROM TABLE ; -- count(字段)会忽略所有的null值
SELECT COUNT(*) FROM TABLE; --count(*) 不会忽略null值 本质计算行数
SELECT COUNT(1) FROM TALBE; -- count(1) 不会忽略所有的null值
SELECT SUM() FROM 表名 --总和
SELECT AVG() FROM 表名 --平均
SELECT MAX() FROM 表名 -- 最大
SELECT MIN() FORM 表名 --最小
7 事务
要么全部成功提交, 要么全部失败回滚
同一个操作单元
事务的原则:
ACID: 原子性\ 一致性\隔离性\持久性
原子性(Atomicity)
要么全部成功, 要么全部失败.
一致性(Consistency)
事务提交前后数据要保持一致性,
持久性(Durability)
事务一旦提交,结果不可逆,被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务不能被其他事务操作数据所干扰,不同事务之间要相互隔离.
存在干扰,隔离所导致的问题
脏读
读取另一个事务未提交的数据
不可重复读
同一个事务读取表中同一行数据,多次读取的结果不同(这个不一定是错误, 只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取的总行数不一致问题;
mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭自动提交
SET autocommit = 1 --开启事务提交
--手动处理事务
SET autocommit = 0 ; -- 关闭自动提交
START TRANSACTION --标记一个事务的开始, 从这句话之后所有的sql 都在用一个事务内,
INSERT xx
INSERT xx
--提交: 持久化(成功!)
COMMIT
--回滚: 回到原来的样子(失败!)
-- 事务结束
SET autocommit =1 --开启事务自动提交
--回滚点
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点;
RELEASE SAVEPOINT 保存点名 --撤销保存点
8 索引
MySql 官方对索引的定义为: 索引(index) 是帮助MySql 高效获取数据的结构.
提取句子主干,就可以得到索引的本质: 索引就是数据结构.
8.1 索引分类
在主键表中主键索引只能有一个, 唯一索引可以有多个
- 主键索引(PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键.
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现.唯一索引可以重复. 多个列都可以标识为唯一索引,
- 常规索引(key/INDEX)
- 默认的.index ,key 关键字用来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有, MyISAM
- 快速定位数据
如何创建索引
-
创建表的时候添加索引
-
创建完毕后,添加索引
show index from 表名 – 显示所有索引
– 添加一个全文索引(索引名,列名)
ALTER TABLE 数据库名.表名 ADD fulltex index 索引名 (列名)--CREATE INDEX 索引名 on 表(字段) CREATE INDEX 索引名 ON 表(字段); --分析sql执行的状况 EXPLATN SELECT * form ...
插入100万数据
DELIMITER $$ -- 写函数之前必须写, 标志
CREATE FUNCTION mock_data()
RETURN INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLART i INT DEFAULT 0;
WHILE i<num DO
--插入语句
/*
insert into 表名(`name` ,`email`,`phone`,`gender`,`password`,`age`) values(
COUNT('用户',i),'3432432423@qq.com','COUNT('18',FLOOR(RAND()*((999999-100000)+100000)))')
*/
SET i=i+1;
END WHILE;
END;
SELECT mock_data()
8.2 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不要添加索引
- 索引一般加在经常查询的字段上
所用的数据结构
Hash 类型的索引
Btree : innodb
9 权限和备份管理
9.1 用户管理
SQL命令操作
用户表: mysql 下面的user 表
本质:对这张表进行增删改查
--创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
--修改密码 (修改当前用户的密码)
-- SET PASSWORD = PASSWORD('111111')
-- 修改指定用户密码
--SET PASSWORD FOR 用户名 = PASSWORD (密码)
-- 用户重命名
--RENAME USER 用户名(old) TO 用户名(new)
-- 用户授权 授予所有的权限所有的库所有的表 (ALL PRIVILEGES 除了给别人授权其余都可以)
-- GRANT ALL PRIVILEGES ON *.* TO 用户名
-- 查询权限
-- SHOW GRANTS FOR 用户名
-- SHOW GRANTS FOR root@localhost
-- ROOT 用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限
-- REVOKE ALL PRIVILEGES ON *.* FROM 用户名
--删除用户
-- DROP USER 用户名
9.2 MySql 备份
为什么要备份?
- 保证数据不丢失
- 数据转移
MySql数据库备份的方式
- 直接拷贝物理未见
- 在可视化工具里面导出
- 使用命令行导出 mysqldump
- mysqldump -主机 -u用户名 -p密码 数据库名 表名 > 位置文件 *.sql
- 导入
- 登陆情况下 -切换到指定数据库source 文件地址
- mysql -u用户名 -p密码 库名< 备份文件名
10 规范数据库设计
10.1 为甚需要设计
当数据比较复杂了,就比较需要设计了
糟糕的数据库设计
- 数据冗余,浪费空间
- 数据库插入和删除都比较麻烦异常
- 程序的性能差
良好的数据库设计
- 节省空间
- 保证数据库的完整性
- 方便开发
开发中,关于数据库的设计
- 分析需求: 分析业务和需要处理的数据库需求
- 概要设计: 设计关系图E-R 图
10.2 三大范式
为甚需要规范
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息(有的数据未插入)
- 删除异常
- 有的数据删除了, 有的数据未删除, 垃圾数据
三大范式
一
原子性: 保证每一列都是不可分的
二
前提: 满足第一范式 , 在第一范式的基础上,每张表只描述意见事情/物体
三
前提: 满足第一范式与大二范式 ,需要确保数据表 中的每一列 都与主键(逻辑主键)直接相关,而不是间接相关;
规范数据的设计
规范性和性能的问题
阿里: 关联查询的表不等超过三张表
- 考虑商业化的需求和目标,(成本,用户体验) 数据库的性能更重要
- 在规范的基础上, 要适当的考虑一下规范性
- 故意给某些表添加一些冗余的字段(从多表查询中变为单表查询)
- 故意增加一些计算列(每一次增加数据加1 查询只查此值, 提高速度)
11 JDBC
11.1 数据库驱动
驱动: 声卡, 显卡, 数据库
11.2 JDBC
SUN 公司为了简化开发人员操作,提供了一个(java操作数据库的规范) 规范,俗称JDBC
这些规范的实现由具体的厂商去做.
扫一扫
2468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
