一篇文章学完Mysql
在数据库中所有的字符串类型，必须使用单引号，不能使用双引！
零：数据库引擎
*看你的mysql现在已提供什么存储引擎:
>show engines;
*看你的mysql当前默认的存储引擎:
>show variables like ‘%storage_engine%‘;
*查看表的引擎类型(MyISAM、InnoDB)
>show table status from 数据库名 where name=‘表名‘;
*你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
>show create table 表名;
*最常使用的2种存储引擎:
1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。
2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
一：操作数据库
查看所有数据库：SHOW DATABASES
切换(选择要操作的)数据库：USE 数据库名
创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET=utf8]
删除数据库：DROP DATABASE [IF EXISTS] 数据库名
修改数据库编码：ALTER DATABASE 数据库名 CHARACTER SET utf8
二：数据类型
int：整型
double：浮点型，例如double(5,2)，表示最多5位，必须有2位小数。
decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题。
char：固定长度字符串类型；char(255)
varchar：可变长度字符串类型；varchar(65535)
text(clob)：字符串类型；
clob：包括：tinytext，text，mediumtext，longtext
blob：包括：tinyblod，blod，mediumblod，longblod
date：日期类型，格式为：yyyy-MM-dd。
time：时间类型，格式为：hh:mm:ss。
timestamp：时间戳类型。
三：表
*创建表：
CREATE TABLE [IF NOT EXISTS] 表名(
列名 列类型，
列名 列类型，
列名 列类型，
....
列名 列类型
)；
*查看当前数据库中所有的表名称：SHOW TABLES;
*查看指定表的创建语句：SHOW CREATE TABLE 表名;
*查看表结构：DESC 表名;
*删除表：DROP TABLE 表名;
*修改表：前缀：ALTER TABLE 表名
>修改之添加列：
ALTER TABLE 表名 ADD(
列名 列类型，
列名 列类型，
...
列名 列类型
)
>修改之修改列类型（如果被修改的列已存在数据，那么新的类型可能会影响到已存在的数据）：ALTER TABLE 表名 MODIFY 列名 列类型；
>修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型；
>修改之删除列：ALTER TABLE 表名 DROP 列名；
>修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;
四：数据操作
*查询表记录：
SELECT * FROM 表名;
*插入数据：
INSTER INTO 表名(列名1，列名2，...) VALUES (列值1，列值2，...)
或
INSTER INTO 表名 VALUES(列值1，列值2，...);
*修改数据：
UPDATE 表名 SET 列名1=列值1，列名2=列值2，...[WHERE 条件]
条件包括：
>条件必须是一个boolean类型的值或表达式
>运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND
between...and举例：update stu set age=20 where age between 10 and 40;
is null举例：select * from stu where age is null;
WHERE age>=18 AND age<=80
WHERE age BETWEEN 18 AND 80
WHERE name IN(‘zhangsan‘,‘lisi‘)
WHERE age IS NULL，不能用等号
WHERE age IS NOT NULL
*删除数据：
DELETE FROM 表名 [WHERE 条件];
TRUNCATE TABLE 表名：TUUNCATE是DDL语句，它是先删除drop该表，再create该表.而且无法回滚。
五：DCL
1.创建用户
*CREATE USER 用户名@IP地址 IDENTIFIED BY ‘密码‘
>用户只能在指定的IP地址上登陆
*CREATE USER 用户名@‘%‘ IDENTIFIED BY ‘密码‘
>用户可以在任意IP地址上登陆
2.给用户授权
*GRAND 权限1，...，权限n ON 数据库.* TO 用户名@IP地址
>权限、用户、数据库
>给用户分派在指定的数据库上的指定的权限
>例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON stu.* TO ;
*GRANT ALL ON 数据库.* TO 用户名@IP地址
>给用户分派指定的数据库上的所有权限
3.撤销授权
*REVOKE 权限1，...，权限n ON 数据库.* FROM 用户名@IP地址；
>撤消指定用户在指定数据库上的指定权限
>例如：REVOKE CREATE,ALTER,DROP ON stu.* FROM ;
4.查看权限
*SHOW GRANTS FOR 用户名@IP地址
>查看指定用户的权限
5.删除用户
*DROP USER 用户名@IP地址
*************************DQL************数据查询语言*********************
一：基本查询
1:查询所有列
SELECT * FROM 表名;
SELECT * FROM emp;
2:查询指定列
SELECT 列1[，列2，...，列N] FROM 表名；
SELECT empno,enname,sal,comm FORM 表名；
3:完全重复的记录只一次
当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列或多列时，这种可能就大了。
SELECT DISTINCT *|列1[，列2，...，列N] FROM 表名；
SELECT DISTINCT sal FROM emp;
->查询员工表的工资，如果存在相同的工资只显示一次
4:字段名称修改,列加字符串
SELECT 列1，CONCAT(列2+“%”) FROM 表名 WHERE 条件;
5：列运算
*数量类型的列可以做加、减、乘、除运算
SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;
*字符串类型可以做连续运算
SELECT CONCAT(‘$‘,sal) FROM emp;
*转换NULL值
有时需要把NULL转换成其它值，例如comm+1000时，如果comm列存在NULL值，那么NULL+1000还是NULL,而我们这时希望把NULL当0来运算。
SELECT IFNULL(comm,0)+1000 FROM emp;
->IFNULL(comm,0) 如果comm中存在NULL值，那么当成0来运算。
*给列起别名
当使用列运算后，查询出的结果集中的列名称很不好看，这时我们可以给列起别名，这样在结果集中列名就显示别名了
SELECT IFNULL(comm,0)+1000 AS 奖金 FORM emp;
->其中AS可以省略
条件查询：
*SELECT empno,ename,sal,comm FROM emp WHERE sal>1000 AND comm IS NOT NULL;
*SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 2000 AND 3000;
*SELECT empno,ename,job FROM emp WHERE job IN(‘经理‘,‘董事长‘);
6：模糊查询
*SELECT * FROM emp WHERE ename LIKE ‘张_‘；
->模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。
->上面语句查询的是姓张，名字由两个字组成的员工。
*SELECT * FROM emp WHERE ename LIKE ‘___‘ ;
->上面查询姓名由3个字组成的员工
SELECT * FROM emp WHERE ename LIKE ‘张%‘；
->如果我们想查询姓张，名字几个字都可以的员工时就要使用“%”了。
->其中%匹配0~N个任意字符，所以上面的语句查询的是姓张的所有员工。
SELECT * FROM emp WHERE ename LIKE ‘%阿%‘
->千万不要认为上面的语句是查询姓名中间带有“阿”字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到。
SELECT * FROM emp WHERE ename LIKE ‘%‘;
->这个条件等同与不存在，但如果姓名为NULL的查询不出来。
7：排序
SELECT * FROM emp ORDER BY sal ASC;
->上面是升序，默认是升序，ASC可省略
SELECT * FROM emp ORDER BY comm DESC
->按comm降序，DESC不能省略
SELECT * FROM emp ORDER BY sak ASC,comm DESC.
->先按sal升序排，如sal一样，按 comm降序排序。
8：聚合函数
聚合函数用来做某列的纵向运算
1) COUNT
SELECT COUNT(*) FROM mep;
->计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
->云计算emp表中comm列不为NULL的记录的行数
2) MAX
SELECT MAX(sal) FROM emp;
->查义最高工资
3） MIN
SELECT MIN(sal) FROM emp;
->查询最低工资
4) SUM
SELECT SUM(sal) FROM emp;
->查询工资合
5） AVG
SELECT AVG(sal) FROM emp;
->查询平均工资
9:分组查询
分组查询是把记录使用某一列进行分组，然后查询组信息
例如：查看所有部门的记录数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
->使用deptno分组，查询部门编号和每个部门的记录数
SELECT job,MAX(sal) FROM emp GROUP BY job
->使用job分组，查询每种工作的最高工资
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*)>3;
->以部门分组，查询每组记录数。条件为记录数大于3
10：limit子句
LIMIT用来限定查询结果的起始行，以及总行数。
SELECT * FROM emp LIMIT 4,3;
->其中4表示从第5行开始，其中的3表示一共查询3行。即第5，6，7行记录。
各关键字执行顺序：
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
*****************编码设置*****************
1.查看MySQL数据库编码
* SHOW VARIABLES LIKE ‘char%‘；
->获取char开头的所有变量，这些变量有编辑有关
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | E:\phpstudy_pro\Extensions\MySQL5.7.26\share\charsets\
*character_set_client:MySQL使用该编码来解读客户端发过来的数据
*character_set_results:MySQL会把数据转换成该编码后，再发送给客户端。
注：在my.ini修改编码才是一劳永逸的办法
[client]
port=3306
default-character-set=utf8 /*修改它一劳永逸，它可以修改三个变量，client、results、connection*/
***********************备份与恢复******************************
1:数据库导出SQL脚本
>mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径
>例如：mysqldump -uroot -proot test>C:/name.sql
>注意：不要打分号，要不登陆mysql，直接在cmd下运行
>注意：生成的脚本文件中不包含create database语句
2：执行SQL脚本
一:
>mysql -u用户名 -p密码 数据库<脚本文件路径
>mysql -uroot -proot test<c:\name.sql
二：
登陆mysql，然后>source SQL脚本路径
********************************约束**********************************
约束是添加在列上的，用来约束列的。
一：主键约束（唯一标识)
>非空
>唯一
>被引用
当表中某一列被指定为主键后，该列就不能为空，不能有重复的值出现。
创建表时指定主键的两种方式：
>CREATE TABLE stu{
sid CHAR(6) PRIMARY KEY,
sname VARCHAR(50),
age INT,
gender VARCHAR(50)
}
>CREATE TABLE stu{
sid CHAR(6),
sname VARCHAR(50),
age INT,
gender VARCHAR(50),
PRIMARY KEY(sid)
}
指定sid为主键列，即为sid列添加主键约束
修改表时指定主键，ALTER TABLE stu ADD PRIMARY KEY(sid)
删除主键：ALTER TABLE stu DROP PRIMARY KEY;
二：主键自增长
*因为主键列的特性是：必须唯一，不能为空，所以我们常会指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性。
*创建表时指定主键自增长：
CREATE TABLE stu{
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
age INT,
gender VARCHAR(20)
}
*修改表时设置主键自增长：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
*修改表时删除主键自增长：ALTER TABLE stu CHANGE sid sid INT;
三：非空约束
*因为某些列不能设置为NULL值，所以可以对列添加非空约束
*例如:
CREATE TABLE stu{
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
age INT,
gender VARCHAR(20)
}
*对sname列设置了非空约束
四：唯一约束
*数据库有些列不能设置为重复的值，所以可以对列添加唯的约束 。
*CREATE TABLE s tu{
sid INT PRIMARY KEY AUTO_INCREMETN,
sname VARCHAR(20) NOT NULL UNIQUE,
age INT,
gender VARCHAR(10)
}
*对sname列设置了非空约束
五：概念模型
当我们要完成一个软件系统时，需要把系统中的实体抽出来，形成概念模型。
如部门，员工都是系统中的实体。概念模型中的实体最终会成为JAVA中的类，数据库中表。
实体之间还存在关系，关系统有三种：
>1对多：如每个员工都从属于一个部门，而一个部门可以有多个员工，其中员工是多方，部门是一方；
>1对1：如老公和老婆
>多对多：老师与学生就是多对多，一个老师可以有多个学生，一个学生可以有多个老师。
概念模型在JAVA中成为实体类
类就是使用成员变量来完成关系，一般都是双向关联。
多对一双各中关联系，即员工关系部门，部门也关联员工。
class Employee{ //多方关联一方
...
private Department department;
}
class Deprtment { //一方关联多方
...
private List<Employee> employees;
}
class Husband{
...
private Wife wife;
}
class Wife{
...
private Husband
}
class Student{
...
private List<Teacher> teachers;
}
class Teacher{
...
private List<Student> students;
}
六：外键约束
*外键必须是另一个表的主键
*外键可以重复
*外键可以为空
*一张表中可以有多个外键。
概念模型在数据库中成为表
数据库表中的多对一关系，只需要在多方使用一个独立的列来引用1方的主键即可
创建外键约束：
CREATE TABLE dept(
deptno INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(50)
);
INSERT INTO dept VALUES(10,‘研发部‘)；
INSERT INTO dept VALUES(20,‘人力部‘)；
INSERT INTO dept VALUES(30,‘财务部‘)；
SELECT * FROM dept;
CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(50),
dno INT,
CONSTRAINT fk_emp_dno FOREIGN KEY(dno) REFERENCES dept(deptno)
)
INSERT INTO emp(empno,ename) VALUES(NULL,‘zhangSan‘);
***********************多表查询********************
一：分类：
>合并结果集
>连接查询
>子查询
二：合并结果集：
>要求被合并的表中，列的类型和列数相同
>UNION，去除重复行
>UNION ALL，不去除重复行
SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2;
三：连接查询：
1：分类
*内连接
*外连接
>左外连接
>右外连接
>全外连接(Mysql不支持)
*自然连接(属于一种简化方式)
2：内连接
*方式：SELECT * FROM 表1 别名1，表2 别名2 WHERE 别名1.xx=别名2.xx
*标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx==别名2.xx
*自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
*内连接查询出的所有记录都满足条件。
3.外连接
*左外：SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
>左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来，左表中不满足条件的记录，右表部分都为NULL
*左外自然：SELECT * FROM 表1 别名1 NATURAL LEFTT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
*右外：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
>右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表中不满足条件的记录，左表部分都为NULL
*右外自然：SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
*全链接：可以使用UNION来完成连接
4:查询中有查询（查看select关键字的个数）
1：出现的位置
*WHERE 后作为条件存在
*from后作为表存在（多行多列）
2：条件
*（***）单行单列：SELECT * FROM 表1 别名1 WHERE 列1[=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
*（**）多行单列：SELECT * FROM 表1 别名1 WHERE 列1[IN,ALL,ANY](SELECT 列 FROM 表2 别名2 WHERE 条件)
*（*） 单行多列：SELECT * FROM 表1 别名1 WHERE （列1，列2）IN （SELECT 列1，列2 FROM 表2 别名2 WHERE 条件）
*（***）多行多列：SELECT * FROM 表1 别名1，（SELECT...）别名2 WHERE 条件
**********案例**********
表1 | 表2 |
 |  |
1：查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。
分析：
列：dept.deptno、dept.deptname、dept.loc、部门人数
表：dept,emp
条件：empt.deptno=dept.deptno
SELECT * FROM dept;
SELECT deptno,count(*) FROM emp GROUP BY deptno
结束：
SELECT dept.*,z1.cnt FROM dept,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z1
WHERE dept.deptno=z1.deptno;
3：列出所有员工的姓名及其直接上级的姓名
分析：
列：员工姓名，上级姓名
表：emp
条件：员工的mgr=上级的empno
结果：
SELECT e.ename,IFNULL(m.ename,‘BOOS‘) 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno
4：列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称。
列：e.empno,e.ename,d.dname
表：emp e,emp m,dept d
条件：e.hiredate<m.hiredate
结果：
SELECT e.empno,e.ename,d.deptname
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno
相关推荐
CoderToy 2020-11-16
bianruifeng 2020-11-16
云中舞步 2020-11-12
敏敏张 2020-11-11
暗夜之城 2020-11-11
好记忆也需烂 2020-11-11
Coder技术文摘 2020-09-29
huacuilaifa 2020-10-29
Gexrior 2020-10-22
lpfvip00 2020-10-07
云中舞步 2020-09-11
康慧欣 2020-09-10
emmm00 2020-11-17
世樹 2020-11-11
tufeiax 2020-09-03
疯狂老司机 2020-09-08
王艺强 2020-11-17
ribavnu 2020-11-16