Mysql数据库

整体框架：

    ddl dml dcl dql ctl
    单表查询
    运算操作符：in、 like(% _) 、not、 is 、NULL 
    多表查询
        连接查询：
        	并（union  +） 交（intersection  +） 差(difference  -)  笛卡尔积(=) 
        内连接（inner join  on）
        外连接(left outer  on 、right outer on)
        子查询()
        组合查询（union All  返回所有匹配行）
    事务、视图、函数（聚合函数、字符串函数、数值函数、日期函数）、3范式
    执行顺序：	
        from->join on->where->group by->聚合函数->having->select->distinct->order by->limit

---

DDL DML单表查询

查看安装的服务器版本	select version()
创建数据库，并指定字符集	create database test3 character set UTF8;
查询所有数据库的名称	show databases;
查询某个数据库的创建语句	show create database test3;
修改数据库的字符集	alter database test2 character set GBK;
删除数据库	drop database test2 ;
查询当前正在使用的数据库名称	select database();
创建表	create table student1(id int,name varchar(32),birthday date,money double(5,2));
修改主键为自动增长	alter table Student change id int auto_increment ;
重置自动递增的初始值	alter table student auto_increment=1;
查询某个数据库中所有的表名称	show tables;
查询表结构	desc student1;
添加一列	alter table teacher add jieshao varchar(50);
修改列类型	alter table teacher modify jieshao varchar(99);
修改列名和类型	alter table teacher change jieshao newjieshao varchar(999);
删除指定列	alter table teacher drop intro;
修改表字符集	alter table teacher charset gbk;
修改表名	rename table teacher to tch;
删除表	drop table tch;
添加数据	INSERT INTO student1(id,name,birthday) VALUES (1,‘李四1’,‘2018-11-11’);
批量插入数据	INSERT INTO student1(id,name,birthday) VALUES(1,‘李四1’,‘2020-11-11’),(2,‘李四2’,‘2020-11-11’),(3,‘李四3’,‘2020-11-11’),(4,‘李四4’,‘2020-11-11’);
删除表中所有记录	delete from student;
先删除表在创建一个一摸一样的表	TRUNCATE TABLE 表名;
根据条件删除数据	delete from student where id = 1;
修改数据	update student set name = ‘王五’, birthday = ‘2020-12-20’ where id = 2;
查询完整语法，查询的结果是一个虚拟表	select字段列表from表名列表where条件列表group by分组字段having分组之后的条件order by排序limit分页限定
例子：创建表进行查询
create table stu(id int,name varchar(20),chinese double,english double,math double);	insert into stu(id,name,chinese,english,math) values(1,‘tom’,89,78,90);insert into stu(id,name,chinese,english,math) values(2,‘jack’,67,98,56);insert into stu(id,name,chinese,english,math) values(3,‘jerry’,87,78,77);insert into stu(id,name,chinese,english,math) values(4,‘lucy’,88,NULL,90);insert into stu(id,name,chinese,english,math) values(5,‘james’,82,84,77);insert into stu(id,name,chinese,english,math) values(6,‘jack’,55,85,45);insert into stu(id,name,chinese,english,math) values(7,‘tom’,89,65,30);
查询所有记录	select * from stu;
查询表中所有学生的姓名和对应的语文成绩	SELECT name,chinese FROM stu;
查询表中学生姓名（去重）	SELECT DISTINCT name FROM stu;
在所有学生数学分数上加10分特长分	SELECT name,math+10 FROM stu;
统计每个学生的总分（在做行运算时,null参与的运算，计算结果都为null #as 起别名 ）	SELECT name, chinese+IFNULL(english,0)+math as 总成绩 FROM stu;
如果第一个expro1为null则自动修改为0	IFNULL(expr1,expr2)
AS的用法	SELECT s.name FROM stu as s
运算符：	iin 、is NULL、like、AND、OR、NOT
条件数据准备
CREATE TABLE stu1 (id int,name varchar(20),age int,sex varchar(5),address varchar(100),math int,english int);	INSERT INTO stu1(id,NAME,age,sex,address,math,english) VALUES(1,‘马云’,55,‘男’,‘杭州’,66,78),(2,‘马化腾’,45,‘女’,‘深圳’,98,87),(3,‘马景涛’,55,‘男’,‘香港’,56,77),(4,‘柳岩’,20,‘女’,‘湖南’,76,65),(5,‘柳青’,20,‘男’,‘湖南’,86,NULL),(6,‘刘德华’,57,‘男’,‘香港’,99,99),(7,‘马德’,22,‘女’,‘香港’,99,99),(8,‘德玛西亚’,18,‘男’,‘南京’,56,65);
条件查询sql
查询math分数大于80分的学生	SELECT * FROM student2 WHERE math > 80;
查询english分数小于或等于80分的学生	SELECT * FROM student2 WHERE english <= 80;
查询age等于20岁的学生	SELECT * FROM student2 WHERE age = 20;
查询age不等于20岁的学生	SELECT * FROM student2 WHERE age != 20;
查询age大于35且性别为男的学生(两个条件同时满足)	SELECT * FROM student2 WHERE age > 35 AND sex = ‘男’;
查询age大于35或性别为男的学生(两个条件其中一个满足)	SELECT * FROM student2 WHERE age > 35 OR sex = ‘男’;
查询id是1或3或5的学生	SELECT * FROM student2 WHERE id = 1 OR id =3 OR id = 5;-- in关键字-- 再次查询id是1或3或5的学生SELECT * FROM student2 WHERE id IN(1,3,5);
查询id不是1或3或5的学生	SELECT * FROM student2 WHERE id NOT IN(1,3,5);
查询english成绩大于等于77，且小于等于87的学生	SELECT * FROM student2 WHERE english >=77 AND english <=87;SELECT * FROM student2 WHERE english BETWEEN 77 AND 87;
查询英语成绩为null的学生	SELECT * FROM student2 WHERE english = NULL; – null这哥们六亲不认…SELECT * FROM student2 WHERE english IS NULL;SELECT * FROM student2 WHERE english IS NOT NULL;
查询姓马的学生	SELECT * FROM student2 WHERE name LIKE ‘马%’;
查询姓名中包含’德’字的学生	SELECT * FROM student2 WHERE name LIKE ‘%德%’;
查询姓马，且姓名有三个字的学生	SELECT * FROM student2 WHERE name LIKE ‘马__’;

以下语句很少使用

修改表名	alter table test_a rename to sys_app;
修改表注释	alter table sys_application comment ‘系统信息表’;
修改字段类型和注释	alter table sys_application modify column app_name varchar(20) COMMENT ‘应用的名称’;
修改字段类型	alter table sys_application modify column app_name text;
单独修改字段注释	alter table sys_application column field_name int comment ‘修改后的字段注释’;
设置字段允许为空	alter table sys_application modify column description varchar(255) null COMMENT ‘应用描述’;
增加一个字段，设好数据类型，且不为空，添加注释	alert table sys_application add url varchar(255) not null comment ‘应用访问地址’;
增加主键	alter table t_app add aid int(5) not null ,add primary key (aid);
增加自增主键	alter table t_app add aid int(5) not null auto_increment ,add primary key (aid);
修改为自增主键	alter table t_app modify column aid int(5) auto_increment ;
修改字段名字(要重新指定该字段的类型)	alter table t_app change name app_name varchar(20) not null;
删除字段	alter table t_app drop aid;
在某个字段后增加字段	alter table t_app add column gateway_id int not null default 0 AFTER aid;
调整字段顺序	alter table t_app change gateway_id gateway_id int not null after aid;

DQL 多表查询

前提表准备

-- 多表查询
create database db3;
use db3;
-- 创建部门表（主表）
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'),('销售部');

-- 创建员工表（从表）
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

测试

查询唐僧的 id，姓名，性别，工资和所在部门名称	SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name = ‘唐僧’;
查询与孙悟空同一个部门人的信息	select * from emp e1 INNER JOIN emp e2 on e2.name=‘孙悟空’ and e1.dept_id=e2.dept_id
左外连接（推荐） – 查询所有员工信息及对应的部门名称	SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
查询所有部门及对应的员工信息	SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;
右外连接（了解） – 查询所有部门及对应的员工信息	SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;
查询工资最高的员工是谁？	SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
查询工资小于平均工资的员工有哪些？	SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
查询工资大于5000的员工，来自于哪些部门的名字	SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);
查询开发部与财务部所有的员工信息	SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name IN(‘开发部’,‘财务部’));
查询员工入职日期是2011-11-11日之后的员工信息和部门信息	SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > ‘2011-11-11’) t2 WHERE t1.id = t2.dept_id;
查询员工入职日期在孙悟空之后的员工信息和部门信息	select * from emp e1 inner join emp e2 on e2.name=‘孙悟空’ where e1.join_date>e2.join_date

多表查询综合实例

数据准备

-- 多表案例
create database db3_pro;
use db3_pro;
-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY auto_increment, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20), -- 职务名称
  description VARCHAR(50) -- 职务描述
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id  外键
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资 99999.99
  bonus DECIMAL(7,2), -- 奖金 99999.99
  dept_id INT, -- 所在部门编号  外键
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,  -- 等级
  losalary INT, -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

测试

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述	SELECT t1.id, – 员工编号 t1.ename, – 员工姓名 t1.salary,-- 工资 t2.jname, – 职务名称 t2.description – 职务描述 FROM emp t1, job t2 WHERE t1.job_id = t2.id;
查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置	SELECT t1.id, – 员工编号 t1.ename, – 员工姓名 t1.salary,-- 工资 t2.jname, – 职务名称 t2.description, – 职务描述 t3.dname, – 部门名称 t3.loc – 部门位置FROM emp t1, job t2,dept t3 WHERE t1.job_id = t2.id AND t1.dept_id = t3.id;
查询员工姓名，工资，工资等级	SELECT t1.ename , t1.salary, t2.* FROM emp t1, salarygrade t2 WHERE t1.salary BETWEEN t2.losalary AND t2.hisalary;
查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级	SELECT t1.ename, t1.salary, t2.jname, t2.description， t3.dname, t3.loc, t4.grade FROM emp t1,job t2,dept t3,salarygrade t4 WHERE t1.job_id = t2.id AND t1.dept_id = t3.id AND t1.salary BETWEEN t4.losalary AND t4.hisalary;
查询出部门编号、部门名称、部门位置、部门人数	SELECT t1.id, t1.dname, t1.loc , t2.total FROM dept t1, ( SELECT dept_id,COUNT(id) total FROM emp GROUP BY dept_id ) t2 WHERE t1.id = t2.dept_id;
查询每个员工的名称及其上级领导的名称（自关联）	SELECT yuangong.id, yuangong.ename, lingdao.id, lingdao.ename FROM emp yuangong LEFT OUTER JOIN emp lingdaoON yuangong.mgr = lingdao.id ;

用户权限DCL

创建用户	create user ‘用户名’@‘主机名’ identified by ‘密码’;
查看用户权限	show grants for ‘用户名’@‘主机名’;
用户授权	grant [elect、insert、delete、update、create、all] on 数据库名.表名 to ‘用户名’@‘主机名’;
撤销授权	revoke [elect、insert、delete、update、create、all] on 数据库名.表名 from ‘用户名’@‘主机名’;
删除用户	drop user ‘用户名’@‘主机名’
管理员为所有用户设置密码	set password for ‘用户名’@‘主机名’=password(‘新密码’);
普通用户修改自己的密码	set password=password(‘新密码’);
超级管理帮你找回密码	SET PASSWORD FOR ‘tom’@‘%’= PASSWORD(‘999’);

事务

#开启事务
BEGIN;
update account set money=money-10 where id=2;
update account set money=money+10 where id=1;
-- COMMIT; 提交
ROLLBACK;   #回滚
#关闭事务

#设置保存点
SAVEPOINT  '保存点名称'
ROLLBACK   TO   '保存点名称'
#回滚到保存点

#查看事务默认提交方式
SELECT @@autocommit; 
show variables like 'autocommit';

#修改默认提交方式 
set @@autocommit = 0;    1表示自动提交0表示手动提交
或
set autocommit=off;        NO表示自动提交 OFF表示关闭自动提交

事务的四大特性 ACID

原子性(要么全做，要么全不做)、

隔离性（事务与事务之间没有关系）、

一致性（数据库中的数据与现实世界中的保持一致）、

持久性（执行的结果永久保存）
事物的隔离性会存在的问题：多个事务操作同一批数据（会存在三种问题：脏读、不可重复读、幻读），解决办法设置隔离级别（4个级别）：

事务的几种状态：

活动的----------最后一个操作执行完--------->部分提交的

活动的---------遇到了错误或手动停止-------->失败的

部分提交的--------------刷新到磁盘------------>提交的

部分提交的---------------刷新到磁盘遇到了问题----------------->失败的

失败的-----------回滚过程中遇到了问题------------->中止的

开启事务的两种方式：

begin

start transaction [read only[read write],with consistent snapshot]

常用函数

聚合函数

avg() count() max() min() sum()

字符串函数

合并字符串	concat(str1,str2,str3…) concat_ws(’ ’ , ‘i’ , ‘am’ , ‘superman’);
比较两个字符串大小	strcmp(‘a’ , ‘b’);
获取字符串字节数	length(‘hello’);
获取字符串字符数	char_length(‘hello’);
字母大小写转换	upper(‘a’); lower(‘A’);
查找str1在集合str2中的位置	find_in_set(‘abc’ , ‘123,abc,456’);
查找str1在str2中的位置	field(‘abc’ , ‘123’,‘abc’,‘456’);
查找str1在str2中第一次出现的位置	locate(‘a’ , ‘123a123a’); position(‘a’ in ‘123abc456abc’);
查找str2在str1中第一次出现的位置	instr(‘123abc456abc’ , ‘a’);
返回指定index位置的字符串	elt( 1, ‘a’ ,‘b’ ,‘c’) ;
截取str左边的n个字符	left(‘superman’ , 5);
截取str右边的n个字符	right(‘superman’ , 3);
从str的index位置截取len个字符	substring(‘iamsuperman’ , 4, 5) ;
字符串去除空格	ltrim(’ hello jack’); rtrim(’ hello jack ‘); trim(’ hello jack ');
使用str2从str1的index位置替换str1的len个元素	insert(‘hello world’ , 7 , 5 , ‘amy’);
将str中的子串str1全部替换成str2	replace(‘123 admin 456admin’ , ‘admin’ , ‘jack’);

数值函数

abs(x) ceil(x) floor(x) rand(x)

日期函数

获取当前日期	curdate();
获取当前时间	curtime();
获取当前日期时间	now();
从日期中选择月份、周数、年数、小时数、分钟数、秒数、周几、星期几、日期部分	year( now() );month( now() );day( now() );Hour( now() );Minute(now() ); Second(now() );weekday( curdate() );dayname( curdate() );date( now() );
格式化日期	DATE_FORMAT(now() , ‘%Y-%m-%d %H:%i:%S’);

范式

第一范式（1NF）要求数据表中每一列都是不可分割的原子数据项

第二范式（2NF）在1NF的基础上，消除非主属性对主码的部分函数依赖

第三范式（3NF）在2NF的基础上，消除传递依赖

视图

是一个人虚拟表（从元表中拿数据，随着元表的更改而更改）

创建视图	CREATE VIEW MyView1 AS SELECT customers.cust_name, customers.cust_contact FROM customers
修改视图	CREATE OR REPLACE VIEW 视图名 AS SELECT […] FROM […];
删除视图	drop view 视图名;
查看视图	desc 视图名;

存储过程

为以后使用存储的一条或几条sql语句的集合

索引

约束

主键约束、外键约束、唯一约束、默认约束、非空约束、检查约束