mysql连表查询基本增删改查
【摘要】 mysql连表查询基本增删改查
#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;
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)