目录
一、第一章SQL
1.1 SQL分类
- DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。
- DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。
- DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。
1.2 DDL
简单来说，就是对数据库内部的对象进行创建、删除、修改等操作的语言。
1.2.1 连接数据库
(base) xubinbin:~ xubinbin$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.19 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql:客户端命令
- -u: 数据库用户名选项
- root: 数据库用户名
- -p:表求需要输入密码 ###2.2.2 创建数据库
- 语法: create database 数据库名;
1.2.2 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| al2 |
| eas |
| game |
| game1 |
| mydb |
| mydb1 |
| mysql |
| new_schema1 |
| performance_schema |
| rgzn2 |
| test |
| test4 |
| test5 |
| testl |
+--------------------+
15 rows in set (0.00 sec)
- information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- mysql：存储了系统的用户权限信息。
- 其它(test1)：自建数据库，任何用户都可以使用。
1.2.3创建数据库
mysql> create database test_1;
Query OK, 1 row affected (0.41 sec)
1.2.4 选择数据库
mysql> use test_1;
Database changed
1.2.5 查看数据库中有哪些表
mysql> show tables;
Empty set (0.00 sec)
由于没有在数据库test_1中创建表，所以没有数据。切换到系统数据mysql，再查看表信息。
mysql> use mysql;
Database changedmysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.05 sec)
1.2.6 删除数据库
mysql> drop database test_1;
Query OK, 0 rows affected (0.44 sec)
查看数据库是否删除成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| al2 |
| eas |
| game |
| game1 |
| mydb |
| mydb1 |
| mysql |
| new_schema1 |
| performance_schema |
| rgzn2 |
| test |
| test4 |
| test5 |
| testl |
+--------------------+
15 rows in set (0.00 sec)
1.2.7 创建表
语法:
create table tablename( column_name_1 column_type constraints,
column_name_2 column_type constraints,
...
column_name_n column_type constraints ）;
1.创建数据库
mysql> create database test_1;
Query OK, 1 row affected (0.00 sec)
2.选择数据库
mysql> use test_1;
Database changed
3.创建学生表: stu,包含四列属性分别是: 姓名、生日、年龄、学号
mysql> create table stu (
-> name varchar(10),
-> birthday date,
-> age int,
-> stu_no varchar(20)
-> );
Query OK, 0 rows affected (0.50 sec)
1.2.8 查看表的结构
mysql> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| stu_no | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.11 sec)
Field: 列名
Type: 列类型
Null: 是否可以为空,
YES:是
NO:否
Key: 是否是主键
Default: 默认值
Extra:其它信息
1.2.9 查看建表语句
1.2.10 删除表
mysql> drop table stu;
Query OK, 0 rows affected (0.47 sec)
1.2.11 修改表
1.2.11.1 修改表字段类型
语法
alter table tablename modify [column ] column_definition [first|after col_name]
1.创建表
2.修改表字段类型
MariaDB [test1]> alter table stu modify age bigint;Query OK, 0 rows affected (0.029 sec)Records: 0 Duplicates: 0 Warnings: 0
3.查看字段类型是否修改成功
1.2.11.2 增加表字段
语法
alter table tablename add [column] column_definition [first|after col_name]
- 添加表字段
MariaDB [test1]> alter table stu add column sex varchar(5);Query OK, 0 rows affected (0.017 sec)Records: 0 Duplicates: 0 Warnings: 0
2.查看字段是否添加成功
MariaDB [test1]> desc stu;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| name | varchar(10) | YES | | NULL | || birthday | date | YES | | NULL | || age | bigint(20) | YES | | NULL | || stu_no | varchar(20) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+5 rows in set (0.004 sec)
1.2.11.3 删除表字段
语法
alter table tablename drop [column] col_name
1.删除表字段
MariaDB [test1]> alter table stu drop column age;Query OK, 0 rows affected (0.014 sec)Records: 0 Duplicates: 0 Warnings: 00
2.查看列是否删除成功
MariaDB [test1]> desc stu;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| name | varchar(10) | YES | | NULL | || birthday | date | YES | | NULL | || stu_no | varchar(20) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.003 sec)
1.2.11.4 修改列名
语法
alter table tablename change [column] old_col_name column_definition [first|after col_name]
- 修改列名
MariaDB [test1]> alter table stu change name stu_name varchar(30);Query OK, 0 rows affected (0.015 sec)Records: 0 Duplicates: 0 Warnings: 0
2.查看列名是否修改成功
MariaDB [test1]> desc stu;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| stu_name | varchar(30) | YES | | NULL | || birthday | date | YES | | NULL | || stu_no | varchar(20) | YES | | NULL | || sex | varchar(5) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.002 sec)
1.2.11.5 更改表名
语法
alter table tablename rename [to] new_tablename
1.修改表名
MariaDB [test1]> alter table stu rename student;Query OK, 0 rows affected (0.007 sec)
2.查看表名是否修改成功
MariaDB [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| student |+-----------------+
二、第二章
DML操作是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作
2.3.1 插入记录
语法
insert into tablename (field1,field2,…,fieldn) values (value1,value2,…,valuen);
1.插入记录
MariaDB [test1]> insert into student(stu_name,birthday,stu_no,sex) values('zs','2000-02-20','1008060032','man');Query OK, 1 row affected (0.013 sec)
2.查看记录是否插入成功
MariaDB [test1]> select * from student;+----------+------------+------------+------+| stu_name | birthday | stu_no | sex |+----------+------------+------------+------+| zs | 2000-02-20 | 1008060032 | man |+----------+------------+------------+------+1 row in set (0.003 sec)
2.3.2 更新记录
语法
update tablename set field1=value1,field2=value2,…,fieldn=valuen [where condition]
2.3.2.1 只更新一个字段
1.更新记录
MariaDB [test1]> update student set sex='woman' where stu_name='zs';Query OK, 1 row affected (0.013 sec)Rows matched: 1 Changed: 1 Warnings: 0
2.查看记录是否更新成功
MariaDB [test1]> select * from student;+----------+------------+------------+-------+| stu_name | birthday | stu_no | sex |+----------+------------+------------+-------+| zs | 2000-02-20 | 1008060032 | woman |+----------+------------+------------+-------+1 row in set (0.001 sec)
2.3.2.2 更新两个字个以上的字段
1.更新记录
MariaDB [test1]> update student set sex='man',birthday='2005-02-20' where stu_name='zs';Query OK, 1 row affected (0.006 sec)Rows matched: 1 Changed: 1 Warnings: 0
2.查看记录是否更新成功
MariaDB [test1]> select * from student;+----------+------------+------------+------+| stu_name | birthday | stu_no | sex |+----------+------------+------------+------+| zs | 2005-02-20 | 1008060032 | man |+----------+------------+------------+------+1 row in set (0.001 sec)
2.3.2.3 根据多个条件更新某一条记录
1.更新记录
MariaDB [test1]> update student set sex='man',birthday='2005-02-20' where stu_name='zs' and stu_no='1008060032';Query OK, 0 rows affected (0.001 sec)Rows matched: 1 Changed: 0 Warnings: 0
2.3.3 删除记录
语法
delete from tablename [where condition]
- 删除记录
MariaDB [test1]> delete from student where stu_name='zs';Query OK, 1 row affected (0.014 sec)
- 查看记录是否删除成功
MariaDB [test1]> select * from student;Empty set (0.001 sec)
2.3.4 查询记录
语法
select * from tablename [where condition]
1.查询所有记录:
MariaDB [test1]> select * from student;+----------+------------+------------+------+| stu_name | birthday | stu_no | sex |+----------+------------+------------+------+| zs | 2005-02-20 | 1008060032 | man |+----------+------------+------------+------+1 row in set (0.003 sec)
2.查询某些属性
MariaDB [test1]> select stu_name,birthday from student;+----------+------------+| stu_name | birthday |+----------+------------+| zs | 2005-02-20 |+----------+------------+1 row in set (0.001 sec)
2.4 DCL语句
DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。下面通过一个例子简单说明一下。
1.创建一个数据库用户z1，具有对test1数据库中所有表的SELECT/INSERT权限：
MariaDB [test1]> grant select,insert on test1.* to 'z1' @'localhost' identified by '123';Query OK, 0 rows affected (0.069 sec)mysql> exitBye
2.登录z1用户
root@217de24f02e0:/# mysql -u z1 -p
Enter password:Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 29Server version: 10.6.4-MariaDB-1:10.6.4+maria~focal mariadb.org binary distributionCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
3.尝试使用mysql数据库
MariaDB [(none)]> use mysql;ERROR 1044 (42000): Access denied for user 'z1'@'localhost' to database 'mysql'
4.选择test1数据库
MariaDB [(none)]> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed
5.执行insert语句
MariaDB [test1]> insert into student(stu_name,birthday,stu_no,sex) values('ls','2000-02-20','1008060033','man');Query OK, 1 row affected (0.015 sec)
6.执行update语句
MariaDB [test1]> update student set sex='woman' where stu_name='ls';ERROR 1142 (42000): UPDATE command denied to user 'z1'@'localhost' for table 'student'
2.5 帮助文档
查看帮助能提供什么:
mysql> ? contentsYou asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories:Account ManagementAdministrationCompound StatementsData DefinitionData ManipulationData TypesFunctionsFunctions and Modifiers for Use with GROUP BYGeographic FeaturesHelp MetadataLanguage StructurePluginsProceduresStorage EnginesTable MaintenanceTransactionsUser-Defined FunctionsUtility
查看mysql支持哪些数据类型
mysql> ? Data TypesYou asked for help about help category: "Data Types"For more information, type 'help <item>', where <item> is one of the followingtopics:AUTO_INCREMENTBIGINTBINARYBITBLOBBLOB DATA TYPEBOOLEANCHARCHAR BYTEDATEDATETIMEDECDECIMALDOUBLEDOUBLE PRECISIONENUMFLOATINTINTEGERLONGBLOBLONGTEXTMEDIUMBLOBMEDIUMINTMEDIUMTEXTSET DATA TYPESMALLINTTEXTTIMETIMESTAMPTINYBLOBTINYINTTINYTEXTVARBINARYVARCHARYEAR DATA TYPE查看int类型具体介绍
mysql> ? intName: 'INT'Description:INT[(M)] [UNSIGNED] [ZEROFILL]A normal-size integer. The signed range is -2147483648 to 2147483647.The unsigned range is 0 to 4294967295.URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html查看show命令能看些什么:
mysql> ? showName: 'SHOW'Description:SHOW has many forms that provide information about databases, tables,columns, or status information about the server. This section describesthose following:SHOW {BINARY | MASTER} LOGSSHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]SHOW CHARACTER SET [like_or_where]SHOW COLLATION [like_or_where]SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]SHOW CREATE DATABASE db_nameSHOW CREATE EVENT event_nameSHOW CREATE FUNCTION func_nameSHOW CREATE PROCEDURE proc_nameSHOW CREATE TABLE tbl_nameSHOW CREATE TRIGGER trigger_nameSHOW CREATE VIEW view_nameSHOW DATABASES [like_or_where]SHOW ENGINE engine_name {STATUS | MUTEX}SHOW [STORAGE] ENGINESSHOW ERRORS [LIMIT [offset,] row_count]SHOW EVENTSSHOW FUNCTION CODE func_nameSHOW FUNCTION STATUS [like_or_where]SHOW GRANTS FOR userSHOW INDEX FROM tbl_name [FROM db_name]SHOW MASTER STATUSSHOW OPEN TABLES [FROM db_name] [like_or_where]SHOW PLUGINSSHOW PROCEDURE CODE proc_nameSHOW PROCEDURE STATUS [like_or_where]SHOW PRIVILEGESSHOW [FULL] PROCESSLISTSHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]SHOW PROFILESSHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]SHOW SLAVE HOSTSSHOW SLAVE STATUS [FOR CHANNEL channel]SHOW [GLOBAL | SESSION] STATUS [like_or_where]SHOW TABLE STATUS [FROM db_name] [like_or_where]SHOW [FULL] TABLES [FROM db_name] [like_or_where]SHOW TRIGGERS [FROM db_name] [like_or_where]SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]SHOW WARNINGS [LIMIT [offset,] row_count]like_or_where:LIKE 'pattern'| WHERE exprIf the syntax for a given SHOW statement includes a LIKE 'pattern'part, 'pattern' is a string that can contain the SQL % and _ wildcardcharacters. The pattern is useful for restricting statement output tomatching values.Several SHOW statements also accept a WHERE clause that provides moreflexibility in specifying which rows to display. Seehttp://dev.mysql.com/doc/refman/5.7/en/extended-show.html.URL: http://dev.mysql.com/doc/refman/5.7/en/show.html查看create table语法
mysql> ? create tableName: 'CREATE TABLE'Description:Syntax:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...)[table_options][partition_options]CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)][table_options][partition_options][IGNORE | REPLACE][AS] query_expressionCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }create_definition:col_name column_definition| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)[index_option] ...| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name] [index_type] (index_col_name,...)[index_option] ...| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)[index_option] ...| [CONSTRAINT [symbol]] FOREIGN KEY[index_name] (index_col_name,...) reference_definition| CHECK (expr)column_definition:data_type [NOT NULL | NULL] [DEFAULT default_value][AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY][COMMENT 'string'][COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}][STORAGE {DISK|MEMORY|DEFAULT}][reference_definition]| data_type [GENERATED ALWAYS] AS (expression)[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment][NOT NULL | NULL] [[PRIMARY] KEY]data_type:BIT[(length)]| TINYINT[(length)] [UNSIGNED] [ZEROFILL]| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]| INT[(length)] [UNSIGNED] [ZEROFILL]| INTEGER[(length)] [UNSIGNED] [ZEROFILL]| BIGINT[(length)] [UNSIGNED] [ZEROFILL]| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]| DATE| TIME[(fsp)]| TIMESTAMP[(fsp)]| DATETIME[(fsp)]| YEAR| CHAR[(length)] [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| VARCHAR(length) [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| BINARY[(length)]| VARBINARY(length)| TINYBLOB| BLOB| MEDIUMBLOB| LONGBLOB| TINYTEXT [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| TEXT [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| MEDIUMTEXT [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| LONGTEXT [BINARY][CHARACTER SET charset_name] [COLLATE collation_name]| ENUM(value1,value2,value3,...)[CHARACTER SET charset_name] [COLLATE collation_name]| SET(value1,value2,value3,...)[CHARACTER SET charset_name] [COLLATE collation_name]| JSON| spatial_typeindex_col_name:col_name [(length)] [ASC | DESC]index_type:USING {BTREE | HASH}index_option:KEY_BLOCK_SIZE [=] value| index_type| WITH PARSER parser_name| COMMENT 'string'reference_definition:REFERENCES tbl_name (index_col_name,...)[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE][ON DELETE reference_option][ON UPDATE reference_option]reference_option:RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULTtable_options:table_option [[,] table_option] ...table_option:ENGINE [=] engine_name| AUTO_INCREMENT [=] value| AVG_ROW_LENGTH [=] value| [DEFAULT] CHARACTER SET [=] charset_name| CHECKSUM [=] {0 | 1}| [DEFAULT] COLLATE [=] collation_name| COMMENT [=] 'string'| COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}| CONNECTION [=] 'connect_string'| DATA DIRECTORY [=] 'absolute path to directory'| DELAY_KEY_WRITE [=] {0 | 1}| ENCRYPTION [=] {'Y' | 'N'}| INDEX DIRECTORY [=] 'absolute path to directory'| INSERT_METHOD [=] { NO | FIRST | LAST }| KEY_BLOCK_SIZE [=] value| MAX_ROWS [=] value| MIN_ROWS [=] value| PACK_KEYS [=] {0 | 1 | DEFAULT}| PASSWORD [=] 'string'| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}| STATS_AUTO_RECALC [=] {DEFAULT|0|1}| STATS_PERSISTENT [=] {DEFAULT|0|1}| STATS_SAMPLE_PAGES [=] value| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]| UNION [=] (tbl_name[,tbl_name]...)partition_options:PARTITION BY{ [LINEAR] HASH(expr)| [LINEAR] KEY [ALGORITHM={1|2}] (column_list)| RANGE{(expr) | COLUMNS(column_list)}| LIST{(expr) | COLUMNS(column_list)} }[PARTITIONS num][SUBPARTITION BY{ [LINEAR] HASH(expr)| [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }[SUBPARTITIONS num]][(partition_definition [, partition_definition] ...)]partition_definition:PARTITION partition_name[VALUES{LESS THAN {(expr | value_list) | MAXVALUE}|IN (value_list)}][[STORAGE] ENGINE [=] engine_name][COMMENT [=] 'comment_text' ][DATA DIRECTORY [=] 'data_dir'][INDEX DIRECTORY [=] 'index_dir'][MAX_ROWS [=] max_number_of_rows][MIN_ROWS [=] min_number_of_rows][TABLESPACE [=] tablespace_name][(subpartition_definition [, subpartition_definition] ...)]subpartition_definition:SUBPARTITION logical_name[[STORAGE] ENGINE [=] engine_name][COMMENT [=] 'comment_text' ][DATA DIRECTORY [=] 'data_dir'][INDEX DIRECTORY [=] 'index_dir'][MAX_ROWS [=] max_number_of_rows][MIN_ROWS [=] min_number_of_rows][TABLESPACE [=] tablespace_name]query_expression:SELECT ... (Some valid select or union statement)CREATE TABLE creates a table with the given name. You must have theCREATE privilege for the table.By default, tables are created in the default database, using theInnoDB storage engine. An error occurs if the table exists, if there isno default database, or if the database does not exist.For information about the physical representation of a table, seehttp://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.URL: http://dev.mysql.com/doc/refman/5.7/en/create-table.html
扫一扫
566
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
