mysql数据库小知识---定义语言DDL

第一章 表操作

1.创建表CREATE

/*
必须要有创建表的权限
表名，列名可以由字母、数字和下划线组成，必须以字母开头，不能是系统关键字

create table 表名
(
列名1 数据类型，
列名2 数据类型，
……
列名n 数据类型
);

数字类型：
int 整数
float(m,n) 小数,最多位数为m，精确到小数点后n位
double(m,n) 双精度小数，最多位数为m，精确到小数点后n位
n会占用m的位数，float(7,2)表示5位整数2位小数

字符类型：
char(n) 最大长度是n，固定长度的字符类型，n的最大值是255字节
varchar(n) 最大长度是n，可变长度的字符类型，n的最大值是65535字节

char(n) 如果存储的数据长度不到n个字节，会用空格补齐到n个字节
varchar(n) 按照数据的实际长度存储

char浪费存储空间，查询效率高
varchar节省存储空间，查询效率低

时间和日期类型
date 日期
time 时间
datetime 时间+日期
timestamp 时间戳
*/

CREATE TABLE test1
(
testid INT,
testname VARCHAR(20),
age TINYINT,
gender CHAR(1),
birth DATE,
score FLOAT(4,1)
);

SELECT * FROM test1;

#desc 表名，查看表结构
DESC test1;

#show create table 表名，查看创建表的语句
SHOW CREATE TABLE test1;
/*
CREATE TABLE `test1` (
  `testid` int(11) DEFAULT NULL,
  `testname` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `score` float(4,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
INSERT INTO test1
VALUES (1000,'xiaoming',38,'M','1985-04-09',60);

SELECT * FROM test1;

#如果放入的数字精度超过了数据类型定义的精度，会四舍五入
INSERT INTO test1
VALUES (1000.898989,'xiaoming',38,'M','1985-04-09',60);

#如果放入的数字大小超出范围，会用最大值替代
INSERT INTO test1
VALUES (1000,'xiaoming',258454,'M','1985-04-09',60);

#如果字符的长度超出限制，会截取前n位放入表中
INSERT INTO test1
VALUES (1000,'xiaoming45444413214444ggrhrt4h4h',38,'M','1985-04-09',60);

#所有的数字可以默认被看作是字符
INSERT INTO test1
VALUES (1000,548458,38,'M','1985-04-09',60);

#如果数字类型的列放入字符，则会保存为0
INSERT INTO test1
VALUES ('hello','xiaoming',38,'M','1985-04-09',60);

#如果日期类型的列，放入非日期格式的数据，会显示一个无效日期
INSERT INTO test1
VALUES (1000,'xiaoming',38,'M','fjdsfsf',60);

/*
在放入数据时，如果不定义某列，该列会使用空值
但是如果这些列在创建时定义了默认值，会使用默认值填入表中
在一些列中，如果有些值是频繁使用的，可以将它们设置为默认值

create table 表名
(
列名1 数据类型 [default 默认值]，
列名2 数据类型 [default 默认值]，
……
列名n 数据类型 [default 默认值]
);

默认值必须符合该列的数据类型，不能是其他列的列名
*/
CREATE TABLE test2
(
testid INT,
testname VARCHAR(20),
age TINYINT DEFAULT 18,
gender CHAR(1) DEFAULT 'F',
score FLOAT(4,1) DEFAULT 0
);

DESC test2;

INSERT INTO test2
VALUES (1000,'xiaoming',NULL,NULL,NULL);

SELECT * FROM test2;

INSERT INTO test2(testid,testname)
VALUES (1001,'xiaoqiang');

#create table 表名 as select ……;  
#表名后面可以有括号，括号中定义列名，但是不能定义数据类型
CREATE TABLE dept20
AS
SELECT empno,ename,job,deptno,12*sal nianxin FROM emp
WHERE deptno = 20;

SELECT * FROM dept20;

DESC emp;
DESC dept20;

2.修改表结构ALTER
#在工作中，一般不推荐在表创建成功后再去改表结构，尤其是当表中已经有数据之后
#如果确实因为特殊原因需要修改结构，使用alter语句

#添加列
#alter table 表名 add 列名 数据类型；
#在语句最后可以使用first表示在表的第一列添加，也可以用 after 列名 ，放到某列之后

#删除列
#alter table 表名 drop (column) 列名；

#修改列名/数据类型
#alter table 表名 change 原列名 新列名 数据类型；

#添加默认值
#alter table 表名 alter (column) 列名 set default 默认值;

#删除默认值
#alter table 表名 alter (column) 列名 drop default;

3.删除表DROP

#drop table 表名;
DROP TABLE dept20;
SELECT * FROM dept20;

4.重命名RENAME

#rename table 原表名 to 新表名;
RENAME TABLE test2 TO newtable;
SELECT * FROM test2;
SELECT * FROM newtable;

5.截取表中的数据TRUNCATE

#truncate table 表名；
SELECT * FROM test1;

TRUNCATE TABLE test1;
#truncate是站在表的层面，快速清空数据
#delete必须进入表，按照行来删除数据

#delete 属于DML语句，可以删除全部也可以删除部分；需要提交才能生效，可以回滚。
#truncate 属于DDL语句，只能快速删除所有数据，不能指定范围；直接生效不能回滚
#drop 属于DDL语句，删除整张表；直接生效不能回滚

第二章 约束

#约束就是数据应该满足的条件或者规则，不满足条件的数据不能被放入表中
#约束以列为单位进行定义，规定某列的数据必须满足的条件
/*
主键约束：定义某一列为主键，该列数据不能重复也不能为空，每张表主键只能有一个
primary key

外键约束：定义某一列为外键，参照其他表的主键，取值可以为空，如果不为空则必须与主键的值对应
foreign key
定义语法：foreign key(列名) references 表名（列名）
外键只能定义成表级约束

非空约束：定义某一列的取值不能为空
not null
非空只能定义成列级约束

唯一约束：定义某一列的值必须唯一
unique

检查约束（check），mysql不支持

*/
#约束可以在创建表的同时创建，也可以在创建表之后创建
#约束分为列级约束和表级约束两种，两者效果一样，只是定义的方式不一样
/*
create table 表名
(
列名1 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
列名2 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
……
列名n 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
constraint 约束名称1 约束类型（列名），
constraint 约束名称2 约束类型（列名），
……
);
*/
CREATE TABLE test3
(
testid INT PRIMARY KEY,
testname VARCHAR(20) NOT NULL,
deptno INT,
FOREIGN KEY(deptno) REFERENCES dept(deptno),
phone BIGINT UNIQUE
);
DESC test3;

INSERT INTO test3
VALUES (1000,'xiaoming',10,13800138000);

SELECT * FROM test3;

INSERT INTO test3
VALUES (1005,'xiaoqiang',40,13800138005);

#创建表之后可以修改约束

#添加主键约束
#alter table 表名 add constraint 约束名称 primary key(列名);

#删除主键约束
#alter table 表名 drop constraint primary key;

#添加唯一约束
#alter table 表名 add unique(列名);

#删除唯一约束
#alter table 表名 drop index 列名;

#添加非空约束
#alter table 表名 change 原列名 新列名 数据类型 not null；

#删除非空约束
#alter table 表名 change 原列名 新列名 数据类型;

#添加外键约束
#alter table 表名 add foreign key(列名) references 表名(列名);

#删除外键约束
#alter table 表名 drop foreign key 外键名称；

SHOW CREATE TABLE test3;
/*
CREATE TABLE `test3` (
  `testid` int(11) NOT NULL,
  `testname` varchar(20) NOT NULL,
  `deptno` int(11) DEFAULT NULL,
  `phone` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`testid`),
  UNIQUE KEY `phone` (`phone`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

test3_ibfk_1 就是系统自动分配给外键约束的名称
*/

【练习】

1.创建班级表（grade）和学生表（student），结构如下:
班级表：
列名              数据类型
grade_id（主键）      INT
grade_name（非空）    VARCHAR(10) 
grade_director（班主任，非空）  VARCHAR(20) 

CREATE TABLE grade
(
grade_id INT PRIMARY KEY,
grade_name VARCHAR(10) NOT NULL,
grade_director VARCHAR(20) NOT NULL
);
SELECT * FROM grade;

学生表：
列名                              数据类型
st_id（主键）                   INT 
st_name（非空）                     VARCHAR(14) 
st_age                          INT 
st_phno（不能重复）               INT 
st_date                         DATE 
st_gender（非空，默认为男）      CHAR(2) 
st_grade（外键，关联班级表主键）   INT

CREATE TABLE student
(
st_id INT PRIMARY KEY,
st_name VARCHAR(14) NOT NULL,
st_age INT,
st_phno INT UNIQUE,
st_date DATE,
st_gender CHAR(2) DEFAULT 'M' NOT NULL,
st_grade INT,
FOREIGN KEY(st_grade) REFERENCES grade(grade_id)
);
DESC student;

第三章 视图

#视图（view），视图就是一个查询语句的快捷方式
#视图本身不存储数据，本质存储的是一条查询语句
#对视图的访问和操作，其实就是对查询语句所查询的数据进行访问和操作
#通过视图修改的数据，等同于对基表中数据的修改
#1.可以根据不同的角色，创建不同的视图，来区分不同的权限
#2.提前创建好视图，可以简化复杂的查询语句
/*
create [or replace] view 视图名称
as
select ……;

创建视图时，如果有重名的视图，不加or replace会直接报错，无法创建
如果加上or replace会覆盖之前的视图
*/

1.区分权限

#公司需要hr和boss可以查看并且修改所有员工的信息
#可以直接访问emp表

#部门经理只能查看和修改本部门的员工信息
#针对部门经理创建部门视图，只包含该部门的员工信息
CREATE VIEW v_dept20
AS
SELECT * FROM emp
WHERE deptno = 20;

#对视图的操作与对表的操作一样
SELECT * FROM v_dept20;

#可以针对视图做dml操作，等同于对基表中数据的操作
UPDATE v_dept20
SET sal=1500
WHERE ename = 'SMITH';

SELECT * FROM emp;

#普通员工只能查看自己的信息，不能修改
#oracle中视图创建的语句最后可以加一个选项：read only
#这样创建的视图只能被查询，不能修改

#mysql不支持该选项，所以只能通过设置用户权限来限定不能修改
CREATE VIEW v_smith
AS
SELECT ename,job,sal,deptno FROM emp
WHERE ename = 'SMITH';

SELECT * FROM v_smith;

2.简化查询语句

#如果某些表之间的关系比较密切，经常互相作为条件进行查询
#这样的表可以提前创建好视图，相关的查询都可以大大简化

#查询research部门的人数
1)多表查询
SELECT COUNT(e.ename)
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND d.dname = 'RESEARCH';

2)子查询
SELECT COUNT(ename) FROM emp
WHERE deptno =
(SELECT deptno FROM dept WHERE dname = 'RESEARCH');

3)视图简化
#emp表和dept表关系比较密切，经常互为条件查询
#可以提前为emp和dept创建视图，然后通过视图查询，简化语句
CREATE VIEW v_emp_dept
AS
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

SELECT * FROM v_emp_dept;

SELECT COUNT(ename) FROM v_emp_dept
WHERE dname = 'RESEARCH';

#查询在纽约工作的员工的平均工资
SELECT AVG(sal) FROM v_emp_dept
WHERE loc = 'NEW YORK';

第四章 索引

#索引(index)，相当于目录，给数据建立目录，提高查询效率
#给经常作为查询条件的列创建索引，提高查询效率
#索引在创建之后，不需要做其他任何操作，只要用到相关的列进行查询，索引会自动优化查询效率
#当数据发生变化时，索引会自动更新，不需要手动维护
/*
create index 索引名称 on 表名(列名);
*/
#emp表中，经常使用ename作为查询条件，可以给该列创建索引
CREATE INDEX emp_ename ON emp(ename);

#确认索引的创建
SHOW INDEX FROM emp;

#mysql中允许同时给多列创建索引
#当某些列经常同时作为查询条件组合查询时，可以建立多列索引
CREATE INDEX emp_dept_job ON emp(deptno,job);

#每一列只能创建一个一级索引，新建的索引会覆盖原来的索引
#创建多列索引时，写在后面的列会创建成相对低级的索引
#索引生效的原则是最左原则，从最左边开始才可以生效
#如果创建一个索引(a,b) 使用a列作为条件则该索引生效，使用a和b两列作为条件则该索引生效
#如果单独使用b列作为查询条件，该索引不生效