数据库操作

2023-05-16

增删改查：

对表结构进行修改

语法：
    1. 修改表名
        ALTER TABLE 表名
                            RENAME 新表名;
    2. 增加字段
          ALTER TABLE 表名
                              ADD 字段名  数据类型 [完整性约束条件…],
                              ADD 字段名  数据类型 [完整性约束条件…];
          ALTER TABLE 表名
                              ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
          ALTER TABLE 表名
                              ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    3. 删除字段
          ALTER TABLE 表名
                              DROP 字段名;
    4. 修改字段
          ALTER TABLE 表名
                              MODIFY  字段名 数据类型 [完整性约束条件…];
          ALTER TABLE 表名
                              CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
          ALTER TABLE 表名
                              CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示范：
    1. 修改存储引擎
        mysql> alter table service
            -> engine=innodb;
    2. 添加字段
        mysql> alter table student10
            -> add name varchar(20) not null,
            -> add age int(3) not null default 22;

        mysql> alter table student10
            -> add stu_num varchar(10) not null after name;                //添加name字段之后

        mysql> alter table student10
            -> add sex enum('male','female') default 'male' first;          //添加到最前面
    3. 删除字段
        mysql> alter table student10
            -> drop sex;
        mysql> alter table service
            -> drop mac;
    4. 修改字段类型modify
        mysql> alter table student10
            -> modify age int(3);
        mysql> alter table student10
            -> modify id int(11) not null primary key auto_increment;    //修改为主键
    5. 增加约束（针对已有的主键增加auto_increment）
        mysql> alter table student10 modify id int(11) not null primary key auto_increment;
        ERROR 1068 (42000): Multiple primary key defined
        mysql> alter table student10 modify id int(11) not null auto_increment;
        Query OK, 0 rows affected (0.01 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    6. 对已经存在的表增加复合主键
        mysql> alter table service2
            -> add primary key(host_ip,port);
    7. 增加主键
        mysql> alter table student1
            -> modify name varchar(10) not null primary key;
    8. 增加主键和自动增长
        mysql> alter table student1
            -> modify id int not null primary key auto_increment;
    9. 删除主键
        a. 删除自增约束
        mysql> alter table student10 modify id int(11) not null;
        b. 删除主键
        mysql> alter table student10
            -> drop primary key;

对表的内容进行修改
增：insert

语法：INSERT INTO 表名（字段名1，字段名2，…） VALUES（值1，值2，…）；
举例：INSERT INTO student(id,name,grade)  VALUES(1,'zhangshan',98);
语法：INSERT INTO 表名[(字段名1，字段名2，…)] VALUES （值1，值2，…），（值1，值2，…），…（值1，值2，…）
举例：INSERT INTO student VALUES (5，‘lilei’,99),(6,'hanmeimei',87),(8,'poly',76);

删：delete truncate

删除指定的部分数据，需要使用WHERE子句来指定删除记录的条件。
举例：删除student表中的id值为7的记录
命令：DELETE  FROM student WHERE id=7;

语法：TRUNCTE [TABLE ] 表名
举例：TRUNCATE TABLE student；即可删除student表
注意：
（1）DELETE 后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录
（2）使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段（如本文中student表中的 id 字段）会默认从1开始，而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除时该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录。

改：update

语法：UPDATE 表名 SET 字段名1=值1，，字段名2=值2，…　[ WHERE 条件表达式 ]
命令：UPDATE student SET name=‘caocao’,grade=50 WHERE id=1;

查：select（distinct去重复）

select  字段名 from 表名 where 条件 group by 字段名 having 条件 order by 字段名（desc、asc）

from 1 where 2 group by 3 having 4 select 5 order by 6
GROUP BY 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等
HAVING关键字和WHERE关键字的作用相同，区别在于HAVING 关键字可以跟聚合函数，而WHERE 关键字不能。通常HAVING 关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤

索引

index`

优点：创建索引可以大大提高系统的性能。
　　　　1):通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
　　　　2):可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
　　　　3):可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
　　　　4):在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　　　　5):通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点： 1):创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
　　　　2):索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
　　　　3):当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

建立索引：
 create index 索引名 on 表名（索引字段名）
建立唯一索引：
 create unique index 索引名 on 表名（索引字段名）
建立复合索引：
create index 索引名 on 表名（索引字段名1，索引字段名2...）
查看表中索引：
show index from 表名
删除索引：
drop index 索引名 on 表名

drop index 索引名 on 表名

触发器：

trigger

定义一个触发器，一旦有满足条件的删除操作，就会执行BEGIN和END中的语句
mysql> DELIMITER ||//改变结束符
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

约束：

1、主键约束 primary key
主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

-- 基本模式
create table temp( 
id int primary key,
name varchar(20)
);

-- 组合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);

2、外键约束 foreign key
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);

-- 副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表级别联合外键*/
foreign key(classes_name, classes_number) references classes(name, number) 
);

3、唯一约束unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。

-- 创建表时设置，表示用户名、密码不能重复
    create table temp(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);

4、非空约束 not null 与默认值 default
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
           id int not null,
           name varchar(255) not null default  'abc',
           sex char null
)；

事务：

事务控制语句
1，显式的开始一个事务：
start transaction或begin
2, 做保存点，一个事务中可以有多个保存点：
savepoint 保存点名称
3, 提交事务，并使数据库中进行的所有修改成为永久性的：
commit或commit work
4，回滚结束用户的事务，并撤销正在进行的所有未提交的修改：
rollback或rollback work
5.删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。
release savepoint 保存点名称
6.将事务滚回标记点：
rollback to 标记点
7.设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
set transaction
事务处理方法
1.用 begin ， rollback ， commit 来实现事务处理。
2.用 set 来改变 MySQL 的自动提交模式。
set autocommit = 0 （禁止自动提交）。
set autocommit = 1 （开启自动提交）。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)