MySQL实操

MySQL实操

数据库操作语法

查看数据库版本

1）没有连接到MySQL服务器，就想查看MySQL的版本。打开cmd，切换至mysql的bin目录，运行下面的命令即可：mysql -V 或 mysqladmin --version 或 mysql --help|find "Distrib"
2）如果已经连接到了MySQL服务器，则运行下面的命令：select version() 或 status 或 \s
3）在命令行连接上MySQL服务器时，其实就已经显示了MySQL的版本，如：mysql -uroot -padmins

查看数据库信息

1）使用 show databases 展示所有数据库；
2）使用 use+数据库名称 进入或改变当前使用的数据库；
3）使用 show+数据库名称 展示该数据库下的所有表；
4）查看表结构的方法：
登录mysql，执行：
desc+表名 或 describe+表名 或 show columns from 表名 或 explain+表名;
使用mysql的工具mysqlshow.exe：
mysql+数据库名称+表名

设置数据库最大连接数

在使用MySQL数据库的时候，经常会遇到这么一个问题，就是“Can not connect to mysql server. Too many connections”-mysql 1040错误，这是因为访问MySQL且还未释放的连接数目已经达到MySQL的上限。通常，mysql的最大连接数默认是100, 最大可以达到16384。

常用的有两种方式修改最大连接数：
1）命令行修改
这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。因为mysql启动后的初始化工作是从其配置文件中读取数据的，而这种方式没有对其配置文件做更改。

-- 命令行登录MySQL
mysql -uuser -ppassword
-- 查可以看当前的最大连接数
show variables like 'max_connections'
-- 设置最大连接数为1000，可以再次查看是否设置成功
set global max_connections=1000
-- 退出
exit

2）修改配置文件
这种方式说来很简单，只要修改MySQL配置文件my.ini或my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。但是有一点最难的就是my.ini这个文件在哪找。通常有两种可能，一个是在安装目录下（这是比较理想的情况），另一种是在数据文件的目录下，安装的时候如果没有人为改变目录的话，一般就在C:/ProgramData/MySQL往下的目录下。

修改数据库密码

1）mysql初始密码为空，默认端口3306，默认最大连接数为100，修改密码方式：在DOS下进入目录mysql\bin，然后键入以下命令：mysqladmin -u用户名 -p旧密码 password 新密码，如： mysqladmin -u root -p ab12 password djg345
2）命令行修改root密码：
mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
显示当前的user：
mysql> SELECT USER();

导入导出与备份

导出单表内容：mysqldump -uroot -padmins 数据库名 表名 > database_dump.sql
导出数据库：mysqldump -uroot -padmins 数据库名 > database.sql
导入脚本：mysql -uroot -padmins 数据库名 < database_dump.sql

另外可以使用图形化界面Navicat进行导出导入：
1）右击可以选择导出导入全部数据
2）先执行查询语句找到数据，然后上面应该会有导出结果的按钮，并且可以选择导出文件的格式

数据库增删改

创建数据库：CREATE DATABASE item_mysql;
删除数据库：DROP DATABASE item_mysql;
修改数据库名称：创建新的数据库，导入之前数据库数据结构和数据

数据库表操作语法

数据库表创建语句

-- 创建数据库表：学生表
-- 如果存在则删除表
DROP TABLE IF EXISTS stu_info;
-- 创建表结构
CREATE TABLE stu_info (
    -- 主键自增
    -- NOT NULL: 非空约束，用于控制字段的内容一定不能为空（NULL）。
    sNo int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    sName varchar(20) NOT NULL,
    sAge int(11) NOT NULL,
    -- mysql不支持检查约束，但是写上检查约束不会报错
	-- CHECK: 检查约束，用于控制字段的值范围。
    check(sAge between 15 and 20),
    -- 18位数字，小数位数为0（身份证），默认为空
    sId decimal(18,0) DEFAULT NULL,
    -- 设置当前日期为该字段默认值
    sDate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    sAddress text,
    -- 设置主键
    -- PRIMARY KEY: 主键约束，也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
    PRIMARY KEY (sNo),
    -- UNIQUE: 唯一约束，控件字段内容不能重复，一个表允许有多个Unique约束。
    UNIQUE KEY sId (sId)
)

-- 创建数据库表：老师表
DROP TABLE IF EXISTS tch_info;
CREATE TABLE tch_info (
    tNo int(11) NOT NULL AUTO_INCREMENT,
    tName varchar(20) NOT NULL,
    tStuNo int(11) DEFAULT NULL,
    PRIMARY KEY (tNo),
    -- 设置外建
    -- FOREIGN KEY: 外键约束，用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
    FOREIGN KEY (tStuNo) REFERENCES stu_info (sNo)
)

-- 创建临时表
CREATE TEMPORARY TABLE stu_info_temp SELECT * FROM stu_info;
-- 设置数量
CREATE TEMPORARY TABLE stu_info_temp AS(SELECT *  FROM stu_info LIMIT 0,10000);

-- 备份表
create table copytable select * from stu_info;

数据库表新增语句

-- 新增一列
alter table stu_info add column stucard varchar(20) not null after sAddress;
alter table stu_info add column sexenum enum('男','女','未知') after stucard;

-- 添加数据，如果主键自增可以不写
insert into stu_info(sName,sAge,sID,sAddress) values('juluy',18,'360724155815457542','江西');
insert into stu_info(sName,sAge,sID,sAddress) values('judy',20,'360724155815457523','珠海');
insert into stu_info(sName,sAge,sID,sAddress) values('sssk',20,'360724155815457524','珠海');

insert into tch_info(tName,tStuNo) values('sada',1);
insert into tch_info(tName,tStuNo) values('jsdg',2);

数据库表删除语句

-- 删除表，删除时要先将有外建的表删除
drop table tch_info;
drop table stu_info;

-- 删除数据
delete from tch_info where tName='xdzy'

-- 清空表（自增ID从头开始）
truncate table tch_info

-- 如果存在外键约束是无法清空的，可以先禁用外键约束，再清空
-- 查看外键约束状态，0：禁用；1：使用
SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

数据库表修改语句

-- 修改数据
update tch_info set tName='xdzy' where tNo=1

数据库表查询语法

基本查询语句

-- 查询部分数据
select sName,sID from stu_info where sNo=1

-- 查询时更改列标题（as,可省略）
select sName 姓名,sID 身份证 from stu_info

函数查询语句

-- 查询时删除重复行（可用all或distinct）distinct：只保留一行输出
select distinct(tName) from tch_info

-- count(*/column)：返回行数
-- sum(column)：返回指定列中唯一值的和
-- max(column)：返回指定列或表达式中的数值最大值
-- min(column)：返回指定列或表达式中的数值最小值
-- avg(column)：返回指定列或表达式中的数值平均值
-- date（Expression）: 返回指定表达式代表的日期值

条件查询语句

-- where后面可以有：比较（>,<,=,<>.!>,!<）
-- between、and（可以返回包括开头与结尾的所有数据）
-- 判断是否为列表指定项（in,not in）
-- 空值：null,not null
-- 逻辑：not,and,or
select * from stu_info where sName in('juluy','judy')

-- group by:按字段分组
-- 使用顺序：SELECT、FROM、JOIN、ON、WHERE、GROUP BY、HAVING、UNION、ORDER BY、LIMIT
-- where不能与聚合函数一起使用；having则可以
select sName from stu_info group by sName

-- 模式匹配符：like,not like（可用于char,varchar,text,ntext,datetime,smalldatetime）
-- %可用于缺少的值
select * from stu_info where sName like 'j%'
-- 下划线可代表一个未知的值
select * from stu_info where sName like '_u%'

-- desc：根据字段反排序；asc：根据字段正排序（优先选择前面排序）
-- 但是如果有二个相同字段的数据，则会根据前面先排序，再二者根据后面排序
select sName,sAge,sNo from stu_info order by sAge desc,sNo asc

-- 分页查询
-- 做分页的话，MySql使用Limit，Sql Service使用top，Oracle使用rownum
select * from stu_info limit 5,10; #返回第6-15行数据 
select * from stu_info limit 5; #返回前5行 
select * from stu_info limit 0,5; #返回前5行

-- 查询时限制返回的行数
select top 2 * from stu_info
-- 20 percent*：20%
select top 20 percent * from stu_info
-- 返回tName的前2行
select top 2 tName from stu_info

-- 查询前10条记录
select * from stu_info WHERE ROWNUM<10;

联合查询语句

-- from后面可以指定256个表或视图（可以为表取个别名）
-- 将2表的有关联的数据合并之后返回
select * from stu_info as a,tch_info as b where a.sNo=b.tStuNo;
select * from stu_info where sNo in(select tStuNo from tch_info where tNo=3);

-- inner join：内连接，没有匹配不返回
select a.sName,b.tName from stu_info a inner join tch_info b on stu_info.sNo=tch_info.tStuNo

-- left join：左连接，返回左边所有数据，没有匹配则对应为null
-- right join：右连接，full join：全连接
select a.sName,b.tName from stu_info a left join tch_info b on stu_info.sNo=tch_info.tStuNo

-- union：具有相似数据类型的2张表合并；union all：即使数据重复也会列出
-- 效率UNION高于UNION ALL
select tName from tch_info union select tName from tch_info

复杂查询语句

1、班级表、学生表、专业表、成绩表多表关联查询

-- 班级信息表
create table class_info(
	c_id int primary key auto_increment not null,
	c_name varchar(20) not null
);
insert into class_info(c_name) values('ST01'),('ST02'),('ST03');
select * from class_info;

-- 学生信息表
create table stu_info(
	s_id int primary key auto_increment,
	s_name varchar(50) not null,
	s_age int not null,
	s_sex int not null,
	s_origin varchar(50),
	s_tel varchar(100),
	c_id int,
	foreign key(c_id) references class_info(c_id)
);
insert into stu_info(s_name,s_age,s_sex,s_origin,s_tel,c_id) 
values('stu1',19,0,'广东珠海','13543090987',1),
('stu2',20,1,'广东广州','13543090987',1),
('stu3',18,0,'江西赣州','13543093245',2),
('stu4',21,1,'江西赣州','15890456789',2),
('stu5',20,0,'广东深圳','18769446565',3),
('stu6',19,1,'广东阳江','15908675453',3),
('stu7',20,0,'广东茂名','13554679546',null);
select * from stu_info;

-- 专业信息表
create table subject_info(
	sub_id int primary key auto_increment,
	sub_name varchar(20) not null
);
insert into subject_info(sub_name) 
values('JAVA'),('.Net'),('PHP'),('UI');
select * from subject_info;

-- 成绩表
create table score_info(
	sc_id int primary key auto_increment,
	score float not null,
	s_id int,
	sub_id int,
	foreign key (s_id) references stu_info(s_id),
	foreign key (sub_id) REFERENCES subject_info(sub_id)
);
insert into score_info(score,s_id,sub_id) 
values(50,1,1),(70,1,4),(65,2,1),(62,2,2),
(75,3,2),(80,3,3),(72,4,1),(55,4,2),(60,5,2),
(83,5,4),(92,6,2),(65,6,3),(65,7,1),(62,7,2);
select * from score_info;

-- 1）拷贝学生信息表数据到学生信息备份表中（stu_bak_info）
create table stu_bak_info select * from stu_info; 

-- 2）查询所有学生信息包含学生所在的班级
-- 这个不能将空的查询出来
select * from stu_info s,class_info c where s.c_id=c.c_id
-- 这个能将空值查询出来
select s.s_name,s.s_age,s_sex,s.s_origin,s.s_tel,c.c_name 
from stu_info s left join class_info c on s.c_id = c.c_id;

-- 3）查询年龄在19到21之间的学员信息
select s_name,s_age,s_sex,s_origin,s_tel 
from stu_info where s_age between 19 and 21;

-- 4）查询ST01班所有学生各科成绩
select s.s_name,sub.sub_name,sc.score
from stu_info s left join class_info c on s.c_id = c.c_id 
inner join score_info sc on s.s_id = sc.s_id
inner join subject_info sub on sub.sub_id = sc.sub_id 
where c.c_name = 'ST01';

-- 5）统计各个班级男生和女生的总人数
select c.c_name as 班级,s.s_sex as 性别, count(*) as 总人数 
from class_info c inner join stu_info s on c.c_id = s.c_id
group by c.c_name,s.s_sex order by c.c_name;

-- 6）统计各班级各科目的平均分
select c.c_name as 班级, sub.sub_name as 科目, avg(score) as 平均分
from class_info c left join stu_info s on c.c_id = s.c_id
inner join score_info sc on s.s_id = sc.s_id 
inner join subject_info sub on sc.sub_id = sub.sub_id 
group by c.c_name,sub.sub_name;

-- 7）查询所有科目的平均分最高的值
select sub.sub_name, avg(sc.score) as avg_score 
from stu_info s left join score_info sc on s.s_id = sc.s_id 
inner join subject_info sub on sc.sub_id = sub.sub_id 
group by sub.sub_name
having avg_score >=all(select avg(sc.score) as avg_score 
from stu_info s left join score_info sc 
on s.s_id = sc.s_id inner join subject_info sub 
on sc.sub_id = sub.sub_id group by sub_name);

-- 8）分页查询学生信息表
select * from stu_info s limit 0,3;

-- 9）每页显示3条，共7条记录，共多少页？
select 7/3 from dual;

2、课程表、成绩表、学生表、教师表多表关联查询

-- 课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) DEFAULT NULL,
  `t` int(11) DEFAULT NULL,
  PRIMARY KEY (`c`),
  KEY `t` (`t`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`t`) REFERENCES `teacher` (`t`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 新增数据
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');

-- 成绩表
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `s` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  KEY `s` (`s`),
  KEY `c` (`c`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`s`) REFERENCES `student` (`s`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`c`) REFERENCES `course` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 新增数据
INSERT INTO `sc` VALUES ('1', '1', '56');
INSERT INTO `sc` VALUES ('1', '2', '78');
INSERT INTO `sc` VALUES ('1', '3', '67');
INSERT INTO `sc` VALUES ('1', '4', '58');
INSERT INTO `sc` VALUES ('2', '1', '79');
INSERT INTO `sc` VALUES ('2', '2', '81');
INSERT INTO `sc` VALUES ('2', '3', '92');
INSERT INTO `sc` VALUES ('2', '4', '68');
INSERT INTO `sc` VALUES ('3', '1', '91');
INSERT INTO `sc` VALUES ('3', '2', '47');
INSERT INTO `sc` VALUES ('3', '3', '88');
INSERT INTO `sc` VALUES ('3', '4', '56');
INSERT INTO `sc` VALUES ('4', '2', '88');
INSERT INTO `sc` VALUES ('4', '3', '90');
INSERT INTO `sc` VALUES ('4', '4', '93');
INSERT INTO `sc` VALUES ('5', '1', '46');
INSERT INTO `sc` VALUES ('5', '3', '78');
INSERT INTO `sc` VALUES ('5', '4', '53');
INSERT INTO `sc` VALUES ('6', '1', '35');
INSERT INTO `sc` VALUES ('6', '2', '68');
INSERT INTO `sc` VALUES ('6', '4', '71');

-- 学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`s`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 新增数据
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');

-- 教师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`t`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 新增数据
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '贺高');
INSERT INTO `teacher` VALUES ('3', '杨艳');
INSERT INTO `teacher` VALUES ('4', '周磊');

-- 1）查询“001”课程比“002”课程成绩高的所有学生的学号
select a.s from (select s,score from sc where c='001') a,
(select s,score from sc where c='002') b 
where a.score>b.score and a.s=b.s

-- 2）查询平均成绩大于60分的同学的学号和平均成绩
select s,avg(score) from sc
group by s having avg(score)>60

-- 3）查询所有同学的学号、姓名、选课数、总成绩
select s.s,s.sname,count(sc.c),sum(score) from student s left join sc on s.s=sc.s
group by s.s,sname

-- 4）查询姓“李”的老师的个数
-- distinct：忽略重复值
select count(distinct(tname)) num from teacher
where tname like '叶%'

-- 5）查询没学过“叶平”老师课的同学的学号、姓名
select student.s,student.sname from student where s not in
(select distinct(sc.s) from sc,course,teacher 
where sc.c=course.c and teacher.t=course.t and teacher.tname='叶平')

-- 6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
select student.s,student.sname from student,sc where student.s=sc.s and sc.c='001'
and exists(select * from sc sc2 where sc.s=sc2.s and sc2.c='002')

-- 7）查询学过“叶平”老师所教的所有课的同学的学号、姓名
select s,sname from student
where s in (select s from sc,course,teacher 
where sc.c=course.c and teacher.t=course.t and teacher.tname='叶平'
group by s having count(sc.c)=
(select count(c) from course,teacher where teacher.t=course.t and tname='叶平'))

-- 8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
select s,sname from (select student.s,student.sname,score,
(select score from sc sc2 where sc2.s=student.s and sc2.c='002') score2
from student,sc where student.s=sc.s and c='001') s2 where score2<score