mysql 常用语句

------- android培训、java培训、期待与您交流！ ----------

 

sql语句
用于操作数据库的语句

 

一、针对数据库和表的操作
数据库 database     表  table
创建  create
删除  drop
修改  alter
查看  show

// 创建库
create database aaa;

// 删除库
drop database aaa;

创建一个名称为mydb1的数据库。
create database mydb1;
查看所有的数据库
show databases;
创建一个使用utf-8字符集的mydb2数据库。
create database mydb2 character set utf8;
查看数据库的创建语句(查看定义信息)
show create database mydb2;
创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_general_ci;
删除前面创建的mydb3数据库
drop database mydb3;
将mydb2的字符集修改为gbk
alter database mydb2 character set gbk;

备份和恢复数据库

准备工作
在mydb1库中创建一个a表 插入一点数据
使用mydb1
use mydb1;
创建表
create table a
(
  name varchar(20)
);
向表中插入两条数据
insert into a (name) values('aaa');
insert into a (name) values('bbb');
查询表中的数据
select * from a;

备份数据库
mysqldump -uroot -proot mydb1 > d:\a.sql

恢复数据库
数据库是不能恢复的  恢复的只是数据库中的数据

创建库
create database mydb1;
使用库
use mydb1;
恢复数据
source d:\a.sql

表的操作
创建一个员工表
字段 属性
Id 整形
name 字符型
gender 字符型或bit型
brithday 日期型
Entry_date 日期型
job 字符型
Salary 小数型
resume 大文本型

create table employee
(
  id int not null,
  name varchar(20),
  gender varchar(6),
  birthday date,
  entry_date date,
  job varchar(60),
  salary float,
  resume text
);

查看库中所有的表
show tables;
查看表的定义语句
show create table employee;
查看表的结构
desc employee;
在上面员工表的基础上增加一个image列
alter table employee add image blob;
修改job列，使其长度为100。
alter table employee modify job varchar(100);
删除gender列。
alter table employee drop gender;
表名改为users。
rename table employee to users;
修改表的字符集为gbk
alter table users character set gbk;
列名name修改为username
alter table users change name username varchar(20);

二、对表中数据的操作
对数据的操作分为四个方面: 增删改查  crud
添加： insert
删除： delete
更新:  update
查询： select

1. insert 语句 添加数据
create table employee
(
 id int,
 name varchar(20),
 gender varchar(10),
 birthday date,
 salary float,
 entry_date date,
 resume text
);
向员工表插入三条数据
insert into employee (id,name,gender,birthday,salary,resume) values(1,'zhangsan','male','1980-11-22',1000,'good boy');
insert into employee (id,name,gender,birthday,salary,resume) values(2,'wangwu','male','1982-6-22',1000,'good boy');
insert into employee (id,name,gender,birthday,salary,resume) values(3,'xiaohong','female','1992-4-15',1000,'good girl');

// 查看表中所有的记录
select * from employee;

插入一条带中文的
insert into employee (id,name,gender,birthday,salary,resume) values(4,'小芳','female','1992-4-15',2000,'一个好女孩');

查看数据库的编码
show variables like 'character%';

修改客户端编码为 gbk   原因是dos命令行窗口使用系统默认字符集 gbk
set character_set_client=gbk;

修改结果集的编码
set character_set_results=gbk;

查看文档   了解 insert 语句
insert employee (id,name,birthday,gender,salary,resume) values(5,'lisi','1980-11-22','male',1000,'good boy');

insert employee (id,name) values(6,'zhaoliu'),(7,'zhangbozhi');

2. update 语句
将所有员工薪水修改为5000元
update employee set salary=5000;
将姓名为’zhangsan’的员工薪水修改为3000元。
update employee set salary=3000 where name='zhangsan';
将姓名为’lisi’的员工薪水修改为4000元,gender改为female。
update employee set salary=4000,gender='female' where name='lisi';
将xiaohong的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name='xiaohong';

3. delete 语句
删除表中name为’zhangsan’的记录。
delete from employee where name='zhangsan';
删除表中所有记录。
delete from employee;
使用truncate删除表中记录。  先摧毁表 再创建表  相当于删除所有的记录   但是效率高
truncate employee;

4. select 语句  查询表中的数据

查询表中所有学生的信息
select * from student;
查询表中所有学生的姓名和对应的英语成绩
select name,english from student;
过滤表中重复数据
select distinct english from student;

在所有学生分数上加10分特长分
select name,english+10 as english,chinese+10 as chinese,math+10 as math from student;
统计每个学生的总分
select name,english+chinese+math as sum from student;
使用别名表示学生分数
select name,english e from student;

where 子句
查询姓名为李一的学生成绩
select * from student where name='李一';
查询英语成绩大于80分的同学
select * from student where english>80;
查询总分大于200分的所有同学
select name,english+chinese+math sum from student where english+chinese+math>200;
查询英语分数在 80－90之间的同学
select * from student where english between 80 and 90;
查询数学分数为89,90,91的同学
select * 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 name,english,english+chinese+math sum from student where english>80 or english+chinese+math>200;

order by 子句
对数学成绩排序后输出
select * from student order by math;
对总分排序后输出，然后再按从高到低的顺序输出  降序
select *,english+chinese+math sum from student order by english+chinese+math desc;
对姓李的学生成绩排序输出   order by 子句要位于where 后面
select *,english+chinese+math sum from student where name like '李%' order by english+chinese+math;

合计函数－count  统计记录数
统计一个班级共有多少学生
select count(*) from student;
统计数学成绩大于90的学生有多少个
select count(*) from student where math>80;
统计总分大于220的人数有多少

合计函数－SUM
统计一个班级数学总成绩
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(english+chinese+math) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
select sum(chinese)/count(chinese) from student;

合计函数－AVG
求一个班级数学平均分
select avg(math) from student;

合计函数－MAX/MIN
select max(math) 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 id,product,sum(price) price from orders group by product;
查询分组总价大于100的
select id,product,sum(price) price from orders group by product having sum(price)>100;

三、表的约束
约束：为了防止对表的误操作，我们需要对表的操作进行一些约束   约束都是加在列上  用于限定某一列数据的存储方式
比如不允许在表的某一列上插入重复的值，就可以加唯一约束
比如某一列上不允许出现null值 就可以加非空约束

定义唯一约束unique
create table a
(
  name varchar(20) unique,
  pass varchar(20)
);

insert into a (name,pass) values('zhangsan','123');
insert into a (name,pass) values('wangwu','123');

定义非空约束not null
create table b
(
  name varchar(20) not null,
  pass varchar(20)
);
insert into b (name,pass) values('zhangsan','123');
insert into b (name) values('zhangsan');

通常来讲 数据库的每张表都有一个id字段，用于标示一条记录
id需要具备唯一性  需要非空
id通常会作为主键列   因为加上主键约束primary key  主键约束=非空约束+唯一约束
create table c
(
  id int primary key,
  name varchar(20)
);

删除主键约束
alter table c drop primary key;

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

增加主键约束
alter table c add primary key(id);

如果id为int型 可以定义为自动增长 auto_increment
create table d
(
  id int primary key auto_increment,
  name varchar(20)
);

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

外键约束  一张表的一列去参照另一张表的一列，  参照列的数据必须填被参照列有的数据   被参照的记录不允许删除
husband 表
create table husband
(
  id int primary key auto_increment,
  name varchar(20)
);

wife 表
create table wife
(
   id int primary key auto_increment,
   name varchar(20),
   husbandid int
);

insert into husband(name) values('冠希哥');
insert into husband(name) values('犀利哥');

insert into wife(name,husbandid) values('zhangbuzhi',1);
insert into wife(name,husbandid) values('凤姐',2);

凤姐查询自己老公是谁？
select husbandid from wife where name='凤姐';
select * from husband where id=(select husbandid from wife where name='凤姐');

犀利哥要将自己移除
delete from husband where name='犀利哥';

加入外键约束    wife表的husbandid要作为外键  参照 husband 表的 id
create table wife
(
   id int primary key auto_increment,
   name varchar(20),
   husbandid int,
   constraint husbandid_FK foreign key(husbandid) references husband(id)
);

四、表的关系

多表操作
多对一
部门表
create table department
(
  id int primary key auto_increment,
  name varchar(20)
);

员工表 有外键约束
drop table employee;

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('人事部');

添加7个员工
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('李四',1);
insert into employee (name,departmentid) values('李五',2);
insert into employee (name,departmentid) values('李六',1);
insert into employee (name) values('李七');

多表查询
查询2号部门所有的员工
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 | 李四 |            1 |  1 | 开发部 |
|  4 | 李四 |            1 |  2 | 销售部 |
|  4 | 李四 |            1 |  3 | 人事部 |
|  5 | 李五 |            2 |  1 | 开发部 |
|  5 | 李五 |            2 |  2 | 销售部 |
|  5 | 李五 |            2 |  3 | 人事部 |
|  6 | 李六 |            1 |  1 | 开发部 |
|  6 | 李六 |            1 |  2 | 销售部 |
|  6 | 李六 |            1 |  3 | 人事部 |
|  7 | 李七 |         NULL |  1 | 开发部 |
|  7 | 李七 |         NULL |  2 | 销售部 |
|  7 | 李七 |         NULL |  3 | 人事部 |
+----+------+--------------+----+--------+
21 rows in set (0.00 sec)

笛卡尔集就是多表所有记录的组合
在笛卡尔集中有很多不匹配的项  可以称为废数据
处理笛卡尔集第一步  去掉废数据    参照表的外键列应该和被参照列一致

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

+----+------+--------------+----+--------+
| id | name | departmentid | id | name   |
+----+------+--------------+----+--------+
|  1 | 李一 |            1 |  1 | 开发部 |
|  4 | 李四 |            1 |  1 | 开发部 |
|  6 | 李六 |            1 |  1 | 开发部 |
|  2 | 李二 |            2 |  2 | 销售部 |
|  5 | 李五 |            2 |  2 | 销售部 |
|  3 | 李三 |            3 |  3 | 人事部 |
+----+------+--------------+----+--------+
6 rows in set (0.00 sec)
没有废数据了  此时应加上条件再查询

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 student(name) values('zhangsan'),('xiaohong'),('李四'),('小兰'),('小白');

描述关系
insert into tea_stu (teaid,stuid) values(1,1),(1,2),(1,3),(1,5),(2,1),(2,3),(2,4);
 
多表查询
2号老师教过的学生
select s.* from student s,tea_stu ts where ts.stuid=s.id and ts.teaid=2;

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

n张表联合查询  去掉笛卡尔集中的废数据要n-1个条件   再加上最后的限制性查询条件 就能得到结果