从零开始学 Mysql - 03

目录

1   select 查询

2   where 子句

3   sql语句中的单引号

4   distinct

 5   order by子句

6   字段别名

7   查询语句执行顺序

8   mysql函数

9   字符串

10     数字、数学

11     日期

---

1   select 查询

     select * from tb1

     select a,b,c from tb1

 

2   where 子句

   对查询的数据进行过滤

     =

    <> 

    > >= < <=    

    between 100 and 200             where a>=100 and a<=200

    in(100,50,80,30)

    is null

   like

      模糊查询

      _ 通配符，匹配单个任意字符

      % 通配符，匹配多个任意字符

      \_ 转义符，转移成普通下划线字符

      \%

      \\ 

   not

      not between … and …

      not in(…)

      is not null

      not like

   and 并且

   or  或者

  hr_mysql.sql

  登录mysql

 source  d:\hr_mysql.sql

 show tables;

    select * from employees;

 

   – 部门id是80

   select

     employee_id,first_name,salary,department_id

   from employees

   where department_id=80;

  

   – 员工id employee_id 是120

   select

     employee_id,first_name,salary

    from employees

   where employee_id=120;

   – job_id 岗位代码是 ‘IT_PROG’

   select

     employee_id,first_name,salary,job_id

    from employees

   where job_id=’IT_PROG’;

   – 主管id manager_id是 100

   select

     employee_id,first_name,salary,manager_id

    from employees

   where manager_id=100;

  

   – 部门id department_id 不是 50

   select

     employee_id,first_name,salary,department_id

    from employees

   where department_id<>50;

  

   – 工资 salary 小于等于 3000

   select

     employee_id,first_name,salary

    from employees

   where salary<=3000;

  

   – 工资>=8000并<=10000

   select

     employee_id,first_name,salary

    from employees

   where salary between 8000 and 10000;

  

   – 员工id employee_id是 100,120,170,150

   select

     employee_id,first_name,salary

    from employees

   where employee_id in(100,120,170,150);

  

    – 岗位代码是 ‘IT_PROG’,’FI_MGR’,’HR_REP’

    select

     employee_id,first_name,salary,job_id

    from employees

   where job_id in(‘it_prog’,’FI_MGR’,’HR_REP’);

   

   – 没有主管的员工 manager_id 是null

   select

     employee_id,first_name,salary,manager_id

    from employees

   where manager_id is null;

   – 没有部门的员工 department_id 是 null

   select

     employee_id,first_name,salary,job_id,department_id

    from employees

   where department_id is null;

   – 没有提成的员工 commission_pct 是 null

   select

     employee_id,first_name,salary,commission_pct

    from employees

   where commission_pct is null;

  

    – first_name 中包含 ‘en’

   select

     employee_id,first_name,salary

    from employees

   where first_name like ‘%en%’;

  

   – first_name 第3和第4个字符是 en

    select

     employee_id,first_name,salary

    from employees

   where first_name like ‘__en%’;

  

    – first_name 以 El 开头

   select

     employee_id,first_name,salary

    from employees

   where first_name like ‘El%’;

   – 电话 phone_number 包含 88

   select

     employee_id,first_name,phone_number

    from employees

   where phone_number like ‘%88%’;

   

   – 工资<2500 和 工资>15000

   select employee_id,first_name,salary from employees

   where salary not between 2500 and 15000;

   – 排除 30,50,80,100 部门

   select employee_id,first_name,salary,department_id from employees

   where department_id not in(30,50,80,100);

   – 提成 commission_pct 不是 null

   select employee_id,first_name,salary,commission_pct from employees

   where commission_pct is not null;

   – first_name 中不包含 en

   select employee_id,first_name,salary from employees

   where first_name not like ‘%en%’;

   – phone_number 不包含 44

   select employee_id,first_name,salary,phone_number from employees

   where phone_number not like ‘%44%’;

  

   – 满足条件：90本门所有员工，

   – 或者满足条件：岗位后缀是 ‘CLERK’

   select employee_id,first_name,salary,

          department_id,job_id from employees

   where department_id=90 or job_id like ‘%CLERK’;

  

   – 50部门中工资<=2500，

   – 以及90部门所有员工

   select employee_id,first_name,salary,

          department_id,job_id from employees

   where department_id=90 or

         (department_id=50 and salary<=2500);

  

  

   – sql注入测试

   use db1;

   drop table if exists user;  

   create table user(

   id int primary key auto_increment,

   username varchar(20),

   password char(32)

   );

   insert into user(username, password)

   values(‘abc’, ‘123’),(‘def’,’456’);

  

   select * from user where

   username=’xxxx’ and password=’1’ or ‘1’=’1’;

  

   select * from user where

   username=’xxxx’ and password=’1” or ”1”=”1’;

 

---

3   sql语句中的单引号

      单引号需要用两个单引号来转义

      ‘5 o”clock’

      sql注入攻击

         用户名：xxxx

         密码：1’ or ‘1’=’1

        

            select * from users

            where username=’xxxx’

            and   pasword=’1” or ”1”=”1’      

         防止sql注入

            把单引号替换成两个单引号

---

4   distinct

      去除重复的值

      select distinct a from …

         去除a的重复值

      select distinct a,b from …

         去除a,b字段组合的重复值

   – 所有的部门id department_id，去除重复值

   select distinct department_id from employees

   where department_id is not null;  

   – 所有的部门id，每个部门的岗位代码

   select distinct department_id,job_id

   from employees;

---

 5   order by子句

对查询结果进行排序

select

from

where

order by

l  order by a

n  按a字段升序排序

l  order by a,b

n  按a字段升序排序

n  a字段相同的值，再按b升序排序

l  asc 升序（默认）

n  order by a asc

l  desc 降序

n  order by a desc

n  order by a desc, b desc

排序查询测试

按员工薪水升序排序

select employee_id,first_name,salary

from employees

order by salary;

 

按部门升序排序，相同部门按工资降序

select employee_id,first_name,salary,

department_id

from employees

order by

department_id,salary desc;

 

工资>=10000的员工，按部门排序，相同部门按工资降序

select employee_id,first_name,salary,

department_id

from employees

where salary>=10000

order by

department_id,salary desc;

 

50和80部门的员工，按岗位降序，相同岗位按工资降序

select employee_id,first_name,salary,

job_id

from employees

where department_id in(50,80)

order by job_id desc,salary desc;

---

6   字段别名

l  在查询结果中，给一个字段起别名

l  给一个运算表达式字段，起别名

l  select employee_id as id,…

l  select employee_id id,…

l  select salary*12 sal,…

 

l  where 子句不能使用字段别名

n  where过滤执行时，还没选取字段

l  order by 可以使用字段别名

字段别名测试

查询年薪>=200000，按年薪降序排序

select

employee_id id,first_name as fname,

salary*12 sal

from employees

where salary*12>=200000

order by sal desc;

 

first_name,last_name 连接成一列显

select

employee_id,

concat(first_name,’ ‘,last_name) name,

salary

from employees

order by name;

---

7   查询语句执行顺序

select a,b,slary*12 sal

from

where

order by

 

1.  where

2.  选取字段

3.  order by

---

8   mysql函数

l  字符串

l  数学

l  日期

l  null值

l  加密

l  分支判断

l  多行函数

 

 

9   字符串

char_length(‘a中’) - 字符数

length(‘a中’) - 字节数

concat(‘a’,’b’,’cde’,’fff’) - 字符串连接，其他数据库可用 || 连接字符串，’abc’ || ‘def’

concat_ws(‘;’,’abc’,’def’,’ggg’) - 用分隔符连接字符串  

instr(‘abcdefgdef’,’def’) - 返回第一个子串的位置，从1开始，找不到返回0

locate(‘abc’, ‘—abc—abc—abc-‘) - 返回第一个子串的位置，从1开始，找不到返回0

locate(‘abc’, ‘—abc—abc—abc-‘,5) - 从指定位置向后找

insert(‘abcdefghijkl’,2, 11, ‘—’) - 用子串取代从2位置开始的11个字符

lower(‘AdFfLJf’) - 变为小写

upper(‘AdFfLJf’) - 变为大写

left(‘AdFfLJf’,3) - 返回最左边的三个字符

right(‘AdFfLJf’,3) - 返回最右边的三个字符

lpad(‘abc’, 8, ‘*’) - 左侧填充，指定长度比源字符串少，相当于left

rpad(‘abc’, 8, ‘*’) - 右侧填充，指定长度比源字符串少，相当于left

trim(‘  a  bc   ’) - 去除两端空格

substring(‘abcdefghijklmn’, 3) - 从3位置开始的所有字符

substring(‘abcdefghijklmn’, 3, 6) - 从3位置开始的6个字符

repeat(‘abc’, 3) - 重复三遍abc

REPLACE(‘Hello MySql’,’My’,’Your’) - 子串替换

REVERSE(‘Hello’) - 翻转字符串

SPACE(10) - 返回10个空格

where

字符串函数测试

set names gbk;

 

select  char_length(‘a中’);

select  length(‘a中’);

select  concat_ws(‘;’,’aa’,’bb’,’cc’);

select  locate(‘abc’,’-abc-abc-abc-‘);

select  locate(‘abc’,’-abc-abc-abc-‘,3);

select  insert(‘abcdefghijk’,3,6,’***’);

select  upper(‘aBcDeF’);

select  left(‘aBcDeF’,3);

select  lpad(‘abc’,8,’*’);

select  trim(‘   a  bc  ’) s;

select  substring(‘abcdefghijk’,5);

select  substring(‘abcdefghijk’,5,4);

select  repeat(‘abc’,3);

select  replace(‘-abc-abc-abc-‘,’ab’,’*’);

select  reverse(‘hello’);

 

 

first_name和last_name首字母相同

select

employee_id,

first_name,last_name,

salary

from employees

where

substring(first_name,1,1)=

substring(last_name,1,1);

 

…

left(fist_name,1)=left(last_name,1)

 

 

电话中的 4 替换成8

select

employee_id,first_name,salary,

replace(phone_number,’4’,’8’) tel

from employees;

 

不符合email格式规则的人

select

employee_id,

first_name,last_name,

salary,email

from employees

where email<>concat(

left(first_name,1),

replace(last_name,’ ‘,”));

 

first_name和last_name连成一列显示,空格居中

select

concat(

lpad(first_name,20,’ ‘),

’ ‘,last_name) name

from employees

order by first_name,last_name;

---

10     数字、数学

ceil(3.94) - 天花板，向上取整

floor(3.94) - 地板，向下取整

round(673.4974) - 四舍五入

round(673.4974, 0) - 四舍五入到小数点后两位

round(673.4974, -2) - 四舍五入到百

truncate(234.39, 1) - 舍去至小数点后1位，必须指定位数

format(391.536, 2) - 数字格式化为字符串，###,###.###，四舍五入，第二个参数为小数位数

rand() - 随机浮点数,[0,1)

数字函数测试

select  ceil(3.14);

select  ceil(-3.14);

select  floor(3.14);

select  floor(-3.14);

select  round(523.8648);

select  round(523.8648,2);

select  round(523.8648,-2);

select  truncate(523.8648);

select  truncate(523.8648,1);

select  format(3467343.65345,2);

select  rand();

 

工资上涨9.5129%，向上取整到10位

select

employee_id,first_name,salary,

ceil(salary*1.095129/10)*10 sal

from employees;

---

11     日期

l  日期字符串格式符合mysql的默认格式 ‘2018-3-15’，可以与日期类型数据之间自动转换

NOW()   返回当前的日期和时间

CURDATE()   返回当前的日期

CURTIME()   返回当前的时间

DATE(时间)   提取日期或日期/时间表达式的日期部分

TIME(时间)    提取日期或日期/时间表达式的时间部分

EXTRACT(字段 From 日期)   返回日期/时间按的单独部分

    字段的合法值：

          MICROSECOND

          SECOND

          MINUTE

          HOUR

          DAY

          WEEK

          MONTH

          QUARTER

          YEAR

          SECOND_MICROSECOND

          MINUTE_MICROSECOND

          MINUTE_SECOND

          HOUR_MICROSECOND

          HOUR_SECOND

          HOUR_MINUTE

          DAY_MICROSECOND

          DAY_SECOND

          DAY_MINUTE

          DAY_HOUR

          YEAR_MONTH

DATE_ADD(日期, INTERVAL 数量 字段)   给日期添加指定的时间间隔

    字段的合法值同上  

DATE_SUB(日期, INTERVAL 数量 字段)   从日期减去指定的时间间隔

DATEDIFF(日期1, 日期2)   返回两个日期之间的天数

DATE_FORMAT(日期, 格式)   用不同的格式显示日期/时间

    格式字符：  %Y-%m-%d %H:%i:%s

                %d/%m/%Y

                %Y年%m月%d日

            %a  缩写星期名

            %b  缩写月名

            %c  月，数值

            %D  带有英文前缀的月中的天

            %d  月的天，数值(00-31)

            %e  月的天，数值(0-31)

            %f  微秒

            %H  小时 (00-23)

            %h  小时 (01-12)

            %I  小时 (01-12)

            %i  分钟，数值(00-59)

            %j  年的天 (001-366)

            %k  小时 (0-23)

            %l  小时 (1-12)

            %M  月名

            %m  月，数值(00-12)

            %p  AM 或 PM

            %r  时间，12-小时（hh:mm:ss AM 或 PM）

            %S  秒(00-59)

            %s  秒(00-59)

            %T  时间, 24-小时 (hh:mm:ss)

            %U  周 (00-53) 星期日是一周的第一天

            %u  周 (00-53) 星期一是一周的第一天

            %V  周 (01-53) 星期日是一周的第一天，与 %X 使用

            %v  周 (01-53) 星期一是一周的第一天，与 %x 使用

            %W  星期名

            %w  周的天 （0=星期日, 6=星期六）

            %X  年，其中的星期日是周的第一天，4 位，与 %V 使用

            %x  年，其中的星期一是周的第一天，4 位，与 %v 使用

            %Y  年，4 位

            %y  年，2 位

LAST_DAY(日期) - 返回当月最后一天

日期函数测试

select  now();

select  curdate();

select  curtime();

select  date(now());

select  time(now());

select  extract(year from now());

select  extract(month from now());

select  extract(day from now());

select  date_add(now(),interval 10 year);

select  date_add(now(),interval -10 year);

select  datediff(now(), ‘1995-9-13’);

select  datediff(‘1995-9-13’,now());

select  date_format(now(),’%d/%m/%Y’);

select  last_day(now());

 

 

入职超过25年

select

employee_id,first_name,salary,

hire_date

from employees

where hire_date<date_add(

now(),interval -25 year)

order by hire_date;

 

97年上半年入职

select

employee_id,first_name,salary,

hire_date

from employees

where extract(year from hire_date)=1997

and extract(month from hire_date)<7;

 

…

where hiredate between ‘1997-1-1’

and ‘1997-6-30’

 

 

所有在1月入职的员工

select

employee_id,first_name,salary,

hire_date

from employees

where extract(month from hire_date)=1

order by hire_date;