语法：alter table table_name add primary key (column_name)
示例：将用户 id 设为主键
alter table users add primary key (`id`)
1.5 为已存在的列创建自增约束
更常用的方式是在创建表语句中添加自增列 id int not null auto_increment。
alter table `users` modify `id` int not null auto_increment
2. 插入
语法：
insert into table_name values (value1, value2, …)
insert into table_name (column1, column2, …) values (value1, value2, …)
示例：新增注册用户
insert into `users` values (1, 'ken', 'http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg', curdate())
/* 指定列插入 */
insert into `users` (`name`, `avatar`) values ('bill', 'http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg')
3. 修改 3.1 修改数据记录
语法：
update table_name set column=new_value where condition
update table_name set column1=new_value1,column2=new_value2,… wherecondition
示例：
update `users` set `regtime`=curdate() where `regtime` is null
/* 一次修改多列 */
update `users` set `name`='steven',`avatar`='http://cdn.awesome_app.com/path/to/xxx/steven.jpg' where `id`=1
3.2 修改数据库字符集为 utf8
alter database `awesome_app` default character set utf8
3.3 修改表字符集为 utf8
alter table `users` convert to character set utf8
3.4 修改表字段字符集为 utf8
alter table `users` modify `name` char(10) character set utf8
3.5 修改字段类型
alter table `users` modify `regtime` datetime not null
3.5 修改字段默认值
alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/* 设置默认为当前时间 current_timestamp，需要重新定义整个列 */
alter table `users` modify `regtime` datetime not null default current_timestamp
3.6 修改字段注释
alter table `users` modify `id` int not null auto_increment comment '用户ID';
alter table `users` modify `name` char(10) comment '用户名';
alter table `users` modify `avatar` varchar(300) comment '用户头像';
alter table `users` modify `regtime` datetime not null default current_timestamp comment '注册时间';
修改后，查看改动后的列：
mysql> show full columns from users;
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 用户ID |
| name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户名 |
| avatar | varchar(300) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户头像 |
| regtime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | 注册时间 |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
4. 删除 4.1 删除数据记录
语法：delete from table_name where condition
示例：删除用户名未填写的用户
# 先增加一条用户名为空的用户
mysql> insert into `users` (`regtime`) values (curdate());
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name | avatar | regtime |
+----+--------+----------------------------------------------------+------------+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
| 3 | NULL | NULL | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+
# 删除用户名为空的行
mysql> delete from `users` where `name` is null;
mysql> select * from users;
+----+--------+----------------------------------------------------+------------+
| id | name | avatar | regtime |
+----+--------+----------------------------------------------------+------------+
| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
| 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
+----+--------+----------------------------------------------------+------------+
4.2 删除数据库
drop database if exists `awesome_app`
4.3 删除表
drop table if exists `users`
4.4 清空表中所有数据
这个操作相当于先 drop table 再 create table ，因此需要有 drop 权限。
mysql> select id,name from users limit 2;
+----+--------+
| id | name |
+----+--------+
| 1 | steven |
| 2 | bill |
+----+--------+
查询从指定偏移(第一行为偏移为0)开始的几行
mysql> select id,name from users limit 2,3;
+----+--------+
| id | name |
+----+--------+
| 3 | 张三 |
| 4 | 李四 |
| 5 | 王五 |
+----+--------+
5.2.6 排序
# 正序
mysql> select distinct name from users order by name asc limit 3;
+--------+
| name |
+--------+
| bill |
| steven |
| 刘八 |
+--------+
# 倒序
mysql> select id,name from users order by id desc limit 3;
+----+--------+
| id | name |
+----+--------+
| 13 | 王五 |
| 12 | 李四 |
| 11 | 张三 |
+----+--------+
5.2.7 分组
增加城市字段
alter table `users` add `city` varchar(10) comment '用户所在城市' after `name`;
update `users` set `city`='旧金山' where `id`=1;
update `users` set `city`='西雅图' where `id`=2;
update `users` set `city`='北京' where `id` in (3,5,7);
update `users` set `city`='上海' where `id` in (4,6,8);
update `users` set `city`='广州' where `id` between 9 and 10;
update `users` set `city`='深圳' where `id` between 11 and 13;
按城市分组统计用户数
mysql> select city, count(name) as num_of_user from users group by city;
+-----------+-------------+
| city | num_of_user |
+-----------+-------------+
| 上海 | 3 |
| 北京 | 3 |
| 广州 | 2 |
| 旧金山 | 1 |
| 深圳 | 3 |
| 西雅图 | 1 |
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user=1;
+-----------+-------------+
| city | num_of_user |
+-----------+-------------+
| 旧金山 | 1 |
| 西雅图 | 1 |
+-----------+-------------+
mysql> select city, count(name) as num_of_user from users group by city having num_of_user>2;
+--------+-------------+
| city | num_of_user |
+--------+-------------+
| 上海 | 3 |
| 北京 | 3 |
| 深圳 | 3 |
+--------+-------------+
5.3 多表关联查询
5.3.1 准备数据
create table if not exists `orders`
(
`id` int not null primary key auto_increment comment '订单ID',
`title` varchar(50) not null comment '订单标题',
`user_id` int not null comment '用户ID',
`cretime` timestamp not null default current_timestamp comment '创建时间'
);
create table if not exists `groups`
(
`id` int not null primary key auto_increment comment '用户组ID',
`title` varchar(50) not null comment '用户组标题',
`cretime` timestamp not null default current_timestamp comment '创建时间'
);
alter table `users` add `group_id` int comment '用户分组' after `city`;
insert into `groups` (`title`) values ('大佬'), ('萌新'), ('菜鸡');
insert into `orders` (`title`, `user_id`) values ('《大佬是怎样炼成的？》', 3), ('《MySQL 从萌新到删库跑路》', 6), ('《菜鸡踩坑记》', 9);
update `users` set `group_id`=1 where `id` between 1 and 2;
update `users` set `group_id`=2 where `id` in (4, 6, 8, 10, 12);
update `users` set `group_id`=3 where `id` in (3, 5, 13);
5.3.2 join
join
用于在多个表中查询相互匹配的数据。
mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users`, `orders` where `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title |
+-----------+--------------------------------------+
| 张三 | 《大佬是怎样炼成的？》 |
| 马六 | 《MySQL 从萌新到删库跑路》 |
| 杨九 | 《菜鸡踩坑记》 |
+-----------+--------------------------------------+
inner join
内部连接。效果与 join 一样 , 但用法不同，join 使用 where ，inner join 使用 on 。
mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` inner join `orders` on `orders`.`user_id`=`users`.`id`;
+-----------+--------------------------------------+
| user_name | order_title |
+-----------+--------------------------------------+
| 张三 | 《大佬是怎样炼成的？》 |
| 马六 | 《MySQL 从萌新到删库跑路》 |
| 杨九 | 《菜鸡踩坑记》 |
+-----------+--------------------------------------+
数据库
发布于：2021-06-25 11:52
|
阅读数：418
|
评论：0
QQ好友和群
QQ空间
