MySQL
服务启动与停止
两种方法
1.此电脑>管理>启动与停止
2.命令行 net start MySQL服务名
停止:net stop MySQL服务名
发生系统错误 5。
拒绝访问。
说明没有权限
这时应该以管理员身份运行,停止后登录会不成功
登录命令mysql -uroot -p0071hanxiaolei
或者用mysql提供的客户端工具
退出指令 quit
或exit
切换mysql的版本mysql -u root -P23306 -p
(大写的P是切换的端口号)
登录不是本机的数据库命令 mysql -u root -h 127.0.0.1 -p
查看数据库的版本 mysql -v
mysql -version
MySQL演示
查看所有数据库
1.show databases; 注意有分号
information_schema 保存数据库的一些系统信息 存储权限啥的
mysql 运行时的信息 比如输出文件夹
performance_schema 监控mysql各类指标的
sys 存储一些指标
2.创建新的表 新的数据库
create database dbtext1; (分号结束)
3.选择数据库
use dbtest1;
创建表
create table employees(id int ,name varchar(15));
查看表
show tables;
查看所有数据
select * from employees;
加入数据
insert into employees values('1001','Tom');
上下箭头可以调命令
调到** select * from employees;
**查看所有数据
mysql> select * from employees;
+------+------+
| id | name |
+------+------+
| 1001 | Tom |
+------+------+
1 row in set (0.01 sec)
如果添加中文,8.0版本可以,,,5.7不可以
查看编码规则
show variables like 'charactor_%';
3. SQL的分类
DDL
四条主线
1、DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
CRUD
查看成形后的表结构
DESC tb_user;
操作数据库
对数据库进行增删改查
操作表
练习:
复制表
create table 表名 like 被复制的表名;
DML
//修改整张表的delete_time
update sp_type set delete_time=2020;
2、DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
(增删改查)(重中之重)
除了数字类型,其他类型数据都需要加引号。
DCL
3、DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
权限控制
DQL
4、DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
select * from 表名;
* 对查询性能有影响
-- 查询出生日期在'1999-01-01'到'2005-03-04'的数据。
SELECT * FROM employees WHERE hire_date BETWEEN '1999-01-01' AND '2005-03-04';
注意日期类型也可以用between and 虽然他有分号
SELECT
*
FROM
employees;
SELECT DISTINCT
country_name
FROM
employees;
SELECT
*
FROM
employees
WHERE
salary > 10000;
SELECT
*
FROM
employees
WHERE
salary >= 10000;-- 查询工资大于1万且小于1万5的数据
SELECT
*
FROM
employees
WHERE
salary > 10000
AND salary <= 10500;-- 简化
SELECT
*
FROM
employees
WHERE
salary BETWEEN 10000
AND 10500;
-- 查询出生日期在'1999-01-01'到'2005-03-04'的数据。
SELECT * FROM employees WHERE hire_date BETWEEN '1999-01-01' AND '2005-03-04';
-- 查询工资为10000的人
SELECT * FROM employees WHERE salary=10000;
-- 查询工资不等于10000万的数据
SELECT * FROM employees WHERE salary!=10000;
SELECT * FROM employees WHERE salary<>10000;
-- 查询工资等于10000 17000 6000的数据
SELECT * FROM employees WHERE salary=10000 or salary=17000 or salary=9000;
SELECT * FROM employees WHERE salary in (10000,17000,9000);
-- 查询pct为null的数据
SELECT * FROM employees WHERE commission_pct is null;
-- 查询pct不为null的数据
SELECT * FROM employees WHERE commission_pct is not null;
模糊查询
-- 模糊查询
-- 查询以J开头的数据
SELECT * FROM employees WHERE first_name like 'J%';
-- 查询第二个字符为o的数据
SELECT * FROM employees WHERE first_name like '_o%';
-- 查询名字中包含am的人
SELECT * FROM employees WHERE first_name like '%am%';
null值不参与聚合函数运算
`SELECT * FROM employees;
-- 查询人员信息,按工资升序排序 默认值是asc
SELECT * FROM employees ORDER BY salary ASC;
-- 查询人员信息,按工资降序排序
SELECT * FROM employees ORDER BY salary DESC;
-- 多字段排序
-- 查询人员信息,按工资降序排序 如果工资一样,就按税率升序排序
SELECT * FROM employees ORDER BY salary DESC ,employee_id DESC;`
-- 聚合函数
-- 查询一共有多少人
SELECT * FROM employees;
-- count 统计的字段不能为null
SELECT count(email) FROM employees;
-- 建议统计的数量字段为星,默认查询最快的一列
SELECT COUNT(*) FROM employees;
-- 查询工资最高的人
SELECT max(salary) FROM employees;
-- 查询工资最低的人
SELECT min(salary) FROM employees;
-- 查询工资总工资
SELECT sum(salary) FROM employees;
-- 查询工资的平均工资
SELECT avg(salary) FROM employees;
-- 查询字段数据中有null的值的数据 null数据不参与聚合运算
SELECT min(manager_id) FROM employees;
分组查询
-- 分组查询
-- 查询各自不同部门id的工资总和
-- 分组之后,查询字段为聚合函数和分组字段,查询其他字段无任何意义
SELECT department_id, sum(salary) FROM employees GROUP BY department_id;
-- 查询各自不同部门id的工资总和 工资不大于10000,不参与
SELECT department_id ,sum(salary) FROM employees WHERE salary>10000 GROUP BY department_id;
-- 查询各自不同部门id的工资总和 以及各自的人数,工资不大于10000,不参与
SELECT department_id,sum(salary),count(salary) FROM employees WHERE salary>10000 GROUP BY department_id;
-- 查询各自不同部门id的工资总和 以及各自的人数,工资不大于10000,不参与 并且分组之后各部门人数大于2
SELECT department_id,sum(salary) ,count(*) FROM employees WHERE salary>10000 GROUP BY department_id having count(*) >2;
分页查询
-- 分页查询
-- 从0开始查询,查询三条数据
SELECT * FROM employees LIMIT 0,3;
-- 每页显示三条数据,查询第一页数据
SELECT * FROM employees LIMIT 0,3;
-- 每页显示三条数据,查询第二页数据
SELECT * FROM employees LIMIT 3,3;
分页起始页码计算公式:
(当前页码-1)*每页显示的条数
DQL执行顺序
DQL总结
函数
字符串函数
select concat('111','2222');
select lower('LOWER');
select upper('upper');
select lpad('111',6,'__');
select rpad('111',6,'--');
select trim(' Hello word ');
select substring('123456789',2,5);
数值函数
# 通过数据库函数,生成一个6位的随机验证码
select lpad(round(rand(),6)*1000000,6,'0');
日期函数
select curdate();
select curtime();
select now();
select date_add(now(),interval 70 month );
select datediff(now(),'2023-7-30');
#查询所有员工的入职天数,根据入职天数倒序
select name datediff(curdate(),entrydate) as entrydates from emp order by entrydates desc;
流程函数
# 返回true
select if(true,'OK','error');
# 返回false
select if('true','OK','error');
# 返回空
select ifnull('','default');
# 返回default
select ifnull(null,'default');
第三条语句
select id,name,
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学' from emp;
第四条语句
查询emp表的员工姓名及工作地址
select name,(case workadress when '北京' then '一线城市' when '天津' then '一线城市' else '二线城市' end) as '工作地址' from emp;
MySQL书写规范
数据库名,表名,表别名,字段名,字段别名等都是小写
SQL关键字,函数名,绑定变量等都是大写
注释
单行注释以`#`号开始
多行注释/* */
最近本的SELECT语句
SELECT (这里面写字段) ....FROM....(这里面写字段所在的表名) DUAl(伪表)
*是表中所有列(字段)的意思
不建议使用*,建议使用列名加别名的方式查寻。
查找某一列就可以用列名替换掉*,多个列要用逗号隔开,最后一个列后面就不用加了
列的别名
在列名后加一个名字,就是列的别名 或者在别名之前加一个As(可以省略)
列的别名也可以用双引号引起来(不要使用单引号,不符合标准)
字符串,,日期时间类型的变量需要使用一对单引号表示
去除重复行
DISTINCT
SELECT DISTINCT .....(点代表字段) from .....(字段所在的表)
错误示范
SELECT 字段名, DISTINCT 字段名 FROM 字段所在的表;
空值参与运算
空值 null
null不等同于0
空值参与运算,结果也一定为空
如果字段名为Null 为了防止结果为null 故IFNULL起作用 用0代替了null
SELECT 字段名 别名 , 字段名*(1+IFNULL(字段名,0))*12 FROM 字段名所在的表名
着重号
1旁边的那个键
当我们出现关键字时,我们使用着重号进行区分
SELECT * FROM `表名`;
查询常数
如果表中没有的列,我们可以通过查询常数进行添加
SELECT '尚硅谷',123,字段名 FROM 表名;
显示表结构
显示了表中字段的详细信息
DESCRIBE 表名; 或者
DESC 表名;
过滤数据
查询满足条件的数据
用WHERE来过滤
SELECT * FROM 表名
WHERE 要筛选的名称=条件;
注意第一句不加分号,第二句才加分号
为了多端适配,区分大小写
选中执行,就可以查出按条件筛选出来的数据了。
运算符
在sql中加号没有拼接作用,进行相加,100+‘1’=101 100+‘a’=100 不能转换为数字的按0处理
100*1.0=100.0
除法
100/2=50.0000 100 DIV 0= NULL(分母为0为NULL)
取模
12 % 3 = 0 12 mod -5 = 2 -12 mod 5= - 2 -12 mod -5 = -2 结果符号与被模数有关
比较运算符
为假0,为真1,否则为NULL
= 不同于其他语言中的 就是判断是否相等
1=2 0
1!=2 1
1='1' 1
1='a' 0
'a'=0 0
纯粹字符串比较不会转换为数字进行比较
'a'='b' 0
只要有NULL参与 结果就是NULL
<=> 安全等于
在没有NULL参与的情况下,使用与=相同
NULL<=>NULL 1
NULL<=>1 0
不像上述条件那样,怎么比都是null
为NULL而生
WHERE salay<=>NULL 查询条件为NULL的列
< > !=
都是不等于的意思
3<>2 1
'a'<>4 1
'a'<>null null
''!=null null
null<>null null
这要有null参与就是null
<= >= < > 与其他语言相同
用法
IS NULL
WHERE 列明 IS NULL; 查询为NULL的数据
ISNULL
WHERE ISNULL(列名);
IS NOT NULL
查询不为空的数据
WHERE 列名 IS NOT NULL;
不能写
WHERE 列名 != NULL 不能这样写,这样写不会有结果
改为
WHERE NOT 列名 <=> NULL
BETWEEN…AND…
区间查询,包括双边值
WHERE salary BETWEEN 6000 AND 8000;
查询工资为6000到8000的数据, 6000与8000要是写反了,就查询不到数据了
WHERE salary NOT BETWEEN 6000 AND 8000;
查询工资不为6000到8000的数据
或者
WHERE salary <6000 OR salary>8000;
不管是什么运算符,左右列名都要写全,否则不会有选择效果
WHERE salary <6000 && salary>8000;
IN , NOT IN
选择工资为60000,8000,90000的数据
WHERE salary IN (60000,8000,90000);
选择工资不为60000,8000,90000的数据
WHERE salary NOT IN (60000,8000,90000);
LINK 模糊查询
查询last-name中包含字符‘a’的员工数据
%号为通配符,代表不确定个数的字符
SELECT last_name FROM employees
WHERE last-name LINK '%a%';
这个比较常用
查询last-name中以‘a’开头的的员工数据
%号为通配符,代表不确定个数的字符
SELECT last_name FROM employees
WHERE last-name LINK 'a%';
查询last-name中以‘a’开头的的员工数据并且包含e的数据
%号为通配符,代表不确定个数的字符
SELECT last_name FROM employees
WHERE last-name LINK 'a%'AND last_name LINK '%e%';
_ 一个下滑线代表一个字符
查询last-name中以第二个字符为‘a’的的员工数据
%号为通配符,代表不确定个数的字符
_代表一个字符
SELECT last_name FROM employees
WHERE last-name LINK '_a%';
查询last-name中以第三个字符为‘a’的并且第二个字符为下滑线员工数据
%号为通配符,代表不确定个数的字符
_代表一个字符
这时候要使用转移字符分隔开
SELECT last_name FROM employees
WHERE last-name LINK '_\_a%';
逻辑运算符
AND的优先级高于OR
约束
如果名称为null了,添加时,主键会加一,即使未添加成功,主键也会加一下一次即隔开一个开始添加
CREATE TABLE emp (
id INT PRIMARY KEY ,
ename VARCHAR(50) NOT NULL UNIQUE,
joindate DATE NOT NULL,
salary DOUBLE(7,2) NOT NULL,
bonus DOUBLE(7,2) DEFAULT 0
);
当列是数字类型并且唯一约束,可以使用auto_increment
外键约束
注意:
先有主表才能再有从表
主表就是外部表列名
从表就是指定谁的列名,谁就是从表
先创建主表
-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY auto_increment,
dep_name VARCHAR(20),
addr VARCHAR(20)
);
才能创建从表
CREATE TABLE emp1(
id int PRIMARY KEY auto_increment,
name VARCHAR(50),
age INT ,
dep_id INT,
-- 添加外键 dep_id ,关联dept 表的id主键
CONSTRAINT fk_emp1_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
先插入主表数据
INSERT INTO dept(dep_name,addr) VALUES('研发部','广州');
INSERT INTO dept(dep_name,addr) VALUES('销售部','上海');
再插入从表数据
INSERT INTO emp1(name,age,dep_id) VALUES('王五',17,1);
INSERT INTO emp1(name,age,dep_id) VALUES('赵六',18,1);
INSERT INTO emp1(name,age,dep_id) VALUES('李四',20,2);
INSERT INTO emp1(name,age,dep_id) VALUES('张三',20,2);
或者
-- 删除外键
ALTER TABLE emp1 DROP FOREIGN KEY fk_emp1_dept;
-- 创建外键 创建完表之后创建外键
ALTER TABLE emp1 ADD CONSTRAINT fk_emp1_dept FOREIGN key(dept_id) REFERENCES dept(id);
数据库设计
多表查询
一对多
多对多
-- 创建订单表
CREATE TABLE tb_order(
id INT PRIMARY KEY auto_increment,
payment DOUBLE(10,2),
payment_type TINYINT,
sattus TINYINT
);
-- 创建商品表
CREATE TABLE tb_goods(
id INT PRIMARY KEY auto_increment,
title VARCHAR(50),
price DOUBLE(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id INT PRIMARY KEY auto_increment,
order_id INT,
goods_id INT,
count INT
);
-- 建表完成后,添加外键
ALTER TABLE tb_order_goods add CONSTRAINT fk_order_id FOREIGN KEY(order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods add CONSTRAINT fk_goods_id FOREIGN KEY(goods_id) REFERENCES tb_goods(id);
一对一
内连接
显式内连接 ...INNER JOIN ... ON ...
select e.name, d.name from emp e inner join user d on e.id=d.id;
外连接
自连接
一定要起别名
联合查询
子查询
标量子查询
指的的子查询返回的是一行一列的数据
列子查询
多行单列指的是查询结果为单列多行
行子查询
指的是多表查询
select * from emp where (id,name) =(select id,name from emp where name='张无忌');
表子查询
多行多列的子查询
//查询与"灰太狼",“红天狼”职位与薪资相同的员工信息
select job,salary from emp where name=‘灰太狼’ or name='红天狼';
select * from emp where (job,salary) in (上边的语句);
第一次查询得到的是一个表,第二次吧第一次查询的结果作为一个临时表
事务
设置全部sql的提交方式为手动,执行任何一条sql都需要手动提交
设置部分代码提交方式为手动提交
-- 创建账户表
CREATE TABLE account(
id int PRIMARY KEY auto_increment,
name VARCHAR(10),
money DOUBLE(10,2)
);
-- 添加数据
INSERT INTO account (name,money) VALUES ('张三',1000),('李四',2000);
SELECT * FROM account;
-- 转账操作
-- 开启事务
BEGIN;
-- 1.查询李四的金额
SELECT money FROM account WHERE name='李四';
SELECT money FROM account;
-- 2.李四的账户-500
UPDATE account set money=money-500 WHERE name='李四';
出错了。。。
-- 3.张三的金额+500
UPDATE account set money=money+500 WHERE name='张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务的并发问题
事务的隔离级别
JDBC