MySQL数据库的使用（一）

冯奎
2017-12-15 19:28:06
MySQL
php mysql

MySQL是Web世界中使用最广泛的数据库服务器，

在php的世界中可以使用现在的所有的数据库系统，
MySQL和其他大型数据库比如oracle相比，有不足的地方
（规模小，功能有限，但是不影响它受欢迎的程度，因为对于中小型企业来说，完全够用。并且免费，开源）
一、MySQL的体系结构：客户端/服务器端
使用两个程序，一个程序是MySQL服务器程序，另外一个是MySQL的客户端程序。通过客户端发送请求给MySQL的服务器。服务器收到请求后给出回应。给到MySQL请求的客户段

好处：

1、自动处理并发，由服务器提供。当两个用户修改同一条记录的时候，因为客户端请求都经过了服务器端，所有由服务器来安排他们处理的先后顺序
2、不必要非得在数据库的服务器上进行操作，可以在任意地点运行MySQL客户端
二、MySQL数据库的结构
库（数据库），存放信息的
库里会有一个一个的表
表里由一行一行的数据组成
一行一行的数据，由多个字段（列）组成
这里面的每一行数据就是一条记录
三、数据库的特点
1、每个SQL命令都要使用分号来完成
2、如果没有分号结尾，MySQL会认为你还没有输入完成，然后提示->
3、如果失误成现在的'>，那么只需要输入'回车就可以了。因为这个时候MySQL会认为你要输入一个字符串
4、MySQL里面所有的命令都不区分大小写，但是一般来写出SQL关键字和函数的时候，实体大写。用小写写出数据库，数据表，数据字段的名字。这样写完全是为了看着舒服
四、操作数据库的步骤
1、连接MySQL服务器
2、账号和密码
3、选择数据库
4、对数据表进行增删改查操作
5、关闭数据库

进入数据库有两种方式：
1、借助工具和网页版的：工具：Navicat Premium 网页：PhpMyAdmin
2、输入命令

mysql -u root -p

mysql：
       -u 用户名 root               -p 密码（默认为空）
       -h 主机 localhost            -b 关闭蜂鸣器

提示这个就说明进入成功了
如果打开cmd输入 mysql -u root -p进不去怎么办？(要去配置环境变量)
计算机->属性->高级系统设置->环境变量->path;C:\xampp\mysql\bin 关机重启电脑

五、密码操作
1、修改密码
1）在退出情况下：mysqladmin -u root -p password 新密码

C:\\Users\\liu>mysqladmin -u root -p password woaini

2）在mysql中

MariaDB \[(none)\]> set password for 'root'@'localhost' = password('123');
Query OK, 0 rows affected (0.00 sec)

        2、mysql忘记密码
                步骤：
                         1、关闭正在运行的mysql
                         2、打开dos跳转到mysql的bin目录下
                         3、输入mysqld --skip-grant-tables 输入之后，mysql会重新启动
                         4、再打开dos窗口进去到mysql的bin目录下
                         5、输入mysql回车，这个时候可以使用root身份强行进入
                         6、进入mysql这个库
                         7、 update user set password = password('新密码') where user='root';
                         8、刷新权限 flush privileges

六、怎么删除匿名账号
        步骤：
                1、使用root权限登录
                2、进入mysql库中
                3、查看是否有空用户:select user,host from mysql.user
                4、删除空用户delete from mysql.user where user='';
七、退出
         quit
         exit
         \q
八、常见的一些操作
         \c 取消未完成的操作
         \g 可以代替分号
九、数据库的操作

        创建数据库 create database 数据库名
        注意：

1、每创建一个数据库都会在你的mysql的data目录下创建一个名你创建的文件夹
2、数据库是唯一的，再次创建报错

MariaDB \[(none)\]> create database if not exists lamp52;
Query OK, 0 rows affected, 1 warning (0.00 sec)

if not exists 如果不存在就创建，先去检查。这样写不会报错了

                3、删除数据库 drop database 数据库名

MariaDB \[(none)\]> drop database if exists lamp52;
Query OK, 0 rows affected, 1 warning (0.00 sec)

                         if exists 如果存在就删除
                 4、查看所有的数据库 show databases;
                         不是自己的，不要去碰瓷
                 5、选择数据库 use 数据库名
                 6、查看自己选择的数据库 select database();
十、数据表的操作
        先查看数据库中有哪些表：show tables
        创建数据表：

create table 表名(
    id int,
    name char(32),
    sex int,
    age int
);

        注意：
                1、每个字段之间使用逗号进行分隔
                2、最后一个字段不需要使用逗号
                 查看表结构 desc 表名
                 如何查看创建表的语句：show create table 表名
                 \G 让数据更加清晰，这样方便查看
                 删除表：drop table 表名

十一、建表
1、定义数据表、会指定的数据类型整型、浮点型、字符串型、日期、枚举、集合类型
                根据你里面存什么东西，来选择用什么类型
                注意：
                        1、整数控制多大多小是由类型来控制的，不是由后面的int(2)来控制的，它的意思不是只要2位
                        2、设置整型的位数如果超出了设定的这个数值将不会被截断
                        3、你在使用的时候可以省略整型数值的值
        浮点、定点数
                float和double都是浮点数，float是单精度、double是双精度
                单精度和双精度的区别：就是双精度比单精度多存点
                        注意：
                                1、浮点数和定点数都可以使用类型后面加（M,D）方式来表示
                                        M表示该类型一共显示出M位
                                        D为小数点后面几位
                                2、decimal和double的不同点
                                        1）浮点数如果不写精度(M)和标度(D),会原样显示，如果有精度和标度将会自动四舍五入
                                        2）定点数如果不写精度和标度，会按照decimal(10,0)来操作。如果写了精度和标度，也会四舍五入。
                                        3）浮点数会存在误差，对于精度和比较敏感的数据都应该用定点数进行表示
        字符串
                注意：
                        1、char和varchar都和类似，都可以用来存储mysql中较短的字符串。它俩的区别：
                                1）char列的长度为固定长度，创建表的时候声明最大长度，可以为0~255。设置了固定长度，不管里面有多少数据都会占你设置的长度
                                2）varchar值位可变长度，长度为0~65535之间的值。里面有多少数据，就会占多少字符
                                3）char和varchar在超出指定长度都会被截取到指定的字符
                        2、text和blob，二者之间的区别：
                                blob是用来保存二进制数据的
                                text只能保存字符串数据
                                        blob和text在经过大量的删除操作之后有可能会引起性能问题，删除以后数据表中留下"空洞"。可以使用optimize table 表名来回收空间
                日期、时间类型不用，存的时候回存成时间戳、整型
        枚举类型：enum类型
                什么是枚举：比如说你的性别男女插入数据的时候只能插入这两个的其中一个

MariaDB \[lamp52\]> create table t12(
-> test enum('a','b'));
Query OK, 0 rows affected (0.02 sec)

                        注意：
                                1、枚举类型的取值范围需要在创建表的时候显示的指定
                                2、对于1~255个成员的枚举需要占1个字节存储，对于256~65535个成员使用2个字节存储。最多存储65535个成员
                                3、如果超出了枚举指定的值，将会插入一个'空'。枚举类型只能从集合中选取单个的值。而不能一次选取多个值
                                4、enum的值不区分大小写
        集合类型：set类型

MariaDB \[lamp52\]> create table t13(
-> col set('a','b','c','d')
-> );
Query OK, 0 rows affected (0.01 sec)

                注意：
                        1、set类型可以保存0~64个成员，根据成员的不同所占空间的大小也不同
                                1~8 占1个字节
                                9~16 占2个字节
                                17~24 占3个字节
                                25~32 占4个字节
                                33~64 占8个字节
                        2、set和enum除了存储之外，主要区别在于set类型可以一次选取多个成员，而enum只能选取一个
                        3、重复的成员只保留一个
                        4、自动排序
                        5、超出范围的成员将会忽略，插入值的时候，带引号
        约束条件：对字段进行一些约束
                1、null、not null。没有插入指的时候，将默认为null。指定not null必须在插入值的该字段中插入一个值。
                2、默认值，在不插入的时候，给默认值，有值的是，就用我给的值
                        default 值
                整型的约束条件
                        1、zerofill表示0填充，一般和整型后面设置的宽度一起使用，如果数值长度小于指定的长度，那么将会在前面补充相对应的0进行填充
                        2、unsigned 无符号。如果要在字段里保存非负数，或者较大上限的值。那可以使用该约束条件。加上unsigned之后，将从0开始。需要在字段类型后面紧跟着定义
                        3、auto_increment自增，在产生一个唯一的标识或顺序值的时候，可以利用这个约束条件。这个约束条件只能用于整型，值一般从1开始，每行加1，插入一个null到一个auto_increment列是，mysql将插入一个比现在出现过最大值+1的值，一个表中只能有一个auto_increment，并且必须定义为primary key 或unqiue才能使用
                索引：索引是数据库中用来提高搜索性能的。我们通常在做数据库优化的时候，通常先做索引的优化。数据量少的时候没有效果，数据越多效果越明显
                        注意：
                                索引加上之后是自动去使用的，不是我们刻意去使用的
                索引的分类
                        1、常规索引index ，最基本的索引，没有任何限制

MariaDB \[lamp52\]> create table t19(
-> id int unsigned,
-> name char(32),index id(id)----index 索引名(要索引的字段)
-> );
Query OK, 0 rows affected (0.00 sec)

创建表成功之后，加索引
create index 索引名 on 表名(要加索引的字段)

MariaDB \[lamp52\]> create index id on t20(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引：
drop index 索引名 on 表名

MariaDB \[lamp52\]> drop index id on t20;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、唯一索引 unique于普通索引相似，但是索引列里面值必须是唯一的，允许有空值

MariaDB \[lamp52\]> create table t21(
-> id int unsigned,
-> name char(32),unique id(id)
-> );
Query OK, 0 rows affected (0.02 sec)

创建表之后，加索引
create unique index 索引名 on 表名(要加索引的字段)

MariaDB \[lamp52\]> create unique index id on t22(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0                                删除索引

                                        drop index 索引名 on 表名
                3、主键索引 primary key 和唯一索引类似，也是唯一的。一个表只能有一个主键
                                删除索引：
                                        alter table 表名 drop primary key

MariaDB \[lamp52\]> alter table t23 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

                                特殊情况：
                                        注意：如果主键字段上有自增auto_increment，那么需要先将自增去掉，然后再删除
                                        删除自增：

alter table t24 change id id int

MariaDB \[lamp52\]> alter table t24 change id id int;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引：

alter table 表名 drop primary key

十二、字段的操作
语法：alter table 表名动作（可以对表里的字段进行修改、添加、删除、更改表名，更改字段名）
1）更改字段
alter table 表名 change 原字段名新字段名类型约束条件
modify
change 和modify的区别：
change可以修改字段名
modify 不可以修改字段名
2）添加字段
alter table 表名 add 字段名类型约束条件
alter table 表名 add 字段名类型约束条件 first 放在在前面

MariaDB [lamp52]> alter table t1 add sex1 int first;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

alter table 表名 add 字段名类型约束条件 after 放在哪个字段后面

MariaDB [lamp52]> alter table t1 add sex2 int after name;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

3）删除字段
alter table 表名 drop 字段名

alter table t1 drop sex1;

4）修改表名
alter table表名 rename as 新表明

alter table t1 rename as t3;

十三、数据的操作
添加
方法1、插入指定的字段:
insert into 表名(字段名1，字段名2....) values (值1，值2.....)
方法2、插入所有的字段
insert into 表名 values (值1，值2，值3.....);
注意：
所有的字段必须是一一对应的，如果不对应就会出错
方法3、插入多条语句
insert into 表名 (字段名1，字段名2...) values （值1，值2），(值3，值4).....
方法4、插入单条语句
insert into 表 set 字段名1 = 值1，字段名2 = 值2.....;
方法5、插入某些查询的结果
insert into t4(name,sex,age) select name,sex,age from t4;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
查询
1、select 主句
select database(); 查询当前的数据库
select version()；查询版本
select 1+2;
2、from子句--从指定的表里提出数据
select 字段1、字段2，字段3 from 表名
select * from 表名
3、where子句 -- 条件查询
数据表一般包含大量的数据，很少需要检索表中所有的行，只会根据特定的操作提取特定的数据
select 语句里面使用where子句指过滤条件
where条件中可以用算数运算符、比较运算符、逻辑运算符

- - / %

< > <= >= != =
not（非） and（与，并且） or（或）
注意：
1）组合and和or可以多次使用
2）sql标准在处理操作之前，优先处理and操作符，这个时候可以使用括号括起来

MariaDB [lamp52]> select * from t4 where name='xxx' and (id=2 or id=3);

4、其他的操作符
in操作符，指定条件范围，范围中每个条件都可以进行匹配
格式：in(值1，值2.....);
between操作符，指定的两个值之间
格式：between 起始值 and 结束值
not，否定后面的操作符
现在我要找包含性福生活的信息，怎么办？模糊查询
要使用like或者not like两个操作符使用包含通配符的字符串
格式
字段名 like'字符串';
通配符：用来匹配一部分特殊字符
通配符：
%表示任意字符出现任意次数，可以放任意位置
格式
字段名 like'%字符串%';
_下划线，用途和%一样，但是表示任意字符出现一次
%值%：包含值
%值：以这个值结尾
值%：以这个值开始
注意：
1、不要过度的时候通配符，如果其他操作符能够达到目的一定要使用其他的操作符，因为通配符慢，它是一个一个找的
2、在确实有必要使用通配符时，除非绝对有必要，否则不要把它放在搜索模式的地方
5、order by子句--对字段进行排序
在sql标准中，对没有经过排序的数据它的顺序是不可信的
格式：1 order by 字段名[asc|desc]
asc 升序（默认的是升序）
desc 降序
格式2：order by 字段名1[asc|desc]，字段名2[asc|desc]？？？？？
注意：指的是字段名1完全相同的情况下按照字段名2进行排序
MariaDB [lamp52]> select * from t4 order by name asc,id asc;
limit 子句 -- 限制结果集
格式1：limit m
注意：
返回多少行数据
格式2：limit n，m
n表示从多少行开始取值（第一条数据结果是从0开始）
m 表示返回的行数
如果没有足够的行数，有多少行就显示多少行
十四、其他相关数据操作
1、count() 用来进行计数
count(*) 数据行的条数
注意：
可以用count(字段名)来进行统计，但是它只会统计全体非null的个数
2、min() 最小值
3、max()最大值
4、sum() 求和
5、avg() 平均数
不会讲null计算在内
6、group by 分组 -- 子句
格式1：group by 字段名

MariaDB [lamp52]> select name,count(*) from test group by name;

按照分组完成之后再次进行过滤

MariaDB [lamp52]> select name,count(*) from test group by name having count(*)>2;

整体顺序

select =>from =>where =>group by =>having =>order by => limit

完全限定

select 表名.字段名 from 库名.表名

起别名

MariaDB [lamp52]> select count(*) as zongshu from test;
select 字段 as  别名 form 表名

拼接字段
concat(字段)

MariaDB [lamp52]> select concat(id,name,score) from scores;

关联查询
步骤：
1、找关系
2、先写select * from 表1，表2
3、写where条件
4、将*替换成你想要的数据

MariaDB [lamp52]> select *from user;
MariaDB [lamp52]> select *from level;
select user.name,level.name from user,level where user.level=level.id;

子查询，就是在查询里面再包含一个查询(前置用法)

select name,(select name from level where user.level=level.id)from user;

后置用法：作为条件来用

select name from user where level in(select id from level where name in('翠红楼老鸨','翠红楼老板','翠红楼老板娘'));

修改数据
方式：指定要修改的字段数据

update 表名 set  字段名1=值1，字段名2=值2...where 条件

删除数据
delete from 表名 where 条件
注意：
1、一定要加where条件
2、一般来说，我们做完项目，这个项目里的数据都要清空
truncate table 表名;将表删除，然后再次重新创建。他比delete快
十五、php中mysql数据库的使用
php&mysqli之间合体
有3中mysql
mysql 纯面向过程不安全
mysqli 面向过程，面向对象混搭，相对安全
pdo 面向对象，安全
php连接mysqli
步骤:
1、连接数据库
$link = mysqli_connect('主机'，'用户名','密码');
2、选择数据库
mysqli_select_db(连接($link),'库名');
3、设置字符集
mysqli_set_charset($link,'utf8');
4、准备sql
$sql = "......增删改查";
5、发送sql语句
mysqli_query($link,sql语句);
注意：
如果mysqli_query（）执行的是select语句，将会有结果集。然后将结果集遍历出来
如果是deleteupdateinsert 语句没有结果集，但是还有影响的行数
6、处理结果集
select 有结果集
mysqli_fetch_assoc(结果集) //一次拿一条，返回关联数组
mysqli_fetch_row(结果集) //一次拿一条，返回索引数组
mysqli_fetch_array(结果集) //一次拿一条，返回关联、索引数组
推荐使用：mysqli_fetch_assoc(结果集)因为拿值的时候方便
mysqli_affected_rows（结果集） //返回影响行数
7、关闭数据库
mysqli_close($link);