当前位置: 澳门新濠3559 > 数据库 > 正文

2.路径自定义选择,最近正在整理有道云笔记内容

时间:2019-12-27 19:58来源:数据库
 6.查看当前库的所有视图    show tables status where comment='view' MySQL 视图,mysql视图 1.视图: 就是一张虚拟表,本质上存储的是一对SQL的集合 --  视图 是一张虚拟的表    01.表示一张表的部

 6.查看当前库的所有视图
   show tables status where comment='view'

MySQL 视图,mysql视图

1.视图: 就是一张虚拟表,本质上存储的是一对SQL的集合

--  视图 是一张虚拟的表
   01.表示一张表的部分数据或者是多张表的综合数据!
   02.结构和数据都是建立在对真表的查询基础之上的!
   03.视图中存放的数据其实就是对真实表的引用!
     对视图中的数据进行添加,更新删除都会影响到真实的表!
   04.一个真实的表可以创建N个视图!
   05.如果视图关联了多表,不允许增 删!单表可以增删改
   06.视图一般都是使用查询!

2.定义视图的语法:
create view VW_stulist(VW_视图的功能)
as
sql语句
 小Tip:当多表中有同名列的时候,在视图这个虚拟表中,只能有一列。手工指定该列是哪个表的

3.视图的使用 和检索基本表一样,直接通过select
select * from VW_stulist

4.删除视图
drop view 视图名
drop table
drop database
drop index
drop constraint
归根结底:drop  删除是结构   delete 删除数据

 

5.查看所有库所有视图

 5.1.切换到系统数据库
  use information_schema

 5.2.select * from views G;

 6.查看当前库的所有视图
   show tables status where comment='view'

 7.视图注意事项
1)视图可以查询多表数据
2)视图可以嵌套
3)update,insert,delete不能封装成视图内容,通过图形化界面可以操作数据(操作视图数据,影响的是基本表)。
4)删除视图数据的时候,有一定的限定,数据结果来源于多表的时候,不能删除

 

--  创建一个视图  只获取 学生姓名 编号 以及考试成绩
CREATE  VIEW view_student_result
AS
SELECT s.studentNo 学生编号,studentName 学生姓名,
studentResult 考试成绩
FROM  student s,result r
WHERE s.`studentNo`=r.`studentNo`
--  查询视图中的内容
SELECT * FROM view_student_result
-- 查询mysql数据库中所有的视图
SELECT * FROM information_schema.views;
--  删除视图
DROP  VIEW view_student_result;

--  创建一个表的视图  学生姓名,地址,手机号
CREATE VIEW  view_student
AS
SELECT  studentName 学生姓名,address 地址,phone 手机号
FROM  student

-- 查询视图

SELECT  * FROM view_student 

视图,mysql视图 1.视图: 就是一张虚拟表,本质上存储的是一对SQL的集合 -- 视图 是一张虚拟的表 01.表示一张表的部分数据或者是多张表...

一、版本相关      

       对于一个mysql的版本号如:mysql-5.6.1-m1,这个版本号意味着什么呢?对于5.6.1的解释:第一个数字5代表了文件格式,第二个数字6代表了发行级别,第三个数字1代表了版本号。更新幅度较小时,最后的数字会增加,出现了重大特性更新时,第二个数字会增加,文件格式改变时,第一个数字会增加


MySQL存储引擎的事务说明

存储引擎 说明
InnoDB 支持行级锁定
MyISAM 支持表级锁定
Memory 支持表级锁定
BDB 支持页级锁定
  • #### Lock Table与Unlock Table
use finance;
lock table orders read;

unlock table;

如果某个进程(session1)lock定了表,那么其他的进程(session2)可以查询,但是不能进行更新操作,直到第一个进程释放了锁

  • #### 事务控制

    事务关键字 说明
    start transaction 开启一个新事务
    commit 提交事务
    rollback 回滚事务
--  创建一个视图  只获取 学生姓名 编号 以及考试成绩
CREATE  VIEW view_student_result
AS 
SELECT s.studentNo 学生编号,studentName 学生姓名,
studentResult 考试成绩
FROM  student s,result r
WHERE s.`studentNo`=r.`studentNo`
--  查询视图中的内容
SELECT * FROM view_student_result
-- 查询mysql数据库中所有的视图
SELECT * FROM information_schema.views;
--  删除视图
DROP  VIEW view_student_result;
--  创建一个表的视图  学生姓名,地址,手机号
CREATE VIEW  view_student 
AS
SELECT  studentName 学生姓名,address 地址,phone 手机号
FROM  student
-- 查询视图
SELECT  * FROM view_student  

十四、事务与锁

     事务控制和锁定语句     (5.0.3 后支持分布事务,当前分布式事务只支持innoDB引擎)

1、事务

事务:transaction是指一组SQL语句,事务用以处理insert、update,delete操作,不能回退select,create,drop操作。注意 InnoDB 支持事务处理。

开启事务:start transaction;

事务提交:commit ;

事务回退:rollback ,rollback to savepoint 回退到保留点(占位符)。

保留点(占位符):savepoint delete1; rollback to delete1;

set autocommit=0 ;事务不自动提交、set autocommit=1 ;事务自动提交

2、锁定语句

lock tables table_name read/write;  锁定当前线程的表

>unlock tables; 打开锁


日期类型的选择

date/time/datetime/timestamp

数据库视图是虚拟表或逻辑表,它被定义为具有连接的SQL SELECT查询语句。 因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据。 大多数数据库管理系统(包括MySQL)允许您通过具有一些先决条件的数据库视图来更新基础表中的数据。

十二、游标

     游标cursor是一个存储在MYSQL服务器上的数据库查询,不是一条SELECT语句,而是查询的结果集。MYSQL中,游标只能用于存储过程和函数

1、语法

创建游标:declare cursorname curor;

打开游标:open  cursorname;

关闭游标:close cursorname;


存储过程变量的使用

存储过程可以使用变量,并且在MySQL5.1版本后,不区分大小写

  • #### 变量的定义

      declare currentTime date;
    

    变量的作用域只能在begin...end块中,可以嵌套在块中

  • #### 变量的赋值

    set currentTime=now();//直接赋值
    

    select x into currentTime from x;//也可以通过sql语句进行赋值

  • #### 定义条件和处理

    declare handler_type handler for contidtion_value
    

    handler_type:

  • continue

  • exit
  • undo

contidtion_value:

  • sqlstate
  • sqlwarning
  • not found
  • sqlexception

     declare continue handler for sqlstate '2' set @x=1
    

 

十、视图相关

     视图是一种虚拟的表,用于简化复杂的SQL操作,视图主要用于数据检索,而不是数据更新,其本身不包含数据,更新的是源表中的数据,视图不能索引

1、语法

create view viewname ;创建视图

create or replace view ;创建或更新视图,注意与create view 的区别。create view 只创建,若已存在视图,则会报错,而create or replace view ,若已存在视图,则会更新视图

create view viewname as select_statement with cascaded|local check option;  ( cascaded满足所有争对该视图的所有视图条件才能更新,local满足本视图的条件即可更新)

alter view viewname as select_statement  with cascaded|local check option; 更新视图

drop view viewname; 删除视图

举例:

>create view viewname as select col1 ,col2 from table where col=?

>show table status like 'tablename|viewname' G 查看视图

>show create view viewname G 查看某视图的定义

>select * from information_schema.views where table_name='viewname' G


存储过程(store procedure)和函数

存储过程和函数是事先经过编译并存在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的
语法:

create database finance;//创建finance数据库

use finance;
create table orders(
 orderId bigint not null auto_increment,
 memberId int not null default 0,
 serialNumber varchar(50) not null default '',
 amount decimal(18,2) not null default 0,
 createTime datetime not null default current_timestamp,
 primary key (orderid)
)//创建orders订单表

insert into orders (memberId,serialNumber,amount) values(6561121,'sz12234222',5),(233444,'ys1652233',10)//插入测试数据

delimiter &
create procedure orders_serial(in serial varchar(50))
reads sql data
begin
  select * from orders
  where serialNumber=serial;
end &

注释:delimiter $$命令就是将语句的结束符从分号;修改成其他符号,这里指的是$$为结尾。这样在number后面的分号就不会认为结束。

  • #### 调用存储过程

    call orders_serial('sz12234222')
    
  • #### 存储过程的好处

逻辑封装在数据库端,调用者不需要了解中间的处理逻辑,一旦调用逻辑发生变化,只需要修改存储过程即可,而对调用者的程序完全没有影响。

  • #### 删除存储过程

    drop procedure if exists orders_serial
    //if exists可选
    
  • #### 查看存储过程状态

    show  procedure status like 'orders_serial'
    
  • #### 查询存储过程的定义

    show  create  procedure orders_serial
    

3.视图的使用 和检索基本表一样,直接通过select
select * from VW_stulist

四、show命令相关

show variables like '变量名' ;

show character set; 展示字符集《=》等同于information_schema.character_sets表内容

show collation like '校对规则名称%';查看校对信息《=》information_schema.collations表

show databases ; 查看数据库《=》informaion_schema.schemata表

show tables ; 查看表 《=》informaion_schema.tables表

show columns from tablename查看列<=> describe tablename 《=》informaion_schema.columns表

show index from table_name 查看表的索引《=》show keys from table_name  《=》information_schema.statistics表

show status 显示服务器状态 -等同于《=》information_schema.global_status表

show processlist; 显示所有活动进程,可用KILL查杀《=》informaion_schema.processlist表

show engines; 查看存储引擎 《=》informaion_schema.engines表

show events 查看定时器(事件)《=》informaion_schema.events 表

show triggers;查看触发器信息《=》select * from informaion_schema.triggers where trigger_name ='trigger_name' G;

show procedure status;查看存储过程 《=》informaion_schema.routines 表

show create database 显示创建数据库的语句

show create table 显示创建表的语句

show grants 显示授权对象,如show grants for username; 查看用户的权限

show warnings 显示告警信息

show errors  显示错误信息

show master logs; 查看binlog是否设置《=》show binary logs;

应用举例:

>show grants for admin;  查看admin权限

>show grants for z1@localhost; show grants for z1; 查看用户权限

>show create view viewname G 查看某视图的定义

>show variables like 'table_type';查看当前的存储引擎 (InnoDB(支持事务,支持外键),MyISAM(默认,不支持事务,不支持外键,但访问速度快..)

>show variables like 'have%';

>show table status like 'tablename|viewname' G ;查看表|视图的状态

>show variables like 'character_set_server'; 查看服务器字符集(默认为latin1)

>show variables like 'collation_server'; 查看服务器校对规则

>show session|global status(session 当前连接,global自数据库上次启动至今,若不写,默认session)

>show status like 'innodb_rows_inserted%';执行insert操作插入的行数

>show status like 'Slow_queries%';查看慢查询的次数

>show status like 'Com_%';常用于优化,com_xxx 表示XXX语句执行的次数,重点关注com_select, com_insert, com_update, com_delete,com_commit,com_rollback

>show processlist 查看当前MYSQL线程情况

>show status like 'handler_read%';查看索引使用情况

>show status like 'table%';  查看表级锁征用情况

>show status like 'innodb_row_lock%'; 分析系统行锁争夺情况

>show variables like '%query_cache%' 使用查询缓存

>show status like '%Qcache%'   监视查询缓存的使用情况


MySQL版本选择

澳门新濠3559 1

 

三、获得MySQL帮助

>help ; 显示MySQL客户端(非服务器)的配置 等同于问号 ?

>help contents; 查看具体小节内容 或? contents; (账户管理,数据库管理,语句,数据定义,数据操作,数据类型,函数,元数据,语言结构,插件(plugins),存储引擎等等);show plugins;(查看mysql的存储引擎)

>help administration; 查看管理方面的内容

>help show create view; 查看如何创建视图

>? data types; 查看数据类型

>ctrl+L 清除屏幕信息

>c 清除输入的错的语句 (clear)

>connect  mysql(db_name) localhost; 重新连接MySQL

>delimiter  $修改语句结束符号(操作符),默认分号 ,delimiter ;

>edit 编辑SQL语句(调用vi)

>exit 退出服务端

>nopager  或pager 查询很大的表时,控制屏幕输出结果

> pager less   >pager more  分页查看结果

>nopager 禁止pager

>tee ore notee 将查询内容及结果输出到文件中去

>tee /tmp/test.txt  将屏幕输入的命令,打印到test文本文件中

>notee; 停止输出

>prompt 改变mysql prompt,prompt命令可以在mysql提示符中显示当前用户、数据库、时间等信息

>system(!)  ls; 调用系统命令 > system cat /tmp/rst.txt

>source /temp/a.sql; 执行SQL文本,并打印结果

>status; 查看状态信息(当前数据库,当前用户,SSL,当前pager,服务器版本,连接协议,字符集,unix socket 文件位置)

>use 切换数据库; >use mysql; >select database() 查看当前数据库

>charset gbk; 更改client,conn characterset 更改客户端,连接字符集 (通常server,db,client,conn字符集设置成一样)

>charset latin1;

>warnings (W) 打印警告信息  >show warnings; >show errors;

>nowarnings; 不显示告警信息

>rehash(#) 自动补齐

更多详情,输入mysql>help ; 查看具体帮助命令


十、触发器

--  视图 是一张虚拟的表
   01.表示一张表的部分数据或者是多张表的综合数据!
   02.结构和数据都是建立在对真表的查询基础之上的!
   03.视图中存放的数据其实就是对真实表的引用!
     对视图中的数据进行添加,更新删除都会影响到真实的表!
   04.一个真实的表可以创建N个视图!
   05.如果视图关联了多表,不允许增 删!单表可以增删改
   06.视图一般都是使用查询!

十七、文本搜索

全文本搜索 :FULLTEXT

create table a ( note_text text null, fulltext(note_text)); #note_text为字段名

select note_text from a where match(note_text) against ('搜索字符串' with query expansion);

布尔文本搜索

select note_text from a where match(note_text) against ('搜索字符串 -rope*' in boolean mode);

#-rope*表示mysql排除包含rope*(任何以rope开始的词)的行,-排除一个词、*截断操作符


       总结下来,发现内容好多,工作中可能有些知识用得少,但并不代表这些知识就没有用处,总有一天,遇到问题了,回想起曾经学到的知识,会发出感叹,原来是这么个理儿。所以,学习的路还很长,经常不使用练习,大脑便会选择性的进行遗忘,所谓好记性不如烂笔头,记录下来总结思考,时常练习实践才是硬道理!

     本文中的笔记多来自于平时工作中总结、网友回答和《深入浅出:MySQL数据库开发、优化与管理维护》一书,适合入门学习,推荐大家去阅读。如果想要深入学习mysql,推荐大家去阅读几本经典书籍:

1、《MySQL技术内幕》-作者:[美]保罗·迪布瓦(PaulDuBois)

2、《高性能mysql》-作者:【美】施瓦茨(Schwartz,B.)【美】扎伊采夫(Zaitsev)

3、《MySQL技术内幕InnoDB存储引擎》-作者:姜承尧

DDL语句(涉及表的定义、结构的修改)

一、create语句
澳门新濠3559 2

  • Query Ok代表语句执行成功
  • 1 row affected代表数据库一行收到影响
  • 0.01 sec代表操作执行的时间
create table student(
 SID  int  not null auto_increment,
 sNo int ,
 sName varchar(50) not null,
 primary key(SID)
);

1.查看系统中都存在哪些数据库(show databases;)

2.在查看系统中已有的数据库后,可以用(use dbname)选择对应的数据库

3.在选择对应的数据库后,查询该数据库下面的所有的表(show tables

二、删除数据库

删除数据库的语法:drop databse dbname;

三、创建表

语法:create table tablename(column_name_1 column_type_1 constraints,column_name_2 column_type2 constrationts)

  • mysql的表名是以目录形式存储在磁盘上,表名的字符可以是任何目录名允许的字符。
  • column_name是列名
  • column_type是列的数据类型
  • constrationts是列的约束条件
    澳门新濠3559 3
  • 查看表定义:desc tablename
  • 查看创建表的SQL语句:show create table tablename

四、删除表

删除表的语法:drop table tablename;

五、修改表
aleter 语法 | 说明
---|---
alter table tablename modify columnname newColumnType | 修改表字段的类型(==modify 不能更改字段名称==)
alter table tablename add newColumnname newColumnType| 增加表字段
alter table tablename drop oldCloumnname|删除表字段
alter table tablename change oldColumname newColumnname newColumntype|修改字段的名称及类型
alter table tablename rename (to) newtablename|修改表名称

  • 修改字段的排列顺序
    在alter的语法后面都有[firstafter columnname]可选项
alter table user add address varchar(20) first ;
alter table user add age int after name ; 

 7.视图注意事项
1)视图可以查询多表数据
2)视图可以嵌套
3)update,insert,delete不能封装成视图内容,通过图形化界面可以操作数据(操作视图数据,影响的是基本表)。
4)删除视图数据的时候,有一定的限定,数据结果来源于多表的时候,不能删除

六、DDL语句

1、select查询

select * from table where col=? ; 查询

组合使用顺序(重要):select ... from tb_name where ... group by  (with rollup)...having ...order by (asc|desc) ...limit(n,m) ...

(注意with rollup 不能与order by 同时使用,limit在rollup后使用)

select常用语句举例:

>select distinct column from table ; 对column列值去重

>select count(1) from emp; 返回emp表行数

>select sum(sal),max(sal),min(sal) from emp; 与函数结合使用

>select * from table limit 5; 返回不多于5行  --limit n1,n2 返回从n1开始的n2行

>select * from table where col1 like '%value'; --模糊匹配  %匹配多个或0个字符     _ 匹配一个字符

>select * from emp where depno in (select depno from dept); 连接子查询

>select emp.* from emp dept where emp.depno=dept.depno;  --mysql 4.1前不支持子查询

分组排序:

>select col1,count(col2) from table group by col1 ;

分组  group by (必须使用select的相同表达式,不能使用别名,出现在where 语句之后,order by 语句之前)

>select deptno,count(1) from emp group by deptno with rollup having deptno=2; 对分组后的结果汇总运算

>select deptno,count(1) from emp group by deptno  having count(1)>1;

注意:where 过滤行,而having 过滤分组,与group by 配合使用,where 是分组前进行过滤,而having是分组后进行过滤

>select col1,col2 from table order by col1 desc,col2 asc;   order by 一定要位于where 语句之后

加锁

>select * from tb_name where ... lock in share mode;  给记录集加共享锁

>select * from tb_name where ... for update; 加排他锁

连接查询相关

>select a.col1,b.col2 from a left outer join b on a.col3=b.col3;  左外连接

>select a.col1,b.col2 from a right outer join b on a.col3=b.col3; 右外连接

>select a.col1,b.col2 from a inner  join b on a.col3=b.col3;  内连接

>select col1,col2 from table where col1=? union |union all select col1,col2 from table where col2=? 组合查询,各子查询之间必须是相同的列,表达式和数据类型,函数

组合查询UNION默认去掉重复行 ,UNION ALL 则不取消重复行,返回所有行;组合查询只能使用一条order by 语句,且必须置于最后一个查询之后,来排序返回所有结果

2、insert插入数据

insert into tablename() values();

>insert into table1 select * from table2  -- 注意table1、table2 含有相同的表结构

3、update更新数据

update tablenem set col=value,... where ...

>update t1,t2,t3 set t1.col=?,t2.col=?,t3.col=? where ... 多表更新

4、delete删除数据

delete from table where col=?   只删除行(delete不会删除索引及数据文件)

truncate table  删除整个表,并删除索引和数据文件,删除速度更快(若删除全表,一般采用truncate)

>delete t1,t2,t3 from t1,t2,t3 where ...;

>delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno='5';#若from后面表名用别名,则delete后面也要用相应别名

MySQL功能自定义选择安装

1.功能自定义选择澳门新濠3559 4
2.路径自定义选择澳门新濠3559 5
3.设置root用户密码澳门新濠3559 6
4.安装完成,点击MySQL Workbench 6.3 CE进入MySQL客户端

 

七、DML语句

1、create创建表

create table tablename (col1  int  not null  auto increment,   --每个表只允许一个auto increment列,且该列必须被索引,可设置为主键

col2  char(20)  null  default ? ,   --默认值?

...

primary key (col1),

)ENGINE=InnoDB; --默认引擎为MYISAM 支持全文本搜索,但不支持事务处理,InnoDB支持事务处理,不支持全文本搜索

create table table_name2 like table_name1; 复制创建一张类似table1的table2表

2、alter更新表

>alter table tablename add/drop column ? ; 对表增加或删除列

>alter table tablename  add column column_definition;  增加列,并定义列

>alter table tablename  modify column column_definition; 修改列的定义

>alter table tablename change column old_col_name column_definition; 修改列的定义

>alter table tablename drop column col_name; 删除某具体列

>alter table tablename  rename to new_tablename; 更改表名

举例:

>alter table emp add birthdate after ename; 对表emp增加add_birthdate列,并位于after_name列后

>alter table emp modify age int(3) first;  --将age列指定为int(3)类型

>alter table emp rename to emp1;  更改表名为emp1

>alter table t1 modify id2 int(5) zerofill;   --zerofill  数值前用0 填充剩余宽度,若一个列指定zerofill,则mysql自动为该列添加UNSIGNED属性

3、删除表

drop table tablename;

重命名表:

rename table tablename1 to new-tablename1,tablename2 to new-tablename2;

注意:创建、更新、删除视图VIEW的语法结构,与表一致。


MySQL的下载

2.定义视图的语法:
create view VW_stulist(VW_视图的功能)
as
sql语句
 小Tip:当多表中有同名列的时候,在视图这个虚拟表中,只能有一列。手工指定该列是哪个表的

九、索引相关

1、索引分类

BTREE索引(unique,index,primary key ,fulltext等索引时默认选择BTREE,可使用like,>,<,between等关键字)

HASH索引(只能使用=,<=>操作,全表扫描,存储引擎为MEMORY时,注意可能默认为Hash索引),

空间列类型索引使用RTREE索引。

2、语法

create index  index_name on table_name(column_name); 创建索引

drop index index_name on table_name;  删除索引

show index from table_name --查看该表的索引=》 show keys from table_name  查看表的索引

举例:

>create index cityname on city(city(10));

>drop index cityname on city;


逻辑运算符(布尔运算符)

运算符 说明
NOT,! 逻辑非
AND,&& 逻辑与
OR 逻辑或
XOR 逻辑异或

 5.2.select * from views G;

十三、触发器

     触发器是与表有关的数据库对象,在满足定义条件时自动触发,并执行触发器定义的语句集合 ( mysql 5.0.2之后支持),注意(MYSQL只支持对DELETE,INSERT,UPDATE语句的响应)触发器名必唯一。

创建触发器:

create trigger triggername after insert on table_name for each row

create trigger trigger_name trigger_time trigger_event on tb1_name  for each row trigger _stmt  (触发器只能创建在永久表上,不能创建在临时表上)

删除触发器:

drop trigger triggername;

>show triggers G ;查看触发器信息《=》select * from informaion_schema.triggers where trigger_name ='trigger_name' G;

>desc triggers; 查看触发器表结构定义


触发器执行的顺序

before insertbefore updateafter update

 5.1.切换到系统数据库
  use information_schema

十一、存储过程和函数

      存储过程和函数是事先经过编译后存储在数据库中的一段SQL语句集合,存储过程参数可使用IN,OUT,INOUT类型,而函数只能使用IN类型,且必须有返回值。创建存储过程和函数需要CREATE ROUTING权限,修改或删除需要ALTER ROUTING权限,执行需要EXECUTE权限。

1、语法

create procedure/function sp_name ...  创建存储过程

alter  procedure/function sp_name ...  修改存储过程

drop procedure/function sp_name ...    删除存储过程

call sp_name(parameter...) 调用存储过程或函数

show create procedure|function sp_name G 查看存储过程的创建内容

show procedure|function status like 'sp_name' G 查看存储过程的信息(由谁创建、在哪儿创建、什么时间创建、字符集设置)

show procedure status;查看存储过程《=》select * from information_schema.routines where routine_name='name' 澳门新濠3559,G 查看定义

举例:

创建存储过程sp

create procedure sp()

BEGIN

select AVG(pro_price) as priceaverage from products;

END;

执行存储过程:

call sp();

call sp(@pricelow,@pricehigh,@priceaverage);   --mysql变量都必须以@开始

删除存储过程 :

drop procedure sp; 注意sp后面不加()

drop procedure sp if exists;  若不存在存储过程,会报错,所以可加上if exists检查存储过程

创建存储过程中的变量定义方法:

declare var_name type ;  eg:>declare last_month_start DATE; 定义变量,变量只能在BEGIN。。。AND块中

set var_name=expr...   ; eg>set last_month_start= DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH);    变量赋值

select col_name ... into var_name ... table expr 变量赋值


索引概述

索引是数据库中用来提高性能的最常用工具。在MySQL中,MyISAM与Innodb存储引擎的表默认创建的都是Btree索引。

1.索引的创建

create table indexTest(
id int not null auto_increment,
memberid int not null,
createtime datetime not null default current_timestamp,
primary key (id)
)

alter table indextest add orderserial varchar(50) not null;


create unique index IX_orderserial on indexTest(orderserial);

insert into indextest (memberid,createtime,orderserial)values(112123,'2016-08-14','sz121213')

说明:上面创建一个表,其中定义orderserial为唯一索引。
语法:create [uniquefulltextspatial] index index_name on tablename(columname)

2.设计索引的原则

  • 最合适的索引列是出现在where子句中列,或连接子句中指定的列,而不是出现在select关键字后面的选择列表的列
  • 使用唯一索引,需要考虑列中某个值得分布,如果索引列种的基数越大,则索引的效果越好。举个例子:订单号就可以设置唯一索引,因为订单号的不一样。而对于rowstatus就无须了,因为rowstatus要么是有效要么是无效。这样的筛选出的范围还是很多,没有意义
  • 不要过度索引。因为所有也要占用额外的磁盘空间,如果一个索引很少使用,那么会不必要的减缓表的修改速度

显示MySQL的执行计划:explain 后面加mysql语句

4.删除视图
drop view 视图名
drop table
drop database
drop index
drop constraint
归根结底:drop  删除是结构   delete 删除数据

二、mysql启动和使用相关

>在MYSQL命令行中使用G参数,将输出按列显示

>mysql -uroot -p -- 连接到MYSQL服务器  -u 后面为数据库用户,-p 后跟密码

>mysql -h(IP地址) -uroot -p -- 连接到MYSQL服务器 -h 后面跟服务器IP地址 -u 后面为数据库用户,-p 后跟密码

>mysqladmin -uroot -p shutdown 关闭mysql数据库

>systemctl shutdown mariaDB #centos7 关闭mysql服务

>select version(); 查看版本

>system hostname 可查看host_name

>select CURRENT_USER(); 查看当前连接用户

>select now(); 返回当前日期和时间

>desc table_name<=>explain table_name<=>show columns from table_name  查看建表语句

>desc information_schema.character_sets; 查看具体系统指定表的字段结构《=》show create table information_schema.character_sets;

>use database ; 切换到指定数据库

>analyze table table_name; 定期分析表和检查表

>check table table_name; 定期检查表

>optimize table tablename ;定期优化表,对表进行碎片整理,节省磁盘空间  

#du -sh tablename.*  linux系统查看该表在系统占用的空间

>explain sql_statement ; 分析低效SQL执行计划

>mysqlcheck -uroot -c test 检查test表

>mysql -uroot -v -- show-warnings  test 显示test表的告警信息

>mysqldump -h IP地址 -P3306 -uroot -p test >test.sql 备份test数据库  -u 指定用户名,-p 指定密码, -h 指定服务器IP或域名, -P指定端口

>create table table_name2 like table_name1; 复制创建一张类似table1的table2表

>rename table tablename1 to new-tablename1 重命名表


定义

declare cur_id cursor for select * from orders;
open cur_id;
fetch cur_id;
close cur_id;

数据库视图是动态的,因为它与物理模式无关。数据库系统将数据库视图存储为具有连接的SQL SELECT语句。当表的数据发生变化时,视图也反映了这些数据的变化。

十六、函数相关

1、函数类型

文本处理函数: RTrim()   Upper()--转化为大写    soundex()找出发音类似的值

日期和时间处理函数

数值处理函数

聚集函数

select now() 返回当前日期和时间

select Trim(' abc')   -- 返回abc 去除空格,Rtrim 删除数据右侧多余的空格   LTrim 左边


其他函数

函数 说明
database() 返回当前数据库名称
password(str) 返回字符串str的加密版本
md5(str) 返回字符串str的md5值

5.查看所有库所有视图

      关于数据库的角色权限管理、备份与恢复、数据库优化、存储过程、函数、日志文件等都是很重要的版块,这里我只是简单的提到,后续再另起专题总结。

流程函数

函数 说明
if(value,t,f) 如果value为真,返回t,否则返回f
ifnull(value1,value2) 如果value1不为空,返回value1,否则返回value2.select ifnull(2,'woc'),返回2
case when value1 then result1 ..else default end 如果value1是真,则返回result1,返回返回default
case expr when value1 then result1.. else default end 如果表达式expr等于value1,则返回result,否则返回default

1.视图: 就是一张虚拟表,本质上存储的是一对SQL的集合

十五、正则表达式

      REGEXP 后接正则表达式语句,参见正则表达式语法

select * from table where col REGEXP '.000' 返回col中含000的行  注意与LIKE区别

select * from table where col REGEXP '1000|2000’;--|相当于OR,同时返回1000或2000的数据,REGEXP BINARY 区分文本中字符的大小写

select * from table where col REGEXP '\.'  ;  --\(转义字符)  匹配特殊字符.  若不加\ .将返回所有字符

常用通配符

* 0个或多个匹配

+ 1个或多个匹配 =〉{1,}

? 0个或1一个匹配=〉{0,1}

^ 文本的开始   $ 文本的结尾


八、视图

        最近正在整理有道云笔记内容,准备把笔记归档分类,但是发现很多笔记杂乱无章,无从下手,知识内容是重复相关的,没有形成系统的归类,所以想把相关的版块整合在一起,方便我记忆与查找。暂时将mysql基础知识整理如下,方便自己的同时,希望也能帮助大家!

字符串类型

常用字符串类型 说明
char(M) M为0-255之间的整数
varchar(M) M为0-65535之间的整数,值长度+1个字节
text 允许0-65535字节,值长度+2字节

char与varchar类型的区别:

char列最后的空格已经删除,而varchar保留空格

八、用户权限相关

create user username identified by 'password';  --创建用户

rename user username to new-username;  重命名用户

drop user username;   删除用户

show grants for username; 查看用户的权限

grant select,insert on database.* to username;  --授权

revoke select on  database.* to username;  撤销权限

set password for username=password('?');  --更改密码口令

set password=password('?'); 更新当前用户的密码口令

flush privileges; 刷新权限设置

举例:

>grant select,insert on sakila.* to 'z1'@'localhost' identified by '123'; 创建一个数据库用户Z1 ,给sakila数据库中所有表赋予select,insert权限

>mysql -uz1 -p123

>use sakila

>revoke insert on sakila.*  from 'z1'@'localhost' ; 回收z1的insert权限

>grant all privileges on *.* to z1@localhost  with grant option; 创建用户z1,权限为可以在所有数据库上执行所有权限,只能从本地进行连接。with grant option #Z1用户可以进行授权

>grant all privileges on *.* to z1@localhost  identified by '123' with grant option; 加密

>grant select,insert,update,delete on test1.* to 'z2'@'%' indentified by '123';创建新用户Z2,可以从任何IP连接,权限为对test1数据库所有表进行

>flush privileges;


十一、事务控制和锁定语句

五、set 命令相关

1、SQL MODE 模式(定义了数据校验,SQL语法,保证数据完整性,有利于数据迁移到其它库)

>set session sql_mode='mode_name';

>select @@sql_mode; 查看数据库有哪些SQL模式

>set session sql_mode='NO_TABLE_OPTION'; 数据迁移时设置该模式,将去掉show create table 中的engine关键字,获得通用的建表脚本

2、innodb迁移数据时,设置以下俩项,便于导入数据loading the data (load data infile '/home/mysql/test.txt' into table tb_name;)

>set UNIQUE_CHECKS=0 关闭唯一性校验,可以提高innodb数据导入效率

>set AUTOCOMMIT=0 关闭自动提交


位运算符

运算符 说明
& 位与
位或
^ 位异或
~ 位取反
>> 位右移
<< 位左移

DML(对数据库表记录进行操作,增(insert)删(delete)改(update)查(select))

1.insert语句
语法:
插入一条insert into tablename(columnname1,columnname2...)values(val1,val2...);

插入多条insert into tablename(columnname1,columnname2...)values(val1,val2...),(val1,val2...);

2.update语句
语法:update tablename set columnname=value [where condition]
如果使用MySQL Workbench,update语句不加where条件的会执行错误,需要如下图设置取消设置:
澳门新濠3559 7

3.delete语句
语法:delete from tablename where condition

4.select语句
语法:select * from tablename [where condition]

select 查询相关的 说明
select distinct name from user; 查询不重复记录
select * from user where id=1; 条件查询
select * from user order by id desc 排序(desc倒序、asc正序)
select * from user order by id desc limit 0,2 根据id进行倒序,0代表从第一个开始,2代表查询出来的个数 select ...[limit offset_start,row_count]
select count(1) from user 聚合函数count(),其他的还有sum()/max()/min()
group by 表示要进行分类聚合select name,count(1) from user group by name
having 表示对分类后的结果再进行条件过滤 select name,count(1) from user group by name having count(1)>2

5.表连接
1.内连接(仅选出两张表中互相匹配的数据)

select cno,cname,sname from  student inner join course on  cno=sno;
select cno,cname,sname from student,course where cno=sno;

2.外连接
外连接又区分:

左连接(left join):包含左边表的所有记录,右边没有的为Null

右连接(right join):包含右边表的所有记录,左边没有的为null

6.子查询

子查询关键字 说明
in 存在
not in 不存在
= 等于
!= 不等于
exists 存在
not exists 不存在

7.记录联合
语法:
1.select * from t1 union all select * from t2;
2.select * from t1 union select * from t2;

union all与union的区别
union all是把结果集直接合并在一起,而union是将union all后的结果进行一次distinct,去除重复后的结果

光标的使用

在存储过程和函数中,可以使用光标对结果集进行循环处理,光标的使用包含光标的声明: open、fetch、close

字符串函数

函数 说明
concat(s1,s2,...,sn) 连接s1,s2,...sn为一个字符串
insert(str,x,y,instr) 将字符串str从第x位置,y个字符串长的子串替换为字符串instr
lower(str) 将字符串str中所有字符变为小写
upper(str) 将字符串str中所有字符变为大写
left(str,x) 返回字符串str最左边的x个字符
right(str,x) 返回字符串str最右边的x个字符
lpad(str,n,pad) 用字符串pad对str最左边进行填充,直到长度为n个字符长度
rpad(str,n,pad) 用字符串pad对str最右边进行填充,直到长度为n个字符长度
ltrim(str) 去掉字符串str左侧的空格
rtrim(str) 去掉字符串str行尾的空格
repeat(str,x) 返回str重复x次的结果
replace(str,a,b) 用字符串b替换字符串str中所有出现的字符串a
strcmp(s1,s2) 比较字符串s1,s2
trim(str) 去掉字符串行尾和行头的空格
substring(str,x,y) 返回字符串str x位置起y个字符串长度的字符串

一、MySQL安装

日期时间类型

日期类型 说明
date 表示年月日
datetime 表示年月日时分秒
time 表示时分秒
timestamp 时间戳(1970-2038)
year 年份(1901-2155)

mysql里面获取当前时间为now().mssql获取当前时间为getdate()

timestamp,支持的范围非常小,从1970-2038年,timestamp受时区的影响

create table timestamptest(
tp timestamp)

系统会自动给tp赋予默认值current_timestamp(系统日期),但是mysql只给第一个timestamp设置默认值,如果有第二个timestamp类型,则默认值设置为0

DCL语句(DCL语句主要是dba用来管理系统中的对象权限)

grant与revoke

六、选择合适的数据类型

五、常用函数

十二、总结

很高兴您能阅读到这里,可能在三十分钟很难吸收这么多的知识,这篇文章也是我之前学习MySQL笔记整合的,但请容许我以这个词为标题。这篇文章也是理论偏多,对于其中比较比较难理解知识点写些Demo,权当个人理解,如有不足的地方,请您指出。如果对您有所帮助,请点个赞!

运算符优先级

大多情况下使用()进行操作

char与varchar

在Innodb存储引擎中,建议使用varchar类型。对于Innodb数据表,内部的行存储格式没有区分固定长度和可变长度列,因此固定长度列的性能不一定比不可变长度的性能好。

二、SQL基础

七、索引的设计和使用

三、MySQL支持的数据类型

数值函数

函数 说明
ABS(x) 返回x的绝对值
ceil(x) 返回大于x的最小整数值。MSSQL则为ceiling。ceil(1.2)返回2
floor(x) 返回小于x的最大整数值。floor(1.2)则返回1
mod(x,y) 返回x/y的模,取余
rand() 返回0~1内的随机数
round(x,y) 返回参数x的四舍五入的有y为小数的值,select round(1.211,2),返回1.21;select round(1.25,1) 返回1.3
truncate(x,y) 返回数字x截断为y位小数的结果.select round(1.25,1)返回1.2

视图(View)

定义:视图是一种虚拟存在的表,对于使用视图的用户来说基本上是透明的,视图并不是在数据库中实际存在。

优势:

  • 简单,用户完全不需要关心后面对应的表的结构/关联条件和筛选条件。对用户来说已经是过滤好的符合条件的结果集
  • 安全,使用视图的用户只能访问他们被允许查询的结果集
  • 数据独立,一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响。

语法:

create or replace view index_view as
select * from indextest
  • 创建create [or replace] view viewName as select ...
  • 查询 select * from 视图名称
  • 展示视图 show tables;
  • 删除视图 drop view viewname

事件调度器

事件调度器是MySQL5.1后面新增的功能,可以将数据库按照自定义时间周期触发某种操作。数据库默认操作是关闭的。需要打开

create event x
on schedule
every 5 second 
do
insert into orders (memberId,serialNumber,amount) values(6561121,'222',5)

set global  event_scheduler =1//打开调度器  
alter event x disable;//禁用事件调度器
drop event x;//删除事件调度器

四、MySQL中运算符

浮点数与定点数

在MySQL中,decimal或者(numberic)用来表示定点数

算术运算符

运算符 说明
+ 加法
- 减法
* 乘法
/,DIV 除法,返回商
%,MOD 除法,

比较运算符,满足返回1,否则返回0

运算符 说明
= 等于
<>,!= 不等于
<=> null安全的等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between 存在于指定范围
in 存在于指定集合
is null 为null
is not null 不为null
like 通配符匹配
regexp,rlike 正则表达式

九、存储过程和函数

SQL语句分类

1.DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create/drop/alter

2.DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用的语句关键字主要包括 insert/delete/update/select等

3.DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant/revoke等

触发器

触发器是在5.02版本后支持的,触发器是与表有关的数据库对象,在满足条件时触发,并执行触发器中定义的语句集合。可以协助应用在数据库端确保数据的完整性

drop trigger orderlog

delimiter $
create trigger orderlog 
after insert on orders for each row
begin
 insert into orderslog (content) values(new.serialNumber);
 end $

 insert into orders (memberId,serialNumber,amount) values(6561121,'sz12234222',5)

解释:上面描述的是创建一个触发器,当往订单表中插入数据之后,在订单日志表插入一条记录。使用old和new来引用触发器发生变化的记录内容,目前只支出行级触发,不支持语句级触发

数值类型

常用的数值类型 字节 说明
int 4 有个额外的额属性Auto_Increment
bigint 8
float 4
double 8
decimal(M,D) M+2 M代表精度,D代表标度

MySQL支持类型后面的小括号指定显示宽度,例如:int(5)表示当数值宽度小于5的时候在数字前面填满宽度,如果不显示指定宽度则默认为int(11)。如果插入的数据大于这个数值宽度,对实际的插入值是没有影响的,是按照int类型的实际大小进行的。

create table valuetype(
age int,
age1 int
)
insert into valuetype(age,age1)values(1,2);//这时候数据库就显示1

alter table valuetype modify age int zerofill;//这时候数据库就显示'0000000001'
位类型 说明
bit(M) 位类型最小1,最大64
create table test(pwd bit(64))

数据插入bit类型字段时,首先转换为二进制,如果位数允许,将插入成功,如果位数小于实际的位置,则插入失败。

Text与blob

一般在保存少量字符串的时候,我们会选择char或者varchar,而在保存较大文本的时候,通常会选择使用text或者blob。两者的区别:text只能保存字符数据,比如日志。blob能保存二进制数据,比如照片。

日期和时间函数

函数 说明
curdate() 返回当前日期。select curdate().2016-08-13
curtime() 返回当前时间
now() 返回当前的日期和时间
week(date) 返回date为一年中的第几周
year(date) 返回日期date的年份
date_format(date,fmt) 返回按字符串fmt格式化日期的date值.fmt的格式化有%M(月),%D(日),%Y(年)
datediff(date1,date2) 返回起始时间和结束时间之间的天数
data_add(date,interval expr type) 返回与所给日期相差interval时间段的日期

编辑:数据库 本文来源:2.路径自定义选择,最近正在整理有道云笔记内容

关键词: