Linux学习之MySQL建表

最新推荐文章于 2024-04-16 18:42:22 发布
最新推荐文章于 2024-04-16 18:42:22 发布
阅读量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 2725
一、安装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...
linux系统mysql常用命令.docx
12-31
很全面的mysql操作指令，从安装 修改密码 建库 建表，到后续一系列 常用操作指令；很全面的mysql操作指令，从安装 修改密码 建库 建表，到后续一系列 常用操作指令；很全面的mysql操作指令，从安装 修改密码 建库 ...
MySQL数据库学习笔记之数据表的基本操作（Linux系统）
最新发布
m0_64448698的博客
04-16 988
字段名 数据类型 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建表_mysql建表 字段类型mysql更改root密码
weixin_42584507的博客
02-26 543
方法1：在win2K命令提示符下更改mysql的root管理员密码>mysql -u root -pEnter password: ******mysql> use mysql;mysql> update user set password=password('new_password') where user='root';通过这种方法就可以直接修改密码了。至于在CMD下能否登...
mysql建表的命名规则_mysql建表规约
weixin_39578513的博客
01-19 1221
1、表达是与否概念的字段，必须使用is_xxx 的方式命名。数据类型是：unsigned tinyint。(1表示是，0表示否)。任何字段如果是非负数，必须使用unsigned。POJO类中的任何布尔类型的变量，都不要加 is 前缀。所以，需要在 设置 is_xxx 到Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的 命名方式是为了明确其取值含义与取值范...
linux mysql 建表 ddl,Mysql之DDL 建表过程
weixin_33456137的博客
05-11 106
一。如何创建一张表建表的过程就是声明表头的过程建表的过程就是声明列的过程列：选什么类型 ？？？？给什么样的属性？​建表语句​二.列的类型与属性分类： 数值型--整型，浮点型，定点行字符串：char--varchar-----text日期时间型:​2012-12-1314:28:13​1.整型列的类型bigint 8字节int 4字节mediumint 3字节smallint2字节​tinyint1...
linux mysql 建表 ddl,linux安装mysql和mysqlDDL语言
weixin_42348233的博客
05-11 65
原标题：linux安装mysql和mysqlDDL语言声明：本栏目所使用的素材都是凯哥学堂VIP学员所写，学员有权匿名，对文章有最终解释权；凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。linux安装mysql和mysqlDDL语言Linux安装mysql需要进行以下步骤： 接下来修改密码：在 etc/my.cnf 文件中加入 跳过验证然后重启服务器修改密码 在去除 跳过验证重启服务器即可...
mysql建表double类型长度_MySQL建表规约整理-《Java开发手册》
weixin_39620099的博客
01-30 977
MySQL建表规约整理-《Java开发手册》建库1、库名与应用名称尽量一致建表1、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL 在 Windows下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。...
Linux之MySQL的数据备份
pdp1196431288的博客
12-23 6001
逻辑备份、物理备份、二进制恢复有什么区别，哪种更高效？
1.2 批量生成MySQL建表语句
weixin_40649680的博客
04-09 2501
批量生成MySQL建表语句
安装虚拟机到从安装linux到安装数据库到建表.zip_linux 数据库_建表
09-20
在Linux系统中，数据库的安装通常涉及MySQL或PostgreSQL等关系型数据库管理系统（RDBMS）。以MySQL为例，你可以通过包管理器如apt（Ubuntu）或yum（CentOS）进行安装，命令可能是`sudo apt-get install mysql-server...
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学习-shell实现100以内的奇数和偶数的和
丢爸
01-30 4870
#!/bin/bash # #定义两个变量用于存放奇数和偶数的和 declare -i EVENSUM=0 declare -i OLDSUM=0 for i in {1..100}; do #通过取余的方法判断是否为偶数 if [ $[$i%2] -eq 0 ]; then let OLDSUM+=$i else let EVENSUM+=$i fi done echo "EVENSUM:$EVENSUM,OLDSUM：$OLDSUM" ...
Linux学习-/bin/bash丢失解决方法
丢爸
03-24 4125
如不小心，删除了系统中的/bin/bash程序，系统将无法正常启动，可以通过光盘启动，进入紧急救援模式重新安装bash即可，操作过程如下： 按F2进入BIOS设置系统启动顺序，将Boot下面的CD-ROM Driver调整到最上面,然后保存退出。 选择Resue installed system选择，进入紧急救援模式 选择相应的配置 先挂载光盘，然后进入软件包目录，进入bash的安装，指定root目录将bash安装到源系统的根目录下 安装完成后，切根查看是否正常，正常后，重新启动系统即
Linux学习-nginx学习之location
丢爸
04-18 2734
Nignix之location学习 简介 用户请求时发出请求后，服务器会先进行域名匹配(server_name)，然后再进行URI匹配，URI中没有使用正则表达式，nginx会先在server中的多个location选取匹配度最高的一个URI,URI是用户请求的字符串，即域名后面的web文件路径，然后使用该location模块中的正则URI和字符串，如果匹配成功就结束搜索，并使用此location处理此请求。 语法规则 location [ = | ~ | ~* | ^~ ] /URI {…} 【=】需
Linux学习-postfix配置
丢爸
04-22 2458
本文基本centos6.10,postfix2.11.11 postfix基于客户端的访问控制 基于客户端的访问控制 postfix内置了多种反垃圾邮件的机制，其中就包括“客户端”发送邮件限制，客户端判别机制可以设定一系列客户信息的判别条件 smtpd_client_restrictions：控制客户端连接时的限制(建立tcp连接) smptd_data_restrictions：控制输入客户端输入数据的限制(data) smtpd_helo_restrictions：控制输入helo的限制(helo)
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、付费专栏及课程。

余额充值