SQL语句（一）

最新推荐文章于 2024-07-15 22:01:43 发布

Mr.Niu

最新推荐文章于 2024-07-15 22:01:43 发布

阅读量5.3k

点赞数 4

分类专栏： MySQL

本文链接：https://blog.csdn.net/sinat_33492854/article/details/70190014

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.简介

Structured Query Language结构化查询语言。SQL语句不依赖于任何平台，对所有的数据库是通用的。学会了SQL语句的使用，可以在任何的数据库使用。
SQL语句是一个非过程性的语言，每一条SQL执行完都会有一个具体的结果出现。
SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能。

2.SQL分类

DDL （数据定义问题）
- 数据定义语言 - Data Definition Language
- 用来定义数据库的对象，如数据表、视图、索引等
DML （数据操纵问题）
- 数据处理语言 - Data Manipulation Language
- 在数据库表中更新，增加和删除记录
  如 update， insert， delete
DCL （数据控制问题）
- 数据控制语言 – Data Control Language
- 指用于设置用户权限和控制事务语句
- 如grant，revoke，if…else，while，begin transaction
DQL （数据查询问题）（重点重点重点）
- 数据查询语言 – Data Query Language
- select

3.学习步骤

学习SQL数据对数据库的操作
学习SQL语句对数据表结构的操作
学习SQL语句对数据表中的数据记录操作
数据仓库中的数据备份和恢复

4.数据仓库的操作

4.1查询所有数据库

查询当前所有的数据库
- 语法：show databases
查看当前数据的创建方式：查看数据库的编码表
- 语法：SHOW CREATE DATABASE study

4.2创建数据库

创建数据库
- 语法：create database 数据库名
创建数据库并指定的编码
- 语法：create database 数据库名 character set 编码表名
创建数据库并指定的编码表同时还根据编码表指定排序规则
- 语法：create database 数据库名 character set 编码表名 collate 排序规则

4.3删除数据库

语法：drop database 数据库名

4.4修改数据库编码集

语法：alter database 数据库名称 character set 字符集 collate 比较规则
实例：ALTER DATABASE study CHARACTER SET GBK

4.5切换数据库和查看正在使用的数据库

切换数据库
- 语法：use 数据库名
查询当前正在使用的数据库
- 语法：select database();

5. 数据表结构的添查删改

5.1数据表的创建

一个数据表可以存在很多列（字段），每列具有类型和长度
语法：

create table 表名(
列名 类型(长度),
列名 类型(长度)... 
  );

Java中属性和数据库表中每列的数据类型
- 字符串型：
- Java： String、char
- MySQL：varchar(列的长度)、char(列的长度)
- 区别：假设我们要存储 abcd 这个字符串。
  1. 如果定义成varchar(10) 这时储存的数据不足10个，这时会把多余取消。
  2. 如果使用char(10)，它会把存储的数据添加到10个长度。
  3. 如果存储的长度超出了表中列的长度，存储报错。
- 大数据类型：
- Java： InputStream（字节流）与 Reader（字符流）
- MySQL： BLOB（保存的字节数据）与TEXT（保存字符数据）
- 通常我们不会把文件存储到数据库（占用资源，操作速度慢），我们会把文件的路径（通常存放在本地磁盘）存到数据库中。
- 数值型：
- Java：byte、short 、 int 、 long 、float、 double
- MySQL：tinyint 、smallint、int、bigint、FLOAT、DOUBLE
- 在创建数据表的时候，数值型也有自己的长度，一般不需要指定，使用默认的长度。
- 逻辑性：
- Java：boolean
- MySQL： BIT
- 日期型：
- Java：Date、Time、DateTime、TimeStamp
- MySQL：DATE、TIME、DATETIME、TIMESTAMP
- 注意：timestamp：它和datetime都可以保存年月日时分秒，但是timestamp它在保存数据的时候表中的这一列会自动的变成当前保存数据时的时间。
- test：
- 创建雇员表，包含雇员的姓名，密码，性别, 生日信息。

create table Employee(
name varchar(20),
psw varchar(20),
sex char(2),
birthday date
);

* ![](http://i.imgur.com/nr8R3k2.png)

5.2查看创建的表的结构

语法：desc 表名

5.3单表创建时约束

约束
- 通过某些限制，来规定当前某张数据表中的某列数据是否可以为null，是否可以重复，当前某一列中的数据在当前表中必须唯一等限制。
- 目的：只是为了保证存储在数据表中的数据完整性和有效性
- 语法：列名数据类型约束条件
主键约束：primary key
- 主键一般表示数据库中的某一列，使用这一列来区分数据表中的数据和其他数据不同。这一列的数据在整个数据表中是不允许重复的。
- 语法：列名列的类型 primary key
唯一约束:unique
- 该列（字段）的值不允许重复。
- 解释：一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束。(即:就是在一张表唯一约束的列可以有多列，但是主键一般只会使用一列或者两列（联合主键）)
非空约束:not null
- 该字段的值不能为空
自增长 auto_increment
- 如果表的主键是int 类型，这时可以在主键的后面添加 auto_increment ，这时表中这一列在添加数据的时候，会自动的增长。
- 一般主键使用java中的UUID类产生。

test

创建雇员表2：工号整形主键自增长，用户名唯一且不为空，密码不为空，性别 , 生日。

CREATE TABLE Employee2(
id INT PRIMARY KEY AUTO_INCREMENT,
uesrname VARCHAR(50) UNIQUE NOT NULL,
psw VARCHAR(40) NOT NULL,
sex VARCHAR(20),
birthday DATE
);

5.4数据表结构修改

可以对表名、表中的列名、列的类型、列的约束进行增删改。
- 语法：alter table 表名增/删/改列名类型(长度) 约束；
增加列
- 语法：alter table 表名 add 列名类型(长度) 约束;
- test1: 在employee2表上增加salary列
  - alter table Employee2 add salary double
- test2:在employee2表上增加age列
  - alter table Employee2 add age int
修改现有列类型、长度和约束
- 语法：alter table 表名 modify 列名类型(长度) 约束
- test1:修改birthday列不能为null
  - alter table Employee2 modify birthday date not null
- test2:修改username列的长度为60
  - alter table Employee2 modify uesrname varchar(60)
修改现有列名称
- 语法：alter table 表名 change 旧列名新列名类型(长度) 约束;
- test1:修改列名uesrname为name
  - alter table Employee2 change uesrname name varchar(60) not null
删除现有列
- 语法：alter table 表名 drop 列名
- test1：删除sex列
  - alter table Employee2 drop sex
修改表名
- 语法：rename table 旧表名 to 新表名;
- test:将employee2表名修改为person表
  - rename table Employee2 to person
修改表的字符集
- 语法：alter table 表名 character set 编码集;
- test1: 将employee的编码修改成utf8
  - alter table person character set utf-8
TEST:
1. 在employee2表上增加salary列
2. 修改birthday列不能为null
3. 修改name列的长度为60
4. 修改列名name为username
5. 删除sex列
6. 将employee2表名修改为person表
7. 将mydb2的编码修改成gbk
  * <找时间补>*

5.5数据表删除

语法：drop table 表名；
test:删除employee表
drop table employee

5.6查看数据表结构

查看库中有多少张表：
- show tables;
查看表结构：
- desc 表名;
查看定义表时，表的编码表
- show create table 表名;

6.简单数据表的增删改

6.1向数据表中插入数据

语法：insert into 表名 (列名，列名，列名......) values (值，值，值......);
注意事项：
1. 值与列一一对应。并且有多少个列，需要写多个值。如果某一个列没有值。可以使用null。表示插入空。
2. 值的数据类型，与列被定义的数据类型要相匹配。并且值得长度。不能够超多定义的列的长度。
3. 字符串类型：插入字符类型的数据，必须英文的单引号括起来。在mysql中。
4. date 时间类型：也必须用英文的单引号括起来，如 ‘yyyyMMdd’ ,’yyyy-MM-dd’,’yyyy/MM/dd’ 。
5. 在插入数据的时候，如果某些列可以为null，或者是自动增长的列，或者有默认值的，在插入的时候可以省略。
6. 如果给表中的所有列插入数据，这时可以省略表名后面的列名，直接写values
test
1. 给表中的所有列插入数据
2. 简化给表中的所有列插入数据
3. 给表中不为空的列插入数据
  - INSERT INTO person (id,NAME,psw,birthday,salary,age) VALUES (101,'hah','123','2017-12-11',12000,24);
  - - INSERT INTO person (id, NAME, psw, birthday) VALUES (NULL, 'hehe','123', '2016-4-12');

6.2 数据记录修改操作

语法: update 表名 set 列名=值,列名=值.... [ where条件语句 ];
注意事项：
1. 如果不加条件，将会修改某一列的所有值。
2. 一般修改数据时，都需要增加条件。
test:
1. 修改person表中的所有用户的age 为 30岁
2. 修改姓名为hah的这个用户的薪水 88888
3. 把id为3 的用户的 name 和psw 修改为 lisi
4. 把id为4 的这个用户用户名修改为中文的王五;
5. 代码：
  - update person set age = 20;
  - update person set salary = 88888 where name = 'hah'
  - update person set name = 'lisi', psw = 'lisi' where id = 3
  - update person set name = '王五' where id = 4;

6.3数据记录的删除操作

语法：delete from 表名 where条件语句
注意：如果删除表中的记录时，没有添加where条件，这时会把表中的所有数据删除。
test
1. 删除person表中name为lisi的用户记录
2. 删除表中所有记录
  - delete from person where name ='lisi'
  - delete from person
如果要删除一张表中的所有数据，这时可以使用 TRUNCATE TABLE 表名;

6.4 面试题

删除表中的数据时没有加where 条件，会删除表中的所有数据，它与truncate 有什么区别？
- delete删除表中的数据是按照行删除。
- truncate 它是先把表删掉，然后再把表创建出来。
delete from person 和 drop table person 有什么区别
- delete from person:把person表中的所有数据全部删除，但是person的表还存在。
- drop table person:把person数据表从数据库中删除。

7.数据表记录的查询

准备工作

create table student(
    id int primary key auto_increment,
    name varchar(32) not null,
    age int ,
    gender varchar(10) not null,
    score double not null,
    birthday date
);

insert into student (id,name,age,gender,score,birthday) values(null,'zhangsan',23,'male',98.99,'1990-09-09');
insert into student (id,name,age,gender,score,birthday) values(null,'lisi',23,'男',56.99,'1990-02-09');
insert into student (id,name,age,gender,score,birthday) values(null,'王五',24,'女',75.99,'1988-01-01');
insert into student (id,name,age,gender,score,birthday) values(null,'赵六',25,'男',80.99,'1980-11-12');
insert into student (id,name,age,gender,score,birthday) values(null,'王思聪',null,'女',84,null);

7.1 查询数据库中的某张表的所有数据

语法select * from 表名;

7.2 查询某张表中指定的列

语法： select 列名,列名... from 表名;
test:查询所有学生的姓名和成绩
- select name, score from student

7.3 按条件查询

语法：select 列名,列名..... from 表名 where 条件;
test：查询表中年龄大于等于24岁的学生信息
- select * from student where age >= 24

7.4 运算符

相等 = 　　不等 <> 　　 !=　不等于
- test：查询年龄不是25岁的学生
- select * from student where age != 25
区间：between …and… 在两者之间取值 between 70 and 80
- 等价于 >=70 并且<=80 注意前面那个数要比后面那个数要小
- test:查询成绩在80~100(包含)之间的学生信息
  - select * from student where score >= 70 and score > 100;
  - select * from student where score between 70 and 100;
in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90
- where 列名 in (值，值，值…….);
- where 列名=值 or 列名=值 or 列名=值 …….
- test:查询年龄为18，23，25的同学信息
  - select * from student where age= 18 or age = 23 or age = 25;
  - select * from student where age in (18,23,25)
like (‘模糊查询pattern’) 进行模糊查询，表达式有两个占位符 % 任意字符串 _ 任意单个字符
- 例子
  1. name like ‘张%’ 所有姓张学员张%
  2. name like ‘张’ 所有姓张名字为两个字学员张
- test：查询所有姓赵的学生信息
- select * from student where name like '赵%'
is null 判断该列值为空
- sql中对伊null的判断，不能写 = null 在sql 中 null = null 结果不成立。
- 正确格式：
  1. is null
  2. 不是null ： is not null
- test
  1. 查询没有生日学员信息
  2. 查询有年龄学员信息
  3. select * from student where birthday is null
  4. select * from student where age is not null
and 逻辑与 or 逻辑或 not 逻辑非
- test:查询年龄>23，成绩>80的同学信息
- select * from student where age > 23 and score > 80
test
1. 查询成绩在80~100(包含)之间的学生信息
2. 查询年龄为18，23，25的同学信息
3. 查询所有姓赵的学生信息
4. 查询没有生日学员信息
5. 查询有年龄学员信息
6. 查询年龄>23，成绩>80的同学信息

7.5对查询结果进行排序

使用order by 子句排序查询结果。
语法：select * from 表名 order by 列名 asc|desc ;
- asc是升序排列，desc是降序排列
test
1. 对成绩排序后输出
  - 升序：select * from student order by score asc
  - 降序：select * from student order by score desc
2. 对年龄排序按从高到低（降序）的顺序输出
  - select * from student order by age desc
3. 对学生年龄按照降序排序，年龄相同按照成绩降序
  - select * from student order by age desc, score asc

7.6别名：可以对查询出来的列名起别名

语法：select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;
注意：在使用别名的时候，as 关键字可以省略。
test
1. 给年龄和分数起别名，并且按照年龄倒序，分数倒序
  - select age as 年龄, score as 分数 from student order by age desc, score desc
2. 省略关键as 再次查询
  - select age 年龄, score 分数 from student order by age desc, score desc

7.7 test

select age score from student;与select age , score from student;什么区别？
7.7.1
7.7.2
第一个是把查询出来的学生年龄起别名为score。第二个是查询当前student表中中的age 和score两列数据。

8.SQL的函数

SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：
1. count 统计个数（行数）
2. sum函数：求和
3. avg函数：求平均值
4. max、min 求最大值和最小值

8.1 count函数

语法：select count(*)|count(列名) from 表名
注意： count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。
test:
1. 统计一个班级共有多少学生？
  - select count(*) from student
2. 统计成绩大于80的学生有多少个？
  - select count(*) from student where sorce > 80

8.2 sum函数

语法：select sum(列名) from 表名;
注意事项：
1. 如果使用sum 多列进行求和的时候，如果某一列中的有null，这一列所在的行中的其他数据不会被加到总和。
2. 可以使用mysql 数据库提供的函数 ifnull(列名,值)
3. 在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)numeric double float
test
1. 统计一个班级成绩和
  - select sum(score) from student
2. 分别统计年龄和, 成绩和
  - SELECT sum(age), sum(score) from student
3. 统计年龄和成绩和值（）
  - select sum(age+score) from student

8.3 avg函数

语法： select avg(列名) from 表名;
test
- 求一个班级平均分
- select avg(score) from student

8.4. max，min函数

Max/min函数返回满足where条件的一列的最大/最小值
test
- 求班级最高分和最低分
- select max(score),min(score) from student

9. group by 分组函数

准备工作

create table orders(
    id int,
    product varchar(20),
    price float
);

insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);

语法：select … from … group by 列名，列名
分组：按照某一列或者某几列。把相同的数据，进行合并输出。
目的：仍然是统计使用。
使用group by 子句对列进行分组。group by 它可以根据指定列对数据进行归类。如果这一列中有重复的数据会被合并成一个
注意事项：
1. 聚集函数：分组之后进行计算；
2. 通常 select的内容：a 被分组的列，b 函数。
3. 如果遇到这种情况按照每种，每个。类似的这些语句的时候，通常会使用分组。
4. 如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。
5. where 后不能跟聚合函数，having中可以跟聚合函数。
test
1. 对订单表中商品归类后，显示每一类商品的总价
  - select product,sum(price) from orders group by product
2. 查询总价大于150的商品是哪几个。

9.1select 语句的执行顺序

标准SQL的解析顺序为：
（1）from 子句, 组装来自不同数据源的数据
（2）where子句, 基于指定的条件对记录进行筛选
（3）group by 子句, 将子句划分为多个分组
（4）使用聚合函数进行计算
（5）使用having子句筛选分组
（6）计算所有的表达式
（7）使用order by 对结果集进行排序
标准SQL的解析顺序为：
（1）from 子句, 组装来自不同数据源的数据
（2）where子句, 基于指定的条件对记录进行筛选
（3）group by 子句, 将子句划分为多个分组
（4）使用聚合函数进行计算
（5）使用having子句筛选分组
（6）计算所有的表达式
（7）使用order by 对结果集进行排序
- 查询总价大于150的商品是哪几个
- select product,sum(price) from orders group by product having sum(price) > 150