兄弟连mysql数据库_兄弟连学python-------MySQL数据库基础知识

1.数据库系统(database system)

数据库系统是计算机系统中一种专门管理数组资源的系统，数据库存储的是一组或多组经过处理后的数据，管理这个数据库的软件成为数据库管理系统。

组成：

数据库(database) 存数据的

数据库管理系统(database managment system) 管理数据库

MySQL数据库作用:

​ 1) 持久保存

​ 2) 方便查询并提取满足条件的数据, 数据访问速度快

​ 3) 处理并发访问

​ 4) 权限管理

2.数据库系统分类：

关系型数据库 : MySQL、SQLServer、oracle、db2、informix、sysbase

非关系型数据库:redis、 MogoDB

3.开发中常用的数据库

IBM：DB2

甲骨文：Oracle MySQL

Microsoft：SQL Server Access等等

4.数据库在动态网站中有什么地位？

1、动态网站是对数据进行操作。浏览网站的时候会发现网页内容变化，页面的布局主体没有变化。

2、WEB系统的开发基本都离不开数据库，所谓的动态网站是基于数据库开发的系统，最主要的是围绕数据库来写程序(业务逻辑)。

5.MySQL和mysql有什么区别：

MySQL指的是完整的数据库系统。

mysql指的是一个叫mysql的客户端程序。

6.MySQL数据库的结构：

数据库->数据表->字段(多个字段组成了一行数据)

数据库：存放信息的文件夹

数据库里面有一个一个的excel文件(数据表)

数据表里面的数据是有数据行和数据列构成

所看到的一行一行的数据是由一个或者多个字段组成的

7.SQL：为了和MySQL进行交互，需要使用一种叫SQL(结构化查询语言)的语言来进行交互。SQL是当今的标准的数据库语言，很多数据库都使用SQL作为交互语言：SQL Server、Oracle

数据定义语言(DDL)：定义和管理数据对象，比如建立数据库、数据表

数据操作语言(DML)：用于操作数据库对象中包含的数据。

数据库查询语言(DQL)：用于查询数据库对象中包含的数据，能够对表进行一个查询。

数据控制语言(DCL)：管理数据库的语言

8.连接数据库的步骤

1、连接MySQL服务器

2、选择数据库

3、对数据表进行增删改查。

4、关闭数据库

9.连接数据库

mysql

-u 用户名 root

-p 密码

-h 主机名

-P 端口号 3306

数据库语法的特点：

mysql>

1、每个SQL命令都要使用分号来完成。

2、->表示MySQL需要你继续输入命令。

3、如果说有一条比较长的命令我就可以拆分成多行来执行。

4、'>表示等待下一行，等待以单引号开始的字符串的结束

5、">表示等待下一行，等待以双引号开始的字符串的结束。

6、查询的命令不区分大小写，通常使用大写字母来写出SQL关键字和函数名，用小写字母写出数据库、数据表和数据列的名字

10.退出MySQL

quit

exit

\q

常见操作

\c 取消命令输入

\g 代替结束符;

\s 查看服务器端信息

\h 查看帮助

11.创建数据库

格式:

create database if not exists 数据库名 default charset utf8;

注意：

1、数据库是唯一的

2、if not exists先判断是否存在这个数据库，存在不创建，不存在就创建。

3、创建数据库，并设定编码集为utf8

12.显示当前数据库服务器下所有的数据库名

show databases;

use 数据库名 选择数据库

注意：

windows下面数据库名是不区分大小写的，在Linux下面严格区分。

13.删除数据库

drop database 数据库名

查看已经选择的数据库

select database();

查看当前的数据库的版本号

select version();

14.数据表的操作

show tables 查看数据库中的数据表。

创建数据表

格式:

create table 表名(列的一些信息);

例子:

create table t1(id int(12), name varchar(100));

create table `test t1`(id int(10), name varchar(50));

desc 表名 查看表的结构.

show create table 表名 查看建表的语句

\G 格式化输出(文本式，竖立显示)

drop table 数据表名

drop table [if exists] 数据表名 尝试性的删除表。

15.记录操作 增、删、改、查

(1)插入数据

格式:

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3);

(2)查询表数据

格式:

select * from 表名;

select 字段1,字段2,字段3 from 表名;

select * from 表名 where 字段=某个值;

(3)修改表数据

格式:

update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

(4)删除数据

格式:

delete from 表名 where 字段=某个值;

delete from 表名; (慎重使用)

delete from 表名 where 字段 = 值;

清空表数据

truncate table 表名;

16.修改用户密码

在退出mysql服务的情况下 输入

1、mysqladmin -u 用户名 -p password 新密码

输入旧密码:

2、登录到mysql的时候使用set password for '用户名'@'登录主机'=password('新密码');

17.修改表的字段信息

修改表的字符集

alter table 表名 charset utf8

修改字段的类型

alter table 表名 modify 字段名 字段类型

修改字段的名字并同时修改字段类型

alter table 表名 change 旧字段名 新字段名 字段类型

修改字段的字符集

alter table 表名 modify 字段名 类型 charset utf8

添加新字段

alter table 表名 add 字段名 类型

删除字段

alter table 表名 drop 字段名

修改表名

alter table 旧表名 rename as 新表名

18.删除默认匿名的账户

为什么我直接输入mysql也能进入数据库中。是匿名用户，MySQL默认创建的。他有test和information_schema这两个库的权限。我们可以把他删掉

删除匿名用户：drop user ''@'localhost';

19.MySQL数据库的数据类型：

MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。

1 数值类型：

*tinyint(1字节) 0~255 -128~127

smallint(2字节)

mediumint(3字节)

*int(4字节)

bigint(8字节)

*float(4字节) float(6,2) float(m,d)

*double(8字节) double (m,d)

decimal(自定义)字串形数值 decimal(m,d)

m精度 d标度

2 字串类型

普通字串

*char 定长字串 char(8)

*varchar 可变字串 varchar(8)

二进制类型

tinyblob

blob

mediumblob

longblob

文本类型

tinytext

*text 常用于

mediumtext

longtext

*enum枚举

set集合

3 时间和日期类型：

date 年月日

time 时分秒

datetime 年月日时分秒

timestamp 时间戳

year 年

4 NULL值

NULL意味着“没有值”或“未知值”

可以测试某个值是否为NULL

不能对NULL值进行算术计算

对NULL值进行算术运算，其结果还是NULL

0或NULL都意味着假，其余值都意味着真

MySQL的运算符：

算术运算符：+ - * / %

比较运算符：= > < >= <= <> !=

数据库特有的比较：in，not in, is null,is not null,like, between and

逻辑运算符：and or not

like: 支持特殊符号%和_ ; 其中 %表示任意数量的任意字符，_表示任意一位字符。

表的字段约束：

unsigned 无符号(正数)

zerofill 前导零填充

default 默认值

not null 非空

auto_increment 自增 在产生一个唯一的标识或顺序值的时候，可以利用这个约束条件。这个约束条件只能用于整数类型，值一般从1开始。每行加1，插入一个null到一个auto_increment列时，MySQL将插入一个比出现过的最大值+1的值。一个表中只能有一个auto_increment列，并且必须定义为primary key或unique才能够使用

primary key 主键索引 (非null并不重复)

unique 唯一索引 (可以为null但不重复)

index 常规索引

19.建表语句格式：

create table 表名(

字段名 类型 [字段约束],

字段名 类型 [字段约束],

字段名 类型 [字段约束]

...

);

实例:

mysql> create table stu(

-> id int unsigned not null auto_increment primary key,

-> name varchar(8) not null unique,

-> age tinyint unsigned,

-> sex enum('m','w') not null default 'm',

-> classid char(6)

-> );

20.插入数据

1 插入指定字段

insert into t1(字段1,字段2,字段3...) values ('值1','值2','值3',....);

2 插入所有的字段

insert into t1 values ('值1','值2','值3',....);

3 插入多条数据

insert into t1(字段1,字段2,字段3...) values ('值1','值2'),('值1','值2')....;

4 插入结果

insert into t1(字段1,字段2,字段3...) select 字段1,字段2,字段3... from t1;

insert into t1() select * from t1;

5 插入单条数据

insert into t1 set 字段1=值1,字段2=值2....;

21.查询数据

格式：

select [字段列表]|* from 表名

[where 搜索条件]

[group by 分组字段 [having 子条件]]

[order by 排序 asc|desc]

[limit 分页参数]

##where条件查询

1. 查询班级为lamp138期的学生信息

mysql> select * from stu where classid='lamp138';

2. 查询lamp138期的男生信息(sex为m)

mysql> select * from stu where classid='lamp138' and sex='m';

3. 查询id号值在10以上的学生信息

mysql> select * from stu where id>10;

4. 查询年龄在20至25岁的学生信息

mysql> select * from stu where age>=20 and age<=25;

mysql> select * from stu where age between 20 and 25;

5. 查询年龄不在20至25岁的学生信息

mysql> select * from stu where age not between 20 and 25;

mysql> select * from stu where age<20 or age>25;

6. 查询id值为1,8,4,10,14的学生信息

select * from stu where id in(1,8,4,10,14);

mysql> select * from stu where id=1 or id=8 or id=4 or id=10 or id=14;

7. 查询lamp138和lamp94期的女生信息

mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';

mysql> select * from stu where (classid='lamp138' or classid='lamp94') and sex='w

##LIKE 子句

这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

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

如果没有使用百分号%, LIKE 子句与等号=的效果是一样的。

LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

你可以使用 AND 或者 OR 指定一个或多个条件。

你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

可以使用regexp正则来代替 like

1.查询name字段值是以zh开头的所有信息

mysql> select * from stu where name like "zh%";

mysql> select * from stu where name regexp "^zh"; --正则写法

2.查询姓名name中含有ang子串的所有信息

mysql> select * from stu where name like "%ang%";

mysql> select * from stu where name regexp "ang";

3.查询姓名是任意四位字符构成的信息。

mysql> select * from stu where name like "____";

mysql> select * from stu where name regexp "^[a-z0-9]{4}$";

创建一个表结构和另一个表一样的结构

create table t3 like t1;

那么现在t3表的结构和t1表的结构是一样的,但是要注意数据是没有的

##MySQL的统计函数(聚合函数)：max() min() count() sum() avg()

1.获取学生表中最大、最小以及平均年龄是多少？

mysql> select max(age),min(age),avg(age) from stu;

2.获取学生表中男生m的数量

mysql> select count(*) from stu where sex='m';

##GROUP BY 语句 分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

统计班级信息，按性别分组，并统计每组人数；

mysql> select sex,count(*) from stu group by sex;

1.统计每个班级的人数

nysql> select classid,count(*) from stu group by classid;

2.统计每个班级的，男生和女生各多少人数。

mysql> select classid,sex,count(*) from stu group by classid,sex;

##ORDER BY 排序 -- asc 默认升序 desc 降序

我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的ORDER BY子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。

你可以设定多个字段来排序。

你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

你可以添加 WHERE...LIKE 子句来设置条件。

1.按年龄升序排序查询学生信息

mysql> select * from stu order by age;

mysql> select * from stu order by age asc; --默认asc升序 可省略

2.年龄降序排序

mysql> select * from stu order by age desc;

3.查询学生信息，按班级做升序排序，相同班级按年龄降序排序

mysql> select * from stu order by classid asc,age desc;

##LIMIT 关键字 查询部分数据

-- 例如： .... limit m; 查询数据只显示前m条

-- 例如： .... limit m,n; 排除前m条，然后再查询出前n条

1.查询前5条信息

mysql> select * from stu limit 5;

2.排除前2条后再获取4条信息

mysql> select * from stu limit 2,4;

22.修改数据

格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

-- 将id为11的age改为35，sex改为m值

mysql> update stu set age=35,sex='m' where id=11;

-- 将id值为12和14的数据值sex改为m，classid改为lamp92

mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面

mysql> update stu set sex='m',classid='lamp92' where id in(12,14);

23.删除操作

格式：delete from 表名 [where 条件]

-- 删除stu表中id值为100的数据

mysql> delete from stu where id=100;

-- 删除stu表中id值为20到30的数据

mysql> delete from stu where id>=20 and id<=30;

-- 删除stu表中id值为20到30的数据(等级于上面写法)

mysql> delete from stu where id between 20 and 30;

-- 删除stu表中id值大于200的数据

mysql> delete from stu where id>200;

24.导入和导出

注意: 在操作导入和导出数据的时候 不要登录到mysql服务中,要退出mysql服务进行操作

-- 将lamp138数据库里面的所有的表导出

C:\>mysqldump -u root -p lamp138 > C:\lamp138.sql

Enter password:

---- 将lamp138数据库中的stu表导出

C:\>mysqldump -u root -p lamp138 stu > C:\lamp138_stu.sql

Enter password:

-- 将lamp138库中的所有表导入

C:\>mysql -u root -p lamp138 < C:\lamp138.sql

Enter password:

-- 将lamp138库中stu表导入

C:\>mysql -u root -p lamp138 < C:\lamp138_stu.sql

Enter password:

*********************

-- 将所有的数据库进行导出

mysqldump -uroot -p密码 --all-databases --events > /tmp/bak.sql 所有数据库备份

25.MySQL的权限管理

格式:

grant 权限 on 数据库.数据表 to '用户名'@'登录主机' identified by '密码'

刷新权限

flush privileges;

举例:

grant select,insert,update,delete on *.* to 'wjs'@'%' identified by '12345';

安全的做法

grant select,insert,update,delete on pass.* to 'root'@'localhost' identified by '12345';

只针对localhost主机里面的 pass数据库里面的数据表进行增删该查

删除用户

drop user 'xxoo'@'%'

26.索引的作用:

索引是数据库中用来提高搜索性能的。我们通常在做数据库优化的时候通常先做索引的优化，数据量少的时候没效果，数据越多效果越明显。

查看表中都有哪些索引

show index from 表名\G

索引的分类:

##常规索引(index)

最基本的索引，没有任何限制

添加表字段的常规索引

create index 索引名 on 表名(字段名)

alter table 表名 add index 索引名(字段名)

在创建表的时候也可以进行添加

create table t2(

id int(10) unsigned auto_increment primary key,

name varchar(255), index 索引名(字段名)

);

删除表字段常规索引**

drop index 索引名 on 表名

alter table 表名 drop index 索引名

##唯一索引(unique)

唯一索引是可以给每个字段进行添加的,添加完了之后字段里面的值就不可以重复了,主键索引和唯一索引类似,

但是数据表里的主键索引只能加在一个字段里(一般都加在id上),id是自增的,索引不会有重复的时候出现

添加表字段的唯一索引

create unique index 索引名 on 表名(字段名)

alter table 表名 add unique 索引名(字段名)

在创建表的时候也可以进行添加

create table t2(

id int(10) unsigned auto_increment primary key,

name varchar(255), unique 索引名(字段名)

);

删除唯一索引

drop index 索引名 on 表名

##主键索引(primary key)

主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。

我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。

注意：

1.最好为每张表指定一个主键，但不是必须指定。

2.一个表只能指定一个主键，而且主键的值不能为空

3.主键可以有多个候选索引(例如NOT NULL，AUTO_INCREMENT)

添加表字段的主键索引

alter table 表名 add primary key (字段名)

添加自增

alter table 表名 modify id int(4) auto_increment

删除主键索引

如果字段有了auto_increment 和 primary key的时候,需要删除主键的话,

先删除自增,然后再删除主键

删除自增

alter table 表名 change 字段名 字段名 类名

删除主键

alter table 表名 drop primary key

##全文索引(fulltext)

全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，

并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。

添加表字段的全文索引

alter table 表名 add fulltext 索引名(字段名)

删除全文索引

drop index (索引名) on 表名

alter table 表名 drop index 索引名

注意:如果在创建索引的时候,不添加索引名的话 默认会把字段名当做索引名

27.表的存储引擎

存储引擎: 是MySQL的一个特性之一,用户可以根据应用的需要选择如何存储

MySQL支持MyISAM、InnoDB、HEAP、BOB、ARCHIVE、CSV、MEMORY等多种数据表引擎，在创建一个新MySQL数据表时，可以为它设置一个引擎类型。

MyISAM和InnoDB两种表类型最为重要：

1.MyISAM数据表类型的特点是成熟、稳定和易于管理。

2.MyISAM表类型会产生碎片空间，要经常使用OPTIMIZE TABLE命令去清理表空间

3.MyISAM不支持事务处理，InnoDB支持

4.MyISAM不支持外键，InnoDB支持

5.MyISAM表类型的数据表效率更高

6.MyISAM表类型的数据表会产生三个文件

---.frm (表结构)

---.myd (表数据)

---.myi (表索引)

InnoDB表类型表默认只会产生两个文件

---.frm (表结构)

---.ibd (表数据+表索引)

7.MyISAM表，其优势就是访问的速度快。对事务没有完整性的要求或以select、insert为主的通常使用MyISAM表

8.InnoDB表：会占用更多的磁盘空间

锁机制：MyISAM 表锁：开销小，加锁快，发生冲突的概率高。

InnoDB 行锁：开销大，加锁慢，发生冲突的概率低。

9.默认是InnoDB引擎

数据存储在 innodb_data_home_dir选项所选的文件名的目录里

查看默认的引擎

show variables like 'default_storage_engine';

查看支持的引擎

show engines

查看当前库里面所有的表引擎

show table status from 库名

修改表引擎

alter table 表名 engine = 新的表引擎

ubuntu下

//mysql配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

//重启

sudo /etc/init.d/mysqld restart