数据库

数据库
    定义：存储数据的仓库，本质是文件系统，可以对数据增删改查等操作

    数据库管理系统：用于建立、使用维护数据库的大型软件，保证数据库的安全。通过管理系统访问其中数据

    常见数据库：
        MYSQL    ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
        Oracle    ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
        DB2        ：IBM公司的数据库产品,收费的。常应用在银行系统中.
        SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
        SyBase    ：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
        SQLite    : 嵌入式的小型数据库，应用在手机端。
        Java相关的数据库：MYSQL，Oracle．
    
    数据库在系统服务：
        开启/关闭：
            方式1: 我的电脑-----> (右键)管理---->服务和应用程序---->服务----找到MySQL服务右键启动或关闭 
            方式2: 进入dos窗口 使用命令: net start mysql 开启MySQL服务;  命令:net stop mysql 关闭MySql服务
        登陆MySQL:
            格式1：cmd>  mysql –u用户名 –p密码
            例如：mysql -uroot –proot
        
            格式2：cmd>  mysql --host=ip地址 --user=用户名 --password=密码
            例如：mysql --host=127.0.0.1  --user=root --password=root
    SQL分类：
        数据定义语言：DDL，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等 
        数据操作语言：DML，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
        数据控制语言：DCL，用来定义数据库的访问权限和安全级别，及创建用户。
        数据查询语言：DQL，用来查询数据库中表的记录。关键字：select，from，where等
    语法：不分大小写 分号结尾
    数据类型：int：整型  double：浮点型  varchar 字符串型  date 日期类型（yyyy-MM-dd）只有年月日
    
数据库操作：
    创建数据库：
         create database 数据库名;
         create database 数据库名 character set 字符集;
    查看数据库：
        查看所有：show databases;
        查看某一个：show create database 数据库名;
    删除数据库：
        drop database 数据库名称;
    切换数据库：
        use 数据库名；
    查看正在使用的数据库：
        select database();
        
        
数据表操作：
    创建表：
        create table 表名(
            字段名 类型(长度) 约束,
            字段名 类型(长度) 约束
        );
    主键约束：
        1.在创建表时创建主键，在字段后面加
            create table tablename(    
                id int primary key,
                .......
            )
        2. 在创建表时创建主键，在表创建的最后来指定主键
            create table tablename(    
                id int，
                .......，
                primary key(id)
            )
    删除主键：
        alter table 表名 drop primary key;
    主键自动增长：主键字段后加auto_increment(只适用MySQL)
    查看表：
        查看所有：show tables;
        查看表结构：desc 表名；
    删除表：
        drop table 表名；
        
    修改表结构：altrt table 表名
        添加列：    alter table 表名 add 列名 类型(长度) 约束;
        修改列的类型长度及约束：alter table 表名 modify 列名 类型(长度) 约束;
        修改列名：    alter table 表名 change 旧列名 新列名 类型(长度) 约束;
        删除列：    alter table 表名 drop 列名；
        修改表名    rename table 表名 to 新表名;
        修改表的字符集：alter table 表名 character set 字符集;

    添加数据：insert into 表名
        常规：insert insert into 表名(列名1，列名2，列名3)values(值1，值2，值3) (可忽略主键)
        简写：insert into 表 values (值1,值2,值3..); --向表中插入所有列  (不能忽略主键)
        一次加多个：insert into 表名 (列名1，列名2，列名3) values （值1，值2，值3),(值1，值2，值3)；

INSERT INTO product (id,pname,price) VALUES (1,'笔记本',5555.99);

 添加数据格式,不考虑主键
INSERT INTO product (pname,price) VALUES('洗衣机',800);

添加数据格式,所有值全给出
INSERT INTO product VALUES (4,'微波炉',300.25);

 添加数据格式,批量写入
INSERT INTO product (pname,price) VALUES 
('智能机器人',25999.22),
('彩色电视',1250.36),
('沙发',5899.02)

 

        注意：    在values中列出的数据位置必须与被加入列的排列位置相对应。
                对于自动增长的列在操作时，直接插入null值即可
                除了数值类型外，其它的字段类型的值必须使用引号引起
    

    更新表记录：update 表名 set 
        update 表名 set 字段名=值,字段名=值;(全改，慎用！)
        update 表名 set 字段名=值,字段名=值 where 条件;

        如：UPDATE sort SET sname='日用品' WHERE sid='s002';
    
    条件查询where的写法：（用id举例子）
        id=6
        id<>6 id不等于6
        id<=6 
        id in（1,3,4,5,6） 包含在内的
        多个条件同时成立：and  
        多个条件任一成立：or 
        不成立：not
        between...and...  之间被修改(小值在前，含头尾)
        like :    % 用来匹配多个字符；例first_name like ‘a%’/‘%a’/‘%a%’；
            _ 用来匹配一个字符。例first_name like ‘a_’；（一个下划线代表一个字符，可用于查询几个字符）
        

    删除表：
        删具体某一条：
        delete from 表名 [where 条件];
        删整表：
        delete from 表名;
        truncate table 表名;

        删整表的区别：    
            delete 一条一条删除，不清空auto_increment记录数。
            truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

    DOS乱码：
        查看所有mysql的编码： show variables like 'character%'; 
    
        一次性修改：set xxx=gbk;/set names gbk;

        永久修改：default-character-set=gbk 客户端编码设置
                character-set-server=utf8 服务器端编码设置    
               注意:修改完成配置文件，重启服务

查询语句：
        查询指定字段信息：select 列名1，列名2 from 表名;    
        查询所有字段：select * from 表名;
        去重查询：select distinct 字段 from 表名；
        查询重命名列：select 字段名 as 别名 from 表名;（临时改名，实质没改）
        查询中进行数学计算：select 列名1，列名2+1000 from 表名；
        
    排序查询：（先过滤条件，在排序）
        升序：order by 列名 [asc]  (升序可不写asc)
            【SELECT * FROM product ORDER BY stock;】
        降序：order by 列名 [desc]
            【SELECT * FROM product ORDER BY stock DESC;】
            
    聚合函数查询计算(竖列计算)：
        count 求和 select count(列名) from 表名;
            SELECT COUNT(*) AS'price' FROM product; //所有列和
            SELECT COUNT(price) FROM product;  //对price列求和
        sum 求和  select sum(列名)  from 表名;
            select sum(zmoney) from zhangwu where zname like '%收入%'; //对所有金额列中的收入求和
        max/min 求最值 select max(列名)  from 表名; 
        avg(列名) 求均值  【不计null】
    
    分组查询：group by 列名          
                select sum(zmoney)
                as 'getsum',zname from zhangwu 
                where zname like '%支出%' 
                group by getsum order by zname;
            
                SELECT 字段1,字段2… FROM 表名 GROUP BY 字段 HAVING 条件;
                
        having&where区别：
                having是在分组后对数据进行过滤.
                where是在分组前对数据进行过滤
                having后面可以使用分组函数(统计函数)
                where后面不可以使用分组函数。

索引优化相关问题

MySQL中的索引官方定义是帮助Mysql高效的获取数据的数据结构。树结构类型，类似于图书的数目索引，可以提高数据检索的效率，降低数据库的IO成本

索引的分类：聚集索引与非聚集索引、唯一索引与非唯一索引

聚集索引的意思可以理解为顺序排列，比如一个主键自增的表即为聚集索引。一个表只能包含一个聚集索引。

唯一索引：如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

CREATE INDEX mycolumn_index ON mytable (myclumn)

建立索引

数据库查询优化方案：

一、对表字段的优化：

1、最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

2、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3、尽可能的使用 varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

二、对SQL语句的优化

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

应尽量避免在 where子句中对字段进行表达式操作、函数操作，这将导致引擎放弃使用索引而进行全表扫描。

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

join 语法，尽量将小的表放在前面，在需要on的字段上，数据类型保持一致，并设置对应的索引，否则MySQL无法使用索引来join查询

in和 not in 也要慎用，否则会导致全表扫描

对于连续的数值，能用 between就不要用 in 了

很多时候可以使用 exist 和not exist代替in和not in。

LIKE操作符 例如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。

对于多张大数据量（这里几百条就算大了）的表JOIN，要 先分页 再JOIN，否则逻辑读会很高，性能很差。

select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。

尽量避免使用游标，因为游标的效率较差（游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果）

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

三、  对数据库进行修改的优化处理

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个

(临时表 #本地临时表 ##全局临时表) 避免频繁创建和删除临时表，以减少系统表资源的消耗。在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

尽量避免大事务操作，提高系统并发能力。

Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志 ORM对象关系映射（性能差）