re_mysql_20221209【基础1】

https://gitee.com/yub4by/apibooks/blob/master/files/hm-mysql/MySQL-1-base.pdf

1. DDL & DML

-- navicat

SHOW TABLES;

DESC tb_user;

SHOW CREATE TABLE tb_user;
/*
CREATE TABLE `tb_user` (
  `id` int(11) DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
*/

DROP TABLE IF EXISTS emp2;

CREATE TABLE employee(
	id INT COMMENT '编号',
	workno VARCHAR(10) COMMENT '工号',
	name VARCHAR(10) COMMENT '姓名',
	gender CHAR(1) COMMENT '性别',
	age TINYINT UNSIGNED COMMENT '年龄',
	idcard CHAR(18) COMMENT '身份证号',
	entrydate date COMMENT '入职时间'
) COMMENT '员工表';


ALTER TABLE employee ADD nickname VARCHAR(20) COMMENT '昵称';

ALTER TABLE employee CHANGE nickname username VARCHAR(30) COMMENT '用户名';

ALTER TABLE employee RENAME TO tb_emp;

-- idea


show tables;

desc tb_emp;

insert into tb_emp(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

select * from tb_emp;

truncate table tb_emp;


insert into tb_emp(id,workno,name,gender,age,idcard,entrydate)
values(2,'2','Itcast','男',-1,'123456789012345678','2000-01-01');


insert into tb_emp
values (2,'2','Itcast','男',55,'123456789012345678','2000-01-01', 'hm');


insert into tb_emp
values (3,'3','Itcast','男',55,'123456789012345678','2000-01-01', 'hm'),
       (4,'4','Itcast','男',55,'123456789012345678','2000-01-01', 'hm'),
       (5,'5','Itcast','男',55,'123456789012345678','2000-01-01', 'hm');



update tb_emp
set name = "itheima"
where id = 1;

update tb_emp set entrydate = '2022-12-09';

update tb_emp set name='秋荷', gender='女', age=28 where id=2;

delete from tb_emp where username='1';

2. DQL

-- 去重
select distinct gender as "?" from tb_emp;


select * from tb_emp where username is null;

select * from tb_emp where username is not null;

select gender, count(*) from tb_emp group by gender ;


select gender, avg(age) from tb_emp group by gender ;

select * from tb_emp order by age asc, entrydate desc;

select * from tb_emp limit 0,2;
select * from tb_emp limit 2;
select * from tb_emp limit 2,2;


select sum(score_chinese)/count(*) as avg_chinese from tb_score; -- 88.5

-- select sum(score_chinese)/count(stuNo) as avg_chinese from tb_score; -- 118

select avg(score_chinese) as avg_chinese from tb_score; -- 88.5


select * from mysql.user;

3. DCL

3.1 管理用户

3.2 权限控制

4. 函数

-- mysql函数四类： 字符串函数、数值函数、日期函数、流程函数


select concat('Hello', ' MySQL'); -- Hello MySQL

select lower('HeLLo'); -- hello

select upper('Hello'); -- HELLO

select lpad('01', 5, '-'); -- ---01

select rpad('01', 5, '-'); -- 01---

select trim('  Hello MySQL    '); -- Hello MySQL

select substring('Hello MySQL',1,5); -- Hello


select ceil(1.1); -- 2

select floor(1.9); -- 1

select mod(7,4); -- 3

select rand(); -- 0~1之前的随机数：0.7216951620967083

select round(2.344,2); -- 保留两位小数四舍五入：2.34


/*
案例：通过数据库的函数，生成一个六位数的随机验证码

思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0
*/
select lpad(round(rand()*1000000 , 0), 6, '0'); -- 551397


select curdate(); -- 2022-12-09
select curtime(); -- 16:34:20
select now(); -- 2022-12-09 16:34:20


select YEAR(now()); -- 2022
select MONTH(now()); -- 12
select DAY(now()); -- 9


-- 增加指定的时间间隔A+B
select date_add(now(), INTERVAL 70 DAY); -- 2023-02-17 16:37:36

-- 获取两个日期相差的天数A-B
select datediff('2021-10-01', '2021-12-01'); -- -61


select if(false, 'Ok', 'Error'); -- Error


select ifnull('Ok','Default'); -- Ok
select ifnull('','Default'); --
select ifnull(null,'Default'); -- Default


create table score(
                    id int comment 'ID',
                    name varchar(20) comment '姓名',
                    math int comment '数学',
                    english int comment '英语',
                    chinese int comment '语文'
) comment '学员成绩表';

insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

select
  id,
  name,
  (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )
          '数学',
  (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'
    end ) '英语',
  (case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'
    end ) '语文'
from score;