最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

MySQL—SQL的基础应用

SQL 基础应用及information_schema

1.SQL(结构化查询语句)介绍

SQL标准:SQL 92   SQL995.7版本后启用SQL_Mode 严格模式

2.SQL作用

SQL 用来管理和操作MySQL内部的对象SQL对象:库:库名,库属性表:表名,表属性,列名,记录,数据类型,列属性和约束

3.SQL语句的类型

DDL:数据定义语言    data definition languageDCL:数据控制语言    data control languageDML:数据操作语言    data manipulation languageDQL:数据查询语言    data query language

4.数据类型
4.1 作用:

 控制数据的规范性,让数据有具体含义,在列上进行控制

4.2.种类
4.2.1 字符串

char(32):定长长度为32的字符串。存储数据时,一次性提供32字符长度的存储空间,存不满,用空格填充。varchar(32):可变长度的字符串类型。存数据时,首先进行字符串长度判断,按需分配存储空间会单独占用一个字节来记录此次的字符长度超过255之后,需要两个字节长度记录字符长度。

问答题:

1. char 和varchar的区别?(1) 255   65535(2) 定长(固定存储空间)   变长(按需)2. char和varchar 如何选择?(1) char类型,固定长度的字符串列,比如手机号,身份证号,银行卡号,性别等(2) varchar类型,不确定长度的字符串,可以使用。3. enum 枚举类型enum('bj','sh','sz','cq','hb',......)数据行较多时,会影响到索引的应用注意:数字类禁止使用enum类型

4.2.2 数字

1. tinyint2. int

4.2.3 时间

1. timestamp2. datetime

4.2.4 二进制

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括binary,varbinary,bit,tinyblob,blob,mediumblob,longblob。

<span style=”display: inline !important;float: none;background-color: transparent;color: rgb(51, 51, 51);font-family: ” break-word;”=”" display”,roboto,noto,arial,”pingfang=”" pro=”" yahei”,”sf=”" microsoft=”">

5. 表属性

存储引擎 :engine =  InnoDB字符集   :charset = utf8mb4utf8    中文  三个字节长度utf8mb4 中文  四个字节长度    才是真正的utf8支持emoji字符排序规则(校对规则) collation针对英文字符串大小写问题

6. 列的属性和约束
6.1 主键:primary key (PK)

说明:唯一非空数字列,整数列,无关列,自增的.聚集索引列?是一种约束,也是一种索引类型,在一张表中只能有一个主键。

6.2 非空:Not NULL

说明:我们建议,对于普通列来讲,尽量设置not null默认值 default :数字列的默认值使用0 ,字符串类型,设置为一个nil null

6.3 唯一:unique

不能重复

6.4 自增 auto_increment

针对数字列,自动生成顺序值

6.5 无符号 unsigned

针对数字列

6.6 注释 comment

描述说明

7. SQL语句应用

7.1 DDL:数据定义语言

7.1.1 库

(1)建库mysql> create database oldguo charset utf8mb4;mysql> show databases;mysql> show create database oldguo;(2)改库mysql> alter database oldguo1 charset utf8mb4;(3)删库mysql> drop database oldguo1;

7.1.2 表
1. 建表建库规范:

1、库名和表名是小写字母为啥?开发和生产平台可能会出现问题。2、不能以数字开头3、不支持-  支持_4、内部函数名不能使用5、名字和业务功能有关(his,jf,yz,oss,erp,crm...)

  2. 建表

create table oldguo (ID int not null primary key AUTO_INCREMENT comment '学号',name varchar(255) not null comment '姓名',age tinyint unsigned not null default 0 comment '年龄',gender enum('m','f','n') NOT null default 'n' comment '性别')charset=utf8mb4 engine=innodb;

  3. 改表

1. 改表结构-- 例子:-- 在上表中添加一个手机号列15801332370.(重点*****)-- alter table oldguo add telnum char(11) not null unique comment '手机号';-- 练习:-- 添加一个状态列ALTER TABLE oldguo ADD state TINYINT  UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态列';-- 查看列的信息DESC  oldguo;-- 删除state列(不代表生产操作)ALTER TABLE oldguo DROP state;-- online-DDL : pt-osc (自己研究下***)-- 在name后添加 qq 列 varchar(255)ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE  COMMENT 'qq' AFTER NAME;-- 练习 在name 之前添加wechat列ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '微信' AFTER ID;-- 在首列上添加 学号列:sid(linux58_00001)ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '学生号' FIRST;-- 修改name数据类型的属性ALTER TABLE oldguo  MODIFY NAME VARCHAR(128)  NOT NULL ;DESC oldguo;-- 将gender 改为 gg 数据类型改为 CHAR 类型ALTER TABLE oldguo  CHANGE gender gg CHAR(1) NOT NULL DEFAULT 'n' ;DESC oldguo;

7.2 DML 数据操作语言

7.2.1 INSERT

--- 最简单的方法插入数据DESC oldguo;INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);--- 最规范的方法插入数据(重点记忆)INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);--- 查看表数据(不代表生产操作)SELECT * FROM oldguo;

7.2.2 UPDATE (注意谨慎操作!!!!)

UPDATE oldguo SET qq='123456' WHERE id=5 ;

7.2.3  DELETE (注意谨慎操作!!!!)

DELETE FROM oldguo WHERE id=5;

7.2.4 生产需求:将一个大表全部数据清空

DELETE FROM oldguo;TRUNCATE TABLE oldguo;DELETE 和 TRUNCATE 区别1. DELETE 逻辑逐行删除,不会降低自增长的起始值。效率很低,碎片较多,会影响到性能2. TRUNCATE ,属于物理删除,将表段中的区进行清空,不会产生碎片。性能较高。

 

7.2.5 生产需求:使用update替代delete,进行伪删除

1. 添加状态列state (0代表存在,1代表删除)ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;2. 使用update模拟deleteDELETE FROM oldguo WHERE id=6;替换为UPDATE oldguo SET state=1 WHERE id=6;SELECT * FROM oldguo ;3. 业务语句修改SELECT * FROM oldguo ;改为SELECT * FROM oldguo WHERE state=0;

8. select
8.1 作用

获取MySQL中的数据行8.2 单独使用select

8.2.1 select @@xxxx;获取参数信息。

mysql> select @@port;mysql> show variables like '%innodb%';

8.2.2 select 函数();

mysql> select database();mysql> select now();mysql> select version();

8.3 SQL92标准的使用语法
8.3.1 select语法执行顺序(单表)

select开始 ---->from子句 --->where子句--->group by子句--->select后执行条件--->having子句 ---->order by ---->limit

8.3.2 FROM

--- 例子:查询city表中的所有数据USE world;SELECT * FROM city;   --->适合表数据行较少,生产中使用较少。SELECT * FROM world.city;--- 例子:查询name和population的所有值SELECT NAME , population FROM city;SELECT NAME , population FROM world.city;
单表查询练习环境:world数据库下表介绍SHOW TABLES FROM world;city(城市):DESC city;id:                自增的无关列,数据行的需要NAME:              城市名字countrycode:      城市所在的国家代号,CHN,USA,JPN。。。。district :         城市的所在的区域,中国是省的意思,美国是洲的意思population:        城市的人口数量说明: 此表是历史数据,仅供学习交流使用。

8.3.3 where

--- WHERE--- 例子:--- WHERE 配合 等值查询(=)--- 查询中国的城市信息SELECT *  FROM   world.city WHERE  countrycode='CHN';--- 查询美国的城市信息SELECT *  FROM   world.city WHERE  countrycode='USA';--- WHERE 配合 不等值(> < >= <=  <>)--- 查询一下世界上人口小于100人的城市SELECT *  FROM   world.city WHERE population<100;--- 查询世界上人口大于10000000的城市
--- WHERE 配合 模糊(LIKE)--- 查询国家代号是C开头的城市SELECT * FROM   world.city WHERE countrycodeLIKE 'C%';--- 注意:like 语句在MySQL中,不要出现%在前面的情况。因为效率很低,不走索引。--- 错误的里SELECT * FROM   world.city WHERE countrycodeLIKE '%C%';--- WHERE 配合 逻辑连接符(AND OR)--- 查询城市人口在1w到2w之间的城市SELECT *FROM cityWHERE population >= 10000AND Population <= 20000;SELECT *FROM cityWHERE populationBETWEEN 10000 AND 20000;--- 查询一下中国或美国的城市信息SELECT *FROM cityWHERE countrycode='CHN' OR countrycode='USA';SELECT *FROM cityWHERE countrycode IN ('CHN','USA');建议改写为,以下语句:SELECT *FROM cityWHERE countrycode='CHN'UNION ALLSELECT *FROM cityWHERE countrycode='USA';

8.3.4 GROUP BY 配合聚合函数应用

常用聚合函数:AVG()   ----平均值COUNT() ----个数SUM()   ----总和MAX()   ----最大值MIN()   ----最小值GROUP_CONCAT()--- 统计每个国家的总人口SELECT   countrycode,SUM(population) FROM city GROUP BY countrycode ;--- 统计每个国家的城市个数1.拿什么站队GROUP BY  countrycode2. 拿什么统计城市id,name3. 统计的是什么?COUNT(id)SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;--- 统计并显示 每个国家的省名字列表SELECT countrycode,GROUP_CONCAT(district)  FROM city GROUP BY countrycode;--- 统计中国每个省的城市名列表SELECT  District,GROUP_CONCAT(NAME)FROM cityWHERE countrycode='CHN'GROUP BY district;--- 统计一下中国,每个省的总人口数SELECT  district ,SUM(population) FROM cityWHERE countrycode='CHN'GROUP BY district--- 统计一下中国,每个省的平均人口SELECT  district ,AVG(population) FROM cityWHERE countrycode='CHN'GROUP BY district

 

8.3.5 HAVING

--- 统计中国,每个省的总人口大于1000w的省及人口数SELECT  district ,SUM(population) FROM cityWHERE countrycode='CHN'GROUP BY districtHAVING  SUM(population)>10000000说明: having后的条件是不走索引的,可以进行一些优化手段处理。

8.3.6 ORDER BY

SELECT  district ,SUM(population) FROM cityWHERE countrycode='CHN'GROUP BY districtORDER BY SUM(population) DESC  ;--- 例子:查询中国所有的城市,并以人口数降序输出SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC;

8.3.7 LIMIT

SELECT *FROM cityWHERE countrycode='CHN'ORDER BY  population DESCLIMIT 5;
SELECT *FROM cityWHERE countrycode='CHN'ORDER BY  population DESCLIMIT 10;
SELECT *FROM cityWHERE countrycode='CHN'ORDER BY  population DESCLIMIT 5,3;
SELECT *FROM cityWHERE countrycode='CHN'ORDER BY  population DESCLIMIT 3 OFFSET 5;说明:LIMIT M,N           跳过M行,显示N行LIMIT X OFFSET Y    跳过Y行,显示X行

8.4 多表连接查询

8.4.1 怎么去多表连接查询

(1)传统的连接:基于where条件1. 找表之间的关系列2. 排列查询条件select name,countrycode from city whrere population<100;PCNselect name,surfacearea from country where code='PCN'--- 人口数量小于100人的城市,所在国家的国土面积(城市名,国家名,国土面积)select city.name,country.name ,country.surfaceareafrom city,countrywhere city.countrycode = country.codeand city.population<100(2)内连接 *****A   BA.x   B.y1. 找表之间的关系列2. 将两表放在join左右3. 将关联条件了放在on后面4. 将所有的查询条件进行罗列select A.m,B.nfrom A  join  Bon A.x=B.ywheregroup byorder bylimit--- 例子:--- 1. 查询人口数量小于100人的国家名,城市名,国土面积SELECT country.name,city.name,country.surfaceareaFROMcity JOIN countryON city.countrycode=country.codeWHERE city.population<100;--- 2. 查询oldguo老师和他教课程名称SELECT teacher.tname ,course.cnameFROM teacherJOIN courseON teacher.tno=course.tnoWHERE teacher.tname='oldguo';
SELECT teacher.`tname` ,course.`cname`FROM teacherJOIN courseON teacher.`tno`=course.`tno`WHERE teacher.`tname`='oldboy';--- 3. 统计一下每门课程的总成绩SELECT course.cname,SUM(sc.score)FROM course JOIN scON course.cno = sc.cnoGROUP BY course.cname;

说明:

-- 5.7 版本会报错的情况,在sqlyog中以下操作没问题-- 但是在命令行上是会报错
mysql>SELECT course.cno,course.cname,SUM(sc.score)                                                                        -> FROM course     -> JOIN sc    -> ON course.cno = sc.cno    -> GROUP BY course.cname;  ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.course.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1. 在select后面出现的列,不是分组条件,并且没有在函数中出现。
2. 如果group by 后是主键列或者是唯一条件列,不会报出错误。如下:

SELECTcourse.cno,course.cname,SUM(sc.score)                                                                    FROM course JOIN scON course.cno = sc.cnoGROUP BY course.cno;
--- 4. 查询oldguo老师教的学生姓名列表SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoWHERE teacher.tname='oldguo'GROUP BY teacher.tname;--- 5. 查询所有老师教的学生姓名列表SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoGROUP BY teacher.tno;
--- 6. 查询oldboy老师教的不及格学生的姓名SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoWHERE teacher.tname='oldboy' AND sc.score<60GROUP BY teacher.tno;
--- 7. 统计zhang3,学习了几门课SELECT student.`sname` ,COUNT(sc.`cno`)FROM studentJOIN scON student.`sno`=sc.`sno`WHERE student.sname='zhang3';
--- 8. 查询zhang3,学习的课程名称有哪些?SELECT student.sname,GROUP_CONCAT(course.`cname`)FROM studentJOIN scON student.`sno`=sc.`sno`JOIN courseON sc.`cno`=course.`cno`WHERE student.`sname`='zhang3';
--- 9. 查询oldguo老师教的学生名.SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoWHERE teacher.tname='oldguo'GROUP BY teacher.tname;--- 10.查询oldguo所教课程的平均分数SELECT teacher.tname ,course.`cname`,AVG(sc.`score`)FROM teacherJOIN courseON teacher.`tno`=course.`tno`JOIN scON course.`cno`=sc.`cno`WHERE teacher.tname='oldguo';
--- 11.每位老师所教课程的平均分,并按平均分排序SELECT teacher.tname ,course.`cname`,AVG(sc.`score`)FROM teacherJOIN courseON teacher.`tno`=course.`tno`JOIN scON course.`cno`=sc.`cno`ORDER BY AVG(sc.`score`);--- 12.查询oldguo所教的不及格的学生姓名SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoWHERE teacher.tname='oldguo' AND sc.score<60GROUP BY teacher.tno;--- 13.查询所有老师所教学生不及格的信息SELECT teacher.tname,GROUP_CONCAT(student.sname)FROM teacherJOIN courseON teacher.tno = course.tnoJOIN scON course.cno = sc.cnoJOIN studentON sc.sno = student.snoWHERE sc.score<60;

转载请注明:XAMPP中文组官网 » MySQL—SQL的基础应用