- 1.查看表中描述
- desc tablename;
- 2.创建表 create
- create 建表规范:
1.表名字 一定是英文 不要写中文 汉语拼音
2.建表风格
1.主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义
3.字段的注释mysql> create table user_info( -> id int(3) not null auto_increment, -> name varchar(10) COMMENT '用户名称', -> age int(3), -> create_user varchar(10), -> create_time timestamp not null default current_timestamp, -> update_user varchar(10), -> update_time timestamp not null default current_timestamp on update current_timestamp -> ,primary key(id) -> ); Query OK, 0 rows affected (0.02 sec)
- create 建表规范:
-
3.插入数据 INSERT
-
语法格式:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]mysql> insert into user_info (name,age) VALUES('zs',18),('ls',20); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into user_info (name,age) VALUES('zs01',18),('ls01',20); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
-
- 4.更新数据 update
- 语法格式:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count] - 其中 where 表示过滤
mysql> update user_info set age='20'; // 对整张表 Query OK, 2 rows affected (0.00 sec) Rows matched: 4 Changed: 2 Warnings: 0 mysql> update user_info set age='20' where name='zs' ; // 对某条数据 Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
- 语法格式:
- 5.删除一条数据 delete
- 语法格式:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]mysql> delete from user_info where id=1; Query OK, 1 row affected (0.00 sec) ##注意: delete 也要考虑 是否加where
- 语法格式:
- 补充:
- 表的字符集:
udf8
Incorrect string value: '\xE5\xAD\x90\xE8\x88\xAA...' for column 'name
mysql 5.7版本 默认建表字符集 :latin1
解决:修改字符集 、建表直接指定字符集 utf8
- 表的字符集:
- 6.删除表
mysql> drop table user_info; // 删除 Query OK, 0 rows affected (0.01 sec) // 重新创建 mysql> CREATE TABLE `user_info` ( -> `id` int(3) NOT NULL AUTO_INCREMENT, -> `name` varchar(10) DEFAULT NULL, -> `age` int(3) DEFAULT NULL, -> `create_user` varchar(10) DEFAULT NULL, -> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -> `update_user` varchar(10) DEFAULT NULL, -> `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec)
- 7.其他语法
- 1.where 过滤条件
1. >
2.<
3. =
4. and or in not inmysql> select * from user_info where age > 18; +----+----------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+----------+------+-------------+---------------------+-------------+---------------------+ | 2 | ls | 20 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 6 | 子航02 | 20 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 8 | 子航03 | 30 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 10 | 子航04 | 21 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 11 | zs | 19 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 12 | zs | 21 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 14 | 子航04 | 21 | NULL | 2022-11-19 16:04:22 | NULL | 2022-11-19 16:04:22 | | 15 | zs | 19 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 16 | zs | 21 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 18 | ls | 20 | NULL | 2022-11-19 16:05:10 | NULL | 2022-11-19 16:05:10 | | 20 | ls | 20 | NULL | 2022-11-19 16:05:49 | NULL | 2022-11-19 16:05:49 | +----+----------+------+-------------+---------------------+-------------+---------------------+ 11 rows in set (0.00 sec) mysql> select * from user_info where name='子航01'; +----+----------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+----------+------+-------------+---------------------+-------------+---------------------+ | 4 | 子航01 | 10 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | +----+----------+------+-------------+---------------------+-------------+---------------------+ 1 row in set (0.00 sec) mysql> select * from user_info where name='zs' and age>18; +----+------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+------+------+-------------+---------------------+-------------+---------------------+ | 11 | zs | 19 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 12 | zs | 21 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 15 | zs | 19 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 16 | zs | 21 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | +----+------+------+-------------+---------------------+-------------+---------------------+ 4 rows in set (0.00 sec) mysql> select * from user_info where age>20 or name='zs'; +----+----------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+----------+------+-------------+---------------------+-------------+---------------------+ | 1 | zs | 18 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 8 | 子航03 | 30 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 10 | 子航04 | 21 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 11 | zs | 19 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 12 | zs | 21 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 14 | 子航04 | 21 | NULL | 2022-11-19 16:04:22 | NULL | 2022-11-19 16:04:22 | | 15 | zs | 19 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 16 | zs | 21 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 17 | zs | 18 | NULL | 2022-11-19 16:05:10 | NULL | 2022-11-19 16:05:10 | | 19 | zs | 18 | NULL | 2022-11-19 16:05:49 | NULL | 2022-11-19 16:05:49 | +----+----------+------+-------------+---------------------+-------------+---------------------+ 10 rows in set (0.00 sec)
- 2.order by 排序语法
mysql> select * from user_info order by age;
- order by column [asc | desc] ,...
1.默认是升序
2. asc desc 降序
- order by column [asc | desc] ,...
- 3.like语法 模糊查询
- 1.like rlike regexp 正则表达式【了解】
1.% 模糊
2.占位符 _mysql> select * from user_info where name like '%z%'; +----+----------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+----------+------+-------------+---------------------+-------------+---------------------+ | 1 | zs | 18 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 3 | zihang01 | 10 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 5 | zihang02 | 11 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 7 | zihang03 | 12 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 9 | zihang04 | 13 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 11 | zs | 19 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 12 | zs | 21 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 13 | zihang04 | 13 | NULL | 2022-11-19 16:04:22 | NULL | 2022-11-19 16:04:22 | | 15 | zs | 19 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 16 | zs | 21 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 17 | zs | 18 | NULL | 2022-11-19 16:05:10 | NULL | 2022-11-19 16:05:10 | | 19 | zs | 18 | NULL | 2022-11-19 16:05:49 | NULL | 2022-11-19 16:05:49 | +----+----------+------+-------------+---------------------+-------------+---------------------+ 12 rows in set (0.00 sec) mysql> select * from user_info where name like "_s%"; +----+------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+------+------+-------------+---------------------+-------------+---------------------+ | 1 | zs | 18 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 2 | ls | 20 | NULL | 2022-11-19 16:03:50 | NULL | 2022-11-19 16:03:50 | | 11 | zs | 19 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 12 | zs | 21 | NULL | 2022-11-19 16:04:09 | NULL | 2022-11-19 16:04:09 | | 15 | zs | 19 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 16 | zs | 21 | NULL | 2022-11-19 16:04:47 | NULL | 2022-11-19 16:04:47 | | 17 | zs | 18 | NULL | 2022-11-19 16:05:10 | NULL | 2022-11-19 16:05:10 | | 18 | ls | 20 | NULL | 2022-11-19 16:05:10 | NULL | 2022-11-19 16:05:10 | | 19 | zs | 18 | NULL | 2022-11-19 16:05:49 | NULL | 2022-11-19 16:05:49 | | 20 | ls | 20 | NULL | 2022-11-19 16:05:49 | NULL | 2022-11-19 16:05:49 | +----+------+------+-------------+---------------------+-------------+---------------------+ 10 rows in set (0.00 sec)
- 1.like rlike regexp 正则表达式【了解】
- 4.合并表
- 1.union 去重
2.union all 不去重 - 1.union
select * from a
union
select * from b; - 2.union all
select * from a
union all
select * from b;
- 1.union 去重
- 5.null
- 1.过滤null
is null
is not nullmysql> select * from user_info where age is null; +----+-----------+------+-------------+---------------------+-------------+---------------------+ | id | name | age | create_user | create_time | update_user | update_time | +----+-----------+------+-------------+---------------------+-------------+---------------------+ | 21 | zihsssg04 | NULL | NULL | 2022-11-19 16:19:53 | NULL | 2022-11-19 16:19:53 | | 22 | zshsssg04 | NULL | NULL | 2022-11-19 16:19:53 | NULL | 2022-11-19 16:19:53 | | 23 | zshsssg04 | NULL | NULL | 2022-11-19 16:19:53 | NULL | 2022-11-19 16:19:53 | | 24 | zshsssg04 | NULL | NULL | 2022-11-19 16:19:56 | NULL | 2022-11-19 16:19:56 | +----+-----------+------+-------------+---------------------+-------------+---------------------+ 4 rows in set (0.00 sec)
- 1.过滤null
- 函数:
处理 null的函数:
1.coalesce()
2.ifnullmysql> select -> id -> ,name -> ,coalesce(age,0) as age_alias -> ,create_user -> ,create_time -> ,update_user -> ,update_time -> from user_info; mysql> select -> id -> ,name -> ,ifnull(age,0) as age_alias -> ,create_user -> ,create_time -> ,update_user -> ,update_time -> from user_info;
- 6.聚合函数: 指标
多行数据按照一定规则 进行聚合为一行
sum max min avg count- 先往表里插入几条数据
insert into user_info (name,age) values("zs",10);
insert into user_info (name,age) values("zs",11);
insert into user_info (name,age) values("zs",12);
insert into user_info (name,age) values("ls",10);
insert into user_info (name,age) values("ls",20);
insert into user_info (name,age) values("ls",30);
insert into user_info (name,age) values("ww",30); - 1.聚合函数
mysql> select -> sum(age) as age_sum, -> max(age) as age_max -> ,min(age) as age_min -> ,avg(age) as age_avg -> ,count(age) as cnt -> from user_info; +---------+---------+---------+---------+-----+ | age_sum | age_max | age_min | age_avg | cnt | +---------+---------+---------+---------+-----+ | 478 | 30 | 10 | 17.7037 | 27 | +---------+---------+---------+---------+-----+ 1 row in set (0.01 sec)
- 2.分组语法
// 按照name进行分组,求每组的平均年龄 // 分组聚合 mysql> select -> name, -> avg(age) as age_avg -> from user_info -> group by name; +-----------+---------+ | name | age_avg | +-----------+---------+ | ls | 20.0000 | | ww | 30.0000 | | zihang01 | 10.0000 | | zihang02 | 11.0000 | | zihang03 | 12.0000 | | zihang04 | 13.0000 | | zihsssg04 | NULL | | zs | 16.7000 | | zshsssg04 | NULL | | 子航01 | 10.0000 | | 子航02 | 20.0000 | | 子航03 | 30.0000 | | 子航04 | 21.0000 | +-----------+---------+ 13 rows in set (0.00 sec) // ---------------------------------------------------- // user_info 各个name的最大年龄、最小年龄、以及人数? mysql> select -> name, -> max(age) as age_max, -> min(age) as age_min, -> count(age) as cnt -> from user_info -> group by name; +-----------+---------+---------+-----+ | name | age_max | age_min | cnt | +-----------+---------+---------+-----+ | ls | 30 | 10 | 6 | | ww | 30 | 30 | 1 | | zihang01 | 10 | 10 | 1 | | zihang02 | 11 | 11 | 1 | | zihang03 | 12 | 12 | 1 | | zihang04 | 13 | 13 | 2 | | zihsssg04 | NULL | NULL | 0 | | zs | 21 | 10 | 10 | | zshsssg04 | NULL | NULL | 0 | | 子航01 | 10 | 10 | 1 | | 子航02 | 20 | 20 | 1 | | 子航03 | 30 | 30 | 1 | | 子航04 | 21 | 21 | 2 | +-----------+---------+---------+-----+ 13 rows in set (0.00 sec) // 分组聚合:注意:1.select 字段 和 group by 字段 要对应 【非聚合函数字段】 mysql> select -> name, -> id, -> max(age) as age_max, -> min(age) as age_min, -> count(age) as cnt -> from user_info -> group by name ,id; // 维度不一样: 1.维度: name 指标:最大年龄、最小年龄、以及人数 2. 维度: name、id 指标:最大年龄、最小年龄、以及人数
- 3.分组聚合 + having
// 求: age_avg 大于 18岁的 信息? mysql> select -> name, -> max(age) as age_max, -> avg(age) as age_avg, -> count(age) as cnt -> from user_info -> group by name -> having age_avg > 18; +----------+---------+---------+-----+ | name | age_max | age_avg | cnt | +----------+---------+---------+-----+ | ls | 30 | 20.0000 | 6 | | ww | 30 | 30.0000 | 1 | | 子航02 | 20 | 20.0000 | 1 | | 子航03 | 30 | 30.0000 | 1 | | 子航04 | 21 | 21.0000 | 2 | +----------+---------+---------+-----+ 5 rows in set (0.00 sec) // 总结: 条件过滤: 1.where 写在 from 后面 2.having 写在 group by 后面 // ----------------------------- // 另一种方法 mysql> select -> name, -> age_max, -> age_avg, -> cnt -> from -> ( -> select -> name, -> max(age) as age_max, -> avg(age) as age_avg, -> count(age) as cnt -> from user_info -> group by name -> ) as res -> where -> age_avg >18;
- 先往表里插入几条数据
- 7.join 多表联查
- 种类: 4种
- 广义上: 内连接、左连接、右连接、全连接
- 创建表a1 , b1;然后往里插入数据
mysql> create table a1(id int(3),name varchar(10),address varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> create table b1(id int(3),name varchar(10),age int(3)); Query OK, 0 rows affected (0.02 sec) // insert into a1 values(1,'aa',"dalian"); // insert into a1 values(2,'bb',"shenyang"); // insert into a1 values(4,'dd',"beijing"); // insert into b1 values(1,'aa',10); // insert into b1 values(2,'bb',20); // insert into b1 values(3,'cc',21); mysql> desc a1; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | address | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> desc b1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
- 1.内连接 join
mysql> select -> * -> from a1 join b1 -> on a1.id = b1.id; +------+------+----------+------+------+------+ | id | name | address | id | name | age | +------+------+----------+------+------+------+ | 1 | aa | dalian | 1 | aa | 10 | | 2 | bb | shenyang | 2 | bb | 20 | +------+------+----------+------+------+------+ 2 rows in set (0.02 sec)
- 2.左连接 left join
- 以左表为主 数据是全的 右表来匹配 匹配不上就是null
mysql> select -> * -> from a1 left join b1 -> on a1.id =b1.id; +------+------+----------+------+------+------+ | id | name | address | id | name | age | +------+------+----------+------+------+------+ | 1 | aa | dalian | 1 | aa | 10 | | 2 | bb | shenyang | 2 | bb | 20 | | 4 | dd | beijing | NULL | NULL | NULL | +------+------+----------+------+------+------+ 3 rows in set (0.01 sec)
- 以左表为主 数据是全的 右表来匹配 匹配不上就是null
- 种类: 4种
- 3.右连接 right join
- 以右表为主 数据是全的 左表来匹配 匹配不上就是null
mysql> select -> * -> from a1 right join b1 -> on a1.id =b1.id; +------+------+----------+------+------+------+ | id | name | address | id | name | age | +------+------+----------+------+------+------+ | 1 | aa | dalian | 1 | aa | 10 | | 2 | bb | shenyang | 2 | bb | 20 | | NULL | NULL | NULL | 3 | cc | 21 | +------+------+----------+------+------+------+ 3 rows in set (0.00 sec)
- 以右表为主 数据是全的 左表来匹配 匹配不上就是null
- 4.全连接 -- mysql 不支持全连接 full join
- 左表右表数据是全的,而且没有重复数据
mysql> select -> * -> from a1 left join b1 -> on a1.id =b1.id -> union -> select -> * -> from a1 right join b1 -> on a1.id =b1.id; +------+------+----------+------+------+------+ | id | name | address | id | name | age | +------+------+----------+------+------+------+ | 1 | aa | dalian | 1 | aa | 10 | | 2 | bb | shenyang | 2 | bb | 20 | | 4 | dd | beijing | NULL | NULL | NULL | | NULL | NULL | NULL | 3 | cc | 21 | +------+------+----------+------+------+------+ 4 rows in set (0.00 sec)
- 左表右表数据是全的,而且没有重复数据
- 1.where 过滤条件
- 8. 查询数据条数 显示限制 limit
Mysql相关命令
最新推荐文章于 2024-09-09 10:43:28 发布