马上要毕业了,以前学的东西有好些忘记了,现在复习下,学生时代就要结束了。。。
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按照功能分为下面三类:
- inner join (内连接 或 等值连接): 获取两个表中字段匹配关系的记录。
- left join (左连接): 获取左表所有数据,即使右表没有对应匹配的记录。
- 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
参考:菜鸟教程