MySQL
Maven服务器—网站
maven私服的网站 maven.tedu.cn
maven外网 maven.aliyun.com
达内开发文档—网站
达内内部网 doc.tedu.cn
外网 doc.canglaoshi.org
达内笔记—网站 code.tarena.com.cn
账号 tarenacode
密码 code_2013
- MySql语句
- 登录小黑框 mysql –uroot -proot
- 小黑框中文乱码
配置MySQL跟目录文件my.ini中的
default-character-set=utf8
character-set-server=utf8
Mysql小黑框中文显示乱码
show variables like ‘char%’; 查询所有输出窗口的字符编码
set character_set_results=gb2312; 把字符编码改成gb2312;
gb2312相当于gbk
MySql小黑框中文显示乱码和显示数据不整齐
mysql --default-character-set=gbk -uroot –pxxxx 每次登陆mysql输入 - 基本操作语句
退出小黑框 exit
查询有哪些数据库 show databases;
进入其中一个数据库(使用数据库) use 数据库名;
查询数据库中有哪些表 show tables;
查看数据库详情 show create database 数据库名;
查看数据表详情 show create table 数据表名;
查看表字段信息 desc 数据表名;
导入文件 source E:/523/Praction-SQL/tables.sql; - 数据库
创建数据库之前,先判断是否有这个数据库(如果有则删除,没有不报错)
drop database if exists mydb1;
创建数据库语句(并设置字符集) create database 数据库名 character set utf8/gbg;
charset相当于character set
删除数据库 drop database 数据库名 - 数据表
创建数据表
普通创建 create table 数据表名(表的列名)
创建表字符集 create table 表名(列名)engine=myisam charset=utf8/gbk;
添加数据
单条数据添加(且按照表中顺序插入) insert into 表名 values(要插入的数据,逗号隔开)
指定字段插入 insert into 表名(要插入数据的字段) values(和指定的字段一一对应)
批量插入数据 insert into 表名 values(插入的数据),(插入的数据);
修改数据
修改数据表名 rename table 原名 to 新名;
修改表引擎和字符集 alter table 表名 engine=innodb/myisam charset=utf8/gbk
innodb：支持数据库的高级操作如事务、外键等
myisam：不支持数据库的高级操作，仅支持数据基础的增删改查操作
添加表字段
最后添加格式 alter table 表名 add 字段名 字段类型;
最前添加格式 alter table 表名 add 字段名 字段类型 first;
某个字段的后面 alter table 表名 add 字段名 字段类型 after 字段名;
修改表字段名称和字段类型 alter table 表名 change 原字段名 新字段名 新字段类型;
修改表字段名的类型和位置 alter table 表名 modify 字段名 新类型 first/after 位置;
修改网络传输的字符集 set names gbk/utf8;
修改数据 update 表名 set 修改的字段=修改的内容 where 修改的条件
Age=Ifnull(x,y) 如果x的值为null则age=y,如果不为null则age=x
update emp set comm=ifnull(comm,0);
删除数据
删除数据表 drop table 数据表名
删除表字段 alter table 表名 drop 字段名;
删除主键 alter table 表名drop primary key
删除达到条件的数据 delete from 表名 where = 条件;
删除表并创建新表(自增数值清零,还是以前的字段) truncate table 表名不变;
truncate：删除表的数据，但是表的结构不变，比如id自增，那么执行完truncate后id仍从初始值开始，就像把表删除后重现建了一个表结构相同的表
delete：直接删除表中的所有数据，还拿id举例子，此时的id就不会从初始值开始，而是继续删除前的增加
drop：直接删除表，这个比较好理解，表结构和表数据全都删除
相同点：
truncate和不带where条件的delete，以及drop都会删除表中的所有数据
drop和truncate都是DDL（数据库定义语言）执行后会自动提
不同点：
删除的内容
truncate和delete只删除表数据，不删除表的结构
drop会删除表的结构、依赖约束、触发器、索引，并且表的额存储过程和函数会保留，变为invalid状态
是否立即生效
delete是数据操作语言（DML），这个操作会放到rollback segement中，事务提交后才会生效，如果有响应的trigger，执行的时候才会生效
truncate和drop是数据库定义语言（DDL），操作立即生效，源数据不会放到rollback segement中，不能回滚，操作不触发trigger
占用空间
truncate后，表和索引所占的空间会恢复到初始大小
delete后，不会减少表和索引所占空间的大小
drop后，表和索引所占的空间全部释放
速度
drop>truncate>delete
安全性
慎用drop和truncate，尤其是没备份的情况下
删除部分数据使用delete+where，并且回滚段要足够大
删除表直接用drop
删除表数据不删表结构，如果和事务没有关系就使用truncate，如果和事务有关或者想触发trigger就使用delete。
查询数据
- select … from 表名 where … group by … having… order by… limit… ;
查询表中所有数据 select * from 表名;
带条件查询 select * from 表名 where 字段名=/>/< 条件;
查询某字段是否为空 (is null)是空 (is not null)不是空
select * from 表名where 字段名 is null;
为查询字段起个名 as ‘名称’
select name as ‘姓名’ from emp;
查询时,去掉某字段里的重复数据 distinct
select distinct 字段 from 表名;
and 和Java中的&&效果相同 or和Java中的||效果相同
当查询某个字段为多个值时 in 在in前面加not是不在
select * from emp where sal in(5000,800,950);
select * from emp where sal not in(5000,800,950);
查询字段的数值在X和Y之间(包含XY) between X and Y 在between前面加not是范围之外
select * from emp where sal between 1000 and 2000;
select * from emp where sal not between 1000 and 2000;
模糊查询 like加引号 (代表一个未知字符,%代表多个未知字符)
以a开头 a%
以b结尾 %b
以X开头Y结尾 X%Y
包含abc %abc% 在like前面加not是不包含
第二个字母是b b%
第一个字母是m 倒数第三个是d m%d
查询并排序 order by 字段名 XX XX如果是desc为降序,是asc为升序,不写默认为升序
select * from 表名 order by 要排序的字段名 XX;
select * from emp order by deptno;
select * from emp order by deptno desc,sal desc; 可以同时使多个字段排序()
分页查询 limit X,Y; a是指这页第一条(页数-1)b,b是指一页多少条
select * from emp limit 4,2;
select * from emp order by sal desc limit 0,3;
查询平均数 avg(字段名)
select avg(字段名) from 表名 where 某个一组的字段=条件
查询最大值 max(字段名)
select max(字段名) from 表名;
查询最小值 min(字段名)
select max(字段名) from 表名;
查询总和 sum(字段名)
select sum(字段名) from 表名;
统计数量 count() count()是指总数
select count() from 表名;
分组查询 group by
select * from 表名group by 字段名;
多个字段分组查询,在group by 后面写多个字段
select deptno,mgr,count() from emp group by deptno, mgr;
where 后面只能写普通字段的条件,不能写聚合函数
having 和where类似,可以写普通函数,也可以写聚合函数,写在group by 后面
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
分组查询,且查询出组内信息 group_concat()
select group_concat (ename) from emp group by deptno;
group_concat(ename,’-’,sal) 可以把多个字段放到一行
子查询
可以在查询语句中嵌套另一条语句—可以嵌套多层 可以单表,也可以多表
子查询可以写在where或having后面
格式 select * from 表名 where 条件(select 详细查询 from表名);
distinct 去掉重复数据 in 多个数据同时比较
select * from dept where deptno in (select distinct deptno from emp);
在创建表的时候
格式 create table 表名 as (子查询)
create table 表名 as (select * from emp )
写在from后面当成一个虚拟表 必须起别名
格式 select 字段名 from (select * from 所要查的表名,后面可以跟条件) 别名;
关联查询
普通关联查询 用=连接俩个表的主键和外键
关联查询必须写关联关系,如果不写则得到俩张表的乘积,这个乘积称为笛卡尔积
工作中不允许出现,因为超级消耗内存,可能直接奔溃
格式 select a.字段名,b.字段名 from 表1 a,表2 b where a.的外键=b.的主键;
等值连接和内连接 查询的是俩张表的交集的部分
等值连接 格式 select * from A,B where A.x=B.x;
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
内连接 格式 select * from A join B on A.x=B.x where 条件;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
外连接 右外链接 左外链接
右外链接 select * from A right join B on A.x=B.x where 条件;
左外链接 select * from A right join B on A.x=B.x where 条件;
关联查询的查询方式包括三种： 等值连接、内连接和外链接
如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接
如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接只需要掌握一种即可
6) 约束
主键约束 primary key 非空且唯一
外键约束 保证数据的完整性,外键字段的值可以为空,可以重复,但是不能是关系表中不存在的数据,被依赖的数据不能先删除,被依赖的表不能先删除.
constraint 约束名 foreign key (本表外键字段名) references 外键的表(字段名);
自增约束 auto_increment
非空约束 not null
唯一约束 unique
字段注释 comment ‘注释’
默认约束 default ‘0’;
实现int型自动补零 zerofill
7**) 和’的区别**是修饰表名和字段名的 可以省略
‘是修饰字符串的
8) 数据冗余
如果表设计不够合理，当数据量增多时出现的大量重复数据，这种现象称为数据冗余，通过拆分表的方式解决冗余问题
9) 事务(用于解决有关系的多条语句—银行转账)
begin; 开始事务
savepoint xxx 设置回滚点
rollback 回滚事务
rollback xxx 回到回滚点
commit 提交事务
end; 结束事务
查看自动提交状态 show variables like ‘%autocommit%’;
开启事务的第二种方式 关闭自动提交,该为手动提交,作用和begin类似
set autocommit = X (X是0则关闭自动提交,X是1则开启自动提交)
事务是数据库中执行同一业务多条SQL语句的工作单元,可以保证全部执行成功,或全部执行失败
事务的ACID特性: 是保证事务正确执行的四大基本要素
原子性(Atomicty): 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性(Consistency): 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
隔离性(Isolation): 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性(Durability) 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
- SQL分类*
DDL (Data Definition Language)—数据定义语言
包括 create,drop,alter,trancate 不支持事物
DML (Data Manipulation Language)—数据操作语言
包括 insert,update,delete ,select 支持事物
DQL (Data Query Language)—数据查询语句
包括 select
TCL (Transaction Control Language)—事物控制语言
包括 begin,rollback,commit,savepoint s1,rollback to s1
DCL (Data Control Language)—数据控制语言
包括 涉及分配用户权限的SQL
11) 数据库的数据类型
整数
int对应java里面的int
bigint对应java中的long
int(m) m代表显示长度,需要结合zerofill使用
create table t_int(id int,age int(10) zerofill);
浮点数
double(m,d) m代表总长度,d代表小数长度 76.234 m=5 d=3
decimal超高精度浮点数，涉及超高精度运算时使用
字符串
char(m): 不可变长度字符串 m=10 “abc” 所占长度为10 ，执行效率略高
varchar(m):可变长度字符串 m=10 “abc” 所占长度3 好处是节省空间 最大长度为65535，但是建议长度不要超过255 超过255建议使用text
text(m):可变长度字符串 m=10 “abc” 所占长度3 最大长度65535
日期 插入数据是用引号引起来
date：只保存年月日
time：只保存时分秒
datetime：年月日时分秒 默认值null 最大值9999-12-31
timestamp：年月日时分秒 默认值当前时间 最大值2038-1-19，以时间戳的形式保存时间
Oracle中的数据类型。
number表示数字类型,经常被定义成NUMBER(p,s)形式,定义中的P表示数字的总位数,S表示小数点后面的位数
char表示固定长度的字符类型,经常被定义成CHAR(N)形式,N表示占用的字节数,最大长度是2000字节
varchar2表示变长的字符类型，定义格式是VARCHAR2(N),N表示最多可占用的字节数,最大长度是4000字节 - 数值计算 + - * / % mod(a,b)是取余数
13) 日期相关的函数
获取当前日期加时间 select now();
把时间戳修改为具体的时间 FROM_UNIXTIME(date)
把具体时间修改成时间戳 UNIX_TIMESTAMP()
获取当前的年月日 select curdate(); cur是指current
获取当前的时分秒 select curtime();
把时间戳修改成具体的时间格式 from_unixtime(时间,“yyyy-MM-dd”)
计算时间差(得出的结果是天数) datediff(前日期,后日期)
从完整的年月日时分秒中提取出年月日时分秒 select date(now()),time(now());
从完整的年月日时分秒中提取时间分量 extract()
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
日期格式化date_format(日期,’格式’)
%Y 四位年 %y 俩位年
%m 俩位月 %c 一位月
%d 日
%H 24小时 %h 12小时
%i 分
%s 秒
select date_format(now(),’%Y年%m月%d %H点%i分%s秒’);
把自定义时间格式转回标准格式 str_to_date(自定义的字符串时间,自定义格式)
select str_to_date(‘2019.01.16 15.50.29’,’%Y.%m.%d %H.%i.%s’);
14) 字符串相关的函数
字符串拼接 concat(s1,s2)
select concat(字段名,’拼接的内容’) from 表名;
获取字符串长度 char_length(字段名)
select char_length(字段名) from 表名;
获取字符串在另一个字符串中的位置 instr(s1,s2) locate(s2,s1)
格式一 insert(str,aubstr) select instr(‘abcdef’,‘b’);
格式二 locate(aubstr, str) select locate (‘b’, ‘abcdef’);
插入字符串 insert(str,start,length,newstr)
select insert(‘abcdefg’,3,2,‘mm’);
转大写 转小写
select upper(‘abc’),lower(‘abc’);
去空白 trim()
select trim(’ a bc ');
截取字符串
从右边截取 select left(‘asdfghj’,2);
从左边截取 select right(‘asdfghj’,2);
任意位置截取
select substring(‘asdfghj’,2); 从2截取到最后
select substring(‘asdfghj’,2,3); 从2开始,长度为3
重复 repeat(str,count)
select repeat(‘dsadsas’,2);
替换 replace(str,old,new)
select replace(‘asdfgh’,‘d’,‘mm’);
反转 reverse(str)
select reverse(‘asd’);
nvl nvl函数为空值转换函数。参数的数据类型可以是数值、字符、日期
nvl（d1,d2）表示如果d1为null则用d2替代.
函数对null值的处理。组函数在计算时会忽略null值行
函数ceil和floor 去小数取值
CEIL(n) 一个是天花板，就是取大于或等于n的最小整数值，
FLOOR(n) 一个是地板，就是取小于或等于n的最大整数值 - 聚合函数
平均值avg 最大值max 最小值min 求和sum 计数count - 数学相关的函数
向下取整 floor(num)
select floor(3.14);
四舍五入 round(num)
只留整数 select round(23.8);
留几位小数 select round(23.879,2);
非四舍五入 truncate(num,m)
直接舍去多余的位数 select truncate(23.879,2);
随机数 rand() 0-1
产生0-1之间的数字 select rand();
获取0-5的整数随机数 select floor( rand()*6 ); - 视图
什么是视图: 数据库中表和视图都是其内部的对象，视图可以理解成是一张虚拟的表，视图本质就是取代了一段sql查询语句
为什么使用视图: 因为有些数据的查询需要书写大量的sql语句，每次书写开发效率太低，使用视图可以重用sql语句，可以隐藏敏感信息
创建视图格式 create view 视图名 as (子查询)
视图分类
简单视图 创建视图的子查询中不包含去重、分组查询、聚合函数、关联查询的视图称为简单视图,可以对视图中的数据进行增删改查操作
复杂视图 和简单视图相反，只能进行查询
插入数据
和表插入数据的格式一样
如果插入的数据在原表中显示,但是在视图中不显示,则称为数据污染
数据污染可以通过with check option 关键字解决 在建立视图的时候
防止数据污染的格式 create view 视图名 as (子查询) with check option;
删除和修改数据 只能操作视图中存在的数据
格式与表删除修改的格式一样
修改视图
创建或替换 如果不存在则创建,如果存在则替换
create or replace view 视图名 as (子查询);
删除视图
格式与表删除一样 drop view 视图名;
视图别名 如果创建视图时,子查询里面使用了别名,则视图操作时,只能使用别名
create view v_emp_20 as (select ename name,sal from emp where deptno = 20);
使用别名后,视图操作不能再使用原名 select ename from v_emp_20;
使用别名可以查询 select name from v_emp_20;
; - 索引
创建索引
格式 create index 索引名 on 表名( 字段名(字符长度) );
字符长度一般不用
查看索引
格式 show index from 表名;
给表添加主键约束,会自动根据主键字段创建索引
删除索引
格式 drop index 索引名 on 表名;
复合索引
通过多个字段创建索引 称为复合索引
格式 create index 索引名 on 表名( 字段1,字段2 );
索引总结
索引是数据库用来提高查询效率的技术,类似于目录
应为索引会占用磁盘空间不是越多越好,只针对常用的查询字段创建索引
数据量小的表,如果添加索引会降低查询效率,所以不是有索引就一定好
; - 序列的使用
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。
CURRVAL 中存放序列的当前值 ，
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效
1，脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下
update account set money=money+100 where name=’B’; (此时A通知B)
update account set money=money - 100 where name=’A’;
当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
2，不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……
3，虚读(幻读)
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
现在来看看MySQL数据库为我们提供的四种隔离级别：
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。
SQL优化
1）建索引
a.字段有大量重复的不要建索引
b.索引不是越多越好，增改操作会因重建索引而变慢
c.避免对索引进行以下操作：计算操作；用not，<>，!=；用IS NULL和IS NOT NULL；数据类型转换；使用函数；使用空值。
2）where之后的优化
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。就是说索引最好能建在where之后要用到的字段上面，但同时也要注意避免上述索引的问题。所以在此重复强调一遍，主要就是避免在WHERE子句中使用in，not in，or 或者having，还有不要任何计算和函数。
a.应尽量避免在 where 子句中使用 != 或 < > 操作符
b.应尽量避免在 where 子句中使用 or 来连接条件,用 union all 来替换：
c.慎用 in 和 not in，改用 exists 和 between
d.应尽量避免在 where 子句中对字段进行表达式操作或者函数
3)select 优化
a.任何地方都不要使用 select * from t
b.推荐使用UNION ALL，尽量避免UNION（UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。）
SQL创建一个已经有的表的复制（空表或者创建并复制新表的数据）
Sql server ：
select * into table_new from table_old ; 复制结构和数据
select * into table_new from table_old where 1=2;只复制结构
Oracle：
create table table_new as select * from table_old;复制结构和数据
create table table_new as select * from table_old where 1=0;只复制结构
DB2：
–复制表结构
create table table_name_new as (select * from table_name_old) definition only;
----definition only是指只复制表结构而不一起复制数据
–插入数据
insert into table_name_new （select * from table_name_old);
MySql：
----- 复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
----- 只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
