数据库笔记

跟着PHP课程重新记录一下MySQL的笔记

1.数据库操作：

• 创建数据库

create database 数据库名;
create database 数据库名 charset=字符集;   #设定默认字符集
create database 数据库名 default cheoharacter set 字符集;   #设定默认字符集

• 删除数据库

drop database 数据库名;

• 使用数据库

use 数据库名;

• 查询当前正在使用的数据库

select database();

• 设置数据库字符集

alter database 数据库名 character set 字符集;

2.数据表操作

• 创建表

create table 表名 (字段名 字段类型 [NOT NULL]);

• 查询数据库的表格个数

# 第一种写法
show tables from 数据库名;

# 第二种写法,如果已经选择了使用的数据库则可以直接
show tables;

• 修改表名

rename table 旧表名 to 新表名;

• 查看表格的详细信息

show create table 表名;

• 查看字段

desc 表名;

• 插入数据

# 两种写法into都可以省略
insert into 表名 values(数据1, 数据2...) (数据1, 数据2...) # 可同时插入多组数据
insert into 表名 (字段1,字段2...) values (数据1, 数据2...) # 这种写法可以跳过部分字段

• 查询数据

select 字段1,字段2 from 表名 [where...][limit n][offset m]

# LIMIT 属性来设定返回的记录数。
# OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

• 更新数据

update 表名 set 字段1 = 值1,字段2 = 值2 [where...]

• 删除数据

delete from 表名 [where...]

3.筛选条件

• 去重(distinct)

例：select distinct job from user;  # 查询user表中所有不重复的job名
经常使用（count(distinct id)）来返回不重复字段的条数

• NULL

# 可以在语句中加入is null 和is not null来筛选字段数据
例：select * from user where job is not null;   # 查询user表中所有有工作的人的信息

• between

select * from user where age between 20 and 30;  #包含范围两端

• in

#  in运算符允许确定指定的值是否与列表中的值或子查询中的任何值匹配。 也可以搭配not使用
select 字段1,字段2 from 表名 where (expr|column_1) in (值1,值2);

• as

# 设置字段的别名
select 字段名 as "别名" from 表名;  # 最完整的写法 as和引号都可以省略

例如:
select 字段名 "别名" from 表名;
select 字段名 别名 from 表名;

• like

# 匹配的字符串必须加单引号或双引号
# 通配符% 它能代表任何长度的字符串，字符串的长度可以为 0
# 通配符_ 它只能代表单个字符，字符串的长度不能为 0

例:select * from user where name like '王%';  #查询所有以王姓开头的用户


# 默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字
例:select * from user where name like binary 't%';
# 这样就不会查询到 T 开头的名字了


tips:
1. % 通配符可以到匹配任意字符，但是不能匹配 NULL。
2. mysql通配符的处理会比其他操作符花费更长的时间,如果其它操作符能达到相同的目的，应该使用其它操作符。
3. 在确定使用通配符后，除非绝对有必要，否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
4. 如果查询内容中包含通配符，可以使用"\"转义符。

• 排序**[ASC|DESC]**

select 字段名 from 表名 [where...] order by 字段名 [ASC|DESC];
# ASC代表升序、DESC代表降序  不跟默认升序
# order by 后面可以跟多个条件，例如 order by age, id desc;

• 分页查询(LIMIT)

格式：limit 偏移量,取出的数据条数	# 偏移量不写默认为0
limit 跳过的条数,请求的条数(每一页的条数)	# 跳过的条数 = (请求的页数-1) * 请求的条数
tips:分页查询一般和排序组合使用

• 聚合函数

max, min, avg, sum, count

• 分组查询(GROUP BY)

格式：group by 分组字段名

• HAVING

# 和where的区别
having：后面跟聚合函数，可以单独使用
where：后面跟普通字段条件，不能包含聚合函数

4.子查询(嵌套查询)

在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为 嵌套查询。

#  查询工资大于二号部门平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp where dept_id = 2);

tips:
1.子查询的SELECT语句中不能使用 ORDER BY 子句，因为 ORDER BY 子句只能对最终查询结果排序。

5.关联查询(同时查询多表) 完善中…

1. 等值连接|笛卡尔积
#  查询工资高于2000的员工姓名和部门名
select emp.name,dept.name from dept, emp where emp.sal > 2000 and emp.dept_id = dept.id;

2. 内连接
select 字段1,字段2 from 表名1 join 表名2 on 关联关系 [where...]

3. 外连接
#　外连接有三种：左外连接、右外连接、全外连接，左/右外连接没有本质区别
select 字段1,字段2 from 表名1 left/right 表名2 on 关联关系 [where...]

6.奇技淫巧

• 修改正在使用被外键约束的列

#　原本不允许进行修改，因为这个操作违反了外键约束，破坏了数据库完整性。
#　但是万能的mysql提供了一个方法，临时关闭外键约束，当修改完成之后再将外键约束加回来。 

SET FOREIGN_KEY_CHECKS = 0;
......
......
SET FOREIGN_KEY_CHECKS = 1;

• SQL语句书写顺序

#  完善中
select 字段 from 表名 where 普通字段条件 group by 分组字段 having 聚合函数条件 order by 排序字段名 limit 跳过的条数，请求的条数