Mysql基础汇总-------复习

库管理

显示创建数据库sql语句：

show create database 数据库名；

修改数据库（编码）：

alter database 数据库名 charset=新编码；

删除数据库：

drop database 数据库名；

选择数据库：

use database 数据库名；

表管理：

创建数据表：

简单：

create database 数据库名 charset=utf8；

复杂：

create dadabase if not exists 数据库名 charset=utf8;
# if not exists （判断数据库名是否存在）

删除数据表：

简单：

drop database 数据库名；

复杂:

 drop database if exists 数据库名；

创建表：

create table [if not exists] 表名(
字段名 字段类型 [null|not null] [default 默认值] [auto_increment] [primary key] [comment 备注],
字段名 字段类型,
......
字段名n 字段类型n
) [engine=存储引擎]  charset=编码;

解释：
[if not exists] 判断是否存在，存在：不管，不存在：创建
[null|not null] 声明字段值是否可以插入null数据
[default 默认值] 声明字段值如果没有录入数据则使用默认值
[primary key] 主键（特点：字段值不能重复且不能为null）
[auto_increment] 自增（该属性必须配合primary key效果字段值自动增加1,2,3）
[comment 备注] 声明字段的作用
[charset=编码] 数据存储编码

显示创建表的语法：

show create table 表名\G

（\G代表格式化显示数据，自带结束，不用加分号）

复制表

create table 新表名称 like 原表名称；

增加数据：

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

删除数据：

delete from 表名 [where条件] [order by 排序 asc|desc] [limit 数量];

改数据：

update 表名 set 字段名=新值；

查数据：

select * from 表名;

查看mysql字符集设置：

show variable like 'character_set_%';

查看Mysq版本：

select version();

备注：

comment
eg.commment  '备注内容'

select 查询语句

in|not in

概念：判断字段是否在值定集合中
语法：

select * from 表名 where 字段 in （值1.....值n）;
# in包含在里面，not in不包含在里面

between|not between

概念：判断字段是否在指定范围中

select * from 表名 where 字段 between 值1 and 值2；

字段起别名：as

select 字段 as 别名，字段2 as 别名，字段3 from 表名；

通配符

_（下划线）          表示任意一个字符
%                   表示任意字符

1.模糊查询:like

select * from stu where name like '小%'；

2.分组查询:group by

概念：以分组的形式展示数据，如性别，地区分组；
目的：统计数据

select 列名 from group by 待分组字段名；
eg.select sex,avg(age) from stu group by sex; 

3.结果条件:having

where  条件是对表的数据进行筛选（通常配合分组使用）
hving  设置了查询条件，条件字段必须在查询结果中存在；

4.排序：order by

概念：按照指定字段值升序（asc）或降序（desc）

select * from stu order by age asc;

5.限制limit

select 字段 from 表名 limit 起始位置，显示条数

select * from stu order by id asc limit 3;
// 从0开始计数，0,1,2,3,4

6.联合查询:union

概念：联合查询就是将多条select语句，合并成一条sql语句（注：结果行增非列增）
语法：

select 字段 from 表a union[选项] select 字段 from 表b;

**选项内容**：
     all     显示两个表中所有记录（a表有5条，b表有10条，其中两条重复=15）
     distinct(默认)  去掉两个表中相同的记录（a表有5条，b表有10条，其中两条重复=13）

小总结：
1-联合查询sql语句必须用小括号包起来
2-字段个数必须一致

(select * from u3 where sex='男' order by age desc) union all (select * from u3 where sex='女' order  by age acs);

多表查询

概念：将多个表按照一定顺序组合起来（注：结果列多）

内连接：inner join

[表1.公共字段]用于声明字段属于哪个表，内连接查询两个表字段相同的数据

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段

左连接：left join

概念：以左边的数据为标准，右边的表如果没有对应的的记录用null表示

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段；

右连接：right join

概念：以右边的数据为标准，左边的表如果没有对应的的记录用null表示

select * from 表1 right join 表2 on 表1.公共字段=表2.公共字段；

子查询：针对查询的结果在进行一次查询

分类：
标子量查询：子查询返回单个值（一行一列）
列子量查询：子查询返回一列值（多行一列）
行子查询：子查询返回一行（一行多列）
表子查询：子表查询返回多行多列（一个表）

exists和not exists

含义：判断指定查询语句是否有数据，有则执行主查询，反之则不执行

select 列 from 表名 where exists （子查询）

子查询的特定关键字：

any   查询的结果中，满足任意一个给定的条件
all   查询的结果中，满足所有给定的条件
some=any

视图（view）

概念：视图是存在数据库中虚拟的表（只是结构没有数据，数据来源于其他表），访问视图相当于执行某个特定的sql的语句

创建视图：

create view 视图 as sql语句；
eg.create view v_t1 as select * from stu;

修改视图：

alter view 视图名 as 新sql语句；

删除视图：

drop view 视图名；

精确查找视图：information_schema

select * from information_schema.view\G

显示创建视图的语法：

show create view 视图名\G

显示视图的结构：

desc 视图名；

主键：

primary key

特性：一个表只能有一个主键，不能重复且不能null（除非主键自增）
作用：保证数据完整性，加快查询速度

外键：

概念：从表中的公共字段称之为外键
作用：外键约束用来保证数据的完整性
语法：

foreign key（外键）references 主表 （公共字段）;

使用引擎必须是innodb类型不同
engine 是用来设置表存储引擎，声明表数据存储方式，不同的方式有各自的特性，如，是否支持外键，增删改查性能。

建表后添加外键：

alter table 从表名 add foreign key（外键） references 主表 （公共字段）；

删除外键：

alter table c从表名 drop foreign key 外键名 ；

唯一键：

unquie

注：一个表可以有多个唯一键，不能重复可以为空

事物：

概念：事物用于保护多个sql语句的执行，只要有一个失败则全部失败，反之都成功。
开启事物：

start transaction 或 begin
提交事物：commit
回滚事物（撤销）：rollback
存储引擎必须是innodb

事务的特性：
原子性：整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
隔离性：一个事务的所在的修改在中最终提交以前，对其他事务是不可见的。
一致性：数据库数据从一个一致性的状态转换到另一个一致性的状态。
永久性：提交事务后对数据的修改是永久的。

修改sql语句结束符号：

delimiter //         （改为以//结束）

存储过程：
概念：Mysql 执行语句是要先编译，然后再执行，会浪费很多资源和时间。
解决：通过存储过程提前封装并编译，后期直接调用传递参数即可。
作用：提升性能。

存储过程的操作：

1.创建

delimiter //

create procedure 存储过程名称（[选项] 参数 类型,.........[选项] 参数n 类型n）
brgin 
	sql语句等代码

end //

delimiter ;

调用语法：

call 存储过程名称（参数）;

删除语法：

drop procedure 存储过程名称；

显示所有存储过程语法：

show procedure status\G

查看创建存储过程SQL语句：

show create procedure 存储过程名称；

设置全局变量：

set @变量名 = 值；

查看全局变量：

select @变量名；

设置局部变量：

declare 变量名 变量类型 [default 默认值];

给变量赋值：

set 变量名 = 值;

查询数据赋给变量：

select 字段名1,.......字段名n from 表名 变量名1，.......变量名n;

eg.
  delimiter //

  create procedure p3()
  begin
  declare data1 varchar(30);
  declare data2 varchar(30) default 111;
  declare data3 int;
  set data3 int;
  select data1,data2,data3;
  end //

 delimiter;
 call p3();

形参修饰符（in、out、inout）

in 特性：可以传递参数（默认）
out特性：不可以传递参数，但是可以输出参数（必须定义变量来接受输出参数）
inout特性：既可以传递参数，又可以输出参数。

SQL编程

if判断语句：

if 条件 then 
       #sql语句
elseif 条件 then
       #sql语句
......
else
end if;

while 循环语句

while 条件 do
        #sql语句
end while；

内置函数

数学函数：

select rand()             #随机数
select  round()          #四舍五入
select ceil()                #向上取整
select floor()              #向下取整

字符串函数：

select lenght('哈哈哈');           #返回数组长度
select concat('a','bcd');           #连接字符串
select replace ('abcdef','a',123);           #替换字符串，将a替换成123

截取字符串：

left(字符串，截取长度)
right(字符串，截取长度)
substring(字符串,开始位置,截取长度)

日期函数：

select now();        #当前时间
select unix_timestamp();            #时间戳
select  from_unixtime();

获取年月日时分秒

select
year(now())as '年',
month(now())as '月',
day(now()) as '日',
hour(now()) as '时',
minute(now()) as '分',
second(now()) as '秒';

加密函数：

md5（数据）；
password（数据）；

**条件**
if(数据，值1，值2)        #判断指定数据是否为真：真-值1，假-值2
ifnull（数据，值2）       #判断指定是否为null：null-值2，非null-本身

自定义函数：

delimiter //
create function 函数名 （参数名 类型,.....,参数n 类型n）returns 返回数据类型
begin
       #sql语句
       return 返回值;
end  //
delimiter ;

#调用
select 函数名();

显示所有函数：

show function status\G

删除函数：

drop function 函数名;

存储过程和函数的区别：

存储过程可以返回多值，而自定义函数只能返回一个值
存储过程一般独立执行，而函数往往作为其他sql语句的一部分来使用

触发器（trigger）：

当进行插入数据，修改数据，删除数据之后触发触发器。（前提是提前设置好触发器）

创建触发器：

delimiter //
create trigger 触发器名称 before|after 事件 on 表名 for each row
begin 
     #sql语句
end //
delimiter ;

解释：
before |after 在执行增删改sql语句之前 或 之后 触发
事件 update/delete/insert
表名 声明哪张表被监控
for each row 声明一次性影响多条数据则触发n次

eg.
create table a (id int) engine=myisam charset=utf8;
create table b(id int) engine=myisam charset=utf8;

drop trigger if exists t1;
delimiter //
create trigger t1 after insert on a for each row
begin
insert into b values(9);
end//
delimiter ;

insert into a values(1);

删除触发器：

drop trigger 触发器名；

显示所有触发器：

show triggers;

显示创建触发器的sql语句：

show create trigger 触发器名称;

创建insert 触发器，添加的新数据自动存储在触发器内部的new关键字中
创建delete触发器，被删除的数据自动存储在触发器内部的old关键字中
创建update触发器，被更新的数据自动存储在触发器内部的old/new关键字中
触发器内部获取语法：new.字段名 或 old.字段名