数据库--MySQL

数据库

 数据库（DB）是存放数据的仓库，只不过这些数据的存在有一定的关联，并且按照一定的格式存放在计算机上面，这些数据包含数字，文本，图像，音频，视频等多种格式。MySQL同Oracel,SQL Server一样是关系型数据库，是开放源码的关系型数据库，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。目前被广泛应用于Internet上的中小型网站中。

数据库连接方式

 数据库的连接方式有多种，ODBC，ADO,ADO.NET,JDBC都可以连接数据库，这里以JDBC的连接为例。
 通过JDBC对数据库的访问包括4个主要的组件：Java应用程序，JDBC驱动管理器，驱动器和数据源。
 使用JDBC接口对数据库的操作有以下的几个优点：
 (1) 、有利于用户理解。
 (2) 、使变成人员从复杂的驱动器调用命令和函数中解脱出来，从而致力于应用程序功能的实现。
 (3) 、JDBC支持不同的关系数据库，增强了程序的可移植性。
 缺点：访问数据记录的速度有一定影响。
e.g.
JDBC连接数据库,连接的数据库是postgresql，如果连接MySQL数据库，把驱动改成MySQL对应的驱动就行了。不要忘记的一点就是一定要引入数据库的驱动包。

image.png

package ConnectJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Postgresqljdbc {

    public static void main(String[] args) {
         Connection connection = null;  
            Statement statement = null;  
            try {  
                
                Class.forName("org.postgresql.Driver");  
                connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "123456");  
      
                statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("select * from test_table");
                
                while(resultSet.next()){
                     System.out.println(resultSet.getString(1));
                }
                
                resultSet.close();
                statement.close();  
                connection.close();  
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }  

    }

}

数据库表的操作

 说明：MySQL数据库的语句不用区分大小写，[]括号里面的内容选填
⚠️ MySQL安装之后，系统自动的创建了information_schema 和mysql 数据库, MySQL把有关的数据库信息存储在这两个数据库中，如果删除了这些数据库，MySQL将不能正常工作。

创建数据库

CREATE DATABASE [IF NOT EXISTS] base_name;

使用数据库

USE base_name;

修改数据库

ALTER DATABASE base_name;
用户必须对数据库有修改的权限，才能岁数据库使用ALTER DATABASE命令

删除数据库

DROP DATABASE [IF EXISTS] base_name;
使用该命令的时候一定要小心，它删除的是整个数据库，包括数据库中的表将会永久被删除。

创建数据表

CREATE [TEMPORARY] TABLE table_name(column1,column2 , column3,.....);

TEMPORARY:使用TEMPORARY关键字表示创建的表为临时表，当断开与该数据库的连接的时候，MySQL会自动删除它们。

对于表格列column1的书写格式 col_name type [NOT NULL][...]，type是指列字段的类型，比如int，varchar类型的，对于[]括号里面有一下的关键字来标注列的属性：

• DEFAULT defalut_value:为列指定默认值，且默认值为一个常数，0，1等
• NOT NULL：非空约束
• AUTO_INCREMENT :设置自增属性，从1开始，如果没有申明，则默认值为0，且每个表都只能有一个AUTO_INCREMENT标记的列，并且他们必须被索引，比如表的ID。
• UNIQUE KEY:唯一约束。唯一约束的列值可以为空,并且一张表中可以有多个UNIQUE约束。
• PRIMARY KEY:主键约束，一个表只能有一个主键约束，并且主键一定要为NOT NULL，通常用来定义标志列。
• FOREIGN KEY：外键约束,对应的字段只能是主键或者唯一约束修饰的字段。有外键约束在，插入数据时，先插入主表中的数据，再插入从表中的数据。删除数据时，先删除从表中的数据，再删除主表中的数据。

1、主键约束
 创建主键约束

①、create table people(
id INTEGER NOT NULL,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT,
PRIMARY KEY(id)
);
②、create table people(
id INTEGER NOT NULL PRIMARY KEY,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT
);

 删除主键约束

alter table people drop primary key;

 添加主键约束

alter table people add primary key(id);

2、唯一键约束

//创建唯一键约束,创建表时创建唯一约束
create table people(
id INTEGER NOT NULL,
num INTEGER NOT NULL,
name VARCHAR,
sex VARCHAR,
phnone_num BIGINT,
PRIMARY KEY(id)，
UNIQUE(num)
);
//创建表后创建唯一约束
ALTER TABLE people ADD UNIQUE(num);

修改数据表

ALTER TABLE table_name 表操作

表操作

ADD[COLUM] : 向表中添加新列。例如：ALTER TABLE table_test ADD name char(10) NOT NULL;

RENAME : 修改表名，例如;ALTER TABLE old_test RENAME TO new_test;

CHANGE : 修改列名。例如：ALTER TABLE table_test CHANGE old_name new_name char(10);

MODIFY[COLUMN] : 修改指定列的类型。例如：ALTER TABLE table_test MODIFY b BIGINT NOT NULL;
DROP:删除列或者约束。例如：ALTER TABLE new_table DROP new_name;
...

修改表名还可以用该方法:

RENAME TABLE old_table TO new_table,old_table1 TO new_table1,....;。该命令可以同时修改多张表，中间以逗号隔开。

复制表，使用LIKE关键字，可以创建一个与原表相同的新表，但是内容不会被复制。

CREATE TABLE new_table LIKE old_table;
如果你想要复制表结构并且连内容一起复制，则：
CREATE TABLE new_table AS (SELECT * FROM old_table);

删除表

DROP TABLE table_name;

插入表数据

INSERT INTO table_name(column1,column2) VALUES (values1,values2), (values1,values2),(......);
该命令适合同时插入多条数据。
插入单条数据：INSERT INTO table_name SET column1 = 'values1', column2 = 'values2';

不仅可以通过INSERT语句插入表数据，还可以通过replace语句插入数据，但是与INSERT不同的是REPLACE语句在插入数据之前会将与新数据冲突的旧数据删除。比如主键值相同，使用INSERT语句将无法插入新数据。

删除表数据

DELETE FROM table_name [WHERE ...],用于删除表数据，WHERE子句后面跟的是删除表数据的条件，如果不写WHERE子句，将会删除表中的所有数据。

TRUNCATE 语句也可以删除表数据，它相当于不带WHERE子句的DELETE语句，但是唯一不同的是TRUNCATE语句的删除速度比DELETE语句快，并且删除了表中的数据无法恢复，因此使用的时候要小心。TRUNCATE TABLE table_name;

修改表数据

UPDATE table_name SET column1 = 'new_values1',column2= 'new_values2' [WHERE ...];

数据库查询
 使用SELECT语句可以实现对表的选择，投影以及连接操作。

SELECT [ALL | DISTICT]
FROM table_name
[WHERE] .... WHERE 子句
[GROUP BY] ... GROUP BY子句  对字段进行分组
[HAVING ]... HAVING子句  与where子句用法类似，但是having子句可以使用聚合函数
[ORDER BY ]... ORDER BY子句 对查询数据进行排序
[LIMIT]... LIMIT子句 限制查询的数据

选择列以及给列定义别名查询
 如果不想查询整张表格的数据，只想查询固定几列的数据，那么可以将要查询的列名写在SELECT 关键字后面，并且可以使用AS关键字给查询出来的列命名。不允许在WHERE子句中使用别名

SELECT column1 AS '序号',column2 AS '姓名' FROM table_name;

将查询出来的结果进行替换
&emsp；很多时候我们使用查询语句，并不是想要的到查询出来的数据，而是想获得查询出来的数据背后的意义。就比如在班级考试中，90分以上的成绩为优秀，80到90分为一般。当这个时候我们就会用到CASE关键字。

CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
...
ELSE 表达式
END

 此时的表达式表达的就如Java中的if()...else if()..else if ()...else{}语句一样。
 查询student表，总分在90以上的同学的姓名，学号，标记为优秀,总分在80到90的标记为一般，其余的为及格。

SELECT num,name,
CASE
WHEN score > 90 THEN '优秀'
WHEN score > 80 AND score < 90 THEN '一般'
ELSE '及格'
END AS '等及'
FROM student

数据去重复

SELECT DISTIC name FROM student;

聚合函数
 聚合函数常常用于对一组数值进行计算，然后返回单一的值，比如COUNT,MAX,SUM等，聚合函数通常和GROUP BY 子句一起使用。

函数名称	描述	例子
COUNT	求组中项数，返回int类型整数	SELECT COUNT(num) AS '人数' FROM student
MAX	求最大值	SELECT MAX(score) AS '最高分' FROM student
MIN	求最小值	SELECT MIN(score) AS '最低分' FROM student
SUM	求表达式中所有值得和	SELECT SUM(score) AS '总分' FROM student
AVG	求平均值	SELECT AVG(score) AS '平均分' FROM student
STD或STDDEV	求给定表达式中所有值得标准差	方差的平方根
VARIANCE	返回给定表达式中所有值得方差	方差是各个数据与平均数之差的平方的和的平均数
BIT_AND	逻辑与
BIT_OR	逻辑或
BIT_XOR	逻辑异或

多表连接查询
 多表连接查询的连接方式有两种，全连接和JOIN连接

• 全连接
   全连接是指将各个表用逗号分隔，在FROM子句产生的中间结果是一张表，如果不规定查询的列，那么新表中的列就包含了所有表中出现的列。

SELECT student.num,student.name score.project,score.score
FROM student,score
WHERE score.id = student.ship_id;

 此时查询出来的是一张新表，新表的列为student.num,student.name score.project,score.score 。

• JOIN连接
   JOIN连接主要分为3种：
   (1) . 内连接，使用INNER关键字的连接

SELECT student.num,student.name score.project,score.score
FROM student INNER JOIN score
ON (score.id = student.ship_id);

 其中ON后面跟的条件是用于连接两张表的条件，如果不属于连接两张表的条件，则可以用WHERE子句。如果没有写明查询的列，则将会查询要查询表的所有列，两张表的列直接是拼接到列后面。查询出来的是两张表共有的部分。

 (2) . 外连接，使用OUTTER关键字的连接。外连接包括
   -左外链接，除了查询出来的共有部分，还包括了左表中有的行但右表中不匹配的，右表中设置为NULL;

select * from depart a left join student b on a.id=b.deptId;

   -右外连接，除了查询出来的共有部分，还包括了右表中有的行但左表中不匹配的，左表中设置为NULL;

select * from depart a right join student b on a.id=b.deptId;

   -自然连接

其中的OUTER关键字均可省略

比较运算符
 比较运算符用于比较两个表达式的值，MySQL的比较运算符有：>,<,=,<=,>=,<>(不等于)，!=.

select * from student
where age <18;

通配符匹配符
 LIKE,用于将字符串与指定的字符串想匹配，返回TURE或FALSE。当使用LIKE进行匹配时，会用到“_”和"%"这两个特殊符号进行模糊查询。

select * from student
where name like "%王";  查询student表中，姓王的学生，%表示匹配0个或多个字符，即表示姓名为“王”，“王X”,"王XX"等的数据都将会被查询出来。

select * from student
where name like "%琪_";  查询student表中，姓名的倒数第二个字为“琪”的数据，
_ 表示匹配一个字符，即表示姓名为“琪X”，“X琪X”,"XX琪X"等的数据都将会被查询出来。

正则表达式匹配符
 REGEXP是正则表达式的的缩写。REGEXP的特殊字符以及含义表。

字符	含义	例子
^	匹配字符串的开始
$	匹配字符串的结束
{n}	匹配括号前的字符串出现n次的序列
()	匹配括号里的内容
[a~z]	匹配出现a~z之间的某一个字符
[^a~z]	匹配没有出现在a~z之间的字符
[abc]	匹配字符串中出现的abc

select * from student
where num REGEXP '[010]';  匹配学号中出现010的数据。

子查询
 在查询条件中可以用用另一条查询结果做为查询的一个条件，这时候可以用IN关键字来作为连接的桥梁。

select name from student
where score IN (
  select score from score
  where score>80
);   该段语句表示的是查询成绩在80分以上的学生的姓名。

IN子查询自能返回一列数据，对于比较复杂的查询可以使用嵌套查询

视图

 视图是数据库的视图，与数据库中的表不同的是，视图是一个虚拟表，表的内容由查询的结果而定义的，创建时图的主要原因是为了在复杂的查询中提高查询效率。通常情况下，当一段SQL的查询结果使用的很平凡需要用来当做子查询的时候以及当一张数据结构表很复杂，而我们只需要其中的一些数据的时候会创建视图。

视图与表的关系
(1) . 视图是表的查询结果，如果表的数据变了，会影响视图的结果。
(2) . 视图的增删改也会影响表,但视图并不总是能增删改的；
(3) . 视图的数据与表的数据一一对应时可以修改；
对于视图的insert还应注意：视图必须包含表中没有默认值的列。

操作指令	sql代码
创建视图	create view(column1，column2...) view_name as select 语句;
修改视图	create or replace view view_name as select 语句; alter view view_name as select 语句;
删除视图	drop view [if exists] view_name ; (只会删除视图的虚拟表，不会删除真是存在的表数据，一次可删多个视图)
查询视图详情	desc view_name(查看视图的结构详情) show fields from view_name select * from view_name;
查询存在的视图	show tables
更新视图数据	update view_name set 语句;
插入视图数据	insert into view_name(column1,column2,...) values(values1,values2,...);
删除视图数据	delete from view_name [where 子句] 可以通过delete语句删除视图基本表的数据，但是对于依赖多个基本表的视图，不能通过delete删除

e.g.

create view v_student as select num,name,sex from student with check option;
select * from v_student;
select phnone_num from student where name in (select name from v_student where name='琪琪')；
INSERT INTO v_student(num,name,sex) VALUES(0001,'柴油','男');

 以上的例子可以看出，视图一旦创建完毕，就可以像一个普通表那样使用，主要用来查询。
  如果要自定义视图列名，请注意视图名后面的列的数量必须匹配select子句中的列的数量。如果不定义视图列名，则默认和select表的列名一致。
 在创建有条件限制的视图的时候，语句后加上with check option; 语句可以保证数据的安全性。即当你想要更新视图的时候，如果更新的内容和创建视图的条件冲突就不会更新成功，保证数据安全性。

 在更新视图的时候应该注意有下列任一结构，视图不能更新：
 (1) . 聚合函数。
 (2) . distinct关键字
 (3) . group by子句
 (4) . order by子句
 (5) . union 、union all等集合运算符
 (6) . from子句中包含多个表
 (7) . where子句中包含相关子查询
 (8) . 如果视图中有计算列，则不能更新
 (9) . 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

--创建表
create table student(
    num INTEGER,
    name VARCHAR,
    sex VARCHAR,
    phnone_num BIGINT
);

-- 删除表
drop TABLE student;


--插入表数据
insert into student (num,name,sex,phnone_num) values(01020180001,'琪琪','女',15884498989),
                                                  (01020180002,'岳岳','男',5884498989),
                                                  (01020180003,'琴琴','女',154444444),
                                                    (01020180004,'康康','男',8989),
                                                    (01020180005,'凤凤','女',7666776),
                                                    (01020180006,'丫妹儿','女',443333);

--查询表
select * from student;

--删除视图
drop view v_student;

--创建视图
create view v_student as select num,name,sex from student with check option;

--查询视图
select * from v_student;

--查询视图
select phnone_num from student where name in (select name from v_student where name='琪琪');

--为视图插入数据
INSERT INTO v_student(num,name,sex) VALUES(0001,'柴油','男');

--删除视图数据
delete from v_student where num = 1;

索引

 MySQL中有多种方法进行访问表中的行，其中最常用的是顺序访问和索引访问。其中顺序访问非常浪费时间且效率很低。但是索引访问能使访问时不必扫描整个数据库就能迅速查到锁需要的数据。
 索引是根据表中一列或者多列按照一定的顺序建立的列值与记录行之间的对应关系表。

建立索引的优点：

• 快速读取数据。
• 保证数据记录的唯一性。
• 实现表与表之间参照的完整性。
• 在使用GROUP BY,ORDER BY 子句进行数据检索时，利用索引可以减少排序和分组的时间。
  注意：
• 当表类型为MyISAM,InnoDB或BDB时，才可以向右NULL,BLOB,TEXT列中添加索引。
• 一个表最多有16个索引，最大索引长度为256个字节。
• 对于CHAR和VARCHAR列，可以索引列的前缀。
• MySQL能在多个表上面创建索引

索引缺点
　　①索引是以文件的形式存储的，会占用磁盘空间。
　　②增，删，改索引列上的数据的时候，对索引也要进行更新，表中的索引越多，更新表的时间越长。

索引的分类

(1) . 普通索引：最基本的索引类型，没有唯一性之类的限制，普通索引的关键字为INDEX。
(2) . 唯一索引：索引列的所有值只能出现一次，即必须是唯一的，其关键字为UNIQUE。
(3) . 主键：主键是一种唯一索引，他必须自定为PRIMARY KEY,每个表只有一个主键。
(4) . 全文索引 ：全文索引的类型为FULLTEXT，全文索引只能在VARCHAR或者TEXT类型的列上面创建，并且只能在MyISAM表上面创建。但是对于大规模的数据，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

创建索引

 表创建完成之后创建索引

1、CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_column_name(length)); // CREATE INDEX 不能创建主键索引
2、ALTER TABLE table_name ADD [UNIQUE | FULLTEXT] INDEX index_name(index_column_name(length));
  ①、ALTER TABLE table_name ADD PRIMARY KEY (index_column_name(length))；//创建主键索引，索引值必须唯一且不能为null。
index_column_name:创建索引的列名。
length:表示使用列的前length个字符创建索引。

 在创建表的时候创建索引

CREATE TABLE [IF NOT EXISTS] table_name (
column1,column2 , column3,.....
PRIMARY KEY (index_column_name), //主键索引
[UNIQUE | FULLTEXT] INDEX [index_name] index_column_name; //唯一，全文，一般索引
)

e.g.

//创建表之后创建索引
--创建索引
CREATE INDEX index_name ON student(num);

ALTER TABLE student ADD INDEX index_name(num);

--查看创建索引的情况
SHOW INDEX FROM student;

//创建表的时候创建索引
create table people(
    id INTEGER NOT NULL,
    num INTEGER NOT NULL,
    name VARCHAR,
    sex VARCHAR,
    phnone_num BIGINT,
    PRIMARY KEY(id),
    INDEX index_name(num)
);

删除索引

①、使用DROP INDEX 删除索引
DROP INDEX index_name ON table_name;
②、使用ALTER TABLE 删除索引
ALTER TABLE table_name
DROP PRIMARY KEY, //删除主键
DROP UNIQUE INDEX index_name, //删除唯一索引
DROP INDEX index_name; //删除索引
DROP FOREIGN KEY fk_symbol; //删除外键
使用DROP 子句可以删除个只能怪各样的索引
e.g.

DROP INDEX index_name ON student;
ALTER TABLE student DROP INDEX index_name;

显示索引信息

SHOW INDEX FROM table_name; \G
//使用\G，是用来格式化输出信息的。

存储过程

 存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
 存储过程的优点：
 (1)、在服务端运行，执行速度快。
 (2)、确保数据库的安全。
 (3)、减少了数据在数据库和服务器之间的传输。即当存储过程执行过一次之后，在以后的操作中只需要在高速缓冲存储器中调用已经编译好的代码执行就可以了，这样就提高了系统的性能。
创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
sp_name:存储过程的名称，默认在当前数据库中创建，如果需要在特定数据库中创建，，则要在名称前面加上数据哭的名称。database_name.sp_name。
proc_parameter:存储过程的参数，其格式为：[IN|OUT|INOUT] param_name type
其中IN 表示输入参数，OUT表示输出参数，INOUT表示输入输出参数。

[IN|OUT|INOUT] param_name type

数据库优化

 尽量避免null,通常情况下最好指定列为NOT NULL,可为null的列会使用更多的存储空间，为NULL的列使得索引、索引统计和值都比较复杂，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MYISAM里可能导致固定大小的索引变成可变大小的索引。通常把 可为NULL的列改为NOT NULL带来的性能提升比较小。除非确实会导致问题才去改。
1、使用join连接来代替子查询，因为MySQL不需要在内存中创建临时表来完成子查询。
2、使用事物，事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。事物有4种特性：
 原子性：只要有一条SQL没有执行成功，事物将会回滚。比如银行取钱不可能出现银行这边的记录已经扣钱了，取钱人那边还没有扣钱记录。
 原子性：
 一致性：
 隔离性：
 持久性：
3、避免使用select * 来做查询，必须指明其要查询的字段
4、避免在where子句中对字段进行null值判断
5、不建议使用%前缀模糊查询
 例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。如果LIKE“%name%”这种情况则可以使用全文索引来完成。