本文共 7911 字，大约阅读时间需要 26 分钟。

一、数据库DataBase,DB

1. 1 概述

指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。简单理解数据的仓库

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库中的数据，数据库是通过数据库管理系统创建和操作的。

1.2 分类

1. 关系型数据库
   MySQL SQL Server Oracle sqlite
2. 非关系型数据库：Redis、MemoryCache 、MongoDB
   热数据处理，访问量极大的数据处理，将用户经常使用的数据放到内存，而不用硬盘空间，增大读取速度

1.3 数据库服务器、数据库及数据表关系

二、SQL语言

2.1 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

各数据库厂商都支持ISO的SQL标准，普通话

各数据库厂商在标准的基础上做了自己的扩展，方言

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建数据库、表等等，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为CRUD操作。

2.2 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。【重点】

2.3 注意事项

1. 每次调用命令，必须 ctrl + c 退出当前命令行，从 mysql > 开始【DOS环境】
2. 注意语句以分号结尾，如果有括号，则在括号外加分号，而括号内紧邻（最后）语句不加任何符号，逗号隔开
3. 注释：【# 开头】【-- 空格 开头】【/* 多行注释 */】
4. 在DOS中，禁止使用右上角的叉号关闭数据库，必须使用【mysql > exit】【mysql > quit】
5. 使用SQLyog可视化工具，F5是刷新数据库， F9执行语句

三、DDL（Data Definition Language）

数据定义，用来定义数据库对象：库、表、列等。

# 当前数据库服务器中的所有数据库有哪些mysql > show databases;# 创建一个数据库mysql > create database mydb1;# 删除数据库mysql > drop database mydb1; # 选择使用数据库 所有对于数据表的操作都是在javaee1904数据库下mysql > use mydb1;# 展示当前数据库下的所有数据表mysql > show tables;# 创建一个数据表mysql > create table person(    # 字段名 数据类型    id int,    # name字段，使用的数据类型是可变长的字符串，最大长度是30个字符     name varchar(30),    # 只有1和0    sex boolean,    # age字段 tinyint ==> Java byte 0 ~ 255     age tinyint,    # 该数据最大长度为8，最多小数位为2    salary float(8, 2),    # info字段 text是文本字段    info text    );# 看一下当前的表结构mysql > desc person;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || age   | tinyint(4)  | YES  |     | NULL    |       || info  | text        | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+   # 看一下当前的表结构mysql > desc person;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       || age   | tinyint(4)  | YES  |     | NULL    |       || info  | text        | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+# 删除字段 alter table person drop info;# 添加字段，性别，使用数据类型为tinyint，第一 够用， 第二 节省空间mysql > alter table person add gender tinyint;# 在指定字段之后添加其他字段mysql > alter table person drop gender;# 在name 字段之后添加新的字段mysql > alter table person add gender tinyint after name;# 修改指定字段的数据类型mysql > alter table person modify name char(30);# 修改指定字段的名字和数据类型mysql > alter table person change gender sex boolean;# 添加新字段 mysql > alter table person add salary float(8, 2);mysql > alter table person add info text;

四、DML（Data Manipulation Language）

数据处理,用来定义数据库记录（数据）

# 插入数据后使用如下语句查看select * from person;-- 规规矩矩插入数据mysql > insert into person(id, name, sex, age, salary, info) values(1, "Lucy", 0, 16, 20, "曾想仗剑走天涯，没想后来遇见Java");-- 指定数据插入mysql > insert into person(id, name, sex, age, info) values(2, "Jack", 0, 66, "爱玩"),(2, "Jone", 0, 88, "老辣"),(...);-- 按照字段顺序，整体插入数据mysql > insert into person values(3, "Rose", 0, 56, 10.05, "打呼");-- update -- 修改数据时如果没有任何的条件约束，所有数据都被修改！！！-- 如果没有开启事务。该操作不可以逆转mysql > update person set name = "加里比海盗Jack"; -- 不允许的操作，影响所有数据-- 通过一些条件来约束数据【常用】mysql > update person set name = "加里比海盗Jack" where id = 1;-- delete -- 删除数据没有任何的约束吗，整个数据表都没有了！！！mysql > delete from person;-- 通过一些条件来约束删除的数据行到底有哪些mysql > delete from person where id = 10;mysql > delete from person where sex = 0;

五、DTL: 事务处理，回滚，存档

-- 保存数据操作的一个安全性，一般用于在数据修改，删除，添加时-- 关闭自动提交mysql > set autocommit = 0;-- 操作存在问题，回滚到set autocommit位置mysql > rollback;-- 确认数据无误 提交在set autocommit 之后的所有SQL语句mysql > commit;

六、DCL（Data Control Language）

数据控制，用来定义访问权限和安全级别

七、DQL（Data Query Language)

select 决定了查哪个列条目，where决定了查哪个行条目

数据查询，用来查询记录（数据）select。

-- select * 查询所有-- 一个在开发中不允许使用的查询语句-- 会展示当前数据库中的所有数据，严重拖慢数据库的效率mysql > select * from person;# 按照字段查询-- 会按照SQL语句指定的字段，生成一个对应的结果集！！！mysql > select id, name, info from person;-- 去重DISTINCTselect DISTINCT sal,comm from emp;-- 可以按照字段查询，另外给字段起一个别名mysql > select id as "编号", name as "姓名", info as "信息" from person;-- 查看员工的月薪与佣金之和，后面判断是否为空，如果是空则0处理-- 保证有null值也能加和select sal,comm,sal+IFNULL(comm,0) from emp;-- 数字可以加和，字符串可以拼接select ename,job,CONCAT(ename,'的工作是',job) AS '描述' from emp;#逻辑运算-- 可以使用最基本的条件-- > < >= <= != <> =mysql > select * from person where age >= 30;-- 可以使用逻辑运算符-- 与 and 或 or, 或者是&&、||mysql > select * from person where age > 30 and salary > 40;SELECT * from stu where gender='female' && age<50;mysql > select * from person where age > 30 or salary > 10;SELECT * from stu where sid='s_1001' || sname='lisi';-- 多项目查询，使用IN，not是取反SELECT * from stu WHERE sid in('s_1001','s_1002','s_1003');SELECT * from stu WHERE sid not in('s_1001','s_1002','s_1003');-- 查询null的记录，空判断，使用 is null 判断select * from stu WHERE age IS null;select * from stu WHERE age IS not null;-- between and 范围内查找，注意小的在前，大的在后SELECT * from stu where age BETWEEN 20 AND 40;-- 排序 -- order by -- asc 升序 desc 降序mysql > select id, name, age, salary from person order by age desc;-- 按照年龄降序排序，如果年龄有相同情况，使用工资的升序mysql > select id, name, age, salary from person order by age desc, salary asc;-- 内置函数 最好别用-- 也会拖慢MySQL数据库效率问题mysql > select max(age) from person;mysql > select min(age) from person;mysql > select avg(age) from person;mysql > select sum(age) from person;mysql > select count(*) from person; -- 非null计数，共有多少个数据SELECT COUNT(comm) AS '奖金' from emp;-- 计数，获得有奖金的人数,括号里面为要查询的关键字段select COUNT(*) from emp where (sal+IFNULL(comm,0))>=2500;-- 获取薪水和佣金之和大于2500的个数-- 模糊查询-- 大量使用在搜索情况下！！！-- like -- % 不限制匹配字符个数 -- _ 必须匹配一个！！！mysql > select * from person where info like "男_";mysql > select * from person where info like "%男%";-- 查询姓名是三个字符的，这里使用三个下划线select * from stu WHERE sname LIKE '___';select * from stu WHERE sname LIKE '_i%';select * from stu WHERE sname not LIKE '%i%';-- 分组 group by-- 凡是和聚合函数同时出现的列名，则一定要写在group by 之后-- 1. 按照性别分组，即男有多少数据，女友多少数据mysql > select sex as "性别", count(1) from person group by sex;-- 2. 找出年龄大于20的，按照性别分组，找出人数大于3的组mysql > select sex, count(1) -- 查什么from person -- 从哪里where age > 20 -- 分组之前什么条件group by sex -- 按照什么分组having count(1) > 3; -- 分组之后的条件-- 分页查询 [重点]-- 按照用户要求的展示数据行数展示，然后其他的分页-- limitmysql > select * from person limit 0, 5;mysql > select * from person limit 5, 5;mysql > select * from person limit 10, 5;mysql > select * from person limit 15, 5;-- 第一个数字，表示从下标指定位置开始, 第二个数字是指定的数据行数-- pageCount 表示当前页码 length 表示当前展示多少行数据-- limit (pageCount - 1) * length, length;

八、注意事项

1. 查询语句书写顺序：select 列 from 表 【where- group by- having- order by-limit】
   where是对原始数据进行筛选，group by把数据进行分组，having是分组后的筛选，order by排序，limit对数据做限制
   where、 group by、 order by、 limit可以单独使用
   having 必须用在group by的后面
2. 查询语句执行顺序：from 表 where -group by -having- order by - select -limit

#列数据是姓名和个数#筛选条件是工资大于2000#按照姓名分组#并对分组后的结果按照姓名含有S进行筛选#员工编号降序排序#显示第一页五行数据select ename,COUNT(*)from empwhere sal>2000GROUP BY enamehaving ename LIKE '%S%'ORDER BY empno DESClimit 0, 5;

3. 解决数据库移植问题，定义引擎和编码

CREATE TABLE `user` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `username` VARCHAR(32) NOT NULL COMMENT '用户名称',  `birthday` DATE DEFAULT NULL COMMENT '生日',  `sex` CHAR(1) DEFAULT NULL COMMENT '性别',  `address` VARCHAR(256) DEFAULT NULL COMMENT '地址',  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;#定义引擎和编码

4. 查询某一角色中男人数

select r.role_name,  count(CASE    when u.sex='男' then 1    else null  END) as maleFROM sys_user u  INNER JOIN sys_user_role ur    on u.user_id = ur.user_id  INNER JOIN sys_role r    on ur.role_id = r.role_idgroup by r.role_name

九、IDEA使用sql语句

转载地址：http://gigzi.baihongyu.com/