对于数据库的简单理解:
数据库可以视为应用系统,其中数据表可以视为实体类,数据表中的字段对应于实体类中的属性
数据库的操作语言可以分为以下几类:
1、DDL,(DataDefinitionLanguage)数据定义语言,是对数据库结构的操作
2、DML,(DataManipulationLanguage)数据编译语言,是对数据库中具体数据内容进行操作(增删改查)的语言,关键词有insert、delete、update、select等
3、DCL,(DataControlLanguage)数据控制语言,只要是对数据库权限的控制设置,因而对用户权限有要求
对于MySQL的常用基本操作如下:
——数据库为db_user,表为tb_user ,实现结构的过程就是运用DDL
【一】对数据库结构进行操作
create database db_user;
create database db_user character set utf8;
show databases;
show create database db_user;
select database(); //注意:select操作是DML操作
use db_user;
drop database db_user;
【二】表结构的操作
create table tb_user(
-> id int(32) unsigned primary key auto_increment,
-> username varchar(32) not null,
-> password varchar(32)
-> );
show tables;
show create table tb_user;
desc tb_user;
drop tb_user;
alter table tb_user add pid tinyint(32) not null;
alter table tb_user modify pid tinyint(12) null;
alter table tb_user change pid new_pid tinyint(32) not null;
alter table tb_user drop new_pid;
alter table tb_user character set gbk;
rename table tb_user to tb1_user;
【三】表记录的操作(crud操作,其中r查询为最重要的)
insert tb_user (username,password) values('root','root');
insert tb_user values(null,'root','root');
delete from tb_user;
truncate table tb_user;
update tb_user set username='123',password='123';
查询操作:
简单查询:
select * from tb_user;
select username,password from tb_user;
select username from tb_user as tb_man;
select username as name from tb_user;
select distinct username from tb_user; //distinct 不重复,查询中去除重复的字段名
条件查询:
select * from tb_user where id=1;
select * from tb_user where id<>1; //<>不等于
select * from tb_user where id>=1;
select * from tb_user where id<=1;
select * from tb_user where not(id>5);
select * from tb_user where id>1 and id<3;
select * from tb_user where id=1 or username='小明';
模糊查询:
select * from tb_user where username like('%王%');
select * from tb_user where id in(1,3,5);
排序:
select * from tb_user where id>1 order by asc;
select * from tb_user where id>1 order by desc; //asc升序,desc降序,且order by必须放到sql语句最后
聚合:
select sum(id) from tb_user;
select avg(id) from tb_user;
select max(id) from tb_user;
select min(id) from tb_user;
select count(id) from tb_user;
分组:
select * from tb_user groupby password ;
select * from tb_user groupby password having id>3;
select * from tb_user where id>1 group by password having id>3;
分页查询:
select * from product limit 2,2; //limit 起始位置,每页显示数目 起始位置=(要查询页码-1)×每页显示数目
注意:
where, group by, having, order by 关键字存在时必须按照由左到右的顺序书写SQL语句
SQL语句规范使用大写书写SQL语句,但是小写不影响,数据库库名、表名、字段名都全部小写,不能有空格,可以存在下划线连接如表名db_table