MySQL

DDL: 数据定义语言

---库的操作----
SHOW DATABASES;        显示所有的库
CREATE DATABASE mydb1;  创建库mydb1
DROP DATABASE mydb1;     删除库mydb1
SHOW CREATE DATABASE mydb1;   显示创建数据库详细信息
CREATE DATABASE mydb2 CHARACTER SET gbk;    创建一个使用gbk字符集的数据库
CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
创建一个使用gbk字符集的数据库,并带有校对规则的mydb3数据库
DROP DATABASE mydb3;
ALTER DATABASE mydb2 CHARACTER SET utf8; 查看数据库,并把mydb2的字符集变成utf8

------表结构操作------
SELECT DATABASE();  显示当前数据库
USE mydb1;  使用mydb1的数据库
创建一张员工表
CREATE TABLE employee(
     id                  int,
    name             varchar(100),
    gender           varchar(10),
    birthday         date,
    entry_date     date,
    job                 varchar(100),
   salary            float(8,2),
   resume          text
);
SHOW TABLES;      查看库中的所有表格
DESC  employee;    看看表的结构
SHOW CREATE TABLE employee; 查看表的创建细节
ALTER TABLE employee ADD image blob;  在表的基础上增加image列,类型是blob
ALTER TABLE employee DROP image ;   删除表中名叫image的一列(表中的一个属性)
DELETE FROM employee WHERE name = '';  删除表中的一条记录(横向)
ALTER TABLE employee  MODIFY job varchar(60);  修改表中job的长度,让它变成60
RENAME TABLE employee TO user;   修改表名为user
ALTER TABLE user CHARACTER SET utf8;  修改表的字符集为utf8
ALTER TABLE user CHANGE name username  varchar(100);    列名name变为username

DML:数据操作语言

作用: 用于向数据库表中插入,删除,修改数据
常用的关键字: INSERT    UPDATE    DELETE
注意:  在MySql中,字符串或者是日期要包含在单引号中,   空值: NULL

SELECT * FROM user;    查询表中的所有记录
向表中插入数据
INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES
                              (1,'wjd','male','2010-11-11','2012-11-11','UFO',10000,'strong');

INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES
                              (2,'lzl','female','2010-11-11','2012-11-11','BFM',10000,'beatufilly');

SHOW VARIABLES LIKE 'character%';   查看MySql中的编码
SET character_set_client=gbk;     客户端使用gbk编码
SET character_set_results=gbk;  返回的结果使用gbk编码
使用上面2句搞定编码问题后,就可以向数据库中加入中文
INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES
                              (3,'高圆圆','female','2010-11-11','2012-11-11','BFM',10000,'beatufilly');
UPDATE user SET salary=5000;  将员工的薪水变成5000
UPDATE user SET salary=50000 WHERE username='高圆圆';   将高圆圆的薪水变成50000
UPDATE user SET salary=60000,job='sb' WHERE username='lzl'; 
将lzl的薪水变成60000,工作变成sb
UPDATE user SET salary=salary+1000 WHERE username='wjd'; 用户wjd薪水加1000
DELETE FROM user WHERE username='lzl';  删除表中名叫lzl的记录
DELETE FROM user;(一条一条的删除)
使用truncate删除表中记录
TRUNCATE TABLE user;(摧毁整张表格，重新建立表结构)

DQL:数据查询语言

作用:查询数据,返回结果集

常用关键字: SELECT
SELECT  *  FROM  student ;  查询表中所有学生的信息
SELECT name,math FROM student;  查询学生表中学生姓名对应的数学成绩
SELECT DISTINCT english FROM student;   过滤掉表中英语的重复成绩
SELECT name,math+10 FROM student;   所有学生,数学成绩加10分,在表中数据还是原来数据
查询显示出来的数据是原来的成绩+10分
SELECT name,chinese+math+english FROM student;  查询所有学生的总分
SELECT name 姓名,chinese+math+english 总分 FROM student;  查询所有学生的总分(使用别名)
SELECT * FROM student WHERE name='吴杰栋';  查询吴杰栋的学习成绩
SELECT * FROM student WHERE english>80;   查询英语成绩大于80的学生
SELECT * FROM student WHERE english BETWEEN 70 AND 80; 
查询英语成绩在70到80之间的学生(包括70和80分的)
SELECT * FROM student WHERE name LIKE '吴%';   查询吴同学的成绩
查询数学分>80，语文分>80的同学。
SELECT * FROM student WHERE math>80 AND chinese>80;
SELECT name,english FROM student ORDER by math;
SELECT name AS 姓名,chinese+english+math 总分 FROM student ORDER BY 总分 DESC;
对姓李的学生成绩排序输出
SELECT * FROM student WHERE name LIKE '李%' ORDER BY chinese;
SHOW TABLES;    查询当前正在使用的数据库中一共有多少张表
DROP TABLE IF EXISTS student;  把名字叫student的表删除

MySQL必知必会书+慕课网知识点

(1) DESC  降序排列    ASC 升序排列(默认)   
     select * from ATable where columnName1 = "XXX" order by columnName2   -->按照columnName2列名升序排列
     select * from ATable where columnName1 = "XXX" order by columnName2 DESC   -->按照columnName2列名降序排列
(2) SELECT prod_id,prod_name,prod_price from product ORDER BY prod_price DESC LIMIT 1  -->在产品表中,找出最贵的那个产品
     顺序: from + orderby + limit
(3) 在SQL语句中,单引号''是用来限定字符串的,如果是数值列,不需要使用单引号
(4) SELECT prod_id,prod_name,prod_price from product WHERE prod_price BETWEEN 1000 AND 2000   -->价格在1000和2000之间的产品,其实都包含1000和2000
(5) 使用IN操作符的优点
    SELECT * from product WHERE vend_name IN('联想公司','步步高公司') -->查询是联想公司和步步高公司生成的产品,是精确查询,不是模糊查询,如果没有就查询不到
 (5.1) 选项清单多的时候,查询语句显示更直观
 (5.2) 计算的次序容易管理,因为使用的操作符更少
 (5.3) IN比OR执行更快
 (5.4) 在IN语句中,可以包含其他的SELECT语句
 (5.5) NOT在和IN操作符联合使用时候,可以找出与条件列表不匹配的行
(6) 下划线(_) 通配符
     该通配符匹配的是一个字符
SELECT * from product AS pro
INNER JOIN vendor AS ven on pro.vendor_id = ven.vendor_id 
where ven.vendor_name LIKE '_步高公_'                                  -->步步高公司的产品会被查询
(7) 在MySQL中,使用Concat()来完成对字符串的拼接
SELECT pro.*,CONCAT(ven.vendor_name,"(",ven.vendor_address,")") AS address from product AS pro
INNER JOIN vendor AS ven on pro.vendor_id = ven.vendor_id 
凭借之后,最后使用一下别名,便于在java代码中去获取到该拼接之后查询到的数据
(8) MySQL中关于时间是如何处理的
  (8.1) Date()和Time()使用技巧
        Date() -->如果表中字段是datetime类型,在进行where查询的时候,只指定了日期,那么使用Date可以查询到当前日期的数据
        Time() -->如果表中字段是datetime类型,在进行where查询的时候,只指定了时间,那么使用Time可以查询到当前日期的数据
SELECT * from product where DATE(prod_time) = '2016-02-01'    -->只要是2016-02-01的数据都能被查询到
另外,在开发的时候,如果要使用日期的话,使用Date(),这样如果以后需求变动,需要将原来的date字段变成datetime字段,那么程序中的代码都不需要改变
(8.2) YEAR()和MONTH()使用技巧
SELECT * from product where YEAR(prod_time) = 2016 AND MONTH(prod_time) = 2     -->可查询2016年2月的所有数据,不需要去在意每月的天数变化
(8.3) SELECT DATEDIFF(CURDATE(),"2016-02-14")  -->当前日期和2016-02-14相差的天数
(9) UNION 和 UNION ALL
UNION : 将多条select语句联合到一起,返回查询的结果,如果多条select语句中,有相同的查询结果的话,相同的查询结果会合并,覆盖
UNION ALL : 将多条select语句联合到一起,返回查询的结果,如果多条select语句中,有相同的查询结果的话,相同的查询都会显示出来,不会合并和覆盖
使用规则 :  必须有2条以上的select语句组成
                  每个select查询必须包含相同的列,表达式或聚合函数(查询的顺序可以不同)
                  列数据类型必须兼容
                  在UNION语句中,使用order by语句,只能使用一条,并它是对所有的查询结果进行排序


(10) 在数据库中,如果需要查询某个字段重复的记录,比如用户编号字段(该属性肯定是唯一存在的),
       在N条记录中,那条记录是重复的,并需要知道该重复的用户编号,可以使用 group by + count() + having的形式,写SQL语句,具体如下
  1. SELECT a.userCode,COUNT(a.userCode) from user a  //userCode是唯一存在的
  2. GROUP BY a.userCode
  3. HAVING COUNT(a.userCode) > 1
 (11) 查询师徒三人的打怪总数,利用sql语句实现列转行,使用case...when...then..end的语句完成
  1. SELECT SUM(CASE username  WHEN '孙悟空' THEN kills end) AS '孙悟空',
  2. SUM(CASE username WHEN '猪八戒' THEN kills end) AS '猪八戒',
  3. SUM(CASE username WHEN '沙和尚' THEN kills end) AS '沙和尚'
  4. FROM user a inner JOIN user_kills b on a.id = b.userID



 (12) 如果select...from中查询出来的子结果需要放在update中,按照子条件的的时候,需要使用中间表包装下子查询结果才可以使用

 (13) 使用update语句的时候,可以联合使用inner join语句,完成更加复杂的条件跟新,具体如下图
 (14) 在使用子查询跟新内容的时候,如果子查询中有select ...where语句,需要再外面再包一层select,不然sql语句运行的时候会报错

数据的完整性

(1)  实体(行)完整性

      规定表中的一行(就是每条记录)在表中是唯一的实体,通过定义主键来实现
   主键: PRIMARY KEY     特点 :不能为null,而且是唯一的
        逻辑主键: 比如ID,不代表实际的业务意义,只用来表示一条唯一的记录
        业务主键:  比如username作为主键
                方式一：
CREATE TABLE t1(
id int PRIMARY KEY,
name varchar(100)
);
方式二：可以定义联合主键
CREATE TABLE t2(
id int,
name varchar(100),
PRIMARY KEY(id)
);
方式三：（推荐）
CREATE TABLE t3(
id int,
name varchar(100)
);
ALTER TABLE t3 ADD PRIMARY KEY (id);

(2)  域(列)完整性

  指数据库表的列(即字段)必须符合某种特定的数据类型或约束
    数据类型      长度
    非空约束: NOT NULL
    唯一约束: UNIQUE
CREATE TABLE t5(
username varchar(100) NOT NULL UNIQUE,
gender varchar(100) NOT NULL,
phonenum varchar(100) UNIQUE
);

(3)  参照完整性: 多表,外键约束

一对多:顾客和订单号

一个顾客可以生成多个订单,一个订单只能对应一个顾客   典型的一对多的关系
CREATE TABLE customers(
    id                  int,
    name           varchar(100),
    address       varchar(255),
    PRIMARY KEY(id)
);

CREATE TABLE orders(
      id                           int    PRIMARY KEY,
     order_num             varchar(100),
     price                       float(8,2),
     status                     int,
     customer_id          int,
    CONSTRAINT customer_id_fk  FOREIGN KEY(customer_id) REFERENCES customers(id)
);

多对多:老师和学生

CREATE TABLE teacher(

id int primary key,

name varchar(100),

salary float(8,2)

);

CREATE TABLE student(

id int primary key,

name varchar(100),

grade varchar(10)

);

CREATE TABLE teacher_student(

t_id int,

s_id int,

CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id),

CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id),

PRIMARY KEY(t_id,s_id)

);


 

DQL:数据查询负责的(多表)

多表查询

a, 交叉链接:
SELECT *  FROM  customers,orders;   返回的是两张表记录的笛卡尔积
SELECT *  FROM customers join orders;   
b, 内连接
    隐式内连接(不使用inner join关键字)
    SELECT * FROM customers c, orders o WHERE c.id = o.customer_id;
    这句话,就能查询出正确的,哪个顾客买的是哪个东西
    
    显式内连接(使用inner join 关键字)
    SELECT * FROM customers c inner join orders o on c.id = o.customer_id;

c,外连接 : outer  join
    左外连接: left  outer join = left  join

返回满足连接条件的所有记录，同时返回左表中剩余的其他记录

查询所有客户，有订单的把订单也显示出来

select * from customers c left outer join orders o on c.id=o.customer_id;


 

MySQL问题

使用*.sql脚本导入数据库出现的问题

在使用海豚软件,导出数据库的时候,会出现以下情况,在A表中,有B表的外键关系,但是B表是在A表下面才被建立的话,导入数据库会出现错误
解决方法: (1)将B表移到A表上面,
         (2)SET FOREIGN_KEY_CHECKS=0;将整个数据库中的表都取消外键约束,这样就能成功导入

navicat连接不上问题2003 - can't connect to MySQL server on 'localhost' 10060

  如果远程计算机是window系统的话,直接在计算机的控制面板中,关闭win的防火墙即可

MySql的恢复和备份


设计mysql数据库的一些技巧



MySQL数据类型

 
 
 

MySQL的优化


(1) 表的设计要合理,满足(3NF)范式
    表的设计最高的级别是6范式,一般满足3范式即可
  (1.1)   1NF:       表的属性(列)具有原子性,就是表的列不能再分,指的列本身的含义是精准的不能再分
                           不能有重复的列
             特点:   只要是关系型数据库,就天然的满足第一范式
  (1.2) 2NF:  表中不能有完全重复的一行记录,通过设置一个主键来搞定,而且该主键是自增的
  (1.3) 3NF: 如果列的内容可以推导出来,就是要有外键的存在,一张表中的数据不能冗余
  (1.4) 反3NF : 一般情况下设计表,是要满足3NF的,具体情况例外


构建海量表的步骤(表中的数据尽量是不同的)
创建3张表,表的引擎是MyISAM 
  1. CREATE TABLE dept( /*部门表*/
  2. deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
  3. dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
  4. loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
  5. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
  7. CREATE TABLE emp
  8. (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
  9. ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
  10. job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
  11. mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
  12. hiredate DATE NOT NULL,/*入职时间*/
  13. sal DECIMAL(7,2)  NOT NULL,/*薪水*/
  14. comm DECIMAL(7,2) NOT NULL,/*红利*/
  15. deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
  16. )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
  18. CREATE TABLE salgrade
  19. (
  20. grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  21. losal DECIMAL(17,2)  NOT NULL,
  22. hisal DECIMAL(17,2)  NOT NULL
  23. )ENGINE=MyISAM DEFAULT CHARSET=utf8;
  25. 测试数据
  26. INSERT INTO salgrade VALUES (1,700,1200);
  27. INSERT INTO salgrade VALUES (2,1201,1400);
  28. INSERT INTO salgrade VALUES (3,1401,2000);
  29. INSERT INTO salgrade VALUES (4,2001,3000);
  30. INSERT INTO salgrade VALUES (5,3001,9999);
修改命令行结束符
  1. delimiter $$

创建1个随机函数,一个返回随机字符串函数,一个存储过程
  1. create function rand_num()
  2. returns int(5)
  3. begin
  4. declare i int default 0;
  5. set i = floor(10+rand()*500);
  6. return i;
  7. end $$
  9. create function rand_string(n INT) 
  10. returns varchar(255) #该函数会返回一个字符串
  11. begin 
  12. #chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  13.  declare chars_str varchar(100) default
  14.    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  15.  declare return_str varchar(255) default '';
  16.  declare i int default 0;
  17.  while i < n do 
  18.    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
  19.    set i = i + 1;
  20.    end while;
  21.   return return_str;
  22.   end $$
  24. create procedure insert_emp(in start int(10),in max_num int(10))
  25. begin
  26. declare i int default 0; 
  27. #set autocommit =0 把autocommit设置成0
  28.  set autocommit = 0;  
  29.  repeat
  30.  set i = i + 1;
  31.  insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  32.   until i = max_num
  33.  end repeat;
  34.    commit;
  35.  end $$

调用存储过程
  1. call insert_emp(100001,8000000)$$

(2) 创建适当的索引(一般索引文件容量是表容量的1/3)
  8千万条数据的表中,在未任何优化的情况下,查询的速度很慢,这个可以通过建立合适的索引去解决问题

给 empno字段添加添加主键索引
      alter table emp add primary key(empno);

存储引擎是MyISAM,在数据库文件保存的时候,对应3个文件1,xx.MYD  数据文件    2,xx.MYI 索引文件      3,xx.frm 表结构


(3) 对SQL语句进行优化   定位慢查询     show VARIABLES like '%quer%'

  定位慢查询(slow  query)
   在默认的情况下,mysql是没有启动慢查询,在测试的时候,可以让mysql启动慢查询
  
     mysql> show variables;
            mysql中默认10秒是慢查询

将10秒改成1秒

慢查询日志详解

Query_time: 查询的时间,加索引什么的处理
Lock_time :等待查询的时间,读写分离什么的处理

explain工具的基本用法

优化方式 : 添加索引  

索引的详解

索引的创建
    (1) 主键索引的创建,2种形式
           在创建表的时候,直接指定某列或者某几列为主键(主键和主键索引是一个概念)
           添加表后再指定索引     alter table 表名 add primary key(列名1,列名2..);
       主键索引的特点
           一个表最多只能有一个主键
           一个主键可以指向多列,俗称复合主键
           主键索引的效率是最高的,给ID,一般ID是自增,不能重复,不能为空 

  (2) 唯一索引的创建的三种方式
        方式1 :直接在创建表的时候,指定一列或者某几列唯一索引
        
        方式2 :表创建之后,再指定,这种语法,必须指定索引的名称,如下图的uni_email
        
        方式3 :这种方式不必使用index了
        
   唯一索引的特点
        一张表中可以有多个唯一索引
        唯一索引不能重复,如果没有指定not null,是可以为null的
        列中一定没有重复的数据,就能使用唯一索引
        效率较高,仅次于主键索引,可以考虑优先使用

(3) 普通索引的创建
     添加的2种方式:  
 
特点:  一张表中可以有多个普通索引
          一个普通索引可以指向多列
          普通索引列中的数据可以重复
          效率相对而言较低

(4) 全文索引的创建
      概述 : 针对汉字,英文,文章的检索,可以快速的检索到文章中的关键字
   案例 : 
  1.  CREATE TABLE articles (
  2.    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  3.    title VARCHAR(200),
  4.       body TEXT,
  5.       FULLTEXT (title,body)
  6.      )engine=myisam charset = utf8; // mysql默认下只在myisam才能使用全文索引,并且只支持英文索引
使用:
 


特点:
    mysql默认的全文索引,只对myisam引擎有效,高版本的对INNDB也有效
    mysql默认的全文索引, 只支持英文
    停止词 : 对于普通的字母,mysql是不会对它建立索引的,利用全文索引是搜索不到的
    匹配度 : 全文索引是按照一定的概率来匹配的

解决mysql的全文索引不支持中文的问题,2个解决方法
  (1) 使用mysql的一个中文检索插件  mysqlcft
  (2) 使用中文检索引擎 sphinx 中文版(coreseek)

索引的查询
    (1) desc 表名
    (2) show keys from 表名
    (3) show index from 表名
    (4) show indexs from 表名
索引的修改
      先删除,再添加
索引的删除
       (1) drop index 索引名字 on 表名
       (2) alter table 表名 drop index 索引名
索引的原理
    添加索引之后,为什么会变快? 主要还是二叉树原来,索引带来搜索性能的提升,但是会降低插入,删除性能的下降,因为插入,删除的时候,需要去维护二叉树


索引的注意事项
      经常出现的where语句中的字段,可以建立索引
      唯一性太差的字段,不需要去建立索引,比如性别,种类等查询条件
      更新非常平凡的字段不需要去建立索引,比如登录的次数,用户状态
      不出现在where语句中的字段,也不需要去建立索引

sql语句的优化和正确的使用索引

(1) 
(2) 对于like查询,查询的字段如果建立的索引,但是在使用'%aaa'和'_aa'这种类型的模糊查询的时候,是不会使用到索引,   但是在'aaa%'这样是会使用到索引的
(3) 在使用or连接查询条件的时候,只有or所有的字段都建立的索引,才会使用到索引
(4) 如果列的类型是字符串,该列上面也有索引的话,在使用where进行查询的时间,查询的数据必须用引号引起来,不然不会使用到索引 
        
(5) 如果mysql估计全表扫描的速度比索引查询的速度快,也不会使用索引
(6) group by语句的优化,mysql在默认情况下,在group by之后,会对查询结果进行排序,类似order by这样,如果需求中明确group by之后,可以不用排序,
     可以在SQL语句之后添加 order by null禁止排序,来提高sql的查询效率
(7) 能用连接查询的不要去使用子查询
(8) 索引的使用情况查询
     如何查看索引使用情况的好坏

(9) 如何选择存储引擎
                
                

(9) 如何选择正确的数据类型
     在精度要求高,数据类型使用 decimal,不要去使用float
     在存储引擎是MyISAM的表,要定时进行碎片整理     optimize table 表名

分表技术(水平分表和垂直分表)

              当一个表很大的时候,先通过添加索引来解决,当索引不能搞定的时候,会使用分表技术或者分区技术搞定

水平分割表

    使用QQ登录表来进行一个案例


垂直分割表

    把表中的某个大字段,而且很少被查询,单独的取出,放到另外的一个表,并通过ID去关联,比如图中的answer





(5) 读写分离
(6) 创建存储过程,触发器,函数
(7) 对my.ini进行优化和配置
(8) 由于mysql的查询有缓存,当出现慢查询的时候,第一次查询速度很慢,等第二次查询速度就正常了,这个使用在写SQL的时候,需要加上SQL_NO_CACHE之后,再写SQL,表示select的时候,不启动mysql
     中的缓存机制,这样操作,会放方便的定位到查询速度慢的源头

MySQL常用函数

  1. convert(DATE_FORMAT(c.deliver_date,'%Y%m') using utf8)












posted @ 2017-03-26 08:06  木有杂念  阅读(234)  评论(0)  编辑  收藏  举报