1.查看表中描述 desc tablename; 2.创建表 create 建表规范: 1.表名字 一定是英文 不要写中文 汉语拼音 2.建表风格 1.主键 使用表中第一个字段使用自增主键 本身没有任何 业务意义 3.字段的注释 业务字段 非业务字段: 1.表创建表用户 vs 更新表用户 2.主键 create table user_info2( 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) ); 3.插入数据 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] insert into user_info (name,age) VALUES('zs',18),('ls',20); insert into user_info (name,age) VALUES('zs01',18),('ls01',20); NULL: 空值 的三种表示方式'' 'null' 'NULL' 4.更新数据 UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] where 过滤 update user_info set age='20'; =》 对整张表 update user_info set age='20' where name='zs' ; 对某条数据 update 语句 注意: where 5.删除一条数据 delete DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] delete from user_info where id=1; 注意: delete 也要考虑 是否加where insert into user_info (name,age) VALUES('zs',18),('ls',20); insert into user_info (name,age) VALUES('zihang01',10),('子航01',10); insert into user_info (name,age) VALUES('zihang02',11),('子航02',20); insert into user_info (name,age) VALUES('zihang03',12),('子航03',30); insert into user_info (name,age) VALUES('zihang04',13),('子航04',21); insert into user_info (name,age) VALUES('zs',19),('zs',21); 表的字符集: udf8 Incorrect string value: '\xE5\xAD\x90\xE8\x88\xAA...' for column 'name mysql 5.7版本 默认建表字符集 :latin1 解决:修改字符集 、建表直接指定字符集 utf8 6.删除表 drop table user_info; 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; 7.其他语法 1.where 过滤条件 1. > 2.< 3. = 4. and or in not in mysql> select * from user_info where age > 18; select * from user_info where name='子航01'; mysql> select * from user_info where name='zs' and age>18; mysql> select * from user_info where age>20 or name='zs'; 产品给你 1000个数据值 in select * from user_info where name='zs' or name = 'ls' or name ="ww" select * from user_info where name in ('zs','ls','ww'); not in select * from user_info where name not in ('zs','ls','ww'); 2.order by 排序语法 mysql> select * from user_info order by age; order by column [asc | desc] ,... 1.默认是升序 2. asc desc 降序 mysql> select * from user_info order by age ,name desc; 3.like语法 模糊查询 1.like rlike regexp 正则表达式【了解】 1.% 模糊 2.占位符 _ mysql> select * from user_info where name like '%z%'; mysql> select * from user_info where name like "_s%"; nsert into user_info (name) VALUES('zihsssg04'); nsert into user_info (name) VALUES('zshsssg04'); nsert into user_info (name) VALUES('zshsssg04'); nsert into user_info (name) VALUES('zshsssg04'); 需求: 1.name 字母开头是y select * from user_info where name like "y%"; 2.name 字母结尾是1 select * from user_info where name like "%1"; 3.name 含有字母h select * from user_info where name like "%h%"; 4.name查询第3个字母是h的数据 select * from user_info where name like "__h%"; 4.合并表 1.union 去重 2.union all 不去重 create table a(id int(3),name varchar(4)); create table b(id int(3),name varchar(4)); insert into a values(1,'zs'); insert into b values(1,'zs'); insert into b values(2,'ls'); 1.union select * from a union select * from b; 2.union all select * from a union all select * from b; 5.null 数据清洗 1.过滤null is null is not null select * from user_info where age is null; 2.null数据进行 【数据转换】 update 数据分析时候 : delte 、update 函数: 处理 null的函数: 1.coalesce() 2.ifnull select id ,name ,coalesce(age,0) as age_alias ,create_user ,create_time ,update_user ,update_time from user_info 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.聚合函数 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 2.分组语法 词频统计: wordcount word,1 => 对每个单词进行分组 分组 x,<1> y,<1,1> z,<1> => 聚合 sum x,1 y,1+1 =2 z,1 group by column... 需求: 按照name进行分组,求每组的平均年龄 分组聚合 select name, avg(age) as age_avg from user_info group by name 需求: user_info 各个name的最大年龄、最小年龄、以及人数? select name, max(age) as age_max, min(age) as age_min, count(age) as cnt from user_info group by name 分组聚合:注意: 1.select 字段 和 group by 字段 要对应 【非聚合函数字段】 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 指标:最大年龄、最小年龄、以及人数 select name, max(age) as age_max, avg(age) as age_avg, count(age) as cnt from user_info group by name 求: 上面的结果 求: age_avg 大于 18岁的 信息? 1.分组聚合 + having 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 总结: 条件过滤: 1.where 写在 from 后面 2.having 写在 group by 后面 上面的结果 求: age_avg 大于 18岁的 信息? 1. 子查询: 查询里面 嵌套查询 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种 广义上 内连接、左连接、右连接、全连接 内连接 ** 左连接 ** 全连接 * create table a1(id int(3),name varchar(10),address varchar(20)); create table b1(id int(3),name varchar(10),age int(3)); 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); 1.内连接 join select * from a1 join b1 on a1.id = b1.id select * from a1 inner join b1 on a1.id = b1.id 2.左连接 left join 以左表为主 数据是全的 右表来匹配 匹配不上就是null select * from a1 left join b1 on a1.id =b1.id; 3.右连接 right join 以右表为主 数据是全的 左表来匹配 匹配不上就是null select * from a1 right join b1 on a1.id =b1.id; 4.全连接 -- mysql 不支持全连接 左表右表数据是全的,而且没有重复数据 full join select * from a1 left join b1 on a1.id =b1.id union select * from a1 right join b1 on a1.id =b1.id 8. 查询数据条数 显示限制 limit
mysql_02
最新推荐文章于 2024-08-16 18:39:05 发布