[大数据]MYSQL库表操作

SQL简介以及分类

1.SQL简介

SQL：结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。使用SQL语句，程序员和数据库管理员可以完成如下的任务：

• 改变数据库的结构
• 更改系统的安全设置
• 增加用户对数据库或表的许可权限
• 在数据库中检索需要的信息
• 对数据库的信息进行更新

2.SQL语句分类

MySQL致力于支持全套ANSI/ISO SQL标准。在MySQL数据库中，SQL语句主要可以划分为以下几类

• DDL(Data Definition Language):数据定义语言，定义对数据库对象(库、表、列、索引)的操作。 CREATE、DROP、ALTER、RENAME、 TRUNCATE等
• DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作。INSERT、DELETE、UPDATE、SELECT(又名DQL)等
• DCL(Data Control Language): 数据控制语言，定义对数据库、表、字段、用户的访问权限和安全级别。GRANT、REVOKE等。

当然还有Transaction Control:事务控制COMMIT、ROLLBACK、SAVEPOINT等语句

数据库操作

数据库命名规则：

必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字
同一Schema下的对象不能同名

1、查看
        SHOW DATABASES
	语法：
    	SHOW DATABASES LIKE 匹配符；
2、创建
	CRATE DATABASE
	语法：
        CREATE DATABASE [IF NOT EXISTS]数据库名;
        功能：用给定的名字创建一个数据库
        如果数据库已经存在，发生一个错误。
        查看创建数据库：SHOW CREATE DATABASE <数据库名>；
        示例：
        create database school DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3、删除
    DROP DATABASE
	语法：
        DROP DATABASE [IF EXISTS]数据库名;
        功能：删除数据库中得所有表和数据库
        要小心使用这个命令（慎用!!!）
4、切换
    使用USE选用数据库
    语法：
        USE 数据库名;
        功能：把指定数据库作为默认(当前)数据库使用，用于后续语句。       
5. 其他操作
        查看当前连接的数据库
        SELECT DATABASE();
        查看数据库版本
        SELECT VERSION();
        查看当前用户
        SELECT USER();
        查看所有用户
        SELECT User,Host,Password FROM mysql.user;

注：关于mysql默认库的解释

MySQL自带数据库：
Information_schema: 主要存储了系统中的一些数据库对象信息：如用户表信息、列信息、权限信
息、字符集信息、分区信息等。(数据字典表)
performance_schema： 主要存储数据库服务器的性能参数
mysql: 存储了系统的用户权限信息及帮助信息。
test: 系统自动创建的测试数据库，任何用户都可以使用。

数据库表的操作

1.表的创建

标准的建表（table）语法(列定义之间以英文逗号,隔开)：
数据表的每行称为一条记录（record），每一列称为一个字段（field）。
主键列：唯一能够标识每条记录的列。
 
CREATE TABLE [schema.]table
    (column datatype[DEFAULT expr] ,
    	…
    ) ENGINE = 存储机制
    
简单语法：
    CREATE TABLE 表名（
    列名 列类型，
    列名 列类型
    ）;

示例：
CREATE TABLE account(
a_id INT(11) NOT NULL AUTO_INCREMENT,
ablance FLOAT DEFAULT NULL,
PRIMARY KEY (a_id)
)ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2.表的物理存储位置

InnoDB(默认的存储引擎)的表：

[root@blackstone mysql]# pwd
/var/lib/mysql/mysql
[root@blackstone mysql]# ll user*
-rw-rw----. 1 mysql mysql 10630 Oct 16 00:32 user.frm   # 描述表结构文件，字段长度等，frame框架
-rw-rw----. 1 mysql mysql   524 Oct 16 00:41 user.MYD   # 数据信息文件，存储数据信息(如果采用独立表存储模式) data
-rw-rw----. 1 mysql mysql  2048 Oct 16 01:27 user.MYI   # 索引信息文件，index
InnoDB(默认的存储引擎)的表：
[root@node2 employess]# ls -l t4*
-rw-r----- 1 mysql mysql  8586 7月  16 20:31 t4.frm
-rw-r----- 1 mysql mysql 98304 7月  16 20:32 t4.ibd
t4.frm：存储列相关信息，描述表结构文件，字段长度等
t4.ibd：数据行+索引，如果采用独立表存储模式，data\a中还会产生b.ibd文件（存储数据信息和索引信息）
    如果采用共存储模式的，数据信息和索引信息都存储在ibdata1中
    如果采用分区存储，还会有一个t.par文件（用来存储分区信息）

3.数据类型

常用的数据类型：

4.查看表

show语句
语法：
SHOW TABLES[FROM 数据库名][LIKE wild];
功能：显示当前数据库中已有的数据表的信息【结构和创建信息】

DESCRIBE语句
语法：
{DESCRIBE|DESC}表名[列名];
# or
show columns from 表名称;
功能：查看数据表中各列的信息

注：用SHOW CREATE TABLE 表名可以查看更全面的表定义信息

5.表的删除

DROP TABLE
语法：
	DROP TABLE [IF EXISTS] 表名；
功能：删除指定的表

6.表的修改

修改列类型
	ALTER TABLE 表名 MODIFY 列名 列类型;
	
增加列
	ALTER TABLE 表名 ADD 列名 列类型;
	
删除列
	ALTER TABLE 表名 DROP 列名;
列改名
	ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
更改表名
	ALTER TABLE 表名 RENAME 新表名;
	RENAME TABLE 表名 TO 新表名;

7.表的复制

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表

方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;

8.约束

约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时,可以保护相关的数据不被删除。
约束的目的：保障数据的一致性，降低数据的冗余，唯一性的确立，取值范围的确定，数据之间的依赖关系。
这里举个例子，一个学生表和一个成绩表，那么在成绩表中插入数据时一定需要确保学生姓名在学生表内是存在的。故对于这种表之间的依赖关系也需要被体现出来，其实现就是用了约束来实现。
约束的分类:

约束创建的时机：建表时和建表后
同时对于约束的创建对象可以是一张表，也可以是一个字段(一列)，当然针对字段数的多少可以划分为单列约束和多列约束。
标准添加语法：

1.创建时添加
语法：
    列定义    [CONSTRAINT 约束名] 约束类型(列名)
2.增加约束：
	alter table 表名 add constraint 约束名 约束类型(要约束的列名)
约束名的要求：
推荐采用：表名_列名_约束类型简介

非空约束(NOT NULL)

列级约束，只能使用列级约束语法定义。确保字段值不允许为空,只能在字段级定义
示例：

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) NOT NULL
);

NULL:所有数据类型的值都可以是NULL。空字符串不等于NULL。0也不等于NULL

唯一键约束

唯一性约束条件确保所在的字段或者字段组合不出现重复值，唯一性约束条件的字段允许出现多个NULL。同一张表内可建多个唯一约束。唯一约束可由多列组合而成，建唯一约束时MySQL会为之建立对应的索引。如果不给唯一约束起名，该唯一约束的名字默认与列名相同。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) UNIQUE NOT NULL
)

注：在组合起来的唯一键中只有所有字段值均不相同才会被报错拒绝插入数据。

主键约束

主键从功能上看相当于非空且唯一，一个表中只允许一个主键。主键是表中唯一确定一行数据的字段，主键字段可以是单字段或者是多字段的组合。当建立主键约束时，MySQL为主键创建对应的索引主键约束名总为PRIMARY。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18)
)

主键选取的原则：
1）主键应当是对用户没有意义的
2）主键应该是单列的，一边提高连接和筛选操作的效率
3）永远额不要更新主键。实际上逐渐除了唯一标识一行之外，在没有其他的用途了，所以也就没有理由对它更新。
4）主键不包含动态变化的数据，如时间戳，创建时间列，修改是键列等。
5）主键应当由计算机自动生成
我们在建立数据库的时候需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或者属性组，一个表只能有一个主键，但是可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

外键约束

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。
格式：

格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

示例：

CREATE TABLE person(
pid INT PRIMARY KEY,
pname VARCHAR(50)
);
DROP TABLE idcard;
CREATE TABLE idcard(
i_cd INT PRIMARY KEY,
i_num INT,
i_startdate DATE,
i_enddate DATE,
i_publish VARCHAR(50),
pid INT
);
#添加外键由子表外键指向主表主键
ALTER TABLE idcard ADD CONSTRAINT dk_pid FOREIGN KEY(pid) REFERENCES person(pid);

这里发现子外键可插入的数据仅可以来自于主表主键

可以看到，人员表内不存在的数据是无法插入的

注：从表外键和主表主键必须保持数据类型一致，否则可能会出现创建失败的情况

删除表的约束

删除NOT NULL约束
	alter table 表名 modify 列名 类型;

删除UNIQUE约束
	alter table 表名 drop index 惟一约束名;

删除PRIMARY KEY约束
	alter table 表名 drop primary key;

删除FOREIGN KEY约束
	alter table 表名 drop foreign key 外键名;

课后练习

练习1

1.按照如下要求创建两张数据库表

2.查看数据库下有几个表以及查看两张表的结构

3.将employees的mobile字段修改该到officecode字段后面

4.将employees的birth字段改名为employee_birth

5.修改sex字段，数据类型为char(1)，非空约束

6.删除字段note

7.增加字段名favoriate_activity,数据类型欸varchar(100)

8.删除表offices

9.将表employees名称修改该为employees_info

答案：
#创建数据库---作业
CREATE DATABASE zuoye;
#创建表
/*DROP table officies;
DROP TABLE employee;*/

CREATE TABLE officies(
officeCode INT(10) PRIMARY KEY NOT NULL,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(5) UNIQUE  
)ENGINE=INNODB;

CREATE TABLE employee(
employeeNumber INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10),
jobtitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
node VARCHAR(255),
sex VARCHAR(5),
FOREIGN KEY (officeCode)REFERENCES officies(officeCode) 
)ENGINE=INNODB;

#2.
SHOW TABLES;
DESC employee;
DESC officies;

#3.alter table 表名 modify 字段名 字段类型 after 字段
ALTER TABLE employee MODIFY mobile VARCHAR(25) AFTER officeCode;

#4.ALTER TABLE 表名 CHANGE 旧列名 新列名 列类型;
ALTER TABLE employee CHANGE birth enployee_birth DATETIME;

#5.修改sex字段，数据类型为char(1)，非空约束
ALTER TABLE employee MODIFY sex CHAR(1) NOT NULL;
#6.删除字段note
ALTER TABLE employee DROP node;

#7.增加字段名favoriate_activity,数据类型欸varchar(100)
ALTER TABLE employee ADD favoriate_activity varcahr(100);
DESC employee;

#8.删除表offices---这里需要先解除外键关系才可以删除,根据回显指出的外键名删除
ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;
DROP TABLE officies;
SHOW TABLES;

#9.将表employees名称修改该为employees_info
ALTER TABLE employee RENAME employees_info;
SHOW TABLES;

练习2

1.创建数据库market,在market中创建数据表customers

2.将c_contact字段插入c_birth字段后面
3.将c_name字段数据类型改为VARCHAR(70)
4.将c_contact字段更名为c_phone
5.增加c_gender字段，数据类型为CHAR(1)
6.将表名修改改为customers_info
7.删除字段c_city
8.修改该数据表的存储引擎为MYLSAM

答案：
#1.创建数据库market,在market中创建数据表
CREATE DATABASE market;
USE market;
#drop table customers;
CREATE TABLE customers(
c_num INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATETIME NOT NULL 
)ENGINE=INNODB;

SHOW TABLES;
#2.将c_contact字段插入c_birth字段后面
DESC customers;
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;
DESC customers;

#3.将c_name字段数据类型改为VARCHAR(70)
ALTER TABLE customers MODIFY c_name VARCHAR(70);
DESC customers;

#4.将c_contact字段更名为c_phone
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);
DESC customers;

#5.增加c_gender字段，数据类型为CHAR(1)
ALTER TABLE customers ADD c_gender CHAR(1);
DESC customers;

#6.将表名修改改为customers_info
ALTER TABLE customers RENAME customers_info;
SHOW TABLES;

#7.删除字段c_city
ALTER TABLE customers_info DROP c_city;
DESC customers_info;

#8.修改该数据表的存储引擎为MYISAM---ALTER TABLE <表名> ENGINE=<存储引擎名>;
ALTER TABLE customers_info ENGINE=MYISAM;