Sql语言与MySql数据库

原创
2018/09/24 17:55
阅读数 169

1. 数据库简介

    1. 数据库,就是存储数据的仓库,只能通过sql语言来访问,数据库也是一个文件系统。通常,MySQL、Oracle等数据库,也被称为关系型数据库,其保存的不仅仅只是数据,还包括数据实体与实体之间的关系。(而如同Redis等这类则属于非关系型数据库,也叫NoSql数据库,不使用SQL语言进行操作)

    2. SQL语言:结构化查询语言,属于非过程性语言,用于操作关系型数据库的语言,具有查询、操纵、控制和定义关系型数据库的功能。

 

2. SQL语言的编写

SQL语句分为四类,分别是DDL(数据定义类SQL语句)、DML(数据操纵类SQL语句)、DCL(数据控制类SQL语句)、DQL(数据查询类SQL语句);注意,每一个SQL语句写完后都需要添加一个英文分号结尾

2.1 DDL(数据定义类SQL语句)

用于定义数据库中存储的对象,如数据表、视图、索引等

2.1.1 数据库操作相关SQL语句

    1. 创建数据库:

create database 数据库名称;
这是最简单创建数据库的写法,比如
create database mydb1;

create database 数据库名称 [character set 编码格式] [collate 校对规则];
创建一个数据库,并且设置其中所有数据的编码格式,不写编码格式时,数据库的编码格式默认为utf8;校对规则是对应着编码格式的,一种编码格式对应着多个校对规则,一般不用写,使用默认的即可;注意:用 []括起来的表示可以写也可以不写,而且编码格式中不写"-",也就是说 不写"utf-8"而是写"utf8",另外在SQL语句中字符串用单引号括起来,比如创建一个使用utf8编码集的数据库
create database mydb2 character set utf8;

    2. 查看数据库:

show databases;
显示所有的数据库

use 数据库名称;
用于指定接下来要操作哪一个数据库,或者说选择哪一个数据库操作,执行该语句后就会连接到这个数据库中

show create database 数据库名称;
用于查询一个数据库创建时设置的信息

select database();
查看当前正在使用哪一个数据库

    3. 删除数据库:

drop database [if exists] 数据库名称;

    4. 修改数据库:

alter database [if not exists] 数据库名称 [character set 字符编码集] [collate 该字符编码集的某个校对方法];

    5. 数据库的备份与恢复:进行该操作时,mysql数据库不能处于登录状态,命令后面没有分号结束,

//数据库备份命令为
mysqldump -u 用户名 -p 数据库名 > 文件名.sql 回车后再输入密码即可完成备份
//数据库恢复命令为
mysql -u 用户名 -p 数据库名 < 文件名.sql 回车后再输入密码

2.1.2 数据表操作相关SQL语句

     创建数据表

完整语法结构如下:
create table 表名称(
    字段名 字段类型(数据最大长度) [字段约束],
   [字段名 字段类型(数据最大长度) [字段约束],]...
   [数据表级约束]
) [character set 字符编码集] [collate 校对规则];

    1. 字段的部分数据类型:

  • 字符串型:char/varchar
    • char表示定长字符串,也就是说数据中字符串的字符个数是一定的,如果存入字符串数据的长度不足,那么也会用空格补足长度,超过设定的最大长度就会报错,无法存储,比如 name varchar(8),存入hello,那么name字段在这一行的数据长度仍为8
    • varchar表示变长字符串,表示存入字符串数据的长度只要不超过设定的最大长度,不会使用空格补足数据长度,否则就会报错,无法存储,也就是说当前字段值的数据长度是可变的,比如 name varchar(8),存入hello,那么name字段在这一行的数据长度为5
  • 大数据类型:blob、text,不常用,通常我们会使用一个文件所在的路径的字符串来代替
    • blob表示字节数据,常用存储电影等大量数据
    • text表示字符数据,常用语存储文本内容
  • 数值类型:tinyint(相当于byte)、smallint(相当于short)、int、bigint(相当于long)、double、float
  • 逻辑类型:bit(1或0),相当于true或false
  • 日期时间类型:date、time、timestamp、datetime等
    • date表示只包含日期数据,也就是年月日,格式为YYYY-MM-DD
    • time表示只包含时间数据,也就是时分秒,格式为HH:MM:SS
    • timestamp表示包含日期和时间数据,也就是年月日 时分秒,如果插入字段时字符为空,则字段值会默认自动从当前系统时间获取到字段值,并存储
    • datetime表示包含日期和时间数据,也就是年月日 时分秒,如果插入字段时字符为空,则字段值也为空
//以创建一个员工表为例
CREATE TABLE employee (
  id int(5),
  name varchar(20),
  gender bit(1),
  job varchar(30),
  birthday date,
  salary float
);

    2. 约束:约束用来保证数据的有效性以及完整性,分为单表约束(字段约束)和多表约束(表级约束)。

单表约束有

  • 主键约束 PRIMARY KEY:用于指定某一个字段作为当前表的主键字段,主键字段的值不允许重复且不允许为空,而且主键可以被其他表引用,作为外键,实现数据表间的关系。
  • 唯一约束 UNIQUE:用于指定字段的值是唯一的,不可重复的。
  • 非空约束 NOT NULL:用于指定字段的值不可以为空。
  • 自动递增约束 AUTO_INCREMENT:只适用于数值类型的字段,用于当添加一条新数据进入表中时,该字段的值会自动加1。
  • 编码集以及校对规则约束 CHARACTER SET utf8 COLLATE utf8_general_ci:设置当前字段的值的编码集合校对规则,不常用

多表约束有:

//建立员工表,并添加约束
CREATE TABLE employee(
id  int(5) NOT NULL AUTO_INCREMENT ,
name  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL UNIQUE,
gender  bit(1) NOT NULL ,
job  varchar(30) NOT NULL ,
birthday  date NOT NULL ,
salary  float NOT NULL ,
PRIMARY KEY (id)
);
//主键约束可以直接写在字段之后,或者如上写法
CREATE TABLE employee (
id  int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL UNIQUE,
gender  bit(1) NOT NULL ,
job  varchar(30) NOT NULL ,
birthday  date NOT NULL ,
salary  float NOT NULL 
);
  • 多表外键约束(foreign key):通过主键和外键就可以建立起两个表之间的关系。外键必须是另一个表的主键;外键可以重复;外键可以为NULL;一张表中可以有多个外键;如果A表中a字段下有数据外键依赖于B表中b字段下的数据,那么不能独自删除B表中b字段下被依赖的某一行数据;比如员工表与部门表之间的关系
//创建部门表,部门表主键为deptid
CREATE TABLE dept (
  deptid int(2) NOT NULL AUTO_INCREMENT,
  dname varchar(20) NOT NULL,
  PRIMARY KEY (deptid)
);

//创建员工表,员工主键为id,员工所属部门就通过外键部门表的deptid建立关系
CREATE TABLE employee (
  id int(5) NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  gender bit(1) DEFAULT NULL,
  job varchar(30) DEFAULT NULL,
  birthday date DEFAULT NULL,
  salary float DEFAULT NULL,
  deptid int(2) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT deptid FOREIGN KEY (deptid) REFERENCES dept (deptid)
);//CONSTRAINT 关键字用于为约束起名,比如当前表的外键约束就被命名为deptid 
//如果employee 表已经创建但未添加外键,则可以通过以下语句
alter table employee add FOREIGN KEY [employee] (deptid) REFERENCES dept (deptid);
 
//分别像两个表中插入数据
insert into dept (deptid,dname) values (1,'研发');
insert into employee (id,name ,gender ,job ,salary ,deptid )
values (0,'vn',1,'java开发',15000,1);

//则此时无法单独执行下列语句
delete from dept where deptid=1;

删除外键约束:

ALTER TABLE  表名 DROP FOREIGN KEY 外键约束名;

为表中的某个字段添加外键约束:

ALTER TABLE  表名  ADD FOREIGN KEY [当前表名] (字段) REFERENCES  外键所在的表  (对应的字段);

    删除数据表

drop table 数据表名;

    查看表信息

    1. 查看表结构:desc 数据表名;

    2. 查看当前数据库中的所有表名:show tables;

    3. 查看建表语句:show create table 数据表名;

    修改表结构以及约束

    1. 添加字段:ALTER TABLE 表名 ADD COLUMN 字段名  字段值类型(长度)  约束;

    2. 删除字段:ALTER TABLE 表名 DROP COLUMN 字段名;

    3. 修改字段:可以修改字段的属性,比如字段值的类型,长度和约束等,语法为

        ALTER TABLE 表名 MODIFY COLUMN 字段名  字段值类型(长度)  约束;  该语法用于修改字段值的类型,长度或约束

        ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名  字段值类型(长度)  约束 该语法用于修改字段名、值的类型、长度或约束

    修改和删除字段必须注意字段的约束问题,以及当前字段下是否有数据的问题:如果有数据,那么修改字段时,字段类型必须不变,或者向同一类型更大范围的数据类型更改,比如int类型可以变成bigint类型,绝对不能转为字符串等类型,也不能向同一类型更小范围的数据类型更改,比如int类型变成smallint类型。

    4. 修改表名:语法为  rename table 旧表名 to 新表名;

    5. 修改表的字符集或者校对方法:ALTER TABLE 表名 character set 字符集 collate 校对规则;

2.2 DML(数据操纵类SQL语句)

对数据表中的数据进行增加、删除、更改操作

1. 插入数据:语法为    insert into 数据表名(字段1名,字段2名[,字段名...])  values (字段1值,字段2值[,字段值...]);    指定要插入数据的字段。注意,字段值必须要与字段类型和长度匹配,字符串与日期类型字符串必须用单引号括起来,插入空值时可以不指定或者插入null,对于主键字段,是必须要插入值的。

也可以使用 insert into 数据表名 values (字段1值,字段2值[,字段值...]);  表示表中的所有字段都要插入数据

//以员工表插入数据为例
insert into employee values(1,'zhangsan',0,'prog','1997-10-24',20000);

2. 修改数据:语法为  update 数据表名  set  字段名=新值[,字段名=新值...]  [where  条件];

    可以通过where条件子句来判断要修改那些行的值,如果不写where子句,则指定的字段的值全部修改给定的新值;

    修改的值必须符合字段约束;

update employee set salary=2500;

3. 删除数据:语法为   delete from  数据表名  [where  条件];

    可以通过where条件子句来判断要删除那些行的值,如果不写where条件子句,则会删除整个表中的数据;

    delete不能删除某一列(某个字段)的数据,但是update可以;

2.3 DCL(数据控制类SQL语句)

用于设置用户权限以及控制事务语句

2.4 DQL(数据查询类SQL语句)

查询数据库中的数据

//建立学生成绩表
create table stu(
id int(5) primary key,
name varchar(20),
math int(3),
english int(3),
chinese int(3)
);

1. 基本查询语句: select [distinct]  *|字段1,字段2,字段3[...]  from 表 ;  dictinct 可选,表示去掉重复的数据,重复指的是后续指定的要查找的每个字段的值都相同;而 * 表示查找表中所有的字段的值。对查询字段进行一些运算,并且可以为查询到的字段起一些别名。比如

select * from stu;//查询所有学生的所有成绩
select distinct math from stu;//把查询所有学生的math成绩,并去除重复的
select name,english,chinese from stu;//显示查询所有学生的name,english,chinese
select name,english+10 from stu;//查询所有学生的name,和english成绩加10的结果
select name,(english+10) as englishs from stu;//字段别名,as关键字可以写也可以不写

2. 基本查询语句+where子句进行过滤查询:select [distinct]  *|字段1,字段2,字段3[...]  from 表   where  条件语句;   常用运算符如下

select * from stu where name='xx';//查询name为xx的学生的所有成绩
select * from stu where math>80;//查询math成绩大于80分的学生
select * from stu where (math+english+chinese)>270;//查询总成绩大于270分的学生

常用运算符如下:

  • <  >  <=  >=  <> :小于,大于,小于等于,大于等于,不等于
  • and  or  not:与 或 非,逻辑运算符
  • in 或 not in :表示确定某字段的是否在一个指定的集合范围,这个集合包括所有可能出现的值,比如 in ('cs','cf')
  • like 或 not like:模糊查询,其占位符有  _  和  两种形式,但  _  表示一个字符,而  %  表示多个字符。比如 like '_xx',表示以xx结尾的三个字符的字符串,而 like '%xx' 就表示以xx结尾的任意长度的字符串。
  • is null  或  is not null:判断某一个字段的数据是否为空
  • between ···  and ··· 或  not  between ···  and ···:用于数值类型的字段,比如  age between  18  and  30,表示年龄18到30岁
select * from stu where math>80 and math<95;//查询所有学生中math成绩大于80并且小于95的
select * from stu where math in (80,85,88);//查询所有学生中math成绩在(80,85,88)集合中的
select * from stu where math is null;//查询math成绩为空的学生
select * from stu where name like "%明";//查询学生名字以 明 结尾的学生

2. 基本查询语句[+where子句进行过滤查询]+order by排序语句:

select [distinct]  *|字段1,字段2,字段3[...]  from 表 [ where  条件语句]  order by  字段  asc|desc;

指定依据排序的字段的数据类型必须是数值类型;字段可以是表中的字段,也可以是select 关键字后紧跟不属于表中的字段;asc表示升序,desc表示降序;order by排序语句必须写在select语句的最后面。

select * from stu order by math asc;//依据math成绩,将所有学生升序排序
select * from stu order by math asc,english asc;//首先依据math成绩,将所有学生升序排序,如果math成绩相同,则按照english成绩升序排序

select (english+math) em from stu order by em asc;

3. 基本查询语句+聚集函数:对某一字段的所有数据进行一些运算操作,聚集函数包括

  • count(字段):统计该字段下共有多少行数据
  • sum(字段):求该字段下所有数值之和,要求字段的值的类型必须是数值类型
  • max(字段):求该字段下所有数值中最大的,要求字段的值的类型必须是数值类型
  • min(字段):求该字段下所有数值中最小的,要求字段的值的类型必须是数值类型
  • avg(字段):求该字段下所有数值的平均数,要求字段的值的类型必须是数值类型
select count(*) from stu;//统计表中有多少条数据
select count(name) from stu;//统计表中name字段下有多少条数据
select sum(math) from stu;//计算表中所有学生的数学成绩之和
select max(math) from stu;//查询表中所有学生中数学成绩最低的

//查询表中所有学生的math和english成绩之和
select sum(math+english) from stu;
//把括号中math+english计算结果看做一个新的字段,而这个新的字段的每行的值就是原表中每行math+english的计算结果,然后将新字段下所有值相加.但是如果math或english字段有任何一行数据为null,则该行上math+english的值也为null,也就是 78+null=null; 所以这种方式是错误的,下面的写法才是正确的

select sum(math)+sum(english) from stu;
//也就是说,null与任何类型的数据相加减,结果都为null
//但在纵向上(列)的数值类型字段的数据使用sum等聚集函数时,null会被忽略掉,count不会,因为count是统计有多少行数据的,并不涉及对字段中每一行具体的值的运算.
//或者
select sum(ifnull(math,0)+english) from stu;

聚集函数中的参数字段,可以是表中的单个字段,也可以是表中多个字段的运算形式;

mysql中有一个 ifnull(column,value) 函数,用于判断 column字段中某一行数据是否为null,如果是null,则将null替换为自己想要的值。

4. 基本查询语句+分组查询(group by):指定多个字段作为分组依据,字段数据相同的为一组;指定多个字段作为分组依据时,会首先将第一个字段作为分组依据进行第一次分组,然后再第一次分组后的各个组内依据第二个字段在进行分组,依次按照字段书写顺序进行分组下去。

语法为   select [distinct]  *|字段1,字段2,字段3[...]|[聚集函数]  from 表 [ where  条件语句]  group by  字段[,字段...]   having  条件语句;

对于聚集函数,其查询所得并不是整个表范围,而是在分组范围之内,在每个组内进行聚集函数的运算。

having子句后跟条件语句,其必须紧跟group by子句之后,where子句与having子句都有过滤的作用,但where是是过滤的是未进行分组之前的数据,但having子句是作用于分组之后每个分组的数据;而且where子句的条件语句中不能拥聚集函数,但having子句的条件语句中则可以使用。

//创建新表订单商品类
create table orders(
id int(5),
name varchar(15),
sort varchar(10),
price int(10)
);
//插入数据
insert into orders(id,name,sort,price) values (1,'华为p10','手机',5000);
insert into orders(id,name,sort,price) values (2,'华为p20','手机',6000);
insert into orders(id,name,sort,price) values (3,'surfacebook2笔记本','电脑',20000);
insert into orders(id,name,sort,price) values (4,'macbook2笔记本','电脑',17000);

//分组查询实例
//查询订单中每一类商品的总价,以及该类名称
select sort,sum(price) from orders group by sort
//查询购买了每类商品买了几个,每类商品中价格最高的商品
select sort,count(sort),max(price),name from orders group by sort;
//查询每类总价大于10000的商品类
select sort from orders group by sort having sum(price)>10000;

5. 总结:一个sql查询语句的基本形式为 select ....  from  ....  [where ....]  [group by ....]  [having ....]  [order by ....]

 

3. 表与表之间的关系与数据库设计

    1. 数据表之间的关系:

  • 一对多:例如一个班级对应多个学生的关系,而一个学生只对应一个班级
  • 多对多:例如一个学生可以选择多门课程,一门课程可以被多个学生选择
  • 一对一:例如一张学生证对应着一个学生,一个学生对应着一张学生证

    2. 一对多关系如何建表(1:n):以员工表和部门表的关系为例,通过主键和外键即可建立起任意两个表之间的一对多关系。也就是说

  • 在n方表中添加一个外键字段,而这个外键字段指向 1 方表的主键
  • 不需要建立额外的关系表,多方表的外键就是 1 方表的主键

    3. 多对多关系建表(m:n):以学生和选课之间的关系为例,要在两张表之间建立多对多关系,必须建立第三张表。即

  • 需要创建第三张表作为关系表,表中至少有两个字段作为外键,分别指向两张多对多关系表的主键。
  • 关系表中的字段,都是两张多对多关表中的主键字段的组合

    4. 一对一关系建表(1:1):以学生和学生证的表关系为例,有两种方法建立关系

  • 可以建立第三张中间表来表示两张表之间的关系
  • 可以通过外键来建立关系,在两个表任意一个表中添加一个外键字段指向另一个表的主键字段,并且将这个外键字段添加唯一约束,也就是unique。

    5. 总结:以购物网站表结构为例,其包括用户表user,订单表order,商品表,商品类别表。

4. 多表查询

    1. 笛卡尔积:假设有两个表,A表与B表,其中的数据如下

Aid Aname Bid Bname
a1 a11 b1 b11
a2 a21 b2 b21
a3 a31 b3 b31

执行查询语句 select * from A , B; 得到的结果就是笛卡尔积

a1 a11 b1 b11    a1 a11 b2 b21    a1 a11 b3 b31

a2 a21 b1 b11    a2 a21 b2 b21    a2 a21 b3 b31

a3 a31 b1 b11    a3 a31 b2 b21    a3 a31 b3 b3

    2. 多表查询——内连接查询:前提是两个表有关系,并且是通过外键约束来建立关系

以员工表和部门表为例,employee(id,name,sal,dno)和dept(id,name),其中员工表的dno字段作为外键依赖部门表的id字段。

并添加数据:

insert into employee(id,name,sal,dno) values (1,'a',22222,1);    insert into employee(id,name,sal,dno) values (2,'b',22222,1);

insert into employee(id,name,sal,dno) values (3,'c',22222,2);    insert into employee(id,name,sal,dno) values (4,'d',22222,2);

insert into employee(id,name,sal,dno) values (5,'e',22222,null);    insert into employee(id,name,sal,dno) values (6,'f',22222,null);

insert into dept(id,name) values(1,'ss');    insert into dept(id,name) values(2,'mm');    insert into dept(id,name) values(3,'vv');

  • 普通内连接:select * | [字段] from 查询主表 inner join  查询附表  on  [建立连接关系条件语句]  [and  其他条件语句进行过滤];
  • 隐式内连接:select * | [字段] from 表1 表2  where  [建立连接关系条件语句]  [and  其他条件语句进行过滤];
//查询属于 id为1的部门 的员工
select * from employee inner join dept on employee.dno=dept.id and employee.dno=1;
select * from employee,dept where employee.dno=dept.id and employee.dno=1;
//添加别名进行查询
select * from employee e,dept d where e.dno=d.id and e.dno=1;
select e.id,e.name,e.sal,d.name from employee e,dept d where e.dno=d.id and e.dno=1;

 建立连接关系条件语句:就是  一个表的主键字段=另一个表的外键字段

      3. 多表查询——外连接查询:外链接查询是要区分左外连接与右外连接的,外连接方式不同,查询结果可能不同

  • 左外连接:select * | [字段] from 主表 left  [outer]  join 附表 on  [建立连接关系条件语句]  [and  其他条件语句进行过滤];
select e.id,e.name,e.sal,d.name from employee as e left outer join dept d on d.id=e.dno;

如果使用左外连接,则主要看主表,会先把主表中的数据全部显示,然后再显示附表中对应的数据,如果附表中没有,则会填充NULL;

以上面语句的执行为例,则结果为

1,'a',22222,'ss'    2,'b',22222,'ss'    3,'c',22222,'mm'    4,'d',22222,'mm'    5,'e',22222,null    6,'f',22222,null

  • 右外连接:select * | [字段] from 主表 right  [outer]  join 附表 on [建立连接关系条件语句]  [and  其他条件语句进行过滤];

select e.id,e.name,e.sal,d.name from employee as e right outer join dept d on d.id=e.dno;

如果使用右外连接,则主要看附表,会先把附表中的数据全部显示,然后再依据附表已显示的数据显示主表中对应的数据,如果主表中没有,则会填充NULL;以上面语句的执行为例,则结果为

1,'a',22222,'ss'    2,'b',22222,'ss'    3,'c',22222,'mm'    4,'d',22222,'mm'   null,null,null,'vv'    null,null,null,'vv'

    4. 内连接与外连接的查询结果比较:

  • 如果过连接查询的两个表中都没有多余的数据,也就是主键字段下的每行字段值,外键字段下都有对应相等的字段值,那么内连接与外连接查询结果是相同的。
  • 如果两个表中有一个表中包含有多余的数据,那么内连接就相当于 左外连接查询与右外连接查询结果的交集,也就是去掉多余数据行。

5. 子查询

    1. 一个查询语句不仅可以由一个select语句组成,还可以由多个select语句嵌套进行查询

  • 可以将一个select语句查询出的结果作为另一个select语句查询的表(可以给这个表起别名来方便引用),也就是放在from关键字后面,那么外层的select关键字后选择查询的字段只能是内层select关键字后选择查询的字段,
  • 可以将一个select语句查询出的结果作为另一个select语句where语句后的条件语句的一部分,来进行一些条件过滤。

    2. 语法一般形式:

  • select * |[子查询中查询的字段] from 子查询语句  [别名]  where  ··· 
  • select * from ···  where   子查询语句构成的条件语句  ··· 

    3. 使用any与all关键字的子查询:all (子查询)  any (子查询),括号内可以是子查询,也可以是具体值的集合,比如(1,2,3,4)

  • >|<|<> any(子查询):表示大于|小于|不等于子查询结果集中某个值即可
  • >|<|<> all(子查询):表示字段值必须大于|小于|不等于子查询结果集中所有的值

 

 

 

 

 

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部