约定:
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;