MySQL复习1

马上要毕业了，以前学的东西有好些忘记了，现在复习下，学生时代就要结束了。。。

MySQL基本使用

之前因为Django2.1的原因，将电脑的MySQL版本升级到了8.0，所以这里使用的MySQL版本是8.0

登录MySQL，在cmd中输入下面命令

mysql -u root -p

1.创建，删除，选择数据库

create database 数据库名;    # 创建数据库
drop database 数据库名;    # 删除数据库
use 数据库名;    # 选择数据库

执行use命令后，你就已经成功选择了 test_mysql 数据库，在后续的操作中都会在 test_mysql 数据库中执行。

注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。

MySQL提供了多种不同的存储引擎，包括处理事务安全表的引擎和处理事务非安全表的引擎。在MySQL中，不需
要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一张表使用不同的存储引擎，可以使用SHOW E
NGINES语句查看系统所支持的引擎类型，MySQL8.0支持的存储引擎类型如下图：

从上图可以看到，MySQL8.0默认使用InnoDB引擎，InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，MySQL5.5之后，InnoDB 作为默认存储引擎。

 

2.创建数据表

创建数据表需要以下信息：表名，表字段名，定义每个表字段

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 test_mysql 数据库中创建数据表  test_tbl：(`` 符号不打没有影响)

CREATE TABLE IF NOT EXISTS `test_tbl`(
   `test_id` INT UNSIGNED AUTO_INCREMENT,
   `test_title` VARCHAR(100) NOT NULL,
   `test_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `test_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ps：MySQL命令的终止符为分号 ;         -> 是换行标识符

使用命令： show tables;  查看当前数据库的所有表， 使用 desc tables 来查看表结构

3.删除数据表

MySQL删除表的几种情况：

删除表全部数据和表结构，立刻释放磁盘空间：

drop table table_name;

 删除表全部数据，保留表结构，立刻释放磁盘空间 （id又从1开始自增）

truncate table table_name

删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间; (虽然没有释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间)

delete from table_name;

带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

delete from table_name where xxx;

delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

delete from test_tbl where test_title='小白';
optimize table test_tbl;

4.插入数据

insert into table_name (field1, field2) values (value1, value2);

insert into table_name values ('所有字段值都要对应写上');

4.查询数据

MySQL数据库中查询数据通用的  select 语法：

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]

查询语句中，可以使用一个或多个表，表之间使用逗号分割，并使用  where 语句来设定查询条件。

可以使用 WHERE 语句来包含任何条件。

可以使用 LIMIT 属性来设定返回的记录数。

可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

5.MySQL的 where 子句

where子句中可以指定任何条件，可以使用 and 或者 or 指定一个或多个条件， where 子句也可以运用于SQL的delete，update命令，where类似程序中的 if 条件。

=        # 等于
<>, !=   # 不等于
>        # 大于
<        # 小于
>=       # 大于等于
<=       # 小于等于

例如：

select test_title from test_tbl where test_title='小白'

6.MySQL  update查询

修改MySQL数据表通用 update SQL语法：

update table_name set field1=new_value, field2=new_value [where Clause]

如果不加where子句指定，修改的是所有字段值，where子句中可以指定任何条件。

例如：

update test_tbl set test_title='新略略路' where test_id=5

7.MySQL  like 子句：

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 * ;

例如：

'%a'     # 以a结尾的数据
'a%'     # 以a开头的数据
'%a%'    # 含有a的数据

如果没有使用 % ，like子句的效果和 = 一样；

MySQL select 语句使用 like 子句 通用语法：

select field1,field2 from table_name where field1 like condition [and] [or] filed2='****'

ps：[ ] 中的参数是可选参数

8.MySQL union操作符

MySQL union操作符用于连接两个以上的 select 语句的结果组合到一个结果集合中。多个 select 语句会删除重复的数据。

操作语法如下：

select expression1, ... expression_n from tables [where conditions] 
union [all] 
select expression1, ... expression_n from tables [where conditions] 

参数：

expression ： 要查询的列， tables： 查询的表，[where ]：可选的筛选条件，[all]：可选，返回所有结果，包括重复

案例：查询两个表的country字段，去重，然后对该字段排序

如果加上可选参数 all 结果如下，不会去除重复数据

9.MySQL 排序

上面案例就有关于排序  order by 的操作，很简单，语法如下：

select field1, field2 from table_name1, table_name2 order by field1,...  [asc [desc]]

asc：升序， desc：降序。

10.MySQL   group by 语句

group by 语句 根据一个或多个列对结果进行分组，在分组的列上可以使用 count， sum， avg等函数。

select column_name, function(column_name) from table_name 
where column_name operator value 
group by column_name;

案例：使用group by 将数据表按名字进行分组，并使用 Count() 统计每个人有多少条记录：

使用 with rollup 实现在分组统计数据的基础上再进行相同的统计(sum , avg, count...)

例如：在上面数据，按名字分组，然后再统计每个人的登录次数：

ps: sum是求和的，as 是对sum(singin) 起别名， null表示的是所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总登录数代替：

11.MySQL 连接的使用 *

真正的应用中经常需要从多个数据表中读取数据，join可以让我们再两个或多个表中查询数据。

在 select， update， delete中都可以使用join来联合多表查询。

join按照功能分为下面三类：

1. inner join (内连接 或 等值连接)： 获取两个表中字段匹配关系的记录。
2. left join  (左连接)： 获取左表所有数据，即使右表没有对应匹配的记录。
3. right join (右连接)：和 left join 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

使用菜鸟教程的数据，结构如下：

 inner  join 

select a.runoob_id,a.runoob_author, b.runoob_count from runoob_tbl a 
inner join tcount_tbl b 
on a.runoob_author=b.runoob_author;

使用MySQL的 inner join（只写join也没有问题）来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

解释：这里将runoob_tbl表取名为 a，tcount_tbl 取名为 b， 查询a的runoob_id和runoob_author字段和b的runoob_count字段，条件是这两个表的runoob_author字段相等，上面SQL语句和下面这个语句等价

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

由此可见：inner join 取的是两个表的交集

left join

select a.runoob_id,a.runoob_author, b.runoob_count from runoob_tbl a 
left join tcount_tbl b 
on a.runoob_author=b.runoob_author;

在这里以 runoob_tbl 在前面 ，所以 runoob_tbl 为左表，tcount_tbl 为右表

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

所以即使tcount_tbl表没有FK ，查询结果也有FK， 但是值为NULL

right join

select a.runoob_id,a.runoob_author, b.runoob_count from runoob_tbl a 
right join tcount_tbl b 
on a.runoob_author=b.runoob_author;

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

这里 runoob_tbl 中runoob_count值为22的 runoob_author 值为 Google， 但是tcount_tbl中没有Google这个runoob_author值，所以查询的id ，author 值都为NULL

 

参考：菜鸟教程