tinyint 数据类型
mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql>
mysql> select * from tt1;
+------+
| num |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
unsigned tinyint 数据类型
mysql> create table t2(num tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(255);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+
| num |
+------+
| 255 |
+------+
1 row in set (0.00 sec)
- 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的
- 可以通过unsigend来说明某个字段是无符号的
bit类型
bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。
mysql> create table t3 (id int, a bit(8));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(10,10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | a |
+------+------+
| 10 |
|
+------+------+
1 row in set (0.00 sec)
mysql> insert into t3 values(66,66);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+------+
| id | a |
+------+------+
| 10 |
|
| 66 | B |
+------+------+
2 rows in set (0.00 sec)
#测试1个字节 gender
mysql> create table t5(gender bit(1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t5 values(0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values(2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1
mysql>
- bit字段在显示时，是按照ASCII码对应的值显示
- 只存放0或1，这时可以定义bit(1)。这样可以节省空间
float
float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
mysql> create table t6(id int,num float(4,1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t6 values(10,88.8);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 values(10,88.88);
Query OK, 1 row affected (0.00 sec)
mysql> select *from t6;
+------+------+
| id | num |
+------+------+
| 10 | 88.8 |
| 10 | 88.9 |
+------+------+
2 rows in set (0.00 sec)
- 截断最后多出的一位,四舍五入
- float(4,1) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.9
- float表示的精度大约是7位
dicimal
decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数
mysql> create table t8 (id int,num float(10,8),num2 decimal(10,8));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t8 values(100,23.12345678,23.12345678);
Query OK, 1 row affected (0.00 sec)
mysql> select *from t8;
+------+-------------+-------------+
| id | num | num2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345678 |
+------+-------------+-------------+
1 row in set (0.00 sec)
- deciaml比float类型精度高
char
char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255
mysql> create table t9(id int,name char(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t9 values(1,'kkk');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9 values(1,'小锋6');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t9;
+------+---------+
| id | name |
+------+---------+
| 1 | kkk |
| 1 | 小锋6 |
+------+---------+
2 rows in set (0.00 sec)
- char字节大小为2 可以存放0~255个字符
varchar
varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节
mysql> create table t10 (id int,name varchar(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t10 values(1,'xxlu');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values(1,'中国万岁');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values(1,'中国万岁！');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t10;
+------+--------------+
| id | name |
+------+--------------+
| 1 | xxlu |
| 1 | 中国万岁 |
+------+--------------+
2 rows in set (0.00 sec)
- varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字 节数是65532。
- 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占 用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符 占用2字节）。
日期和时间类型
- date :日期 ‘yyyy-mm-dd’ ，占用三字节
- datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用字节
- timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节
mysql> create table birthday (t1 date,t2 datetime,t3 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into birthday(t1,t2)values('2003-10-10','2003-10-10 10:10:10');
Query OK, 1 row affected (0.01 sec)
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2003-10-10 | 2003-10-10 10:10:10 | 2024-04-01 09:29:39 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> update birthday set t1='2003-10-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2003-10-01 | 2003-10-10 10:10:10 | 2024-04-01 09:30:58 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)
enum和set
- enum：单选类型； enum(‘选项1’,‘选项2’,‘选项3’,…); --枚举
- set：多选类型； set(‘选项值1’,‘选项值2’,‘选项值3’, …); --集合
mysql> create table v1(name varchar(32),hobby set('篮球','足球','乒乓球'),
gender enum('男','女'));
Query OK, 0 rows affected (0.01 sec)
mysql> desc v1;
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| name | varchar(32) | YES | | NULL | |
| hobby | set('篮球','足球','乒乓球') | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
+--------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into v1 values('小锋','乒乓球','男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into v1 values('康康','乒乓球,篮球','2');
Query OK, 1 row affected (0.00 sec)
mysql> select *from v1 where gender=2;
+--------+------------------+--------+
| name | hobby | gender |
+--------+------------------+--------+
| 康康 | 篮球,乒乓球 | 女 |
+--------+------------------+--------+
1 row in set (0.00 sec)
mysql> select *from v1 where hobby='篮球';
Empty set (0.00 sec)
find_in_set函数
- find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串
mysql> select find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.01 sec)
mysql> select find_in_set('k','a,b,c');
+--------------------------+
| find_in_set('k','a,b,c') |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select *from v1 where find_in_set('篮球',hobby);
+--------+------------------+--------+
| name | hobby | gender |
+--------+------------------+--------+
| 康康 | 篮球,乒乓球 | 女 |
+--------+------------------+--------+
1 row in set (0.00 sec)
mysql> select *from v1 where find_in_set('排球',hobby);
Empty set (0.00 sec)
表的约束
空属性
mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> select 1+null;
+--------+
| 1+null |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> create table v2(
-> id int,
-> name varchar(32);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> create table v2( id int, name varchar(32));
Query OK, 0 rows affected (0.00 sec)
mysql> create table v3( id int not null, name varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> select *from v3;
Empty set (0.00 sec)
mysql> desc v3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table v3 add age enum('男','女');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc v3;
+-------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(32) | NO | | NULL | |
| age | enum('男','女') | YES | | NULL | |
+-------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
默认值
mysql> create table v4(id int,name varchar(32),age tinyint unsigned default 0,sex char(2) default '男');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into v4(name) values('张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from v4;
+------+--------+------+------+
| id | name | age | sex |
+------+--------+------+------+
| NULL | 张三 | 0 | 男 |
+------+--------+------+------+
1 row in set (0.00 sec)
- 只有设置了default的列，才可以在插入值的时候，对列进行省略
列表描述
mysql> create table v5(id int,name varchar(32) not null comment '姓名'
,age tinyint unsigned default 0 comment '年龄'
,sex char(2) default '男' comment '性别');
Query OK, 0 rows affected (0.01 sec)
mysql> desc v5;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| sex | char(2) | YES | | 男 | |
+-------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show create table v5\G
*************************** 1. row ***************************
Table: v5
Create Table: CREATE TABLE `v5` (
`id` int(11) DEFAULT NULL,
`name` varchar(32) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',
`sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 通过mysql> show create table v5\G 查看列表描述
zerofill
mysql> create table v6(a int unsigned default null,b int unsigned default null)engine=myisam default charset=gbk
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into v6 values(1,2);
Query OK, 1 row affected (0.01 sec)
mysql> select *from v6;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> alter table v6 change a a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table v6\G;
*************************** 1. row ***************************
Table: v6
Create Table: CREATE TABLE `v6` (
`a` int(5) unsigned zerofill DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> select *from v6;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+
1 row in set (0.00 sec)
mysql> select *from v6;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+
1 row in set (0.00 sec)
mysql> select a,hex(a) from v6;
+-------+--------+
| a | hex(a) |
+-------+--------+
| 00001 | 1 |
+-------+--------+
1 row in set (0.00 sec)
- zerofill 格式化输出 不够输出大小则添加前置0
主键(primary key)
- 主键：primary key
- 每个表只有一个主键，不能重复，不能为空
alter table 表名 drop primary key;
alter table 表名 add primary key(字段列表)
mysql> create table v7(id int unsigned primary key comment '学号必填',name varchar(32) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> desc v7;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into v7 values(1,'kk');
Query OK, 1 row affected (0.00 sec)
mysql> insert into v7 values(1,'kk');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#删除主键
mysql> alter table v7 drop primary key;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc v7;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#添加主键
mysql> alter table v7 add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
复合主键
- 多个字段名称设置为主键
mysql> create table v9(id int unsigned ,course char(10) comment '课堂代码',score tinyint unsigned default 60 comment '成绩',primary key(id,course));
Query OK, 0 rows affected (0.00 sec)
mysql> desc v7;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc v9;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| course | char(10) | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into v9 (id,course) values(1,'kk');
Query OK, 1 row affected (0.00 sec)
mysql> insert into v9 (id,course) values(1,'kk');
ERROR 1062 (23000): Duplicate entry '1-kk' for key 'PRIMARY'
自增长（auto_increment）
- 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
- 自增长字段必须是整数
- 一张表最多只能有一个自增长
select last_insert_id();# 查看最后一个自增的下标
mysql> create table v10(id int unsigned primary key auto_increment,
name varchar(32) not null default '');
Query OK, 0 rows affected (0.00 sec)
mysql> insert into v10(name) values('1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into v10(name) values('2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into v10(name) values('3');
Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
唯一键（unique）
- 唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
mysql> create table student(id char(10) unique comment '学号不能重复，但是可以为空',
name varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student(id,name) values('01','aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id,name) values('01','bbb');
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student(id,name) values(null,'bbb');
Query OK, 1 row affected (0.00 sec)
mysql> select *from student;
+------+------+
| id | name |
+------+------+
| 01 | aaa |
| NULL | bbb |
+------+------+
2 rows in set (0.00 sec)
外键约束（foreign key）
- 减少数据冗余
mysql> create table students(id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key(class_id) references class(id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into class values(10,'c++'),(20,'java'),(30,'python');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into students values(100,'zhangsan',10),(101,'lisi',20),(102,'wangwu',30);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from class;
+----+--------+
| id | name |
+----+--------+
| 10 | c++ |
| 20 | java |
| 30 | python |
+----+--------+
3 rows in set (0.00 sec)
mysql> select *from students;
+-----+----------+----------+
| id | name | class_id |
+-----+----------+----------+
| 100 | zhangsan | 10 |
| 101 | lisi | 20 |
| 102 | wangwu | 30 |
+-----+----------+----------+
3 rows in set (0.00 sec)
实战案例
- 商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商 provider)
- 客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
- 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)
要求：
每个表的主外键 、客户的姓名不能为空值 、邮箱不能重复 、客户的性别(男，女)
mysql> create database if not exists test5;
Query OK, 1 row affected (0.00 sec)
mysql> use test5;
Database changed
mysql> create table goods(goods_id int(10) primary key comment '商品编号',
goods_name varchar(32),unitprice int,
category varchar(32),provider varchar(32));
Query OK, 0 rows affected (0.00 sec)
mysql> create table customer(
-> customer_id int primary key,
-> name varchar(32) not null,
-> address varchar(32) ,
-> email varchar(32) unique,
-> sex enum('男','女')，
-> card_id char(18));
mysql> desc goods;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| goods_id | char(10) | NO | PRI | NULL | |
| goods_name | varchar(32) | YES | | NULL | |
| unitprice | int(11) | YES | | NULL | |
| category | varchar(32) | YES | | NULL | |
| provider | varchar(32) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> create table if not exists customer( customer_id int primary key auto_increment,
name varchar(32) not null, address varchar(32) ,
email varchar(32) unique key ,sex varchar(2) not null,
card_id varchar(18) unique key );
Query OK, 0 rows affected (0.00 sec)
mysql> create table if not exists purchase(
-> order_id int primary key auto_increment ,
-> customer_id int ,
-> goods_id int,
-> nums int,
-> foreign key (customer_id) references customer(customer_id),
-> foreign key (goods_id) references goods(goods_id));
mysql> create table if not exists purchase
( order_id int primary key auto_increment ,
customer_id int ,
goods_id int, nums int,
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id));
Query OK, 0 rows affected (0.01 sec)
表的增删改查
创建（create）
mysql> create table v12(
-> id int unsigned primary key auto_increment,
-> sn int not null unique ,
-> name varchar(32) not null,
-> wechat varchar(32));
单行插入
mysql> insert into v12 values(100,1000,'张三',null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into v12 values(101,1001,'李四','12345');
Query OK, 1 row affected (0.00 sec)
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 101 | 1001 | 李四 | 12345 |
+-----+------+--------+--------+
2 rows in set (0.00 sec)
多行插入
ysql> insert into v12 (id,sn,name) values (102,1002,'王五'),(103,1003,'康康');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 101 | 1001 | 李四 | 12345 |
| 102 | 1002 | 王五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
插入否则更新（on duplicate key update）
- 当主键或者唯一键冲突时就需要更新操作
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 101 | 1001 | 李四 | 12345 |
| 102 | 1002 | 王五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
mysql> insert into v12 (id , sn,name) values (102,1002,'陈五')
on duplicate key update sn =1005,name = '陈五';
Query OK, 2 rows affected (0.00 sec)
mysql> select row_count();#改变的行数
+-------------+
| row_count() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
#修改后
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 101 | 1001 | 李四 | 12345 |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
替换（REPLACE）
- 当主键或者唯一键冲突时，直接删除并插入；否则直接插入
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 101 | 1001 | 李四 | 12345 |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
mysql> replace into v12(sn,name,wechat) values (1001,'lisi','00000');
Query OK, 2 rows affected (0.00 sec)
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
搜索
全列查询（数量大时不建议用）
- 查询的列越多，意味着需要传输的数据量越大
- 可能会影响到索引的使用。
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
指定列查询
mysql> select id,name,wechat from v12;
+-----+--------+--------+
| id | name | wechat |
+-----+--------+--------+
| 100 | 张三 | NULL |
| 102 | 陈五 | NULL |
| 103 | 康康 | NULL |
| 104 | lisi | 00000 |
+-----+--------+--------+
4 rows in set (0.00 sec)
查询字段为表达式
mysql> select id,name,10 from v12;
+-----+--------+----+
| id | name | 10 |
+-----+--------+----+
| 100 | 张三 | 10 |
| 102 | 陈五 | 10 |
| 103 | 康康 | 10 |
| 104 | lisi | 10 |
+-----+--------+----+
4 rows in set (0.00 sec)
求和后查询
mysql> select id,name,wechat+10 from v12;
+-----+--------+-----------+
| id | name | wechat+10 |
+-----+--------+-----------+
| 100 | 张三 | NULL |
| 102 | 陈五 | NULL |
| 103 | 康康 | NULL |
| 104 | lisi | 10 |
+-----+--------+-----------+
4 rows in set (0.00 sec)
查询多个字段
mysql> select id+name+wechat from v12;
+----------------+
| id+name+wechat |
+----------------+
| NULL |
| NULL |
| NULL |
| 104 |
+----------------+
4 rows in set, 4 warnings (0.00 sec)
指定列
mysql> select wechat from v12;
+--------+
| wechat |
+--------+
| NULL |
| NULL |
| NULL |
| 00000 |
+--------+
4 rows in set (0.00 sec)
结果去重
mysql> select distinct wechat from v12;
+--------+
| wechat |
+--------+
| NULL |
| 00000 |
+--------+
2 rows in set (0.00 sec)
where条件
- id>100
mysql> select * from v12 where id>100;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
- id>100 and <=103
mysql> select * from v12 where id>100 and id<=103;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
2 rows in set (0.00 sec)
between(包含当前数值)
mysql> select * from v12 where id between 100 and 103;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
+-----+------+--------+--------+
3 rows in set (0.00 sec)
or
mysql> select name from v12 where id =100 or id=102;
+--------+
| name |
+--------+
| 张三 |
| 陈五 |
+--------+
2 rows in set (0.00 sec)
in
mysql> select id,name from v12 where id in(102,103);
+-----+--------+
| id | name |
+-----+--------+
| 102 | 陈五 |
| 103 | 康康 |
+-----+--------+
2 rows in set (0.00 sec)
匹配字符
匹配一个字符
mysql> select name from v12 where name like '陈_';
+--------+
| name |
+--------+
| 陈五 |
+--------+
匹配多个字符
mysql> select name from v12 where name like 'l%';
+------+
| name |
+------+
| lisi |
+------+
查询不为空
mysql> select name,wechat from v12 where wechat is not null;
+------+--------+
| name | wechat |
+------+--------+
| lisi | 00000 |
+------+--------+
结果排序(order by)
- order by 子句不做排序
- ACS 升序 DESC降序
mysql> select name,wechat from v12 order by name;
+--------+--------+
| name | wechat |
+--------+--------+
| lisi | 00000 |
| 康康 | NULL |
| 张三 | NULL |
| 陈五 | NULL |
+--------+--------+
4 rows in set (0.00 sec)
mysql> select name,wechat from v12 order by name desc;
+--------+--------+
| name | wechat |
+--------+--------+
| 陈五 | NULL |
| 张三 | NULL |
| 康康 | NULL |
| lisi | 00000 |
+--------+--------+
4 rows in set (0.00 sec)
mysql> select name,wechat from v12 order by name asc;
+--------+--------+
| name | wechat |
+--------+--------+
| lisi | 00000 |
| 康康 | NULL |
| 张三 | NULL |
| 陈五 | NULL |
+--------+--------+
4 rows in set (0.00 sec)
筛选分页结果
- 查找2个id【id limit 2】
- 【offset 2】2个id组成一页
select id ,name from v12 order by id limit 2 offset 0;
+-----+--------+
| id | name |
+-----+--------+
| 100 | 张三 |
| 102 | 陈五 |
+-----+--------+
mysql> select id ,name from v12 order by id limit 2 offset 2;
+-----+--------+
| id | name |
+-----+--------+
| 103 | 康康 |
| 104 | lisi |
+-----+--------+
更新（update）
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 100 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
mysql> update v12 set id =101 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 101 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
删除(Delete)
删除数据
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 101 | 1000 | 张三 | NULL |
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
4 rows in set (0.00 sec)
mysql> delete from v12 where id = 101;
Query OK, 1 row affected (0.00 sec)
mysql> select *from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
3 rows in set (0.00 sec)
删除表
delete from v12; #自行测试
截断表
mysql> CREATE TABLE for_truncate (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into for_truncate(name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)
#截断表时不会对任何行进行破坏
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from for_truncate;
Empty set (0.00 sec)
mysql> insert into for_truncate(name) values ('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
聚合函数
| 函数 | 说明 |
|---|---|
| COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
mysql> select * from v12;
+-----+------+--------+--------+
| id | sn | name | wechat |
+-----+------+--------+--------+
| 102 | 1005 | 陈五 | NULL |
| 103 | 1003 | 康康 | NULL |
| 104 | 1001 | lisi | 00000 |
+-----+------+--------+--------+
3 rows in set (0.00 sec)
mysql> select count(id) from v12;
+-----------+
| count(id) |
+-----------+
| 3 |
+-----------+
分组查询（group by）
搜索优先级
SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select
> distinct > order by > limit