FKJAVA读书笔记--第十三章--MySQL数据库与JDBC编程

1.Mysql默认使用分号作为结束符号。

 

2.常用的sql语句：

①　Mysql -u 用户名 -p 密码 -h 主机名  进入mysql命令行客户端

②　查看当前实例下有多少的数据库：show databases；

③　删除制定的数据库：drop database 数据库名；

④　创建新的数据库：create database （不存在） 数据库名；

⑤　进入指定的数据库：use 数据库名；

⑥　进入制定的数据库之后，查看下面有多少的表：show tables；

⑦　想查看某个表的表结构：desc 表名；

 

3.DDL语句：DDL语句是操作数据库对象的语句，包括创建（create）、删除（delete）和修改（alter）数据库对象。

①　创建表的语句：

create table test(

test_id int,

test_price decimal,

test_name varchar(255) default ‘xxx’,

test_desc text,

test_img blob,

test_date date

);

②　使用子查询创建表：

       create table haha  (此处可以有子查询列表)

       as

       select * from user_inf;

 

4.DDL语句：

①　修改表结构的语法：

②　

***添加表字段

alter table hehe add hehe_id int;

alter table hehe add  ( aaa varchar(255) default ‘xxx’ ,bbb varchar(255));

 

****修改表字段

alter table hehe modify hehe_id varchar(255);

alter table hehe modify bbb int;

 

***从表中删除列的语法

alter table hehe drop aaa;

 

***重命名数据表

alter table hehe rename to hehe2;

 

***修改表的列名

alter table hehe change aaa ddd int;

 

③　删除表的语法：

****删除表的语法

drop table hehe;

④　truncate表：删除该表里面全部数据，但保留表结构

truncate hehe;

 

5.数据库约束：

①　NOT NULL:非空约束

②　UNIQUE:唯一约束

③　PRIMARY KEY:主键===》可以唯一的标识该条记录

④　FOREIGN KEY:外键===》改行从属与主表中的一条记录  

⑤　CHECK:检查===》指定一个布尔表达式，用于指定对应列的值必须满足该表达式（mysql不支持）

一、NOT NULL：

 

****创建时

create table hehe（

hehe_id int not null;

hehe_name varchar(255) default ‘xyz’ not null,

hehe_gender varchar(2) null

);

 

***修改表时

alter table hehe modify hehe_gender varchar(2) not null;

alter table hehe modify hehe_name varchar(2) null;

alter table hehe modify hehe_name varchar(255) default ‘abc’ null;

二、UNIQUE:不允许出现重复的值，但可以出现多个null值（同一个表可创建多个唯一约束，唯一约束也可由多个列组合而成【需要使用表级约束语法】）

***列级约束语法

 

create table unique_test(

test_id int not null,

test_name varchar(255) unique

);

***表级约束语法

 

create table unique_test2(

test_id int not null,

test_name varchar(255),

test_pass varchar(255),

unique (test_name),

constranit test2_uk unique(test_pass)

);

create table unique_test3(

    test_id int not null,

test_name varchar(255),

test_pass varchar(255),

constraint test3_uk unique(test_name,test_pass)

);

***修改表时

 

alter table unique_test3 add unique(test_name,test_pass);(表级)

 

alter table haha modify test_name varchar(255) unique;(列级)

 

***删除unique_test3表上的test3_uk唯一约束

alter table unique_test3

drop index test3_uk;

 

三、PRIMARY KEY 约束（相当于非空约束和唯一约束）

***列级约束语法

create table primary_test(

test_id int primary key,

test_name varchar(255)

);

 

  ***表级约束语法

create table primary_test3(

test_id int not null,

test_name varchar(255),

test_pass varchar(255),

#指定主键约束名为test2_pk，对大部分数据库有效，但对MySql无效

#MySql数据库中该主键约束名依然是PRIMARY

constraint test2_pk primary key (test_id)

);

   create table primary_test3(

test_name varchar(255),

test_pass varchar(255),

#只要name和pass不要同时一样就可以

primary key(test_name ,test_pass)

);

 

***修改表时增加主键约束

alter table primary_test3 add pimary key(test_name,test_pass);(表级)

alter table primary_test3 modify test_name varchar(255) primary key;（列级）

 

***删除主键约束

alter table primary_test3 drop primary key;

 

***自增长的主键

create table primary_test4(

test_id int auto_increment primary key,

test_name varchar(255),

test_pass varchar(255)

);

 

四、FOREIGN KEY 外键约束：保证一个或者两个表数据之间的参照的完整性（一个表的两个字段或者两个表两个字段之间）

***创建外键(列级：mysql虽然支持，但是这种外键约束不会生效)

create table teacher_table1(

teacher_id int auto_increment,

teacher_name varchar(255),

primary key(teacher_id)

);

create table student_table1(

student_id int auto_increment primary key,

student_name varchar(255),

java_teacher int references teacher_table1(teacher_id)

);

***创建外键（表级约束语法 如果不指定创建的外键的名称，会自动命名为table_name_ibfk_n）

create table teacher_table (

teacher_id int auto_increment,

teacher_name varchar(255),

primary key(teacher_id)

);

create table student_table(

student_id int auto_increment primary key,

student_name varchar(255),

java_teacher int,

foreign key(java_teacher) references teacher_table(teacher_id)

);

 

***创建外键（表级约束语法并指定约束的约束名）

create table teacher_table (

teacher_id int auto_increment,

teacher_name varchar(255),

primary key(teacher_id)

);

create table student_table(

student_id int auto_increment primary key,

student_name varchar(255),

java_teacher int,

constraint student_teacher_fk foreign key(java_teacher) references teacher_table(teacher_id)

);

 

***创建组合外键

create table teacher_table3(

teacher_name varchar(255),

teacher_pass varchar(255),

primary key(teacher_name,teacher_pass)

);

create table student_table3 (

student_id int auto_increment primary key,

student_name varchar(255),

java_teacher_name varchar(255),

java_teacher_pass varchar(255),

foreign key (java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass)

);

***修改表时增加外键约束

alter table student_table add foreign key(java_teacher,java_teacher_name) references teacher_table(teacher_id,teacher_name);

***创建自关联(比如员工表中：部门经理和员工)

create table foreign_test(

foreign_id int auto_increment primary key,

foreign_name varchar(255) ,

refer_id int,

foreign key(refer_id) references foreign_test(foreign_id)

);

 

***删除外键约束

alter table student_table3 drop foreign key student_table3_ibfk_1;

 

***定义主表记录删除时，从表记录也随之删除

create table teacher_table4(

teacher_id int auto_increment,

teacher_name varchar(255),

primary key (teacher_id)

);

create table student_table4(

student_id int auto_increment primary key,

student_name varchar(255),

java_teacher int,

foreign key (java_teacher) references teacher_table4(teacher_id) on delete  set null

#on delete cascade

);

 

五、CHECK约束

mysql支持指定check约束，但是不起任何作用，但可以借助触发器机制完成这个

create table check_test(

emp_id int auto_increment primary key,

emp_name varchar(255),

emp_salary decimal,

check(emp_salary>0)

);

 

 

7.索引：索引是存放在模式中的一个数据库对象，虽然索引总是从属于数据表，但是它和数据表一样属于数据库对象。

索引作为数据库对象，在数据字典中独立存放，但不能独立存在，必须属于某个表。

MySQL使用information_schema数据库里的STATISTICS表来保存该数据库实例中的所有索引信息，用户可以查询该表来获取该数据库的索引信息

 

8.创建索引的方式：

a) 自动：当在表上定义主键约束、唯一性约束和外键约束时，系统会为该数据列自动创建对应的索引。

b) 手动：用户可以通过create index...语句来创建索引。

 

9.删除索引的两种方式

a) 自动：数据表被删除时候，该表上的索引会自动被删除

b) 手动：用户可以通过drop index...语句来删除指定数据表上的指定索引

 

10.索引的作用就像书的目录，几乎没有书是没有目录的，利用索引可以加快查询的速度。   

 

11.创建索引的语法格式如下：

a) 例：基于last_name字段建立索引:

i. create index emp_last_name_idx on employees(last_nam);

ii. create index emp_last_name_idx2 on employees(first_name,last_name);

b) 例：删除指定索引

i. drop index emp_last_name_idx2 on employees;

 

12.有的数据库删除索引时候无需指定表名，因为他们在建立所以时都有唯一的名字，比如ORACLE就采用的这种策略，但是MySQL只要求同一个表内的索引不能同名，所以在删除索引时必须指定表名。

 

13.索引的坏处：

a) 与书的目录类似，当数据表中的记录被添加、删除、修改时，数据库系统需要维护索引，因此会有一定的系统开销

b) 存储索引信息需要一定的磁盘空间。

 

 

14.视图：视图并不是数据表，因为它不能存储数据。视图只是一个或者多个数据表中数据的逻辑展示。

a) 好处：

i. 可以限制对数据的访问

ii. 可以使复杂的查询变简单

iii. 提供了数据的独立性

iv. 提供了对相同数据的不同显示

b) 因为视图只是一个表中数据的展示，即查询结果==》建立语法：

i. create or replace view 视图名 as suquery;

c) 删除视图语句：

i. drop view 视图名； 

       

 

 

15.DML语句语法：DDL语句主要操作数据库对象，而DML主要操作数据表里的数据：

a) 插入新数据

b) 修改已有数据

c) 删除不需要的数据

16.常用的DML语句：

a) insert into :每次只能插入一条记录；

i. insert into teacher_table2(teacher_name) values (‘xyz’);

ii. insert into teacher_table2 values (null,’xyz’);

iii. 使用子查询的值插入

insert into student_table2(student_name) select teacher_name from teacher_table2;

iv. 同时插入多个值：

insert into teacher_table2 values (null,’yeeku’),(null,’sharfly’);

b) 当外键列里插值是，外键列的值必须是被参照列里已有的值，外键约束保证被参照的记录必须存在，但并不保证必须由被参照的记录，即外键是可以被null的，如果想要每条从表记录必须存在对应的主表记录，则应该用非空、外键两个约束。

c) update:

i. 会修改表中所有记录的teacher_name值

1. update teacher_table2 set teacher_name=’孙悟空’；

ii. 通过添加where条件类指定只修改特定记录

1. update teacher_table2 set teacher_name=’sss’ where teacher_id>1;

d) delete from:

i. 删除表中所有的记录：

1. delete from 表名;

ii. 删除符合条件的记录：

1. delete from 表名 where 条件

e) 当主表记录被从表记录参照是，主表记录不能被删除。还有一种情况，从不定义外键时使用了on delete cascade或者 on delete set null 时，从表中参照该记录的值设置为删除或者null

 

17.select查询：

a) 单表查询：

i. select  column1,column2... from 数据库 [where condition];

b) 能使用变量的地方大都可以使用数据列：

i. select teacher_id+5 from teacher_table;

ii. select * from teacher_table where teacher_id*3>4;

iii. MYSQL使用concat函数进行字符串链接运算，用来代替java中“+”的功能，并且如果在concat函数的表达式用有null会导致其结果也为null

c) 如果为某条查询结果列重命名时的名称里面有特殊字符，只要把别名用双引号引起来就好

d) select 后面加上distinct 去除重复行，它的作用是去除后面字段组合的重复值，而不是对应记录在数据库里是否重复。

e) sql中判断两个值是否相等的比较运算符是单等号，判断不相等的运算符是<>;sql中的赋值运算符不是等号，而是冒号等号：:=

f) between val1 and val2（可以比较常量也可以是列名）

i. 必须要保证val2小于val2，否则查询不到任何记录

ii. eg：select * from student_table where 2 between java_teacher and student_id;===>表示查询出java_teacher小于等于2，student_id 大于等于2的所有记录。

g) 使用in比较运算符时，必须在in后面的括号里面列出一个或者多个值，它要要求制定列必须与in括号里任意一个值相等，才会有结果输出，in后面的括号里面也可以是列名。

h) like：模糊查询=>%代表任意多个字符    “_”代表任意一个字符

 有时候需要使用%或者_，但是不想把它当做通配符，这个时候就要用“\”作为转义字符。 

标准的sql语句并没有提供反斜线的转义字符，需要使用escape关键字显式的进行转义:select * from student_table where student_name like ‘\_%’ escape ‘\’;

i) is null:用于判断是否为空：select * from table_name where 列名 is null;

j) and  or  not   三个关键字的支持。

i. select * from student_table where student_name like ‘_’ and student_id >3;

ii. select * from student_table where not student_name like ‘\_%’;

k) 查询结果的排序：order by   (数据库默认排序是按插入顺序)

i. select * from student_table order by java_teacher;

ii. select * from student_table order by java_teacher desc,student_name;[desc 降序；默认省略是升序，即asc]

 

18.数据库函数：

a) 出现在sql语句各个位置

b) 单行函数   多行函数

c) 大致几大函数

i. 转换函数

ii. 位函数

iii. 流程控制函数

iv. 加密解密函数

v. 信息函数

d) 几个null函数的处理：

i. ifnull（expr1,expr2）:如果expr1为null，则返回expr2，否则返回expr1；

1. select  ifnull(student_name ,’no name’) from stdudent_table;

ii. nullif(expr1,expr2): 如果expr1和expr2相等，则返回null，否则返回expr1；

1. select nullif(student_name ,’张三’) from student_table;

iii. if(expr1,expr2,expr3):如果expr1为true,不等于0，并且不等于null，则返回expr2，否则返回expr3.

1. select if(isnull(student_name),’没有名字’;,’有名字’) from student_table;

iv. isnull(expr1):判断expr1是否为null，如果为null返回true，否则返回false

e) case函数：

i. 用法一：

1. select student_name ,case java_teacher

when 3 then ‘java老师’

when 4 then ‘ruby老师’

else ‘其他老师’

end

from student_table;

ii. 用法二：

1. select stuent_name ,case 

when student_id<=3 then ‘primary’

when student_id<=6 then ‘intermidate’

else ‘22’

end 

from student_table;

 

通常不推荐在java程序中使用特定的数据库函数，因为这个会是程序代码和特定的数据耦合。

 

19.分组和组函数：[distinic 与 * 不能同时使用]

a) 组函数：

i. avg（[distinc|all]expr）计算多行的平均值

1. 对于可能出现null的列，用ifnull函数来处理

select avg(ifnull(java_teachre,0)) from student_table;

ii. count({*|[distinct|all]expr}) 计算多行expr的总条数  [null不会被计算在内]

1. select count(*) from student_table;

2. select count(distinct java_teacher) from student_table;

iii. max(expr) 计算多行expr的最大值

1. select max(student_id) from student_table;

iv. min(expr) 计算多行expr的最小值

1. select min(student_id) from student_table;

v. sum([distinct|all]expr) 计算多行的expr的总和

1. select sum(student_id) from student_table;  id*记录的行数

2. select sum(20) from student_table; 20*记录的行数

3. select sum(distinct 20)from student_table;   20 

20.group by:后面会跟一个或多个列名，当一列或者多列的值完全相同的时候，系统会把记录当成一组：

select count(*) from student_table group by java_teacher;

 

21.多表连接查询：

a) SQL99：交叉连接、自然连接、使用using子句的连接、使用on子句的连接、全外连接或者左、右外连接

b) SQL92：等值连接、非等值连接、外连接、广义笛卡儿积 

 

22.sql92查询；

a) 多表连接等值查询：select s.*,t.teacher_name from student_table s,teacher_table t where s.java_teacher = t.teacher_id;

b) 广义笛卡儿积：将会产生n*m条记录：select s.*,t.teacher_name from student_table s,teacher_table t;

c) 多表连接非等值查询：select s.*,t.teacher_name from student_table s,teacher_table t where s.java_teahcer > t.teacher_id;

d) 如果增加过滤条件，则要将过滤条件和连接条件用and连接起来:select s.*,t.teacher_name from student_table s,teacher_table t where s.java_teahcer > t.teacher_id and student_name is not null;

e) 自关联：

create table emp_table(

emp_id int auto_increment primary key,

emp_name varchar(255),

manager_id int ,

foreign key (manager_id ) references emp_table (emp_id)

);

      insert into emp_table values (null ,"boss",null);

      insert into emp_table values (null ,"emp1",1);

  insert into emp_table values (null ,"emp2",1);

      insert into emp_table values (null ,"emp3",1);

  

      select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名 from emp_table emp,emp_table mgr where emp.manager_id = mgr.emp_id;

 

23.SQL99的连接查询：

a) 交叉查询（cross join）：select s.* ,t.teacher_name from student_table s cross join teacher_table t;

b) 自然连接(natural join):自然连接会以两个表中的同列名作为连接条件，如果两个表中没有同列名，那么自然连接将和交叉连接没有区别：select s.* ,t.teacher_name from student_table s natual join teacher_table  t ;

c) using 子句连接：using子句可以指定一列或多列，用于显式的指定两个表中的同名列作为连接条件：select s.* ,t.teacher_name from student_table s join teacher_table t using (teacher_id);

d) on子句连接：这是最常用的连接方式，每个on只能指定一个连接条件：select s.*,teacher_name from student_table s join teacher_table t on s.java_teacher =t.teacher_id;(可以完美待敌sql92的等值非等值连接)

e) 左外连接：将会把左边表中所有不满足连接条件的记录全部列出

f) 右外连接：将会把右边表中所有不满足连接条件的记录全部列出

g) 全外连接:mysql并不是全外连接。

 

24.jdbc常用接口和类的简介：

a) DriverManager:

b) Connection:

c) Statement:

d) PreparedStatement:同样有executeUpdate(),executeQuery(),execute()三个方法，只是这几个方法无需传入参数

e) ResultSet：

 

25.JDBC编程步骤：

a) 加载数据库驱动：通常使用Class.forName（driverClass）；

b) 通过DriverManager获取数据库连接：DriverManager.getConnection（String url，String user，String pass）；

i. mysql的url：jdbc:mysql://hostname:port/databasename

ii. oracle数据库的url：jdbc:oracle:thin:@hostname:port:databasename

c) 通过Connection对象创建Statement对象。

i. createStatement():创建基本的Statement

ii. prepareStatement(String sql):根据传入的SQL语句创建预编译的Statement

iii. prepareCall(String sql):根据传入的sql语句创建CallableStatement对象

d) 使用Statement执行SQL语句：

i. execute():

ii. executeUpdate();

iii. executeQuery():

e) 操作结果集。

 

26.使用prepareStatement的好处：

a) 预编译sql语句，性能更好

b) 无须拼接sql语句，编译更简单

c) 可以防止sql注入，安全性更好