DDL:Data Definition Language # 数据定义语言 (create、alter)
DCL:Data Control Language # 数据控制语言 (grant、revoke、commit、rollback)
DML:Data Manipulate Language # 数据操作语言 (insert、delete、update)
DQL:Data Query Language # 数据查询语言 (select、desc)
二、DDL --数据定义语言
1. create 操作库
1)查看语法
mysql> help create database;
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
#1.修改表字符集
mysql> alter table play charset utf8mb4;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#2.修改表名
mysql> alter table play rename student;
Query OK, 0 rows affected (0.01 sec)
#3.给表插入字段
mysql> alter table student add name varchar(10);
#4.给表插入多个字段
mysql> alter table student add sex enum('男','女'),add age tinyint;
#5.插入字段到第一列
mysql> alter table student add id int first;
#6.插入字段到指定字段后面
mysql> alter table student add class varchar(12) after id;
#7.删除字段
mysql> alter table student drop qiudao;
#8.修改列属性
mysql> alter table student modify name varchar(20);
#9.修改列的名字和属性
mysql> alter table student change sex xingbie enum('男','女');
三、DCL--数据控制语言
1.授权命令 grant
#1.授权
grant all on *.* to root@'172.16.1.%' identified by '123';
#应该授权低一点的权限
grant select,update,insert on database.* to dev@'172.16.1.%' identified by 'Lhd@123456'#2.查看用户权限
mysql> show grants for root@'localhost';
#3.特殊权限授权
max_queries_per_hour:一个用户每小时可发出的查询数量
max_updates_per_hour:一个用户每小时可发出的更新数量
max_connections_per_hour:一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to lhd@'localhost' identified by '123' with max_connections_per_hour 1;
max_user_connections:允许同时连接数量
mysql> grant all on *.* to test@'localhost' identified by '123' with max_user_connections 2;
2.回收权限 revoke
#1.回收权限
mysql> revoke drop on *.* from test@'localhost';
#2.查看权限
mysql> show grants fortest@'localhost';
#3.所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
3.授权一个超级管理员
mysql> grant all on *.* to superuser@'localhost' identified by 'Lhd@123456' with grant option;
四、DML--数据操作语言
1.insert命令
#1.插入数据之前先看表结构
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | enum('男','女') | YES | | 男 | |
| age | tinyint(3) unsigned | YES | | NULL | |
| cometime | datetime | YES | | CURRENT_TIMESTAMP | |
| class | varchar(12) | NO | | NULL | |
| status | enum('0','1') | YES | | 0 | |
+----------+---------------------+------+-----+-------------------+----------------+
7 rows inset (0.00 sec)
#2.insert不规范写法
mysql> insert student values('4','周昊','男','20','2020-10-23','Linux10','0');
#一个字段都不能少#3.规范的写法(只插入需要的值)
mysql> insert student(name,class) values('邱导','linux10');
mysql> insert student(name,class,age) values('邱导','linux10',20);
#4.规范写法插入多条数据
mysql> insert student(name,class,age) values('增加','linux10',20),('减少','linux10',30);
2.update语句
#1.修改数据之前一定要确认数据
mysql> select * from student;
#2.修改数据,错误写法
mysql> update student set age='16';
#3.修改数据,正确写法
mysql> update student set age='18'whereid=1;
#4.如果说就是修改表中所有数据
mysql> update student set age='18'where 1=1;
3.delete语句
#1.错误语法
mysql> delete from student;
#2.正确写法
mysql> delete from student whereid=1;
#3.删除
mysql> delete from student where name='增加' and cometime='2020-10-23 20:16:57';
#4.使用状态列表示已删除
mysql> update student set status='1'whereid=9;
mysql> select * from student where status='0';
#1.查看库下面的表
mysql> show tables from world;
mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows inset (0.00 sec)
#2.查看表结构
mysql> desc city;
#3.查询所有数据
mysql> select count (*) from city;
mysql> select * from city;
#4.查询指定列数据
mysql> select name,population from city;
#5.按照人口数量排序#升序
mysql> select name,population from city order by population;
#降序
mysql> select name,population from city order by population desc;
#6.查看人口数量最多排名前十的城市
mysql> select name,population from city order by population desc limit 10;
#7.按照步长查询数据#查询数据从10后面开始计算,展示20条数据,20就是步长
mysql> select id,name,population from city limit 10,20;
mysql> select id,name,population from city limit 0,60;
mysql> select id,name,population from city limit 60,60;
mysql> select id,name,population from city limit 120,60;
3.按条件查询
#1.条件查询where的符号where的条件符号: = < > >= <= != <>
where的连接符:and or like in#2.查看中国城市的人口数量
mysql> select CountryCode,name,population from city where CountryCode='CHN';
#3.查看黑龙江省城市的人口数量
mysql> select CountryCode,District,name,population from city where CountryCode='CHN' and District='heilongjiang';
#4.查询中国人口数量小于10万的城市
mysql> select CountryCode,population,name from city where CountryCode='CHN' and population<'100000';
#5.查看国家代码以H开头的
mysql> select * from city where CountryCode like 'H%';
#6.查看国家代码以H结尾的
mysql> select * from city where CountryCode like '%H';
#7.查看国家代码包含H的
mysql> select * from city where CountryCode like '%H%';
#8.查询中国城市和美国城市的人口数量
mysql> select CountryCode,name,population from city where CountryCode='CHN' or CountryCode='USA';
mysql> select CountryCode,name,population from city where CountryCode in ('CHN','USA');
#9.联合查询
mysql> select CountryCode,name,population from city where CountryCode='CHN' union all select CountryCode,name,population from city where CountryCode='USA';
#查看qiudao的成绩
1.方式一:
mysql> select student1.name,score.mark from student1,score where student1.id='1' and score.id='1';
2.方式二:
mysql> select student1.name,score.mark from student1,score where student1.id=score.id and name='qiudao';
6)查询题1:
#查询世界上小于100人的城市是哪个国家的?#1.审题:查看需要查询哪些数据
城市名字 城市人口数量 国家名字
#2.找到查询内容的字段在哪个表
城市名字 城市人口数量 国家名字
city.name city.population country.name
#3.找出两个表中关联的列
city.countrycode
country.code
#4.编写语句
select city.name,city.population,country.name from city,country where city.countrycode=country.code and city.population < '100';
select city.name,city.population,country.name from city natural join country where city.population < '100';
7)多表联查练习题2:
#查询世界上小于100人的城市是哪个国家的,使用什么语言?#1.审题:查看需要查询哪些数据
城市名字 城市人口数量 国家名字 国家的语言
#2.找到查询内容的字段在哪个表
城市名字 城市人口数量 国家名字 国家的语言
city.name city.population country.name countrylanguage.language
#3.找出三个表相关联的列
city.countrycode
country.code
countrylanguage.CountryCode
#4.编写语句
select city.name,city.population,country.name,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.CountryCode and city.population < '100';
2.自连接
#自连接会自动关联两个表中数据相同的字段,自连接的两个表必须有相同的字段和数据
1)自连接查询
#查询人口数量大于100万的城市,列出他们的国家代码和国家语言
1.传统连接:
select city.name,city.population,countrylanguage.CountryCode,countrylanguage.language from city,countrylanguage where countrylanguage.CountryCode=city.CountryCode and city.population > '1000000';
2.自连接:
select city.name,city.population,countrylanguage.CountryCode,countrylanguage.language from city natural join countrylanguage where city.population > '1000000';
#注意:
1.自连接会自动去获取两个表之间的关联列和数据,所以自连接的两个表必须有相同的字段和数据
3.内连接
1)语法
select * from 表1 join 表2 on 关联条件 where 条件
#注意:
表 1 是小表
表 2 是大表
2)例子:
#查询世界上小于100人的城市是哪个国家的,国家代码是什么
1.传统链接:
select city.population,city.name,country.name,country.code from city,country where country.code=city.countrycode and city.population < '100';
2.内连接:
select city.population,city.name,country.name,country.code from country join city on country.code=city.countrycode where city.population < '100';
3)内连接三表联查
#查询世界上小于100人的城市是哪个国家的,用什么语言?
select city.population,city.name,country.name,countrylanguage.language from country join city on city.countrycode=country.code join countrylanguage on country.code=countrylanguage.countrycode where city.population < '100';
4.外连接
1)左外连接
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100;
2)右外连接
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100;
5.UNION(合并查询)
#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');
#替换为:
mysql> select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA'limit 10;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥