MySQＬ 入门（上）

MySQL 入门

为方便起见，示例以MySQL数据库默认数据库mysql表help_topic为准。

数据库连接

• mysql -h 192.169.3.78 -u username -p -port 3306 -db help_topic
• show databases;显示数据库；
• show tables; 显示数据库列表；
• desc table_name; 显示表的属性
• show columns from database_name;#显示数据库内可用表的列表

检索数据

• select name from help_topic; #单个 列
• select name ,help_category_id from help_topic limit 10; 多列
• select * from help_topic limit 10; 所以列
• selec distinct name from help_topic; 不同的行
• select name ,help-category_id from help topic limit 10,20 # 显示从第10行开始，共显示20行数据；
• select name, help_category_id from help_topic order by help_category_id desc; 排序数据 order by 列名称
• select name,length(name) as
• select name from help_topic where length(name)>10;
• select name from help_topic where help_category_id>20; 检查单个值
• select name from help_topic where help_category_id <> 34; 不匹配查询
• select name from help_topic where help_category_id between 20 and 30; 范围检查
• select count(example) from help_topic where example is NULL; 空值检查
• select name ,url from help_topic where length(name)>20 and help_category_id <30; 组合查询：AND
• select name ,url from help_topic where name like “%pe” or name like “M%”;组合查询：OR
• select name ,url from help_topic where help_category_id in (20,30); 组合查询：IN
  -select name ,url from help_topic where help_category_id not in (20,30); 组合查询：NOT
• select name from help_topic where name like “%pe” or name like “%or_”; 通配符过滤%匹配多个，_ 匹配单个；
• select name ,url from where name regexp “.[1-6]pe” order by name;使用正则表达式进行搜索
  
• select concat(name ,”-” ,help_category_id) as name_id from help_topic limit 10;拼接字段
  
• select help_category_id*10 from help_topic;

数据处理函数

• select Left(name,4) from help_topic limit 10; 文本处理函数；返回左边的字符转；
• select length(name) from help_topic limit 10; 返还串的长度；
  
  
  
• select created from proc where Date(created)= ‘2016-07-27’;
  
  
• select sum(help_category_id) as sum_id,avg(help_category_id) as avg_id ,max(help_category_id) as max_id ,min(help_category_id) as min_id ,count(help_category_id) as id_num from help_topic;
  

分组数据

• select length(name),count(name) as num from help_topic group by length(name) having num >20; 数据分组与Having 对分组数据过滤；
  select length(name),count(name) as num from help_topic group by length(name) having num >20 order by num; 分组与排序
• select name,help_category_id from help_topic where name in (select name from help_topic where length(name)>20);
• 内部联结：select vend_name,prod_anme from vendors inner join product on verdor,vendors_id=product.id;等价于：
• select vend_name,prod_anme from verdor,product where verdor.id=product.id;
  desc table_name;
  组合查询 union