2018-08-09 Mysql Database Operat
数据操作语言
使用 DML 语句可执行:
1、插入新数据 insert into
2、修改已有数据 update
3、删除数据 delete
(1)INSERT语句一次将一个记录的数据输入表中
INSERT INTO dept (deptno, dname, loc)
VALUES (50, 'DEVELOPMENT', 'DETROIT');
INSERT INTO dept (deptno, dname )
VALUES (60, 'MIS');
INSERT INTO dept (没有列名，表明都插入)
VALUES (70, 'FINANCE', NULL);
INSERT语句中使用函数；例如使用NOW()函数插入当前的日期和时间
INSERT INTO emp (empno, ename, job,
mgr, hiredate, sal, comm,
deptno)
VALUES (7196, 'GREEN', 'SALESMAN',
7782, NOW(), 2000, NULL,
10);
从其他表中拷贝记录
INSERT INTO managers(id, name, salary, hiredate)
SELECT empno, ename, sal, hiredate
FROM emp
WHERE job = 'MANAGER'; （managers是已经存在的表）
creat table emp_copy
as
SELECT *
FROM emp
WHERE job = 'MANAGER‘; #创建一个空表emp_copy,并将emp中的列名及数据完全复制
（2）UPDATE 语句
使用UPDATE语句修改已存在的记录
使用WHERE子句修改指定的记录
UPDATE emp
SET deptno = 20 改变的内容
WHERE empno = 7782; 关联属性条件
修改所有的记录.
UPDATE employee
SET deptno = 20;
通过查询更新
UPDATE emp_copy
SET job = (SELECT job
FROM emp
WHERE empno = 7698), deptno= (SELECT deptno
FROM emp WHERE empno = 7698)
WHERE empno=7566;
或用for update 操作关系表改值
（3）DELETE 语句
使用DELETE命令从表中删除记录
DELETE FROM table
[WHERE condition];
从表中删除记录
DELETE FROM dept
WHERE dname = 'DEVELOPMENT';
删除所有的记录
DELETE FROM dept;
（4）数据类型
使用子查询创建表
CREATE TABLE dept30
AS
SELECT empno, ename, sal*12 ANNSAL, hiredate
FROM emp
WHERE deptno = 30;
ALTER TABLE 语句
使用ALTER TABLE 语句可:
加一列
修改列
删除列
使用 ADD 子句增加列
ALTER TABLE dept30
ADD (job VARCHAR(9));
可修改列的数据类型,大小和缺省值.
修改后的缺省值只影响以后插入的数据.
ALTER TABLE dept30
MODIFY job VARCHAR(20);
删除列，如果存在数据一起删除.
ALTER TABLE dept30 DROP COLUMN（可有可无） job;
改表的名称
ALTER TABLE dept30 RENAME dept40
约束
（1）什么是约束
约束是表级强制执行的规则.
当表中数据有相互依赖性时,可保护数据不被删除.
有如下类型的约束:
NOT NULL
UNIQUE Key
PRIMARY KEY
FOREIGN KEY
DEFAULT 'M' （默认约束，值为M）
CHECK (sm_sex IN ('M','F')) （选择约束，字段sm_sex可选择值只有 M，F 两种）
创建约束:
在建表的同时创建
建表后创建
可定义列级或表级约束
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk
PRIMARY KEY (EMPNO));
列级约束
column [CONSTRAINT constraint_name] constraint_type,
表级约束
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
非空约束（NOT NULL）
CREATE TABLE dept6(deptno INT NOT NULL,
deptname VARCHAR(20),
deptloc VARCHAR(50));
唯一性约束（UNIQUE）
CREATE TABLE dept(
deptno INT,
dname VARCHAR2,
loc VARCHAR2,
CONSTRAINT uk_dept_dname UNIQUE(dname));
主键约束（ PRIMARY KEY）
CREATE TABLE dept6(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT dept_dname_uk UNIQUE(dname),
CONSTRAINT pk_deptno PRIMARY KEY(deptno))
外键约束（FOREIGN KEY）
CREATE TABLE `emp_copy` (
`empno` int(4) NOT NULL,
`ename` varchar(60) default NULL,
`job` varchar(30) default NULL,
`mgr` int(4) default NULL,
`hiredate` date default NULL,
`sal` float default NULL,
`comm` float default NULL,
`deptno` int(2) default NULL,
PRIMARY KEY (`empno`),
KEY `emp_index` (`deptno`),
CONSTRAINT `pk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
)
视图
什么是视图？为什么使用
视图是一个虚拟表，限制对数据的访问
很容易的写成复杂的查询
允许数据的独立性
不同的视图可获得相同的数据
创建视图
创建视图的语句中可嵌入子查询.
CREATE [OR REPLACE] VIEW
view_name [(column_list)]
AS select_statement
#CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
在子查询中使用列别名创建视图
CREATE VIEW salvu30
AS SELECT empno as EMPLOYEE_NUMBER, ename as NAME,
sal as SALARY
FROM emp
WHERE deptno = 30;
在查询视图时使用列别名
SELECT EMPLOYEE_NUMBER, NAME, SALARY
FROM salvu30;
修改视图
使用CREATE OR REPLACE VIEW 子句修改 视图 ,并为每列加别名
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
在CREATE VIEW语句中列的别名的顺序是和子查询中一致的.
创建复杂的视图
复杂视图的特点：
从多个表查询
包含函数
包含分组数据
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal),
AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
删除视图并不删除基表中的数据
用户及权限
设置密码
CREATE USER user
IDENTIFIED BY password;
CREATE USER tester IDENTIFIED BY 'tiger';
超级用户可使用 CREATE USER 语句建用户.
SET PASSWORD=PASSWORD (password)
SET PASSWORD=PASSWORD ('tom1');
权限列表
ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 抛弃(删除)数据库和表。
INDEX: 创建或抛弃索引。
INSERT: 向表中插入新行。
REFERENCE: 引用。
SELECT: 检索表中的记录。
UPDATE: 修改现存表记录。
FILE: 读或写服务器上的文件
PROCESS: 查看服务器中执行的线程信息或杀死线程。
RELOAD: 重载授权表或清空日志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限，ALL PRIVILEGES同义词。
USAGE: 特殊的 "无权限" 权限。
赋予权限
GRANT privilege ON object TO host IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'tester'@'localhost' IDENTIFIED BY 'tester';
说明：授予tester在服务器本机上所有权限。
*.* 代表所有数据库所有对象。Test.* 代表test数据库所有对象
’ tester ‘ @ ’ localhost ‘ 代表tester用户在本机操作权限。
’ tester ‘ @ ’ % ‘ 代表tester用户从所有机器上操作权限。
刷新权限
通常执行完需要刷新权限
FLUSH PRIVILEGES;
撤销权限
REVOKE privilege ON object FROM username;
REVOKE update ON company.* FROM tester;
