MYSQL学习笔记

运行环境：win10

登陆

1.确保本地mysql服务器已经启动

查看方法：运行命令窗口（快捷键win+R）输入：services.msc   就可找到mysql

若其关闭，可手动将其打开 或 以管理员身份运行命令提示符 输入：net start mysql来启动mysql服务

2.命令行界面登陆

输入指令： mysql -h hostname -u username -p

这个命令将调用MYSQL监视程序。

-h命令指定运行MYSQL服务器的机器。

-u命令指定连接数据库时使用的用户名称。如若不指定，默认值是登陆该操作系统时使用的用户名。

-p密码指令。

若登陆成功，则会出现如下字样：

查询当前数据库的信息

输入\s来查看当前数据库的信息（如下可知当前数据库Current database是没有选择的）

输入create database database_name; 来创建数据库(注意：最后要加分号)

输入use database_name; 来使用数据库

如下我使用了test数据库：

输入show databases;来查看系统有多少个数据库

查看当前数据库有多少个表：show tables;

查看某一表的结构：

有以下四种查询方法：

第一种：describe table_name; 

第二种：desc table_name;

第三种：show columns from table_name;

第四种：explain table_name;

多表连接查询

以下例子使用的表结构为上面的四个表。

例子一：要查看顾客Julie Smith的订单

可使用嵌套子查询:

select orderid,amount,date  from orders where customerid = (select customerid from customers where name = 'Julie Smith');

或者使用多表连接查询，以下为简单的双表关联：

（表名之间的逗号等价于输入INNER JOIN或CROSS JOIN。这是一种关联类型，有时也称为完全关联(full join)或表的笛卡尔乘积）

select orders.orderid,orders.amount,orders.date from orders,customers where customers.name = 'Julie Smith' and customers.customerid = orders.customerid;

以上俩中方法的查询结果一模一样

顺便在此说一下USING子句的使用，如以上的双表关联，可用USING子句改写为：

select orders.orderid,orders.amount,orders.date from orders inner join customers using(customerid)where customers.name = 'Julie Smith';

优点：USING子句不需要指定连接属性所来自的表。

使用USING子句的注意事项：

1、两表所要连接的属性列必须有同样的名称，如上，两表中都有customerid

2、两表的关联必须写明关联内容，如上的from orders,customers须换成from orders inner join customers

3、using所替换的语句中原本须是用on连接的，如上，原本为on customers.customerid  = orders.orderid

例子二：想要知道哪些顾客订购了关于Java的图书：

（思路与双表关联一样，只是这个查询把四个表关联起来而已）

select customers.customerid,customers.name,customers.address,customers.city from customers,books,orders,order_items where books.title LIKE '%Java%' and books.isbn = order_items.isbn and order_items.orderid = orders.orderid and orders.customerid = customers.customerid;

例子三：想知道哪些顾客从来没有订购过一次，即没有订单。

实现此类查询最简单的方法就是使用左关联(left join)。

左关联是在两个表之间指定关联条件下匹配的数据行，如果右边的表中没有匹配的行，会以NULL值参与匹配。

select customers.customerid,customers.name,orders.orderid from customers left join orders on customers.customerid = orders.customerid;

如上图所示，orderid为NULL的就是从来没有订购的顾客，

但我们只需要查看这些没有订购任何商品的顾客，就可以使用 IS NULL语句：

select customers.customerid,customers.name from customers left join orders on customers.customerid = orders.customerid where orders.orderid is NULL;

别名的使用(Aliases)

我们可以对列名和表名起一个别名，他们用as 或是空格 进行连接。

优点：1、便于记忆，简化操作

用途：当关联一个表到表本身的时候就必须使用表别名

例子：想要查找住在同一城市的顾客。

select c1.name,c1.cityfrom customers as c1,customers as c2where c1.city = c2.cityand c1.name != c2.name;

order by 以特定顺序获取数据

默认升序ASC

还可以用DESC指定它为降序

聚合函数（常用于GROUP BY从句的SELECT查询）

AVG(column):指定该列的平均值

MIN(column):指定该列的最小值

MAX(column):指定该列的最大值

SUM(column):指定列该列的所有值的和

COUNT(column/*):如果指定一列，则给出本列中非空(NULL)值的行数；

                              如果在列前加DISTINCT，将得到本列中非空不同值的行数；

                              如果指定为COUNT(*)将得到包含空值(NULL)的行的行数，即得到这个表的行数

GROUP BY子句

当聚合函数遇上group by子句的时候，它实际上改变了聚合函数的行为。

如查询每个顾客订单总量的平均值：

select customerid,avg(amount)from ordersgroup by customerid;

但是仔细想想，如果上述查询去掉了group by customerid这一句，那么猜想一下结果，每个customerid对应的avg(amount)值应该是什么？

HAVING子句

增加having子句的原因是：where关键字无法与聚合函数一起使用。所以having与where类似，都是对结果集的筛选。

例如：希望知道哪些顾客的平均订单金额超过50：

select customerid,avg(amount)from ordersgroup by customeridhaving avg(amount) > 50;

LIMIT子句
跟where子句一样都是对结果集进行筛选，但是比where更直接。
语法：limit m
意思：从结果集返回m行
语法： limit m,n
意思：从结果集的第m行开始，返回n行。（行号开始的索引都是从0开始的）
用途：分页的时候可用，比如每页显示10个项目之类的。
例：select * from customers limit 2,3;

子查询
常见用法：用一个查询结果作为另一个查询的比较条件。
例：查询金额最大的订单

select customerid,amount from orderswhere amount = (select max(amount) from orders);

但MYSQL很长时间没有采纳子查询的主要原因在于，多数查询可以在没有子查询的情况下完成。

如上面的例子可以换另一种写法

select customerid,amount from ordersorder by amount desclimit 1;

由于它依赖LIMIT这个查询与大多数RDBMS并并不兼容，但在mysql中，它的执行比子查询版本的查询效率更高。

子查询操作符

一般规定了子查询只能返回一个标量值。但是，如果子查询返回的是一个集合，怎么办？

我们就可以使用子查询操作符来对结果集进行判断

使用ANY进行子查询

any的意思比较好明白，直译就是任意一个，只要条件满足任意的一个，就返回TRUE。

any关键字必须与比较操作符一起使用（>,<,=）

使用IN进行子查询

使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。

in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。

有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思。

使用SOME进行子查询

some是any的别名，用的比较少。只需要理解any的意思就好了

使用ALL进行子查询

all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。

all关键字与any一样，必须与比较操作符一起使用。

<>all的同义词是not in，表示不等于集合中的所有值，这个很容易和<>any搞混。

关联子查询

在关联子查询中，可以在内部查询使用外部查询的结果。

例如查询没有被订购的书

select isbn,title from books where not exists (select * from order_items where order_items.isbn = books.isbn);

这与关联左查询相同，如上例子用关联左查询的写法：

select books.isbn,books.title from books left join order_items using(isbn) where orderid IS NULL;

表的删除

删除整个表：drop table table_name;

删除整个表的内容，但不删除表：delete from table_name;(可被ROLLBACK)

                                                truncate table_name;(不可被ROLLBACK)