20220602_MySQL部分示例语句_库表操作_数据操作_条件查询

-- DDL
show databases;
use databasename;
select database();
create database student;
drop database name;
drop database if exists name;
create table stu;
show table status;
show tables;
desc stu;
drop table stu;
drop table stu if exists;

-- DML
insert into stu(id,name) values(1,'罗翔');
select * from stu;
insert into stu(id,name,gender,birth,score,email,tel,status)
values(2,'张三','男','1999-11-11','99.8','zhangsan@mysqlstu.com','090-4122-7837',8);
insert into stu values(2,'张三','男','1999-11-11','99.8','zhangsan@mysqlstu.com','090-4122-7837',8);

update stu set name = '马大帅', birth = '1957-02-23', score = 99.9, email = 'madashuai@kaiyuan.com',
tel = '090-4746-6754' where id = 2;
desc stu;
update stu set gender='男', birth='1974-12-11', score=100.1 where name = '罗翔';
update stu set gender = '女';
update stu set gender = '男', status = 9 where id = 2 order by id desc limit 1;
update stu set id = 3, name = '范德彪', gender = '男', birth = '1967-08-19', score = 98.8,
email = 'fandebiao@victoria.com', tel = '090-3645-6534' where status = 8;

insert into stu(id,name,gender) values(4, '张三', '男');
delete from stu where name = '张三';
update stu set gender = '男' where name = '罗翔';

改表

rename table stu to bilibili;
alter table bilibili add address varchar(50);
alter table bilibili modify address char(50);
desc bilibili;
alter table bilibili change address addr varchar(30);
alter table bilibili drop addr;

create table `new money`(
id int,
name varchar(20),
age int,
gender varchar(5),
address varchar(100),
math double(5,2),
english double(5,2),
build_busi_date date);
insert into `new money` values
(1,'马冬梅', 55, '女', '天津', 66,98, '1958-09-01'),
(2, '刘强东', 45, '男', '南京', 78,87, '1970-11-18'),
(3, '马斯克', 55, '男', '纽约', 88,99, '1975-06-26');

insert into `new money` values
(1,'雷军', 54, '男', '北京', 96,58, '1968-09-01'),
(2, '董明珠', 53, '女', '东莞', 78,87, '1970-11-18'),
(1,'李秀丽', 40, '女', '杭州', 66,98, '1978-09-01'),
(2, '吴京', 45, '男', '北京', 78,87, '1970-11-18'),
(3, '姜文', 59, '男', '北京', 88,90, '1975-06-26');

update `new money` set id = 4 where name = '雷军';
update `new money` set id = 5 where name = '董明珠';
update `new money` set id = 6 where name = '李秀丽';
update `new money` set id = 7 where name = '吴京';
update `new money` set id = 8 where name = '姜文';

select name, age from `new money`;
update `new money` set math = null where name = '李秀丽';
select address from `new money`;
select distinct address from `new money`;
select name, math, english from `new money`;
select name, math 数学成绩, english as 英语成绩 from `new money`;

本段用表图
修改了数学英语重复分数英语部分，三句，语法如下

update `new money` set english = 88 where id = 5;

select name from `new money` where age > 50;
select name from `new money` where age >= 55;
select name from `new money` where age >= 40 && age <= 54;
select name from `new money` where build_busi_date 
between '1975-01-01' and '1978-12-12';
select name from `new money` where age = 45;
select name from `new money` where age != 55;
SELECT name FROM `new money` where age = 40 or age = 45 or age = 54;
select name from `new money` where math is null;
select * from `new money` where age <> 55;
select * from `new money` where age in (53,54,55);
SELECT * from `new money` where name = '马%';
SELECT * from `new money` where name like '_明%';

DQL_排序

SELECT * from `new money` order by age;
select * from `new money` order by math desc, english;

DQL_聚合函数

select count(id) from `new money`;
select max(english) from `new money`;
SELECT min(math) from `new money`;
select sum(math) from `new money`;
select avg(math) from `new money`;-- 计算平均数时 null值所在对象不会成为平均对象

DQL_分组

select avg(math), count(*) from `new money`group by gender;
select avg(math), count(math) from `new money`group by gender;
select avg(english),count(*) from `new money` where english > 80 group by gender;
select gender, avg(english),count(*) from `new money` where english > 80 group by gender;
select gender, avg(english),count(*) from `new money` where english > 80 group by gender having count(*) > 3;

DQL_分页查询

select * from `new money` LIMIT 0,3;
select * from `new money` limit 3,3;
select * from `new money` limit 6,3;