数据库
库相关操作
1、建库命令:
CREATE database name(数据库名称);
2、展示库命令:SHOW databases;
SHOW databases;
3、删除库命令:DROP database name(数据库名称)
DROP database mytest;
数据库常用数据类型(国际标准)
1、double:浮点型 double(5,2)---->表示共 5 位数据,2位为小数;
2、decimal:浮点型 不丢失经度;
3、char:固长字符串,最大长度为 255字节,不足 255字节 长度 自动填充到 255字节 长度;
4、varchar:可变长度字符串,最大长度为 65535 字节,每一个该类型数据都最少有一位字节进行长度标记;
5、text(clod —>MySQL的字符型):字符串类型;
6、blog:字节类型;
7、data:日期类型-----> 只有 年 月 日;
8、time:时间类型---->只有 时 分 秒;
9、timestamp:时间戳;
数据表相关操作
1、创建表:CREATE table name(表名)(列名 列类型,列名 列类型…);
CREATE table tb_mytest(tid int ,tname varchar(50));
2、展示表:SHOW tables;
SHOW tables;
3、展示表结构:DESC name(表名);
DESC tb_mytest;
4、删除表 DROP table name(表名);
DROP table tb_mytest;
5、修改表操作
1)添加列: ALTER table name(表名) ADD(列名 列类型,列名 列类型 …)
ALTER table my_mytest ADD (tage int,tfrom varchar(50));
2)删除列:ALTER table name(列名) drop 列名;
ALTER table tb_mytest DROP tfrom;
3)修改列类型:ALTER table name(表名)modity 列名 新的列类型;
ALTER table tb_mytest modity tage varchar(20);
4)修改表名: ALTER table name(表名)rename to 新表名;
ALTER table tb_mytest rename to tb_stu;
5)修改列名:ALTER table name(表名)change 列名 新列名 列类型;
ALTER table tb_stu change tid sid int;
数据相关
1、添加数据
1)指定列增加数据:INSERT INTO name(表名)(列名,列名,列名…)values (数据、数据、数据);
INSERT INTO tb_stu (sid,tname,tage) values (1,'han','18');
2)所有列添加数据:INSERT INTO name(表名)values (数据,数据,,数据);
INSERT INTO tb_stu values (2,'xiao','20');
2、删除数据
1)删除全部数据:DELETE FROM name(表名);
DELETE FROM tb_stu;
2)删除指定数据:DELETE FROM name(表名)WHERE 列名=数值;
DELETE FROM name(表名)WHERE 列名=数值;
3、修改数据
1)修改全部数据(整列数值):UPDATE name(表名)set 列名=数据,列名=数据…;
UPDATE tb_stu SET sid=1;
2)条件修改:UPDATE name(表名)SET 列名=数值,列名=数值… WHERE (筛选条件)列名=数值;
UPDATE tb_stu SET sid=2 WHERE tname='han';
3)条件运算符
=、!=、<>(不等于)、>、<、>=、<=、between…and…(在…和…之间)、and(并且)、in (…)(集合内的 列)、is null(空的)、is not null(不是空)、or(或者);
4、查询数据:
1)查询全表:SELECT * FROM name(表名);
SELECT * FROM tb_stu;
2)查询单列:SELECT name(列名)FROM name(表名);
SELECT tname FROM tb_stu;
3)去除重复行:SELECT DISTINCT name(列名)FROM name(表名);
SELECT DISTINCT sid FROM tb_stu;
4)表后面追加 表内一行数据:SELECT *, name(列名)FROM name(表名);
SELECT * , tname FROM tb_stu;
5) 列运算:
(1)某列 + - x ÷ 某个值并追加到表后:SELECT name(列名)+100 FROM name(表名);
SELECT * sid + 10 FROM tb_stu;
- 注意 :
- 字符型数据直接 进行四则运算 会 将原数据当做 0来处理;
- 列数据为 null 的数据 进行四则运算 结果为 null;
(2)当列数值为 null 时进行四则运算:
SELECT ifnull(name(列名),数值 )+(四则表达式)num (数值) from name(表名);
SELECT IFNULL(sid,0) +10 FROM tb_stu;
(2)字符串拼接:
SELECT CONCAT (‘自定义字符串’,列名,‘自定义字符串’ ,列名)
FROM name(表名);
SELECT CONCAT('find name ',tname,'ok')
FROM tb_stu
(3)给列起别名:SELECT name(列名) AS nickname(别名),name nickname(别名)FROM name(表名);
SELECT tname AS 姓名 , tage 年龄 FROM tb_stu;
5)精确查询:SELECT * FROM name(表名) WHERE 列名=数值;
SELECT * FROM tb_stu WHERE tname='cao';
6)模糊查询
(1)匹配单个字符
SELECT *FROM name(表名) WHERE 列名 LIKE '某 _ _ '; 一个 “”表示一个模糊字符;
SELECT *FROM tb_stu WHERE tname LIKE 'c_ _';
(2) 匹配多个字符
SELECT * FROM name(表名) WHERE 列名 LIKE ‘某%’; % 表示无约束的字符个数;
SELECT *FROM tb_stu WHERE tname LIKE 'c%';
7)排序
(1)升序排序:SELECT *FROM name(表名)ORDER BY name(列名)ASC;
SELECT *FROM tb_stu ORDER BY sid ASC;
(2)降序排序:SELECT FROM name(表名) ORDER BY name (列名)DESC;
SELECT *FROM tb_stu ORDER BY name(列名)DESC;
8) 纵向查询----聚合函数
(1)查询数据总数:SELECT COUNT(*)FROM name(表名);
SELECT COUNT(*)FROM tb_stu;
(2)查询某列总数:SELECT COUNT(name(列数))FROM name(表名);
SELECT COUNT(sid) FROM tb_stu;
注:该查询会跳过 改行 数据 为 null 的行;
(3)查询某列数值总和:SELECT SUM (name (列名))FROM name(表名);
SELECT SUM(sid) FROM tb_stu;
(4)查询某列最大值:SELECT MAX(name(列名))FROM name(表名);
SELECT MAX(sid) FROM tb_stu;
(5)查询某列最小值:SELECT MIN(name(name (列名)) FROM name(表名);
SELECT MIN(sid) FROM tb_stu;
(6)查询某列平均值:SELECT AVG(name (列名))FROM name(表名);
SELECT AVG tage FROM tb_stu;
9)分组查询
(1)普通分组查询:SELECT name(列名),COUNT()(任意聚合函数) FROM name(表名)GROUP BY name(列名,与之前出现的列名相同);
SELECT tage,MAX(sid)FROM tb_stu GROUP BY tage;
(2)分组前进行筛选查询:SELECT name(列名),COUNT()(任意聚合函数)FROM name(表名)WHERE name(列名)>(任意条件运算符)GROUP BY name(列名);
SELECT tage,min(sid)FROM tb_stu WHERE tage>20 GROUP BY tage
(3) 分组后进行筛选查询:SELECT name(列名),COUNT ()(任何聚合函数)FROM name(表名)WHERE name(列名) >(任意调价运算符)num(条件数值) GROUP BY name(列名)HAVING COUNT()(任意聚合函数)>2;
SELECT sid , COUNT (*) FROM tb_stu WHERE tage>20 GROUP BY sid HAVING COUNT(*)=2;
10)约束
(1)主键约束(唯一标识):非空、唯一、被引用;
创建主键约束:name(列名)int (数据类型)PRIMARY KEY;
CREATE TABLE tb_stu(sid INT PRIMARY KEY,sname VARCHAR(50),sage INT,sgrade INT);
表存在 指定主键约束:ALTER table name(表名)ADD PRIMARY KEY(name(列名)) ;
ALTER table tb_teacher ADD PRIMARY KEY(tid);
删除 列主键约束:ALTER table name(表名)DROP PRIMARY KEY;
ALTER tabble tb_teacher DROP PRIMARY KEY
(2) 添加非空约束:name(列名)int not null;
CREATE TABLE tb_teacher(tid INT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(50),tage INT,tpay INT NOT NULL);
(3) 唯一约束:name(列名)int unique;
CREATE TABLE tb_teacher(tid INT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(50),tage INT UNIQUE,tpay INT NOT NULL);
五、多表相关操作
1、多表概念
1)外键必须是另一张表的主键(不太准确),外键要引用主键;
2)外键可以重复;
3)外键可以为 null;
4)一张表可以包含多个外键。
2、外键约束 一对多
1)建表时 进行关联(注意:先有从表 后有主表)
name (主表从表相同的列名)int(主表从表相同的类型),
CONSTRAINT name(名字)FOREIGN KEY (name(外键名称)) REFERENCES name(从表名称)(name(从表关联键));
主表 CREATE TABLE tb_stu(sid INT PRIMARY KEY,sname VARCHAR(50),sage INT,sgrade INT,tid INT, CONSTRAINT fk_stu_teacher FOREIGN KEY (tid) REFERENCES tb_teacher (tid) );
从表:CREATE TABLE tb_teacher(tid INT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(50),tage INT UNIQUE,tpay INT NOT NULL);
2)已知表进行关联
ALTER table name(主表名称) ADD CONSTRAINT name(名字)FOREIGN KEY(name(外键名称))REFERENCES name(从表名称)(name (从表关联列));
ALTER TABLE tb_stu ADD CONSTRAINT fk_stu_teacher FOREIGN KEY (tid) REFERENCES tb_teacher (tid);
3、外键约束 一对一
CONSTRAINT name(关联名称)FOREIGN KEY (name(主表主键列)) REFERENCES name(从表名称)(name(从表主键列));
CONSTRAINT fk_stu_teacher FOREIGN KEY (sid) REFERENCES tb_teacher(tid);
4、外键约束 多对多
原理:一共三张表 A与B只存在主键、C表只存在 被关联的键,一一对应;
5、多表查询
1)基础知识
多表查询分为:合并结果集、连接查询、子查询;
2)合并结果集 前提 结果集表结构相同
(1)合并结果集 去重---- union 关键字
SELECT * FROM name(表1)UNION SELECT *FROM name(表2);
SELECT *FROM tb_noo UNION SELECT *FROM tb_not;
(2)展示所有数据 ---- 关键字 union all
SELECT * FROM name(表1)UNION ALL SELECT *FROM name(表2);
SELECT *FROM tb_noo
UNION ALL
SELECT *FROM tb_not;
3)连接查询
(1)概念:一次查询多表、内连接(MySQL独有方式、标准、自然连接)、外连接(左外连接、右外连接)、全连接(MySQL不支持,可模拟)
(2)内连接— 方言
去掉笛卡尔积 :SELECT *FROM name(表1),name(表2)WHERE name(表1).l列名=name(表2). 列名;
SELECT *FROM tb_stu ,tb_school WHERE tb_stu.scid=tb_school.scid;
4)跨表查询
SELECT name(主表名称).列名 name(从表名称).列名 FROM name(主表名称),name(从表名称) WHERE name(主表名称).列名(外键)=name(从表名称).列名(关联键);
SELECT tb_stu.sname,tb_school.scname FROM tb_stu ,tb_school WHERE tb_stu.scid=tb_school.scid;
5)标准内连接
SELECT * FROM name(主表名称)INNER JOIN name(从表名称)ON name(主表名称).列名(主表外键)=name(从表名称).列名(从表键);
SELECT *FROM tb_stu INNER JOIN tb_school ON tb_stu.scid=tb_school.scid;
注:按照 sid 进行排序:SELECT *FROM tb_stu INNER JOIN tb_school ON tb_stu.scid=tb_school.scid ORDER BY sid ASC;
6)自然连接
SELECT *FROM name(主表名称)NATURAL JOIN name(从表名称);
SELECT *FROM tb_stu NATURAL JOIN tb_school;
7)左外连接(左表为主)
SELECT *FROM name(主表名称)LEFT OUTER JONIN name(从表名称)ON name(主表名称).列名=name(从表名称).列名;
SELECT *FROM tb_stu LEFT OUTER JOIN tb_school ON tb_stu.scid=tb_school.scid;
8)右外连接(右表为主)
SELECT *FROM name(主表名称)RIGHT OUTER JOIN name(从表名称)ON name(主表名称).列名=name(从表名称).列名;
SELECT *FROM tb_stu RIGHT OUTER JOIN tb_school ON tb_stu.scid=tb_school.scid;
6、子查询 查询语句内嵌套查询语句
1)从查询表格内再次查询:
SELECT *FROM tb_stu WHERE scid= (SELECT MAX(scid) FROM tb_school);
SELECT s.scname FROM (SELECT *FROM tb_school WHERE scid>2) s;
2)单行单列查询:SELECT *FROM name(表名)WHERE 列名 条件限定符(>、<、=、!=、>=、<=等)(另一张表的单行单列查询结果);
SELECT* FROM tb_stu s WHERE sid>(SELECT scid FROM tb_school WHERE scid=2);
3)多行单列
SELECT *FROM name(表名) 别名 WHERE 列名 条件限定符 限定符合(in(集合)、all(所有)、any(任一))(另一张表的单行单列查询结果);
SELECT* FROM tb_stu s WHERE sid < ALL(SELECT scid FROM tb_school WHERE scid=2);