连接 mysql -uroot -p 退出 exit / quit 查询mysql版本 select version(); 查看所有的数据库 show databases; 使用数据库 use 库名; 查看创建数据库的语句 show create database 库名; 查看时间 select now(); 创建数据库 create database 库名; 删除 drop database 库名; 查看当前使用的数据库 select database();
数据表的操作
查看所有的表 show tables; 创建表 create table 表名; 查询表中所有的字段 select * from 表名; 查看指定的字段 select 字段1,字段2, from 表名;
修改表(alter)
添加字段 alter table 表名 add 字段 类型; 修改字段 alter table 表名 modify 字段 类型及约束; 修改字段 alter table 表名 change 原字段名 新字段名 类型及约束; 删除字段 alter table 表名 drop 字段; 设置和更改unique约束
设置 alter table TABLE_NAME add unique key(name) 给name字段添加unique约束
删除 alter table TABLE_NAME drop key name 给name字段删除unique约束
删除表
drop table 表名;
数据库的增删改查
-- 数据的准备-- 创建一个数据库create database python_test charset&＃61;utf8;-- 使用一个数据库use python_test;-- 显示使用的当前数据是哪个?select database();-- 创建一个数据表-- students表create table students(id int unsigned primary key auto_increment not null,name varchar(20) default &＃39;&＃39;,age tinyint unsigned default 0,height decimal(5,2),gender enum(&＃39;男&＃39;,&＃39;女&＃39;,&＃39;中性&＃39;,&＃39;保密&＃39;) default &＃39;保密&＃39;,cls_id int unsigned default 0,is_delete bit default 0);-- classes表create table classes (id int unsigned auto_increment primary key not null,name varchar(30) not null);-- 查询-- 查询所有字段-- select * from 表名;select * from students;-- 查询指定字段-- select 列1,列2,... from 表名;select name,age,gender from students;-- 使用 as 给字段起别名-- select 字段 as 名字.... from 表名;select name as 姓名,age,gender from students;-- select 表名.字段 .... from 表名;select name as 姓名,age,gender from students as s;-- 可以通过 as 给表起别名-- select 别名.字段 .... from 表名 as 别名;-- 消除重复行-- distinct 字段select distinct name,age from students;-- 条件查询-- 比较运算符-- select .... from 表名 where .....-- >-- 查询 大于18岁 的信息select * from students where age > 18;-- <-- 查询小于18岁的信息select * from students where age <18;-- >&＃61;-- <&＃61;-- 查询小于或者等于18岁的信息select * from students where age <&＃61; 18;-- &＃61;-- 查询年龄为18岁的所有学生的名字select * from students where age &＃61; 18;-- !&＃61; 或者 <> 不等于 -- 查询姓名不是居然的select * from students where name !&＃61; &＃39;居然&＃39;;-- 逻辑运算符-- and-- 18到28之间的所有学生信息select * from students where age >18 and age <28;-- select * from students where 18 18 and <28;-- 18岁以上的女性select * from students where age > 18 and gender&＃61;&＃39;女&＃39;;-- or-- 查询编号小于4或没被删除的学生select * from students where id <4 or is_delete &＃61; 0;-- not 取反-- 查询年龄不是18岁的 女性 这个范围内的信息select * from students where not (age &＃61; 18 and gender &＃61; &＃39;女&＃39;); -- 可读性 更高select * from students where age !&＃61; 18 and gender &＃61; &＃39;女&＃39;;-- 年龄不是小于或者等于18 并且是女性select * from students where (not age <&＃61; 18) and gender &＃61; 2;-- select * from students where age > 18 and gender &＃61; 2;-- MySQL数据库中优先级&＃xff1a;not>and>or ## 模糊查询-- like -- % 替换任意多个-- _ 替换1个-- 查询姓名中 以 "小" 开始的名字 select * from students where name like &＃39;小%&＃39;; -- &＃39;小于&＃39; &＃39;小张&＃39; &＃39;小xx&＃39; -- 查询姓名中 有 "小" 所有的名字select * from students where name like &＃39;%小%&＃39;; -- &＃39;小于&＃39; &＃39;小张&＃39; &＃39;小xx&＃39; &＃39;x小x&＃39;-- 查询有2个字的名字select * from students where name like &＃39;__&＃39;; -- xx xx-- 查询至少有2个字的名字select * from students where name like &＃39;__%&＃39;;-- 范围查询-- in (1, 3, 8)表示在一个非连续的范围内-- 查询 年龄为18、34的姓名select name from students where age in (18,34); -- 查询 姓名为居然,老王的select * from students where name in (&＃39;居然&＃39;,&＃39;python&＃39;); -- not in 不非连续的范围之内-- 年龄不是 18、34岁之间的信息select name from students where age not in (18,34); -- between ... and ...表示在一个连续的范围内-- 查询 查询id是3到8的学生select * from students where id between 3 and 8;-- select * from students where id between 3 and 8;-- 查询学生id是3到8的男生select * from students where id between 3 and 8 and gender&＃61;1;select * from students where (id between 3 and 8) and gender&＃61;1; -- 可读性-- not between ... and ...表示不在一个连续的范围内-- 查询 年龄不在在18到34之间的的信息select * from students where age not between 18 and 34;-- select * from students where not age between 18 and 34;-- 空判断-- 判空is null 不能用 &＃61; -- 查询身高为空的信息select * from students where name is null;-- 判非空is not nullselect * from students where name is not null;-- 聚合函数-- 总数-- count-- 查询学生总数select count(*) from students;-- 查询男性有多少人&＃xff0c;女性有多少人select count(*) as 男性人数 from students where gender &＃61; 1;select count(*) as 女性人数 from students where gender &＃61; 2;-- 最大值-- max-- 查询最大的年龄select max(age) from students;-- 查询女性的最大编号select max(id) from students where gender &＃61; 2;-- 查询最大年龄的女性id 隐式的类型转换-- select id,name from students where age &＃61; (select max(age) from students where gender&＃61;2) and gender&＃61;2;-- select max(age) from students where gender&＃61;2;-- 最小值-- min-- 查询未删除的学生最小编号select min(id) from students where is_delete&＃61;0;-- 求和-- sum-- 查询男生的年龄和select sum(age) from students where gender &＃61; 1; -- 求和字段 是varchar 0 如果字段内容是数字的话会进行相加-- 平均值-- avg-- 查询未删除女生的年龄的平均值select avg(age) from students where gender&＃61;2 and is_delete &＃61; 0; -- 默认保留4位小数-- 计算平均年龄 sum(age)/count(*)-- 四舍五入 round(123.23 , 1) 保留1位小数-- 计算所有人的平均年龄&＃xff0c;保留2位小数select round(avg(age),2) from students;select round(sum(age)/count(*),2) from students;-- 计算男性的平均身高 保留2位小数-- 分组-- group by-- select ... from students group by gender;select name from students group by gender; -- 聚合函数-- 真正能区分这个组的字段 -- 按照性别分组,查询所有的性别select gender from students group by gender;-- 计算男生和女生中的人数select count(*) from students group by gender;select gender as 性别,count(*) from students group by gender;select gender as 性别,count(*) from students where gender &＃61; 1;select count(*) from students where gender &＃61; 2;-- 男女同学最大年龄select gender as 性别,max(age) from students group by gender;-- group_concat(...) 查看组内的信息-- 查询同种性别中的姓名select gender as 性别,group_concat(name) from students group by gender;-- 查询组内年龄,姓名 &＃39;a&＃39; &＃43; b &＃43; &＃39;c&＃39; b 变量select gender as 性别,group_concat(&＃39;姓名:&＃39;,name,&＃39;-&＃39;,&＃39;年龄:&＃39;,age) from students group by gender;-- 分组之后的筛选-- having-- 查询男生女生总数大于2select gender,count(*) from students group by gender having count(*)>2;-- 查询男生女生总数大于2的姓名select gender,count(*),group_concat(name) from students group by gender having count(*)>2;-- 查询平均年龄超过18岁的性别&＃xff0c;以及姓名 having avg(age) > 18select gender,group_concat(name),avg(age) from students group by gender having avg(age) > 18;-- 排序-- order by 字段-- asc从小到大排列&＃xff0c;即升序 默认-- desc从大到小排序&＃xff0c;即降序-- 查询年龄在18到26岁之间的男同学&＃xff0c;按照年龄从小到到排序select * from students where (age between 18 and 26) and gender&＃61;1;select * from students where (age between 18 and 26) and gender&＃61;1 order by age;select * from students where (age between 18 and 26) and gender&＃61;1 order by age asc;-- 查询年龄在18到26岁之间的女同学&＃xff0c;身高从高到矮排序select * from students where (age between 18 and 26) and gender&＃61;2 order by id desc;-- order by 多个字段-- 查询年龄在18到28岁之间的男性&＃xff0c;年龄从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序-- 排序的字段 相同了 -- 先按照 年龄排序 如果年龄相同 在按照身高进行排序select * from students where (age between 18 and 28) and gender&＃61;1 order by age desc,hiht asc;-- 按照年龄从小到大、身高从高到矮的排序-- 分页-- limit start, count 起始的位置(从0开始), 个数-- 限制查询出来的数据个数select * from students limit 2;-- 查询前5个数据select * from students limit 5;-- 查询id 6-10&＃xff08;包含&＃xff09;的数据select * from students limit 5,5;-- 制作分页-- 每页显示2个&＃xff0c;第1个页面select * from students limit 0,2;-- 每页显示2个&＃xff0c;第2个页面select * from students limit 2,2;-- 每页显示2个&＃xff0c;第3个页面select * from students limit 4,2;-- 每页显示2个&＃xff0c;第4个页面select * from students limit 6,2; -- limit 第三页的话 limit (第N页-1)*每页显示的个数,每页显示的个数-- select * from students limit 2*(6-1),2; 错误的-- select gender from students limit 0,2 group by gender ; 只能写到最后-- 限制显示的条数-- 连接查询-- inner join ... on-- select ... from 表A inner join 表B;select * from students inner join classes;-- 查询 有能够对应班级的学生以及班级信息select * from students inner join classes on students.cls_id &＃61; classes.id;-- 按照要求显示姓名、班级select students.*,classes.name from students inner join classes on students.cls_id &＃61; classes.id;-- 给数据表起名字select s.*,c.name from students as s inner join classes as c on s.cls_id &＃61; c.id;-- 查询 有能够对应班级的学生以及班级信息&＃xff0c;显示学生的所有信息&＃xff0c;只显示班级名称select s.*,c.name from students as s inner join classes as c on s.cls_id &＃61; c.id;-- 在以上的查询中&＃xff0c;将班级姓名显示在第1列select c.name, s.* from students as s inner join classes as c on s.cls_id &＃61; c.id;-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序select c.name, s.* from students as s inner join classes as c on s.cls_id &＃61; c.id order by c.name;-- 当同一个班级的时候&＃xff0c;按照学生的id进行从小到大排序select c.name, s.* from students as s inner join classes as c on s.cls_id &＃61; c.id order by c.name,s.id asc;-- left join-- 查询每位学生对应的班级信息select * from students left join classes on students.cls_id &＃61; classes.id;select * from classes left join students on students.cls_id &＃61; classes.id;-- 查询没有对应班级信息的学生-- select ... from xxx as s left join xxx as c on..... having .....-- select * from students as s left join classes as c on s.cls_id &＃61; c.id where c.id is null;select * from students as s left join classes as c on s.cls_id &＃61; c.id having c.id is null;-- right join on-- 将数据表名字互换位置&＃xff0c;用left join完成-- 子查询-- 查询最高的男生信息select * from students where higt &＃61; 170;select max(higt) from students where gender &＃61; &＃39;男&＃39;;select * from students where higt &＃61; (select max(higt) from students where gender &＃61; &＃39;男&＃39;);-- select * from students where higt &＃61; 170.4;select * from students where higt &＃61; (select max(higt) from students) and gender&＃61;&＃39;男&＃39;;-- select * from students where higt &＃61; 175.4; select * from students where higt &＃61; (select max(higt) from students where gender&＃61;&＃39;男&＃39;) and gender&＃61;&＃39;男&＃39;;-- 查询出高于平均身高的信息select * from students where higt > (select avg(higt) from students);-- 列级子查询-- 查询学生的班级号能够对应的学生信息select * from students where cls_id in (select id from classes);-- 自关联-- 省级联动 url:http://www.jq22.com/yanshi14715-- 黑龙江省下面的市select * from provinces as p inner join citys as c on p.provinceid &＃61; c.provinceid and p.province &＃61; &＃39;黑龙江省&＃39;; -- 查询所有省份select * from provinces;-- 查询出黑龙江有哪些市select * from cities where provinceid &＃61; 230000;select * from areas as a inner join areas as c on a.id &＃61; c.pid having a.name &＃61; &＃39;黑龙江&＃39;; -- 查询出哈儿市有哪些县城select * from areas as a inner join areas as c on a.id &＃61; c.pid having a.name &＃61; &＃39;哈尔滨&＃39;;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 