本文详细介绍了MySQL的基础知识，包括数据库、数据库管理系统（DBMS）、SQL语言的分类，以及如何登录和设置MySQL密码。重点讲解了DQL（数据查询语言）、DML（数据操作语言）、DDL（数据定义语言）和TCL（事务控制语言）。此外，还涵盖了数据表的操作，如创建、修改、删除，以及数据类型、约束和事务处理。文章深入探讨了SQL语句的执行计划、索引的创建和使用，以及事务的隔离级别。最后，提到了存储引擎的选择和数据库优化的重要性。
MySQL基础
DB: database 数据库
DBMS:database Management System 数据库管理系统（如MySQL Oracle..............）
SQL:结构化查询语言（Structured Query Language，SQL），是一门标准通用语言，标准的SQL适用于所有的数据库产品。（先编译在执行）
SQL语句的分类
DQL Data Query Language （数据查询语言） 查询语句，凡是select 语句都是DQL
DML Data Manipulation Language（数据操作语言） 对表中数据进行增删该 insert delete updata
DDL Data Denifition Language （数据定义语言） 对表结构的增删该 create drop alter
TCL Trasactional Control Languag（事务控制语言） commit 提交事务，rollback回滚事务 。
DCL Data Control Language （数据控制语言） grant授权 revoke撤销权限等
MySQL登入与设置密码
1 登入MySQL
在MySQL的bin目录中mysql.exe是MySQL提供的命令行客户端工具，用于访问数据库。该程序不能直接双击运行，需打开命令行窗口，先切换到mysql的bin目录下，然后执行登入MySQL服务器的命令。
//登入命令 mysql-u root //退出 exit / quit mysql -u root -p //-u表示指定用户为root用户 -p 后接密码（可直接后接明文密码或先回车换行接密文密码） mysql -u root -h(用于指定的登入MySQL服务器地址域名或ip/-h local(-h 127.0.0.1)均表示登入本地服务器) -p mysql -u root -P(大写的P表示链接指定的端口号) -p mysql -uUserName -pPassword -h HostName_IP -P 3306 -D dbName -h: 主机名，连接数据库的主机名或者IP; -u: 用户名，连接数据库的用户名; -P: 端口，连接数据库的端口，默认是3306; -p: 密码，连接数据库的密码，-p后面直接跟密码，但不太安全，建议-p后回车，再输入密码； -D: 数据库名称，连接数据库的数据库名称； -p后面必须紧跟密码，不能有空格， 否则会要求输入密码； 其他参数后面可以有空格， 比如-uroot和-u root是等价的。 直接输入mysql等价于: mysql -uroot -h localhost2 设置密码
//设置密码 mysql> alter user 'root' @ 'localhost' identified by '12345'; //取消密码 mysql> alter user 'root' @ 'localhost' identified by '';3 环境变量的设置
MySQL客户端的相关命令
\h 显示帮助信息
\? 显示帮助信息
\c 清除当前语句,终止一个正在编写的语句
\q 退出MySQL（相当于exit/quit）
\p 打印当前命令
\C 切换到另一个字符集
\u 选择一个数据库使用，参数为数据库名称（相当于use addressbook;）
\s 从服务器获取MySQL的状态信息（相当于status;）
\x 清理会话上下文信息
\W 每一个语句之后显示警告
\w 每一个语句之后不显示警告
查看数据库版本信息
select version();
查询当前使用的数据库
select database();
show databases; 查看数据库
在Navicat工具软件中显示数据库
MySQL自动创建的数据库的作用
提示：不要随意删除系统自带的数据库，否则MySQL不能正常运行。
创建数据库
create database if not exists mydb;
使用Navicat工具创建学生信息管理数据库
对话框中的相应文本框中输入数据库名，单击【确定】按钮，完成数据库的创建工作.字符集不选择表示取默认值，当然也可以重新选择数据库使用的字符集。
查看指定数据库的创建信息
show create database mydb;
打开数据库
use addressbook;（ \u 选择一个数据库使用，参数为数据库名称）
删除数据库
drop database if exists mydb;
SQL语句注释
# MySQL单行注释
-- 标准SQL单行注释（MySQL支持）在第二个短横线之后至少添加一个控制字符（空格 制表符 换行符等）
/* 多行注释 */
SQL 语句结尾
; (英文半角分号结尾)
\g 和分号效果一样
\G 将所有字段纵向排列展示
MySQL 关键字大小写
在Windows下 数据库名，表名，字段名忽略大小写
在Linux下 数据库与数据表 名 区分大小写 （推荐使用小写）
SQL反引号
为了避免用户自定义的名称与系统中的命令冲突，最好使用反引号（``）包裹自定义名称，反引号在tab键上方英文半角下按下此键。
导入初始化数据
source + sql脚本的路径（复制路径或直接拖入窗口） （+ 表示后接）
mydb.sql 脚本
-- 判断是否已经存在需要创建的表，存在则删表 drop table if exists emp ; drop table if exists dept ; drop table if exists salgrade ; -- 分别创建dept(部门表)，emp(员工表)，salgrade(员工等级表) create table dept ( deptno int(2) not null comment '编号', dname varchar(14) comment '名称', loc varchar(13) comment '位置', primary key (deptno) ); create table emp ( empno int(4) not null comment '编号', ename varchar(10) comment '名字', job varchar(9) comment '岗位', mgr int(4) comment '领导编号', hiredate date default null comment '入职日期', sal double(7,2) comment '薪资', comm double(7,2) comment '补助', primary key (empno), deptno int(2) ); create table salgrade( grade int comment '等级', losal int comment '最低薪资', hisal int comment '最高薪资' ); /*分别在三张表中插入数据*/ insert into dept values (10,'accounting','new york'), (20,'research','dallas'), (30,'sales','chicago'), (40,'operations','boston'); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); commit; insert into salgrade value ( 1, 700, 1200), ( 2, 1201, 1400), ( 3, 1401, 2000), ( 4, 2001, 3000), ( 5, 3001, 9999);脚本免费下载
https://download.csdn.net/download/weixin_49070591/85174266DQL语句操作命令
mysql -u root -p -- 登入mysql status ; -- 查看MySQL状态信息 select version \G -- 查看MySQL版本信息 show databases ; -- 查看数据库 select database() ; -- 查看当前使用的数据库 use mydb -- 使用数据库 show tables \g -- 查看所有表 -- 查看表面中带有emp的数据表 show tables like '%emp%'; -- 查看数据库中所有表的详细信息 show table from mydb \G -- 查看数据表名中含有emp的数据表的详细信息 show table status from mydb like '%emp%'\G -- 查看表结构 describe emp ; desc dept ; desc salgrade ; -- 查看数据表的创建语句 show create table emp \G /*简单查询*/ -- 查看所有员工信息 select * from emp ; select empno ,ename , job ,mgr, hiredate,sal,comm,deptno from emp ; --查看员工部分字段信息 select ename , sal from emp ; -- 别名查询 关键字是as 可省略 select ename as '姓名', sal '薪资' from emp ; -- 列出员工的年薪(字段可以参与数字运算) select ename xingming , (sal+comm)*12 '年薪' from emp ; -- 注意若有null参与运算其结果均为null,故应该把null 字段做空函数处理 select ename xingming , (sal+ifnull(comm,0))*12 '年薪' from emp ; /* 条件查询 语法：select 字段.... from 表名 where 条件 执行顺序：先from 在where 最后select */ -- 列出工资等于5000 的员工 select ename , sal from emp where sal=5000 ; -- 查询某人(ford)的工资 select ename, sal from emp where ename = 'ford'; -- 找出工资大于等于3000的员工 select ename ,sal from emp where sal>=3000 ; -- 找出工资不等于3000的员工 select ename ,sal from emp where sal <> 3000 ; select ename ,sal from emp where sal != 3000 ; -- 找出工资[1100,3000]的员工 select ename ,sal from emp where sal>=1100 && sal<=3000 ; select ename ,sal from emp where sal>=1100 and sal<=3000 ; select ename , sal from emp where sal between 1100 and 3000 ; /* between...and...是闭区间 ，必须左小右大 ，可以对数字[]和字符串[)操作 */ -- 找出工资为1100和3000的员工 select ename ,sal from emp where sal = 1100 || sal = 3000 ; select ename ,sal from emp where sal = 1100 or sal = 3000 ; select ename , sal from emp where sal in (1100,3000); /* is null 和 is not null */ -- 查出那些员工没有津贴 select ename , comm from emp where comm is null ; select ename , comm from emp where comm is null or comm = 0 ; -- 查出那些员工有津贴 select ename , comm from emp where comm is not null ; /* and和or */ -- 找出薪资大于2000且是在20 ，30 部门的员工 select ename ,sal ,deptno from emp where sal >2000 and (deptno =20 or deptno = 30) ; /* 条件查询 in */ -- 找出工资为1100和3000的员工 select ename , sal from emp where sal in (1100,3000); -- 列出工资等于5000 的员工 select ename , sal from emp where sal in (5000); /* 模糊查询like */ -- 创建一张带下标表名的表 create table new_emp( id int , name varchar(12) ); -- 插入数据 insert into new_emp values ( 1 ,'张三'), ( 2 ,'李-四'), ( 3 ,'wan_wu'); -- 找出表面中含有下划线的数据表 show tables like '%\_%'; -- 找出表中含有下划线的名字 select name ,id from new_emp where name like '%\_%'; -- 找出名字最后一个字母是t的员工 select ename from emp where ename like '%t'; -- 最后一个字母是t -- 找出名字第一个字母是A的员工 select ename from emp where ename like 'a%'; -- 第一个字母是a -- 找出名字中含有ll的员工 select ename from emp where ename like '%ll%'; -- 查找名字是含四个字符的员工 select ename from emp where ename like '____'; /* 排序（降序，升序） order by 字段 asc/desc select 字段1 ，字段2，字段... from 表名 where 条件 order by asc | desc 执行顺序：from->where->select->order by */ -- 对员工名字进行排序 select ename from emp where ename between 'A' and 'D' ; -- 总感觉不对！ select ename from emp order by ename ; -- 默认升序 select ename from emp order by 1 ; -- 该操作不要用在Java程序中 select ename from emp order by ename asc ; -- 升序 select ename from emp order by ename desc ; -- 降序 /* 拆入一条数据 */ insert into emp (empno, ename, sal) value(7935,'adams',2200); -- 员工名字相同的工资进行降序排序 select ename , sal from emp order by ename asc ,sal desc; -- 去重排序 （列出不同部门并排序） select distinct deptno from emp order by deptno ; -- 找出工作岗位是salesman的员工并对其薪资进行降序列出(若工资相同的员工其名字升序排) select ename ,job, sal from emp where job = 'salesman' order by sal desc , ename asc ; /* 分组函数 (多行处理函数) 会自动忽略null count 计数，sum 求和， avg 平均， max 最大， min 最小 一般与group by 联合使用 */ -- 求员工个数,总工资，平均工资，最高工资，最低工资 select count(ename)as'人数',sum(sal) '总工资' ,avg(sal) '平均工资',max(sal) max, min(sal) min from emp ; -- 求有津贴的员工个数 select count(comm) from emp ; -- 会自动忽略null -- 求出津贴总金额 select sum(comm) from emp ; select sum(ifnull(comm,0)) from emp ; -- 画蛇添足 select sum(comm) from emp where comm is not null ; -- 画蛇添足 -- 找出工资高于平均工资的员工 select ename ,sal , avg(sal) from emp where avg(sal)<sal ;/* 报错 ERROR 1111 (HY000): Invalid use of group function 无效的使用了分组函数 原因：分组函数不可直接使用在where子句中？因为当没有分组时整张表就是一个分组，而 group by 是在where 执行结束之后在执行的。*/ select ename ,sal from emp where sal>(select avg(sal) from emp) ; -- 子查询嵌套 /* 单行处理函数 */ /* count(*) 与 count(某个字段) 的区别 count(*) 统计总记录条数 count(某个字段) 统计某个字段的非null的数据总数 */ /* 分组查询 group by 按某个字段或某些字段进行分组 having 对分组后的数据进行再次过滤 1 select 字段1 ，字段2，字段... 2 from 表名 3 where 条件 4 group by 字段 5 having 条件 6 order by asc | desc 执行顺序 ：2->3->4->5->1->6 */ -- 找出每个工资岗位的最高工资并对工资进行降序，工资相同名字升序 select job, max(sal) from emp group by job order by max(sal) desc ,job asc ; select ename,job, max(sal) from emp group by job order by max(sal) desc ,job asc ; --这条语句可以执行吗？？ 在MySQL里可以执行，但是结果没有意义，在Oracle会报语法错误✕ /* 多字段分组查询 */ -- 找出每个部门不同岗位的最高薪资 select deptno ,job , max(sal) from emp group by deptno , job order by deptno ; /* having */ -- 找出每个部门的最高薪资，并列出最高薪资大于2900 的数据 select deptno , max(sal) from emp group by deptno having max(sal) >2900 order by deptno ; -- 效率低 select deptno , max(sal) from emp where sal >2900 group by deptno order by deptno ; -- 找出每个部门的最高薪资，并列出最高薪资大于2000 的数据 select deptno, avg(sal) from emp group by deptno having avg(sal)>2000; -- 只能用having /* 查询去重 */ -- 去除某一列重复的 （列出不同部门并排序） select distinct deptno from emp ; -- #去除某一行重复的 SELECT DISTINCT * FROM emp; -- 统计岗位的数量 select count(distinct job )from emp ;
连接查询
连接查询分类
SQL92 SQL99连接划分
内连接：等值连接，非等值连接，自连接 外连接：左外连接，右外连接 全连接： 交叉连接：笛卡尔积现象(但两张表进行连接查询，没有加任何限制下，最终的查询结果记录是两张表的记录的乘积)
# 内连接-等值连接：(最大特点：条件是等量连接) # SQL92语法： -- 找出每一个员工的部门名称，显示员工及部门编号与名称 select e.ename , d.dname from emp e , dept d; -- 会产生笛卡尔积现象 -- 避免笛卡尔积现象（加限制条件）(匹配次数不变) select e.ename , d.dname from emp e , dept d where e.deptno = d.deptno ; # SQL99语法： -- select...from A join B on 条件 where 条件 -- 语法结构更清晰！连接条件与where条件分离。 select e.ename , d.dname from emp e join dept d on e.deptno = d.deptno ; select e.ename , d.dname from emp e inner join dept d on e.deptno = d.deptno ; -- inner可以省略# 内连接-非等值连接 -- 找出每个员工的工资等级，要求显示员工名，工资，工资等级 -- SQL92语法 -- select e.ename , e.sal , s.grade from emp e , salgrade s where e.sal>=s.losal and e.sal<= s.hisal ; select e.ename , e.sal , s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal ; -- select e.ename , e.sal , s.grade from emp e cross join salgrade s where e.sal>=s.losal and e.sal<= s.hisal ;# 自连接 -- 找出每个员工的领导并显示 select e.ename AS '员工' , p.ename '领导' from emp e inner join emp p on e.mgr = p.empno ; -- select e.ename AS '员工' , p.ename '领导' from emp e , emp p where e.mgr = p.empno ; -- select e.ename AS '员工' , p.ename '领导' from emp e cross join emp p where e.mgr = p.empno ;# 外连接 -- 主要查询主表中的数据，捎带查询能匹配的副表，当副表没有与主表匹配的数据，副表自动模拟出null与之匹配 # 左外连接：左表为主表 左连接有右连接写法 # 右外连接：右表为主表 右链接有左连接写法 -- 找出每个员工的领导并显示 # 左写法 select e.ename AS '员工' , p.ename '领导' from emp e left join emp p on e.mgr = p.empno ; # 右写法 select p.ename AS '员工' , e.ename '领导' from emp e right outer join emp p on p.mgr = e.empno ; -- outer 可以省略 -- 找出那个部门没有员工 select d.* , e.deptno from dept d left join emp e on d.deptno = e.deptno where e.deptno is null ; select d.* , e.deptno from emp e right outer join dept d on d.deptno = e.deptno where e.deptno is null ;# 交叉连接 -- 找出每一个员工的部门名称，显示员工及部门编号与名称 select e.ename , d.dname from emp as e cross join dept as d ; -- 会产生笛卡尔积现象 select e.ename , d.dname from emp as e cross join dept as d where e.deptno = d.deptno ;# 三表查询 -- 找出每一个员工的部门名称及工资等级 select e.ename , d.dname ,e.sal, s.grade from emp e join (dept d, salgrade s) on (e.deptno = d.deptno)and(e.sal between s.losal and s.hisal) ; select e.ename , d.dname ,e.sal, s.grade from emp e join dept d join salgrade s on e.deptno = d.deptno and e.sal between s.losal and s.hisal ; select e.ename , d.dname ,e.sal, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal ; -- 找出每一个员工的部门名称及工资等级，上级领导 select e.ename '员工', d.dname ,e.sal, s.grade , e1.ename '领导' from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left outer join emp e1 on e.mgr = e1.empno ;# 子查询 -- select 语句中嵌套 select 语句 ，被嵌套的select 语句就是子查询 /* select ..(select) from ..(select) where ..(select)... */ # where 语句中嵌套子查询 -- 求出高于平均工资的员工信息 select ename , sal from emp where sal > (select avg(sal) from emp ) ; # from 后面嵌套子查询 # 找出每个部门平均工资的工资等级 -- 先找出每个部门的平均工资 select deptno, avg(sal) from emp group by deptno ; -- 方法一： select a.* , s.grade from ( select deptno, avg(sal) avgsal from emp group by deptno) a join salgrade s on a.avgsal between s.losal and s.hisal ; -- 方法二： select a.* ,(select s.grade from salgrade s where a.avgsal between s.losal and s.hisal ) as grade from ( select deptno, avg(sal) avgsal from emp group by deptno) a ; # 找出每个部门平均的薪水等级 -- 先找出每个员工的工资等级 select e.ename , s.grade , e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno ; -- 然后在分组求平均值 select a.deptno, avg(a.grade) from (select e.ename , s.grade , e.deptno from emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno ) a group by a.deptno ; -- 效率低 ，没必要 -- 直接在第一步后面进行分组 select e.deptno , avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno ; # select 后接嵌套子查询 # 找出每个员工所在部门的名称 select e.ename , d.dname from emp e join dept d on e.deptno = d.deptno ; select ename , (select d.dname from dept d where e.deptno = d.deptno ) as dname from emp e ;# union (可以将查询结果拼接) # 找出工作岗位是salesman 和 manager 的员工 # 方法一： select ename , job from emp where job in ('salesman','manager') ; # 方法二： select ename , job from emp where job = 'salesman' or job = 'manager' ; # 方法三： select ename , job from emp where job = 'salesman' union select ename , job from emp where job = 'manager' ;/* 分页查询 MySQL特有 select...from...where...group by...order by...limit limit M,N 。N表示每页要显示多少条 ，M表示，起始索引值，从第几条开始显示 */ # 取出工资前5名员工 select ename , sal from emp order by sal desc limit 5 ; select ename , sal from emp order by sal desc limit 0 , 5 ; # 找出工资早第4名到第9名的员工 select ename , sal from emp order by sal desc limit 3 , 6 ; -- (4-1,9-(4-1)) # 通用的标准分页SQL /* 每页显示pageSize条记录 (页数-1)*pageSize, pageSize */
数据表的操作
数据表的创建
1 use mydb ; 2 create table 表名 ( 字段1 数据类型 约数条件 comment '' , -- comment '添加注释内容' 字段2 数据类型 约数条件 comment '' , 字段3 数据类型 约数条件 comment '' , ..... -- 注意：最后一个字段不需要用半角',' ) ; # 1 2 合并写法 create table mydb.表名 ( ...... ) ;查看数据库下的所有数据表
\u mydb show tables ; -- 查看表面中带有xxx的数据表 show tables like '%xxx%';查看数据库中所有表的详细信息
show table from 库名 \G -- 查看数据表名中含有xxx的数据表的详细信息 show table status from 库名 like '%xxx%'\G查看表结构字段信息
describe 表名 ; desc 表名 ; # 查看表结构的某个字段 desc 表名 字段名 desc 数据库名.数据表名 字段名 show columns from 数据表名
查看表结构的详细信息
比 [desc 表名 ]更详细的显示表结构 【 Collation（字符集）， Privileges（权限），Comment（注释）】
show full columns from 数据表名 [from 数据库名] ; show full columns from 数据库名.数据表名 ; -- show full columns from mydb.emp ; -- show full columns from emp from mydb ;
查看数据表的创建语句
show create table 表名 \G
修改数据表
修改表表名
alter table 旧表名 rename [to|as] 新表名 -- 可同时修改多个表名 rename table 旧表名1 to 新表名1 , [ 旧表名2 to 新表名2] ...修改表选项
数据中的表选项【字符集】，【存储引擎】，【校对集】也可通过 【alter table】修改
alter table 表名 表选项 = 值 ; # 字符集 charset -- # 存储引擎 Engine -- # 校对集 Collation --修改表字段名
alter table 数据表名 change [column] 旧字段名 新字段名 字段类型 [字段属性] ;修改字段类型
alter table 数据表名 modify [column] 字段名 字段类型 [字段属性] ;修改字段位置
alter table 表名 modify [column] 字段名1 数据类型 [字段属性] [first|after] 字段名2 ;-- first 后面不用接字段，表示第一个新增字段
# 语法1 新增一个字段并指定其位置 alter table 数据表名 add [column] 新字段名 字段类型 [first|after] 字段名 ; # 语法2 新增多个字段 alter table 数据表名 add [column] (新字段1 字段类型1 , 新字段2 字段类型2 , ... ) ;删除字段
alter table 数据表名 drop [column] 字段名 ;删除数据表
drop [temporary] table [if exists] 数据表1 , 数据表2 ,... ;
数据操作
插入数据
# 多行插入 insert into 数据表(字段1, 字段2, ...) [values|value] (数据1, 数据2, ...) , (数据1, 数据2, ...) , ... (数据1, 数据2, ...) ; -- 注意： /* 1 插入数据时 字段数应该与数据数等对，否则会报错！。 2 在 配置MySQL环境时若未指定字符集，并且创建数据表时也未指定字符集时，若在插入的数据中包含中文，则会出现插入错误提示！因为数据表默认使用的字符集是 latinl. 解决办法： 1 在配置mysql环境时便在my.ini 文件中指定默认的字符集。 2 在创建数据表时就指定默认字符集 create table 表名 (...)default charset utf8 ; 3 可直接在表中的某一字段指定默认字符集 create table 表名 (...字段名 字段类型[字段属性] character set utf8 , ... ) ; */ # my.ini 文件 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 [mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=D:\MySQL\mysql-8.0.28-winx64\ # 设置mysql数据库的数据的存放目录 datadir=D:\MySQL\mysql-8.0.28-winx64\data # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB # 允许最大连接数 max_connections=200 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8mb4 # 默认使用“mysql_native_password”插件认证 default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8mb4修改数据
update 数据表 set 字段1=值1 , 字段2 = 值2 , ... where 条件 ;删除数据
delete from 数据表 where 条件 ; truncate table 表名 ; # 不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。 效率上truncate比delete快，但truncate删除(截断)后不记录mysql日志，不可以恢复数据。 delete的效果有像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。
数据类型与约束
表的数据类型
常见数据类型：
类型 字节 取值范围 int 4 整数型 (java中的int) bigint 8 长整型 (java中 long) float 单精度浮点型 (java中 float) double 双精度浮点型 (java中 double) char 定长字符串 (java中String) varchar 可变长字符串 (StringBuffer/StringBuilder) data 日期类型 (java.sql.Date) BLOB 二进制大对象 (存储图片，视频等媒体信息)(object) CLOB 字符大对象(存储较大文本) (object)整数类型
数据类型 字节数 无符号取值范围 有符号取值范围 tinyint 1B=8b [0~2^8-1] [-2^7~2^7-1] smallint 2 mediumint 3 int 4 bigint 8 # 注意：默认都是有符号数，无符号数需要在数据类型右边加上unsigned关键字修饰 (id int unsigend) # 数据的插入一定要在数据类型的取值范围之内，否则会失败！ # 显示宽度 : 显示是的位数 (与取值范围无关) int(4) : 有符号表示1位符号位3位数值位 若数值位数小于显示宽度，会自动补全空格，大于时不影响显示结果 小于时可以设置零填充使用zerofill关键字修饰 int(4) zerofill : 数值位数小于4位时，如1 会以 0001 显示。 注意：设置零填充会自动设为无符号类型(负数不能使用零填充)。 # 表的主键推荐使用整数类型， 整数类型处理效率更高，查询速度更快 # 当插入的数据类型与字段的数据类型不一致，或使用 alter table 修改数据类型时，MySQL会尝试尽可能的将现有的值转换为新类型。例如：字符串'123','-123','1.23'与数字可以相互转换，但需要注意：1.5转换位整数时会被四舍五入结果位2.浮点数类型
数据类型 字节数 负数取值范围 非负数取值范围 float 4 double 8 # unsigned 修饰 # 精度问题：定点数类型
# decimal(M,D) M表示数字总位数不包括('.'和'-') 范围(0~65] default = 10 ; D表示小数点后的位数 (0~30] default = 0 ; #例如： decimal(5.2) 表示[-999.99~999.99] # 会自动根据存储的数据分配存储空间 # 可使用unsigned修饰 # 插入的小数部分超出范围时会四舍五入并发出警告 ，超出导致进位并使整数部分也超出范围，这是会插入数据失败！ # 查看警告 [show warnings] # 浮点数类型也可以设置位数和精度，如floal(8,2),但能有可能损失精度。 # 对于小数类型设置，推荐使用定点数类型BIT类型
# BIT(M) 存储二进制数 M表示二进制位数 ，该类型字段插入的数字会转换为二进制保存 # 函数 ASCII() :获取字符的ascii码 BIN() :将十进制转换为二进制 LENGTH() :计算二进制长度 b' 二进制 ' :将二进制转换为ascii码 # select length(bin(ascii('A'))); -- 求出字母A的二进制长度MySQL中的直接常量
# 十进制数科学计数 : 123 = 1.23E2 , 1.23 = 123e-2 ; # 二进制数表示 ：b'1000001' ; # 十六进制数表示 : x'41' / 0x'41' # 布尔值 : true = 1 ,false = 0 ; # 空值 : NULL ;MySQL常用转义字符
\0 空字符NUL \r 回车符CR \n 换行符LR \t 制表符HT \b 退格BS \' 单引号 \" 双引号 \\ 反斜杆 \% 常用于like条件 \_ 常用于like条件时间和日期类型
# year 类型 # date 类型 # time 类型 # datetime 类型 # timestamp 类型字符串类型
# char # varchar # text # enum # set # binary # varbinary # blobjosn 数据类型
表的约束
约束的作用: 对表中的数据进行限制，保证数据的正确性、有效性、完整性。违反约束的不正确数据，将无法插入到表中默认约束(default+默认值)
# 设置字段初始默认值 # 添加默认约束 1 创建表时在字段类型后添加默认约束属性 create table 表名(...字段名 数据类型 default 值,...) ; 2 使用 alter table 修改列表属性进行添加 alter table 表名 modify 字段名 数据类型[属性] default 值 ; # 删除默认约束 1 使用 alter table [修改/添加]列表[属性/名字/类型]进行删除 alter table 表名 modify 字段名 数据类型[属性] ;非空约束(not null)
# 保证字段不能为空 # 添加默认约束 1 创建表时在字段类型后添加默认约束属性 create table 表名(...字段名 数据类型 default 值 not null ,...) ; 2 使用 alter table 修改列表属性进行添加 alter table 表名 modify 字段名 数据类型[属性] default 值 not null ; (注意: 若目标已经保存了null值,此时添加非空约束会失败,只需将null值修改即可) # 删除默认约束(利用修改去掉该属性) 1 使用 alter table [修改]列表[属性/名字/类型]进行删除 alter table 表名 modify 字段名 数据类型[属性] ;唯一约束(unique)（创建索引）
特点：唯一 ，可以为null 。
# 保证该字段具有唯一性但是可以为null # 列级约束/表级约束 表级约束:对多个数据列建立的约束，只能在列定义后声明 列级约束:对一个数据列建立的约束，既可以在列定义时声明，也可以在列定义后声明 ps:简单来理解的话就是列级约束针对的是单列,表级约束针对的是整个表 非空和默认约束不能使用表级约束添加。 # 添加唯一约束 1 列级约束添加 create table 表名(...字段名 数据类型 [default 值] [not null] unique ,...) ; 2 表级约束添加(所有字段之后添加) create table 表名(...字段名 数据类型 [default 值] [not null] ...unique(字段名),... ) ; -- 必须要有字段名 3 使用 alter table 添加列表进行添加 alter table 表名 add unique(字段名) ; # 删除唯一约束 1 使用 alter table 删除列表进行删除 alter table 表名 drop index 字段名 ; # 添加唯一复合约束 只有多个字段的值相同才被视为重复记录！ 1 create table 表名(...字段名 数据类型 [default 值] [not null] ...unique(字段名1,字段名2,...),... ) ;主键约束（primary key）
特点：不重复、唯一、非空
# 保证该字段具有非空且唯一性，一张表中只能有一个主键，主键是表中字段的唯一标识 # 添加唯一约束 1 列级约束添加 create table 表名(... 字段名 数据类型 [default 值] [not null] [unique] primary key , ...) ; 2 表级约束添加(所有字段之后添加) create table 表名(...字段名 数据类型 [default 值] [not null] ...[unique(字段名)] primary key(字段名) ,... ) ; -- 必须要有字段名 3 使用 alter table 添加列表进行添加 ALTER TABLE 表名 ADD PRIMARY KEY(eid); # 删除唯一约束 1 使用 alter table 删除列表进行删除 alter table 表名 drop primary key ; # 添加复合主键约束 1 create table 表名(...字段名 数据类型 [default 值] [not null] ...[unique(字段名1,字段名2,...)] primary key(字段名1,字段名2,...) ,... ) ; # 业务主键 : 最好不要用使用与业务挂钩的字段作为主键 # 自然主键 : 与其它字段没有任何业务挂钩的主键,业务改变，主键不受影响。外键约束
# 外键是指：在一个表中引用另一个表中的一列或多列。 # 被引用的键应该具有主键约束或唯一约束，从而保证数据的一致性和完整型 # 添加外键： 1 create table 表名( 字段名1 字段类型 [字段属性] [约束]..., ... [约束primany key(字段)/uniqe(字段)...] ... foreign key (从表字段) references 主表名(主表字段) -- 添加外键 )engine = 存储引擎 default charset = 字符集 ; 2 alter table 时添加外键约束 alter table 数据库.表名 add foreign key (从表字段) references 主表名(主表字段) # 添加外键基本语法 [constraint 外键约束名称] foreign key [外键索引名称] (外键字段/字段1,字段2,...) references 关联主表名(主表字段/字段1,字段2,...) [on delete { restrict| cascade| set null| no action | set default}] [on update { restrict| cascade| set null| no action | set default}] # 参数说明 restrict 默认值，拒绝主表删除或修改外键关联字段 cascade 主表删除或更新记录时，同时自动删除或更新从表中的对应记录 set null 主表删除或更新记录时，使用null值替换从表中对应的记录(不适用于非空约束字段) no action 与默认值效果相同 set default 设默认值 ， 但现在innoDB引擎 目前不支持。 # 查看外键约束 desc 数据库.表名 外键字段 ; Key (索引)值为MUL (表示非唯一性索引（MUL tiple key）) 表示可重复 在创建外键约束时MySQL会自动为没有所有的外键字段创建索引 # 数据的插入与更新 从表外键字段插入的值必须选取主表相关联字段已经存在的数据 # 设置外键的优势与劣势 优势： 节省开发量 能约束数据的有效性，防止非法数据的插入 劣势： 会带来额外的开销 主表被锁，会引发从表也会被锁 删除主表数据时先删除从表数据 含有外键约束的从表字段不能修改表结构 # 删除外键约束 alter table 数据库.数据表 drop foreign key 外键名 ; 注意： 删除外键约束的字段后其Key值仍为MUL 因为删除外键约束并不会自动删除系统自动创建的普通索引。 若要一起删除 alter table 数据库.数据表 drop key 外键名 ;自动增长
# 防止插入主键值时因插入重复问题而多次检查并因重复而插入失败。 # 一个表中只能有一个自动增长字段，且必须为整数类型，且该字段必须定义为 unique key 和 peimary key # 自动增长字段插入 NULL , 0 , default , 或省略时, 则该字段会启动自动增长 ，插入具体值时不会启动自动增长 # 自动增长从1开始，插入的数大于1 时,下一个自动增长值会启用最大值+1,小于没影响。 # 使用delete 删除记录时不会影响自动增长值。 # 添加自动增长 1 create table 表名(...字段名 数据类型 [default 值] [not null] [unique] primary key auto_increment ...) ; 2 使用 alter table 修改列表添加 ALTER TABLE 表名 modify 字段名 字段类型[属性] auto_increment ; # 删除自动增长 1 使用 alter table 修改列表进行删除 alter table 表名 modify 字段名 字段类型[属性] ; # 查看自动增长值 show create table 数据表 \G # 修改自动增长值 alter table 数据表 auto_increment = 10 ; 修改的值若小于该列现有最大值时,修改不会生效。 # 删除在重新添加自动增长 , 其自动增长值 会从该列现有最大值+1 # 通过show variables like '%auto_increment%' 可以查看MySQL 中用于维护自动增长的变量分别是auto_increment_increment(默认值1)和auto_increment_offset(默认值1),通过这两个变量可以改变自动增长的计算方式。字符集与校对集
事务 （transaction）
1 事务处理
保证在同一个事务中的操作具有同步性1.1 事务的概念
概念：事务就是针对数据库的一组操作，它可以有一条或多条SQL语句组成且每一个语句都相互依赖的， 只要程序执行过程中有一条SQL 语句执行失败或发生错误，则其他语句都不会执行。 事务在生活中的应用： 例如，生活中的转账操作就是一组事务，转账1000过程分为 转出(updata 数据表 set 金额=10000-1000 where 账户 = '10111') 转入(updata 数据表 set 金额=0+1000 where 账户 = '10222') 上面的两SQL语句就必须用事务处理，从而保证数据的同步， 否则会出现钱转出去却在转入时发生错误导致钱没到账，这换做谁谁会乐意啊！1.2 事务的基本特征（ACID）
1、原子性（Autmic） 就是指事务的SQL命令要么全部完成，要么全部不完成， 不允许事务中的SQL命令部分执行的情景。 2、一致性（Consistency） 就是值事务必须使得数据库从一个状态转变到另一个状态， 一致性的描述，从某种程度上而言，是原子性的另一个角度的描述。 3、隔离性（Isolation） 是指当有多个事务并发执行的时候，事务之间的执行应该相互隔离， 不能相互干扰，这些操作在MySQL中通过锁来实现。 4、持久性（Durability） 是指一个事务在提交后，对数据库中数据的改变是永久性的， 其他的故障不应该对事务操作的数据有影响。1.3 事务的基本操作
# 建表 drop table if exists shop ; create table shop ( name varchar(12) , money double(12,2) ); insert into shop values ('Alex' ,1000.00), ('Bill' ,1000.00); select * from shop ; # 开启一个事务进行转账模拟，账户Alex向账户Bill转账100.00 -- 开启事务 start transaction ; -- 更新数据 update shop set money=money-100 where name = 'Alex' ; update shop set money=money+100 where name = 'Bill' ; -- 提交事务 commit ; select name , money from shop ; # 测试事务回滚(在未提交事务之前可以回滚) start transaction ; update shop set money=money-100 where name = 'Bill' ; select name ,money from shop where name = 'Bill' ; -- 回滚事务 rollback ; select name ,money from shop where name = 'Bill' ;1.31 MySQL 事务不允许嵌套
1.32 MySQL 默认是自动提交模式（前提是没有开启事务）
# 查看当前autocommit值 select @@autocommit ; # 关闭会话自动提交 set autocommit = 0 ; 注意：关闭操作后，一定要记住手动执行提交操作， 否则若终止MySQL会自动进行回滚操作 在开启会话自动提交时： 开始事务操作后还未提交突然异常退出MySQL，MySQL会自动回滚 若前一个事务还未提交进入下一个事务，前一个事务会隐式的执行提交操作1.4 事务的保存点
select name , money from shop where name = 'Alex'; # 设置保存点 -- 开启事务 start transaction ; update shop set money = money -100 where name = 'Alex' ; -- 创建保存点 savepoint s1 ; update shop set money = money -50 where name = 'Alex' ; select name , money from shop where name = 'Alex'; -- 查看金额750 -- 回滚到保持点s1 rollback to savepoint s1 ; select name , money from shop where name = 'Alex'; -- 查看金额800，说明回滚成功！ -- 再次回滚 rollback ; -- 金额900 # 注意： 1 一个事务可以创建多个保存点！ 2 事务只要提交了保存点就会被删除！ 3 回滚到某个保存点后，该保存点之后创建的保存点就会消失 ！1.41 控制事务结束后的行为
commit [and [no] chain] [[no] release] rollback [and [no] chain] [[no] release]1.5 事务隔离
数据库是一个多用户共享资源，允许多线程并发访问，因此用户可以通过不同的线程执行不同的事务，这时事务隔离就保证了事务之间不受影响！
1.51MySQL四种隔离级别
1 read uncommitted 读取未提交 2 read committed 读取提交 3 repeatable read 可重复读 4 serializable 可串行化# read uncommitted
级别最低 ，该级别下的事务可以读取到其他事务未提交的数据 (脏读 Dirty Read)
# 演示脏读
# 演示脏读 # 1 设置B 的隔离级别 set session transaction isolation level read uncommitted ; select name , money from shop where name = 'Bill'; -- 金额1100 -- A 开启事务并进行转账操作 start transaction ; update shop set money=money-100 where name = 'Alex' ; update shop set money=money+100 where name = 'Bill' ; -- 此时A 未提交 ，B查看金额，金额已到账！进行发货 select name , money from shop where naame = 'Bill'; --金额1200 -- A 收到货后进行了不道德操作进行回滚！从而导致B受损 # 3 避免脏读设置更高级别的事务隔离 select session transaction isolation level read committed select name , money from shop where naame = 'Bill'; -- 金额1100，避免了脏读！
# read committed
是大多数DBMS（SQL Service ，Oracle ）的默认隔离级但不包括MySQL
只能读取其他事务已提交的数据，避免了脏读 ，但会出现不可重复读（non-repeatable read）问题，不可重复都是指在一个事务中多次查询结果不一致。例如：在网站后台统计所有用户的总金额，第一次查询Alex有900，为了验证查询结果 ，第二次查询有800 ，两次查询结果不同，原因是在第二次查询前 Alex就已经再次转出100。
# 不可重复问题演示
# 不可重复问题演示 -- 客户端B 隔离等级修改为 read committed set session transaction isolation level read committed ; start transaction ; -- 注意是B开启事务 select name , money from shop where name = 'Alex'; -- 第一次查询 900 -- 客户端A 更新数据 update shop set money=money-100 where name = 'Alex' ; -- 客户端B再次查询 select name , money from shop where name = 'Alex'; -- 第二次查询 800 commit ; # 问题 出现了不可重复读， 在将隔离事务级别设置为默认级别 repeatable read 就可避免 -- 再次执行下面代码 # B set session transaction isolation level repeatable read ; start transaction ; select name , money from shop where name = 'Alex'; -- 800 # A update shop set money=money-100 where name = 'Alex' ; # B select name , money from shop where name = 'Alex'; -- 在这个事务中还是800 commit ;
# repeatable read
MySQL默认事务隔离级，解决了脏读和不可重复读，确保了同事务的多个实例在并发读取数据时会看到相同的结果。
但理论上该级会出现幻读（phantom read）的现象，又称虚读，不过MySQL的InnoDB存储引擎通过多版本并发控制机制已经解决了幻读问题，所有这里就不多介绍，需要了解点击☞
# 修改隔离事务级别为repeatable read (可重复读) set session transaction isolation level repeatable read# 幻读演示
# serializable
最高级别隔离等级，在每个读的数据行上加锁，使之不会发生冲突，从而解决脏读，不可重复读，幻读。
加锁会导致超时（Timeout）和锁竞争（Lock Contention）现象，因此是性能最低的一个事务隔离级
# 超时演示
-- 客户端B执行查询操作 A 执行更新操作
# B set session transaction isolation level serializable ; start transaction ; select name , money from shop where name = 'Alex';# A update shop set money = money +100 where name = 'Alex' ; (此处有光标不停的闪烁，进入等待状态)
若客户端B一直为提交事务，客户端A会一直处于等待状态，直到超时 ，默认时间为50,查询语句如下
select @@innodb_lock_wait_timeout ;
# 客户端B提交事务
commit ;
1.6 查看隔离级别
-- 查看全局隔离级 select @@global.transaction_isolation ; -- 影响所有连接MySQL的用户 -- 查看当前绘画隔离级 select @@session.transaction_isolation ; -- 只影响当前正在登入MySQL服务器的用户 -- 查看下一个事务的隔离级 select @@transaction_isolation ; -- 仅对当前用户的下一个事务操作有影响 -- 上述默认返回的都是repeatable-read (可重复读)1.7 修改事务隔离等级
set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable] -- session 表示当前会话 -- global 表示全局 -- transaction 表示事务 -- isolation 表示隔离 -- level 表示级别
数据库优化
1 存储引擎
数据表存储数据的一种格式
MySQL数据库服务器的底层组件之一，采用"可插拔"的存储引擎构架
1.1查看当前MySQL版本支持那些存储引擎
# 查看MySQL版本 select version(); # 查看当前版本支持哪些存储引擎 show engines ;
1.2 InnoDB 存储引擎
1 MySQL默认存储引擎 2 具有高性能和高可靠性 3 具有提交，回滚，崩溃恢复的事务处理能力 4 具有提高多用户并发处理的能力和维护数据完整性 5 适合业务逻辑较强，修改操作较多的项目1.2.1存储格式
2 索引
2.1 索引的概述
索引相当于一本书的目录，通过目录可以快速定位指定资源的数据的位置# 索引分类
1 普通索引: 使用Key 或index 定义 2 唯一索引: 使用unique index 定义 防止用户添加重复的值 3 主键索引: 使用primary key 防止添加的主键索引的字段值重复或为null 4 全文索引: 使用fulltext index 定义 用于根据查询字符提高数据量较大的字段查询速度，字段类型必须为：char,varchar,text的一种。 5 空间索引: 使用spatial index 定义 该创建的索引字段不能为空2.2 检索的两种方式：
1 全表扫描 2 索引检索(效率高)# 注意：索引不能随意添加，因为索引也是数据库中的对象，也需要数据库的不断维护，数据经常被修改就不适合添加索引，因为数据一旦修改索引就需要重新排序，进行维护。
2.3 满足添加索引的条件
1 数据量庞大 2 该字段很少执行DML语句操作 3 该字段经常出现在where子句中# 注意：具有主键约束和unique约束的字段会自动添加索引
2.4 查看DQL 语句的执行情况
explain DQL语句 ;2.5 创建和删除普通索引
1 create index 索引名称 on 表名(字段名) ; -- 创建字段索引 drop index 索引名称 on 表名 ; -- 删除字段索引 2 alter table 表名 add index 索引名称(字段名) 3 create table 表名( 字段1 ... , ... key 索引名称(字段名) );# 执行DQL语句
select ename , sal from emp where sal = 5000 ;
# 查看DQL语句执行情况
explain select ename , sal from emp where sal = 5000 ;
# 给薪资sal添加索引
create index emp_sal_index on emp(sal);
# 再次查看执行情况
explain select ename , sal from emp where sal = 5000 ;
# 删除sal索引
drop index emp_sal_index on emp ;
2.6 索引实现原理
# 底层采用的数据结构是：B + Tree# 创建唯一索引
alter table 表名 add unique index 索引名(字段名) ;# 全文索引
alter table 表名 add fulltext index 表名(字段名) ;# 空间索引
create table 表名(...space ceometry not null); --创建表示保证非空 alter table 表名 add spatial index(space) ;# 复合索引
alter table 表名 add index 索引名(字段1,字段2,...) -- 要遵循最左前缀原则(吧使用最频繁的字段放在最左边)# 前缀索引
# 完整索引语法格式
# create table 创建数据表时添加索引 create table 数据表( 字段1 数据类型 [约束条件] ... primary key [索引类型](字段列表) [索引选项] , {index|key} [索引名称] [索引类型](字段列表) [索引选项], unique [index|key] [索引名称] [索引类型] (字段列表) [索引选项] , {fulltext|spatlal} [索引名称] (字段列表) [索引选项] )[表选项]; # alter table 向已经创建数据表添加索引 alter table 数据表 add primary key [索引类型](字段列表) [索引选项] |add {index|key} [索引名称] [索引类型](字段列表) [索引选项] |add unique [index|key] [索引名称] [索引类型] (字段列表) [索引选项] |add fulltext {index|key} [索引名称] (字段列表) [索引选项] |add spatlal {index|key} [索引名称] (字段列表) [索引选项],...; # create index 向已创建的数据表添加索引 create [unique|fulltext|spatial] index 索引名称 [索引类型] on 数据表名 (字段列表)[索引选项][算法选项|锁选项] ;
快捷键
ctrl + Y 删除一行
ctrl + / 单行 注释
ctrl + shift + / 多行注释
/** + enter 注解
ctrl + shift +alt 获取代码块或多行同时操作
ctrl + shift + L 代码块整理
表的复制
1、复制表结构及数据到新表
CREATE TABLE 新表SELECT * FROM 旧表这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用"alter"添加，而且容易搞错。
2、只复制表结构到新表
CREATE TABLE 新表SELECT * FROM 旧表WHERE 1=2 或CREATE TABLE 新表LIKE 旧表3、复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表SELECT * FROM 旧表4、复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表5、可以将表1结构复制到表2
SELECT * INTO 表2 FROM 表1 WHERE 1=26、可以将表1内容全部复制到表2
SELECT * INTO 表2 FROM 表17、 show create table 旧表;
这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来，更改table的名字，就可以建立一个完全一样的表
8、mysqldump
用mysqldump将表dump出来，改名字后再导回去或者直接在命令行中运行
9、复制旧数据库到新数据库（复制全部表结构并且复制全部表数据）
#mysql -u root -ppassword >CREATE DATABASE new_db; #mysqldump old_db -u root -ppassword--skip-extended-insert --add-drop-table | mysql new_db -u root -ppassword10、表不在同一数据库中（如，db1 table1, db2 table2)
sql: insert into db1.table1 select * from db2.table2 (完全复制) insert into db1.table1 select distinct * from db2.table2(不复制重复纪录） insert into tdb1.able1 select top 5 * from db2.table2 (前五条纪录)
问题：
PRI主键约束；UNI唯一约束；MUL可以重复。
ERROR 3780 (HY000): Referencing column 'name' and referenced column 'studentID' in foreign key constraint 'FK_NAME' are incompatible.
原因： 外键字段的约束条件与对应的主键约束条件不一致
1 mysql> alter table new_address add constraint FK_P foreign key (paiming) references achievement(studentID)
-> on delete restrict on update cascade;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`addressbook`.`#sql-1234_1d`, CONSTRAINT `FK_P` FOREIGN KEY (`paiming`) REFERENCES `achievement` (`studentID`) ON DELETE RESTRICT ON UPDATE CASCADE)2 mysql> alter table new_address add constraint FK_NUM foreign key (num) references achievement(studentID)
-> on delete restrict on update cascade;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`addressbook`.`#sql-1234_1d`, CONSTRAINT `FK_NUM` FOREIGN KEY (`num`) REFERENCES `achievement` (`studentID`) ON DELETE RESTRICT ON UPDATE CASCADE)3 mysql> alter table new_address add constraint FK_SID foreign key (studentID) references achievement(studentID)
-> on delete restrict on update
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
