mysql常用命令

　　mysql是目前最流行的关系型数据库

 

 

 

1. 命令行初始化配置密码

mysqladmin -u root -p password "123456"

 

2. 登录mysql

mysql -uroot -p123456

 

3. 查看数据库

　　注意：sql语句末尾一般要用";"，指代当前语句结束了

show databases;

 

4. 进入数据库中

use mysql;

 

5. 查看数据库中的表

show tables;

 

6. 建表

create table student( 
    stu_id int not null auto_increment,     
    name char(32) not null, 
    age int not null, 
    register_date date, 
    primary key (stu_id)
); 

# int整型，not null不允许为空，auto_increment字段自增，char(32)32位字符串类型，date日期类型，primary key()设置主键

 

7. 查看表结构

desc user;        查看user表的结构

 

8. 表数据的增、删、查、改

　　8.1 添加表数据

insert into student (name,age,register_date) values("wong",22,"2018-05-07")

　　8.2 删除表数据

delete from student where stu_id=1;

　　8.3. 查看表数据

select * from user;    #查看user表的所有数据

select * from user\G;        #\G 单列显示表

select * from student where age>18;        #筛选查找

select * from student where register_date like "2016%";        #like匹配查找

select * from student order by register_date desc;        #排序，asc正序，desc倒序

select register_date,count(*) from student group by register_date;    #分组统计数量
    
select coalesce(name,"sum_age"),sum(age) from student group by name with rollup;
    with rollup统计分组累加数之和
    coalesce(name,"name")指定累加数之和的字段名

select * from student limit 4 offset 3;        #limit限制行数，offset定位起始行标

　　　　8.31 连接查询

# 查交集
select * from A inner join B on A.a=B.b 

# 查差集
select * from A left join B on A.a=B.b
select * from A right join B on A.a=B.b

# 查并集
select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b;

8.4 修改表数据

update student set name=dbb where age=30; 

 

9. 操作表结构

# 删除age列
alter table student drop age ;        

# 增加age列
alter table student add age int not null default 0;  

# 修改age列的默认值
alter table student modify age int not null default 18;  

# 改列名并修改列结构
alter table student change age AGE int default 18;   

 

 

10. 授权

# mysql-5.x版本
grant select,insert,update,delete,create,drop on test.* to 'caiyun'@'%' identified by '123456';
grant all on test.* to 'caiyun'@'localhost' identified by '123456';

# mysql-8.x版本：
create user 'caiyun'@'%' identified by 'caiyun814';
grant all on testdb.* to 'caiyun'@'%';

 

11. 删除数据库与表

# 创建test数据库
create database test;

# 删除test数据库
drop database test;

# 删除student表
drop table student;

 

12. 索引

# 查看文件索引
show index from student;    

# 创建索引
create index index_name on student(name(32));
# 创建唯一索引
create unique index index_name on student(name(32));

# 删除索引
drop index index_name on student