1.表的约束
真正约束字段的数据类型,但数据类型约束会很单一,需要一些额外的约束,更好的保证数据的合法性
1.1 空属性
- null(默认)
- not null
注意:数据库默认字段基本都是空的,在实际开发中,尽可能保证字段不为空,空数据无法参与运算
1.2 默认值
某一种数据会经常性的出现某个具体的值,可以在一开始使用 default 指定好,在需要的时候直接使用默认值
mysql> create table t1( name varchar(20) not null,
-> gender char(2) default '男');
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| gender | char(2) | YES | | 男 | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
在插入数据时不给该字段赋值,就使用默认值
mysql> insert into t1(name) values('小明');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+--------+--------+
| name | gender |
+--------+--------+
| 小明 | 男 |
+--------+--------+
1 row in set (0.01 sec)
1.3 列描述
没有实际含义,专门用来描述字段,使用comment进行描述
mysql> create table t2 (
-> name varchar(20) not null comment '姓名',
-> age tinyint unsigned default 0 comment '年龄',
-> sex char(2) default '男' comment '性别'
-> );
注意:使用desc看不到描述信息,可以使用show查看
1.4 zerofill
zerofill是一种格式化输出
先创建一个表:
mysql> create table t3(
-> a int,
-> b int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t3\G;
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
ERROR:
No query specified
给表中插入元素,此时表为:
mysql> insert into t3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
此时给列增加zerofill属性,再查看表:
mysql> alter table t3 change a a int(5) zerofill;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+
1 row in set (0.00 sec)
发现 int(5) 中的5代表数据输出的宽度,数据若小于宽度,就用0补齐
1.5 主键
主键是用来唯一约束该字段里的数据,不能重复,不能为空,一张表最多只能有一个主键;主键所在列通常是整数类型
- 在创建表的时候直接在字段后面指定 primary key来创建主键
- 如果有多个字段作为主键,可以使用复合主键
- 当表创建好以后,可以再次追加主键`
alter table 表名 add primary key(字段列表)
- 主键对应字段不能重复
- 删除主键
alter table 表名 drop primary key;
1.6 自增长
auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键
注意:
- 任何一个字段要做自增长,前提是本身是一个索引(key一栏有值)
- 自增长字段必须是整数
- 一张表最多只能有一个自增长
1.7 唯一键
unique:一张表中有往往有很多字段需要唯一性,数据不能重复,但是一张表中只能有一个主键:唯一键就可以解决表中有多个字段需要唯一性约束的问题
注意:唯一键的本质和主键差不多,唯一键允许为空,而且可以多个为空,空字段不做唯一性比较
1.8 外键
foregin key:外键约束主要定义在从表上,主表必须有主键约束或唯一键约束。当定义外键后,要求外键列数据必须在主表的主键列存在或为null
语法:
foreign key (字段名) references 主表(列)
2.表的增删查改
2.1插入
语法:
INSERT [INTO] table_name
[(column [, column] …)]
VALUES (value_list) [, (value_list)] …
value_list: value, [, value] …
创建一张表:
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sn | int(11) | NO | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
两种插入方式:
- 单行数据全列插入
mysql> insert into student values (100, 10000, '唐三藏', null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values (101, 10001, '孙悟空', '111111');
Query OK, 1 row affected (0.00 sec)
--查看结果
mysql> select * from student;
+-----+-------+-----------+--------+
| id | sn | name | qq |
+-----+-------+-----------+--------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孙悟空 | 111111 |
+-----+-------+-----------+--------+
2 rows in set (0.00 sec)
- 多行数据指定列插入
mysql> insert into student(id, sn, name) values
-> (102, 20001, '曹孟德'),
-> (103, 20002, '孙仲谋');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-----+-------+-----------+--------+
| id | sn | name | qq |
+-----+-------+-----------+--------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孙悟空 | 111111 |
| 102 | 20001 | 曹孟德 | NULL |
| 103 | 20002 | 孙仲谋 | NULL |
+-----+-------+-----------+--------+
4 rows in set (0.00 sec)
更新:
INSERT … ON DUPLICATE KEY UPDATE
column = value [, column = value] …
替换:
REPLACE INTO…
2.2 查找
语法:
SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …
以下表为例:
+----+-----------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)
2.21 SELECT 列
- 全列查询
mysql> select * from exam_result;
+----+-----------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)
- 指定列查询
mysql> select id,name,yuwen from exam_result;
+----+-----------+-------+
| id | name | yuwen |
+----+-----------+-------+
| 1 | 唐三藏 | 67 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 88 |
| 4 | 曹孟德 | 82 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 70 |
| 7 | 宋公明 | 75 |
+----+-----------+-------+
7 rows in set (0.00 sec)
- 查询字段为表达式
--表达式包含一个字段
mysql> select id, name, yuwen + 10 from exam_result;
+----+-----------+------------+
| id | name | yuwen + 10 |
+----+-----------+------------+
| 1 | 唐三藏 | 77 |
| 2 | 孙悟空 | 97 |
| 3 | 猪悟能 | 98 |
| 4 | 曹孟德 | 92 |
| 5 | 刘玄德 | 65 |
| 6 | 孙权 | 80 |
| 7 | 宋公明 | 85 |
+----+-----------+------------+
7 rows in set (0.00 sec)
--表达式包含多个字段
mysql> select id, name, yuwen + shuxue + yingyu from exam_result;
+----+-----------+-------------------------+
| id | name | yuwen + shuxue + yingyu |
+----+-----------+-------------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)
- 为查询结果指定别名
语法:
SELECT column [AS] alias_name […] FROM table_name;
mysql> select id name, yuwen + shuxue + yingyu 总分 from exam_result;
+------+--------+
| name | 总分 |
+------+--------+
| 1 | 221 |
| 2 | 242 |
| 3 | 276 |
| 4 | 233 |
| 5 | 185 |
| 6 | 221 |
| 7 | 170 |
+------+--------+
7 rows in set (0.00 sec)
- 结果去重
mysql> select distinct shuxue from exam_result;
+--------+
| shuxue |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)
2.22 WHRER 条件
比较运算符:
逻辑运算符:
2.23 结果排序
语法:
– ASC 为升序(从小到大)
– DESC 为降序(从大到小)
– 默认为 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];
2.24 筛选分页结果
语法:
– 起始下标为 0
– 从 0 开始,筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 从 s 开始,筛选 n 条结果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
– 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET s;
2.25 group by
语法:
select column1, column2, … from table group by column;
2.3 更新表中数据
语法:
UPDATE table_name SET column = expr [, column = expr …]
[WHERE …] [ORDER BY …] [LIMIT …]
2.4删除表中数据
语法:
DELETE FROM table_name [WHERE …] [ORDER BY …] [LIMIT …]
- 插入查询结果
语法:
INSERT INTO table_name [(column [, column …])] SELECT …
- 截断表
语法:
RUNCATE [TABLE] table_name
注意:
- 只能对整表操作,不能像 DELETE 一样针对部分数据操作
- 实际上 MySQL 不对数据操作,所以比 DELETE 更快
- 会重置 AUTO_INCREMENT 项
3.函数
3.1 聚合函数
3.2 日期函数
- current_date():获取当前日期
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-05-28 |
+----------------+
1 row in set (0.20 sec)
- current_time():获取当前时间
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 22:19:12 |
+----------------+
1 row in set (0.01 sec)
- current_timestamp():获取当前时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2020-05-28 22:20:46 |
+---------------------+
1 row in set (0.00 sec)
- date_add(date, interval d_value_type):在date中添加日期或时间
select date_add('2017-10-28', interval 10 day);
+-----------------------------------------+
| date_add('2017-10-28', interval 10 day) |
+-----------------------------------------+
| 2017-11-07 |
+-----------------------------------------+
- date_sub(date, interval d_value_type):在date中减去日期或时间
select date_sub('2017-10-1', interval 2 day);
+---------------------------------------+
| date_sub('2017-10-1', interval 2 day) |
+---------------------------------------+
| 2017-09-29 |
+---------------------------------------+
- datediff():计算 两个日期之间的差值
mysql> select datediff('2020-5-28', '2019-7-21');
+------------------------------------+
| datediff('2020-5-28', '2019-7-21') |
+------------------------------------+
| 312 |
+------------------------------------+
1 row in set (0.00 sec)
- now():显示当前日期时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-05-28 22:27:43 |
+---------------------+
1 row in set (0.00 sec)
3.3 字符串函数
- charset():显示字符集
- concat(string, [,…]):连接字符串
- instr(string1, string2):返回 string2 在 string1 中出现的位置,没有则返回0
mysql> select instr('abcd', 'bcd');
+----------------------+
| instr('abcd', 'bcd') |
+----------------------+
| 2 |
+----------------------+
1 row in set (0.33 sec)
- ucase(string):转成大写
- lcase(string):转成小写
- left(string, length):从 string 的左边起,取 length 个字符
mysql> select left('我爱你世界', 3);
+----------------------------+
| left('我爱你世界', 3) |
+----------------------------+
| 我爱你 |
+----------------------------+
1 row in set (0.01 sec)
- length():字符串长度
- replace(str, search_str, replace_str):在 str 中用 replace_str 替换 search_str
mysql> select replace('天空是蓝色','蓝色','黑色');
+----------------------------------------------+
| replace('天空是蓝色','蓝色','黑色') |
+----------------------------------------------+
| 天空是黑色 |
+----------------------------------------------+
1 row in set (0.00 sec)
- strcmp(string1,string2):逐字符比较两字符串的大小
- substring(str, position [,length]):从 str 的 position 开始取 length 个字符
- ltrim(string),rtrim(string):去除前,后空格
3.4 数学函数
- abs():绝对值函数
select abs(-100.2);
- bin():十进制转换二进制
- hex():转换十六进制
- ceiling():向上取整
select ceiling(23.04);
- floor():向下取整
select floor(23.7);
- format():转换格式,保留小数位数
select format(12.3456, 2);
- rand():产生随机数
select rand();
3.5 其他函数
- user():查询当前用户
- md5(str):对一个字符串进行md5摘要,摘要后得到一个32位字符串
- database():显示当前正在使用的数据库
- password():MySQL数据库使用该函数对用户加密
- ifnull(val1, val2):如果val1为null,返回val2,否则返回val1的值