MySQL基础

两种启动mysql的方法，第二种具体实现仍需了解。

 　　　　

　　

 

1个字节就是8个bit,也就是8位，最大就是8个1（11111111）即十进制255

通过控制台登陆mysql数据库时：mysql　　-uroot　　-proot　　-P3306　　-h127.0.0.1(-P代表的是端口号，默认3306，-h代表的是服务器名称，默认本机127.0.0.1)

mysql> select database();　　查看当前使用的数据库

查看数据库中的表：show tables;

 查看数据表结构：

mysql> show columns from db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

mysql> create table tab1(
    -> id smallint unsigned auto_increment primary key,
    -> username varchar(30) not null
    -> );

mysql> insert tab1(username) values('john');

mysql> create table tb6(
    -> id smallint unsigned auto_increment primary key,
    -> username varchar(20) not null unique key,
    -> sex enum('1','2','3') default '3'
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> show columns from tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+

 mysql> select * from tb6;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Jane     | 3    |
+----+----------+------+

查看默认存储引擎，去配置文件里看，即my.ini文件

外键列和参照列必须具有相似的数据类型，否则会报错

mysql> create table provinces(
    -> id smallint unsigned primary key auto_increment,
    -> pname varchar(20) not null
    -> );

mysql> show create table provinces;

mysql> create table users(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(10) not null,
    -> pid bigint,
    -> foreign key(pid) references provinces(id)
    -> );
ERROR 1005 (HY000): Can't create table 'day15.users' (errno: 150)

mysql> create table users(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(10) not null,
    -> pid smallint,
    -> foreign key(pid) references provinces(id)
    -> );
ERROR 1005 (HY000): Can't create table 'day15.users' (errno: 150)

mysql> create table users(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(10) not null,
    -> pid smallint unsigned,
    -> foreign key(pid) references provinces(id)
    -> );
Query OK, 0 rows affected (0.08 sec)

 mysql> show indexes from provinces;
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C
ardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| provinces |          0 | PRIMARY  |            1 | id          | A         |
         0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show indexes from provinces\g;
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | C
ardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
| provinces |          0 | PRIMARY  |            1 | id          | A         |
         0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+--
-----------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show indexes from provinces\G;
*************************** 1. row ***************************
        Table: provinces
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> show indexes from users\G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

 mysql> show create table users;

| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |