MySQL实践基础-1

1. 环境操作：

• 命令行下远程连接数据库：mysql -h ip地址 -u root -p
• centos下初始化数据库的安全设置：mysql_secure_installation
• 授权一个root用户{允许远程的其他机器登陆本数据库}：grant all on *.* to root@'%' identified by 'root用户的密码'
• 修改mysql对数据库的默认编码：{打开配置文件}
  老版本：default-character-set=utf8
  新版本：character-set-server=utf8

2. sql功能：

• 数据库数据的增删改查操作（CRUD）
• 数据库对象的创建，修改和删除操作
• 用户权限/角色的授予和取消
• 事务控制

3. sql语句分类：

• DQL (数据查询，最重要且最常用的)，如：select
• DML (数据操作，)，如：insert, update, delete
• DDL (数据定义，)，如：create, alter, drop
• DCL (数据控制)，如：grant, revoke
• TCL (事务操作)，如：SAVEPOINT 、 ROLLBACK、SET TRANSACTION ，COMMIT

4. DDL:

• 创建，使用, 删除 数据库：

create database 数据库名称; -- 分号表示一行结束，不可忘 
drop database 数据库名称; 
use 数据库名称

例如：
-- 创建d_student
create database d_student
-- 使用d_student
use d_student
-- 删除d_student
drop database d_student

• 对表及表字段的CRUD：

查看当前数据库中所有表：show tables;
创建表（auto_increment表示自动增长）:create table 表名(列及类型);
修改表：alter table 表名 add|change|drop|modify 列名 类型;
删除表：drop table 表名;
查看表结构：desc 表名;
更改表名称：rename table 原表名 to 新表名;
查看表的创建语句：show create table '表名';

-- 使用数据库
use d_student;

-- 查看数据库所有的表、
show tables;

-- 创建表
-- sql对大小写不敏感{视作一致}
create table t_user(
id  int auto_increment PRIMARY KEY, 	-- 逗号被认为是一个字段的定义结束
sname VARCHAR(20) not NULL 						-- 在最后一个字段的定义处不要加逗号
);

-- 修改表
alter TABLE t_user add sex int DEFAULT 0;														-- 新增一个属性
alter TABLE t_user add class VARCHAR(30) not NULL;
alter TABLE t_user add score int DEFAULT 0;

alter table t_user modify sex int DEFAULT 1;  											-- 在旧属性基础上修改

ALTER TABLE t_user CHANGE class v_subject VARCHAR(40) not NULL; 			-- 旧属性换成新属性

-- 查看表结构、
desc t_user;

-- 更改表名称 : 
RENAME TABLE t_user to tt_user;
RENAME TABLE tt_user to t_user;

-- 查看表的创建语句
show CREATE TABLE `t_user`;

-- 删除表
drop TABLE t_user;
-- 删完再重建一次

5. DML：

全列插入：insert into 表名 values (...)
缺省插入：insert into 表名 (列1,...) values (值1,...)
同时插入多条数据：insert into 表名 values (...),(...)...;
或insert into 表名(列1,...) values (值1,...),(值1,...)...;

修改：update 表名 set 列1=值1,... where 条件

删除：delete from 表名 where 条件

-- select其实是DQL
select * from t_user;

-- 先看下表结构
desc t_user;  			
-- 完整写法
INSERT INTO t_user (id, sname, sex, v_subject) values (0, 'n1', 0, 's1');     -- id是自增的，所以这个字段随便写{一般用0占位}
-- 还可以缺省{比如：如果要插入所有字段，就无需写字段名}
INSERT INTO t_user values (0, 'n2', 0, 's2', 100);
-- 只插入一些字段
INSERT INTO t_user (sname, v_subject) values ('n2', 's2');
-- 一次性插入多条记录
INSERT INTO t_user (sname, v_subject) values ('n3', 's3'), ('n4', 's4'), ('n5', 's5'); 

-- 更新记录、
update t_user set sname='n6', v_subject='s6' where id=6;

-- 删除记录
DELETE from t_user WHERE sname = 'n5'; 

-- 	逻辑删除
alter TABLE t_user add isdelete bit DEFAULT 0; -- 增加一个标志位属性，默认为0表示存在，为1表示已删除
-- 执行逻辑删除即修改标志位
update t_user set isdelete=1 WHERE id = 6;

• 数据库备份：
  在cmd下执行 mysqldump -h mysql服务器IP地址 –u root –p 数据库名 > 某路径/备份文件.sql;

• 数据库恢复：
  先在cmd环境下登录进入mysql，在里面创建一个数据库：create database 数据库名;
  再在cmd下执行 mysql -u root –p 数据库名 < 某路径/备份文件.sql;

• 已知有一个数据库，要向里面批量增加一些数据：
  use 数据库名;
source 某路径/待执行的数据.sql

6. DQL语句{核心是select}:

select的几个用法：

1. select * from 表名
2. WGOL四个筛选条件: where, group by, order by, limit
3. 支持 { 算术>连接>比较>逻辑 } 4种运算符
   算术：可以对表中某数值型字段进行数学运算后再作为一个新字段查询出来
   连接： 字段1 || 字段2 || ... || 字段n 等价于把n个字段合并为一个超大字段返回 （mysql有可能不支持，oracle支持）
   比较：对某些字段施加（<,=,>及其组合）,并将之作为where后面的条件。
   逻辑：对某些字段施加 is [not] null, like, [not] in, [not] between .. and, and, or, not 等逻辑运算符，并作为where条件
4. 还支持小括号，强制改变运算符优先级
5. 可以把任何字段通过as创建别名，并且as 可以省略
   （用空格来代替as的功能，因为空格区分字段及其别名；而只有逗号才是分隔开两个字段。故不会混淆）
6. distinct可以对查询的某个字段进行去重

• 各个关键字的优先级：当一个sql里面有 select, from, where, order by 时，首先执行from, 其次where, 接着select, 最后order by

-- 看一下表结构
DESC t_user;
-- 最简单的select
select * from t_user;
 
-- where筛选
SELECT * from t_user where id =4;

-- GROUP BY 以某字段对记录进行分组
-- SELECT * from t_user GROUP BY 

-- ORDER BY
INSERT INTO t_user VALUES (0, 'nn', 0, 'ss', 10, 0), (0, 'nn', 0, 'ss', 20, 0);
SELECT * from t_user ORDER BY score; -- 默认是正序
SELECT * from t_user ORDER BY score ASC; -- 正序
SELECT * from t_user ORDER BY score DESC; -- 逆序

-- 分页查询
-- LIMIT n:仅显示符合条件的前两条记录
SELECT * from t_user LIMIT 2; 
-- LIMIT offset,num 从offset开始拿出num条记录
SELECT * from t_user limit 0,4; -- 0代表第一条记录
SELECT * from t_user limit 4,4;

-- WGOL，可以和算术，连接，比较，逻辑，括号等运算符联合使用
-- 改造一个数值型的字段，并添加where 条件
select score, score*1/2 from  t_user where id =  1;
SELECT sname, score*1/3 from t_user where score <> 0; -- 即score不为0
SELECT sname, score*1/3 from t_user where (id>7 and score <> 0); -- 即score不为0

-- 使用as为字段创建别名
select score, score*1/2 as halfOfScore from  t_user where id = 6;

-- 省略as
select score, score*1/2 halfOfScore from  t_user where id = 6;

-- dictinct
SELECT * from t_user;
INSERT INTO t_user VALUES (0, 'n1', 0, 's1', 0, 0); -- 插入一条和id=1相同内容的记录
SELECT sname from t_user; -- 不使用distinct
SELECT DISTINCT sname from t_user; -- 使用distinct

7. 聚合查询和内置函数：

count() :统计某个字段或者记录个数
max() ：计算某个字段的max
avg() ：计算某个字段的均值
date:日期；datetime：日期时间；time：时间；timestamp：时间戳
注意字符串到上述各个时间格式的转换函数的使用{非常非常常用，必须熟练}
# 更多函数用到时查手册和help一下

8. 分组：

select * from 表名 [where] [group by ...] [order by ...] [limit ...]
# 上述的顺序是固定的，不可以更改：W在G前，G在O前。当然O在L前：WGOL

-- 给t_user加一个字段：class{班级}
ALTER TABLE t_user add class int DEFAULT 0;
-- 查看下表结构
DESC t_user;

-- 手动把每一个记录的class和score 都给修改一下
SELECT * FROM t_user;

-- 分组查询
SELECT class from t_user GROUP BY class;
-- 注意：如果使用了{group by 某些字段X}，那么select的字段只能是X中的字段，或者使用了聚合函数的其他字段
SELECT class, max(score), min(score), AVG(score) from t_user GROUP BY class;
-- count(常数)比count(*)更高效，也是统计记录个数
SELECT class, max(score), min(score), AVG(score), count(1) from t_user GROUP BY class;
-- 加一个where条件，注意WGOL顺序哦 {因为对执行结果有影响}
SELECT class, max(score), min(score), AVG(score), count(1) from t_user where id <> 7 GROUP BY class;

-- 如果要过滤某些班级{这个班级score的均值小于30},
-- 考虑到优先级 from>w>G>>select, 而且where里面不能写聚合函数{聚合函数只能出现在select中}
-- 有一个方法：对分组并同统计了各组的score的均值之后，再过滤：即having关键字
-- 写在 GROUP BY 后面，自然优先级低于 GROUP BY
SELECT class, max(score), min(score), AVG(score), count(1) from t_user where id <> 7 GROUP BY class HAVING AVG(score) > 30;
-- 先把那个聚合函数的值取个别名，再用在having后面，我个人喜欢这样使用。


-- 再次回顾执行顺序：from>where>GROUP BY>HAVING>SELECT>ORDER BY>LIMIT {非常重要}

9. 约束：

• 四大约束：

类型	详情
主键约束(primary key)	功能上唯一标识一条记录。特性上等价于唯一且非空
检查约束(check)	对某个属性的范围限制，格式限制等
非空约束(not null)	某个字段的内容不能为空
唯一约束(unique)	某个字段的内容必须唯一，但可以为空
外键约束(foreign key)	为两张表建立联系。语法上定义了外键的那个表叫子表，那个被引用的那个表叫主表。要先创建主表，再创建子表

• 主键约束：

1. 满足实体完整性：作为主键的字段要求唯一且非空
2. 一个表只能一个主键
3. 主键可以是单字段或者多字段的组合

• check约束：比如：default 0, check(age>18 and age<35)

• 非空约束：比如：name not null

• 唯一约束：比如：classId unique

• 外键约束：

1. 先建立主表，再建议子表
2. 要删除主表，先处理子表中所有引用了主表的记录

use d_student;

-- 创建主表
CREATE TABLE t_class(
id int auto_increment PRIMARY KEY,
class_name VARCHAR(30) not null
)
INSERT INTO t_class VALUES (0, 'math'), (0, 'english'), (0, 'chinese');

-- 创建子表
CREATE TABLE t_selectclass(
id int PRIMARY KEY,
student_selectclass_id int 
)
-- 表外再添加外键约束
ALTER TABLE t_selectclass add FOREIGN KEY (student_selectclass_id) REFERENCES t_class (id);

--现在在子表中{即外键}引用主表的主键
INSERT INTO t_selectclass VALUES (0, 1); -- 失败，因为1在主表 t_class中不存在
INSERT INTO t_selectclass VALUES (0, 6); -- 成功
INSERT INTO t_selectclass VALUES (1, 7); -- 成功
INSERT INTO t_selectclass VALUES (0, 8); -- 失败，因为8在主表 t_class中不存在。

10. 索引：

1. 创建和删除

• 自动：当指定一个字段为primary key 或 unique 时，自动为此字段创建索引
• 手动：create index 索引名 on 表名 (字段名)
• 手动删除：drop -- 查阅手册即可

-- 为t_selectclass的id创建索引
CREATE INDEX i_selectclass_id on t_selectclass (id);
-- 当基于id对表t_selectclass做select操作，才会使用索引i_selectclass_id加快查找
SELECT * from t_selectclass WHERE id = 0;

2. 注意：索引仅仅针对select查询加速，反而会造成 insert, update, delete操作的效率降低；其次，索引只适合唯一性的字段，不适合重复性较高的字段；索引造成存储空间的浪费；故而要慎用。

11. 视图:

1. 作用：

• 实现对基本表的隐藏及限制访问，把几个基本表的某些字段返回为一个视图（虚表），外部通过视图来对基本表的某些字段进行有限的访问。
• 简化复杂的sql，分解为多步的简单sql
• 实现对相同字段的不同展示

2. 创建与删除：

• create or replace view v_

12. 事务：

• 使用事务锁来保证一组sql语句的修改操作具有原子性。
  也就是说，要么这些修改操作全部正常执行，要么一条也不执行。

begin
# 加入一组sql {insert, update, delete}
commit
rollback

13. 表的关系：

• 一对一：
• 一对多：
• 多对多：
• 表设计实例 {结合powerdesingner}:

14. 表连接与子查询：