mysql基础一

浅浅的花香味﹌ 2021-12-16 15:34 285阅读 0赞

三大引擎：myisam（cms等）、innodb（商城等）、memory（临时存储在内存中）

# **一、数据类型** #

char：长度固定；最多255个字符；效率高，没有碎片，更新频繁的时候，方便数据文件指针的操作。  
varchar：长度可变、灵活；最多65532（ 65535-1-2）个字符，可变类型，一般保存字母、数字、特殊字符  
tinytext：1字节，范围（-128~127）  
text：65535个字符  
boolean：1或者0  
int：4字节，数字，范围（-2147483648~2147483647）  
datetime：8字节，日期时间，格式：2014-09-18 08:42:30  
bigint：8字节，范围（+-9.22\*10的18次方）  
smallint：2字节，范围（-32768~32767）  
mediumint：3字节，范围（-8388608~8388607）

# **二、CRUD操作**，增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)： #

1.insert:增

“insert into” 增加 ，返回值为数字，没有操作是0。例如，insert into user(username,pwd)values("3131","12345678");在数据表末尾加入一行数据

2.DELETE:删 / truncate table '表名';

“delete from”删除，不加条件删除整个数据表。例如，DELETE FROM `user` WHERE id=25;删除id为25的数据
    　　“in”多个删除，返回值为数字，没有操作是0。例如，DELETE * FROM `user` WHERE id in(3,5,7);删除id中为3、5、7的3个的数据

3.update:改，修改，必须加条件，返回值为数字，不能是0

update 表 set 字段名=新值,字段名=新值 where 。//例如，update user set username = '张三' where id = 8;将id=8处修改为..

4.SELECT:查（having-聚合后进行过滤，where-聚合前进行过滤（范围更小））

“=“等于                                                
       “<>”不等于//例如，SELECT * FROM `user` WHERE username<>"tom"; 不等于tom的                                                
       “<”小于                                                
       “>”大于//例如，SELECT * FROM `user` WHERE id>"5";id大于5的                                                
       “<=”小于等于                                                
       “>=”大于等于                                                
       “between..and..”之间                                                
       //例如，SELECT * FROM `user` WHERE id between 5 and 10;id为5-10之间的                                                
       “like ‘%..%’”模糊查询                                                
       //例如，SELECT * FROM `user` WHERE username like "%t%";“%”替代符，t之前或之后，有无字符都符合                                                
       “in(..)”指定数据                                                
       //例如，SELECT * FROM `user` WHERE id in(3,5,7);id中为3、5、7的3个的                                                
       “..and..”多个条件                                                
       //例如，SELECT * FROM `user` WHERE username="tom" and pwd="12";查询username为tom和pwd为12的，并的关系                                                
       “or()”或者..                                                
       //例如，SELECT * FROM `user` WHERE username="95" or pwd="12";查询username为95或pwd为12的，或的关系                                                
       “order by..descend（降）、ascend（升）”排序                                                
       //SELECT * FROM `user` order by id desc;                                                
       “limit”对结果进行限制输出                                                
       //例如，SELECT * FROM `user` order by id desc limit 0,5;“0,5”id降序输出id当前页的第一个数据开始的5个数据                                                
       “is null”为null                                    
        “is not null”不为null

is null在查询中的简单使用：

![1614235-20190614155038829-1044743343.png][]

“group by”用于结合合计（sum）等函数，根据一个或多个列对结果集进行分组。//select sum(kucun_num) from h_goods_kucun where hid = 60 and ks_id = 43 GROUP BY g_code;查询每个g_code的kucun_num合计后的总数。

与group by结合统计的函数：

Count 统计数量，参数是要统计的字段名（可选）                 
    Max 获取最大值，参数是要统计的字段名（必须）                 
    Min 获取最小值，参数是要统计的字段名（必须）                 
    Avg 获取平均值，参数是要统计的字段名（必须）                 
    Sum 获取总分，参数是要统计的字段名（必须）

![1614235-20190614155506847-691404568.png][]

“distinct”将表中的记录去掉重复后显示出来                                        
       //例如，select distinct ks_id from h_check;将h_check表的字段ks_id中去除重复数据后，显示出来                                        
         “聚合”进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人                                        
       //例如，select ks_id,count(0) from h_check group by ks_id with rollup;查出ks_id的每个数据的条数                                        
       //例如，select ks_id,count(1) from h_check group by ks_id having count(1)>1;统计大于1的数据

![1614235-20190614155944207-1666369720.png][]

5.子查询：关键字主要包括 in、not in、=、!=、exists、not exists 等

“in”                                                    
        //例如，select * from emp where deptno in(select deptno from dept);                                                    
         “=”子查询记录数唯一时                                                    
        //例如，select * from emp where deptno = (select deptno from dept);                                                    
        //例如，select * from emp where deptno = (select deptno from dept limit 1);                                                    
    子查询转为表连接：                                                    
    select * from emp where deptno in(select deptno from dept); ===》select emp.* from emp ,dept where emp.deptno=dept.deptno;

6.多表查询:

user as u：为user表取别名u;例如：select u.username,v.money from user as u,vip as v                                                                
    运用"Id_P"相等，将两个表Persons和Orders联系起来，获取数据。SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P

![1614235-20190614160324257-139718188.png][]

使用**Join**关键词来从两个表中获取数据：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo 
    FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName;

INNER JOIN:内连接

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

连接：                                
    交叉连接：又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。                                
    内连接：是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。                                
    外连接：结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。                                
    左外连接：也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以 NULL 来填充。                                
    右外连接：也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。                                
    全外连接：左连接和右连接可以互换，MySQL 目前还不支持全外连接。

**Union**使用:进行一次distinct，会列出所有不重复的字符。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。(并且union只会列出不同的字符)

**Union ALL**使用：结果集直接合并在一起。

![1614235-20190614160922954-466718218.png][]

单个字段查询：

SELECT username FROM user。查询user表中的username

多个字段查询：

SELECT username,pwd FROM `user`。查询user表中的username、pwd

所有字段查询(\*)：

SELECT * FROM `user`。“*”通配符，代表所有字段，返回一张临时的表（结果集）

条件查询(where)：这里需要**注意**与having的区别！where是在查询的时候进行结果过滤，having是对查询后的结果进行过滤，**切记！切记！切记！！！**

SELECT * FROM `user` WHERE username="tom"。查询user表中的所有username为tom的

# **三、数据库结构操作** #

创建数据库：

1.create database member;                                
      //简单创建member数据库                                
    2.create database member default character set utf8 collate utf8_general_ci;                                
      //default character set utf8，默认utf8字符集；collate utf8_general_ci，在utf8中默认大小写不敏感                                
    3.create database if not wxists member default character set utf8 collate utf8_general_ci;                                
      //如果数据库不存在，就创建数据库并设置字符集

删除数据库：

drop database member;//删除member数据库

创建数据表：

1.create table member.javascript(id int);                                
      //在数据库member中创建数据表javascript，添加单个字段                                
    2.create table if not exists member.javascript(id int);                                
      //如果不存在数据表，就创建javascript，并添加字段id                                
    3.create table vip(id int not null auto_increment,money int,primary key(id));                                
      //创建vip表，并设置id和money属性                                
    3.create table if not exists member.javascript(id int auto_increment,username varchar(130),pwd varchar(32),
    email varchar(132),regTime datetime,primary key(id),unique key（username）,                                
    key (pwd),fulltext key (email));
      //添加多个字段，  primary key(id)把id设为主键，auto_increment自动增加

删除数据表：

"drop table member.javascript;
      //在数据库member中删除数据表javascript"

在数据表存在时，添加字段：

alter table javascript add address varchar(130);

在数据表存在时，修改字段：

alter table javascript change address newadd int(130);

在数据表存在时，删除字段：

alter table javascript drop newadd;

修改字段为不可重复：

ALTER TABLE dbname.table ADD UNIQUE (fieldname);

查看数据库表结构、列及其注释：

select * from information_schema.columns where table_schema = 'db'  #表所在数据库                                
    and table_name = 'tablename' ; #你要查的表

查看数据库结构表及其注释：

select table_name,table_comment from information_schema.tables                         
     where table_schema = 'db' and table_name ='tablename';

查看数据库中所有数据表名：

show tables;

查看数据库名：

select database();

## **索引操作：** ##

创建索引(可以非常快速定位我们需要找到的信息。)：

主键索引：alter table 表名 add primary key [索引名称（可填可不填）] (id);                                
    唯一索引：alter table 表名 add unique key [索引名称（可填可不填）]（id）;                                
    普通索引：alter table 表名 add key [索引名称（可填可不填）] (id);                                
    全文索引：alter table 表名 add fulltext key [索引名称（可填可不填）] (id);

全文索引（目前只有5.6以上版本innodb支持，myisam则全部支持）：

创建：alter table 表名 add fulltext key [索引名称（可填可不填）] (id);                                
    使用：select * from 表名 where match(字段名) against('内容');                                
    注意：    
    　　 ①普通的模糊查找是不会用到全文索引！！                            
        ②目前只有5.6以上版本innodb支持，myisam则全部支持                            
        ③字段类型varchar、char、text支持                            
        ④现阶段只支持英文，中文须使用第三方软件sphinx                            
        ⑤mysql的全文索引会自作聪明，对关键字的收录有自己的考虑，例如：生活常用单词、频繁使用的单词

复合全文索引：

创建：alter table 表名 add fulltext key [索引名称（可填可不填）] (字段A，字段B);                                
    使用：select * from 表名 where match(字段A，字段B) against('内容A,内容B');

创建复合索引：

alter table 表名 add key [索引名称（可填可不填] (字段A,字段B,....);

创建前缀索引：

alter table 表名 add key (字段名(前n位位数));                                
    select count(distinct substring(字段名,开始位置,长度n)) from emp;//获取前几位适合做前缀索引

创建自增特性：

alter table 表名 modify id int(11)unsigned auto_increment comment '主键';

删除非主键索引和主键索引：

alter table 表名 drop key 索引名称;//非主键索引删除                                
    alter table 表名 modify id int(11)unsigned not null comment '主键';//删除主键索引前须去掉自增特性                                
    alter table 表名 drop primary key;//主键索引删除

# **四、函数** #

rowCount();数据总记录数  
current\_time() 日期时间函数返回当前本地时间。  
date\_format(datetime, format)用于格式化日期，如：select date\_format(now(),'%Y%m%d');  
group\_concat() 会计算哪些行属于同一组，将属于同一组的列显示出来。

UNIX\_TIMESTAMP() mysql中表示当前时间戳  
FROM\_UNIXTIME() mysql中把时间戳转化成格式化时间

字符串函数：  
concat(s1,s2,s3,...) --把传入的参数连接成为一个字符串。

select concat('aaa','bbb','ccc'),concat('aaa',null);任何字符串与 NULL 进行连接的结果都将是 NULL

INSERT(str ,x,y,instr) --将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr。

select insert('beijing2008you',12,3,'me')

LOWER(str)和 UPPER(str) --把字符串转换成小写或大写

select lower('BEIJING');select upper('beijing');

LEFT(str,x)和 RIGHT(str,x) --分别返回字符串最左边的 x 个字符和最右边的 x 个字符。

select left('qwertasd',1);select right('qwertasd',1);如果第二个参数是 NULL，那么将不返回任何字符串。

LPAD(str,n ,pad)和 RPAD(str,n ,pad) --用字符串 pad 对 str 最左边和最右边进行填充,直到长度为 n 个字符长度。

select lpad('2008',20,'beijing');select rpad('2008',20,'beijing')

LTRIM(str)和 RTRIM(str) --去掉字符串 str 左侧和右侧空格。

select ltrim(' |beijing');select rtrim('beijing| ');

REPEAT(str,x) --返回 str 重复 x 次的结果。

select repeat('mysql',3);

REPLACE(str,a,b) --用字符串 b 替换字符串 str 中所有出现的字符串 a。

select replace('beijing_2012','_2012','2008');

STRCMP(s1,s2) --比较字符串 s1 和 s2 的 ASCII 码值的大小。如果 s1 比 s2 小，那么返回-1；如果 s1 与 s2 相等，那么返回 0；如果 s1 比 s2 大，那么返回 1。

select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');

TRIM(str) --去掉目标字符串的开头和结尾的空格。

select trim(' $ beijing2008 $ ');

SUBSTRING(str,x,y) --返回从字符串 str 中的第 x 位置起 y 个字符长度的字串。

select substring('beijing2008',8,4),substring('beijing2008',1,7);

数值函数：  
ABS(x) --返回 x 的绝对值。

select abs(-0.8);

CEIL(x) --返回大于 x 的最大整数。

select ceil(-0.4);select ceil(0.4);

FLOOR(x) --返回小于 x 的最大整数，和 CEIL 的用法刚好相反。

select floor(0.4);

MOD(x，y) --返回 x/y 的模。和 x%y 的结果相同，模数和被模数任何一个为 NULL 结果都为 NULL。

select MOD(15,10),MOD(1,11),MOD(NULL,10);

RAND() --返回 0 到 1 内的随机值。每次执行结果都不一样。

select RAND(),RAND();    
    select ceil(100*rand()),ceil(100*rand());

"ROUND(x,y) --返回参数 x 的四舍五入的有 y 位小数的值。如果是整数，将会保留 y 位数量的 0；如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整。  
适合于将所有数字保留同样小数位的情况。"

select ROUND(1.1),ROUND(1.136,2),ROUND(1,2);

TRUNCATE(x,y) --返回数字 x 截断为 y 位小数的结果。

select ROUND(1.235,2),TRUNCATE(1.235,2);

日期和时间函数：

CURDATE() --返回当前日期，只包含年月日。

select CURDATE();

CURTIME() --返回当前时间，只包含时分秒。

select CURTIME();

NOW() --返回当前的日期和时间，年月日时分秒全都包含。

select NOW();

UNIX\_TIMESTAMP(date) --返回日期 date 的 UNIX 时间戳。

select UNIX_TIMESTAMP(now());

FROM\_UNIXTIME（unixtime） --返回UNIXTIME时间戳的日期值。

select FROM_UNIXTIME(1184134516);

WEEK(DATE)和 YEAR(DATE) --前者返回所给的日期是一年中的第几周，后者返回所  
给的日期是哪一年。

select WEEK(now()),YEAR(now());

HOUR(time)和 MINUTE(time) --前者返回所给时间的小时，后者返回所给时间的分钟。

select HOUR(CURTIME()),MINUTE(CURTIME());

MONTHNAME(date) --返回 date 的英文月份名称。

select MONTHNAME(now());

DATE\_FORMAT(date,fmt) --按字符串 fmt 格式化日期 date 值。

select DATE_FORMAT(now(),'%M,%D,%Y');

DATE\_ADD(date,INTERVAL expr type) --返回与所给日期 date 相差 INTERVAL 时间段的日期。

select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;"

DATEDIFF（date1，date2） --用来计算两个日期之间相差的天数。

select DATEDIFF('2008-08-08',now());

流程函数：

IF(value,t f) --如果 value 是真，返回 t；否则返回 f。

select if(salary>2000,'high','low') from salary;

IFNULL(value1,value2) --如果 value1 不为空返回 value1，否则返回 value2。

select ifnull(salary,0) from salary;

CASE WHEN \[value1\] THEN\[result1\]…ELSE\[default\]END --如果 value1 是真，返回 result1，否则返回 default。

select case when salary<=2000 then 'low' else 'high' end from salary;

CASE \[expr\] WHEN \[value1\] THEN\[result1\]…ELSE\[default\]END --如果 expr 等于 value1，返回 result1，否则返回 default。

select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from
    salary;

其他函数：

DATABASE() --返回当前数据库名。  
VERSION() --返回当前数据库版本。  
USER() --返回当前登录用户名。  
INET\_ATON(IP) --返回 IP 地址的网络字节序表示。  
INET\_NTOA(num) --返回网络字节序代表的 IP 地址。  
PASSWORD(str) --返回字符串 str 的加密版本，一个 41 位长的字符串。（只可用于密码）  
MD5(str) --返回字符串 str 的 MD5 值，常用来对应用中的数据进行加密。

# ** 五、位运算**：(将十进制数转化为二进制，再一位一位的比较) #

select 6&1  ===》0110&0001 = 0000 = 0;    
    select 6&2  ===》0110&0010 = 0010 = 1;    
    select 8|4  ===》1000|0100 = 1100 = 12;    
    select 12|4 ===》1100|0100 = 1100 = 12;    
    select 12^4 ===》1100^100 = 1000 = 8;    
    select 2^3 ===》10^11 = 01 = 1;    
    select 100>>3 ===>1100100右移3位，左边补零 = 1100 = 12;    
    select 100<<3 ===>1100100左移3位，右边补零 = 1100100000 = 800;

六、PDO应用

1.停止

exit("hello");//输出hello，下面内容停止执行。类似于return fasle

2.简洁化错误信息

error_reporting(0);//排除所有错误    
    exit ($e->getMessage());抛出错误信息，并停止执行下面内容    
    (PDOException $e){
         //catch,捕获异常;$e=new PDOException();

![1614235-20190614170549588-981722108.png][]

3.变量的预处理：执行两次，先执行上面没有参数的函数但不改变数据库，当有数据时，再传入改变数据库。更加灵活，减少服务器负担

:username方式    
    :username形式-sql语句中的预处理变量,':'开头的变量非常灵活    
    now()是第一次预备执行的时间;:regTime是第二次正式执行的时间    
    prepare($sql);预处理    
    execute($array);执行，运行的是int类型    
    
    ?方式：有顺序问题，可直接传值或参数，同样执行两次    
    bindValue();绑定值    bindParam();绑定参数，需先创建变量

![1614235-20190614170428143-1177173015.png][]

![1614235-20190614170458891-2108350463.png][]

![1614235-20190614170524983-2086622815.png][]

4.pdo的执行事务(transaction)：作为一个单元的一组有序的数据库操作。

先禁止‘0’pdo的自动影响数据库 $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);    
    再开启交易$pdo->beginTransaction();    
    执行交易;$pdo->exec($sql);    
    如果出错，回滚（回到交易的开始状态）$pdo->rollBack();    
    手动影响数据库$pdo->commit();

![1614235-20190614170328047-1380241647.png][]

一个事务的小实例，收付款

![1614235-20190614170402966-781457725.png][]

之前都是放在excel文件中，每次用就添加一些，所以有点多。

但是有的时候不用了，岂不是就忘了！？所以还是做好记录比较好。

耐心、耐心、耐心，耐心就好

转载于:https://www.cnblogs.com/two-bees/p/11024383.html

[1614235-20190614155038829-1044743343.png]: /images/20211214/58fe632ce9824af8ae708532b2fd4b4f.png
[1614235-20190614155506847-691404568.png]: /images/20211214/6da19b34d349494db933161608fb5660.png
[1614235-20190614155944207-1666369720.png]: /images/20211214/3c73858721f64dcf87081efe40ac0505.png
[1614235-20190614160324257-139718188.png]: /images/20211214/63434c9ea7d14d159fb16b2d58958035.png
[1614235-20190614160922954-466718218.png]: /images/20211214/2bbfde063a1943f5b7cf865d92351220.png
[1614235-20190614170549588-981722108.png]: /images/20211214/a634ccb91a18427a908261579f680c9e.png
[1614235-20190614170428143-1177173015.png]: /images/20211214/8f4828f367c64c07b834b4ecaf3a4991.png
[1614235-20190614170458891-2108350463.png]: /images/20211214/8a34a283308645bdb9ecf5f6645eae08.png
[1614235-20190614170524983-2086622815.png]: /images/20211214/0a97322765ea4ac686bcfb7f8d36db7a.png
[1614235-20190614170328047-1380241647.png]: /images/20211214/c13dee8dc8cc40e6ac719e1b3b7d4609.png
[1614235-20190614170402966-781457725.png]: /images/20211214/d76620dcffbc44c98192f173b07829d7.png