MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。
MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。接下来记录下MySQL的基本使用:
常见术语
- 数据库:数据库是一些关联表的集合
- 数据表:表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格
- 列:一列包含了相同的数据
- 行:一行是一组相关的数据
- 冗余:存储两倍数据,冗余可以使系统速度更快
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据
- 外键:外键用于关联两个表
- 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引
- 参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性
登录我的MySQL
mysql -uroot -p # root为用户名
如果出现:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
为用户名或密码错误修改密码,请看:http://blog.csdn.net/y472360651/article/details/73565221
退出MySQL
exit;
查看我的MySQL所有数据库
show databases;
使用如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
进入某个数据库
use xxx; # xxx为数据库名
使用如下:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看该数据库所有表
show tables;
使用如下:
mysql> show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| alembic_version |
| user |
+-----------------+
2 rows in set (0.00 sec)
查看表的结构
describe xxx;
或
desc xxx;
或
show columns from xxx;
使用如下:
mysql> describe user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
查看表的所有数据
select * from xxx; # xxx为表名
使用如下:
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | laozhang | 123456 |
+----+----------+----------+
1 row in set (0.00 sec)
因为我们用的是终端操作的,而且数据过多,所以会显示的乱七八糟的,此时我们可以用命令:
select * from xxx\G;
进行格式化一下使得更加具有可读性:
mysql> select * from user\G;
*************************** 1. row ***************************
id: 1
username: laozhang
password: 123456
1 row in set (0.00 sec)
为MySQL创建用户
为MySQL创建用户,起始就是在mysql数据库中的user表添加一个数据而已
# grant all 表示拥有所有权限,也可指定权限(grant select,insert,update,delete)
# *.* 可操作所有数据库下的所有表,也可以指定(如:test.info。test数据库下的info表)
# 'python'为用户名,'localhost'为域名,此时代表本机。'localhost'改成'%'则代表所有
# '123456'为密码
mysql> grant all on *.* to 'python'@'localhost' identified by '123456'
# 重新载入授权表
mysql> flush privileges
创建数据库
mysql> create database xxx charset "utf8"; # xxx为数据库名 设置编码为utf8,使其能支持中文
删除数据库
mysql> drop database xxx; # xxx为数据库名
创建表
# 创建表,id自增,name不能为空,age不能为空
mysql> create table student(id int auto_increment, name text not null, age int not null, primary key(id));
删除表
mysql> drop table student; # 删除student表
给表插入信息
mysql> insert into student(name,age) values("laowang",38);
查看表的所有数据信息
- 查看所有
mysql> select * from student;
- limit:限制查找条数
mysql> select * from student limit 2; # 限制值查找2条
- offset:设置查找位置,需跟limit结合使用,并且需放置在limit后面
mysql> select * from student limit 2 offset 1; # 从第一个位置查找,限制为2条
- where和and:限制条件
mysql> select * from student where age > 35; # 查找所有age大于35的数据
mysql> select * form student where age > 35 and age < 40 # 查看所有age大于35并且小于40的数据
- like:模糊查询
# 查询name以'l'开头的数据
mysql> select * from student where name like 'l%'
# 查询name长度为8的所有数据,8个_符号代表长度为8
mysql> select * from student where name like '________'
- regexp: 正则匹配
# 查询name以lao开头的所有数据
mysql> select * from student where name regexp '^lao'
# 查询name以wang结尾的所有数据
mysql> select * from student where name regexp 'wang$'
# 查询name包含w的所有数据
mysql> select * from student where name regexp 'w'
# 查询name长度为8的所有数据,8个.符号代表长度为8
mysql> select * from student where naem regexp '^........$'
# 也可以使用这种方式,查询name长度为8的所有数据
mysql> select * from student where name regexp '^.{8}$'
- order by:排序,desc降序,asc升序。默认为asc
mysql> select * from student order by age desc; # 降序查询
# 以年龄升序,年龄相同则分数降序查询
mysql> select * from student order by age asc, score desc;
- group by:分组
mysql> select name from student group by name; # 以名字分组
类型隐式转换
在MySQL的查询中,支持一种类型隐式转换,如下:
mysql> select 1 = '1';
+-------+
| 1='1' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select '0.01' = 0.01;
+---------------+
| '0.01' = 0.01 |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
值得注意的是,在MySQL数据库中并无存储true或者false的类型,统一用1代表true,0代表false
运算符
常见的比较运算符有>
、>=
、<
、<=
、=
和!=
,这几种运算符将不再多做介绍,接下来介绍几种其他的运算符,如下:
- <=>: 该运算符具有与
=
相类似的比较,但如果两个都为null,<=>
比较返回的是1,而=
返回的是null;如果其中一个为null,<=>
比较返回的是0,而=
返回的是null。如下:
mysql> select 1=1, 1=2, 1<=>1, 1<=>2;
+-----+-----+-------+-------+
| 1=1 | 1=2 | 1<=>1 | 1<=>2 |
+-----+-----+-------+-------+
| 1 | 0 | 1 | 0 |
+-----+-----+-------+-------+
1 row in set (0.00 sec)
mysql> select null=null, 1=null, null<=>null, 1<=>null;
+-----------+--------+-------------+----------+
| null=null | 1=null | null<=>null | 1<=>null |
+-----------+--------+-------------+----------+
| NULL | NULL | 1 | 0 |
+-----------+--------+-------------+----------+
1 row in set (0.00 sec)
<=>
还可以这样使用,(a, b) <=> (x, y)
等价于(a <=> x) and (b <=> y)
- <>: 该运算符具有与
!=
相类似的比较,使用如下:
mysql> select 1<>2;
+------+
| 1<>2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
<>
和!=
还可以这样使用,(a, b) <> (x, y)
等价于(a <> x) or (b <> y)
,(a, b) != (x, y)
等价于(a != x) or (b != y)
- is <boolean value>: 只针对boolean值,
boolean value
可以是true、false或者unknown,不可用于数值比较操作,使用如下:
mysql> select 1 is true, 0 is false, null is unknown;
+-----------+------------+-----------------+
| 1 is true | 0 is false | null is unknown |
+-----------+------------+-----------------+
| 1 | 1 | 1 |
+-----------+------------+-----------------+
1 row in set (0.00 sec)
mysql> select 1 is 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
- is not <boolean value>: 只针对boolean值,
boolean value
可以是true、false或者unknown,不可用于数值比较操作,使用如下:
mysql> select 1 is not unknown, 0 is not unknown, null is not unknown;
+------------------+------------------+---------------------+
| 1 is not unknown | 0 is not unknown | null is not unknown |
+------------------+------------------+---------------------+
| 1 | 1 | 0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)
- is null: 是否为空
mysql> select 1 is null, 0 is null, null is null;
+-----------+-----------+--------------+
| 1 is null | 0 is null | null is null |
+-----------+-----------+--------------+
| 0 | 0 | 1 |
+-----------+-----------+--------------+
1 row in set (0.00 sec)
- is not null: 是否不为空
mysql> select 1 is not null, 0 is not null, null is not null;
+---------------+---------------+------------------+
| 1 is not null | 0 is not null | null is not null |
+---------------+---------------+------------------+
| 1 | 1 | 0 |
+---------------+---------------+------------------+
1 row in set (0.00 sec)
- between <min> and <max>: 大于或等于min,并且小于或等于max
mysql> select 1 between 1 and 2, 2 between 1 and 2, 3 between 1 and 2;
+-------------------+-------------------+-------------------+
| 1 between 1 and 2 | 2 between 1 and 2 | 3 between 1 and 2 |
+-------------------+-------------------+-------------------+
| 1 | 1 | 0 |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
- not between <min> and <max>: 小于min,或大于max
mysql> select 1 not between 1 and 2, 2 not between 1 and 2, 3 not between 1 and 2;
+-----------------------+-----------------------+-----------------------+
| 1 not between 1 and 2 | 2 not between 1 and 2 | 3 not between 1 and 2 |
+-----------------------+-----------------------+-----------------------+
| 0 | 0 | 1 |
+-----------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)
此用法等同于not (between <min> and <max>)
删除数据
delete from student where name = 'laowang' # 删除name为laowang的数据,如果没有where条件,将删除表所有数据
更新数据
update student set age = 40 where name = "laowang" # 把name为laowang的age改为40
对表的字段操作
- 删除表的一个字段
alter table student drop age; # 删除student表的age字段
- 为表添加字段
alter table student add phone int not null; # 为student表添加phone字段并且phone不能为空
- 修改字段类型
alter table student modify age text not null; # 将student表的age字段改为text类型
- 修改字段默认值
alter table student modify age text not null default "20"; # 修改age的默认值,以后添加的数据,如果age无需手动赋值,自动赋值为20
- 修改字段名
alter table student change age a int not null; # 将student表的age字段改为a
- 修改表名
alter table student rename r_student; # 将student改为r_student
外键的使用
表1:
create table student(
id int auto_increment primary key,
name text not null,
age int not null,
date date not null default "2017-6-20"
);
表2:
create table study_record(
id int auto_increment primary key,
day int not null.
stu_id int not null,
foreign key(stu_id) references student(id)
);
是stu_id与student表的id进行约束,那么stu_id就是study_record的外键。
临时变量
我们可以使用@xxx:=xxx
来定义一个临时变量,来提供给下次查询使用,如下:
mysql> select @max_age:=max(age) from student;
+--------------------+
| @max_age:=max(age) |
+--------------------+
| 22 |
+--------------------+
1 row in set (0.00 sec)
mysql> select name from student where age = @max_age;
+----------+
| name |
+----------+
| laojiang |
+----------+
1 row in set (0.01 sec)
多表查询
假设现在有两张表,student
与people
,数据如下:
mysql> select * from student;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | laozhang | 80 |
| 2 | laowang | 60 |
| 3 | laoli | 55 |
| 4 | laohe | 70 |
| 5 | laoyang | 66 |
| 6 | laowang | 75 |
| 7 | laozhang | 81 |
+----+----------+-------+
7 rows in set (0.00 sec)
mysql> select * from people;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | laohuang | 20 |
| 2 | laoliang | 18 |
| 3 | laoli | 19 |
| 4 | laohe | 21 |
+----+----------+-----+
4 rows in set (0.00 sec)
- join
mysql> select people.`name`, people.age, student.`name`, student.score from people join student on people.`name` = student.`name`;
+-------+-----+-------+-------+
| name | age | name | score |
+-------+-----+-------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
+-------+-----+-------+-------+
2 rows in set (0.00 sec)
- inner join
mysql> select people.`name`, people.age, student.`name`, student.score from people inner join student on people.`name` = student.`name`;
+-------+-----+-------+-------+
| name | age | name | score |
+-------+-----+-------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
+-------+-----+-------+-------+
2 rows in set (0.00 sec)
- left join
# people left join student
mysql> select people.`name`, people.age, student.`name`, student.score from people left join student on people.`name` = student.`name`;
+----------+-----+-------+-------+
| name | age | name | score |
+----------+-----+-------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
| laohuang | 20 | NULL | NULL |
| laoliang | 18 | NULL | NULL |
+----------+-----+-------+-------+
4 rows in set (0.00 sec)
# student left join people
mysql> select people.`name`, people.age, student.`name`, student.score from student left join people on people.`name` = student.`name`;
+-------+------+----------+-------+
| name | age | name | score |
+-------+------+----------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
| NULL | NULL | laozhang | 80 |
| NULL | NULL | laowang | 60 |
| NULL | NULL | laoyang | 66 |
| NULL | NULL | laowang | 75 |
| NULL | NULL | laozhang | 81 |
+-------+------+----------+-------+
7 rows in set (0.00 sec)
- right join
# people right join student
mysql> select people.`name`, people.age, student.`name`, student.score from people right join student on people.`name` = student.`name`;
+-------+------+----------+-------+
| name | age | name | score |
+-------+------+----------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
| NULL | NULL | laozhang | 80 |
| NULL | NULL | laowang | 60 |
| NULL | NULL | laoyang | 66 |
| NULL | NULL | laowang | 75 |
| NULL | NULL | laozhang | 81 |
+-------+------+----------+-------+
7 rows in set (0.00 sec)
# student right join people
mysql> select people.`name`, people.age, student.`name`, student.score from student right join people on people.`name` = student.`name`;
+----------+-----+-------+-------+
| name | age | name | score |
+----------+-----+-------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
| laohuang | 20 | NULL | NULL |
| laoliang | 18 | NULL | NULL |
+----------+-----+-------+-------+
4 rows in set (0.00 sec)
- union:
mysql> select people.`name`, people.age, student.`name`, student.score from student
-> right join people on people.`name` = student.`name`
-> union
-> select people.`name`, people.age, student.`name`, student.score from student
-> left join people on people.`name` = student.`name`;
+----------+------+----------+-------+
| name | age | name | score |
+----------+------+----------+-------+
| laoli | 19 | laoli | 55 |
| laohe | 21 | laohe | 70 |
| laohuang | 20 | NULL | NULL |
| laoliang | 18 | NULL | NULL |
| NULL | NULL | laozhang | 80 |
| NULL | NULL | laowang | 60 |
| NULL | NULL | laoyang | 66 |
| NULL | NULL | laowang | 75 |
| NULL | NULL | laozhang | 81 |
+----------+------+----------+-------+
9 rows in set (0.00 sec)