MySQL day1---初涉MySQL 基本操作数据类型约束

最新推荐文章于 2021-05-29 13:37:49 发布

ccccccccccs

最新推荐文章于 2021-05-29 13:37:49 发布

阅读量352

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/cswoniu111/article/details/78006130

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

因为web课的课设，今天竟然神不知鬼不觉的看了一部分MySQL的javaweb网课，感觉还是有收获。既然毫不知觉的开始看了，希望自己能坚持下来。附上网课地址，感觉很全面，希望能看完（希望不是flag）。http://www.imooc.com/course/programdetail/pid/31

--Chapter 1--------------------------------------------------------------------------------------------------------------

MySQL登录：mysql -u账户 -p密码 (这应该是最简单的登录方式了还有可以 eg:mysql -uroot -p -P3306 -h127.0.0.1）

MySQL退出：exit; 或者 quit; 或者 \q;

修改MySQL命令提示符：PROMPT

eg:

mysql>prompt \u@\h \d>
PROMPT set to '\u@\h \d>'
root@localhost (none)>

查看MySQL版本:SELECT VERSION()

eg:

root@localhost (none)>SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

查看用户：SELECT USER();

eg:

root@localhost (none)>SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

创建数据库:CREATE DATABASE table_name;

eg:

mysql>CREATE DATABASE t1;
Query OK, 1 row affected (0.00 sec)

展示所有数据库:SHOW DATABASE;

eg:

mysql>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| t1                 |
+--------------------+
5 rows in set (0.00 sec)

查看指定数据库：SHOW CREATE DATABASE table_name;

eg:

mysql>SHOW CREATE DATABASE t1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

特定方式创建数据库：CREATE DATABASE IF NOT EXISTS table_name CHARACTER SET gbk;

eg:

mysql>CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)

mysql>SHOW CREATE DATABASE t2;
+----------+------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------+
| t2 | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

修改指定数据库的编码方式：ALTER DATABASE t2 CHARACTER SET utf8;

eg:

mysql>ALTER DATABASE t2 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql>SHOW CREATE DATABASE t2;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t2 | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

删除指定数据库：DROP DATABASE table_name;

eg:

mysql>DROP DATABASE t1;
Query OK, 0 rows affected (0.01 sec)

mysql>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| t2                 |
+--------------------+
5 rows in set (0.00 sec)

如果存在的情况下删除（防止出错）：DROP DATABASE if exists table_name;

eg:

            mysql>DROP DATABASE T1;
            ERROR 1008 (HY000): Can't drop database 't1'; database doesn't exist
            mysql>DROP DATABASE if exists T1;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

展示警告：SHOW WARNINGS;

--Chapter 2--------------------------------------------------------------------------------------------------------------

数据类型：

1.整型：

类别               字节数

TINYINT             1
SMALLINT 2
MEDIUMINT    3
INT                     4
BIGINT 8
2.浮点型

FLOAT 单精度 E 38
DOUBLE 双精度 E 308
3.日期时间型

YEAR           1
TIME    3
DATE 3
DATETIME      8
TIMESTAMP   4
4.字符型

CHAR(M)       M个字节
VERCHAR(M)
TINYTEST         L+1
TEXT                  L+2
MEDIUMTEXT     L+3
LONGTEXT         L+4
ENUM    1/2 字节
SET    1 2 3 4 / 8字节（排列组合）

数据表：

行记录列字段
插入记录查找记录
创建数据表约束使用

创建数据表：

CREATE TABLE tb1(
-> username VARCHAR(20),

->age TINYINT(3) UNSIGNED,

->salary FLOAT(8,2)

-> );

查看数据表：SHOW COLUMNS FROM tb1;

插入记录：INSERT tb1 VALUES('TOM',25,7584.23);

插入特定项:INSERT tb1(username,salary) VALUES('JOHN',8000);

查看所有记录：SELECT * FROM tb1;

插入非空项：CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL,
    -> salare float(8.2) NULL
    -> );

eg:

mysql> CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL,
    -> salare float(8.2) NULL
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> SHOW COLUMNS FROM tb2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES |     | NULL    |       |
| salare   | float               | YES |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

此时插入的数据项 username不能为NULL

mysql> INSERT tb2 VALUES(NULL,25,13141);
ERROR 1048 (23000): Column 'username' cannot be null

主值约束：

               eg:

mysql> CREATE TABLE tb3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> SHOW COLUMNS FROM tb3;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

AUTO_INCREMENT 一定主值主值不一定AUTO_INCREMENT

mysql> INSERT tb3(username) VALUES('John');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tb3(username) VALUES('Rose');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tb3(username) VALUES('Dim');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM tb3
    -> ;
+----+----------+
| id | username |
+----+----------+
| 1 | TOM      |
| 2 | John     |
| 3 | Rose     |
| 4 | Dim      |
+----+----------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE tb4(
    -> id SMALLINT UNSIGNED PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW COLUMNS FROM tb4;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT tb4 VALUES(4,'TOM');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT tb4 VALUES(22,'TOM');
Query OK, 1 row affected (0.08 sec)

mysql>
mysql> SELECT * FROM tb4;
+----+----------+
| id | username |
+----+----------+
| 4 | TOM      |
| 22 | TOM      |
+----+----------+
2 rows in set (0.00 sec)

mysql> INSERT tb4 VALUES(22,'TOM');
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'

主值保证不重复
仅可有一个

唯一约束：

eg:

mysql> CREATE TABLE tb5
    -> (id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age tinyint UNSIGNED);
Query OK, 0 rows affected (0.45 sec)

mysql> SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned | YES |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT tb5(username,age) VALUE('T0M',22);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT tb5(username,age) VALUE('T0M',22);
ERROR 1062 (23000): Duplicate entry 'T0M' for key 'username'

唯一约束可有多个

默认约束：

eg:

mysql> CREATE TABLE tb7(
    -> 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.25 sec)

mysql> SHOW COLUMNS FROM tb7
    -> ;
+----------+----------------------+------+-----+---------+----------------+
| 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       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT tb7(username) values('TOM');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM tb7;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | TOM      | 3    |
+----+----------+------+
1 row in set (0.00 sec)