MySql教程
数据库之mysql入门笔记一
本文主要是介绍数据库之mysql入门笔记一，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
笔记一
1: 数据库概述
1.1: 什么是数据库
数据库是一个专业的存储和管理数据的仓库
扩展内容:数据库的分类
早期: 层次式数据库,网络型数据库
现在: 关系型数据库,非关系型数据库
现在市面上大部分用的还是关系型数据库
1.2: 什么是关系型数据库
底层以二维表的形式保存数据的库,就是关系型数据库
常见的关系型数据库有哪些?
Oracle: 甲骨文公司提供的一款数据库产品,收费的,之前在Java中的市场份额超过50%。
主要适用于一些大型或者超大型应用系统。
SQL Server: 微软提供的一款数据库产品,收费,主要适用于一些中型或者大型的应用系统
MySQL: 瑞典的一个公司(MySQLAB)提供的一款数据库产品,特点是小巧轻量,简单易用,适用于一些小型或中型的应用系统,如果做mysql集群,也可以用于一些大型或者超大型系统。免费！
mysql被甲骨文收购了！
DB2: IBM公司提供的一款数据库产品, 用于金融/银行等系统较多, 收费!
SQLite: 迷你数据库, 用于嵌入式设备(手机/智能家居等产品)
...
1.3: 数据库相关名字解释
数据库服务器: 其实就是你安装的哪个mysql软件,将mysql安装在计算机上,那么这台计算机就可以作为数据库服务器使用,可以实现数据的存和取。一个数据库服务器中可以包含多个数据库。
比如：装好的mysql服务器中自带了四个数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
数据库: 数据库就是存储数据的仓库，通常情况下一个网站（系统）中的所有数据会存放在一个数据库中
京东网站的所有数据 db_jd
淘宝网站的所有数据 db_taobao
百度网站的所有数据 db_baidu
...
表: 数据库中的数据是安装类型存放的, 一类数据往往存储在一张表中, 一个数据库中可以创建多张表!
京东网站的用户数据 tb_user
京东网站的商品数据 tb_product
京东网站的订单数据 tb_order
...
表记录: 一张表中可以包含多行表记录, 一张表中用于存储一类信息, 一行表记录就用于存储某一个具体的数据
数据库中的表 java中的类(student)
表记录 对象
1.4: SQL语言
SQL语言是一门操作关系型数据库的通用的语言(学会了SQL可以操作所有的关系型数据库)
SQL语言可以操作的有:
1)查看库、创建库、删除库、修改库（了解）
2)创建表、删除表、修改表(扩展内容中)、查看表
3)新增表记录、删除表记录、修改表记录、查询表记录(数据)
4)存储过程/视图/索引等也可以操作
SQL语言是一门通用的操作关系型数据库的语言,但每个数据库厂商为了增强自己数据库的功能,都提供了少量的"方言"(独有的SQL语句),SQL语言通用,但方言不通用!
1.5: 如何连接mysql服务器(cmd窗口)
方式一: mysql -u用户名 -p密码
mysql中默认有一个超级管理员(具有所有权限),用户名就是root
方式二: mysql -u用户名 -p
在下一行键入密码
方式三: mysql -u用户名 -p -h主机名或ip地址 -P端口
-h: 后面跟的是主机名或ip地址,如果不写-h,默认连接localhost(127.0.0.1)
-P: 后面跟的是端口, 如果不写-P,默认端口是3306
退出连接mysql服务器:
exit
quit
或者直接关闭窗口
扩展内容: SQL注释
-- 单行注释(注意--后面的空格不要省去)
#单行注释
/* 多行注释 */
扩展内容:
\c: 取消当前sql语句的执行
----------------------------------
1.数据库及表操作
1.1.创建、删除、查看数据库
----------------------------------
-- 01.查看mysql服务器中所有数据库
show databases;
-- 02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）
-- 语法：USE 库名;
use mysql; -- 进入mysql数据库
use test; -- 进入test数据库
-- 查看已进入的库
select database();
-- 03.查看当前数据库中的所有表
-- 先进入某一个库,再查看当前库中的所有表
use test;
show tables;
-- 04.删除mydb1库
-- 语法：DROP DATABASE 库名;
drop database test; -- 删除test库
drop database mydb1;
-- 删除mydb1库,如果删除的库不存在,会报错!
-- 思考：当删除的库不存在时，如何避免错误产生?
drop database if exists mydb1;
-- 删除mydb1库,如果存在则删除mydb1,如果不存在则不执行删除操作,也不会报错
-- 05.重新创建mydb1库，指定编码为utf8
-- 语法：CREATE DATABASE 库名 CHARSET 编码;
create database mydb1 charset utf8;
-- 需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；
-- 如果不存在则创建mydb1;
create database if not exists mydb1 charset utf8;
-- 如果不存在则创建mydb1,如果已存在,则不执行创建操作,也就不会报错了!
-- 06.查看建库时的语句（并验证数据库库使用的编码）
-- 语法：SHOW CREATE DATABASE 库名;
show create database mydb1;
----------------------------------
1.2.创建、删除、查看表
----------------------------------
-- 07.进入mydb1库，删除stu学生表(如果存在)
-- 语法：DROP TABLE 表名;
use mydb1;
drop table if exists stu;
-- 08.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
...
列名 数据类型
);
创建stu表的SQL语句如下:
create table stu(
id int,
name varchar(50), -- 50表示最多存50个字符
gender varchar(10),
birthday date,
score double
);
-- mysql中也有数据类型,这里先使用,后面再做说明!
-- 09.查看stu学生表结构
-- 语法：desc 表名
desc stu;
----------------------------------
2.新增、更新、删除表记录
----------------------------------
-- 10.往学生表(stu)中插入记录(数据)
select * from stu;
-- 语法：INSERT INTO 表名(列名1,列名2,列名3...) VALUES(值1,值2,值3...);
-- 创建mydb1库时一定要指定编码utf8,这样在库中创建的表也是utf8编码
-- 如果是在cmd中执行插入记录的语句,先 set names gbk; 再插入记录!
-- mysql中推荐使用单引号包裹字符串和日期(有些版本的数据库双引号包裹会报错!)
-- 一个cmd窗口只需要设置一次(set names gbk;)编码
-- 注意: 如果是要给表中的所有列都插入值,列名可以省略不写, 值的个数和顺序必须和表中创建的列的个数和顺序保持一致!
-- value后面只能跟一个括号,只能一次插入一条记录,values后面可以跟多个括号,用逗号分隔,一次可以插入多条数据!
insert into stu(id, name, gender, birthday, score)
value(1, 'tom', 'male', '1985-10-11', 86);
insert into stu values(2, '陈子枢', '男', '1978-10-12', 75);
insert into stu values(3, '程晓宇', '女', '2000-6-12', 80);
-- 11.查询stu表所有学生的信息
-- 语法：SELECT 列名 | * FROM 表名
select * from stu;
-- 12.修改stu表中所有学生的成绩，加10分特长分
-- 修改语法: UPDATE 表名 SET 列=值,列=值,列=值...[WHERE子句];
update stu set score=score+10;
-- mysql不支持复合运算符 score+=10 错!
-- 13.修改stu表中编号为1的学生成绩，将成绩改为83分。
update stu set score=83 where id=1; -- 只修改编号为1的学生的成绩
update stu set id=22, name='刘沛霞', gender='女',
birthday='1985-7-7', score=88 where id=2;
提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。
-- 14.删除stu表中所有的记录
-- 删除记录语法: DELETE FROM 表名 [where子句]
-- 仅删除符合条件的
delete from stu where id<2; -- 仅删除复合条件的记录
delete from stu; -- 没有where默认删除所有记录
----------------------------------
3.查询表记录
3.1.基础查询
-- 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!
----------------------------------
-- 15.查询emp表中的所有员工，显示姓名，薪资，奖金
select * from emp; -- *是通配符,表示查询所有的列
select name,sal,bonus from emp; -- 查询指定的列(只会显示查询的列)
-- 16.查询emp表中的所有部门和职位
select dept, job from emp;
-- 上面的查询结果中有很多重复的记录,如何剔除重复记录,只保留一条?
-- distinct用于去除重复记录,只保留一行!
select distinct dept, job from emp;
3.2.WHERE子句查询
-- 17.查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资
select name,sal from emp where sal>3000;
-- 18.查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资
select name,sal+bonus from emp where sal+bonus > 3500;
-- null和任何数据运算结果都是null,这里应该将null值看做零来处理
-- 方式一: 将所有的奖金为null的列值更新为0
update emp set bonus=0 where bonus is null;
-- 方式二: 在查询时将null值看做0来处理(这种方式对表中的数据不产生任何影响)
-- ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
ifnull(bonus, 0) -- 对bonus列中的null进行处理,将null值用第二个参数0进行替换
select name,sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0) > 3500;
-- 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 "总薪资"
-- 使用`as`可以为表头指定别名(另外as可以省略)
select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;
-- as可以省略
select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;
-- 19.查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资
select name,sal from emp where sal>3000 and sal<4500;
-- and是并的意思(相当于java中的&),要求表中的记录要同时满足and两边的条件才算满足条件!
select name,sal from emp where sal>=3000 and sal<=4500;
-- 也可以使用`between 值1 and 值2` 来完成, 表示判断某个列的值是否在值1和值2之间(包含值1也包含值2)
select name,sal from emp where sal between 3000 and 4500;
-- 20.查询emp表中【薪资为 1400、1600、1800】的员工，显示员工姓名和薪资
select name,sal from emp where sal=1400 or sal=1600 or sal=1800;
-- sal in(1400,1600,1800),意思时只要员工的薪资等于in括号里面的任何一个值就算满足条件
select name,sal from emp where sal in(1400,1600,1800);
-- 21.查询薪资不为1400、1600、1800的员工，显示员工姓名和薪资
select name,sal from emp where !(sal=1400 or sal=1600 or sal=1800);
select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800);
select name,sal from emp where sal not in(1400,1600,1800);
-- 22.(自己完成) 查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。
select name, sal from emp where sal>4000 or sal<2000;
-- 23.(自己完成) 查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。
select name, sal, bonus from emp where sal>3000 and bonus<600;
-- 查询结果有误差
-- 对null值进行处理
select name, sal, bonus from emp
where sal>3000 and ifnull(bonus,0)<600;
-- 24.查询没有部门的员工（即部门列为null值）
select * from emp where dept is null;
-- 判断一个列中的值是不是null不能用 = (等号), 而是用 is 判断
-- 思考：如何查询有部门的员工（即部门列不为null值）
select * from emp where not(dept is null);
select * from emp where dept is not null;
3.3.模糊查询(like)
like可以用于做模糊查询(可以按照指定的模式进行搜索),需要结合%和_使用
select * from emp where name like '王海涛';
select * from emp where name='王海涛';
在通过like进行模糊查询时,如果不配合%以及_使用,和=作用相同
%: 是通配符,可以表示0个或多个任意字符
_: 是通配符,只能表示1个任意字符
-- 25.查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。
select name from emp where name like '刘%';
-- 查询姓名列中以'刘'开头的名字, %表示'刘'的后面可以是0个或者1个或者多个任意字符
-- 26.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select name from emp where name like '%涛%';
-- %涛%,可以匹配三种情况,1)当第一个%匹配0个字符时,表示以'涛'开头 2)第二个%匹配0个字符时,表示以'涛'结尾, 3)当前后两个%至少匹配一个字符时,包含涛在中间的某一个位置(既不在开头,也不再结尾的情况)
-- 27.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。
select name from emp where name like '刘_';
select name from emp where name like '刘__';
-- 刘_,可以匹配姓名中以'刘'开头,并且刘的后面只能有一个字符的情况
-- 刘__,可以匹配姓名中以'刘'开头,并且刘的后面只能有两个字符的情况
3.4.多行函数查询
多行函数也叫做聚合函数(聚集函数), 常见的多行函数有:
(多行函数会默认过滤null值,即不统计null值)
count(列名 | *):
count(列名): 表示统计当前列的值有多少个(不统计null值)
count(*): 以行为单位,统计查询结果中有多少行记录
max(列名): 表示统计当前这一列中所有值中的最大值
min(列名): 表示统计当前这一列中所有值中的最小值
sum(列名): 表示统计当前这一列中所有值的和(也就是说会将这一列中所有的值加在一起返回)
avg(列名): 表示统计当前这一列中所有值的平均值(这一列中所有值的和 / 不是null值的个数 )
-- 28.统计[emp表中薪资大于3000的员工]个数
select count(*) from emp where sal>3000;
select count(*), name from emp where sal>3000;
-- 在进行统计时,不要添加额外的列,因为没有任何意义(这里的name只会将第一行的name显示出来)
-- 29.求emp表中的最高薪资
select max(sal) from emp;
select min(sal) from emp;
select max(sal), name from emp;
-- 在进行统计时,不要添加额外的列,因为没有任何意义(这里的name也是只会将第一行的name显示出来)
-- 这里的最高薪资和姓名没有任何关系, 薪资是所有薪资中的最大值,但name就是结果中的第一行的name
-- 30.统计emp表中所有员工的薪资总和(不包含奖金)
select sum(sal) from emp; -- 所有员工薪资总和
select sum(bonus) from emp; -- 所有员工奖金总和
-- 虽然bonus列中有null值,但是在通过多行函数统计时,遇到null会直接剔除,不会参与统计!
-- 31.统计emp表员工的平均薪资(不包含奖金)
select avg(sal) from emp; -- 所有薪资的平均值
select avg(bonus) from emp; -- 所有奖金的平均值
select sum(bonus) / count(bonus) from emp; -- 所有奖金的平均值
-- 统计emp表员工的平均总薪资(包含奖金)
select avg(sal+bonus) from emp;
-- 错!,因为韩少云的薪资(5000)加上奖金(null),结果是null,会被avg直接过滤
select avg(sal)+avg(bonus) from emp;
-- 错!,avg(sal)由于sal中没有null值,是总薪资/12, 而avg(bonus)由于bonus中有一个null值,是总奖金/11
select avg(sal+ifnull(bonus,0)) from emp;
不分组使用多行函数 和 分了组使用多行函数的区别?
-------------------------------------
select count(*) from emp;
-- 如果没有分组,其实在使用count统计时会默认将整个查询结果当成一个组,这样的话,统计这一个组的人数,返回的就是一个数值;
select count(*) from emp group by dept;
-- 如果分了组(按照部门分了3组), 再使用count统计时会根据每个组来进行统计,有多少个组,就会统计出多少个结果(现有3个组,因此会统计出三个组的人数)
select count(*),dept from emp
-- 如果没有分组, 再通过多行函数进行统计时, 不要显示额外的列, 因为没有任何意义
-- (比如,上面的dept只会将第一行的dept显示出来, 和前面统计的人数没有任何关系)
select count(*),dept from emp group by dept;
-- 如果分了组, 再通过多行函数进行统计时, 可以将进行分组的列和多行函数一起显示
-- 比如:按照dept分了组(分成了3组,此时每组中的dept是相同), 可以将dept这一列的值显示出来, 因为是根据这个列分的组,每组中的这个列的值也是相同的,所以显示这个组中的dept列的任意一个值都是一样的。
-------------------------------------
3.5.分组查询
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
-- 32.对emp表，按照部门对员工进行分组，查看分组后效果。
select id,name,dept from emp group by dept;
-- 如何证明上面查询的结果是三组,而不是三条记录?
-- 可以通过多行函数对分组后的数据进行统计,分成几组,就会统计出几个结果。
select count(*) from emp group by dept;
-- 33.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数
select id,name,job from emp group by job;
+----+--------+------+
| id | name | job |
+----+--------+------+
| 10 | 韩少云 | CEO |
| 4 | 陈子枢 | 总监 |
| 1 | 王海涛 | 讲师 |
+----+--------+------+
-- 显示职位和对应人数
select count(*),job from emp group by job;
+----------+------+
| count(*) | job |
+----------+------+
| 1 | CEO |
| 2 | 总监 |
| 9 | 讲师 |
+----------+------+
-- 34.对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资
select id,name,sal,dept from emp group by dept;
+----+--------+------+--------+
| id | name | sal | dept |
+----+--------+------+--------+
| 10 | 韩少云 | 5000 | NULL |
| 1 | 王海涛 | 1800 | 培优部 |
| 6 | 王克晶 | 3700 | 就业部 |
+----+--------+------+--------+
-- 显示部门名称和最高薪资
select max(sal), dept from emp group by dept;
+----------+--------+
| max(sal) | dept |
+----------+--------+
| 5000 | NULL |
| 4500 | 培优部 |
| 4850 | 就业部 |
+----------+--------+
3.6.排序查询
语法：SELECT 列名 FROM 表名 [where子句] [group by 列] ORDER BY 列名 [ASC|DESC]
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
-- 35.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。
select name, sal from emp order by sal; -- 默认是asc，就是升序
select name, sal from emp order by sal asc; -- 默认是asc，就是升序
-- 36.对emp表中所有员工的奖金进行降序(从高到低)排序，显示员工姓名、奖金。
select name, bonus from emp order by bonus desc; -- 降序必须写desc
-- 按照奖金降序排序,如果奖金相同,再按照薪资降序排序
select name, bonus, sal from emp order by bonus desc, sal desc;
3.7.分页查询
在mysql中，通过limit进行分页查询，查询公式为：
limit (页码-1)*每页显示记录数, 每页显示记录数
-- 37.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据
-- 查询emp表中的记录,每页3条,查询第1页
select * from emp limit 0, 3;
-- 查询emp表中的记录,每页3条,查询第2页
select * from emp limit 3, 3;
-- 查询emp表中的记录,每页3条,查询第3页
select * from emp limit 6, 3;
-- 查询emp表中的记录,每页3条,查询第4页
select * from emp limit 9, 3;
-- 38.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资
-- 根据薪资降序排序(从高到低), 第一条就是薪资最高的记录
select name, sal from emp order by sal desc;
-- 在上面查询的基础上, 将第一条取出来(每页显示1条,查询第1页)
select name, sal from emp order by sal desc limit 0,1;
-- 根据薪资降序排序, 每页显示3条, 查询第一页就是薪资最高的前3名
select name, sal from emp order by sal desc limit 0,3;
3.8.其他函数
curdate() -- 获取当前日期: 年月日
curtime() -- 获取当前时间: 时分秒
sysdate()/now() -- 获取当前日期+时间, 年月日 时分秒
year('2020-8-10'): 返回日期中的年份, 2020
month('2020-8-10'): 返回日期中的月份, 8
day('2020-8-10'): 返回日期中的天数, 10
hour('2020-8-10 12:34:56'): 返回时间中的小时, 12
minute('2020-8-10 12:34:56'): 返回时间中的分钟数, 34
second('2020-8-10 12:34:56'): 返回时间中的秒值, 56
------------------------------------------
concat(s1,s2,...sn): 将 s1、s2、...sn 拼接在一起返回
例如: name('王海涛'),birthday('1995-03-25'),sal(2450)
select concat('王海涛', '1995-03-25', 2450);
concat_ws(x,s1,s2,...sn):
将 s1、s2、...sn 拼接在一起,并且每两个拼接时会通过x作为分隔符进行拼接,再返回
select concat_ws(',' ,'王海涛', '1995-03-25', 2450);
-- 39.查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。
select name, birthday from emp where birthday between 1993 and 1995;
-- 错误,由于birthday是日期类型(年月日格式),而1993和1995都是数值,没法比较
-- 方式一: 将1993和1995两个数值转成日期格式,再和birthday进行比较!
select name, birthday from emp
where birthday between '1993-1-1' and '1995-12-31';
-- 方式二: 将birthday中的年份用year函数提取出来,再和1993以及1995进行比较
select name, birthday from emp
where year(birthday) between 1993 and 1995;
-- 40.查询emp表中本月过生日的所有员工
-- 首先通过month函数从当前日期中获取本月是几月: month( now() )
-- 再通过month函数从员工的birthday中获取出生月份: month( birthday )
select * from emp where month( now() ) = month( birthday );
-- 41.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）
select name, concat(sal, '(元)') from emp;
-- 补充练习：查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）
select name, concat(sal, '/元') from emp;
----------------------------------
补充内容1: mysql的数据类型
1.1:数值类型
mysql中提供了多种数值类型,其中包括:
tinyint、smallint、int、bigint、float、double、decimal等
其中较为常用的就是 int、double
1.2.字符串类型
char类型: 定长字符串, char(n), n的范围是: 0~255个字符
char类型之所以叫做定长字符串,是因为一旦确定了n的最大字符数,不管存的数据是多少,该数据占用的空间就是n个字符。例如：name char(10), 存入'张三丰', 存入了3个字符,剩余的空间会用空格补全.
因此char类型可能会浪费空间!
所以char类型适合存储长度固定的数据, 比如:
student_id char(11), 用这个列存储所有学生的编号.
idcard char(18), 用这个列存储所有人的身份证号.
char类型相比varchar类型速度要快一些,因为char类型只需要判断一个数据是否能存入该列中,而不需要将剩余的空间留给别的数据使用!
varchar类型: 变长字符串, varchar(n), n的范围是: 0~?个字符
varchar类型之所以叫变长字符串,是因为n只是限制该列中最多能存的字符数, 如果你实际存的数据量小于n,剩余的空间还可以留给别的数据使用。例如：name char(10), 存入'张三丰', 存入了3个字符,剩余的7个空间会留给别的数据使用!
因此varchar类型不会浪费空间!
所以varchar类型适合存储长度不固定的数据(长度固定的数据我们会使用char类型)
varchar类型最大能存的数据量是 0~65535个字节
latin1编码中,1个字符对应1个字节, n的最大值约是 65535/1 (max = 65532)
测试表: create table t2(id int, name varchar(60000)) charset latin1;
gbk编码中,1个字符对应2个字节, n的最大值约是 65535/2 (max = 32766)
测试表: create table t3(id int, name varchar(40000)) charset gbk;
utf8编码中,1个字符对应3个字节, n的最大值约是 65535/3 (max = 21844)
测试表: create table t4(id int, name varchar(20000)) charset utf8;
create table t5(id int, name varchar(5));
面试题: char和varchar有什么区别?
1) char和varchar存的数量是不同的, char类型最多能存255个字符, varchar类型最多能存65535个字节
2) char类型如果存的数据量小于最大长度, 剩余的空间会使用空格填充, 因此可能会浪费空间
所以char类型适合存储长度固定的数据, 这样既不会浪费空间, 效率还比varchar略高
3) varchar类型如果存的数据量小于最大长度, 剩余的空间会留给别的数据使用
所以varchar类型适合存储长度不固定的数据, 这样虽然没有char存储效率高, 但至少不会浪费空间。
1.3: 日期类型
date: 日期类型, 格式是: 年月日
time: 时间类型, 格式是: 时分秒
datetime: 日期+时间,格式是: 年月日 时分秒
timestamp: 时间戳, 格式和datetime相同, 也是: 年月日 时分秒, 和datetime不同的是:
1) 范围上: datetime范围是: 1000~9999(年份)
timestamp范围是: 1970到2038年
2) 实际存的数据: datetime实际存的就是一个`年月日 时分秒`格式的日期+时间
而timestamp实际存储的是这个从1970年1月1日到这个日期+时间的时间毫秒值
create_time timestamp, 2018-2-3 14:45:56, 实际存储的是 1970年1月1日到2018-2-3 14:45:56时间的时间毫秒值
3) 在使用上: timestamp可以设置自动获取当前时间作为值插入到表中, 而datetime不可以.
2018-2-3 14:45:56
补充内容2: mysql的字段约束
----------------------------------
use mydb1;
drop table if exists stu;
create table stu(
id int primary key, -- id是主键,值不能为空且不能重复
name varchar(50), -- 50表示最多存50个字符
gender varchar(10),
birthday date,
score double
);
insert into stu value('a', '张飞', '男', '1980-1-2', 80);
insert into stu value('b', '刘备', '男', '1981-2-2', 90);
insert into stu value('c', '关羽', '男', '1982-3-2', 85);
----------------------------------
2.1: 主键约束
如果一个列添加了主键约束, 那么这个列的值就必须是非空的且不能重复
主键通常用于唯一的表示一行表记录(就像人的身份证号一样)
一张表中通常都会有且只有一个主键
添加主键约束的格式:
create table stu(
id int primary key,
...
);
----------------------------------
如果id是主键并且是数值类型,为了方便维护,可以设置主键自增策略,设置方法:
create table stu(
id int primary key auto_increment,
...
);
在设置完主键自增之后,表中会维护一个AUTO_INCREMENT的值,这个值从1开始,如果插入主键时没有给主键赋值,就会从AUTO_INCREMENT这里获取一个值再作为主键插入到表中。再用完之后，会自动将AUTO_INCREMENT的值加1
2.3: 非空约束
如果一个列添加了非空约束, 那么这个列的值就不能为空(null), 但可以重复
添加非空约束的格式:
create table stu(
...
gender varchar(10) not null,
...
);
2.4: 唯一约束
如果一个列添加了唯一约束, 那么这个列的值就不能重复, 但可以为空(null)
比如: 网站绑定的邮箱, 前期可以不绑定, 即可以为null, 但一旦绑定, 这个邮箱是不能和其他账号的邮箱重复的。
添加唯一约束的格式:
create table stu(
...
email varchar(50) unique,
...
);
create table stu(
...
username varchar(50) unique not null, -- 用户名既不能重复,也不能为空
...
);
----------------------------------
主键约束 和 (非空+唯一约束) 有什么区别:
1) 主键约束 和 非空+唯一 特点是相同的, 都是不能为空且不能重复
2) 主键约束除了非空且不能重复之外, 还可以表示唯一一行表记录, 即作为表记录的唯一标识。
补充内容3: mysql的外键约束
外键约束不同于主键、非空、唯一约束，外键约束是用于表示两张表的对应关系
1、如何保存部门(dept) 和 员工(emp)的对应关系?
可以在员工表中添加一个列(比如: dept_id)用于保存部门的编号, 就可以保存员工和部门的对应关系（可以将dept_id设置为外键, 当然也可以不这样做）
2、添加外键和不添加外键有什么区别？
1）如果不添加外键：对于数据库来说，dept_id这个列就是一个普通的列，数据库也不会知道 dept 和 emp两张表存在任何关系，自然也不会帮我们去维护这层关系。
假如，现在要删除dept表中的某一个部门（4号部门），删除后就会造成emp表中的赵六和刘能找不到部门，而赵六和刘能后面对应的dept_id值为4, 这个数据也变成了冗余数据。
这样会破坏数据库中数据的完整性和一致性！
2）如果将dept_id添加为外键：将dept_id添加为外键就等同于：通知数据库 dept 和 emp 两张表存在对应关系, 而且emp表中的dept_id列要参考dept表中的id列。
这样数据库就会帮我们维护这两张表的对应关系，如果此时删除dept表中的某一个部门（4号部门），数据库会首先检查这个4号部门在emp表中还有没有对应的员工，如果有，数据库就会阻止我们删除！这样就保证了数据的完整性和一致性。
如果非要删除，可以将4号部门里面的员工记录转移到其他部门或者删除，只要保证所删除部门中没有对应的员工即可！
补充内容4: 表关系
1对多(多对1): 在这种关系中,往往会在多的一方添加列,保存一的一方的主键(可以设置外键,当然也可以不设置,看需求)，比如：
部门表(1) 员工表(*), 在员工表(*)中添加列(dept_id)保存部门的编号
班级表(1) 学生表(*), 在学生表(*)中添加列(class_id)保存班级的编号
1对1: 在这种关系中,可以在任意一方添加列保存另一方的主键(可以设置外键,当然也可以不设置,看需求), 比如:
班级表(1) 教室表(1), 在班级表(1)添加列(room_id)来保存教室的编号
班级表(1) 教室表(1), 在教室表(1)添加列(class_id)来保存班级的编号
多对多: 在这种关系中,在任何一方添加列保存另一方的主键都不合适
此时可以再创建一张表,在这种表中分别添加两个列(stuid,teaid), 分别用于保存学生表的主键和教师表的主键，以此来保存学生和教师的对应关系！
----------------------------------
4.多表查询
4.1.连接查询
-- 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!
----------------------------------
-- 42.查询部门和部门对应的员工信息
select * from dept,emp;
以上查询有一个名字叫做笛卡尔积查询
笛卡尔积查询: 其实就是同时查询两张表,其中一张表有m条记录,另外一张表有n条记录,查询的结果是m*n条，但这种查询结果中包含大量错误的数据，所以我们一般不会直接使用这种查询。
在笛卡尔积查询的基础上，通过where子句将错误的数据剔除，只保留正确的数据，这就是连接查询。
select * from dept,emp where emp.dept_id=dept.id;
内连接查询:
select * from dept inner join emp on emp.dept_id=dept.id;
----------------------------------
4.2.连接查询
----------------------------------
-- 43.查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null
如果两张表在连接查询时,要求查询出其中一张表的所有数据,此时可以使用左外连接查询或者右外连接查询。
select * from dept left join emp on emp.dept_id=dept.id;
-- 如果要查询部门表(dept)中的所有数据,而部门表(dept)在左边,此时可以使用左外连接查询,就可以查询出所有的部门信息(而员工信息只显示和部门对应的)
【左外连接查询】：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。
-- 44.查询【所有员工】及员工所属部门，如果某个员工没有所属部门，部门显示为null
select * from dept right join emp on emp.dept_id=dept.id;
【右外连接查询】：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。
-- 查询所有部门以及所有员工, 如果部门没有对应员工,可以对应null,如果员工没有对应部门,也可以对应null，这种情况应使用全外连接查询。
-- 但，mysql不支持全外连接查询！但可以通过union来模拟这种查询！
union关键字是用于将两个查询结果上下合并在一起显示，并且会去除重复记录。
union all关键字是用于将两个查询结果上下合并在一起显示，不会去除重复记录
能够使用union和union all合并结果的查询语句,必须符合:
1)两条SQL语句查询的结果列数必须相同
2)两条SQL语句查询的结果列名必须相同(低版本mysql要求)
select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on emp.dept_id=dept.id;
select * from dept left join emp on emp.dept_id=dept.id
union all
select * from dept right join emp on emp.dept_id=dept.id;
----------------------------------
4.3.子查询练习
-- 准备数据：以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!
----------------------------------
-- 45.列出薪资比'王海涛'的薪资高的所有员工，显示姓名、薪资
-- 求出'王海涛'的薪资是多少
select sal from emp where name='王海涛';#2450
-- 求出薪资比'王海涛'的薪资还高的所有员工
select name, sal from emp where sal>(select sal from emp where name='王海涛');
-- 将一个SQL语句的执行结果作为另外一条SQL语句的条件来执行, 这就是子查询!
-- 46.列出与'刘沛霞'从事相同职位的所有员工，显示姓名、职位
-- 求出'刘沛霞'从事的职位
select job from emp where name='刘沛霞'; -- 推销员
-- 求出和'刘沛霞'具有相同职位的员工
select name, job from emp where job=(select job from emp where name='刘沛霞');
----------------------------------
4.4.多表查询练习
----------------------------------
-- 47.列出在'培优部'任职的员工，假定不知道'培优部'的部门编号，显示部门名称，员工名称。
/*
查询的列:select dept.name, emp.name
查询的表:from dept, emp
筛选条件:where emp.dept_id=dept.id
and dept.name='培优部'
*/
SELECT dept.name, emp.name
FROM dept, emp
WHERE emp.dept_id=dept.id AND dept.name='培优部';
----------------------------------
SELECT dept.name, emp.name
FROM dept INNER JOIN emp
ON emp.dept_id=dept.id
WHERE dept.name='培优部';
-- 48.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
/* emp e1(员工表) emp e2(上级表)
查询的列: SELECT e1.name, e1.topid, e2.name
查询的表: FROM emp e1, emp e2
筛选条件: WHERE e1.topid=e2.id
*/
SELECT e1.name, e1.topid, e2.name
FROM emp e1, emp e2
WHERE e1.topid=e2.id;
-- 49.列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资
/*
列出职位:
1)求出各种职位的最低薪资
先按照职位进行分组,职位相同为一组,再用min(sal)求每组中的最低薪资,也就是每种职位的最低薪资
select job, min(sal) from emp group by job;
2)求出有哪些职位的最低薪资是大于1500的
select job, min(sal) from emp group by job where min(sal)>1500;-- 错误写法!
1)where应在放在from子句后, group by子句前
2)where中不能使用多行函数(列别名也不能用在where中)
3)where是在分组之前之前,先过滤掉一些记录,再基于剩余的记录进行分组,
而本地是先分组,再过滤,所以不能使用where,应该用having
*/
select job, min(sal) from emp group by job having min(sal)>1500;
----------------------------------
where和having的区别?
1)where和having都是用于对表中的记录进行筛选过滤
2)where用于在分组之前对记录进行筛选过滤,而having用于对分组之后的记录进行筛选过滤
3)where子句中不能使用多行函数 和 列别名,但可以使用表别名!
select name as 姓名, sal as 薪资 from emp e;
-- 其中上面的'姓名','薪资'都是列别名, e是表别名
4)having子句中可以使用多行函数 和 列别名 以及 表别名!
----------------------------------
-- 50.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
select dept_id, count(*), avg(sal) from emp group by dept_id;
select dept_id 部门编号, count(*) 员工数量, avg(sal) 平均薪资
from emp group by dept_id;
-- 51.列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、部门名称、上级编号、上级姓名。
/* emp e1(员工表), emp e2(上级表)
查询的列:SELECT e1.id, e1.name, d.name, e2.id, e2.name
查询的表:FROM emp e1, emp e2, dept d
连接条件:WHERE e1.topid=e2.id
AND e1.dept_id=d.id
筛选条件: AND e1.hdate < e2.hdate
*/
SELECT e1.id, e1.name, d.name, e2.id, e2.name
FROM emp e1, emp e2, dept d
WHERE e1.topid=e2.id
AND e1.dept_id=d.id
AND e1.hdate < e2.hdate;
----------------------------------
补充内容5: set names gbk;
set names gbk;(这个命令是用在cmd窗口中)用来通知数据库服务器, 当前cmd窗口发送给服务器的数据的GBK的,那么服务器就会按照GBK编码来接收 cmd窗口发送过来的数据, 再将GBK的数据转换成utf8编码的数据存入数据库中!
这个命令只能用在cmd窗口! 而且是每次新开一个cmd窗口都需要重新设置一次!
像Navicat/SQLYog等工具不需要设置该命令,因为这些工具底层已经设置过编码了!
补充内容6: 数据库的备份和恢复
6.1.备份数据库
1、备份单个数据库
在cmd窗口（未登录、未连接到mysql服务器的界面）中，可以通过如下命令对指定的数据库进行备份：
mysqldump -u用户名 -p密码 库名 > 备份文件的位置
示例1: 对db40库中的数据(表,表记录)进行备份,备份到 d:/db40.sql 文件中
mysqldump -uroot -proot db40 > d:/db40.sql
注意: 1)如果输入命令回车之后没有提示错误,就说明备份成功了!
2)备份单个数据库,其实只会备份这个库中的表和表记录,并不会备份库本身!
2、备份多个数据库（比如备份两个数据库）
在cmd窗口（未登录、未连接到mysql服务器的界面）中
mysqldump -u用户名 -p密码 --databases 库名1 库名2 ... > 备份文件的位置
示例2: 对db20 和 db40 库中的数据进行备份,备份到 d:/db2040.sql 文件中
mysqldump -uroot -proot --databases db20 db40 > d:/db2040.sql
注意: 1)如果输入命令回车之后没有提示错误,就说明备份成功了!
2)备份多个数据库,不仅会备份这个库中的表和表记录,同时会备份库本身!
或者如果想备份mysql服务器中的所有的库以及库中的表和表记录,可以通过如下命令:
mysqldump -uroot -proot -A > d:/dball.sql
输入完后回车如果没有提示错误(error是错误,警告不是错误可以忽略),即表示备份成功!
6.2.恢复数据库
1、恢复数据库方式一(单个数据库)：
在cmd窗口中（未登录的状态下），可以通过如下命令对指定的数据库进行恢复
mysql -u用户名 -p密码 库名 < 备份文件的位置
示例1: 将 d:/db40.sql 文件中的数据恢复到 db60 库中
1) 先在cmd窗口中(已登录的状态下), 先创建db60库
create database db60 charset utf8;
2) 再回到cmd窗口中(未登录的状态下), 执行下面恢复的命令
mysql -uroot -proot db60 < d:/db40.sql
2、恢复数据库方式二(多个数据库)
在cmd窗口中(已登录的状态下)，可以通过source命令来执行指定位置的sql文件中的sql语句：
source sql文件的位置
示例2: 将 d:/db40.sql 文件中的数据恢复到 db80 库中
1) 先创建db80库, 并进入到db80库
create database db80 charset utf8;
use db80;
2) 再通过source命令执行 d:/db40.sql 文件中的sql语句
source d:/db40.sql
示例3: 将 d:/db2040.sql 文件中的数据恢复回来
1) 将db20,db40库删除(模拟数据丢失)
drop database db20;
drop database db40;
2) 再通过source命令执行 d:/db2040.sql 文件中的sql语句
source d:/db2040.sql
补充内容7: navicat软件的使用
1)下发的navicat软件是绿色解压版,解压之后进入到目录,找到navicat.exe双击即可启动程序
2)点击左上角的连接图标,输入连接名(就是个自定义的名字),输入主机或ip地址,以及端口,再输入用户名和密码,点击左下角的连接测试,如果连接成功,点击确定即可!
3)使用navicat进行如下操作:
数据库操作: 创建库, 修改库, 删除库
表操作: 创建表, 修改表, 删除表
表记录操作: 新增表记录, 修改表记录, 删除表记录, 查询表记录
在navict中执行单条sql语句,或者执行多条/所有sql语句
这篇关于数据库之mysql入门笔记一的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
部署MySQL集群入门：新手必读指南11-20
-
部署MySQL集群教程：初学者指南11-20
-
部署MySQL集群项目实战：新手教程11-20
-
部署MySQL集群资料：新手入门教程11-20
-
MySQL集群部署教程：入门级详解11-20
-
MySQL集群教程：入门与实践指南11-20
-
部署MySQL集群教程：新手入门指南11-20
-
MySQL读写分离教程：轻松入门11-20
-
部署MySQL集群入门：一步一步搭建你的数据库集群11-20
-
部署MySQL集群学习：入门教程11-19
-
如何部署MySQL集群：新手入门教程11-19
-
Mysql安装教程：新手必看的详细安装指南11-19
-
Mysql安装入门：新手必读指南11-18
-
MySQL事务MVCC原理入门详解11-18
-
MySQL资料：新手入门教程11-16
栏目导航
