mysql的学习笔记（阶段一）

use mysql;
create table if not exists stu2
(
  sid int(11),
  name varchar(10),
  gender varchar(10),
  brith date
);
show table stu1;
show columns from  stu1;
-- alter table stu2 add address varchar(10); 
alter table stu2 add (address varchar(10),score int(10));
insert into stu2 values(1,"李自成","男","1000-12-12","北京",44),(2,"华佗","男","1201-12-13","三国",100);
select distinct * from stu2; #无重复记录查找
select * from stu1 as p1; -- as 可以省略
select *from stu2 as p2; -- 起别名
select name , score+10 score  from stu2;
select  distinct * from stu2 where name = "李自成";  -- 条件查询
select * from stu2 where not (name ="李自成");
-- 一个查询条件嵌套
-- if语句里面如果满足就返回yes，不满足的话就返回no
select a,b,c,
if(a*a=b*b+c*c or a*a+b*b=c*c or a*a+c*c = b*b,'Yes','No') as right_triangle
from line_segments;
select * from stu2 where score >=100;
#间隔
select * from stu2 where score between 40 and 101;
select * from stu2 where score >=40 and score <=100;
select * from stu2 where score >=40 && score<=101;
#或
select * from stu2 where score in(50,100); -- 50or100
select * from stu2 where score =50 or score =100;
#关键字查询···
select * from stu2 where name like "%佗"; -- 佗结尾
select * from stu2 where name like "%自%"; -- 代表中间的字是自
select * from stu2 where name like "_自%"; -- 代表第二个字是自，后面不管是什么
#c查看NULL
select * from stu2 where sid is null;
select * from stu2 where sid is not null;
#查看最小值最大值
select least(10,5,10) as small_number; -- 如果其中有一个数为null,则直接为NULL
select greatest(10,20,30) as big_number; -- 最大与上相同
select * from stu2 order by score asc; -- 根据score进行升序排列，asc可以省略。将序为 desc
select * from stu2 order by score desc , sid desc; -- 先按照第一个进行排序，然后如果第一个相同，就按照第二个进行排序。
-- 查询 name ,score 并按照score进行降序排列
select  distinct name,score from stu2 order by  score desc; 
-- 查询数目(不统计行包含空)
select count(*) from stu2;
select count(sid) from stu2;
select count(sid) from stu2 where score >50;
select sum(score) from stu2 where score = 100;
select  max(score) from stu2 where name = '华佗';
select min(score) from stu2 where name = '华佗';
select avg(score)from stu2 where name = '华佗';
use mysql;
create table product
(
  id bigint(20),-- 20代表值的位数
  name varchar(10),
  price double,
  producer varchar(10)
);
insert into product values(1,"海尔冰柜",3400,'海尔'),(2,'海尔冰柜',4000,'海尔'),(3,'海尔电扇',2400,"海尔");
insert into product (id,name,price,producer) values (4,"格力空调",6000,"格力"),(5,'格力电扇',1300,"格力"),(6,'李宁运动鞋',500,'李宁'),
(7,'李宁卫衣',300,'李宁');
-- 增加或修改字段属性
alter table product modify price double(16,0) comment '价格';
alter table product alter price set default 200;
insert into product values(8,'oppoA11',2300,'oppo'),(9,'oppoA13',3000,'oppo');
-- 删除重复统计的数据
-- 不能将直接查处来的数据当做删除数据的条件，我们应该先把查出来的数据新建一个临时表，然后再把临时表作为条件进行删除功能
-- 在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名)比如下面的a
delete from product where id in (select id from(select id from product group by id having count(id)>1)as a)
select  * from product order by id; -- 默认按照id进行降序排列
-- 分组一般和聚合放在一起,select后面只能选择唯一的字段作为标准。
select  producer,count(id) from product group by producer; 
-- 将主键顺序重新排序，解除乱序
alter table product drop column id;
alter table product add id int(4) not null primary key auto_increment first;
-- 按照producer进行分组，对应cnt(count(id)获取数目的别名,然后按照cnt的大小进行排序(默认升序))
select producer, count(id) cnt from product group by producer having cnt > 1 order by cnt;
-- limit n 显示前几条
-- limit m,n 从m+1开始，显示n条
select * from product limit 5; -- 显示前五条
select *from product limit 3,5;
use mysql;
select * from product;
create table produc2
(
  name varchar(20),
  price double(8,1)
);
alter table produc2 rename product2;
insert into product2(name,price) select name,price from product; -- 插入字段
-- isnull(number) 判断number是否为空，如果为空，返回1，否则返回0
-- ifnull(number1,number2),判断number1是否为空，如果为空就用number2代替number1
-- nullif(number1,number2),如果number=number,返回null,否则返回number1
-- 正则表达式
-- ^在字符串开头进行匹配
select  'abc' regexp '^a';
select * from product where name regexp "^海";
-- ^在字符串末尾开始匹配
select  'abc' regexp 'c$';
select * from product where name regexp '尔$';
-- 匹配任意字符(.可以匹配除了换行符以外的任意字符(单个字符))
select 'abc' regexp '.b';
-- [...]匹配括号内的任意字符
select 'abc'  regexp '[asn]'; # 表示括号内的任意字符是否在前面的字符串中出现，是的话，就返回1
-- [^...] 代表任意字符都没有出现，与上面的相反 
-- a* 表示匹配0个或者多个a，包含空字符串。可以作为占位符使用，有没有指定字符都可以匹配到数据
select 'stab' regexp 'ta*b'; -- a*表示a可以出现0次或者多次
-- a+  表示匹配1个或者多个a
select 'stab' regexp 'ta+b';
-- a?表示匹配0个或者一个字符
select 'stb' regexp '.ta?b';
-- a|a1 匹配a或者a1
select 'a' regexp 'a|a1';
-- a{m} 出现m次
select 'annna' regexp 'an{3}a';
-- a{m,},匹配m个或者更多个a
-- {m,n}至少m次，最多n次
-- 创建部门表
create table if not exists dept
(
  detpno varchar(20) primary key, -- 部门号
  name varchar(20) -- 部门名字
);
create table if not exists emp
(
  eid varchar(20) primary key, -- 员工编号
  ename varchar(20), -- 员工名字
  age int, -- 员工年龄
  dept_id varchar(20), -- 员工所属部门
  constraint emp_fk foreign key (dept_id) references dept(detpno)
);
 -- 多表直接存在外键约束的时候，删除的时候不能直接删除主表
 -- 删除外键约束
 alter table emp drop foreign key emp_fk ;
select * from emp,dept;
-- 外键约束对查询没有影响
-- 交叉联合查询
select * from emp where age > all(select age from emp where dept_id = "1003");
select * from emp where age > all(60,58);
-- 查询不属于任何一个部门的员工信息
select * from emp where dept_id != all(select detpno from dept);
-- any关键字(some 和 any 的作用一样)，可以理解为任一
select * from emp3 where age > any(select age from emp3 where dept = '1003') and dept_id!= '1003';
-- IN(子查询关键字)
-- mysql 函数
-- group_concat，一列的数据合并到一行
select group_concat(name) as result from employee; -- 默认结果之间是按照,号分割
-- 设置查询数据的之间的间隔符号
select group_concat(name separator";")  as result from employee; 
-- 指定排序方式和分隔符
select department,group_concat(emp_name separator ';') from emp group by department; -- 先分组，可以认为先根据部门进行分组，然后将同一部门的名字进行合并为一行
-- 还可以分组后，将名字合并一行进行再排序，排序按照薪资排序
select department,group_concat(emp_name order by salary separator ';')
from emp group by department; -- 注：group_concat里面的 separator 必须放在最后
-- 数学函数
-- 取绝对值
select abs(-10);
select abs(10);
-- 向上取整
select ceil(1.1); -- 返回比它大的最小的整数
select floor(1.1) -- 向下取整
-- 返回列表中最大值
select greatest(1,2,3);
-- 求列表中的最小值
select least(1,2,3);
-- 求余数
select mod(5,2);
-- 取x的y次方
select power (2,3);
-- 取随机数
select rand() * 100; -- 生成0到100的随机的数
-- 取小数的四舍五入
select round(3.1415);
-- 四舍五入保留指定小数位数
select round(3.1415,3);
-- avg() 求平均数
select category_id,avg(price) from product group by category_id;
-- 将小数直接截取到指定位数，不会进行四舍五入
select truncate(3.1415,3);
-- 字符串函数
-- char_length() 函数,求字符串中包含多少个字符
select char_length("jgdabc");
-- length() 取长度，返回字节
select length("你好");
-- concat() 进行字符串合并、
select concat('hello','world');
-- 指定合并的时候分隔符
select concat_ws('-','hello','world')
-- 返回字符串在列表中首次的位置
select field('aaa','aaa','bbb','ccc'); -- 要查找的是第一个参数
-- ltrim()去除字符串左边的空格
select ltrim('  aaaa');
-- rtrim() 去除字符串右边的空格
select rtrim('sask   ');
-- trim() 去除两端的空格
select trim(" sjsn  ");
-- mid() 截取字符串
select mid("helloworld",2,3); -- 从第二个字符开始截取，截取三个，截取长度为三
-- position(a in b)获取字符串a在b中出现的位置
select position("abc"in"abcsk");
-- replace(str1,str2,str3)字符串替换,将字符串str1中的str2替换为str3
select replace("hello world",'hello','hi');
-- reverse() 字符串反转
select reverse("hello");
-- right() 返回字符串的后几个字符
select right("Hello",3
-- strcmp(),字符串比较
select strcmp("hello","Hello");
-- substr() 字符串截取
select substr("hello",2,3);
-- substring() 字符串截取，与上用法一样
-- ucase(),upper()将小写转大写
select ucase("Hello world");
select upper("Hello World");
-- lcase(),lower() 将大写转为小写
select lcase("HELLO");
select lower("WORLD");
-- 
 -- 日期函数
 select UNIX_TIMESTAMP(); -- 获取时间戳(毫秒值) 从 1970年
-- 将一个日期字符串转换为毫秒值
select UNIX_TIMESTAMP('2021-12-21 08:00:00'); --还是从1970年开始
-- 将时间戳转换为指定格式的·日期
select FROM_UNIXTIME(1924023030,'%Y-%m-%d %H:%i:%s')
-- 获取当前的年月日
select curdate()
select current_date();
-- 获取当前的时分秒
select current_time();
select curtime();
-- 获取当前时间和日期
select current_timestamp() 
-- 从日期字符串中获取年月日
select date('2022-12-12 12:34:56')
-- 获取日期之间的差值
select datediff("2021-12-23","2008-08-08");
-- 获取秒级别的差值
select timediff('12:12:34','10:18:56');
-- 将日期按照指定的格式进行转换(日期格式化)
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
-- 将字符串转换为日期
select str_to_date('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
-- 将日期进行减法
select date_sub('2021-10-01',interval 2 day);
select date_sub('2021-10-01',interval 2 month)
-- 将日期进行加法
select date_add('2021-10-01',interval 2 day)
-- 从日期获取指定的值
select extract(hour from '2020-1-10 11:11:11');
-- 返回给定日期的最后一天
select last_day('2021-08-11');
-- 获取指定年份和天数的日期
select makedate('2021',53); -- 获取2021年的第53天
-- 获取季度
select quarter('2021-12-13 11:12:13');
-- 获取英文名称的月份等等--
select monthname('2020-12-13 11:11；11');
select dayname('2020-12-13 11:11；11'); -- 获取周几(英文)
select dayofmonth('2020-12-13 11:11；11') -- 获取当月的第几天
select dayofweek('2020-12-13 11:11；11'); -- 数字状态
select dayofyear('2020-12-13 11:11；11'); -- 获取一年的第几天
-- 获取一年的第几周
select week('2020-12-13 11:11；11');
select now(); -- 获取当前时间