开发者常用Mysql命令
开发者必备Mysql常用命令,基于Mysql5.7。
数据定义语句(DDL)
数据库操作
- 登录数据库:
mysql -uroot -proot -h 127.0.0.1
- mysql导出sql文件:
mysqldump -uroot -proot dataBaseName > /data/htdocs/dataBaseName.sql
- mysql导入sql文件:
mysql -uroot -proot -h 127.0.0.1 dataBaseName </data/htdocs/dataBaseName.sql
- 添加字段索引:
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
- 查看索引:
SHOW INDEX FROM tableName;
- 创建数据库:test为库名
create database test;
- 查看所有数据库:
show databases;
- 选择数据库并使用:
use test;
- 查看所有数据表:
show tables;
- 删除数据库:
drop database test;
表操作
- 创建表:tableName为表名
create table tableName(userId varchar(10),name varchar(10))
- 查看表的定义:
desc tableName
- 删除表:
drop table tableName
- 修改表字段:
alter table tableName modify ename varchar(20)
- 添加表字段:
alter table tableName add column age int(3)
- 删除表字段:
alter table tableName drop column age
- 字段改名;
alter table tableName change age age1 int(4)
- 修改表名:
alter table tableName rename tableName2
数据操纵语句(DML)
插入记录
- 指定名称插入:
insert into tableName (ename,hiredate,sal,deptno) values ('zhangsan','2018-01-01','2000',1)
- 不指定名称插入:
insert into tableName values ('lisi','2018-01-01','2000',1)
- 批量插入数据:
insert into tableName values(1,'dept1'),(2,'dept2')
修改记录
update tableName set sal='4000',deptno=2 where ename='zhangsan'
删除记录
delete from tableName where ename='zhangsan'
查询记录
- 查询所有记录:
select * from emp
- count 带查询条件:
COUNT(IF(exam_practice_rule_record.is_open = 0,TRUE,NULL))
- 查询不重复的记录:
select distinct deptno from emp
- 条件查询:
select * from emp where deptno=1 and sal<3000
- 排序和限制:
select * from emp order by deptno desc limit 2
- 分页查询(查询从第0条记录开始10条):
select * from emp order by deptno desc limit 0,10
- 聚合(查询部门人数大于1的部门编号):
select deptno,count(1) from emp group by deptno having count(1) > 1
- 连接查询:
select * from emp e left join dept d on e.deptno=d.deptno
- 子查询:
select * from emp where deptno in (select deptno from dept)
- 记录联合:
select deptno from emp union select deptno from dept
数据控制语句(DCL)
权限相关
- 授予操作权限(将test数据库中所有表的select和insert权限授予test用户):
grant select,insert on test.* to 'test'@'localhost' identified by '123'
帐号相关
- 删除账号:
drop user 'test'@'localhost'
- 修改自己的密码:
set password = password('123')
- 管理员修改他人密码:
set password for 'test'@'localhost' = password('123')
其他
字符集相关
- 查看字符集:
show variables like 'character%'
- 创建数据库时指定字符集:
create database test2 character set utf8