图片 12

doc命令操作数据库(下)

1、给数据表添加一组数据:

图片 1

参考

图片 2  

网络选图

MySQL数据库的优化:

图片 3

MySQL

   数据库优化的目的:

 2、给数据表添加多组数据:

简介

官网地址

          1、避免出现页面访问错误(

图片 4

Win7安装配置

                          由于数据库连接timeout产生的5XX错误

3、对数据进行删除和修改:

安装版安装配置
> 直接下载安装包,直接下一步安装即可!此处重点说下绿色版的安装过程。

                          由于慢查询造成页面无法加载

图片 5

绿色版安装配置
1.下载:https://dev.mysql.com/downloads/mysql/
2.创建data文件夹
3.创建my-ini配置文件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port=3307
# 设置mysql的安装目录
basedir=E:/dev/soft/MySQL-5.7.19/mysql-5.7.19-winx64/
# 设置mysql数据库的数据的存放目录
datadir=E:/dev/soft/MySQL-5.7.19/mysql-5.7.19-winx64/data/
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 免密码登陆
skip-grant-tables

书写脚本:
1.注册数据库服务
E:\dev\soft\MySQL-5.7.19\mysql-5.7.19-winx64\bin\mysqld.exe --install MySQL_OpenJWeb
echo 执行完毕!
pause
2.卸载数据库服务
E:\dev\soft\MySQL-5.7.19\mysql-5.7.19-winx64\bin\mysqld.exe -remove mysql_OpenJWeb
echo 执行完毕!
pause
3.启动数据据库
NET START MySQL_OpenJWeb
echo 执行完毕!
pause
3.停止数据据库
NET STOP MySQL_OpenJWeb
echo 执行完毕!
pause

                          由于阻塞造成数据无法提交)

 

DDL语法

创建数据库
create database dbName charset utf8;
进入你选择的库:
use databaseName;
设置编码:
set names gbk;
创建数据库表
create table student(id int,name varchar(20),age int);
create table guo(id int primary key,name varchar(20),age
int);(注意添加主键)
create table guo(id int primary key not null AUTO_INCREMENT,name
varchar(36),age INT);(主键非空自增)
create table guo(id int primary key not null AUTO_INCREMENT comment
‘字段注解’,name varchar(36) comment ‘字段注解’,age INT comment
‘字段注解’) comment ‘表注解’;(主键非空自增,添加注解)

CREATE TABLE guo (
    id VARCHAR (36) PRIMARY KEY NOT NULL COMMENT '主键ID,看情况使用,如果是数量比较多最好不要使用自增',
    name VARCHAR (36) COMMENT '普通字符',
    age INT COMMENT '字段注解',
    price DECIMAL (8, 2) NOT NULL COMMENT '金钱格式',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT '表注解';

删除数据库
Drop database dbName;
修改数据库:
Alter datebase db_name;
查看当前数据库服务下的库:
Show databases;
查看当前库中的表:
show tables;
删除表:
drop table table_name;
查看表的结构:
desc tableName;
添加或删除表的字段:
alter table table_name add column ages int;
alter table tb_user drop column ages
查看创建表的sql语句

show create table tableName;
清空表的数据
Truncate tableName;
更改表名
Rename table oldtable to newtable;

          2、
增加数据库的稳定性(很多数据库问题都是由于低低效查询引起的)

图片 6

DML语法

  1. 增:insert into users(userid, username, password) values(1,
    ‘mysql’, ‘mysql’);
  2. 删:delete from users where userid = 1;
  3. 改:update users set password = ‘password’ where userid = 1;
  4. 查:select userid, username, password from users;
  5. 过滤查:select distinct id from a

          3、优化用户体验(流畅页面的访问速度 , 良好的网站功能体)

4、用select查询单个或多个数据信息:

查询模型

  1. where:(in&not in、between and、like、is null)
  2. group by:(max,min,sum,avg,count)
  3. having:(磁盘原始数据使用where,内存中的结果集having;having和where的区别)
  4. order by:(升序排列:asc默认的,降序排列:desc)
  5. limit:(limit(偏移量,限制条目),当偏移量为0时,可以省略偏移量!)
    例子:
    select * from goods where shop_price in(100,12222);
    select * from goods where shop_price between 12 and 66666;
    select * from goods where goods_name like ‘诺基亚%’;
    select * from goods group by cat_id;
    select (market_price-shop_price) as guo from goods having
    guo>100;
    select * from goods order by shop_price limit 3;
    select * from goods order by shop_price limit 0,3;
    注意:列是变量,可以放入函数中(大胆的把列看成变量,变量可以计算,可以放入函数中);投影运算:本有三列,但是取出来两列;广义投影:通过投影的数据进行运算操作得到的列;As:将广义投影给一个新的变量。

 
 可以从以下几个方面进行优化:SQL及索引,数据表结构,系统配置,硬件。其中SQL及索引最重要,首先我们要根据我们的需求写出结构良好的SQL,另外要根据SQL在表中建立有效的索引,但是如果我们的索引过多,不但会影响我们写入的效率,同时还会影响我们查询的效率,所以索引建立要适量有效。但是如果表结构设计不合理,我们就很难写出结构良好的SQL,所以要建立简洁明了的表结构,所以说在设计表结构的时候,我们要考虑怎样对表结构进行查询,怎样的表结构的设计才是有利于表结构的查询,然后是系统配置的优化,但是当前我们大多数的MySQL都是在lnuix系统上,但是系统上是有一些本身的限制,比如说:TCP/IP连接数的限制,打开文件数的限制和一些安全上的限制,因为MySQL查询是基于文件的,没查询一个表就要打开一个文件,如果文件数达到一定的限制,这个文件就无法打开。硬件优化,就是我们要选择更适合数据库的CPU,更快的IO,以及更多的内存,数据库数据要放在内存中查询修改,所以内存越大,对我们数据库的性能就越好,但CPU却不是这样的,CPU越大,不见得对数据库性能越好,因为SQL会对CPU的可数有限制,比如说他并不会用到太多的核数,有的查询他只用于单核,所以说CUP也对数据库有影响,并不是核数越多越好,而IO,目前由于多新型IO设备,如
ssd,,但它并不能减少数据库锁的机制,因为锁是数据库保存完整性的一种机制,虽然IO很快,但它并不能减少阻塞,所以我们说硬件上的优化是成本最高但效果最差的,所以说如果慢查询很多,阻塞很多,那么并发量就会上去,导致应用响应缓慢。

图片 7

子查询

  1. where子查询:(内层的查询结果作为外层sql的比较条件!)
  2. from子查询:(把子查询的结果作为一个表,供你的外边的查询语句使用)
  3. exists子查询:(子查询实际上不产生任何数据,它只返回 ture 或 false
    值)
  4. 比较子查询:
  5. Select子查询:
    例子:
    select goods_name from goods where goods_id=(select
    max(goods_id) from goods);
    select * from(select goods_name,goods_id,shop_price,cat_id
    from goods) as t group by cat_id;
    select * from(select num from a) as t where t.num =(select
    max(num) from a)
    select * from category where exists (select * from goods where
    goods.cat_id=category.cat_id);
    select 学号,姓名,专业名,出生日期 from xs where 出生日期 <
    all(select 出生日期 from xs where 专业名=’计算机’);

 

图片 8

连接查询

语法:Select xxxx from Table1 连接查询的类型 table2 on
table1xx=table2xx;

  1. 左连接:(以左边的数据为主进行查询,右表进行配对,没有的补null)永远以左表为准
  2. 右连接:(以右边的表为主要的查询,左边进行配对,没有的补null)
  3. 内连接:(以左右数据表查询,没有的不需要补null)
  4. 外连接:(MySQL不支持,oracle支持)
  5. 左外连接:(进行两张表中的人员配对?(boy表和gril表))
  6. 多表关联:
    练习:
    select boy.hid,boy.bname,girl.hid,girl.gname from boy right join
    girl on boy.hid=girl.hid;
    select boy.hid,boy.bname,girl.hid,girl.gname from boy left join
    girl on boy.hid=girl.hid;
    select boy.hid,boy.bname,girl.hid,girl.gname from boy inner join
    girl on boy.hid=girl.hid;

1.SQL及索引的优化

图片 9

Union&&Union all查询

Union查询是:把两条或多条sql的查询结果合并成一个结果集。
select * from boy union select * from girl;
注意:

  1. 满足的条件,各语句取出来的列要相同。
  2. 使用union的,完全相同的列,将会被合并。Union all 可以不合并。
  3. 在union的子句中不使用order by,sql合并后得到总的结果集排序。

   
对Max(),Count()函数的优化,子查询的优化,group by
的优化(目的是减少IO),limit优化(使用有索引的列或主键进行orderby操作以避免IO)

5、去除重复值:

视图操作

  1. 视图的创建
    第一类:create view v as select * from table;
    第二类:create view v as select id,name,age from table;
    第三类:create view v[vid,vname,vage] as select id,name,age from
    table;
    查看视图信息:describe v
    查看视图基本信息:show table status like ‘v’
    view的algorithm
  2. 视图DML操作
    创建视图:create view v as select * from sun;
    查看视图:select * from v;
    更改视图:update student_view set name= “guo” where id = ‘1’;
    删除视图:drop view if exists student_view;

 

图片 10

索引

MVI:索引文件
索引是数据的目录,能快速定位数据的位置
Key:普通索引(提高查询速度)
primary key:主键索引
unique key:唯一索引(提高查询速度、约束作用)
fulltext:全文索引(中文环境下,全文索引无效,要分词+索引。
一般使用第三方解决方案:sphinx)
索引可以提高查询的速度,但是增删改查的速度变慢
一般在查询频繁的地方加索引
索引的长度:建索引的时候,
多列索引:把两列或多列看成一个整体,然后建索引
冗余索引:在某个列上存在多个索引
Explain
操作索引:
查看:show index from tableName;
删除:alter table tableName drop index indexName;
增加(普通的):alter table tableName add index indexName;
增加主键索引:alter table tableName add primary key(列名);
删除主键索引:alter table tableName drop primary key;

     a. 如何选择合适的列建立索引: 在where从句,group by, order
by,on从句中出现的列建立索引;索引字段越小越好;离散度大的列放到联合索引的前面。

 6、查询的各种用法:

事物

命令:select @@tx_isolation
DDL的基本语法
视图(view)
View:又称为虚拟表,是sql语句查询的结果
视图的作用:
权限控制的作用 比如某些列允许用户查看,有些列不允许用户查看
简化复杂的查询

     b.索引的维护及优化——重复及冗余索引和对不用索引的删除

between的用法:

Java数据库编程

第一部分:代码实现

public class MysqlConnection {
Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    private static final String JDRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3333/student";
    private static final String UNAME = "root";
    private static final String PWD = "mysql";
    // 通过静态代码块实现
    static {
        try {
            // 1.加载驱动程序
            Class.forName(JDRIVER);
            System.out.println("驱动加载成功!");
            DriverManager.getConnection(URL, UNAME, PWD);
            System.out.println("连接成功!");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

第二部分:配置实现

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123654
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

server:
  port: 7103
spring:
  datasource:
    url: jdbc:mysql://10.9.254.173:3306/invoice?useOldAliasMetadataBehavior=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
    username: root
    password: passw0rd

 2.数据库结构的优化

图片 11

Oracle

    a.选择合适的数据类型,数据类型的选择,重点在于合适。 

 

简介

官网地址

   
 b.表的范式化和反范式化(范式化指的是表设计的规则) 

图片 12

Win7安装配置

       
 反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余,已达到优化效率的目的,反范式化是以空间换时间的做法。