增城企业网站建设,制作视频软件哪个好,大型门户网站建设推广,深圳办公室装修招标一、MySQL数据库表操作
MySQL表的基本概念
在windows中有个程序叫做excel. 而Excel文件中存在了如sheet1、sheet2、sheet3的表, 所有的sheet都存储在这个Excel文件中, 在某个sheet中有相应的数据.
回到数据库和表的关系上来说, 这个Excel文件就是一个数据库, 所有的sheet就是…一、MySQL数据库表操作
MySQL表的基本概念
在windows中有个程序叫做excel. 而Excel文件中存在了如sheet1、sheet2、sheet3的表, 所有的sheet都存储在这个Excel文件中, 在某个sheet中有相应的数据.
回到数据库和表的关系上来说, 这个Excel文件就是一个数据库, 所有的sheet就是存储在库中的表, 表中去存储数据, 而我们学习的MySQL程序就是Excel程序,它是用来集中管理这些Excel文件的工具. 而我们在工作中又称这种机制类型为: 关系型数据库
1.1、MySQL表的操作DDL
数据库DDL操作
系统数据库(了解)
information_schema 虚拟库主要存储了系统中的一些数据库对象的信息例如用户表信息、列信息、权限信息、字符信息等
performance_schema 主要存储数据库服务器的性能参数
mysql 授权库主要存储系统用户的权限信息
sys 主要存储数据库服务器的性能参数目标是把performance_schema的把复杂度降低
创建数据库DDL
1. mysqladmin -u root -p1 create db1
2. 直接去创建数据库目录并且修改权限
3. mysql create database newrain;
数据库命名规则区分大小写唯一性不能使用关键字如 create SELECT不能单独使用数字
查看数据库
mysql show databases; //查看所有数据库
mysql show create database laowang; //查看创建的库信息
mysql SELECT database(); //查看当前库
切换数据库
mysql use laowang
mysql show tables;
删除数据库
DROP DATABASE 数据库名;
表的DDL操作
这些操作都是数据库管理中最基本也是最重要的操作。
内容包括:创建表 create table查看表结构 desc table, show create table表完整性约束修改表 alter table复制表 create table ...删除表 drop table
表是数据库存储数据的基本单位由若干个字段组成主要用来存储数据记录。
命令行操作数据库脚本
mysqlcreate database db2;
mysqluse db2
mysqlcreate table t1(name char(20),age int(2));
# mysql -u root -p1 -e create database newrain
# mysql -u root -p1 -e use newrain;create table t2(name char(20),age int(3))
在mysql客户端内执行系统命令mysql system lsmysql \! lsedit创建表
表:school.student1
字段 字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录
语法
create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
在同一张表中字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的
mysql CREATE DATABASE school;
mysql use school;
mysql create table student1(- id int,- name varchar(50), - sex enum(m,FROM),- age int- );
Query OK, 0 rows affected (0.03 sec)
查看表当前所在库
mysql show tables;
------------------
| Tables_in_school |
------------------
| student1 |
------------------
1 row in set (0.00 sec)
mysql desc student1; //查看表结构
mysql show create table student1;
mysql show table status like student1 \G 显示数据的详细信息
查看表内容
mysql SELECT id,name,sex,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)
mysql SELECT * from student1; //查询表中所有字段的值
Empty set (0.00 sec)
mysql SELECT name,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)修改表
alter 修改表名称 修改字段名称 修改字段数据类型 修改字段的修饰符
insert 插入数据
delete 删除数据
update 更新数据
修改表名称
mysql rename table emp to abc;
mysql alter table abc rename emp;添加新字段
mysql create table t1(id int(5),name varchar(20));
mysql alter table t1 add math int(10);
mysql alter table t1 add (chinese int(10),english int(10));
修改字段数据类型、修饰符约束mysql alter table t1 modify chinese int(5) not null;
修改字段名称、数据类型、修饰符约束mysql alter table t1 change chinese china int(6);mysql alter table t1 change english en int(6) after id;mysql alter table t1 change en en int(6) after name;mysql alter table t1 modify en int(6) first;mysql alter table t1 modify en int(6) after id;删除字段mysql alter table t1 drop en; 插入数据(添加记录)字符串必须引号引起来 mysql insert into t1(id,name,math,china) values(1,wing,80,90);mysql insert into t1(id,name,math,china) values(2,king,70,100),(3,tom,50,70);mysql insert into t1 values(4,xiaosan,50,100);mysql insert into t1(id,math) values(5,70);mysql insert into t1 set id6,math65;
更新记录 mysql update t1 set namelili WHERE id5;
删除记录mysql delete from t1 WHERE id6;mysql delete from t1; //删除所有记录
表复制
复制一张表mysql create table t10(SELECT * from t3);mysql create table t10(SELECT id,name from t3);
复制表结构
mysql desc t3;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.01 sec)
mysql create table t13(SELECT id1,id2 from t3 WHERE 54);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql desc t13;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
-------------------------------------------
2 rows in set (0.00 sec)
mysql create table t14(SELECT id1 from t3 WHERE 54);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql desc t14;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id1 | int(11) | YES | | NULL | |
-------------------------------------------
1 row in set (0.00 sec)
mysql create table t4(SELECT * from t3 WHERE 54);
mysql create table t4(SELECT id,name from t3 WHERE 54);
复制记录
mysql insert into t3 SELECT * from t10 WHERE id9; 俩表格字段要相同
删除表
mysql drop table t1;
删除库
mysql drop database newrain;
了解()
表完整性约束
作用用于保证数据的完整性和一致性约束条件 说明
NOT NULL 标识该字段不能为null
UNIQUE KEY (UK) 标识该字段的值是唯一的可以为空一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长整数类型而且为主键
DEFAULT 为该字段设置默认值
UNSIGNED 无符号(正数)
ZEROFILL 使用0填充例如0000001
说明
1. 是否允许为空默认NULL可设置NOT NULL字段不允许为空必须赋值
2. 字段是否有默认值缺省的默认值是NULL如果插入记录时不给字段赋值此字段使用默认值sex enum(male,female) not null default maleage int unsigned NOT NULL default 20 必须为正值无符号 不允许为空 默认是20
1.2、MySQL数据类型
一、MySQL常见的数据类型
常见的数据类型
数值类型整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT 整型可以指定是有符号的和无符号的默认是有符号的可以通过UNSIGNED来说明某个字段是无符号的。浮点数类型 FLOAT DOUBLE字符串类型CHAR系列 CHAR VARCHARBINARY系列 BINARY VARBINARY 枚举类型 ENUM集合类型 SET 时间和日期类型 DATE TIME DATETIME TIMESTAMP YEAR
二、数据类型测试
整数类型测试tinyint(有符号型最大值127),int有符号型最大值2147483647)
作用用于存储用户的年龄、游戏的Level、经验值等。
LAB1(案例1):
mysql create database db1;
mysql use db1;
mysql create table test1( - tinyint_test tinyint,- int_test int- );
mysql desc test1;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| tinyint_test | tinyint(4) | YES | | NULL | |
| int_test | int(11) | YES | | NULL | |
-----------------------------------------------------
2 rows in set (0.01 sec)
mysql insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)
mysql insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column tinyint_test at row 1
mysql insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)
mysql insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column int_test at row 1
//测试结果默认有符号超过存储范围出错。
LAB2: 无符号整型测试
mysql create table test2(- tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值无符号- int_test int unsigned- );
Query OK, 0 rows affected (0.00 sec)
mysql desc test2;
--------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------------
| tinyint_test | tinyint(3) unsigned | YES | | NULL | |
| int_test | int(10) unsigned | YES | | NULL | |
--------------------------------------------------------------
2 rows in set (0.00 sec)
mysql insert into test2(tinyint_test) values(111);
Query OK, 1 row affected (0.06 sec)
mysql insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column tinyint_test at row 1
测试整数类型的显示宽度int不限制宽度
mysql create table t1 (- id1 int,- id2 int(6)- );
mysql desc t1;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
-------------------------------------------
mysql insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from t1;
------------
| id1 | id2 |
------------
| 1 | 1 |
------------
1 row in set (0.00 sec)
mysql create table t2 (- id1 int zerofill,- id2 int(6) zerofill- );
Query OK, 0 rows affected (0.05 sec)
mysql desc t2;
-------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------
| id1 | int(10) unsigned zerofill | YES | | NULL | |
| id2 | int(6) unsigned zerofill | YES | | NULL | |
-------------------------------------------------------------
2 rows in set (0.00 sec)
mysql insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * from t2;
--------------------
| id1 | id2 |
--------------------
| 0000000002 | 000002 |
--------------------
1 row in set (0.00 sec)
mysql insert into t2 values(3,2222222); //插入大于宽度限制的值仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql SELECT * from t2;
---------------------
| id1 | id2 |
---------------------
| 0000000002 | 000002 |
| 0000000003 | 2222222 |
---------------------
2 rows in set (0.00 sec)
结论整形的宽度仅为显示宽度不是限制。因此建议整形无须指定宽度。
浮点数类型测试
作用用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示(M,D)表示一共显示M位数字整数位
小数位其中D位于小数点后面M和D又称为精度和标度。
float表示的精度大约是7位。
mysql create table test4(float_test float(5,2)); //一共5位小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql desc test4;
---------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------
| float_test | float(5,2) | YES | | NULL | |
---------------------------------------------------
1 row in set (0.00 sec)
mysql insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * from test4;
------------
| float_test |
------------
| 10.20 |
| 70.24 |
| 70.25 |
------------
3 rows in set (0.00 sec)
可以观察到超出表示的范围MySQL在保存值时会进行四舍五入。
mysql insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column float_test at row 1
mysql create table t111(FROM1 float,FROM2 float(10,2));
mysql insert into t111 values(123.123,12345678.90);
mysql SELECT * from t111;
----------------------
| FROM1 | FROM2 |
----------------------
| 123.123 | 12345679.00 |
----------------------
注意如果数据精度丢失那么浮点型是按照四舍五入的方式进行计算
mysql insert into t111 values(123.12345678,123456789.90);
ERROR 1264 (22003): Out of range value for column FROM2 at row 1
超出范围报错
mysql insert into t111 values(123.12345678,99999999.99);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * from t111;
-----------------------
| FROM1 | FROM2 |
-----------------------
| 123.123 | 12345679.00 |
| 123.123 | 100000000.00 |
-----------------------
2 rows in set (0.00 sec)
double精度较高更精确精度有15位左右。
mysql insert into t112 values(123.12345678,99999999.99);
mysql SELECT * from t112;
---------------------------
| FROM1 | FROM2 |
---------------------------
| 123.12345678 | 99999999.99 |
---------------------------
1 row in set (0.00 sec)
定点数decimal测试精度最高
mysql create table test17(id int,a float(10,8),b decimal(10,8));
Query OK, 0 rows affected (0.01 sec)
mysql insert into test17 values (100,23.12345612,23.12345612);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from test17;
--------------------------------
| id | a | b |
--------------------------------
| 100 | 23.12345695 | 23.12345612 |
--------------------------------
1 row in set (0.00 sec)
注float表示的精度大约是7位。decimal整数大位数m为65。支持小数大位数d是30。
时间和日期类型测试year、date、time、datetime、timestamp
作用用于存储用户的注册时间文章的发布时间文章的更新时间员工的入职时间等
mysql create table test_time(- d date,- t time,- dt datetime- );
Query OK, 0 rows affected (0.03 sec)
mysql desc test_time;
--------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
--------------------------------------------
3 rows in set (0.01 sec)
mysql SELECT now();
---------------------
| now() |
---------------------
| 2019-08-19 06:17:28 |
---------------------
1 row in set (0.00 sec)
mysql insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql SELECT * from test_time;
-------------------------------------------
| d | t | dt |
-------------------------------------------
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
-------------------------------------------
1 row in set (0.00 sec)
timestamp
mysql create table d(name char(20),time timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql insert into d values(laowang,null);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from d;
--------------------------------
| name | time |
--------------------------------
| laowang | 2019-08-20 15:06:23 |
--------------------------------
1 row in set (0.00 sec)
注意事项
其它的时间按要求插入
插入年份时尽量使用4位值
插入两位年份时69以20开头比如65, 结果2065 70以19开头比如82结果1982
mysql create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql desc t3;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| born_year | year(4) | YES | | NULL | |
-----------------------------------------------
1 row in set (0.00 sec)
mysql insert into t3 values- (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * from t3;
-----------
| born_year |
-----------
| 2012 |
| 1980 |
-----------
2 rows in set (0.00 sec)
字符串类型测试CHAR、VARCHAR
作用用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度 0 ~ 255
VARCHAR 列中的值为可变长字符串长度 0 ~ 65535
注在检索的时候CHAR列删除了尾部的空格而VARCHAR则保留这些空格
mysql create table vc (- v varchar(4),- c char(4)- );
Query OK, 0 rows affected (0.03 sec)
mysql desc vc;
----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------
| v | varchar(4) | YES | | NULL | |
| c | char(4) | YES | | NULL | |
----------------------------------------------
2 rows in set (0.00 sec)
mysql insert into vc values(ab ,ab );
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql SELECT * from vc;
------------
| v | c |
------------
| ab | ab |
------------
1 row in set (0.00 sec)
mysql SELECT length(v),length(c) from vc;
----------------------
| length(v) | length(c) |
----------------------
| 4 | 2 |
----------------------
1 row in set (0.00 sec)
mysql SELECT concat(v,), concat(c,) from vc; //在后面加字符看的更清楚
------------------------------
| concat(v,) | concat(c,) |
------------------------------
| ab | ab |
------------------------------
1 row in set (0.00 sec)
字符串类型测试BINARY、VARBINARY
BINARY 和 VARBINARY类似于CHAR 和 VARCHAR不同的是它们包含二进制字符而不包含
非二进制字符串
mysql create table binary_t (c binary(3));
mysql insert into binary_t values (10);
Query OK, 1 row affected (0.01 sec)
mysql create table binary_d (c varbinary(3));
mysql insert into binary_d values (10);
Query OK, 1 row affected (0.01 sec)
mysql SELECT length(c) from binary_t WHERE c10;
-----------
| length(c) |
-----------
| 3 |
-----------
1 row in set, 2 warnings (0.01 sec)
mysql SELECT length(c) from binary_d WHERE c10;
-----------
| length(c) |
-----------
| 2 |
-----------
1 row in set (0.00 sec)
字符串类型
ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值爱好1,爱好2,爱好3...
表school.student3
姓名 name varchar(50)
性别 sex enum(m,FROM)
爱好 hobby set(music,book,game,disc)
mysql use school
mysql create table student3(- name varchar(50),- sex enum(m,FROM),- hobby set(music,book,game,disc)- );
Query OK, 0 rows affected (0.31 sec)
mysql desc student3;
---------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------------------
| name | varchar(50) | YES | | NULL | |
| sex | enum(m,FROM) | YES | | NULL | |
| hobby | set(music,book,game,disc) | YES | | NULL | |
---------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql insert into student3 values- (tom,m,book,game);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from student3;
-----------------------
| name | sex | hobby |
-----------------------
| tom | boy | book,game |
-----------------------
1 row in set (0.00 sec)
mysql insert into student3 values (jack,m,play);
ERROR 1265 (01000): Data truncated for column hobby at row 1
mysql show create table student3\G
*************************** 1. row ***************************Table: student3
Create Table: CREATE TABLE student3 (name varchar(50) default NULL,sex enum(m,FROM) default NULL,hobby set(music,book,game,disc) default NULL
) ENGINEMyISAM DEFAULT CHARSETlatin1
1 row in set (0.00 sec)MySQL约束
约束条件 说明
- NULL 标识是否允许为空默认为NULL。
- NOT NULL 标识该字段不能为空可以修改。
- UNIQUE KEY (UK) 标识该字段的值是唯一的可以为空一个表中可以有多个UNIQUE KEY
- DEFAULT 为该字段设置默认值
- UNSIGNED 无符号正数
- PRIMARY KEY (PK) 标识该字段为该表的主键可以唯一的标识记录不可以为空
- AUTO_INCREMENT 标识该字段的值自动增长整数类型而且为主键
- UNIQUE NOT NULL
- FOREIGN KEY (FK) 标识该字段为该表的外键实现表与表父表主键/子表1外键/子表2外键之间的关联
not null default例子
mysql create table t7 (id int not null default 8);
Query OK, 0 rows affected (0.02 sec)
mysql desc t7;
-------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------
| id | int(11) | NO | | 8 | |
-------------------------------------------
1 row in set (0.00 sec)
mysql insert into t7 values();
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from t7;
----
| id |
----
| 8 |
----
1 row in set (0.00 sec)
mysql insert into t7 values(NULL);
ERROR 1048 (23000): Column id cannot be null
auto_increment例子
自增键每张表只能一个字段为自增
mysql create table t8(id int unique auto_increment,name char(10));
mysql desc t8;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
-----------------------------------------------------
2 rows in set (0.00 sec)
mysql insert into t8 (name) values(newrain);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * from t8;
--------------
| id | name |
--------------
| 1 | newrain |
--------------
1 row in set (0.00 sec)
mysql insert into t8 (name) values(ehome);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from t8;
--------------
| id | name |
--------------
| 1 | newrain |
| 2 | ehome |
--------------
2 rows in set (0.00 sec)
default例子
mysql create table db1.student4 (- id int not null,- name varchar(50) not null,- sex enum(m,FROM) default m not null,- age int unsigned default 18 not null,- hobby set(music,disc,dance,book) default book,dance);
mysql desc db1.student4;
-------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------------------------------
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| sex | enum(m,FROM) | NO | | m | |
| age | int(10) unsigned | NO | | 18 | |
| hobby | set(music,disc,dance,book) | YES | | dance,book | |
-------------------------------------------------------------------------
5 rows in set (0.00 sec)
mysql insert into student4 values (1,jack,m,20,book);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * from student4;
------------------------------------
| id | name | sex | age | hobby |
------------------------------------
| 12 | newrain | m | 18 | dance,book |
------------------------------------
2 rows in set (0.00 sec)
mysql insert into student4 (id,name) values (12,newrain);
Query OK, 1 row affected (0.01 sec)
mysql SELECT * from student4;
------------------------------------
| id | name | sex | age | hobby |
------------------------------------
| 12 | newrain | m | 18 | dance,book |
| 1 | jack | m | 20 | book |
------------------------------------
2 rows in set (0.00 sec)
mysql insert into student4 values (3,NULL,m,22,book);
ERROR 1048 (23000): Column name cannot be null
unique例子 uniquekey 唯一的
第一种写法
mysql create table department1(- dept_id INT,- dept_name varchar(30) unique- comment varchar(50)- );
第二种写法
mysql create table department1(- dept_id INT,- dept_name varchar(30) unique- comment varchar(50)- UNIQUE(dept_name)- );
mysql desc department1;
---------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
---------------------------------------------------
3 rows in set (0.00 sec)
mysql insert into department1 values (1,zhangsan,yyy);
Query OK, 1 row affected (0.00 sec)
mysql insert into department1 values (1,zhangsan,yyy);
ERROR 1062 (23000): Duplicate entry zhangsan for key dept_name
mysql insert into department1 values (1,wangsi,yyy);
Query OK, 1 row affected (0.00 sec)
primary keykey
每张表里只能有一个主键
不能为空而且唯一
mysql use db1;
mysql create table t7(hostname char(20) primary key,ip char(150));
mysql create table t9(hostname char(20),ip char(150),primary key(hostname));
mysql alter table t7 drop primary key; //删除主键
Query OK, 3 rows affected (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 0
联合主键(有一个主键不一样即可)
mysql create table t10(hostname char(20),ip char(150),primary key(hostname,ip));
Query OK, 0 rows affected (0.02 sec)
mysql insert into t10 values (hah,ksk);
Query OK, 1 row affected (0.01 sec)
mysql insert into t10 values (hah,ksk);
ERROR 1062 (23000): Duplicate entry hah-ksk for key PRIMARY
mysql insert into t10 values (hah,ks);
Query OK, 1 row affected (0.00 sec)
mysql insert into t10 values (ha,ks);
Query OK, 1 row affected (0.01 sec)
索引和外键拓展
index(key)
索引 优化查询速度
mysql create table t105(hostname char(20) primary key,ip char(150),index dizhi(ip));//添加索引到列名ip索引名为dizhi
mysql create index dizhi on t105(ip); //单独创建索引
mysql drop index dizhi on t105; //删除index索引
mysql alter table t101 drop index dizhi; //删除index索引外键foreign key (key)
mysql create table t1(id int,manager char(10) primary key) engine innodb
mysql create table t2(id int,admin char(10),foreign key (admin) references t1 (manager)) engine innodb;
1.3、MySQL数据操作DML
在MySQL管理软件中可以通过SQL语句中的DML语言来实现数据的操作包括使用INSERT实现数据
的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
更新数据 insert
更新数据 update
删除数据 delete
一、插入数据INSERT
1. 插入完整数据顺序插入语法一INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES (值1,值2,值3…值n);
语法二INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据语法INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);
3. 插入多条记录语法INSERT INTO 表名 VALUES(值1,值2,值3…值n),(值1,值2,值3…值n),(值1,值2,值3…值n);4. 插入查询结果语法INSERT INTO 表1(字段1,字段2,字段3…字段n) SELECT (字段1,字段2,字段3…字段n) FROM 表2WHERE …;
例子
mysql create table student7(id int,name varchar(20),sex enum(m,FROM),age int(2));
mysql desc student7;
-------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | enum(m,FROM) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
-------------------------------------------------
4 rows in set (0.00 sec)
mysql insert into student7 values(1,jack,m,20); \\顺序插入数据
mysql insert into student7(name,age) values(bob,21); \\指定字段插入数据
mysql insert into student7 values(6,jex,m,21),(7,bob1,FROM,22); \\插入多条记录
插入查询结果
mysql create table student_his(id int,name varchar(20),sex enum(m,FROM),age int(2));
mysql insert into student_his SELECT * from student7 WHERE name bob; \\插入查询结果
mysql insert into student_his SELECT * from student7 WHERE age 17; \\插入符合条件的
二、更新数据UPDATE
语法UPDATE 表名 SET 字段1值1,字段2值2 WHERE CONDITION;
示例
mysql update student7 set id8; #修改全部
mysql SELECT * from student7;
------------------------
| id | name | sex | age |
------------------------
| 8 | jex | m | 21 |
| 8 | bob1 | FROM | 22 |
------------------------
2 rows in set (0.00 sec)
mysql update student7 set id9 WHERE namebob1; 指定条件
mysql SELECT * from student7;
------------------------
| id | name | sex | age |
------------------------
| 8 | jex | m | 21 |
| 9 | bob1 | FROM | 22 |
------------------------
2 rows in set (0.00 sec)
mysql update student7 set id10,namenewrain WHERE namebob1;
mysql SELECT * from student7;
----------------------------
| id | name | sex | age |
----------------------------
| 8 | jex | m | 21 |
| 10 | newrain | FROM | 22 |
----------------------------
2 rows in set (0.01 sec)
三、删除数据DELETE
语法DELETE FROM 表名 WHERE CONITION;
示例DELETE FROM mysql.user WHERE authentication_string’’;
delete from 表名 WHERE 条件;
mysql delete from student7 WHERE namejack;
delete from 表名; //删除表的全部数据
mysql delete from student7;
Query OK, 3 rows affected (0.00 sec)
mysql SELECT * from student7;
Empty set (0.01 sec)
作业 更新MySQL root用户密码NewRain!#
注表的修改练习作为课下作业
二、MySQL数据库的查询操作
MySQL数据库查询
2.1、MySQL单表查询
准备测试表company.employee5
字段解析字段名字段类型雇员编号idint雇员姓名namevarchar(30)雇员性别sexenum雇用时期hire_datedate雇员职位postvarchar(50)职位描述job_descriptionvarchar(100)雇员薪水salarydouble(15,2)办公室officeint部门编号dep_idint
mysql CREATE TABLE company.employee5(id int primary key AUTO_INCREMENT not null,name varchar(30) not null,sex enum(male,female) default male not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);
mysql insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values (jack,male,20180202,instructor,teach,5000,501,100),(tom,male,20180203,instructor,teach,5500,501,100),(robin,male,20180202,instructor,teach,8000,501,100),(alice,female,20180202,instructor,teach,7200,501,100),(tianyun,male,20180202,hr,hrcc,600,502,101),(harry,male,20180202,hr,NULL,6000,502,101),(emma,female,20180206,sale,salecc,20000,503,102),(christine,female,20180205,sale,salecc,2200,503,102),(zhuzhu,male,20180205,sale,NULL,2200,503,102),(gougou,male,20180205,sale,,2200,503,102);
mysql SELECT 字段名称,字段名称2 from 表名 条件
mysql SELECT column_name,column_2 from table WHERE ...
简单查询
mysql SELECT * from t3;
mysql SELECT name, salary, dep_id from employee5;
避免重复DISTINCTSELECT post FROM employee5;SELECT DISTINCT post FROM employee5;
通过四则运算查询运算mysqlSELECT 437.4384/5;mysqlSELECT 53;
SELECT name, salary, salary*14 FROM employee5;SELECT name, salary, salary*14 AS Annual_salary FROM employee5;SELECT name, salary, salary*14 Annual_salary FROM employee5;
定义显示格式CONCAT() 函数用于连接字符串SELECT CONCAT(name, annual salary: , salary*14) AS Annual_salary FROM employee5;
单条件查询mysql SELECT name from employee5 WHERE salary5000;
多条件查询mysql SELECT name from employee5 WHERE salary5000 and salary6000;
关键字BETWEEN ANDSELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
关键字IS NULLSELECT name,job_description FROM employee5 WHERE job_description IS NULL;
SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;SELECT name,job_description FROM employee5 WHERE job_description;NULL说明1、等价于没有任何值、是未知数。2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。3、对空值做加、减、乘、除等运算操作结果仍为空。4、比较时使用关键字用“is null”和“is not null”。5、排序时比其他数据都小所以NULL值总是排在最前。
关键字IN集合查询
SELECT name, salary FROM employee5 WHERE salary4000 OR salary5000 OR salary6000 OR salary9000;SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
排序查询
mysql SELECT name from employee5 order by name;
mysql SELECT name from employee5 order by name desc;
mysql SELECT name from employee5 order by name desc limit 3; //限制次数
mysql SELECT name from employee5 order by name desc limit 1,3;
mysql SELECT name from employee5 order by name desc limit 2,3;
注ascending 美音 /əsɛndɪŋ/ 升序descending 美音 /dɪsɛndɪŋ/ 降序
按多列排序:入职时间相同的人薪水不同SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;有差别于SELECT * from employee5 ORDER BY hire_date DESC; 先按入职时间再按薪水排序SELECT * from employee5 ORDER BY hire_date DESC,salary DESC;先按职位再按薪水排序SELECT * from employee5 ORDER BY post,salary DESC;限制查询的记录数SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默认初始位置为0 SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //从第4条开始共显示5条分组查询GROUP BY和GROUP_CONCAT()函数一起使用SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;模糊查询(通配符) % 所有字符mysql SELECT * from employee5 WHERE salary like %20%;正则查询SELECT * FROM employee5 WHERE salary regexp 72;SELECT * FROM employee5 WHERE name REGEXP ^ali;SELECT * FROM employee5 WHERE name REGEXP yun$;SELECT * FROM employee5 WHERE name REGEXP m{2};函数count()max()min()avg()database()user()now()sum()password()
SELECT COUNT(*) FROM employee5;SELECT COUNT(*) FROM employee5 WHERE dep_id101;SELECT MAX(salary) FROM employee5;SELECT MIN(salary) FROM employee5;SELECT AVG(salary) FROM employee5;SELECT SUM(salary) FROM employee5;SELECT SUM(salary) FROM employee5 WHERE dep_id101;
2.2、MySQL多表查询
多表查询 多表连接查询复合条件连接查询子查询
一、准备两张测试表
表company.employee6
mysql create table employee6(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int);
mysql desc employee6;
-----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------------
| emp_id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dept_id | int(11) | YES | | NULL | |
-----------------------------------------------------------
mysql insert into employee6(emp_name,age,dept_id) values
(tianyun,19,200),
(tom,26,201),
(jack,30,201),
(alice,24,202),
(robin,40,200),
(natasha,28,204);
mysql select * from employee6;
---------------------------------
| emp_id | emp_name | age | dept_id |
---------------------------------
| 1 | tianyun | 19 | 200 |
| 2 | tom | 26 | 201 |
| 3 | jack | 30 | 201 |
| 4 | alice | 24 | 202 |
| 5 | robin | 40 | 200 |
| 6 | natasha | 28 | 204 |
---------------------------------
表company.department6
mysql create table department6(
dept_id int,
dept_name varchar(100)
);
mysql desc department6;
----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(100) | YES | | NULL | |
----------------------------------------------------
mysql insert into department6 values
(200,hr),
(201,it),
(202,sale),
(203,fd);
mysql select * from department6;
--------------------
| dept_id | dept_name |
--------------------
| 200 | hr |
| 201 | it |
| 202 | sale |
| 203 | fd |
--------------------
注
Financial department:财务部门 fd
二、多表的连接查询
交叉连接 生成笛卡尔积它不使用任何匹配条件 自己了解就好这个生产用会把数据库跑死
内连接 只连接匹配的行
外连接了解左连接 会显示左边表内所有的值不论在右边表内匹不匹配右连接 会显示右边表内所有的值不论在左边表内匹不匹配
全外连接了解 包含左、右两个表的全部行
内连接
两种方式方式1使用where条件 方式2使用inner join
只找出有部门的员工 (部门表中没有natasha所在的部门)
mysql select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name
from employee6,department6
where employee6.dept_id department6.dept_id;
-----------------------------------
| emp_id | emp_name | age | dept_name |
-----------------------------------
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
-----------------------------------
使用别名select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a,department6 b where a.dept_id b.dept_id;
-----------------------------------
| emp_id | emp_name | age | dept_name |
-----------------------------------
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
-----------------------------------
使用inner joinselect a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join department6 b on a.dept_id b.dept_id;
-----------------------------------
| emp_id | emp_name | age | dept_name |
-----------------------------------
| 1 | tianyun | 19 | hr |
| 2 | tom | 26 | it |
| 3 | jack | 30 | it |
| 4 | alice | 24 | sale |
| 5 | robin | 40 | hr |
-----------------------------------
外连接语法
SELECT 字段列表FROM 表1 LEFT|RIGHT JOIN 表2ON 表1.字段 表2.字段;
外连接(左连接 left join)
mysql select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id department6.dept_id;
找出所有员工及所属的部门包括没有部门的员工
-----------------------------
| emp_id | emp_name | dept_name |
-----------------------------
| 1 | tianyun | hr |
| 5 | robin | hr |
| 2 | tom | it |
| 3 | jack | it |
| 4 | alice | sale |
| 6 | natasha | NULL |
-----------------------------
外连接(右连接right join)
mysql select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id department6.dept_id;
找出所有部门包含的员工包括空部门
-----------------------------
| emp_id | emp_name | dept_name |
-----------------------------
| 1 | tianyun | hr |
| 2 | tom | it |
| 3 | jack | it |
| 4 | alice | sale |
| 5 | robin | hr |
| NULL | NULL | fd |
-----------------------------
全外连接select * from employee6 full join department6;
-----------------------------------------------------
| emp_id | emp_name | age | dept_id | dept_id | dept_name |
-----------------------------------------------------
| 1 | tianyun | 19 | 200 | 200 | hr |
| 1 | tianyun | 19 | 200 | 201 | it |
| 1 | tianyun | 19 | 200 | 202 | sale |
| 1 | tianyun | 19 | 200 | 203 | fd |
| 2 | tom | 26 | 201 | 200 | hr |
| 2 | tom | 26 | 201 | 201 | it |
| 2 | tom | 26 | 201 | 202 | sale |
| 2 | tom | 26 | 201 | 203 | fd |
| 3 | jack | 30 | 201 | 200 | hr |
| 3 | jack | 30 | 201 | 201 | it |
| 3 | jack | 30 | 201 | 202 | sale |
| 3 | jack | 30 | 201 | 203 | fd |
| 4 | alice | 24 | 202 | 200 | hr |
| 4 | alice | 24 | 202 | 201 | it |
| 4 | alice | 24 | 202 | 202 | sale |
| 4 | alice | 24 | 202 | 203 | fd |
| 5 | robin | 40 | 200 | 200 | hr |
| 5 | robin | 40 | 200 | 201 | it |
| 5 | robin | 40 | 200 | 202 | sale |
| 5 | robin | 40 | 200 | 203 | fd |
| 6 | natasha | 28 | 204 | 200 | hr |
| 6 | natasha | 28 | 204 | 201 | it |
| 6 | natasha | 28 | 204 | 202 | sale |
| 6 | natasha | 28 | 204 | 203 | fd |
-----------------------------------------------------
三、复合条件连接查询
示例1以内连接的方式查询employee6和department6表并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
select emp_id,emp_name,age,dept_name FROM employee6,department6 WHERE employee6.dept_iddepartment6.dept_id AND age 25;
示例2以内连接的方式查询employee6和department6表并且以age字段的升序方式显示
SELECT emp_id,emp_name,age,dept_name FROM employee6,department6 where employee6.dept_iddepaartment6.dept_id ORDER BY age asc;
四、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果可以为外层查询语句提供查询条件。
子查询中可以包含IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符 、 !、 、等
1. 带IN关键字的子查询
查询employee表但dept_id必须在department表中出现过
SELECT * FROM employee6 WHERE dept_id IN (SELECT dept_id FROM department6);
2. 带比较运算符的子查询
、!、、、、、
查询年龄大于等于25岁员工所在部门查询老龄化的部门
SELECT dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERER age 25);
3. 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时内层查询语句不返回查询的记录而是返回一个真假值。
True或False当返回True时外层查询语句将进行查询当返回值为False时外层查询语句不进行查询
department表中存在dept_id203Ture
SELECT * from employee6 WHERE EXISTS (SELECT * FROM depratment6 WHERE dept_id203);
2.3、Mysql安全控制 确保MySQL运行用户为一般用户
# groupadd mysql
# useradd -M -s /sbin/nologin -g mysql mysql
# vim /etc/my.cnf
user mysql
#注意点:改变拥有者和所属组对于mysql的安装目录 建议修改默认端口3306,改为其他的一些端口
# vim /etc/my.cnf
port 3306 false
port 10086 true 开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点
# vim /etc/my.cnf
log_bin othername
4.删除空口令账号
#禁用匿名账号
# vim /etc/my.cnfskip-grant-tables 1. --改成 #skip-grant-tables 1
#删除空口令用户
mysql SELECT User,Host,authentication_string from mysql.user;
---------------------------------------------------------------------
| User | Host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
---------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql create user newrainlocalhost; #(这是在做实验)创建空口令账户
Query OK, 0 rows affected (0.00 sec)
mysql SELECT User,Host,authentication_string from mysql.user;
---------------------------------------------------------------------
| User | Host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| newrain | localhost | |
---------------------------------------------------------------------
4 rows in set (0.00 sec)
mysql drop user newrainlocalhost; #这是删除空口令账户
Query OK, 0 rows affected (0.01 sec)
mysql SELECT User,Host,authentication_string from mysql.user;
---------------------------------------------------------------------
| User | Host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
---------------------------------------------------------------------
3 rows in set (0.00 sec) 1. 禁止root账户远程访问(允许普通用户远程访问某个网段即可)
mysql SELECT User,Host,authentication_string from mysql.user;
---------------------------------------------------------------------
| User | Host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
---------------------------------------------------------------------
3 rows in set (0.00 sec)
mysql create user root10.0.11.% identified by 123;
Query OK, 0 rows affected (0.00 sec)
mysql SELECT User,Host,authentication_string from mysql.user;
---------------------------------------------------------------------
| User | Host | authentication_string |
---------------------------------------------------------------------
| root | localhost | *AFE7E33F3C21EAE1BF8231C3B09F9B0184A670F6 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root | 10.0.11.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
---------------------------------------------------------------------
4 rows in set (0.00 sec)
mysql drop user root10.0.11.%;
Query OK, 0 rows affected (0.00 sec) 2. 使用mysql的时候,经常会遇到MySQL: ERROR 1040: Too many connections这样的问题,一种是访问量确实很高, MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小, 这时就需要调整当前最大连接数
##设置最大连接数02
修改mysql配置文件my.cnf,在[mysqld]段中添加或修改max_connections值:
max_connections256
DCLData Control Language 数据库控制语言
用于数据库授权、角色控制等操作
GRANT 授权为用户赋予访问权限
REVOKE 取消授权撤回授权权限
用户管理
登录和退出MySQL
远程登陆
客户端语法mysql -u 用户名 -p 密码 -h ip地址 -P端口号:如果没有改端口号就不用-P指定端口
# mysql -h192.168.62.148 -P 3306 -uroot -p123
创建用户create user 用户名客户端来源IP地址 identified by 密码;mysql create user newrain192.168.62.% identified by 123;删除用户drop user 用户名客户端来源IP地址;mysql drop user newrain192.168.62.%;
修改用户rename user 用户名客户端来源IP地址 to 新用户名客户端来源IP地址 ;mysql rename user newrain192.168.62.% to ehome%;
修改密码// 第一种方法set password for 用户名IP地址Password(新密码)mysql set password for ehome%Password(123);// 第二种方法alter user 用户名客户端来源IP地址 identified by 新密码;
// 第三种方法(忘记密码时必须使用此方法修改密码)UPDATE mysql.user SET authentication_stringpassword(QFedu123!) WHERE userroot and hostlocalhost;root修改自己密码
# mysqladmin -uroot -p123 password new_password //123为旧密码
案例:
# mysqladmin -uroot -p123 password qf123;PS用户权限相关数据保存在mysql数据库的user表中所以也可以直接对其进行操作不建议
权限管理
grant 权限 on 数据库.表 to 用户客户端来源IP地址 identified by 密码; -- 授权并设置密码
revoke 权限 on 数据库.表 from 用户客户端来源IP地址 -- 取消权限
mysql grant all privileges on company.* to ehome%;
mysql revoke all privileges on company.* from ehome%;
mysql flush privileges; #关于权限方面的修改注意刷新权限否则有可能不生效
查看授权信息
查看授权语句
show grants for 用户客户端来源IP地址;
mysql show grants for ehome%;
-----------------------------------
| Grants for ehome% |
-----------------------------------
| GRANT USAGE ON *.* TO ehome% |
-----------------------------------
1 row in set (0.00 sec
查看生效的授权信息
针对所有库和表的权限比如 *.* 。 去 mysql.user 中查看
SELECT * from mysql.user WHERE userroot\G
mysql SELECT * from mysql.user WHERE userehome\G
*************************** 1. row ***************************Host: %User: ehomeSELECT_priv: NInsert_priv: NUpdate_priv: NDelete_priv: NCreate_priv: NDrop_priv: NReload_priv: NShutdown_priv: NProcess_priv: NFile_priv: NGrant_priv: NReferences_priv: NIndex_priv: NAlter_priv: NShow_db_priv: NSuper_priv: NCreate_tmp_table_priv: NLock_tables_priv: NExecute_priv: NRepl_slave_priv: NRepl_client_priv: NCreate_view_priv: NShow_view_priv: NCreate_routine_priv: NAlter_routine_priv: NCreate_user_priv: NEvent_priv: NTrigger_priv: N
Create_tablespace_priv: Nssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0max_updates: 0max_connections: 0max_user_connections: 0plugin: mysql_native_passwordauthentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257password_expired: Npassword_last_changed: 2019-08-20 19:35:41password_lifetime: NULLaccount_locked: N
1 row in set (0.00 sec)
针对具体到库的权限比如db_name.* 。 去 mysql.db 中查看
mysql SELECT * from mysql.db WHERE userehome\G
*************************** 1. row ***************************Host: %Db: companyUser: ehomeSELECT_priv: YInsert_priv: YUpdate_priv: YDelete_priv: YCreate_priv: YDrop_priv: YGrant_priv: NReferences_priv: YIndex_priv: YAlter_priv: Y
Create_tmp_table_priv: YLock_tables_priv: YCreate_view_priv: YShow_view_priv: YCreate_routine_priv: YAlter_routine_priv: YExecute_priv: YEvent_priv: YTrigger_priv: Y
1 row in set (0.00 sec)
假如是 MySQL8.x
CREATE USER 你的用户名localhost IDENTIFIED BY 你的密码;
#创建新的用户
GRANT ALL PRIVILEGES ON 你的数据库名.* TO 你的用户名localhost;
#把刚刚创建的数据库的管理权限给予刚刚创建的MySQL用户
FLUSH PRIVILEGES;
#刷新权限使用设置生效
Mysql调优策略(面试拓展)
1. 选择合适的存储引擎: InnoDB
2. 选取磁盘读写速度较快的设备作为Mysql服务器
3. 充分使用索引
4. 增加慢查询日志功能
5. 频繁访问的数据用缓存数据库解决
6. 单条查询最后增加 LIMIT 1停止全表扫描
7. 设置最大连接数
8. 设置引擎的读写速度
数值类型 TINYINT: 范围-128 到 127 有符号或 0 到 255 无符号 存储需求1 字节 SMALLINT: 范围-32768 到 32767 有符号或 0 到 65535 无符号 存储需求2 字节 MEDIUMINT: 范围-8388608 到 8388607 有符号或 0 到 16777215 无符号 存储需求3 字节 INT也称为INTEGER: 范围-2147483648 到 2147483647 有符号或 0 到 4294967295 无符号 存储需求4 字节 BIGINT: 范围-9223372036854775808 到 9223372036854775807 有符号或 0 到 18446744073709551615 无符号 存储需求8 字节 FLOAT: 单精度浮点数类型用于存储小数值范围为-3.402823466E38到-1.175494351E-38、0和1.175494351E-38到3.402823466E38。 存储需求为4字节。 DOUBLE: 双精度浮点数类型用于存储更大范围的小数值范围为-1.7976931348623157E308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E308。 存储需求为8字节。 DECIMAL(p, s): 定点数类型用于存储精确的小数值其中p表示总的位数s表示小数点后的位数。 存储需求为p/92字节。
CREATE TABLE ExampleInteger (tinyint_col TINYINT COMMENT 这是一个TINYINT字段,smallint_col SMALLINT COMMENT 这是一个SMALLINT字段,mediumint_col MEDIUMINT COMMENT 这是一个MEDIUMINT字段,int_col INT COMMENT 这是一个INT字段,bigint_col BIGINT COMMENT 这是一个BIGINT字段,float_col FLOAT COMMENT 这是一个FLOAT字段,double_col DOUBLE COMMENT 这是一个DOUBLE字段,decimal_col DECIMAL(10, 2) COMMENT 这是一个DECIMAL字段,
);
字符串类型 CHAR(n) 定长字符类型n 表示字符的最大长度范围为0到255默认为1。 存储需求为n字节如果字符长度小于n则在末尾填充空格以达到n长度。 VARCHAR(n) 变长字符类型n 表示字符的最大长度范围为0到65535默认为255。 存储需求取决于存储的实际字符数加上一到两个字节的长度前缀。 TEXT 变长字符类型用于存储较长的文本数据最大长度为65535个字符。 实际存储需求取决于存储的文本长度。 TINYTEXT 变长字符类型用于存储较短的文本数据最大长度为255个字符。 实际存储需求取决于存储的文本长度。 MEDIUMTEXT 变长字符类型用于存储中等长度的文本数据最大长度为16777215个字符。 实际存储需求取决于存储的文本长度。 LONGTEXT 变长字符类型用于存储较长的文本数据最大长度为4294967295个字符。 实际存储需求取决于存储的文本长度。
CREATE TABLE ExampleChar (char_col CHAR(10) COMMENT 这是一个CHAR字段,varchar_col VARCHAR(255) COMMENT 这是一个VARCHAR字段,text_col TEXT COMMENT 这是一个TEXT字段,tinytext_col TINYTEXT COMMENT 这是一个TINYTEXT字段,mediumtext_col MEDIUMTEXT COMMENT 这是一个MEDIUMTEXT字段,longtext_col LONGTEXT COMMENT 这是一个LONGTEXT字段
);
时间日期类型 DATE: 日期类型格式为 YYYY-MM-DD支持的范围是 1000-01-01 到 9999-12-31。 存储需求为3字节。 TIME: 时间类型格式为 HH:MM:SS支持的范围是 -838:59:59 到 838:59:59。 存储需求为3字节。 DATETIME: 日期时间类型格式为 YYYY-MM-DD HH:MM:SS支持的范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59。 存储需求为8字节。 TIMESTAMP: 时间戳类型格式为 YYYY-MM-DD HH:MM:SS支持的范围与DATETIME相同。 存储需求为4字节但有时会根据时区设置自动调整。 YEAR: 年份类型用于存储年份信息格式为 YYYY 或 YY范围为1901到2155默认显示格式为四位数字。 存储需求为1字节。
CREATE TABLE ExampleDateTime (date_col DATE COMMENT 这是一个DATE字段,time_col TIME COMMENT 这是一个TIME字段,datetime_col DATETIME COMMENT 这是一个DATETIME字段,timestamp_col TIMESTAMP COMMENT 这是一个TIMESTAMP字段,year_col YEAR COMMENT 这是一个YEAR字段
);
其他类型 ENUM: 枚举类型用于存储预定义的字符串值列表中的一个可以在创建表时指定枚举值。 存储需求为1或2字节取决于枚举值的数量。 SET: 集合类型用于存储预定义的字符串值集合中的一个或多个值可以在创建表时指定集合值。 存储需求为1、2、3、4或8字节取决于集合值的数量。 BOOLEAN: 布尔类型用于存储TRUE或FALSE的值。 存储需求为1字节。 CREATE TABLE ExampleOther (boolean_col BOOLEAN COMMENT 这是一个BOOLEAN字段,enum_col ENUM(value1, value2, value3) COMMENT 这是一个ENUM字段,set_col SET(option1, option2, option3) COMMENT 这是一个SET字段
); -- 约束
-- PRIMARY KEY 主键 auto_increment 自增键自增键必须建立在其他键上面 CREATE TABLE car ( id INT PRIMARY KEY auto_increment, brand varchar(20) NOT NULL COMMENT 汽车的品牌, type VARCHAR(20) NOT NULL COMMENT 汽车型号, money INT NOT NULL, idcard VARCHAR(20) not NULL UNIQUE KEY, birth_date DATETIME not NULL DEFAULT now(), INDEX ind_card(idcard) ); -- 测试插入数据库 INSERT INTO car (brand, type, money, idcard) VALUES (宝马,M3, 523455,12415vab234); INSERT INTO car (brand, type, money, idcard) VALUES (奥迪,A4, 323455,12aa15vab234),(宝马,M4, 523425,12415vab235),(法拉利,lf, 52345544,12415aab234),(兰博基尼,牛牛, 52345115,12415vafff4),(特斯拉,modely, 523455,1ff15vab234); -- 调试sql(扩展)
explain SELECT * FROM car WHERE idcard 1ff15vab234;
-- mysql 数据类型 -- int tinyint -- 数值类型指定长度不是写入长度而是显示长度 CREATE TABLE t1( date DATE, time TIME, datetime datetime, timestamp timestamp, year year ); INSERT INTO t1 VALUES(2024-10-15,22:10:15,2024-10-15 22:10:15, 2024-10-15 22:10:15, 75);
-- 字符串 -- char varchar CREATE TABLE t2( comment char(10), text varchar(20) ); INSERT into t2 VALUES(a ,a ); SELECT LENGTH(comment),length(text) from t2;
-- 字符串2 CREATE TABLE t3( a BINARY(4), b VARBINARY(4) );
INSERT INTO t3 VALUES(15,15);
-- 枚举和集合 -- enum set CREATE TABLE student ( name varchar(10), sex enum(男,女), object set(语文,数学,英语,政治,历史) ); INSERT into student VALUES (小明,男,语文,历史,英语);
