mysql入门笔记3

男娘i 2022-07-21 02:16 151阅读 0赞

**26课 union查询**

就是把两条或者多条的查询结果合并成一个结果

两条语句的where 都比较复杂，在一起写就很麻烦，分成两个简单的

mysql> select \* from goods where cat\_id =3  
\-> union  
\-> select \* from goods where cat\_id = 4;  
\+———-+————————+——–+———-+———–+-  
—+————+————–+————-+  
| goods\_id | goods\_name | cat\_id | brand\_id | goods\_sn |  
er | shop\_price | market\_price | click\_count |  
\+———-+————————+——–+———-+———–+-  
—+————+————–+————-+  
| 8 | 飞利浦9@9v | 3 | 4 | ecs000008 |  
1 | 399.00 | 478.79 | 10 |  
| 9 | 诺基亚e66 | 3 | 1 | ecs000009 |  
4 | 2298.00 | 2757.60 | 20 |  
| 10 | 索爱c702c | 3 | 7 | ecs000010 |  
7 | 1328.00 | 1593.60 | 11 |  
| 11 | 索爱c702c | 3 | 7 | ecs000011 |  
1 | 1300.00 | 0.00 | 0 |  
| 12 | 摩托罗拉a810 | 3 | 2 | ecs000012 |  
8 | 983.00 | 1179.60 | 13 |  
| 13 | 诺基亚5320 xpressmusic | 3 | 1 | ecs000013 |  
8 | 1311.00 | 1573.20 | 13 |  
| 15 | 摩托罗拉a810 | 3 | 2 | ecs000015 |  
3 | 788.00 | 945.60 | 8 |  
| 17 | 夏新n7 | 3 | 5 | ecs000017 |  
1 | 2300.00 | 2760.00 | 2 |  
| 19 | 三星sgh-f258 | 3 | 6 | ecs000019 |  
12 | 858.00 | 1029.60 | 7 |  
| 20 | 三星bc01 | 3 | 6 | ecs000020 |  
12 | 280.00 | 336.00 | 14 |  
| 21 | 金立 a30 | 3 | 10 | ecs000021 |  
40 | 2000.00 | 2400.00 | 4 |  
| 22 | 多普达touch hd | 3 | 3 | ecs000022 |  
1 | 5999.00 | 7198.80 | 16 |  
| 24 | p806 | 3 | 9 | ecs000024 |  
00 | 2000.00 | 2400.00 | 35 |  
| 31 | 摩托罗拉e8 | 3 | 2 | ecs000031 |  
1 | 1337.00 | 1604.39 | 5 |  
| 32 | 诺基亚n85 | 3 | 1 | ecs000032 |  
4 | 3010.00 | 3612.00 | 9 |  
| 1 | kd876 | 4 | 8 | ecs000000 |  
1 | 1388.00 | 1665.60 | 9 |  
| 14 | 诺基亚5800xm | 4 | 1 | ecs000014 |  
1 | 2625.00 | 3150.00 | 6 |  
| 18 | 夏新t5 | 4 | 5 | ecs000018 |  
1 | 2878.00 | 3453.60 | 0 |  
\+———-+————————+——–+———-+———–+-  
—+————+————–+————-+  
18 rows in set (0.08 sec)

Union取出来的一定要列相同，，才能合并，而且可以跨表查询

列名称未必要一致，列名称会使用第一个的列名称

注意 使用union 完全相等的行将会被合并  
合并是比较耗时的工作，一般不让union进行合并，使用union all 可以避免  
合并，可以读一速度有一个很好的提升。

mysql> select \* from a  
\-> union  
\-> select \* from b;  
\+——+——+  
| id | num |  
\+——+——+  
| a | 5 |  
| b | 10 |  
| c | 15 |  
| d | 10 |  
| b | 5 |  
| d | 20 |  
| e | 99 |  
\+——+——+  
7 rows in set (0.00 sec)

mysql> select \* from a  
\-> union all  
\-> select \* from b;  
\+——+——+  
| id | num |  
\+——+——+  
| a | 5 |  
| b | 10 |  
| c | 15 |  
| d | 10 |  
| b | 5 |  
| c | 15 |  
| d | 20 |  
| e | 99 |  
\+——+——+  
8 rows in set (0.00 sec)

Union 的子句中不用写order by 就算写了也没有效果  
因为sql合并后得到的总的结果集可以order ，子句没有意义去order by  
最后还会合并在一起

例题，把两个表中同id的加和显示出来

mysql> select id,sum(num) from  
\-> (  
\-> select \* from a  
\-> union all  
\-> select \* from b  
\-> )  
\-> as tmp  
\-> group by id;  
\+——+———-+  
| id | sum(num) |  
\+——+———-+  
| a | 5 |  
| b | 15 |  
| c | 30 |  
| d | 30 |  
| e | 99 |  
\+——+———-+  
5 rows in set (0.00 sec)

不要忘了运用学到的东西。

如果不用union 用连接查询做。。。。

学习了sql的操作语言。

下面学习数据定义语言

**28课 创建table**

建表的过程就是声明表头的过程，也就是列就是一个声明列的过程。

列声明什么类型，给什么属性，即放得下内容，又不浪费空间

列的类型与其属性

建表  
：  
Create table 表名（  
列1 列类型 列属性 默认值， 可以只写到列类型  
列2 列类型 列属性 默认值

）；

29课整型列

大的分

数值型——-整型，浮点，定点

字符串—-char varchar ,text

日期时间类型  
2012- 12-13  
14:26:23

整型  
Int 占据磁盘4个字节

Bingint 8个字节  
Mediumint 3个字节

Smallint 2个字节

Tinyint 1个字节 -128 ~127  
都有两种可能全正和有正有负  
一个字节8位2进制

**30课 整型列的可选参数**

Unsigned 无符号，就是使他为0-255那种全是正的也就是无符号的

Alter table 表名 add unum tinyint unsigned  
修改表，增加 列 就是增加个unnum的列，且类型类tinyint ，属性为unsigned就是无符号

Zerofill 适合用于学号，编码等，固定宽度的，可以用0进行填充长度一样的

填充到多宽，M 就是宽度的意思

Alert table t2 sn tinyint(5) zerofill

那个括号中的5就是长度或者说宽度。

用0进行填充位。

他不会等于负数，不用写他也是unsigned的。默认是无符号的

那个M也就是类型括号里的是要和zerofill一起结合用的。

就是用0填充到多少。

**31课 浮点列与定点列**

Float、double、decimal

Float（M,D）M是精度，总位数，D是标度小数点后面的位数

Double同float，就是很大。

Decimal是定点型，也有M,D

主要是decimal是定点型，比之前两个更精确

**32课 字符型列**

Char varchar

他们两个的区别

Char(10) 无论给多少都占10个字符，定长的

Varchar（10）  
最多能放10个字符，只放一个字符，他占多一个字符加1到2个字节，会用部分空间来存储  
占了多少，结尾是多少~~

不是很大的话，空间又很大的话，用charf会更快一点。

Char 位不够的话用个空格来补齐

Concat 拼接字符串

如

Select concat (‘asdasd’,n1,’asd’) from t4

在char 的后面加空格在连接别的，出来后就把空格弄没了，因为是用空格填充连接或，区分不了，然后就省略了。但是varchar不会，因为有一部分是用来记录数据的

Text 可以存很多。特大文本。

Blob 是二进制类型，用来存图像，音频等二进制信息。  
Enum 枚举类型 是定义好值就在某几个枚举范围内。  
像性别 ，只可能是男或者女

Create tables t7 (  
Xingbie enum(‘男’,女’)  
)；

只能是男或女。

SET 与枚举的区别就是可以选几个，不止一个枚举只能是男或女

日期时间 year 可以存 1901-2155年 1970  
Date 1000/01/01 —-9999/12/12 1998-12-31  
Time -838:59:59 —–9999:12:31 23:59:59  
Datetime 1000、01、01 00:00:00 —-9999:12:31 23:59:59  
（就是上面两个的结合）

写入数据的时候就是直接写字符串。

年是直接写年

还有timestmp 类型

不对他进行输入的话会自动的输入当前时间

不建议用，效率并不高。

**34课 列的默认值**

1、NULL 查询不便，  
2、NULL的效率不高  
所以避免不默认为NULL  
声明列为not NULL default 默认值  
声明不默认为NULL,而是默认default

要写创建表的时候在创建每一列的后面加  
如：

Id int not NULL default 0,

**35课 主键与自增**

主键 能够区分每一行的列

Auto\_increment

声明主键

Create table t11 (  
Id int primary key,  
………  
)；

或者在写完列后再写谁是主键  
……….  
Primary key (id)  
);

Auto\_increment 自增，只能有一列是自增列  
此列必须加索引  
Key（那个列）  
或者index  
Oracle 没有自增，

**36课 综合建表案例**

一个表如果全是定长的话可以提高效率

定长与变长分离，常用与不常用列分离

**37课 列的删除增加与修改**  
改名 ： rename tablle 现表名 to 改的表名  
新增一个列

Alertr table 表名 修改表  
Add hight tinyint unsigned not null default 0; 增加列  
默认在表的最后。  
放在指定位置  
Alertr table 表名 修改表  
Add hight tinyint unsigned after 另一个列名 ; 增加列到另一个列名后

Alertr table 表名 修改表  
Drop column 列名；

修改

Alert table 表名 change 现列名 更改列名 类型 属性

就像声明一个新列，然后替代他。

Alert table 表名 modify 列名 类型 属性

Desc 表名，查看这个表的各个列的类型，属性

**38课 视图**

知道视图的概念，  
会建视图

有些东西查询出来经常用，不如把他存起来

创建视图

Create view 视图名 as 查询的语句

下次直接select \* from 视图名  
就是那个查询的效果了。

View 又被称为虚拟表，view是一个sql语句的查询结果，

有什么用？  
1、权限控制，某几个列允许用户查询，其他的不允许，  
通过视图，开放其中几列，  
2、简化复杂的查询，把一个表提出来可以在操作。

3、创建完视图后，show tables 也会出来视图，根本区分不了  
那能不能更新和删除~

改了表，视图也会变，直接改视图的话，如果是数据同物理表的的话，没有进行运算  
就可以改，如果是进行运算的，如，取平均值，直接修改了，那么改了，物理表  
的数据不知道怎么变。

**39课 视图的algorithm**

视图放哪了

View 把建这个查询语句与再次对view的查询语句拼接起来，直接去查物理表  
这种视图存储的是一个语句。这种是土地额算法叫 merge(合并)

也有可能视图的本身语句就比较复杂，很难结合，mysql就先执行创建视图的语句，存储为一个临时表，然后在对临时表进行查询temptable

可以自己设置 运用的算法  
Create algorithm = merge view as sql查询语句 就是拼接的。

不写则是数据库自己判断。

**40课 表/视图的管理语句**

删除表 drop table 表名  
删除视图 drop view 视图名

查看建表过程 show create table;

查看建视图过程 show create view;

查看所有表的详细信息 show table status  
后面加个\\G就会竖着显示了 方便看一点。

不看那么多，单独查某表

Show table status where name =’表名’；

去查看后最后面有个coment ：view的就是视图

Truncate 清空一个表的数据

Delete from 表名；

用delete去删除的话，如果是自增的话，删除一个，再次增加一个后是会增加的，不是原来的id了。

把这个表删除增加就又是1

Delete 是删除数据，再次增加，自增还会增加  
Truncate则不会，相当于删除表再重建。

**41课 存储引擎的概念**

存储在磁盘的MYI是索引文件。  
Frm是说明  
MYD是数据

引擎不同，有人数据信息重要存放的位置不同。

Engine 引擎不同。  
5.5 以默认innoDB

常用的引擎 Myisam InnoDb Memory

不持久的用 Memory

Myisam InnoDb 区别

Myisam 速度快 不安全。

InnoDb 安全 有日志。数据不易丢失。

建表的时候，在后面写 Engine = 使用的引擎。

Myisam  
可以直接把目录拷过去

InnoDb 就不行。

**42课 字符集与乱码**

字符集，校对集，乱码

文本本来的字符集与展示的字符集不一致到字段的。

就选utf-8

如果存储的是utf-8，但是客户端是GBK  
就可以在中间做个转换。

Set names gbk;

就是把那3个都设为gbk 输入，显示 中间

在页面中设置是utf-8  
但是在这个客户端中设置gbk；

校对集：  
按什么的规则排序~

**43课 索引概念**

快速查到，MVI索引文件  
以某种数据结构存储，比如树  
高效 ，像书的一个目录。  
快速定位行，数据的位置  
更改数据的同时要改索引。  
索引是有代价的，降低了增删改的速度。  
有可能会产生索引文件比数据文件还大。  
一般在重复度低的列上加索引效果好。  
还有查询频繁时用好。

普通索引 key  
唯一索引 uniquwkey  
主键索引 primarykey  
全文索引 fulltest

普通索引 key

在建完列后 key 索引的名字 （以哪个列做索引）

一般索引的名字和 以哪个列做索引 是一样的。

唯一索引 uniquwkey

在建完列后 uniquwkeykey 索引的名字 （以哪个列做索引）

唯一索引不允许重复

主键索引 primarykey 与之前一样

全文索引 fulltest  
，在中文环境下无效，要分词加索引，一般用第三方解决方案  
如 sphinx

索引长度，建索引时，乐意只索引列的前一部分的内容  
比如，前10个字符。

在建完列后 uniquwkeykey 索引的名字 （以哪个列做索引（长度））  
就像邮箱~~

还可以建多列索引  
就是把两列或多列看成一个整体建索引

索引的名字 （以哪个列做索引，另一个列）

看看索引 show index from 表名 \\G

查询时要都写，才能发挥索引的作用。

**44课 索引操作**

看看索引 show index from 表名 \\G

删除索引  
Alert table 表名 drop index 索引名  
Drop index 索引名 from 也可以删除

添加还用  
Alert table 表名 add index 索引名( );

添加主键索引不用起索引名

**45课常用函数**

一、数学函数  
abs(x) 返回x的绝对值  
bin(x) 返回x的二进制（oct返回八进制，hex返回十六进制）  
ceiling(x) 返回大于x的最小整数值  
exp(x) 返回值e（自然对数的底）的x次方  
floor(x) 返回小于x的最大整数值  
greatest(x1,x2,…,xn)返回集合中最大的值  
least(x1,x2,…,xn) 返回集合中最小的值  
ln(x) 返回x的自然对数  
log(x,y)返回x的以y为底的对数  
mod(x,y) 返回x/y的模（余数）  
pi()返回pi的值（圆周率）  
rand()返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。  
round(x,y)返回参数x的四舍五入的有y位小数的值  
sign(x) 返回代表数字x的符号的值  
sqrt(x) 返回一个数的平方根  
truncate(x,y) 返回数字x截短为y位小数的结果

二、聚合函数(常用于group by从句的select查询中)  
avg(col)返回指定列的平均值  
count(col)返回指定列中非null值的个数  
min(col)返回指定列的最小值  
max(col)返回指定列的最大值  
sum(col)返回指定列的所有值之和  
group\_concat(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数  
ascii(char)返回字符的ascii码值  
bit\_length(str)返回字符串的比特长度  
concat(s1,s2…,sn)将s1,s2…,sn连接成字符串  
concat\_ws(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔  
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果  
find\_in\_set(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置  
lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果  
left(str,x)返回字符串str中最左边的x个字符  
length(s)返回字符串str中的字符数  
ltrim(str) 从字符串str中切掉开头的空格  
position(substr,str) 返回子串substr在字符串str中第一次出现的位置  
quote(str) 用反斜杠转义str中的单引号  
repeat(str,srchstr,rplcstr)返回字符串str重复x次的结果  
reverse(str) 返回颠倒字符串str的结果  
right(str,x) 返回字符串str中最右边的x个字符  
rtrim(str) 返回字符串str尾部的空格  
strcmp(s1,s2)比较字符串s1和s2  
trim(str)去除字符串首部和尾部的所有空格  
ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数  
curdate()或current\_date() 返回当前的日期  
curtime()或current\_time() 返回当前的时间  
date\_add(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate\_add(current\_date,interval 6 month);  
date\_format(date,fmt) 依照指定的fmt格式格式化日期date值  
date\_sub(date,interval int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate\_sub(current\_date,interval 6 month);  
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)  
dayofmonth(date) 返回date是一个月的第几天(1~31)  
dayofyear(date) 返回date是一年的第几天(1~366)  
dayname(date) 返回date的星期名，如：select dayname(current\_date);  
from\_unixtime(ts,fmt) 根据指定的fmt格式，格式化unix时间戳ts  
hour(time) 返回time的小时值(0~23)  
minute(time) 返回time的分钟值(0~59)  
month(date) 返回date的月份值(1~12)  
monthname(date) 返回date的月份名，如：select monthname(current\_date);  
now() 返回当前的日期和时间  
quarter(date) 返回date在一年中的季度(1~4)，如select quarter(current\_date);  
week(date) 返回日期date为一年中第几周(0~53)  
year(date) 返回日期date的年份(1000~9999)  
一些示例：  
获取当前系统时间：select from\_unixtime(unix\_timestamp());  
select extract(year\_month from current\_date);  
select extract(day\_second from current\_date);  
select extract(hour\_minute from current\_date);  
返回两个日期值之间的差值(月数)：select period\_diff(200302,199802);  
在mysql中计算年龄：  
select date\_format(from\_days(to\_days(now())-to\_days(birthday)),’%y’)+0 as age from employee;  
这样，如果brithday是未来的年月日的话，计算结果为0。  
下面的sql语句计算员工的绝对年龄，即当birthday是未来的日期时，将得到负值。  
select date\_format(now(), ‘%y’) - date\_format(birthday, ‘%y’) -(date\_format(now(), ‘00-%m-%d’)