DDL (操作数据库)

1.创建数据库 ：create database 数据库名;Create database if not exist 数据库名;Create datebase if not exist 数据库名 character set 码表名字2.查看数据库：show databases;查看数据库的定义信息 ：show create database 数据库名;3.修改数据库（修改定义码表）：alter database 数据库名 character set 码表名;4.删除数据库：drop database 数据库名;5.使用数据库：use 数据库名;查看正在使用的数据库：select(选择) database();

#DML(操作表结构)：

1.添加表：create table 表名 (列名1 字段类型,列名2 字段类型)创建一个表结构相同的表：Create table 新表名 like 旧表名;【注】： 类型 int varchar(最大长度) double(x,x) like date TIMESTAMP（时间戳）2.查看表：show tables;查看表结构 ： desc表名;查看创建表的sql语句：show create table 表名;3.删除表 ：drop table 表名;判断表是否存在之后再删除：drop table if exists 表名;4.修改表 ：A.添加列：alter table 表名 add 列名 类型;B.修改列类型：alter table 表名 modify(修改) 列名 新类型;C.修改列名:alter table 表名 change 列名 新列名 新类型;D.删除列：alter table 表名 drop 列名;E.修改表名；rename table 表名 to 新表名;F.修改表的字符集；alter table 表名 character set 新码表;

DML(操作表中的数据) 对表中的记录进行增删改查

1.插入记录：insert(插入) into 表名 (字段1, 字段2,...字段n) value(值1, 值2, ... 值n);2.更新表中的记录 ：update(更新) 表名 set 列名 = 要修改的值 where [条件];3.删除表中的记录 ：A.delete from表名 where [条件];B.Truncate table 表名;  [删除表结构 创建新表]

DQL(查询表中的数据)

1. 语法：select字段列表from表名列表where条件列表group by分组字段having分组之后的条件order by排序limit分页限定1.查询： select 列名1, 列名2,.....列名n from 表名 where [条件];A.查询所有 ：select * from 表名;B.查询指定列 ：select 字段名 from 表名;C.指定的列的别名查询：select 字段名 as 别名 from 表名 as 表别名;D.清除重复值查询：select distinct(截然不同的) 字段名 from 表名;E.查询结果参与运算：select 字段1 + 字段2 as 别名 from 表名F.条件查询 ：select 字段名 from表名 where [条件];G.运算符参与查询：select 字段名 from 表名 where 字段名 >= ?;select 字段名 from 表名 where 字段名 in (x,y);Between .. and;H.模糊查询：select 字段名 from 表名 where 字段名 like 通配符I.排序 ：select 排序输出的字段 from 表名 order by 排序的标准字段名1 [asc][desc]select from where order byJ.在某个条件下多层排序查询 ：排序输出的字段名 from 表名where 排序的范围 order by 排序的标准的字段名1 [asc][desc]，排序的标准的字段名2 [asc][desc];例如：select * from student order by math asc , chinese asc;上面的sql代表 在数学分数相同时 用语文分数排序 而且输出的是排序后的整体K.聚合函数 ：select avg (字段名) 别名form 表名;L.分组:select 输出的字段1，输出的字段2，...输出的字段n  from 表名 group by 分组字段 having 条件;例如:按性别分组 输出显示性别和人数和数学平均分Select sex, count(number),avg(math) from student group by sex ;例如:按性别分组 并且只统计年纪大于25岁的人的平均分和人数 并且不输出人数少于2的组Select sex,count(*),avg(math) from student where age > 25 group by sex having count(*) > 2;复杂：SELECT DISTINCT *, AVG(math + IFNULL (english,0) + java) 总分 FROM student01 WHERE math != 0 GROUP BY id HAVING 总分 > 100 ORDER BY math DESC, java DESC , english DESC;

约束：

主键约束： primary(主要的)  key非空约束： not null唯一约束： unique(独特的)外键约束： foreign (外国)  key1.主键约束:A.在创建表的时候 添加主键约束语法：create table 表名(Id int primary key auto_increment       (增量));注：这里的auto_increment是自增B.再创建表之后 删除主键约束 或者添加主键约束语法_删除: alter table 表名 drop primary key;[因为主键只有一个 所以不用声明drop的字段]语法_添加:alter table 表名 add primary key(主键字段);2.唯一约束A.语法：create table 表名(Id int primary key auto_increment,Name varchar(20) unique);3.非空约束语法：create table 表名(Id int primary key auto_increment,Name varchar(20) not null,);4.外键约束语法：1. 创建时：Create table 表名 (Id int primary key auto_increment,Name varchar(20) not null,dep_id int,//外键约束Constraint dep_emp_fk foreign key (dep_id) references department (id);约束 外键名称 外键关键字 (外键字段) 参照 主表(主键字段))2. 创建完:Alter table 表名add constraint dep_emp_fk foreign key (dep_id) references department(id);3. 删除：Alter table employee drop foreign key dep_emp_fk；【注：】 外键约束的使用：1. 必须有主键2. 在一对一的表关系中 任意定义副表定义外键 关联主表中的主键3. 在一对多 或者 多对一 的表关系中1. 多的那方 为副表 在多的那方定义外键 关联主表的主键例如：有一个学生表 一个课程表那么一个学生可以对应多个课程写法：  create table class (c_id int,c_name varchar(20),stu_id,-- 定义外键foreign key (stu_id) references student(id))；4. 在多对多的表关系中 要是用一张中间表 扮演副表的角色 分别关联两个多的主表1. 注意：中间表需要有集合主键2. 写法：例如：有一个账号表 一个要购买的游戏道具表 那么这两个表之间就是多对多的关系以为 一个账号可以购买多个游戏道具 而一种道具也可以被多个账号购买写法：1. 首先创立一个账号表create table user (u_id int primary key unique auto_incream,u_name varchar(20) not null)；2. 然后创立一个游戏道具表create table gametools(gt_id int primary key unique auto_incream,ft_name varchar(30) not null unique)；3. 创立一个中间表create table tool_user(gameToolsId int,userId int,/*集合主键*/primary key(gameToolsId,userId),/*外键*/foregin key (gameToolsId) references gametools(gt_id),foregin key (userId) references user(u_id))；4. 级联操作1. 添加级联操作语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;2. 分类：1. 级联更新：ON UPDATE CASCADE2. 级联删除：ON DELETE CASCADE

数据库的设计

1. 多表之间的关系1. 分类：1. 一对一(了解)：* 如：人和身份证* 分析：一个人只有一个身份证，一个身份证只能对应一个人2. 一对多(多对一)：* 如：部门和员工* 分析：一个部门有多个员工，一个员工只能对应一个部门3. 多对多：* 如：学生和课程* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择2. 实现关系：1. 一对多(多对一)：* 如：部门和员工* 实现方式：在多的一方建立外键，指向一的一方的主键。2. 多对多：* 如：学生和课程* 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键3. 一对一(了解)：* 如：人和身份证* 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。3. 案例-- 创建旅游线路分类表 tab_category-- cid 旅游线路分类主键，自动增长-- cname 旅游线路分类名称非空，唯一，字符串 100CREATE TABLE tab_category (cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(100) NOT NULL UNIQUE);-- 创建旅游线路表 tab_route/*rid 旅游线路主键，自动增长rname 旅游线路名称非空，唯一，字符串 100price 价格rdate 上架时间，日期类型cid 外键，所属分类*/CREATE TABLE tab_route(rid INT PRIMARY KEY AUTO_INCREMENT,rname VARCHAR(100) NOT NULL UNIQUE,price DOUBLE,rdate DATE,cid INT,FOREIGN KEY (cid) REFERENCES tab_category(cid));/*创建用户表 tab_useruid 用户主键，自增长username 用户名长度 100，唯一，非空password 密码长度 30，非空name 真实姓名长度 100birthday 生日sex 性别，定长字符串 1telephone 手机号，字符串 11email 邮箱，字符串长度 100*/CREATE TABLE tab_user (uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(100) UNIQUE NOT NULL,PASSWORD VARCHAR(30) NOT NULL,NAME VARCHAR(100),birthday DATE,sex CHAR(1) DEFAULT \'男\',telephone VARCHAR(11),email VARCHAR(100));/*创建收藏表 tab_favoriterid 旅游线路 id，外键date 收藏时间uid 用户 id，外键rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次*/CREATE TABLE tab_favorite (rid INT, -- 线路idDATE DATETIME,uid INT, -- 用户id-- 创建复合主键PRIMARY KEY(rid,uid), -- 联合主键FOREIGN KEY (rid) REFERENCES tab_route(rid),FOREIGN KEY(uid) REFERENCES tab_user(uid));2. 数据库设计的范式* 概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。* 分类：1. 第一范式（1NF）：每一列都是**不可分割**的原子数据项2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的**部分函数**依赖）* 几个概念：1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A例如：学号-->姓名。  （学号，课程名称） --> 分数2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。例如：（学号，课程名称） --> 分数3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。例如：（学号，课程名称） -- > 姓名4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A例如：学号-->系名，系名-->系主任5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码例如：该表中码为：（学号，课程名称）* 主属性：码属性组中的所有属性* 非主属性：除过码属性组的属性3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除**传递依赖**）

数据库的备份和还原

1. 命令行：* 语法：* 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径* 还原：1. 登录数据库2. 创建数据库3. 使用数据库4. 执行文件 source 文件路径

* 查询语法：* select列名列表form表名列表where..* 笛卡尔积：* 有两个集合A,B .取这两个集合的所有组成情况。* 要完成多表查询，需要消除无用的数据* 多表查询的分类：1. 内连接查询：1. 隐式内连接：使用where条件消除无用数据2. 显式内连接：1. 语法：select 字段列表 from 表名1 join 表名2 on 条件2. 这里的 on 条件 相当于 where 条件3. 内连接查询1. 从哪些表中查询数据2. 条件是什么3. 查询哪些字段2. 外链接查询：1. 左外连接：* 语法： select 字段名 from 表1 left join 表名2 on 条件* 这里的 on 相当于 where* 查询的是左表中的所有数据 和两个表的交集部分（可以解决 null 值不显示的弊端）2. 右外连接：3. 子查询1. 概念 查询中嵌套查询，被嵌套查询 叫做子查询* 分类1.  子查询的结果是单行 单列的* 子查询可以作为条件， 使用运算符来判断* -- 查询员工工资小于平均工资的人SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);2. 子查询的结果是多行单列的：* 子查询可以作为条件，使用运算符in来判断-- 查询\'财务部\'和\'市场部\'所有的员工信息SELECT id FROM dept WHERE NAME = \'财务部\' OR NAME = \'市场部\';SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;-- 子查询SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = \'财务部\' OR NAME = \'市场部\');3. 子查询的结果是多行多列的：* 子查询可以作为一张虚拟表参与查询-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息-- 子查询SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > \'2011-11-11\') t2WHERE t1.id = t2.dept_id;-- 普通内连接SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  \'2011-11-11\'

事务

* 语法start transaction （事务,交易）commit（提交，犯罪，交付）rollback* 单词1. transaction 事务2. commit 交付3. rollback 回滚4. repeatable read 重复的（mysql默认隔离级别）5. read uncommit6. read commit（oracle默认隔离级别）7. serializable 可串行化的8. 查询隔离级别：select @@tx_isolation;9. 设置隔离级别：set global transaction isolation level 级别字符串;10. identified 被认同的11. grant 授予12. revoke 撤销1. 事务的操作1. 事务的概念：* 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。2. 操作：1. 开启事务： start transaction;2. 提交事务： commit1. mysql在执行(dml_ 表的增删改) 操作后默认提交2. oracl不默认提交3. 修改事务的默认提交方式：* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交* 改默认提交方式： set @@autocommit = 0;3. 回滚： rollback3. 执行的例子：* start transaction* update lisi set balance = balance - 500 where name = \'lisi\';* 出错了.. (程序停止 此时lisi少了500 而wangwu没多 违背了事务的特征之一 -> 一致性)* update wangwu set balance = balance + 500 where name = \'wangwu\';* -- 发现执行没有出现问题* commit； --提交* -- 发现执行出现问题* rollback; -- 回滚2. 事务的四大特征1. 原子性2. 持久性3. 隔离性4. 一致性3. 事务的隔离级别1. 多个事务读取表中数据存在问题1. 脏读2. 虚读3. 幻读2. 隔离级别1. read uncommitted；读未提交* 产生的问题 所有问题2. read committed* 产生的问题 虚读 幻读（oracle默认）3. repetable read; 重复读* 产生的问题 幻读（mySql默认）4. serialozable 串行化（锁表）*  可解决所有问题 但效率太低

DCL（管理用户 授权等操作）

* SQL分类：1. DDL(数据库和表的操作)2. DML(表的增删改)3. DQL(表的查询)4. DCL(管理用户 授权等操作)* DCL的操作人员 一般为DBA* DCL:1. 用户管理1. 增1. create user \'用户名\'@\'主机名\' identified by \'密码\';2. 删1. drop user \'用户名\'@\'主机名\';3. 改1. 修改密码_1* update user set passwrod = password(\'新密码\') where user = \'用户名\';2. 修改密码_2* set password for \'用户名\'@\'主机\' = password(\'新密码\');4. 查1. use mysql2. select * from user;2. 权限管理：1. 查询权限：-- 查询权限SHOW GRANTS FOR \'用户名\'@\'主机名\';2. 授予权限：-- 授予权限grant 权限列表 on 数据库名.表名 to \'用户名\'@\'主机名\';3. 撤销权限：-- 撤销权限：revoke 权限列表 on 数据库名.表名 from \'用户名\'@\'主机名\';