MySQL笔记

• Day01.  MySql
• 1   MySql简介
  • 创始人芬兰人，2009年以10亿美金MySql卖给Sun公司
  • 1年后，Sun被Oracle收购
  • MySql不被Oracle重视，开发社区被收缩，开发进度缓慢
  • 开源社区认为MySql存在闭源风险
  • MySql创始人，在MySql源码基础上，开了一个新的分支 MariaDB
• 2   MySql 客户端
  • 进入命令行
    • 连接本机服务器，登录服务器
      • mysql -uroot -p
      • [输入密码root]
  • 2.1     查看数据库
    • show databases;
  • 2.2     进入数据库
    • use test;
    • use hr;
    • use mysql;
  • 2.3     查看数据表
    • show tables;
  • 2.4     查看表结构
    • desc user;
  • 2.5     退出登录、断开连接
    • exit;
    • quit;
    • \q
• 3   建库、建表
  • 3.1     建库
    • -- 删除db1库
      • drop database if exists db1;
    • -- 重新创建db1库
      • create database db1 charset utf8;
    • -- 查看、进入db1库
      • show databases;
      • use db1;
  • 3.2     建表
    • -- 删除stu学生表
      • drop table if exists stu;
    • -- 创建stu表
      • create table stu (
      • id int,
      • name varchar(20),
      • gender char(1),
      • birthday date
      • );
    • -- 查看表结构
      • desc stu;
• 4   数据类型
  • 4.1     数字
    • unsigned 无符号，只有正数
    • zerofill 配合显示位数，如果不足，补0
    • tinyint
    • smallint
    • int
      • int(3)
      • 在查询结果中，不足3位按3位显示，超出3位正常显示
    • bigint
    • float
    • double
      • ·运算不精确
    • decimal
      • decimal(8,2)
      • 总共8位，整数6位，小数2位
  • 4.2     字符串
    • char
      • char(6)
        • 定长字符串，存储访问效率高
        • 字符串长度不足，会补空格
        • 超出6个，根据数据库设置，可能出错，也可能截断
        • 最长255个字符
    • varchar
      • varchar(6)
        • 变长字符串，存储访问效率比char低
        • 最长不超过 65535个字节
        • 一般超过255个字节，应该使用text类型
    • text
      • 长文本类型
      • 最长65535个字节
  • 4.3     日期时间
    • date 年月日
    • time 时分秒
    • datetime 年月日时分秒
    • timestamp
      • 时间戳
      • 与datetime存储相同的数据
      • timestamp最大表示2038年
      • timestamp在插入数、修改数据时，可以自动更新成系统当前时间
• 5   sql
  • 结构化的查询语言
  • Structured Query Language
  • DDL - 数据定义语言（建库建表）
  • DML - 数据操作（增删改）
  • DQL - 数据查询（select）
• 6   中文
  • set names gbk;
  • 把客户端编码告诉服务器，这样服务器可以做正确的编码转换

    

• 7   insert
  • 插入数据
    • insert into stu values(6,'张三','男','1996-8-4');
    • insert into stu(id, name)values(7, '李四');
    • insert into stu(id, name)values(8,'王五'), (9,'赵六'), (10,'钱七');
      • -- 中文
      • -- 先告诉服务器，客户端是什么编码
      • -- 连接断开之前，服务器可以记住这个编码
        • set names gbk;
      • -- 插入数据
        • insert into stu values(6,'张三','男','1996-8-4');
      • -- 查询stu表的数据
        • select * from stu;
      • -- 插入id,name
        • insert into stu(id, name) values(7, '李四');
        • insert into stu(id, name) values(8,'王五'), (9,'赵六'), (10,'钱七');
        • select * from stu;
• 8   update
  • 更新、修改数据
    • update stu set gender='女',birthday='1998-8-4' where id=7;
      • -- 把7，李四的性别和生日，
      • -- 修改成'女', '1998-8-4'
        • update stu set gender='女',birthday='1998-8-4' where id=7;
        • select * from stu;
• 9   delete
  • 删除数据
    • delete from stu where id>8;
      • -- 删除 id>8 的数据
        • delete from stu where id>8;
        • select * from stu;
• 10     select 查询
  • select * from stu //查询所有字段
  • select name, gender from stu // 查询指定的字段
    • -- 准备测试数据
    • -- hr_mysql.sql 放在d盘根目录
    • -- sql脚本文件，
    • -- 建库、建表、插入数据的sql代码
    • -- 执行这个文件中的sql代码
      • source d:\hr_mysql.sql -- 没有分号
    • -- 查看表
      • show tables;
    • -- 员工表表结构
      • desc emps;
    • -- 员工表数据
      • select * from emps;
      • select id,fname,sal,dept_id from emps;
• 11     where子句
  • 设置过滤条件

    

  • -- 员工id是122
    • select id,fname,sal,dept_id
    • from emps
    • where id=122;
  • -- 部门编号dept_id是30
    • select id,fname,sal,dept_id
    • from emps
    • where dept_id=30;
  • -- 工作岗位代码job_id是'IT_PROG'
    • select id,fname,sal,dept_id,job_id
    • from emps
    • where job_id='IT_PROG';
  • -- 部门编号dept_id 不是 50
    • select id,fname,sal,dept_id
    • from emps
    • where dept_id<>50;
  • -- 工资sal>5000
    • select id,fname,sal,dept_id
    • from emps
    • where sal>5000;
  • -- 工资范围 [5000, 8000]
    • select id,fname,sal,dept_id
    • from emps
    • where sal>=5000 and sal<=8000;
    • --
    • select id,fname,sal,dept_id
    • from emps
    • where sal between 5000 and 8000;
  • -- id是 120,122,100,150
    • select id,fname,sal,dept_id
    • from emps
    • where id in(120,122,100,150);
    • --
    • select id,fname,sal,dept_id
    • from emps
    • where id=120 or id=122 or id=100 or id=150;
  • -- 没有部门的员工，dept_id 是 null
    • select id,fname,sal,dept_id
    • from emps
    • where dept_id is null;
  • -- 有提成的员工，com_pct 不是null
    • select id,fname,sal,dept_id
    • from emps
    • where com_pct is not null;
  • -- fname 中包含 en
    • select id,fname,sal,dept_id
    • from emps
    • where fname like '%en%';
  • -- fname 第3、4个字符是en
    • select id,fname,sal,dept_id
    • from emps
    • where fname like '__en%';
• 12     distinct
  • 去除重复值
    • select distinct a from ... //去除a字段重复值
    • select distinct a,b from ... //去除a,b字段组合的重复值
      • -- 查询所有部门id
        • select distinct dept_id from emps
        • where dept_id is not null;
• 13     order by 子句
  • 排序
  • order by a
    • 按a字段升序排列
  • order by a,b
    • 按a字段升序排列，a相同，再按b字段升序排列
  • asc 升序（默认）
  • desc 降序
    • order by a desc
    • order by a desc, b asc
    • order by a desc, b desc
  • 举例
    • -- 查询50部门员工，按工资降序
      • select id,fname,sal,dept_id
      • from emps
      • where dept_id=50
      • order by sal desc;
    • -- 所有员工，按部门升序，相同部门按工资降序
      • select id,fname,sal,dept_id
      • from emps
      • order by dept_id, sal desc;
• 14     查询执行顺序
  • select 字段
  • from
  • where
  • order by
    • 1.  where 过滤
    • 2.  选取字段
    • 3.  order by 排序
• 15     单引号
  • 字符串内容中的单引号，用两个单引号转义
    • 'I'm Abc'
    • 'I''m Abc'
      • use db1;
      • insert into stu(id,name)
      • values(6433, 'I''m Xxx');
      • select * from stu;
  • 15.1  sql注入攻击
    • 通过在sql语句中，注入单引号，来改变sql语句结构
    • user
    • id  username  password
    • 1   abc        123
    • 2   def        456
    • 3   ghi        789
    • • 用户名：def
      • 密码： 1' or '1'='1
        • select * from user
        • where username='def'
        • and password='1' or '1'='1'
      • 防止sql注入攻击
      • 用户填写的内容中，所有单引号，都变成两个单引号
        • select * from user
        • where username='def'
        • and password='1'' or ''1''=''1'
• 16     函数
  • 16.1  字符串函数
    • char_length(字符串) 字符数
    • length(字符串) 字节数
    • left(字符串, length) 获得左侧字符
    • substring(字符串, start, length) 截取字符串
    • instr(字符串, 子串) 查找子串位置
    • concat(s1,s2,s3...) 字符串连接
    • lpad(字符串,8,'*') 左侧填充
      • use hr;
      • -- fname和lname首字母相同
        • select id,fname,lname,sal,dept_id
        • from emps
      • -- where left(fname,1)=left(lname,1);
        • where substring(fname,1,1)=substring(lname,1,1);
      • -- fname和lname连接起来，再对齐中间的空格
        • select concat(lpad(fname, 20, ' '), ' ', lname)
        • from emps;
  • 16.2  数字函数
    • ceil(数字) 向上取整到个位
    • floor(数字) 向下取整到个位
    • round(数字, 2) 四舍五入到小数点2位
    • truncate(数字, 2) 舍弃到小数点2位
    • rand() 随机数[0, 1)
      • -- 工资上涨 11.31%，向上取整到10位
        • select id,fname,sal, ceil(sal*1.1131/10)*10
        • from emps;
      • -- 所有员工随机排序
        • select id,fname,sal,dept_id
        • from emps
        • order by rand();
  • 16.3  日期函数
    • now() 当前日期时间
    • curdate() 当前日期
    • curtime() 当前时间
    • extract(字段 from 日期) 抽取指定字段的值
    • date_add(日期, interval 字段 值) 在指定字段上加一个值
    • datediff(日期1， 日期2) 两个日期之间相差的天数
      • -- 查询系统当前时间
        • select now();
      • -- 1997年入职的所有员工
        • select id,fname,hdate
        • from emps
      • -- where hdate between '1997-1-1'
      • -- and '1997-12-31';
        • where extract(year from hdate)=1997;
      • -- 员工已入职多少年
        • select id,fname,hdate,
        • datediff(now(), hdate)/365 y
        • from emps
        • order by y;
  • 16.4  null值函数
    • ifnull(a, b)
      • a不是null返回a
      • a是null返回b
        • -- 年薪*提成
          • select id,fname,sal,
          • sal*12*(1+ifnull(com_pct, 0)) t
          • from emps
          • order by t desc;
• 17     多行函数、聚合函数
  • 例图

    

  • sum() 和
  • avg() 平均
  • max() 最大
  • min() 最小
  • count() 行数
  • 多行函数不能和其他普通字段一起查询
  • 多个多行函数可以一起查询
  • 多行函数会忽略null值
  • count(*) 记行数
  • count(distinct a) 去除重复再计数
    • -- 最低工资值
      • select min(sal)
      • from emps;
    • -- 最低工资值
      • select id,fname,min(sal)
      • from emps;
• 18     group by  子句，分组求多行函数
  • 例图

    

  • 按指定字段中相同的值进行分组
  • 分组后分别求多行函数
  • 分组字段，可以查询
  • group by a
    • 按a字段相同值分组
  • group by a,b
    • 按a,b组合的相同值分组
      • -- 每个部门的平均工资
        • select dept_id, avg(sal)
        • from emps
        • where dept_id is not null
        • group by dept_id;
      • -- 每个工作岗位job_id的人数
        • select job_id,count(*)
        • from emps
        • group by job_id;