MySQL数据库笔记

MySQL

1.安装和使用

使用mariadb 也是(mairieDB)一个数据库,MariaDB数据库管理系统是MySQL的一个分支。

2.数据类型

• MySQL支持多种类型，大概分为三类：数值、日期、字符类型 。
• char和varchar一定要指定长度，float会自动提升为double，timestamp是时间的混合类型

类型	用途
int	整型
bigint	整型 long
float	浮点数
double	浮点数
datetime	日期类型 date:2021-09-20 time:00:00:33
timestamp	日期类型（包含时间戳）
char	定长字符
varchar	不定长字符
text	大文本，存储很长的字符
blob	字节数据类型，存储图片、音频等文件

3.建表操作

语法

-- 删除表
DROP TABLE IF EXISTS 表名;
-- 新建表
CREATE TABLE 表名(
	字段名 类型 约束（主键、非空、唯一、默认值）,
    字段名 类型 约束（主键、非空、唯一、默认值）,
)编码,存储引擎;

在 SQL 中，约束：

• 主键约束（PRIMARY KEY,）: 主键是表中用来唯一标识每一行的列，它的值不能重复且不能为空。
• 唯一约束（UNIQUE）: 确保表中的某个列的值是唯一的，不允许重复。
• NOT NULL : 不能存储NULL值。
• 外键约束（FOREIGN KEY (table1_id) REFERENCES table1(column1)）: 外键约束用于在两个表之间建立引用关系，确保一个表中的值在另一个表中必须存在。外键关系用来维护表之间的数据一致性。
• 检查约束（CHECK）: 检查约束用于限制某一列的值必须满足特定的条件。可以使用逻辑表达式来定义检查约束。
• 默认约束（DEFAULT default_value）: 默认约束用于在插入新行时为某一列提供默认值，如果在插入数据时未指定该列的值，则会自动使用默认值。

案例

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites`(
		id int(11) NOT NULL AUTO_INCREMENT,
		webname char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
		url varchar(255) NOT NULL DEFAULT '',
		alexa int(11) NOT NULL DEFAULT '0' COMMENT 'ALEXA排名',
		sal double COMMENT '广告收入',
		country char(10) NOT NULL DEFAULT '' COMMENT '国家',
		PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.插入、删除、更新

插入语句

INSERT INTO websites(webname,url,alexa,sal,country) VALUES ('腾讯','https://www.qq.com',18,1000,'CN');

删除语句

delete from websites where id=5;

更新语句

update websites set sal=null where id=3;

5.基本select查询语句

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites`(
		id int(11) NOT NULL AUTO_INCREMENT,
		webname char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
		url varchar(255) NOT NULL DEFAULT '',
		alexa int(11) NOT NULL DEFAULT '0' COMMENT 'ALEXA排名',
		sal double COMMENT '广告收入',
		country char(10) NOT NULL DEFAULT '' COMMENT '国家',
		PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
INSERT INTO websites(id,webname,url,alexa,sal,country) VALUES 
		(1,'腾讯','https://www.qq.com',18,1000,'CN'),
		(2,'Google','https://www.google.com',1,1000,'USA'),
		(3,'淘宝','https://www.taobao.com',13,1000,'CN'),
		(4,'菜鸟','https://www.runoob.com',4689,1000,'CN'),
		(5,'微博','https://www.weibo.com',3,1000,'CN');

CREATE TABLE IF NOT EXISTS `access_log` (
		`aid` int(11) NOT NULL AUTO_INCREMENT,
		`site_id` int(11) NOT NULL DEFAULT '0' COMMENT '网站id',
		`count` int(11) NOT NULL DEFAULT '0' COMMENT'访问次数',
		`date` date NOT NULL,
		PRIMARY KEY (`aid`)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`) VALUES
		(1,1,45,'2016-05-10'),
		(2,3,100,'2016-05-13'),
		(3,1,230,'2016-05-14'),
		(4,2,10,'2016-05-14'),
		(5,5,205,'2016-05-14'),
		(6,4,13,'2016-05-15'),
		(7,3,220,'2016-05-15'),
		(8,5,545,'2016-05-16'),
		(9,3,201,'2016-05-17'),
		(10,88,9999,'2016-09-09');

查询语句

-- 很少使用*作为查询
select * from websites

-- 使用字段
select id,webname,url,alexa,sal,country from websites

查询结果会自动显示出来

6.分页查询

select * from websites limit 2,3;-- 从第二条开始，查三条数据

7.distinct关键

有重复的话，使用distinct除去重复项

select distinct country from websites

8.where语句

作为条件筛选，运算符：> ,< ,>=, <= , <>, =

select * from websites where sal >500

9.逻辑条件： and、or

select * from websites where sal >=0 and sal <=2000;
select * from websites where sal between 0 and 2000;
select * from websites where sal <5 or sal is null;-- null的判断用is null 或is not null

10.order by

排序：默认情况下是升序，asc可以省略

select * from websites order by sal asc,alexa desc;-- 现根据sal 升序排列，在根据alexa降序排列

11.like和通配符

• like模糊查询
• 通配符
  • %:0个或多个字符 select * from websites where webname like '%o%' 多个含有o的
  • _:1个字符 select * from websites where webname like '_o%' 1个含有o的

12.in

• 匹配多个条件

select * from websites where country in ('USA','CN','鸟国');

• 等价于

select * from websites where country ='USA' or country ='CN' or country ='鸟国';

13.别名

select tt.webname '网站名字' from websites tt

14.Group by分组

分组时候的筛选用having

常见的几个组函数： max() min() avg() count() sum()

select avg(sal) aa from websites where sal is not null group by country having aa>1500

15.子查询

一个嵌套在主查询中的查询语句。

子查询的一般结构如下：

SELECT column1, column2, ...
FROM table_name
WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

其中，括号内的部分就是子查询。子查询可以用在多个地方，如：

1. 在 SELECT 语句中用于计算某个列的值：

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

2. 在 WHERE 子句中用于过滤结果：

SELECT name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students);

3. 在 FROM 子句中用于创建临时表：

SELECT name, total_sales
FROM (
    SELECT customer_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY customer_id
) AS customer_sales;

子查询的性能可能不如连接（JOIN）操作或其他优化查询，因此在使用子查询时要谨慎，确保它能够高效地执行。

16.连接查询

select webname,count,date from websites w , access_log a where w.id = a.site_id; -- 这是 1992的语法
-- 过时👆

-- join连接表 
select webname,count,date from websites w inner join access_log a 
on w.id = a.site_id; -- 这是1999 年的语法，推荐使用


select webname,count,date from websites w left outer join access_log a on w.id = a.site_id;-- 把没有访问的网站也显示出来 -- 注意:inner 和 outer 是可以默认省略的 左外连接，右外连接 

名字和访问记录一起查出来

17.NULL处理

select webname,ifnull(count,0),ifnull(date,'') from websites w left outer join access_log a 
on w.id=a.site_id

18.有关题目

1. MySQL是一种关系型数据库管理系统。

2. Mysql中表student_table(id,name,birth,sex)，插入如下记录：

   ('1004' , '张三' , '2000-08-06' , '男');
   ('1005' , NULL , '2001-12-01' , '女');
   ('1006' , '张三' , '2000-08-06' , '女');
   ('1007' , ‘王五’ , '2001-12-01' , '男');
   ('1008' , '李四' , NULL, '女');
   ('1009' , '李四' , NULL, '男');
   ('1010' , '李四' , '2001-12-01', '女');
   

   执行

   select t1.*,t2.*
   from (
   select * from student_table where sex = '男' ) t1 
   right join 
   (select * from student_table where sex = '女') t2 
   on t1.birth = t2.birth and t1.name = t2.name ; 的结果行数是（）？
   

   的结果行数是（）？4