#mysql（二）
###mysql工具的使用
- -u // 指定用户名，默认为root
- -p //指定用户的密码
- h //指定服务器主机，默认为localhost，推荐使用ip地址
- P //如果端口是3306就没事 如果不是那后面就要用大写的P
- V //查看当前使用的mysql版本
- e //不登录mysql执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -p"Lxy123456!"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
[root@localhost ~]# mysql -uroot -pLxy123456! -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using EditLine wrapper
[root@localhost ~]# mysql -uroot -pLxy123456! -h127.0.0.1 -e 'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
###服务器监听的两种socket地址
socket类型
- ip socket
默认监听在tcp的3306端口，支持远程通信 - unix sock
- 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）
- 仅支持本地通信
- server地址只能是：localhost，127.0.0.1
###mysql数据库操作
####DDL操作
####数据库操作
- 创建数据库
mysql> create database lixueyi;
Query OK, 1 row affected (0.00 sec)
- 查看当前实例有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lixueyi |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql>
- 删除数据库
mysql> drop database lixueyi;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
###表操作
- 创建表
- 进入数据库创建表
mysql> use lixueyi;
Database changed
mysql> create table Lxy(id int not null,name varchar(10) not null,age tinyint(3));
Query OK, 0 rows affected (0.00 sec)
- 查看当前数据库有哪些表
mysql> show tables;
+-------------------+
| Tables_in_lixueyi |
+-------------------+
| Lxy |
+-------------------+
1 row in set (0.00 sec)
- 删除表
mysql> drop table Lxy;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
用户操作
mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
-
IP地址，如：172.16.12.129
-
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符
-
数据库用户创建
mysql> create user 'xueyi'@'localhost'identified by 'Lxy123456!';
Query OK, 0 rows affected (0.00 sec)
- 使用新创建的用户和密码登录
[root@localhost ~]# mysql -uxueyi -pLxy123456! -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.38 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
- 删除数据库用户
mysql> drop user 'xueyi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *D2DDC9669C523B361297FCD21F3B8A555DA2931C
password_expired: N
password_last_changed: 2022-07-24 15:26:42
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2022-07-24 15:06:42
password_lifetime: NULL
account_locked: Y
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2022-07-24 15:06:42
password_lifetime: NULL
account_locked: Y
3 rows in set (0.00 sec)
###查看命令SHOW
- 查看支持的所有字符集
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
- 查看当前数据库支持的所有存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
- 查看数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lixueyi |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 不进入某数据库而列出其包含的所有表
mysql> show tables from sys;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_bytes |
| memory_global_total |
| metrics |
| processlist |
| ps_check_lost_instrumentation |
| schema_auto_increment_columns |
| schema_index_statistics |
| schema_object_overview |
| schema_redundant_indexes |
| schema_table_lock_waits |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| session |
| session_ssl_status |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_bytes |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_flattened_keys |
| x$schema_index_statistics |
| x$schema_table_lock_waits |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$session |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+-----------------------------------------------+
101 rows in set (0.00 sec)
- 查看表结构
mysql> desc lixueyi.lxy;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
- 查看某表的创建命令
mysql> show create table lixueyi.lxy;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| lxy | CREATE TABLE `lxy` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`age` tinyint(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 查看某表的状态
mysql> use lixueyi
Database changed
mysql> show table status like 'lxy'\G;
*************************** 1. row ***************************
Name: lxy
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-07-25 23:26:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
###获取帮助
- 获取创建表的帮助
mysql> help create table;
Name: '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_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| CHECK (expr)
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
}
data_type:
(see https://dev.mysql.com/doc/refman/5.7/en/data-types.html)
key_part:
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 (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
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 | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| 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}]
| 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 [=] 'string' ]
[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 [=] 'string' ]
[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 the
CREATE privilege for the table.
By default, tables are created in the default database, using the
InnoDB storage engine. An error occurs if the table exists, if there is
no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system
may have a limit on the number of files that represent tables.
Individual storage engines may impose engine-specific constraints.
InnoDB permits up to 4 billion tables.
For information about the physical representation of a table, see
https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.
URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html
###DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。
###INSERT语句
- DML操作之增操作insert
mysql> insert into lxy(id,name,age) value(1,'pozhan',30);
Query OK, 1 row affected (0.00 sec)
mysql> select * from lxy;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
+----+--------+------+
2 rows in set (0.00 sec)
//单个添加value 不加s
mysql> insert into lxy(id,name,age) values(1,'pozhan',30),(2,'niurou',25);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from lxy;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.00 sec)
//多个添加 用values
###SELECT语句
表示符
- *： 所有字段
mysql> select * from lxy;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.00 sec)
- as ：字段别名，如col1 AS alias1当表名很长时用别名代替
mysql> select name as 姓名 from lxy;
+--------+
| 姓名 |
+--------+
| pozhan |
| pozhan |
| pozhan |
| niurou |
+--------+
4 rows in set (0.00 sec)
mysql> select name as 姓名,age as 年龄 from lxy;
+--------+--------+
| 姓名 | 年龄 |
+--------+--------+
| pozhan | 30 |
| pozhan | 30 |
| pozhan | 30 |
| niurou | 25 |
+--------+--------+
4 rows in set (0.01 sec)
//设置别名“姓名”“年龄”
操作符
- =
mysql> select * from lxy where name = 'niurou';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | niurou | 25 |
+----+--------+------+
1 row in set (0.00 sec)
- / “> =”
mysql> select * from lxy where name >= '25';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.00 sec)
- <=
mysql> select * from lxy where age <= 25;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | niurou | 25 |
+----+--------+------+
1 row in set (0.00 sec)
- <
mysql> select * from lxy where age < 29;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | niurou | 25 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from lxy where age > 29;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
+----+--------+------+
3 rows in set (0.00 sec)
- != 不等于
mysql> select * from lxy where age != 29;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.00 sec)
BETWEEN //区间匹配
mysql> select * from lxy where age between 20 and 30;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.00 sec)
- %变量%
mysql> select * from lxy where name like '%po%';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
+----+--------+------+
3 rows in set (0.00 sec)
%变量 //以变量结尾的显示出来
mysql> select * from lxy where name like '%po';
Empty set (0.00 sec)
- 变量% //以变量开头的显示出来
mysql> select * from lxy where name like 'po%';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
+----+--------+------+
3 rows in set (0.00 sec)
IS NOT NULL：非空
mysql> select * from lxy where name is not null;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 1 | pozhan | 30 |
| 2 | niurou | 25 |
+----+--------+------+
4 rows in set (0.01 sec)
IS NULL :空
mysql> select * from lxy where name is null;
Empty set (0.00 sec)
- 添加数据
mysql> insert lxy(id,name,age,salary) values(3,'hjd',21,5000),(4,'lx',22,6000),(5,'hjd',23,7000),(6,'lxy',21,9999);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 2 | niurou | 25 | NULL |
| 3 | hjd | 21 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 23 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
###DML操作之查操作select
- 排序
mysql> select name from lxy;
+--------+
| name |
+--------+
| pozhan |
| pozhan |
| pozhan |
| niurou |
| hjd |
| lx |
| hjd |
| lxy |
+--------+
8 rows in set (0.00 sec)
mysql> select * from lxy order by age;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 3 | hjd | 21 | 5000 |
| 6 | lxy | 21 | 9999 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 23 | 7000 |
| 2 | niurou | 25 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
+----+--------+------+--------+
8 rows in set (0.00 sec)
mysql> select * from lxy where age is null;
Empty set (0.00 sec)
###update语句(更新语句)
DML操作之改操作update
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 2 | niurou | 25 | NULL |
| 3 | hjd | 21 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 23 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
8 rows in set (0.01 sec)
mysql> update lxy set age = 30 where name = 'hjd';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 2 | niurou | 25 | NULL |
| 3 | hjd | 30 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 30 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
###delete语句
DML操作之删操作delete
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 2 | niurou | 25 | NULL |
| 3 | hjd | 30 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 30 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
mysql> delete from lxy where id = 1;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 2 | niurou | 25 | NULL |
| 3 | hjd | 30 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 30 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 1 | pozhan | 30 | NULL |
| 2 | niurou | 25 | NULL |
| 3 | hjd | 30 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 30 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> delete from lxy where id = 1;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from lxy;
+----+--------+------+--------+
| id | name | age | salary |
+----+--------+------+--------+
| 2 | niurou | 25 | NULL |
| 3 | hjd | 30 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 30 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> desc lxy;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
###truncate语句
truncate与delete的区别：
- delete
- DELETE删除表内容时仅删除内容，但会保留表结构
- DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
- 可以通过回滚事务日志恢复数据
- 非常占用空间
- truncate
- 删除表中所有数据，且无法恢复
- 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
- 执行速度比DELETE快，且使用的系统和事务日志资源少
- 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
- 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
- 不能用于加入了索引视图的表
mysql> select * from lxy;
+----+------+------+--------+
| id | name | age | salary |
+----+------+------+--------+
| 3 | hjd | 21 | 5000 |
| 4 | lx | 22 | 6000 |
| 5 | hjd | 23 | 7000 |
| 6 | lxy | 21 | 9999 |
+----+------+------+--------+
4 rows in set (0.00 sec)
mysql> truncate lxy;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from lxy;
Empty set (0.00 sec)
mysql> desc lxy;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
###DCL操作
####创建授权grant
- 权限类型：
- ALL //代表所有权限
- SELECT //读取内容的权限
- INSERT //插入内容的权限
- UPDATE //更新内容的权限
- DELETE //删除内容的权限
指定要操作的对象db_name.table_name
- 表示方式
- *.* //所有库的所有表
- db_name //指定库的所有表
- db_name.table_name //指定库的指定表
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。
- 授权wangqing用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'Lxy123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL ON *.* TO 'root'@'192.168.66.128' IDENTIFIED BY 'Lxy123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//刷新一下
- 授权root用户在172.16.12.129上远程登录访问root数据库
- 授权root用户在所有位置上远程登录访问root数据库
mysql> select * from niuroujiang;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | youqian | 20 |
+----+---------+------+
1 row in set (0.01 sec)
###查看授权
查看当前登录用户的授权信息
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看指定用户wangqing的授权信息
mysql> show grants for 'root'@'192.168.66.128';
+--------------------------------------------------------+
| Grants for root@192.168.66.128 |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.66.128' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
###取消授权REVOKE
mysql> revoke all on *.* from 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
c)
496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
