数据库复习（一）

程序胖
172 阅读11分钟

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

数据库概述

  1. 数据库（DataBase:DB）:指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。（文件系统）是存储，维护和管理数据的集合
  2. 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

数据库与数据库管理系统的关系

在这里插入图片描述

数据库服务器，数据库和表的关系

在这里插入图片描述

SQL

特点： 高度非过程化，即用SQL操作数据库，只需要指出“做出来”，无须指明“怎么做”，存取路径的选择和操作的执行由DBMS自动完成。

分类：

  1. DDL ： 数据定义语言，用来定义数据库对象：库，表，列等等
  2. DML ： 数据操作语言，用来操作数据库表中的记录
  3. DQL ： 数据查询语言，用来查询记录
  4. DCL ： 数据控制语言，用来定义访问权限和安全级别

DDL

  1. 创建数据库：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification[,create_specification]......] 列子：

    1. 创建mydb1数据库 语句：CREATE DATABASE mydb1; 结果： 在这里插入图片描述 在这里插入图片描述
    2. 创建使用gbk字符集的mydb3数据库 语句：create database mydb3 character set gbk; 结果：在这里插入图片描述 在这里插入图片描述 在这里插入图片描述
    3. 创建使用gbk字符集的并带校对规则的mydb4数据库 语句：Create database mydb4 character set gbk COLLATE gbk_chinese_ci; 结果：在这里插入图片描述 在这里插入图片描述

  2. 查看数据库：show 例子： 1. 显示数据库语句：show databases; 在这里插入图片描述 2. 显示数据库创建语句：show create database mydb3; 在这里插入图片描述

  3. 删除数据库：drop database db_name; 例子： 1. drop database mydb4; 在这里插入图片描述 在这里插入图片描述

  4. 修改数据库：ALTER DATABASE db_name[alter_specification....] 例子：

    1. alter database mydb3 character set utf8; 在这里插入图片描述

  5. 使用数据库：use db_name;

  6. 查看当前使用的数据库： select database();

  7. 操作数据表： 1. 创建表： create table 表名( 字段1 字段类型， 字段2 字段类型， 字段3 字段类型， ..... ); 常用数据类型： int：整型 double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小 数，即最大值为999.99； char：固定长度字符串类型； char(10) 'abc ' varchar：可变长度字符串类型；varchar(10) 'abc' text：字符串类型;（可以存大数据的文本） blob：字节类型；（存储图片，视频，音频） date：日期类型，格式为：yyyy-MM-dd； time：时间类型，格式为：hh:mm:ss timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值 datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

    例子： 创建一个员工表 在这里插入图片描述

    代码： create table emp( id int, name varchar(50), gender varchar(10), birthday date, entry_date date, job varchar(100), salary double, resume varchar(200) );

    1. 查看当前数据库中所有的表：show tables;
    2. 查看表的字段信息：desc 表名; 在这里插入图片描述
    3. 在表中添加一个列：alter table 表名 add 字段1 字段类型; 在这里插入图片描述 5. 修改表中的列：alter table 表名 modify job(列表名) varchar(60)(要修改字段属性); 6. 删除表中的列：alter table 表名 drop 列表名; 7. 表改名：rename table 表名 to 新表名; 8. 查看表的创建信息：SHOW CREATE TABLE表名; 9. 修改表的字符集为gbk：ALTER TABLE 表名 CHARACTER SET gbk; 10. 列名name修改为username：ALTER TABLE 表名 CHANGE name username varchar(100); 11. 删除表：DROP TABLE 表名 ;

DML（*）

是对表中的数据进行增，删，改的操作。 ps：在mysql中，字符串类型和日期类型都要用单引号括起来。'tom' '2015-09-04'，空值：null

  1. 查询表中所有的数据：select * from 表名;
  2. 插入操作：INSERT 语法：INSERT INTO 表名(列名1，列名2，.....) values(列值1，列值2,...) 列名与列值的类型个数顺序要一 一对应。 练习： 向员工表中插入数据 代码：
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

在这里插入图片描述 在这里插入图片描述 练习批量复制： 代码：

INSERT INTO emp VALUES
(4,'zs','m','2015-09-01',10000,'2015-09-01',NULL),
(5,'li','m','2015-09-01',10000,'2015-09-01',NULL),
(6,'ww','m','2015-09-01',10000,'2015-09-01',NULL);
  1. 修改操作 UPDATE ： 语法：UPADTE 表名 SET 列名1=列值1，列名2=列值2 .....WHERE 列名=值 练习： 1. 将所有员工薪水修改为5000元。 代码：update emp set salary = 5000; 2. 将姓名为’zs’的员工薪水修改为3000元。 代码：update emp set salary = 3000 where name = 'zhangsan';
  2. 删除操作 DELETE 语法：DELETE FROM 表名 【WHERE 列名=值】 练习： 1. 删除表中名称为‘zhangsan’的记实 代码：delete from emp where name = 'zhangsan'; 2. 删除表中所有的记录： 代码：delete from emp; 3. 使用truncate删除表中记录。 代码：TRUNCATE TABLE emp; DELETE 删除表中的数据，表结构还在;删除后的数据可以找回 TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。 删除的数据不能找回。执行速度比DELETE快。

DQL操作

DQL数据查询语言 （重要） 数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表。

查询关键字：SELECT 语法： SELECT 列名 FROM表名 【WHERE --> GROUP BY -->HAVING--> ORDER BY】

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

  1. 基础查询 1. 查询所有列 代码：select * from stu; 2. 查询指定列表 代码：select sid,sname,age from stu;
  2. 条件查询 条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字： =、!=、<>(跟 != 等价)、<、<=、>、>=； BETWEEN…AND； IN(set)；（）括号里跟多个值 IS NULL（表示空值）； IS NOT NULL （表示非空值） AND；与 OR；或 NOT；非 1. 查询性别为女，并且年龄小于50的记录 代码：select * from stu where gender='female' and age<50; 2. 查询学号为S_1001，或者姓名为liSi的记录 代码：select * from stu where sid = 'S_1001' or sname = 'liSi'; 3. 查询学号为S_1001，S_1002，S_1003的记录 代码：select * from stu where sid in ('S_1001','S_1002','S_1003'); 4. 查询学号不是S_1001，S_1002，S_1003的记录 代码：select * from stu where sid not in ('S_1001','S_1002','S_1003'); 5. 查询年龄为null的记录 代码：select * from stu where age is NULL; 6. 查询年龄在20到40之间的学生记录 代码：select * from stu where age>=20 and age <= 40; 代码：select * from stu where age between 20 and 40; 7. 查询性别非男的学生记录 代码：select * from stu where gender = 'female'; 代码：select * from stu where gender != 'male'; 代码：select * from stu where gender <> 'male'; 代码：select * from stu where not gender = 'male'; 代码：select * from stu where gender not in ('male'); 8. 查询姓名不为null的学生记录 代码：select * from stu where sname is not null; 代码：select * from stu where not sname is null;
  3. 模糊查询 当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。 通配符: _ 任意一个字符 %：任意0~n个字符 '%张%' '张_' 练习： 1. 查询姓名由5个字母构成的学生记录 代码：select * from stu where sname like '_____'; 2. 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录 代码：select * from stu where sname like'____i'; 3. 查询姓名以“z”开头的学生记录 代码：select * from stu where sname like 'z%'; 4. 查询姓名中第2个字母为“i”的学生记录 代码：select * from stu where sname like '_i%'; 5. 查询姓名中包含“a”字母的学生记录 代码：select * from stu where sname like '%a%';
  4. 字段控制查询 1. 去除重复记录 去除重复记录（两行或两行以上记录中系列的上的数据都相同）想去除重复记录，需要使用DISTINCT： 练习： 查询性别（使用DISTINCT）： 代码：select distinct gender from stu; 2. 查询的和运算 当两个字段都是同一个数值类型，可以做加运算 练习： 查询雇员的月薪与佣金之和 在这里插入图片描述 代码：SELECT *,sal+comm FROM emp2; 3. 给新的列名加别名 练习： 查询雇员的月薪与佣金之和 在这里插入图片描述 代码：SELECT *,sal+comm as total FROM emp2; 代码：SELECT *,sal+comm total FROM emp2;(也可以将as省去) 4. ifnull 过滤空值 为了更加美观 练习： 将comm中的null改成0 代码：select *,ifnull(comm,0) from emp2;
  5. 排序 order by 列名 asc(默认) desc asc是升序，desc是降序，其中asc是默认的 练习： 1. 查询所有学生记录，按年龄升序排序 代码：select * from stu order by age asc; 2. 查询所有学生记录，按年龄降序排序 代码：select * from stu order by age desc; 3. 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序 代码：SELECT * FROM emp2 order by sal desc,empno asc;
  6. 聚合函数 sum avg max min count 聚合函数是用来做纵向运算的函数： COUNT()：统计指定列不为NULL的记录行数； MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算； SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0； AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0； 1. count()：当需要纵向统计时可以使用COUNT() 练习： 1. 查询stu表中记录数： 代码：select count(*) as cnt from stu; 2. 查询emp2表中有佣金的人数： 代码：select count(comm) as count from emp2 where comm is not null; 代码：SELECT COUNT(comm) cnt FROM emp2;（只要不是null就计算） 3. 查询emp2表中月薪大于2500的人数： 代码：select count(sal) cnt from emp2 where sal>2500; 4. 统计月薪与佣金之和大于2500元的人数： 代码：select count(*) cnt from emp2 where sal+ifnull(comm,0)>2500; 5. 查询有佣金的人数，有领导的人数： 代码：select count(comm),count(mgr) from emp2; 2. SUM和AVG 1. 当需要纵向求和时使用sum()函数 2. 当需要纵向求平均值时使用avg()函数 练习： 1. 查询所有雇员月薪和： 代码：select sum(sal) from emp2; 2. 查询所有雇员月薪和，以及所有雇员佣金和： 代码：select sum(sal),sum(comm) from emp2; 3. 查询所有雇员月薪+佣金和： 代码：select sum(sal+ifnull(comm,0)) from emp2; 4. 统计所有员工平均工资： 代码：select avg(sal) from emp2; 3. max和min 1. 当需要纵向求最大值时使用max()函数 2. 当需要纵向求最小值时使用min()函数 练习： 1. 查询最高工资和最低工资： 代码：select max(sal),min(sal) from emp2;
  7. 分组查询 1. 当需要分组查询时需要使用GROUP BY子句（凡和聚合函数同时出现的列名，一定要写在group by 之后） 练习： 1. 查询每个部门的部门编号和每个部门的工资和： 代码：select deptno,sum(sal) from emp2 group by deptno; 2. 查询每个部门的部门编号以及每个部门的人数： 代码：select deptno,count(*) from emp2 group by deptno; 3. 查询每个部门的部门编号以及每个部门工资大于1500的人数： 代码：select deptno,count(*) from emp2 where sal>1500 group by deptno; 2. HAVING子句 练习： 1. 查询工资总和大于9000的部门编号以及工资和： 代码：SELECT deptno, SUM(sal) FROM emp2 GROUP BY deptno HAVING SUM(sal) > 9000; 3. 注：having与where的区别: 1. having是在分组后对数据进行过滤.， where是在分组前对数据进行过滤 2. having后面可以使用聚合函数(统计函数)， where后面不可以使用聚合函数。 3. WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。
  8. LIMIT 方言 LIMIT用来限定查询结果的起始行，以及总行数。 第一个参数，是从第几行开始，第二个参数，是表示一共查几行 练习： 1. 查询5行记录，起始行从0开始（注意，起始行从0开始，即第一行开始！） 代码：SELECT * FROM emp2 LIMIT 0, 5; 2. 查询10行记录，起始行从3开始 代码：SELECT * FROM emp2 LIMIT 3, 10; 9. 查询相关关键字的顺序 () 1. 查询语句书写顺序：select ---> from ----> where ---> group by ----> having ----> order by --->limit 2. 查询语句执行顺序：from ---> where ---> group by ---> having - select ---> order by ----> limit*

今天复习就到这里，如果其中内容有错误，或者一些不合适，请大家给我指正，谢谢大家！

avatar
文章
阅读
粉丝