本文提到的数据类型、函数等，仅挑选出个人部分常用的来写。

子查询、多表查询、索引、视图、存储过程、触发器，看这里 >基础笔记2

常用数据类型

1.数值类型

类型 大小 范围（有符号） 范围（无符号） 用途
INT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值

2.时间日期类型

类型 大小( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间

3.字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串

4.枚举

1
sex enum('男','女')，

这个枚举是真的坑爹，不推荐使用，但有一些时候偷懒蛮好使的

建议满足以下几点再使用：
1.存储的是准确、不变的值时
2.不需要存储额外的关联信息
3.enum值的数量大于2个并少于20个
4.储存的是字符串类型的数值

约束

1.主键：primary key

  主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
  每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
  当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

主键的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--在创建表时添加主键
create table tab_name(
id int primary key,
name char(10)
);

--在创建表时添加复合主键
create table tab_name(
id int,
name char(10),
sex enum('男','女'),
primary key(id,name)
);

--添加主键
alter table 表名 add primary key(字段,字段);

--修改主键
alter  table 表名 modify 字段 int primary key；

--删除主键
alter table 表名 drop primary key;

2.外键：foreign key

  外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。

外键的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--主表
create table tab_name(
id int primary key,
name char(10)
);
--副表添加外键
create table tab_name2(
id int primary key,
name char(10),
foreign key(id) references tab_name(id)
);

--添加外键
alter table 副表 add foreign key(字段) references 主表(字段);

--删除外键
alter table 表名 drop foreign key 字段;

3.唯一：unique

  唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。
  唯一约束不允许出现重复的值，但是可以为多个null。

唯一约束的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--在创建表时添加唯一约束
create table tab_name(
id int unique,
name char(10)
--unique(id,name) 或者这样添加多个字段
);

--添加自增约束
alter table 表名e add unique (字段,字段);

--修改唯一约束
alter table 表名 modify 字段 数据类型 unique;

--删除唯一约束
alter table 表名 drop index 字段

4.非空：not null

  强制列不能为 NULL 值，约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

非空约束的增删改

1
2
3
4
5
6
7
8
9
10
11
--在创建表时添加非空约束
create table tab_name(
id int primary key,
name char(10) not null
);

--添加非空约束
alter table 表名 modify 字段 数据类型 not null;

--删除非空约束
alter table 表名 modify 表名 数据类型 null;

5.自增：auto_increment

  一个表只能有一个自增列。
  自增列必须是整型的。

自增约束的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--在创建表时添加自增约束
create table tab_name(
id int auto_increment,
name char(10)
);

--添加自增约束
alter table 表名 modify 字段 数据类型 auto_increment;

--修改自增初值，可在创建表时设置
alter table 表名 auto_increment=初值

--删除自增约束
alter table 表名 modify 字段 数据类型;

6.默认值：default

  在插入一个新记录的时候，如果没有给字段赋值，那么系统会自动赋予字段默认值。

默认值约束的增删改

1
2
3
4
5
6
7
8
9
10
11
--在创建表时添加默认值约束
create table tab_name(
id int primary key,
name char(10) default 'tom'
);

--添加默认值约束
alter table 表名 modify 字段 数据类型 default '默认值';

--删除默认值
alter table 表名 alter column 字段 drop default;

数据库与表

数据库

数据库的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--创建数据库
create database 库名;

--创建字符集为utf-8的数据库
create database 库名 default character set utf8 collate utf8_general_ci;

--修改数据库字符集
alter database 库名 default character set utf8 collate utf8_general_ci;

--修改数据库名字
rename database 库名 to 库名;

--删除数据库
drop database 库名;

表

表的增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--创建表
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束
);

--创建utf8表
create table 表名(
字段名 数据类型 约束,
字段名 数据类型 约束
)character set utf8 collate utf8_general_ci;

--改名
rename table 表名 to 表名;

--删除表结构和全部数据
drop table 表名;

--保留表结构，删除全部数据
truncate table 表名;

--delete	保留表结构，删除指定的数据
delete table 表名  --删除全部数据
delete table 表名 where 条件  --按条件删除

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
!列与值的数目必须一致
!逗号隔开，字符型要带''，自增可写NULL或DEFAULT

--不指定字段插入
insert into 表名 values(数据,'数据','数据')；
replace into 表名 values(数据);

--指定字段插入
insert into 表名(字段,字段) values('数据','数据')

--同时添加多行数据
insert into 表名 values
(数据,'数据','数据'),
(数据,'数据','数据');

修改数据

1
2
3
4
5
6
7
8
9
10
11
!列与值的数目必须一致
!逗号隔开，字符型要带''，自增可写NULL或DEFAULT

--更新表 ！不加 where 条件为更新整个表
update 表名 set 数据 where 条件;

--update 多列
update 表名 set 数据,数据 where 条件;

--修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;

删除数据

1
2
--删除数据 ！不加 where 为删除表里全部数据
delete from 表名 where 条件

复制表

1
2
3
4
--复制结构
create table 表2 like 表1;
--复制数据
insert into 表2 select * from 表1;

查询

简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--查看数据库
show databases;

--查看当前数据库
select database();

--查看当前库里的表
show tables;

--查看表数据
select * from 表名;

--查看表结构
desc 表名;

各种条件的应用

where 条件

1
select * from 表名 where 条件;
1
2
3
4
5
6
7
mysql> select * from c where id=2;
+----+------+
| id | name |
+----+------+
|  2 | Ajie |
+----+------+
1 row in set (0.00 sec)

distinct 重复字段只取一次

1
2
3
4
5
6
7
8
9
mysql> select distinct name from c;
+------+
| name |
+------+
| Ajie |
| ahua |
| ago  |
+------+
3 rows in set (0.00 sec)

as 别名

1
select 字段 as 别名 from 表名;
1
2
3
4
5
6
7
mysql> select name as '名字' from c;
+------+
| 名字 |
+------+
| tom  |
+------+
1 row in set (0.00 sec)

order by 排序

1
select 字段 from 表名 order by 条件,条件,排列顺序;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select * from c order by id;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | Ajie |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from c order by id desc;
+----+------+
| id | name |
+----+------+
|  2 | Ajie |
|  1 | tom  |
+----+------+
2 rows in set (0.00 sec)

group by 分组

1
2
3
!分组字段必须要在查询字段里，或是个聚合函数:count(分组条件)
select 字段 from 表名 group by 分组字段;
select 字段 from 表名 group by 分组字段 having 分组条件;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select * from c;
+----+------+
| id | name |
+----+------+
|  2 | Ajie |
|  3 | ajie |
|  4 | ahua |
|  5 | ago  |
+----+------+
4 rows in set (0.00 sec)

mysql> select name from c group by name;
+------+
| name |
+------+
| ago  |
| ahua |
| Ajie |
+------+
3 rows in set (0.00 sec)

mysql> select * from c group by name having id<4;
+----+------+
| id | name |
+----+------+
|  2 | Ajie |
|  1 | tom  |
+----+------+
2 rows in set (0.00 sec)

limit 限制返回数量

1
2
3
4
--只有一个值的时候，表示返回数量
select * from 表名 limit 返回数量;
--当limit有两个值的时候 第一个值代表起点位置，第二个值代表返回数量
select * from 表名 limit 起点位置,返回数量;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from c limit 3;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | Ajie |
|  3 | ajie |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from c limit 3,2;
+----+------+
| id | name |
+----+------+
|  4 | ahua |
|  5 | ago  |
+----+------+
2 rows in set (0.00 sec)

group_concat()查询字段一起显示

1
select group_concat(字段,字段) from c;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
建议插入适当的符号和搭配 as别名，来便于查看

mysql> select group_concat(id,'-',name) from c;
+----------------------------------+
| group_concat(id,'-',name)        |
+----------------------------------+
| 1-tom,2-Ajie,3-ajie,4-ahua,5-ago |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select group_concat(id,'-',name)as '号数-名字' from c;
+----------------------------------+
| 号数-名字                        |
+----------------------------------+
| 1-tom,2-Ajie,3-ajie,4-ahua,5-ago |
+----------------------------------+
1 row in set (0.00 sec)

binary 区分大小写

1
select * from 表名 where binary 条件;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from c where name='ajie';
+----+------+
| id | name |
+----+------+
|  2 | Ajie |
|  3 | ajie |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from c where binary name='ajie';
+----+------+
| id | name |
+----+------+
|  3 | ajie |
+----+------+
1 row in set (0.00 sec)

用%和like来匹配字符

1
select 字段 from 表名 where 字段 like "%关键字%"
1
2
3
4
5
6
7
mysql> select * from c where name like '%t%';
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

is 判断空值

1
2
3
4
--判断空值
select 字段 from 表 where 条件is null;
--判断非空值
select 字段 from 表 where 条件is not null;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select * from c where age is not null;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
|  3 | ajie |   35 |
|  4 | ahua |   41 |
|  5 | ago  |   21 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select * from c where age is null;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | Ajie | NULL |
+----+------+------+
1 row in set (0.00 sec)

常用函数

abs 绝对值

1
2
3
4
5
6
7
mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mod和% 取余

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select mod(17,5);
+-----------+
| mod(17,5) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql> select 17%5;
+------+
| 17%5 |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

round 四舍五入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select round(字段,2)  表示保留两位小数

mysql> select round(1.3);
+------------+
| round(1.3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select round(1.5);
+------------+
| round(1.5) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

max最大值min最小值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select age from c;
+------+
| age  |
+------+
|   20 |
| NULL |
|   35 |
|   41 |
|   21 |
+------+
5 rows in set (0.00 sec)

mysql> select max(age) from c;
+----------+
| max(age) |
+----------+
|       41 |
+----------+
1 row in set (0.00 sec)

mysql> select min(age) from c;
+----------+
| min(age) |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

avg 平均值

1
2
3
4
5
6
7
mysql> select avg(age) from c;
+----------+
| avg(age) |
+----------+
|  29.2500 |
+----------+
1 row in set (0.00 sec)

加密函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select md5('ajie');
+----------------------------------+
| md5('ajie')                      |
+----------------------------------+
| 9e19640e042ec78ec44069a13e1284f9 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select password('ajie');
+-------------------------------------------+
| password('ajie')                          |
+-------------------------------------------+
| *24B9CAB82D59636EC28B511C71C1E7D2EDCD89E6 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)