概述
[TOC]
8:00 之前进班
晚自习10：00
Day26 笔记 -- Mysql
数据库入门
SQL：结构化查询语言
DDL：数据定义语言
DML：数据操作语言
DCL：数据控制语言
TCL：事务控制语言
数据库的连接：
mysql -u用户名 -p密码
C:WINDOWSsystem32>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> quit
Bye
C:WINDOWSsystem32>mysql -uroot -p410221
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 数据库管理语句
创建数据库
create database 数据库名 charset 字符集编码 collate 校队规则（排序规则）
如果不指定字符集、使用安装 mysql 时设置的字符集
mysql> create database db2220;
Query OK, 1 row affected (0.00 sec)删除数据库
drop database 数据库名
mysql> drop database dbtest;
Query OK, 0 rows affected (0.01 sec)展示所有的数据库
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bdtest |
| company |
| db_2220 |
| javaee_2218 |
| mysql |
| performance_schema |
| sys |
+--------------------+展示数据库创建信息
show create database 数据库名
mysql> show create database db2220;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db2220 | CREATE DATABASE `db2220` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)修改数据库
alter database 数据库 charset.... collate...
mysql> alter database db2220 default charset gb2312;
Query OK, 1 row affected (0.00 sec)切换数据库
use 数据库名
mysql> use db2220;
Database changed表管理的操作语句
展示所有的表
show tables
mysql> show tables;
Empty set (0.00 sec)创建表
create table 表名（列名 1 类型， ....）
mysql> create table student(id int, name varchar(20), age int);
Query OK, 0 rows affected (0.04 sec)数据类型：
数值：int/bigint/double/decimal
int(11) 数字表示数据的显示宽度，需要配合无符号属性和zerofill一起使用
decimal 表示确定精度的小数
字符串、文本等：
char() 固定长度，最大255字符
varchar() 长度可变，最大65535字符
text...
blob...
日期时间：
date
datetime 常用
timestamp 照样是按照年月日时分秒进行展示
显示表结构
desc student;
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)删除表
drop table 表名
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)修改表
添加列
alter table 表名 add column 列名 类型
mysql> alter table student add column sex char(2);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0删除列
alter table 表名 drop column 列名
mysql> alter table student drop column sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0修改列的类型
alter table 表名 modify column 列名 新的类型
mysql> alter table student modify column sex varchar(2);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0修改列名
alter table 表名 change column 旧列名 新列名 类型
mysql> alter table student change column sex gender varchar(1);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0重命名表
rename table 旧表名 to 新表名
mysql> rename table student to stu;
Query OK, 0 rows affected (0.01 sec)增删改操作
insert
insert into 表名（列名,.......） values （值, ....）
列名和值的个数、顺序、类型一定要保持一致
如果给所有列添加数据、列名可以省略、需要注意值的个数和顺序
同一个 insert 语句、也可以一次插入多条记录
-- values和列名个数、顺序、类型要保持一致
mysql> insert into stu(id,name,age,gender) values(1,'Jobs',50,'男');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(name,id,age,gender) values('Jordan',2,55,'男');
Query OK, 1 row affected (0.00 sec)
-- 如果给所有字段插入数据，列名可以省略，但是values后面数据，顺序要和创建表的是顺序一致
mysql> insert into stu values(10,'张三',23,'女');
Query OK, 1 row affected (0.01 sec)
-- 可以针对某些列添加数据
mysql> insert into stu(id,name) values(6,'李四');
Query OK, 1 row affected (0.00 sec)
-- 插入多条记录
mysql> insert into stu(id,name,age,gender) values(12, 'Tom', 12, '男'),(13, 'Jerry', 10, '男');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 报错
mysql> insert into stu(id,name,age,gender) values(12, 'Tom', 12, '男邢');
ERROR 1406 (22001): Data too long for column 'gender' at row 1delete
delete from 表名 where 条件
mysql> delete from stu where id=1;
Query OK, 0 rows affected (0.00 sec)
-- 删除表中所有数据
mysql> delete from stu;
Query OK, 5 rows affected (0.00 sec)截断表：truncate 表名
mysql> truncate stu;
Query OK, 0 rows affected (0.03 sec)delete删除可以通过事务的回滚进行恢复、 truncate 不能针对自增值、delete 不会清空自增的记录、truncate 会
update
update 表名 set 列 = 值，...... where 条件
-- 更新符合条件的记录
mysql> update stu set age=30 where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 不写条件，所有记录都会更新
mysql> update stu set age=25;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0查询语句
查询所有记录
select * from 表名
根据列名进行查询
select 列名，..... from ....
mysql> select id,name,age+10 from stu;
+------+-------+--------+
| id | name | age+10 |
+------+-------+--------+
| 12 | Tom | 35 |
| 13 | Jerry | 40 |
+------+-------+--------+
2 rows in set (0.00 sec)列别名
select 列 as 别名....
as 可以省略
mysql> select id,name sname from stu;
+------+-------+
| id | sname |
+------+-------+
| 12 | Tom |
| 13 | Jerry |
+------+-------+
2 rows in set (0.00 sec)
mysql> select id as sid,name sname from stu;
+------+-------+
| sid | sname |
+------+-------+
| 12 | Tom |
| 13 | Jerry |
+------+-------+
2 rows in set (0.00 sec)针对列可以进行基本算数运算
针对数值型字段
mysql> select id,name,age+10 from stu;
+------+-------+--------+
| id | name | age+10 |
+------+-------+--------+
| 12 | Tom | 35 |
| 13 | Jerry | 40 |
+------+-------+--------+
2 rows in set (0.00 sec)
mysql> select id,name,age+10 new_age from stu;
+------+-------+---------+
| id | name | new_age |
+------+-------+---------+
| 12 | Tom | 35 |
| 13 | Jerry | 40 |
+------+-------+---------+
2 rows in set (0.00 sec)
mysql> select id,name,age+id from stu;
+------+-------+--------+
| id | name | age+id |
+------+-------+--------+
| 12 | Tom | 37 |
| 13 | Jerry | 43 |
+------+-------+--------+
2 rows in set (0.00 sec)去重 distinct
distinct 后可以跟一个或多个字段
只有多个字段的值都相同时，才属于重复数据
mysql> select distinct age from stu;
+------+
| age |
+------+
| 25 |
| 30 |
| 24 |
| 19 |
+------+
4 rows in set (0.00 sec)
mysql> select * from stu;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
5 rows in set (0.00 sec)
-- distinct后如果使用多个字段，只有当这些字段的数据都相同时，才认为重复
mysql> select distinct age,gender from stu;
+------+--------+
| age | gender |
+------+--------+
| 25 | 男 |
| 30 | 男 |
| 25 | 女 |
| 19 | NULL |
+------+--------+
4 rows in set (0.00 sec)条件查询
比较运算符
> >= < <= =等于 <>不等于 !=不等于mysql> select * from stu where age=25;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 12 | Tom | 25 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from stu where age!=25;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 13 | Jerry | 30 | 男 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu where age<>25;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 13 | Jerry | 30 | 男 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu where age>25;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 13 | Jerry | 30 | 男 |
+------+-------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stu where age>=25;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)
mysql> select * from stu where age<25;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stu where age>20 and age<30;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 12 | Tom | 25 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from stu where age>20 and age<=30;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)
mysql> select * from stu where age>=20 and age<=30;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)逻辑运算符
and (&&) or (||)
mysql> select * from stu where age>=20 and age<=30;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)
mysql> select * from stu where age=20 or age=30;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 13 | Jerry | 30 | 男 |
+------+-------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stu where age=20 or age=30 or age=19;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 13 | Jerry | 30 | 男 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
2 rows in set (0.00 sec)between and
表示范围、闭区间
相当于通过 and 进行比较
-- 相当于 age>=20 and age<=30
mysql> select * from stu where age between 20 and 30;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)判断 null
is null
is not null
mysql> select * from stu where gender is null;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stu where gender is not null;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+-------+------+--------+
4 rows in set (0.00 sec)模糊查询
like， 针对字符串类型
% 匹配一个或多个字符
_ 匹配一个字符
mysql> select * from stu where name like 'h%';
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from stu where name like '%e%';
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 13 | Jerry | 30 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from stu where name like '_e__';
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 2 | hehe | 25 | 女 |
+------+------+------+--------+
1 row in set (0.00 sec)in
in 后的括号中，给出的是集合，集合中的数据不一定是连续的 一般可以转换成 or 的写法
mysql> select * from stu where id in(1,12,4,5);
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 12 | Tom | 25 | 男 |
| 1 | haha | 25 | 男 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
3 rows in set (0.00 sec)
-- in 可以使用or进行替换表示
mysql> select * from stu where id=1 or id=12 or id=4 or id=5;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 12 | Tom | 25 | 男 |
| 1 | haha | 25 | 男 |
| 5 | heihei | 19 | NULL |
+------+--------+------+--------+
3 rows in set (0.00 sec)使用聚合函数
count 统计记录数
count(*) count(1) count(列名)， 使用列名， 忽略 null 的数据
-- 统计总记录数
mysql> select count(*) from stu;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from stu;
+----------+
| count(1) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from stu;
+-----------+
| count(id) |
+-----------+
| 5 |
+-----------+
1 row in set (0.00 sec)
-- 根据字段进行统计，忽略值是null的数据
mysql> select count(gender) from stu;
+---------------+
| count(gender) |
+---------------+
| 4 |
+---------------+
1 row in set (0.00 sec)
mysql> select count(*) from stu where age=25;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)avge 求平均值
返回浮点数
mysql> select avg(age) from stu;
+----------+
| avg(age) |
+----------+
| 24.8000 |
+----------+
1 row in set (0.00 sec)sum 求和
mysql> select sum(age) from stu;
+----------+
| sum(age) |
+----------+
| 105 |
+----------+
1 row in set (0.00 sec)最大值和最小值
max（）/ min（）
mysql> select max(age) from stu;
+----------+
| max(age) |
+----------+
| 30 |
+----------+
1 row in set (0.00 sec)
mysql> select min(age) from stu;
+----------+
| min(age) |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)排序 order by
order by 列名 asc/desc, 列名...
默认升序
mysql> select * from stu order by id;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | NULL | NULL |
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
+------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from stu order by id asc;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | NULL | NULL |
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
+------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from stu order by id desc;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 13 | Jerry | 30 | 男 |
| 12 | Tom | 25 | 男 |
| 5 | heihei | NULL | NULL |
| 2 | hehe | 25 | 女 |
| 1 | haha | 25 | 男 |
+------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from stu order by name;
+------+--------+------+--------+
| id | name | age | gender |
+------+--------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | heihei | NULL | NULL |
| 13 | Jerry | 30 | 男 |
| 12 | Tom | 25 | 男 |
+------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> update stu set name='hehe',age=36 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu order by name;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
| 5 | hehe | 36 | NULL |
| 13 | Jerry | 30 | 男 |
| 12 | Tom | 25 | 男 |
+------+-------+------+--------+
5 rows in set (0.00 sec)
-- 先根据name排序，name值相同，根据age排序
mysql> select * from stu order by name,age desc;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 1 | haha | 25 | 男 |
| 5 | hehe | 36 | NULL |
| 2 | hehe | 25 | 女 |
| 13 | Jerry | 30 | 男 |
| 12 | Tom | 25 | 男 |
+------+-------+------+--------+
5 rows in set (0.00 sec)分组 group by
group by 列名、列名
mysql 8 不再支持根据分组的类名进行排序
select后可以使用分组的字段，可以使用聚合函数（聚合函数中可以使用非分组字段）
分组一般结合聚合函数使用
mysql> select gender from stu group by gender;
+--------+
| gender |
+--------+
| NULL |
| 女 |
| 男 |
+--------+
3 rows in set (0.00 sec)
mysql> select gender from stu group by gender desc;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| NULL |
+--------+
3 rows in set, 1 warning (0.00 sec)
mysql> select gender,count(*) from stu group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL | 1 |
| 女 | 1 |
| 男 | 3 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select gender,avg(age) from stu group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| NULL | 36.0000 |
| 女 | 25.0000 |
| 男 | 26.6667 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select gender,avg(age) avg_age from stu group by gender;
+--------+---------+
| gender | avg_age |
+--------+---------+
| NULL | 36.0000 |
| 女 | 25.0000 |
| 男 | 26.6667 |
+--------+---------+
3 rows in set (0.00 sec)
-- 本例中查询id和name字段无实际意义
mysql> select id,name,gender,avg(age) avg_age from stu group by gender;
+------+------+--------+---------+
| id | name | gender | avg_age |
+------+------+--------+---------+
| 5 | hehe | NULL | 36.0000 |
| 2 | hehe | 女 | 25.0000 |
| 12 | Tom | 男 | 26.6667 |
+------+------+--------+---------+
3 rows in set (0.00 sec)
-- 根据多个字段进行分组，只有两个字段的值都相同时，才属于一组
mysql> select gender,age,count(*) from stu group by gender,age;
+--------+------+----------+
| gender | age | count(*) |
+--------+------+----------+
| NULL | 36 | 1 |
| 女 | 25 | 1 |
| 男 | 25 | 2 |
| 男 | 30 | 1 |
+--------+------+----------+
4 rows in set (0.00 sec)mysql 查询
分页 limit
size 2
pageNum 1 2 3
offest 0 2 4
根据页码计算偏移量的算法： offset = (pageNum -1) * size
mysql> select * from stu limit 0,2;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
+------+-------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu limit 2;
+------+-------+------+--------+
| id | name | age | gender |
+------+-------+------+--------+
| 12 | Tom | 25 | 男 |
| 13 | Jerry | 30 | 男 |
+------+-------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu limit 2,2;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | haha | 25 | 男 |
| 2 | hehe | 25 | 女 |
+------+------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu limit 4,2;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 5 | hehe | 36 | NULL |
+------+------+------+--------+
1 row in set (0.00 sec)
mysql> select * from stu limit 4,4;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 5 | hehe | 36 | NULL |
+------+------+------+--------+
1 row in set (0.00 sec)书写函数【重点】
书写顺序
select from 表
where
group by having
order by
limit
mysql> select gender,count(*) from stu where gender is not null group by gender order by gender desc limit 0,2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
| 女 | 1 |
+--------+----------+
2 rows in set (0.00 sec)
mysql> select gender,count(*) from stu where gender is not null group by gender having count(*)>1;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
+--------+----------+
1 row in set (0.00 sec)
mysql> select gender,count(*) from stu where gender is not null group by gender having gender='男';
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
+--------+----------+
1 row in set (0.00 sec)
-- order by 写到group by前面，报错
mysql> select gender,count(*) from stu where gender is not null order by gender group by gender having gender='男';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by gender having gender='男'' at line 1
mysql>常用函数
日期函数
now()/ date_format()/ date_add()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-02-14 10:18:20 |
+---------------------+
1 row in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2023-02-14 10:18:49 |
+---------------------+
1 row in set (0.00 sec)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-02-14 |
+----------------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 10:19:25 |
+----------------+
1 row in set (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-02-14 10:20:15 |
+---------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1676341245 |
+------------------+
1 row in set (0.00 sec)
mysql> select datediff('2023-01-14', '2023-02-14');
+--------------------------------------+
| datediff('2023-01-14', '2023-02-14') |
+--------------------------------------+
| -31 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2023-02-14', '2023-01-14');
+--------------------------------------+
| datediff('2023-02-14', '2023-01-14') |
+--------------------------------------+
| 31 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2023-02-14 10:51:58 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(), '%Y-%m-%d');
+--------------------------------+
| date_format(now(), '%Y-%m-%d') |
+--------------------------------+
| 2023-02-14 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(), '%Y年%m月%d日');
+---------------------------------------+
| date_format(now(), '%Y年%m月%d日') |
+---------------------------------------+
| 2023年02月14日 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(), interval 1 day);
+---------------------------------+
| date_add(now(), interval 1 day) |
+---------------------------------+
| 2023-02-15 10:54:07 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(), interval 10 day);
+----------------------------------+
| date_add(now(), interval 10 day) |
+----------------------------------+
| 2023-02-24 10:54:27 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(), interval 10 year);
+-----------------------------------+
| date_add(now(), interval 10 year) |
+-----------------------------------+
| 2033-02-14 10:54:44 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub(now(), interval 10 year);
+-----------------------------------+
| date_sub(now(), interval 10 year) |
+-----------------------------------+
| 2013-02-14 10:54:56 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2023 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> alter table stu add column birth datetime;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> update stu set birth='2000-01-01' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+------+-------+------+--------+---------------------+
| id | name | age | gender | birth |
+------+-------+------+--------+---------------------+
| 12 | Tom | 25 | 男 | NULL |
| 13 | Jerry | 30 | 男 | NULL |
| 1 | haha | 25 | 男 | 2000-01-01 00:00:00 |
| 2 | hehe | 25 | 女 | NULL |
| 5 | hehe | 36 | NULL | NULL |
+------+-------+------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> update stu set birth=now() where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+------+-------+------+--------+---------------------+
| id | name | age | gender | birth |
+------+-------+------+--------+---------------------+
| 12 | Tom | 25 | 男 | NULL |
| 13 | Jerry | 30 | 男 | NULL |
| 1 | haha | 25 | 男 | 2023-02-14 11:00:22 |
| 2 | hehe | 25 | 女 | NULL |
| 5 | hehe | 36 | NULL | NULL |
+------+-------+------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> select id,year(birth) from stu where id=1;
+------+-------------+
| id | year(birth) |
+------+-------------+
| 1 | 2023 |
+------+-------------+
1 row in set (0.00 sec)
mysql>字符串函数
concat()
-- 字节长度
mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
mysql> select length('哈哈');
+------------------+
| length('哈哈') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
-- 字符长度
mysql> select char_length('哈哈');
+-----------------------+
| char_length('哈哈') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select length('你好');
+------------------+
| length('你好') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
-- 相同返回0
mysql> select strcmp('hello', 'hello');
+--------------------------+
| strcmp('hello', 'hello') |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)
-- 前面比后面小，返回-1
mysql> select strcmp('hello', 'wello');
+--------------------------+
| strcmp('hello', 'wello') |
+--------------------------+
| -1 |
+--------------------------+
1 row in set (0.00 sec)
-- 前面比后面大，返回1
mysql> select strcmp('wello', 'hello');
+--------------------------+
| strcmp('wello', 'hello') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
-- 拼接
mysql> select concat('haha', 'hehe');
+------------------------+
| concat('haha', 'hehe') |
+------------------------+
| hahahehe |
+------------------------+
1 row in set (0.00 sec)
mysql> select concat('haha', 'hehe', 'houhou');
+----------------------------------+
| concat('haha', 'hehe', 'houhou') |
+----------------------------------+
| hahahehehouhou |
+----------------------------------+
1 row in set (0.00 sec)
-- 第2个参数表示截取的开始索引，从1开始
mysql> select substring('hello', 2);
+-----------------------+
| substring('hello', 2) |
+-----------------------+
| ello |
+-----------------------+
1 row in set (0.00 sec)
-- 第三个参数，表示截取的长度
mysql> select substring('hello', 2, 2);
+--------------------------+
| substring('hello', 2, 2) |
+--------------------------+
| el |
+--------------------------+
1 row in set (0.00 sec)
mysql> select trim(' haha ');
+------------------+
| trim(' haha ') |
+------------------+
| haha |
+------------------+
1 row in set (0.00 sec)
mysql> select length(trim(' ahah '))
-> ;
+-------------------------+
| length(trim(' ahah ')) |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)
-- 如果存在，返回开始位置的索引，不存在，返回0
mysql> select instr('hello', 'll');
+----------------------+
| instr('hello', 'll') |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
mysql> select instr('hello', 'lldd');
+------------------------+
| instr('hello', 'lldd') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)数据约束
默认值
如果某个字段设置了默认值、插入数据时、如果不处理该字段，使用默认值填充数据
非空
非 null
INSERT INTO stu(id) VALUES(8)
错误代码： 1364
Field 'name' doesn't have a default value
INSERT INTO stu(id, NAME) VALUES(9, null)
错误代码： 1048
Column 'name' cannot be null唯一
mysql 中、通过唯一索引设置唯一性约束
针对唯一索引、多个记录的值可以为 null
update stu set stu_no='22222' where id=6
错误代码： 1062
Duplicate entry '22222' for key 'stu_no'主键
通过主键表述表中唯一一条记录
唯一 + 非空
多个字段可以设置联合主键
INSERT INTO stu(id, NAME) VALUES(8, '冠希')
错误代码： 1062
Duplicate entry '8' for key 'PRIMARY'
INSERT INTO stu(id, NAME) VALUES(null, '冠希')
错误代码： 1048
Column 'id' cannot be null自增
针对主键
delete 数据时，不会修改系统中记录的自增值
数据库的设计的范式【了解】
第一范式：字段要保持原子性
第二范式：满足第一范式的基础上、其他字段要和主键有关联、主要针对联合主键的情况
第三范式：满足第二范式的基础上、其他字段和主键要直接关联
表关系【重点】
一对一
在其中任意表中，额外增加字段，关联另一张表
比如：人和身份证表
一对多
在表示多的表中增加额外的字段，通过该字段关联表示的表示少的表
比如： 部门和员工表
多对多
需要增加中间表、来表示多对多的关系
比如：课程表、学生表、选课表
外键约束理解
一般不建议设置外键约束
多表查询 【重点】
内连接
表 1 inner join 表 2 on 两个表的关联条件
首先明确需要那些表、然后再进行关联
SELECT * FROM employee
INNER JOIN department
ON employee.dept_id=department.id
-- 表名也可以有别名
SELECT * FROM employee AS e
INNER JOIN department d
ON e.dept_id=d.id
-- inner join 后的数据，当成一个临时表，从中取字段，可以通过where给出查询条件
SELECT d.dept_name FROM employee AS e
INNER JOIN department d
ON e.dept_id=d.id
WHERE e.emp_name='马云'
-- on 后面可以给出其他查询条件
SELECT d.dept_name FROM employee AS e
INNER JOIN department d
ON e.dept_id=d.id AND e.emp_name='马云'
-- inner 可以省略
SELECT d.dept_name FROM employee AS e
JOIN department d
ON e.dept_id=d.id
WHERE e.emp_name='马云'-- 查询庆澳选择了哪些课，需要查询课程id和课程的名称
-- 首先明确需要哪些表，然后再进行关联
SELECT c.id,c.course_name FROM student s
INNER JOIN sel_course sc
ON s.id=sc.stu_id
INNER JOIN course c
ON c.id=sc.course_id
WHERE s.stu_name='庆澳'
SELECT c.* FROM student s
INNER JOIN sel_course sc
ON s.id=sc.stu_id
INNER JOIN course c
ON c.id=sc.course_id
WHERE s.stu_name='庆澳'
-- 已知学生id，根据学生id查询学生选课的id和名称
SELECT c.* FROM sel_course sc
INNER JOIN course c
ON sc.course_id=c.id
WHERE sc.stu_id=2
ORDER BY c.id DESC左外连接
left join
满足条件的左表中的数据都会查询出来，如果右表总没匹配的数据，显示 null
-- left join .... on .....左外连接
SELECT e.emp_name,dept_id,d.dept_name FROM department d
left JOIN employee e
ON d.id = e.dept_id;右外连接
right join
右表中的数据都会查询出来、如果左表中没有匹配的数据，显示 null
-- right join ...on..... 右外连接
SELECT e.emp_name,dept_id,d.dept_name FROM department d
right JOIN employee e
ON d.id = e.dept_id;
SELECT e.emp_name,dept_id,d.dept_name FROM department d
right JOIN employee e
ON d.id = e.dept_id
where e.dept_id is not null;交叉连接
cross join
通过交叉连接，可以得到所有可能的组合，查询到的记录数 = 表 1 的行数 * 表 2 的行数
通过交叉连接返回被连接的两个表的笛卡尔积，即返回结果的行数等于两个表行数的成绩
-- 交叉连接 不能写 on
-- 写 on 后会变成 内连接
SELECT * FROM student s
CROSS join person p;
-- 也是得到笛卡尔积 后面可以写 where 也是交叉连接效果
select * from department, employee;自连接
进行连接的表示同一张表，使用自连接查询的表、一般是数据中存在上下级关系的表，比如表示菜单关系的表
针对菜单项、分类有这种父子或层级关系、一般可以采用自连接的表设计
-- 自连接
select c.id,c.menu_name from menu p
inner join menu c
on p.id = c.pid
where p.menu_name ='文件';
select p.id,p.menu_name,c.menu_name from menu p
left join menu c
on p.id = c.pid
where p.pid is null
ORDER BY p.id;子查询
子查询也称 "内部查询" 或者 "嵌套查询"，是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询） 的数据来源或者 判断条件。
子查询可以敲入 SELECT、INSERT、UPDATE和 DELETE 语句中、也可以和 = 、<、 >、 in 等运算符一起使用
子查询常用在 WHERE 子句和 FROM 子句后边：
当用于 WHERE 子句、根据不同的运算符，子查询可以返回单行单列、多行多列、单行多列数据。子查询就是要能够作为 WHERE 子句查询条件的值。
当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。
insert 中使用子查询
将一个表中的数据添加到另一个表中
-- 子查询
INSERT INTO new_employee ( username, age ) SELECT
emp_name,
age
FROM
employee;from 后使用子查询
将一个表中的数据添加到另一个表中
-- form后使用子查询，得到的临时表，需要设置别名
SELECT temp.did,temp.eid FROM (
SELECT d.id did,d.dept_name, e.id eid, e.emp_name FROM department d
INNER JOIN employee e
ON d.id=e.dept_id) tempwhere 后使用子查询
子查询得到数据，作为 where 后条件判断的数据使用
结合 =，>, < 等运算符
-- 查询比马化腾工资高的员工的数据
SELECT * FROM employee
WHERE salary > (
SELECT salary FROM employee
WHERE emp_name='马化腾'
)
-- 比销售部最高工资还高的所有员工信息
SELECT * FROM employee WHERE salary > (
SELECT MAX(e.salary) FROM department d
INNER JOIN employee e
ON d.id=e.dept_id
WHERE d.dept_name='销售部'
)in
in 表示是否在某个范围内、作用上等效于多个条件使用 or 连接
-- 查询 10 楼有哪些员工
select * from department d
INNER JOIN employee e
On d.id = e.dept_id
WHERE d.floor = 10;
-- 子查询
SELECT
*
FROM
employee
WHERE
dept_id IN (
SELECT
id
FROM
department
WHERE
floor = 10);any
any 是表示子查询结果中任意一个【比最小的大】
-- 比销售部任意一个员工年龄大的所有员工信息
-- > any表示最小的大
SELECT * FROM employee WHERE age > ANY(
SELECT e.age FROM employee e
INNER JOIN department d
ON e.dept_id=d.id
WHERE d.dept_name='销售部'
)
SELECT * FROM employee WHERE age > (
SELECT MIN(e.age) FROM employee e
INNER JOIN department d
ON e.dept_id=d.id
WHERE d.dept_name='销售部'
)all
all 表示子查询结果中的所有
-- >ALL 表示比最大的大
SELECT * FROM employee WHERE age > ALL(
SELECT e.age FROM employee e
INNER JOIN department d
ON e.dept_id=d.id
WHERE d.dept_name='销售部'
)
SELECT * FROM employee WHERE age > (
SELECT MAX(e.age) FROM employee e
INNER JOIN department d
ON e.dept_id=d.id
WHERE d.dept_name='销售部'
)union 和 union all
UNION 相关操作符用于合并两个或多个 SELECT 语句的结果集
UNION 操作符选取不同的值。如果允许重复的值、使用 UNION ALL
SELECT * FROM store_sales WHERE store_name='A'
UNION ALL
SELECT * FROM online_sales WHERE store_name='A'
-- 会去重
SELECT * FROM store_sales WHERE store_name='A'
UNION
SELECT * FROM online_sales WHERE store_name='A'事务【重点】
针对数据库的一组操作，这些操作要么全部成功，要么全部失败
启动事务：start transaction
提交：commint
回滚：rollback
提交和回滚都会结束事务
成功提交事务
-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money-1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id | name | money |
+------+----------+-------+
| NULL | zhangsan | 7000 |
| NULL | lisi | 2000 |
+------+----------+-------+
2 rows in set (0.00 sec)
mysql> update account set money=money+1000 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id | name | money |
+------+----------+-------+
| NULL | zhangsan | 7000 |
| NULL | lisi | 3000 |
+------+----------+-------+
2 rows in set (0.00 sec)
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)异常事务回滚
-- 开始事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money-1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id | name | money |
+------+----------+-------+
| NULL | zhangsan | 6000 |
| NULL | lisi | 3000 |
+------+----------+-------+
2 rows in set (0.00 sec)
-- 报错
mysql> update1 account set money=money+1000 where name='lisi';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update1 account set money=money+1000 where name='lisi'' at line 1
mysql> select * from account;
+------+----------+-------+
| id | name | money |
+------+----------+-------+
| NULL | zhangsan | 6000 |
| NULL | lisi | 3000 |
+------+----------+-------+
2 rows in set (0.00 sec)
-- 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
-- 恢复到之前数据
mysql> select * from account;
+------+----------+-------+
| id | name | money |
+------+----------+-------+
| NULL | zhangsan | 7000 |
| NULL | lisi | 3000 |
+------+----------+-------+
2 rows in set (0.00 sec)事务的特性
ACID
原子性【Atomicity】
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都要发生，要么都不发生。
一致性【Consistency】
事务必须是数据库从一个一致性状态变换成另外一个一致性状态。
隔离性【lsolation】
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性【Durability】
持久性是指一个事务一旦被提交，他对数据库中的数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
索引
索引是一种快速查询表中内容的机制，使用索引可以提高查询效率，相当于字典的目录可以将查询过程中经常使用的条件设为索引
索引分类
普通索引
这是最基本的索引类型，他没有唯一性子类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如 CREATE INDEX <索引的名字> ON tablename（列的列表）；
修改表，例如 ALTER TABLE tablename ADD INDEX 【索引的名字】（列的列表）；
创建表的时候指定索引，例如 CREATE TABLE tablename（【...】, INDEX [索引的名字]）(列的列表)；
唯一索引
这种索引和前面的"普通索引" 基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键索引
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。
主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。
但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表);
全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。
在MySQL中，全文索引的索引类型为FULLTEXT。
全文索引可以在VARCHAR或者TEXT类型的列上创建。
它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。
对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。
需要注意的是，MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效
索引的缺点
首先，索引要占用磁盘空间。表中建的索引越多，占的磁盘空间也就越多。
第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。
扩展
默认存储引擎：InnoDB，很多时候将其和 MyISAM进行对比，InnoDB 支持事务，外键，行锁
InnoDB 默认使用 B + 树 结构存储索引数据
存储结构
聚簇索引 ：主键对应的索引，属于聚簇索引，叶子节点存的是表中的记录
非聚簇索引：非主键对应的索引，又称为二级索引，叶子节点存储的是主键值和索引值的对应关系【回表（优化效率）】
联合索引：本质上也是二级索引，多个字段构成联合索引，多个索引是有排列顺序的。
最后
以上就是甜甜大树为你收集整理的千锋教育2218 MYSQLDay26 笔记 -- Mysql的全部内容，希望文章能够帮你解决千锋教育2218 MYSQLDay26 笔记 -- Mysql所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复