MYSQL基础命令_MySQL

1、修改root命令

    mysqladmin -u root -p password [newpassword]

    注意password 是修改密码的命令

 

2、远程登录

    mysql -u root -h 127.0.0.1 -P 3306 -D demo -p

 

3、between包含首尾

    select * from product where price between 5 and 10

    价格在[5,10]，包括5和10

 

4、null值判断

   select * from contract where contact_mobile is null

 

5、模糊匹配

   select * from user where name like '%tom%'

   %表示任意字符，任意次数

   select * from user where name like '_tom'

   _ 表示一个字符

 

 6、连接字符串

    select concat(name,'(',phone,')') from contract

 

7、去除字符串空格

   select trim(phone) from contract

 

8、统计

    select count(*) from product  包含空值

    select count(phone) from product  不包含空值

 

9、分组

    group by 默认约定

    group by 子句中列出的每个列都必须是检索列 或者 有效的表达式（不能使聚集函数）

    过滤分组

    select user_id ,count(*) as orders from orders group by user_id having count(*) >=2

 

 10、子查询作为select字段

     select user_name,(select count(*) from orders where orders.user_id = customers.user_id) as orders

     from customers

     select user_name,(case when add_time > '2014-03-01' then 1 else 0 end) as flag from users

 

11、插入数据

     插入多条

     insert into action_detail(action_id,add_time,user_id) values(1,'2014-04-25',9090),(1,'2014-02-30',9090)

    从select 中插入

    insert into action_detail(action_id,add_time,user_id) select action_id,add_time,user_id from action

 

12、更新多个值

    update product set name='123',phone='010-9890987' where id=9