爱站程序员基地distinct
显示出表employees中的全部job_id（不能重复）distinct
SELECT DISTINCT job_id FROM employees;
Concat
显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT CONCAT(last_name,’,’,first_name,’,’,email) out_put FROM employees;
DESC employees;
模糊查询
1.like
% 任意多个字符
_ 任意单个字符 1
2.between and
①等价于 使用 字段>=值1 and 字段<=值2
②两个临界值是包含的 [1,10]
3.in
特点：判断某个字段的值是否在某个列表内
in(值1，值2，…)
等价于 or
4.is null/is not null
降序 desc
默认是 升序asc
按年薪降序，名字长度升序
SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC，
LENGTH(last_name) ASC;
#一、显示当前连接的所有数据库名称
SHOW DATABASES;
分组函数的分类
max():最大值
min():最小值
sum():和
avg():平均值
count(*):计算非空的个数
这些是通用的，sqlserver、oracle、mysql 都是一样的
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees
分组查询
语法：
select 分组函数 别名,分组后的字段
from 表
【where 分组前筛选】
group by 分组的字段
【having 分组后筛选】
【order by 排序的字段】
LIMIT 条目起始索引【,条目数】#分页查询
从上到下依次顺序，不能颠倒顺序
内连接
#join连接 特点：① 效果和等值连接是一样的！② n表连接，至少需要n-1 个连接条件
内连接：[inner] join on
外连接：
左外连接 left 【outer】 join on
右外连接 right 【outer】 join on
全外连接 full 【outer】 join on ，mysql中不支持！！！ sqlserver 和oracle 没有问题
#案例1 ：查询员工名、部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e ON e.
department_id
=d.
department_id
WHERE commission_pct IS NOT NULL;
外链接
/*
语法：
select 字段1，字段2，…
from 表1
left|right 【outer】 join 表2
on 连接条件
特点：
④一般用于查询主表中有但从表中没有的记录
*/
#案例2：查询哪个城市没有部门
SELECT city,department_name
FROM locations l
LEFT OUTER JOIN departments d ON l.
location_id
=d.
location_id
WHERE d.
department_id
IS NULL;
自连接
#案例：查询员工名和上级领导的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m ON e.
manager_id
=m.employee_id;
#全外连接
SELECT j.job_id,e.
employee_id
FROM employees e
FULL OUTER JOIN jobs j ON e.
job_id
=j.
job_id
#案例3：查询员工表的第15条到30条
SELECT * FROM employees LIMIT 14,16;
函数
1、字符函数
#concat 拼接字符
SELECT CONCAT(‘hello’,’,’,last_name) out_put
FROM employees
#upper 转换成大写，lower 转换成小写
SELECT UPPER(‘ABC’);
SELECT LOWER(‘ABC’);
#案例：将姓转大写，名转小写，然后拼接
SELECT CONCAT(UPPER(last_name),’ ‘,LOWER(first_name)) 姓名
FROM employees;
#instr 获取子串第一次出现的索引，索引从1开始，如果找不到返回0
SELECT INSTR(‘张三丰爱上了三丰的郭襄’,‘三丰’); #2
#substr
SELECT SUBSTR(‘郭襄爱上了杨过’,6); #杨过
SELECT SUBSTR(‘郭襄爱上了杨过’,1,2); #郭襄
#lpad左填充、rpad右填充
SELECT LPAD(‘尹志平爱上了’,10,’*’); #***尹志平爱上了
#trim 去前后空格或特殊字符
SELECT LENGTH(TRIM(’ 小 龙 女 \’)) 美女;
SELECT TRIM(‘a’ FROM ‘aaaaaaaqqqqqq小qq龙q女qqqqqqqqqqqqqq’) 美女;
2、数学函数
向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.0);
#向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.0);
#四舍五入,先按绝对值四舍五入，然后加正负号
SELECT ROUND(-1.56);
SELECT ROUND(1.537,1);
#随机数,返回0-1之间的小数，不包含1
SELECT RAND();
#返回50-100 a——b floor(rand()(b-a+1)+a)
SELECT CEIL(RAND()*50+50);
SELECT FLOOR(RAND()*51+50);
#截断
SELECT TRUNCATE(1.99,1); # 1.9
3、日期函数
#获取当前日期
SELECT NOW();
#获取日期，没有时间
SELECT CURDATE();
#获取时间，没有日期
SELECT CURTIME();
#获取日期的指定部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
SELECT CONCAT(YEAR(NOW()),‘年’,MONTH(NOW()),‘月’,DAY(NOW()),‘日’) 日期;
#判断两个日期的差别天数，前者比后者大，返回正数，小返回负数，相等返回0.
SELECT DATEDIFF(NOW(),‘2022-8-8’);
4、流程控制函数
#1.if函数
SELECT IF(100>9,‘不错呦’,‘不够大’) 备注;
#2.case语句
#语法：
#CASE
#WHEN 条件1 THEN 显示的值1
#WHEN 条件2 THEN 显示的值2
#…
#ELSE 显示的值n
#END
#案例：
SELECT salary,
CASE salary
WHEN salary>20000 THEN ‘购买汽车’
WHEN salary>10000 THEN ‘购买手机’
WHEN salary>5000 THEN ‘购买pad’
ELSE ‘购买棒棒糖’
END 购买资格
FROM employees;
多行插入
INSERT INTO beauty (id,NAME,phone,boyfriend_id)
VALUES(106,‘古力娜扎’,‘119’,1),
(107,‘范冰冰’,‘112’,1),
(108,‘吉克隽逸’,‘888’,1);
数据更新
update
UPDATE 表名 SET 字段名=新值,字段名=新值
[WHERE 条件]
#案例1：将赵丽蓉更新为 关晓彤
UPDATE beauty SET NAME=‘关晓彤’
WHERE NAME=‘赵丽蓉’;
#数据删除
#删除的方式一：
DELETE FROM 表 [WHERE 条件]
#案例4：将黄晓明的女朋友信息和黄晓明都删除
DELETE b,bo FROM beauty b,boys bo
WHERE b.
boyfriend_id
=bo.
id
AND bo.
boyName
=‘黄晓明’;
库和表的管理
#一、库的操作
SHOW DATABASES;
#1.创建库
CREATE DATABASE student;
CREATE DATABASE IF NOT EXISTS student;
#2.删除库
DROP DATABASE student;
DROP DATABASE IF EXISTS student;
#二、表的操作
#1.创建表
#案例：创建学员信息表
#学号、姓名、性别、邮箱、生日
CREATE TABLE stuinfo(
stuNo INT,#学号
stuName VARCHAR(20),#姓名
gender CHAR(1),#性别
email VARCHAR(50),#邮箱
borndate DATETIME #生日
)
SHOW TABLES;
DESC stuinfo;
#2.修改表
#①修改表名
ALTER TABLE stuinfo RENAME TO student;
ALTER TABLE student RENAME stuinfo;
尖叫提示：to 是可以省略的
#②修改列名,不加类型 报错，需要加类型
ALTER TABLE stuinfo CHANGE COLUMN borndate birthday DATETIME;
change
#③修改列的类型
ALTER TABLE stuinfo MODIFY COLUMN gender VARCHAR(2);
#④添加新列
ALTER TABLE stuinfo ADD COLUMN phone VARCHAR(11);
#⑤删除列
ALTER TABLE stuinfo DROP COLUMN phone;
#3.删除表
DROP TABLE stuinfo;
DROP TABLE IF EXISTS stuinfo;
SHOW TABLES;
#4.表的复制
INSERT INTO stuinfo VALUES(2,‘少杰’,‘男’,’’,NOW());
SELECT * FROM stuinfo;
#①仅仅复制表的结构
CREATE TABLE newTable2 LIKE stuinfo
一、试创建表 Book
#字段 id，bookname,price,author
#id类型为 无符号整型，长度为12
#bookname类型为 可变字符型，最多字符数为10
#price 类型为浮点型
#author类型为可变字符型
CREATE TABLE IF NOT EXISTS Book(
id INT(12) UNSIGNED,
bookName VARCHAR(10),
price FLOAT,
author VARCHAR(10)
)
添加表级约束
CREATE TABLE stuinfo(
id INT ,
stuName VARCHAR(20),
gender CHAR,
age INT UNSIGNED,
【CONSTRAINT 约束名】约束类型(字段)
);
#案例
CREATE TABLE grade(
id INT PRIMARY KEY,
gradename VARCHAR(20)
)
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT NOT NULL,
stuName VARCHAR(20),
gender CHAR DEFAULT ‘男’,
age INT UNSIGNED,
gradeid INT,
PRIMARY KEY(id,stuname),#组合主键：id+stuname
CONSTRAINT uq UNIQUE(age),#唯一键
CONSTRAINT fk_stuinfo FOREIGN KEY(gradeid) REFERENCES grade(id)#外键约束
);
修改表时添加约束
#3.添加主键
#①方式一：列级约束的做法
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②方式二：表级约束的做法
#语法：
#ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (字段);
#案例：
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
★修改表时删除约束
#3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT ;
ALTER TABLE stuinfo DROP PRIMARY KEY;
事物的使用
1、隐式事务
比如：insert、update、delete本身就是一个事务
2、显式事务
特征：具有明显的开启和结束的标记
set autocommit=0; 取消隐式事务自动提交的功能
start transaction;开启事务
…增删改查sql语句
commit;提交事务
rollback;回滚事务
read uncommitted： 脏读、不可重复、幻读read committed:不可重复读、幻读repeatable read:幻读（mysql5.5版本之前会出现幻读，但5.5之后不会出现）serializable：串行化（性能极低）mysql默认的是repeatable read
如何查看隔离级别？
select @@tx_isolation;
如何设置隔离级别？
set @@tx_isolation=‘read-uncommitted’;
set [global] transaction isolation level read uncommitted;
2、视图应用
CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;
创建视图的语法：
create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]
修改视图的语法
alter view view_name
As select_statement
[with|cascaded|local|check option]
查看视图结构的语法
show tables;
3、创建存储过程和函数
（1）创建存储过程
create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body
（2）创建函数
create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body
（1）调用存储过程
call 存储过程名(参数列表)
（2）调用函数
Select 函数名(参数列表)
修改存储过程：
alter procedure 存储过程名 [charactristic…]
修改函数：
alter function 函数名 [charactristic…]
删除存储过程：
drop procedure [if exists] 存储过程名
删除函数：
drop function [if exists] 函数名
1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名
2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名
3.通过查看information_schema.routines了解存储过程和函数的信息（了解）
select * from rountines where rounine_name =存储过程名|函数名
#存储过程
#一、创建
#语法：
#CREATE PROCEDURE 存储过程的名称(【IN/OUT/INOUT】 参数名 参数类型)
#BEGIN
主体
#END
#一、创建无参的存储过程
#案例：创建存储过程，实现 查询beauty表的大于3号id的记录
window+R -> cmd -> mysql –uroot –proot
DELIMITER $$
自定义结束符
CREATE PROCEDURE mypro1()
BEGIN
SELECT * FROM beauty WHERE id>3;
END 调用存储过程callmypro1()调用存储过程call mypro1()调用存储过程callmypro1()
解决乱码
set names gbkKaTeX parse error: Expected \’EOF\’, got \’#\’ at position 2:#̲二、创建in类型参数的存储过程…
#三、创建多个in类型参数的存储过程
CREATE PROCEDURE mypro3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
SELECT COUNT() FROM admin
WHERE admin.username=username AND admin.password=PASSWORD;
END KaTeX parse error: Expected \’EOF\’, got \’#\’ at position 2:#̲四、创建out类型参数的存储过…
#调用存储过程
SELECT @a 查看返回的值CALLmypro4(′′,′′,@a) 查看返回的值CALL mypro4(\’\’,\’\’,@a) 查看返回的值CALLmypro4(′′,′′,@a)
#五、创建out类型参数的存储过程
CREATE PROCEDURE mypro5(IN a INT,IN b INT,OUT c INT,OUT d INT)
BEGIN
SET c=a2;
SET d=b*2;
END KaTeX parse error: Expected \’EOF\’, got \’#\’ at position 2:#̲调用存储过程CALL myp…
SELECT @a KaTeX parse error: Expected \’EOF\’, got \’#\’ at position 2:#̲六、创建inout类型参数的存…
#删除存储过程
DROP PROCEDURE 存储过程名
DELIMITER ;
DROP PROCEDURE mypro6 ;
#查看某个存储过程
SHOW CREATE PROCEDURE 存储过程名
SHOW CREATE PROCEDURE mypro1;
