mysql 的基本操作

---恢复内容开始---

mysql是一个数据库，太多的也不想说了，什么公司的什么年代创建的，跟我没关系，会用就完了，怎么安装自行百度吧。

1初识mysql语句

操作文件夹（库）

增

create database db1 charset utf8;

查

查看当前创建的数据库；show create database db1；

查看所有的数据库；show databases；

改

alter database db1 charset gbk；

删

drop database db1；

操作文件（表）

切换文件夹；（进入文件夹）use db1；

查看当前所在的文件夹；select database（）；

增

create table t1（id int，name char）；

查

查看当前的这张t1表；show create table t1；

查看所有的表；show tables；

查看表的详细信息；desc t1；

改

modify修改的意思； alter table t1 modify name char（6）；

改变name为答谢的NAME；alter table t1 change name NAME char（7）；

删

删除表；drop table t1；

操作文件内容（记录）

增

插入一条数据，规定id，name数据

insert t1（id，name） valuse（1，‘名字’），（2，“名字”）；

select id from db1。t1；

select id，name from db1.t1；

select * from db1。t1；

改

update db1.t1 set name=“新名字”；

update db1.t1 set name=‘新名字’ where id = 2；

删

delete from t1；

delect from t1 where id = 2；

2库的操作

1创建数据库

create database 数据库名 charset utf8；

数据库命名规则：基本上跟python或者js的命名规则一样

可以由字母，数字，下划线，@，#，¥，

区分大小写

唯一性

不能使用关键字 create select

不能单独使用数字

最长128位

2数据库相关操作

查看数据库；show databases；

查看当前库；show create database db1；

查看所在的库；select database（）；

选择数据库；use 数据库名

删除数据库；drop database 数据库名；

修改数据库；alter database db1 charset utf8；

3表的操作

1，创建数据库

create database db2 charset utf8；

2，使用数据库

use db2；

3，创建表

create table 表名（字段名1 类型[(宽度)约束条件]，

。。。。

）；

create table a1（

　　id int，

　　name varchar（50），

　　age int（3）

）；

4，插入表的记录

insert into a1 values

（1，‘名字’，18），

。。。

；

5，查询表的数据和结构

（1）查询a1表中的存储数据

select * from a1；

（2）查询a1表的结构

desc a1；

（3）查看表的详细结构

show create table a1\G；

6，复制表

（1）新创建一个数据库db3

create database db3 charset utf8；

（2）使用db3

use db3；

（3）复制db2.a1的表结构和记录

复制表的操作（即复制了表结构，又复制了记录）

create table b1 select * from db2.a1；

4数据类型

mysql常用的数据类型包括

1，数字：

整数：tinyint int bigint

小数：

　　float：在位数比较短的情况下不精准

　　double：在位数比较长的情况下不精准

　　decimal：（如果用小数，推荐使用decimal）

　　　　精准

　　　　内部原理是以字符串形式去存

2，字符串：

char（10）：简单粗暴，浪费空间，存取速度快

　　　　root存成root000000

varchar：jingz，节省空间，存取速度慢

sql优化：创建表时，定长的类型往前放，变长的往后放

　　　　　　　　　　　　性别　　　　　　地址，或详细信息

255个字符，超了就把文件路径存放到数据库中。

　　比如图片，视频等找一个文件服务器，数据库中只存路径或url。

3，时间类型：

最常用：datetime

4，枚举类型与集合类型

enum和set

整数类型：tinyint smallint mediumlnt int bigint

作用存储年龄，等级，id，各种号码等

========================================
        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
～ 255

            PS： MySQL中无布尔值，使用tinyint(1)构造。



========================================
        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
～ 4294967295



========================================
        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
 ～  18446744073709551615

以上重点在于有符号，可以有负数

验证1:有符号和无符号tinyint

============有符号tinyint==============
# 创建数据库db4
create database db4 charset utf8;

# 切换到当前db4数据库
mysql> use db4;

# 创建t1 规定x字段为tinyint数据类型（默认是有符号的）
mysql> create table t1(x tinyint);

# 验证，插入-1这个数
mysql>   insert into t1 values(-1);

# 查询 表记录，查询成功（证明默认是有符号类型）
mysql> select * from t1;
+------+
| x    |
+------+
| -1 |
+------+

#执行如下操作，会发现报错。因为有符号范围在(-128,127)
mysql>   insert into t1 values(-129),(128);
ERROR 1264 (22003): Out of range value for column 'x' at row 1


============无符号tinyint==============
# 创建表时定义记录的字符为无符号类型（0,255） ，使用unsigned
mysql> create table t2(x tinyint unsigned);

# 报错，超出范围
mysql>   insert into t2 values(-129);
ERROR 1264 (22003): Out of range value for column 'x' at row 1

# 插入成功
mysql>   insert into t2 values(255);
Query OK, 1 row affected (0.00 sec)

以上重点在于带符号的有负数，无符号的不带负数，存储数据一样多，也就是不带符号的正数存的多

验证2：int类型后面的存储是显示宽度，而不是存储宽度

mysql> create table t3(id int(1) unsigned);

#插入255555记录也是可以的
mysql> insert into t3 values(255555);

mysql> select * from t3;
+--------+
| id     |
+--------+
| 255555 |
+--------+
ps:以上操作还不能够验证，再来一张表验证用zerofill 用0填充

# zerofill 用0填充
mysql> create table t4(id int(5) unsigned zerofill);


mysql> insert into t4 value(1);
Query OK, 1 row affected (0.00 sec)

#插入的记录是1，但是显示的宽度是00001
mysql> select * from t4;
+-------+
| id    |
+-------+
| 00001 |
+-------+
row in set (0.00 sec)

以上重点是存在数据库中的和看到的不一样，数据库中会默认加0

浮点型

定点数类型：dec等同于decimal

浮点类型：float double

作用：存储薪资、身高、体重等

-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
#参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30

#有符号：
           -3.402823466E+38 to -1.175494351E-38,
           1.175494351E-38 to 3.402823466E+38

#无符号：
           1.175494351E-38 to 3.402823466E+38
#精确度： 
           **** 随着小数的增多，精度变得不准确 ****


 -------------------------DOUBLE-----------------------
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

#参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30

#有符号：
           -1.7976931348623157E+308 to -2.2250738585072014E-308
           2.2250738585072014E-308 to 1.7976931348623157E+308

#无符号：
           2.2250738585072014E-308 to 1.7976931348623157E+308

#精确度：
           ****随着小数的增多，精度比float要高，但也会变得不准确 ****

======================================
--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill]

#参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。


#精确度：
           **** 随着小数的增多，精度始终准确 ****
           对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储。

以上重点就是说越长越不准确

日期类型

date time datetime timestamp year

作用：存储用户注册时间，文章发布时间，员工入职时间，出生日期等

语法：
        YEAR
            YYYY（1901/2155）

        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

枚举和集合

枚举enum 单选只能在给定的范围内选一个值，如性别

集合set 多选在给定的范围内可以选择一个或一个以上的值（爱好1，爱好2.。。。）

mysql> create table consumer(
    -> id int,
    -> name varchar(50),
    -> sex enum('male','female','other'),
    -> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
    -> fav set('play','music','read','study') #在指定范围内，多选多
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> insert into consumer values
    -> (1,'赵云','male','vip2','read,study'),
    -> (2,'赵云2','other','vip4','play');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from consumer;
+------+---------+-------+-------+------------+
| id   | name    | sex   | level | fav        |
+------+---------+-------+-------+------------+
|    1 | 赵云    | male  | vip2  | read,study |
|    2 | 赵云2   | other | vip4  | play       |
+------+---------+-------+-------+------------+
rows in set (0.00 sec)

5完整性约束

主要有这几种

标识该字段为该表的主键，可以唯一的标识记录；primary key（pk）

标识该字段位该表的外键；foreign key（fk）

not null ；标识该字段不能为空

unique key（uk）；标识该字段的值是唯一的

auto——increment ；标识该字段的值自动增长（整数类型，而且为主键）

default ；为该字段设置默认值

unsigned 无符号

zerofill 使用0填充

#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺少的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20 
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

not null 与 default

是否可空，null表示空，非字符串

not null 不可以 null 可以

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table tb1（

　　nid int not null defalut 2，

　　num int not null

）；

unique不同的，mysql中称为单列唯一

create table departm（

　　id int unique，建表的时候就写里面

　　name char（10） unique

）；

联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束

create table services（

　　id int，

　　ip char

　　port int

　　unique（id）

　　unique（IP，port）

）；

primary key一般写在id里唯一且不为空

auto_increm约束的字段为自动增长，约束的字段必须同时被key约束

foreign key 让两张表有关联

6外键的变种三种关系

如何找出两张表之间的关系

因为有了foreign key的约束，使得两张表形成了三种关系

一对多，多对多，多对一

找出两张表的关系

分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

7数据的增删改查

插入数据 insert

更新数据 update

删除数据 delete

插入数据

一、插入数据 INSERT
1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

 4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

二、更新数据UPDATE
语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;
三、删除数据DELETE
语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

查又分为单表查询和多表查询

一、单表查询的语法
   SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数
二、关键字的执行优先级（重点）

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

（1）where约束

where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之间
3.in(10,20,30)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

关键字like模糊查询像

select * from 表 where 字段 like ‘jin%’；只要该字段以jin开头不管后面什么，都拿出来

通配符‘_‘

select * from 表 where 字段 like ‘ale_’；以ale开头后面只有一位的

（2）group by 分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数

 select * from emp group by post;# 报错

 select post from employee group by post;

（3）聚合函数

max（）求最大值 ------min

avg（）求平均值

sum（）求和

count（）求总个数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
# 每个部门有多少个员工
select post,count(id) from employee group by post;
# 每个部门的最高薪水
select post,max(salary) from employee group by post;
# 每个部门的最低薪水
select post,min(salary) from employee group by post;
# 每个部门的平均薪水
select post,avg(salary) from employee group by post;

（4）having过滤

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

以上重点就是having更精确

（5）limit限制查询的记录数：

示例：
    SELECT * FROM employee ORDER BY salary DESC 
     LIMIT 3;                    #默认初始位置为0 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

desc倒序排序从大到小

acs正序排序从小到大

多表查询

多表连接查询

1，多表连接查询

语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

连接所有

先写个左连接中间加个union all 再写个右连接

2，符合条件查询

select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

3，子查询

子查询是将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件。

子查询中可以包含in，not in，any，all，exists，not exists等关键字

可以包含比较运算符=，！=，>,<等

发表于 2018-11-08 08:18 我才是真正的彭于晏阅读(378) 评论(1) 编辑收藏举报

公告