1.1 数据库概述
-
(1)为什么要学习数据库
- java是将数据存储在运行内存中,需要将数据进行持久化操作(运行内存中的数据存储到磁盘上)
- a. IO流 -> 字节流、字符流、对象流
- b. 数据库:数据库就是存储数据的"仓库" (永久性的)
- 数据库的优点:
- a. 统一管理易于查询
- b. 共享性
- java是将数据存储在运行内存中,需要将数据进行持久化操作(运行内存中的数据存储到磁盘上)
-
(2)数据库的相关概念
- DB:(Database) 数据库:存储数据的“仓库”,它保存了一系列有组织的数据
- DBMS:(Database Managerment System)数据库管理系统: 数据库是通过DBMS创建和操作的容器
- SQL:(Structure Query Language) 结构化查询语言: 专门用来和数据库通信的语言
-
(3)数据库的分类
- 关系型数据库: 采用表格的形式存储数据,表格和表格之间存在关联关系
- 非关系型数据库
1.2 SQL语言的分类
- DDL: Data Definition Language 数据定义语言 (和数据无关)
库和表的管理
常见的约束 - DML: Data Manipulation Language 数据操纵语言 ★
新增数据 insert
修改数据 update
删除数据 delete
查询数据 select DQL: data query language 数据查询语言 - DCL:Data Control Language 数据控制语言
权限 (MySQL没有)
事务
1.3 DDL 数据定义语言
1.3.1 库的管理
(注意:数据库中,关键字不区分大小写,字段名区分,‘’与“”不区分)
接下来演示代码,【】中的代码表示可以写也可以不写
#显示所有数据库
show databases;
#切换数据库
use 数据库名;
#新建数据库
create database 【if not exists】 数据库名;
#删除数据库
delete database 【if exists】 数据库名;
1.3.2 表的管理
-
(1)mysql数据类型:
- 整型:
- tinyint、smallint、mediumint、int、bigint
- int【(3)】 默认是11位
- 浮点型:
- float、double
- float/double【(n,m)】
m:小数的位数 3
n:整数+小数的位数 5
- 字符串:
- char【(n)】 (定长字符串) n代表的是字符串的最大长度,如果省略默认是1
- varchar(n) (可变长度字符串) n代表的是字符串的最大长度,设置长度不能省略
(假如你向一个长度为四十个字符的VARCHAR型字段中输入数据Bill Gates。当你以后从这个字段中取出此数据时,你取出的数据其长度为十个字符——字符串Bill Gates的长度。 假如你把字符串输入一个长度为四十个字符的CHAR型字段中,那么当你取出数据时,所取出的数据长度将是四十个字符。字符串的后面会被附加多余的空格。)
- 日期类型:
- year 只能存储年份
- date 只能存储年月日
- datetime 日期+时间 1900-1-1~9999-12-31 不受时区和服务器版本的影响
- timestamp 日期+时间 时间戳 1970-1-1~2038-12-31 受时区和服务器版本的影响
- 大字节: blob
- 大文本: text
- 整型:
-
(2)创建表时的约束条件 六大约束
- a. 非空 not null
-
create table test1(id int,name varchar(10) not null);
-
- b. 唯一 unique
-
create table test2(id int unique,name varchar(10) unique);
-
- c. 主键(非空+唯一) primary key 一般设置在编号这一列
-
create table test4(id int primary key,age int);
-
- d. 默认值 default 值
-
create table test5(id int,name varchar(10) default 'jack');
-
- e. 检查约束 check (MySQL不支持)
- f. 外键约束 foreign key 涉及到多表
-
foreign key (字段名) references 外键的表名(字段名)
-
- a. 非空 not null
-
(3)机制:自增长机制
- auto_increment 必须使用在主键列、使用方式和约束条件一致
-
create table test4(id int primary key auto_increment,age int);
-
- auto_increment 必须使用在主键列、使用方式和约束条件一致
-
(4)如何对表进行操作
-
#显示所有表格 show tanles; #新建表格 create table 表名( 列名1 数据类型 【约束条件】, 列名2 数据类型 【约束条件】 ); #查看表结构 desc 表名; #删除表 drop table 表名; #表格结构的修改(改名、新增一列、删除一列、修改列名、修改列的数据类型) #语法:alter table 表名 后续操作: #修改表名 alter table 旧表名 rename 新表名: #新增一列(必须有数据类型,可以有约束条件) alter table 表名 add 列名 数据类型 【约束条件】; #修改列名和数据类型 --此处旧列名、数据类型可以和新列名、数据类型一样,运行时不报错 --支持不改列名,只修改数据类型或只改表名不改数据类型 alter table 表名 change 旧列名 新列名 数据类型 【约束条件】; #修改列的数据类型 (如果表中有数据,新数据类型要符合原来数据的类型) alter table 表名 modify 列名 数据类型; #删除一列 alter table 表名 drop 列名:
-
1.4 DML 数据操纵语言
1.4.1 新增数据 insert into
- 语法1: insert into 表名(列名…) values(值…);指定添加部分列的数据,值与列必须按顺序对应
-
insert into student(stu_name,stu_age) values('张三',20);
-
- 语法2: insert into 表名 values(值…); 添加全部列数据(值的顺序按照表结构的字段顺)
-
#这里的null对应的是主键自增的id,对于主键自增的列,可以传null,但不可以不传 insert into student values(null,'张三',20,'北京'); #相当于 insert into student(stu_id,stu_name,stu_age) values(null,'张三',20); ```
-
- 语法3: insert into 表名(列名…) values(值…),(值…);一次添加多条数据
-
insert into student(stu_name,stu_age) values('李四',20),('王五',25),('赵六',22);
-
- 语法4: insert into 表名 values(值…),(值…);
-
insert into student values(null,'张三',20,'北京'),(null,'李四',25,'北京');
-
注意: SQL的标准插入语法是insert into…,但有可能你会遇到有人写插入语句的时候省略掉into,这种写法不规范,但在执行时也并不会报错,能够完成插入功能,但不建议大家这样写
-
insert student values(null,'张三',20,'北京'),(null,'李四',25,'北京');
1.4.2 修改数据 update
语法: update 表名 set 需要修改的列名=新值,需要修改的列名=新值… 【where 修改条件】
- (1)修改表格中所有的数据
-
#将所有学生的年龄改为20 update student set stu_age=20;
-
- (2)添加修改条件
关系运算符: >,>=,<,<=,=,!=,<>(<>也是不等于)-
#将张三的年龄改为25 update student set stu_age=25 where stu_name='张三';
-
#将年龄是小于25岁并且地址是北京的学生手机号码设置为888 update student set stu_phone='888' where stu_age<25 and stu_address='北京' # 将不是中国北京的学生手机号设置为999 update student set stu_phone='999' where not stu_address='北京' # 将部门不为空的员工薪资设置为35000 update employee set salary=35000 where dep_id is not null update employee set salary=35000 where not dep_id is null
-
1.5.3 删除数据 delete
语法: delete from 表名 【where 删除条件】
- (1)删除表格中全部数据
-
#将student表中数据全部删除 delete from student; #一条一条的删 #清空表student truncate table student;
-
方式 | 效率 | 自增长机制 | 是否支持数据回滚 | 删除条件 |
---|---|---|---|---|
DELETE | 较低 | 不会破坏原来的 | 支持 | 允许 |
TRUNCATE | 较高 | 会破坏原来的 | 不支持 | 不允许 |
- (2)添加删除条件
- 删除条件的语法和修改条件的语法一致
-
# 将student表中id大于3的删除 DELETE FROM student WHERE id>3;
1.5 DQL 数据查询语言
1.5.1 简单查询
语法: select 常量、字段名、函数、表达式以及上述组合形式 from 表名 where 查询条件;
- (1)查询所有列信息
-
select stu_id,stu_nam... from student; #简写 select * from student;
-
- (2)查询部分列数据
-
# 查询所有学生的姓名和地址 select stu_name,stu_address from student;
-
- (3)添加查询条件
- 查询条件和修改删除条件写法都一样
-
#查询年龄大于25并且小于30的学生姓名和地址 select stu_name,stu_address from student where stu_age>25 and stu_age<30; select stu_name,stu_address from student where stu_age between 25 and 30;
- (4)模糊查询
- 语法:字段 like 值
- % 0-n个字符
- _ 1个字符
- mysql中默认是不区分内容的大小写的,可以添加BINARY关键字区分
-
#查询出名字中包含“玉”字的学生 select * from student where stu_name like '%玉%' #查询出姓张的学生信息 select * from student where stu_name like '张%'; #查询出姓张并且名字是两个字的学生 select * from student where stu_name like '张_'; #查询出名字中包含j的学生 select * from student where binary stu_name like '%j%';
- 语法:字段 like 值
- (5)排序
- 语法: ORDER BY 需要排序的字段名 排序规则; Ps: 不需要where关键字
-
#查询所有学生的数据,按照年龄进行排序 默认是升序(ASC),降序(DESC) SELECT * FROM student ORDER BY stu_age DESC; #将地址不为空的所有学生按年龄排序 SELECT * FROM student WHERE stu_address IS NOT NULL ORDER BY stu_age; #将所有地址不为空的学生信息查出,按年龄升序排序,如果年龄相同,按id升序排序 SELECT * FROM student WHERE stu_address IS NOT NULL ORDER BY stu_age,ORDER BY id;
- (6)表达式
-
#查询所有员工的姓名和年薪 SELECT emp_name,emp_salary*12 FROM employee;
-
- (7)别名
- 语法: 字段名、表达式、表名 as 别名 as是可以省略的
- 别名可以用于排序、分组,但不能用于where
-
#起别名 SELECT emp_name 姓名,emp_salary*12 年薪 FROM employee; #查询出所有员工的姓名和工资,按工资排序 SELECT emp_name,emp_salary FROM employee ORDER BY emp_salary;
1.5.2 函数
- (1)单行函数
-
#字符相关: SELECT LOWER('Abc');#将数据的字母全部转为小写 SELECTUPPER('Abc');#将数据的字母全部转为大写 #查询books中的id,书名(改为全大写),类型(改为全小写) SELECT b_id,UPPER(b_name),LOWER(note) FROM books; SELECT CONCAT('java','mysql','jdbc');#将数据拼接到一起 #将books中id,并将书名和作者拼接到一起输出 SELECT b_id,CONCAT(b_name,auther) FROM books; #从第2个字符截取到末尾(下标从1开始,第2个是e) SELECT SUBSTR('helloworld',2) SELECT SUBSTR('helloworld',3,6)#从第三个字符开始截取总共截取6个 SELECT LENGTH('helloworld你好')#返回字节数,ut8编码汉字占3个字符 SELECT INSTR('helloworld','o')#返回第一次出现指定字符的位置 SELECT LPAD('java',10,'a')#如果字符串不够10位,在前面补a SELECT RPAD('java',10,'a')#如果字符串不够10位,在后面补a SELECT TRIM(' hello world ')#去除字符串的前后空格 SEELCT TRIM('a' from 'aahelloaaworldaa')#去除字符串的前后指定字符'a' SELECT REPLACE('helloworld','o','j')#将'o'替换为'j' #B:和数学相关 SELECT FLOOR(-1.3)#向下取整 SELECT CEIL(1.4)#向上取整 SELECT ROUND(1.5)#四舍五入 SELECT TRUNCATE(12.3456,2)#截取2位小数 SELECT RAND()#产生随机数 SELECT MOD(10,6)#求10%6的余数 #C:日期函数 1.STR_TO_DATE(str,format)#字符串->date STR_TO_DATE('1977年3月3日 13:34:56','%Y年%m月%d日 %H:%i:%s') 2.DATE_FORMAT(date,format) #date->format SELECT DATE_FORMAT(stu_birthday,'%Y年%m月%d日') 3.重日期取出单个属性 YEAR(date) # 单独从生日中取出年份 MONTH(date) DAY(date) HOUR(date) MINUTE(date) SECOND(date) FROM stu WHERE stu_id>14 4.SELECT NOW() #获取当前系统的日期+时间 SELECT CURDATE()#获取当前系统的日期 SELECT CURTIME()#获取当前系统的时间
-
- (2)组合函数
①count(列名/*) 数据的总条数 ps:null值不算总条数
②max(列名); 最大值
③min(列名); 最小值
④sum(列名); 总和
⑤avg(列名); 平均值 -
# 查询一共有多少本不重名的书 SELECT count(*) FROM books WHERE pubdate>2000; # 求出库存最多的书的数量 SELECT max(num) FROM books; # 求图书馆中所有书的数量 SELECT sum(num) FROM books; # 求所有图书价格的平均值 SELECT avg(price) avg,sum(num) FROM books;
- (3)去重
-
#查询出图书有多少种类 #去重 DISTINCT 将相同的数据删除 SELECT DISTINCT note FROM books
-
(4)分组查询 将相同的数据自动归为一组
语法:GROUP BY 需要分组的字段名;
#查询出图书有多少种类
SELECT note FROM books GROUP BY note
# 查询出每种图书种类的图书数 分组+组合函数
# 组合函数在没有分组的sql语句中,对整张表格起作用
# 组合函数在有分组的sql语句中,是对一个小组起作用
SELECT note,sum(num) FROM books GROUP BY note