网站代运营推广,wordpress百度推送工具,wordpress播放mp4,网页设计与制作模板图片MySQL查询1 MySQL查询2
表管理
#1. 建库#1#xff09;库名命名规则仅可以使用数字、字母、下划线、不能纯数字#xff0c;区分字母大小写#xff0c;具有唯一性#xff0c;不可使用MySQL命令或特殊字符#创建数据表时可以查看一下默认的字符集#xff0c;8.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): Cant 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 ENCRYPTIONN */ |
---------------------------------------------------------------------------------------------------------------------------------------------
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
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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服务时会报错
[rootmysql50 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
...
secure_file_priv/myload
[rootmysql50 ~]# mkdir /myload
[rootmysql50 ~]# chown mysql.mysql /myload
[rootmysql50 ~]# 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: 0mysql 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)
