MySQL基础

最新推荐文章于 2024-06-28 15:42:05 发布

High_Gent

最新推荐文章于 2024-06-28 15:42:05 发布

阅读量245

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_29593573/article/details/74992988

版权

MySQL常用数据类型

bit：值为0/1（0为false、1为true）
整数：int
变长字符串：varchar，用时需赋值长度
定长字符串：char
decimal(M,D)：浮点数，(M,D)中M表示所有位数，D表示小数点后的位数
datetime：通常使用它表示时间（起始时间1000节省时间9999）
timestamp：时间（起始时间1970结束时间2037）

DDL

概念：数据定义语言（主要对表的结构进行操作）
操作：
- CREATE（创建）
  - 创建数据库：create database 数据库名
  - 创建表：create table 表名
  - 使用旧表创建新表
    - 不复制数据：create table 新表名 like 旧表名
    - 复制数据：create table 新表明 as select 旧表字段名 from 旧表名
  - 创建索引：create index 索引名 on 表名(表属性名)
  - 创建视图：create view 视图名 as select 表字段名 from (关联表)

/* 创建数据库 */
	create database sms;
	/* 使用sms数据库作为当前数据库 */
	use sms;
	/*创建表*/
	create table student(
		stu_no varchar(20) not null primary key,
		stu_name varchar(50) not null
	);
	
	/*描述表结构*/
	desc student;
	#使用旧表创建新表
	#复制值
	create table stumessage as select id,name,sex,birthday,height from stu;
	#不复制值
	create table stuname like stu;
	#创建索引
	create index name on stu(name);
	#重命名表
	rename table student to stu;
	#创建视图
	create view clazz_view as select stu.name as stu_name,
	clazz.name as class_name
	from stu,clazz
	where stu.clazz_id=clazz.id;

ALTER（修改）
- 添加列：alter table 表名 add column 字段名数据类型及长度是否允许为空等
- 删除列：alter table 表名 drop column 字段名
- 修改列
  - 改变类型及长度：alter table 表名
    modify column 字段名类型及长度;
  - 改名、改类型及长度：alter table 表名
    change column 旧字段名新字段名类型及长度;
- 添加主键：alter table 表名 add primary key 字段名（auto-increment自增的必须设置为主键）
- 添加外键：alter table 表名 add foreign key 字段名 references 关联表名 (字段名)

	alter table student#操作表 
	add column class#添加列名为class
	varchar(10) #声明数据类型为可变字符串
	not null#不允许为空
	commit'班级'#备注说明
	#删除列
	alter table student
	drop column class;
	#修改列属性及类型modify
	alter table student
	modify column name varchar(25)
	not null comment '姓名';
	#修改列，列名、属性及类型：change
	alter table student
	change column sex gender char(1)
	not null
	comment '性别';
	#添加主键
	alter table stu add parimary key id;
	#添加外键
	alter table course add foreign key (teacher_id)
references teacher(teacher_id);

DROP（删除）：drop table/view/index/database
- 删除表
- 删除数据库
- 删除列
- 删除视图
- 删除索引
DECLARE（声明）
https://blog.csdn.net/luyaran/article/details/80967331

DML

概念：数据操纵语言（操作表中的数据）
操作
- SELECT（查询）
  - 查询全部：select * from 表名
  - 查询某些字段：select 字段名（可用多个","隔开） from 表名
  - 查询带多个条件：select 字段名 from 表名 where 条件1 and/or 条件2
  - 去重复查询：select distinct
  - 排序：order by 字段名 asc（升序）/desc（降序）
  - 模糊匹配：like
  - between…and…区间选择
  - in指定几个选项
  - count统计条数
  - is null/is not null

	#MySQL 的 WHERE 子句的字符串比较是不区分大小写可使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
	select * from stu;
	select id,name from stu;
	select id,name,height from stu where height<1.80;
	select * from stu where clazz_id=2 and height>1.70;
	select * from stu where clazz_id>1 or height<1.75;
	select * from stu order by height desc , clazz_id asc;
	select distinct  clazz_id from stu  ; 
	#模糊匹配 like
	select * from stu where name like '张%龙';
	select * from stu where name like '张%';
	select * from stu where name like '%虎';
	select * from stu where name like '%一%';
	#between...and... 区间选择
	select * from stu where birthday between '1930-01-01' and '2000-01-01';
	# in 指定几个选项
	select * from stu where id in(1,2,5,6);
	# is null /is not null
	select * from stu where gender is not null;
	select * from stu where hometomw is null
	#count统计条数
	select count(*) from stu where class_id=1 and gender='男' and birthday > '2000-01-01';

复杂查询
- limit：限制显示条数

	# limit
	select * from score order by degree desc limit 3;
	select * from score order by sno;
	select * from score order by  sno limit 3,3;
	select * from score order by sno limit 0,5;
	select * from score order by sno limit 5,5;
	select * from score order by sno limit 10,5;
	#如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：
	SELECT * FROM runoob_tblORDER BY runoob_title;
	#如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：
	SELECT * FROM runoob_tbl ORDER BY CONVERT(runoob_title using gbk);
	# limit (n-1)*pageSize,pageSize
	#最基本的分页方式：SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
	SELECT * FROM score WHERE course_id = 123 ORDER BY sno LIMIT 50, 10
	#子查询的分页方式
	SELECT * FROM score WHERE course_id = 123 ORDER BY sno LIMIT 10000, 10
	#越往后分页，LIMIT 语句的偏移量就会越大，速度也会明显变慢。此时提高分页效率，大致如下：
	SELECT * FROM articles WHERE  id >=(SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

group by：分组
- having：用在group by之后的选择

	# group by
	##max sum min avg count
	select cno,max(degree) from score group by cno;
	select ssex,count(*) from student group by ssex;
	#1.求每个班有多少人
	select class,count(*) from student group by class;
	#2.求每科的平均成绩
	select cno,avg(degree) from score group by cno;
	select cno,avg(degree) from score where degree>80 group by cno;
	select cno,avg(degree) from score group by cno having avg(degree)<80;

union与union all：有all不去重，没all去重
- 子查询

#子查询
select s.SNAME from student s where s.SNO in (select sno from score c where c.DEGREE>80 and c.DEGREE<90);

四种连接
- inner join on
- left join on
- right join on
- full join

	SELECT *
	FROM websites w
	INNER JOIN access_log a ON w.id=a.site_id;
	SELECT *
	FROM websites w
	LEFT JOIN access_log a ON w.id=a.site_id;
	select * from  access_log a right join websites w on w.id=a.site_id ;
	#select * from  access_log a full join websites w on w.id=a.site_id;

多表关联from，取得笛卡尔积

#关联查询取得笛卡尔积
select * from stu,clazz where stu.class_id=clazz.id and stu.class_id=1;

DELETE（删除）
- UPDATE（更新）：update 表名 set 该字段数据

	#更新操作
	update stu set sex="女" where name="唐xx";
	select * from stu;

INSERT（插入）：insert into 表名 values()
- 全部
- 部分（非必填、自增、含默认值可省略）

	#插入
	insert into stu values(4,"唐xx","男","1993-05-23 12:00:00",1.70,4); 
	#非必填、自增、默认值
	insert into stu(name,clazz_id,sex,birthday,height) values("唐逛逛",3,"女","1993-05-23 12:00:00",1.65);

DCL

概念：权限操作
操作
- GRANT（授予权限）
- REVOKE（撤销权限）
- COMMIT（提交）
- ROLLBACK（回滚）

用户

创建用户：create user 用户名@localhost idtentified by ‘xxxx’
删除用户：drop user 用户名@localhost

drop user lucas@localhost;
create user lucas@localhost identified by 'xxx123';
	# 将test下的所有对象的所有权限赋给用户lucas
grant all on test to lucas@'localhost';
flush privileges;
show grants for lucas@localhost;
select * from book;
revoke all on test from lucas@'localhost';
revoke all on test.* from lucas@localhost;
flush privileges;
revoke all on test.book from lucas@localhost;
		drop user lucas@localhost;

日期计算

当前日期

select now();
select sysdate();
select current_timestamp;

格式化与解析

	#格式化与解析
select date_format(now(), '%Y-%m-%d %H:%i:%s');

select  date_format(str_to_date('2008/05/12','%Y/%m/%d'), '%Y-%m-%d %H:%i:%s');

select year(now());

select month(now());

select day(now());

日期计算

#日期计算
#date_add 加
select date_add(now(), interval 2 day);
select date_add(now(), interval 2 month);
select date_add(now(), interval 2 QUARTER);
select date_add(now(), interval 2 year);

#date_sub 减
select date_sub(now(), interval 2 day);
select date_sub(now(), interval 2 month);
select date_sub(now(), interval 2 QUARTER);
select date_sub(now(), interval 2 year);