# mysql

select distinct 字段1，字段2，。。。 from 表名 where 分組之前的過濾條件 group by 分組條件 having 分組之后過濾條件 order by 排序字段1 asc，排序字段2 desc limit 5,5as語法中給某個(gè)查詢結(jié)果起別名的時(shí)候需要把查詢語句中的分號(hào)去除 (select name,salary*12 as '年薪' from emp) as t1;

# 一個(gè)字段展示用戶名和年齡
select concat(name,':',age) as info from emp;

# 字段為NAME和AGE，值為‘NAME:jason’,'AGE:18'
select concat("NAME:",name) as NAME,concat("AGE:",age) as AGE from emp;

# 如果拼接的符號(hào)是統(tǒng)一的可以用
select concat_ws(':',name,age,sex) as info from emp;

# 1.子查詢相關(guān)
# 查詢平均年輕在25歲以上的部門名

select name from dep where id in (select dep_id from emp group by dep_id having avg(age)>25);select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25;

exist(了解)

EXISTS關(guān)字鍵字表示存在。在使用EXISTS關(guān)鍵字時(shí)，內(nèi)層查詢語句不返回查詢的記錄， 而是返回一個(gè)真假值，True或False。 當(dāng)返回True時(shí)，外層查詢語句將進(jìn)行查詢 當(dāng)返回值為False時(shí)，外層查詢語句不進(jìn)行查詢。 select * from employee where exists (select id from department where id > 3);select * from employee where exists (select id from department where id > 250);

?

1. Navicat使用

#1. 測(cè)試+鏈接數(shù)據(jù)庫(kù) #2. 新建庫(kù) #3. 新建表，新增字段+類型+約束 #4. 設(shè)計(jì)表：外鍵 #5. 新建查詢 #6. 建立表模型#注意： 批量加注釋：ctrl+？鍵 批量去注釋：ctrl+shift+？鍵

# 練習(xí)題

導(dǎo)出的sql語句代碼

?

2. 數(shù)據(jù)導(dǎo)入：

Navicat Premium Data TransferSource Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexamTarget Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8Date: 10/21/2016 06:46:46 AM */SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT;-- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術(shù)', '2'); COMMIT;-- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT;-- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四'); COMMIT;-- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱云海老師'), ('5', '李杰老師'); COMMIT;SET FOREIGN_KEY_CHECKS = 1; View Code

拷貝上述代碼，新建一個(gè).sql文件，保存到桌面

打開navicat新建數(shù)據(jù)庫(kù)day41，選中新建的數(shù)據(jù)庫(kù)鼠標(biāo)右鍵選擇運(yùn)行SQL文件

彈出文件框，選中剛剛保存到桌面的.sql文件即可

**快速建表**

#準(zhǔn)備表、記錄 >>> 命令行 mysql> create database db1; mysql> use db1; mysql> source /root/init.sql# navicat建表

?

# 練習(xí)題

1、查詢所有的課程的名稱以及對(duì)應(yīng)的任課老師姓名 4、查詢平均成績(jī)大于八十分的同學(xué)的姓名和平均成績(jī) 7、 查詢沒有報(bào)李平老師課的學(xué)生姓名 8、 查詢沒有同時(shí)選修物理課程和體育課程的學(xué)生姓名 9、 查詢掛科超過兩門(包括兩門)的學(xué)生姓名和班級(jí)

# 參考答案

#1、查詢所有的課程的名稱以及對(duì)應(yīng)的任課老師姓名 SELECT course.cname, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid;#4、查詢平均成績(jī)大于八十分的同學(xué)的姓名和平均成績(jī) SELECT student.sname, t1.avg_num FROM student INNER JOIN ( SELECT student_id, avg(num) AS avg_num FROM score GROUP BY student_id HAVING avg(num) > 80 ) AS t1 ON student.sid = t1.student_id;#7、 查詢沒有報(bào)李平老師課的學(xué)生姓名(找出報(bào)名李平老師課程的學(xué)生，然后取反就可以) SELECT student.sname FROM student WHERE sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT course.cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老師' ) );#8、 查詢沒有同時(shí)選修物理課程和體育課程的學(xué)生姓名(沒有同時(shí)選修指的是選修了一門的，思路是得到物理+體育課程的學(xué)生信息表，然后基于學(xué)生分組，統(tǒng)計(jì)count(課程)=1) SELECT student.sname FROM student WHERE sid IN ( SELECT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '物理' OR cname = '體育' ) GROUP BY student_id HAVING COUNT(course_id) = 1 );# 9、 查詢掛科超過兩門(包括兩門)的學(xué)生姓名和班級(jí) select student.sname,class.caption from class INNER JOIN student on class.cid = student.class_id WHERE student.sid in (select student_id from score where num < 60 GROUP BY student_id HAVING COUNT(course_id) >=2) ; View Code

?

3. pymysql模塊

# 1.安裝:pip3 insatll pymysql# 2.代碼鏈接 import pymysql #鏈接 conn=pymysql.connect( host='localhost', user='root', password='123', database='egon', charset='utf8') #游標(biāo) cursor=conn.cursor() #執(zhí)行完畢返回的結(jié)果集默認(rèn)以元組顯示 #cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) # 以字典的方式顯示數(shù)據(jù)# 3.pymysql操作數(shù)據(jù)庫(kù) #執(zhí)行sql語句 user = input(">>>:").strip() pwd = input(">>>:").strip() sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引號(hào) rows=cursor.execute(sql) #執(zhí)行sql語句，返回sql查詢成功的記錄數(shù)目 # 獲取真實(shí)數(shù)據(jù)cursor.fetchone(),cursor.fetchall(),cursor.fetchmany(),類似管道取值，獲取一條，所有，多條 cursor.scroll(1,'relative') # 相對(duì)移動(dòng) cursor.scroll(3,'absolute') # 絕對(duì)移動(dòng) cursor.close() conn.close()

?

# sql注入問題

# 不要手動(dòng)去拼接查詢的sql語句 username = input(">>>:").strip() password = input(">>>:").strip() sql = "select * from user where username='%s' and password='%s'"%(username,password)# 用戶名正確 username >>>: jason' -- jjsakfjjdkjjkjs # 用戶名密碼都不對(duì)的情況 username >>>: xxx' or 1=1 --asdjkdklqwjdjkjasdljad password >>>: ''

?

#### 增刪改

# 增 sql = "insert into user(username,password) values(%s,%s)" rows = cursor.excute(sql,('jason','123'))# 修改 sql = "update user set username='jasonDSB' where id=1" rows = cursor.excute(sql)

?

增和改單單執(zhí)行excute并不會(huì)真正影響到數(shù)據(jù)，需要再執(zhí)行conn.commit()才可以完成真正的增改

# 一次插入多行記錄
res = cursor,excutemany(sql,[(),(),()]

?

轉(zhuǎn)載于:https://www.cnblogs.com/Ryan-Yuan/p/11394991.html

總結(jié)

以上是生活随笔為你收集整理的day38-数据库应用软件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。