02-MySQL基本操作

操作数据库

增：

create database 数据库名称 charset utf8；  # 字符集默认时latin1

数据库命名规范：

• 可以由字母、数字、下划线、@、#、$
• 区分大小写
• 具有唯一性
• 不能使用关键字如 select create
• 不能单独使用数字
• 最长128位

删：

drop database 数据库名称；

改：

数据库删除了再添加

线上环境下，不能直接删除数据，再删除之前需要进行备份

查：

show database;   # 查看数据库
use 数据库名称

创建数据表

语法：
    create table 表名(
        字段名   列类型  [可选参数],
        字段名   列类型  [可选参数]
        ....
    ) charset=utf8;
 
 如：
 create table t4(
    id int,
    name char(15)
    )charset=utf8;

列约束

• auto_increment 自增长1
• primary key 主键索引，加快查询速度，列的值不能重复
• not null 标识该字段不能为空
• default 该字段设置默认值

create table t5(
    id int primary key auto_increment,
    name char(15) not null default '',
)charset=utf8;

查看数据表结构

desc t5;

show create table t5;

列类型（字段类型）

整型

类型	大小	范围（无符号）
Tinyint	1个字节	（0-255）
Smallint	2个字节	（0-65535）
Mediumint	3个字节
int（一般直接用int）	4个字节
bigint	8个字节

注：unsigned 加上代表不能取负数，只适用于整型。 基本语法：在类型之后加上一个 unsigned

应用场景：根据公司业务的场景，来选取合适的类型

浮点型

• float

Float又称之为单精度类型：系统提供4个字节用来存储数据，但是能表示的数据范围比整型大的多，大概是10^38；只能保证大概7个左右的精度（如果数据在7位数以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）

基本语法

Float：表示不指定小数位的浮点数

Float(M,D)：表示一共存储M个有效数字，其中小数部分占D位

Float(10,2)：整数部分为8位，小数部分为2位

• decimal

Decimal定点数：系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的。

Decimal(M,D)：M表示总长度，最大值不能超过65，D代表小数部分长度，最长不能超过30。

比较

mysql> create table t6(
    -> id int unsigned auto_increment primary key,
    -> salary decimal(16,10),
    -> num float
    -> )charset=utf8;
    
    

insert into t6 values(0,500023.2312345678, 5000.2374837284783274832);

mysql> select * from t6;
+----+-------------------+---------+
| id | salary            | num     |
+----+-------------------+---------+
|  1 | 500023.2312345678 | 5000.24 |
+----+-------------------+---------+

decimal比较精确，适合描述钱

字符串

• char（长度）： 定长字符：指定长度之后，系统一定会分配指定的空间用于存储数据，char（L），L长度为0到255
• varchar（长度）：变长字符，指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度），长度理论值位0到65535

区别：

Char和varchar数据存储对比（utf8，一个字符都会占用3个字节）

存储数据	Char(2)	Varchar(2)	Char所占字节	Varchar所占字节
A	A	A	2 * 3 = 6	1 * 3 + 1 = 4
AB	AB	AB	2 * 3 = 6	2 * 3 + 1 = 7

• char 无论插入的字符是多少个，永远固定占规定的长度
• varchar：根据插入的字符的长度来计算所占的字节数，但是有一个字节是用来保存字符串大小的
• char的数据查询效率比varchar高

时间日期类型

Date

日期类型：系统使用三个字节来存储数据，对应的格式为：YYYY-mm-dd，能表示的范围是从1000-01-01 到9999-12-12，初始值为0000-00-00

Time

时间类型：能够表示某个指定的时间，但是系统同样是提供3个字节来存储，对应的格式为：HH:ii:ss，但是mysql中的time类型能够表示时间范围要大的多，能表示从-838:59:59~838:59:59，在mysql中具体的用处是用来描述时间段。

Datetime

日期时间类型：就是将前面的date和time合并起来，表示的时间，使用8个字节存储数据，格式为YYYY-mm-dd HH:ii:ss，能表示的区间1000-01-01 00:00:00 到9999-12-12 23:59:59，其可以为0值：0000-00-00 00:00:00

Timestamp

时间戳类型：mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是：YYYY-mm-dd HH:ii:ss

Year

年类型：占用一个字节来保存，能表示1900~2155年，但是year有两种数据插入方式：0~99和四位数的具体年

mysql> create table t7(
    -> d date,
    -> t time,
    -> dt datetime
    -> );
    
mysql> insert into t7 values(now(),now(),now());

mysql> select * from t7;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-10-29 | 15:37:02 | 2019-10-29 15:37:02 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

枚举enum

就是列举出所有的选项

mysql> create table t8(
    -> id int unsigned auto_increment primary key,
    -> name char(15) not null,
    -> age int,              ,
    -> gender enum('男','女')
    -> )charset=utf8;
    
    
insert into t8 values(0,'老张',36,1);

mysql> select * from t8;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | 老张 |   36 | 男     |
+----+------+------+--------+

修改表名

alter table t8 rename info;

rename table t7 to t777;

增加字段

语法： alter table 表名 add 字段名 列类型 [可选参数]

alter table t6 add name char(25) not null default '';

默认是添加在最后一列的

--添加在第一列
alter table t6 add name char(25) not null default '' first;
--添加在指定列
alter table t6 add name char(25) not null default '' after id;

删除字段

语法：alter table 表名 drop 字段名

alter table t9 drop name;

修改字段

修改列类型

语法：alter table 表名 modify 字段名 列类型 [约束条件]

alter table t9 modify name1 char(20);

修改字段名:

语法：alter table 表名 change 旧字段名 新字段名 新的列类型 [约束条件]

alter table t6 change sex gender enum('male', 'female')

删除表

drop table 表名;

复制表结构

create table t99 like t7;

操作数据行

增加数据

语法：insert into 表名 (列1, 列2) values (值1,'值2');

insert into t1(id,name) values(1,'老赵');

删除数据

语法：delete from 表名 where 条件

--删除id=1的这一列
delete from t1 where id=1;

--删除表中所有数据
delete from t1;

另一种删除的方法：truncate 表名 没有where条件

truncate t1;

区别：

1. delete删除是一行行删除；而且插入数据从上一次主键自增1开始，
2. truncate删除是全选删除，删除速度高于delete；而且插入数据从1开始

修改数据

语法：update 表名 set 列名1=新值1，列名2=新值2 where 条件

update t5 set name='小明' where id=3;

查询数据

--条件查询
select * from t4 where id=3;
select * from t4 where id>3;
select * from t4 where id!=3;

--between...and... 取值范围是闭区间
select * from t3 where id between 3 and 8;

--去重查询
select distinct gender from t7;

--in
select * from t6 where id in (2,3,11);

--like模糊查询
select * from t7 where name like 'x%';   x开头
select * from t7 where name like '%x';   x结尾
select * from t7 where name like '%x%';  包含x

单表查询

分组操作

group by 指的是：将所有记录按照某个相同字段进行归类

用法 ：select 聚合函数(count)，选取的字段 from 表名 group by 分组的字段;

注意点：

• group by：是分组的关键字
• group by 必须和聚合函数一起使用

例子：

--以性别为例， 进行分组， 统计一下男生和女生的人数是多少个
select count(id), gender from students group by gender;
+-----------+--------+
| count(id) | gender |
+-----------+--------+
|         2 | 男     |
|         3 | 女     |
|         1 | 中性   |
+-----------+--------+


--对班级进行分组，统计出每个班级年龄最大的那个人

select cls_id,max(age),group_concat(name) from students group by cls_id;
+--------+----------+----------------------+
| cls_id | max(age) | group_concat(name)   |
+--------+----------+----------------------+
|      1 |       30 | 张飞,关羽,小鬼       |
|      2 |       18 | 小乔,孙尚香          |
|      3 |       20 | 甄姬                 |
+--------+----------+----------------------+

常用的聚合函数

#求最大年龄   
mysql> select  MAX(age)   from  student_detail;

#求最小年龄
mysql> select  MIN(age) from student_detail;

#求和
mysql> select  SUM(age)  from  student_detail;

#求平均数
mysql> select  AVG(age)   from  student_detail;

#四舍五入
mysql> select  ROUND(AVG(age))   from   student_details;

#统计
mysql> select count(s_id)   from  student;

having 二次删选

表示对group by之后的数据，进行再一次的二次筛选

-- 查询平均年龄超过20岁的性别，以及姓名
select gender,group_concat(name) from students group by gender having avg(age)>20;
+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| 男     | 张飞,关羽          |
+--------+--------------------+

排序

order by 字段名 asc(升序) desc(降序)

升序asc可省略

-- 查询年龄在18到30岁之间的男性，按照年龄从小到大排序
select * from students where age between 18 and 30 and gender='男' order by age asc;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  5 | 关羽   |   28 | 190.00 | 男     |      1 |
|  4 | 张飞   |   30 | 190.00 | 男     |      1 |
+----+--------+------+--------+--------+--------+

可以多个排序：order by age desc, id asc

表示：先对age降序，如果年龄相同，就按id升序

select * from students order by age desc,id asc;
+----+-----------+------+--------+--------+--------+
| id | name      | age  | height | gender | cls_id |
+----+-----------+------+--------+--------+--------+
|  4 | 张飞      |   30 | 190.00 | 男     |      1 |
|  5 | 关羽      |   28 | 190.00 | 男     |      1 |
|  3 | 甄姬      |   20 | 170.00 | 女     |      3 |
|  1 | 小乔      |   18 | 180.00 | 女     |      2 |
|  2 | 孙尚香    |   18 | 180.00 | 女     |      2 |
|  6 | 小鬼      |   16 | 178.00 | 中性   |      1 |
+----+-----------+------+--------+--------+--------+

分页查询

用法：limit offset, size

offset: 行数据索引

size: 取多少条数据

select * from students limit 3,5;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  4 | 张飞   |   30 | 190.00 | 男     |      1 |
|  5 | 关羽   |   28 | 190.00 | 男     |      1 |
|  6 | 小鬼   |   16 | 178.00 | 中性   |      1 |
+----+--------+------+--------+--------+--------+



--limit  可以把offset省略，默认从第一行取
select * from students limit 5;
+----+-----------+------+--------+--------+--------+
| id | name      | age  | height | gender | cls_id |
+----+-----------+------+--------+--------+--------+
|  1 | 小乔      |   18 | 180.00 | 女     |      2 |
|  2 | 孙尚香    |   18 | 180.00 | 女     |      2 |
|  3 | 甄姬      |   20 | 170.00 | 女     |      3 |
|  4 | 张飞      |   30 | 190.00 | 男     |      1 |
|  5 | 关羽      |   28 | 190.00 | 男     |      1 |
+----+-----------+------+--------+--------+--------+

总结：

查询的使用顺序：

select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件

必须严格按照这样的顺序写！！！！！

多表查询

外键约束

使用方法：

constraint 外键名 foregin key (被约束的字段) references 约束的表(约束的字段)

• 外键约束：保持数据的一致性、一致性实现一对多关系
• 外键一般关联到另一张表的主键
• 因为一个表只存一类信息，用外键来约束，可以减少数据冗余

例子：

create table department(
    id int auto_increment primary key,
    name varchar(32) not null default ''
)charset utf8;


create table useinfo(
    id int auto_increment primary key,
    name varchar(32) not null default '',
    depart_id int not null default 1,
    constraint fk_user_depart foreign key (depart_id) references department(id)
)charset utf8;

constraint fk_user_depart只是给外键约束起了一个名字，可写可不写

唯一约束

unique key

例子：

mysql> create table st1(
    -> id int unique key,
    -> name varchar(20) );
    


--方法二    
create table department2(
id int,
name varchar(20),
comment varchar(100),
unique(id)
);

有唯一约束的字段，不能再添加id相同的值，确保唯一

一对一

user:

id	name	age
1	张三	18
2	李四	27
3	王五	23

由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来

pri:

id	salary	uid
1	5000	1
2	6000	2
3	3500	3

create table user(
    id int auto_increment primary key,
    name varchar(32) not null default '',
    age int not null
)charset utf8;


create table pri(
    id int auto_increment primary key,
    salary int not null default 1,
    constraint fk_pri_user foregin key (uid) references user(id),
    unique(uid)
)charset utf8;

一对多

一个表中的id可以被另一个表多次调用，比如员工信息表和部门表，可以有多个员工是同一个部门

# 语法
constraint 外键名 foreign key (被约束的字段) references 约束的表 (约束的字段)

# 例子
# 一张部门表，一张员工信息表，用id表示部门，约束该id必须在部门表的范围内
create table department(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset utf8;

insert into department (name) values ('研发部');
insert into department (name) values ('运维部');
insert into department (name) values ('前台部');
insert into department (name) values ('小卖部');

# 约束user_info中的字段depart_id只能是department中id中的一个
create table  user_info(
id int auto_increment primary key,
name varchar(32) not null default '',
depart_id int not null default 1,
constraint fk_user_depart foreign key (depart_id) references department (id)
)charset utf8;

insert into userinfo (name, depart_id) values ('a1', 1);
insert into userinfo (name, depart_id) values ('xxx', 2);
insert into userinfo (name, depart_id) values ('a2', 3);
insert into userinfo (name, depart_id) values ('a2', 4);
insert into userinfo (name, depart_id) values ('a3', 1);
insert into userinfo (name, depart_id) values ('a4', 2);

#报错
insert into userinfo (name, depart_id) values ('zekai4', 5);

多对多

--男生女生相亲，建立一张男生表，女生表，再建立一张相亲约会表，把男生女生联系在一起
create table boy(
    id int auto_increment primary key,
    bname varchar(32) not null default ''
)charset utf8;



create table girl(
    id int auto_increment primary key,
    gname varchar(32) not null default ''
)charset utf8;


create table boy2girl(
    id int auto_increment primary key,
    bid int not null default 1,
    gid int not null default 1,
    constraint fk_boy2girl_boy foreign key (bid) references boy(id),
    constraint fk_boy2girl_girl foreign key (gid) references girl(id)
)charset utf8;

多表联查

--一张部门表，一张员工信息表，员工部门id外键关联部门表的id

mysql> select * from department;
+----+--------+
| id | name   |
+----+--------+
|  1 | 研发部 |
|  2 | 运维部 |
|  3 | 前台部 |
|  4 | 小卖部 |
+----+--------+

mysql> select * from userinfo;
+----+--------+-----------+
| id | name   | depart_id |
+----+--------+-----------+
|  1 | zekai  |         1 |
|  2 | xxx    |         2 |
|  3 | zekai1 |         3 |
|  4 | zekai2 |         4 |
|  5 | zekai3 |         1 |
|  6 | zekai4 |         2 |
+----+--------+-----------+

内连接

只显示连接匹配的行，如果不加匹配条件就相当于笛卡尔积

select userinfo.id,userinfo.name,department.name,userinfo.depart_id from userinfo inner join department on userinfo.depart_id=department.id;

+----+--------+-----------+-----------+
| id | name   | name      | depart_id |
+----+--------+-----------+-----------+
|  1 | zekai  | 研发部    |         1 |
|  2 | xxx    | 运维部    |         2 |
|  3 | zekai1 | 前台部    |         3 |
|  4 | zekai2 | 小卖部    |         4 |
|  5 | zekai3 | 研发部    |         1 |
|  6 | zekai4 | 运维部    |         2 |
+----+--------+-----------+-----------+

左连接

left join …on..

优先显示左表的记录

本质就是：在内连接的基础上增加左边有右边没有的结果

右连接

right join… on…

优先显示右表的记录

本质就是：在内连接的基础上增加右边有左边没有的结果