MySQL的常用SQL语句和简单存储过程

技术数据库
MySQL

前言

本文主要是汇总记录，我自己在工作中经常使用的，MySQL数据库的SQL语句，而不是这些SQL语句的说明详解。如果需要快速了解常用MySQL操作，可以查看其他的网友的教程，如：21分钟 MySQL 入门教程

本文第一部分是单独的SQL语句整理，第二部分为组合这些SQL语句的存储过程写法的介绍。

MySQL常用的语句

  1. 登陆数据库系统

    • 本地： mysql -u [username] -p [password]
    • 远程： mysql -h [ip] -P [port] -u [username] -p [password]

  2. 库的操作

    • 创建数据库： create database [databasename]
    • 显示数据库： show databases
    • 进入数据库： use [databasename]
    • 删除数据库： drop database if exists [databasename]

  3. 表的操作

    • 创建表：create table [databasename] (col1 bigint not null,col2 varchar(15),col3 double precision,col4 bigint,primary key (col1)) ENGINE=InnoDB
    • 显示表： show tables
    • 删除表： drop table [tablename] if exists ORDERS
    • 获取列信息： desc [columnname]
    • 清空表： truncate table [tablename];
    • 复制表的结构到新表： create [newtable] like [oldtable]
    • 复制表的数据到新表： insert into [table1] (col1, col2, …) select col1, col2, … from [table2] where [condition]
    • 插入数据时覆盖已有数据： replace into [table1] (col1, col2, …) select col1, col2, … from [table2] where [condition]
    • 插入数据时忽略已有数据： insert ignore into [table1] (col1, col2, …) where [condition]
    • 重命名表： alter table [tablename] rename [newtablename]
    • 增加一列： alter table [tablename] add column [columnname] [type] default [default]
    • 修改列名： alter table [tablename] change [oldlolname] [newcolname] [type]
    • 删除一列： alter table [tablename] drop [colname]

  4. 主键操作

    • 设置主键： alter table [tablename] add primary key([colname])
    • 删除主键： alter table [tablename] drop primary key
    • 设置自增字段(前提是该列为一个key)： alter table tb change [colname] [colname] int(10) not null auto_increment=1
    • 删除自增字段： alter table [tablename] change [colname] [colname] int(10)

  5. 记录

    • 插入记录： insert [table1] (col1,col2…) [table2] (val1,val2…)
    • 删除记录： delete from [tablename] where [condition]
    • 查找记录： select [columnname] from [tablename] where [condition]
    • 修改字段值： update [tablename] set [col]=[val],… where [condition]

  6. 用户的操作

    • 添加用户： insert into mysql.user(Host,User,Password) values(“localhost”,”test”,password(“1234”));
    • 删除用户： delete from user where User=’test’ and Host=’localhost’
    • 分配所有权限： grant all privileges on testDB.* to test@localhost identified by ‘1234’
    • 刷新权限： flush privileges
    • 修改密码： mysqladmin -u root -p password [newpassword]

  7. 参数与状态

    • 查看配置参数(以wait_timeout为例)： show variables like ‘%wait_timeout%’
    • 查看状态： show status
    • 设置参数： set [paraName] = [value]
    • 设置全局参数(重启后仍然会失效)： set global [paraName] = [value]

存储过程

  1. 引用系统变量

    • 全局变量： @@global.[varname]
    • 局部变量： @@session.[varname] 或者 @@local.[var_name]

    如果在变量前面没有级别限定，则优先会话级别的变量。

  2. 声明或定义变量

    变量的作用域只能在begin…end块中，不区分大小写

    1
    declare [val] [type];

  3. 创建存储

    注意begin和end后无分号，不能有return

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create procedure [procedurname]
    (
        [param] [type],[param] [int],.....
    )
    begin
    ...;
    ...;
    ...;
    end
    }

  4. 调用存储过程

    1
    call [procedurname]([param,...])

  5. 删除存储过程

    1
    drop procedure if exists [proceduename]

  6. 实例

    delimiter $$命令就是将语句的结束符从分号修改成其他符号，这里指的是$$为结尾

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    delimiter $$
    drop procedure if exists wk;
    create procedure wk()
    begin
    declare i bigint; 
    set i = 17011210000;
    while i < 17011213999 do
    insert ignore into mini_s(pn) values(i);
    set i = i+1;
    end while;
    end $$

    call wk();
Compartir