SQL语句&详细的实战&帮助

SQL语句的学习

  • SQL

    • Structured Query Language 结构化查询语言

  • 四种语句

    • DDL
      • Data Definition Language数据定义语言
        • CTEATE,DROP和ALTER
        • 创建表，删除库表，添加字段和索引
    • DCL
      • Data Contral Language授权控制
        • GRANT和REVOKE
        • commit 用于事务
        • Rollback用于回滚
        • 确定单个用户和用户组对数据可以对象进行访问，授权以及取消权限
    • DML
      • Data Manipulation Language 数据操作语言
        • INSERT,UPDATE和DELETE
        • 添加，修改和删除表中的数据
    • DQL
      • Data Query Language (数据查询语言)
        • SELECT查询

DDL数据定义语言

DDL

创建数据库，删除数据库，添加表字段，表索引

#创建数据库lol
#数据库对大小写不敏感
#内置的SQL关键字全部大写，然后自己定义的数据，可以进行大小写管理
#最简单的缩写方式
mysql> CREATE DATABASE lol;
Query OK, 1 row affected (0.00 sec)
#如果在创建就会报错了
mysql> CREATE DATABASE lol;
ERROR 1007 (HY000): Can't create database 'lol'; database exists
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lol                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
#比较完善的写法，创建数据库这个写法九不会出现上述报错了
CREATE DATABASE IF NOT EXIsTS lol;
mysql> CREATE DATABASE IF NOT EXIsTS lol;
Query OK, 1 row affected, 1 warning (0.00 sec)

#最完善的写法，包含了数据库的字符集指定，让你的mysql能够支持UTF-8编码，支持中文,指定了字符集大小写九会不敏感 
CREATE DATABASE IF NOT EXISTS lol2 CHARSET UTF8 COLLATE utf8_general_ci;
mysql> CREATE DATABASE IF NOT EXISTS lol2 CHARSET UTF8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
#查看数据库创建的SQL是什么，下面这个编码插入中文有问题
mysql> SHOW CREATE DATABASE lol;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| lol      | CREATE DATABASE `lol` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
#下面语句支持中文
mysql> SHOW CREATE DATABASE lol2;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| lol2     | CREATE DATABASE `lol2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

DQL查询

#查询mysql库中默认的用户表信息，进行展示
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)
#查看数据库中有多少表
mysql> show tables;
#查看user数据表的信息
#查看user结构
mysql> desc user;
#查看user所有表信息，查出特别多特别乱
mysql> select * from user;
#指定字段查询，usr,host,passeord
mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)

DML数据操作

insert

update

delete

#删除一些无用的用户信息
delete from 数据库.表 where 条件
mysql> delete from mysql.user where user='root' and host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> delete from mysql.user where user=' ' ;
Query OK, 2 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
+------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)

DCL授权控制

grant添加授权

revoke取消授权

#创建mysql的用户，且设置权限
#给开发创建一个账号，用于连接数据库，以及查看数据库信息
create user 名字@'允许登入的主机网段' identified by '密码';
mysql> create user zhengxinqiang@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#查询用户权限
#默认创建的用户，只有一个USAGE权限，作用是只能登入mysql，没有其他权限
mysql> show grants for zhengxinqiang@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for zhengxinqiang@%                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhengxinqiang'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

给zhengxinqiang用户，添加登入后允许执行DQL权限

#使用root用户给zhengxinqiang这个用户添加权限
#授权语句
#允许zhengxinqiang用户使用grant命令 with grant option
#你不希望zhengxinqiang 用户使用grant,就不需要加
grant 给与的权限  on 数据库.数据表 to 用户@'允许登入的主机'；
grant select on mysql.user to zhengxinqiang@'%';

mysql> grant select on mysql.user to zhengxinqiang@'%';
Query OK, 0 rows affected (0.00 sec)
#刷新数据库表的权限让授权语句立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

以下是通过zhengxinqiang的账号登入之后看到的信息

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

MySQL [mysql]> select user,host,password from mysql.user;
+---------------+-----------------------+-------------------------------------------+
| user          | host                  | password                                  |
+---------------+-----------------------+-------------------------------------------+
| root          | localhost             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root          | localhost.localdomain |                                           |
| root          | 127.0.0.1             |                                           |
| zhengxinqiang | %                     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------------------+-------------------------------------------+
4 rows in set (0.00 sec)

在root用户查看zhengxinqiang用户的权限

多了一个select的权限

mysql> show grants for zhengxinqiang@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for zhengxinqiang@%                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhengxinqiang'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT ON `mysql`.`user` TO 'zhengxinqiang'@'%'                                                        |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看MySQL帮助信息

DDL的帮助

查看DDL语句的具体指令

mysql> ? DATA Definition

mysql> ? DATA Definition
You asked for help about help category: "Data Definition"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER LOGFILE GROUP
   ALTER PROCEDURE
   ALTER SCHEMA
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER VIEW
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SCHEMA
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE VIEW
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP INDEX
   DROP PROCEDURE
   DROP SCHEMA
   DROP SERVER
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP VIEW
   FOREIGN KEY
   RENAME TABLE
   TRUNCATE TABLE
   #查看具体的命令用法
   mysql> ? CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.6/en/create-database.html
#查看use命令
mysql> ? use ;
Name: 'USE'
Description:
Syntax:
USE db_name

The USE statement tells MySQL to use the named database as the default
(current) database for subsequent statements. This statement requires
some privilege for the database or some object within it.

The named database remains the default until the end of the session or
another USE statement is issued:

USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

The database name must be specified on a single line. Newlines in
database names are not supported.

URL: https://dev.mysql.com/doc/refman/5.6/en/use.html

DCL的帮助

查看DCL语句的具体指令

mysql> ? Account Management;

mysql> ? Account Management;
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

DML的帮助

查看DML语句的具体指令

mysql> ? DATA Manipulation;

mysql> ? DATA Manipulation;
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CALL
   DELETE
   DO
   DUAL
   HANDLER
   INSERT
   INSERT DELAYED
   INSERT SELECT
   JOIN
   LOAD DATA
   LOAD XML
   REPLACE
   SELECT
   UNION
   UPDATE

本文作者：迷茫的28岁

本文链接：https://www.cnblogs.com/zheng520/p/16670940.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   迷茫的28岁  阅读(12)  评论(0)  编辑  收藏  举报
相关博文：
·  MySQL语句
·  MySQL实战1
·  SQL语句介绍以及如何使用
·  SQL语言
·  SQL语言
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
点击右上角即可分享
微信分享提示
SQL AI 助手
💬
评论
📌
收藏
💗
关注
👍
推荐
🚀
回顶
➖
收起
🔑
  1. 1 岁月神偷 金玟岐
岁月神偷 - 金玟岐
00:00 / 00:00
An audio error has occurred.

作词 : 金玟岐

作曲 : 金玟岐

编曲 : 薛琳可

能够握紧的就别放了

能够拥抱的就别拉扯

时间着急的冲刷着

剩下了什么

原谅走过的那些曲折

原谅走过的那些曲折

原来留下的都是真的

纵然似梦啊半醒着

笑着哭着都快活 谁让~

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻

能够握紧的就别放了

能够握紧的就别放了

能够拥抱的就别拉扯

时间着急的冲刷着

剩下了什么

原谅走过的那些曲折

原谅走过的那些曲折

原来留下的都是真的

纵然似梦啊半醒着

笑着哭着都快活 谁让~

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻