mysql基础2

3.2 服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信 server地址只能是：localhost，127.0.0.1

连接数据库的方式：

1 [root@localhost ~]# mysql -uroot -pRunTime123!
2 [root@localhost ~]# mysql -uroot -pRunTime123! -hlocalhost
3 [root@localhost ~]# mysql -uroot -pRunTime123! -h127.0.0.1
4 [root@localhost ~]# mysql -uroot -pRunTime123! -S /var/lib/mysql/mysql.sock

4. mysql数据库操作

4.1 DDL操作（数据定义语言，用来创建数据库，创建表，创建视图，用户等）

4.1.1 数据库操作

//创建数据库
//语法：CREATE DATABASE（数据库） [IF NOT EXISTS] 'DB_NAME'; 中括号起来的表示可以有也可没有，没有也可以创建出来

 1 mysql> CREATE DATABASE school;     固定用法的大写，可以替换的小写
 2 Query OK, 1 row affected (0.00 sec)    创建一个数据库不加s
 3 
 4 mysql> SHOW DATABASES;     查看数据库时时多个加s
 5 +--------------------+
 6 | Database           |
 7 +--------------------+
 8 | information_schema |
 9 | mysql              |
10 | performance_schema |
11 | school             |
12 | sys                |
13 +--------------------+
14 5 rows in set (0.00 sec)

 1 mysql> create database school;    如果有了在创建就会报错
 2 ERROR 1007 (HY000): Can't create database 'school'; database exists
 3 mysql> CREATE DATABASE IF NOT EXISTS school;
 4 Query OK, 1 row affected, 1 warning (0.00 sec)
 5 
 6 mysql> CREATE DATABASE IF NOT EXISTS school;
 7 Query OK, 1 row affected, 1 warning (0.00 sec)
 8 
 9 mysql> CREATE DATABASE IF NOT EXISTS school;
10 Query OK, 1 row affected, 1 warning (0.00 sec)
11 可以用大写加入IF NOT EXISTS来进行检查，IF NOT EXISTS如果不存在就创建，存在所以就不创建

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';

 1 mysql> drop database school;    删除
 2 Query OK, 0 rows affected (0.05 sec)
 3 mysql> show databases;   
 4 +--------------------+
 5 | Database           |
 6 +--------------------+
 7 | information_schema |
 8 | mysql              |
 9 | performance_schema |
10 | sys                |
11 +--------------------+
12 4 rows in set (0.00 sec)
13 
14 mysql> drop database school;   再删，报错不能删除不存在的
15 ERROR 1008 (HY000): Can't drop database 'school'; database doesn't exist
16 mysql> drop database if exists school; 也可以加入if exists(如果存在）就删除
17 Query OK, 0 rows affected, 1 warning (0.00 sec)  更加严谨

4.1.2 表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';

 1 mysql> use school;  进入school库
 2 Database changed
 3 mysql> show tables; 查看表 显示没有
 4 Empty set (0.00 sec)
 5 
 6 mysql> CREATE TABLE info(name varchar(50) not null,age tinyint,sex varchar(6) not null,salary float);
 7 Query OK, 0 rows affected (0.14 sec)
 8 创建一个叫info的表 名字 50个字符范围 不能空，年龄 可以空tinyint最大范围127岁 性别6给字符范围，不能空，收入，可以空
 9 mysql> DESC info;   DESC 查看表的结构
10 +--------+-------------+------+-----+---------+-------+
11 | Field  | Type        | Null | Key | Default | Extra |
12 +--------+-------------+------+-----+---------+-------+
13 | name   | varchar(50) | NO   |     | NULL    |       |
14 | age    | tinyint(4)  | YES  |     | NULL    |       |
15 | sex    | varchar(6)  | NO   |     | NULL    |       |
16 | salary | float       | YES  |     | NULL    |       |
17 +--------+-------------+------+-----+---------+-------+
18 4 rows in set (0.01 sec)
19 mysql> DESC info; 如果退出来school库DESC时查看不了的
20 ERROR 1046 (3D000): No database selected
21 mysql> DESC school.info;从外面看就要加上库的名字和然后点
22 +--------+-------------+------+-----+---------+-------+
23 | Field  | Type        | Null | Key | Default | Extra |
24 +--------+-------------+------+-----+---------+-------+
25 | name   | varchar(50) | NO   |     | NULL    |       |
26 | age    | tinyint(4)  | YES  |     | NULL    |       |
27 | sex    | varchar(6)  | NO   |     | NULL    |       |
28 | salary | float       | YES  |     | NULL    |       |
29 +--------+-------------+------+-----+---------+-------+
30 4 rows in set (0.00 sec)

//查看当前数据库有哪些表

 1 mysql> show tables from school;  在库外面就用from看
 2 +------------------+
 3 | Tables_in_school |
 4 +------------------+
 5 | info             |
 6 +------------------+
 7 1 row in set (0.00 sec)
 8  
 9 或者
10 mysql> use school;   进到school库
11 Reading table information for completion of table and column names
12 You can turn off this feature to get a quicker startup with -A
13 
14 Database changed
15 mysql> show tables;  再来查看有那些表
16 +------------------+
17 | Tables_in_school |
18 +------------------+
19 | info             |
20 +------------------+
21 1 row in set (0.01 sec)

//删除表
//语法：DROP TABLE [ IF EXISTS ] 'table_name';

 1 mysql> drop table info;   删除info
 2 Query OK, 0 rows affected (0.09 sec)
 3 
 4 mysql> show tables;查看显示没有了
 5 Empty set (0.00 sec)
 6 mysql> drop table info;   在删就会报错
 7 ERROR 1051 (42S02): Unknown table 'school.info'
 8 mysql> drop table if exists info;
 9 Query OK, 0 rows affected, 1 warning (0.00 sec) 加上if exists判断就不会报错，有就删除
10 
11 mysql> drop table if exists info;
12 Query OK, 0 rows affected, 1 warning (0.00 sec)

4.1.3 用户操作

mysql用户帐号由两部分组成，如'USERNAME'（用户名）@'HOST'（主机名），表示此USERNAME只能从此HOST上远程登录

这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：172.16.12.129
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符

4.1.4 查看命令SHOW

`mysql> SHOW CHARACTER SET; //查看支持的所有字符集`

 1 字符集用来定义输入的内容的   支持41种
 2 mysql> show character set;
 3 +----------+---------------------------------+---------------------+--------+
 4 | Charset  | Description                     | Default collation   | Maxlen |
 5 +----------+---------------------------------+---------------------+--------+
 6 | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
 7 | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
 8 | cp850    | DOS West European               | cp850_general_ci    |      1 |
 9 | hp8      | HP West European                | hp8_english_ci      |      1 |
10 | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
11 | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
12 | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
13 | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
14 | ascii    | US ASCII                        | ascii_general_ci    |      1 |
15 | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
16 | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
17 | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
18 | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
19 | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
20 | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
21 | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
22 | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
23 | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
24 | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
25 | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
26 | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
27 | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
28 | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
29 | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
30 | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
31 | macce    | Mac Central European            | macce_general_ci    |      1 |
32 | macroman | Mac West European               | macroman_general_ci |      1 |
33 | cp852    | DOS Central European            | cp852_general_ci    |      1 |
34 | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
35 | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
36 | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
37 | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
38 | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
39 | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
40 | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
41 | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
42 | binary   | Binary pseudo charset           | binary              |      1 |
43 | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
44 | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
45 | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
46 | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
47 +----------+---------------------------------+---------------------+--------+
48 41 rows in set (0.00 sec)

mysql> show engines; 查找当前数据库锁支持的所有存储引擎

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)

查看某表的创建命令

语法：SHOW CREATE TABLE table_name;

1 mysql> use mysql;   进到mysql表
2 Reading table information for completion of table and column names
3 You can turn off this feature to get a quicker startup with -A
4 
5 Database changed  
6 mysql> show create table user;   查看user表是怎么创建出来的

查看某数据库的创建命令

1 mysql> show create database mysql;  看某一个database不用加s
2 +----------+------------------------------------------------------------------+
3 | Database | Create Database                                                  |
4 +----------+------------------------------------------------------------------+
5 | mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
6 +----------+------------------------------------------------------------------+
7 1 row in set (0.01 sec)

查看某表的状态

语法：SHOW TABLE STATUS LIKE 'table_name'\G

  1 mysql> use school;    创建3个表
  2 Database changed
  3 mysql> create table student(id int not null,name varchar(30),age tinyint);
  4 Query OK, 0 rows affected (0.11 sec)
  5 
  6 mysql> create table student1(id int not null,name varchar(30),salary float);
  7 Query OK, 0 rows affected (0.00 sec)
  8 
  9 mysql> create table student2(id int not null,name varchar(30),sex varchar(6));
 10 Query OK, 0 rows affected (0.02 sec)
 11 mysql> show tables;
 12 +------------------+
 13 | Tables_in_school |
 14 +------------------+
 15 | student          |                3个表
 16 | student1         |
 17 | student2         |
 18 +------------------+
 19 3 rows in set (0.00 sec)
 20 mysql> show table status like 'student';    
 21 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
 22 | Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
 23 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
 24 | student | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2022-04-20 01:03:52 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
 25 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
 26 1 row in set (0.00 sec)
 27 mysql> show table status like 'student'\G   把；换成\G换了一种显示方式
 28 *************************** 1. row ***************************
 29            Name: student
 30          Engine: InnoDB
 31         Version: 10
 32      Row_format: Dynamic
 33            Rows: 0
 34  Avg_row_length: 0
 35     Data_length: 16384
 36 Max_data_length: 0
 37    Index_length: 0
 38       Data_free: 0
 39  Auto_increment: NULL
 40     Create_time: 2022-04-20 01:03:52
 41     Update_time: NULL
 42      Check_time: NULL
 43       Collation: latin1_swedish_ci
 44        Checksum: NULL
 45  Create_options: 
 46         Comment: 
 47 1 row in set (0.00 sec)
 48 mysql> show table status like 'student%'\G 加%号显示所有studet的表
 49 *************************** 1. row ***************************
 50            Name: student
 51          Engine: InnoDB
 52         Version: 10
 53      Row_format: Dynamic
 54            Rows: 0
 55  Avg_row_length: 0
 56     Data_length: 16384
 57 Max_data_length: 0
 58    Index_length: 0
 59       Data_free: 0
 60  Auto_increment: NULL
 61     Create_time: 2022-04-20 01:03:52
 62     Update_time: NULL
 63      Check_time: NULL
 64       Collation: latin1_swedish_ci
 65        Checksum: NULL
 66  Create_options: 
 67         Comment: 
 68 *************************** 2. row ***************************
 69            Name: student1
 70          Engine: InnoDB
 71         Version: 10
 72      Row_format: Dynamic
 73            Rows: 0
 74  Avg_row_length: 0
 75     Data_length: 16384
 76 Max_data_length: 0
 77    Index_length: 0
 78       Data_free: 0
 79  Auto_increment: NULL
 80     Create_time: 2022-04-20 01:04:04
 81     Update_time: NULL
 82      Check_time: NULL
 83       Collation: latin1_swedish_ci
 84        Checksum: NULL
 85  Create_options: 
 86         Comment: 
 87 *************************** 3. row ***************************
 88            Name: student2
 89          Engine: InnoDB
 90         Version: 10
 91      Row_format: Dynamic
 92            Rows: 0
 93  Avg_row_length: 0
 94     Data_length: 16384
 95 Max_data_length: 0
 96    Index_length: 0
 97       Data_free: 0
 98  Auto_increment: NULL
 99     Create_time: 2022-04-20 01:04:12
100     Update_time: NULL
101      Check_time: NULL
102       Collation: latin1_swedish_ci
103        Checksum: NULL
104  Create_options: 
105         Comment: 
106 3 rows in set (0.00 sec)

4.1.5 获取帮助

获取命令使用帮助

语法：HELP keyword;

  1 mysql> help create database;    创建数据库的帮助手册
  2 Name: 'CREATE DATABASE'
  3 Description:
  4 Syntax:
  5 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
  6     [create_option] ...
  7 
  8 create_option: [DEFAULT] {
  9     CHARACTER SET [=] charset_name
 10   | COLLATE [=] collation_name
 11 }
 12 
 13 CREATE DATABASE creates a database with the given name. To use this
 14 statement, you need the CREATE privilege for the database. CREATE
 15 SCHEMA is a synonym for CREATE DATABASE.
 16 
 17 URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html
 18 mysql> heip create table;
 19 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'heip create table' at line 1
 20 mysql> help create table;   创建表的帮助手册
 21 Name: 'CREATE TABLE'
 22 Description:
 23 Syntax:
 24 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 25     (create_definition,...)
 26     [table_options]
 27     [partition_options]
 28 
 29 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 30     [(create_definition,...)]
 31     [table_options]
 32     [partition_options]
 33     [IGNORE | REPLACE]
 34     [AS] query_expression
 35 
 36 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 37     { LIKE old_tbl_name | (LIKE old_tbl_name) }
 38 
 39 create_definition: {
 40     col_name column_definition
 41   | {INDEX | KEY} [index_name] [index_type] (key_part,...)
 42       [index_option] ...
 43   | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
 44       [index_option] ...
 45   | [CONSTRAINT [symbol]] PRIMARY KEY
 46       [index_type] (key_part,...)
 47       [index_option] ...
 48   | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
 49       [index_name] [index_type] (key_part,...)
 50       [index_option] ...
 51   | [CONSTRAINT [symbol]] FOREIGN KEY
 52       [index_name] (col_name,...)
 53       reference_definition
 54   | CHECK (expr)
 55 }
 56 
 57 column_definition: {
 58     data_type [NOT NULL | NULL] [DEFAULT default_value]
 59       [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
 60       [COMMENT 'string']
 61       [COLLATE collation_name]
 62       [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
 63       [STORAGE {DISK | MEMORY}]
 64       [reference_definition]
 65   | data_type
 66       [COLLATE collation_name]
 67       [GENERATED ALWAYS] AS (expr)
 68       [VIRTUAL | STORED] [NOT NULL | NULL]
 69       [UNIQUE [KEY]] [[PRIMARY] KEY]
 70       [COMMENT 'string']
 71       [reference_definition]
 72 }
 73 
 74 data_type:
 75     (see https://dev.mysql.com/doc/refman/5.7/en/data-types.html)
 76 
 77 key_part:
 78     col_name [(length)] [ASC | DESC]
 79 
 80 index_type:
 81     USING {BTREE | HASH}
 82 
 83 index_option: {
 84     KEY_BLOCK_SIZE [=] value
 85   | index_type
 86   | WITH PARSER parser_name
 87   | COMMENT 'string'
 88 }
 89 
 90 reference_definition:
 91     REFERENCES tbl_name (key_part,...)
 92       [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
 93       [ON DELETE reference_option]
 94       [ON UPDATE reference_option]
 95 
 96 reference_option:
 97     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
 98 
 99 table_options:
100     table_option [[,] table_option] ...
101 
102 table_option: {
103     AUTO_INCREMENT [=] value
104   | AVG_ROW_LENGTH [=] value
105   | [DEFAULT] CHARACTER SET [=] charset_name
106   | CHECKSUM [=] {0 | 1}
107   | [DEFAULT] COLLATE [=] collation_name
108   | COMMENT [=] 'string'
109   | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
110   | CONNECTION [=] 'connect_string'
111   | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
112   | DELAY_KEY_WRITE [=] {0 | 1}
113   | ENCRYPTION [=] {'Y' | 'N'}
114   | ENGINE [=] engine_name
115   | INSERT_METHOD [=] { NO | FIRST | LAST }
116   | KEY_BLOCK_SIZE [=] value
117   | MAX_ROWS [=] value
118   | MIN_ROWS [=] value
119   | PACK_KEYS [=] {0 | 1 | DEFAULT}
120   | PASSWORD [=] 'string'
121   | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
122   | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
123   | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
124   | STATS_SAMPLE_PAGES [=] value
125   | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
126   | UNION [=] (tbl_name[,tbl_name]...)
127 }
128 
129 partition_options:
130     PARTITION BY
131         { [LINEAR] HASH(expr)
132         | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
133         | RANGE{(expr) | COLUMNS(column_list)}
134         | LIST{(expr) | COLUMNS(column_list)} }
135     [PARTITIONS num]
136     [SUBPARTITION BY
137         { [LINEAR] HASH(expr)
138         | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
139       [SUBPARTITIONS num]
140     ]
141     [(partition_definition [, partition_definition] ...)]
142 
143 partition_definition:
144     PARTITION partition_name
145         [VALUES
146             {LESS THAN {(expr | value_list) | MAXVALUE}
147             |
148             IN (value_list)}]
149         [[STORAGE] ENGINE [=] engine_name]
150         [COMMENT [=] 'string' ]
151         [DATA DIRECTORY [=] 'data_dir']
152         [INDEX DIRECTORY [=] 'index_dir']
153         [MAX_ROWS [=] max_number_of_rows]
154         [MIN_ROWS [=] min_number_of_rows]
155         [TABLESPACE [=] tablespace_name]
156         [(subpartition_definition [, subpartition_definition] ...)]
157 
158 subpartition_definition:
159     SUBPARTITION logical_name
160         [[STORAGE] ENGINE [=] engine_name]
161         [COMMENT [=] 'string' ]
162         [DATA DIRECTORY [=] 'data_dir']
163         [INDEX DIRECTORY [=] 'index_dir']
164         [MAX_ROWS [=] max_number_of_rows]
165         [MIN_ROWS [=] min_number_of_rows]
166         [TABLESPACE [=] tablespace_name]
167 
168 query_expression:
169     SELECT ...   (Some valid select or union statement)
170 
171 CREATE TABLE creates a table with the given name. You must have the
172 CREATE privilege for the table.
173 
174 By default, tables are created in the default database, using the
175 InnoDB storage engine. An error occurs if the table exists, if there is
176 no default database, or if the database does not exist.
177 
178 MySQL has no limit on the number of tables. The underlying file system
179 may have a limit on the number of files that represent tables.
180 Individual storage engines may impose engine-specific constraints.
181 InnoDB permits up to 4 billion tables.
182 
183 For information about the physical representation of a table, see
184 https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.
185 
186 URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

 1 mysql> desc student;      增加  INSERT
 2 +-------+-------------+------+-----+---------+-------+
 3 | Field | Type        | Null | Key | Default | Extra |
 4 +-------+-------------+------+-----+---------+-------+
 5 | id    | int(11)     | NO   |     | NULL    |       |
 6 | name  | varchar(30) | YES  |     | NULL    |       |
 7 | age   | tinyint(4)  | YES  |     | NULL    |       |
 8 +-------+-------------+------+-----+---------+-------+
 9 3 rows in set (0.00 sec)
10 mysql> select * from student;    查看所有的字段
11 Empty set (0.00 sec)
12 mysql> insert student value(1,'tom',15);   数字不加引号，字母要加
13 Query OK, 1 row affected (0.05 sec)
14 mysql> select * from student;
15 +----+------+------+
16 | id | name | age  |
17 +----+------+------+
18 |  1 | tom  |   15 |
19 +----+------+------+
20 1 row in set (0.00 sec)  查看
21 mysql> insert student values(2,'jerry',16),(3,'zhangshan',20),(4,'lisi',18),(5,'wangwu',19),(6,'zhaoliu',22);
22 Query OK, 5 rows affected (0.00 sec)
23 Records: 5  Duplicates: 0  Warnings: 0
24 mysql> select * from student;
25 +----+-----------+------+
26 | id | name      | age  |
27 +----+-----------+------+
28 |  1 | tom       |   15 |
29 |  2 | jerry     |   16 |
30 |  3 | zhangshan |   20 |
31 |  4 | lisi      |   18 |
32 |  5 | wangwu    |   19 |
33 |  6 | zhaoliu   |   22 |
34 +----+-----------+------+
35 6 rows in set (0.00 sec)

 1 mysql> create table info(id int not null primary key,name varchar(30),age tinyint);
 2 Query OK, 0 rows affected (0.03 sec)   
 3 mysql> desc info;
 4 +-------+-------------+------+-----+---------+-------+
 5 | Field | Type        | Null | Key | Default | Extra |
 6 +-------+-------------+------+-----+---------+-------+
 7 | id    | int(11)     | NO   | PRI | NULL    |       |
 8 | name  | varchar(30) | YES  |     | NULL    |       |
 9 | age   | tinyint(4)  | YES  |     | NULL    |       |
10 +-------+-------------+------+-----+---------+-------+
11 3 rows in set (0.00 sec)
12 mysql> insert info values(1,'tom',15),(2,'jerry',16);
13 Query OK, 2 rows affected (0.00 sec)
14 Records: 2  Duplicates: 0  Warnings: 0
15 mysql> select * from info;
16 +----+-------+------+
17 | id | name  | age  |
18 +----+-------+------+
19 |  1 | tom   |   15 |
20 |  2 | jerry |   16 |
21 +----+-------+------+
22 2 rows in set (0.00 sec)
23 mysql> insert info values(1,'tom',15);
24 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
25     id作为主件1出现过了不能重复

 1 mysql> create table info1(id int not null primary key  auto_increment,name varchar(30),age tinyint);   
 2 Query OK, 0 rows affected (0.05 sec)  新建一个表，id自动增长
 3 
 4 mysql> desc info1;  
 5 +-------+-------------+------+-----+---------+----------------+
 6 | Field | Type        | Null | Key | Default | Extra          |
 7 +-------+-------------+------+-----+---------+----------------+
 8 | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
 9 | name  | varchar(30) | YES  |     | NULL    |                |
 10| age   | tinyint(4)  | YES  |     | NULL    |                |
   +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

12 ysql> insert info1(name,age) values('tom',15),('jerry',16);
13 Query OK, 2 rows affected (0.00 sec) 插入两条内容 不用输序号
14 Records: 2  Duplicates: 0  Warnings: 0
15 mysql> select * from info1;
16 +----+-------+------+
17 | id | name  | age  |
18 +----+-------+------+  id自动增长，
19 |  1 | tom   |   15 |
20 |  2 | jerry |   16 |
21 +----+-------+------+
22 2 rows in set (0.00 sec)

4.2.3 update语句

DML操作之改操作update
语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

更新表表的名字设置某一个字段 = 新的值第2个字段=新的值可以同时改多个在哪一行

 1 mysql> select * from info1;      更改更新update
 2 +----+-------+------+
 3 | id | name  | age  |
 4 +----+-------+------+
 5 |  1 | tom   |   15 |
 6 |  2 | jerry |   16 |
 7 +----+-------+------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> update info1 set age =18 where id = 2;
11 Query OK, 1 row affected (0.00 sec) 更改info1这个表里id为2的年纪改成18
12 Rows matched: 1  Changed: 1  Warnings: 0
13 
14 mysql> select * from info1;
15 +----+-------+------+
16 | id | name  | age  |
17 +----+-------+------+
18 |  1 | tom   |   15 |
19 |  2 | jerry |   18 |
20 +----+-------+------+
21 2 rows in set (0.00 sec)

4.2.2 SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1 当表名很长时用别名代替

 1 mysql> select name,age from foin1;    只查看名字和年龄 2 ERROR 1146 (42S02): Table 'school.foin1' doesn't exist
 3 mysql> select name,age from info1;
 4 +-------+------+
 5 | name  | age  |
 6 +-------+------+
 7 | tom   |   15 |
 8 | jerry |   18 |
 9 +-------+------+
10 2 rows in set (0.00 sec)

1 mysql> select * from info1;    *就是查看所有字段
2 +----+-------+------+
3 | id | name  | age  |
4 +----+-------+------+
5 |  1 | tom   |   15 |
6 |  2 | jerry |   18 |
7 +----+-------+------+
8 2 rows in set (0.00 sec)

1 mysql> select name as '姓名',age as '性别' from info1;  可以把表里面的英文改成中文，表里的数据不变，是一张虚拟表只是把效果显示出来
2 +--------+--------+
3 | 姓名   | 性别   |
4 +--------+--------+
5 | tom    |     15 |
6 | jerry  |     18 |
7 +--------+--------+
8 2 rows in set (0.00 sec)

1 mysql> select name as 'n',age as 'a' from info1;   假如说name和age字符太多，你可以替换成短用别名表示的如图所示
2 +-------+------+
3 | n     | a    |
4 +-------+------+
5 | tom   |   15 |
6 | jerry |   18 |
7 +-------+------+
8 2 rows in set (0.01 sec)

条件判断语句WHERE

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作	AND OR NOT

 1 mysql> select * from info1;        
 2 +----+----------+------+
 3 | id | name     | age  |
 4 +----+----------+------+
 5 |  1 | tom      |   15 |
 6 |  2 | jerry    |   18 |
 7 |  8 | zhangsan |   16 |
 8 |  9 | wangwu   |   19 |
 9 | 10 | qianliu  |   20 |
10 | 11 | zhangsan |   20 |
11 | 12 | lisi     | NULL |
12 +----+----------+------+
13 7 rows in set (0.00 sec)
14 mysql> select * from info1 where age >19;   筛选出年龄大于19的
15 +----+----------+------+
16 | id | name     | age  |
17 +----+----------+------+
18 | 10 | qianliu  |   20 |
19 | 11 | zhangsan |   20 |
20 +----+----------+------+
21 2 rows in set (0.00 sec)
22 mysql> select * from info1 where age >=19; 筛选出年龄大于等于19
23 +----+----------+------+
24 | id | name     | age  |
25 +----+----------+------+
26 |  9 | wangwu   |   19 |
27 | 10 | qianliu  |   20 |
28 | 11 | zhangsan |   20 |
29 +----+----------+------+
30 3 rows in set (0.00 sec)
31 mysql> select * from info1 where age between 18 and 20;
32 +----+----------+------+    筛选出18到20岁之间
33 | id | name     | age  |          between两者之间
34 +----+----------+------+
35 |  2 | jerry    |   18 |
36 |  9 | wangwu   |   19 |
37 | 10 | qianliu  |   20 |
38 | 11 | zhangsan |   20 |
39 +----+----------+------+
40 4 rows in set (0.00 sec)
41 mysql> select * from info1 where age is null;
42 +----+------+------+    可以筛选年纪为空的
43 | id | name | age  |        is not null就是筛选所有年纪不为空的
44 +----+------+------+
45 | 12 | lisi | NULL |
46 +----+------+------+
47 1 row in set (0.00 sec)

 1  1 mysql> select * from info1;
 2  2 +----+----------+------+
 3  3 | id | name     | age  |
 4  4 +----+----------+------+
 5  5 |  1 | tom      |   15 |
 6  6 |  2 | jerry    |   18 |
 7  7 |  8 | zhangsan |   16 |
 8  8 |  9 | wangwu   |   19 |
 9  9 | 10 | qianliu  |   20 |
10 10 | 11 | zhangsan |   20 |
11 11 | 12 | lisi     | NULL |
12 12 +----+----------+------+
13 13 7 rows in set (0.00 sec)
14 14 
15 15 mysql> select * from info1 where name = 'zhangsan' and age =20;
16 16 +----+----------+------+  取出名字叫zhangsan年纪为20岁的
17 17 | id | name     | age  |
18 18 +----+----------+------+
19 19 | 11 | zhangsan |   20 |
20 20 +----+----------+------+
21 21 1 row in set (0.00 sec)
22 22 mysql> select * from info1 where name = 'zhangsan' or age =20;
23 23 +----+----------+------+  删选出叫zhangsan的或者20岁的
24 24 | id | name     | age  |
25 25 +----+----------+------+
26 26 |  8 | zhangsan |   16 |
27 27 | 10 | qianliu  |   20 |
28 28 | 11 | zhangsan |   20 |
29 29 +----+----------+------+
30 30 3 rows in set (0.01 sec)
31 31 mysql> select * from info1 where not  age =20;
32 32 +----+----------+------+筛选出年纪没有20岁的
33 33 | id | name     | age  |
34 34 +----+----------+------+
35 35 |  1 | tom      |   15 |
36 36 |  2 | jerry    |   18 |
37 37 |  8 | zhangsan |   16 |
38 38 |  9 | wangwu   |   19 |
39 39 +----+----------+------+
40 40 4 rows in set (0.00 sec)

ORDER BY：排序，默认为升序（ASC）

ORDER BY语句	意义
ORDER BY ‘column_name'	根据column_name进行升序排序
ORDER BY 'column_name' DESC	根据column_name进行降序排序
ORDER BY ’column_name' LIMIT 2	根据column_name进行升序排序并只取前2个结果
ORDER BY ‘column_name' LIMIT 1,2	根据column_name进行升序排序并且略过第1个结果取后面的2个结果

 1 mysql> select * from info1 order by age;
 2 +----+----------+------+  根据年龄来升序排序
 3 | id | name     | age  |
 4 +----+----------+------+
 5 | 12 | lisi     | NULL |
 6 |  1 | tom      |   15 |
 7 |  8 | zhangsan |   16 |
 8 |  2 | jerry    |   18 |
 9 |  9 | wangwu   |   19 |
10 | 10 | qianliu  |   20 |
11 | 11 | zhangsan |   20 |
12 +----+----------+------+
13 7 rows in set (0.00 sec)
14 mysql> select * from info1 order by age desc;
15 +----+----------+------+  根据年龄降序排序
16 | id | name     | age  |
17 +----+----------+------+
18 | 10 | qianliu  |   20 |
19 | 11 | zhangsan |   20 |
20 |  9 | wangwu   |   19 |
21 |  2 | jerry    |   18 |
22 |  8 | zhangsan |   16 |
23 |  1 | tom      |   15 |
24 | 12 | lisi     | NULL |
25 +----+----------+------+
26 7 rows in set (0.01 sec)
27 
28 mysql> select * from info1 order by age desc limit 1;
29 +----+---------+------+   删选出年龄最大的人
30 | id | name    | age  |
31 +----+---------+------+
32 | 10 | qianliu |   20 |
33 +----+---------+------+
34 1 row in set (0.00 sec)
35 mysql> select name from info1 order by age desc limit 1;
36 +---------+   删选出年龄最大的，只显示名字
37 | name    |
38 +---------+
39 | qianliu |
40 +---------+
41 1 row in set (0.00 sec)
42 mysql> select * from info1 order by age limit 1,1;
43 +----+------+------+略过第一个，删选出一个年龄最小的
44 | id | name | age  |
45 +----+------+------+
46 |  1 | tom  |   15 |
47 +----+------+------+
48 1 row in set (0.00 sec)

4.2.4 delete语句


DML操作之删操作delete   只是删除表里面的内容表本身还是存在
语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间
truncate	删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间

truncate

删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表

 1 mysql> select * from info1;     
 2 +----+----------+------+
 3 | id | name     | age  |
 4 +----+----------+------+
 5 |  1 | tom      |   15 |
 6 |  2 | jerry    |   18 |
 7 |  8 | zhangsan |   16 |
 8 |  9 | wangwu   |   19 |
 9 | 10 | qianliu  |   20 |
10 | 11 | zhangsan |   20 |
11 | 12 | lisi     | NULL |
12 +----+----------+------+
13 7 rows in set (0.00 sec)
14 
15 mysql> DELETE FROM info1 WHERE id = 8;   删除id为8的
16 Query OK, 1 row affected (0.00 sec)
17 mysql> select * from info1;
18 +----+----------+------+
19 | id | name     | age  |
20 +----+----------+------+
21 |  1 | tom      |   15 |
22 |  2 | jerry    |   18 |
23 |  9 | wangwu   |   19 |
24 | 10 | qianliu  |   20 |
25 | 11 | zhangsan |   20 |
26 | 12 | lisi     | NULL |
27 +----+----------+------+
28 6 rows in set (0.00 sec)
29 mysql> DELETE FROM info1;       删除名为info1的整个表的内容保留表的结构
30 Query OK, 6 rows affected (0.00 sec)
31 
32 mysql> select * from info1;     查看内容为空
33 Empty set (0.00 sec) 
34 mysql> show tables;   查看有那些表，表的名字和结构还在，
35 +------------------+
36 | Tables_in_school |
37 +------------------+
38 | info             |
39 | info1            |
40 | student          |
41 | student1         |
42 | student2         |
43 +------------------+
44 5 rows in set (0.00 sec)
45 mysql> insert info1(name,age) value('tom',15); 插入一条内容
46 Query OK, 1 row affected (0.01 sec)
47 
48 mysql> select * from info1; 它会在删除的内容基础上加一显示
49 +----+------+------+
50 | id | name | age  |
51 +----+------+------+
52 | 13 | tom  |   15 |
53 +----+------+------+
54 1 row in set (0.00 sec)
55 mysql> truncate table info1;   用truncate删除
56 Query OK, 0 rows affected (0.01 sec)
57 
58 mysql> select * from info1;   表的内容被全部删除
59 Empty set (0.00 sec)
60 mysql> insert info1(name,age) value('tom',15);插入内容
61 Query OK, 1 row affected (0.00 sec)
62 
63 mysql> select * from info1;
64 +----+------+------+     它的id会重置从1开始
65 | id | name | age  |
66 +----+------+------+
67 |  1 | tom  |   15 |
68 +----+------+------+
69 1 row in set (0.00 sec)

4.3 DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示方式	意义
.	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

 1 mysql> grant all on *.* to 'sun'@'localhost' identified by 'SunYiMing123!';   授权所有库所有表所有权限
 2 Query OK, 0 rows affected, 1 warning (0.01 sec)
 3 
 4 mysql> flush privileges;   刷新表的权限 也可以重启数据库服务
 5 Query OK, 0 rows affected (0.00 sec)
 6 mysql> show grants;
 7 +---------------------------------------------------------------------+
 8 | Grants for root@localhost                                           |
 9 +---------------------------------------------------------------------+
10 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
11 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
12 +---------------------------------------------------------------------+
13 2 rows in set (0.00 sec)  查看当前登录用户的授权信息
14 mysql> show grants for 'sun'@'localhost';
15 +--------------------------------------------------+
16 | Grants for sun@localhost                         |
17 +--------------------------------------------------+
18 | GRANT ALL PRIVILEGES ON *.* TO 'sun'@'localhost' |
19 +--------------------------------------------------+
20 1 row in set (0.00 sec)  查看用户sun的授权信息

4.3.3 取消授权REVOKE

mysql> revoke select on *.* from 'sun'@'localhost'; 取消select权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'sun'@'localhost';查看剩下的权限
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'sun'@'localhost'

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

mysql> select * from info1 order by age limit 1,1;+----+------+------+| id | name | age |+----+------+------+| 1 | tom | 15 |+----+------+------+1 row in set (0.00 sec)

posted @ 2022-04-20 23:11 孙一鸣阅读(76) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mysql基础

· 4. mysql数据库备份与恢复

· MySQL基础

· mysql基础

阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 零经验选手，Compose 一天开发一款小游戏！
· 因为Apifox不支持离线，我果断选择了Apipost！
· 通过 API 将Deepseek响应流式内容输出到前端

公告

昵称：孙一鸣
园龄： 2年11个月
粉丝： 1
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

sunyiming023654