mysql常用增删改查命令详解

MySQL常用命令

1. 创建数据库

CREATE DATABASE 数据库名;

create database demo;

2. 选择数据库

use 数据库名;

use demo;

3. 删除数据库

drop database <数据库名>;

drop database demo;

4. 创建表

CREATE TABLE table_name (column_name column_type);

create table user(
	 id int auto_increment not null,
	 username varchar(20) ,
     primary key(id)
)

5. 查看表结构

desc table_name;

desc user;

6. 插入数据

INSERT INTO table_name ( field1, field2,…fieldN )
VALUES
( value1, value2,…valueN );

insert into user(username) 
values('小灰灰')

7. 更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

update user set username ='张三' where id=1

8. 删除表数据

DELETE FROM table_name [WHERE Clause]

delete from user where id=1

9. 删除数据表

drop database <数据库名>;

drop table user;

10. 排序 asc 升序（默认），desc 降序

SELECT field1, field2,…fieldN FROM table_name1, table_name2…
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2…] [ASC [DESC][默认 ASC]]

select * from user order by id desc;

11. 分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

select count(*) from user group by username;

12. 三目运算符

IF(condition, value_if_true, value_if_false)

参数说明
condition: 判断条件
value_if_true: 如果condition的结果为TRUE，返回该值
value_if_false: 如果condition的结果为FALSE，返回该值

select username, if(age=1, '女','男') as '性别' 
from user;

13. case when

select username, case age when 0 then '男'
			 else '女' end as '性别'
from user;

14. 既复制表结构也复制表内容

create table new_table  as select * from old_table;

15. 只复制表结构不复制表内容

create table new_table as select * from old_table where 1=2;

16.查询客户端连接情况

show processlist;

17. 查看空闲连接

show variables like 'wait_timeout';

18. 查看最大连接数

show variables like 'max_connections';

19. 设置最大连接数

set global max_connections=200

20. 查看已连接的数量

show variables like 'Threads_connections';