MYSQL基础知识(三)__数据定义语句DDL

第一章 表操作

一、创建表CREATE

新建表需注意：
    ①.必须要有创建表的权限
    ②.表名，列名可以由字母、数字和下划线组成，必须以字母开头，不能是系统关键字

1.create语句模板：
/*
create table 表名
(
列名1 数据类型，
列名2 数据类型，
……
列名n 数据类型
);
*/

2.数据类型：
    ①.数字类型：
        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       时间戳

3.实例
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);

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

二、修改表结构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;

三、删除表DROP

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

四、重命名RENAME

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

五、截取表中的数据TRUNCATE

-- truncate table 表名；
SELECT * FROM test1;

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

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

第二章   约束

1.约束：
    ①.约束就是数据应该满足的条件或者规则，不满足条件的数据不能被放入表中
    ②.约束以列为单位进行定义，规定某列的数据必须满足的条件

2.五种约束：
    ①.主键约束(primary key)：定义某一列为主键，该列数据不能重复也不能为空，每张表主键只能有一个
    ②.外键约束(foreign key)：定义某一列为外键，参照其他表的主键，取值可以为空，如果不为空则必须与主键的值对应
        定义语法：foreign key(列名) references 表名（列名）
        外键只能定义成表级约束
    ③.非空约束(not null)：定义某一列的取值不能为空, 非空只能定义成列级约束
    ④.唯一约束(unique)：定义某一列的值必须唯一
    ⑤.检查约束（check），mysql不支持

3.注意：
    ①.约束可以在创建表的同时创建，也可以在创建表之后创建
    ②.约束分为列级约束和表级约束两种，两者效果一样，只是定义的方式不一样
/*
create table 表名
(
列名1 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
列名2 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
……
列名n 数据类型 [default 默认值] [列级约束1 列级约束2 ……]，
constraint 约束名称1 约束类型（列名），
constraint 约束名称2 约束类型（列名），
……
);
*/
4.实例
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);

5.创建表之后可以修改约束
    ①.添加主键约束
    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 就是系统自动分配给外键约束的名称
*/

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


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

第三章 视图

1.视图
    ①.视图（view），视图就是一个查询语句的快捷方式
    ②.视图本身不存储数据，本质存储的是一条查询语句
    ③.对视图的访问和操作，其实就是对查询语句所查询的数据进行访问和操作
    ④.通过视图修改的数据，等同于对基表中数据的修改
2.注意：
    ①.可以根据不同的角色，创建不同的视图，来区分不同的权限
    ②.提前创建好视图，可以简化复杂的查询语句
3.模板：
/*
create [or replace] view 视图名称
as
select ……;
创建视图时，如果有重名的视图，不加or replace会直接报错，无法创建
如果加上or replace会覆盖之前的视图
*/

4.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;

4.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列作为查询条件，该索引不生效