mysql学习记录

建库并查看

 create database if not exists `school` default charset utf8;
 show databases;

进入数据库

use `school`;

创建student表，teacher表

CREATE TABLE IF NOT EXISTS `student`(
    `s_id` INT UNSIGNED AUTO_INCREMENT,
    `s_name` VARCHAR(100) NOT NULL,
    `gender` VARCHAR(4) NOT NULL,
    `class` VARCHAR(100) NOT NULL,
    PRIMARY KEY ( `s_id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table if not exists `teacher`(
    `t_id` int unsigned auto_increment,
    `t_name` varchar(100) not null,
    `gender` varchar(40) not null,
    primary key(`t_id`)
    )engine=InnoDB default charset=utf8;
CREATE TABLE IF NOT EXISTS `result`(
    `stu_id` INT UNSIGNED AUTO_INCREMENT,
    `subject_id` VARCHAR(100) NOT NULL,
    `s_result` VARCHAR(10) NOT NULL,
    PRIMARY KEY ( `stu_id`,`subject_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
show tables;

查看表结构

desc `student`;

表插数据

--插入单条数据
 insert into student
 (s_name,gender,class)
  values
 ("小明","男","大三");

--插入多条数据
 insert into student
  (s_name,gender,class)
  values
  ("小暗","男","大三"),("小红","女","大三"),("小丽","女","大四"),("小兰","女","大三"),("小白","女","大一"),("小黑","男","大一");

insert into result
  (stu_id,subject_id,s_result)
  values
  ("1","1","88"),("1","2","67"),("1","3","98"),("2","1","75"),("2","2","95"),("2","3","58"),("3","1","75"),("3","2","91"),("3","3","87"),("4","1","79"),("4","2","87"),("4","3","97"),("5","1","69"),("5","2","71"),("5","3","62"),("6","1","92"),("6","2","81"),("6","3","99"),("7","1","92"),("7","2","88"),("7","3","89");

简单查询

--查询全部数据
select * from `student`;

--查询指定字段
select `s_id`,`gender` from `student`;
--别名 
select `s_id` as 学号,`gender` as 学生姓名 from `student` as s;
--拼接
select concat('姓名:',s_name) as 新名字 from student;

select `stu_id` from result;
--去重
select distinct `stu_id` from result;

--查询结果加1
select `stu_id`,`s_result`+1 as '提分后' from result;

where的使用
作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成，应该是布尔值。
逻辑运算符：
and &&
or ||
not !

--and
select `stu_id`,`s_result` from result where `s_result`>=95 and `s_result`<=100;

--模糊查询 between and
 select `stu_id`,`s_result` from result where `s_result` between 95 and 100;

--not !=
select `stu_id`,`s_result` from result where not `stu_id`=2;
select `stu_id`,`s_result` from result where `stu_id`!=2;

比较运算符
is null 如果操作符为null，结果为真
is not null 如果操作符不为null，结果为真
between and 如果在两个值之间，结果为真
like SQL匹配，如果匹配成功，则结果为真
in 如果值在某一个值中，结果为真

--like 
--%通配符，代表0到任意个字符
--_一个字符
--查找张姓同学
select `s_id`,`s_name` from `student` where `s_name` like '张%';
--查找张X
select `s_id`,`s_name` from `student` where `s_name` like '张_';
--查找张XX
select `s_id`,`s_name` from `student` where `s_name` like '张__';
--查找中间带有国字的
select `s_id`,`s_name` from `student` where `s_name` like "%国%";

--in 具体的一个或多个值
--查询2，4,7号学生
 select `s_id`,`s_name` from `student` where `s_id` in (2,4,7);
 --查询大三学生
 select `s_id`,`s_name` from `student` where `class` in ('大三');

--null not null
 --查询班级为空的学生
 select `s_id`,`s_name` from `student` where `address`='' or address is null;
 --查询有出生日期的学生
 select `s_id`,`s_name` from `student` where `BornDate`is not null;

连表查询

--join