SQL删除多列语句

最近在写SQL过程中发现需要对一张表结构作调整(此处是SQL Server),其中需要删除多列,由于之前都是一条SQL语句删除一列,于是猜想是否可以一条语句同时删除多列,如果可以,怎么写法?

本章内容:

MySQL 系列(三)你不知道的 视图、触发器、存储过程、函数、事务、索引、语句

 

 

前天有一个五年java开发经验工程师,问我存储过程怎么写?

My god, 你这个也太 low 了!

 

本章内容:

  • 视图、增/删/改/查
  • 触发器、增/删/改/查
  • 存储过程、增/删/改/查
  • 存储过程三种传参,pymysql 怎么用?
  • 函数、增/删/改/查/return值
  • 内置函数
  • 事务
  • 索引!

 

第一次猜想如下(注意:此处是猜想,非正确的写法):

  1. 查看创建使用删除
    数据库
  2. 用户管理及授权实战
  3. 局域网远程连接法
  4. 查看创建使用删除清空修改
    数据库表(是否可空,默认值,主键,自增,外键)
  5. 表内容的增删改查
  6. where条件、通配符_%、限制limit、排序descasc、连表join、组合union
  7. 查看建表语句、查看表结构、查看是否走索引
  8. 数据类型
  9. 索引!

一、视图

视图是查询命令结果构成的一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集合,并可以当作表来查询使用。

ALTER TABLE TableName DROP COLUMN column1,column2

一、数据库操作

1、创建视图

--格式:CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
    name
FROM
    A
WHERE
    nid > 4

但是执行后,发现语法错误, 于是改成如下的方式:

1、查看数据库

SHOW DATABASES;

# 默认数据库:
  mysql - 用户权限相关数据
  test - 用于用户测试数据
  information_schema - MySQL本身架构相关数据

 

2、删除视图

--格式:DROP VIEW 视图名称

DROP VIEW v1
ALTER TABLE TableName DROP COLUMN column1,COLUMN column2

 2、创建数据库

# utf-8 编码
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# gbk 编码
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

3、修改视图

-- 格式:ALTER VIEW 视图名称 AS SQL语句

ALTER VIEW v1 AS
SELET A.nid,
    B. NAME
FROM
    A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
    A.id > 2
AND C.nid < 5

执行正确,之后查看表结构,发现列已删除,证明猜想正确。

3、使用数据库

USE db_name;

# 可以不使用分号

4、使用视图

视图的使用和普通表一样,由于视图是虚拟表,所以无法对其真实表进行创建、更新和删除操作,仅做查询用。

select * from v1

 

 4、用户管理

 

# 创建用户
    create user '用户名'@'IP地址' identified by '密码';
# 删除用户
    drop user '用户名'@'IP地址';
# 修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
# 修改密码
    set password for '用户名'@'IP地址' = Password('新密码')

PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)

 

 

# 查看当前用户
        select user();
# 查看所有用户
        select host,user from mysql.user;
# 人性化显示所有用户
        SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
# 查看用户的所有权限
        show grants for 'nick'@'%';




mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+---------------------------+
| query                     |
+---------------------------+
| User: 'nick'@'%';         |
| User: 'root'@'localhost'; |
+---------------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> select host,user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | nick |
| localhost | root |
+-----------+------+
2 rows in set (0.00 sec)

mysql> show grants for 'nick'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for nick@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nick'@'%' IDENTIFIED BY PASSWORD '*ECE7D02DCD7D4EF7CFE8E3B249FD1D5062A821F7' |
| GRANT ALL PRIVILEGES ON `kaoshi`.* TO 'nick'@'%'                                                    |
| GRANT ALL PRIVILEGES ON `xxxxx`.* TO 'nick'@'%'                                                    |
| GRANT ALL PRIVILEGES ON `xxxxxx`.`chouti` TO 'nick'@'%'                                              |
+-----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

图片 1图片 2

all privileges  除grant外的所有权限
            select          仅查权限
            select,insert   查和插入权限
            ...
            usage                   无访问权限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存储过程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(关闭MySQL)
            super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
            replication client      服务器位置的访问
            replication slave       由复制从属使用

更多权限

mysql> delete from mysql.user;
Query OK, 2 rows affected (0.00 sec)
mysql> grant all privileges on *.* to system@'localhost' identified by 'oldsuo' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| system | localhost |
+--------+-----------+
1 row in set (0.00 sec)
mysql>

 

图片 3授权示例

图片 4创建账号授权步骤

图片 5图片 6

#百分号匹配法
    grant all on *.* to 'test'@'192.168.200.%' identified by 'test123';
#子网掩码配置法
    grant all on *.* to 'test'@'192.168.200.0/255.255.255.0' identified by 'test123';
#刷新权限
    flush privileges;
#远程登陆连接
    mysql -utest -ptest123 -h 192.168.200.96

局域网授权示例

 

二、触发器

对某个表进行【增/删/改】操作的前后触发一些操作即为触发器,如果希望触发增删改的行为之前或之后做操作时,可以使用触发器,触发器用于自定义用户对表的行进行【增/删/改】前后的行为。

二、表操作

1、基本语法

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

1、创建表

# 基本语法:
create table 表名(
    列名  类型  是否可以为空  默认值  自增  主键,
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

not null         # 不可以为空
default 1        # 默认值为1
auto_increment   # 自增
primary key      # 主键
constraint 外键名 foreign key (从表字段’自己‘) references 主表(主键字段)    # 外键

        是否可空,null表示空,非字符串
            not null    - 不可空
            null        - 可空

        默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )

 

       自增,如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列)
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null auto_increment,
                num int null,
                index(nid)
            )
            注意:1、对于自增列,必须是索引(含主键)。
                 2、对于自增可以设置步长和起始值
                     show session variables like 'auto_inc%';
                     set session auto_increment_increment=2;
                     set session auto_increment_offset=10;

                     shwo global  variables like 'auto_inc%';
                     set global auto_increment_increment=2;
                     set global auto_increment_offset=10;

 

 

     主键,一种特殊的唯一索引,不允许有空值,如果主键使用单个列,则它的值必须唯一,如果是多列,则其组合必须唯一。
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )

 

 

        外键,一个特殊的索引,只能是指定内容
            creat table color(
                nid int not null primary key,
                name char(16) not null
            )

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )

 

2、创建触发器

创建触发器基本语法是以下code:

但有一点要注意,触发器内关键字NEW表示即将插入的数据行,OLD表示即将删除的数据行。

# 插入前触发器

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'nick' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('aa')
END
END//
delimiter ;

# 插入后触发器

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('666'),
            ('666') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('555'),
            ('555') ;
    END IF;
END//
delimiter ;

 2、删除表

drop table 表名

3、删除触发器

DROP TRIGGER tri_after_insert_tb1;

3、清空表

# 表还存在,表内容清空

delete from 表名
truncate table 表名

4、使用触发器

触发器是由于对表的增、删、改操作时被动执行的

insert into tb1(num) values(666)

 

4、修改表

 

# 添加列:
        alter table 表名 add 列名 类型
# 删除列:
        alter table 表名 drop column 列名
# 修改列:
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名,类型

 

# 添加主键:
        alter table 表名 add primary key(列名);
# 删除主键:
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;

# 添加外键:
        alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
# 删除外键:
        alter table 表名 drop foreign key 外键名称

# 修改默认值:
        ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# 删除默认值:
        ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

# 更改表名
         rename table 原表名 to 新表名;

 

三、存储过程

存储过程是一个SQL语句集合,类似函数,需要主动调用。

 三、表内容操作

1、创建存储过程

# 无参数存储过程

# 创建存储过程
delimiter //
create procedure p1()
BEGIN
    select * from t1;
END//
delimiter ;

# 执行存储过程
call p1()

都说了类似函数,那必须得可以接收参数,且参数有三类:

  • in          仅用于传入参数用
  • out        仅用于返回值用
  • inout     既可以传入又可以当作返回值

    # 有参数存储过程

    # 创建存储过程
    delimiter # 结尾分号改为
    create procedure p1(

    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
    

    )
    BEGIN

    DECLARE temp1 int;    # 创建申明局部变量
    DECLARE temp2 int default 0;
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100;
    

    end
    delimiter ;

    # 执行存储过程
    DECLARE @t1 INT default 3;
    DECLARE @t2 INT;
    CALL p1 (1, 2 ,@t1, @t2);
    SELECT @t1,@t2;

1、增

语法:insert into 表 (列名,列名...) values (值,值,值...)

 

# 插入单条数据
        insert into 表 (列名,列名...) values (值,值,值...)
# 插入多条数据
       insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
# 插入另一条语句的查询结果
        insert into 表 (列名,列名...) select 列名,列名... from 表

 

2、删除存储过程

drop procedure proc_name;

2、删

语法:delete from 表

delete from 表;
delete from 表 where id=1;

3、执行存储过程

执行为函数名加括号;

DECLARE代表创建一个局部变量

# 无参数
call proc_name()

# 有参数,全in
call proc_name(1,2)

# 有参数,有in,out,inout
DECLARE @t1 INT;
DECLARE @t2 INT default 3;
call proc_name(1,2,@t1,@t2)

图片 7图片 8

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

pymysql执行存储过程

 

3、改

语法:update 表 set name = 'nick' where id>1

update 表 set name = 'nick' where id>1

四、函数

该讲讲真正的函数了,与存储过程不同的是有return值

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图