Mysql基础（下）

一、SQL语言

1、 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关
系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准，普通话
各数据库厂商在标准的基础上做了自己的扩展，方言
SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，
并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。

2、SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
  注意：sql语句以;结尾
  mysql中的关键字不区分大小写

3、 DDL操作数据库

• 创建
  CREATE DATABASE语句用于创建新的数据库：
  编码方式:gb2312,utf-8,gbk,iso-8859-1

//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;  
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

• 2 查看数据库
  查看当前数据库服务器中的所有数据库

show databases;

查看前面创建的mydb2数据库的定义信息

//show create database 数据库名;
Show CREATE DATABASE mydb2;

• 3 修改数据库

alter database 数据库名 character set 编码方式

• 4 删除数据库
  drop database 数据库名

DROP DATABASE mydb3;

• 5 其他语句
  查看当前使用的数据库

Select database();

切换数据库: use 数据库名

USE mydb2;

4、DDL操作表

CREATE TABLE语句用于创建新表。
语法：

CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);

说明:表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写
[约束] 表示可有可无

• 其他表操作
  删除表：drop table 表名;
  当前数据库中的所有表：show tables;
  查看表的字段信息：desc 表名；
  增加列:alter table 表名 add 新列名 新的数据类型；
  修改列：alter table 表名 change 旧列名 新列名 新的数据类型
  修改表名：alter table 旧表名 rename 新表名;
  查看表格的创建细节：show create table 表名;

5、DML操作

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。
主要包括：INSERT 、UPDATE、 DELETE
小知识：
在mysql中，字符串类型和日期类型都要用单引号括起来。
空值：null

• 插入操作：INSERT:insert into 表名(列名) values(数据值);
  – 注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
  – 非数值的列值两侧需要加单引号
  常见错误: Data too long for column ‘stusex’ at row 1
  – 添加数据的时候可以将列名省略->当给所有列添加数据的时候
  – 此时列值的顺序按照数据表中列的顺序执行
  – 同时添加多行:insert into 表名(列名) values(第一行数据),(第二行数据),(),();
  – 注意：列名与列值的类型、个数、顺序要一一对应。
  参数值不要超出列定义的长度。
  如果插入空值，请使用null
  插入的日期和字符一样，都使用引号括起来。

• 修改（更新）操作：UPDATE:
  语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 列名=值

• 删除操作：DELETE:
  语法 ： DELETE from 表名 【WHERE 列名=值】
  DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
  TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
  删除的数据不能找回。执行速度比DELETE快。

6、DCL

• 创建用户：create user 用户名@指定ip identified by 密码;
• 指定IP才能登陆：create user 用户名@客户端ip identified by 密码;
• 用户授权：

grant 权限1,权限2,........,权限n on
数据库名.* to 用户名@IP; 给指定用户授予指定指定数据库指定权限
例：
grant select,insert,update,delete,create on
chaoshi.* to 'test456'@'127.0.0.1';

grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限
例：
grant all on *.* to 'test456'@'127.0.0.1'

• 用户权限查询：

show grants for 用户名@IP;
例：
show grants for 'root'@'%';

• 撤销用户权限：

revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;
例：
REVOKE SELECT ON *.* FROM 'root'@'%' ;

• 删除用户：

drop user 用户名@IP;
例：
drop user test123@localhost;

7、DQL数据查询

DQL数据查询语言（重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。
查询关键字：SELECT
语法： SELECT 列名 FROM 表名 【WHERE --> BROUP BY–>HAVING–> ORDER BY】

SELECT  要查询的列名称
FROM  表名称
WHERE 限定条件 /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果分组*/
LIMIT offset_start, row_count /*结果限定*/

• 简单查询
  查询所有列：SELECT * FROM 表名;
  查询指定列：SELECT 列名,列名,列名 FROM 表名;
• 条件查询
  条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
  =、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

例如：查询学号为S_1001，S_1002，S_1003的记录 列名 in (列值1,列值2)
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

• 模糊查询
  当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
  语法: 列名 like ‘表达式’ //表达式必须是字符串
  通配符:
  _(下划线): 任意一个字符
  %：任意0~n个字符,‘张%’
• 字段控制查询
  (1)去除重复记录
  去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的
  记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用
  DISTINCT：SELECT DISTINCT sal FROM emp;
  (2)查看雇员的月薪与佣金之和
  因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不
  是数值类型，那么会出错。
  comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出
  现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

(3)给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为

total：`SELECT *, sal+IFNULL(comm,0) AS total FROM emp;`

注：as可以省略

• 排序
  语法： order by 列名 asc/desc
  //asc 升序 desc 降序 默认不写的话是升序
  多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序：

 order by 列名，列名 asc/desc

• 聚合函数
  聚合函数是用来做纵向运算的函数：
  COUNT(列名)：统计指定列不为NULL的记录行数；
  MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

(1) COUNT
当需要纵向统计时可以使用COUNT()。
查询emp表中记录数：SELECT COUNT(*) AS cnt FROM emp;
查询emp表中有佣金的人数：SELECT COUNT(comm) cnt FROM emp;
注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

(2) SUM和AVG
当需要纵向求和时使用sum()函数。
查询所有雇员月薪和：SELECT SUM(sal) FROM emp;
统计所有员工平均工资：SELECT AVG(sal) FROM emp;

(3) MAX和MIN
查询最高工资和最低工资：SELECT MAX(sal), MIN(sal) FROM emp;

• 分组查询
  当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
  注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名
  查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

• HAVING子句
  查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

注：having与where的区别:
1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分
组；而HAVING是对分组后数据的约束。
补充: 多列分组
– 统计出stu表中每个班级的男女生各多少人
select gradename,gender ,count(*) from stu
group by gradename,gender

• LIMIT
  LIMIT用来限定查询结果的起始行，以及总行数。
  limit 开始下标,显示条数；//开始下标从0开始
  limit 显示条数；//表示默认从0开始获取数据

1.查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

注意，起始行从0开始，即第一行开始！
2.查询10行记录，起始行从3开始

SELECT* FROM emp LIMIT 3, 10;

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
l 第一页记录起始行为0，一共查询10行； limit 0,10
l 第二页记录起始行为10，一共查询10行；limit 10,10
l 第三页记录起始行为20，一共查询10行； limit 20,10
pageIndex 页码值 pageSize 每页显示条数

limit  (pageindex-1)*pagesize,pagesize;

查询语句书写顺序：select – from- where- groupby- having- order by-limit
查询语句执行顺序：from - where -group by -having - select - order by-limit