mysql常见命令

数据库操作

1. 创建数据库

create database if not exists [数据库名称]；

2. 创建数据库时指定字符集和校对规则

mysql> create database if not exists)[数据库名称]
    -> default character set [字符集] 
    -> default collate [校对规则];

3. 查看数据库的定义声明

show create database [数据库名称];

4. 查看数据库校对规则

show collation where charset like '[查询内容]%';

5. 删除数据库

drop database <数据库名>;

6. 修改数据库参数

mysql> alter database  [数据库名称]
    -> default character set [字符集] 
    -> default collate [校对规则];

7. 选择数据库

USE <数据库名>;

数据表操作

1. 创建数据表

语法格式：create table 表名 (数据名称 数据类型，数据名称 数据类型，…);

例：创建一个学生信息表，学生属性有学号、姓名、性别、年龄：

mysql> create table student
    -> (sid int(4),
    -> sname varchar(8),
    -> sex varchar(8),
    -> sage int(4));

2. 在数据表添加信息

语法格式：insert into 表名(数据名称1，数据名称2，…) VALUES(字段值1，字段值2, …)；

例：
（1）插入所有字段，一次性加入一条完整的信息，插入的字段值的个数和数据表的属性个数相同。如

insert student (sid,sname,sex,sage) values(001,'小肚腩','女',18);

（2）向数据表student插入sid为002，sname为曾曾曾，ssex为男，sage为30的一条记录。此时可以省student后面的属性，等同于

insert student values(002,'曾曾曾','男',30);

（3）插入部分字段，在表名后面添加需要插入的属性名，在VALUES后面添加对应的值：

insert student(sname,sex) values('曾一一','男');

3. 查看表结构

describe <表名>;
desc <表名>;

4. 查看建表语句

show create table <表名>;

5. 在数据表中查询信息

语法格式：select * from student;

（1）简单的信息查询
例：

• 查询学生表中的所有学号（可以添加多个属性，用逗号隔开），语法格式为：

select sid from student;

• 在查询的同时，也可以用AS为数据名称制定别名，例如：

select sid as snumber from student;

• 查询数据表student中学号为001的学生信息，语法格式为：

select * from student where sid=001;

（2）数据库表的聚合查询和条件查询

• 常用的聚合函数有：max()、min()、sum()、avg()、count()。
  例：查询学生表中学号最大的数值

select max(sid) from student;

• 常用的比较条件有：<、＞、＞＝、＜＝、== 、<>!=
  例：查询学生表中年龄大于18岁的

select * from student where sage>18; 

• 常用的逻辑条件有：and、or
  例：

select * from student where sid=001 or sage=30;

• 常用的判空条件（null空字符串）：is null、is not null
  例：

select * from student where sage is null;

• 常用的模糊条件（like）
  例

select * from student where sname like '小_';(_表示一个字或字符)
select * from student where sname like '小%';(%表示不限制字符个数)

5. 在数据表修改信息
   语法格式：UPDATE 表名 SET 数据名称=数据值;

• 修改所有数据，一般这种情况比较少用
  例：将学生所有的年龄都改成12

update student set sage=12;

• 带条件的修改
  例：将学号为002的学生年龄改为30

update student set sage=30 where sid=002;

在SET后面也可以进行多个数据名称的修改
例：

update student set sage=30,sex='女' where sid=2;

6. 在数据表删除信息
   语法格式：delete from 表名 where数据名称=数据值
   删除特定条件的某条数据（不加WHERE进行限制就是删除所有数据）
   例：

delete from student where sage=30;

7. 修改表的结构

• 添加一个字段，在表中增加一列属性
  例：在学生表中添加一列课程的属性

alter table student add column sclass varchar(20);

• 删除一个字段，在表中删除一列属性
  例：在学生表中删除一列课程的属性

alter table student drop column sclass;

• 修改表中某一个字段的类型,()内容可要可不要
  例：将学生表中的sname字段类型修改为varchar(50)

alter table student modify (column) sname varchar(50);     

• 修改表中某一个字段的名称
  例：将学生表中的sname修改为name

alter table student change (column) sname name varchar(50);

• 修改数据表的名称
  例：将student的学生表名称改为people

alter table student rename (to) people;

8. 删除表

drop table <表名>;

多表查询

多对多

1. 创建一张学生表和一张课程表

#学生表  
mysql> create table student(
    -> id int auto_increment primary key comment '主键ID', #auto_increment表示自增的意识，当id列没有定义数字的时候，会自动添加
    -> name varchar(10) comment '姓名',
    ->  no varchar(10) comment '学号'  
    -> ) comment '学生表';
insert into student values (null,'小肚腩','12001'),(null,'曾曾曾','12002'),(null,'曾一一','12003');    

#课程表
mysql> create table course(
    -> id int auto_increment primary key comment '主键ID',
    -> name varchar(10) comment '课程名称'
    -> ) comment '课程表';
insert into course values (null,'JAVA'),(null,'PHP'),(null,'Mysql'),(null,'Hadoop');

2. 建立中间表，用于联系学生表和课程表

# constraint  foreign key references 外键约束：用于定义表之间外键关联的规则。它指示了一个表的列或一组列参考另一个表的主键或唯一键，目的是为了维护数据的完整性、一致性和关系的正确性。
mysql> create table student_course(
    -> id int auto_increment comment '主键' primary key,
    -> studentid int not null comment '学生ID',
    -> courseid int not null comment '课程ID',
    -> constraint fk_courseid foreign key (courseid) references course (id),
    -> constraint fk_studentid foreign key (studentid) references student (id)
    -> )comment '学生课程中间表';
insert into student_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

内连接

内连接查询的是两张表交集的部分

语法：
（1）隐式内连接

select 字段列表 from 表1，表2 where 条件...;

例：

select * from student,course where student.id=course.id;
select student.name, course.name from student,course where student.id=course.id;

#修改表别名
select a.name,b.name from student a,course b where a.id=b.id;

（2）显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

例：

select * from student inner join course on student.id=course.id;

隐式内连接和显式内连接的区别
（1）语法上

• 隐式内连接使用where条件进行连接
• 显示内连接使用INNER JOIN进行连接
  （2）执行顺序上
• 隐式内连接会先做笛卡尔积，然后再过滤不匹配的行
• 显示内连接先匹配两表的连接条件，然后返回匹配的行
  （3）可读性上
• 显示内连接的JOIN语法可读性更好
  （4）性能上
• 在大数据量下，显示内连接性能更好

外连接

语法：
（1）左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...；

例：

select student.*,course.name from student left outer join course on student.id=course.id; 

相当于查询表1（左表）的所有数据包含 表1和表2交集部分的数据
（2）右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...；

select student.*,course.name from student right outer join course on student.id=course.id;  

相当于查询表2（右表）的所有数据包含 表1和表2交集部分的数据

自连接

语法：

select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;

select * from student a join course b where a.id=b.id;

自连接查询，可以是内连接查询，也可以是外连接查询

常见命令

1. 执行脚本

source [脚本文件];

2. 查看版本号

select version();

MySQL配置用户权限

1. 查看mysql用户有哪些

select user,host from mysql.user;

2. 查看当前用户

mysql> select user();

3. 创建用户
语法：

create user 'username'@'host' identified by 'password';   

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符%
• password：该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器
  例：

create user 'lyx'@'%' identified by '123456';   

4. 赋权
   语法：

grant privileges on databasename.tablename to 'username'@'host';

• privileges：用户的操作权限，如select、insert、update等，如果要授权所有的权限则使用all
• databasename：数据库名
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示
  例：

grant select on mysql.* to 'lyx'@'%';

5. 显示权限

show grants for 'lyx'@'%';

6. 回收其所有权限

revoke all on *.* from 'lyx'@'%';

7. 刷新权限

flush privileges;

8. 删除用户

drop user 'lyx'@'%';

9. 用户重命名

rename user 'otheruser'@'%' to 'lyx'@'%';

10. 修改密码

# mysql 5.7之后
update user set authentication_string='654321' where user='root';
# mysql 5.7之前
update user set password='654321' where user='root';