sql语句增删改查

PercyXuBiao

已于 2022-07-18 17:10:53 修改

阅读量374

点赞数 1

文章标签：大数据 mysql 数据库

于 2022-07-16 15:59:51 首次发布

本文链接：https://blog.csdn.net/PercyXuBiao/article/details/125819679

版权

库

增加

create database +库名；

删除

drop database + 库名；

修改

rename database 老名字 to 新名字；

进入数据库 use +库名字；

查找

查看当前库 select database（）；

查看当前库下所有表格 show tables；

表

增加

create table +表名（表结构）；

create table t1 (id int, name varchar(20), age int);
字段类型字段类型(长度)，字段类型

删除

删除表 drop table 表名；

修改

修改表名 1 rename table 旧表名 to 新表名；

修改表名 2 alter table 旧表名 rename 新表名；

查找

查看所有表 show tables；

查看表状态 show table status like ’表名‘\G；---每条SQL语句会以分号结尾，想看的清楚一些以\G结尾，一条记录一条记录显示。（把表90度向左反转，第一列显示字段，第二列显示记录）使用的\G就不用添加分号了

查看表结构 desc 表名；

查看表创建过程 show create table 表名；

查看表内所有记录 select 内容 from 表名；

select * from 表名；（*）代表所有内容

查看表内指定字段 select 字段，字段 from 表名；

字段与记录的区别

字段

增加

添加字段 alter table 表名 add字段名数据类型；

添加字段到指定为位置 alter table 表名 add 添加的字段(和类型) after name; -------把添加的字段放到name后面（如果放到第一位把after修改成first即可）

删除

删除指定字段 alter table 表名 drop 字段名；

修改

修改字段1 alter table 表名 modify 字段名新数据类型；（不可以修改字段名字）

修改字段2 alter table 表名 change 旧字段名字新字段名字新数据类型；（可以修改字段名字）

查找

记录

增加

添加记录 insert into 表名（字段1，字段2）values（数据1，数据2）；

添加指记录 insert into 表名 set 字段=（“数据”）；

删除

删除记录 delete from 表名 where 条件；

删除记录 delete from 表名；（将表内所有记录删除）

修改

修改记录 update 表名 set 字段=记录 where 条件；

查找

查找字段 select 字段名 from表名；

select * from 表名；

单表查询

语法：
select 字段名称,字段名称2 from 表名条件

简单查询
mysql> select * from employee5;

多字段查询：
mysql> select id,name,sex from employee5;

有条件查询：where
mysql> select id,name from employee5 where id<=3;
mysql> select id,name,salary from employee5 where salary>2000;

设置别名：as
mysql> select id,name,salary as "salry_num" from employee5 where salary>5000;
给 salary 的值起个别名，显示值的表头会是设置的别名

统计记录数量：count()
mysql> select count(*) from employee5;

统计字段得到数量:
mysql> select count(id) from employee5;

避免重复DISTINCT:表里面的数据有相同的
mysql> select distinct post from employee5;
#字段表名

表复制：key不会被复制: 主键、外键和索引

复制表
1.复制表结构＋记录 （key不会复制: 主键、外键和索引）
语法:create table 新表 select * from 旧表;
mysql> create table new_t1 select * from employee5;

2.复制单个字段和记录:
mysql> create table new_t2(select id,name from employee5);

3.多条件查询: and ----和
语法: select 字段，字段2 from 表名 where 条件 and 条件；
mysql> select name,salary from employee5 where post='hr' AND salary>1000;
mysql> select name,salary from employee5 where post='instructor' AND salary>1000;

4.多条件查询: or ----或者
语法： select 字段，字段2 from 表名 where 条件 or 条件；
mysql> select name from employee5 where salary>5000 and salary<10000 or dep_id=102;
mysql> select name from employee5 where salary>2000 and salary<6000 or dep_id=100;

5.关键字 BETWEEN AND 什么和什么之间。
mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
mysql> select name,dep_id,salary from employee5 where not salary>5000;
注:not 给条件取反

6.关键字IS NULL 空的
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL; #-取反不是null
mysql> SELECT name,job_description FROM employee5 WHERE job_description=''; #什么都没有==空
NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。

7.关键字IN集合查询
一般查询：
mysql> SELECT name,salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
IN集合查询
mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000);
mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000); #取反

8.排序查询 order by ：指令，在mysql是排序的意思。
mysql> select name,salary from employee5 order by salary; #-默认从小到大排序。
mysql> select name,salary from employee5 order by salary desc; #降序，从大到小

9.limit 限制
mysql> select * from employee5 limit 5; #只显示前5行
mysql> select name,salary from employee5 order by salary desc limit 0,1; #从第几行开始，打印一行
查找什么内容从那张表里面降序排序只打印第二行。
注意：
0-------默认第一行
1------第二行依次类推...
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5; #降序，打印5行
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 4,5; #从第5条开始，共显示5条
mysql> SELECT * FROM employee5 ORDER BY salary LIMIT 4,3; #默认从第5条开始显示3条。

10.分组查询：group by
mysql> select count(name), post from employee5 group by post;

count可以计算字段里面有多少条记录，如果分组会分组做计算
mysql> select count(name),group_concat(name) from employee5 where salary>5000;
查找统计（条件：工资大于5000）的有几个人（count(name)），分别是谁（group_concat(name)）

11.GROUP BY 和 GROUP_CONCAT() 函数一起使用
GROUP_CONCAT()-------组连接
mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; #以dep_id分的组，dep_id这个组里面都有谁
mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id; #给组连接设置了一个别名

12.函数
max() 最大值
mysql> select max(salary) from employee5;
查询薪水最高的人的详细信息：
mysql> select name,sex,hire_date,post,salary,dep_id from employee5 where salary = (SELECT MAX(salary) from employee5);
# select name,salary from t1 where salary =(select max(salary) from t1);
min()最小值
select min(salary) from employee5;
avg()平均值
select avg(salary) from employee5;
now() 现在的时间
select now();
sum() 计算和
select sum(salary) from employee5 where post='sale';

多表查询

内连接查询：只显示表中有匹配的数据
只找出有相同部门的员工
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id;

外连接：在做多张表查询时，所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接.外连接分为三种

左外连接：表A left [outer] join 表B on 关联条件，表A是主表，表B是从表
右外连接：表A right [outer] join 表B on 关联条件，表B是主表，表A是从表
全外连接：表A full [outer] join 表B on 关联条件，两张表的数据不管满不满足条件，都做显示。

1.左外链接
mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;

2.右外连接
案例：找出所有部门包含的员工
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;

表完整性约束

作用：用于保证数据的完整性和一致性

约束条件说明
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表之间的关联
NULL 标识是否允许为空，默认为NULL。
NOT NULL 标识该字段不能为空，可以修改。
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号，正数

1.主键
每张表里只能有一个主键，不能为空，而且唯一，主键保证记录的唯一性，主键自动为NOT NULL。
一个 UNIQUE KEY 又是一个NOT NULL的时候，那么它被当做PRIMARY KEY主键。

定义两种方式：
#表存在，添加约束
mysql> alter table t7 add primary key (hostname);

创建表并指定约束
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));

删除主键
mysql> alter table tab_name drop primary key;

2.auto_increment自增--------自动编号，且必须与主键组合使用默认情况下，起始值为1，每次的增量为1。当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况：
- 如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
- 如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。
（每张表只能有一个字段为自曾）（成了key才可以自动增长）
mysql> CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(30),
comment VARCHAR(50)
);

删除自动增长
mysql> ALTER TABLE department3 CHANGE dept_id dept_id INT NOT NULL;

3.设置唯一约束 UNIQUE，字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。
mysql> CREATE TABLE department2 (
dept_id INT,
dept_name VARCHAR(30) UNIQUE,
comment VARCHAR(50)
);

指定字符集:

修改字符集：在创建表的最后面指定一下： default charset=utf8 #可以指定中文

* 创建表格式指定字符集为utf-8
mysql> create table t6(id int(2),name char(5),age int(4)) default charset=utf8;

修改字符集 #alter table Student default charset=utf8;

MYSQL常见的数据类型

破解密码

先将这个修改成简单密码注释掉

root账户没了或者root密码丢失：
关闭Mysql使用下面方式进入Mysql直接修改表权限
5.6/5.7版本：
# mysqld --skip-grant-tables --user=mysql &

# mysql -uroot
mysql> UPDATE mysql.user SET authentication_string=password('QianFeng@123') WHERE user='root' AND host='localhsot';
mysql> FLUSH PRIVILEGES;

#编辑配置文件将skip-grant-tables参数注释
#重启mysql