一、SQL语言
结构化查询语言
操作关系型数据库
二、操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
~创建一个名称为mydb1的数据库。
CREATE DATABASE mydb1;
~创建一个使用gbk字符集的mydb2数据库。
create database mydb2 character set gbk;
~创建一个使用utf8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin;
2.查看数据库
查询所有数据库:
show databases;
查看数据库的创建语句:
show create database db_name;
3.删除数据库
drop database db_name;
~删除前面创建的mydb1数据库
drop database mydb1;
4.修改数据库
ALTER DATABASE [IF NOT EXISTS] db_name [alter_specification [, alter_specification] ...]
alter_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
~ 查看服务器中的数据库,并把其中某一个库的字符集修改为utf8;
alter database mydb2 characer set utf8;
5.选择数据库
选择数据库:use db_name;
查看当前所在的数据库:select database();
三、操作数据库表
1.创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
...
)character set 字符集 collate 校对规则
field:指定列名 datatype:指定列类型
~创建employee表
create table employee(
id int primary key auto_increment ,
name varchar(20) unique,
gender bit not null,
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text
);
约束:可以为字段定义一些限制
主键约束 primary key 可选的 auto_increment 主键自动增长
非空约束 not null
唯一约束 unique
外键约束
2.查看表
查询当前数据库中所有的表 show tables;
查看表结构 desc tab_name;
查看建表语句 show create table tab_name;
3.修改表
ALTER TABLE table ADD/MODIFY/DROP/change/character set (column datatype [DEFAULT expr] [, column datatype]...);
rename table 表名 to 新表名;
~在上面员工表的基本上增加一个image列。
alter table employee add image blob;
~修改job列,使其长度为60。
alter table employee modify job varchar(60);
~删除gender列。
alter table employee drop gender;
~表名改为user。
rename table employee to user;
~修改表的字符集为gbk
alter table user character set gbk;
~列名name修改为username
alter table user change name username varchar(20);
4.删除表
drop table tabName;
~删除之前的user表
drop table user;
四、操作数据记录 - 增删改查 - CRUD
注意:
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);
insert into exam values(null,'关羽',85,76,70);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);
create table cust(
id int primary key auto_increment,
name varchar(20)
);
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
insert into cust values (null,'aaa');
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);
insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);
1.Insert语句
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
~使用insert语句向表中插入三个员工的信息。
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)
values(null,'张飞',0,'1990-09-09','1990-10-01','打手',998.0,'真的很能打...');
insert into employee values (null,'关羽',1,'1988-08-08','1989-09-09','财神',9999999.9,'公司挣钱都指着他了....');
insert into employee values
(null,'刘备',1,'1980-09-09','1980-10-01','ceo',10.0,'公司的老大')
,(null,'赵云',0,'1999-09-09','2000-01-01','保安队长',10000.0,'公司老大的贴身保镖,关系很亲密...');
**mysql的乱码:
方案1:set names gbk; -- 通知服务器和当前客户端交互时应该使用的编码.
案例2:修改mysql服务器的配置,指定服务器默认认为的客户端编码集 找到mysql的安装目录下的my.ini,修改[mysql] default-character-set=gbk 修改即可
2.Update语句
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
~将所有员工薪水修改为5000元。
update employee set salary = 5000.0;
~将姓名为’张飞’的员工薪水修改为3000元。
update employee set salary=3000 where name='张飞';
~将姓名为’赵云’的员工薪水修改为4000元,job改为ccc。
update employee set salary=4000,job='ccc' where name='赵云';
~将刘备的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='刘备';
3.Delete语句
delete from tbl_name[WHERE where_definition]
~删除表中名称为’张飞’的记录。
delete from employee where name='张飞';
~删除表中所有记录。
delete from employee;
~使用truncate删除表中记录。
truncate table employee;
4.Select语句
(1)基本查询
SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;
~查询表中所有学生的信息。
select * from exam;
~查询表中所有学生的姓名和对应的英语成绩。
select name,english from exam;
~过滤表中重复数据。
select distinct english from exam;
~在所有学生分数上加10分特长分显示。
select name,english+10,math+10,chinese+10 from exam;
~统计每个学生的总分。
select name,math+english+chinese from exam;
~使用别名表示学生总分。
select name as 姓名 ,math+english+chinese as 总成绩 from exam;
select name 姓名 ,math+english+chinese 总成绩 from exam;
(2)使用Where子句进行过滤查询
~查询姓名为XXX的学生成绩
select * from exam where name='张飞';
~查询英语成绩大于90分的同学
select * from exam where english>90;
~查询总分大于230分的所有同学
select name 姓名,math+english+chinese 总分 from exam where math+english+chinese>230;
~查询英语分数在 80-100之间的同学。
select name,english from exam where english between 80 and 100;
~查询数学分数为75,76,77的同学。
select name,math from exam where math in(75,76,77);
~查询所有姓张的学生成绩。
select * from exam where name like '张%';
select * from exam where name like '张_';
select * from exam where name like '张__';
~查询数学分>70,语文分>80的同学。
select * from exam where math>70 and chinese>80;
(3)排序查询
SELECT column1, column2. column3.. FROM table order by column [asc|desc]
~对语文成绩排序后输出。
select name ,chinese from exam order by chinese desc;
~对总分排序按从高到低的顺序输出
select name 姓名,math+english+chinese 总分 from exam order by 总分 desc;
~对姓张的学生成绩排序输出
select name 姓名,ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0) 总分 from exam where name like '张%' order by 总分 desc;
(4)聚合函数
~1.count() -- 计数
~统计一个班级共有多少学生?
select count(*) from exam;
~统计数学成绩大于70的学生有多少个?
select count(name) from exam where math>70;
~统计总分大于230的人数有多少?
select count(math+english+chinese) from exam where math+english+chinese>230;
~2.sum() -- 求和
~统计一个班级数学总成绩?
select sum(math) from exam;
~统计一个班级语文、英语、数学各科的总成绩
select sum(math) ,sum(english) ,sum(chinese) from exam;
~统计一个班级语文、英语、数学的成绩总和
select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
~统计一个班级语文成绩平均分
select sum(chinese)/count(*) 语文平均分 from exam;
~3.avg() -- 求平均
~求一个班级数学平均分?
select avg(ifnull(math,0)) from exam;
~求一个班级总分平均分?
select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
~4.Max/Min -- 最大/最小
~求班级最高分和最低分(数值范围在统计中特别有用)
select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
(5)分组查询
SELECT column1, column2. column3.. FROM table group by column having ...
~对订单表中商品归类后,显示每一类商品的总价
select product,sum(price) from orders group by product;
~查询购买了几类商品,并且每类总价大于100的商品
select product ,sum(price) from orders group by product having sum(price)>100;
~查询单价小于100总价大于100的商品的名称.
select product,sum(price) from orders where price<100 group by product having sum(price)>100;
**where 和 having都可以实现过滤,where是在分组之前进行过滤,having是在分组之后进行过滤,where中不可以使用聚合函数,having中可以使用聚合函数.
***sql的编写顺序:select from where groupby having orderby
***sql的执行顺序:from where select group by having order by
===================================================================================================================================================
五、备份和恢复数据库
备份:
在cmd窗口中:mysqldump -u 用户名 -p 数据库名>文件名.sql
恢复:
方法1:在cmd窗口中 mysql -u 用户名 -p 数据库名<文件名.sql -- 只能恢复数据,不能恢复数据库本身!
方法2:在mysql命令行下执行 source 文件名.sql -- 可以在当前位置执行sql文件从而恢复数据,但是仍然只能恢复数据,不能恢复数据库本身!
===================================================================================================================================================
六、外键:用来声明表和表之间的关系的键,可以使数据库维系这种关系,一旦某个操作执行时会破坏这种关系,数据库会进行阻止.
foreign key(ordersid) references orders(id)
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'人事部'),(null,'财务部'),(null,'销售部'),(null,'科技部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key (dept_id) references dept(id)
);
insert into emp values (null,'张无忌','1'),(null,'奥巴马',2),(null,'哈利波特',3),(null,'金正恩',4),(null,'朴乾',4);
===================================================================================================================================================
七、多表设计、多表查询
1.多表设计
1-1:
1-*:
*-*:
图解:
2.多表查询
笛卡尔积查询:这种查询,查出来的结果是两张表相乘的结果,如果左边表有m条记录右边表有n条记录,最终查处m*n条记录.但是这种查询其中往往包含大量的错误的数据,所以笛卡尔积查询通常我们不使用.
select * from dept ,emp;
内连接查询:只查寻出左边表有记录且右边表也有对应记录的数据
select * from dept,emp where dept.id = emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id;
外连接查询:
左外连接查询:在内连接的基础上增加上左边表有而右边表没有的记录
select * from dept left join emp on dept.id=emp.dept_id;
右外连接查询:在内连接的基础上增加上右边表有而左边表没有的记录
select * from dept right join emp on dept.id=emp.dept_id;
全外连接查询:在内连接的基础上增加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录
select * from dept full join emp on dept.id=emp.dept_id; # mysql 不支持全外链接!!!
select * from dept left join emp on dept.id=emp.dept_id
union
select * from dept right join emp on dept.id=emp.dept_id; #可以通过union拼接左外和右外的结果模拟全外连接