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)
扫一扫
453
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
