快速入门

安装

# 连接
$ mysql -u root -p

常用命令

# 查看数据库列表
$ show databases;

# 切换库
$ use user-db;

# 查看所有表
$ show tables;

# 查看表字段信息
$ desc user;

# 查看表数据
$ select * from user;

库表语句

# 创建数据库
create database if not exists `user-db` default character set = 'utf8mb4';

# 创建表
create table `user` (
    user_id int not null primary key auto_increment comment '主键',
    name varchar(255) not null comment '姓名',
    age int comment '年龄',
    address varchar(255) comment '地址',
    create_time timestamp default current_timestamp comment '创建时间'
) comment '用户表';

# 修改表名
alter table `user` rename `users`;

# 给表新增列
alter table `user` add column `hobby` varchar(255) comment '爱好';

# 删除表中某一列
alter table `user` drop `hobby`;

alter table `user` drop `hobby`,drop `address`;

# 修改表中列的结构
alter table `user` modify `age` varchar(100) comment '年龄';

查询

# 创建数据库
create database if not exists `user-db` default character set = 'utf8mb4';

# 创建表
create table `user` (
    user_id int not null primary key auto_increment comment '主键',
    name varchar(255) not null comment '姓名',
    age int comment '年龄',
    address varchar(255) comment '地址',
    create_time timestamp default current_timestamp comment '创建时间'
) comment '用户表';

# 修改表名
alter table `user` rename `users`;

# 给表新增列
alter table `user` add column `hobby` varchar(255) comment '爱好';

# 删除表中某一列
alter table `user` drop `hobby`;

alter table `user` drop `hobby`,drop `address`;

# 修改表中列的结构
alter table `user` modify `age` varchar(100) comment '年龄';

# 查询某一列
select name from `user`;

# 定义列的别名
select name as user_name from `user`;

# 查询多个列
select user_id,name from `user`;

# 查询全部
select * from `user`;

# 排序
select * from `user` order by create_time desc;

# 指定查询条数(开始行:0行,数据:3条)
select * from `user` limit 0,3;

# 条件查询
select * from `user` where name = 'a';

# 联合查询
select * from `user` where name = 'a' and age >= 18;

select * from `user` where name = 'a' or age >= 18;

# 模糊查询
select * from `user` where name like '%张%';

新增

# 新增
insert into `user` (name, age, address) VALUES ('张三','20','北京');

删除

# 删除
delete from `user` where user_id = 2;

# 批量删除
delete from `user` where user_id in (10,11,12);

更新

# 更新
update `user` set name = '管理员', address = '深圳' where user_id = 1;

函数

# 表达式
select age + 10 as age from `user`;

# 拼接
select concat(`name`, '-new') as name from `user`;

# 截取
select left(`name`,1) from `user`;

# 求和
select sum(age) from `user`;

# 平均数
select avg(age) from `user`;

# 最大值
select max(age) from `user`;

# 统计
select count(1) from `user`;

子查询

# 子查询
select * from `post` where user_id  = (select user_id from `user` where name = '张三');

连表查询

# 连表查询
# 内连接
select * from `user`,`post` where user.user_id = post.user_id;

# 外连接
# 外连接-左连接
# 结果以user表为主，如果没有填充null
select * from `user` left join `post` on `user`.user_id = `post`.user_id;

# 外连接-右连接
select * from `user` right join `post` on `user`.user_id = `post`.user_id;