1，DDL(Data Definition Language) 数据定义语言 ：
create，drop，alter
2，DML(Data Manipulation Language) 数据操控语言 insert，update，delete
3，DQL(Structure Query Language) 数据查询语言
select
4，DCL(Data Control Languag) 数据控制语言
grant，revoke，commit，rollback
搜索引擎概念：
搜索引擎概念：
表的类型==> 默认innodb
查看支持的搜索引擎
mysql> show engines\G
查看正在使用的搜索引擎
mysql> show variables like 'storage_engine%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
一，DDL 数据定义语言
1，create
(1) create创建库
1,查询创建库 语法
mysql> help create database
2.1，创建库t1
mysql> create database t1;
Query OK, 1 row affected (0.02 sec)
2.2，创建库t1 （如果t1库存在，防止报错）
mysql> create database if not exists t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
2.3，创建库t1 指定字符集
mysql> create database if not exists t1 character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
3, 查看t1库语句
mysql> show create database t1;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
4, 查看当前的库
mysql> use t1;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| t1 |
+------------+
1 row in set (0.00 sec)
5，删除t1库
mysql> drop database t1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
接上：
mysql> create database t2;
mysql> create database if not exists t1 character set utf8;
区别：
1，
mysql> create database t2;
mysql> use t2
mysql> create table t2(id int,name varchar(10),gender enum('男','女'));
ERROR 1291 (HY000): Column 'gender' has duplicated value '?' in ENUM
# 这里有男女的中文字符会报错
2，
mysql> create database t1;
mysql> use t1
mysql> create table t1(id int,name char(10),gender enum('男','女'),cometime datetime);
Query OK, 0 rows affected (0.01 sec)
# 成功
(2) create创建表的字段及属性
一，创建表注意⚠️：
1，创建表的时候指定数据库；
2，创建表的时候要包含一个字段；
3，创建表的时候，每个字段必须指定字段类型
二，字段类型：
int 整数类型 范围（ -2*31 ～ 2*31 ）
tinyint 最小整数 范围（ -127 ～ 127 ） 年龄
char 固定长度 身份证，手机号等；比如varchar（10）,最长长度是10，实际数据如果是2，磁盘也占10的大小）
varchar 可变长度 （比如varchar（10）,最长长度是10，实际数据如果是2，磁盘只占2的大小）
enum 枚举类型 少数取值内容 性别，状态信息等（比如淘宝待支付，待发货，待评价）
datetime 时间类型
char与varchar，大多数情况下varchar省空间
当char(5)与varchar(5)的情况下，char更省空间，varchar会默认多加一个字符作为头（存储机制）
验证：
mysql> insert into q1 value('ab');
Query OK, 1 row affected (0.00 sec)
mysql> insert into q2 value('ab');
Query OK, 1 row affected (0.00 sec)
# 查看字符
mysql> select char_length(name) from q1;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec)
mysql> select char_length(name) from q2;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec)
# mysql底层默认处理机制重新设置
mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
# 再次查看
mysql> select char_length(name) from q2;
+-------------------+
| char_length(name) |
+-------------------+
| 2 |
+-------------------+
1 rows in set (0.00 sec)
mysql> select char_length(name) from q1;
+-------------------+
| char_length(name) |
+-------------------+
| 4 |
+-------------------+
1 rows in set (0.00 sec)
补充：
char类型，=查询，会忽略空格，用like查询不会忽略空格
比如name char(4),输入数据为 'a ' (a后面有三个空格)
select * from s1 where name='a'或者 ='a '都可以查到
但是select * from s1 where name like 'a' 不输入后面的空格就会查不到
三，字段属性
primary key 主键 （唯一标识符）
auto_increment 自增长 （比如序号，自动1234...可以为该字段生成不重复的唯一的id标识）
unique key 唯一 身份证，手机号 （限制除了主键外的其它字段内的数据不能重复）
unsigned 非负数
not null 非空 （比如，注册信息中必填项）
default 属性 （当没有指定字段的值，mysql会使用该字段默认的值填充）
comment 备注
not null unique 相当于 primary key
如果字段既没有not null unique 也没用primary key，innodb机制会默认建隐藏字段（不会有加速查询效果）
(3) create创建表
mysql> use t1
Database changed
mysql> create table t1(id int,name char(10),gender enum('男','女'),cometime datetime);
Query OK, 0 rows affected (0.01 sec)
# 查询表的字段及字段类型
mysql> desc t1;
+----------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| gender | enum('男','女') | YES | | NULL | |
| cometime | datetime | YES | | NULL | |
+----------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 删除表t2
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
(4) create加上属性创建表
#建测试库
mysql> create database t4;
Query OK, 1 row affected (0.00 sec)
#指定库
mysql> use t4
Database changed
#建测试表
mysql> create table test(
-> id int primary key auto_increment,
-> name varchar(10) not null,
-> age tinyint unsigned not null,
-> id_card char(18) not null unique key,
-> hobby varchar(20) comment '爱好');
#查看建表语句
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`id_card` char(18) NOT NULL,
`hobby` varchar(20) DEFAULT NULL COMMENT '爱好',
PRIMARY KEY (`id`),
UNIQUE KEY `id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(5) create复制表
1,复制表格
mysql> select host,userpassword from mysql.user;
ERROR 1054 (42S22): Unknown column 'userpassword' in 'field list'
mysql> select host,user,password from mysql.user;
+------------+--------+-------------------------------------------+
| host | user | password |
+------------+--------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| db01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| db01 | | |
| 172.16.1.% | wp | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 172.16.1.% | zh | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 172.16.1.% | dz | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| % | yunwei | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost | yunwei | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost | py | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 172.16.1.% | py | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| % | yy | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------------+--------+-------------------------------------------+
14 rows in set (0.01 sec)
# 复制以上表格
mysql> create table t6 select user,host,password from mysql.user;
Query OK, 14 rows affected (0.07 sec)
Records: 14 Duplicates: 0 Warnings: 0
#查询验证
mysql> select * from t6;
+--------+------------+-------------------------------------------+
| user | host | password |
+--------+------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | db01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db01 | |
| wp | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| zh | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| dz | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| yunwei | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| yunwei | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| py | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| py | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| yy | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+--------+------------+-------------------------------------------+
14 rows in set (0.00 sec)
2，只复制表格的表结构（后面加上假条件）
mysql> create table t8 select user,host,password from mysql.user where 1<0;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看表结构
mysql> select * from t8;
Empty set (0.00 sec)
mysql> desc t8;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user | char(16) | NO | | | |
| host | char(60) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(6) 字段类型_数字类型 float， double，decimal
# 创建浮点数表
mysql> create table t9(x float(255,30));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t9;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| x | float(255,30) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
# 插入数据
mysql> insert into t9 values(1.12344);
Query OK, 1 row affected (0.00 sec)
#增加列数（表结构）
mysql> alter table t9 add id int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t9 add name varchar(8) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查询数据表
mysql> select * from t9;
+------+----------------------------------+------+
| name | x | id |
+------+----------------------------------+------+
| NULL | 1.123440027236938500000000000000 | NULL |
+------+----------------------------------+------+
1 row in set (0.00 sec)
# 创建double，decimal表
mysql> create table t13(id int,x double(255,30));
Query OK, 0 rows affected (0.01 sec)
mysql> create table t14(name varchar(10),score decimal(65,30));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t13;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| x | double(255,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc t14;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| score | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入数据
mysql> insert into t13 value(1,1.6786578875785857),(2,87868787556);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t13;
+------+--------------------------------------------+
| id | x |
+------+--------------------------------------------+
| 1 | 1.678657887578585800000000000000 |
| 2 | 87868787556.000000000000000000000000000000 |
+------+--------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into t14 values('a',76.87767768),('b',87.6467376473);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t14;
+------+-----------------------------------+
| name | score |
+------+-----------------------------------+
| a | 76.877677680000000000000000000000 |
| b | 87.646737647300000000000000000000 |
+------+-----------------------------------+
2 rows in set (0.01 sec)
总结：
mysql> create table t9(x float(255,30));
mysql> create table t13(id int,x double(255,30));
mysql> create table t14(name varchar(10),score decimal(65,30));
float支持个位数255位，小数点位30位；
精确程度float < double < decimal
(7) 字段类型_日期类型
1，日期类型使用
# 建表
mysql> create table student(id int,name varchar(10),birth date,class_time time,regtime datetime,born_time year);
# 查看表结构
mysql> desc student; +------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| birth | date | YES | | NULL | |
| class_time | time | YES | | NULL | |
| regtime | datetime | YES | | NULL | |
| born_time | year(4) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
# 插入数据
mysql> insert into student values(1,'tom',"1993-10-7","08:30:00","2010-3-3 09:30","1993");
Query OK, 1 row affected (0.00 sec)
# 查看表信息
mysql> select * from student; +------+------+------------+------------+---------------------+-----------+
| id | name | birth | class_time | regtime | born_time |
+------+------+------------+------------+---------------------+-----------+
| 1 | tom | 1993-10-07 | 08:30:00 | 2010-03-03 09:30:00 | 1993 |
+------+------+------------+------------+---------------------+-----------+
2，timestamp可不填入时间，自动默认非空
# 建表 数字类型为datetime 和 timestamp
mysql> create table s1(id int,cometime datetime);
mysql> create table s2(id int,cometime timestamp);
# datetime 插入时间数据
mysql> insert into s1 values(1,"2018-09-12 13:21");
Query OK, 1 row affected (0.00 sec)
# timestamp只插入id
mysql> insert into s2(id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from s1;
+------+---------------------+
| id | cometime |
+------+---------------------+
| 1 | 2018-09-12 13:21:00 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from s2;
+------+---------------------+
| id | cometime |
+------+---------------------+
| 1 | 2021-03-10 17:30:11 |
+------+---------------------+
1 row in set (0.00 sec)
以上可看出，timestamp自动填入时间数据
3, datestamp 修改内容会自动更新时间
mysql> select * from s2;
+------+------+---------------------+
| id | name | cometime |
+------+------+---------------------+
| 1 | abc | 2021-03-10 17:38:47 |
+------+------+---------------------+
1 row in set (0.00 sec)
mysql> update s2 set name='cc' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from s2;
+------+------+---------------------+
| id | name | cometime |
+------+------+---------------------+
| 1 | cc | 2021-03-10 17:39:13 |
+------+------+---------------------+
1 row in set (0.00 sec)
更改name会自动更新时间
timestamp与datetime区别总结：
timestamp 2037以前 4字节的存储空间 时间范围只到1970-2037年；默认非空
datetime 8字节的存储空间
(8) 枚举集合类型
enum 和 set
mysql> create table w1(id int,name varchar(6),gender enum('f','m','else'),hobby set('sing','dance','eriting'));
Query OK, 0 rows affected (0.01 sec)
mysql> select * from w1;
Empty set (0.00 sec)
mysql> desc w1;
+--------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(6) | YES | | NULL | |
| gender | enum('f','m','else') | YES | | NULL | |
| hobby | set('sing','dance','eriting') | YES | | NULL | |
+--------+-------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into w1 values(1,'tom','else','dance');
Query OK, 1 row affected (0.00 sec)
mysql> select * from w1;
+------+------+--------+-------+
| id | name | gender | hobby |
+------+------+--------+-------+
| 1 | tom | else | dance |
+------+------+--------+-------+
1 row in set (0.00 sec)
2，alter, 修改表信息
创建表stu
mysql> create table stu(sid int,sname char(10),sage varchar(2),sgender enum('f','m'),scometime datetime);
Query OK, 0 rows affected (0.00 sec)
mysql> desc stu;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | char(10) | YES | | NULL | |
| sage | varchar(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| scometime | datetime | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1,把表stu更改名为student
mysql> alter table stu rename student;
Query OK, 0 rows affected (0.00 sec)
2,student表 增加phone_num和address字段
mysql> alter table student add phone_num char(11),add address varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | char(10) | YES | | NULL | |
| sage | varchar(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| scometime | datetime | YES | | NULL | |
| phone_num | char(11) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
3, student表 删除scometime和age字段(删除多字段)
mysql> alter table student drop scometime,drop sage;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | char(10) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| phone_num | char(11) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4，增加表字段age，并在sname后面
mysql> alter table student add age char(2) after sname;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | char(10) | YES | | NULL | |
| age | char(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| phone_num | char(11) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5, 在开头增加字段hobby
mysql> alter table student add hobby varchar(10) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| hobby | varchar(10) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sname | char(10) | YES | | NULL | |
| age | char(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| phone_num | char(11) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
6, 修改字段sname的类型
mysql> alter table student modify sname enum('lisa','tom');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+-----------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------------+------+-----+---------+-------+
| hobby | varchar(10) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sname | enum('lisa','tom') | YES | | NULL | |
| age | char(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| phone_num | char(11) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+-----------+--------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
7，修改phone_num字段的名称和类型
mysql> alter table student change phone_num phone varchar(13);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
验证：
mysql> desc student;
+---------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| hobby | varchar(10) | YES | | NULL | |
| sid | int(11) | YES | | NULL | |
| sname | enum('lisa','tom') | YES | | NULL | |
| age | char(2) | YES | | NULL | |
| sgender | enum('f','m') | YES | | NULL | |
| phone | varchar(13) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+---------+--------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
二，DML数据操控语言
1，insert
加上属性建表
mysql> create database if not exists t2 character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use t2
Database changed
mysql> crate table student(
-> id int not null primary key auto_increment comment '学号',
-> name varchar(10) not null comment '姓名',
-> age tinyint unsigned not null comment '年龄',
-> gender enum('男','女') not null default '男' comment '性别默认男',
-> cometime datetime default now() comment '入学时间',
-> birthday datetime comment '学生生日',
-> class varchar(10) not null comment '班级');
#查看建表语句
mysql> show create table student;
1，插入数据
# 插入数据_ 方式一
mysql> insert into student values(1,'tom',13,'男',now(),now(),'11');
Query OK, 1 row affected (0.00 sec)
# 查看表信息
mysql> select * from student;
+----+------+-----+--------+---------------------+---------------------+-------+
| id | name | age | gender | cometime | birthday | class |
+----+------+-----+--------+---------------------+---------------------+-------+
| 1 | tom | 13 | 男 | 2021-03-08 10:00:20 | 2021-03-08 10:00:20 | 11 |
+----+------+-----+--------+---------------------+---------------------+-------+
1 row in set (0.00 sec)
# 插入数据_ 方式二
先更改一下字段属性（方便操作）
mysql> alter table student drop birthday,modify class varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into student (name,age,gender) values ('egon',13,'男');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+-----+--------+---------------------+-------+
| id | name | age | gender | cometime | class |
+----+------+-----+--------+---------------------+-------+
| 1 | tom | 13 | 男 | 2021-03-08 10:00:20 | 11 |
| 2 | egon | 13 | 男 | 2021-03-08 10:08:37 | NULL |
+----+------+-----+--------+---------------------+-------+
2 rows in set (0.00 sec)
# 以上egon的班级没有，则为空
2，插入多条数据（逗号分隔开即可）
mysql> insert into test(id,name,age,id_card,hobby) values (3,'snow',15,122222222222222222,'sing'),(4,'tutu',3,121212121212121212,'dance');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
#验证查看：
mysql> select * from test;
+----+------+-----+--------------------+-------+
| id | name | age | id_card | hobby |
+----+------+-----+--------------------+-------+
| 3 | snow | 15 | 122222222222222222 | sing |
| 4 | tutu | 3 | 121212121212121212 | dance |
+----+------+-----+--------------------+-------+
2 rows in set (0.00 sec)
2，update
1,修改之前要确认数据-查看数据
mysql> select * from world.city where name='shanghai';
+------+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+----------+-------------+----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
+------+----------+-------------+----------+------------+
1 row in set (0.01 sec)
# 注意，这里如果不加where及后面的定位，会把表里所有的名字都改为sh
#例1
mysql> update world.city set name='sh' where name='shanghai';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
验证查询：
mysql> select * from world.city where name='sh';
+------+------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+------+-------------+----------+------------+
| 1890 | sh | CHN | Shanghai | 9696300 |
+------+------+-------------+----------+------------+
1 row in set (0.00 sec)
#例2
mysql> select * from subject;
+------+------+---------+---------+
| mask | id | course | teacher |
+------+------+---------+---------+
| NULL | NULL | english | zoe |
+------+------+---------+---------+
1 row in set (0.00 sec)
mysql> update subject set id=1 where teacher='zoe';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3，delete && truncate
删除之前一定要确认数据-查看数据
mysql> delete from world.city where name='shanghai';
# 如果不加where及定位，会删除所有的内容，但是会保存表结构
一个表如果设置了auto_increment，删除表内容再添加，id号会继续增加，用truncate会刷新id重新从1开始，并删除表内容
#建表
mysql> create table test1(id int primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.01 sec)
#输入内容
mysql> insert into test1(name) values('tom'),('lily');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
#查看表信息，AUTO_INCREMENT=3 （已有两行内容，下一个从3开始）
mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
# 删除表内容
mysql> delete from test1;
Query OK, 2 rows affected (0.00 sec)
# 确认表无内容
mysql> select * from test1;
Empty set (0.00 sec)
# 再次插入数据
mysql> insert into test1(name) values('egon');
Query OK, 1 row affected (0.00 sec)
# 查看表信息的id为3
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 3 | egon |
+----+------+
1 row in set (0.00 sec)
#如果id想从1开始，用truncat
mysql> truncate test1;
Query OK, 0 rows affected (0.01 sec)
# 查看已没有AUTO_INCREMENT
mysql> show create table test1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#插入数据
mysql> insert into test1(name) values('okk');
Query OK, 1 row affected (0.00 sec)
# 查看表，不仅刷新了id号从1开始，同时删除了表内容
mysql> select * from test1;
+----+------+
| id | name |
+----+------+
| 1 | okk |
+----+------+
1 row in set (0.00 sec)
三，DQL数据查询语言
select单表查询
一（1），表与表之间建立关系（foreign key） 一对一
1,# 创建表时，需要先建立被关联表
mysql> create table a1(id int primary key auto_increment,name varchar(10),comment varchar(20));
Query OK, 0 rows affected (0.00 sec)
# 创建关联表
mysql> create table a2(
-> id int primary key auto_increment,
-> name varchar(10),
-> age tinyint,
-> comment_id int,
-> foreign key(comment_id) references a1(id)
-> on update cascade
-> on delete cascade);
2,输入内容
mysql> insert into a1(name,comment) values('it','ma_nong'),('hr','zhao_ping'),('sale','sale_something');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from a1;
+----+------+----------------+
| id | name | comment |
+----+------+----------------+
| 1 | it | ma_nong |
| 2 | hr | zhao_ping |
| 3 | sale | sale_something |
+----+------+----------------+
mysql> insert into a2(name,age,comment_id) values('tom','18',1),('alex','37',2),('lily','19',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from a2;
+----+------+------+------------+
| id | name | age | comment_id |
+----+------+------+------------+
| 1 | tom | 18 | 1 |
| 2 | alex | 37 | 2 |
| 3 | lily | 19 | 3 |
+----+------+------+------------+
3 rows in set (0.00 sec)
3，测试
#测试一，无法新插入a2的comment_id(因为a2已关联a1的id，而a1的id只有1，2，3)
mysql> insert into a2(name,age,comment_id) values('xxx',13,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`a2`, CONSTRAINT `a2_ibfk_1` FOREIGN KEY (`comment_id`) REFERENCES `a1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
#测试二，更新关联表a1,a2会同步更新
mysql> update a1 set id=333 where name='it';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from a1;
+-----+------+----------------+
| id | name | comment |
+-----+------+----------------+
| 2 | hr | zhao_ping |
| 3 | sale | sale_something |
| 333 | it | ma_nong |
+-----+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from a2;
+----+------+------+------------+
| id | name | age | comment_id |
+----+------+------+------------+
| 1 | tom | 18 | 333 |
| 2 | alex | 37 | 2 |
| 3 | lily | 19 | 3 |
+----+------+------+------------+
3 rows in set (0.00 sec)
#测试3，同步删除
mysql> delete from a1 where id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select * from a1;
+-----+------+-----------+
| id | name | comment |
+-----+------+-----------+
| 2 | hr | zhao_ping |
| 333 | it | ma_nong |
+-----+------+-----------+
2 rows in set (0.00 sec)
mysql> select * from a2;
+----+------+------+------------+
| id | name | age | comment_id |
+----+------+------+------------+
| 1 | tom | 18 | 333 |
| 2 | alex | 37 | 2 |
+----+------+------+------------+
2 rows in set (0.00 sec)
注意：
删除表时，不能先删除被关联表
mysql> drop table a1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table a2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table a1;
Query OK, 0 rows affected (0.00 sec)
一（2），表与表之间建立关系（foreign key） 多对多
# 建表
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
#建关联以上2个表格的表
mysql> create table authbook(
-> id int primary key auto_increment,
-> author_id int,
-> book_id int,
-> foreign key(author_id) references author(id)
-> on update cascade
-> on delete cascade,
-> foreign key(book_id) references book(id)
-> on update cascade
-> on delete cascade;
# 输入内容
create table authbook( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade);
insert into author(name) values('tom'),('alex'),('peter');
insert into book(name) values('aa'),('bb'),('cc');
mysql> insert into authbook(author_id,book_id) values(1,3),(1,2),(1,1),(2,3),(3,1),(3,2);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
二，select查询数据
1,查看表的所有内容（如果内容过多，可能会导致内存过度）
#1）在指定库中
mysql> use t4
Database changed
mysql> select * from test;
+----+------+-----+--------------------+-------+
| id | name | age | id_card | hobby |
+----+------+-----+--------------------+-------+
| 3 | snow | 15 | 122222222222222222 | sing |
| 4 | tutu | 3 | 121212121212121212 | dance |
+----+------+-----+--------------------+-------+
2 rows in set (0.00 sec)
#2）非当前库中，需要绝对路径
mysql> select * from t1.student;
+------+-------+------+---------+
| sid | sname | age | sgender |
+------+-------+------+---------+
| 1 | tom | 18 | f |
| 2 | tom | 30 | m |
+------+-------+------+---------+
2 rows in set (0.00 sec)
2，查询数据之前，先查看数据量
# 查询所有数据量
mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.01 sec)
# 查看指定数据量
mysql> select count(district) from city;
+-----------------+
| count(district) |
+-----------------+
| 4079 |
+-----------------+
1 row in set (0.00 sec)
3,查看所有的countrycode（distinct去重）
mysql> select distinct(countrycode) from world.city;
4，查看表字段
mysql> desc country;
三，查询数据库测试
1，查看指定字段，某几列的内容
# 查看表country的name 和capital
mysql> select name,capital from world.country;
+----------------------------------------------+---------+
| name | capital |
+----------------------------------------------+---------+
| Aruba | 129 |
| Afghanistan | 1 |
| Angola | 56 |
| Anguilla | 62 |
| Albania | 34 |
| Andorra | 55 |
| Netherlands Antilles | 33 |
| United Arab Emirates | 65 |
| Argentina | 69 |
| Armenia | 126 |
... ...
2, 查询上海的人口
mysql> select population from world.city where name='shanghai';
+------------+
| population |
+------------+
| 9696300 |
+------------+
1 row in set (0.00 sec)
3，按照人口数量排序（生序）
mysql> select * from world.city order by population;
4，按照人口数量排序（降序）
mysql> select * from world.city order by population desc;
备注：如果人口数量一样的情况下，再用id排序
mysql> select * from world.city order by population desc,id desc;
asc 升序（mysql语句中省略）
desc 降序
5,查看city表中，人数排名前十的城市名
mysql> select name from city order by population desc limit 8;
+-----------------+
| name |
+-----------------+
| Mumbai (Bombay) |
| Seoul |
| So Paulo |
| Shanghai |
| Jakarta |
| Karachi |
| Istanbul |
| Ciudad de Mxico |
+-----------------+
8 rows in set (0.00 sec)
四，按照条件查询
- where条件语句
- like模糊匹配
1, 查看primary key排序的前8列
mysql> select * from world.city limit 8;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
+----+----------------+-------------+---------------+------------+
8 rows in set (0.00 sec)
2，查看从第五列开始，后的三列
mysql> select * from world.city limit 5,3;
+----+-----------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+--------------+------------+
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
+----+-----------+-------------+--------------+------------+
3 rows in set (0.00 sec)
3, 查询人口大于900w的城市和地区
mysql> select name,district from city where population >= 9000000;
+-----------------+--------------+
| name | district |
+-----------------+--------------+
| So Paulo | So Paulo |
| Jakarta | Jakarta Raya |
| Mumbai (Bombay) | Maharashtra |
| Shanghai | Shanghai |
| Seoul | Seoul |
| Karachi | Sindh |
+-----------------+--------------+
6 rows in set (0.00 sec)
4, 查询countrycode='CHN' 并且 population >=2100000的地区
mysql> select district from city where countrycode='CHN' and population >=2100000;
+--------------+
| district |
+--------------+
| Shanghai |
| Peking |
| Chongqing |
| Tianjin |
| Hubei |
| Heilongjiang |
| Liaoning |
| Guangdong |
| Sichuan |
| Jiangsu |
| Jilin |
| Shaanxi |
| Liaoning |
| Shandong |
| Shandong |
| Zhejiang |
| Henan |
+--------------+
17 rows in set (0.00 sec)
5，查询countrycode是CHN 或者USA
#方式一
mysql> select * from city where countrycode='CHN' or countrycode='USA';
方式二
mysql> select * from city where countrycode in ('CHN','USA');
6，查询countrycode不是CHN 或者USA
mysql> select * from city where countrycode not in ('CHN','USA');
7, 精确匹配
mysql> select population from world.city where id=1000;
+------------+
| population |
+------------+
| 107000 |
+------------+
1 row in set (0.00 sec)
8, 模糊查询 # 查询地区含有chong
mysql> select * from city where district like '%chong%';
+------+-----------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+---------------+------------+
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 2343 | Chongju | KOR | Chungchongbuk | 531376 |
| 2350 | Chonan | KOR | Chungchongnam | 330259 |
| 2368 | Chungju | KOR | Chungchongbuk | 205206 |
| 2375 | Asan | KOR | Chungchongnam | 154663 |
| 2378 | Nonsan | KOR | Chungchongnam | 146619 |
| 2381 | Chechon | KOR | Chungchongbuk | 137070 |
| 2382 | Sosan | KOR | Chungchongnam | 134746 |
| 2385 | Kongju | KOR | Chungchongnam | 131229 |
| 2389 | Poryong | KOR | Chungchongnam | 122604 |
+------+-----------+-------------+---------------+------------+
10 rows in set (0.00 sec)
9，查找地区以chong为开头
mysql> select * from city where district like 'chong%';
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
+------+-----------+-------------+-----------+------------+
1 row in set (0.00 sec)
10，查找地区以abk为结尾
mysql> select * from city where district like '%abk';
+------+--------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 3400 | Karabk | TUR | Karabk | 118285 |
+------+--------+-------------+----------+------------+
1 row in set (0.00 sec)
11,查找为空或者不为空
mysql> select * from city where name is null;
mysql> select * from city where name is not null;
# 测试
mysql> update city set name='' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from city where id<4;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | | AFG | Herat | 186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)
五，select别名
# 只在查询时候运用
mysql> select name as '城市名称',population as '人口数量' from world.city limit 10;
+----------------+--------------+
| 城市名称 | 人口数量 |
+----------------+--------------+
| Kabul | 1780000 |
| Qandahar | 237500 |
| Herat | 186800 |
| Mazar-e-Sharif | 127800 |
| Amsterdam | 731200 |
| Rotterdam | 593321 |
| Haag | 440900 |
| Utrecht | 234323 |
| Eindhoven | 201843 |
| Tilburg | 193238 |
+----------------+--------------+
10 rows in set (0.00 sec)
# 表的定义，多表查询
mysql> select a.name,a.population,b.name from world.city a,world.country b limit 10;
+-------+------------+----------------------+
| name | population | name |
+-------+------------+----------------------+
| Kabul | 1780000 | Aruba |
| Kabul | 1780000 | Afghanistan |
| Kabul | 1780000 | Angola |
| Kabul | 1780000 | Anguilla |
| Kabul | 1780000 | Albania |
| Kabul | 1780000 | Andorra |
| Kabul | 1780000 | Netherlands Antilles |
| Kabul | 1780000 | United Arab Emirates |
| Kabul | 1780000 | Argentina |
| Kabul | 1780000 | Armenia |
+-------+------------+----------------------+
10 rows in set (0.00 sec)
补充
#建表
mysql> create table excel(id int primary key auto_increment,name varchar(10),gender enum('f','m') default 'f',office varchar(7),salary int);
Query OK, 0 rows affected (0.01 sec)
# 查看表结构
mysql> desc excel;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| gender | enum('f','m') | YES | | f | |
| office | varchar(7) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
#插入数据
mysql> insert into excel(name,office,salary) values('tom','a1','12000'),('lily','b2','8000'),('alex','c3','6700');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
#查看表信息
mysql> select * from excel;
+----+------+--------+--------+--------+
| id | name | gender | office | salary |
+----+------+--------+--------+--------+
| 1 | tom | f | a1 | 12000 |
| 2 | lily | f | b2 | 8000 |
| 3 | alex | f | c3 | 6700 |
+----+------+--------+--------+--------+
3 rows in set (0.00 sec)
1，查询每个人的年薪
mysql> select name,salary*12 from excel;
+------+-----------+
| name | salary*12 |
+------+-----------+
| tom | 144000 |
| lily | 96000 |
| alex | 80400 |
+------+-----------+
3 rows in set (0.01 sec)
2，接上，定义新的名字（as可省略）
mysql> select name as 'xingming',salary*12 as 'nianxin' from excel;
+----------+---------+
| xingming | nianxin |
+----------+---------+
| tom | 144000 |
| lily | 96000 |
| alex | 80400 |
+----------+---------+
3 rows in set (0.00 sec)
3，引入函数
mysql> select concat('xingming:',name),concat('xinzi:',salary) from excel;
+--------------------------+-------------------------+
| concat('xingming:',name) | concat('xinzi:',salary) |
+--------------------------+-------------------------+
| xingming:tom | xinzi:12000 |
| xingming:lily | xinzi:8000 |
| xingming:alex | xinzi:6700 |
+--------------------------+-------------------------+
3 rows in set (0.00 sec)
#以上的名字会显得不好看，加上定义别名
mysql> select concat('xingming:',name) as new_name,concat('xinzi:',salary) as new_salary from excel;
+---------------+-------------+
| new_name | new_salary |
+---------------+-------------+
| xingming:tom | xinzi:12000 |
| xingming:lily | xinzi:8000 |
| xingming:alex | xinzi:6700 |
+---------------+-------------+
3 rows in set (0.00 sec)
4，contact_ws 把查询的信息放在同一列
mysql> select concat_ws(':',name,gender,salary) from excel;
+-----------------------------------+
| concat_ws(':',name,gender,salary) |
+-----------------------------------+
| tom:f:12000 |
| lily:f:8000 |
| alex:f:6700 |
+-----------------------------------+
3 rows in set (0.00 sec)
5，case判断
mysql> select (
case
when name = 'tom' then
name
when name = 'lily' then
concat(name,'_beauty')
else
concat(name,'_idot')
end
) as new_name from excel;
+-------------+
| new_name |
+-------------+
| tom |
| lily_beauty |
| alex_idot |
+-------------+
-------------------------------------------------------------------------------------------
# 表如下
mysql> select * from excel;
+----+---------+--------+--------+--------+
| id | name | gender | office | salary |
+----+---------+--------+--------+--------+
| 1 | tom | f | a1 | 12000 |
| 2 | lily | m | b2 | 8000 |
| 3 | alex | f | c3 | 6700 |
| 4 | xiaohua | m | a1 | 9700 |
+----+---------+--------+--------+--------+
4 rows in set (0.00 sec)
1，查询以性别区分组的最高工资
mysql> select name,gender,max(salary) from excel group by gender;
+------+--------+-------------+
| name | gender | max(salary) |
+------+--------+-------------+
| tom | f | 12000 |
| lily | m | 9700 |
+------+--------+-------------+
2 rows in set (0.00 sec)
2，查平均工资
mysql> select name,gender,avg(salary) from excel group by gender;
+------+--------+-------------+
| name | gender | avg(salary) |
+------+--------+-------------+
| tom | f | 9350.0000 |
| lily | m | 8850.0000 |
+------+--------+-------------+
2 rows in set (0.00 sec)
3，查询最少工资
mysql> select name,gender,min(salary) from excel group by gender;
+------+--------+-------------+
| name | gender | min(salary) |
+------+--------+-------------+
| tom | f | 6700 |
| lily | m | 8000 |
+------+--------+-------------+
2 rows in set (0.00 sec)
4，查询工资之和
mysql> select name,gender,sum(salary) from excel group by gender;
+------+--------+-------------+
| name | gender | sum(salary) |
+------+--------+-------------+
| tom | f | 18700 |
| lily | m | 17700 |
+------+--------+-------------+
5，查询性别为‘m’有多少个人
mysql> select gender,count(id) from excel where gender='m';
+--------+-----------+
| gender | count(id) |
+--------+-----------+
| m | 2 |
+--------+-----------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------
having是在分组后的筛选
注意：
在where后面不可以使用聚合函数avg(salary)
mysql> select * from excel where gender='m' group by office having avg(salary)>8600;
+----+---------+--------+--------+--------+
| id | name | gender | office | salary |
+----+---------+--------+--------+--------+
| 4 | xiaohua | m | a1 | 9700 |
+----+---------+--------+--------+--------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------
regexp正则表达
# 查询name中以m为结尾的信息
mysql> select * from excel where name regexp '.*m$';
+----+------+--------+--------+--------+
| id | name | gender | office | salary |
+----+------+--------+--------+--------+
| 1 | tom | f | a1 | 12000 |
+----+------+--------+--------+--------+
1 row in set (0.00 sec)
网友评论