mysql常用操作

数据库基本操作

查看基本信息

• 显示服务器版本
  SELECT VERSION();
• 显示当前日期时间
  SELECT NOW();
• 显示当前用户
  SELECT USER();

修改数据库存储引擎

• my.ini 文件中配置 default-storage-engine=INNODB

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXIST] db_name [DEFAULT] CHARACTER SET [=] UTF8

修改数据库

ALTER {DATABASE | SCHEMA } [DB_NAME] [DEFAULT] CHARACTER SET [=] CHARSET_NAME

删除数据库

DROP {DATABSE | SCHEMA} [IF EXISTS] DB_NAME

数据类型

• 整型
  TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT

• 浮点型
  FLOAT[(M,D)]（常用） < DOUBLE[(M,D)]

• 日期
  YEAR < TIME <DATE < DATETIME < TIMESTAMP

• 字符型
  CHAR < VARCHAR < TINYTEXT < TEXT < MEDIUMTEXT < LONGTEXT < ENUM('VAL','VAL') < set(val, val2)

创建数据表

• UNSIGNED 无符号位（即不要负数，节省存储空间）

create table t1(
  username varchar(20),
  age tinyint UNSIGNED,
  salary float(8, 2) UNSIGNED
)

NULL 与 NOT NULL （设置字段可不可以为空）

查看数据表

SHOW TABLES [FORM DB_NAME] [LIKE 'PATTERN' | WHERE expr]

查看数据表结构

show columns from table_name

插入记录

insert [into] tb_name [col_name, ... ] values (val, ...)

insert [into] tb_name set col_name = {expr|dafault},...

insert [into] tb_name [col_Name] select...

insert user(username) select username from user2 where age >30;

查找记录

select * from tb

primary key 主键约束

• 每张表只有一个主键
• 常与 auto_increment 一起使用
• 自动为 NOT NULL

UNIQUE KEY

• 与主键类似，区别在于主键在一张表中只能有一个

DEFAULT 默认约束

create table tb(
  id smallint unsigned auto_increment primary key,
  username varchar(20) not null unique key,
  sex enum('male', 'female') DEFAULT 'male'
) 

FOREIGN KEY 外键约束

• 父子表使用相存储引擎，InnoDB
• 外键列与参照列必须有相似数据类型
• 若外键列不存在索引，则自动创建

外键约束的参照操作

• cascade: 从父表删除或更新时自动删除或更新子表中匹配的项
• set null: 从父表删除或更新行，并设置子表中外键列为 null.
  若使用该选项，必须保证字表列没有指定 not null
• restrict: 拒绝对父表的删除或更新操作。
• no action: 标准 sql 关键字，在 mysql 中与 restrict 相同

create table user1(
  id smallint unsigned primary key auto_increment,
  username varchar(10) not null,
  pid smallint unsigned,
  foreign key (pid) references province(id) on delete cascade
)

修改数据表

添加单列

alter table tb_name add [column] col_name col_definition [first |after col_name] 

删除列

alter table tb_name drop [column] col_name

添加主键约束

alter table tb_name add [constraint [symbol]] primary key [index_type] (index_col_name, ...)

alter table user2 add constraint pk_user_id primary key (id); 

删除主键约束

alter table tb_name drop primary key;

添加唯一约束

alter table tb_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,..)

alter table user2 add unique (username)

删除唯一约束

alter table tb_name drop {index|key} index_name;

alter table user2 drop index username;

添加外键约束

alter table tb_name add [constraint [symbol]] foreign key [index_name] (index_col_name, ...) reference definition

alter table user2 add foreign key (pid) references province(id)

删除外键约束

alter table tb_name drop foreign key fk_symbol

添加/删除默认约束

alter table tb_name alter [column] col_name {set default literal | drop default}

alter table user2 alter age set default 15;

alter table user2 alter age drop default;

修改列定义

alter table tb_name modify [column] col_name column_definition [first | after col_name] 

<!-- 将 id 字段放到第一列 -->
alter table user modify id smallint not null first;

修改列名称

alter table tb_name change [column] old_col_name new_col_name column_definition [first | after col_name];

alter table user change pid p_id unsigned not null;

数据表重命名

alter table tb_name rename [to|as] new tb_name;

rename table tb1_name to new_name

更新记录（单表更新）

update [low_priority] [ignore] tb_reference set col_name = {expr|default} [,col_name2={e|d},...] [where where_condition]

update users set age=age+5;

update users set age = age -id, sex = 0;

update users set age = age + 10 where id % 2 = 0;

删除记录（单表删除）

delete from tb_name [where where_condition]

delete from users where id=6;

查找记录

select select_expr [,select_expr ...]
[
  from table_references
  [where where_condition]
  [group by {col_name|position} [asc|desc], ...]
  [having where_condition]
  [order by {col_name|expr|position} [asc|desc], ...]
  [limit {[offsetm] row_count | row_count offset}]
]

select id,username from users group by id;

<!-- group 分组 -->
select sex,age from users group by age having age > 5;
<!-- count()聚合函数，还有max(),min()等 -->
select sex from users group by 1 having count(id) > 2;

<!-- order:排序 -->
select * from users order by age, id desc;

<!-- limit 可用于分页操作-->
select * from users limit 2,2;  //获得第 3,4 条数据（sql 从 0编号）

<!-- as 使用别名 -->
select id as username from users;

子查询: select 中 select

select * from t1 where col1 = (select col2 from t2)

使用比较运算符的子查询

• 语法结构
  operand comparison_operator subquery

any、some、all 修饰比较运算符

operand comparison_operator any(subquery)
operand comparison_operator some(subquery)
operand comparison_operator all(subquery)

 > any(subquery)  返回大于子查询结果中最小的值 
 > all(subquery)  返回大于子查询结果中最大的值

in 和 NOT in 的子查询

operand comparison_operator [not] in (subquery)
=any 与 in 等效
！=all 或 <>all 与 NOT in 等效

exists 与 NOT exists 的子查询

exists 则返回 true

insert...select

• 将查询结果写入数据表
  insert [into] tb_name [(col_name,...)] select ...
insert into tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate

create...select

• 创建数据表的同时将查询结果写入到数据表

create table [if not exists] tb_name [(create_defineition,...)] select_statement

create table tdb_goods_brands(
  brand_id smallint unsigned primary key auto_increment,
  brand_name varchar(40) not null
) select brand_name from tdb_goods group by brand_name;

多表更新

update table_references
set col_name={expr|default} 
[, col_name2={expr|default}] 
[where where_condition]

update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name
set goods_cate = cate_id;

连接类型

• inner join 内连接
  在 mysql 中，join, cross join 和 inner join 是等价的。
  left [outer] join, 左外连接
  right [outer] join, 右外连接

• on 设置连接条件
  内连接：两个表的交集
  select goods_id, goods_name, cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;

• left join: 左表中的全部和右表中符合条件的

• right join: 右表中的全部和左表中符合条件的

多表连接

select goods_id, goods_name, cate_name, brand_name, goods_price from tdb_goods as g
inner join tdb_goods_cates as c on g.cate_id = c.cate_id 
inner join tdb_goods_brands as b on g.brand_id = b.brand_idG;

自身连接

• 同一个数据表对自身进行连结。

select c.type_id, c.type_name, p.type_name 
from tdb_goods_types as c 
left join tdb_goods_types as p
on c.parent_id = p.type_id;

多表删除

delete tb_name [.*] [,tb_name[.*]]... 
from table_references
[where where_condition]

//删除表中重复数据，id 号较大的
delete t1 from tdb_goods as t1 
left join
(select goods_id, goods_name from tdb_goods 
group by goods_name having count(goods_name) >=2) as t2 on t1.goods_name = t2.goods_name where t1.goods_id > t2.goods_id; 

函数

字符函数

• concat() select concat('first_name', 'last_name')as fullname from user;
• concat_ws() select concat_ws('|', 'a', 'b') //'a|b'
• format() select format(12560.75, 1) 12560.8
• lower()
• upper()
• left() select left('mysql', 2) //my
• right() select right('mysql', 3) //sql
• length()
• ltrim()
• rtrim()
• trim() select trim(both '?' from '??my??sql????'
• substring()
• [not] like select 'mysql' like 'm*'
• % 表示任意字符 _ : 表示任意一个字符
• like '%1%%' escape '1' //表示 1 后的字符不需匹配
• replace() replace('ab？c', '？'，'')

数值运算符与函数

• ceil()
• floor()
• div 3 div 4
• mod 5 mod 3
• power() 幂运算
• round() round(3,62, 0) //4
• truncate() 数值截取
• [not] between ... and ... select 34 between 1 and 22
• [not] in() select 13 in(5,10,15,20)
• is [not] null select null is null;

时间日期函数

• now() 当前日期和时间
• curdate() 当前日期
• curtime() 当前时间
• date_add()

select date_add('2018-8-10', interval 3 week);
select date_add('2018-8-10', interval 1 year);

• datediff() 日期差值计算 datediff('2013-3-12', '2014-03-12')
• date_format() 日期格式化
  date_format('2014-3-2', '%m/%d/%Y')

信息函数

• connection_id() 连接id
• datebase()
• last_insert_id() 新写入记录的 id 号
• user()
• version()

聚合函数

• avg()
• count()
• max()
• min()
• sum()

加密函数

• md5() 信息摘要算法 select md5('admin')
• password() 密码算法 set password = password('heiha')

函数

自定义函数两个必要条件

• 参数
• 返回值

创建自定义函数

create function function_name
returns
{string|integer|real|decimal}
routine_body  //函数体

//不带参数的函数        指定返回类型
create function f1() returns varchar(30)
return date_format(now(), '%Y年%m月%d日 %H点:%i分:%s秒');


//带参数
create function f2(num1 smallint unsigned, num2 smallint unsigned)
returns float(10, 2) unsigned
return (num1 + num2)/2

<!-- 复合结构的函数体 -->
delimiter //  
create function adduser(username varchar(20))
returns int unsigned
<!-- 具有复合结构的函数体必须包含在 begin...end 之间 -->
begin
insert test(username) values (username);
return last_insert_id();
end
//

select adduser('rose')

• 修改结束符
  delimiter //

mysql 存储

存储过程

sql命令 -> mysql 引擎(分析) -> 语法正确 -> 可识别的命令(执行) -> 结果 -> (返回给)客户端

存储过程是 sql 语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理,下次再次访问时直接读取缓存,不用再编译

创建存储过程

create
[define = {user|current_user}]
procedure sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[in|out|inout] param_name type

• in: 表示该参数的值必须在调用存储过程时指定
• out: 表示该参数值可以被存储过程改变,并且可以返回
• inout: 表示该参数调用时指定,并可被改变和返回

过程体

• 由合法 sql 语句组成
• 可以使任意(CRUD)sql语句
• 若是复合结构则使用 begin...end
• 可包含声明,循环,控制结构

创建不带参数的存储过程

create procedure sp1() select version();

<!-- 调用 -->
call sp1;

call sp1();

delimiter //
create procedure removeUserById(in p_id int unsigned)

<!-- 传参的 命名id 不能和 where 后的 id 相同,否则会全部删除 -->
delete from users where id = p_id;
end
//

<!-- 调用 -->
delimiter ;
call removeUserById(3);


delimiter //
create procedure removeUserAndReturnUserNums(in p_id int unsigned, out userNums int unsigned)
begin
delete from users where id = p_id;
select count(id) from users into userNums;
end
//

<!--  -->
delimiter ;
select count(id) from users;
call removeUserAndReturnUserNums(27, @nums);

删除存储过程

drop procedure [if exists] removeUserById

局部变量与用户变量

begin
//局部变量
declare ...
end

set @i = 7; //用户变量

mysql支持的存储引擎

• 存储引擎

• myisam (适用于事务处理不多的情况,支持索引,表级锁定,数据压缩,存储 256TB)
• innodb(只有innodb 支持事务,外键;行级锁定,存储限制:64TB)
• memory
• csv
• archive

• 并发控制
  当多个连接对记录进行修改时保证数据的一致性和完整性.

• 锁

• 共享锁(读锁)
• 排它锁(写锁)

• 事务

• 用于保证数据库的完整性

修改存储引擎

创建时指定

create table tb_name(

) ENGINE = engine;

修改引擎

alter table t1 ENGINE = engine;