JAVA-19-数据库基本操作

一、数据库入门
1.1 引入
　　数据保存在内存：
　　　　优点： 存取速度快
　　　　缺点： 数据不能永远保存！！！
　　数据保存在文件：
　　　　优点： 数据永远保存！！！
　　　　缺点：
　　　　　　1）速度比内存操作慢，频繁的IO操作。
　　　　　　2）查询数据不方便
　　数据保存在软件：
　　　　1）数据永远保存！！！
　　　　2）速度比较快
　　　　3）查询和管理数据方便
数据库软件可以实现以上的功能！！！
1.2 市面常见数据库软件
　　SQL Server： 微软的产品。与net平台兼容比较好！！！收费的。
　　Oracle：甲骨文公司的产品。 与java平台兼容性最好！！！收费的。
　　　　收购Sun公司： java
　　　　收购mysql: mysql 数据库软件　中大型企业（中大型应用）
　　　　　　　　　mysql： 开源产品。免费。与java平台兼容性最好！！　中小企业（中小应用）
我们先学习mysql，再学习oracle。
1.3mysql学习大纲：
　　1）学习如何安装和基本使用
　　2）如何管理数据库
　　3）如何管理表
　　4）如何管理数据
　　5）如何查询数据
二、MySQL数据库
2.1 安装和基本使用
　　1）下载安装包。mysql-5.5.40-win32.ms
　　2）如果之前安装过mysql，要先安装文档步骤卸载mysql
　　3）按照安装文档安装mysql数据库软件。
　　4）打开cmd命令行：
输入： mysql -u root -p 回车
输入：root
看到：

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.40 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

说明安装成功。
2.2 mysql的结构
　　先有数据库 -> 再有表 ->再有数据
　　通过sql语句对mysql数据库进行管理。
三、管理数据库
3.1　查看所有数据库
mysql> show databases; –分号结束。sql语句就会发送给mysql服务器端执行

+--------------------+| Database           |+--------------------+| information_schema |      --mysql元数据数据库。| mysql              |   　　--mysql配置数据库。其中user表用于管理mysql用户和密码、权限信息。| performance_schema |   　　--mysql性能监控信息数据库。| test               |     　--测试数据库。+--------------------+ 4 rows in set (0.00 sec)mysql>

3.2　创建数据库
　　1）创建数据库，并制定默认字符集

mysql> create database day16    -> default character set utf8;Query OK, 1 row affected (0.00 sec)

　　查看数据库的状态（默认字符串）：show create database day16;
3.3 删除数据库

mysql> drop database day15;Query OK, 0 rows affected (0.00 sec)

3.4 修改数据库
　　修改数据库默认字符集

mysql> alter database day15 default character set gbk;Query OK, 1 row affected (0.00 sec)

四、管理表(在SQLyog客户端上操作)
4.1 查询所有表

mysql> use day15;   --选择数据库mysql> show tables;  --查看数据库中的所有表

4.2 创建表
需求：创建学生表，字段(id,name,age)

mysql> create table student(    -> id int,      --字段：字段名称  字段类型    -> name varchar(20),    -> age int    -> );

　　查看一个表结构：desc student; 以表格格式返回
4.3 删除表

mysql> drop table student;Query OK, 0 rows affected (0.01 sec)

4.4 修改表
　　1）添加字段

mysql> alter table student add column gender varchar(2);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add a int,add b int; 多个操作

2）修改字段类型

mysql> alter table student modify column remark varchar(100);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

3）修改字段名称

mysql> alter table student change column remark remark2 varchar(100)Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

4）删除字段

mysql> alter table student drop column a,drop column b;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

5)修改表名

mysql> alter table student rename to student2;Query OK, 0 rows affected (0.01 sec)

五、管理数据
———（1）数据增删修改操作———
1.1 查看表的数据

SELECT * FROM student;

1.2 插入数据
　　　　插入所有列（所有列的值都要按顺序依次插入）

INSERT INTO student VALUES(1,'eric',20,'广州人','男');INSERT INTO student VALUES(2,'rose',25,'佛山人','女');

　　　　字段的数据一定要和值的数量要一致

INSERT INTO student VALUES(2,20);　-- 报错

　　　　插入部分列(列的数据和值的顺序和数量一定保持一致！)

INSERT INTO student(id,NAME,age,remark2) VALUES(3,'jacky',27,'佛山人');

1.3 修改数据
　　　　修改所有（用的很少）需求:将所有学生性别改为男

UPDATE student SET gender='男';

　　　　修改某个某些条件的数据（用的较多）需求：将id为2的学生性别改为女

UPDATE student SET gender='女' WHERE id=2;

　　　　修改多个列(SET 列名=值,列名=值…)需求：修改id为2的学生姓名和年龄

UPDATE student SET age=28,remark2='韶关人' WHERE id=2;

1.4 删除数据（delete from）
　　　　1) 全表数据删除 需求：删除全表的数据

DELETE FROM student;

　　　　2）按条件删除数据 删除id为2的学生

DELETE FROM student WHERE id=2;

　　　　3)truncate table也可以删除全表数据

TRUNCATE TABLE student;

　　注意：delete from 和 truncate table 的区别：
　　　　1）delete from删除全部，也可以按条件删除，但是truncate table只能全表删除，不能按条件删除
　　　　2）delete from删除的数据可以回滚，truncate table删除的数据不能回滚。
　　　　3）delete from不可以把自增长约束(auto_increment)重置，truncate table可以把自增长约束(auto_increment)重置

　truncate table student where id=1; 　-- 错误的语法

———（2）数据查询———
2.1 查询所有列

SELECT * FROM student;

2.2 查询指定列(多个列之间以逗号分隔)

SELECT NAME,gender FROM student;

2.3 查询时给列指定别名(AS)

SELECT NAME AS '姓名',gender AS '性别' FROM student;-- AS可以省略，等同于下面：SELECT NAME '姓名',gender '性别' FROM student;

2.4 合并列查询

-- 添加servet,jsp成绩列ALTER TABLE student ADD servlet INT,ADD jsp INT;SELECT * FROM student;UPDATE student SET servlet=86,jsp=75 WHERE id=1;UPDATE student SET servlet=90,jsp=65 WHERE id=2;UPDATE student SET servlet=78,jsp=50 WHERE id=3;UPDATE student SET servlet=85,jsp=80 WHERE id=4;-- 需求： 查询每个学生的servlet和jsp的总成绩-- 注意： 必须是数值类型SELECT NAME '姓名',(servlet+jsp) '总成绩'  FROM student;-- 注意，和数值以外合并没有意义SELECT NAME '姓名',(gender+jsp)  FROM student;

2.5查询时添加常量列

-- 需求： 在查询学生表时都带上一个班级列，内容为"java就业班"SELECT NAME '姓名',gender '性别',age '年龄','java就业班' AS '班级' FROM student;

2.6 查询去除重复数据

-- 需求： 统计学生都来自于哪里SELECT DISTINCT remark2 FROM student;-- 这种语法也可以SELECT DISTINCT(remark2) FROM student;

2.7 条件查询(where)

SELECT * FROM student;-- 1) 逻辑条件： and   or-- 需求: 查询id值为3，且性别为男的学生(交集：两个条件同时满足)SELECT * FROM student WHERE id=3 AND gender='男';-- 需求：查询id为3，或性别为男的学生（并集： 两个条件其中一个满足）SELECT * FROM student WHERE id=3 OR gender='男';-- 2）比较条件： >  <  >=  <=  = between and-- 需求： 查询servlet分数大于80分的学生SELECT * FROM student WHERE servlet>80;-- 需求：查询jsp分数小于或等于80分的学生SELECT * FROM student WHERE jsp<=80;SELECT * FROM student WHERE jsp<80 OR jsp=80;-- 需求： 查询servlet成绩大于等于80，且小于等于88的学生SELECT * FROM student WHERE servlet>=80 AND servlet<=88;-- 等价于上面SELECT * FROM student WHERE servlet BETWEEN 80 AND 88; -- (包前包后)-- 需求：查询年龄不等于30岁的学生SELECT * FROM student WHERE age<>30;-- 3）判空条件:  =''  is null  <>''  is not nullUPDATE student SET remark2="" WHERE id=1;UPDATE student SET remark2=NULL WHERE id=4;-- 需求： 查询remark字段为空的学生（包含null，也包含字符串）-- null 和 空字符串的区别：-- null： 没有数据。 判断null： is null,判断不为null： is not null-- 空字符： 有数据，数据就是空字符串。判断空字符： =''； 判断不为空字符串： <>''SELECT * FROM student WHERE remark2 IS NULL;SELECT * FROM student WHERE remark2='';SELECT * FROM student WHERE remark2 IS NULL OR remark2='';-- 需求：查询remark字段不为空的学生(空串或者null都被视为空)SELECT * FROM student WHERE remark2 IS NOT NULL AND remark2<>'';-- 4) 模糊条件： like-- like后面跟上符号-- %: 表示任意多个字符（0-多个）-- _: 表示一个字符-- 需求： 查询姓张的学生SELECT * FROM student WHERE NAME LIKE '张%';-- 需求： 查询姓名中包含'张'字的学生SELECT * FROM student WHERE NAME LIKE '%张%';-- 需求： 查询姓张，且姓名只有两个字的学生SELECT * FROM student WHERE NAME LIKE '张__';

2.8 聚合查询

SELECT * FROM student;DELETE FROM student WHERE id=5;-- 需求： 查询所有学生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;SELECT COUNT(id) FROM student; -- 效率会比count(*)效率稍高-- 注意： 聚合函数，会排除null值的数据SELECT COUNT(remark2) FROM student;

2.9 分组查询

-- 需求： 查询男女的数量各自多少-- 预期结果： -- 男   3-- 女   2-- 1) 对性别进行分组（group by） 2）在分组基础上进行聚合函数总计SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender;

2.10分组筛选(group by + having(条件))

-- 需求： 查询人数大于2的性别-- 1)分组   2）统计 3)条件SELECT gender,COUNT(*) '人数' FROM student GROUP BY gender HAVING COUNT(*)>2;

2.11 分页查询(limit)

-- limit 起始行数,查询的行数-- 起始行数从0开始SELECT * FROM student;-- 需求：学生共20条数据，每页显示5条，共4页-- 看第3页(第11到15条)的学生数据sql：select * from student limit 10,5;-- 看第4页(第16到20条)的学生数据sql: select * from student limit 15,5;-- 知道： 当前页码，每页显示条数 -- 结论分页查询当前页数据的sql： select * from student limit (当前页码-1)*每页显示条数,每页显示条数;-- 需求： 查询第1,2条数据SELECT * FROM student LIMIT 0,2;-- 需求： 查询第3,4条数据SELECT * FROM student LIMIT 2,2;-- 需求：查询第5,6条数据SELECT * FROM student LIMIT 4,2;

2.12 查询后排序（order by）

-- desc: 降序。数值从大到小，字母z-a-- asc: 升序。数值从小到大，字母a-z-- 默认情况下，按照插入的顺序排序SELECT * FROM student;-- 需求：按照id的升序排序SELECT * FROM student ORDER BY id ASC;-- 需求： 按照servlet成绩降序排序SELECT * FROM student ORDER BY servlet DESC;-- 多个排序条件的情况：先按照前面的条件排序，当出现重复记录，再按照后面的条件排序-- 需求： 按照age升序，按照servlet成绩升序排序SELECT * FROM student ORDER BY age ASC,servlet ASC;

六、字段类型
a.　char(20) 和 varchar(20)的区别
　　① char(20): 固定长度的字符串。不管实际存储的数据的大小，一定占用20个字符空间
　　② varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。
　　
b.　int vs int(4)
　　① int: 默认最多11位，长度根据实际存储的数值的长度
　　② int(4): 固定的数值长度