Mysql学习笔记2--使用入门
mysql> create database kakaxi;
Query OK, 1 row affected (0.02 sec)
---要知道系统中有哪些数据库，可以使用如下命令查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| kakaxi |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
information_schema-- 主要存储系统中的一些数据库对象信息，比如用户表信息，列信息，权限信息，字符集信息，分区信息。
mysql -- 存储了系统的用户权限信息。
performance_schema -- 是mysql 提供的在系统底层监视mysql服务器性能的一个特性，performance_schema存储引擎会监视mysql服务的事件。
sys -- mysql5.7 增加了sys系统数据库，通过这个库可以快速的了解系统元数据信息。
---查询当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> use kakaxi;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| kakaxi |
+------------+
1 row in set (0.00 sec)
---删除数据库
mysql> drop database kakaxi;
Query OK, 0 rows affected (0.56 sec)
---使用use dbname 选择要连接的数据库
mysql> use mysql;
Database changed
---查看数据库中存在的表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.04 sec)
---创建表，由于mysql 的表名是以目录的形式存在磁盘上的，所以表名要符合任何目录允许的字符
mysql> create table delp(deptno int(4),deptname varchar(50));
Query OK, 0 rows affected (0.74 sec)
---查看表结构
mysql> desc delp
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int(4) | YES | | NULL | |
| deptname | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.11 sec)
mysql> create table employee(eid int(4),ename varchar(50),birthday date,deptno i
nt (4));
Query OK, 0 rows affected (0.38 sec)
---查看构造表的ddl语句
mysql> show create table employee \G;
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`eid` int(4) DEFAULT NULL,
`ename` varchar(50) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`deptno` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
ERROR: （这里有个ERROR,暂时还不明白是什么意思）
No query specified
---alter table的使用方法
mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.22 sec)
mysql> desc emp
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| eid | int(4) | YES | | NULL | |
| ename | varchar(50) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| deptno | int(4) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.07 sec)
---修改表中列名
alter table tabname change column old_col_name new_col_name col_definition;
---将birthday 改为birthdate
mysql> alter table emp change birthday birdate date;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| eid | int(4) | YES | | NULL | |
| ename | varchar(50) | YES | | NULL | |
| birdate | date | YES | | NULL | |
| deptno | int(4) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
---修改表中字段的类型
mysql> alter table emp modify eid int(5);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table tabname modify [column] column_definition [FIRST|AFTER col_name]
mysql> desc emp;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| eid | int(5) | YES | | NULL | |
| ename | varchar(50) | YES | | NULL | |
| birdate | date | YES | | NULL | |
| deptno | int(4) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
比较一下change 和modify 的区别：
1. change 和modify 都可以修改表的定义，不同的是change 后面需要写两次列名，不方便。
2. change的优点是可以修改表中的列名称，而modify则不能。
