待发基础网
首页 基础百科 正文

SQL基础知识500例

来源:待发基础网 2024-07-11 10:55:20

  SQL是结构化查询语言的缩写,是用于管理关系数据库管理系的语言来自www.bangsongkuaidi.com。在现代信息技术中,SQL已成为一种非常重要的技能aXX。本文将为大家介SQL基础知识500例,帮助读者更好掌握SQL语言待.发.基.础.网

  一、基础语法

  1.创建一个数据库

  CREATE DATABASE database_name;

  2.使用一个数据库

  USE database_name;

  3.创建一个表

  CREATE TABLE table_name (

  column1 datatype,

column2 datatype,

  column3 datatype,

  ....

  );

4.插入数据

  INSERT INTO table_name (column1, column2, column3, ...)

  VALUES (value1, value2, value3, ...);

5.查询数据

SELECT column1, column2, ...

FROM table_name

  WHERE condition;

6.更数据

  UPDATE table_name

  SET column1 = value1, column2 = value2, ...

  WHERE condition;

  7.删除数据

  DELETE FROM table_name

  WHERE condition;

  二、数据

  8.整型

  INT, INTEGER

  9.

FLOAT, DOUBLE, DECIMAL

  10.字符型

  CHAR, VARCHAR, TEXT

11.日期型

DATE, DATETIME, TIMESTAMP

  三、约束

12.主键

  CREATE TABLE table_name (

  column1 datatype PRIMARY KEY,

  column2 datatype,

  column3 datatype,

  ....

);

  13.唯一约束

  CREATE TABLE table_name (

  column1 datatype UNIQUE,

column2 datatype,

  column3 datatype,

  ....

);

14.非空约束

  CREATE TABLE table_name (

column1 datatype NOT NULL,

  column2 datatype,

column3 datatype,

  ....

  );

  15.默认值约束

  CREATE TABLE table_name (

column1 datatype DEFAULT default_value,

  column2 datatype,

  column3 datatype,

....

  );

16.外键约束

CREATE TABLE table_name1 (

  column1 datatype PRIMARY KEY,

  column2 datatype,

  column3 datatype,

  ....

  );

  CREATE TABLE table_name2 (

  column1 datatype,

column2 datatype,

  column3 datatype,

  ....

  FOREIGN KEY (column1) REFERENCES table_name1(column1)

  );

四、函数

  17.计数函数

  SELECT COUNT(*) FROM table_name;

  18.求和函数

  SELECT SUM(column_name) FROM table_name;

19.平均值函数

SELECT AVG(column_name) FROM table_name;

  20.最大值函数

SELECT MAX(column_name) FROM table_name;

  21.最值函数

  SELECT MIN(column_name) FROM table_name;

  22.字符串函数

SELECT CONCAT(column1, column2) FROM table_name;

  23.日期函数

SELECT DATE_FORMAT(column_name, '%Y-%m-%d') FROM table_name;

  五、连接

  24.内连接

  SELECT column_name(s)

  FROM table1

  INNER JOIN table2

  ON table1.column_name = table2.column_name;

  25.连接

SELECT column_name(s)

  FROM table1

LEFT JOIN table2

  ON table1.column_name = table2.column_name;

  26.右连接

  SELECT column_name(s)

FROM table1

  RIGHT JOIN table2

  ON table1.column_name = table2.column_name;

27.全连接

  SELECT column_name(s)

  FROM table1

  FULL OUTER JOIN table2

  ON table1.column_name = table2.column_name;

六、子查询

28.量子查询

  SELECT column_name

  FROM table_name

  WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

  29.列子查询

  SELECT column_name

FROM table_name

WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);

30.行子查询

  SELECT column_name

  FROM table_name

  WHERE (column1, column2) = (SELECT column1, column2 FROM table_name WHERE condition);

  七、分组和聚合

  31.分组

  SELECT column_name, COUNT(*)

  FROM table_name

GROUP BY column_name;

  32.分组和聚合

SELECT column_name, COUNT(*), AVG(column_name), SUM(column_name)

  FROM table_name

GROUP BY column_name;

  33.分组和过滤

SELECT column_name, COUNT(*)

  FROM table_name

  GROUP BY column_name

  HAVING COUNT(*) > 1;

八、排序

  34.升序排序

  SELECT column_name

  FROM table_name

ORDER BY column_name ASC;

35.降序排序

  SELECT column_name

FROM table_name

  ORDER BY column_name DESC;

九、限制

  36.限制结果集

  SELECT column_name

  FROM table_name

  LIMIT number;

37.分页

  SELECT column_name

  FROM table_name

  LIMIT start, number;

  、索引

  38.创建索引

  CREATE INDEX index_name

ON table_name (column_name);

  39.删除索引

  DROP INDEX index_name

  ON table_name;

  40.查看索引

  SHOW INDEX FROM table_name;

一、事务

41.开始事务

SQL基础知识500例(1)

  BEGIN;

  42.提交事务

COMMIT;

  43.回滚事务

ROLLBACK;

二、备份和恢复

  44.备份数据库

  mysqldump -u root -p database_name > backup.sql;

  45.恢复数据库

mysql -u root -p database_name < backup.sql;

  三、权限管理

  46.创建用户

  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

47.授权

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

  48.撤销权限

  REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

49.删除用户

DROP USER 'username'@'localhost';

  50.查看权限

  SHOW GRANTS FOR 'username'@'localhost';

  总结

本文介了SQL基础知识500例,包括基础语法、数据型、约束、函数、连接、子查询、分组和聚合、排序、限制、索引、事务、备份和恢复、权限管理方面待~发~基~础~网。希望读者能够通过本文更好掌握SQL语言,提高自己的技能水平aXX

标签 基础知识
我说两句
0 条评论
请遵守当地法律法规
最新评论

还没有评论,快来做评论第一人吧!
相关文章
最新更新
最新推荐