[root@localhost /]# yum install mysql 安装mysql客户端 [root@localhost /]# yum install mysql-server 安装mysql服务端 [root@localhost /]# rpm -qa|grep -i mysql 查看有没有安装好mysql [root@localhost /]# service mysqld start 启动mysql [root@localhost /]# service mysqld stop 关闭数据库 [root@localhost /]# service mysqld restart 重启数据库 [root@localhost /]# service mysqld status 查看mysql状态mysqld后面这个d代表一个守护进程daemo [root@localhost /]# mysql -uroot -p 进入mysql数据库 mysql>exit； 退出数据库（也可以按ctrl+c） [root@localhost /]# mysqladmin -uroot password '123456' 修改mysql数据库密码为123456 [root@localhost /]# mysql -uroot -p123456 进入mysql数据库(密码为123456） mysql> show databases; 查看mysql数据库所有库 mysql> create database dcs31; 创建一个dcs31库 mysql> drop database dcs31; 删除dcs31库 mysql> use dcs31; 进入dcs31库 mysql> show tables; 查看当前库的所有表 mysql> select database(); 查看当前所在哪个库里面 mysql> create table duoceshi31(id int(20) primary key auto_increment,score float(20,2) not null,name varchar(20),phone bigint(20) default 13388887777,time date); ====》创建一个duoceshi31表及表的结构和对应表字段的类型 mysql> desc duoceshi31; 查看duoceshi31表结构 +-------+-------------+------+-----+-------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+-------------+----------------+ | id | int(20) | NO | PRI | NULL | auto_increment | | score | float(20,2) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | bigint(20) | YES | | 13388887777 | | | time | date | YES | | NULL | | +-------+-------------+------+-----+-------------+----------------+ field：字段 type: 类型 null: 是否为空 key: 主键约束 default: 默认值约束 extra: 额外备注 ，auto_increment 自增长 int： 整型 最大存储的值为2147483647 float：浮点型 date ：日期类型：存储的数据需求加单引或者双引号‘2023-2-17’ varchar: 字符串类型（一定要加单引或者双引号） char : 单个字符类型 bigint：可以存储超过手机号码的整数，手机号码只能用bigint来存储 数据库常见的约束： not null：非空约束 primary key：主键约束 ，里面的值必须是唯一的不能有重复 default：默认值约束 auto_increment: 自增长约束（一定结合primary key搭配一起使用） 02344 ===》当前基础上加1 02345 foreign key ===》外键约束 对表结构的操作： mysql> alter table duoceshi31 rename duoceshi; 把duoceshi31表名修改为duoceshi mysql> alter table duoceshi change id sid int(10); 把duoceshi表的id字段改成sid且去掉自增长 mysql> alter table duoceshi change sid sid int(10) auto_increment; 把duoceshi表的sid字段改成sid且加上自增长 mysql> alter table duoceshi add sex int(20) after name; 添加一个sex字段且放在name字段后面 mysql> alter table duoceshi add age int(20); 添加age字段默认放在表最后面 mysql> alter table duoceshi modify age int(20) first; 把age字段移动到表的第一个位置 mysql> alter table duoceshi modify age int(20) after name; 把age字段移动到name字段后面 mysql> alter table duoceshi add (age1 int(20),age2 int(20)); 同时添加字段age1和age2默认放在表最后 mysql> alter table duoceshi drop age1,drop age2; 同时删除age1和age2字段 mysql> drop table student; 删除student表 对表数据的操作： 1.增加 ===》insert into 插入数据 insert into duoceshi(sid,class,score,name,age,sex,phone,time)values(1,1833,88,'xiaochen',21,0,13400005555,'2023-2-17'); 往duoceshi表中插入一条数据 mysql> select * from duoceshi; 查询duoceshi表中所有数据 mysql> insert into duoceshi(class,name,age,sex)values(1832,'xiaozhang',1,23); 插入对应字段值 insert into duoceshi(class,score,name,age,sex,phone,time)values(1831,89.56478,'xiaohong',19,1,13122223333,'2023-08-09'),(1833,68.879,'xiaoming',18,0,13122225555,'2023-08-12'),(1832,90.345,'xiaoliu',19,0,13155553333,'2023-02-09'); ===》往duoceshi表中同时插入多条数据 注意：0不等于null ，null指的是一个空属性，0是一个值 2.修改====》update+表名+set+字段对应值 where +指定位置 mysql> update duoceshi set score=76 where sid=2; ===》修改duoceshi表中的sid=2的score的值为76 mysql> update duoceshi set score=85 where sid<=3; ===》修改duoceshi表中的sid小于等于3的score的值为85 mysql> update duoceshi set score=100 where name like 'xiao%'; ====》修改duoceshi表中name以xiao开头的score的值为100（%号代表通配符，%放在后面就是以什么开头，%放在前面就是以什么结尾，前后都有%就是包含） mysql> update duoceshi set score=60 where class=1833 and phone=13400005555; ===》修改duoceshi表中class为1833班且phone为13400005555的score的值为60 mysql> update duoceshi set score=75 where sid>=5 and sid<=7; ===》修改duoceshi表中sid在5到7之间的score的值为75 mysql> update duoceshi set score=80 where sid between 5 and 7; ====》修改duoceshi表中sid在5到7之间的score的值为80
1.删除表数据====》delete from +表名 where +限定的条件 mysql> delete from duoceshi where name='lisi'; =====》删除duoceshi表中name等于lisi的数据 2.删除表所有数据====》truncate +表名 mysql> truncate dcs; ===》删除表所有数据，表结构还在 3.删除表所有数据和表结构===》drop table +表名 mysql> drop table dcs; ====》删除表所有数据和表结构，直接把表删除 4.查询====》select * from +表名：查询表所有的数据（*代表所有） mysql> select class,score,name from duoceshi; ===》查询对应字段的数据 mysql> select * from duoceshi where sex!=0; ===》查询性别不等于0的所有数据 mysql> select * from duoceshi where sex<>0; ====》查询性别不等于0的所有数据 mysql> select * from duoceshi where score>=80 and score<=90; ===》查询score在80到90之间的所有数据 mysql> select * from duoceshi where score between 80 and 90;====》查询score在80到90之间的所有数据 mysql> select * from duoceshi where class is NULL; ===》查询class字段为null的数据（null是属性不能用等于） mysql> select * from duoceshi limit 5;===》查询表中前五行数据 mysql> select * from duoceshi limit 2,4; ===》查询表中3到6行数据 mysql> select * from duoceshi limit 2,3;===》查询表中3到5行数据 mysql> select * from duoceshi where name like 'xiao%'; ===》查询name以xiao开头的所有数据 mysql> select * from duoceshi where name like '%zh%'; ====》查询name包含zh的所有数据 mysql>select * from duoceshi order by score desc; ===》对socre进行降序排序 mysql>select * from duoceshi order by score asc; ===》对socre进行升序排序 mysql> select name from duoceshi order by score desc limit 3; 查询出表中score为前三名的name的值 mysql> select class,count(*) from duoceshi group by class; ===》根据class进行分组，然后求出每个班级的人数（对某个分组，select后面查询字段必须是分组的字段或者聚合函数，不能接其他字段） mysql> select count(sex) from duoceshi where sex=0; ===》统计sex为0的人数 mysql> select count(*) from duoceshi where sex=0; ===》统计sex为0的人数 mysql> select sum(score) from duoceshi where class=1832; ===》求出1832班的score总和 mysql> select avg(score) from duoceshi where class=1832; ===》求出1832班的score平均数 mysql> select max(score) from duoceshi where class=1832;===》求出1832班最高成绩 mysql> select min(score) from duoceshi where class=1832;====》求出1832班最低成绩 mysql> select distinct(class) from duoceshi; ===》把表中的class字段的值去重 mysql> select class,count(*) as a from duoceshi group by class;==》帮count(*)取别名为a mysql> select class from duoceshi group by class having sum(score)>150; ==》求出总成绩大于150分的班级 常用的聚合函数： sum(): 求和 count(): 统计 avg(): 求平均数 max():最大值 min():最小值 distinct():去重 （group by也有去重功能） 重点： 1.分组函数group by只能和聚合函数、分组的字段一起使用 2.where 后面可以接group by，但是group by 后面不能接where条件 3.group by前面加where条件是为了先过滤再分组，group by后面接条件用having 加条件（一般接聚合函数）
十、数据库单表练习题 1、查询1832班的成绩信息 mysql> select english,chinese,math from duoceshi where class=1832; 2，查询1833班，语文成绩大于80小于90的成绩信息 mysql> select english,chinese,math from duoceshi where class=1833 and chinese>80 and chinese<90; 3，查询学生表中5到10行的数据 mysql> select * from duoceshi limit 4,6; 4，显示1832班英语成绩为98，数学成绩为77的姓名与学号 mysql> select sid,name from duoceshi where class=1832 and english=98 and math=77; 5，查询出1832班成绩并且按语文成绩排序（降序） mysql> select english,chinese,math from duoceshi where class=1832 order by chinese desc; 6，查询1833班与1832班，语文成绩与数学成绩都小于80的姓名。 mysql> select name from duoceshi where (class=1833 or class=1832) and chinese<80 and math<80; 7，查询出没有参加语文考试的学生姓名和班级名称。 mysql> select name,class from duoceshi where chinese is null; 8，求出班上语文成绩不及格的学生姓名 mysql> select name from duoceshi where chinese<60 or chinese is null; 9，求出每个班的数学平均成绩 mysql> select class,avg(math) from duoceshi group by class; 10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组 mysql> select class,sum(chinese) from duoceshi group by class; 11、将语文成绩不及格的学生成绩改为60分 mysql> update duoceshi set chinese=60 where chinese<60 or chinese is null; 12、三科分数都大于70分的人名和年纪 mysql> select age,name from duoceshi where english>70 and chinese>70 and math>70; 13、求出英语分数高于70且其它任何一科目大于60分的人和班级 mysql> select class,name from duoceshi where english>70 and (chinese>60 or math>60); 14、统计每个班的人数 mysql> select class,count(*) from duoceshi group by class; 15、求每个班数学成绩大于80的人数 mysql> select class,count(*) from duoceshi where math>80 group by class;
1.新建数据表_水果表fruits CREATE TABLE fruits( f_id char(8) PRIMARY KEY NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(10) NOT NULL); INSERT INTO fruits (f_id, s_id, f_name, f_price)VALUES ('a1', 81,'apple',5), ('b1',81,'blackberry', 8), ('bs1',82,'orange', 11), ('bs2',85,'melon',8), ('t1',82,'banana', 8), ('t2',82,'grape', 5), ('o2',83,'coconut', 9), ('c0',81,'cherry', 3), ('a2',83, 'apricot',2), ('l2',84,'lemon', 6), ('b2',84,'berry', 7), ('m1',86,'mango', 15), ('m2',85,'xbabay', 2), ('t4',87,'xbababa', 3), ('m3',85,'xxtt', 11), ('b5',87,'xxxx', 3); 2.创建数据表_供应商表 suppliers CREATE TABLE suppliers( s_id int PRIMARY KEY AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(8) NULL, s_call CHAR(50) NOT NULL) ; INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call) VALUES(81,'FastFruit Inc.','Tianjin','300000','48075'), (82,'LT Supplies','Chongqing','400000','44333'), (83,'ACME','Shanghai','200000','90046'), (84,'FNK Inc.','Zhongshan','528437','11111'), (85,'Good Set','Taiyuang','030000', '22222'), (86,'Just Eat Ours','Beijing','08', '45678'), (87,'DK Inc.','Zhengzhou','450000', '33332'); 3.创建表_订单表 orders CREATE TABLE orders( o_num int PRIMARY KEY NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int NOT NULL); INSERT INTO orders(o_num, o_date, c_id)VALUES (30001, '2008-09-01', 8001), (30002, '2008-09-12', 8003), (30003, '2008-09-30', 8004), (30004, '2008-8-03', 8005), (30005, '2008-8-08', 8001); -- 4.新建数据表 orderitems CREATE TABLE orderitems( o_num int NOT NULL, o_item int NOT NULL, f_id char(8) NOT NULL, quantity int NOT NULL, item_price decimal(8) NOT NULL, PRIMARY KEY (o_num,o_item)) ; INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)VALUES (30001, 1, 'a1', 8, 5), (30001, 2, 'b2', 3, 7), (30001, 3, 'bs1', 5, 11), (30001, 4, 'bs2', 15, 9), (30002, 1, 'b3', 2, 20), (30003, 1, 'c0', 80, 8), (30004, 1, 'o2', 50, 2), (30005, 1, 'c0', 5, 8), (30005, 2, 'b1', 8, 8), (30005, 3, 'a2', 8, 2), (30005, 4, 'm1', 5, 14); 5.创建数据表_客户表 customers CREATE TABLE customers( c_id int PRIMARY KEY NOT NULL AUTO_INCREMENT, c_name char(50) NOT NULL, c_address char(50) NULL, c_city char(50) NULL, c_zip char(8) NULL, c_contact char(50) NULL, c_email char(255) NULL); INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) VALUES (8001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'), (8002, 'Stars', '333 Fromage Lane','Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'), (8003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000','LuoCong', NULL), (8004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com'); 1.在SELECT语句中指定所有字段 mysql> select f_id,s_id,f_name,f_price from fruits; 2.查询fruits表中f_name列所有水果名称 mysql> select f_name from fruits; 3.从fruits表中获取f_name和f_price两列 mysql> select f_name，f_price from fruits; 4.查询价格为8元的水果的名称 mysql> select f_name from fruits where f_price=8; 5.查找名称为“apple”的水果的价格 mysql> select f_price from fruits where f_name='apple'; 6.查询价格小于8的水果的名称 mysql> select f_name from fruits where f_price<8; 7.查询s_id为81和82的记录，并按照f_name升序排序 mysql> select * from fruits where s_id=81 or s_id=82 order by f_name asc; 8.查询所有s_id不等于81也不等于82的记录，并按照f_name升序排序 mysql> select * from fruits where s_id!=81 and s_id!=82 order by f_name asc; mysql> select * from fruits where s_id<>81 and s_id<>82 order by f_name asc; 9.查询价格在2元到8元之间的水果名称和价格 mysql> select f_name,f_price from fruits where f_price>=2 and f_price<=8; mysql> select f_name,f_price from fruits where f_price between 2 and 8; 10.查询价格在2元到8元之外的水果名称和价格 mysql> select f_name,f_price from fruits where f_price<2 or f_price>8; mysql> select f_name,f_price from fruits where f_price not between 2 and 8; 11.查找所有以’b’字母开头的水果 mysql> select f_name from fruits where f_name like 'b%'; 12.在fruits表中，查询f_name中包含字母’g’的记录 mysql> select * from fruits where f_name like '%g%'; 13.查询以’b’开头，并以’y’结尾的水果的名称 mysql> select f_name from fruits where f_name like 'b%y'; 14.查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值 mysql> select c_id,c_name,c_email from customers where c_email is not null; 15.在fruits表中查询s_id=81，并且f_price大于等于5的水果价格和名称 mysql> select f_name,f_price from fruits where s_id=81 and f_price>=5; 16.在fruits表中查询s_id=81或者82，且f_price大于5，并且f_name=‘apple’的水果价格和名称 mysql> select f_name,f_price from fruits where (s_id=81 or s_id=82) and f_price>5 and f_name='apple'; 17.在fruits表中查询s_id=81或者s_id=82的水果的f_price和f_name mysql> select f_name,f_price from fruits where s_id=81 or s_id=82; 19.查询fruits表中s_id字段的值，返回s_id字段值且不得重复 mysql> select distinct(s_id) from fruits; mysql> select s_id from fruits group by s_id; 20.查询fruits表的f_name字段值，并对其进行排序 mysql> select f_name from fruits order by f_name desc; 21.查询fruits表中的f_name和f_price字段，先按f_name降排序，再按f_price升排序 mysql> select f_name,f_price from fruits order by f_name desc,f_price asc; 22.查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序 mysql> select f_name,f_price from fruits order by f_price desc; 23.查询fruits表，先按f_price降序排序，再按f_name字段升序排序 mysql> select f_name,f_price from fruits order by f_price desc,f_name asc; 24.在fruits表中根据s_id对fruits表中的数据进行分组并统计数量 mysql> select s_id,count(*) from fruits group by s_id; 26.在fruits表中根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 mysql> select s_id,count(*) from fruits group by s_id having count(*)>1; 28.在fruits表中根据s_id对fruits表中的数据进行分组，并显示记录数量 mysql> select s_id,count(*) from fruits group by s_id; 30.在orderitems表中查询总订单价格大于80的订单号和总订单价格 mysql> select o_num,sum(quantity*item_price) a from orderitems group by o_num having a>80; 31.显示fruits表查询结果的前4行 mysql> select * from fruits limit 4; 32.在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录 mysql> select * from fruits limit 4,3; 33.查询customers表中总的行数 mysql> select count(*) from customers; 34.查询customers表中有电子邮箱的顾客的总数 mysql> select count(*) from customers where c_email is not null; 35.在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类。 mysql> select o_num,count(*) from orderitems group by o_num; 36.在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量。 mysql> select o_num,sum(quantity) from orderitems group by o_num; 37.在fruits表中，查询s_id=83的供应商的水果价格的平均值。 mysql> select avg(f_price) from fruits where s_id=83; 38.在fruits表中，查询每一个供应商的水果价格的平均值。 mysql> select s_id,avg(f_price) from fruits group by s_id; 39.在fruits表中查找市场上价格最高的水果。 mysql> select f_name from fruits where f_price=(select max(f_price) from fruits); 40.在fruits表中查找不同供应商提供的价格最高的水果。 mysql> select f_name from fruits where (s_id,f_price) in(select s_id,max(f_price) from fruits group by s_id); 42.在fruits表中查找市场上价格最低的水果。 mysql> select f_name from fruits where f_price=(select min(f_price) from fruits); 43.在fruits表中查找不同供应商提供的价格最低的水果。 mysql> select f_name from fruits where (s_id,f_price) in(select s_id,min(f_price) from fruits group by s_id);
数据库备份： [root@localhost /]# mysqldump -uroot -p123456 dcs31>/duoceshi/dcs31.sql 把dcs31库备份到根目录下的duoceshi目录中且取名为dcs31.sql [root@localhost duoceshi]# mysql -uroot -p123456 duoceshi</duoceshi/dcs31.sql 把根目录下duoceshi目录下的dcs31.sql 文件还原到duoceshi库中（duoceshi库必须要存在） 数据看权限操作： mysql> select host,user,password from user; +-----------------------+------+-------------------------------------------+ | host | user | password | +-----------------------+------+-------------------------------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost.localdomain | root | | | 127.0.0.1 | root | | | localhost | | | | localhost.localdomain | | | +-----------------------+------+-------------------------------------------+ localhost、127.0.0.1====》代表的本地用户，可以直接通过centos和xshell对数据库进行操作的用户 % ===》代表远程访问用户，可以通过数据库客户端工具连接centos mysql> insert into user(host,user,password)values('localhost','dcs31',password('123456')); ===》新增一个dcs31本地用户且密码为123456 mysql> show grants for 'dcs31'@'localhost'; ====》查看本地用户dcs31是否有权限 报如下说明没有加载权限 ERROR 1141 (42000): There is no such grant defined for user 'dcs31' on host 'localhost' mysql> flush privileges; ====》刷新权限 mysql> grant select,update,delete,drop on *.* to 'dcs31'@'localhost' identified by '123456'; 给dcs31用户赋予select,update,delete,drop权限 mysql> grant all privileges on *.* to 'dcs31'@'localhost' identified by '123456'; ===》给dcs31用户赋予所有权限 mysql> revoke all on *.* from 'dcs31'@'localhost'; =====》移除所有权限 mysql> insert into user(host,user,password)values('%','root',password('123456')); ====》新增一个root远程（具有%远程访问）用户且密码为123456 mysql> grant all privileges on *.* to 'root'@'%' identified by '123456'; ======》给root具有%远程访问用户赋予所有权限
navicat的使用： ctrl+q ：新建一个窗口 ctrl+w：关闭窗口 ctrl+s: 保存 ctrl+鼠标滚动上下移动可以缩小放大字体 处理mysql数据中文乱码问题： 1、vim /etc/my.cnf ====》数据库配置文件 2、加入这行character_set_server=utf8 3、重启数据库 4、如果边里面还是乱码，删除表重新创建 5、选择数据库属性把latin改为utf8编码格式
create table aa(id int(1) PRIMARY key,name char(20)); create table cc(s_id int(1) PRIMARY key,score char(20)); insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi'); insert into cc(s_id,score)values(1001,'99'); select * from aa; select * from cc; 1.基本连接：2个表有相同字段的值则可以连接(相同字段的值连接在一起，没有相同的部分被舍弃) select * from aa,cc where aa.id=cc.s_id; 2.内连接：2个表有相同字段的值则可以连接(相同字段的值连接在一起，没有相同的部分被舍弃) select * from aa inner join cc on aa.id=cc.s_id; 3.左连接：以左表为准，右表为辅(如果左表有的数据右边没有自动补充null) select * from aa left JOIN cc on aa.id=cc.s_id; select * from cc left JOIN aa on aa.id=cc.s_id; 4.右连接：以右表为准，左表为辅(如果右表有的数据左边没有自动补充null) select * from aa right JOIN cc on aa.id=cc.s_id; select * from cc right JOIN aa on aa.id=cc.s_id; 5.硬链接：机械追加----强行连接(2个表字段数量必须是相同的) select * from aa union select * from cc; 一：求张三的成绩 1.临时表方法： select * from aa,cc where aa.id=cc.s_id; ===》临时表 select t.score from (select * from aa,cc where aa.id=cc.s_id) t where t.name='zhangsan'; 2.子查询（嵌套） select id from aa where name='zhangsan'; select score from cc where s_id=(select id from aa where name='zhangsan'); 3.连接查询 select score from aa,cc where aa.id=cc.s_id and name='zhangsan'; 二：求没有参加考试的学生的姓名 1.左连接方法 select * from aa LEFT JOIN cc on aa.id=cc.s_id; ===》临时表 select a.name from (select * from aa LEFT JOIN cc on aa.id=cc.s_id) a where a.score is null; 2.右连接方法 select * from cc right JOIN aa on aa.id=cc.s_id; select a.name from (select * from cc right JOIN aa on aa.id=cc.s_id) a where a.score is null; 3.子查询方法 select id from aa,cc where aa.id=cc.s_id; select name from aa where id not in (select id from aa,cc where aa.id=cc.s_id); select name from aa where id not in (select s_id from cc); 4.连接查询 select name from aa LEFT JOIN cc on aa.id=cc.s_id where score is null;
创建表dept表数据 create table dept (dept1 int(10) PRIMARY key,dept_name VARCHAR(20)); desc dept; SELECT * from dept; INSERT INTO dept VALUES(101,'财务'); INSERT into dept VALUES(102,'销售'); INSERT into dept VALUES(103,'IT技术'); INSERT into dept VALUES(104,'行政'); 创建表emp表数据 create table emp(sid int(10) PRIMARY key,name VARCHAR(20),age int(20),worktime_start date,incoming int(20),dept2 int(20)); desc emp; SELECT * from emp; INSERT INTO emp VALUES(1789,'张三',35,"1980-01-01",4000,101); INSERT into emp VALUES(1674,'李四',32,"1983-04-01",3500,101); INSERT into emp VALUES(1776,'王五',24,"1990-07-01",2000,101); INSERT into emp VALUES(1568,'赵六',57,"1970-10-11",7500,102); INSERT into emp VALUES(1564,'荣七',64,"1963-10-11",8500,102); INSERT into emp VALUES(1879,'牛八',55,"1971-10-20",7300,103); 1.列出每个部门的平均收入及部门名称; SELECT * from dept; SELECT * from emp; select * from dept,emp where dept.dept1=emp.dept2 select t.dept_name,avg(incoming) from (select * from dept,emp where dept.dept1=emp.dept2) t group by t.dept_name; select dept_name,avg(incoming) from dept,emp where dept.dept1=emp.dept2 group by dept_name; 2.财务部门的收入总和； select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务'; 3.It技术部入职员工的员工号 select sid from dept,emp where dept.dept1=emp.dept2 and dept_name='IT技术'; 4.财务部门收入超过2000元的员工姓名 select name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming>2000; 5.找出销售部收入最低的员工的入职时间； select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售'; select worktime_start from emp where incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售'); 6.找出年龄小于平均年龄的员工的姓名，ID和部门名称 select avg(age) from emp; select name,sid,dept_name from dept,emp where dept.dept1=emp.dept2 and age<(select avg(age) from emp); 7.列出每个部门收入总和高于9000的部门名称 select dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name having sum(incoming)>9000; 8.查出财务部门工资少于3800元的员工姓名 select name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming <3800; 9.求财务部门最低工资的员工姓名； select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务'; select name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务') and dept_name='财务'; 10.找出销售部门中年纪最大的员工的姓名 SELECT * from dept; SELECT * from emp; select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售'; select name from dept,emp where dept.dept1=emp.dept2 and age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name='销售') and dept_name='销售'; 11.求收入最低的员工姓名及所属部门名称： select min(incoming) from emp; select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming=(select min(incoming) from emp); 12.求李四的收入及部门名称 select incoming,dept_name from dept,emp where dept.dept1=emp.dept2 and name='李四'; 13.求员工收入小于4000元的员工部门编号及其部门名称 select sid,dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 where incoming<4000; 14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序； select max(incoming) from emp group by dept2; select name,dept_name,incoming from dept,emp where dept.dept1=emp.dept2 and incoming in (select max(incoming) from emp group by dept2) order by incoming desc; 15.求出财务部门收益最高的俩位员工的姓名，工号，收益 select name,sid,incoming from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' order by incoming desc limit 2; 16.查询财务部收入低于所有部门平均收入的员工号与员工姓名： select avg(incoming) from emp; select sid,name from dept,emp where dept.dept1=emp.dept2 and dept_name='财务' and incoming<(select avg(incoming) from emp); 17.列出部门员工数大于1个的部门名称； select dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name having count(*)>1; 18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门名称 select age,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>3000 and incoming<=7500; 19.求入职于20世纪70年代的员工所属部门名称； select dept_name from dept,emp where dept.dept1=emp.dept2 and worktime_start like '197%'; 20.查找张三所在的部门名称； select dept_name from dept,emp where dept.dept1=emp.dept2 and name='张三'; 21.列出每一个部门中年纪最大的员工姓名，部门名称； select max(age) from emp group by dept2; select name,dept_name from dept,emp where dept.dept1=emp.dept2 and age in (select max(age) from emp group by dept2); 22.列出每一个部门的员工总收入及部门名称； select sum(incoming),dept_name from dept,emp where dept.dept1=emp.dept2 group by dept_name; 23.列出部门员工收入大于7000的员工号，部门名称； select sid,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming>7000; 24.找出哪个部门还没有员工入职； select dept_name from dept LEFT JOIN emp on dept.dept1=emp.dept2 where sid is null; select dept2 from emp; select dept_name from dept where dept1 not in (select dept2 from emp); 25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ； select * from emp order by dept2 desc,worktime_start asc; 26.求出财务部门工资最高员工的姓名和员工号 select max(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务'; select name,sid from dept,emp where dept.dept1=emp.dept2 and incoming=(select max(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务') and dept_name='财务'; 27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。 select max(age) from emp; select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming between 7500 and 8500 and age=(select max(age) from emp); select name,dept_name from dept,emp where dept.dept1=emp.dept2 and incoming >= 7500 and incoming<=8500 and age=(select max(age) from emp);
存储过程： 存储过程有哪些优点： 1.存储过程是一个预编译的sql语句 优点： (1)：存储过程预编译过得，执行效率高 (2): 存储过程的代码直接放在数据库中，通过存储过程的名称直接调用 (3): 安全性表较高，执行存储需要一定权限用户 (4): 存储过程可以重复使用，可减少开发人原的工作量 (5)：对于单个增删改查语句可以直接封装成一个函数，或者一个集合中，需要的时候可以直接调用，也可以重复使用 (6): 单个sql语句每次执行都需要数据进行编译，而存储过程被创建只需要编译一次后续可以直接调用 (7): 可以防止sql注入 缺点：移植性比较差 drop table if exists mm; create table mm(id int(20) PRIMARY key auto_increment,score int(20)); #创建mm表 insert into mm values(1,67),(2,88); insert into mm values(3,90),(4,79); insert into mm values(5,80),(6,97); drop procedure if exists duoceshi31; #如果存在duoceshi31这个存储过程名的时候就删除 create procedure duoceshi31(n int) #创建一个存储过程名称叫duoceshi31,n是一个变量或者形式参数，int是数据类为整型 begin #存储过程的开始 declare i int(20) default(select count(*) from mm); #声明变量i的默认值是表的行数 #select * from mm; #select * from mm where id =6; #不带参数的调用 #select * from mm where id=n; #带参数的调用 if单分支语句 if n=0 then #如果n=0时，统计表中的行数 select count(*) from mm; else select sum(score) from mm; end if; if多分支语句 if n=0 then select count(*) from mm; else if n>0 and n<=10 then select * from mm order by score; else if n>10 then select avg(score) from mm; else select * from mm; end if; end if; end if; while循环 while n>i DO insert into mm(score)values(88); set i=i+1; end while; select * from mm; end #存储过程的结束 call duoceshi31(8); #调用存储过程 if 条件判断语句 1. if 单分支判断语句 if 条件 then sql语句 else sql语句 end if; 2.if 多分支判断语句 if 条件 then SQL语句 else if 条件 then sql语句 else if 条件 then sql语句 .... end if; end if; end if; 3.while 循环 while 语句的格式： while 条件 do sql语句 end while; 注意： 什么时候进入循环：当条件成立时，进入循环 什么时候退出循环：当条件不成立时，退出循环
第一题：补充的数据在最小或者最大的成绩上逐次加1分 drop table if exists mm; create table mm(id int(20) PRIMARY key auto_increment,score int(20)); #创建mm表 insert into mm values(1,67),(2,88); insert into mm values(3,90),(4,79); insert into mm values(5,80),(6,97); drop procedure if exists duoceshi31; #如果存在duoceshi31这个存储过程名的时候就删除 create procedure duoceshi31(n int) #创建一个存储过程名称叫duoceshi31,n是一个变量或者形式参数，int是数据类为整型 begin #存储过程的开始 declare i int(20) default(select count(*) from mm); #声明变量i的默认值是表的行数 declare j int(20) default(select max(score) from mm); if n=0 then select avg(score) from mm; else if n>0 and n<=i then select max(score) from mm; else while n>i DO set j=j+1; insert into mm(score)values(j); set i=i+1; end while; select * from mm; end if; end if; end #存储过程的结束 call duoceshi31(100); 第二题：由于目前学校数据表中女生的数学成绩不足20条，校长希望作为DBA的你能帮忙补充满20条且要求添加分数，在原来女生数学成绩最高分基础上逐次加1，还要显示女生数学分数总和 drop table if exists mm; create table mm(id int(20) PRIMARY key auto_increment,math int(20)); #创建mm表 insert into mm values(1,67),(2,88); insert into mm values(3,90),(4,79); insert into mm values(5,80),(6,97); drop procedure if exists duoceshi31; #如果存在duoceshi31这个存储过程名的时候就删除 create procedure duoceshi31(n int) #创建一个存储过程名称叫duoceshi31,n是一个变量或者形式参数，int是数据类为整型 begin #存储过程的开始 declare i int(20) default(select count(*) from mm); #声明变量i的默认值是表的行数 declare j int(20) default(select max(math) from mm); if i>=20 then select avg(math) from mm; else if n<0 then select max(math) from mm; else while n>i DO set j=j+1; insert into mm(math)values(j); set i=i+1; end while; select sum(math) from mm; select * from mm; end if; end if; end #存储过程的结束 call duoceshi31(20); 第三题： 存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录 用户 要求如下： 1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实 际的行数、如若不存在则自动补齐剩余的数据 2、表名为user,存储过程名字随意取,表中字段有id user_name user_pwd verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify 验证码字段为随机生成6位数验证码 CONCAT函数 可以把2个字符串进行连接。 drop table if exists user; create table user(id int(20) PRIMARY key auto_increment,user_name varchar(20),user_pwd int(20) default 123456,verify varchar(20)); insert into user values(1,'user1',123456,'W4E38J'); drop procedure if exists test; create procedure test(n int) begin declare i int(20) default(select count(*) from user); declare a varchar(20) default ''; #声明变量a的默认值为空 declare b varchar(20) default ''; if i>=1000 then select count(*) from user; else while n>i do set i=i+1; set a=(select concat('user',i)); set b=(select substring(md5(rand()),1,6)); insert into user(user_name,verify)values(a,b); end while; select * from user; end if; end call test(1000);
我的答案 由于目前学校数据表中女生的数学成绩不足20条，校长希望作为DBA的你能帮忙补充满20条且要求添加分数，在原来女生数学成绩最高分基础上逐次加1，还要显示女生数学分数总和 DROP TABLE if EXISTS sxcj; CREATE TABLE sxcj(id int(20) PRIMARY KEY auto_increment,score int(20)); insert into sxcj value (1,56); drop PROCEDURE if EXISTS aaa; create procedure aaa(n int) begin DECLARE i int(20) DEFAULT(select COUNT(*) from sxcj); DECLARE j int(20) default(select max(score) from sxcj); if i>=20 THEN select * from sxcj; select sum(score) from sxcj; else while n>i do set j=j+1; insert into sxcj(score) values(j); set i=i+1; end while; select * from sxcj; select sum(score) from sxcj; end if; end call aaa(2); 【【【【【【【【【【1】】】】】】】】】】】 存储过程题目：现在有一个user用户表,需要往user表中插入1000个登录用户 要求如下： 1、在插入用户前先判断是否存在1000个登录用户,如果存在则统计表中实 际的行数、如若不存在则自动补齐剩余的数据 2、表名为user,存储过程名字随意取,表中字段有id user_name user_pwd verify 格式如下(1,user1,123456,W4E38J),且id、用户名不能重复,verify 验证码字段为随机生成6位数验证码 CONCAT函数 可以把2个字符串进行连接。 #select concat('user',i) #select substring(md5(rand()),1,6) drop table if exists yh; create table yh(id int(20) primary key auto_increment,name varchar(20),pwd int(20) default 123456,verify varchar(20)); insert into yh value(1,'qq1',123456,'w4e38j'); drop procedure if exists bbb; create procedure bbb(n int) begin declare i int(20) default(select count(*) from yh);#行数为i declare x varchar(20) default ''; declare y varchar(20) default ''; if i>=1000 THEN select count(*) from yh; else while n>i do set x=(select concat('qq',i+1)); set y=(select substring(md5(rand()),1,6)); insert into yh(name,pwd,verify) values (x,123456,y); set i=i+1; end while; select * from yh; end if; end call bbb(10)
一：索引 show tables; desc aa; desc cc; desc duoceshi; select * from duoceshi; 1.普通索引 create index bb on aa(name); #在aa表的name字段上创建一个索引叫bb名字 show index from aa; #查看索引 create index yy on cc(score); #在cc表的score字段上创建一个索引叫yy名字 show index from cc; alter table aa drop index bb; #删除aa表中的bb索引 2.唯一索引 ===对应字段的值必须是唯一的，允许有空值(如果有多个字段同时创建唯一索引字段值可以不唯一) create unique index r on duoceshi(class); #创建唯一索引 show index from duoceshi; alter table duoceshi drop index r; #删除唯一索引 3.主键索引跟主键约束相辅相成的 注意：添加一个主键约束就是添加一个主键索引，添加主键索引也是加主键约束(一个表里面只能有一个主键约束或者主键索引) alter table duoceshi add PRIMARY key (id); #添加主键约束也叫主键索引 alter table duoceshi change sid id int(20); #去掉自增长 alter table duoceshi drop PRIMARY key; #删除主键索引 show index from duoceshi; desc duoceshi; 二：视图 select * from duoceshi; create view dcs as(select name from aa); #创建一个dcs视图参照duoceshi表的name，age字段 show create view dcs; #查看视图 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `dcs` AS (select `duoceshi`.`name` AS `name`,`duoceshi`.`age` AS `age` from `duoceshi`) select * from dcs; 修改视图或者修改原表数据，同时都会影响 update duoceshi set age=60 where id=3; #修改原表数据 update dcs set name='wangwu' where age=60; 删除视图和原表：删除原表会影响视图，删除视图不会影响原表 drop view dcs; drop table aa; 三：外键约束====》foreign key 表对表之间的约束 show create table duoceshi; CREATE TABLE `duoceshi` ( `id` int(20) NOT NULL DEFAULT '0', `class` int(20) DEFAULT NULL, `english` int(20) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(20) NOT NULL DEFAULT '0', `chinese` int(20) DEFAULT NULL, `math` int(20) DEFAULT NULL, `time` date DEFAULT NULL, PRIMARY KEY (`age`) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 ENGINE=MyISAM ===>数据库的默认引擎 create table dcs1(id int(20) PRIMARY key,name varchar(20))engine=INNODB; #创建一个dcs1表数据库引擎为engine=INNODB show create table dcs1; CREATE TABLE `dcs1` ( `id` int(20) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 create table dcs2(sid int(20) PRIMARY key,sname varchar(20),constraint aa foreign key (sid) REFERENCES dcs1(id))engine=innodb; #dcs2表中的sid参照dcs1表的id创建一个名叫aa的外键约束 show create table dcs2; CREATE TABLE `dcs3` ( `sid` int(20) NOT NULL, `sname` varchar(20) DEFAULT NULL, PRIMARY KEY (`sid`), CONSTRAINT `aa` FOREIGN KEY (`sid`) REFERENCES `dcs1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 如果主表不存在的数据任何一张子表是无法插入跟该数据相关的任何数据 insert into dcs1 values(1,'xiaocheng'),(2,'xiaoli'); select * from dcs1; insert into dcs2 values(3,'lisi'); select * from dcs2; 如果要删除主表数据需要先删除与主表相关的子表数据，否则不能删除 delete from dcs1 where id=1; delete from dcs2 where sid=1; 删除外键 alter table dcs2 drop foreign key aa; #删除外键 show create table dcs2; CREATE TABLE `dcs3` ( `sid` int(20) NOT NULL, `sname` varchar(20) DEFAULT NULL, PRIMARY KEY (`sid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8