数据分析之MySql篇{数据定义DDL与数据操作DML}

  由于篇幅的设置，文章中介绍了代码作用和书写规范，真正的案例操作我上传到了资源，建议大家自己去软件上运行一下—MySql 基础练习题资源

 关于MySql基础学习的链接:

• 数据分析之MySql篇{数据库基础概念}
• 数据分析之MySql篇{数据查询语言DQL}
• 数据分析之MySql篇{常用函数}
• 数据分析之MySql篇{练习题}
• 数据分析之MySql篇{电商数据处理案例}

数据定义DDL

数据库的增删选查

• 查看数据库：show databases;
• 创建数据库：create database 数据库名称;

  数据库名称不能与SQL关键字相同，也不能重复

• 选择使用数据库：use 数据库名称;
• 删除数据库：drop database 数据库名称;

数据表的增删改查

• 创建数据表：create table 表名(…);

   建表之前要先选择进入数据库：use 数据库名称; 建表时可以不指定约束条件，但是必须指定表名、字段名及每个字段的数据类型。表名不能与SQL关键字相同，同一个数据库下的表名不能重复

create table department(depno int, 
dname varchar(10),
num int);

• 查看当前数据库中所有表： show tables;
• 查看表结构：desc 表名;
• 删除数据表： drop table 表名; 不仅会删除表结构，表内的数据也会被删除。

约束条件

  约束条件是在表上强制执行的数据检验规则

• 用来保证创建的表的数据完整性和准确性
• 主要在两方面对数据进行约束：空值和重复值
  

主键约束(primary key)

• 每个表中只能有一个主键

  主键值须非空不重复，可设置单字段主键，也可设置多字段联合主键，联合主键中多个字段的取值完全相同时，才违反主键约束

 添加主键约束：

  列级添加主键约束：create table <表名> (<字段名1> <字段类型1> primary key，……<字段名n> <字段类型n>);

  表级添加主键约束：create table <表名>(<字段名1> <字段类型1>,......<字段名n> <字段类型n>,[constraint 主键约束名]

唯一约束(unique)

• 指定字段的取值不能重复

 添加唯一约束：

  列级添加唯一约束：create table <表名> (<字段名1> <字段类型1> unique,……<字段名n> <字段类型n>);

  表级添加唯一约束：create table <表名> (<字段名1> <字段类型1,……<字段名n> <字段类型n>,[constraint 唯一约束名] unique (字段名1[,字段名2...字段名n]));

自动增长列(auto_increment)

• 指定字段的取值自动生成，默认从1开始，每增加一条记录，该字段的取值会加1

 只适用于整数型，配合主键一起使用

 创建自动增长约束：

create table <表名> (<字段名1> <字段类型1> primary key auto_increment,……<字段名n> <字段类型n>);

非空约束(not null)

• 字段的值不能为空

 创建非空约束

create table <表名> (<字段名1> <字段类型1> not null,……<字段名n> <字段类型n>);

默认约束(default)

• 如果新插入一条记录时没有为该字段赋值，系统会自动为这个字段赋值为默认约束设定的值

 创建默认约束

create table <表名> (<字段名1> <字段类型1> default value,……<字段名n> <字段类型n>);

外键约束(foreign key)

• 在一张表中执行数据插入、更新、删除等操作时，DBMS都会跟另一张表进行对照，避免不规范的操作，以确保数据存储的完整性。

某一表中某字段的值依赖于另一张表中某字段的值，主键所在的表为主表，外键所在的表为从表，每一个外键值必须与另一个表中的主键值相对应。

 创建外键约束

create table <表名> (<字段名1> <字段类型1>,……<字段名n> <字段类型n>,[constraint 外键约束名] foreign key(字段名) references <主表>(主键字段));

create database studytest; # 创建数据库
USE studytest;# 进入数据库
create table dept(
deptno int primary key,  # 主键
dname varchar(10),
loc varchar(15)
);

create table emplyee(
empid int primary key auto_increment,
ename varchar(10) unique,
job varchar(10) not null default '未知 ',  # 默认值为字符串
mgr int,
hiredate date,
sal float default 0, #默认值为数值型
comm float,
deptno int,
foreign key(deptno) references dept(deptno) #外键

  运行结果：

修改数据表

  修改数据库中已经存在的数据表的结构

• 修改表名：alter table 原表名rename 新表名;
• 修改字段名：alter table 表名 change 原字段名新字段名数据类型;
• 修改字段类型：alter table 表名modify 字段名新数据类型;
• 添加字段：alter table 表名add 新字段名数据类型;
• 修改字段的排列位置：
    排列在第一列：alter table 表名modify 字段名数据类型first;
    排列在某一列的后面：alter table 表名modify 要排序的字段名数据类型after 参照字段;
• 删除字段：alter table 表名drop 字段名;

数据操作语言DML

插入数据

  字段名与字段值的数据类型、个数、顺序必须一一对应

-指定字段名插入：
insert into 表名(字段名1[,字段名2,...]) values(字段值1[,字段值2,...]);’

insert into dept(deptno,dname,loc) values (10,'accounting','new york'),(20,'research','dallas');

• 不指定字段名插入：
  默认每个字段都要加入数据：insert into 表名values(字段值1[,字段值2,...]);

insert into dept values (30,'sales','chicago'),(40,'operations','boston');

  需要为表中每一个字段指定值，且值的顺序须和数据表中字段顺序相同

• 批量导入数据：
    （路径中不能有中文，并且要将‘\’改为‘\\’或‘/’）

load data infile‘文件路径.csv’ 
into table 表名 
fields  terminated  by ‘,'  # 指定文件分隔符为逗号
ignore 1 lines; # 忽略第一行

更新数据

update 表名 set 字段名1=字段值1[, 字段名2=字段值2[,…]][ where 更新条件];

  加入 where 条件，那么sql只对 表中符合条件的记录进行更新
  不加入where 条件，就对表中的每一条记录进行更新，但是一般mysql中批量操作需要有权限，需要set sql_safe_updates=0;来设置数据库安全权限。

删除数据

delete from 表名[ where 删除条件];

  加入 where 条件，那么sql只对 表中符合条件的记录进行删除
  不加入where 条件，就对表中的每一条记录进行删除，但是一般mysql中批量操作需要有权限，需要set sql_safe_updates=0;来设置数据库安全权限。

 清空数据

truncate 表名;

  与delete from 表名一样，都是删除表中全部数据，保留表结构

 delete和truncate的区别：
  delete可以添加where子句删除表中部分数据，truncate只能删除表中全部数据
   delete删除表中数据保留表结构，truncate直接把表删除(drop table)然后再创建一张新表(create table)，执行速度比delete快。

小练习

show databases;
use studytest;
# 创建表名
create table salgarde(
grade int,
losal int,
hisal int
);
# 查看是否成功
desc salgarde;
# 开始插入数据
insert into salgarde(grade,losal,hisal) values
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
# 查看是否插入成功
select * from salgarde;