SQL 之增删改 &约束条件 &表操作&库操作

SQL 基本增删改操作 约束条件的操作 对表/库的操作

一、基本增删改

（一）、向表中插入数据

语法：

insert into 表名（字段名,字段名） values(值，值)/*注意：1.数据每次插入都是按行插入2.不能为空的字段，必须插入值3.插入的值的类型要和字段类型一致4.字段的个数要与插入的值的个数保持一致5.字符型或日期型的 值，要用''引起来   数值型，不用单引号6.插入的数据要遵循表中设置的约束（外键、主键、唯一、默认）否则插入失败*/

1.演示 ：向users表中插入一条记录

INSERT INTO users (id,NAME,sex,email,borndate)VALUES(3,'小花','男女是否更好','[e-mail]','2010-9-9');    

2.向可以为空的列插入null值

方式一：//可以不添加值允许为空的字段    INSERT INTO users (id,NAME,sex,email)    VALUES(6,'小丽','男','[e-mail]');  方式二：//可以显示的添加字段 并直接插入值为null    INSERT INTO users (id,NAME,sex,email,borndate)    VALUES(7,'小顺','男','[e-mail]',NULL);

3：如果插入的是全部列的话，所有列名可以省略

INSERT INTO usersVALUES(800,'小顺','男','[e-mail]',NULL)//插入时要注意://应对应所有位置插入所有字段值INSERT INTO users(id,NAME,sex,email,borndate)VALUES(9,'小顺','女','[e-mail]',NOW())   

（二）、修改

语法：

update 表名 set 列名=新值，列名=新值 where 条件UPDATE users SET sex='男' WHERE NAME = '张三';

案例：修改 1-6号的 用户的性别 为 ‘女’，生日 为 今天

UPDATE users SET sex='女',borndate=NOW()WHERE id BETWEEN 1 AND 6;

（三）、删除

1、语法：

方式一：

delete from 表 where 条件

方式二：
truncate table 表

二者的区别：

delete 可以加条件，truncate不能加条件truncate 删除的是表中所有数据，效率比较高，清空日志文件

案例：删除生日为null的用户

DELETE FROM users WHERE borndate IS NULL;

---

二、数据库的事务（表面的认识性了解 详细事务后期专门解析）

/*事务：数据库区分于其他文件系统的一种形式目的：保证数据库的数据从一种状态到另一种状态，数据是完整的！！数据完整性：数据的准确和可靠性账户编号  余额张   1000李   1000update 表 set 余额=1000-500  where 账户编号=张意外update 表 set 余额=1000+500  where 账户编号=李账户编号  余额张   500李   1000特点：ACID     原子性 ：一个事务中的所有语句是一个整体  不可再分割        要么都执行，要么都不执行    一致性：数据的状态保证完整性    隔离性：一个事务和其他事务之间是互不干扰的，相互隔离的    永久性：一个事务提交成功后，持久化到本地分类：    隐式事务：没有明确的开始和结束标记        一条insert、update、delete    显式事务：有明确的开始和结束标记        希望多条sql语句 要么都执行，要么都不执行*/

例如：

SELECT * FROM users;DELETE FROM users;  

取消自动提交
SET autocommit=0;

开启事务
START TRANSACTION;

INSERT INTO users(id,NAME,sex,email,borndate)    VALUES(1,'小顺1','女','[e-mail]',NOW()) ;    INSERT INTO users(id,NAME,sex,email,borndate)    VALUES(2,'小顺2','女','[e-mail]',NOW()); INSERT INTO users(id,NAME,sex,email,borndate)    VALUES(3,'小顺3','女','[e-mail]',NOW()); INSERT INTO users(id,NAME,sex,email,borndate)    VALUES(4,'小顺4','女','[e-mail]',NOW()); INSERT INTO users(id,NAME,sex,email,borndate)    VALUES(5,'小顺5','女','[e-mail]',NOW()); 

ROLLBACK; //此处用回滚ROLLBACE所以以上sql不会成功

结束事务(commit提交 、rollback回滚)

---

★三、约束条件的操作

（一）经常使用的约束条件

/*NOT NULL     非空约束，规定某个字段不能为空UNIQUE       唯一约束，规定某个字段在整个表中是唯一的PRIMARY KEY  主键(非空且唯一) ★FOREIGN KEY  外键CHECK        检查约束（Mysql）DEFAULT      默认值auto_increment  自增长(此处只是呈现 不是约束条件)*/

★约束条件 唯一 UNIQUE

unique（和主键区分）   1、一个表可以有多个唯一   2、可以为空   3、唯一键的值不能重复★ 主键 primary key    1、一个表最多有一个主键   2、必须是非空   3、主键列的值不能重复

自增长(不算约束)

auto_increment1、设置成自增长的列只能是 int类型2、值不用手动插入3、一个表最多有一个自增长列

（二）测试约束 UNIQUE

1 唯一约束 可以为空

//如果给最后字段添加唯一约束测试如下INSERT INTO stuinfo VALUES(1,'john1','男',4);  //可以INSERT INTO stuinfo VALUES(3,'john2','男',5);  //可以INSERT INTO stuinfo VALUES(4,'john3','男',5);  //不可以INSERT INTO stuinfo VALUES(5,'john4','男',6);  //可以INSERT INTO stuinfo VALUES(6,'john5','男',6);  //不可以

3 外键约束

/*1.设置外键的表称为 从表，被引用的表称为主表2.从表中的列值 必须来自于主表的关联列3.插入数据，要先插入主表，再插入从表  删除数据，要先删除从表，再删除主表4.添加外键时    情况一：删除主表被引用的数据时，删不掉     情况二：删除主表被引用的数据时，从表关联的数据也随之删除           on delete cascade    情况三：删除主表被引用的数据时，从表关联的数据置null           on delete set null5.主表和外表的关联列要求 类型必须一致，名称没有要求6.主表的类要求 必须是主键*/

例如：
//stuinfo是主表表 并且被其他表引用了值

    DELETE FROM stuinfo; //删除失败    SELECT * FROM major;  //从表中结果不变

例如： 删除从表major的id 为6的记录

   DELETE FROM major  WHERE majorid = 6;//删除失败

（三）、创建表时，添加约束

/*列级约束：约束直接追加到字段的后面    not null    unique    primary key    外键（不起作用，语法不报错）表级约束 CONSTRAINT：约束不是直接追加到某指定字段的后面       而是所有字段的下面    unique    primary key    外键注意：非空约束和默认值 只能添加 列级约束外键只能添加表级约束才起作用，列级约束 不起作用*/

1 列级约束

CREATE TABLE stuinfo(id INT NOT NULL PRIMARY KEY,NAME VARCHAR(20)  NOT NULL UNIQUE,sex CHAR(1) DEFAULT '男',majorid INT NOT NULL REFERENCES major(majorid))INSERT INTO stuinfo VALUES(1,'john','女',1);INSERT INTO stuinfo VALUES(1,'lu',DEFAULT,30);//因为主键约束 插入失败

2 表级约束

CREATE TABLE stuinfo(id INT NOT NULL ,NAME VARCHAR(20)  NOT NULL ,sex CHAR(1) DEFAULT '男',majorid INT NOT NULL ,CONSTRAINT pk PRIMARY KEY(id),//添加主键约束CONSTRAINT fk FOREIGN KEY (majorid) REFERENCES major(majorid),//添加外键约束CONSTRAINT uq UNIQUE(NAME)//添加唯一约束)

(四)、删除约束
/*

alter table 表名  drop 约束类型  约束名;*/

4-1 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

4-2 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY fk_major_stuinfo;

4-3 删除唯一键

ALTER TABLE 表名 DROP INDEX uq_stuinfo;

4-4 删除 非空约束

ALTER TABLE 表名 MODIFY majorid INT ;

（五）、添加约束

/*alter table 表名 add constraint 约束名约束类型 字段名*/

//添加列及约束

CREATE TABLE stuinfo(id INT NOT NULL ,NAME VARCHAR(20)  NOT NULL ,sex CHAR(1) ,majorid INT )

5-1 添加 主键
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(id);

5-2 添加唯一

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(`name`);

5-3 添加外键

ALTER TABLE 表名 ADD CONSTRAINT 约束名
FOREIGN KEY (majorid) REFERENCES major(majorid) ON DELETE SET NULL;

---

四、对表/库的操作

DDL 数据定义语言

/*create 创建alter 修改drop 删除*/

（一）、数据库的操作

1.创建数据库CREATE DATABASE stu;2.删除数据库DROP DATABASE stu;3.查看当前连接下的所有数据库SHOW DATABASES;

（二）、数据表的操作

1.创建数据表

/*create table 表名(字段名1 字段类型 [长度] [约束] ,//中括号里有时候可以没有 看情况字段名2 字段类型 [长度] [约束] ,...字段名n 字段类型1 [长度] [约束]);注意：varchar和char类型要求必须添加长度*/

例如：

CREATE TABLE student(studentNo VARCHAR(20) NOT NULL,studentName VARCHAR(20) NOT NULL,sex CHAR(2),gradeId INT NOT NULL,borndate DATETIME NOT NULL//注意 最后一行没有逗号);

2 修改表的结构

2-1 添加列ALTER TABLE student ADD COLUMN email VARCHAR(20);2-2 删除列ALTER TABLE student DROP COLUMN email;2-3 修改列（类型）ALTER TABLE student MODIFY COLUMN studentNo INT ;2-4 修改表名ALTER TABLE student RENAME TO stuinfo;2-5 修改列名ALTER TABLE stuinfo CHANGE sex gender CHAR(2);

3查看表的结构

DESC stuinfo;

4 删除表

DROP TABLE stuinfo;

5 表的复制

方式一：仅仅复制表结构

CREATE TABLE student4 LIKE student;

方式二：复制表的结构和数据

CREATE TABLE student3  SELECT * FROM student 【WHERE 条件 】;