SQL基础语言

DDL   Data Definition Language

数据库操作
查询所有数据库

show databases;

创建数据库

create database workdb;

如果不存在则创建

create database if not exists workdb;

删除数据库

drop database test;

如果存在则删除

drop database if exists test;

使用数据库

use workdb;

查询当前数据库

select database();

表操作
创建表

CREATE TABLE user_tb (
    id INT COMMENT '编号',
    name VARCHAR(50) COMMENT '姓名',
    age INT COMMENT '年龄',
    gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

查询当前数据库所有表

show tables;

查询表结构

desc user_tb;

查询创建表语句

show create table user_tb;

添加字段

alter table user_tb add nickname varchar(50) comment '昵称';

修改字段名和字段类型

alter table user_tb change nickname username varchar(30) comment '用户名';

修改数据类型

alter table user_tb modify username varchar(50);

删除字段

alter table user_tb drop username;

修改表名

alter table user_tb rename to employee;

删除表

drop table if exists user_tb;

删除指定表，并重新创建该表(保留表的结构、索引和约束等信息)

truncate table employee;

DML   Data Manipulation Language

给指定字段添加数据

INSERT INTO employee (id, name, age, gender) VALUES (1, 'wuyj', 20, '男');

给全部字段添加字段

INSERT INTO employee VALUES (2, 'zhangsan', 20, '男');

批量添加数据

INSERT INTO employee (id, name, age, gender) VALUES (3, 'lisi', 20, '男'),(4, 'wangwu', 20, '女');
INSERT INTO employee VALUES (3, 'lisi', 20, '男'),(4, 'wangwu', 20, '女');

修改数据  修改id为1的name

update employee set name = 'laoliu' where id = 1;

修改表所有的gender数据

update employee set gender = '男';

删除数据

delete from employee where id = 1;

删除表所有的数据

delete from employee;

DQL   Data Query Language

查询多个字段

select name,age from employee;

查询所有字段

select * from employee;

设置别名

select name as '姓名' from employee;

查询去除重复记录

select distinct gender from employee;

条件查询

select * from employee where gender = '女' and age <= 30;

select * from employee where gender = '女' and age in(21,22,23);

select * from employee where gender = '男' and age between 20 and 40;

 聚合函数

select count(id) from employee;
selectmax(age) from employee;
select min(age) from employee;
select avg(age) from employee;
select sum(age) from employee;

分组查询     having过滤，可不写

select gender, count(*) from employee group by gender having max(age) <= 30;

 排序查询
升序

select * from employee order by age asc;

降序

select * from employee order by age desc;

对年龄进行排序，相同再根据id排序

select * from employee order by age asc,id asc;

分页查询
查询第一页员工数据10条

select * from employee limit 0,10;

查询第二页员工数据10条       （页码 - 1)*展示的页数

select * from employee limit 10,10;

 DCL   Data Control Language

管理用户

查询用户

use mysql;
select * from user;

创建用户

当前主机可以访问

create user 'itcast'@'localhost' identified by '123456'; 

任意主机可以访问

create user 'heima'@'%' identified by '123456';

修改用户密码

alter user 'itcast'@'localhost' identified with mysql_native_password by '12345'; 

删除用户

drop user 'itcast'@'localhost';

权限控制

查询权限

show grants for 'heima'@'%';

授予权限

grant all on itcast.* to 'heima'@'%';

 撤销权限

revoke all on itcast.* from 'heima'@'%';