SQL语句详解大全

约定：

shell>window下输入命令

mysql>进入MySQL程序后，输入的MySQL的命令

---------------------------------------------------------------------------------------------------------

一·SQL简介

1·【SQL：Structured Query Language  结构化查询语句】

【它是客户与数据打交道的通道】

【作用】：是一种定义，操作，管理关系数据库的语法。大多数关系数据库都支持

---------------------------------------------------------------------------------------------------------------

2.【SQL是有工业标准的。ansi】

符合工业标准的SQL，称之为普通话

不同的数据库都在SQL工业标准的基础上进行扩展，扩展的部分称之为方言

——————————————————————————————————————————————————————————

3【验证安装是否成功】

shell>mysql -u root -p

mysql>SHOW DATABASE;//显示目前有几个数据库

——————————————————————————————————————————————————————————

二·【DDL：数据定义语言 Data Definition Language】

作用：定义数据库或者表结构的；

操作对象：数据库或者表的结构

关键字：【CREATE ALTER DROP】(create alter drop--增删改查)

练习：

//创建数据库

创建一个名称为mydb1的数据库

mysql>CREATE DATABASE mydb1

查看数据库创建的细节

mysql>SHOW CREATE DATABASE mydb1

创建一个使用gbk字符集，并带有矫正规则的mydb3数据库。

mysql>CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci

查看当前数据库服务器中的所有数据库

mysql>SHOW DATABASESES

查看前面创建mydb2数据库的定义信息

mysql>SHOW CREATE DATABASE mydb1

删除前面创建的mydb3数据库

mysql>DROP DATABASE mydb3

//【创建表】

创建表之前一定要先选择数据库

mysql>USE test

创建一个员工表

mysql>CREATE TABLE employee(

id int,

name varchar(200),

gender varchar(10),

birthday date,

entry_date date,

job varchar（200）,

salary float(8,2),

resume text

);

显示当前数据库中的所有表格

mysql>SHOW TABLES

在上面员工表的基础上增加一个image列

mysql>ALTER TABLE employee ADD image blob

查看表结构的定义

mysql>DES employee

修改job列，使其长度为60

mysql>ALTER TABLE employee MODIFY job varchar(60)

删除列

mysql>ALTER TABLE employee DROP image

标名改为user

mysql>RENAME TABLE employee TO user

查看创建表的细节

mysql>SHOW CREATE TABLE user

修改字符集为gbk

mysql>ALTER TABLE user CHARACTER SET gbk

列名name修改为username

mysql>ALTER TABLE user CHANGE name username varchar（100）

————————————————————————————————

三·DML:Data Manipulation Language 数据库操作语言

作用：操作表中的数据的

关键字：INSERT UPDATE DELETE

【特别注意：日期或字符串，字符要使用单引号引起来】

查看表中的所有记录

mysql>SELECT * FROM user 

使用insert语句向表中插入三个员工的信息

建议：mysql>INSERT INTO user 

(id,username,gender,birthday,entry_data,job,salary,resume) VALUES (3,'王祥云'，‘1’，‘1989-09-07’，'2013-04-12','UFO',10000.00,'good boy')

————————————————————————————————————————

【插入中文时的问题：（编码问题）】

查看数据库目前的各种编码：


mysql>SHOW VARIABLES LIKE 'character%';


通知服务器客户端(黑窗口)使用的编码字符集


mysql>SET character_set_client=gbk;


显示时乱码


mysql>SET character_set_results=gbk;

————————————————————————————————————————————————

将所有员工薪水修改为5000元

mysql>UPDATE user SET salary=5000;

将姓名为‘王祥云’的员工薪水修改为3000元；

mysql>UPDATE user SET salary=3000 WHERE username='王祥云'

将姓名为‘王祥云’的员工薪水修改为4000元，job改为CMO。

mysql>UPDATE user SET salary=4000,job='CMO' WHERE username='王翔云';

将zql的薪水在原有的基础上增加1000元

mysql>UPDATE user SET salary=salary+1000 WHERE username='zql'

删除表中名称为‘王祥云’的记录

mysql>DELETE FROM user WHERE username='王祥云‘

删除表中所有的记录

mysql>DELETE FROM user （一条一条的删除）

使用TRUNCATE删除表中的记录

mysql>TRUNCATE user;(摧毁整张表，然后重建表结构）

——————————————————————————————————————————————————

四·DQL简单的：Data Query Language 

关键字：SELECT 

查询表中所有学生信息

mysql>SELECT * FROM student

mysql>SELECT id ,name,chinese,english,math FROM student

查询表中所有学生的姓名和对应的英语成绩

mysql>SELECT name english FROM student

过滤表中重复的数据

mysql>SELECT DISTINCT english FROM student 

___________________________________________________________________________

【SELECT语句支持一些基本的运算】

在所有学生数学分数上加10分的特长分

mysql>SELECT id,name,math+10 FROM student

统计每个学生的总分

mysql>SELECT name,chinese+english+math FROM student

使用别名表示学生分数

mysql>SELECT name AS 姓名，chinese+english+math 总分 from student；（姓名，总分不要加单引号，AS了一省略，将经常用到）

查询姓名为王五的学生成绩

mysql>SELECT name,english.chinese,math FROM student WHERE name='王五'

查询英语成绩大于90分的同学


msyql>SELECT name,english,chinese,math FROM student WHERE english>90;


查询总分大于200分的所有同学

mysql>SELECT name,chinese+english+math FROM student WHERE (chinese


+english+math)>200;

————————————————————————————————————————————————

【WHERE语句支持运算表达式】

【注意：】

等于：=

不等于：<>

between……and显示在某一区间的值（包含头尾）

int （set）【显示在指定列表中的值为set的数据，列：int（80）或者in（100,200）[值为100或者200的数据]】

like ‘张pattern’【模糊查询】

is null判断是否为空

——————————————————————————————————

查询英语分数在80-90之间的同学

mysql>SELECT * FROM student WHERE english BETWEEN 80 AND 90;

查询数学分数为89,90,91的同学

mysql>SELECT * FROM student WHERE math IN(89,90,91)

  查询所有姓李的学生成绩

mysql>SELECT * FROM student WHERE  name like'李%'

查询数学分数，和语文分数大于80的同学

mysql>SELECT * FROM student WHERE math>80 AND chinese>80;

对总分排序后输出，然后在按照高低的顺序输出

mysql>SELECT name ，chinese+english+math FROM student ORDER BY (chinese+english+math) DESC

对姓李的学生数学成绩排序输出

mysql>SELECT name,math,FROM student WHERE name LIKE '李%' ORDER BY math

________________________________________---________________________________

五·数据的完整性

数据的完整性是为了保证插入到数据中的数据是正确的，它防止了用户可能的输入错误

一共三个方面：

【1·实体的完整性:规定表中的一行在表中是唯一的实体】。

一般通过自定义主键的形式来实现的

【主键】：

关键字；PRIMARY KEY 

特点：不能为null，必须唯一

CREATE TABLE SHANG_HALL(

id int PRIMARY KEY;----------如果用户输入id时与之前的id重复则会报错

name varchar（100）

………………

)；

//实际开发中不建议使用

//实际开发中不建议使用。
CREATE TABLE shanghai2(
id int PRIMARY KEY auto_increment,(auto_increment自动增长)
name varchar(100)
);
insert into shanghai2 (name) values('aa')

【2.域完整性】

指数据库的列（即字段）必须符合某种特定的数据类型或者约束

NOT NULL:不能为空

UNIQUE:必须唯一

CREATE TABLE shanghai3(

id int PRIMARY KEY,

name varchar NOT NULL,

idnum varchar(100) unique,

);

关于主键：

（建议）逻辑主键（PRIMARY KEY）:给编程人员用的。与具体业务无关。类似：id，无实际意义的东西

业务主键：用户也可以使用。但是与具体业务有关了。类似：身份证号码，有实际意义的东西

【3·参照完整性（多表设计）】

【外键：是主键在另一张表的表现形式，其内容参照主键（个人理解）】

【3、参照完整性（多表设计）】


【外键：是主键在另一张表中的表现形式，其内容参照主键（个人理解）】


一对多
create table department(
id int primary key,
name varchar(100)
);
create table employee(
id int primary key,
name varchar(100),
salary float(8,2),
dept_id int,
constraint dept_id_fk【这个是自己取的外键的名称，可随意，只要在


该表中唯一即可】 foreign key(dept_id) references department(id)
);

多对多
create table teacher(
id int primary key,
name varchar(100),
salary float(8,2)
);
create table student1(
id int primary key,
name varchar(100),
grade varchar(10)
);
create table teacher_student1(
t_id int,
s_id int,
primary key(t_id,s_id),------------【t_id,s_id组成联合主键，不


能出现重复的t_id,s_id组合。】
constraint t_id_fk foreign key(t_id) references teacher(id),
constraint s_id_fk foreign key(s_id) references student1(id)
);

一对一
create table human(
id int primary key,
name varchar(100)
);
create table idcard(
id int primary key,
num varchar(100),
constraint huanm_id_fk foreign key(id) references human(id)
);


--------------------------------------------------------------------------------------


---------------------------------------------------------
六、表的复杂查询
1、【连接查询】


【1.0连接的基本语法格式：】


from TABLE1 join_type TABLE2 [on (join_condition)][where 


(query_condition)]


TABLE1与TABLE2为参与连接操作的表


TABLE1:左表


TABLE2：右表


join_type：连接的类型。交叉(cross join)、内连接(inner 


join)、左外连接(left outer join)、右外连接(right outer join)


on：设置连接条件


where：设置查询条件





-------------------------------------------------------------------------------------
【1.1交叉连接：不带on子句，返回连接表中所有数据行的笛卡尔积】


select * from CUSTOMER cross join ORDERS;
或者
select * from CUSTOMER,ORDERS;

select c.name,o.order_number from CUSTOMER c,ORDERS o;





--------------------------------------------------------------------------------------


-
【1.2内连接：返回连接表中符合连接条件及查询条件的数据行】


【隐式内连接：(不使用on关键字和inner join，使用where设置连接条件


)】
select * from CUSTOMER c,ORDERS o where 


c.id=o.customer_id;


【显式内连接：(使用on关键字和inner join，在on子句张设置连接条件)


】
select * from CUSTOMER c inner join ORDERS o on 


c.id=o.customer_id;


显式与隐式返回的最终结果是一样的



--------------------------------------------------------------------------------------


--
【1.3外连接：分为左外连接、右外连接。与内连接不同的是，外连接不仅返回连接


表中符合连接条件及查询条件的数据行，也返回左表（左外连接）或右表（右外连接）中符合查询条件


但不符合连接条件的数据行】

【左外连接：(返回符合连接条件的所有记录，同时还返回左表中其余的所


有记录,在where子句中设置查询条件)】
select * from CUSTOMER c left outer join ORDERS o on 


c.id=o.customer_id;


【右外连接：(返回符合连接条件的所有记录，同时还返回右表中其余的所


有记录，在where子句中设置查询条件)】
select * from CUSTOMER c right outer join ORDERS o on 


c.id=o.customer_id;





--------------------------------------------------------------------------------------


---------------------------------
【2、子查询（嵌套查询）在select子句或者where子句中又嵌入select查询语句】


查询“陈冠希”的客户id 
select id from customer where name='陈冠希';   ---获得id=1；


查询“陈冠希”的所有订单信息
查询订单信息：  select * from orders where customer_id=1;


--------
子查询（整合上面2个步骤）： 
select * from orders where customer_id=(select id from 


customer where name='陈冠希');





--------------------------------------------------------------------------------------


----------------------------------
【3、联合查询：能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有


重复数据行的查询结果。】
关键字：
UNION，去除重复行
  UNION ALL，不去除重复行


SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE 


customer_id=1;


取两条语句的并集，并去除重复的记录。





--------------------------------------------------------------------------------------


----------------------------------
【4、报表查询（合计函数）（使用原来的test数据库）】
语句：
[select...] from ... [where...][group by ...[having...]][order 


by...]

【合计函数-count：】
count（列名）返回某一列，行的总数




统计一个班级共有多少学生？


select count(*) from student;


统计数学成绩大于90的学生有多少个？


select count(*) from student where math>=90;


统计总分大于250的人数有多少？


select count(*) from student where (chinese+math+english)>250;



【合计函数-sum：（sum仅对数值起作用）】
sum函数返回满足where条件的行数值和


统计一个班级数学总成绩？


select sum(math) from student;


统计一个班级语文、英语、数学各科的总成绩


select sum(chinese),sum(english),sum(math) from student;

统计一个班级语文、英语、数学的成绩总和


select sum(chinese+english+math) from student;


统计一个班级语文成绩平均分


select sum(chinese)/count(*) from student;




【合计函数-avg】
avg函数返回值满足where条件的一列的平均值


求一个班级数学平均分？


select avg(math) from student;


求一个班级总分平均分


select avg(chinese+english+math) from student;





--------------------------------------------------------------------------------------


-------
【注意】：
Tips:如果要使用关键字作为表名，要使用`(Esc按键下面的)包围起来。


group by指定按照那些字段分组

having对数据进行过滤

【注意：】


having和where均可实现过滤，但在having可以使用合计函数，having通


常跟在group by后。having是分组之后的过滤。

对订单表中商品归类后，显示每一类商品的总价


select product,sum(price) from orders group by product;


查询购买了几类商品，并且每类总价大于100的商品


select product,sum(price) from orders group by product having 


sum(price)>100;


【合计函数：】


时间日期相关的函数


字符串相关的函数

数学相关的函数

--------------------------------------------------------------------------------------


----------------------------------------------------------
七、MySQL的数据库的备份与恢复


数据库的备份：(不会备份数据库名)


shell>mysqldump -u root -psorry test>c:/test.sql --------将test数据库


备份到C盘中


恢复数据库：(数据库名必须存在)


方式一：
shell>mysql -u root -psorry test<c:/test.sql

方式二：
mysql>USE test;


mysql>SOURCE c:/test.sql;