MySQL -- 基础
- 数据库
- 1. 概念
- 2. 分类
- MySQL数据库
- 3. SQL语言
- 4. 常用命令
- 5. 约束
- 6. 基础函数
- 7. 条件查询
数据库
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等
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 |
+-------+-------+------+------+---------------------+-------+---------+--------+
# 如果是英文字母，按照字母对应的数字排序
# 如果是汉字，就是按照汉字对应的数字排序
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
