Mysql core statement

Reference: B stations mad God said JAV: MYSQL latest tutorial
SQL statement: structured query language Structured Query Language
Category

Insert picture description here

1. Operating the database

【】The content is optional.
1. Create a database:

create database 【if not exists】 数据库名;

Create a database table:

create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null default '123456' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '邮箱',
    primary key(`id`)      //设置主键
)engine=InnoDB default charset=utf8;

Summary formula:

create table 【if not exists】 `表名`(
	`字段名` 列类型 【属性】 【索引】 【注释】, 
    `字段名` 列类型 【属性】 【索引】 【注释】,
    .........
    `字段名` 列类型 【属性】 【索引】 【注释】,
    【primary key(`id`) 设置主键】
)【表类型】 【字符集设置】 【注释】;

2. Delete the database:

drop database 【if exists】数据库名;

3. Use the database:

use 数据库名;

4. View all databases:

show databases;

5. Delete and modify the attributes of the table:

//修改表名     alter table 旧表名 rename as 新表名;
alter table zw rename as zhuwang;
//增加表的字段   alter table 表名 add 字段名 列属性;
alter table zhuwang add age int(4);
//修改表的字段  (重命名,属性)
alter table zhuwang modify age  varchar(11);
alter table zhuwang change age age1 int(1);
//删除表的字段
alter table zhuwang drop age1;

2. DML language (data manipulation language)

Database meaning: data storage, data management
1.insert

//insert into 表名 ([字段名1,字段名2,.....]) value ('值1','值2',.....)
insert into `zhuwang` value (1,20010101,2008);

insert into `student` (`name`) values ('张三');
insert into `student` (`name`,`pwd`,`sex`) values ('张四','aaaaaa','男');
注意:
	1.字段与字段用英文逗号隔开
	2.字段是可以省略的,但后面的值必须要一一对应,不能少
	3.可以同时插入多条数据,values后面的值需要使用都好隔开即可

2.updata

//修改
update `student` set `name`='王老五' where id = 1; 
update `student` set `name`='王老五' where id between 1 and 5; 
update `student` set `name`='王老五' where id = 1 and pwd='123456'; 

//修改多个属性,用逗号隔开
update `student` set name='张三',pwd = '123456' where id = 1;

//不指定条件的情况下会修改所有内容
语法:
update 表名 set 列名=值 where 条件;
条件: where 子句,运算符,id等于某个值,大于某个值,在某个区间范围内修改...

3.delete

//删除数据
//语法:  delete from 表名 【where 条件】
delete from `student` where id=1;

truncate命令:完全清空一个数据库表,表的结构和索引约束不会变!
truncate `student`;

truncate 与 delete 区别:
	同:都能删除数据,都不会删除表结构
	异:truncate 自增会归0,delete不会改变自增

Three, DQL query data

select syntax summary

Insert picture description here

data query language: database query language
1. Query

select * from student;  //查询全部的学生
select `StudentNo`,`StudentName` from student;//查询指定字段
select `StudentNo` as 学号,`StudentName` as 学生姓名 from student;//给结果起一个别名

//函数:Concat(a,b)
select concat('姓名:',StudentName) as 新名字 from student;

语法:
select 字段,.... from 表;

去重:
select distinct `StudentNo` from result;//从结果中去重

select version();   // 查询系统版本号
select 100*3-1 as 计算结果  //用来计算
select `StudentNo`,`StudentName`+1 as '加一分后' from student;学员考试成绩加一分后查看

2. Where condition clause
Function: Retrieve the value that meets the condition in the data

逻辑运算符: and(&&)   or(||)   not(!)
select studentno,studentresult from result where studentresult between 60 and 90;
select studentno,studentresult from result where studentresult>=60 and studentresult<=90;
select studentno,studentresult from result where studentresult!=60;
===================================================================
模糊查询:比较运算符
is null        语法:a is null
is not null    语法:a is not null
between        语法:a between b and c
like           语法:a like b
in             语法:a in (a1,a2,a2,....)
like结合   %代表0到任意个字符, _代表一个字符
===================================================================
//查询姓刘的同学
//like结合   %代表0到任意个字符, _代表一个字符
select studentno from student where studentname like '刘%';
select studentno from student where studentname like '%佳%';
select studentno from student where studentno in (1000,1001,1002);

3. Joint table query
Seven kinds of connection query

Insert picture description here
//查询参加了考试的同学(学号,姓名,科目编号,分数)
select s.studentno,studentname,subjectno,studentresult from student as s
 inner join result as r on s.studentno=r.studentno;
 ====================================================================
 inner join       如果表中至少有一个匹配,就返回行
 left join        会从左表中返回所有的值,即使右表中没有匹配
 right join       会从右表中返回所有的值,即使左表中没有匹配
 ====================================================================

4. Self-connection (understanding)
self-connection test sample creation table:

CREATE TABLE `school`.`category`( 
			 `categoryid` INT(10) NOT NULL COMMENT '主题id', 
			 `pid` INT(10) NOT NULL COMMENT '父id', 
             `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
             PRIMARY KEY (`categoryid`) 
             ) ENGINE=INNODB default CHARSET=utf8; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values (3, 1, '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values (5, 1, '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES (4, 3, '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values (8, 2, '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values (6, 3, 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS (7, 5, 'ps技术');
//自链接查询
select a.categoryname as '父栏目',b.categoryname as '子栏目' from 
category as a,category as b where a.categoryid=b.pid;

5. Paging and sorting
Paging:limit

//为什么分页:环节数据库压力,给人体验更好,瀑布流
=================================================================
语法:limit (查询起始下标,页面大小)
=================================================================
select * from result order by studentresult desc limit 1,5

Sort:Ascending order: asc Descending order: desc order by: Sort by which field

select * from result order by studentresult asc;
select * from result order by studentresult desc;
Insert picture description here


6. Subquery:
where (this value is calculated)
Essence: nesting a sub-query statement in the where statement

//查询数据结构-1的所有考试结果(学号,科目编号,成绩),降序排列
方式一:联表查询
select studentno,r.subjectno,studentresult 
from result r
inner join `subject` sub 
on r.subjectno=sub.subjectno
where subjectname='数据库结构-1'
order by studentresult desc

方式二:使用子查询 (由里及外)
select studentno,r.subjectno,studentresult 
from `result`
where subjectno(
	select subjectno from `subject`
	where subjectname='数据库结构-1')
Insert picture description here


Insert picture description here

Example complete SQL statement:

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
	`studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别,0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话,允许为空',
    `address` varchar(255) not null comment '地址,允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`),
    unique key `identitycard`(`identitycard`),
    key `email` (`email`)
)engine=myisam default charset=utf8;

-- 创建年级表
drop table if exists `grade`;
create table `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

-- 创建科目表
drop table if exists `subject`;
create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) default null comment '课程名称',
    `classhour` int(4) default null comment '学时',
    `gradeid` int(4) default null comment '年级编号',
    primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;

-- 创建成绩表
drop table if exists `result`;
create table `result`(
	`studentno` int(4) not null comment '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;


-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,
`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1',
'[email protected]','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1',
'[email protected]','123456199001011233');

-- 插入成绩数据  这里仅插入了一组,其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),
(3,'大三'),(4,'大四'),(5,'预科班');

Four, Mysql function

Official website: https://dev.mysql.com/doc/refman/8.0/en/functions.html
1. Commonly used functions

数学运算:
select abs(-8)  --绝对值
select ceiling(9.4)  --向上取整
select floor(9.4)    --向下取整
select rand()        --返回0~1之间随机数

字符串函数:
select char_length('dhsudhs')   --返回字符串长度
select concat('拼接','所有','字符串') --拼接字符串
select upper();select lower()     --转换大小写

时间和日期函数:
select current_date()   --获取当前日期  ==select curdate()
select now()   --获取当前时间
select year(now())

2. Aggregate functions

count()  --计数
select count(studentname) from student;  --count(指定列) 会忽略所有的null
select count(*) from student;  --count(指定列) 不会忽略null
select count(1) from student;  --count(指定列) 不会忽略null

sum()   --求和
select sum(`studentresult`) as 总和 from result;
avg()   --求平均
select avg(`studentresult`) as 平均 from result;
select max(`studentresult`) as 最高 from result;
select min(`studentresult`) as 最低 from result;

MD5()  --加密
update testmd5 set pwd=MD5(pwd) where id = 1;
update testmd5 set pwd=MD5(pwd);    --加密全部的密码
--如何校验:将用户传递进来的密码进行MD5加密,然后比对加密后的值;