本文共 16403 字,大约阅读时间需要 54 分钟。
简单来说,就是按照数据结构组织,存储和管理数据的仓库。
1.持久性的保存
2.方便数据的存储查询
3.处理并发访问
4.更加安全的权限管理访问机制
C:\WINDOWS\system32>mysql -u root -pEnter password: ******Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.20 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> show databases -> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test03 |+--------------------+4 rows in set (0.01 sec)
如上所示,前者回车会进入下一行;有“;”后回车才会显示结果。
create database 库名 default charset=utf8;
【举例】
mysql> create database XiaoAiFu default charset=utf8;Query OK, 1 row affected (0.01 sec)--查看库mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test03 || xiaoaifu |+--------------------+5 rows in set (0.00 sec)--进入库mysql> use XiaoAiFu;Database changed
--创建表的语法create table 表名( 字段名 类型 字段约束, 字段名 类型 字段约束, 字段名 类型 字段约束, )engine=innodb defaultcharset=utf8;
--创建用户表mysql> create table user( -> name varchar(20), -> age int, -> sex char(1) -> )engine=innodb default charset=utf8;Query OK, 0 rows affected (0.03 sec)
创建好用户表后,现在做一个添加数据的实例
mysql> insert into user(name,age,sex) values('小艾扶',26,'男');Query OK, 1 row affected (0.01 sec)mysql> insert into user(name,age,sex) values('朱立青',23,'女');Query OK, 1 row affected (0.01 sec)mysql> select * from user;+--------+------+------+| name | age | sex |+--------+------+------+| 小艾扶 | 26 | 男 || 朱立青 | 23 | 女 |+--------+------+------+2 rows in set (0.01 sec)mysql>
其中,SQL ( Structure query language ) 结构化查询语言 SQL语言分为4个部分:DDL(定义)、DML(操作)、DQL(查询)、DCL(控制)
show databases
;create database 库名 default charset=utf8
;drop database 库名
;use 库名
;show tables
;create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8
;create table if not exists 表名(字段1 类型,字段2 类型)
;【举例】
create table if not exists users( id int not null primary key auto_increment, name varchar(4) not null, age tinyint, sex enum('男','女'))engine=innodb default charset=utf8;
drop table 表名
;desc 表名
;show create table users
;5.1 插入
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);
5.2 查询
select * from 表名;
select 字段1,字段2,字段3 from 表名;
select * from 表名 where 字段=某个值;
5.3 修改
update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件;
update 表名 set 字段=字段+值 where 条件;
5.4 删除
delete from 表名 where 字段=某个值;
exit;
或者 quit;
char(7) --不管实际插入多少字符,它都会占用7个字符位置
存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7个字符。
【注意】
既然变长数据类型这样灵活,为什么还要使用定长数据类型?回答:因为性能,MySQL处理定长列远比处理变长列快得多。
数值数据类型存储数值。MySQL支持多种数值数据类型,每种存储的数值具有不同的取值范围。支持的取值范围越大,所需存储空间越多与字符串不一样,数值不应该括在引号内。
【举例】
decimal(5, 2) 表示数值总共5位, 小数占2位tinyint 1字节(8位) 0-255。-128,127int 4字节。 -21亿,21亿。0-42亿float.MySQL中没有专门存储货币的数据类型,一般情况下使用DECIMAL(8, 2)
有符号或无符号
所有数值数据类型(除BIT和BOOLEAN外)都可以有符号或无符号
【注意】
如果将邮政编码类似于01234存储为数值类型,则保存的将是数值1234,此时需要使用字符串类型手机号应该用什么进行存储呢?答:字符串~~手机号首先不用于计算
MySQL使用专门的数据类型来存储日期和时间值
datetime 8字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
二进制数据类型可存储任何数据(甚至包括二进制信息),如图像、多媒体、字处理文档等
unsigned
无符号(给数值类型使用,表示为正数,不写可以表示正负数都可以)char(5). varchar(7)
在字符类型后面加限制表示字符串的长度int(4)
没有意义,默认无符号的int为int(11)
,有符号的int(10)
int(4) unsigned zerofill
只有当给int类型设置有前导零时,设置int的宽度才有意义。int(4) unsigned zerofill
只有给Int
类型设置前导零时,设置Int
宽度才有意义。not null
不能为空,在操作数据库时如果输入该字段的数据为NULL ,就会报错default
设置默认值primary key
主键不能为空,且唯一.一般和自动递增一起配合使用。auto_increment
定义列为自增属性,一般用于主键,数值会自动加1unique
唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度其中%表示任意数量的任意字符,_表示任意一位字符
要求
记录一旦插入到表中,主键最好不要再修改
不允许NULL
不在主键列中使用可能会更改的值。(例如,如果使用一个名字作为主键以标识某个供应商,当该供应商合并和更改其名字时,必须更改这个主键。)
自增整数类型:数据库会在插入数据时自动为每一条记录分配一个自增整数,这样我们就完全不用担心主键重复,也不用自己预先生成主键
可以使用多个列作为联合主键,但联合主键并不常用。使用多列作为主键时,所有列值的组合必须是唯一的。
此处承接第4点相关内容
create database if not exists XiaoAiFu default charset=utf8;
show databases;
#user 库名use XiaoAiFu
--删除库有风险,动手时要谨慎!!!drop database 库名
语法格式:create table 表名(字段名,类型,【字段约束】,...);
【举例】
# 以下创建一个 users 的表create table users(-- 创建ID字段,为正整数,不允许为空 主键,自动递增id int unsigned not null primary key auto_increment,-- 创建 存储 名字的字段,为字符串类型,最大长度 5个字符,不允许为空username varchar(5) not null,-- 创建存储 密码 的字段,固定长度 32位字符, 不允许为空password char(32) not null,-- 创建 年龄 字段,不允许为空,默认值为 20age tinyint not null default 20)engine=innodb default charset=utf8;# 查看表结构desc users;#查看建表语句show create table users;
【实例】
这一次我们创建一个person表,如下展示。
--创建person表mysql> create table person( -> id int unsigned not null primary key auto_increment, -> name varchar(5) not null, -> password char(32) not null, -> age tinyint not null default 20 -> )engine=innodb default charset=utf8;Query OK, 0 rows affected (0.02 sec)--查看建表结构mysql> desc person;+----------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+------------------+------+-----+---------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || name | varchar(5) | NO | | NULL | || password | char(32) | NO | | NULL | || age | tinyint(4) | NO | | 20 | |+----------+------------------+------+-----+---------+----------------+4 rows in set (0.01 sec)
创建表的基本原则:
语法格式:alter table 表名 action (更改的选项)
添加字段
# 语法:alter table 表名 add 添加的字段信息-- 在 users 表中 追加 一个 num 字段alter table users add num int not null;-- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段alter table users add email varchar(50) after age;-- 在指定字段后面追加字段,在 users 表中 age字段后面 添加一个 phonealter table users add phone char(11) not null after age;-- 在表的最前面添加一个字段alter table users add aa int first;
删除字段
# 删除字段 alter table 表名 drop 被删除的字段名alter table users drop aa;
修改字段
语法格式: alter table 表名 change|modify 被修改的字段信息change: 可以修改字段名,modify: 不能修改字段名。# 修改表中的 num 字段 类型,使用 modify 不修改表名alter table users modify num tinyint not null default 12;# 修改表中的 num 字段 为 int并且字段名为 nnalter table users change num mm int;# 注意:一般情况下,无特殊要求,不要轻易修改表结构
修改表名
# 语法:alter table 原表名 rename as 新表名
更改表中的自增的值
# 在常规情况下,auto_increment 默认从1开始继续递增alter table users auto_increment = 1000;
修改表引擎
# 推荐在定义表时,表引擎为 innodb。# 通过查看建表语句获取当前的表引擎mysql> show create table users\G;*************************** 1. row ***************************Table: usersCreate Table: CREATE TABLE `users` (PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf81 row in set (0.00 sec)# 直接查看当前表状态信息mysql> show table status from tlxy where name = 'users'\G;*************************** 1. row ***************************Name: usersEngine: InnoDB# 修改表引擎语句alter table users engine = 'myisam';
删除表
drop table 表名
格式: insert into 表名[(字段列表)] values(值列表...);
--标准添加(指定所有字段,给定所有的值)mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');Query OK, 1 row affected (0.13 sec)mysql>--指定部分字段添加值mysql> insert into stu(name,classid) value('lisi','lamp138');Query OK, 1 row affected (0.11 sec)-- 不指定字段添加值mysql> insert into stu value(null,'wangwu',21,'w','lamp138');Query OK, 1 row affected (0.22 sec)-- 批量添加值mysql> insert into stu values-> (null,'zhaoliu',25,'w','lamp94'),-> (null,'uu01',26,'m','lamp94'),-> (null,'uu02',28,'w','lamp92'),-> (null,'qq02',24,'m','lamp92'),-> (null,'uu03',32,'m','lamp138'),-> (null,'qq03',23,'w','lamp94'),-> (null,'aa',19,'m','lamp138');Query OK, 7 rows affected (0.27 sec)Records: 7 Duplicates: 0 Warnings: 0
格式:update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件
【实例】
mysql> update user set sex='男' where age=19;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+--------+------+------+| name | age | sex |+--------+------+------+| admin | 26 | 男 || 朱立青 | 23 | 女 || 草右丹 | 19 | 男 || 口天涛 | 31 | 男 |+--------+------+------+4 rows in set (0.01 sec)
格式:delete from 表名 [where 条件]
【实例】
mysql> delete from user where age=19;Query OK, 1 row affected (0.01 sec)mysql> select * from user;+--------+------+------+| name | age | sex |+--------+------+------+| admin | 26 | 男 || 朱立青 | 23 | 女 || 口天涛 | 31 | 男 |+--------+------+------+3 rows in set (0.00 sec)
语法格式:
select 字段列表|* from 表名[where 搜索条件][group by 分组字段 [having 分组条件]][order by 排序字段 排序规则][limit 分页参数]
# 查询表中所有列 所有数据select * from users;# 指定字段列表进行查询select id,name,phone from users;
【示例】
-- 查询users表中 age > 22的数据select * from users where age > 22;-- 查询 users 表中 name=某个条件值 的数据select * from users where name = '王五';-- 查询 users 表中 年龄在22到25之间的数据select * from users where age >= 22 and age <= 25;select * from users where age between 22 and 25;-- 查询 users 表中 年龄不在22到25之间的数据select * from users where age < 22 or age > 25;select * from users where age not between 22 and 25;-- 查询 users 表中 年龄在22到25之间的女生信息select * from users where age >= 22 and age <= 25 and sex = '女';
【问题】
假设要求查询 users 表中 年龄为22或者25 的女生信息 select * from users where age=22 or age = 25 and sex = ‘女’;
思考上面的语句能否返回符合条件的数据? 实际查询结果并不符合要求?
select * from users where age=22 or age = 25 and sex = '女';+------+--------+------+-------+-------+------+------+| id | name | age | phone | email | sex | mm |+------+--------+------+-------+-------+------+------+| 1 | 章三 | 22 | | NULL | 男 | 0 || 1002 | cc | 25 | 123 | NULL | 女 | NULL |+------+--------+------+-------+-------+------+------+2 rows in set (0.00 sec)-- 上面的查询结果并不符合 查询条件的要求。-- 问题出在 sql 计算的顺序上,sql会优先处理and条件,所以上面的sql语句就变成了-- 查询变成了为年龄22的不管性别,或者年龄为 25的女生-- 如何改造sql符合我们的查询条件呢?-- 使用小括号来关联相同的条件select * from users where (age=22 or age = 25) and sex = '女';+------+------+------+-------+-------+------+------+| id | name | age | phone | email | sex | mm |+------+------+------+-------+-------+------+------+| 1002 | cc | 25 | 123 | NULL | 女 | NULL |+------+------+------+-------+-------+------+------+1 row in set (0.00 sec)
事实证明,我们同样可以通过使用小括号的方式,使得运算级发生改变。
-- like 语句 like某个确定的值 和。where name = '王五' 是一样select * from users where name like '王五';+----+--------+------+-------+-----------+------+------+| id | name | age | phone | email | sex | mm |+----+--------+------+-------+-----------+------+------+| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 |+----+--------+------+-------+-----------+------+------+1 row in set (0.00 sec)-- 使用 % 模糊搜索。%代表任意个任意字符-- 查询name字段中包含五的select * from users where name like '%五%';-- 查询name字段中最后一个字符 为 五的select * from users where name like '%五';-- 查询name字段中第一个字符 为 王 的select * from users where name like '王%';-- 使用 _ 单个的下划线。表示一个任意字符,使用和%类似-- 查询表中 name 字段为两个字符的数据select * from users where name like '__';-- 查询 name 字段最后为五,的两个字符的数据select * from users where name like '_五';
max(),min(),count(),sum(),avg()
# 计算 users 表中 最大年龄,最小年龄,年龄和及平均年龄select max(age),min(age),sum(age),avg(age) from users;+----------+----------+----------+----------+| max(age) | min(age) | sum(age) | avg(age) |+----------+----------+----------+----------+| 28 | 20 | 202 | 22.4444 |+----------+----------+----------+----------+-- 上面数据中的列都是在查询时使用的函数名,不方便阅读和后期的调用,可以通过别名方式 美化select max(age) as max_age,min(age) min_age,sum(age) as sum_age,avg(age) as avg_agefrom users;+---------+---------+---------+---------+| max_age | min_age | sum_age | avg_age |+---------+---------+---------+---------+| 28 | 20 | 202 | 22.4444 |+---------+---------+---------+---------+-- 统计 users 表中的数据量select count(*) from users;+----------+| count(*) |+----------+| 9 |+----------+select count(id) from users;+-----------+| count(id) |+-----------+聚合函数除了以上简单的使用意外,通常情况下都是配合着分组进行数据的统计和计算| 9 |+-----------+-- 上面的两个统计,分别使用了 count(*) 和 count(id),结果目前都一样,有什么区别?-- count(*) 是按照 users表中所有的列进行数据的统计,只要其中一列上有数据,就可以计算-- count(id) 是按照指定的 id 字段进行统计,也可以使用别的字段进行统计,-- 但是注意,如果指定的列上出现了NULL值,那么为NULL的这个数据不会被统计-- 假设有下面这样的一张表需要统计+------+-----------+------+--------+-----------+------+------+| id | name | age | phone | email | sex | mm |+------+-----------+------+--------+-----------+------+------+| 1 | 章三 | 22 | | NULL | 男 | 0 || 2 | 李四 | 20 | | NULL | 女 | 0 || 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 || 1000 | aa | 20 | 123 | NULL | 女 | NULL || 1001 | bb | 20 | 123456 | NULL | 女 | NULL || 1002 | cc | 25 | 123 | NULL | 女 | NULL || 1003 | dd | 20 | 456 | NULL | 女 | NULL || 1004 | ff | 28 | 789 | NULL | 男 | NULL || 1005 | 王五六 | 23 | 890 | NULL | NULL | NULL |+------+-----------+------+--------+-----------+------+------+9 rows in set (0.00 sec)-- 如果按照sex这一列进行统计,结果就是8个而不是9个,因为sex这一列中有NULL值存在mysql> select count(sex) from users;+------------+| count(sex) |+------------+| 8 |+------------+
聚合函数除了以上简单的使用意外,通常情况下都是配合着分组进行数据的统计和计算
group by
语句根据一个或多个列对结果集进行分组-- 统计 users 表中 男女生人数,-- 很明显按照上面的需要,可以写出两个语句进行分别统计select count(*) from users where sex = '女';select count(*) from users where sex = '男';-- 可以使用分组进行统计,更方便select sex,count(*) from users group by sex;+------+----------+| sex | count(*) |+------+----------+| 男 | 4 || 女 | 5 |-- 统计1班和2班的人数select classid,count(*) from users group by classid;+---------+----------+| classid | count(*) |+---------+----------+| 1 | 5 || 2 | 4 |+---------+----------+-- 分别统计每个班级的男女生人数select classid,sex,count(*) as num from users group by classid,sex;+---------+------+-----+| classid | sex | num |+---------+------+-----+| 1 | 男 | 2 || 1 | 女 | 3 || 2 | 男 | 2 || 2 | 女 | 2 |+---------+------+-----+# 注意,在使用。group by分组时,一般除了聚合函数,其它在select后面出现的字段列都需要出现在grouop by 后面
Having
时在分组聚合计算后,对结果再一次进行过滤,类似于where
,-- 要统计班级人数select classid,count(*) from users group by classid;-- 统计班级人数,并且要人数达到5人及以上select classid,count(*) as num from users group by classid having num >=5
-- 查询users表中的数据,只要3条select * from users limit 3;-- 跳过前4条数据,再取3条数据select * from users limit 4,3;-- limit一般应用在数据分页上面-- 例如每页显示10条数据,第三页的 limit应该怎么写? 思考第一页 limit 0,10第二页 limit 10,10第三页 limit 20,10第四页 limit 30,10-- 提取 user表中 年龄最大的三个用户数据 怎么查询?select * from users order by age desc limit 3;
【总结】
以上是根据学习时的一些学习,有些举例是在课堂上讲的,也有的是自己敲的。希望能帮助到大家!谢谢~
转载地址:http://iegdi.baihongyu.com/