mysql数据类型以及各种语句的总结

陈皮糖爱悦读 2023-03-28 原文

数据类型分类

整数型，浮点型(float和double)，定点数，字符串(char,varchar,text)，日期时间类型，修饰符

字符串(char,varchar,text)

char(n)      固定长度，最多255个字符,注意不是字节
varchar(n)   可变长度，最多65535个字符
tinytext     可变长度，最多255个字符
text         可变长度，最多65535个字符
mediumtext   可变长度，最多2的24次方-1个字符
longtext     可变长度，最多2的32次方-1个字符
BINARY(M)    固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节
内建类型：ENUM枚举, SET集合

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符
串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1
个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快

varchar 和 text：

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text 是实际字符数+2个字节。

2.text类型不能有默认值

3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

二进制数据BLOB

BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，
不分大小写
BLOB存储的数据只能整体读出
TEXT可以指定字符集，BLOB不用指定字符集

日期时间类型

date      日期 '2008-12-2'
time      时间 '12:25:36'
datetime  日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp 此字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

适用所有类型的修饰符：

NULL           数据列可包含NULL值，默认值
NOT NULL       数据列不允许包含NULL值，相当于网站注册表中的 * 为必填选项
DEFAULT        默认值
PRIMARY KEY    主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY     唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET name     指定一个字符集

适用数值型的修饰符：

AUTO_INCREMENT 自动递增，适用于整数类型, 必须作用于某个 key 的字段,比如primary key UNSIGNED 无符号

范例：关于AUTO_INCREMENT

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.02 sec)

mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> use test 
Database changed
mysql> create table t1 (id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.03 sec)

#创建db1
mysql> create database db1 character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)
#修改db1
mysql> alter database db1 character set utf8 collate utf8_b
bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show table status from db1 like "t1" \G
Empty set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't1.PRIMARY'
MariaDB [testdb]> insert t1 value(null);
ERROR 167 (22003): Out of range value for column 'id' at row 1

#上面表的数据类型无法存放所有数据,修改过数据类型实现
mysql> alter table t1 modify id bigint auto_increment;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | bigint | NO   | PRI | NULL    | auto_increment |
+-------+--------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> insert t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
| 4294967296 |
+------------+
3 rows in set (0.00 sec)

DDL 语句

创建表

创建表的方法

mysql> create table student(
      id int unsigned auto_increment primary key,
      name varchar(20) not null,
      age tinyint unsigned,
    #height DECIMAL(5,2),
      gender enum ('M','F')default 'M'
      )engine=innodb auto_increment=10 default charset=utf8;
      #id字段以10初始值
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert student (name,age)values('xiaoming',20);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
+----+----------+------+--------+
1 row in set (0.00 sec)

mysql> insert student (name,age,gender)values('xiaolv',18,'F');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
| 11 | xiaolv   |   18 | F      |
+----+----------+------+--------+
2 rows in set (0.00 sec)

#创建表的格式
CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age 
tinyint UNSIGNED,PRIMARY KEY(id,name));

范例：auto_increment 属性

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)

mysql> set @@auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 3     |
+--------------------------+-------+
2 rows in set (0.01 sec)

mysql> create table autoinc1 (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into autoinc1 values (null),(null),(null),(null);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select col from autoinc1;
+-----+
| col |
+-----+
|   3 |
|  13 |
|  23 |
|  33 |
+-----+
4 rows in set (0.00 sec)

范例：时间类型

#创建test数据库
mysql> create database test;
Query OK, 1 row affected (0.01 sec)

#使用test数据库
mysql> use test
Database changed

#创建testdate表
mysql> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

#插入数据
mysql> insert testdate values(),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

#查看表
mysql> select * from testdate
    -> ;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-11-08 14:42:03 |
|  2 | 2022-11-08 14:42:03 |
|  3 | 2022-11-08 14:42:03 |
+----+---------------------+
3 rows in set (0.00 sec)


#修改其它字段,会自动更新timestamp字段
mysql> select * from testdate
    -> ;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-11-08 14:42:03 |
|  2 | 2022-11-08 14:42:03 |
|  3 | 2022-11-08 14:42:03 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> update testdate set id=5 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testdate;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-11-08 14:42:03 |
|  2 | 2022-11-08 14:42:03 |
|  5 | 2022-11-08 14:46:06 |
+----+---------------------+
3 rows in set (0.00 sec)

MariaDB [testdb1]> create table testdate (id int auto_increment primary key,date
timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);
MariaDB [testdb1]> insert testdate ()values()()();
MariaDB [testdb1]> select * from testdate;
+----+---------------------+
| id | date               |
+----+---------------------+
|  1 | 2020-09-23 08:41:43 |
|  2 | 2020-09-23 08:41:43 |
|  3 | 2020-09-23 08:41:43 |
+----+---------------------+
3 row in set (0.000 sec)

(2) 通过查询现存表创建；新表会被直接插入查询而来的数据

MariaDB [db1]> create table user select user,host,password from mysql.user;
Query OK, 4 rows affected (0.008 sec)
Records: 4 Duplicates: 0  Warnings: 0
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
| user         |
+---------------+
2 rows in set (0.000 sec)
MariaDB [db1]> desc user;
+----------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user     | char(80) | NO   |     |         |       |
| host     | char(60) | NO   |     |         |       |
| password | char(41) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+

(3) 通过复制现存的表的表结构创建，但不复制数据

MariaDB [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra         |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned   | NO   | PRI | NULL   | auto_increment |
| name   | varchar(20)         | NO   |     | NULL   |               |
| age   | tinyint(3) unsigned | YES |     | NULL   |               |
| gender | enum('M','F')       | YES |     | M       |               |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
#创建像学生表的老师表
MariaDB [db1]> create table teacher like student;
Query OK, 0 rows affected (0.006 sec)
MariaDB [db1]> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra         |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned   | NO   | PRI | NULL   | auto_increment |
| name   | varchar(20)         | NO   |     | NULL   |               |
| age   | tinyint(3) unsigned | YES |     | NULL   |               |
| gender | enum('M','F')       | YES |     | M       |               |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

范例: 创建外键表

#创建外键首先要排除同表有相同的值，如有则需要新建换个表
#换表
mysql> use db1
Database changed
#创建一个学校的表
mysql> create table school ( id int primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.05 sec)
#创建一个学校一样的老师外键表
mysql> create table teacher ( id int primary key auto_incremenment,name  varchar(10),school_id int,foreign key(school_id)references school(id));
Query OK, 0 rows affected (0.04 sec)
#对文件内容降序排序
mysql> desc school;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#对文件内容降序排序
mysql> desc teacher;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(10) | YES  |     | NULL    |                |
| school_id | int         | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#查看
mysql> show create table teacher;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8mb3_bin DEFAULT NULL,
  `school_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `school_id` (`school_id`),
  CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#对文件插入多条数据
mysql> insert school values(0,'wang'),(0,'edu');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
#筛选和学校有关的所有信息
mysql> select * from school;
+----+------+
| id | name |
+----+------+
|  1 | wang |
|  2 | edu  |
+----+------+
2 rows in set (0.00 sec)
#对文件插入多条数据
mysql> insert teacher values(0,xiaoming,1);
ERROR 1054 (42S22): Unknown column 'xiaoming' in 'field list'
mysql> insert teacher values(0,'xiaoming',1);
Query OK, 1 row affected (0.01 sec)
#对文件插入多条数据
mysql> insert teacher values(0,'xiaohong',2);
Query OK, 1 row affected (0.01 sec)
#筛选和老师有关的所有信息
mysql> select * from teacher;
+----+----------+-----------+
| id | name     | school_id |
+----+----------+-----------+
|  1 | xiaoming |         1 |
|  2 | xiaohong |         2 |
+----+----------+-----------+
2 rows in set (0.00 sec)


ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`))
mysql>

表查看

desc 查看表结构 show tables 查看表

查看表：

SHOW TABLES [FROM db_name]

查看表创建命令：

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看当前库里的所有表或指定表的状态：

SHOW TABLE STATUS [LIKE 'tbl_name']

查看支持的engine类型

SHOW ENGINES;

查看库中所有表状态

SHOW TABLE STATUS FROM db_name

#查看整个表的内容
mysql> show table status like 'student'\G
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 12
    Create_time: 2022-11-08 11:29:43
    Update_time: 2022-11-08 11:43:47
     Check_time: NULL
      Collation: utf8mb3_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)
#查看整个库的内容
mysql> SHOW TABLE STATUS FROM test\G

修改和删除表

修改表

ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name] 
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

查看修改表帮助

Help ALTER TABLE

删除表

DROP TABLE [IF EXISTS] 'tbl_name';

修改表范例

#修改表名
ALTER TABLE students RENAME s1;
#添加字段
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
#修改字段类型
ALTER TABLE s1 MODIFY phone int;
#修改字段名称和类型
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
#删除字段
ALTER TABLE s1 DROP COLUMN mobile;
#修改字符集
ALTER TABLE s1 character set utf8;
#修改数据类型和字符集
ALTER TABLE s1 change name name varchar(20) character set utf8;
#添加字段
ALTER TABLE students ADD gender ENUM('m','f');
alter table student modify is_del bool default false;
#修改字段名和类型
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 
#删除字段
ALTER TABLE students DROP age;
#查看表结构
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
#添加外键
ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键
SHOW CREATE TABLE students #查看外键名
ALTER TABLE students drop foreign key <外键名>;

DML 语句

范例: 全值插入

mysql> insert student values(0,'wang',18,default);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+--------+
| id | name | age | gender |
+----+------+------+--------+
|  1 | wang |   18 | M     |
+----+------+------+--------+
1 row in set (0.00 sec)

范例: 部分列插入

mysql> insert student(name,age)values('zhang',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert student(id,name,age)values(default,'li',19);
Query OK, 1 row affected (0.00 sec)
mysql> insert student(id,name,gender)values(null,'zhao','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
|  1 | wang |   18 | M     |
|  2 | zhang |   20 | M     |
|  3 | li   |   19 | M     |
|  4 | zhao | NULL | F     |
+----+-------+------+--------+
4 rows in set (0.00 sec)

UPDATE 语句

注意：一定要有限制条件，否则将修改所有行的指定字

可利用mysql 选项避免此错误：

mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

DELETE 语句

删除表中数据，但不会自动缩减数据文件的大小。

注意：一定要有限制条件，否则将清空表中的所有数据

如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。

TRUNCATE TABLE tbl_name;

缩减表大小

OPTIMIZE TABLE tb_name

范例: 删除数据可以使用逻辑删除，添加一个标识字段实现,删除数据即修改标识字段

mysql> alter table student add is_del bool default false;
#mysql> alter table student add is_del tinyint(1) default 0;
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra         |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL   | auto_increment |
| name   | varchar(20)     | NO   |     | NULL   |               |
| age   | tinyint unsigned | YES |     | NULL   |               |
| gender | enum('M','F')   | YES |     | M       |               |
| is_del | tinyint(1)       | YES |     | 0       |               |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

DQL 语句

单表操作

范例：简单查询

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE 't%'
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students 
select * from students where classid in (1,3,5);
select * from students where classid not in (1,3,5);

范例：记录去重

MariaDB [hellodb]> select distinct gender from students ;
+--------+
| gender |
+--------+
| M     |
| F     |
+--------+
2 rows in set (0.001 sec)
#将age和gender多个字段重复的记录去重
mysql> select distinct age,gender from students;

范例：SQL 注入入侵

create table user (id int auto_increment primary key,name varchar(20),password 
varchar(30));
insert into user (name,password) values('admin','123456'),('wang','654321');
select * from user where name='admin' and password='' or '1'='1';
select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='wang123';
select * from user where name='admin'; # ' and password='wang123';

范例: 分页查询

#只取前3个
mysql> select * from students limit 0,3;
mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M     |       2 |         3 |
|     2 | Shi Potian |  22 | M     |       1 |         7 |
|     3 | Xie Yanke   |  53 | M     |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from students limit 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M     |       1 |         7 |
|     3 | Xie Yanke |  53 | M     |       2 |        16 |
|     4 | Ding Dian |  32 | M     |       4 |         4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
# 查询第n页的数据,每页显示m条记录
mysql> select * from students limit (n-1) * m,m;

范例: 聚合函数

mysql> select sum(age)/count(*) from students where gender
 = 'M';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           33.0000 |
+-------------------+
1 row in set (0.01 sec)

mysql> select sum(age)/count(*) from students where gender = 'F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           19.0000 |
+-------------------+
1 row in set (0.00 sec)

范例：分组统计

mysql> select classid, count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量   |
+---------+--------+
|       2 |      3 |
|       1 |      4 |
|       4 |      4 |
|       3 |      4 |
|       5 |      1 |
|       7 |      3 |
|       6 |      4 |
|    NULL |      2 |
+---------+--------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,gender, count(*)   数量 from students group by
classid,gender;
+---------+--------+--------+
| classid | gender | 数量   |
+---------+--------+--------+
|    NULL | M     |      2 |
|       1 | F     |      2 |
|       1 | M     |      2 |
|       2 | M     |      3 |
|       3 | F     |      3 |
|       3 | M     |      1 |
|       4 | M     |      4 |
|       5 | M     |      1 |
|       6 | F     |      3 |
|       6 | M     |      1 |
|       7 | F     |      2 |
|       7 | M     |      1 |
+---------+--------+--------+
12 rows in set (0.001 sec)
#分组统计
select classid,avg(age) as 平均年龄  from students where classid > 3  group by 
classid having 平均年龄 >30 ;
select gender,avg(age) 平均年龄  from students group by gender having gender='M';
#多个字段分组统计
select classid,gender,count(*) 数量 from students group by classid,gender; 
select classid,gender,count(*) 数量 from students group by gender,classid;

范例: group_concat函数实现分组信息的集合

mysql> select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
| gender | group_concat(name)                                                                                                                                   
|
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
| F     | Xiao Qiao,Huang Yueying,Xi Ren,Lin Daiyu,Ren Yingying,Yue 
Lingshan,Diao Chan,Wen Qingqing,Xue Baochai,Lu Wushuang                         
          |
| M     | Tian Boguang,Sun Dasheng,Xu Xian,Ma Chao,Hua Rong,Lin Chong,Xu 
Zhu,Duan Yu,Shi Zhongyu,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi 
Potian |
+--------+-----------------------------------------------------------------------
---------------------------------------------------------------------------------
+
2 rows in set (0.00 sec)

# with rollup 分组后聚合函数统计后再做汇总
mysql> select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F     |       10 |
| M     |       15 |
| NULL   |       25 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select gender,group_concat(name) from students group by gender with
rollup;

范例: 分组统计

#注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL_MODE的值不同而不同的结果
#以下为MySQL8.0.17 的执行结果
mysql> use hellodb
mysql> select classid,count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量   |
+---------+--------+
|       2 |      3 |
|       1 |      4 |
|       4 |      4 |
|       3 |      4 |
|       5 |      1 |
|       7 |      3 |
|       6 |      4 |
|    NULL |      2 |
+---------+--------+
8 rows in set (0.00 sec)


mysql> select @@sql_mode;
+--------------------------------------------------------------------------------
---------------------------------------+
| @@sql_mode                                                                     
                                      |
+--------------------------------------------------------------------------------
---------------------------------------+
| 
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DI
VISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------
---------------------------------------+
1 row in set (0.00 sec)
#以下是Mariadb10.3.17的执行结果
MariaDB [hellodb]> select classid, count(*), stuid  from students group by
classid;
+---------+----------+-------+
| classid | count(*) | stuid |
+---------+----------+-------+
|    NULL |        2 |    24 |
|       1 |        4 |     2 |
|       2 |        3 |     1 |
|       3 |        4 |     5 |
|       4 |        4 |     4 |
|       5 |        1 |     6 |
|       6 |        4 |     9 |
|       7 |        3 |     8 |
+---------+----------+-------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select @@sql_mode;
+--------------------------------------------------------------------------------
-----------+
| @@sql_mode                                                                     
          |
+--------------------------------------------------------------------------------
-----------+

范例: 排序

#只取前3个
mysql> select * from students order by age desc limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng | 100 | M     |   NULL |     NULL |
|     3 | Xie Yanke   |  53 | M     |       2 |        16 |
|     6 | Shi Qing   |  46 | M     |       5 |     NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
#跳过前3个只显示后续的2个
mysql> select * from students order by age desc limit 3,2;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    13 | Tian Boguang |  33 | M     |       2 |     NULL |
|     4 | Ding Dian   |  32 | M     |       4 |         4 |
+-------+--------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)


select classid,sum(age) from students where classid is not null  group by
classid   order by classid;
select classid,sum(age) from students group by classid  having classid is not
null  order by classid;
select classid,sum(age) from students where classid is not null  group by
classid   order by classid limit 2,3;
#必须先过滤，再排序
select * from students where classid is not null order by gender desc, age asc ;
#多列排序
select * from students order by gender desc, age asc;

范例：正序排序时将NULL记录排在最后

#对classid 正序排序，NULL记录排在最后
select * from students order by -classid desc ;

#分组后再排序
MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is
not null group by gender,classid order by gender,classid;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F     |       1 |  19.0000 |
| F     |       3 |  18.3333 |
| F     |       6 |  20.0000 |
| F     |       7 |  18.0000 |
| F     |      77 |  18.0000 |
| F     |      93 |  18.0000 |
| M     |       1 |  21.5000 |
| M     |       2 |  35.2000 |
| M     |       3 |  23.0000 |
| M     |       4 |  23.6000 |
| M     |       5 |  46.0000 |
| M     |       6 |  23.0000 |
| M     |       7 |  23.0000 |
| M     |      94 |  18.0000 |
+--------+---------+----------+
14 rows in set (0.001 sec)
#有年龄的正序排列前10个
MariaDB [hellodb]> select * from students order by age limit 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    14 | Lu Wushuang   |  17 | F     |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F     |       7 |      NULL |
|    33 | Miejue Shitai |  18 | F     |      77 |      NULL |
|    32 | Zhang Sanfeng |  18 | M     |      94 |      NULL |
|    27 | liudehua     |  18 | F     |       1 |      NULL |
|    34 | Lin Chaoying |  18 | F     |      93 |      NULL |
|    19 | Xue Baochai   |  18 | F     |       6 |      NULL |
|     7 | Xi Ren       |  19 | F     |       3 |      NULL |
|    12 | Wen Qingqing |  19 | F     |       1 |      NULL |
|    20 | Diao Chan     |  19 | F     |       7 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.001 sec)
#由不同的年龄排序取前三个
MariaDB [hellodb]> select distinct age  from students order by age limit 3 ;
+-----+
| age |
+-----+
|  17 |
|  18 |
|  19 |
+-----+
3 rows in set (0.001 sec)
MariaDB [hellodb]> select distinct age  from students order by age limit 3,5 ;
+-----+
| age |
+-----+
|  20 |
|  21 |
|  22 |
|  23 |
|  25 |
+-----+
5 rows in set (0.001 sec)

范例: 分组和排序的次序

#顺序: group by,having,order by
mysql> select classid,count(*) from students group by classid having classid is
not null  order by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
|       1 |        4 |
|       2 |        3 |
|       3 |        4 |
|       4 |        4 |
|       5 |        1 |
|       6 |        4 |
|       7 |        3 |
+---------+----------+
7 rows in set (0.00 sec)

以下排序会造成错误排序

#以下顺序会出错,group by,order by,having
mysql> select classid,count(*) from students group by classid  order by classid 
having classid is not null;
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 
'having classid is not null' at line 1
#以下顺序会出错,order by,group by,having
mysql> select classid,count(*) from students order by classid group by classid 
having classid is not null; 
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 'group 
by classid having classid is not null' at line 1

范例：时间字段进行过滤查询,并且timestamp可以随其它字段的更新自动更新

MariaDB [testdb]>  create table testdate (id int auto_increment primary key,date
timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
MariaDB [testdb]>  insert testdate () values();
MariaDB [testdb]>  insert testdate values(),(),();
MariaDB [testdb]> select * from testdate;
+----+---------------------+
| id | date               |
+----+---------------------+
|  1 | 2020-06-03 15:21:03 |
|  2 | 2020-06-03 15:21:12 |
|  3 | 2020-06-03 15:21:14 |
|  4 | 2020-06-03 15:21:17 |
|  5 | 2020-06-03 18:27:39 |
|  6 | 2020-06-03 18:27:44 |
+----+---------------------+
6 rows in set (0.001 sec)
MariaDB [testdb]> select * from testdate where date between '2020-06-03 
15:21:12' and '2020-06-03 18:27:40';
+----+---------------------+
| id | date               |
+----+---------------------+
|  2 | 2020-06-03 15:21:12 |
|  3 | 2020-06-03 15:21:14 |
|  4 | 2020-06-03 15:21:17 |
|  5 | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> select * from testdate where date >= '2020-06-03 15:21:12'
and date <= '2020-06-03 18:27:40';
+----+---------------------+
| id | date               |
+----+---------------------+
|  2 | 2020-06-03 15:21:12 |
|  3 | 2020-06-03 15:21:14 |
|  4 | 2020-06-03 15:21:17 |
|  5 | 2020-06-03 18:27:39 |
+----+---------------------+
4 rows in set (0.001 sec)
#修改其它字段,会自动更新timestamp字段
mysql> update testdate set id=10 where id=1;
mysql> select * from testdate3;
+----+---------------------+
| id | date               |
+----+---------------------+
|  2 | 2020-06-03 15:21:12 |
|  3 | 2020-06-03 15:21:14 |
|  4 | 2020-06-03 15:21:17 |
|  5 | 2020-06-03 18:27:39 |
|  6 | 2020-06-03 18:27:44 |
| 10 | 2020-06-03 18:34:51 |
+----+---------------------+
6 rows in set (0.001 sec)

有关mysql数据类型以及各种语句的总结的更多相关文章

ruby - 什么是填充的 Base64 编码字符串以及如何在 ruby 中生成它们？ - 2
我正在使用的第三方API的文档状态:"[O]urAPIonlyacceptspaddedBase64encodedstrings."什么是“填充的Base64编码字符串”以及如何在Ruby中生成它们。下面的代码是我第一次尝试创建转换为Base64的JSON格式数据。xa=Base64.encode64(a.to_json) 最佳答案他们说的padding其实就是Base64本身的一部分。它是末尾的“=”和“==”。Base64将3个字节的数据包编码为4个编码字符。所以如果你的输入数据有长度n和n%3=1=>"=="末尾用于填充n%
ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2
我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i
ruby - Infinity 和 NaN 的类型是什么？ - 2
我可以得到Infinity和NaNn=9.0/0#=>Infinityn.class#=>Floatm=0/0.0#=>NaNm.class#=>Float但是当我想直接访问Infinity或NaN时:Infinity#=>uninitializedconstantInfinity(NameError)NaN#=>uninitializedconstantNaN(NameError)什么是Infinity和NaN？它们是对象、关键字还是其他东西？最佳答案您看到打印为Infinity和NaN的只是Float类的两个特殊实例的字符串
ruby - 检查方法参数的类型 - 2
我不确定传递给方法的对象的类型是否正确。我可能会将一个字符串传递给一个只能处理整数的函数。某种运行时保证怎么样？我看不到比以下更好的选择:defsomeFixNumMangler(input)raise"wrongtype:integerrequired"unlessinput.class==FixNumother_stuffend有更好的选择吗？最佳答案使用Kernel#Integer在使用之前转换输入的方法。当无法以任何合理的方式将输入转换为整数时，它将引发ArgumentError。defmy_method(number)
ruby - Ruby 有 `Pair` 数据类型吗？ - 2
有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？最佳
ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2
我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s
ruby-on-rails - 在 Rails 开发环境中为 .ogv 文件设置 Mime 类型 - 2
我正在玩HTML5视频并且在ERB中有以下片段:mp4视频从在我的开发环境中运行的服务器很好地流式传输到chrome。然而firefox显示带有海报图像的视频播放器，但带有一个大X。问题似乎是mongrel不确定ogv扩展的mime类型，并且只返回text/plain，如curl所示:$curl-Ihttp://0.0.0.0:3000/pr6.ogvHTTP/1.1200OKConnection:closeDate:Mon,19Apr201012:33:50GMTLast-Modified:Sun,18Apr201012:46:07GMTContent-Type:text/plain
ruby - 是否有用于序列化和反序列化各种格式的对象层次结构的模式？ - 2
给定一个复杂的对象层次结构，幸运的是它不包含循环引用，我如何实现支持各种格式的序列化？我不是来讨论实际实现的。相反，我正在寻找可能会派上用场的设计模式提示。更准确地说:我正在使用Ruby，我想解析XML和JSON数据以构建复杂的对象层次结构。此外，应该可以将该层次结构序列化为JSON、XML和可能的HTML。我可以为此使用Builder模式吗？在任何提到的情况下，我都有某种结构化数据-无论是在内存中还是文本中-我想用它来构建其他东西。我认为将序列化逻辑与实际业务逻辑分开会很好，这样我以后就可以轻松支持多种XML格式。最佳答案我最
ruby - 如何在 Ruby 中向现有方法定义添加语句 - 2
我注意到类定义，如果我打开classMyClass，并在不覆盖的情况下添加一些东西我仍然得到了之前定义的原始方法。添加的新语句扩充了现有语句。但是对于方法定义，我仍然想要与类定义相同的行为，但是当我打开defmy_method时似乎,def中的现有语句和end被覆盖了，我需要重写一遍。那么有什么方法可以使方法定义的行为与定义相同，类似于super，但不一定是子类？最佳答案我想您正在寻找alias_method:classAalias_method:old_func,:funcdeffuncold_func#similartoca
ruby - 我如何添加二进制数据来遏制 POST - 2
我正在尝试使用Curbgem执行以下POST以解析云curl-XPOST\-H"X-Parse-Application-Id:PARSE_APP_ID"\-H"X-Parse-REST-API-Key:PARSE_API_KEY"\-H"Content-Type:image/jpeg"\--data-binary'@myPicture.jpg'\https://api.parse.com/1/files/pic.jpg用这个:curl=Curl::Easy.new("https://api.parse.com/1/files/lion.jpg")curl.multipart_form_