MySQL 基础
文章目录
1.数据库概述
数据库(database): 以特定的结构批量存储业务数据的软件。
关系型数据库逻辑结构(RDBMS)
Relation Database Manage System[RDBMS]
2.MySQL 数据库
- Oracle分支:MySQL
- Martin分支:MariaDB
xampp 是一个服务器的套装,包含多款开源的服务器,例如MySQL,PHP… |
---|
下载地址 https://www.apachefriends.org/download.html |
MySQL部署结构
- 服务器端软件:负责存储/维护数据 —— 银行总行机房
- 客户端软件:负责向服务器发起增删改查的命令 —— ATM机
(1)服务器端:
C:\xampp\mysql\bin\mysqld.exe 启动文件
C:\xampp\mysql\data 数据文件——人无法看懂
(2)客户端:
C:\xampp\mysql\bin\mysql.exe 客户端软件
用来连接服务器端
(3)连接MySQL服务器
mysql.exe -h127.0.0.1 -P3306 -uroot -p
简写形式
mysql -uroot
3.常用MySQL管理命令
quit; 退出服务器的连接
show databases; 显示服务器上当前所有的数据库
use 数据库名; 进入指定的数据库
show tables; 显示当前数据库中所有的数据表
desc 表名称; 描述表中有哪些列
4.MySQL常用的SQL命令
SQL:Structured Query Language,结构化查询语言,用于对关系型数据库服务器中的数据进行增删改查的操作。
SQL语言最早是由IBM提出,后提交给了ISO,最终成为了数据库行业的标准语言,分为SQL-87、SQL-92、SQL-99等。当前标准的SQL命令可以被绝大多数的关系型数据库所支持。
SQL语法规范
(1)每条SQL语句必须以英文的分号结尾,一条语句可以跨越多行,见到分号认为语句结束。
(2)若第n行语句有错误,则此语句以及后续的语句都不能再执行。
(3)SQL命令不区分大小写,习惯上数据库关键字用大写,非关键字用小写。
(4)SQL命令还可以使用单行注释:#...,和多行注释:/*....*/,注释的代码不会被服务器执行。
日常开发中常用的SQL命令
(1)丢弃指定的数据库,如果存在的话
DROP DATABASE IF EXISTS jd;
(2)创建新的数据库
CREATE DATABASE jd;
(3)进入刚刚创建的数据库
USE jd;
(4)创建保存数据的表
CREATE TABLE student(
sid INT,
name VARCHAR(8),
sex VARCHAR(1),
score INT
);
(5)向数据表中插入数据
INSERT INTO student VALUES('1','tom','M','80');
(6)查询表中所有数据
SELECT * FROM student;
(7)修改数据
UPDATE student SET score='66',sex='F' WHERE sid='2';
(8)删除数据
DELETE FROM student WHERE sid='3';
5.标准SQL语句分类
5.1 DDL: Data Define Language 定义数据结构
CREATE/DROP/ALTER
5.2 DML: Data Munipulate Language 操作数据
INSERT/DELETE/UPDATE
5.3 DQL:Data Query Language 查询数据
SELECT
5.4 DCL:Data Control Language 控制用户权限
GRANT(授权)/REVOKE(收权)
6.计算机存储字符
(1)如何存储英文字符
- ASCII: 总共有128个,对所有的英文字符及标点符号进行了编码。 Hello -> 72102108108111
- Latin-1: 总共有256个,兼容ASCII码,同时对欧洲符号进行了编码。MySQL默认就使用这种编码。
(2)如何存储中文字符
- GB2312: 对常用的6千多汉字进行了编码,兼容ASCII编码
- GBK: 对2万多个汉字进行了编码,兼容GB2312
- BIG5: 台湾繁体字编码,兼容ASCII
- Unicode: 对世界上主流的语言常用的字符进行了编码,兼容ASCII,不兼容GBK,GB2312,BIG5等系列。具体分为UTF-8、UTF-16、UTF-32等存储方案。
(3)解决MySQL存储中文乱码
- sql脚本文件存储的编码utf8
- 客户端连接服务器端使用的编码为utf8
- 服务器端创建数据库存储使用的编码为utf8
7.MySQL中的列类型
创建数据表的时候,指定的列可以存储的数据类型。
CREATE TABLE book( bid 列类型);
(1)数值类型——引号可加可不加
- TINYINT 微整型,占1个字节 范围-128~127
- SMALLINT 小整型,占2个字节 范围-32768~32767
- INT 整型,占4个字节
- 范围 -2147483648~2147483647
- BIGINT 大整型,占8个字节
- FLOAT 单精度浮点型,占4个字节,范围3.4e38,范围比INT大的多,可能产生计算误差。
- DOUBLE 双精度浮点型,占8个字节,范围比BIGINT大的多
- DECIMAL(M,D) 定点小数,不会产生计算误差,M代表总的有效位数,D代表小数点后的有效位数
- BOOL 布尔型,只有两个结果TRUE/1、FALSE/0,TRUE和FALSE不能添加引号;真正存储数据的时候,会使用TINYINT。
(2)日期时间类型——必须添加引号
- DATE 日期型 ‘2018-12-31’
- TIME 时间型 ‘14:22:30’
- DATETIME 日期时间型 ‘2018-12-31 14:22:30’
(3)字符串类型——必须添加引号
- VARCHAR(M) 变长字符串,不会产生空间浪费,操作速度相对较慢,M最大值是65535
- CHAR(M) 定长字符串,可能产生空间浪费,操作速度较快,M最大值是255;用于存储手机号码,身份证号等固定长度的字符。
- TEXT(M) 大型变长字符串,最多存2G
8.列约束
MySQL可以对插入的数据进行特定的验证,只有满足条件才允许插入到数据表中,否则被认为是非法的插入。
例如:一个人的性别只能是男或者女,一个人的年龄0~100之间。
(1)主键约束——PRIMARY KEY
声明了主键列上的值不能出现重复,表中查询的记录会按照主键由小到大排序——加快查找的速度;通常主键添 加到编号列中。
注意:一个表中只能有一个主键
(2)唯一约束——UNIQUE
声明了唯一约束的列上不能插入重复的值,允许插入NULL,而且允许插入多个NULL
NULL表示空,在插入数据时,无法确定要保存的数据。例如:无法确定员工的姓名、工资。
(3)非空约束——NOT NULL
声明了非空约束的列上不能插入NULL
(4)默认值约束——DEFAULT
可以使用DEFAULT关键字声明默认值,有两种方式可以应用默认值
INSERT INTO xz_laptop_family VALUES(40,'苹果',DEFAULT);
INSERT INTO xz_laptop_family(fid,fname) VALUES(50,'华硕');
(5)检查约束——CHECK
检查约束可以对插入的数据进行检验
CREATE TABLE student(
age TINYINT CHECK(age>=18 AND age<=60 )
);
注: MySQL不支持检查约束,会降低数据的插入速度。
(6)外键约束——FOREIGN KEY
声明了外键约束的列,取值必须在另一个表的主键列上出现过,列类型要保持一致,取值可以是NULL
FOREIGN KEY(familyId) REFERENCES xz_laptop_family(fid)
9.MySQL中的自增列
AUTO_INCREMENT: 自动增长,假入一个列声明了自增列,无需手动赋值,直接设置为NULL,会获取当前的最大值,然后加1插入。
注意:
自增列允许手动赋值
只适用于整数型的主键列上
10.简单查询
(1)查询特定的列
示例:查询所有员工的姓名、工资、生日
SELECT ename,salary,birthday FROM emp;
练习:查询所有员工的编号,姓名,性别,所属部门编号
SELECT eid,ename,sex,deptId FROM emp;
(2)查询所有的列
SELECT * FROM emp;
SELECT eid,ename,sex,birthday,salary,deptId FROM emp;
(3)给列起别名
示例:查询所有员工的姓名和工资,使用汉字的别名显示
SELECT ename AS 姓名,salary AS 工资 FROM emp;
练习:查询所有员工的编号,姓名,性别,生日,用中文别名
SELECT eid AS 编号,ename AS 姓名,sex AS 性别,birthday AS 生日 FROM emp;
练习:查询所有员工的编号,姓名,使用1个英文字母别名
SELECT eid a,ename b FROM emp;
(4)只显示不同的记录/合并相同的记录
示例:查询出员工都在哪些部门
SELECT DISTINCT deptId FROM emp;
练习:查询出都有哪些性别的员工
SELECT DISTINCT sex FROM emp;
(5)在查询时执行计算
示例:计算2*33/47-8+10
SELECT 2*33/47-8+10;
练习:查询出所有员工的姓名及其年薪
SELECT ename,salary*12 FROM emp;
练习:假设每个员工工资增加500元,年终奖5000元,查询所有员工的姓名及其年薪,要给列起别名
SELECT ename AS 姓名,(salary+500)*12+5000 AS 年薪 FROM emp;
(6)查询结果集的排序
示例:查询所有的部门,结果按照部门编号升序排序
SELECT * FROM dept ORDER BY did ASC; #ascendant
示例:查询所有的部门,结果按照部门编号降序排序
SELECT * FROM dept ORDER BY did DESC; #descendant
练习:查询所有员工信息,结果按照工资由大到小排序
SELECT * FROM emp ORDER BY salary DESC;
练习:查询所有员工,结果按照年龄由大到小排序
SELECT * FROM emp ORDER BY birthday ASC;
练习:查询所有员工,结果按照年龄由小到大排序
SELECT * FROM emp ORDER BY birthday DESC;
练习:查询所有员工,结果按照姓名的升序排序
SELECT * FROM emp ORDER BY ename;
练习:查询所有员工,结果按照工资降序排序,如果工资相同,按照姓名排序。
SELECT * FROM emp ORDER BY salary DESC,ename;
练习:查询所有员工,结果按照性别排序,如果性别相同,按照工资的升序排序。
SELECT * FROM emp ORDER BY sex,salary;
ORDER BY 可以按照**数值、日期/时间、字符串**来排序 默认按照ASC升序排序
(7)条件查询
示例:查询出编号为5的员工所有信息
SELECT * FROM emp WHERE eid=5;
练习:查询出姓名为king的员工的编号,工资,生日。
SELECT eid,salary,birthday FROM emp WHERE ename='king';
练习:查询出20号部门下所有员工信息
SELECT * FROM emp WHERE deptId=20;
练习:查询出男员工的所有信息
SELECT * FROM emp WHERE sex=1;
练习:查询出工资大于等于5000的员工所有信息
SELECT * FROM emp WHERE salary>=5000;
>= <= > < = !=(不等于)
练习:查询出1991-1-1后出生的员工所有信息
SELECT * FROM emp WHERE birthday>'1991-1-1';
练习:查询出不在10号部门的员工所有信息
SELECT * FROM emp WHERE deptId!=10;
练习:查询出没有明确部门的员工所有信息
SELECT * FROM emp WHERE deptId IS NULL;
练习:查询出有明确部门的员工所有信息
SELECT * FROM emp WHERE deptId IS NOT NULL;
练习:查询出工资大于6000的女员工所有信息
SELECT * FROM emp WHERE salary>6000 AND sex=0;
练习:查询出工资在5000~7000之间的员工所有信息
SELECT * FROM emp WHERE salary>=5000 AND salary<=7000;
SELECT * FROM emp WHERE salary **BETWEEN** 5000 **AND** 7000;
练习:查询出工资为5000以下,7000以上的员工所有信息。
SELECT * FROM emp WHERE salary<5000 OR salary>7000;
SELECT * FROM emp WHERE salary NOT BETWEEN 5000 AND 7000;
练习:查询出1990之前和1993以后出生的员工所有信息
SELECT * FROM emp WHERE birthday<'1990-1-1' OR birthday>'1993-12-31';
练习:查询出1993年出生的员工所有信息
SELECT * FROM emp WHERE birthday>='1993-1-1' AND birthday<='1993-12-31';
练习:查询出10号部门和30号部门员工所有信息
SELECT * FROM emp WHERE deptId=10 OR deptId=30;
SELECT * FROM emp WHERE deptId IN(10,30);
练习:查询出不在10号和30号部门员工所有信息
SELECT * FROM emp WHERE deptId NOT IN(10,30);
IS NULL / IS NOT NULL AND / OR BETWEEN...AND.../NOT BETWEEN...AND... IN() / NOT IN()
注意:删除、更改、查询都可以结合条件查询。
(8)模糊条件条件
示例:查询出姓名含有字母e的员工所有信息
SELECT * FROM emp WHERE ename LIKE '%e%';
练习:查询出姓名中以e结尾的员工所有信息
SELECT * FROM emp WHERE ename LIKE '%e';
练习:查询出姓名中倒数第2个字符为e的员工所有信息
SELECT * FROM emp WHERE ename LIKE '%e_';
(9)分页查询
假如查询的结果集中有太多的数据,一次显示不完,可以分页显示。
需要有两个条件:当前的页码、每页的数据量
SELECT * FROM emp LIMIT start,count;
start: 是一个数字,从结果集中的哪1条开始读取,第1条称为0.
count: 是一个数字,最多读取的行数
假设每一页显示5条记录
第1页:SELECT * FROM emp LIMIT 0,5;
第2页:SELECT * FROM emp LIMIT 5,5;
第3页:SELECT * FROM emp LIMIT 10,5;
第4页:SELECT * FROM emp LIMIT 15,5;
假设每一页显示6条记录
第1页:SELECT * FROM emp LIMIT 0,6;
第2页:SELECT * FROM emp LIMIT 6,6;
11.复杂查询——聚合查询/分组查询
示例:查询出所有员工的数量
SELECT COUNT(eid) FROM emp; #14
练习:使用员工的姓名获取员工数量
SELECT COUNT(ename) FROM emp;#14
练习:使用员工部门编号获取员工数量
SELECT COUNT(deptId) FROM emp;#14
SELECT COUNT(*) FROM emp;#15 推荐写法
函数:功能体,接收若干个数据,返回特定的结果 —饺子机 |
---|
聚合函数****COUNT()/SUM()/AVG()/MAX()/MIN() |
练习:查询出所有员工工资的总和
SELECT SUM(salary) FROM emp;
练习:查询出所有男员工的平均工资
SELECT SUM(salary)/COUNT(*) FROM emp WHERE sex=1;
SELECT AVG(salary) FROM emp WHERE sex=1;
练习:查询出工资最高的员工的工资
SELECT MAX(salary) FROM emp;
练习:查询出工资最低的员工的工资
SELECT MIN(salary) FROM emp;
练习:查询年龄最大的员工的生日
SELECT MIN(birthday) FROM emp;
示例:查询出每个部门员工的最高工资
SELECT deptId,MAX(salary) FROM emp GROUP BY deptId;
练习:查询出男女员工的平均工资,最高工资,最低工资
示例:查询1991年出生的员工
SELECT * FROM emp WHERE YEAR(birthday)='1991';
示例:查询3月份出生的员工
SELECT * FROM emp WHERE MONTH(birthday)='3';
12.复杂查询——子查询
本质上就是一个SQL语句的查询结果作为另一个SQL语句的查询条件
示例:查询出研发部员工的所有信息
步骤1:查询研发部的部门编号
SELECT did FROM dept WHERE dname='研发部';#10
步骤2:使用部门编号查询员工——10
SELECT * FROM emp WHERE deptId=10;
综合:
SELECT * FROM emp WHERE deptId=(
SELECT did FROM dept WHERE dname='研发部'
);
练习:查询比tom工资高的员工所有信息
步骤1:查询tom的工资
SELECT salary FROM emp WHERE ename=‘tom’; #6000
步骤2:查询工资比6000高的员工
SELECT * FROM emp WHERE salary>6000;
综合:
SELECT * FROM emp WHERE salary>(
SELECT salary FROM emp WHERE ename='tom'
);
练习:查询出和tom同一年出生的员工所有信息
步骤1:查询tom的生日的年份 ——1990
SELECT YEAR(birthday) FROM emp WHERE ename=‘tom’;
步骤2:查询出1990年出生的员工
SELECT * FROM emp WHERE YEAR(birthday)=1990;
综合:
SELECT * FROM emp WHERE YEAR(birthday)=(
SELECT YEAR(birthday) FROM emp WHERE ename='tom'
);
13.复杂查询——多表查询
示例:查询出所有员工的姓名及其所在部门名称
SELECT ename,dname FROM emp,dept;
笛卡尔积!
如何避免笛卡尔积,为多表查询添加条件。
SELECT ename,dname FROM emp,dept WHERE deptId=did;
(1)内连接 INNER J****OIN … ON ——和之前的结果一样
SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;
(2)左外连接 LEFT OUTER JOIN…ON
SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;
查询结果是左侧表中所有的记录,即使右侧没有对应的记录。
OUTER可以省略
(3)右外连接 RIGHT OUTER JOIN…ON
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
显示右侧表中所有的记录,OUTER可以省略
(4)全连接 FULL JOIN
显示左侧和右侧表中所有的记录——MySQL不支持
(SELECT ename FROM emp_bj)
UNION
(SELECT ename FROM emp_sh);
MySQL中的全连接
(SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)
UNION
(SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);
UNION合并相同的记录
UNION ALL 不合并相同的记录
14.项目中如何保存日期、时间
2018-12-6 14:40:30
2018年12月6月 2018/12/6 12/6/2018
存储的是距离计算机元年(1970-1-1)的毫秒数
48*365*24*60*60*1000
1513728000000 使用BIGINT的列类型
参考新闻部分
—和之前的结果一样
SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;
(2)左外连接 LEFT OUTER JOIN…ON
SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;
查询结果是左侧表中所有的记录,即使右侧没有对应的记录。
OUTER可以省略
(3)右外连接 RIGHT OUTER JOIN…ON
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
显示右侧表中所有的记录,OUTER可以省略
(4)全连接 FULL JOIN
显示左侧和右侧表中所有的记录——MySQL不支持
(SELECT ename FROM emp_bj)
UNION
(SELECT ename FROM emp_sh);
MySQL中的全连接
(SELECT ename,dname FROM emp LEFT JOIN dept ON deptId=did)
UNION
(SELECT ename,dname FROM emp RIGHT JOIN dept ON deptId=did);
UNION合并相同的记录
UNION ALL 不合并相同的记录
14.项目中如何保存日期、时间
2018-12-6 14:40:30
2018年12月6月 2018/12/6 12/6/2018
存储的是距离计算机元年(1970-1-1)的毫秒数
48*365*24*60*60*1000
1513728000000 使用BIGINT的列类型
参考新闻部分
http://www.gree.com.cn/pczwb/xwzx/index.shtml