java学习【数据库入门】

数据库入门
1.1 引入
数据保存在内存：
优点： 存取速度快
缺点： 数据不能永远保存！

数据保存在文件：
优点： 数据永远保存！
缺点：
1）速度比内存操作慢，频繁的IO操作。
2）查询数据不方便

数据保存在软件：
1）数据永远保存！！！
2）速度比较快
3）查询和管理数据方便

数据库软件可以实现以上的功能！！！

1.2 市面常见数据库软件

SQL Server： 微软的产品。与net平台兼容比较好！！！收费的。Oracle：甲骨文公司的产品。 与java平台兼容性最好！！！收费的。        收购Sun公司： java        收购mysql: mysql 数据库软件        中大型企业（中大型应用）mysql：开源产品。免费的。与java平台兼容性最好！！        中小企业（中小应用）    前面： 学习mysql    后面： 学习oracle

mysql学习大纲：

1）学习如何安装和基本使用2）如何管理数据库3）如何管理表4）如何管理数据5）如何查询数据

MySQL数据库
先有数据库 -> 再有表 ->再有数据
通过sql语句对mysql数据库进行管理

一，管理数据库

1.创建数据库，并制定默认字符集
– 创建库，day16
CREATE DATABASE day16 DEFAULT CHARACTER SET utf8;

– 使用day16这个数据库
USE day16;

二，管理表
1.查询所有表
– 查询day16这个库中的所有的表
SHOW TABLES;

2.创建表
– 需求：创建学生表(id,name,age)
CREATE TABLE student(
– 字段名称 字段类型
id INT,
NAME VARCHAR(20),
age INT
);

– 查询表中的数据
SELECT * FROM student;

– 查询一个表结构
DESC student;

– 删除student表
DROP TABLE student;

3.修改表

3.1添加字段
– 添加一个地址字段(gender varchar(2))
ALTER TABLE student ADD COLUMN gender VARCHAR(2);

3.2修改字段类型
– 修改gender字段类型为varchar(3)
ALTER TABLE student MODIFY COLUMN gender VARCHAR(3);

3.3修改字段名称
– 将gender字段修改为sex字段 varchar(2)
ALTER TABLE student CHANGE COLUMN gender sex VARCHAR(2);

– 添加一个地址字段
ALTER TABLE student ADD COLUMN address VARCHAR(20);

3.4删除字段
– 删除sex和address字段
ALTER TABLE student DROP COLUMN sex,DROP COLUMN address;

3.5修改表名
– 将student这个表的表名改为stu
ALTER TABLE stu RENAME TO student;

三，管理数据
1.查看表的数据
– select 字段名称(如果查询所有字段*) from student;

– 查看表数据
SELECT * FROM student;

2.插入数据
– 给表中插入3条数据（插入所有字段）
INSERT INTO student VALUES(1,’刘德华’,50,’中国香港’);
INSERT INTO student VALUES(2,’陈奕迅’,40,’中国香港’);
INSERT INTO student VALUES(3,’韩红’,50,’河北’);

– 当需要插入所有字段的时候我们可以直插入部分字段吗？插入不了
INSERT INTO student VALUES(1,’成龙’,60,’中国香港’);

– 指向给表中只想插入id字段和name字段，请问怎么办？
INSERT INTO student(id,NAME) VALUES(4,’郭德纲’);

3.修改数据
– 将所有学生的年龄改为50岁,修改所有学生的年龄，用的非常少
UPDATE student SET age=50;

– 将id为3的学生姓名改为房祖名
UPDATE student SET NAME=’房祖名’ WHERE id=3;

– 修改多个字段，修改id为2的学生姓名为张学友，年龄改为60
UPDATE student SET NAME=’张学友’,age=60 WHERE id=2;

4.删除数据（delete from）
– 删除全表数据,本质上是一条一条进行删除的，效率比较慢
DELETE FROM student;

– 删除id为3的数据
DELETE FROM student WHERE id=3;

– 使用truncate table 表名，删除全表数据，并不是一条条删，而是直接将全表数据删除，效率比较快
TRUNCATE TABLE student;

– delete from和truncate table 这两种删除全表的方式有什么区别呢？
– 1.delete from一条条删除，truncate table直接将全表数据干掉
– 2.delete from可以按条件删除一条数据，truncate table只能删除全表数据，不能按照条件删除
– 3.delete from无法重置自增长主键，truncate table可以重置自增长主键

四，查询数据
1.查询所有列
SELECT * FROM student;

2.查询指定字段，查询id,name
SELECT id,NAME FROM student;

3.查询时指定别名，name–姓名，address–住址
SELECT NAME AS ‘姓名’,address AS ‘住址’ FROM student;

– 上面指定别名的as是可以省略的
SELECT NAME ‘姓名’ FROM student;

4.合并列查询
– 添加servlet,jsp字段
ALTER TABLE student ADD COLUMN servlet INT,ADD COLUMN jsp INT;

– 给每条学生数据添加上servlet和jsp的成绩
UPDATE student SET servlet=50,jsp=60 WHERE id=1;
UPDATE student SET servlet=60,jsp=70 WHERE id=2;
UPDATE student SET servlet=70,jsp=80 WHERE id=3;

– 合并servlet和jsp这两个列进行查询，查询每个学生的servlet和jsp的成绩总和
– 合并列查询有一个特点：只能合并数值类型的字段
SELECT NAME ‘姓名’,(servlet+jsp) ‘总成绩’ FROM student;

5.查询时添加常量列
– 查询时添加常量列，给student表添加一个常量列 班级–java001
SELECT NAME ‘姓名’,address ‘地址’,’java001’ AS ‘班级’ FROM student;

– 查询你们班的学生都来自于哪里
SELECT NAME ‘姓名’,address ‘地址’ FROM student;

6.查询去除重复数据
– 去除重复值来查询每一个学生来自于哪里
SELECT DISTINCT address FROM student;
– 去除重复值的另一种写法
SELECT DISTINCT(address) FROM student;

7.条件查询
– 查询id为1并且，并且servlet成绩等于50的学生(交集 and)
SELECT * FROM student WHERE id=1 AND servlet=50;

– 查询id为1或者来自中国香港的学生（并集 or）
SELECT * FROM student WHERE id=1 OR address=’中国香港’;

– 查询servlet成绩大于60分的学生
SELECT * FROM student WHERE servlet>60;

– 查询jsp成绩小于等于70的学生
SELECT * FROM student WHERE jsp<=70;
– 另一种写法
SELECT * FROM student WHERE jsp<70 OR jsp=70;

– 查询jsp的成绩大于等于70并且小于等于80的学生
SELECT * FROM student WHERE jsp<=80 AND jsp>=70;
– 上面写法的另一种语法,between…and…包前也包后
SELECT * FROM student WHERE jsp BETWEEN 70 AND 80;

– 查询学生年龄不等于30岁的学生
SELECT * FROM student WHERE age<>30;

– 给student表中添加一个数据
INSERT INTO student VALUES(4,’郭德纲’,NULL,”,80,90);

– 查询age字段为null的学生(IS NULL)
SELECT * FROM student WHERE age IS NULL;

– 查询address字段为空字符串的学生(=”)
SELECT * FROM student WHERE address=”;

– 查询age字段不为null的学生(is not null)
SELECT * FROM student WHERE age IS NOT NULL;

– 查询address字段不为”的学生(<>”)
SELECT * FROM student WHERE address<>”;

– 模糊查询(like),like后面跟的是符号
– %任意多个字符
– _一个字符

– 查询姓刘的学生(like ‘刘%’)
SELECT * FROM student WHERE NAME LIKE ‘刘%’;
– 查询姓名中含有刘这个字的学生
SELECT * FROM student WHERE NAME LIKE ‘%刘%’;
– 查询姓刘，且姓名有3个字的学生
SELECT * FROM student WHERE NAME LIKE ‘刘__’;

8.聚合函数
– 查询servlet的总成绩(sum – 求和函数)
SELECT SUM(servlet) FROM student;

– 查询每个学生的servlet平均分（avg,平均函数）
SELECT AVG(servlet) FROM student;

– 查询学生的servlet的最高成绩(max ,最大值函数)
SELECT MAX(servlet) FROM student;

– 查询所有学生的servlet的最低成绩(min,求取最小值函数)
SELECT MIN(servlet) FROM student;

– 求取这张学生表中有多少条数据(count(*))
– 效率比较低
SELECT COUNT(*) FROM student;

– 根据某一个字段求取学生表中的数据条数,当一个字段数值为null的时候，是不予计算的
– 但是这种方式求取得统计值的时候效率会更高，但是有时候数据不够准确
SELECT COUNT(age) FROM student;

9.分组查询
– 求取本班中香港和河北的学生分别有多少人
– 1.给学生使用address这个字段进行分组（group by） 2.求取每一组中的学生人数
– address count
– 香港 2
– 河北 1
SELECT address,COUNT(*) FROM student GROUP BY address;

10.分组筛选
– 查询人数大于1的地域(group by 分组字段 having 筛选条件)
SELECT address,COUNT() FROM student GROUP BY address HAVING COUNT()>1;

11.分页查询(limit)
– 分页查询 limit 起始行数,查询的条数 注意：起始行数从0开始
– 假设我的表中有20条数据，分为4也显示
– 第一页：limit 0,5
– 第二页：limit 5,5
– 第三页：limit 10,5
– 第四页：limit 15,5
– 结论：查询某一页要现实的数据的时候可以利用如下公式 limit (当前页数-1)*每页显示的条数,每页现实的条数

– student表中目前有4条数据，分为2页显示，每页显示2条
– 查询第一页的显示数据：
SELECT * FROM student LIMIT 0,2;
– 查询第二页现实的数据
SELECT * FROM student LIMIT 2,2;

12.查询后排序（order by）
– 按照id字段的升序进行排序
– asc,升序，数字从小到大，字母a-z
– desc,降序，数字从大到小，字母z-a
SELECT * FROM student ORDER BY id DESC;

– 按照servlet的成绩的降序进行排序
SELECT * FROM student ORDER BY servlet DESC;

– 当有多个排序条件的时候，先按照第一个条件排序，如果第一个条件相同则按照第二个条件进行排序
– 先按照学生的年龄升序进行排序，年龄相同按照学生的servelt成绩的升序进行排序
SELECT * FROM student ORDER BY age ASC,servlet ASC;

DESC student;

SELECT * FROM student;

---

数据约束 ：
给表添加一些数据约束从而可以达到约束用户操作数据的效果
1：默认值约束（default）
– :当给这个字段没有添加值的时候，会给一个默认值,如果给默认值约束的字段添加的值为null的时候，那么他的字段值就为null
– 创建一个stu表
CREATE TABLE stu(
id INT,
NAME VARCHAR(20),
– 给性别gender这个字段添加一个默认值约束
gender VARCHAR(2) DEFAULT ‘男’
);
– 给stu表中添加几个数据
INSERT INTO stu VALUES(1,’张三’,’男’);
INSERT INTO stu(id,NAME) VALUES(2,’李四’);

– 给stu表中插入数据，性别为null
INSERT INTO stu VALUES(3,’刘诗诗’,NULL);

2.非空约束(not null),插入的字段不为null，而且必须插入数据
CREATE TABLE stu(
– 给stu表中的id字段添加一个非空约束
id INT NOT NULL,
NAME VARCHAR(20),
gender VARCHAR(2)
);
– 给这张表中添加一个元素，不插入id字段的值
INSERT INTO stu(NAME,gender) VALUES(‘郭德纲’,’男’);
– 给这张表添加一条数据，id字段的值直接给成null,这样的话是插入不进去的。。

3.唯一约束(unique)
– 给stu表添加一个唯一约束
CREATE TABLE stu(
– 给stu表中的id字段添加唯一约束
id INT UNIQUE,
NAME VARCHAR(20),
gender VARCHAR(2)
);
– 给表中插入两条id相同的数据
INSERT INTO stu VALUES(1,’刘德华’,’男’);
INSERT INTO stu VALUES(1,’张学友’,’男’);– Duplicate entry ‘1’ for key ‘id’,无法插入重复的id值

– 给表中插入一条id为null的数据，当给id添加了唯一约束之后，依然可以给他插入多条null值，不会出现重复
INSERT INTO stu VALUES(NULL,’吴奇隆’,’男’);
INSERT INTO stu VALUES(NULL,’刘诗诗’,’女’);

4.主键约束
– 经过我们的分析，我们认定我们的这个id字段(唯一+非空)–主键(primary key)
– 注意：
– 1.一般来说我们需要给每一张表都设定一个主键字段(非空+唯一)，用来标示一条信息的唯一性
– 2.我们一般不会将业务字段设定为主键字段，比如name字段，一般我们会给每一张表添加一个id字段作为主键字段
– 3.建议给每张表添加一个主键字段，用来标示每一条数据的唯一性
CREATE TABLE stu(
– 给stu表中的id字段设置为主键（唯一+非空）
id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(2)
);
– 给表中插入两条id为1的学生信息
INSERT INTO stu VALUES(1,’华仔’,’男’);
INSERT INTO stu VALUES(1,’华建’,’男’);– Duplicate entry ‘1’ for key ‘PRIMARY’

– 给表中插入id为null的元素
INSERT INTO stu VALUES(NULL,’杰伦’,’男’);– Column ‘id’ cannot be null

– 上面的实验我们可以得出结论，当我们给id字段设置了主键约束后，这个id字段就非空+唯一了

5.自增长约束(auto_increment)
– 给stu表中的id字段添加一个主键自增长约束
CREATE TABLE stu(
– 给stu表中的id字段设置主键自增长约束，我们其实就将id这个字段交给了数据库自己去维护，我们自己不需要去动他
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(2)
);
– 给stu表中添加两条数据
INSERT INTO stu(NAME,gender) VALUES(‘华仔’,’男’);
INSERT INTO stu(NAME,gender) VALUES(‘周杰伦’,’男’);
INSERT INTO stu(NAME,gender) VALUES(‘周杰伦’,’男’);
INSERT INTO stu(NAME,gender) VALUES(‘周杰伦’,’男’);

– 删除id为4的数据
DELETE FROM stu WHERE id=4;
– 给表中添加一条数据
INSERT INTO stu(NAME,gender) VALUES(‘张学友’,’男’);
– delete from 这种删除数据的方式，无法重置自增长的主键

– 删除stu的全表数据
DELETE FROM stu;
– 添加一条数据
INSERT INTO stu(NAME,gender) VALUES(‘张学友’,’男’);

– 删除全表数据的truncate table 表名 ，删除全表数据，这种删除全表数据的方式可以重置主键
TRUNCATE TABLE stu;
– 给表中添加一条数据
INSERT INTO stu(NAME,gender) VALUES(‘华仔’,’男’);