MySQL基础操作应用学习-用户、数据库、数据表、数据的基本操作

MySQL基础应用学习

首先了解下MySQL的基本结构，按照级别的包含关系是：用户>数据库>数据表>数据，以下一一介绍下基本操作应用。

一、用户操作

1、创建用户

格式：create user ‘用户名’@‘用户适用的网络地址’ identified by ‘密码’；

说明：用户适用的网络地址取值与远程登录访问权限有关。

1）localhost：代表只可以本地登录访问用户；

2）10.0.151.%：代表10.0.151.*网段可以登录访问；

3）%：代表所有网段都可以登录访问；

示例：create user ‘student’@‘localhost’ identified by ‘123456’；

2、查看用户

查看所有用户

>use mysql; #切换到mysql数据库才能进行查询
>select user,host from user;

#如下查询结果举例，第一个root用户和最后一个root用户不是同一个用户。具体的可以参看官方文档，在我的另一篇文章里（https://blog.csdn.net/hu1258123819/article/details/95665366）也有简单介绍。
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| root             | %           |
| test             | %           |
| debian-sys-maint | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| root             | localhost   |
+------------------+-------------+

查看当前用户

>select user();

3、删除用户

>drop user '用户名'@'IP地址';
#即drop user 'user'@'host'；下同
例：drop user 'test'@'%';

4、修改用户密码

>set password for '用户名'@'IP地址' = password（'新密码');#根据版本号可能提示语法错误，如提示则去除password

5、用户授权管理（需要使用root用户）

**查看权限：**show grants for ‘用户名’@‘IP地址’;

授权：grant 权限 on 数据库.表 to ‘用户名’@‘IP地址’;

#给test用户添加权限，只能对demo数据库下的t1表进行查看和插入操作
>grant select,insert on demo1.t1 to 'test'@'%';
#只能对demo数据库下的所有表进行查看和插入操作
>grant select,insert on demo.* to 'test'@'%';
#对test数据库下所有表，给test添加grant以外的所有权限
>grant all privileges on test.* to test;

#数据库.表：第一个*表示所有数据库，也可指定数据库；.*表示表除grant之外的所有权限。
grant all privileges on *.* to test;

#。。。to test with grant option；操作后，test用户也可以给别的用户授权。权限的具体用户，感兴趣可以另行找资料了解。

#取消授权：
>revoke 权限 on 数据库.表 from '用户名'@'IP地址';

#刷新权限
flush privileges

二、数据库操作

1、创建数据库

格式：create database 数据库名 charset utf8;

说明：charset:字符集；编码规范utf8：输入时没有“-”，也可以是gbk等。

2、删除数据库

格式：drop database 数据库名;
示例：drop database student；

3、切换数据库

格式：use 数据库名;
示例：use mysql;

4、查看数据库

查看当前选择的数据库：select database();

查看当前用户的所有数据库：show databases；

三、数据表操作

1、创建表

格式：create table 表名(列及类型);

#示例
create table student(
    id int auto_increment primary key, 
    name varchar(20) not null, 
    age int not null, 
    gender bit default 1, 
    address varchar(20), 
    isDelete bit default 0);
'''说明：
id、name等前面的是列名，紧跟的是Type类型
auto_increment表示自增长   
primary key表示主键  
not null表示不为空
default表示默认值
int、char、varchar等数据类型可以看我的另一篇博文（https://blog.csdn.net/hu1258123819/article/details/95924857），也可以自行找资料了解。
'''

2、查看表

查看当前数据库中所有表：show tables;

查看表结构：

格式：desc 表名;
示例：desc student;

查看建表语句：

格式：show create table 表名;
示例：show create table student;

3、删除表

格式：drop table 表名;
示例：drop table student;

4、重命名表名

格式：rename table 原表名 to 新表名;
示例：rename table car to newCar;

5、修改表

格式：alter table 表名 add|change|drop 列名 类型;
示例：

1、修改Type属性
alter table student modify name char(20) unique;
2、想在已经建好的表中添加一列
alter table student add column info2 varchar(100);
3、修改列名
alter table student change column name stuname varchar(20);
4、删除一列
alter table student drop column sex;

四、数据操作

数据操作经典的四个格言：增删改查

1、增

a、全列插入
格式：insert into 表名 values(…);
说明：主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功以后以实际数据为准
示例：insert into student values(0,“Susna”,18,1,“纽约”,0);
b、缺省插入
格式：insert into 表名(列1,列2,……) values(值1,值2,……);
示例：insert into student(name,age,address) values(“Juli”,19,“深圳”);
c、同时插入多条数据
格式：insert into 表名 values(…),(…),……
示例：insert into student values(0,“hanmeimei”,18,0,“北京”,0),(0,“poi”,22,1,“海南”,0),(0,“wenli”,20,0,“石家庄”,0);

2、删

格式：delete from 表名 where 条件;
示例：delete from student where id=4;
注意：没有条件是全部删除，慎用

3、改

格式：update 表名 set 列1=值1,列2=值2,…… where 条件;
示例：update student set age=16 where id=7;
注意：没有条件是全部列都修改，慎用

4、查

基本语法：select 列名 from 表名;
示例：

#*表示查询student表格中的所有列
select * from student;
#查询name和age列
select name, age from student;

消除重复行

格式：select distinct 列名 from 表名;

示例：select distinct gender from student;

五、特殊重要的数据操作

1、条件查询

a、语法

select * from 表名 where 条件

b、比较运算符

等于 =
大于 >
小于 <
大于等于 >=
小于等于 <=
不等于 !=或<>
需求：查询id值大于8的所有数据
示例：select * from student where id>8;

c、逻辑运算符

and 并且
or 或者
not 非
需求：查询id值大于7的女同学
示例：select * from student where id>7 and gender=0;

d、模糊查询

like

%表示任意多个任意字符
_表示一个任意字符

insert into student values(0,"蜘蛛侠",18,1,"纽约",0);
insert into student values(0,"蜘蛛",20,1,"洛杉矶",0);
#查询姓蜘的同学
select * from student where name like "蜘%";
#查询姓蜘，且名字是两个字的
select * from student where name like "蜘_";

e、范围查询

in 表示在一个非连续的范围内
between…and… 表示在一个连续的范围内

#查询编号为8、10、12的学生
示例：select * from student where id in (8,10,12);
#需求：查询编号为6到8的学生
示例：select * from student where id between 6 and 8;

f、空判断

insert into student(name,age) values(“特朗普”,70);
注意：null与""是不同
判断空：is null
判断非空： is not null

需求：查询没有地址的同学
示例：select * from student where address is null;
需求：查询有地址的同学
示例：select * from student where address is not null;

g、条件优先级说明

小括号>not >比较运算符>逻辑运算符
其中逻辑运算符中and比or优先级高，如果同时出现并希望先选or,需要结合()来使用。

2、聚合

为了快速等到统计数据，提供了5个聚合函数
a、count() 表示计算总行数，括号中可以写和列名
b、max(列) 表示求此列的最大值
c、min(列) 表示求此列的最小值
d、sum(列) 表示求此列的和
e、avg(列) 表示求此列的平均值

#查询学生总数
select count(*) from student;
#查询女生的编号最大值
select max(id) from student where gender=0;
#查询女生的编号最小值
select min(id) from student where gender=0;
#查询所有学生的年龄和
 select sum(age) from student;
#查询所有学生的年龄平均值
select avg(age) from student;

3、分组

按照字段分组，表示此字段相同的数据会被放到一个集合中。分组后，只能查询出相同的数据列，对于有差异的数据列无法显示在结果集中可以对分组后的数据进行统计，做聚合运算。

语法：select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……;

#查询男女生总数
select gender,count(*) from student group by gender;

#分组后的数据筛选：select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,…… having 列1,……聚合……;
#where与having的区别：where是对from后面指定的表进行筛选，属于对原始数据的筛选；having是对group by的结果进行筛选。
select gender,count(*) from student group by gender having gender;

4、排序

语法：select * from 表名 order by 列1 asc|desc，列2 asc|desc , ……;
说明：
a、将数据按照列1进行排序，如果某些列1的值相同，则按照列2进行排序；
b、默认按照从小到大的顺序排序，asc升序，desc降序。

示例：

需求：将没有被删除的数据按年龄排序
示例：
select * from student where isDelete=0 order by age desc;
select * from student where isDelete=0 order by age desc, id desc;

7、分页
    语法：select * from 表名 limit start,count;
    说明：start索引从0开始
    示例：
          select * from student limit 0,3;
          select * from student limit 3,3;
          select * from student where gender=1 limit 0,3;

5、分页

语法：select * from 表名 limit start,count;
说明：start索引从0开始
示例：
select * from student limit 0,3;

select * from student where gender=1 limit 3,3;

6、关联

举例

#建表语句
mysql>create table class(
    id int auto_increment primary key, 
    name varchar(20) not null, 
    stuNum int not null);

mysql>create table students(
    id int auto_increment primary key, 
    name varchar(20) not null, 
    gender bit default 1, 
    classid int not null, 
    foreign key(classid) references class(id)#建立外键约束
);

插入一些数据：
insert into class values(0, "h1", 55),(0, "h2", 50),(0, "h3", 60),(0, "h4", 80);

insert into students values(0, "tom", 1, 1);
insert into students values(0, "juli", 1, 10);
insert into students values(0, "jack", 1, 2);


select * from students;
关联查询：
select students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

select students.name,class.name from class right join students on class.id=students.classid;


'''分类：
1、表A inner join 表B：
    表A与表B匹配的行会出现在结果集中
2、表A left join 表B：
    表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
3、表A right join 表B：
    表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充  
注意：不建立外键也可以关联查询