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