一,安装MYSQL数据库时生成系统使用的数据库。
1,显示数据库
2,创建数据库
3,选择数据库
mysql> use stusys;
Database changed
4,修改数据库
mysql> alter database stusys
-> default character set gb2312
-> default collate gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)
5, 删除数据库
mysql> drop database stusys;
Query OK, 0 rows affected (0.01 sec)
二,表的基本概念
表(TABLE)是数据库中用来存储数据的对象,是有结构的数据的集合,是整个数据库系统的基础。SQL数据库中用于存储数据的工具。表是包含数据库中所有数据的数据库对象。表定义为列的集合。与电子表格相似,数据在表中式按行和列的格式组织排列的。表中的每一列都设计为存储某种类型的信息(例如日期、名称、美元金额或数字)。表上有几种控制(约束、规则、默认值和自定义用户数据类型)用于确保数据的有效性。
1,数值类型
(1)整数
(2)定点数
(3)浮点数
2,字符串类型
char(n)
varchar(n)
tinytext
text
3,日期和时间类型
date
time
datetime
timestamp
year
4,二进制数据类型
(1)binary
(2)blob
5,其它数据类型
(1),枚举类型
(2),集合类型
6,数据类型的选择
三,定义表
1,创建
create table student
-> (
-> sno char(6) NOT NULL PRIMARY KEY,
-> sname char(8) NOT NULL,
-> ssex char(2) NOT NULL DEFAULT '男',
-> sbirthday date NOT NULL,
-> speciality char(12) NULL,
-> tc tinyint NULL
-> );
Query OK, 0 rows affected (0.03 sec)
2,复制
mysql> use stusys;
Database changed
mysql> create table student1 like student;
Query OK, 0 rows affected (0.01 sec)
3,(1)查看
mysql> use stusys;
Database changed
mysql> show tables;
+------------------+
| Tables_in_stusys |
+------------------+
| student |
| student1 |
+------------------+
2 rows in set (0.01 sec)
(2)查看数据库基本结构
mysql> show columns from student;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sbirthday | date | NO | | NULL | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint(4) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
3,查看详细结构
mysql> show create table tb_name;
ERROR 1146 (42S02): Table 'stusys.tb_name' doesn't exist
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`sno` char(6) NOT NULL,
`sname` char(8) NOT NULL,
`ssex` char(2) NOT NULL DEFAULT '男',
`sbirthday` date NOT NULL,
`speciality` char(12) DEFAULT NULL,
`tc` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
4,修改表
(1),添加列
mysql> alter table stusys.student1
-> Add COLUMN sid int NOT NULL UNIQUE AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC stusys.student1;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| sid | int(11) | NO | UNI | NULL | auto_increment |
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sbirthday | date | NO | | NULL | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint(4) | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
(2)修改列
mysql> alter table stusys.student1
-> CHANGE COLUMN sbirthday sage tinyint DEFAULT 18;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC stusys.student1;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| sid | int(11) | NO | UNI | NULL | auto_increment |
| sno | char(6) | NO | PRI | NULL | |
| sname | char(8) | NO | | NULL | |
| ssex | char(2) | NO | | 男 | |
| sage | tinyint(4) | YES | | 18 | |
| speciality | char(12) | YES | | NULL | |
| tc | tinyint(4) | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
(3)删除列
mysql> ALTER TABLE stusys.student1
-> DROP COLUMN sid;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
4,重命名表
mysql> ALTER TABLE stusys.student1
-> RENAME TO stusys.student2;
Query OK, 0 rows affected (0.01 sec)
5,删除表
mysql> DROP TABLE stusys.student3;
Query OK, 0 rows affected (0.01 sec)
五,存储引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)