linux之MYSQL的SQL和DDL和DML语句,字符型,数值型,数据类型和数据库和数据表内容的操作
创建数据库 用户 表 修改表内容 设置空值默认值 行增删减改
一.SQL语言应用领域
属于特定应用领域编程语言，主要用于管理存放在关系型数据库（RDBMS）中的数据
特别适用于处理存放在数据库的数据表中存在一定关联关系的结构化数据
属于第四代编程语言，人比较容易理解
应用广泛，可以简单理解为操纵数据库的语言，一般的关系型数据库都支持SQL语言
网址：https://www.tiobe.com/tiobe-index/
常见数据库软件流行度排名(201911)
网址：https://db-engines.com/en/ranking
备注：红框标注的数据库支持SQL语言
SQL语言标准 与扩展
标准SQL不支持变量、循环体、函数等 往往由数据库软件开发商提供支持。其中微软公司的SQL扩展叫做 transact-sql（简称:t-sql）是微软公司对SQL语言的扩展；甲骨文Oracle公司的SQL扩展叫做plsql。这两种扩展是正规的后端编程语言，语言排名榜在20到30名
2.SQL语句分类 常用数据类型 数值 字符 时间日期 二进制
1.语言 ddl dql dcl tcl
语言(Language)：
语句(statement)：
create、alter、drop、insert、update、delete、select等
子句(clause):语句的一部分，
where, order by等
DDL (Data Definition Language) 数据定义语句
创建数据库、创建二维表、修改二维表等。 +tables
create创建
alter
drop删除
truncate
rename
一般要先用DDL（create）创建二维表的列，才能使用后续其它类别的语句
DML (DATA MANIPULATION LANGUAGE)数据操纵语句 增删改查
Insert向已建二维表中插入行记录
update修改行记录
Delete删除行记录
应用非常广泛，银行刷卡，打电话等交易型数据均以DML语句的形式存在
DQL (Data Query Language) 数据查询语句
select从数据库的二维表中查找数据。
应用非常广泛，但前提是先用DML（insert）语句，向已建二维表中插入行记录，才能用select查找；业内通常把select语句划分为DML语句
l
DCL (DATA CONTROL LANGUAGE)数据控制语句
grant授权
revoke收回权限
一般数据库为多用户系统，不同用户的权限不一样，可以访问的数据不一样，可以用DCL语句为给用户赋予或收回数据库的各类权限
TCL (TRANSACTION CONTROL LANGUAGE)事物控制语句
Commit数据提交
rollback
Savepoint在中间插入
只有支持事务的数据库管理系统 或存储引擎TCL语句才能有效
注意：
以表为数据存储单元的关系数据库中，往往要先建表(先DDL)，再往表中录入数据(后DML)
2.MySQL常用数据类型
因为数据的种类有多种，如数值、字符、日期等，所以为了存放各类数据，MySQL设置了不同的数据类型。
MySQL四种主要数据类型
数值
身高、体重、工资、销量、房屋面积等数据，适用于数值类型存储
字符
姓名、公司名称、地址、商品描述、简历等数据，适用于字符类型存储
日期时间
生日、订单日期、登录时间等数据，适用于时间类型存储
二进制
照片、文件扫描件、音频、视频等数据，适用于二进制类型存储
3.字符型 varchar char enum set
变长字符 varchar(N)N指最多可存储的字符个数 不能超过
定长字符 char(N)N指存储字符的个数都是指定的字符个数
内建类型 enum(枚举) 只能选一个
set(集合) 可以多选属于mysql数据库独有的数据类型
enum字符类型 适用于多选一， 只能选一个 如录入性别 “男”、“女”、“未知” 单值
set 字符类型 使用与多选多， 可选多个如录入兴趣 “读书”,“运动”, 多值逗号隔开
char使用 字符型数据时，需要加单引号
字符类型char(10) 和 varchar(10)的区别保存一个'a'字符
char(10)为固定长度，10个字符char(10)会在'a'后补充9个空格，占满10个字符，
varchar(10) 为变长，最多可以储存10个字符varchar(10) 仅占用1个字符的存储空间
char适用于存储固定长度的字符数据，如身份证号,手机号,产品编号，学号,优点是查询效率高
varchar适合存储长度不固定的字符数据，如：姓名、地址,产品介绍,备注 优点是节约存储空间
1.creat table 创建表 char varchar enum set的用法案例 插入内容inster into 表 values desc 查看表结构
create table k1(a1 varchar(10),a2 char(10),e1 enum('A','B','c','D'),s1 set('AB','AC','AD','CD'));
创建一个k1 表 a1 十个字符 a2有多少显示多少 e1选一个 s1 集合 可以使用多个数值
varchar(10) 最多可以储存10个字符 char(10)固定长度10个字符
enum 多选一，选一个set 集合可以多个
desc k1;查看 表 k1 的设定规则
insert into k1 values('ABCD','ABC','A','AB');在表中 安规定 插入数值
select * from k1;查看k1 表 所有的内容
enum设定的规则为只取一个 当同时选多个时，不能生效
desc k1;查看规则
insert into k1 values('BDEF','SSS','B','AB');因为设定了规则 所有插入一个字符为真确
insert into k1 values('BDEF','SSS','B,A','AB');报错 e1
2.查看表内容长度,length 占用长度
select * from k1;查看k1表 这两列的内容长度
select length(a1),length(a2) from k1;
指定查看实际占用的长度
set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
select length(a1),length(a2) from k1; 再次查看 varchar实际不变 char为固定10 字符
而且指定十位，如果超过十位的话他也只截取10个。
insert into k1(a1) values('ABCDEFGHIJKLM');在k1表 a1列插入13个字符 显示10个
select * from k1;查看表 k1
select length(a1),length(a2) from k1;查看第一列 ,第二列字符长度
4.数值型decimal 时间日期 年份表 大对象型
1.精确数值：decimal
一个字节 保存255个数字 2个字节 为256*256=16k
1：整形 int (包含正数和负数)
TINYINT,SMALLINT,INT,MEDIUMINT,BIGINT --使用的字节数1 2 3 4 8
2：十进制 DECIMAL (包含正数和负数) salary decimal(8,2) 最大数为65 个数 缺省10 个数
近似数值：8个数字 2个为小数点后面的数
1：单精度：FLOAT
2：双精度：DOUBLE
create table t2(d1 decimal);创建表 t2 d1列 让他为默认decimal的值 缺省10 个数
desc t2;
注意：使用数值型数据时，不需要加单引号，select * from students where stuid<2;
2.日期时间型
MySQL标准的日期时间格式为：'年-月-日 时:分:秒'，数据需要加单引号
日期：DATE 年-月-日
时间：TIME 时:分:秒
日期和时间：datetime '1000-01-01 00:00:00'到'9999-12-31 23:59:59' 不带时区全球时间都一样
时间戳： TIMESTAMP '1970-01-01 00:00:01'到'2038-01-19 03:14:07'，带有时区信息
年份： YEAR(2),YEAR(4) 88 1988
注意：
使用日期型数据时，需要加单引号，select user,host from mysql.user where now()>'1986-01-15';
时间 时区 datetime类型与timestamp类型的区别
create table t3(login_time datetime,meeting_time timestamp);
登录时间 国际会议时间
insert into t3 values(now(),now());
select * from t3;
set time_zone='+00:00';
select * from t3; 第一个为当前时间 第二个为伦敦时间 timestamp转换为当地时间
set time_zone='+9:00';东京的时间 +时区即可
select * from t3;
创建年份表时
create table t4(y1 year,y2 year(4));
insert into t4 values('19','1999');
select * from t4;
缺省默认为21世纪 2019年
3.大对象型
text, tinytext mediumtext, logtext(文本大对象类型 报刊 书籍）
BLOB(binary large object)，tinyblob，mediumblob，longblob (二进制大对象类型 图片 电影）
创建表要 定义 数据类型 int char varchar
5.数据类型 修饰符 unsigned 非负数 zerofill填充 default默认值 unique主键唯一 not null 非空 auto_increment 自增长
1.)unsigned 非负数tinyint 字节
unsigned 非负数修饰整数类型，插入或更新该列的整数不能为负数
创建表， 一个字节 tinyint 为256个数-127~+127
drop table t1;
create table t1(c1 tinyint(3));
insert into t1 values(130);
select * from t1;
加上非负数修饰符
create table t2(c1 tinyint(3) unsigned);tinyint一个字节-127到 127
insert into t2 values(130);修饰符unsigned则为从0-255
select * from t2;
插入负数时
T1表因为一个字节范围为 -127~+127 所以会显示负数
insert into t1 values(-12);
select * from t1;
T2 表 加上非负数修饰符一个字节范围为 0 ~ 256 所以不会显示负数
insert into t2 values(-12);
select * from t2;
2.)zerofill 零填充
不足指定位数的整数，在显示数据时在左边用“0”来补齐到指定位数
创建t3表 c1 四个字节显示不会默认填充
create table t3(c1 tinyint(4));
insert into t3 values(120);
select * from t3;
创建t3表 c1 四个字节显示会默认填充0 zerofill
create table t4(c1 tinyint(4) zerofill);
insert into t4 values(120);
select * from t4;
3.)default 默认值
给列的设定的默认值，如果该列没有插入数据，该列的值会用默认值来填充；列如果没有指定默认值，mysql默认会用null值（空值）来填充。
drop table t4; 删除表
创建t1 表 id为主键 名字 为10个字符 性别为2个字符 默认为nv
create table t1(id int,name varchar(10),xingbie char(2) default 'nv');设置默认选定的性别为nv
insert into t1 values(1,'jingyang','na');在t1表插入 1 jingyang na
select * from t1;插入指定内容时正常
当不插入性别时默认为女不指定性别为默认 nv
insert into t1(id,name) values(2,'taotao');在t1表 id 和name 插入 2,'taotao'
select * from t1;
4.)primary key 主键（ 非空 + 唯一）
创建 t4 表 id int 为主键 name为指定10个字符(可不写满)
create table t4 (id int primary key,name varchar(10));
insert into t4 values(1,'ABCD');
insert into t4 values(2,'sbzht');
select * from t4;
当使用重复id时失败
因为指定的是varchar会自动补个 id 0
insert into t4(name) values('zzz');
select * from t4;
insert into t4(name) values('www');只能指定一次
再插入就不可以了，所以也不能插入空值
unique 唯一（该列不能有重复值）
空值可以重复
create table t2(id int,name varchar(10) unique);
insert into t2 values(1,null);
insert into t2 values(2,null);
非空值不可以重复
insert into t2 values(3,'yang');不能都为yang 在这个表中不能有重复的值 null空值除外
insert into t2 values(4,'yang');
not null 非空 该列不能是null空值， 可以理解为该列必须填写
5.)auto_increment 自增长int整数，必须与primary key主键结合使用
自增长修饰符方便生成primary key主键值
默认从1开始按照步长1自动增长，该列值不能自动往回退
仍然可以给id列指定一个值
创建 t5表 设置 自增长
create table t5 (id int primary key auto_increment,name varchar(10),mobil char(11) unique);
在插入一个值它的id会自动增加
20行 会直接自增长到 21
6.数据类型选择原则 适当 简洁 完整
适当 (Appropriate)需要以最适合数据类型来表示数据
存储日期应当选择日期类型，而不要选择字符串类型。
如果选择字符串类型存储，当需要日期计算的时候，需要用相关函数进行转换，记录数多的时候会浪费额外的系统资源
简洁 (Brief)选择所用存储空间最少的数据类型。这可节省资源并提高性能
存储学生姓名，应该选择varchar类型，而不要选择char类型，以节约存储空间
完整 (Complete)选择的数据类型应分配有可存储特定项的最大可能值的充足空间
例如：存储地址信息时，使用varchar(N)来存储，N值给太小了，当地址字符串过长时会出现不能存储的情况，应当考虑业务数据中最大的地址长度，给一个合适的值。
DDL(数据定义语言)
常用三种语句：CREATE DROP ALTER
上述三种语句针对的数据组件为：
数据库(database)、表(table)、索引(index)、视图(view)、用户(user)、
存储过程(procedure)、存储函数(function)、触发器(trigger)、事件调度器(event)
使用HELP 命令可以查看上述三种语句的使用方法
Help create help alter table help create view
7.库级别 创建数据库 查看字符集 和排序规则
1.创建数据库 指定字符集 排序规则
命令格式 :
CREATE DATABASE [IF NOT EXISTS] DB_NAME [create_specification];
CHARACTER SET [=] charset_name;（指明字符集）
COLLATE [=] collate_name;（指明字符集排序规则）
create database testdb character set utf8 collate 'utf8_general_ci';
show character set;查看字符集
show collation;查看排序规则
选定 排序方式 自动选定 字符集 英文一种排序方式 只对应一种字符集
create database mydb1 collate gb2312_chinese_ci;
show create database mydb1;
也可以修改
alter database mydb1 collate utf8_general_ci;
show create database mydb1;
2.删除数据库（慎用）
格式 DROP DATABASE [IF NOT EXISTS] ‘db_name’;
drop database mydb1;删除mydb1数据库
show databases;查看数据库
8.表级别 创建表
数据库最基本存储单元，如果对表进行增删改查操作，必须先用DDL语句创建表
表的结构，是由二维关系构成（行、列）
定义表，可以在创建表的时候定义字段和索引（字段必须定义）
定义字段包括：字段名、字段数据类型、修饰符
定义约束包括：索引（应该定义在经常查的字段上）
1.)创建表 命令格式 案例 创建stuednt表
命令格式 CREATE TABLE [IF NOT EXISTS] tbl_name (col1 type1,col2 type2,col3 type3.......);
创建学生表，表名student，根据需求，需要存储学号，姓名，性别、生日4列信息：
mysql -uroot -p 以root用户本地连接MYSQL数据库
show databases; 查看当前可以使用哪些数据库
use test; 使用test数据库
show tables; 查看数据库下有哪些表
create table student (sno int,sname varchar(20),sex char(1),birth date); 创建学生表 包括4列信息
show tables;
desc student; 查看student表列的信息
2.)DML-insert(数据表 操作语言) 添加表内容案例：
向student表中添加学生信息，学号1，孔，男，1999-04-09
insert into student (sno,sname,sex,birth) values(1,'孔','男','1999-04-09');
select * from student;
注意：SQL语言对于 数值型数据 不用加单引号， 字符型和日期型 要加单引号
向student表中添加学生信息，学号2，金洋，女，不填写生日
insert into student(sno,sname,sex)values(2,'金洋','女');
select * from student;
向表中插入一行数据，没有指定 金洋 的生日 而‘birth’这一列没有指定默认值，会用 null 来填充
3.SQL语言中的空值 设置null 空值案例
null是SQL语言中的特殊值
null值代表不确定，数据类型未知属于不明确、状态未知的数据
向student表中一次添加3名学生信息，
学号3，涛涛，男，1999-02-24；
学号4，甄甄，男, 1999-02-22
学号5，赵六 性别未知，1996-03-15
insert into student values(3,'涛涛','男','1998-02-24'),(4,'甄甄','男','1999-02-22');
select * from student;
insert into student values(5,'赵六',null,'1996-03-15');性别未知
select * from student;
按自己制定的顺序添加，不添加会默认由空值来填充
insert into student(sex,sname,sno) values('女','朱朱','2');
select * from student;
或者直接制定空值
insert into student(sex,sname,sno) values('null','肉肉','null');
select * from student;
DML-insert语法总结：
Insert into 表名 (列名1,列名2,....) values(列1对应值,列2对应值,....);
Insert into 表名 values(列1对应值,列2对应值,....,列N对应值); #不指定列名需要在values中指定所有列对应的值，顺序不能错
Insert into 表名 values(列1对应值,列2对应值,....,列N对应值),(列1对应值,列2对应值,....,列N对应值),(列1对应值,列2对应值,....,列N对应值).....; (MySQL、SQLServer支持这种写法)
一次插入多行，每行用逗号隔开，写法仅限于MYSQL数据库
insert into student values(3,'涛涛','男','1998-02-24'),(4,'甄甄','男','1999-02-22');
select * from student;
4.)DML-update修改案例 修改表内容性别参数：
把金洋的性别修改为‘男’
update student set sex='男' where sno=2;
select * from student;
如果不指定会把所有的参数都改了
指定唯一参数
update student set birth='1999-02-21' where sno=2;
select * from student;
DML-update： 修改多个参数
update 表名 set 列1=列1值, 列2=列2值 where 列N=列N值;
一般更新语句需要加上where子句已定位要修改的行，
如果不加将会修改所有行相应的列；如果修改多个列对应的值，用“逗号”隔开各个列
修改多个参数
update student set sname='金洋',birth='1997-12-15' where sno=2;
select * from student;
5.)DML-delete删除案例 删除表内容 行：
delete from student where sno in (5,6);
select * from student;
DML-delete语句总结：
delete from 表名 where 列N=列N值;
有些数据库如oracle可以省略delete后面的from，mysql不可以；
一般删除语句需要加上where子句已定位要删除的行，如果不加会删除整个表的所有行
6.DDL建表语句create table 其它用法 复制数据表 (全部 或部分内容)
基于已有 数据表 创建新表并 复制所有数据
create table student1 as select * from student; (CTAS建表方式)
select * from student1;把student表 复制为新表 1
基于已有数据表创建新表并复制部分数据
create table student2 as select * from student where sex='女';
select * from student2;
基于已有数据库创建新表仅复制表结构不包括行数据
create table student3 as select * from student where 1=2;复制student的1,2行 表结构
select * from student3;查看内容
desc student3;查看表结构
或者
create table student4 like student; (MYSQL专有语法）
表的内容是空的 但是复制了表结构
或者
create table student4 like student; （MYSQL专有语法）
同样也是只复制了表结构
7.常用DDL语句 修改字段位置： modify column要修改名称datetime 之前之后第一列末尾 加名称
修改字段 birth成第一列
alter table student1 modify column birth datetime first;
desc student1;查看表结构
修改字段brith 成为最后一列
alter table student1 modify column birth datetime after sex;
desc student1;
8.)修改字段默认值：为空 或者 为别的 修改字段为空
设置默认指定的性别为男
alter table student1 modify sex char(1) default '男';
desc student1;
修改 性别 字段为非空：
alter table student1 modify sex char(1) not null;
desc student1;
但是如果表中该列已经为空，就不能再做此修改了。
9.追加表的某个字段唯一约束 设置取消主键
desc student1;
给字段sname添加唯一性约束(sname不能有重值，但是可以有空值）
alter table student1 add unique(sname);
desc student1;
追加表的某个字段为主键：
desc student1;
给字段sno添加主键约束（sno不能有重值，不能有空值）
alter table student1 add primary key(sno);
desc student1;
取消表的主键：
alter table student1 drop primary key;
desc student1;
10.)添加 删除 多列：
desc student1;
alter table student1 add high tinyint unsigned ,add weight tinyint unsigned,add adderss varchar(50);
desc student1;
删除多列：
alter table student1 drop sex;
desc student1;删除多列 逗号隔开
9.DDL语句 综合练习案例 创建员工表staff
1.创建员工表staff，
其中：
员工编号empno 定长10位，主键
员工姓名ename 变长10位，
性别 sex 男，女 （二选一）
学历 edu 高中，大专，本科，研究生，博士（多选一）
技能 tech linux, java, python, c, c++ （多选多）
工资 sal decimal(8,2)
入职时间 hiredate date
手机（不能有重值） phone char(11)
部门编号 deptno 2位整数即可
create table staff(empno char(10)primary key, enam varchar(10),sex enum('男','女'),deu enum('高中','大专','本科','研究生','博士'),tech set('linux','java','python','c','c++'),sal decimal(8,2),hiredate date,phone char(11),deptno char(5));
desc staff1;
create table staff(
empno char(10) primary key,
ename varchar(10) not null,
sex enum('男','女') default '男',
edu enum('高中','大专','本科','研究生','博士'),
tech set('linux','java','python','c','c++') default 'linux',
hiredate date,
phone char(11) unique,
qq varchar(20),
deptno char(5)
);
或
create table staff1(
empno char(10),
ename varchar(10) not null,
sex enum('男','女') default '男',
edu enum('高中','大专','本科','研究生','博士'),
tech set('linux','java','python','c','c++') default 'linux',
hiredate date,
phone char(11),
qq varchar(20),
deptno char(5),
primary key(empno),
unique(phone)
);
2.查看建表的DDL语句
show create table student1\G
DQL-SELECT简单查询语句示例
select * from student; * 代表显示所有列对应的行数据
select sno,sname,sex from student; 仅挑选student表的3列对应的行数据
10.DQL-SELECT语句特点及用途
DQL-SELECT语句相对其他类型语句DML, DDL等灵活度较高
SELECT语句用途广泛
提取数据
分析统计数据
生成报表
支持决策
后续学习根据需求创建例子数据表：学生表、班级表、体重指标表
需求描述（假设）
某大学需要将新生的相关信息放到数据库中集中管理
l入学前对每名新生进行了体检，测量了身高体重，后续需要找出过胖或过瘦的学生进行健康指导，以确保体型达标
l新生已分班并为每个班指定一名班主任
l学生的信息包括：学号、姓名、性别、生日、身高、体重、高考分、班号
l班级的信息包括：班号、班级名称、班主任姓名
l学生的体重指数(BODY MASS INDEX)按照公式：体重kg/身高m^2计算
l按照通用健康BMI数据进行统计
1.根据需求建立数据表 导入stu表student_utf8.sql
使用mysql客户端source命令导入数据表和数据
mysql> source /root/student_utf8.sql
7学生表 stu
学号、姓名、性别、生日、身高、体重、高考分、班号
7班级表 class
班号、班级名称、班主任姓名
7体重指标表 bmi
编号，体型描述，低值，高值，性别
