MySql之基础查询_DML操作_数据表操作_约束_事务_权限_视图

# 基本查询
SELECT id,zname,zno,zbirthday,zgender,zweight,zheight,zage FROM `t_soldiers`;
SELECT id,zname,zage*2 FROM t_soldiers;
SELECT id,zname,zage+12 FROM t_soldiers;
SELECT id,zname,zage-2 FROM t_soldiers;
SELECT id,zname,zage/2 FROM t_soldiers;

SELECT id AS 编号,zname AS 姓名,zage AS 年龄 FROM t_soldiers;
###distinct去重
SELECT  DISTINCT zage FROM t_soldiers;
SELECT  DISTINCT zname FROM t_soldiers;
SELECT  DISTINCT zweight FROM t_soldiers;

## 排序查询 asc升序 desc 降序
SELECT	\* FROM t_soldiers ORDER BY zage ASC;
SELECT 	\* FROM t_soldiers ORDER BY zage ASC ,zweight DESC;

## 条件查询
SELECT  \*  FROM t_soldiers  WHERE zage=24;
SELECT  \*  FROM t_soldiers  WHERE  NOT zage=24;
SELECT \*   FROM t_soldiers  WHERE zage >23 AND zage<25;

###区间查询between...and..小值在前，大值在后，包含两头值
SELECT \*FROM t_soldiers WHERE zage BETWEEN 23 AND 25;

SELECT \*FROM t_soldiers WHERE zbirthday IS NOT NULL;
SELECT \* FROM t_soldiers WHERE zage IN(21,22,23,24);

###模糊查询只能和LIKE关键字结合使用
SELECT \*FROM t_soldiers WHERE zname LIKE '%1%';

SELECT \* ,CASE 
	
	WHEN zage >=28 THEN 'a'
	
	WHEN zage >=26 AND zage<28 THEN 'b'
	
	WHEN zage >=24 AND zage <26 THEN 'c'
   ELSE 'd'
END AS "level"
FROM t_soldiers;

## 时间查询 
SELECT SYSDATE();
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

## 字符串查询
SELECT CONCAT ('my','s','ql');
SELECT INSERT('这是一个数据库',3,2,'mysql');
SELECT LOWER ('MYSQL');
SELECT UPPER('mysql');
SELECT SUBSTRING('javamysqloracle',5,5 );

##聚合函数  返回一行数据
自动忽略null值，不进行统计。
SELECT SUM(zage) FROM t_soldiers;
SELECT	AVG(zage) FROM t_soldiers;
SELECT MAX(zage) FROM t_soldiers;
SELECT MIN(zage) FROM t_soldiers;
SELECT COUNT(\*) FROM t_soldiers;
SELECT COUNT(zage) FROM t_soldiers;

## 分组查询
SELECT zage,COUNT(id)  FROM t_soldiers GROUP BY zage;
SELECT 显示的列只能是分组依据列，或者聚合函数列

## 分组过滤依据 having过滤规则
SELECT zage,MIN(id)  FROM t_soldiers GROUP BY zage HAVING zage>23;

##限定查询 (limit起始行，查询行数)
是从起始行+1 行开始显示的
SELECT \* FROM t_soldiers LIMIT	0,5;
SELECT \* FROM t_soldiers LIMIT	5,5;

## 查询总结
SELECT 列名 
FROM 表名 
WHERE 条件 
GROUP BY 分组 
HAVING 过滤条件 
ORDER BY 排序列(ASC | DESC) 
LIMIT 起始行，总条数;

## 子查询
SELECT 列名 FROM 表名 WHERE 条件(子查询结果);
SELECT zage FROM t_soldiers WHERE zname="用户2";
SELECT \* FROM t_soldiers WHERE zage >(SELECT zage FROM t_soldiers WHERE zname="用户2");
SELECT id FROM t_soldiers WHERE zage=24;
SELECT \* FROM t_soldiers WHERE id IN (SELECT id FROM t_soldiers WHERE zage=24);

##合并查询
SELECT \* FROM 表名1 UNION SELECT \* FROM 表名2；
SELECT \* FROM 表名2 UNION ALL SELECT \* FROM 表名2；
###合并两张表的结果，去重复
两张表的列个数必须相同
SELECT \* FROM t1 UNION SELECT \* FROM t2;
SELECT \* FROM employees UNION SELECT \* FROM jobs;
###合并查询，保留重复
SELECT \* FROM t1 UNION ALL SELECT \* FROM t2;
##表连接查询
SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件；
###内连接查询inner join on 
通用标准语句
SELECT \* FROM employees INNER JOIN jobs ON employees.`job_id`=jobs.`job_id`;
mysql也可用的语句
SELECT \* FROM employees,jobs WHERE employees.`job_id`=jobs.`job_id`;
###三表连接查询
SELECT \* FROM employees e
INNER JOIN jobs	d
ON e.`job_id`=d.`job_id`
INNER JOIN departments l
ON e.`department_id`=l.`department_id`;
###左外连接 left join on 
SELECT \* FROM employees e
LEFT JOIN departments d 
ON e.`department_id`=d.`department_id`;
左外连接，以左表为主表，匹配不到返回null
###右外连接right join on 
SELECT \* FROM employees e 
RIGHT JOIN departments d 
ON e.department_id=d.department_id;
以右表为主表，匹配不到返回null


# DML 操作
## 新增insert    (列名和表名用`` (键盘1键左边那个键),值用"" 或'')
INSERT INTO \`t_soldiers\`(\`id\`,\`zname\`,\`zno\`,\`zbirthday\`,\`zgender\`,\`zweight\`,\`zheight\`,\`zage\`)
VALUE('11','用户11','11','','0','77','180','25');
## 修改update 
UPDATE t_soldiers SET \`zage\`=26 WHERE id=11;
## 删除delete 
DELETE FROM t_soldiers WHERE id=11;
## 清空整表数据truncate
与delete不同，truncate是把表销毁再重新创建一个该表
TRUNCATE TABLE 表名

# 数据表操作
## 数据类型
数值int 、double、double(M,D)、decimal(M,D)
日期date、time、datetime
字符串 char、varchar、blob、text
## 表的创建create（注意符号 最后一列后无符号）
CREATE TABLE t_subject(
subjectid INT,
subjectname VARCHAR(20),
subjecthours INT 
)CHARSET=utf8;
SELECT \* FROM t_subject;
INSERT INTO \`t_subject\` (  \`subjectid\`,\`subjectname\`,\`subjecthours\` )
VALUES (1,'java',40);
INSERT INTO t_subject(subjectid,subjectname,subjecthours)VALUES(2,'mysqk',20);
INSERT INTO t_subject(subjectid,subjectname,subjecthours)VALUES(3,'javascript',30);
## 表的修改alter 
###新增列gradeid
ALTER TABLE t_subject ADD gradeid INT;
SELECT \*FROM t_subject;
###修改列
ALTER TABLE t_subject MODIFY subjectname VARCHAR(10);	
###删除表中的列
ALTER TABLE t_subject DROP gradeid;
###修改列名
ALTER TABLE t_subject CHANGE subjecthours classhours INT;
###修改表名
ALTER TABLE t_subject RENAME t_sub;
SELECT *FROM t_sub;
###数据表的删除drop
DROP TABLE sub_ject;
# 约束 
## 实体完整性约束
表中的一行数据代表一个类class的实体entity,实体完整性的作用就是标识每一行数据不可重复、唯一性。
###主键约束primary key 
**标识此列的值不可重复且不为null**
CREATE TABLE sub_ject(
subid INT **PRIMARY KEY**  ** AUTO_INCREMENT**,
subname VARCHAR(20) **UNIQUE**  **NOT NULL**,
subhours INT **DEFAULT** 20
)CHARSET=utf8;
INSERT INTO sub_ject(subid,subname,subhours)VALUES(1,'java',40);
INSERT INTO sub_ject(subid,subname,subhours)VALUES(2,'mysql',30);
SELECT \*FROM sub_ject;
###唯一约束unique
**标识此列数据不可重复，可为null**
NSERT INTO sub_ject(subid,subname,subhours)VALUES(3,'mysql',30);
以上语句就会报错already EXISTS
###自动增长列auto_increment
**只能配合主键使用，从1开始，自动加1**
INSERT INTO sub_ject(subid,subname,subhours)VALUES('javascript',40);

##域完整性约束
限制列的单元格的数据正确性
###非空约束
**NOT NULL** 不能为null,必须要有值
###默认值约束
**DEFAULT** 值 为列赋予默认值，当新增数据不指定时，默认值填充。
###引用完整性约束(外键)
**CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名（列名）
创建关系表时，一定要先创建主表，再创建从表
删除关系表时，先删除从表，再删除主表。**
创建个专业表
CREATE TABLE speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
specialname VARCHAR(20) UNIQUE NOT NULL 
)CHARSET=utf8;
创建课程表，表中的specialid引用专业表的id
CREATE TABLE ubject(
ubid INT PRIMARY KEY AUTO_INCREMENT,
ubname VARCHAR(20) UNIQUE NOT NULL,
ubhours INT DEFAULT 20,
specialid INT NOT NULL,
**CONSTRAINT hcznbd.ubject.specialid FOREIGN KEY(specialid) REFERENCES speciality(id)**
)CHARSET=utf8;
SELECT \* FROM speciality;
SELECT \* FROM ubject;
##约束创建整合
创建表grade
CREATE TABLE Grade(
Gradeid INT **PRIMARY KEY AUTO_INCREMENT**,
Gradename VARCHAR(20) **UNIQUE NOT NULL**
)CHARSET=utf8;
创建表student
CREATE TABLE student(studentid VARCHAR(50) **PRIMARY KEY**,
studentname VARCHAR(50)**NOT NULL**,
sex CHAR(2) **DEFAULT** '男',
borndate DATE **NOT NULL**,
phone VARCHAR(11),
gradeid INT **NOT NULL**,
**CONSTRAINT hcznbd.student.gradeid FOREIGN KEY (gradeid) REFERENCES Grade(Gradeid)**
);
SELECT \* FROM student;
#事务
##模拟转账
###数据库模拟转账
CREATE TABLE account (
id INT,
money INT
)CHARSET=utf8;
INSERT INTO account (id,money)VALUES(1,10000);
INSERT INTO account (id,money)VALUES(2,1000);
SELECT * FROM account;
模拟转账，账号1给账户2转1000元
UPDATE account SET money=money-1000 WHERE id=1;
UPDATE account SET money=money+1000 WHERE id=2;
###模拟转账错误
如果上述减钱之后出现异常或加钱语句出错，会发现减钱成功，加钱失败。
每条sql语句都是独立的操作，一个操作执行完对数据库是永久的
##事务的概念
事务是一个原子操作。是一个最小执行单元，可由一个或多个sql语句组成，在同一个事务
中，所有的sql语句都成功执行时，整个事务才完成。
有一个sql语句执行失败，整个事务都会执行失败。
##事务的边界
开始：连接到数据库，执行一条DML语句。上一个事务结束后，
又输入了一条DML语句，即事务的开始
1）提交：显示提交：commit
	隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）
2）回滚：显示回滚：rollback
	隐式提交：非正常退出（断电、宕机），执行了创建、删除语句，但是失败了
		会为这个无效的语句执行回滚。
##事务的原理
数据库会为每一个客户端都维护一个空间独立的缓冲区（回滚段），
一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，
只有当事务中所有的sql语句均正常结束commit，才会将回滚段中的数据同步到数据库。
否则无论因为哪种原因失败，整个事务都将回滚rollback。
##事务的特性
###原子性atomicity
表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。
###一致性consistency
表示一个事务内有一个操作失败时，所有的更改过得数据都必须回滚到修改前状态。
###隔离性lsolation
事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，
要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
###持久性durability
持久性事务完成之后，它对于系统的影响是永久性的。
##事务应用
应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），
可通过程序逻辑手动控制事务提交或回滚
###事务完成转账
1.开启事务
开启事务后，执行的语句均属于当前事务，成功commit,失败rollback
START TRANSACTION;|或者setautocpmmit=0;#禁止自动提交setautocommit=1;#开启自动提交
2.事务内数据操作语句
UPDATE account SET money=money-1000 WHERE id=1;
UPDATE account SET money=money+1000 WHERE id=2;
3.事务内语句都成功了，执行commit;
COMMIT;
4.事务内如果出现错误，执行rollback;
ROLLBACK;
SELECT \* FROM account;
#权限管理
##创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
###创建一个用户
CREATE USER 'zhangsan' IDENTIFIED BY '123';
报错1290时执行：flush PRIVILEGES;
##授权
GRANT ALL ON myemployees.\* TO `zhangsan`;
##撤销权限
REVOKE ALL ON myemployees.\* FROM 'zhangsan';
##删除用户
DROP USER 'zhangsan';
#视图
##视图的创建
CREATE VIEW 视图名 AS 查询数据源表语句；
CREATE VIEW t_empinfo AS SELECT last_name,email,job_id,salary 
FROM employees;
##使用视图
SELECT \* FROM t_empinfo WHERE salary=6000;
SELECT \* FROM t_empinfo;
##视图修改
1：create OR REPLACE VIEW 视图名 AS 查询语句；
	如果视图存在则修改，如果不存在则创建
2：alter VIEW 视图名 AS 查询语句；
	直接对已存在的视图进行修改
CREATE OR REPLACE VIEW t_empinfo AS
SELECT employee_id,last_name,salary FROM employees;
##视图的删除
不会影响原表
DROP VIEW t_empinfo;
##视图的注意事项
视图不会独立存储数据，原表发生改变，视图也发生改变，没有优化任何查询性能。
如果视图包含一下结构中的一种，则视图不可更新
	聚合函数的结果
	distinct去重后的结果
	GROUP by分组后的结果
	having筛选过滤后的结果
	union、union all联合后的结果
#SQL语言分类
数据查询语言DQL：select 、 WHERE 、order by、 GROUP by、having
数据定义语言DDL：create 、 ALTER 、frop
数据操作语言DML：insert 、 UPDATE 、delete 
事务处理语言TPL：commit 、rollback
数据控制语言DCL：GRANT 、revoke