美文网首页
SQL语句大全

SQL语句大全

作者: brave_wu | 来源:发表于2022-12-05 15:24 被阅读0次

搜集所闻所见的SQL语句,无论简单还是复杂,只按时间顺序,持续更新。。。

CREATE DATABASE mydb1

创建一个名为 mydb1 的数据库。如果这个数据库已经存在会报错

CREATE DATABASE IF NOT EXISTS mydb1

如果名为 mydb1 的数据库不存在就创建该库

DROP DATABASE mydb1

删除一个名为 mydb1 的数据库。如果这个数据库已经删除会报错

DROP DATABASE IF EXISTS mydb1

如果名为 mydb1 的数据库存在就删除该库

CREATE TABLE stu(
    sid CHAR(6), 
    sname VARCHAR(20), 
    age INT, 
    gender VARCHAR(10)
);

创建stu表

ALTER TABLE stu ADD (classname varchar(100));

给 stu 表添加 classname 列

ALTER TABLE stu MODIFY gender CHAR(2);

修改 stu 表的 gender 列类型为 CHAR(2)

ALTER TABLE stu change gender sex CHAR(2);

修改 stu 表的 gender 列名为 sex

ALTER TABLE stu DROP classname;

删除列:删除 stu 表的 classname 列

ALTER TABLE stu RENAME TO student;

修改 stu 表名称为 student

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');
INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');
INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

插入数据到stu表

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

更新数据到stu表

DELETE FROM stu WHERE sid=’s_1001’003B;
DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;
DELETE FROM stu;
TRUNCATE TABLE stu;// 先DROP TABLE,再 CREATE TABLE,速度快无法回滚

删除stu中的数据

CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;

创建一个数据库用户

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;

给用户授权

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

取消授权

SHOW GRANTS FOR user1@localhost;

查看授权

DROP USER ‘user1’@localhost;

删除权限

use mysql;
alter user '用户名'@localhost identified by '新密码';

修改密码

SELECT * FROM stu;
SELECT sid, sname, age FROM stu;
SELECT * FROM stu WHERE gender='female' AND age<50;//且
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';//或
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003')
SELECT * FROM stu WHERE sid NOT IN ('S_1001','S_1002','S_1003');
SELECT * FROM stu WHERE age IS NULL;
SELECT * FROM stu WHERE age>=20 AND age<=40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
SELECT * FROM stu WHERE gender!='male';//非
SELECT * FROM stu WHERE gender<>'male';//非
SELECT * FROM stu WHERE NOT gender='male';
SELECT * FROM stu WHERE NOT sname IS NULL;
SELECT * FROM stu WHERE sname IS NOT NULL;
SELECT * FROM stu WHERE sname LIKE '_ _ _ _ _';
SELECT * FROM stu WHERE sname LIKE '_ _ _ _i';
SELECT * FROM stu WHERE sname LIKE 'z%';//以“z”开头
SELECT * FROM stu WHERE sname LIKE '_i%';//以"某i"开头
SELECT * FROM stu WHERE sname LIKE '%a%';//包含“a”字母
SELECT DISTINCT sal FROM emp;//去重
SELECT *,sal+comm FROM emp;//查询sal+comm之和
SELECT *, sal+IFNULL(comm,0) FROM emp;//上条的优化,把comm中的null转为0
SELECT *, sal+IFNULL(comm,0) total FROM emp;//上条的优化,取列名为total
SELECT * FROM stu ORDER BY sage ASC;//按年龄升序排序
SELECT * FROM stu ORDER BY sage;
SELECT * FROM stu ORDER BY age DESC;//降序
SELECT * FROM emp ORDER BY age DESC ,empno ASC;//如果年龄相同时,按empno升序排序
SELECT COUNT(*) AS cnt FROM emp;//统计表数据条数
SELECT COUNT(comm) cnt FROM emp;//统计表中comm有数据的条数
SELECT COUNT(*) FROM emp WHERE sal > 2500;
SELECT COUNT(comm), COUNT(mgr) FROM emp;//统计comm有数据的条数和mgr有数据的条数
SELECT SUM(sal) FROM emp;//sal的总和
SELECT SUM(sal), SUM(comm) FROM emp;
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
SELECT SUM(sal), COUNT(sal) FROM emp;//平均值
SELECT AVG(sal) FROM emp;//平均值
SELECT MAX(sal), MIN(sal) FROM emp;//最高和最低
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

SELECT deptno ,COUNT(*)
FROM emp
WHERE sal>1500`
GROUP BY deptno;

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

SELECT * FROM emp LIMIT 0, 5;//查询 5 行记录,起始行从 0 开始
SELECT * FROM emp LIMIT 3, 10;

select staff.name,deptname from staff,deptno where
staff.name=deptno.name;//内链接
select staff.name,deptname from staff left join deptno onstaff.name=deptno.name;//外连接(左连接)
select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;//外连接(右连接)

查询表中的列

特别鸣谢:
史上最全SQL基础知识总结

相关文章

  • 【搬运】MySQL语句

    mysql sql语句大全

  • Mysql的 sql 语句大全

    Mysql的 sql 语句大全

  • sql

    sql经典语句经典SQL语句大全(绝对的经典) - 浪迹天涯芳草 - 博客园 sql语法SQL语句查询语句完整语法...

  • mysql

    ##mysql sql语句大全 ====================== ####1、说明:创建数据库 CRE...

  • SQL语句大全

    SQL语句参考,包含Access、MySQL 以及 SQL Server 基础 创建数据库 CREATE DATA...

  • SQL语句大全

    50个常用的sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cna...

  • sql语句大全

    一、基础 1、创建数据库(create) CREATE DATABASE database-namecreate ...

  • sql 语句大全

    1.查询去重:SELECT DISTINCT vend_id FROM table1; 2.指定查询范围:SELE...

  • 常用sql注入语句

    转载链接 渗透常用SQL注入语句大全(网上收集) 记一次通过fckeditor入侵提权拿服务器 常用sql注入语句

  • 关系数据库常用SQL语句语法大全

    此人写的很详细,我就借用了哈 SQL语句语法大全

网友评论

      本文标题:SQL语句大全

      本文链接:https://www.haomeiwen.com/subject/jaobfdtx.html