Linux学习之MySQL建表

于 2023-09-09 11:28:52 发布
阅读量1k 收藏 5
点赞数 1
分类专栏： MySQL Linux 文章标签： linux 学习 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xuwenpeng/article/details/132773407
版权
Linux 同时被 2 个专栏收录
146 篇文章 4 订阅
订阅专栏
MySQL
6 篇文章 0 订阅
订阅专栏

MySQL查询1 MySQL查询2

表管理
#1. 建库
  #1）库名命名规则
	  仅可以使用数字、字母、下划线、不能纯数字，区分字母大小写，具有唯一性，不可使用MySQL命令或特殊字符

#创建数据表时可以查看一下默认的字符集，8.0后创建数据库和表时默认字符集utf8mb4,5.7之前使用的是latin1，数据库默认编码不设置的话会使用server的字符集，
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

//库名区分字母大小写
mysql> create database gamedb ;
Query OK, 1 row affected (0.14 sec)
mysql> create database GAMEDB ;
Query OK, 1 row affected (0.08 sec)
mysql> create database GAMEDB ;  
ERROR 1007 (HY000): Can't create database 'GAMEDB'; database exists //重名报错
//加if not exists 命令避免重名报错
mysql> create database  if not exists  gamedb ;  
Query OK, 1 row affected, 1 warning (0.03 sec) //正常
mysql> show  databases; //查看创建的库
+--------------------+
| Database           |
+--------------------+
| GAMEDB             |
| gamedb             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tarena             |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database gamedb;  //删除库
Query OK, 0 rows affected (0.11 sec)
mysql> drop database gamedb; // 删除没有的库报错
ERROR 1008 (HY000): Can’t drop database ‘gamedb’; database doesn’t exist
//加if exists 删除没有的库，也不报错
mysql> drop database if exists gamedb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 查看建库语句
mysql> create database studb;
Query OK, 1 row affected (0.10 sec)

mysql> use studb;
Database changed
mysql> show create database studb;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| student  | CREATE DATABASE `studb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#2. 建表
mysql> create table stuinfo(name char(10),grade char(9),sex char(4),age int);
Query OK, 0 rows affected (0.94 sec)
#查看建表语句，COLLATE表示排序规则
mysql> show create table stuinfo;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                       |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stuinfo | CREATE TABLE `stuinfo` (
  `name` char(10) DEFAULT NULL,
  `grade` char(9) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 查看表结构信息
mysql> desc stuinfo;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| grade | char(9)  | YES  |     | NULL    |       |
| sex   | char(4)  | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 删除表
mysql> drop table stuinfo;
Query OK, 0 rows affected (0.37 sec)
# 删除库
mysql> drop database student;
Query OK, 0 rows affected (0.18 sec)
#3. 修改表
 # 修改表名
mysql> alter table studb.stuinfo rename studb.student;
Query OK, 0 rows affected (0.31 sec)
# 查看数据库中包含哪些表
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)
# 删除age表头
mysql> alter table studb.student drop age;
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| grade | char(9)  | YES  |     | NULL    |       |
| sex   | char(4)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 添加表头，默认添加在末尾
mysql> alter table studb.student add mail char(30);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| grade | char(9)  | YES  |     | NULL    |       |
| sex   | char(4)  | YES  |     | NULL    |       |
| mail  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# first指定添加列到行首，after 添加至指定表头名的下方
mysql> alter table studb.student add number char(30) first ;
Query OK, 0 rows affected (0.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| grade  | char(9)  | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| mail   | char(30) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table studb.student add num1 char(30) after sex ;
Query OK, 0 rows affected (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| grade  | char(9)  | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| num1   | char(30) | YES  |     | NULL    |       |
| mail   | char(30) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 修改表头数据类型
mysql> alter table studb.student modify mail char(50);
Query OK, 0 rows affected (1.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| grade  | char(9)  | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| num1   | char(30) | YES  |     | NULL    |       |
| mail   | char(50) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 修改表头名
mysql> alter table studb.student change mail 邮箱 char(50);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| grade  | char(9)  | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| num1   | char(30) | YES  |     | NULL    |       |
| 邮箱   | char(50) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
#删除多个表头
mysql> alter table studb.student drop num1,drop grade;
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| 邮箱   | char(50) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 通过modify修改表头位置
mysql> alter table studb.student modify sex char(4) after number;
Query OK, 0 rows affected (1.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.student;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(30) | YES  |     | NULL    |       |
| sex    | char(4)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| 邮箱   | char(50) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 复制表并将表内数据复制过来
mysql> create table studb.departments select * from tarena.departments;
Query OK, 11 rows affected (0.73 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> desc studb.departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int         | NO   |     | 0       |       |
| dept_name | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) from studb.departments;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tarena.departments;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)
数据类型
# 1. 字符类型
mysql> create table studb.t2(name char(3),address varchar(5));
Query OK, 0 rows affected (0.68 sec)

mysql> desc studb.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| name    | char(3)    | YES  |     | NULL    |       |
| address | varchar(5) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into studb.t2 values('a','a');
Query OK, 1 row affected (0.12 sec)
mysql> insert into studb.t2 values('abc','abc');
Query OK, 1 row affected (0.10 sec)
# name定义为char(3),只能存储3个[字符]，所以报错数据太长了
mysql> insert into studb.t2 values('abcd','abcd');
ERROR 1406 (22001): Data too long for column 'name' at row 1
#mysql8.0使用了utf8mb4字符集，默认支持中文
mysql> insert into studb.t2 values("张无忌","武当山");
Query OK, 1 row affected (0.10 sec)
mysql> select * from studb.t2;
+-----------+-----------+
| name      | address   |
+-----------+-----------+
| a         | a         |
| ab        | ab        |
| abc       | abc       |
| 张无忌    | 武当山    |
+-----------+-----------+
4 rows in set (0.00 sec)
# 2. 数值类型
mysql> create table studb.t1(name char(10),level tinyint unsigned,money double);
Query OK, 0 rows affected (0.59 sec)

mysql> desc studb.t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | char(10)         | YES  |     | NULL    |       |
| level | tinyint unsigned | YES  |     | NULL    |       |
| money | double           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# tinyint unsigned表示无符号短整型，占用1个字节，只能存储0-255的数字
mysql> insert into studb.t1 values("战士",301,1.292);
ERROR 1264 (22003): Out of range value for column 'level' at row 1
mysql> insert into studb.t1 values("战士",255,1.292);
Query OK, 1 row affected (0.10 sec)
#整型不能存储不数，直接对小数进行四舍五入
mysql> insert into studb.t1 values("英雄",1.222,1.292);
Query OK, 1 row affected (0.08 sec)

mysql> select * from studb.t1;
+--------+-------+-------+
| name   | level | money |
+--------+-------+-------+
| 法师   |    80 |    88 |
| 战士   |   255 | 1.292 |
| 英雄   |     1 | 1.292 |
+--------+-------+-------+
3 rows in set (0.00 sec)
# 3. 枚举类型
mysql> create table studb.t8( name char(10),sex enum("男","女","保密"),hobby set("美女","吃","睡"));
Query OK, 0 rows affected (0.60 sec)

mysql> desc studb.t8;
+-------+----------------------------+------+-----+---------+-------+
| Field | Type                       | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+-------+
| name  | char(10)                   | YES  |     | NULL    |       |
| sex   | enum('男','女','保密')     | YES  |     | NULL    |       |
| hobby | set('美女','吃','睡')      | YES  |     | NULL    |       |
+-------+----------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# enum类型的数据只能选择其中一项，set类型的数据可以选择其中多项
mysql> insert into studb.t8 values("小迪总","男人","美女,睡,金钱");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into studb.t8 values("小迪总","男","美女,睡,金钱");
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> insert into studb.t8 values("小迪总","男","美女,睡,吃");
Query OK, 1 row affected (0.12 sec)
mysql> select * from studb.t8;
+-----------+------+----------------+
| name      | sex  | hobby          |
+-----------+------+----------------+
| 小迪总    | 男   | 美女,吃,睡     |
+-----------+------+----------------+
1 row in set (0.00 sec)
# 4. 日期时间
mysql> create table studb.t6(name char(10),birth date,year_birth year,homeparty datetime,partyaddress varchar(15),worktime time);
Query OK, 0 rows affected (1.22 sec)

mysql> desc studb.t6;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name         | char(10)    | YES  |     | NULL    |       |
| birth        | date        | YES  |     | NULL    |       |
| year_birth   | year        | YES  |     | NULL    |       |
| homeparty    | datetime    | YES  |     | NULL    |       |
| partyaddress | varchar(15) | YES  |     | NULL    |       |
| worktime     | time        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> insert into studb.t6 values("小崔",19900101,1990,20230909090000,"郑州",100000);
Query OK, 1 row affected (0.11 sec)

mysql> select * from studb.t6;
+--------+------------+------------+---------------------+--------------+----------+
| name   | birth      | year_birth | homeparty           | partyaddress | worktime |
+--------+------------+------------+---------------------+--------------+----------+
| 小崔   | 1990-01-01 |       1990 | 2023-09-09 09:00:00 | 郑州         | 10:00:00 |
+--------+------------+------------+---------------------+--------------+----------+
1 row in set (0.00 sec)
数据批量处理
# 1.修改检索目录为/myload
# 查看默认检索目录
mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
# 此变量是只读变量，只能修改mysql配置文件，在mysqld下面添加一行secure_file_priv=/myload,并创建/myload目录，修改目录属主和属组为mysql,否则重启mysql服务时会报错
[root@mysql50 ~]# cat /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
...
secure_file_priv=/myload
[root@mysql50 ~]# mkdir /myload
[root@mysql50 ~]# chown mysql.mysql /myload
[root@mysql50 ~]# systemctl restart mysqld
# 2.将/etc/passwd文件导入db1库的user3表里，并添加行号字段
#创建库和表
mysql> create database db1;
Query OK, 1 row affected (0.14 sec)
mysql> create table db1.user3(name varchar(30),password char(1),uid int ,gid int,comment varchar(200),homedir varchar(50),shell varchar(30));
Query OK, 0 rows affected (0.80 sec)

mysql> desc db1.user3;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(30)  | YES  |     | NULL    |       |
| password | char(1)      | YES  |     | NULL    |       |
| uid      | int          | YES  |     | NULL    |       |
| gid      | int          | YES  |     | NULL    |       |
| comment  | varchar(200) | YES  |     | NULL    |       |
| homedir  | varchar(50)  | YES  |     | NULL    |       |
| shell    | varchar(30)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
# 拷贝文件到检索目录 system 在MySQL里执行系统命令
mysql> system cp /etc/passwd /myload/
mysql> system ls /myload
passwd
# 导入数据
mysql> load data infile "/myload/passwd" into table db1.user3 fields terminated by ":" lines terminated by "\n";
Query OK, 25 rows affected (0.13 sec)
Records: 25  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from db1.user3;
+------------------+----------+-------+-------+-----------------------------+------------------+----------------+
| name             | password | uid   | gid   | comment                     | homedir          | shell          |
+------------------+----------+-------+-------+-----------------------------+------------------+----------------+
| root             | x        |     0 |     0 | root                        | /root            | /bin/bash      |
| bin              | x        |     1 |     1 | bin                         | /bin             | /sbin/nologin  |
| daemon           | x        |     2 |     2 | daemon                      | /sbin            | /sbin/nologin  |
| adm              | x        |     3 |     4 | adm                         | /var/adm         | /sbin/nologin  |
| lp               | x        |     4 |     7 | lp                          | /var/spool/lpd   | /sbin/nologin  |
| sync             | x        |     5 |     0 | sync                        | /sbin            | /bin/sync      |
| shutdown         | x        |     6 |     0 | shutdown                    | /sbin            | /sbin/shutdown |
| halt             | x        |     7 |     0 | halt                        | /sbin            | /sbin/halt     |
| mail             | x        |     8 |    12 | mail                        | /var/spool/mail  | /sbin/nologin  |
| operator         | x        |    11 |     0 | operator                    | /root            | /sbin/nologin  |
| games            | x        |    12 |   100 | games                       | /usr/games       | /sbin/nologin  |
| ftp              | x        |    14 |    50 | FTP User                    | /var/ftp         | /sbin/nologin  |
| nobody           | x        | 65534 | 65534 | Kernel Overflow User        | /                | /sbin/nologin  |
| dbus             | x        |    81 |    81 | System message bus          | /                | /sbin/nologin  |
| systemd-coredump | x        |   999 |   997 | systemd Core Dumper         | /                | /sbin/nologin  |
| systemd-resolve  | x        |   193 |   193 | systemd Resolver            | /                | /sbin/nologin  |
| polkitd          | x        |   998 |   995 | User for polkitd            | /                | /sbin/nologin  |
| unbound          | x        |   997 |   994 | Unbound DNS resolver        | /etc/unbound     | /sbin/nologin  |
| tss              | x        |    59 |    59 | Account used for TPM access | /dev/null        | /sbin/nologin  |
| chrony           | x        |   996 |   993 |                             | /var/lib/chrony  | /sbin/nologin  |
| sshd             | x        |    74 |    74 | Privilege-separated SSH     | /var/empty/sshd  | /sbin/nologin  |
| tcpdump          | x        |    72 |    72 |                             | /                | /sbin/nologin  |
| mysql            | x        |    27 |    27 | MySQL Server                | /var/lib/mysql   | /sbin/nologin  |
| apache           | x        |    48 |    48 | Apache                      | /usr/share/httpd | /sbin/nologin  |
| nginx            | x        |   995 |   992 | Nginx web server            | /var/lib/nginx   | /sbin/nologin  |
+------------------+----------+-------+-------+-----------------------------+------------------+----------------+
25 rows in set (0.00 sec)
# 3.将db1库user3表中所有记录导出，存到/myload/user.txt中
mysql> select * from db1.user3 into outfile "/myload/user.txt";
Query OK, 25 rows affected (0.00 sec)

mysql> system ls /myload
passwd  user.txt
mysql> system cat /myload/user.txt
root    x       0       0       root    /root   /bin/bash
bin     x       1       1       bin     /bin    /sbin/nologin
daemon  x       2       2       daemon  /sbin   /sbin/nologin
adm     x       3       4       adm     /var/adm        /sbin/nologin
lp      x       4       7       lp      /var/spool/lpd  /sbin/nologin
sync    x       5       0       sync    /sbin   /bin/sync
shutdown        x       6       0       shutdown        /sbin   /sbin/shutdown
halt    x       7       0       halt    /sbin   /sbin/halt
mail    x       8       12      mail    /var/spool/mail /sbin/nologin
operator        x       11      0       operator        /root   /sbin/nologin
games   x       12      100     games   /usr/games      /sbin/nologin
ftp     x       14      50      FTP User        /var/ftp        /sbin/nologin
nobody  x       65534   65534   Kernel Overflow User    /       /sbin/nologin
dbus    x       81      81      System message bus      /       /sbin/nologin
systemd-coredump        x       999     997     systemd Core Dumper     /       /sbin/nologin
systemd-resolve x       193     193     systemd Resolver        /       /sbin/nologin
polkitd x       998     995     User for polkitd        /       /sbin/nologin
unbound x       997     994     Unbound DNS resolver    /etc/unbound    /sbin/nologin
tss     x       59      59      Account used for TPM access     /dev/null       /sbin/nologin
chrony  x       996     993             /var/lib/chrony /sbin/nologin
sshd    x       74      74      Privilege-separated SSH /var/empty/sshd /sbin/nologin
tcpdump x       72      72              /       /sbin/nologin
mysql   x       27      27      MySQL Server    /var/lib/mysql  /sbin/nologin
apache  x       48      48      Apache  /usr/share/httpd        /sbin/nologin
nginx   x       995     992     Nginx web server        /var/lib/nginx  /sbin/nologin
表头基本约束
约束是一种限制，设置在表头上，用来控制表头的赋值
- NOT NULL：非空，用于保证该字段的值不能为空
- DEFAULT：默认值，用于保证该字段有默认值
- UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可为空
- PRIMARY KEY：主键，用于保证字段的值具有唯一性且非空
- FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键约束，用于引用主表中的某些的值

# 1.表头不允许赋空值练习
mysql> create table db1.t33(name char(10) not null,grade char(7) default "nsd",likes set("money","game","film","music") not null default "film,music");
Query OK, 0 rows affected (0.64 sec)

mysql> desc db1.t33;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | char(10)                           | NO   |     | NULL       |       |
| grade | char(7)                            | YES  |     | nsd        |       |
| likes | set('money','game','film','music') | NO   |     | film,music |       |
+-------+------------------------------------+------+-----+------------+-------+
3 rows in set (0.00 sec)

# 验证默认值和不允许为null
mysql> insert into db1.t33 values(null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null
# 表头likes赋null值，报错
mysql> insert into db1.t33 values("bob",null,null);
ERROR 1048 (23000): Column 'likes' cannot be null
# 符合约束条件不报错
mysql> insert into db1.t33 values("bob",null,"money,game,film");
Query OK, 1 row affected (0.09 sec)
mysql> insert into db1.t33(name) values("lucy");
Query OK, 1 row affected (0.12 sec)
mysql> insert into db1.t33 values("jim","nsd2018","game,film");
Query OK, 1 row affected (0.17 sec)
mysql> select * from db1.t33;
+------+---------+-----------------+
| name | grade   | likes           |
+------+---------+-----------------+
| bob  | NULL    | money,game,film |
| lucy | nsd     | film,music      |
| jim  | nsd2018 | game,film       |
+------+---------+-----------------+
3 rows in set (0.00 sec)
# 2.表头加唯一索引练习
mysql> create table db1.t44(name char(10),passport char(18) unique);
Query OK, 0 rows affected (1.14 sec)

mysql> desc db1.t44;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(10) | YES  |     | NULL    |       |
| passport | char(18) | YES  | UNI | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into db1.t44 values("bob",null);
Query OK, 1 row affected (0.13 sec)

mysql> insert into db1.t44 values("tom","666888");
Query OK, 1 row affected (0.08 sec)
# 表头值不可以重复
mysql> insert into db1.t44 values("jim","666888");
ERROR 1062 (23000): Duplicate entry '666888' for key 't44.passport'
mysql> insert into db1.t44 values("jim","766888");
Query OK, 1 row affected (0.16 sec)

mysql> select * from db1.t44;
+------+----------+
| name | passport |
+------+----------+
| bob  | NULL     |
| tom  | 666888   |
| jim  | 766888   |
+------+----------+
3 rows in set (0.00 sec)
丢爸
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
linux mysql 创建表_Linux下，MySQL-安装，登录和建表
weixin_36400198的博客
02-26 2722
一、安装1、首先检查机器是否已经安装MySQL输入：rpm -qa | grep mysql2、去官网下载相应的rpm包下载：mysql-community-libs-5.7.23-1.el7.x86_64.rpmmysql-community-server-5.7.23-1.el7.x86_64.rpmmysql-community-common-5.7.23-1.el7.x86_64.rpmm...
MySQL数据库学习笔记之数据表的基本操作（Linux系统）
最新发布
m0_64448698的博客
04-16 976
字段名 数据类型 PRIMARY KEY [默认值]例2：定义数据表tb_emp2，其主键为id[CONSTRAINT<约束名>] PRIMARY KEY[字段名]例3：定义数据表tb_emp3，其主键为id例2，例3两个例子执行后的结果是一样的，都会在id字段上设置主键约束字段名 数据类型 UNIQUE例7：定义数据表ttb_dept2，指定部门的名称唯一。[CONSTRAINT <约束名>] UNIQUE(<字段名>)例8：定义数据库表 tb_dept3，指定部门的名称唯一。
【Linux】-- Mysql(2) : 创建数据库和数据表
m0_71272694的博客
11-06 3476
CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的，比如，存储字符串 “abc”，对于 CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)，而同样的 VARCHAR(12) 则只占用4个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。顺便说一下，mysql 中是不区分大小写的，所以随意而为就行了，不过上网查了大部分老成的都会说特殊语句还是用大写的比较好呢。Ubuntu 14.04 64位版本。
Linux_MySQL表操作 DDL操作（建表，删表，改表，查表结构）
dodamce的博客
04-13 809
对表的操作分为两部分，这里先练习DDL操作。 DDL：建表，删表，改表，查表结构 DML：对表内部的信息进行增删查改 文章目录1.DDL操作创建表查看表结构修改表删除表 1.DDL操作 创建表 create table table_name(field1 datatype comment 'comment对这一列的批注可省略', field2 datatype, field3 datatype.... )character set 字符集 collate 校验规则 engine 存储引擎; fiel
linux mysql数据库、数据表创建
xuyang980926的博客
05-09 821
创建 create table info(name varchar(50),sex int,age varchar(50),class varchar(50),score varchar(50))default character set utf8;create table sales(名称 varchar(50),销售 varchar(50),地点 varchar(50),销售价格 varchar(50))default character set utf8;创建数据表：info。
Linux下MySQL 数据库，数据表使用
时间的博客
06-12 709
创建数据库：create database 库名创建数据表：create table 表名 字段插入数据 ： INSERT INTO 数据表名里面name 是字符串类型的字段VALUES是一个关键字，用于在INSERT INTO语句中指定要插入的值namesexageclassscore创建完毕后在数据表中插入如下数据：tommale20185lilyfemale21268tonymale23165bobmale22。
安装虚拟机到从安装linux到安装数据库到建表.zip_linux 数据库_建表
09-20
在Linux系统中，数据库的安装通常涉及MySQL或PostgreSQL等关系型数据库管理系统（RDBMS）。以MySQL为例，你可以通过包管理器如apt（Ubuntu）或yum（CentOS）进行安装，命令可能是`sudo apt-get install mysql-server...
linux系统mysql常用命令.docx
12-31
很全面的mysql操作指令，从安装 修改密码 建库 建表，到后续一系列 常用操作指令；很全面的mysql操作指令，从安装 修改密码 建库 建表，到后续一系列 常用操作指令；很全面的mysql操作指令，从安装 修改密码 建库 ...
linux下数据库MYSQL语句
04-04
Linux 下 MySQL 数据库常用基本操作 Linux 下 MySQL 数据库是一种关系型数据库管理系统，广泛应用于网络应用程序中。下面是 Linux 下 MySQL 数据库常用基本操作的知识点总结： 显示数据库 在 MySQL 中，使用 `show...
Mysql高性能学习笔记02
05-23
　优化数据类型，基本上是用在建表和修改表的场景上，整个优化数据类型这一话题说下来，基本上都是集中于：对于DB数据的高效存储和高效查询。在原生的Mysql中，数据类型大体上分为以下几种：整数类型、实数类型、...
MySQL+人大金仓+数据迁移
09-09
"MySQL+人大金仓+数据迁移" MySQL 是一个流行的开源关系数据库管理系统，而人大金仓是基于 PostgreSQL 的一款商业数据库管理系统。数据迁移是指将数据从一个数据库管理系统迁移到另一个数据库管理系统的过程。本文...
Linux下新建一个MySQL数据库
热门推荐
白水的博客
04-20 5万+
方法适用于MySQL和MariaDB，MariaDB完全兼容MySQL 首先登入MySQL mysql -u root -p //之后输入自己的root密码 下面新建一个数据库 mynewdatabase create database mynewdatabase; //注意别漏了“;”分号哦 这下就新建了一个数据库 mynewdatabase 我们来查看一下： show datab...
linux命令：mysql中创建database（数据库）并且指定编码
陈哈哈的菜园子
03-02 5740
在mysql中是不区分大小写的，所以命令大小写都没关系； 下面这条命令指：创建一个名叫“test”的数据库（database），同时指定编码：utf8 CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_general_ci; ...
Linux命令行执行sqlite3命令创建表格，插入数据，获取数据
m0_51429770的博客
09-25 2124
Sqlite3命令行实现表格相关操作（创建表格，插入数据，获取数据）
MySQL—Linux下认识数据库与创建数据库
weixin_68644350的博客
03-14 2475
库操作和表操作
MySQL ----索引
520_LH
08-28 293
1：索引的定义： 索引是帮助MySql高效获取数据的有序数据结构，在数据库系统维护数据之外，还维护着满足特定算法的数据结构，这种数据结构以某种的特定的方式指向数据，这样就可以在这些数据上实现高级算法，这就是索引。 2：索引的优缺点 2.1 索引类似于书籍的目录，提高数据的查询速度，降低数据库的IO 2.2 通过索引对数据进行排序，降低数据库排序成本，降低cpu消耗 2.3 索引实际上可以算作一张保存了主键和指向实体类的一些记录，需要占用物理空间 2.4 索引同时会降低操作数...
linux mysql创建一张表_Linux学习笔记：MySQL创建表
weixin_31715171的博客
02-03 1587
语句Create Table: CREATE TABLE `student` (`id` int(4) NOT NULL,`name` varchar(16) NOT NULL,`score` int(1) DEFAULT ‘0‘) ENGINE=InnoDB DEFAULT CHARSET=utf8增加主键alter table student change id id int primary ...
linux mysql如何建表_linux下怎么在数据库中创建表
weixin_34847198的博客
01-26 1343
满意答案gg64092017.09.09采纳率：46%等级：7已帮助：110人1.设置oracle_sid默认的oracle_sid是orcl，我个人强烈建议起个唯一、且意义的名字，比如**testdb，**proddb等等，在oracle用户下执行：exportoracle_sid=lxdbtest2.重建pfile在$oracle_home/dbs目录下重建pfile(initorac...
linux 装mysql 建表_Linux下安装MySQL并为其创建新用户图解教程
weixin_31774171的博客
01-27 322
第三步:安装MySQL安装顺序:先安装服务器,然后再安装客户端Linux命令:rpm-ivhMySQL-server-5.5.28-1.rhel4.x86_64.rpm安装后启动MySQL命令: /etc/init.d/mysql start(有的版本在安装完服务器就已经启动就不需要执行)出现如上提示,启动成功.可以使用命令查看安装了哪些命令：rpm -qa|grep -i mysql...
linux mysql建表
04-11
在Linux环境下建立MySQL表需要先安装MySQL数据库，然后通过命令行进入MySQL环境，创建具体的数据库和表。具体步骤如下： 1. 安装MySQL数据库 sudo apt-get install mysql-server 2. 进入MySQL环境 mysql -u root -...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值