整数int
小整数tinyint
小数decimal
字符串varchar
日期时间datetime
database数据库table表field列，字段
record记录，行
- create table 创建表
create table表名(字段名 数据类型(字符长度)，字段名 数据类型());
- insert增
insert into X values(…,…);
在表X中插入此数据。
insert into X (ID，name) values (3，'曹操');只添加ID和name
在表X中插入3和曹操
insert into X values(5,'犹豫',90);
insert into X(ID，name) values (6，'鲁肃');
insert into X (name) values ('纣王');
多条insert语句用;分开。
insert into X values(……),(……);
一条insert语句添加多条。
- update改
语法update 表名 set 字段＝值,字段＝值where 条件;
update X set age＝50;把表X中所有的age改为50
update X set age＝50，name＝'狗蛋' where ID＝2;把ID为2的age改为50name改为狗蛋。
4、select查询
语法：select* from 表名
select* from X;查询表X所有数据
select ID from X;查询表X中的ID字段
select now();
5、delete删除数据
语法：delete from 表名 where 条件;
delete from X where ID＝6;
删除表中ID为6的记录
delete from X;
删除表X的所有记录。
truncate table 表名;没有条件
删除表中所有数据。
速度上truncate＞delete
如果想删除部分数据用delete，注意加上where子语句
如果想保留表而将所有数据删除，自增长字段恢复从1开始，用truncate，
6、drop table删除表
语法：drop table 表名;
drop table X;删除表X。
drop table if exists B;删除表B，如果B不存在什么都不做。
约束
主键(primary key)值不能重复
auto_increment代表值自动增长
非空(not null)此字段不允许填写空值
唯一(unique)此字段的值不允许重复
默认值(default)当不填写此值时会使用默认值，如果填写以填写为准
总结：创建表create table
删除表drop table 表/drop table if exists 表名
插入记录insert into
查询表select * from 表名
修改记录 update 表名 set 字段＝值 where 条件
删除记录delete from 表名 where条件
truncate table 表名
第二部分
1、查询所有字段
select * from X 查询X表中的所有字段
select name,sex from X查询X表name和sex字段
2、通过as给字段起一个别名
select card as 身份证，name as 姓名，sex as 性别 from X
别名的as可以省略
select card 身份证，name 姓名，sex 性别from X;
字段名as 别名和字段名 别名的结果是一样的
- 通过as给表X起一个别名
select * from X as putong;
可以省略as
select * from X putong;
- 消除重复记录distinct.
select distinct * from X;
5、where子语句
查询X表中学生ID等于'222'的记录
select * from X where ID＝'222';
- 查询X表中年龄age等于30的姓名name,班级class
select name,class from X where age＝30;
- select后面的*或字段名,决定了返回什么样的字段(列)
select中的where子句,决定了返回什么样的记录(行)
8、where后面支持多种运算符，进行条件的处理
比较运算、逻辑运算、模糊查询、范围查询、空判断
逻辑运算and、or、not
模糊查询
select * from X where name like '孙%';查询X表中姓孙的人员
select * from X where name like '孙_';
查询X表中姓孙并且两个字的人员
select * from X where name like '%阿%'
查询姓名中带有阿的同学
范围查询
查询家乡在北上广的学生记录
select * from X where hometown＝'北京'or hometown＝'上海'or hometown＝'深圳';
select * from X where hometown in ('北京','上海','深圳');
查找age年龄在25至30的学生的记录
select * from X where age＞＝25 and age＜＝30;
select * from X where age between 25 and 30;
空判断
null不是0也不是空
null不能用比较运算符判断
查询card身份证为null的学生的记录
select * from X where card is null;
查询card身份证不为空的学生的记录
select * from X where card not is null;
修改age年龄为25且name为'孙俪'的学生class为'2班'
update X set class ＝'2班' where age＝25 and name＝'孙俪';
删除class为'1班',并且age大于30的学生记录
delete from X where age＞30 and class＝'1班';
排序
语法select * from 表名order by 字段1 asc | desc,字段2 asc|desc，……
asc(默认值)从小到大排序，既升序
desc从大到小排序，既降序
查询所有学生记录按age年龄从小到大排序
select * from X order by age;
select * from X order by age asc;可省略asc
查询所有学生记录按age年龄从大到小排序
select * from X order by age desc;
查询所有学生记录，按age年龄从大到小排序，年龄相同时，再按ID从小到大排序
select * from X order by age desc, ID;
查询所有男学生记录，按class班级从小到大排序，班级相同时，再按ID从小到大排序
select * from X where sex ＝'男'order by class,ID desc;
当一条select语句出现了where和order by
select * from 表名 where 条件 order by 字段1,字段2;
- 聚合函数
聚合函数不能用在where条件中
1,count总记录数
语法select count (字段名) from表名;
查询学生总数(查询表有多少记录)
select count (*) from X;
select count (name) from X;
有多少name
select count (distinct class) from X;
查询有几个
select count distinct class from X;
查询显示不重复的几个
select count (*) from X where sex＝'女';
查询女同学数量
2,max最大值min最小值
查询X表中的最大年龄
select max (age) from X;
查询X表中女生最大年龄
select max (age) from X where sex＝'女'
查询X表中'1班'最大年龄
select max (age) from X where class ＝ '1班';
3,sum求和
查询学生age年龄总和
select sum(age) from X;
查询hometown为'北京'的学生age年龄总和
select sum (age) from X hometown＝'北京';
4,avg平均值
avg(字段)表示求此字段的平均值
查询学生年龄平均值
select avg (age) from X;
查询sex性别为'男'的平均年龄
select avg (age) from X where sex＝'男';
使用avg忽略空null值计算时直接跳过
查询所有学生中年龄最大，最小，平均年龄;
select max (age), min (age), avg (age) from X;
查询'1班'共有多少学生
select * from X where class＝'1班';
查询'3班'中年龄小于30岁的同学有几个
select count(*) from X where class＝'3班' and age＜30;
- 数据分组
1分组
group by 表名
select 聚合函数 from 表名 where 条件 group by 字段;
select 聚合函数 from 表名 group by 字段
group by就是配合聚合函数使用的。
分别查询sex男女同学数量
select count(*) from X group by sex;
男女数量5 7
select sex, count (*) from X group by sex;
表中显示'男'、'女'
where和分组
查询'1班'不同性别学生数量
select sex, count (*) from X where class ＝ '1班' group by sex;
用数据分组方法，统计各个班级学生总数，平均年龄最大年龄。最小年龄
select class, count (*), avg(age), max(age), min(age) from X group by class;
统计各个班级学生总数，平均年龄最大年龄最小年龄，但不统计'3班'统计结果按班级名称从大到小排序
select class, count (*) avg (age), max (age), min (age) from X where class ＜＞ '3班' group by class desc;
where和group by 和order by的顺序
select * from 表名 where 条件 group by 字段 order by 字段
分组聚合之后的数据筛选
having子句
总是出现在group by之后
select * from 表名 group by 字段 having 条件
用where查询男生总数
where找筛选符合条件的记录。然后再聚合统计
select count (*) from X where sex ＝ '男';
用having查询男生总数
having先分组聚合统计，再在统计的结果中筛选
select count (*) from X group by sex having sex ＝ '男';
having的使用
where是对标的原始数据进行筛选
having是对group by之后已经分过组的数据进行筛选
where后面条件不能使用聚合函数having可以使用聚合函数
求班级人数大于3人得班级名字
select class from X group by class having count (*) ＞3;
查询班级总人数大于2人得班级名称以及班级对应的总人数
select class, count(*) from X group by class having count (*) ＞2;
查询平均年龄大于30的班级名称和班级总人数
select class, count(*) from X group by class having avg(age) ＞ 30;
limit 显示指定记录数
select * 表名 where 条件 group by 字段 order by 字段 limit start, count;
limit总是出现在select语句的最后
start代表开始行号，行号从0开始编号
count代表要显示多少行
省略start,默认从0开始,从第一行开始
语法limit 开始行，获取行数
select * from 表名 limit start, count;
从start开始获取count条数据
start索引从0开始，如省略start默认从0开始
查询前3行学生记录
select * from X limit 0,3;
查询从第4行开始的三条语句
select * from X limit 3, 3;
查询年龄最大同学的name
select name from X order by age desc limit 1;
数据分页显示
m每页显示多少条记录
n第n页
(n－1)*m,m
把计算结果写到limit后面
每页显示4条记录，第3页的结果
select * from X limit 8,4;
已知每页记录数，求一张表需要几页显示完
求总页数
总页数/每页的记录数
如果结果是整数，那么就是总页数，如果结果有小数，那么就在结果的整数上+1
每页显示5页记录，分别多条select显示每页的记录
第一页
select * from X limit 5;
第二页
select * from X limit 5, 5;
第五部分连接
1,内连接
select *from 表1 inner join 表2 on 表1.
字段 ＝ 表2.字段
内连接最重要的是要找对两张表要关联的字段
select * from a inner join b on a.id ＝ b.id;
方法二隐式内连接
select * from 表1,表2 where 表1.字段 ＝ 表2.字段;
a表与b表内连接，只显示name，courseno和score
select name, courseno, score from a inner join b on a.studentno ＝ b.studentno;
起别名as可以省略
select name 姓名, courseno 课程编号, score 成绩 from a aa inner join b bb on aa.studentno ＝ bb.studentno;
带走where条件的内连接
语法 select * from 表1 inner join 表2 on 表1.字段 ＝表2.字段 where 条件
查询王昭君的信息，要求只显示姓名，课程号，成绩
select name, courseno, score from X inner join Y on X.studentno ＝ Y.studentno where X.name ＝'王昭君';
带有and逻辑运算符的内连接查询
查询姓名为'王昭君',并且成绩小于90的信息,要求只显示姓名成绩
select name, score from X inner join Y on X.studentno ＝ Y.studentno where X.name＝ '王昭君' and Y.score＜90;
多表内连接查询
查询学生信息和成绩以及成绩对应的课程名称(三表查询)
select name, coursename from X inner join Y on X.studentno ＝ Y.studentno inner join Z on Y.courseno ＝ Z.courseno;
写SQL三步法
搭框架 基本的select 语句框架搭建起来，如果有多表，把相应的多表也联合进来
看条件 决定where后面的具体条件
显示的字段 select后面要显示什么字段
查询所有学生的Linux课程成绩，要求只显示姓名，成绩，课程名。
select name, score, coursename from X inner join Y on X.studentno ＝ Y.studentno inner join Z on Y.courseno ＝ Z.courseno where coursename ＝ 'Linux';
查询成绩最高的男生信息，要求显示姓名，课程，成绩
select name, score, coursename from X inner join Y on X.studentno ＝ Y.studentno inner join Z on Y.courseno ＝ Z.courseno where sex ＝ '男' order by score desc limit 1;
左连接
查询所有学生的信息以及成绩，包括没有成绩的学生
select * from X left join Y on X.studentno ＝Y.studentno;
右连接
查询所有课程的信息，包括没有成绩的课程
select * from X right join Y on X.courseno ＝ Y.courseno;
多表联合查询，同名字段的处理方式
如果一条select要用到多个表，表中有同名字段，就需要表名.字段名加以区分
select X.studentno from X inner join Y on X.studentno ＝ Y.studentno;
自关联
自关联是同一张表做连接查询
自关联下，一定找同一张表可关联的不同字段
查询一共有多少省
select count(*) from areas where pid is null;
查询一共有多少市
select count(*) from areas where pid is not null;
同一张表查询广东省的所有城市
select * from areas a1 inner join areas a2 on a1.id ＝ a2.pid where a1.name ＝ '广东省';
子查询
子查询是嵌入到主查询中
子查询是辅助主查询的，要么充当条件，要么充当数据源
子查询是可以独立存在的语句，是一条完整的select语句，主查询离开子查询是无法独立运行的
查询大于平均年龄的学生的记录
select * from X where age ＞ (select avg(age) from X);
标量子查询
子查询返回结果只有一行一列
列子查询
子查询返回一列多行
查询30岁的学生的成绩
select studentno from X where age＝30;
select * from Y where studentno in ('001', '002', '003');
用子查询实现
select * from Y where studentno in (select studentno from X where age ＝ 30);
列子查询－子查询返回一列多行
用内连接实现查询所有女信息成绩
select * from X inner join Y on X.studentno ＝ Y.studentno where sex ＝ '女';
用子查询查询所有女生的信息和成绩
select * from (select * from X where sex ＝ '女') x inner join Y on x.studentno ＝ Y.studentno;
表级子查询返回多行多列
列出男职工的总数和女职工的总数
select sex, count(*) X group by sex;
列出非党员职工总数
select count(*) from X where politicalstatus ＜＞ '党员';
列出所有职工工号姓名以及所在部门名称
select empid, empname, deptname from X inner join Y on X.deptid ＝ Y.deptid;
列出所有职工工号姓名和对应工资
select X.empid, empname, salary from X inner join Y on X.empid ＝ Y.empid;
列出领导岗的姓名和所在部门名称
select empname, deptname from X inner join Y on X.deptid ＝ Y.deptid where leader is null;
列出职工数总人数大于4的部门和总人数
select deptid, count(*) from X group by deptid having count(*) ＞4;
列出职工总人数大于4的部门号和部门名称
select X.deptid,deptname from X inner join Y on X.deptid ＝ Y.deptid group by X.deptid having count(*) ＞4;
列出开发部和测试部的职工号和姓名
select empid, empname from X inner join Y on X.deptid ＝ Y.deptid where deptname in ('开发部', '测试部');
列出市场部所有的女职工姓名和政治面貌
select empname,politicalstatus from X inner join Y on X.deptid ＝Y.deptid where deptname ＝'市场部' and sex ＝'女';
显示所有职工姓名工资，包括没有工资的职工姓名
select empname, salary from X left join Y on X.empid ＝Y.empid;
求不姓孙的所有职工工资总和
select sum(salary) from X inner join Y on X.empid ＝ Y.empid where not empname like '孙%';
MySQL内置函数
1,字符串函数
拼接字符串concat (str1,str2……)
把12，34，'ab'拼接为一个字符串'1234ab'
select concat (12, 34, 'ab');
2,包含字符个数length(str)
如果字符串中包含utf8格式汉字，一个汉字length返回3
计算字符串'abc'长度
select length('abc');为3
计算字符串'我和你'长度
select length ('我和你');为9
计算字符串为'我andyou'长度
select length ('我andyou');为9
查询表X中name长度等于9(3个utf8格式的汉字)的学生的信息
select * from X where length (name) ＝ 9;
MySQL内置函数可以在where条件后面使用
left从字符串左侧截取指定数量字符
left(字符串, n);
n代表字符串左侧截取n个字符
截取字符串'我和你abc'的左端3个字符
select left('我和你abc' ,3);结果为我和你
right(字符串, n);
从字符串右侧截取指定数量字符串
substring从字符串指定位置截取指定数量字符
starting (字符串,起始位置,n)
起始位置从一开始，n代表截取的数量
截取字符串'我和你abc'从第二个字符开始的三个字符
select startring('我和你abc', 2, 3)
结果为和你a
内置函数可以用在select显示的字段名中
截取X表中所有学生的姓
select left(name, 1) name from X;
或select substring (name, 1, 1) from X;
查询X表中card字段，截取出生年月日，显示李白的生日
select substring(card, 7, 8) from X where name ＝ '李白';
查询X表所有学生信息，按生日从大到小排序
select * from X order by substring (card, 7, 8);
3,去除空格
ltrim 去除字符串左侧空格
ltrim(带空格的字符串)
ltrim(str)返回删除左侧空格字符串str
去除字符串' abcd '左侧空格
select ltrim (' abcd ');
结果为'abcd '
rtrim 去除字符串右侧空格
trim去除字符串两侧空格
数学函数
round
1.745四舍五入，保留整数位
select round (1.745, 0);返回2
1.768四舍五入保留两位小数
select round (1.768, 2);结果为1.77
查询中学生平均年龄，并四舍五入
select round(avg(age)) from X;
随机数rand
每次运行都会产生一个从0到1之间的浮点数
select rand();
结果为一个随机数
从表中随机抽取一名学生
select * from X order by rand() limit 1;
日期时间函数
current_date返回系统日期
select current_date ();
current_time 返回系统时间
select current_time ();
now返回系统日期与时间
select now ();
日期和时间函数案例
插入记录时，插入系统当前时间
create table a (id int, indate datetime);
insert into a values (1, '2022－06－06 12:01:01');
insert into a values (1, now());
select *from a;
存储过程
创建存储过程stu()，查询X表所有学生信息
create procedure stu()
begin
select *from X;
end
调用存储过程stu
call stu();
删除存储过程stu()
删除的时候不用写名字后面的()
drop procedure stu;
drop procedure if exists stu;
视图
视图就是对select语句的封装
视图可以理解为一张只读的表，针对视图只能用select，不能用delete和update
创建视图
语法:create view视图名称as select 语句;
创建视图，名叫stu_nan,查询所有男生信息
create view stu_nan as
select * from X where sex ＝ '男';
使用视图stu_nan
select *from stu_nan;
在视图stu_nan中查找年龄大于25岁学生信息
select *from stu_nan where age＞25;
删除视图
语法:drop view 视图名称;
drop view if exists 视图名称;
删除视图stu_nan
drop view stu_nan;
drop view if exists stu_nan;
事务
事务是多条更改数据操作的SQL语句集合
一个集合数据有一致性，要么都成功要么都失败
begin开始事务
rollback回滚事务，放弃对表的修改
commit提交事务，对表的修改生效
开启事务
命令:begin
说明:开启事务后执行修改update或者删除delete记录语句，变更会写到缓存中，而不会即刻生效
回滚事务
命令:rollback
说明:放弃修改
提交事务
命令:commit
说明:将修改的数据写入实际的表中
开启事务，删除X表中studentno为001的记录，同时删除Y表中studentno为001的记录，回滚事务，两个表的删除同时放弃
begin;
delete from X where studentno＝ '001';
delete from Y where studentno＝'001';
rollback;
开启事务，删除X表中studentno为001的记录，同时删除Y表中studentno为001的事务。提交事务，使两个表的删除同时生效
begin;
delete from X where studentno＝'001';
delete from Y where studentno＝'001';
提交事务，两个删除操作同时生效
commit;
索引index
调用索引不需要显示的写调用索引的语句，只要where条件后面用到的字段建立了索引，那么系统就会自动调用
给表建立索引，目的是加快select查询速度
如果一个表记录很少，几十几百条不用索引
但如果表的记录特别多，没有索引select语句效率会非常低
创建索引
语法:create index 索引名称 on 表名(字段名称(长度));
如果指定字段是字符串，需要指定长度。建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分
为表X的age字段创建索引名为age_index
create index age_index on X (age);
为表X的name字段创建索引，名为name_index
create index name_index on X (name (10));
查询表中age等于30的学生
select *from X where age＝30;
where条件后面的字段，数据库系统会自动查找是否有索引
这里会自动调用age_index
查看索引
对于主键，系统会自动建立索引
语法show index from 表名;
查看X表中的所有索引
show index from X;
删除索引
语法drop index 索引名称 on 表名;
删除X表中的索引age_index
drop index age_index on X;
索引的优缺点
提高select的查询速度
降低update，delete，insert语句的执行速度
项目中绝大部分80%以上是select，所以index是必须的
在实际工作中如果涉及到大量的数据修改操作，修改之前可以先把索引删除，修改完成后再把索引建立起来
基于命令行的MySQL
mysql －h mysql服务器的地址 －u 用户名 －p
－h如果使用本机的mysql，－h可以省略
MySQL登陆后的常用命令
show databases
显示系统所有的数据库
use 数据库名
使用指定的一个数据库
使用itdate数据库
use itdate
show tables
查看指定数据库有多少表
如果命令行默认字符集与数据库默认字符集不同
在Windows默认字符集是gbk
set names gbk
告诉mysql，客户端用的字符集是gbk
选择了数据库之后就可查看数据库有多少表
在命令行中每条sql语句用;结尾
可以通过desc 表名 查看一个表的字段结构
desc X
查看X表每个字段的定义
创建数据库
命令格式
create database 数据库名 default [默认字符集];
建立一个数据库，名为mytest，默认字符集为utf8
create database mytest default charset utf8;
在命令行下创建和删除数据库
创建:create database 数据库名 default charset 字符集;
删除drop database X
drop database if exists X;