SQL常用操作

sql语句，一种用于操作数据库的语言

--

数据库， 简单地理解为硬盘上的文件， 可以存放数据

--

sql 语句大致可以分类两大类

--

针对数据库database和表table的操作

创建create

查看show

修改alter

删除drop

--

// 创建一个数据库

create database mydb;

--

// 查看所有的数据库库

show databases;

--

// 删除 mydb

drop database mydb;

--

// 删除 user 表

drop table user;

--

针对表中的记录的操作

增 insert

删 delete

改 update

查 select

--

一、 操作数据库

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

create database mydb1;

创建一个使用utf-8字符集的mydb2数据库。

create database mydb2 character set utf8;

创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

create database mydb3 character set utf8 collate utf8_general_ci;

--

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

show databases;

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

show create database mydb2;

删除前面创建的mydb3数据库

drop database mydb3;

--

将mydb2的字符集修改为gbk

alter database mydb2 character set gbk;

--

// 备份数据库

use mydb2;

create table a

(

name varchar(20)

);

// 插入数据

insert into a (name) values("zhangsan");

insert into a (name) values("wangwu");

--

// 备份数据库

mysqldump -uroot -p mydb2 > d:\a.sql 

--

// 删除 mydb2

drop database mydb2;

--

// 恢复数据库

create database mydb22;

source d:\a.sql

--

// source 命令

指定一个sql脚本

--

二、操作表

1. 创建表

创建员工表

Id整形

name字符型

sex字符型或bit型

brithday日期型

Entry_date日期型

job字符型

Salary小数型

resume大文本型

--

create table employee

(

id int,

name varchar(20),

sex varchar(6),

brithday date,

entry_date date,

job varchar(20),

salary double,

reshme text

);

--

2. 修改表

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

alter table employee add image blob;

--

修改job列，使其长度为60

alter table employee modify job varchar(60);

--

删除sex列。

alter table employee drop sex;

--

表名改为users。

rename table employee to user;

--

修改表的字符集为utf-8

alter table user character set utf8;

--

列名name修改为username

alter table user change column name username varchar(20);

--

查看所有表

show tables;

--

查看表的创建语句

show create table user;

--

查看表结构

desc user;

--

删除 user 表

drop table user;

--

三、数据的增删改查

1. insert语句

employee.sql

create table employee

(

id int,

name varchar(20),

sex varchar(10),

birthday date,

salary float,

entry_date date,

resume text

);

--

// 向员工表插入三条记录

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,'zhangsan','male','1987-11-23',1500,'2010-2-18','good boy');

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'wangwu','male','1988-11-23',1200,'2010-2-18','good boy');

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'xiaohong','female','1983-11-23',1800,'2010-2-18','good girl');

--

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(4,'赵楠','男','1986-11-23',3800,'2011-2-18','绝种好男人');

--

// 查看表的所有记录

select * from employee;

--

// 查看数据使用的所有编码

show variables like 'character%';

--

// 修改客户端的编码 为 gbk

set character_set_client=gbk;

--

// 修改结果集的编码为gbk

set character_set_results=gbk;

--

// insert 语句的其他写法

create table a

(

username varchar(20),

password varchar(20)

);

--

insert into a values('zs','111');

--

insert into a values('wangwu','2222'),('lisi','3333');

--

insert a values('aaa','bbb');

--

insert a (username) values('aaa');

--

2. update语句

--

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

update employee set salary=5000;

将姓名为’zhangsan’的员工薪水修改为3000元。

update employee set salary=3000 where name='zhangsan';

将姓名为’lisi’的员工薪水修改为4000元,sex改为female。

update employee set salary=4000,sex='female' where name='wangwu';

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

update employee set salary=salary+1000 where name='xiaohong';

--

--

3. delete语句

--

删除表中name为’赵楠’的记录。

delete from employee where name='赵楠';

--

删除表中所有记录。

delete from employee;

使用truncate删除表中记录。  摧毁表再创建表

truncate employee;

--

4. select语句

student.sql

create table student(

id int,

name varchar(20),

chinese float,

english float,

math float

);

--

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);

insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);

insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);

insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);

insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);

insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);

insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

--

查询表中所有学生的信息。

select * from student;

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

select name,english from student;

过滤表中重复数据。

select distinct english from student;

--

--

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

select name,english+10,chinese+10,math+10 from student;

统计每个学生的总分。

select name,english+chinese+math as sum from student;

使用别名表示学生分数。

--

where 子句

查询姓名为李一的学生成绩

select * from student where name='李一';

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

select * from student where english>90;

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

select name,english+chinese+math from student where english+chinese+math>200;

--

运算符

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

select * from student where english between 65 and 85;

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

select name,math from student where math in(89,90,91);

查询所有姓李的学生成绩。

select * from student where name like '李%';

// 查询姓李的两个字的学生

select * from student where name like '李_';

查询数学分>80，语文分>80的同学。

select * from student where math>80 and chinese>80;

查询英语>80或者总分>200的同学

select *,chinese+math+english from student where english>80 or chinese+english+math>200;

--

order by 子句

对数学成绩排序后输出。

select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出

select *,chinese+math+english from student order by chinese+math+english desc;

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

select * from student where name like '李%' order by chinese+math+english;

--

合计函数

count

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

select count(*) from student;

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

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

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

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

--

sum

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

select sum(math) from student; 

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

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

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

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

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

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

缺考的不参与计算

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

--

avg

语文平均分

select avg(chinese) from student;

--

max/min

语文最高分

select max(chinese) from student;

--

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 orders group by product;

显示单类商品总结

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

商品分类 显示单类商品总价大于100的

select *,sum(price) from orders group by product having sum(price)>100;

--

// 将单价大于20 的商品进行归类显示  按照价格排序

select * from orders where price>20 group by product order by price;

--

四、表的约束

我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定

--

唯一约束  unique

create table a

(

name varchar(20) unique

);

--

insert into a values('aaa');

insert into a values('aaa'); 错 name有唯一约束

--

非空约束 not null

create table b

(

id int,

name varchar(20) not null

);

--

insert into b values(1,'aaa');

--

insert into b (id) values(2);错，name有非空约束

--

主键  每张表都应该有个主键 方便我们找到记录 

主键必须有唯一约束、非空约束

--

主键约束 primary key

create table c

(

id int primary key,

name varchar(20) not null

);

--

insert into c (id,name) values(1,'aaaa');

--

insert into c(id,name) values(1,'bbbb'); 错，主键重复

insert into c(name) values('ccc'); 错，主键不能为null

--

主键可以定义为自动增长， 注意主键类型必须是int

create table d

(

id int primary key auto_increment,

name varchar(20)

);

--

insert into d(name) values('ccc');

--

insert into d(name) values('aaaa');

--

delete from d where id=4;

--

--

create table e

(

id int,

name varchar(20)

);

// 增加主键约束

alter table e modify id int primary key;

// 删除主键约束

alter table e drop primary key;

--

// 创建联合主键

create table f

(

firstname varchar(20),

lastname varchar(20),

primary key(firstname, lastname)

);

--

insert into f values('zhao','nan');

insert into f values('li', 'nan');

--

--

最重要的约束  外键约束

--

create table husband

(

id int primary key,

name varchar(20)

);

--

create table wife

(

id int primary key auto_increment,

name varchar(20),

husbandid int,

constraint husbandid_FK foreign key(husbandid) references husband(id)

);

--

insert into husband (id,name) values(3,'liuxiaoshuai');

--

insert into wife (name, husbandid) values('fengjie', 3);

--

delete from husband where id=3;

--

create table aaa

(

id int

);

--

// 为aaa 加外键约束

alter table aaa add constraint husid_FK foreign key(id) references husband(id);

--

// 删除外键约束

alter table aaa drop foreign key husid_FK;

--

--

五、对象和表的关系

javabean  一张表

--

多对一  

在多的一方建外键  参照一的一方的主键

--

多对多

需要创建中间表描述关系

中间表需要两列   作为联合主键 同时又是外键分别参照两张表的主键

--

一对一

分清主从关系

在从的一方建外键参照主的一方的主键

由于在一对一的关系中外键不允许为空和重复(必须要找到主的一方，否则从的一方就没有存在的意义)

干脆将从的一方的主键直接作为外键

--

--

六、多表操作

--

创建表   多表查询

--

多对一    多的一方加外键

create table department

(

id int primary key auto_increment,

name varchar(20)

);

--

create table employee

(

id int primary key auto_increment,

name varchar(20),

departmentid int,

constraint departmentid_FK foreign key(departmentid) references department(id)

);

--

// 插入三个部门

--

insert into department (name) values('开发部');

insert into department (name) values('销售部');

insert into department (name) values('人事部');

--

// 插入5个员工

insert into employee (name, departmentid) values ('张三', 1);

insert into employee (name, departmentid) values ('王五', 2);

insert into employee (name, departmentid) values ('李四', 3);

insert into employee (name, departmentid) values ('赵六', 2);

insert into employee (name, departmentid) values ('田七', 1);

--

// 查询

1号部门的员工

select * from employee where departmentid=1;

销售部所有的员工

select id from department where name='销售部';

select * from employee where departmentid=2;

--

select * from employee where departmentid=(select id from department where name='销售部');

--

// 多表查询

select * from employee,department;

+----+------+--------------+----+--------+

| id | name | departmentid | id | name   |

+----+------+--------------+----+--------+

|  1 | 张三     |            1 |  1 | 开发部       |

|  1 | 张三     |            1 |  2 | 销售部      |

|  1 | 张三     |            1 |  3 | 人事部      |

|  2 | 王五     |            2 |  1 | 开发部       |

|  2 | 王五     |            2 |  2 | 销售部      |

|  2 | 王五     |            2 |  3 | 人事部      |

|  3 | 李四     |            3 |  1 | 开发部       |

|  3 | 李四     |            3 |  2 | 销售部      |

|  3 | 李四     |            3 |  3 | 人事部      |

|  4 | 赵六     |            2 |  1 | 开发部       |

|  4 | 赵六     |            2 |  2 | 销售部      |

|  4 | 赵六     |            2 |  3 | 人事部      |

|  5 | 田七     |            1 |  1 | 开发部       |

|  5 | 田七     |            1 |  2 | 销售部      |

|  5 | 田七     |            1 |  3 | 人事部      |

+----+------+--------------+----+--------+

--

笛卡尔积  

结果中有很多不匹配的数据  废数据

--

去除不匹配数据 (废数据)  一张表的外键列=参照表的主键列

select * from employee,department where employee.departmentid=department.id;

+----+------+--------------+----+--------+

| id | name | departmentid | id | name   |

+----+------+--------------+----+--------+

|  1 | 张三     |            1 |  1 | 开发部       |

|  5 | 田七     |            1 |  1 | 开发部       |

|  2 | 王五     |            2 |  2 | 销售部      |

|  4 | 赵六     |            2 |  2 | 销售部      |

|  3 | 李四     |            3 |  3 | 人事部      |

+----+------+--------------+----+--------+

--

加上我们的帅选条件

select * from employee,department where employee.departmentid=department.id and department.name='销售部';

--

最终答案

select e.* from employee e,department d where e.departmentid=d.id and d.name='销售部';

--

--

多对多

create table teacher

(

id int primary key auto_increment,

name varchar(20)

);

--

create table student

(

id int primary key auto_increment,

name varchar(20)

);

--

// 中间表

create table tea_stu

(

teaid int,

stuid int,

primary key(teaid, stuid),

constraint teaid_FK foreign key(teaid) references teacher(id),

constraint stuid_FK foreign key(stuid) references student(id)

);

--

// 插入三个老师记录

insert into teacher (name) values('老张');

insert into teacher (name) values('老黎');

insert into teacher (name) values('老方');

--

// 插入7个学生记录

insert into student(name) values('张三');

insert into student(name) values('李四');

insert into student(name) values('王五');

insert into student(name) values('赵楠');

insert into student(name) values('刘小帅');

insert into student(name) values('王芳');

insert into student(name) values('刘红');

--

// 插入中间表  描述关系

insert into tea_stu(teaid, stuid) values(1,1);

insert into tea_stu(teaid, stuid) values(1,2);

insert into tea_stu(teaid, stuid) values(1,3);

insert into tea_stu(teaid, stuid) values(1,4);

insert into tea_stu(teaid, stuid) values(2,3);

insert into tea_stu(teaid, stuid) values(2,4);

insert into tea_stu(teaid, stuid) values(2,5);

insert into tea_stu(teaid, stuid) values(2,7);

insert into tea_stu(teaid, stuid) values(3,2);

insert into tea_stu(teaid, stuid) values(3,4);

insert into tea_stu(teaid, stuid) values(3,1);

insert into tea_stu(teaid, stuid) values(3,5);

insert into tea_stu(teaid, stuid) values(3,6);

--

// 查询

查询2号老师的学生信息

select s.* from student s, tea_stu ts where s.id=ts.stuid and ts.teaid=2;

--

查询老方的所有学生

select s.* from student s, teacher t, tea_stu ts where ts.stuid=s.id and ts.teaid=t.id and t.name='老方';

--

多表查询

n张表联合查 需要去掉废数据  需要n-1个条件 

条件都是 外键列=参照列

--

一对一

create table person

(

id int primary key auto_increment,

name varchar(20)

);

--

create table idcard

(

id int primary key,

location varchar(20),

constraint personid_FK foreign key(id) references person(id)

);

--

insert into person (name) values('zhangsan');

insert into person (name) values('lisi');

--

insert into idcard (id,location) values(2,'天津');

--

insert into idcard (id,location) values(1,'上海');

--

查李四的身份证

select idcard.* from person,idcard where idcard.id=person.id and person.name='lisi';