mysql入门到精通学习笔记

一、入门语句

1.连接服务器：mysql -u用户名 -p密码    建议密码回车后输入，这样输入密码不可见

2.选库：use 库名；

3.查看所有库：show databases;

4.查看所有表：show tables;

5.建库：create database 数据库名 charset 字符集 utf-8；

6.删库：drop database 库名；谨慎使用，谨慎使用

7.改库名：不能改；

8.删除表：drop table 表名；

9.改表名：rename table 旧表名 to 新表名

10.清空表：truncate 表名；

11.查看表中所有列：desc 表名

注意：1、1064语法错误   2、truncate和delect的区别：truncate删表在重建，delect删除数据行

二、增删查改基本语法

1.增

      insert into 表名 （列名） valves (值)；     //插入个别列

      insert into 表名 valvers (值)；      //插入所有列若id为自增则需要一个null占位不推荐这样使用，个别服务器有可能会不支持

      insert into 表名 查询语句；

2.改

      update 表名 set 字段名 = ‘值’ where id = 值 ；   //update必须结合where使用，where后面跟表达式，表达式为真就执行，若不跟where条件则修改的是整列，切记切记

      update 表名 set 字段名 = ‘值’，字段名 = ‘值’ where id = 1；   //修改多个值用逗号隔开

3.删

      delect from 表名 where id = 1     // 不跟where条件全表删除，1在mysql中代表真，还可以以where 1这样表达式也成立

4.查

      select 字段名 from 表名 where id = 1  //可select * from 表名  如果不是查所有列不推荐使用这样耗费了内存增加了服务器压力

三、列类型

1.整数型

      类型                    字节                       最小值                                           最大值

      tinyint                  1                            -128或0                                         127或255

      smallint               2                            -32768或0                                      32767或65535

      mediumint           3                           -8388608或0                                   8388607或16777215

      int                        4                           -2147483648或0                             2147483647或4294967295

      bigint                   8                           -9223372036854775808或0           9223372036854775807或18446744073709551615

注意：一般而言设某类型为n字节，一个字节占8位，可计算出该字节的最大值和最小值

          正：0 到 2^8n-1

          负：-2^(8n-1)  到  +2^(8n-1)-1

1.1.整数系列声明时参数

      （m）：（m）必须和zerofill配合使用才有意义；列：alter table 表名 add 列名 int<1> not null default 0;    //<1>是无效的

        unsigned：在列类型后加unsigned表示无符号（存储正数）；列：alter table 表名 add 列名 int unsigned not null default 0;

        zerofill：zero是0，fill是填充，zerofill代表0填充，即不够位数用0填充；列：alter table 表名 add 列名 int<5>  zerofill                                  not null default 0;  数据插入1 结果则是00001  即不够位数0填充,<m>m为几则补几位，带0填充的默认为unsigned

       注意：整数类型，不加特殊符号默认为有符号类型（负数）

2.浮点数（小数）

      浮点数在计算机内表示是很复杂的

      Float(M,D)：M叫精度，代表总位数；D是标度，代表小数位（即小数右边的数）列（float(5,2)存储小数则为000.00）

      Decimal(M,D)：比float精确，列float存入1.23，取出为1.25，Decimal存入1.23取出为1.23

      注：浮点数占用空间float能存10^38到-10^38,其中M若<=24则占4字节，否则占8字节

3.字符串类型（常用4种）

      3.1：char(N)：不够N个长度用空格在尾部补齐N个长度，取出时将右侧填补空格去掉

            查询原理：数据行数*长度；列：查询第5行数据每行长度为2指针直接啪啪啪移动10个长度在取2个长度

            缺点：浪费空间

            优点：查询快，空间的利用率可达100%

            注意：char的右侧将不能存储空格,查存储长度在0---255之间

     :3.2varchar(N)：存储不用补齐，但存储前会计算字符串长度，在列内容前用1--2个字符长度来存储该字符串长度

            查询原理：每行数据前面会有1--2个字符串存储该字符长度，当指针指向该行时会选读前面的字符串长度来确定往后取几个字符

            缺点：存储不能达到100%

            注意：char(N）和varchar(N)限制的是字符不是字节，varchar能存储近1万字左右，char为定长存储，varchar为变长存储，存储范围在0--65536之间

     3.3 Text：文本域，声明text时不必给默认值，存储在2-3万汉字之间

     3.4 blob：存储图像二进制

4.时间类型

      date：yyyy-mm-dd，占3字节，存储范围1000-01-01到9999-12-31

      time：hh:ii:ss，占3字节，存储范围负838-59-59到838-59-59

      datetime：yyyy-mm-dd hh:ii:ss，占8字节，存储范围在1000-01-01 00:00:00 到9999-12-31 23:59:59

      timestamp：占4字节，存储为时间搓，若插入时不给默认值会取当前时间作为它的值（很少用）

      year：占1字节，存储范围在1000-9999，注意：year可简写为2位数，如year(2)表示为95年等

四、建表语句

      1.新增语法

            create table 表名（

                    列名  列类型   列参数 

                    id int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT, 

            ） ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;  

      详解：

      ENGINE=InnoDB使用innodb存储引擎

      DEFAULT CHARSET=utf8 数据库默认编码为utf-8

      AUTO_INCREMENT=1 自增键的起始序号为1

      扩展：

      1.InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一，InnoDB支持事务处理和外键等高级功能。          2.AUTO_INCREMENT会在新记录插入表中时生成一个唯一的数字。希望在每次插入新记录时，自动地创建主键字段的值，可以在表中创建一个 auto-increment 字段。

      2.修改

            2.1：增加列：alter  table  表名  add  列名  列类型  列参数；    //默认加在列尾

                                   alter  table  表名  add  列名  列类型  列参数  after 某列；       //指定加在某列后

                                   alter  table  表名  add  列名  列类型  列参数  first；       //first加在表最前列

           2.2：删除：alter table 表名  drop  列名；

           2.3：修改：alter  table  表名  modify  列名  新列类型  新列参数；     //modify 只能更改列属性 只需要写一次列名, 比change 省事点

                              alter  table  表名  change  旧列名  新列名  新类型  新参数；       //change 可以更改列名 和 列类型 (每次都要把新列名和旧列名写上, 即使两个列名没有更改,只是改了类型)

五、五种子句

1、运算符

比较运算符：不等于 ！=，<> 

     等于： =

     大于 ：>

     小于： <

     包含： in(4,11)   //取等于4和11的值

     区间：between 100 and 500   //包括边界值

     不包含：no in (4,11)    //and实现  字段名 ！= 4  and  字段名 ！= 11

     或：字段名 >= 100  and  字段名 <= 300  or  字段 <= 500  and  >=1000   //(true OR false)返回true,true AND false返回false

     例：取第三个id下价格小于100或大于300且点击量大于5

            id=3 and (字段 <100 or 字段 >300) and 字段>5   //此处的括号包裹是因为or的优先级低于and

2、模糊查询：以like  %起通配作用，%通配任意字符，_单字符匹配，一个_代表一个字符

3、统计函数：

count()：统计行数

例：select count(*) from 表名, 查询的就是绝对的行数,哪怕某一行所有字段全为NULL,也计算在内.

而select couht(列名) from 表名, 查询的是该列不为null的所有行的行数.

max()：求最大

min()：求最小

sum()：求和

avg()：求平均

4、5种子句

4.1、Where：对表中列进行筛选

4.2、Group：分组，配合统计函数使用功能强大

4.3、having：对查询出的结果集进行筛选

4.4、Order by：为终结果集出来后,进行排序.

排序的语法: Order by 结果集中的列名 desc/asc

例:order by shop_price desc ,按价格降序排列

Order by add_time asc ,按发布时间升序排列.

多字段排序

Order by 列1 desc/asc , 列2 desc/asc , 列3 desc,asc

4.5、Limit 在语句的最后, 起到限制条目的作用 Limit [offset,] N ，Offset: 偏移量,----跳过几行 ，N: 取出条目

Offset,如果不写,则相当于 limit 0,N

六、3种子查询

1、Where型子查询: 指把内层查询的结果作为外层查询的比较条件，如果 where 列=(内层sql),则内层sql返回

的必是单行单列,单个值 ，如果 where 列 in (内层sql), 则内层sql只返回单列,可以多行.

例：select id from test where id = (select max(id) from test)  子句只能返回单行

例：select id from test where id in (select id from test)   子句单行多行都可以

2、From 型子查询: 把内层的查询结果当成临时表,供外层sql再次查询

例：select sb1, sb2, sb3 from (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1;

3、Exists子查询 : 把外层的查询结果,拿到内层,看内层的查询是否成立.

例：select id,name from test1 where exists (select * from test2 where test2.id = test1.id)

七、链接查询

概念：把表看为集合，行看为元素，

例：表1数据 a,b,c

表二数据1，2

select * from 表1，表2；

得到数据为二表相乘后的结果可用where限制要取出的行比如表1.id=表二.id

a 1 

b 1

c 1

a 2

b, 2

c 2

以上案列是耗内存操作，不能使用，且索引失效

1、左连接:

语法：A left join B

假设A表在左,不动,B表在A表的右边滑动. A表与B表通过一个关系来筛选B表的行. 语法:

A left join B on 条件 条件为真,则B表对应的行,取出

A left join B on 条件

这一块,形成的也是一个结果集,可以看成一张表 设为C 既如此,可以对C表作查询,自然where,group ,having ,order by ,limit 照常使用

2、左右连接是可以互换的

语法：B right join A

A left join B, 就等价于 B right join A

注意:既然左右连接可以互换，尽量用左连接，出于移植时兼容性方面的考虑

3、内连接的特点

语法：A inner join B

和 left join /right join的关系

答: 内连接是左右连接的交集

注意：mysql不支持外连接！！!

4、union，合并结果集,完成外连接的效果

八、常用结果集

九、视图

视图的定义:：视图是由查询结果形成的一张虚拟表

视图的创建语法:：Create view 视图名 as select 语句;

视图的删除语法：Drop view 视图名

视图的algorithm：

Algorithm = merge/ temptable/ undefined Merge: 当引用视图时,引用视图的语句与定义视图的语句合并.

Temptable:当引用视图时,根据视图的创建语句建立一个临时表

Undefined:未定义,自动,让系统帮你选.

Merge,意味着视图只是一个规则,语句规则, 当查询视图时,

把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析.

形成一条select 语句.

例: 创建视图的语句:

mysql> create view g2 as select goods_id,cat_id,goods_name,shop_price from goods where

shop_price>2000

查询视图的语句:

select * from g2 where shop_price < 3000;

最终执行的语句:

select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 2000 and

shop_price < 3000

而temptable是根据创建语句瞬间创建一张临时表,

然后查询视图的语句从该临时表查数据.

create algorethm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price from

goods where shop_price > 2000

查询视图的语句:

select * from g2 where shop_price < 3000;

最终执行的2句话: 取数据并放在临时表,然后去查临时表.

Select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 2000;

========> temptable

再次查临时表

Select * from temptable where shop_price< 3000

十、乱码