MySQL 操作数据库、数据表

MySQL  操作数据库、数据表

 

通过mysql-client连接到mysql-server,sql语句要以分号结尾,这样按Enter才会执行sql语句。

mysql关键字不区分大小写

字符串、时间日期类型的值要引起来

 

 

数据库操作

show databases;  #显示已有的数据库

create database db_student character set utf8;  #新建数据库并指定使用的字符集
create database db_student;  #新建数据库,使用默认的字符集

drop database db_studrnt; #删除数据库

use db_student; #使用指定的数据库。要先使用此语句,才能使用该数据库中的表

 

 

 

数据表操作

show tables;  #查看当前数据库中的表

drop table 表名; #删除表

desc 表名; #查看该表的定义

show create table 表名; #查看该表的建表语句

rename table 原表名 to 新表名;

 create table tb_user(
    id int not null auto_increment,  #字段名 数据类型 【约束】

    name varchar(20) not null, 
    primary key (id)   #设置主键  

  );

 

 alter table 表名 add 字段名 数据类型 [约束];  #添加一列

  alter table 表名 drop 字段名;  #删除一列

 alter table 表名 modify 字段名 数据类型 [约束] ;  #修改某列的定义

  alter table 表名 character set utf8;  #修改字符集

 

 

  

字段名写成`字段名`的形式,可以防止字段名和mysql关键字冲突(字段名和mysql关键字相同)

 

 

 

记录操作

select 列名1,列名2 from 表名;  #返会的结果集是一张虚拟的表
select * from 表名;




insert into 表名 (列名1,列名2) values (值1,值2); #插入一条记录
insert into 表名 (列名1,列名2) values (值1,值2),(值11,值22);  #同时插入多条记录,()之间逗号分隔



delete from 表名 where ... #删除记录

delete from 表名; #删除表中所有记录
truncate table 表名; #删除表中的所有记录
#delete是逐条删除表中的所有记录,truncate是直接将原表删除,再根据表定义新建一张空表。truncate速度比delete快



update 表名 set 列名1=值1,列名2=值2 where... #更新一条记录
update tb_user set age=age+2 where name="chy"; #在原值的基础上


时间日期类型、字符串类型的值都要引起来,比如where name="chy"

插入、更新操作如果要写的字段、值很多,自己写很麻烦、还容易出错,在navicat、datagrip等工具中插入、更新一条记录,复制控制台代打印出来的sql语句,粘到项目中改下即可。

 

 

 

where子句

where子句select、update、delete都可以使用。

where id=1  #等于

where id!=1  #不等于
where id<>1  #不等于也可以这样写

#> >= < <= 不一一写了


where id between 1 and 10 #id在[1,10]上,是闭区间

where id in (1,3,9) #id=1或id=3或id=9


where score is null
where score is not null


where score>60 and score<90
where score<60 or score>90
where score not



#模糊匹配,用于匹配字符串。常见于模糊查询,但delete、update也可以用
where name like "张%" #以张开头。pattern要引起来
where name like "%伟" #以伟结尾
where name like "%国%" #含有国字就ok

% #一个或多个字符
_ #任意一个字符
[abcd] #abcd中的任意一个字符,只代表一个字符。比如 "张[杰伟]" 匹配张杰或张伟
[!abcd] #不是abcd中的任意一个字符就行,只代表一个字符,比如"张[!杰伟]" 只要不是张杰、张伟就ok
[^abcd] #!也可以写成^,同上

 

 

 

查询专用

select distinct 列名 from 表名;  #去重。如果结果集中有完全相同的记录,只保留一条


select 列名1*列名2 from 表名; #可以进行数学运算,但要求参与运算的列都是数值型


select 列名 as 别名 from 表名; #别名,常用于原列名较长、难记的情况
select 列名1+列名2 as 别名 from 表名; #或者要进行运算




#order by子句,结果集排序
select 列名 from 表名 order by 列名; #默认按升序(asc)排列

select 列名 from 表名 order by 列名 desc; #可指定序别,desc是降序
select 列名 from 表名 order by 列名1 asc,列名2 desc;  #先按列名1排序,列名1相同记录再按列名2排序。列名1叫做第一排序关键字,列名2叫做第二排序关键字

#排序的列结果集中要有




#聚合函数。所谓聚合函数,就是mysql自带的对结果集进行统计、分析的函数。在聚合函数中,值是null的记录均不参与统计。

select count(*) from 表名; #统计记录数
select count(列名) from 表名; #统计某一列的记录数
select count(distinct 列名) from 表名; #去重,结果集中重复的记录只算做一条
#
null不计算在内

select max(列名) from 表名;
select min(列名) from 表名;

select sum(列名) from 表名;
select avg(列名) from 表名;

select sum(列名),avg(列名) from 表名; #都可以配合使用





#group by 子句,结果集分组

select * from tb_student group by dep; #对结果集按指定的列进行分组,比如此处把dep相同的记录分为一组,显示分好的组

select count(*) from tb_student group by dep; #单独使用group by没多大意义,一般都是和聚合函数搭配使用,用于统计每组的数据。比如此处计算每个系的学生人数
select avg(score) from tb_student group by class;  #计算每个班的平均分





#having子句
select * from user_tb having id>10; #having的作用和where差不多,都是对记录进行过滤
#但过滤时机不同,where是在数据库中过滤,将符合要求的记录放到结果集中
#having是对结果集中的记录进行过滤,将过滤好的结果返回给客户端


select * from student_tb [where子句] group by class having avg(score)<80 #having一般和group by配合使用
#因为group by后面不能使用where,且where子句中不能使用聚合函数
#此处是先根据where从数据库中获取结果集,再将结果集按班级分组,去掉平均分<80的班级,最终返回给客户端的是平均分>=80的班级





#分页,常见的分页方式有三种:
#1、where只取出指定区间上的记录,比如where id>0 and where id<10,或者where id between 1 and 9
#2、limit对结果集进行限制
#3、查询、返回所有记录,由客户端自己实现分页


#limit子句 结果集分页
select * from user_tb [where子句] [order by子句] limit 10; #只取出结果集的前10条记录。注意是对结果集进行限制,从结果集中取
select * from user_tb [where子句] [order by子句] limit 9,19;  #只取出结果集的第[10,20]条记录。都+1,再取闭区间




#多个子句常见的书写、执行顺序

select ... from ... [where子句] [group by子句] [having子句] [order by子句] [limit子句]

#先执行select ... from ... [where子句] 将匹配的记录放到结果集中
#再group by将结果集中的记录分组
#having对分组进行过滤,去掉不满足要求的分组
#orderby排序
#limit只取出结果集某个区间的记录,返回给客户端

#和书写顺序一致
#一般不会这么长,常见的是以下2种:
select ... from ... [where子句] [group by子句] [having子句]
select ... from ... [where子句] [order by子句] [limit子句]

字符串类型的排序、比较均按码值比较,先按第一个字符的码值排序、比较,如果相同则再比较第二个字符的码值

 

 

 

表别名

select user.name from tb_user user;

如果表名很长、难记,可以取别名,使用别名来操作