mysql/数据库管理.md at main

wxin/mysql

Fork 0

wxin 6465a57054 更新数据库管理.md

2025-03-11 16:47:28 +08:00

26 KiB

Raw Permalink Blame History

数据库管理

一：数据类型

在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。

1. 数值类型

整数类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：用于存储用户的年龄、游戏的Level、经验值等

浮点数类型

浮点数类型：FLOAT DOUBLE

作用：用于存储用户的身高、体重、薪水等

float(5,3)       # 5宽度   3精度
注意：
	宽度不算小数点
	宽度-精度=整数

案例：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1(id float(6,2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('2.22');
Query OK, 1 row affected (0.02 sec)

定点数类型

定点数类型：DEC

作用：定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据

位类型

位类型：BIT

作用：BIT（M）可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

2. 字符串类型

CHAR系列：CHAR VARCHAR
TEXT系列：TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB系列：TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列：BINARY VARBINARY

枚举类型

枚举类型：枚举列可以把一些不重复的字符串存储成一个预定义的集合

mysql> create table enum_table(e ENUM('fish','apple','dog'));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into enum_table(e) values('fish');
Query OK, 1 row affected (0.01 sec)

mysql> select * from enum_table;
+------+
| e    |
+------+
| fish |
+------+
1 row in set (0.00 sec)

mysql> insert into enum_table(e) values('nihao');
ERROR 1265 (01000): Data truncated for column 'e' at row 1

时间和日期类型

时间和日期类型：DATE、TIME、DATETIME、TIMESTAMP、YEAR

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

mysql> create table t2(
    -> id1 timestamp NOT NULL default CURRENT_TIMESTAMP,
    -> id2 datetime default NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2(id1) values('20250311104200');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+---------------------+------+
| id1                 | id2  |
+---------------------+------+
| 2025-03-11 10:42:00 | NULL |
+---------------------+------+
1 row in set (0.00 sec)

	timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。
扩展：
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2025-03-11 10:42:44 |
+---------------------+
1 row in set (0.00 sec)

二：表操作

使用编辑器编辑指令

mysql> edit 
mysql> \e

在mysql客户端内执行系统命令

mysql> system  ls
mysql> \!  ls

1. 案例

表：school.student1

字段		    字段		        字段
id             name	        sex		       age
1              tom		    male	        23		 记录
2              jack		    male	        21		 记录
3              alice	    female	        19		 记录

语法：

create table 表名(自定义)(
		字段名1  类型[(宽度) 约束条件],
		字段名2  类型[(宽度) 约束条件],
		字段名3  类型[(宽度) 约束条件]
)[存储引擎 字符集];

1.在同一张表中，字段名是不能相同
2.宽度和约束条件可选
3.字段名和类型是必须的

创建库表：

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school
Database changed
mysql> create table student1(
    -> id int,
    -> name varchar(50),
    -> sex enum('m','f'),
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

查看库：

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)

插入语法：

insert into 表名(字段1,字段2...)  values(字段值列表...);

插入数据：

mysql> insert into student1(id,name,sex,age) values(1,'wxin','m',26);
Query OK, 1 row affected (0.01 sec)

查看表结构：

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 
mysql> show create table student1;       
mysql> show table status like 'student1' \G

查询数据：

mysql> select id,name,sex,age from student1;  //查询表中所有字段的值
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | wxin | m    |   26 |
+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from student1;   //查询表中所有字段的值
+------+------+------+------+
| id   | name | sex  | age  |
+------+------+------+------+
|    1 | wxin | m    |   26 |
+------+------+------+------+
1 row in set (0.00 sec)

mysql> select name,age from student1;   //查询表中指定字段的值
+------+------+
| name | age  |
+------+------+
| wxin |   26 |
+------+------+
1 row in set (0.00 sec)

扩展插入：

mysql> insert into student1 values(2,'wyu','f',25),(3,'wyi','m',24),(4,'wbo','m',23);   //顺序插入
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into student1(name,age) values('zhangsan',22),('lisi',21);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

2. 案例

表：school.student2

               字段名             数据类型
编号             id                   int
姓名			  name			      varchar(50)
出生年份	     born_year	          year
生日			  birthday		      date
上课时间	     class_time	         time
注册时间	     reg_time		     datetime

创建表：

mysql> create table student2(
    -> id int,
    -> name varchar(50),
    -> born_year year,
    -> birthday date,
    -> class_time time,
    -> reg_time datetime
    -> );
Query OK, 0 rows affected (0.00 sec)

插入数据：

mysql> insert into student2 values(1,'tom',now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);
Query OK, 1 row affected (0.00 sec)

表：school.student3

id     id              int
姓名	name	    varchar(50)
性别	sex		    enum('male','female')
爱好	hobby	    set('music','book','game','disc')

创建表：

mysql> create table student3( 
    -> id int, 
    -> name varchar(50), 
    -> sex enum('m','f'), 
    -> hobby set('music','book','game','disc')
	-> );
Query OK, 0 rows affected (0.00 sec)

查看表结构：

mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| id    | int(11)                           | YES  |     | NULL    |       |
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('m','f')                     | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table student3\G
*************************** 1. row ***************************
       Table: student3
Create Table: CREATE TABLE `student3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum('m','f') DEFAULT NULL,
  `hobby` set('music','book','game','disc') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

插入数据：

mysql> insert into student3 values(1,'tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student3 values(2,'jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

注意：

DESCRIBE查看表结构

DESCRIBE 表名;
DESC 表名;

查看表详细结构：

SHOW CREATE TABLE 表名;

三：表完整性约束

1. 作用

用于保证数据的完整性和一致性

2. 约束条件

RPIMARY KEY（PK）：标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE+NOT NULL
FOREIGN KEY（FK）：标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NOT NULL：标识该字段不能为空
UNIQUE KEY（UK）：标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT：标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT：该字段设置默认值

注意：

是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

mysql> sex enum('male','female') not null default 'male'
mysql> age int unsigned NOT NULL default 20
必须为正值（无符号） 不允许为空  默认是20

是否是key 主键 primary key 外键 forengn key

3. NOT NULL

表：school.student4

创建表：

mysql> create table student4(
    -> id int not null,
    -> name varchar(50) not null,
    -> sex enum('m','f') default 'm' not null,
    -> age int unsigned default 18 not null,
    -> hobby set('music','disc','dance','book') default 'book,dance'
    -> );
Query OK, 0 rows affected (0.01 sec)

插入数据：（注意观察查询到的数据）

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;
+----+-------+-----+-----+------------+
| id | name  | sex | age | hobby      |
+----+-------+-----+-----+------------+
|  2 | robin | m   |  18 | dance,book |
+----+-------+-----+-----+------------+
1 row in set (0.00 sec)

注意报错的原因：

mysql> insert into student4 values(3,null,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null

4. 唯一约束

作用：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

表：company.department

创建表：

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> create table company.department1(
    -> dept_id int,
    -> dept_name varchar(30) UNIQUE,
    -> comment varchar(50)
    -> );
Query OK, 0 rows affected (0.00 sec)

查看表结构：

mysql> desc company.department1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

数据插入：（注意查看插入数据时的提示）

mysql> insert into company.department1 values(1,'zhangsan','hr');
Query OK, 1 row affected (0.00 sec)

mysql> insert into company.department1 values(1,'zhangsan','hr');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'

5. 主键约束

注意：

primary key字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL）

表：school.student6

创建表：

mysql> create table school.student6(
    -> id int primary key not null auto_increment,
    -> name varchar(50) not null,
    -> sex enum('m','f') not null default 'm',
    -> age int not null default 18
    -> );
Query OK, 0 rows affected (0.01 sec)

插入数据：

mysql> insert into school.student6 values (1,'alice','f',22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into school.student6(name,sex,age) values ('jack','m',19),('tom','m',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from school.student6;
+----+-------+-----+-----+
| id | name  | sex | age |
+----+-------+-----+-----+
|  1 | alice | f   |  22 |
|  2 | jack  | m   |  19 |
|  3 | tom   | m   |  23 |
+----+-------+-----+-----+
3 rows in set (0.00 sec)

四：修改表

语法格式：

修改表名
1. alter table 表名 rename 新表名
2. rename table 新表名 to 表名
增加字段
1. alter table 表名
  
  add 字段名数据类型 [完整性约束条件...],
  
  add 字段名数据类型 [完整性约束条件...];
2. alter table 表名 add 字段名数据类型 [完整性约束条件...] after 字段名；
删除字段：alter table 表名 drop 字段名
修改字段
1. alter table 表名 modify 字段名数据类型 [完整性约束条件...];
2. alter table 表名 change 旧字段名新字段名旧数据类型 [完整性约束条件...];
3. alter table 表名 change 旧字段名新字段名新数据类型 [完整性数据约束...]：

1. 修改数据库引擎

mysql> alter table student2 engine=myisam;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
//engine=innodb|memory|....

2. 添加字段

mysql> create table student5(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table student5 add name varchar(30) not null,add age int not null default 22;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student5 add stu_num int not null after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student5 add sex enum('m','f') default 'm' first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. 删除字段

mysql> alter table student5 drop sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. 修改字段类型

mysql> desc student5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | int(11)     | NO   |     | 22      |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student5 modify age tinyint not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | tinyint(4)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table student5 modify id int not null primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | tinyint(4)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5. 增加约束

mysql> desc student5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | tinyint(4)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student5 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student5;                                         +---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30) | NO   |     | NULL    |                |
| stu_num | int(11)     | NO   |     | NULL    |                |
| age     | tinyint(4)  | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

6. 增加主键

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student1 add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

7. 修改主键和自增

mysql> alter table student1 modify id int auto_increment;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc student1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)   | YES  |     | NULL    |                |
| sex   | enum('m','f') | YES  |     | NULL    |                |
| age   | int(11)       | YES  |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

8. 删除主键

mysql> desc student5;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30) | NO   |     | NULL    |                |
| stu_num | int(11)     | NO   |     | NULL    |                |
| age     | tinyint(4)  | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student5 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table student5 modify id int not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student5;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| stu_num | int(11)     | NO   |     | NULL    |       |
| age     | tinyint(4)  | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

9. 复制表

复制表结构＋记录（key不会复制: 主键、外键和索引）复制表结构/记录+表结构，不会将Key复制

mysql> create table new_service select * from service;

只复制表结构

mysql> create table new1_service select * from service where 1=2;   //条件为假，查不到任何记录

可以复制主键，只复制表结构

mysql> create table t4 like employees;

10. 删除表

mysql> DROP TABLE 表名;

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
| student3         |
| student4         |
| student5         |
| student6         |
+------------------+
6 rows in set (0.00 sec)

mysql> drop table student5;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1         |
| student2         |
| student3         |
| student4         |
| student6         |
+------------------+
5 rows in set (0.00 sec)

11. 修改数据表中字段的值

语法：

update 表名 set 列名=值 where 条件

mysql> update student set name='123' where id=1;

12. 删除某一行

语法：

delete from 表名 where id = 1

mysql> delete from type where id=1;

五：库操作

1. 简介

系统自带库的含义及作用

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

information_schema：虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
performance_schema：主要存储数据库服务器的性能参数
mysql：授权库，主要存储系统用户的权限信息
sys：主要存储数据库服务器的性能参数

注意：information_schema

SCHEMATA 存放的是系统中的库

mysql> select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | company            | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | utf8                       | utf8_general_ci        | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | school             | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
7 rows in set (0.00 sec)

目录_名称
实际库_名称
默认_字符_设置_名称
默认_分类_名称

TABLES 存储表名

mysql> select * from information_schema.TABLES\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 384
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16434816
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2025-03-11 12:37:46
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=43690
  TABLE_COMMENT:

2. 创建库

方案一：交互式操作

mysql> create database wxin;
Query OK, 1 row affected (0.00 sec)

方案二：非交互式

[root@rpm mysql]# ./bin/mysql -uroot -p'Wfahui@3123' -e "create database wxin";

数据库命名规则：

区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字

sql语句结尾

每条sql语句都要以;结尾，但是如果列比较多，想看的清楚一点，可以以\G结尾

3. 查看数据库

mysql> show databases;
mysql> show create database wxin;
mysql> select database(); 查看当前所在的库

4.切换数据库

mysql> use wxin

5. 删除数据库

mysql> DROP DATABASE 数据库名;

26 KiB Raw Permalink Blame History Unescape Escape

数据库管理

一：数据类型

1. 数值类型

2. 字符串类型

二：表操作

1. 案例

2. 案例

三：表完整性约束

1. 作用

2. 约束条件

3. NOT NULL

4. 唯一约束

5. 主键约束

四：修改表

1. 修改数据库引擎

2. 添加字段

3. 删除字段

4. 修改字段类型

5. 增加约束

6. 增加主键

7. 修改主键和自增

8. 删除主键

9. 复制表

10. 删除表

11. 修改数据表中字段的值

12. 删除某一行

五：库操作

1. 简介

2. 创建库

3. 查看数据库

4.切换数据库

5. 删除数据库

26 KiB

Raw Permalink Blame History