Mysql常用操作

一、初始数据库

数据库管理系统   DBMS   -->   Oracle、MySQL、SQL Server、informix、DB2

数据库/仓库 DB

SQL              结构化查询语言

二、数据库常用命令

  a、查询数据库版本

    1、mysql -v

    2、mysql --version

    3、进入数据库  select version();

  b、基本的命令

     1、show databases；-->查看所有数据库

     2、use 数据库名;   -->使用数据数据库

     3、show tables；   -->查看当前数据库下面的表

     4、select database() -->查看当前的数据库

     5、select * from 数据库名   -->查看数据库下面有多少表

     6、source 脚本路径   -->导入数据库脚本关键字

     7、desc 表名       -->查看表结构

     8、show creat table 表名;   -->查看创建该表的sql语句

     9、drop table if exists 表名;  -->如果存在  删除该表

  c、SQL语句分类

     1、DQL语句   -->数据查询语句

        1）、简单查询

   1、select 字段名称 from 表名;   -->查询单个字段

   2、select 字段1,字段2 from表名; -->查询多个字段

   3、select * from 表名;    -->查询全部（不建议使用*，在开发中尽量不要用*，写字段）

   4、select 字段1,字段2 as 别名 from 表名;  -->  as给字段2起别名，as 可省略  但是开发中不建议省略 

2）、条件查询  where 关键字  放在 from 之后  

   1、常用的运算符

1、=、<>或！=（不等于）、<=、>=、<、>

2、between...and...   -->两个值之间包括两端的值

3、is null   为空  is not null 不为空

4、and    并且

5、or     或者

6、in     包含  相当于多个or（not in 不在这个范围）

7、not    可以取非  主要用在is或in中

8、like    like为模糊查询，支持%或下划线匹配%匹配任意个字符  下划线，一个下划线只匹配一个字符

9、模糊查询  %是通配符    下划线是占位符

   2、or和and优先级    and 比or的优先级高

   3、null  什么都不是  不能进行运算，用is来查询  

   4、select * from  表名 where 字段 like '%字段%'  -->模糊查询   like 关键字

3）、数据排序  asc（升序）、desc（降序）

   1、单一字段排序  order by字段名称默认升序  order by放在where 语句后面

        1、单一字段排序语法：select 字段1,字段2 from 表名 order by 字段 asc/desc;

2、多个字段排序语法：select 字段1,字段2 from 表名 order by 字段1 asc/desc, 字段2asc/desc....;

3、按条件排序语法：select 字段1,字段2 from 表名 where 条件 order by 字段1 asc/desc, 字段2asc/desc....;

   2、字段下标排序（下标最终显是出来）  不建议使用下标 程序不健壮

1、先查询出来数据  再根据下标排序，    

4）、处理函数

     1)、单行处理函数

1、Lower(字段)    转换小写

2、upper(字段)    转换大写

3、substr(字段,第几个字段开始,截取几个字段)    取字符串（substr （被截取的字符串，起始下标，截取的长度））

4、length(字段) as 别名     取长度

5、trim(字段)       去空格  mysql默认去除字段后面的空格

6、str_to_date('1993-08-14','%m-%d-%Y')    将字符串转换日期

1、str_to_date('1993-08-14','%m-%d-%Y')   条件查询时

2、

7、format()       设置千分为

8、round(数字,保留几位小数)        四舍五入

9、rand(rand()*122)       生成随机数0,1之间的随机数

10、Ifnull()      可以将null装换成一个具体值

11、date_format()    格式化日期  yyyy-MM-dd HH:mm:ss  java中

1、date_format(日期类型数据, '日期格式%Y/%m/%d')

12、case...when..then...else...end     当..的时候  做什么操作

     2)、分组凼数/聚合凼数/多行处理凼数

     自动忽略控制 不能直接使用在where语句后面

a、常用函数  聚合函数

1、sum(字段)      求和

2、avg(字段)      求平均值

3、max(字段)      取最大值

4、min(字段)      取最小值

5、count(字段)    取记录数  * 或具体字段

 b、distinct     去除重复

1、distinct 字段    -->  去除单一重复字段

2、distinct 字段1,字段2    -->  去除多个字段去重复字段

     3)、分组查询  group by

1、group by 字段名

2、group by 字段1,字段2

3、group by 字段1   having 

4）、select语句总结

select

xxx -->查询之后显示的字段

from 

xxx -->查询的表

where    

xxx -->查询的条件

group by

xxx -->分组字段

having

xxx -->

order by

xxx -->排序

5）、跨表查询

a、年代分类

1、SQL92表关联语法

select 字段1,字段2 from 表1 as 别名1,表2 as 别名2 where 别名1.字段1=别名2.字段2

2、SQL99表关联语法（掌握）

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

join 

表名2 别名2 

on

别名1.字段1=别名2.字段2

where

数据过滤条件

b、连接分类

1、内连接 省略关键字   inner 

1、等值连接

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

inner join 

表名2 别名2 

on

别名1.字段1=别名2.字段2

where

数据过滤条件

2、非等值连接

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

inner join 

表名2 别名2 

on

别名1.字段1 between 别名2.字段2 and 别名2.字段3

where

数据过滤条件;

3、自连接：把一张表看成两张表使用

select 

别名1.字段1 ,别名2.字段1 as 区别的字段 

from 

表名1 别名1 

inner join 

表名1 别名2 

on

别名1.字段1=别名2.字段2

where

数据过滤条件;

2、外连接   查询出来的条数>=内连接查询出来的数据  outer省略一个关键字

1、左外连接/左连接  outer省略一个关键字

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

lift join 

表名2 别名2 

on

别名1.字段1=别名2.字段2

where

数据过滤条件

2、右外连接/右连接  outer省略一个关键字

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

right outer join 

表名2 别名2 

on

别名1.字段1=别名2.字段2

where

数据过滤条件

3、全连接

多表查询

select 

别名1.字段1,别名2.字段2 

from 

表名1 别名1 

join 

表名2 别名2 

on

别名1.字段1=别名2.字段2

join 

表名3 别名3 

on

别名1.字段1 between 别名2.字段2 and 别名2.字段3

6）、子查询  select 查询语句中套select 语句

select ...(select).. -->极少使用

from ...(select)..

where ...(select)..

7）合并集合 union 合并（相加）集合

字段类型，字段个数，字段所表示的意义都要一样

select 字段1,字段2 from 表1 where 字段2=值1

union

select 字段1,字段2 from 表1 where 字段2=值2;

8）分页查询  mysql 中 limit  起始下标 m   长度n

1、从第一个截取

select * from 表名 limit 起始下标,截取长度

2、MySQL分页查询（三要素：当前页,起始索引,一页显示几天）

select * from 表名 limit (当前页-1)*一页多少条,一页多少条);

     2、DML语句   -->数据库操作语言

1、insert  插入数据

insert into 表名(字段名1,字段2...) values(字段值,字段值,...);

2、delete  删除字段

delete from 表名 where 删除条件

3、update   修改字段

update 表名 set 字段名称=字段值,字段名称= 字段值 where 限制条件;

     3、DDL语句   -->数据库定义语言

1、create

create table t_表名/tb1_表名(

字段名称 字段类型 字段长度 字段约束

)

create table 表2 as select * from 表1;   快速创建一张表

2、drop

drop table 表名;

drop table if exists 表名;如果存在 就删除表

3、alter 修改表结构

1、添加1列

alter table 表名 add 字段名称 字段类型 字段长度 字段约束;

2、修改表中的字段类型

alter table 表名 modify 字段名 字段类型;

3、删除表中的字段

alter table 表名 drop 字段名称;

4、修改表中的字段名

alter table 表名 change 原字段名称 新字段名称 字段类型;

     4、TCL语句   -->事务控制语言

1、commit     提交事务

2、rollback   回滚事务 

三、表

a）、表的组成

行和列组成：行称为数据或记录  列称为字段 字段有包含：字段名称、字段类型、长度、约束

b）、表的创建

create table t_表名/tb1_表名(

字段名称 字段类型 字段长度 字段约束,

字段名称 字段类型 字段长度 字段约束,

字段名称 字段类型 字段长度 字段约束

)

快速复制一张表

create table 表2 as select * from 表1;

c）、字段类型

char      -->  char(n)   定长的字符串

varchar   -->  varchar(n) 变长的字符串

int       -->   4个字节

bigint    -->   8个字节

float(有效数字位数, 小数位)

double(有效数字位数, 小数位)

date       -->   8字节  日期

BLOB       -->   流媒体的

CLOB       -->   字符串

d）、删除表

drop table 表名;

drop table if exists 表名; 如果存在  删除该表

e）、给表里面字段设置默认值

创建表时字段加 default '1' 默认为空

f）、创建表加入约束（constraint）

查询表中的所有约束

select constraint_name from 

1、非空约束（not null）

2、唯一性约束（unique）：唯一性，不可重复

列级写法：写到字段数据类型后面 unique

表级写法：写到最后 unique(字段名)

可以联合唯一性：

3、主键约束(primary key)

自动生成主键 primary key auto_increment

4、外键约束(foreign key)

添加外键约束：constraint 外键别名 foreign key(外键字段名) references [引用的那张表的那个字段]表名(字段名);

级连操作：切忌不要瞎玩

级联更新：on update cascade

 1、删除原来的外键约束

alter table 子表名 drop foreign key 外键名;

 2、添加新的外键约束

alter table 子表名 add constraint 外键别名 foreign key(外键字段名) references [引用的那张表的那个字段]表名(字段名) on update cascade;

级联删除：on delete cascade

 1、删除原来的外键约束

alter table 子表名 drop foreign key 外键名;

 2、添加新的外键约束

alter table 子表名 add constraint 外键别名 foreign key(外键字段名) references [引用的那张表的那个字段]表名(字段名) on delete cascade;

四、存储引擎

a、

b、

c、

五、索引(index)

1、

1、数据量大加索引

2、少的使用DML操作

3、经常出现在where语句

2、创建索引

create index 索引名 on 表名(字段);

3、查看索引

show index from 表名;

4、删除索引

drop index 索引名 on 表名;

六、视图(view)

1、创建视图

create view 视图名 as select * from 表名;

2、删除视图

drop view 视图名;

3、视图的作用

a、隐藏表的细节

b、提高检索效率

七、数据库设计三大范式

1、第一范式：必须有主键最基本的要求、字段不能再分

2、第二范式：建立在第一字段基础之上，减少联合主键

3、第三范式：建立在第二范式基础之上，要求非主键字段不能产生传递与主键字段

八、数据库的事务(Transaction)

1、事务是什么

a、一个最小的不可再分的工作单元

b、通常一个事务对应一个完整的事务

c、而一个完整的业务需要批量的DML（insert、update、delete）语句共同完成

d、事务只和DML语句有关系，或者说DML语句才有事务

e、业务逻辑不同DML语句也不同

2、事务的四个特性ACID

a、原子性（Atomicity）：事务是最小单元，不可再分；

b、一致性（Consistency）：必须同时成功或同时失败

c、隔离性（Isolation）：不影响其他事务的运行

d、持久性（Durability）：不会回滚

3、事务的一些概念

a、开启事务：start transaction

b、结束事务：end transaction

c、提交事务：commit transaction

d、回滚事务：rollback transaction

4、查看数据库事务：

show variables like '%commit%';

开启事务：

1：手动开启(一次设置一次)

start transaction;commit;

2：关闭自动提交事务

set autocommit = off;

事务的隔离级别：

1、read uncommitted        读未提交（级别最低）

2、read committed           读已提交

3、repeatable read          可重复读mysql默认

4、seriallzable             串行化

5、查看隔离级别

select @@tx_isolation;会话级隔离级别

select @@session.tx_isolation;会话级隔离级别

select @@global.tx_isolation；全局的隔离级别

6、设置隔离级别

set transaction isolation level isolation-level;

#查询重复的数量

SELECT COUNT(*) FROM users WHERE username= 'lisi'

#查询两列返回一列

SELECT CONCAT(id,username) FROM users;