mysql连表查询基本增删改查

最新推荐文章于 2024-07-30 03:51:08 发布
风水道人
最新推荐文章于 2024-07-30 03:51:08 发布
阅读量552
点赞数
分类专栏： mysql
本文链接：https://blog.csdn.net/jcmj123456/article/details/110442917
版权
mysql 专栏收录该内容
75 篇文章 0 订阅
订阅专栏
#MYSQL的一下基础
启动mysql服务
net start mysql


===================================================
CREATE TABLE user_u_s (
	id INT(4) ZEROFILL,#属性zerofill长度不够0来补齐
	`name` NVARCHAR(20)
);
=====================================================
CREATE TABLE user_tb(
	id INT(5) PRIMARY KEY AUTO_INCREMENT,#auto_increment属性为自增

);
=========================================================

#表 test
DESC test  #desc 表名 查看表结构

SHOW CREATE TABLE test;#展示创建表的语句


======================================================
#创建t01表
CREATE TABLE t01 (
	id INT(5)PRIMARY KEY AUTO_INCREMENT,
	`name` NVARCHAR(10)
)AUTO_INCREMENT =100;#创建表的末尾添加auto_increment = 100 ;自增从100开始
#修改表的自增方式,增长方式为20,20 的增加
ALTER TABLE t01 AUTO_INCREMENT = 20;

#增长方式为跳着增长.为5个5个的增长
SET @@auto_increment = 5;

#清空表，但是自增在原有数据上增长
DELETE FROM t01;
#清空表，自增从1开始 truncate
TRUNCATE
=======================================================

CREATE TABLE t02(
	id INT(4),
	`name` NVARCHAR(10) DEFAULT '男' #default 用来设置默认值的

);
=====================================================
CREATE TABLE t03(
	id INT(2) COMMENT 't03表的主键',#列注释
	`name` NVARCHAR(10) COMMENT '姓名'#列注释
	PRIMARY KEY ('id') #约束可以这样写
)COMMENT = 't03表' ;#表注释

数据库表类型
=============================================
#表类型写在括号后面
creat table 表 (

id int(4),
name varcher(10)

)engine = MyISAM或engine = innoDB;
#engine = MyISAM用于表查询
#engine = innoDB 用于表事物，增删改


=============================================
#修改表名:ALTER TABLE 旧表名 RENAME AS 新表名;
ALTER TABLE test RENAME AS test01;

#添加字段
ALTER TABLE test01 ADD `age1` INT(2);
#修改字段属性
ALTER TABLE test01 MODIFY `name` CHAR(4)
#删除字段
ALTER TABLE test01 DROP age;
#修改字段
ALTER TABLE test01 CHANGE `name` `username` NVARCHAR(10)
#添加主键
ALTER TABLE test01 ADD CONSTRAINT pk_test01 PRIMARY KEY(id);
#删除主键
ALTER TABLE test01 PRIMARY KEY ;
#删除外键
ALTER TABLE test01 DROP FOREIGN KEY fk_test01;
#添加外键
ALTER TABLE test01
	ADD CONSTRAINT  fk_test01
	FOREIGN KEY (id)
	REFERENCES test(id);

创建主外键
===================================================
CREATE TABLE t01(
id INT(5) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10)

);

CREATE TABLE t02(
idd INT(5)  NOT NULL,
`name` NVARCHAR(10)

);

ALTER TABLE t02 ADD CONSTRAINT
fk_t02 FOREIGN KEY (idd) 
REFERENCES t01(id);




===================================================
DML数据操作介绍
insert 
update
delete

================================================
INSERT INTO t01 (`name`,age) VALUES('掌',1);
COMMIT

修改数据
====================================================
update t01 set name = 'zhang' , age =0 ; #不加where条件表里面的数据会全部被修改

UPDATE t01 SET `name`='张' ,age = 4 WHERE id= 3;

mysql自带的一些函数
UPDATE t01 SET age = age +10;

concat字符追加、、、在name列字段前添加hao的字符
UPDATE t01 SET `name` = CONCAT('hao',`name`);


where 条件拼接
=====================================================
and  or  < > !=或<>  >=  <= between(一般不用 效率低)

UPDATE t01 SET `name` ='新值' WHERE id>1 AND id<4; 

UPDATE t01 SET `name` ='hehe' WHERE id BETWEEN 3 AND 5; 



事物提交方法
===========================================================
1.start transction 开始一个事物，标记事物的起点。
2.commit 提交一个事物给数据库。
3.rollback 将事物回滚，数据回到本次事物的初始状态。
4.set autocommit = 1； 还原mysql数据库的自动提交。
5.set autocommit = 0； 关闭mysql数据库的自动提交。

演示银行转账
============================================================
CREATE TABLE `account`(
`id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` NVARCHAR(30) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL #整数位9位，小数位2位。

)ENGINE=INNODB;

INSERT INTO account (`name`,`cash`) VALUES('a',2000);
INSERT INTO account (`name`,`acsh`) VALUES('b',3000);

SET autocommit=0;
START TRANSACTION;

UPDATE account SET cash=cash-600 WHERE `name`= 'a';
UPDATE account SET cash=cash_600 WHERE `name`='b'
sql语句。。。
SET autocommit =1;
#commit 
#rollback


使用DQL查询
============================================================
`grade` 年级表  
字段
`GradeID`（PRIMARY KEY）,`GradeName`
PRIMARY KEY (`GradeID`)

`result` 成绩表
字段
`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`
KEY `SubjectNo` (`SubjectNo`)

`subject` 课程表
字段
`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`
PRIMARY KEY (`SubjectNo`)

student 学生表
字段
`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`
PRIMARY KEY (`StudentNo`)
==========================================================
SELECT * FROM student ;# 用*查询效率低，一般不用

SELECT Sex,Phone FROM student; #只查看Sex,Phone 这两个字段
SELECT s.`Address`,s.`Email` FROM student AS s ; 使用别名查询其中 AS 可以省略（表起别名）

SELECT s.`Address`,s.`Email` FROM student s ; 使用别名查询（表起别名）

SELECT s.`Address` AS 地址,s.`Email` AS 邮箱 FROM student s ; 字段使用别名查询

SELECT COUNT(studentno) AS 总数 FROM student; 记录总数的函数（count）

使用distinct
=====================================================================
使用distinct去除重复，跟在select后面 后面是需要去除重复的字段
语法：select distinct 字段1，字段2，字段N from 表
SELECT DISTINCT r.`SubjectNo`FROM result AS r;

===============================================================
concat 查询出的列前加：学生 并跟列起的别名：学生姓名
SELECT CONCAT('学生：',studentName) 学生姓名 FROM student ;

SELECT VERSION();查询版本号

SELECT s.`ClassHour` AS 旧课时,s.`ClassHour`+10 新的课时 FROM `subject` AS s;

SELECT s.subjectName AS 课程名称,s.`ClassHour` AS 总课时, s.`ClassHour`/10 AS 平均课时 FROM `subject` AS s;

where条件语句
==========================================================
逻辑操作符
and或&& 逻辑与
or或|| 逻辑或
xor或^ 逻辑异域
not或！逻辑非
SELECT r.`StudentResult` AS 考试成绩 FROM result AS r 
	WHERE r.`StudentResult`>=70 AND r.`StudentResult`<=90;


=========================================================
#查询StudentName 为空的字段 而不是StudentName=null
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` IS NULL; 
#查询StudentName字段 ‘李 ’开头的
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李%';
#查询StudentName字段 ‘文’ 结尾的
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '%文';
#查询StudentName字段 ‘李 ’开头的后面匹配 任意 一个 字 一个下划线代表一个字
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '李_';
#查询StudentName字段 开头的匹配任意一个 字 末尾以梅字结尾 一个下划线代表一个字
SELECT s.`StudentName` FROM student AS s WHERE s.`StudentName` LIKE '_梅';
#查询StudentResult字段 成绩等于 79,90,80的人
SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` IN (79,90,80);
#转义字符 关键字 escape 来定义转义字符
SELECT r.`StudentResult` FROM result AS r WHERE r.`StudentResult` LIKE '%:%%' ESCAPE ':';


联合查询(内连接)inner join
===========================================================
语法：select 字段1....字段n from 表1 inner join 表2 on 表1条件=表2条件；
（inner）可以省略
#两张表联合查询
SELECT s.`StudentName`,r.`StudentResult` 
	FROM student AS s INNER JOIN result AS r ON s.`StudentNo`=r.`StudentNo`


#三张表联合查询
SELECT s.`StudentName`,r.`StudentResult` ,u.`SubjectName`
	FROM student AS s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
		INNER JOIN `subject` AS u ON r.`SubjectNo`=u.`SubjectNo`;
#查询成绩小于70的学生
SELECT s.`StudentName`,r.`StudentResult` 
	FROM student AS s INNER JOIN result AS r ON r.`StudentNo`=s.`StudentNo`
		WHERE r.`StudentResult`<=70;

left join 		
=================================================================
#右表中没有返回null
SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
	FROM student AS s LEFT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;


right join 		
=================================================================
#左表中没有返回null	
SELECT DISTINCT s.`StudentName` AS 学生姓名：,r.`StudentResult`AS 成绩
	FROM student AS s RIGHT JOIN result AS r ON s.`StudentNo`=r.`StudentNo`;	

等值与非等值（相当于 inner join）	
=============================================================
语法：select 字段1，字段2 from 表1，表2 where 字段1=字段2；
SELECT a.`StudentName`,r.`StudentResult` FROM student AS a ,result AS r 
	WHERE a.`StudentNo`=r.`StudentNo` AND r.`StudentResult`>=80	

自身连接
=================================================================
1.类似父子标题的关系
CREATE TABLE IF NOT EXISTS  category(
	categoryId INT(10)  UNSIGNED  NOT NULL  AUTO_INCREMENT,
	pid  INT(10) NOT NULL,
	categoryName   VARCHAR(32) NOT NULL,
	PRIMARY KEY(categoryId)
);

INSERT INTO  category VALUES
(2,1,"美术设计"),
(3,1,"软件开发"),
(4,3,"数据库基础"),
(5,2,"Photoshop基础"),
(6,2,"色彩搭配学"),
(7,3,"PHP基础"),
(8,3,"一起学JAVA");

SELECT c1.`categoryName`,c2.`categoryName` FROM category AS c1 INNER JOIN category AS c2 
	ON c1.`categoryId` = c2.`pid`

==================================================================
#order by 排序 desc （降序） asc（升序 默认）

SELECT r.studentResult AS 成绩 FROM result AS r ORDER BY r.studentResult DESC;

SELECT s.`SubjectName`,r.`StudentResult` 
	FROM result AS r INNER JOIN `subject` AS s ON s.`SubjectNo`=s.`SubjectNo`
	WHERE s.`SubjectName`='数据库结构-1' ORDER BY r.`StudentResult` DESC;

limit分页
========================================================
#查询总素
SELECT COUNT(s.`StudentNo`)AS 总数 FROM student AS s ;
#分页关键字 limit 
#语法：limit m,n
	m 从第几条开始取（默认0）
	n 取出的 最大 数目
	
SELECT s.studentName,s.studentNo FROM student AS s ORDER BY s.studentNo LIMIT 0,3;

子查询
=======================================================
在查询语句中有嵌套了一个where查询




#聚合函数
==========================================================
#聚合函数
COUNT() 总记录数
SUM()求和
AVG()平均数
MAX()最大值
MIN()最小值

SELECT COUNT(r.studentNo) '总条数',
AVG(r.studentResult) '平均数',
 MAX(r.studentResult) '最高分', 
 MIN(r.studentResult) '最低分'
FROM result AS r ;

字符函数
===========================================================

数学函数
=============================================================

日期函数
==============================================================
SELECT NOW(); 获得系统当前时间。


分组group by 一般与having结合使用（注意having的使用位置）
=============================================================
SELECT COUNT(r.studentNo) '总条数',
AVG(r.studentResult) '平均数',
 MAX(r.studentResult) '最高分', 
 MIN(r.studentResult) '最低分',
 s.subjectno AS 姓名
FROM result AS r INNER JOIN `subject` AS s ON r.subjectno=s.subjectno
GROUP BY  r.subjectno HAVING AVG(r.studentResult)>=80;



索引
==========================================================================
作用：
提高查询速度
确保数据唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索可以显著减少分组和排序时间
全文检索字段进行收索优化

# 主键索引primary key 
（只能有一个）
#唯一索引 unique  
（避免重复 ，可以有多个）

#常规索引index/key
注意：1.用于快速定位
	  2.加在查找条件的列
	  3.不宜添加太多常规索引，影响数据插入，修改，删除
#全文索引fulltext
注意：1.fulltext索引只能用于MyISASM表类型；
      2.只能用于char ，varchar，text 数据类型列；
	  3.适合大型数据集



添加 primary key 默认这一列添加索引
例：
	CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
	或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）

);
索引2（常规，唯一）创建
=========================================================================
	CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
	或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
`sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
	或 INDEX index_sex ('sex')
	或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前）	
);
#创建表完成后创建索引
ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');

全文索引创建
=========================================================================
CREATE TABLE t03(
`name` VARCHAR(10) FULLTEXT，
`sex` text(10)
)ENGINE = MYISAM;

#或
ALTER TABLE t03 ADD FULLTEXT ('sex');

ALTER TABLE t03 ADD `sex` TEXT(100)

注意：
	select * from t03 where match(列名) against（‘love’）；
		1.添加索引后统计出现的（love）次数；
		2.使用关键字against

管理索引
==============================================================

#创建表索引
ALTER TABLE table_name ADD 索引类型 （‘列名’）；
#删除索引
1. DROP INDEX 索引名 ON 表名
2. ALTER TABLE 表名 DROP INDEX 索引名
3. ALTER TABLE 表名 DROP PRIMARY KEY;

#查看索引
SHOW INDEX  FROM 表名

mysql数据备份（DOS窗口下）
================================================================
备份方法：
	1.使用命令
	2.使用管理工具sqlyog
	3.直接拷贝数据文件和相关配置文件

mysql dump客户端
语法：
mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
例：
mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车

mysql恢复
==============================================================
1.是mysql下
	关键字 source
		source /path/sql.sql
2.DOS下
	mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;



触发器
=============================================================


创建视图
===============================================================
view_lottert:视图的名
db_lottery：数据库中的表名
查询视图跟普通查询一样
视图一般做查询用
CREATE VIEW view_lottert AS SELECT * FROM db_lottery LIMIT 0,3




复制表结构
=============================================================


存储过程(看做函数或sql的集合)
================================================================



索引
==========================================================================
作用：
提高查询速度
确保数据唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索可以显著减少分组和排序时间
全文检索字段进行收索优化

# 主键索引primary key 
（只能有一个）
#唯一索引 unique  
（避免重复 ，可以有多个）

#常规索引index/key
注意：1.用于快速定位
	  2.加在查找条件的列
	  3.不宜添加太多常规索引，影响数据插入，修改，删除
#全文索引fulltext
注意：1.fulltext索引只能用于MyISASM表类型；
      2.只能用于char ，varchar，text 数据类型列；
	  3.适合大型数据集



添加 primary key 默认这一列添加索引
例：
	CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
	或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）

);
索引2（常规，唯一）创建
=========================================================================
	CREATE TABLE t03(
id INT(2) AUTO_INCREMENT PRIMARY KEY NOT NULL,
`name` NVARCHAR(10) NOT NULL unique（唯一索引） 
	或 UNIQUE KEY `nameID` (`name`)一般跟索引起名，不起名，默认用的列名。（一般用这种，方便删除）
`sex` NVARCHAR(2) INDEX/KEY(常规索引)一般常作为条件的时候添加索引
	或 INDEX index_sex ('sex')
	或 KEY key_sex('sex1','sex2')一次添加两个（谁的信息量小谁在前）	
);
#创建表完成后创建索引
ALTER TABLE 表名 ADD INDEX inde_sex('sex1','sex2');

全文索引创建
=========================================================================
CREATE TABLE t03(
`name` VARCHAR(10) FULLTEXT，
`sex` text(10)
)ENGINE = MYISAM;

#或
ALTER TABLE t03 ADD FULLTEXT ('sex');

ALTER TABLE t03 ADD `sex` TEXT(100)

注意：
	select * from t03 where match(列名) against（‘love’）；
		1.添加索引后统计出现的（love）次数；
		2.使用关键字against

管理索引
==============================================================

#创建表索引
ALTER TABLE table_name ADD 索引类型 （‘列名’）；
#删除索引
1. DROP INDEX 索引名 ON 表名
2. ALTER TABLE 表名 DROP INDEX 索引名
3. ALTER TABLE 表名 DROP PRIMARY KEY;

#查看索引
SHOW INDEX  FROM 表名

mysql数据备份（DOS窗口下）
================================================================
备份方法：
	1.使用命令
	2.使用管理工具sqlyog
	3.直接拷贝数据文件和相关配置文件

mysql dump客户端
语法：
mysqldump -h主机名 -u用户名 -p数据库名 [表1 表2 表n] >path/filename.sql
例：
mysqldump -uroot -pt04 student（数据库名） student（表名） > f:/sql.sql  回车

mysql恢复
==============================================================
1.是mysql下
	关键字 source
		source /path/sql.sql
2.DOS下
	mysql -u用户名 -p密码 数据库名 表名 </path/sql.sql;