🍁博客主页:👉@不会压弯的小飞侠 ✨欢迎关注:👉点赞👍收藏⭐留言✒ ✨系列专栏:👉MySQL数据库专栏 ✨欢迎加
🍁博客主页:
👉@不会压弯的小飞侠
✨欢迎关注:
👉点赞
👍收藏
⭐留言
✒
✨系列专栏:
👉MySQL数据库专栏
✨欢迎加入社区:
👉不会压弯的小飞侠
✨人生格言:知足上进,不负野心。
🔥欢迎大佬指正,一起学习!一起加油!
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增册改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
show databases;
select databases();
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
drop database if exists 数据库名
use 数据库名;
案例:
mysql> show databases;+--------------------+| Database |+--------------------+| infORMation_schema || cakeshop || cloud_order || cloud_user || could_order || could_user || demo || estore || jdbc || jdbcone || jdbctestone || jkj || mybatis || mybatisplus || mybatisthree || mysql || performance_schema || personmessage || reggie || spring || SpringBoot || SSM || ssmp || store || student || teachinfo || test || testfive || testfour || testone || xfx |+--------------------+31 rows in set (0.00 sec)
mysql> creat database demo;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near 'creat database demo' at line 1
mysql> create database if not exists jkj;Query OK, 1 row affected (0.01 sec)
mysql> drop database if exists jkj;Query OK, 0 rows affected (0.04 sec)
mysql> create database jkj default charset utf8mb4;Query OK, 1 row affected (0.00 sec)
mysql> use jkj;Database changedmysql>
mysql> select database();+------------+| database() |+------------+| jkj |+------------+1 row in set (0.00 sec)
show tables;
desc 表名;
show create table 表名;
案例分析:
1.查询当前数据库所有表,此时为空
mysql> show tables;Empty set (0.01 sec)
查询tb_user表结构
mysql> desc tb_user;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(50) | YES | | NULL | || age | int(11) | YES | | NULL | || gender | varchar(1) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set, 4 warnings (0.03 sec)
查询tb_user表的建表语句
mysql> show create table tb_user;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb_user | CREATE TABLE `tb_user` ( `id` int(11) DEFAULT NULL COMMENT '编号', `name` varchar(50) DEFAULT NULL COMMENT '姓名', `age` int(11) DEFAULT NULL COMMENT '年龄', `gender` varchar(1) DEFAULT NULL COMMENT '性别') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表' |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
创建表结构
create table 表名(字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释],字段3 字段3类型 [COMMENT 字段3注释],字段n 字段n类型 [COMMENT 字段n注释]) [COMMENT 表注释];
注意:[...]为可选参数,最后一个字段后面没有逗号
案例:
mysql> create table tb_user( -> id int comment '编号', -> name varchar(50) comment '姓名', -> age int comment '年龄', -> gender varchar(1) comment '性别' -> ) comment '用户表';Query OK, 0 rows affected (0.03 sec)
MySQL与Java一样,也有数据类型。MySQL中数据类型主要应用在列上。
常用类型:
int:整型 如果字段是int类型 那么不需要指定长度 如果是自增的字段一定要是int类型
double:浮点型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
decimal:泛型,在表单钱方面使用该类型,因为不会出现精度缺失问题;decimal(5,2)
char:固定长度字符串类型; char(10)
varchar:可变长度字符串类型; varchar(10)
text:字符串类型;String
blob:字节类型;
date:日期类型,格式为:yyyy-MM-dd;
time:时间类型,格式为:hh:mm:ss
timestamp:时间长类型;
案例:
mysql> create table emp( -> id int comment '编号', -> workno varchar(10) comment '工号', -> name varchar(10) comment '姓名', -> gender char(1) comment '性别', -> age tinyint unsigned comment '年龄', -> idcard char(18) comment '身份证号', -> entrydate date comment '入职时间' -> ) comment '员工表';Query OK, 0 rows affected (0.03 sec)
查询表结构:
mysql> desc emp;+-----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || workno | varchar(10) | YES | | NULL | || name | varchar(10) | YES | | NULL | || gender | char(1) | YES | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || idcard | char(18) | YES | | NULL | || entrydate | date | YES | | NULL | |+-----------+---------------------+------+-----+---------+-------+7 rows in set, 7 warnings (0.03 sec)
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
mysql> alter table emp add nickname varchar(20) comment '昵称';Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;+-----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || workno | varchar(10) | YES | | NULL | || name | varchar(10) | YES | | NULL | || gender | char(1) | YES | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || idcard | char(18) | YES | | NULL | || entrydate | date | YES | | NULL | || nickname | varchar(20) | YES | | NULL | |+-----------+---------------------+------+-----+---------+-------+8 rows in set, 8 warnings (0.02 sec)
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [COMMENT注释][约束];
案例:
将emp表的nickname字段修改为username,类型为varchar(15)
mysql> alter table emp change nickname username varchar(15) comment '昵称';Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;+-----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || workno | varchar(10) | YES | | NULL | || name | varchar(10) | YES | | NULL | || gender | char(1) | YES | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || idcard | char(18) | YES | | NULL | || entrydate | date | YES | | NULL | || username | varchar(15) | YES | | NULL | |+-----------+---------------------+------+-----+---------+-------+8 rows in set, 8 warnings (0.01 sec)
删除字段
alter table 表名 drop 字段名;
mysql> alter table emp drop username;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;+-----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || workno | varchar(10) | YES | | NULL | || name | varchar(10) | YES | | NULL | || gender | char(1) | YES | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || idcard | char(18) | YES | | NULL | || entrydate | date | YES | | NULL | |+-----------+---------------------+------+-----+---------+-------+7 rows in set, 7 warnings (0.01 sec)
修改表名
alter table 表名 rename to 新表名;
mysql> alter table emp rename to employee;Query OK, 0 rows affected (0.00 sec)
mysql> desc emp;ERROR 1146 (42S02): Table 'jkj.emp' doesn't existmysql> desc employee;+-----------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || workno | varchar(10) | YES | | NULL | || name | varchar(10) | YES | | NULL | || gender | char(1) | YES | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || idcard | char(18) | YES | | NULL | || entrydate | date | YES | | NULL | |+-----------+---------------------+------+-----+---------+-------+7 rows in set, 7 warnings (0.01 sec)
删除表
drop table [ if exists] 表名;
mysql> show tables;+---------------+| Tables_in_jkj |+---------------+| employee || tb_user || xfx |+---------------+
mysql> drop table if exists xfx;Query OK, 0 rows affected (0.00 sec)
mysql> show tables;+---------------+| Tables_in_jkj |+---------------+| employee || tb_user |+---------------+2 rows in set (0.00 sec)
删除指定表,并重新创建该表(可以理解为清空表)
truncate table 表名;
mysql> desc tb_user;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(50) | YES | | NULL | || age | int(11) | YES | | NULL | || gender | varchar(1) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set, 4 warnings (0.01 sec)
mysql> truncate table tb_user;Query OK, 0 rows affected (0.00 sec)
给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);
给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
批量添加数据
INSERT INTO 表名(字段名1,字段名2,.….)VALUES(值1,值2,..),(值1,值2...),(值1,值2,.….);
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
案例:
1.给指定字段添加数据
insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'001','tom','男',3,'111111111111111111','2022-01-01');select * from employee;
给全部字段添加数据
insert into employee values(2,'002','cat','女',5,'222222222222222222','2000-01-01');select * from employee;
批量添加数据
insert into employee values(3,'003','aaa','女',12,'333333333333333333','2006-02-01'),(4,'004','bbb','男',16,'444444444444444444','2007-09-01');select * from employee;
UPDATE 表名 SET 字段名1=值1,字段名2=值2,....[ WHERE条件];
update employee set name='zzz' where id=1;select * from employee;
修改id为1的数据,将name修改为xfx,age修改为88
update employee set name='xfx',age=88 where id=1;select * from employee;
将所有员工入职日期修改为2000-01-01
update employee set entrydate='2000-01-01';select * from employee;
DELETE FROM 表名 [ WHERE条件]
案例:
1.删除gender为男的员工
delete from employee where gender='男';select * from employee;
删除所有员工
delete from employee;select * from employee;
总结:
查询多个字段
select 字段1, 字段2, 字段3 ... from 表名 ;
select * from 表名 ;
字段设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] ... from 表名;
select 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... from 表名;
去除重复记录
select distinct 字段列表 from 表名;
create table employee(id int comment '编号',workno varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',idcard char(18) comment '身份证号',workaddress varchar(50) comment '工作地址',entrydate date comment '入职时间')comment '员工表';
select name,age from employee;
select id ,workno,name,gender,age,idcard,workaddress,entrydate from employee;
select workaddress as '工作地址' from employee;
select distinct workaddress as '工作地址' from employee;
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN … AND … | 在某个范围之内(含最小、最大值) |
IN(…) | 在in之后的列表中的值,多选一 |
LIKE | 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且 (多个条件同时成立) |
OR | 或者 (多个条件任意一个成立) |
NOT | 或 ! 非 , 不是 |
注意:|| :或者 (多个条件任意一个成立)
和or一样。
select * from employee where age=45;
select * from employee where age<35;
select * from employee where age<=18;
select * from employee where idcard is null;
select * from employee where idcard is not null;
select * from employee where age !=18;
select * from employee where age between 18 and 25;select * from employee where age>=18 && age<=25;select * from employee where age>=18 and age<=25;
select * from employee where gender='女' and age<=25;
查询年龄等于18 或 20 或 45 的员工信息
select * from employee where age=18 or age=20 or age=45;select * from employee where age in(18,20,45);
查询姓名为两个字的员工信息
select * from employee where name like '__';
查询身份证号最后一位是X的员工信息
select * from employee where idcard like '%X';
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
SELECT 聚合函数(字段列表) FROM 表名 ;
select count(*) from employee;
select count(idcard) from employee;
select avg(age) from employee;
select max(age) from employee;
select min(age) from employee;
select sum(age) from employee where workaddress='北京';
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
select gender,count(*) from employee group by gender;
select gender,avg(age) from employee group by gender;
select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;
select workaddress, gender, count(*) '数量' from employee group by gender,workaddress;
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
ASC : 升序(默认值)
DESC: 降序
select * from employee order by age asc;
select * from employee order by entrydate desc;
select * from employee order by age asc , entrydate desc;
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
select * from employee limit 0,5;select * from employee limit 5;
select * from employee limit 5,5;
DQL语句的执行顺序为: from ... where ... group by ...having ... select ... order by ... limit ...
查询用户
select * from mysql.user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_passWord BY '新密码' ;
删除用户
DROP USER '用户名'@'主机名' ;
CREATE USER 'xfx'@'localhost' IDENTIFIED BY '123456';select * from mysql.user;
CREATE USER 'any'@'%' IDENTIFIED BY '123456';select * from mysql.user;
修改用户any的访问密码为111111;
7版本之后
alter user 'any'@'%' identified with mysql_native_password by '111111';
drop user 'xfx'@'localhost';select * from mysql.user;
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
show grants for 'any'@'%';
grant all on xfx.* to 'any'@'%';
revoke all on xfx.* from 'any'@'%';
来源地址:https://blog.csdn.net/qq_43514330/article/details/126531405
--结束END--
本文标题: 猿创征文 |【MySQL数据库一SQL 语句】
本文链接: https://www.lsjlt.com/news/403097.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0