MySQL基础

一对一关联
1
2
3
select * from city;

select code_id from city where city_name='北京';

根据MySQL的设置，默认接受使用单引号或双引号来表示字符串。

将上面两句以子查询的方式合并一句

1
select * from code where id=(select code_id from city where city_name='北京');

on和where后面所跟的条件不同：

select+查询字段+from+查询表+join+关联表+on+关联条件+where+筛选条件

1
select city_name,code_name from city join code as c on code_id=c.id where city_name='北京';

表别名或字段别名，关键字“as”可以省略

一对多关联

多表需添加外键（XX_id）指向主表 ，例如：班级和学生，是一对多关系，学生表students表添加外键class_id,指向班级表class

1
select s.name from class c join students s on c.id=s.class_id where c.class_name = "一班"；

张三的同班同学（自关联）

1
select s1.name ,s2.name from students s1 join students s2 on s1.class_id=s2.class_id where s2.name='张三' and s1.name!='张三';
多对多关联

多对多关联需添加第三张表作为中间表，中间表至少包含两个字段，这两个字段分别作为外键，指向各自一方的主键。例如以下是articles与tags是多对多关系，添加中间表articles_tags，其中字段分别指向两表

文章表articles

中间表articles_tags

标签表 tags

给每篇文章计数标签

1
select a.title,count(*) from articles a join articles_tags at on a.id=at.articles_id group by a.title;

「马上雾霾就来了」文章有哪些标签

1
select a.title,t.name from articles a join articles_tags at on a.id=at.articles_id join tags t on at.tags_id=t.id where a.title='马上雾霾就来了';
数据库操作

创建数据库两种方式

1
2
create database testdb charset utf8;
create database if not exists testdb charset utf8;

查看所有数据库以及数据库的设置

1
2
show databases;
show create database +库名

进入（使用）数据库

1
use testdb;

删除数据库的两种方式

1
2
drop database testdb;
drop database if exists testdb;

数据库名字不可以修改,数据库的修改仅限库选项: 字符集和校对集(校对集依赖字符集)

表的操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
字段+数据类型+其他要求 
create table empt(
id int(11) not null primary key auto_increment,
name varchar(20) not null comment "姓名",
gender tinyint(2) not null default 0 comment "性别",
dept_id int(11) not null,
is_enable tinyint(2) not null comment "是否启用，1启用，2关闭"
);

create table if not exists class(...
未进数据库前创建数据表 
create table if not exists testdb.students(...

删除表

drop table students,...;

进数据库前不能使用命令show tables

查看表

1
show tables;

模糊查询

show tables like 's%';

查看表结构 \g横向，默认设置;\G纵向;注意后面不能加分号

1
2
3
desc students;desc students\G
describe students;
show columns from students;

查看表结构创建语句

1
2
show create table students;
show create table students\G

复制表结构

1
create table stu_bak like students;

复制表数据

1
insert into stu_bak select * from students;

复制表结构同时备份数据

1
create table stu_bak2 select * from students;

rename修改表名

1
2
3
4
5
alter table 旧表名 rename 新表名;
alter table students_copy1 rename sty_copy1;  

rename 旧表名to 新表名;
rename table stu to sstt;
字段操作

修改字段名：alter...change...

修改字段修饰：alter...modify...

添加字段：add

删除字段：drop

修改字段名用change：

Alter table +表名+change old_name new_name +字段修饰;

1
alter table students change username name varchar(100) not null default '';

修改字段修饰用modify :

1
alter table students modify name varchar(50) not null default '' comment '姓名';

添加字段 用add (after/before):

1
alter table students add time int not null default 0 after money;

删除字段drop:

1
alter table students drop time;

主键操作

添加主键自增：先添加主键 ，再添加自增

1
2
alter table students add primary key(id);
alter table students modify id int auto_increment;

删除主键：先删除自增，再删除主键

1
2
alter table students modify id int;
alter table students drop primary key;
常用数据类型与修饰

decimal(5,2):浮点型,指一共五位数字，其中两位小数,整数3位多了报错，小数多了四舍五入。相比于float，double类型，decimal类型属于精确类型，不会丢失精度，所以只要涉及到金钱，财务相关的数据，对应的类型一定要选择decimal。

1
2
Decimal(18,9)，18这个数代表最长可以到18位，而9这个数字表示小数点后面有9位数字，那么18-9=9，也就得出了整数位可以有9位。
1个字节8位，4个字节也就是32位，算符号位也可以存21亿多，也就是可以存10位数字，那说明存9位数字绰绰有余。decimal(18.9)小数两边个存储9个数字，一共使用9个字节：小数点前的数用四个字节，小数点后用4个字节，小数点本身占一个字节。所以decimal(18,9)占用9个字节。

enum：枚举，单选型，用于存储单一值

1
alter table students add gender1 ENUM('男', '女')

set：集合类型，多选型，用于存储多个值

1
alter table students add hobby1 set('羽毛球', '篮球','乒乓球');

char(6) ：定长 效率高，最多存储6个字符

varchar(30) ：变长，省空间

Int(10):指定zerofill前导0填充,不足10位时，以0填充，超过则正常显示，不做宽度限制。无论int(9)还是int(3)，都是占用四个字节的空间

unique：唯一约束

primary key：主键，一个表最多只能有一个主键，主键列中不能null 0 重复值

auto_increment：自增

unsigned：非负修饰（设置前导零会自动设置上）

not null：不为空

default ：默认值

如果是char或varchar类型，length可以小于字段规定长度。当varchar太大时，会自动转化为text。

Mysql 数据类型：

数值类型：

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型( INTEGER、SMALLINT、DECIMAL 和 NUMERIC )，以及近似数值数据类型( FLOAT、REAL 和 DOUBLE PRECISION )。

关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB 表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT

数据类型 大小 范围（无符号） 范围（有符号） 描 述
TINYINT(size) 1字节 （0,255） （-125，127） 小整数值
SMALLINT(size) 2字节 （0,65535） （-32768,32767） 大整数值，size 默认为9
MEDIUMINT(size) 3字节 （0，16777215） （-8388608，8388607） 大整数值，size 默认为 9
INT(size)或INTEGER 4字节 （0，4294967295） （-2147483648，2147483647） 大整数值，size 默认为 11
BIGINT(size) 8字节 （0，18446744073709551615） （-9223372036854775808，9223372036854775807） 极大整数值，size 默认为 20
FLOAT(size,d) 4字节 0，(1.175 494 351 E-38，3.402 823 466 E+38) (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 单精度浮点数，size 参数中规定显示最大位数，d 参数中规定小数点右侧的最大位数
DOUBLE(size,d) 8字节 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数，size 参数中规定显示最大位数，d 参数中规定小数点右侧的最大位数
DECIMAL(M,D) 默认M=10，D=0 依赖于size和d的值 依赖于size和d的值 size 为精度；d 为保留的小数位数

以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。

实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。

例如：

1
2
3
4
int的值为10 （指定zerofill）
int（9）显示结果为000000010
int（3）显示结果为010
显示的长度不一样,但都是占用四个字节的空间

字符创类型：

指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET

数据类型 大小 描述
CHAR(size) 0-255字节 定长字符串，保存固定长度的字符串（可包含字母、数字以及特殊字符），括号中指定字符串的长度，最多 255 个字符。
VARCHAR(size) 0-65535 字节 变长字符串，保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。**注释：**如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT 0-255字节 短文本字符串，存放最大长度为 255 个字符的字符串。
TEXT 0-65 535字节 长文本数据，存放最大长度为 65,535 个字符的字符串。
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
BLOB 0-65 535字节 二进制形式的长文本数据，用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据，存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据，用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT 0-4 294 967 295字节 极大文本数据，存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据，用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) - 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。**注：**这些值是按照您输入的顺序排序的。
SET - 与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不是非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据，可以存储图片，音视频等文件。有 4 种 BLOB 类型，它们只是可容纳值的最大长度不同。

1
2
3
4
5
6
7
TINYBLOB：大小：0 - 255字节，用途：短文本二进制字符串

BLOB：大小：0 - 65KB，用途：二进制字符串，例如图标或 logo 图片

MEDIUMBLOB ：大小：0 - 16MB，用途：二进制形式的长文本数据

LONGBLOB：大小：0 - 4GB，用途：二进制形式的极大文本数据

数据库并不适合直接存储图片，如果有大量存储图片的需求，请使用对象存储或文件存储，数据库中可以存储图片路径来调用。

TEXT类型同 char、varchar 类似，都可用于存储字符串，一般情况下，遇到存储长文本字符串的需求时可以考虑使用 text 类型。按照可存储大小区分，有 4 种 类型：

1
2
3
4
5
6
7
TINYTEXT：大小：0 - 255字节，用途：一般文本字符串

TEXT：大小：0 - 65 535字节，用途：长文本字符串

MEDIUMTEXT ：大小：0 - 16 772 150字节，用途：较大文本数据

LONGTEXT：大小：0 - 4 294 967 295字节，用途：极大文本数据

不过在日常场景中，存储字符串还是尽量用 varchar ，只有要存储长文本数据时，可以使用 text 类型。

对比 varchar ，text 类型有以下特点

1
2
3
4
5
- text 类型无须指定长度。
- 若数据库未启用严格的 sqlmode ，当插入的值超过 text 列的最大长度时，则该值会被截断插入并生成警告。
- text 类型字段不能有默认值。
- varchar 可直接创建索引，text 字段创建索引要指定前多少个字符。
- text 类型检索效率比 varchar 要低。

text存储字节，varchar存储字符。

数据库规范中一般不推荐使用 blob 及 text 类型，但由于一些历史遗留问题或是某些场景下，还是会用到这两类数据类型的。

日期时间类型：

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的 MySQL 不能表示的值时使用"零"值。

TIMESTAMP 类型有专有的自动更新特性。

数据类型 大小（字节） 范围 格式 描述
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 日期和时间的组合
TIMESTAMP 4 '1970-01-01 00:00:01’UTC/'2038-01-09 03:14:07’UTC YYYY-MM-DD HH:MM:SS 时间戳，使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间
YEAR 1 1901/2155 YYYY 2 位或 4 位格式的年份值。4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

数据操作

别名设置，left(),mid(),right(),count(),year(),max(),avg(),sum()函数，if(条件，成立反值，不成立反值)

显示表所有数据

1
select * from students;

在字段中查找set类型数据

find_in_set(s1,s2):返回在字符串s2中与s1匹配的字符串的位置,列中逗号需得是英文","

1
2
3
在表students中筛选出字段hobby中包含“篮球”的数据
select * from students where find_in_set('篮球',hobby);
select * from students where age>30 and sex='女';

连接字符串concat

1
select concat(username,'-',age) from students;

distinct去重相同数据，后面数据和前面数据合并为一个数据

1
select distinct sex from students;

if表达式 i作为if(条件,成立反值，不成立反值)的字段名，显示大于小于

1
select age,if(age>30,'大于30','小于等于30') i from students;

order排序 desc 降序 asc升序 默认升序可以省略，有重复age时可以用到

1
select * from students order by age desc，id desc;

随机获取3个数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from students order by rand() limit 3;

rand([seed])可选,如果指定了seed，则返回可重复的随机数序列。如果未指定种子，则返回完全随机的数字。

返回一个随机十进制数（没有种子值 - 所以它返回一个完全随机的数字> = 0且<1）
>select rand();// 0.10445088809770614

>select rand(8);//0.15668530311126755
>select rand(8);//再来一次，值还是0.15668530311126755

返回一个随机十进制数> = 5且<10：rand()*(j-i)+i
>SELECT RAND()*(10-5)+5; //8.627276326182557

返回一个随机数> = 5且<= 10：floor(rand()*(j-i+1)+i)
>SELECT FLOOR(RAND()*(10-5+1)+5); //10

年龄在10-55之间的同学 between… and…

1
2
select * from students where age between 10 and 55;
select * from students where age>=10 and age<=55;

查询年龄在23,34,45岁的同学

1
select * from students where age in (23,34,45);

字符串截取 left(str,length)，mid(str,pos,len)，right(str,length)

截取左边开始的2个字符

1
select left(username,2) from students;

截取左边第2个字开始的1个字符长度

1
2
select mid(username,2,1) from students;
select * from students where hobby like '%羽毛球%';

as+别名：查询结果字段显示别名，as可以省略

1
select username as u,age as a from students;

获得所有人出生年份

1
select year(birthday) from students;

查看班级有多少男同学

1
2
select count(*) from students where sex='男';
select count(name) from students where sex='男';

max()最大，sum()求和，avg()求平均值

1
2
3
4
select max(age) from students;
错误书写：
select name,max(age) a from students;
由于max()是聚合函数max(age)属于聚合列，而name不是，不兼容报错

男生有多少人

1
select sex,count(*) from students group by sex having sex='男';

插入数据

1
insert into students set age=18,money=100;

写入多条数据：省略字段名，则默认所有字段

1
insert into students (username,age,sex) values ('超人',18,'男'),('葫芦娃',18,'男')...;

更新数据必须要有主键 ,即主键id,否则视为插入数据

1
replace into students (id,username,age,sex) values (32,'超人复仇者联盟222',18,'男')

update:

1
2
3
4
//所有男变为女
update stu_bak set sex='女' where sex='男';
//更新一条数据的多个字段
update stu set name="小明", age=19 where tel="13288097888";

replace into 与 update的区别：

REPLACE INTO 和 UPDATE 都是在数据库中更新数据的操作，但REPLACE INTO语句首先尝试插入一条新记录，如果已存在具有相同主键（或唯一键）的记录，则先删除旧记录，然后再插入新记录。 而 UPDATE 语句仅更新现有记录的某些列，不会删除或重新插入记录。

删除数据
delete from 表名 条件

1
delete from students where id=19;

清空所有数据，让id重新从1开始

1
truncate students;

显示警告：

1
show warnings；
其他

select now(); 获得当前日期时间

顺序:where—group by having—order by—limit

1
select * from students where id>4 group by gender having gender='女' order by age limit 1;

使用关键字 需加上"``"

1
2
create database `databases`;
drop database `databases`;

查看服务器识别的字符集

1
show charset;

服务器默认的跟客户端打交道的字符集

1
show variables like 'character_set%';

查看mysql中的存储过程和函数状态

1
SHOW PROCEDURE STATUS LIKE 'insert_%'\G

查看设置的过程

1
SHOW CREATE PROCEDURE insert_d(过程名)\G

MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写。但再将数据库导出放到linux服务器中使用时就报错了，
因为在linux下，表名区分大小写而找不到表，即大小写敏感。可以在linux下更改MySQL的设置使其也不区分大小写，反之要使得在windows下大小写敏感，相应的更改windows中MySQL的设置就行了。