mysql增删改查
# myslq数据类型
MySQL支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。
(1)数值数据类型:包括整数类型TINYINT. SMALLINT, MEDIUMINT, INT、BIGINT、 浮点小数数据类型FLOAT和DOUBLE,定点小数类型DECIMAL。 (2)日期/时间类型:包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。 (3)字符串类型:包括CHAR、VARCHAR、BINARY、VARBINARY. BLOB、TEXT. ENUM和SET等。字符串类型又分为文本字符串和二进制字符串。
# 整数类型
# 浮点数类型和定点数类型
# 日期与时间类型
# 文本字符串类型
# 二进制字符串类型
# 常见运算符
# 1.算术运算符
算术运算符用于各类数值运算,包括加(+)、减(-)、乘(*)、除(/)、求余(或称模运算,%)。
create table tmp14 (num int);
select num, num+10, num-3+5, num+5-3, num+36.5 from tmp14;
select num, num*2, num /2, num/3, num%3 from tpm14;
select num, num / 0, num %0 from tmp14;
2
3
4
# 2.比较运算符
比较运算符用于比较运算。包括大于(>) 、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(!=),以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等。
select 1=0, '2'=2, 2=2, '0,02'=0, 'b'='b', (1+3) = (2+2),null=null;
select 1<=>0, '2'<=>2, 2<=>2, '0.02'<=>0, 'b'<=>'b', (1+3) <=>(2+1), null<=>null;
select 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),null<>null;
select 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1), null<=null;
select 'good'<'god', 1<2, 4<4, 5.5<5, (1+3)<(2+1),null<null;
2
3
4
5
6
# 3.逻辑运算符
逻辑运算符的求值所得结果均为1 (TRUE)、0 (FALSE),这类运算符有逻辑非(NOT或者!)、逻辑与(AND或者&&)、逻辑或(OR或者|)、逻辑异或(XOR)。
# 4.位操作运算符
位操作运算符参与运算的操作数按二进制位进行运算。包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)6种。
# 综合案例
create table tmp15 (note varchar(100), price int);
insert into tmp15 VALUES("Thisisgood",50);
select price,price+10,price-10,price*2,price/2,price%3 from tmp15;
select price,price>10,price<10,price!=10,price=10,price<=>10,price<>10 from tmp15;
select price,price between 30 and 80,GREATEST(price,70,30),price in(10,20,50,35) from tmp15;
select note ,note is null,note like 't%',note REGEXP '$y',note REGEXP '[gm]' from tmp15;
select price,price&&1,price&&null,price||0,price and 0,0 and null,price or null from tmp15;
select price,!price,not null,price xor 3,0 xor null, price xor 0 from tmp15;
select price,price&2,price|4,~price from tmp15;
select price,price<<2,price>>2 from tmp15;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql使用特殊字符需要转义 \
# mysql函数
# 数学函数
绝对值函数ABS(x)和返回圆周率的函数PI()
select ABS(2), ABS(-3.3), ABS(-33); select pi();
平方根函数SQRT(x)和求余函数MOD(x,y)
select sqrt(9) sqrt(40), sqrt(-49); select mod(31,8)
获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)
select CEIL(3.35),CEILING(3.35); select FLOOR(-3.35),FLOOR(3.35);
获取随机数的函数RAND()和RAND(x)
select RAND(),RAND(),RAND(); select RAND(10),RAND(10),RAND(11);
函数ROUND(x),ROUND(x,y)和TRUNCATE(x,y)
四舍五入
select round(-1.14),round(-1.67),round(1.14),round(1.66); select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);
返回被舍去至小数点后y位的数字x
select rtuncate(1.31,1), truncate(1,99,1),truncate(1.99,0), truncate(19.99,-1);
符号函数SIGN(x)
select SIGN(-21),SIGN(0),SIGN(21);
幂运算函数POW(x,y),POWER(x,y)和EXP(x)
select POW(2,2),POWER(2,2),POW(2,-2),POWER(2,-2) select EXP(3),EXP(-3),EXP(0);
对数运算函数LOG(x)和LOG10(x)
# 创建数据表
create table tb_emp1
(
id int(11),
name varchar(25),
deptid int(11),
salary float);
2
3
4
5
6
# 使用主键约束
# 单字段主键
create table tb_emp2
(
id int(11) primary key,
name varchar(25),
deptid int(11),
salary float);
在定义完所有列之后指定主键
constraint 约束名 primary key 字段名
create table tb_emp3
(
id int(11),
name varchar(25),
deptid int(11),
salary float,
primary key(id)
);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 多字段联合主键
create table tb_emp4
(
name varchar(25),
deptid int(11),
salary float,
primary key(name,deptid)
);
2
3
4
5
6
7
# 外键约束
constraint 外键名 foreign key 字段名1,字段名2 references 主表名 主键列1,主键列2
create table tb_dept1
(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
create table tb_emp5
(
id int(11) primary key,
name varchar(25),
deptid int(11),
salary float,
constraint fk_emp_dept1 foreign key(deptid) references tb_dept1(id)
);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 使用非空约束
字段名 数据类型 not null
create table tb_emp6
(
id int(11) primary key,
name varchar(25) not null,
deptid int(11),
salary float
);
2
3
4
5
6
7
# 使用唯一性约束
create table tb_dept2
(
id int(11) primary key,
name varchar(22) unique,
location varchar(50)
);
create table tb_dept3
(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint sth unique(name)
);
2
3
4
5
6
7
8
9
10
11
12
13
14
# 使用默认约束
create table tb_emp7
(
id int(11) primary key,
name varchar(25) not null,
deptid int(11) default 1111,
salary float
);
2
3
4
5
6
7
# 设置表的自增
create table tb_emp8
(
id int(11) primary key auto_increment,
name varchar(25) not null,
deptid int(11),
salary float
);
insert into tb_emp8 (name,salary)
values('lucy',1000),('Lura',1200),('Kevin',1500)
2
3
4
5
6
7
8
9
10
11
# 查看数据表结构
# 基本结构语句
describe 表名
# 表的详细结构语句
show create table 表名\G;
# 修改数据表
# 修改表名
alter table 旧表名 rename to 新表名;
# 修改字段的数据类型
alter table 表名 modify 字段名 数据类型 alter table tb_dept1 modify name varchar(30);
# 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型; alter table tb_dept1 change location loc varchar(50);
# 添加字段
alter table 表名 add 新字段名 数据类型 约束条件 first|after 已存在字段名;
添加无完整条件约束条件的字段 alter table tb_dept1 add managerid int(10);
添加有完整性约束条件的字段 alter table tb_dept1 add column1 varchar(12) not null;
在表的第一列添加字段 alter table tb_dept1 add column2 int(11) first;
在表的指定列之后添加一个字段 alter table tb_dept1 add column3 int(11) after name;
# 删除字段
alter table 表名 drop 字段名; alter table tb_dept1 drop column2;
# 修改字段的排列位置
alter table 表名 modify 字段1 数据类型 first|after 字段2;
修改字段为表的第一个字段 alter table tb_dept1 modify column1 varchar(11) first;
修改字段到表的指定列之后 alter table tb_dept1 modify column1 varchar(12) after location;
# 更改表的存储引擎
alter table tb_deptment3 ENGINE=MyISAM;
# 删除表的外键约束
alter table 表名 drop foreign key 外键约束名;
create table tb_emp9
(
id int(11) primary key,
name varchar(25),
deptid int(11),
salary float,
constraint fk_emp_dept foreign key (deptid) references tb_dept1(id)
);
alter table tb_emp9 drop foreign key fk_emp_dept;
2
3
4
5
6
7
8
9
10
# 删除数据表
删除数据表就是将数据库中已经存在的表从数据库中删除。注意,在删除表的同时,表的定义和表中所有的数据均会被删除。因此,在进行删除操作前,最好对表中的数据做个备份,以免造成无法挽回的后果。本节将详细讲解数据库表的删除方法。
# 删除没有被关联的表
drop table [IF EXISTS] 表1,表2,表3 drop table if exists tb_dept2;
# 删除被其他表关联的主表
数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败。原因是直接删除,将破坏表的参照完整性。如果必须要删除,可以先删除与它关联的子表,再删除父表,只是这样同时删除了两个表中的数据。但有的情况下可能要保留子表,这时如要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表。
create table tb_dept2
(
id int(11) primary key,
name varchar(22),
location varchar(50)
);
create table tb_emp
(
id int(11) primary key,
name varchar(25),
deptid int(11),
salary float,
constraint fk_emp_dept foreign key (deptid) references tb_dept2(id)
);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
解除关联子表tb_emp的外键约束 alter table tb_emp drop foreign key fk_emp_dept;
再将原来的父表tb_dept2删除
# 综合案列
create database company;
use company;
create table offices
(
officecode int(10) not null unique,
city varchar(50) not null,
address varchar(50)
country varchar(50) not null,
postalcode varchar(15)
primary key (officecode)
);
create table employees
(
employeenumber int(11) not null primary key auto_increment,
lastname varchar(50) not null,
firstname varchar(50) not null,
mobile varchar(25) unique,
officecode int(10) not null,
jobtitle varchar(50) not null,,
birth datetime, not null
note varchar(255)
sex varchar(5)
constraint office_fk foreign key(officecode) references offices(officecode)
);
alter table employees modify mobile varchar(25) after officecode;
alter table employees change birth employee_birth datetime;
alter table employees modify sex char(1) not null;
alter table employees drop note;
alter table employees add favoriate_activity varchar(100);
alter table employees drop foreign key office_fk;
drop table offices;
alter table employees ENGINE=MyISAM;
alter table employees rename employees_info;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 练习题
create table customers
(
c_num int(11) primary key auto_increment,
c_birth datetime not null,
constraint sth unique(c_num)
);
c_name varchar(50)
c_contact varchar(50)
c_city varchar(50)
alter table customers add c_gender char(1);
alter table customers add c_contact varchar(50) after c_birth;
alter table customers change c_name c_name varchar(70);
desc customers_info
alter table customers rename customers_info;
alter table customers_info ENGINE=MyISAM;
create table orders
(
o_num int(11) primary key auto_increment,
o_date date,
c_id varchar(50),
CONSTRAINT fk_num foreign key(c_id) REFERENCES customers_info(c_num)
);
select c_num,c_name,c_birth from customers_info;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 插入、更新与删除数据
# 插入数据
# 为表的所有字段插入数据
create table person (
id int unsigned not null auto_increment,
name char(40) not null default '',
age int not null default 0,
info char(50) null,
primary key (id)
);
select * from person;
insert into person (id,name,age,info) values(1,'Green',21,'Lawyer');
insert into person (age,name,id,info) values(22,'Suse',2,'dancer');
insert into person values(3,'Mary',24,'Musician');
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 为表的指定字段插入数据
insert into person (name,age,info) values('Willam',20,'sports man');
insert into person (name,age) values('Laura',25);
2
3
# 同时插入多条记录
insert into person(name,age,info) values('Evans',27,'secretary'),('Dale',22,'cook'),('Edison',28,'singer');
insert into person values (9,'Harry',21,'magician'),(NULL,'Harriet',19,'pianist');
2
3
# 将查询结果插入到表中
insert into table_name1 (column_list1) select (column_list2) from table_name2 where (condition)
create table person_old
(
id int unsigned not null auto_increment,
name char(40) not null default '',
age int not null default 0,
info char(50) null,
primary key (id)
);
insert into person_old values(11,'Harry',20,'student'),(12,'Bechham',31,'police');
insert into person(id,name,age,info) select id,name,age,info from person_old;
2
3
4
5
6
7
8
9
10
11
12
13
14
# 更新数据
select * from person where id = 11;
update person set age=15,name='LiMing' where id=11;
select * from person where age between 19 and 22;
update person set info='student' where age between 19 and 22;
2
3
4
5
6
7
# 删除数据
select * from person where id=11;
delete from person where id=11;
select * from person where age between 19 and 22;
delete from person where age between 19 and 22;
delete from person;
2
3
4
5
6
7
8
# 综合案例
create table books
(
id int(11) not null primary key,
name varchar(50) not null,
authors varchar(100) not null,
price float not null,
pubdate year not null,
note varchar(255) null,
num int(11) not null default 0
);
select * from books;
INSERT INTO books ( id, NAME, AUTHORS, price, pubdate, note, num )
VALUES
(
1,'Tale of AAA','Dickes',23,'1995','novel',11);
insert into books values
( 2,'EmmaT','Jane lura',35,'1993','joke',22);
insert into books values
(3, 'story of Jane','Jane Tim',40,'2001','novel',0),
(4,'Lovey Day','George Byron',20,'2005','novel',30),
(5,'Old Land','Honore Blade',30,'2010','law',0),
(6,'The Battle','Upton Sara',33,'1999','medicine',40),
(7,'Rose Hood','Richard Kale',28,'2008','cartoon',28);
update books set price=price+5 where note='novel';
update books set price=40,note='drama' where name='EmmaT';
delete from books where num=0;
select * from books where num=0;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 练习题
create table pet
(
name varchar(20) not null,
owner varchar(20),
species varchar(20) not null,
sex char(1) not null,
birth year not null,
death year
);
insert into pet(name,owner,species,sex,birth,death) values
('Fluffy','Harold','cat','f',2003,2010),
('Claws','Gwen','cat','m',2004,null);
select * from pet;
insert into pet values
('Buffy',NULL,'dog','f',2009,null),
('Fang','Benny','dog','f',2000,null),
('Bowser','Diane','dog','m',2003,2009),
('Chirpy',null,'bird','f',2008,null)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#
# 数据查询语言
# #基础查询
SELECT
*
FROM
myemployees.departments
# #查询单个字段
SELECT manager_id FROM departments
# #查询多个字段
SELECT
manager_id,
department_id,
location_id
FROM
departments
#查询表中所有字段
SELECT * FROM departments
# #查询常量值
SELECT 100; SELECT 'john';
# #查询表达式
SELECT 100*98; SELECT 100%98;
# #查询函数
SELECT VERSION();
#起别名 /* a.便于理解 b.如果要查询的字段有重名的情况,使用别名来区分开来 */
SELECT 100%98 AS 结果; SELECT last_name AS 姓,first_name AS 名 FROM employees;
SELECT last_name 姓,first_name 名 FROM employees;
SELECT salary AS "out put" FROM employees;
# #去重
SELECT DISTINCT department_id FROM employees;
+号的作用 仅仅只有一个功能:运算符
/* select '123'+90; 只要其中一方为字符型,试图将字符数值转换成数值型 select 'john'+90; 如果转换成功,则继续做加法运算,如果失败,则将字符型数值转换成0 select null+10; 只要其中一方为Null,则结果肯定为null. */ SELECT CONCAT(last_name,'.',first_name) AS 姓名 FROM employees;
DESC departments;
SHOW CREATE DATABASE myemployees;
SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct FROM employees;
SELECT CONCAT(first_name
,',',last_name
,',',email
,',',IFNULL(commission_pct,0)) AS out_put FROM employees;
# #条件查询
SELECT 查询列表 FROM 表名 WHERE 筛选条件
/*
按条件表达式筛选 条件运算符 > < = != <> >= <= 按逻辑表达式筛选 逻辑运算符 && || ! and or not 模糊查询 like between and in is null
*/
# #按条件表达式筛选
SELECT * FROM employees WHERE salary>12000;
SELECT last_name,department_id FROM employees WHERE department_id<>90;
# #按逻辑表达式筛选
SELECT last_name,salary FROM employees WHERE salary>=10000 AND salary<=20000;
#查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT(department_id>=90 OR department_id<=110) OR salary>15000;
# #模糊查询
/*
一般和通配符搭配使用
% 任意多个字符,包含0个字符
— 任意单个字符
\ 转义符
使用between and 可以提高语句的简洁度
包含临界值
不要改变位置
*/
SELECT * FROM employees WHERE last_name LIKE '%a%';
SELECT last_name,salary FROM employees WHERE last_name LIKE '__a_t%';
SELECT last_name FROM employees WHERE last_name LIKE '__%'
#between and SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
in
/* 判断某字段的值是否属于in列表中的某一项 使用in提高语句简洁度 要求值类型必须统一 */
SELECT last_name,job_id FROM employees WHERE job_id='IT_PROT' OR job_id='AD_VP' OR job_id='AD_PRES';
SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');
# is null
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
#安全等于 <=>
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=>NULL;
SELECT last_name,salary employees WHERE salary <=> 12000;
SELECT last_name,department_id,salary12(1+IFNULL(commission_pct,0)) 年薪 FROM employees;
SELECT last_name,salary FROM employees WHERE salary>12000; SELECT * FROM employees;
SELECT employee_id,first_name,last_name,department_id,salary*12 FROM employees WHERE employee_id=176
SELECT first_name,last_name,salary FROM employees WHERE NOT(salary>5000 AND salary <12000)
SELECT department_id,first_name,last_name FROM employees WHERE department_id=20 OR department_id=50
SELECT first_name,last_name,job_id,manager_id FROM employees WHERE manager_id IS NULL;
SELECT first_name,last_name,commission_pct,salary FROM employees WHERE commission_pct IS NOT NULL;
SELECT first_name,last_name FROM employees WHERE last_name LIKE '__a%';
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NULL AND salary<18000;
SELECT job_id,salary FROM employees WHERE NOT(job_id='IT_PROG') OR salary=9000
SELECT DISTINCT location_id FROM departments
# #排序查询
SELECT * FROM employees; SELECT * FROM employees ORDER BY salary ASC; #asc 代表的是升序,DESC代表的是降序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
SELECT LENGTH(last_name) 字节长度,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC;
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;
#order by 子句中可以支持单个,多个字段,表达式,函数,别名 #order by 子句一般是放在查询语句的最后面,但limit子句除外
SELECT last_name,department_id,salary12(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC; SELECT last_name,salary FROM employees WHERE NOT(salary>=8000 AND salary<=17000) ORDER BY salary DESC; SELECT *,LENGTH(email) FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;
# 一、单行函数
常见函数 #单行函数 concat、length、ifnull #分组函数 又称为统计函数、聚合函数、组函数
字符函数 LENGTH 获取参数值的字节个数 SELECT LENGTH('john'); SELECT LENGTH('张三丰hahaha'); SHOW VARIABLES LIKE '%char%';
CONCAT 拼接字符串 SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
upper、lower 大小写 SELECT UPPER('john'); SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
substr、substring 截取返回 索引从1开始 SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put; 截取从指定索引处指定字符长度的字符 SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put; SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;
INSTR 返回子串第一次出现的索引,如果找不到返回0 SELECT INSTR('杨不悔爱上了殷六侠','殷八侠') AS out_put;
TRIM 去掉指定的字符 SELECT LENGTH(TRIM('张翠山')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaaaaaaaaaaa张aaaaaa翠山aaaaaaa') AS out_put;
LPAD 用指定的字符实现左填充指定长度 SELECT LPAD('殷素素',10,'*') AS out_put;
RPAD 用指定的字符实现右填充指定长度 SELECT RPAD('殷素素',10,'*') AS out_put;
REPLACE 替换 SELECT REPLACE('周芷若周芷若周芷若周芷若爱上了张无忌','周芷若','赵敏') AS out_put;
# 数学函数
ROUND 四舍五入 SELECT ROUND(1.65); SELECT ROUND(1.567,2);
CEIL 向上取整,返回>=该参数的最小整数 SELECT CEIL(1.00);
FLOOR 向下取整,返回<=该参数的最大整数 SELECT FLOOR(-9.99);
TRUNCATE 截断 SELECT TRUNCATE(1.699,1);
MOD 取余 SELECT MOD(10,3);
# 日期函数
YEAR MINUTE MONTH MONTHNAME DAY HOUR SECOND NOW 当前系统时期时间 SELECT NOW();
CURDATE 返回当前系统日期,不包含时间 CURTIME 返回当前系统时间,不包含日期
SELECT CURDATE(); SELECT CURTIME();
获取指定的部分,年、月、日、小时、分钟、秒 SELECT YEAR(NOW()) 年; SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTHNAME(NOW()) 月;
STR_TO_DATE 将日期格式的字符转换成指定格式的日期 DATE_FORMAT 将日期转换为字符
# 其他函数
SELECT VERSION(); SELECT DATABASE(); SELECT USER();
# 流程控制函数
if函数
SELECT IF(10>5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金嘻嘻','有资金') FROM employees;
case函数使用方式一
CASE 要判断的字段或表达式 WHEN 常量1 THEN 要显示的值1或者语句1 WHEN 常量2 THEN 要显示的值2或者语句2 ... ELSE 要显示的值n或者语句n; END
SELECT salary 原始工资,department_id, CASE department_id WHEN 30 THEN salary1.1 WHEN 40 THEN salary1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工资 FROM employees;
case函数使用方式二 CASE WHEN 条件1 THEN 要显示的值1或语句1 WHEN 条件2 THEN 要显示的值2或语句2 ... ELSE 要显示的值n或者语句n END
SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END 工资评分 FROM employees;
# 二、分组函数
sum 求和,avg平均值,max最大值,min最小值,count计算个数
SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
SELECT MAX(hiredate),MIN(hiredate) FROM employees;
COUNT只计算不为空的字符 SUM,avg只处理数值型 MIN,MAX,COUNT 可以处理任何类型
是否忽略Null SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees;
和distinct搭配 SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
count函数的详细介绍 SELECT COUNT(salary) FROM employees; 效率高的计算 SELECT COUNT(*) FROM employees; SELECT COUNT(1) FROM employees;
和分组函数一同查询的字段有限制 和分组函数一同查询的字段要求是group by后的字段
相差天数 SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
# 分组查询
group by 子句
select 分组函数,列 (要求出现在group by的后面) from 表 where 筛选条件 group by 分组的列表 order by 子句
# 简单分组查询
查询工种的最高工资 SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
查询每个位置上的部门个数 SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
添加筛选条件 查询邮箱中包含a字符的每个部门的平均工资 SELECT AVG(salary),department_id,email FROM employees WHERE email LIKE '%a%' GROUP BY department_id
查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
添加复杂的筛选条件 查询哪个部门的员工个数>2
SELECT COUNT(),department_id FROM employees GROUP BY department_id HAVING COUNT()>2;
查询每个工种有奖金的员工的最高工资>12000的工程编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT MIN(salary)manager_id FROM employees WHERE department_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;
分组查询中的筛选条件分为两类 分组前筛选 WHERE 分组后筛选 havin
分组函数做条件肯定是放在having子句中 能用分组前筛选的,就优先考虑使用分组前筛选
按表达式或函数分组
按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
SELECT COUNT(),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT()>5;
按多个字段分组
查询每个部门每个工种的员工的平均工资 SELECT CEIL(AVG(salary)) 平均,department_id,job_id FROM employees WHERE department_id IS NOT NULL GROUP BY job_id,department_id HAVING 平均>10000 ORDER BY 平均 DESC;
GROUP BY 子句支持单个字段分组,多个字段分组,表达式或函数 也可以添加排序
# 连接查询
SELECT * FROM beauty;
SELECT NAME,boyName FROM beauty,boys WHERE beauty.boyfriend_id=boys.id;
添加有效连接条件连接查询
分类 按年代分类 sql192标准 sql199标准 推荐使用
按功能分类 内连接:等值连接,非等值连接,自连接 外连接:左外连接,右外连接,全外连接 交叉连接
查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id
=departments.department_id;
查询员工名,工种号,工种名
为表起别名 提高语句的简洁度,区分多个重名的字段
如果为表起了别名,则查询的字段就不能使用原表名
SELECT e.last_name,j.job_id,job_title
FROM employees AS e,jobs j
WHERE e.job_id=j.job_id
;
加筛选 查询有奖金的员工名,部门名
SELECT last_name,department_name FROM employees e,departments d WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL;
查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id=l.location_id AND city LIKE '_o%';
查询每个城市的部门个数 SELECT COUNT(*),city FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY city;
查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 SELECT MIN(salary),d.department_id,d.manager_id FROM departments d,employees e WHERE d.department_id=e.department_id AND commission_pct IS NOT NULL GROUP BY e.department_id,d.manager_id;
查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT COUNT(),job_title FROM employees e,jobs j WHERE e.job_id=j.job_id GROUP BY e.job_id ORDER BY COUNT() DESC;
实现三表连接
查询员工名,部门名和所在的城市 SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id ORDER BY last_name;
1、多表等值连接的结果为多表的交集部分 2、n表连接,至少需要n-1个连接条件 3、多表的顺序没有要求 4、一般需要为表起别名 5、可以搭配前面介绍的所有子句使用,比如排序,分组,筛选
非等值连接
查询员工的工资和工资级别
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal AND g.grade_level='A'
自连接
查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE e.manager_id=m.manager_id
外连接
应用场景:用于查询一个表中有,另一个表没有的记录 特点 1、外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的,则显示匹配的值 如果从表中没有和它匹配的,则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录 2、左外连接,left join左边的是主表 右外连接,right join右边的是主表 3、左外和右外交换两个表的顺序,可以实现同样的效果
查询男朋友不在男神表的女神名
左外连接
select b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id
= bo.id
where bo.id
is null;
select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id
=e.department_id
where e.employee_id
is null;
右外连接
select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id
=e.department_id
where e.employee_id
is null;
子查询 出现在其他语句中的select语句,称为子查询或内查询 外部的查询语句,称为主查询或外查询
分类: 按子查询出现的位置: select后面 from后面 where或having后面 exists后面(相关子查询) 按结果集的行列数不同: 标量子查询(结果集只有一行一列) 列子查询 (结果集中只有一列多行) 行子查询(结果集有一行多列) 表子查询(结果集一般为多行多列)
where或having后面 1、标量子查询(单行子查询) 2、列子查询(多行子查询) 3、行子查询(多列多行)
特点: 子查询放在小括号内 子查询一般放在条件的右侧 标量子查询,一般搭配着单行操作符使用
列子查询,一般搭配着多行操作符使用 in,any/some,all
标量子查询
a.谁的工资比Abel高 select salary from employees where last_name='Abel';
查询员工的信息,满足salary> a结果
# 9.索引
索引的优点主要有以下儿条: (1)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。 (2)可以大大加快数据的查询速度,这也是创建索引的最主要的原因。 (3)在实现数据的参考完整性方面,可以加速表和表之间的连接。 (4)在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。
增加索引也有许多不利,主要表现在如下几个方面: (1)创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。 (2)索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。 (3)当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。
# 普通索引和唯一索引
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。 唯一索引,索引列的值必须唯一, 但允许有空值。如果是组合索引,则列值的组合必须唯一。主键索引是一种特殊的唯一索引, 不允许有空值。
# 单列索引和组合索引
单列索引即一个索引只包含单个列,一个表可以有多个单列索引。 组合索引指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
# 索引的设计原则
索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时,应该考虑以下准则: (1)索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间,而且会影响NSERT、DELETE、UPDATE 等语句的性能,因为当表中的数据更改的同时,索引也会进行调整和更新。
(2)避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。
(3)数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
(4)在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无须建立索引。如果建立索引不但不会提高查询效率,反而会严重降低数据更新速度。
(5)当唯- 性是某种数据本身的特征时,指定唯一 索引。使用唯-索引需能确保定义的列的数据完整性,以提高查询速度。
(6)在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
# 创建索引
# 创建普通索引
create table book
(
bookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
year_publication year not null,
index(year_publication)
);
2
3
4
5
6
7
8
9
10
explain语句查看索引是否正在使用
explain select * from book where year_publication=1990 \G
# 创建唯一索引
create table t1
(
id int not null,
name char(30) not null,
unique index uniqidx(id)
);
2
3
4
5
6
# 创建单列索引
create table t2
(
id int not null,
name char(50) null,
index singleidx(name(20))
);
2
3
4
5
6
# 创建组合索引
create table t3
(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index multiidx(id,name,age(100))
);
2
3
4
5
6
7
8
# 创建全文索引
create table t4
(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
fulltext index fulltxtidx(info)
) ENGINE=MyISAM;
2
3
4
5
6
7
8
# 创建空间索引
create table t5
(
g geometry not null,
spatial index spatidx(g)
) ENGINE=MyISAM;
2
3
4
5
# 在已经存在的表上创建索引
使用alter 创建索引
alter table book add index bknameidx(bookname(30));
alter table book add unique index uniqididx(bookid);
alter table book add index bkcmtidx(comment(50));
alter table book add index bkauandinfoidx(authors(30),info(50));
create table t6
(
id int not null,
info char(255)
)ENGINE=MyISAM;
alter table t6 add fulltext index infoftidx(info);
create table t7 ( g geometry not null)ENGINE=MyISAM;
alter table t7 add spatial index spatidx(g);
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
使用create index创建索引
create index bknameidx on book(bookname):
create unique index uniqididx on book(bookid);
create index bkcmtidx on book(comment(50));
create index bkauandinfoidx on book(authors(20),info(50));
create fulltext index on t6(info);
create spatial index spatidx on t7 (g);
2
3
4
5
6
# 删除索引
使用alter table 删除索引
alter table book drop index uniqididx;
使用drop index语句删除索引
drop index bkauand
# 综合案例--创建索引
create table test_table1
(
id int(11) not null auto_increment,
name char(100) not null,
address char(100),
description char(100),
unique index uniqidx(id),
index multicolidx(name(10),address(30)),
index comidx(description(30))
);
create table test_table2
(
id int(11) not null primary key,
firstname char(50) not null,
middlename char(50) not null,
lastname char(50) not null,
birth date not null,
title char(100)
) ENGINE=MyISAM;
alter table test_table2 add index comdateidx(birth);
alter table test_table2 add unique index uniqidx2(id desc);
create index multicolidx2 on test_table2(firstname,middlename,lastname);
create fulltext index ftidx on test_table2(title);
alter table test_table1 drop index uniqidx;
drop index multicolidx2 on test_table2;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32