1. sql语言
Structured Query Language, 结构化查询语言----是标准的操作数据库的语言。
特点:
举例(java int a= 1, b=2; a+b);
非过程性语言(每个语句是独立的)一个语句一个结果
为加强SQL的语言能力,各厂商增强了过程性语言的特征
如Oracle的PL/SQL 过程性处理能力
SQL Server、Sybase的T-SQL
SQL是用来存取关系数据库的语言。
具有查询、操纵、定义和控制关系型数据库的四方面功能
DDL:data definition language(数据库定义语言) create alter drop
DCL:Data Control Language(数据库控制语言):grant
DML:data manipulation language(数据操纵语言):insert update delete。。。
DQL:并不是标准的说法。---select
l DDL (数据定义问题)
• 数据定义语言 - Data Definition Language
• 用来定义数据库的对象,如数据表、视图、索引等
l DML (数据操纵问题)
• 数据处理语言 - Data Manipulation Language
• 在数据库表中更新,增加和删除记录
• 如 update, insert, delete
l DCL (数据控制问题)
• 数据控制语言 – Data Control Language
• 指用于设置用户权限和控制事务语句
• 如grant,revoke,if…else,while,begin transaction
l DQL (数据查询问题)
• 数据查询语言 – Data Query Language
• select
1、数据库的操作语句,表结构的操作语句,表记录的操作语句
1.1. 数据库操作语句
1.1.1. 创建数据库
语法1:create database 数据库名 -----表示创建一个数据库。采用的编码集是数据库本身默认的编码集
语法2:create database 数据库名 character set 编码集 collate 排序方式
注意:mysql中utf8 (没有-)
-----show databases;
1.1.2. 查询数据库
show databases ;-----查看所有的数据库
show create database 数据库名 ; ----查看数据库编码集
1.1.3. 删除数据库
drop database 数据库名;
1.1.4. 数据库的修改操作
修改数据库,通常是指修改数据库的编码集
alter database 数据库名 charater set 编码集 collate 校对规则;
1.1.5. 其他
use 数据库名 ----表示的切换当前数据库到 给定的数据库名
1.2. 数据表结构的sql语句
1.2.1. 数据表的创建语句
写法:
create table 表名(
列名数据类型(长度) 约束,
列名数据类型(长度)约束,
,………..
)
字符串型
VARCHAR、CHAR
varchar ----长度可变, varchar(10) ---10以内的。
char长度固定--- char(10) 存放’lisi’ ----剩下的使用空格去占位。
------效率 char效率更高。
数值型
TINYINT 、SMALLINT、INT、BIGINT、
我们通常使用的是 int
FLOAT、DOUBLE
逻辑性
BIT ---- 0 或 1
------char(1)
日期型
DATE、TIME、DATETIME、TIMESTAMP
data:只有日期----只包含年月日的信息
time:只有时间 ---时分秒
datetime : 既有日期又有事件
timestamp:既有日期又有事件
从外观上看没有任何的区别—timestamp当数据库做更新的时候,自动更新。
大数据类型
BLOB、TEXT
blob ----存放大的二进制的数据,图片,音频,视频
text----大文本。 txt
tinyblob tinytext 255字节
blob text 64KB
mediumblob mediumtext 16MB
longblob longtext 4GB
注意:文件的存贮,通常不会把文件存放到数据库中。存放到硬盘上。数据库中存放的是文件路径。
创建一个员工表employee ---- 查看表结构: desc 表名;
create table employee(
id int,
name varchar(20),
gender varchar(10),
birthday date,
entry_date date,
job varchar(15),
salary double,
resume text
);
1.2.2. 约束
意义:保证数据的有效性和完整性。
单表而言----单表约束
主键约束:primary key 表示该字段唯一并且不能为空,(可以定位到表中的确定的一行)
如果主键是整形的,可以设置自动增长 auto_increment
唯一约束:unique 表示该字段的内容不能重复出现,
区别:1、主键是不能为空,唯一约束可以空
2、主键约束在一张表中只能有一个,唯一约束可以有多个。
非空约束:不允许为空。not null
1.2.3. 数据表结构的修改
1、添加表的字段信息
alter table 表名 add列名数据类型(长度)约束;
alter table employee modify name varchar(19);//修改列表的数据类型
2、修改列长度,或者约束---增加长度
alter table 表名modify列名数据类型(长度)约束;-----修改之后的长度或者约束。
3、删除一列------一般情况下不会。
alter table 表名 drop 列名;
4、修改列的名字----一般情况下不会做,
alter table 表名 change 旧的列名 新的列名数据类型(长度) 约束;
5、修改表名----一般情况下不会做。
rename table 旧表名 to 新表明
6、修改表的字符集-----也不会修改,
alter table 表名 character set 字符集
1.2.4. 数据表的删除
语法:drop table 表名;
1.2.5. 查看表结构
1、show tables; 查看所有的表
2、desc 表名 ;查看表的字段信息
3、 show create table 表名;----查看建表语句。
1.3. 数据记录的增删改查(重点)
1.3.1. insert 语句
写法1:
insert into 表名(列名1,列名,列名) values(值1,值,值。。。);
1、值与列要一一对象,有多少个值,要有多少个列,顺序相同,
2、插入的值要与字段的数据类型进行相匹配,
3、插入值的长度,不能超过列定义的长度,
4、如果插入的是字符类型,或者日期类型,插入的数据必须用单引号包围起来。
5、某些列是可以省略不写的,(允许为空,有默认值)
-------select * from 表名 :表示查看表的所有列的所有记录
写法2:省略了字段
insert into 表名 values(值,值,值。。。);
1、列对应值的顺序,是按照表中的列的顺序去对应。
当insert插入数据的时候出现
client conntion result ------客户端方面的字符集
server database system----服务器端的字符集。
修改方法:
查看系统所有字符集 : show variables like 'character%';
第一种:当前窗口临时修改set names gbk ;
* 只对当前窗口有效,关闭后就会失效
第二种:配置mysql/my.ini文件
[mysql] 客户端配置
[mysqld] 服务器端配置
修改客户端字符集[mysql] 后字符集 default-character-set=gbk
--------gbk后面不要跟任何的字符。
1.3.2. update 语句
update 表名 set 列名=值,列名=值…. where 条件
1、如果没有where,表示的是修改表中的所有记录。
2、update语句只能修改行数据。
1.3.3. delete语句
删除表中的数据记录
delete from 表名 where 条件。-----
-----truncate table 表名
truncate 先删除表,然后在建表。------语句DDL
delete是逐行的删除数据。 truncate效率高于delete
truncate 删除的记录是不能恢复的。
在事务中 delete删除的语句是可以恢复的。
----update ,insert ,delete 都可以恢复
----start transaction; 开启事务
----rollback; 事务回滚。
-----commit ;提交事务。
1.3.4. select语句
语法一:
select * from 表名;----查询表的所有记录
select 列名,列名,列名…….. from表名
select distinct(列名) ,列名。。。。。from 表名
distinct 去掉重复数据。
语法二:使用表达式对查询结果进行列运算
select 列名 +(-*/)列名 from 表名
****使用as 为查询结果添加别名
select 列名 as 名称 from 表名
----as可以省略
select 列名 from 表名 as 名称
-----可以使用表名的别名.列名 去查询数据
语法三:
select * from 表名 where 条件
比较运算符 | > < <= >= = <> | 大于、小于、大于(小于)等于、不等于 注意= 和 <> |
between .. and .. | 显示在某一区间的值 between 70 and 80 在70 到 80之间 | |
in(set) | 显示在in列表中的值,例:in(100,200) =100 or =200等于其中的某一个值 | |
like ‘张pattern’ | 模糊查询 % 代表的是任意字符 ‘张%’ 表示所有以姓张开头的同学 _ 表是的任意的一个字符‘张_’ 表示姓张的,并且2个字的。 | |
Is null | 判断是否为空 | |
逻辑运算符 | and | 多个条件同时成立 |
or | 多个条件任一成立 | |
not | 不成立,例:where not(salary>100); |
语法四:排序
select * from 表名 order by 列名 (asc|desc),列名(asc|desc)…… 默认是升序排列
目的1:统计;
语法五:聚集函数,统计结果,目的:统计。
count(列名) 表示的是返回 某一列行的总数。
sum(列名)
select sum(列名) from 表名 where 条件
----null 与其他数值进行运算结果是null
-----ifnull(字段,默认值)
sum(列)在进行运算的时候 把null排除或者认为 null是0
avg(列) --- 求平均值
max(列)----列中的最大值 ,min(列)----列中的最小值
max或者min(列的类型不一定是数值类型);
语法五:分组
根据某一列或者某几列,把相同的数据进行合并。
语法:
select 列名 from 表名 group by 列名,列名 having 过滤条件。
注意:1、查询的列名一定是分组的列名,如果列名没有参加分组不能查询
2、对于聚集函数,针对没一组进行单独的运算
3、where 条件 ,having 条件过滤
where是分组之前进行过滤
having分组之后进行过滤
having可以写聚集函数 去进行判断。where里面不可以。
where里的所有条件,都可以书写在 having 内。
1.4. 数据库的备份和恢复
备份数据库表中的数据
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
恢复数据库
source 文件名.sql // 在mysql内部使用
cmd>mysql –u 用户名 -p 数据库名 < 文件名.sql // 在cmd下使用
数据库备份, 并没有把数据库的数据库创建语句进行备份。
课堂练习:
创建一个名称为mydb1的数据库。
create database mydb1;
创建一个使用utf8字符集的mydb2数据库。
create database mydb2 CHARACTER set utf8;
创建一个使用utf8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8collate utf8_general_ci;
创建一个员工表employee---- 查看表结构: desc 表名;
create table employee(
idint,
namevarchar(20),
gendervarchar(10),
birthdaydate,
entry_datedate,
jobvarchar(15),
salarydouble,
resumetext
);
insert into employee (name,job,salary) values('zs','manger',124.4);
创建一个员工表employee
create table employee2(
idint primary key auto_increment,
namevarchar(20) unique not null,
gendervarchar(10),
birthdaydate,
entry_datedate,
jobvarchar(15),
salarydouble not null,
resumetext
);
在上面员工表的基本上增加一个image列。
alter table employee2 add imagevarchar(50);
修改job列,使其长度为60
alter table employee2 modify jobvarchar(60);
删除gender列。
alter table employee2 drop gender;
把name字段 改为username
alter table employee2 change name usernamevarchar(20) unique not null;
把employee2 改名为user
rename table employee2 to user;
向user表中添加一条记录
insert into user (id,name,salary)values(null,'wangwu',10000);
将所有员工薪水修改为5000元。
update user set salary=5000 ;
将姓名为’zs’的员工薪水修改为3000元。
update user set salary=3000 where username='zs';
将姓名为’ls’的员工薪水修改为4000元,job改为ccc。
update user set salary=4000,job='ccc' whereusername='ls';
将wangwu的薪水在原有基础上增加1000元。
update user set salary=salary+1000 whereusername='wangwu';
update user set salary=5000+1000 whereusername='wangwu';
删除表中名称为’zs’的记录。
delete from user where username='zs';
删除表中所有记录。
delete from user ;
使用truncate删除表中记录。
查询英语分数在 80-90之间的同学。
select * from exam where english between 80and 90;
select * from exam where english > 80and english <90;
查询数学分数为89,75,91的同学。
select * from exam where math in(89,75,91);
查询所有姓刘的学生成绩。
select * from exam where name like '刘%';
查询所有姓刘两个字的学生成绩。
select * from exam where name like '刘_';
查询数学分>80并且语文分>80的同学。
select * from exam where math > 80 andchinese >80;
查询数学分>80 或者 语文分>80的同学。
select * from exam where math >80 orchinese > 80;
对数学成绩排序后输出。
select * from exam order by math;
对总分排序按从高到低的顺序输出
select name, chinese + math + english as 总分 from exam order by chinese + math+ english desc;
对姓李的学生成绩排序输出
-----count
统计一个班级共有多少学生?
select count(id) from exam;
统计数学成绩大于90的学生有多少个?
select count(id) from exam wheremath>90;
统计总分大于250的人数有多少?
select count(id) from exam where (math +chinese + english) >= 250;
统计一个班级数学总成绩?
select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩
select sum(chinese) as 语文总成绩, sum(english) 英语总成绩,sum(math) 数学总成绩 from exam;
统计一个班级语文、英语、数学的成绩总和
select sum(chinese+english+math) from exam;
select sum(chinese)+sum(english)+sum(math)as 总成绩 from exam;
统计一个班级语文成绩平均分
select sum(chinese)/count(id) from exam;
求一个班级数学平均分?
select avg(math) from exam;
求一个班级总分平均分
selectavg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
求班级最高分和最低分(数值范围在统计中特别有用
selectmax(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)),min(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
----------------------------------------
create table orders(
idint,
productvarchar(20),
pricefloat
);
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 product,sum(price) from orders groupby product ;
总价话费超过100的商品
select product from orders group by product having sum(price) > 100
;
selectprodcut_id,max(product) from ordersgroup by prodcut_id