基础

DDL 数据定义语言

[]：表示可选

1、DDL-数据库操作

show databases;

create database 数据库名;

user 数据库名;

select database();

drop database [if exit] 数据库名;

2、DDL-表操作

show tables;

create table 表名(…);

desc 表名;

show create tables 表名;

alter table 表名 add/modif/change/drop/rename/TO…;

drop table [if exit] 表名;

DML

1、给指定字段添加数据

insert into 表名(字段1, 字段2…) values(值1, 值2…)

2.给全部字段添加数据

inset into 表名 values(值1, 值2…)

3、批量添加数据

insert into 表名(字段1, 字段2…) values(值1, 值2…), (值1, 值2…), …

DML-修改数据

update 表名 set 字段1=值1, 字段2=值2, … [where 条件]

eg: update user  set name = ‘zhangsan’ where id = 1;

DML-删除数据

delete from 表名 [where 条件]

删除所有数据：delete from user;

删除指定数据：delete from user where id = 1;

— 练习
— 1.
select * from emp where age BETWEEN 30 AND 41;

select * from emp;

— 答案
select * from emp where gender=’女’ and age in(20,21,22,23);
— 2.

select * FROM emp where name like ‘___’ AND age BETWEEN 20 AND 40 and gender=’男’;
— 答案
select * from emp where gender=’男’ and age BETWEEN 20 AND 40 AND `name` LIKE ‘___’;

— 3 正确
select gender AS ‘性别’, count(*) AS ‘人数’ from emp WHERE age <60 GROUP BY gender;

select count(*) as ‘人数’ from emp where age < 60;

— 4 错误
select name as ‘姓名’, age AS ‘年龄’ from emp ORDER BY age asc, entryDate DESC;

select name as ‘姓名’, age AS ‘年龄’ , entryDate AS ‘入职时间’ from emp ORDER BY age asc, entryDate DESC;
— 答案
select name, age from emp where age<=35 ORDER BY age ASC, entryDate DESC
— 5 正确
SELECT * from emp where gender=’男’ ORDER BY age ASC, entryDate DESC limit 0,5;