去重查询

在需要去重的查询字段前加上distinct

模糊查询

模糊查询通配符:

  • % : 表示任意多个字符,包含0个字符.
  • _ : 任意单个字符

查询员工名中包含 ‘a’ 的员工信息

  1. select * from employees  where last_name like '%a%';

查询员工名中第二个字母为_的员工信息

  1. #转移字符
  2. select * from employees  where last_name like '_\_%';
  4. #或 自定义转移字符
  5. select * from employees  where last_name like '_$_%' escape '$';

between and

  • 包含临界值
  • 两个临界值不可调换顺序

查询员工编号在100到200之间的员工信息

  1. select * from employees
  2. where employees_id between 100 and 200;

in

  • 判断字段的值是否属于in列表中的某一项
  • in列表中值的类型必须一致

查询员工部门编号为1,3,5,7的员工姓名和部门编号

  1. select employee_name dpt_id 
  2. from employees
  3. where dpt_id in(1,3,5,7);

is null 和 is not null

  • =或<>不能判断null值

查询有奖金的员工信息

  1. select * from employees
  2. where bonus is not null;

排序查询

  • 升序: ASC(默认)
  • 降序: DESC

排序条件可以为表达式,别名,函数,还可以按多个字段排序

注意: ORDER BY 必须放在WHERE条件之后

查询员工信息,要求工资从高到底

  1. select * from employees ORDER BY salary DESC;

查询员工信息,要求先按工资升序,再按员工编号降序

  1. select * from employees
  2. ORDER BY salary ASC,employess_id DESC;

常见函数

  • 字符函数
  • 数学函数
  • 日期函数
  • 流程控制函数
  • 分组函数

字符函数

  1. length(agrs): 获取参数值的字节个数
  2. concat(agrs,agrs1,...): 拼接字符串
  3. upper(agrs): 将字母变大写
  4. lower(agrs): 将字母变小写
  5. substr(str,opt),substr(str,opt,length): 字符串截取,str表示字符,opt表示索引,从1开始,length表示长度
  6. instr(str,substr): 返回字串第一次出现的索引,如果找不到字串返回0;
  7. trim(str):去前后空格,trim(substr FROM str): 去除字符substr
  8. lpad(str,len,padstr): 左填充; 将指定字符pad填充到字符str左边满长度len
  9. repad(str,len,padstr) : 右填充
  10. replace(str,form_str,to_str) : 将str字符串中的字串from_str替换成to_str字符串

数字函数

  1. round(ages), round(args,number):四舍五入; 当只有一个参数时,就是将小数四舍五入; 两个参数时,将保留小数位number
  2. ceil(number): 向上取整.
  3. floor(number):向下取整.
  4. truncat(double,number) : 去小数点number位后去掉.
  5. mod(num1,num2):取余=>num%num2

日期函数

  1. now() : 返回当前系统日期+时间
  2. curdate(): 返回当前系统日期,不包含时间
  3. curtime():返回当前系统时间,不包含日期
  4. str_to_date(str): 将日期格式的字符转换成指定格式的日期
  1. STR_TO_DATE('2019-4-19','%Y-%m-%d')
  1. date_format(date):将日期转换成字符
  1. DATE_FORMAT('2019/4/19','%Y年%m月%d日')

流程控制函数

  1. if(expre1,expre2,expre3): expre1表示表达式,当条件成立时,选择值expre2,当条件不成立时选择值expre3
  2. case : 用法一
  1. case dept_id #部门编号
  2. when 1 then salary*1.2 #如果是1号部门工资1.2倍
  3. when 2 then salary*1.3 #如果是2号部门工资1.3被
  4. when 3 then salary*1.4 #如果是2号部门工资1.4被
  5. else salary  #否则为原工资
  1. case : 用法二
  1. case 
  2. when 表达式1 then 要显示的值1或表达式1
  3. when 表达式2 then 要显示的值2或表达式2
  4. when 表达式3 then 要显示的值3或表达式3
  5. else 要显示的值n或表达式n

分组函数

  1. sum : 求和,计算时忽略null值
  2. avg : 平均值,计算时忽略null值
  3. max : 最大值,计算时忽略null值
  4. min : 最小值,计算时忽略null值
  5. count:计算个数,计算时忽略null值

    分组查询

语法

  1. select 分组函数,列(要求出现在group by的后面)
  2. from 表
  3. [where 筛选条件] 
  4. group by 分组的列表
  5. [order by 子句]

注意: 查询列表是分组函数和group by 后出现的字段

分组后筛选: having

group by 和 having 都支持别名

分页查询

查询返回的记录太多了，查看起来很不方便， 所谓分页显示，就是将数据库中的结果集，一段一段显示 出来需要的条件

注意：limit子句必须放在整个查询语句的最后！

select **table from column **limit start pageSize;

start：就是当前页的起始索引,pageSize就是每页的条数

currentPage：就是当前页

公式公式：select **table from column **limit (currentPage-1)*pageSize pageSize

连接查询

连接查询又称为多表查寻

  1. 内连接
    1. 等值连接
    2. 非等值连接
    3. 自连接
  2. 外连接
    1. 左外连接
    2. 右外连接
    3. 全外连接(MySQL不支持)
  3. 交叉连接

语法

  1.     select 查询列表
  2.     from 表1 别名 【连接类型】
  3.     join 表2 别名 
  4.     on 连接条件
  5.     【where 筛选条件】
  6.     【group by 分组】
  7.     【having 筛选条件】
  8.     【order by 排序列表】

笛卡尔集

笛卡尔集会在下面条件下产生

  • 省略连接条件
  • 连接条件无效
  • 所有表中的所有行互相连接

内连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

分类：

  1. 等值
  2. 非等值
  3. 自连接

语法：

  1. select 查询列表
  2. from 表1 别名
  3. inner join 表2 别名
  4. on 连接条件;

等值连接

案例1.查询员工名、部门名

  1. SELECT last_name,department_name
  2. FROM departments d
  3.  JOIN  employees e
  4. ON e.`department_id` = d.`department_id`;

案例2.查询名字中包含e的员工名和工种名（添加筛选）

  1. SELECT last_name,job_title
  2. FROM employees e
  3. INNER JOIN jobs j
  4. ON e.job_id=  j.job_id
  5. WHERE e.last_name LIKE '%e%';

案例3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数
②在①结果上筛选满足条件的

  1. SELECT city,COUNT(*) 部门个数
  2. FROM departments d
  3. INNER JOIN locations l
  4. ON d.location_id=l.location_id
  5. GROUP BY city
  6. HAVING COUNT(*)>3;

案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
①查询每个部门的员工个数

  1. SELECT COUNT(*),department_name
  2. FROM employees e
  3. INNER JOIN departments d
  4. ON e.department_id=d.department_id
  5. GROUP BY department_name

② 在①结果上筛选员工个数>3的记录，并排序

  1. SELECT COUNT(*) 个数,department_name
  2. FROM employees e
  3. INNER JOIN departments d
  4. ON e.department_id=d.department_id
  5. GROUP BY department_name
  6. HAVING COUNT(*)>3
  7. ORDER BY COUNT(*) DESC;

案例5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

  1. SELECT last_name,department_name,job_title
  2. FROM employees e
  3. INNER JOIN departments d ON e.department_id=d.department_id
  4. INNER JOIN jobs j ON e.job_id = j.job_id
  5. ORDER BY department_name DESC;

非等值连接

查询员工的工资级别

  1. SELECT salary,grade_level
  2. FROM employees e
  3.  JOIN job_grades g
  4.  ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

查询工资级别的个数>20的个数，并且按工资级别降序

  1. SELECT COUNT(*),grade_level
  2. FROM employees e
  3.  JOIN job_grades g
  4.  ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
  5.  GROUP BY grade_level
  6.  HAVING COUNT(*)>20
  7.  ORDER BY grade_level DESC;

自连接


查询员工的名字、上级的名字

  1. SELECT e.last_name,m.last_name
  2.  FROM employees e
  3.  JOIN employees m
  4.  ON e.`manager_id`= m.`employee_id`;

查询姓名中包含字符k的员工的名字、上级的名字

  1. SELECT e.last_name,m.last_name
  2.  FROM employees e
  3.  JOIN employees m
  4.  ON e.`manager_id`= m.`employee_id`
  5.  WHERE e.`last_name` LIKE '%k%';

外连接


应用场景：用于查询一个表中有，另一个表没有的记录

特点：

  1. 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null，外连接查询结果=内连接结果+主表中有而从表没有的记录
  2. 左外连接，left join左边的是主表，右外连接，right join右边的是主表
  3. 左外和右外交换两个表的顺序，可以实现同样的效果
  4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

左外连接

查询哪个部门没有员工

  1. SELECT d.*,e.employee_id
  2.  FROM departments d
  3.  LEFT OUTER JOIN employees e
  4.  ON d.`department_id` = e.`department_id`
  5.  WHERE e.`employee_id` IS NULL;

右外

查询哪个部门没有员工

  1. SELECT d.*,e.employee_id
  2.  FROM employees e
  3.  RIGHT OUTER JOIN departments d
  4.  ON d.`department_id` = e.`department_id`
  5.  WHERE e.`employee_id` IS NULL;

全外

  1. USE girls;
  2.  SELECT b.*,bo.*
  3.  FROM beauty b
  4.  FULL OUTER JOIN boys bo
  5.  ON b.`boyfriend_id` = bo.id;

交叉连接

  1. SELECT b.*,bo.*
  2.  FROM beauty b
  3.  CROSS JOIN boys bo;

子查询

子查询出现的位置
select后面：仅仅支持标量（一列一行）子查询
from后面：支持表子查询
where或having后面：标量子查询、列子查询（一列多行）、行子查询（一行多列）
exists后面：表子查询（多列多行）

数据库的创建和删除

创建数据库

创建数据库的语法

  1. CREATE DATABASE  database_name;

如果不确定要创建的数据库是否存在,可以先使用创建时判断或者查看有哪些数据库

  1. -- 如果数据库不存在才创建
  2. create database if not exists database_name;
  4. -- 查看有那些数据库
  5. show databases;

删除数据库

删除数据库的语法

  1. drop database  database_name;

创建表

创建表的语法

  1. CREATE TABLE  table_name（column_name column_type ...）;    
  3. -- 创建时判断表是否存在 if not exists
  4. CREATE TABLE if not exists  table_name（column_name column_type ...）;

例 : 在这里，我们将在数据库“customers”中创建一个名为“cus_tbl”的表。

  1. CREATE TABLE cus_tbl(  
  2.    cus_id INT NOT NULL AUTO_INCREMENT,  
  3.    cus_firstname VARCHAR(100) NOT NULL,  
  4.    cus_surname VARCHAR(100) NOT NULL,  
  5.    PRIMARY KEY ( cus_id )  
  6. );

查看已创建的表

  1. show tables;

查看数据表的结构

  1. describe table_name;

修改表

修改表名

  1. ALTER TABLE  table_name   
  2. RENAME  TO  new_table_name;

添加字段

添加字段的语法

  1. ALTER TABLE table_name  
  2. ADD new_column_name column_definition  
  3. [ FIRST | AFTER column_name ];  -- 在某列之前或之后

参数的含义 :

  • table_name：指定要修改的表的名称。
  • new_column_name：它指定要添加到表中的新列的名称。
  • column_definition：它指定列的数据类型和定义（NULL或NOT NULL等）。
  • FIRST | AFTER column_name：它是可选的。它告诉MySQL在表中创建列的位置。如果未指定此参数，则新列将添加到表的末尾。

添加多个字段

  1. ALTER TABLE table_name  
  2. ADD new_column_name column_definition  
  3. [ FIRST | AFTER column_name ],  
  4. ADD new_column_name column_definition  
  5. [ FIRST | AFTER column_name ],  
  6. ...  
  7. ;

修改字段

MODIFY 修改某个字段的定义,比如说修改字段的数据类型和约束

  1. ALTER TABLE  table_name   
  2. MODIFY  column_name column_definition  
  3. [  FIRST  | AFTER  column_name];

CHANGE 修改某个字段的字段名,和数据类型或约束

  1. ALTER TABLE  table_name   
  2. CHANGE  COLUMN  old_name new_name   
  3. column_definition  
  4. [  FIRST  | AFTER  column_name]

删除字段

  1. ALTER TABLE  table_name   
  2. DROP COLUMN  column_name;

删除表

删除表中所有的数据

删除全部数据的两种方式

  1. DELETE
  2. TRUNCATE

两个删除的区别 : delete是删除表中的记录 , truncate是删除表然后在创建表

  1. TRUNCATE TABLE table_name;   
  3. -- 或者
  4. delete TABLE table_name

删除表

  1. DROP TABLE   table_name;

插入数据

插入一条数据

  1. -- 指定字段插入
  2. INSERT INTO  table_name（field1，field2，... fieldN）   
  3. VALUES  
  4. （value1，value2，... valueN）;  
  6. -- 全字段插入
  7. INSERT INTO  table_name  VALUES  （value1，value2，... valueN）;

插入多条记录

  1. INSERT INTO  cus_tbl   
  2. （cus_id，cus_firstname，cus_surname）  
  3. VALUES  
  4. （5，  'Ajeet' ，  'Maurya' ），  
  5. （6，  'Deepika' ，  'Chopra' ），  
  6. （7，  'Vimal' ，  'Jaiswal' ）;

更新数据

UPDATE语句用于更新数据库中MySQL表的数据。

  1. UPDATE table_name SET field1=new-value1, field2=new-value2  
  2. [WHERE Clause]

注意：

  • 可以完全更新一个或多个字段。
  • 可以使用WHERE子句指定任何条件。
  • 您可以一次更新单个表中的值。
  • WHERE子句用于更新表中的选定行。

删除数据

删除指定数据-行

  1. DELETE FROM table_name   
  2. WHERE  
  3. (Condition specified);

MySQL的约束

约束是用来限定表中数据准确性、完整性、一致性、联动性的一套规则。在Mysql中，约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息。
MySQL的约束种类 :

  1. 非空约束(not null)
  2. 唯一性约束(unique)
  3. 主键约束(primary key) PK
  4. 外键约束(foreign key) FK
  5. 检查约束(目前MySQL不支持、Oracle支持)

    not null

    创建NOT NULL约束
    1. CREATE TABLE emp(
    2. id INT(10) NOT NULL,
    3. NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
    4. sex CHAR NULL
    5. );

增加NOT NULL约束

  1. ALTER TABLE emp
  2. MODIFY sex VARCHAR(30) NOT NULL;

取消NOT NULL约束

  1. ALTER TABLE emp
  2. MODIFY sex VARCHAR(30) NULL;

取消 not null 约束，增加默认值

  1. ALTER TABLE emp
  2. MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

UNIQUE

同一个表可以有多个唯一约束，多个列组合的约束。 在创建唯一约束的时候，如果不给唯一约束名称，就 默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引

使用表级约束语法

  1. CREATE TABLE USER(
  2. id INT NOT NULL,
  3. NAME VARCHAR(25),
  4. PASSWORD VARCHAR(16),
  5. #使用表级约束语法
  6. CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
  7. );

添加唯一约束

  1. #方式一
  2. ALTER TABLE USER
  3. ADD UNIQUE(NAME,PASSWORD);
  5. #方式二
  6. ALTER TABLE USER
  7. ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);
  9. #方式三
  10. ALTER TABLE USER
  11. MODIFY NAME VARCHAR(20) UNIQUE;

删除唯一约束

  1. ALTER TABLE USER
  2. DROP INDEX uk_name_pwd;

PRIMARY KEY

  1. 主键约束相当于唯一约束+非空约束的组合，主 键约束列不允许重复，也不允许出现空值
  2. 如果是多列组合的主键约束，那么这些列都不允 许为空值，并且组合的值不允许重复。
  3. 每个表最多只允许一个主键，建立主键约束可以 在列级别创建，也可以在表级别上创建。
  4. MySQL的主键名总是PRIMARY，当创建主键约束 时，系统默认会在所在的列和列组合上建立对应的 唯一索引。

创建主键约束
表级模式

  1. CREATE TABLE emp5(
  2. id INT NOT NULL AUTO_INCREMENT,
  3. NAME VARCHAR(20),
  4. pwd VARCHAR(15),
  5. CONSTRAINT emp5_id_pk PRIMARY KEY(id)
  6. );

列级模式

  1. CREATE TABLE emp4(
  2. id INT AUTO_INCREMENT PRIMARY KEY,
  3. NAME VARCHAR(20)
  4. );

组合模式

  1. CREATE TABLE emp6(
  2. id INT NOT NULL,
  3. NAME VARCHAR(20),
  4. pwd VARCHAR(15),
  5. CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
  6. );

添加主键约束

  1. ALTER TABLE emp5
  2. ADD PRIMARY KEY(NAME,pwd);

修改主键约束

  1. ALTER TABLE emp5
  2. MODIFY id INT PRIMARY KEY;

删除主键约束

  1. ALTER TABLE emp5
  2. DROP PRIMARY KEY;