sql常用语句
程序员文章站
2024-01-24 15:45:46
...
目录
数据库操作
登陆数据库
mysql -uroot -p我的密码;
退出数据库
# exit/quit/ctrl+d
显示时间
select now();
显示版本
select version();
创建数据库
create database python_test char set = utf8;
查看创建数据库的语句
show create database python_test;
删除数据库
drop database python_test;
使用数据库
use python_test;
查看当前使用数据库
select database();
数据表的操作
查看数据表
show tables ;
创建数据表 约束中符号默认为有符号
create table table_test(
id int unsigned unique primary key not null auto_increment,
name varchar(30) not null,
age tinyint unsigned,
high decimal(5,2),
gender enum("男","女","中性","保密") default "保密",
cls_id int unsigned
);
查看数据表的结构
desc table_test;
数据表中插入数据
insert into python_test values (0, "老王", 23, 187, "男", 2);
修改表结构
# 添加字段
alter table studuent add birthday datetime;
# 修改字段-不重命名版
alter table studuent modify birthday date;
# 修改字段-重命名版
alter table studuent change birthday brith date default "2000-01-01";
# 删除字段
alter table studuent drop brith; #一般不会删除数据,多做加法,少做减法
# 删除表中的数据 不删除表
delete from studuent where id > 3;
# 删除表
drop table studuent;
查看表的创建语句
show create table studuent;
1.数据创建
创建数据库
create database python_test charset=utf8;
查看数据库
show databases;
使用数据库
use python_test;
显示当前使用那个数据库
select database();
创建一个数据表 存0 1时使用bit 就行 bit(2)可以存4种 tinyint = bit(8) 2^8 = 256
create table student(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal (5,2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
向表中插入数据
insert into student values
(0,'小明',18,168.00,2,1,0),
(0,'小黄',17,175.00,1,2,0),
(0,'小红',14,177.00,2,3,0),
(0,'小汉',11,180.00,3,4,0),
(0,'小八',12,187.00,3,5,0),
(0,'小九',13,182.00,4,6,0),
(0,'小十',18,188.00,3,7,0),
(0,'小之',17,186.00,2,8,0),
(0,'小一',10,188.00,2,9,0),
(0,'小二',15,182.00,3,9,0),
(0,'小三',18,184.00,2,6,0),
(0,'小四',19,185.00,4,4,0),
(0,'小五',13,190.00,2,3,0),
(0,'小六',14,189.00,2,4,0),
(0,'小七',15,178.00,2,5,0),
(0,'十一',15,167.00,1,7,0),
(0,'十二',18,176.00,1,2,0);
insert into classes values
(0, "python01期"),
(0, "python02期"),
(0, "python04期");
show tables ;
desc classes;
select * from student;
2.基本查询语句
查询
# 查询所有字段
select * from student;
# 查询指定字段
select id, name from student;
# 使用as给字段起别名
select name as 姓名, age as 年龄 from student;
# select 表明.字段 ..... from 表名;
select student.name, student.age from student;
# 可以通过as给表其别名
# select 别名.字段 .... from 表名 as 别名;
select student.name, student.age from student;
select s.name, s.age from student as s;
# 消除重复行
# distinct 字段
select distinct gender from student;
3.条件查询语句
条件查询
# 1. 比较运算符 > < >= <= !=
# --select ... from 表名 where ...;
select * from student where age > 18; # 显示所有字段
select name, gender, id from student where age > 18; # 显示选中的字段信息
#2. 逻辑运算符
# and
# 18到28岁之间的学生信息
select * from student where age > 18 and age < 28;
select * from student where age > 18 and gender = "女";
select * from student where age > 18 and gender = 2;
# or
select * from student where age > 18 or gender = 2;
# not
# 不在 18岁以上 并且是女性这个范围内的信息
select * from student where not age > 18 or gender = 2; # 仅仅对前一个条件起作用
# 不在18岁以上的女性的这个范围内的信息
select * from student where not (age > 18 or gender = 2); # not对后面和括号内的都起作用
# 年龄不是小于或者等于18 并且是女性
select * from student where not ( age < 18 or age = 18) and gender = 2;
select * from student where not age <=18 and gender = 2;
#3. 模糊查询（进行匹配 所以效率低）
# like
-- % 替换零个或者多个
-- _ 替换一个
# eg:查询姓名以小开头的信息
select * from student where name like "小%";
# 查询姓名当中有小的名字
select * from student where name like "%小%";
# eg:查询有两个字的名字
select * from student where name like "小_";
# eg:查询两个字以上的名字数据信息
select * from student where name like "__%";
# rlike (正则表达式)
# 查询以小开始的信息
select * from student where name rlike "^小.*";
# 查询已小开始 九结束的信息
select * from student where name rlike "^小*九$"
select * from student where name regexp "^小*九$"
select * from student where name regexp "一"
4.范围查询
in表示在一个非连续的范围内
# 查询年龄为18,34的人信息
select name from student where age = 18 or age = 34;
select name from student where age = 18 or age = 34 or age = 12;
select name, age from student where age in (12, 18, 34);
not in不非连续的范围内
# 不是年龄为18， 34的人信息
select name, age from student where age not in (12, 18, 34);
between … and …表示在什么范围之内 属于连续问题查询
# 在什么范围
select name, age from student where age between 12 and 34;
# 不再范围
select name, age from student where age not between 12 and 34;
select name, age from student where not age between 12 and 34; # not ... between ... and 是一种语法
select name, age from student where age not (between 12 and 34); # 语法错误
判断空 is null
# 判断身高为空
select name from student where age is null;
判断非空 is not null
select name from student where age is not null;
5.排序查询
排序
# order by 字段
# asc 从小到达排序 即升序
# desc 从大到小排序 即将序
# 在排序过程中遇到相同值时会按照主键进行排序
# 查询年龄到18～34之间的男性，按照年龄从小到达排序
select * from student where (age between 12 and 23) and gender = 1 order by age desc;
# order by 多个字段
select * from student where (age between 12 and 23) and gender = 1 order by age desc, gender desc, height desc; # 在年龄相同的情况下按照性别按照升序的方式排序,如果前面排序字段都不相同则后面的字段不执行
6.聚合函数
聚合函数：一些普通的函数得出的一些结论，不允许与其他字段混合使用, 想要用就要使用分组, 分组和聚合函数一起使用
# 求总数
# count
# 查询男性有多少人
select count(name), avg(height) from student;
select * from student where gender = 1;
select count(*) from student where gender = 1;
select count(*) as 男性人数 from student where gender = 1;
# 求最大值
# max
# 查询最大年龄
select * from student;
select max(age) as 最大值 from student;
# 最小值
# min 秋女性年龄最小值
select min(age) as 最小值 from student where gender = 2;
# 求和
# sum
# 求所有年龄总和
select sum(age) as 和 from student;
# 求平均值
# avg
select sum(age)/count(*) as 均值 from student;
select avg(age) as 最大值 from student;
# 四舍五入
# round(123.232221133, 4)
select round(avg(age), 2) as 四舍五入 from student;
# 经验：语言里面小数是由误差的，例如银行当中，但是整数没有
# 举例：3.14 我们乘以100变成整数。
分组GROUP BY
分组和聚合函数一起使用，否则分组就没啥意义
GROUP BY关键字通常和集合函数一起使用，比如MAX()、MIN()、COUNT()、SUM()、AVG()。
例如，要返回每个水果供应商提供的水果种类，这时就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。
# 按照性别分组，查询所有的性别
select gender, count(gender) from student group by gender; # 先分组，再从组内取数据
# 计算每种性别的人数
select gender, count(*) from student group by gender; # count(*)表示对分组的计算个数 count(*)指从分组内计算个数
desc student;
# 按照性别分组
select gender, count(gender), group_concat(name, ' ', id, ' ', height, ' ', cls_id) from student group by gender;
# 计算男性的人数
select gender, count(*) from student where gender = 1;
select gender, count(*) from student where gender = 1 group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 3 |
+--------+----------+
1 row in set (0.00 sec)
# group_concat(...) 显示的内容
#在MySQL中，可以在GROUP BY子句中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。
select gender, group_concat(name, age, id), count(*) from student where gender = 1 group by gender;
+--------+---------------------------------+----------+
| gender | group_concat(name, age, id) | count(*) |
+--------+---------------------------------+----------+
| 男 | 小黄172,十一1516,十二1817 | 3 |
+--------+---------------------------------+----------+
1 row in set (0.00 sec)
select gender, group_concat(name, "_", age, " ", id), count(*) from student where gender = 1 group by gender;
+--------+---------------------------------------+----------+
| gender | group_concat(name, "_", age, " ", id) | count(*) |
+--------+---------------------------------------+----------+
| 男 | 小黄_17 2,十一_15 16,十二_18 17 | 3 |
+--------+---------------------------------------+----------+
1 row in set (0.00 sec)
# where是对原表的数据进行判断
# having表示对分组进行条件判断,是进行分组之后在判断
# 查询平均年年龄超过18岁的性别以及姓名 hanving avg(age)>18
select gender, group_concat(name) from student group by gender having avg(age)>8;
select gender, group_concat(name), avg(age), count(*) from student group by gender having count(*) > 3;
select gender, group_concat(name), avg(age) from student group by gender having avg(age) > 11;
#在GROUP BY子句中使用WITH ROLLUP
#使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
select gender, group_concat(name), count(name) from student group by gender with rollup having avg(age)>8;
#由结果可以看到，通过GROUP BY分组之后，在显示结果的最后面新添加了一行，该行Total列的值正好是上面所有数值之和。
多字段分组
使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，
然后在第1个字段值相同的记录中再根据第2个字段的值进行分组，以此类推。
select gender, cls_id, group_concat(name, ' ', cls_id) from student group by gender, cls_id;
GROUP BY和ORDER BY一起使用
某些情况下需要对分组进行排序，在前面的介绍中，ORDER BY用来对查询的记录排序，如果和GROUP BY一起使用可以完成对分组的排序。
select gender, group_concat(name, age, height) from student group by gender having avg(age)>8 order by count(height);
GROUP BY子句按订单号对数据进行分组，SUM()函数便可以返回总的订单价格，HAVING子句对分组数据进行过滤，使得只返回总价格大于100的订单，最后使用ORDER BY子句排序输出。
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
7.分页查询
limit start, count
–直接限制查询出来的个数
带一个参数的LIMIT指定从查询结果的首行开始，唯一的参数表示返回的行数，即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。返回第一行时，位置偏移量是0。因此，“LIMIT 1, 1”将返回第二行，而不是第一行。
MySQL 8.0中可以使用“LIMIT 4 OFFSET 3”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
# 查询前五个数据
select * from student limit 5; # 只显示前两个
select * from student order by age desc limit 5;
# 限制查询的个数
select * from student limit 7, 4; # 开始第一个为0 规律：limit (第N页-1)*每页的个数, 每页的个数;
# 注意：limit必须放在最后。
# 顺序为where order by limit
select * from student where gender = 2 order by height desc limit 0, 2
8.连接查询：即多个表之间进行关联查询
1.内连接查询 即取交集
#inner join ... on on意味着条件
select * from studuent inner join classes;
select * from studuent inner join classes on studuent.cls_id = classes.id; #找打相应的信息显示
--按照要求现实姓名和年级
select studuent.* from studuent;
select classes.* from classes;
select studuent.*, classes.name from studuent inner join classes on studuent.cls_id = classes.id;
--简写重命名
select s.*, c.name from studuent as s inner join classes as c on s.cls_id = c.id;
--加上排序
select s.*, c.name from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
--更改显示顺序
select c.name, s.* from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id ;
--多条件排序
select c.name, s.* from studuent as s inner join classes as c on s.cls_id = c.id order by c.name, s.id, s.age;
2. 外连接查询(分为左连接右连接)
–右连接查询
–左连接查询(那个表在左边，以这个表为基准取查询的信息，取不出来莫认为Null)
select * from studuent as s left join classes as c on s.cls_id = c.id;
--与inner join ... on比较
select * from studuent as s inner join classes as c on s.cls_id = c.id;
--left是指left左边(等号左边)的数据表
select * from studuent;
select * from classes as c left join studuent as s on c.id = s.cls_id;
–right join … on 一般用的比较少 使用左连接调换两个数据表的顺序即可
–查询没有对应班级信息的学生
–思路：对于查询出的结果我们可以将其看作一个新的表，然后在此新表的基础上进行再次操作查询
–如果在原表里面进行查询使用where
–在查询的基础上进行再次操作使用having
select * from studuent as s left join classes as c on c.id = s.cls_id having c.id is NULL;
select * from studuent as s left join classes as c on c.id = s.cls_id where c.id is NULL; #这样也行
数据表中数据的增删该查(C Create U Update R Retrieve D Delete)
# 增加
insert into classes values (0, "菜鸟班"); #枚举类型插入式可以相当于一个从1开始的列表
#部分插入
insert into studuent (name, gender) values ("小桥", 2);
#多行插入
insert into studuent (name, gender) values ("小桥", 2), ("大桥", 2);
# 删除
#物理删除(不推荐)
delete from 表名 where 条件;
delete from studuent where id > 3;
#逻辑删除
#用一个字段表示该条信息是否可用,例如给studuent表添加一个字段is_delete
alter table studuent add is_delete bit default 0;
update studuent set is_delete = 1 where id = 11;
# 修改
update studuent set gender = 1 where id = 2;
update studuent set gender = 1, name = "西施" where id = 2;
# 查询(重点)
select * from studuent;
select * from studuent where id > 3;
select id, name, age, gender from studuent where gender = 2;
select id, name as 姓名, age as 年龄, gender as 性别 from studuent where gender = 2; #字段的顺序也是查询结果显示的顺序
数据库引擎 InnoDB(支持事务处理,外键与行级锁)