数据库
1. 概念
- 数据库就是按照数据结构来组织、存储和管理数据的仓库
2. 分类
- 常见的数据库分为：
- 关系型数据库：数据库建立在数据的紧密关系基础之上，称其为关系型数据库
- 非关系型数据库： 数据库建立在数据的松散关系基础之上，称其为非关系型数据库nosql（not only sql）
MySQL数据库
3. SQL语言
- 结构化查询语言SQL(Structured Query Language)，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
- 分类：
- DCL（Data Control Language）数据库控制语言
- 如：grant、deny、revoke等，只有管理员才有相应的权限
- DDL（Data Definition Language）数据库定义语言
- 如：create、drop、alter、truncate 等
- DML（Data Manipulation Language）数据操纵语言
- 如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
- DQL（Data Query Language）数据查询语言
- select（查询）
- DTL - Data Transaction Language - 数据事务语言
- 如：commit、rollback、savepoint等
- DCL（Data Control Language）数据库控制语言
4. 常用命令
- 登录 （注意-u和-p后没有空格）
mysql -u用户名 -p密码 - 创建数据库，并设置字符集
create database cgb2106 default charset=utf8; - 查看所有数据库
show databases; - 删除数据库
drop database cgb2106; - 使用数据库
use cgb2106; - 创建表
create table account( id int primary key auto_increment, username varchar(20), password varchar(20) ) default charset=utf8; - 删除表
drop table account; - 修改表记录，插入一列
alter table account add column num varchar(20); - 显示当前数据库的所有表
show tables; - 查看某张表的结构
desc account; - 表记录常用操作
- 插入记录
insert into account values(1,'张三','123456'); - 查询所有记录
select * from account; - 更新（修改）表
update account set password='123' where id=1; - 删除某条记录
delete from account where id=1; - 查看记录总数
select count(*) from account;
5. 约束
- 主键约束
primary key- 如果为一个列添加了主键，那么该列就是主键列
- 主键非空且不能重复(唯一)
- 通常情况下，每张表都会有主键，一般设置id为主键
- 主键自增策略
- 设置
auto_increment表示使用主键自增策略
- 设置
- 非空约束
not null- 如果一个列设置了非空约束，那么该列的值不能为空，但是可以重复
- 唯一约束
unique- 如果一个列设置了唯一约束，那么这个列的值必须是唯一的，不能存在重复的记录 ，但是可以为空（null可以有多个）
- 外键约束
6. 基础函数
- lower ---- 全转小写
mysql> select ename,lower(ename) from emp; +-------+--------------+ | ename | lower(ename) | +-------+--------------+ | jack | jack | | tony | tony | | hana | hana | | leo | leo | | liu | liu | +-------+--------------+ - upper ---- 全转大写
mysql> select ename,upper(ename) from emp; +-------+--------------+ | ename | upper(ename) | +-------+--------------+ | jack | JACK | | tony | TONY | | hana | HANA | | leo | LEO | | liu | LIU | +-------+--------------+ - length ---- 查看数据长度
mysql> select ename,length(ename) from emp; +-------+---------------+ | ename | length(ename) | +-------+---------------+ | jack | 4 | | tony | 4 | | hana | 4 | | leo | 3 | | liu | 3 | +-------+---------------+ - substr ---- 截取长度，[ 1 , 3 ]
# 截取长度，从1开始，截取3个字母（注意开头为1，不是0） mysql> select ename,substr(ename,1,3) from emp; +-------+-------------------+ | ename | substr(ename,1,3) | +-------+-------------------+ | jack | jac | | tony | ton | | hana | han | | leo | leo | | liu | liu | +-------+-------------------+ - concat ---- 拼接数据
# 拼接数据，可以拼接数字，字母等，并且可以有多个 mysql> select ename,concat(ename,666,'aaa') from emp; +-------+-------------------------+ | ename | concat(ename,666,'aaa') | +-------+-------------------------+ | jack | jack666aaa | | tony | tony666aaa | | hana | hana666aaa | | leo | leo666aaa | | liu | liu666aaa | +-------+-------------------------+ - replace ---- 替换
# 替换，把名字中的a，全部替换成666 mysql> select ename,replace(ename,'a','666') from emp; +-------+--------------------------+ | ename | replace(ename,'a','666') | +-------+--------------------------+ | jack | j666ck | | tony | tony | | hana | h666n666 | | leo | leo | | liu | liu | +-------+--------------------------+ - ifnull ---- 判断是否为null，若为null，用后面的值替代
mysql> select dname,ifnull(dname,'aaa') from dept; +------------+---------------------+ | dname | ifnull(dname,'aaa') | +------------+---------------------+ | accounting | accounting | | research | research | | operations | operations | | NULL | aaa | +------------+---------------------+ - round & ceil & floor ---- round四舍五入，ceil向上取整，floor向下取整
# round()四舍五入 mysql> select comm,round(comm) from emp; +---------+-------------+ | comm | round(comm) | +---------+-------------+ | NULL | NULL | | 2000.00 | 2000 | | 1000.00 | 1000 | | 200.12 | 200 | | 200.58 | 201 | +---------+-------------+ # round(m,n)四舍五入,保留n位小数 mysql> select comm,round(comm,1) from emp; +---------+---------------+ | comm | round(comm,1) | +---------+---------------+ | NULL | NULL | | 2000.00 | 2000.0 | | 1000.00 | 1000.0 | | 200.12 | 200.1 | | 200.58 | 200.6 | +---------+---------------+ # ceil()向上取整，floor()向下取整 mysql> select comm,ceil(comm),floor(comm) from emp; +---------+------------+-------------+ | comm | ceil(comm) | floor(comm) | +---------+------------+-------------+ | NULL | NULL | NULL | | 2000.00 | 2000 | 2000 | | 1000.00 | 1000 | 1000 | | 200.12 | 201 | 200 | | 200.58 | 201 | 200 | +---------+------------+-------------+ - uuid ---- 返回uuid
mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 1a1c9327-eedd-11eb-bf20-98fa9bbd9bb2 | +--------------------------------------+ - now ---- 返回当前时间，年月日 时分秒
- curdate ---- 返回年月日 ，curtime ---- 时分秒
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-07-27 21:19:34 | mysql> select curdate(); +------------+ | curdate() | +------------+ | 2021-07-27 | +------------+ mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 22:31:38 | +-----------+- year 年，month 月，day 日，hour 时，minute 分，second 秒
mysql> select now(),year(now()),month(now()),day(now()); +---------------------+-------------+--------------+------------+ | now() | year(now()) | month(now()) | day(now()) | +---------------------+-------------+--------------+------------+ | 2021-07-27 22:36:53 | 2021 | 7 | 27 | +---------------------+-------------+--------------+------------+ mysql> select now(),hour(now()),minute(now()),second(now()); +---------------------+-------------+---------------+---------------+ | now() | hour(now()) | minute(now()) | second(now()) | +---------------------+-------------+---------------+---------------+ | 2021-07-27 22:37:34 | 22 | 37 | 34 | +---------------------+-------------+---------------+---------------+ - 转义字符
'是sql语句中的符合，如果单写就会出错，使用时，如果需要则进行转义，或者使用"
# 单引号是一个SQL语句的特殊字符 select 'ab'cd' ' # 数据中有单引号时，在前面加一个转义字符 \ ，使其变成普通字符 select 'ab\'cd' # 或者直接使用 " " select "ab'cd"
7. 条件查询
- distinct ---- 去除重复的行
mysql> select * from dept; +--------+------------+------+ | deptno | dname | loc | +--------+------------+------+ | 1 | accounting | 一区 | | 2 | research | 二区 | | 3 | operations | 二区 | +--------+------------+------+ mysql> select distinct loc from dept; +------+ | loc | +------+ | 一区 | | 二区 | +------+ - where ---- where条件（注意不能使用列别名，where中不能使用聚合函数）
mysql> select * from emp where empno=200; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # and 表示并且 两边条件都要成立 mysql> select * from emp where mgr=300 and deptno=2; +-------+-------+------+------+---------------------+------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+------+--------+--------+ | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+------+--------+--------+ # or 表示或者 两边条件有一个成立就行 mysql> select * from emp where mgr=300 or deptno=1; +-------+-------+------+------+---------------------+-------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+--------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+-------+--------+--------+ - in() ， not in()
mysql> select * from emp where sal=3000 or sal=8000 or sal=10000; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # 此时可以换成 in(3000,8000,10000) mysql> select * from emp where sal in(3000,8000,10000); +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # 如果不想要这三个数据，想要其他的，可以在前面加个not mysql> select * from emp where sal not in(3000,8000,10000); +-------+-------+------+------+---------------------+-------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+--------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+-------+--------+--------+ - like ----
%表示通配符，匹配0-n个字符，_表示匹配一个字符# 以l开头的 mysql> select * from emp where ename like 'l%'; +-------+-------+------+------+---------------------+------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+------+--------+--------+ | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+------+--------+--------+ # 以a结束的 mysql> select * from emp where ename like '%a'; +-------+-------+------+------+---------------------+------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+------+---------+--------+ | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | +-------+-------+------+------+---------------------+------+---------+--------+ # 中间包含a的 mysql> select * from emp where ename like '%a%'; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # l后面有两个_ ，一个_代表一个字符位置 mysql> select * from emp where ename like 'l__'; +-------+-------+------+------+---------------------+------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+------+--------+--------+ | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+------+--------+--------+ - null
# 过滤字段值为空的 mysql> select * from emp where mgr is null; +-------+-------+------+------+---------------------+-------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | +-------+-------+------+------+---------------------+-------+------+--------+ # 过滤字段值不为空的 mysql> select * from emp where mgr is not null; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ - between and ---- 只能查询包含的部分，左右都包含
# 查询工资在3000到10000之间的员工信息 mysql> select * from emp where sal>=3000 and sal<=10000; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ mysql> select * from emp where sal between 3000 and 10000; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # 查询入职年份在2017到2019之间的员工信息 mysql> select * from emp where year(hiredate) between 2017 and 2019; +-------+-------+------+------+---------------------+------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+------+---------+--------+ | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | +-------+-------+------+------+---------------------+------+---------+--------+ - limit ----
limit n返回前n条，limit m.n返回从m+1开始的n条数据# limit n 取前n条数据 mysql> select * from emp limit 2; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # limit m,n 返回从第m+1开始的 n条数据 mysql> select * from emp limit 1,3; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ - order by 排序 默认是
asc升序，desc降序# 默认就是升序，asc可以不写 mysql> select * from emp order by sal asc; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | +-------+-------+------+------+---------------------+-------+---------+--------+ mysql> select * from emp order by sal desc; +-------+-------+------+------+---------------------+-------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+------+------+---------------------+-------+---------+--------+ | 100 | jack | 副总 | NULL | 2002-05-01 00:00:00 | 90000 | NULL | 1 | | 200 | tony | 总监 | 100 | 2015-02-02 00:00:00 | 10000 | 2000.00 | 2 | | 300 | hana | 经理 | 200 | 2017-02-02 00:00:00 | 8000 | 1000.00 | 2 | | 500 | liu | 员工 | 300 | 2019-03-19 00:00:00 | 3500 | 200.58 | 2 | | 400 | leo | 员工 | 300 | 2019-02-22 00:00:00 | 3000 | 200.12 | 2 | +-------+-------+------+------+---------------------+-------+---------+--------+ # 如果是英文字母，按照字母对应的数字排序 # 如果是汉字，就是按照汉字对应的数字排序
8006
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
