博客源为书写的载体,书写以对思维的缓存 正文

MySQL数据库(上)


工作中离不开数据库,而不同的工作领域对数据库SQL语句运用的侧重点又不同,再加上各种数据库管理工具以及框架,久而久之会让我们对原生SQL语句的掌握变得生疏、片面化。所以让我们一起复习,加深印象。

一:MySQL数据库的安装与配置

1.安装

Linux:
执行命令:yum -y install mysql mysql-server

Windows:
官网下载地址:http://dev.mysql.com/downloads/mysql/

2.启动

Linux:
-- service mysql start      # 开启服务
-- service mysql restart    # 重启服务
-- service mysql stop    # 关闭服务
-- chkconfig mysqld on      # 设置开机自启

Windows:
快捷键Win+R调出命令窗口,输入services.msc进入到服务管理窗口,找到MySQL,点击运行。也可右击鼠标-->属性-->启动类型,来设置开机自启。

3.查看服务是否开启

Linux:
-- ps aux |grep mysql     # 查看进程
-- netstat -an |grep 3306 # 查看端口

Windows:进入cmd
-- net start    # 显示所有启动的服务信息

4.设置/更改/忘记 登录密码

Linux/Windows:
设置初始密码:
-- mysqladmin -uroot password '123'   # 设置初始密码

修改密码:
登陆MySQL终端,运行如下命令:
mysql> set password = password(‘123456‘);
mysql> flush privileges;

忘记密码:官方教程

5.登录

Linux/Windows:
-- mysql -u用户名 -p密码
举例:
>>>mysql -uroot -pmysql
如果不想暴露密码则输入:
>>> mysql -u用户名 -p
>>> *****

远程登录:
>>> mysql -h IP -P 端口 -u用户名 -p密码    # 注意:IP和端口前有空格

6.退出

-- quit; 或者exit; 或者\q;

二:MySQL数据类型:

1.数值型:

877318-20170129165834706-1279238667.png

2.日期和时间类型

877318-20170129165909409-310855737.png

关于日期类型有个知识点:

已知出生年月日(DATE类型),如何得到年龄?

第一种方式:通过年份来得到(粗略计算)
语句:
SELECT
   name,birth,(YEAR(CURDATE()) - YEAR(birth))
FROM Employee;
-- CURDATE():当前时间的年月日,与now()类似的用法
-- YEAR():提取日期的年部分
所以通过当前的年份减去表中birth的年份就能得出了年龄,但忽略了月份和日期,是不准确的。

第二种方式:通过年月日来精确计算
语句:
SELECT
   name,birth,((YEAR(CURDATE())-YEAR(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5))
FROM Employee;
-- RIGHT(str,len):返回字符串str的最右面len个字符
-- 比较运算符条件为真返回1,为假则返回0
-- (RIGHT(CURDATE(),5)<RIGHT(birth,5):如果当前日期早于birth,则年份应该减1,否则不变。

3.字符串类型

877318-20170129170151472-2101364934.png

老生常谈:char、varchar的区别是什么?

char长度是固定的,不管你存储的数据是多少字符长度他都会都固定的长度。而varchar则处可变长度但他要在总长度上加1字符,这个用来存储位置。由于char固定长度,所以在处理速度上要比varchar快速很多,但是对费存储空间。所以对存储不大,但在速度上有要求的可以使用char类型,反之可以用varchar类型来实例。

三:SQL

SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言,也是我们最需要掌握的知识点。SQL语言共分为四大类:数据定义语言DDL,数据操纵语言DML,数据查询语言DQL,数据控制语言DCL。

1.DDL

DDL:定义或改变表的结构、数据类型、表之间的链接和约束等初始化工作,主要命令为CREATE、ALTER、DROP等。

2.DML

主要用来对数据库的数据进行一些操作,常用的就是INSERT、UPDATE、DELETE。

3.DQL

数据检索语句,用于从表中获取数据。通常最常用的为SELECT并且常与FROM子句、WHERE子句组成查询SQL查询语句。

4.DCL

数据库控制功能,是用来设置或更改数据库用户或角色权限的语句,包括grant、deny、revoke等语句。在默认状态下,只有sysadmin、dbcreator、db_owner或db_securityadmin等人员才有权限执行DCL。

注:

SQL是一个标准,每个数据库服务器都在标准的基础上进行了相应的调整和扩展。在MySQL中,SQL通常分为DDL、DML和DCL,还包括一些其它语句类别。MySQL将SELECT与INSERT、UPDATE、DELETE划分到了DML中。

四:MySQL命令大全

1.操作数据库

1.创建数据库:
   create database if not exists 数据库名 charset=utf8;

2.删除数据库:
   drop database 数据库名;

3.切换数据库:
   use 数据库名;

4.查看数据库的创建信息:
   show create database 数据库名;

5.修改数据库编码方式:
   alert database 数据库名 character set = 编码方式;

6.查看当前所有的数据库:
   show databases;

7.显示当前版本:
   select version();

8.显示当前日期时间:
   select now();

9.显示当前用户:
   select user();

10.查看当前使用的数据库:
   select database();

11.显示具体警告信息:
   show warnings;

2.操作表

1.查看当前数据库的所有表:
   show tables;
 查看其他数据库的所有表:
   show tables from 数据库名;

2.创建表:
   create table 表名(
       字段 字段类型 [约束条件],
       ......
   );
 举例:
   create table student(
       id int auto_increment primary key not null,
       name varchar(10) not null,
       gender bit default 1,
       birthday datetime
   );
 注意:
   auto_increment必须配合主键设置,非主键不能定义。

3.查看表结构:
   desc 表名;
 查看表的索引:
   show indexes from 表名\G;
 查看表的创建语句:
   show create talbe 表名;

4.修改表结构:
   (1).添加/删除字段
   alter table 表名 add/drop 字段名 类型;
   举例:
       alter table student add isDelete bit default 0;
   添加主键:
       alter table student add primary key(id);
       Alter table student change id id int(10) not null auto_increment=1;
   删除主键:
       alter table student drop primary key;

   (2).修改字段
   alter table 表名 modify 字段名称 字段类型 约束;
   举例:
       alter table student modify isDelete bit default 0;

   (3).修改字段名:
   alter table 表名 change 原字段名 新字段名 字段类型 约束;
   举例:
       alter table student change name username varchar(3) not null;

5.删除表:
   drop table 表名;

6.更改表名:
   rename table 原表名 to 新表名;
   
7.修该表所用的字符集:
     alter table 表名 character set utf8;

3.操作数据

(1).

1.全列插入:insert into 表名 values(值一...),(值二...);
   举例:insert into student values(1,'史强',1,'1990-1-1',0);
   注意:全列插入values()中所有的字段都必须插入值且顺序要一致,无论是自增还是设置了默认值

2.缺省插入:insert into 表名(字段1,...) values(值一,...)
   举例:insert into student(name,age) values("张三",45);
   注意:缺省插入values()中的值需要与前面列里的值对应

3.多条插入:insert into 表名(字段1,...) values(值一,...),(值二,...)
   举例:insert into student(name,age) values("AA",25),("程心",23);

(2).

语法:
delete from tab_name [where ....];
举例:
delete from employee where name='泰勒';   # 删除表中所有name为'泰勒'的记录
注意:
1.如果不设置where条件则删除表中的所有数据。
2.delete语句只能删除表中的内容,不能删除表本身,想要删除表用drop。

扩展:删除表中所有数据的方式
1.delete from 表名;
2.truncate table 表名;
两种区别:
delete方式是一条一条数据的删除,而truncate是删除表,再新建表。truncate方式删除的数据不能在事务中恢复。如果数据量特别大的话就选择truncate方式,效率更高。

(3).

语法:
update 表名 set field1=value1,field2=value2,...[where ....]
举例:
update employee set name='雷迪亚兹' where name='独裁者';
注意:
如果不设置where条件,则修改所有字段。

(4).查(单表查询)

1.基本语法:
   select *|field1,filed2... from 表名;
   
2.消除重复行:
   select distinct *|field1,filed2... from 表名;
   
3.使用表达式
   # 在所有学生分数上加10分(不更改数据,只作展示)
   select name, Chinese+10, Math+10, English+10 from Student;
   
4.字段别名
   # 语法:字段 as 别名
   select name as 姓名, Chinese+Math+English as 总成绩 from Student;
   注意:as也可省略,字段和别名用空格隔开

5.where语句+比较运算符
   # 比较运算符:> < >= <= <> !=
   select name, Math from Student where Math>90;
   
6.where语句+逻辑运算符
   # 逻辑运算符:and(并且)、or(或者)、not(取反)
   select name from Student where Math>90 and English>80;
   
7.where语句+模糊查询
   # 关键字:"like"、"%":匹配任意的多个字符、"_":匹配一个任意字符
   select name from Student where name like "黄%";    # 匹配以黄开头的任意名字
   select name from Student where name like "_黄_";   # 匹配长度为3且中间是黄字的名字
   
8.使用正则表达式查询
   # 关键字:regexp
   select * from Student where name regexp '^李';    # '李'开头的名字
   select * from Student where name regexp '雷$';    # '雷'结尾的名字
   select * from Student where name regexp 'm{2}';   # 包含两个m的名字
   
9.where语句+范围查询:
   1.关键字:"in":在一个非连续的范围内
   select * from Student where id in(2,7);    # 查询id为2和id为7的数据

   2.关键字:"between...and...":表示在一个连续的范围内
   select * from Student where id between 2 and 3;    
   表示查询id在2-3(包括2和3)之间的数据
   select * from Student where id between 3 and 8 and gender='男';
   表示查询id在3-8(包括3和8)之间的男生的数据
   注意:
   1.between a and b中a必须要大于b
   2.逻辑运算符"and"的优先级没有"between...and..."高
   
10.where语句+空判断:
   # 关键字:is null
   select * from Student where birthday is null;      # 查询生日没有填写的数据
   # 关键字: is not null
   select * from Student where birthday is not null;  # 查询填写了出生年月日的数据
   
11.聚合函数:快速得到统计数据,只会得到聚合的结果,不会得到原始的数据
   1.count()--计算总个数
   举例:
   select count(*) from Student;    # 查询共有多少条数据
   注意:count(*)统计所有行;count(字段)不统计null值
   
   2.max()--求最大值
   举例:
   select max(Math) from Student where gender='女';    # 查询女生中数学最高的分数

   3.min() --求最小值
   举例:
   select min(Math) from Student where gender='女';    # 查询女生中数学最低的分数
   注意:min(字段)也不统计null值

   4.sum() --求和
   举例:
   select sum(English) from Student where gender='男';  # 查询男生英语总成绩
   注意:sum()仅对数值起作用,否则会报错,其它几个函数处理非数值的数据不报错。

   5.avg() --求平均值
   举例:
   select avg(Chinese) from Student where gender='女';  # 查询女生语文成绩的平均值
   注意:avg()对值为null的数据不会计算进去。
   
   6.扩展:ifnull()
   null和所有的数计算都是null,如果有字段值为null需要用ifnull将null转换为0!
   语法:
   ifnull(字段,0)
   举例:
   select avg(ifnull(Math,0)) from Student where gender = '男';
   
   7.总结
   对于聚合函数的使用可能会让你感到头晕,我的心得就是:先不要管聚合函数要干嘛,先把要求的内容     查出来再包上聚合函数即可。

12.分组查询:按照字段分组,表示此字段相同的数据放到一个组里。分组的目的就是为了聚合。
   关键字:group by
   语法:
   select 字段1,字段2,聚合... from 表名 group by 字段1,字段2;
   举例:
   select gender,count(*) from Student group by gender;  # 分别查询男生和女生的人数
   注意:
   select后面的字段必须是group by后面的字段中的

13.筛选:对group by分组集进行筛选,having必须依赖分组group by
   关键字:having
   语法:
   select 字段1,...,聚合... from 表名 group by 字段1,... having 字段;
   举例:
   select count(*) from Student group by gender having gender='女';
   # 统计出女生的人数
   扩展:
   having和where两者都可以对查询结果进行进一步的过滤,差别有:
       (1)where语句只能用在分组之前的筛选,having用在分组之后的筛选
       (2)使用where语句的地方都可以用having进行替换
       (3)having后面可以用聚合函数,where后面就不行
       
14.排序:为了方便查看数据,可以对数据进行排序
   关键字:order by; asc--升序(默认), desc(降序),
   语法:select * from 表名 order by 字段1 asc/desc;
   举例:
   select * from Student where gender='男' order by Math desc;
   # 查询男生的信息,按数学分数降序排列
   
15.分页:当数据量过大时,在一页中查看数据非常麻烦
   关键字:limit
   语法一:
   select * from 表名 limit count;    # 表示取count条数据(从第一条数据开始算)
   举例:
   select * from Student limit 5;
   语法二:
   select * from 表名 limit start,count;
   表示从start开始,获取count条数据,start索引从0开始
   举例:
   select * from Student limit 2,5;
   表示从索引为2的位置(包括2的位置)开始共获取5条数据
   直观来看就是start+1--start+count条即第3--第7条数据

16.总结:
   1.完整的select语句:
   mysql> select distinct *|字段|聚合函数 from 表名
       -> where ...
       -> group by ... having 聚合函数 ...
       -> order by asc|desc
       -> limit start,count;
   2.执行sql语句时的执行顺序:
   from 表名-->where-->select-->group by-->having-->order by-->limit

五:未完待续

暂告一段落,欲知后事如何,且看下回分解:MySQL数据库(下)

六:最后

6ee46461878d4ae9.jpg

    • 曹达华10 2019年3月21日 12:07
    • 楼下有个2元店, 整天放个大喇叭: “本店清仓处理,一律两块钱,两块……两块…… 两块钱你买不到吃亏,两块钱你买不了上当,两块钱你就能买走……本店任一商品……” 如此循环着, 搞得我睡不着觉。 我走进店里,扔给老板2元钱,把大喇叭抱走了……
    • 鬼王达5号: 2019年4月5日 21:26
    • 牛逼