MySQL常用基本指令
1 常用数据库操作指令
- 创建数据库
create database - 删除数据库
drop databases - 查看数据库
show databases - 修改数据库
alert database
1.1 创建数据库 create
CREATE DATABASE IF NOT EXISTS dbtest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
1.2 删除数据库 drop
DROP DATABASES db_name
1.3 查看数据库
1.3.1 查看当前所有的数据库show
SHOW DATABASES;
1.3.2 查看当前操作的数据库 SELECT
SELECT DATABASE()
1.3.3 查看数据库的版本信息 status
status
1.3.4 查看MYSQL所支持的字符集
SHOW CHARSET;
1.3.5 查看数据库的字符集
SHOW CREATE DATABASE db_name
1.4 修改数据库 alert
1.4.1 设置数据库的字符集
ALTER DATABASE test2 DEFAULT CHARACTER SET GBK;
修改数据库忽略大小写
2 常用数据表基本指令
- 创建表
- 删除表
- 查看表
- 修改表
2.1 创建表
create table if not exists userinfo(
id int not null primary key auto_increment,
name varchar(32) not null,
age int(10) not null,
sex bit default 1)
ENGINE=InnoDB
DEFAULT CHARSET=utf8;
2.2 删除表
DROP TABLE tb_name ;
2.3 查看表
2.3.1 查看表
SHOW TABLES;
2.3.2 查看表结构
DESC tb_name;
2.3.3 查看创建语法
show create table userinfo;
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int(10) NOT NULL,
`sex` bit(1) DEFAULT b'1',
PRIMARY KEY (`id`) ###尾部设置主键###
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.4 修改表
2.4.1 增加字段 add
增加字段(默认增加在最后一列)
alter table userinfo add isdelete int null default 0;
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| isdelete | int(11) | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
增加字段在第一列 FIRST
alter table userinfo add first_test_id int null default 0 first;
mysql> desc userinfo;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| first_test_id | int(11) | YES | | 0 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| isdelete | int(11) | YES | | 0 | |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
增加字段在指定位置增加 AFTER
alter table userinfo add age int null default 1 after name;
mysql> desc userinfo;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| first_test_id | int(11) | YES | | 0 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| age | int(11) | YES | | 1 | |
| isdelete | int(11) | YES | | 0 | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
2.4.2 删除字段
mysql> alter table userinfo drop first_test_id;
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| age | int(11) | YES | | 1 | |
| isdelete | int(11) | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
注意：如果数据表中只剩余一个字段则无法使用DROP来删除字段。
2.4.3 修改字段
修改字段的属性 MODIFY
alter table userinfo modify name varchar(11); # 将name字段中的varchar(32)-->varchar(11)
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(11) | YES | | NULL | |
| age | int(11) | YES | | 1 | |
| isdelete | int(11) | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
修改字段的名称 change
alter table userinfo change age sex varchar(11) not null;
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(11) | YES | | NULL | |
| sex | varchar(11) | NO | | NULL | |
| isdelete | int(11) | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
modify 和 change的区别
您可以使用CHANGE old_col_namecolumn_definition子句对列进行重命名。重命名时，需给定旧的和新的列名称和列当前的类型。例如：要把一个 INTEGER 列的名称从 a 变更到 b，您需要如下操作：
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
如果您想要更改列的类型而不是名称， CHANGE语法仍然要求旧的和新的列名称，即使旧的和新的列名称是一样的。例如：
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
您也可以使用MODIFY来改变列的类型，此时不需要重命名：
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
修改默认值
alter table userinfo alter sex set default 'man';
mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(11) | YES | | NULL | |
| sex | varchar(11) | NO | | man | |
| isdelete | int(11) | YES | | 0 | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
2.4.4 修改表名
alter table t1 rename t2;
2.4.5 修改数据表的引擎
alter table tableName ENGINE = MyISAM (InnoDB);
2.4.6 主键的删除和切换
# 删除主键
alter table tablename drop primary key;
drop primary key on tablename;
# 重新设置主键
mysql> alter table tablename add primary key(id);
3 常用数据的操作指令
- 插入数据
- 删除数据
- 修改数据
3.1 插入数据 insert
3.1.1 对应数据插入
insert into userinfo values(0,'Kevin', 18, 1); # 注意：对应的表后面跟valuses值
3.1.2 缺省插入数据
insert into userinfo(name, age) values('Bob',25);
3.1.3 多行插入
# 多行全列插入
insert into userinfo values(4,'Emilie',18, 0),(5,'Hogo',25, 1); # 注意当使用全列插入的时候列值每一个都不能少，包括NULL以及DEFAULT类型的值都需要进行填写。
# 多行缺省插入
insert into userinfo (name,age) values ('En',15), ('Siile',17);
3.2 删除数据
3.2.1 物理删除 delete from
delete from userinfo where id = 7;
注意：delete from + 表名
意义:彻底的将数据从数据库中删除。
3.2.2 逻辑删除 update...set...
update userinfo set isdelete=1 where id=5;
意义：定义一种删除数据的状态。实际上数据依然在数据库中。并没有删除。
3.3 数据的修改
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
update userinfo set name=Kevin,isdelete=1 where id=1;
注意：update + 表名字 而不是 update table + 表名和alter table 进行区分。
4 MySQL的数据查询
- 条件
- 聚合查询
- 分组
- 排序
- 分页
- 关系
- 连接
- 自关联
- 子查询
- 内置函数
- 视图
4.1 条件查询 where
4.1.1 比较运算符
- 等于
- 大于
- 大于等于
- 小于
- 小于等于
- 不等于
mysql> select * from user where id > 3;
+----+------+------+-------------+
| id | name | sex | email |
+----+------+------+-------------+
| 4 | dev | 男 | dev@qq.com |
| 5 | Lisa | 女 | lisa@qq.com |
+----+------+------+-------------+
4.1.2 逻辑运算符
- and
- or
- not
# and
mysql> select * from user where id > 3 and sex = '女';
+----+------+------+-------------+
| id | name | sex | email |
+----+------+------+-------------+
| 5 | Lisa | 女 | lisa@qq.com |
+----+------+------+-------------+
# not
mysql> select * from user where not (sex = '女');
+----+-------+------+--------------+
| id | name | sex | email |
+----+-------+------+--------------+
| 1 | Kevin | 男 | kevin@qq.com |
| 2 | Bob | 男 | Bob@qq.com |
| 3 | root | 男 | root@qq.com |
| 4 | dev | 男 | dev@qq.com |
+----+-------+------+--------------+
4 rows in set (0.00 sec)
4.1.3 模糊查询
- like
- %表示任意多个字符
- _表示一个任意字符
mysql> select * from user where name like 'K%';
+----+-------+------+--------------+
| id | name | sex | email |
+----+-------+------+--------------+
| 1 | Kevin | 男 | kevin@qq.com |
+----+-------+------+--------------+
mysql> select * from user where name like '%o%';
+----+------+------+-------------+
| id | name | sex | email |
+----+------+------+-------------+
| 2 | Bob | 男 | Bob@qq.com |
| 3 | root | 男 | root@qq.com |
+----+------+------+-------------+
2 rows in set (0.00 sec)
4.1.4 范围查询
- in 非连续的范围
- between...and...连续的范围
mysql> select * from user where id in(1,4);
+----+-------+------+--------------+
| id | name | sex | email |
+----+-------+------+--------------+
| 1 | Kevin | 男 | kevin@qq.com |
| 4 | dev | 男 | dev@qq.com |
+----+-------+------+--------------+
2 rows in set (0.00 sec)
mysql> select * from user where id between 1 and 4;
+----+-------+------+--------------+
| id | name | sex | email |
+----+-------+------+--------------+
| 1 | Kevin | 男 | kevin@qq.com |
| 2 | Bob | 男 | Bob@qq.com |
| 3 | root | 男 | root@qq.com |
| 4 | dev | 男 | dev@qq.com |
+----+-------+------+--------------+
4 rows in set (0.01 sec)
4.1.5 空判断
mysql> select * from user where email is null;
Empty set (0.00 sec)
mysql> select * from user where email is not null;
+----+-------+------+--------------+
| id | name | sex | email |
+----+-------+------+--------------+
| 1 | Kevin | 男 | kevin@qq.com |
| 2 | Bob | 男 | Bob@qq.com |
| 3 | root | 男 | root@qq.com |
| 4 | dev | 男 | dev@qq.com |
| 5 | Lisa | 女 | lisa@qq.com |
+----+-------+------+--------------+
5 rows in set (0.00 sec)
注意:
- null 是 字符串 '' 有很大的区别
4.1.6 优先级
- 小括号, not, 比较运算符, 逻辑运算符
- and 比 or 先运算, 如果同时出现并希望先算 or,需要结合()使用
4.2 聚合查询
- count()
- max()
- min()
- sum()
- avg()
4.2.1 count
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
4.2.2 max
mysql> select max(id) from user;
+---------+
| max(id) |
+---------+
| 5 |
+---------+
1 row in set (0.05 sec)
4.2.3 min
mysql> select min(id) from user;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
4.2.4 sum
mysql> select sum(id) from user;
+---------+
| sum(id) |
+---------+
| 15 |
+---------+
1 row in set (0.00 sec)
4.2.5 avg
mysql> select avg(id) from user;
+---------+
| avg(id) |
+---------+
| 3.0000 |
+---------+
1 row in set (0.00 sec)
4.3 分组查询 group by
- 按照字段分组，表示此字段相同的数据会被放到一个组中
- 分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
- 可以对分组后的数据进行统计，做聚合运算
mysql> select sex,count(*) from user group by sex;
+------+----------+
| sex | count(*) |
+------+----------+
| 女 | 1 |
| 男 | 4 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select isdelete,count(*) from user group by isdelete;
+----------+----------+
| isdelete | count(*) |
+----------+----------+
| 0 | 2 |
| 1 | 3 |
+----------+----------+
2 rows in set (0.00 sec)
4.3.1 分组后的筛选 having
mysql> select count(*) from user where sex = '男';
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select sex,count(*) from user group by sex having sex = '男';
+------+----------+
| sex | count(*) |
+------+----------+
| 男 | 4 |
+------+----------+
1 row in set (0.00 sec)
注意:
- where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
- having是对group by的结果进行筛选
4.4 排序查询 order by
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
# 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
# - asc从小到大排列，即升序
# -desc从大到小排序，即降序
mysql> select * from user order by id desc;
+----+-------+------+--------------+----------+
| id | name | sex | email | isdelete |
+----+-------+------+--------------+----------+
| 5 | Lisa | 女 | lisa@qq.com | 1 |
| 4 | dev | 男 | dev@qq.com | 1 |
| 3 | root | 男 | root@qq.com | 0 |
| 2 | Bob | 男 | Bob@qq.com | 0 |
| 1 | Kevin | 男 | kevin@qq.com | 1 |
+----+-------+------+--------------+----------+
5 rows in set (0.00 sec)
4.5 分页查询 limit start,count
# limit start,count
# 从start开始，获取count条数据
mysql> select * from user limit 1,4;
+----+------+------+-------------+----------+
| id | name | sex | email | isdelete |
+----+------+------+-------------+----------+
| 2 | Bob | 男 | Bob@qq.com | 0 |
| 3 | root | 男 | root@qq.com | 0 |
| 4 | dev | 男 | dev@qq.com | 1 |
| 5 | Lisa | 女 | lisa@qq.com | 1 |
+----+------+------+-------------+----------+
4.6 完整的SQL语句的顺序
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count
4.7 关系查询
4.7.1 一对多
# 创建用户表 用户-部门表
create table userinfo(
id int not null primary key auto_increment,
name varchar(32),
department_id int,
constraint fk_user_depar foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;
# 创建部门表
create table department(
id int not null primary key auto_increment,
title char(15)
)engine=innodb default charset=utf8;
# inner join...on...
1 查看 userinfo 表
mysql> select * from userinfo;
+----+--------+---------------+
| id | name | department_id |
+----+--------+---------------+
| 1 | 小红 | 2 |
| 2 | 小芳 | 2 |
| 3 | 大牛 | 1 |
| 4 | 小牛 | 1 |
+----+--------+---------------+
2 查看 department 表
mysql> select * from department;
+----+-----------+
| id | title |
+----+-----------+
| 1 | 技术部 |
| 2 | 人事部 |
| 3 | 财务部 |
+----+-----------+
3 rows in set (0.00 sec)
需求
- 连接查询用户名对应的部门表
select
userinfo.name,
department.title
from userinfo
inner join department
on # 跟关系
userinfo.department_id = department.id;
+--------+-----------+
| name | title |
+--------+-----------+
| 小红 | 人事部 |
| 小芳 | 人事部 |
| 大牛 | 技术部 |
| 小牛 | 技术部 |
+--------+-----------+
4 rows in set (0.00 sec)
4.7.2 一对一
# 创建user表 用户-管理员
create table user(
id int not null primary key auto_increment,
name char(10),
sex char(10),
email varchar(64)
)engine=innodb default charset = utf8;
# 创建admin表
create table admin(
id int not null primary key auto_increment,
username varchar(64) not null,
password varchar(64) not null,
user_id int not null,
unique uq_u1 (user_id),
constraint fk_admin_user foreign key(user_id) references user(id)
)engine=innodb default charset = utf8;
show create table admin;
4.7.3 多对多
自关联
# 创建用户表 约会(约会表中有男有女)
create table persion(
id int not null primary key auto_increment,
name varchar(64) not null,
sex varchar(64) not null,
age varchar(64) not null
)engine = innodb default charset = utf8;
# 创建约会表
create table love(
id int not null primary key auto_increment,
boy_id int not null,
girl_id int not null,
constraint fk_love_per_boy foreign key (boy_id) references persion(id),
constraint fk_love_per_girl foreign key (girl_id) references persion(id)
)engine = innodb default charset = utf8;
两表关联
# 运维人员
create table progammer(
id int auto_increment primary key,
name char(10),
sex char(10),
email varchar(64)
)engine=innodb default charset=utf8;
# 维护服务器
create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;
create table progammer2host(
id int auto_increment primary key,
user_id int not null,
host_id int not null,
unique uq_user_host (user_id,host_id), # 联合唯一索引
CONSTRAINT fk_u2h_user FOREIGN key (user_id) REFERENCES progammer(id),
CONSTRAINT fk_u2h_host FOREIGN key (host_id) REFERENCES host(id)
)engine=innodb default charset=utf8;
网友评论