• 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;
  • 相关阅读:
    【JAVA】浅谈java枚举类
    【JAVA】浅谈java枚举类
    WinForm时间选择控件（DateTimePicker）如何选择（显示）时分秒
    WinForm时间选择控件（DateTimePicker）如何选择（显示）时分秒
    SQL Server中的between（在某个范围）
    SQL Server中的between（在某个范围）
    如何安装Microsoft网络客户端 无法访问局域网的共享文件夹
    如何安装Microsoft网络客户端 无法访问局域网的共享文件夹
    SQL Server 中WITH (NOLOCK)浅析
    SQL Server 中WITH (NOLOCK)浅析
  • 原文地址：https://www.cnblogs.com/fdbk/p/9479646.html
Copyright © 2020-2023  润新知