数据库 MySQL表操作

2023-12-21 06:39:34

数据库表操作

mysqlDDL操作

系统数据库
information_schema; 虚拟库,主要存储了系统中一些数据库对象信息。
performance_schema: 主要存储数据库的性能参数
mysql:授权库,主要存储系统用户的权限信息
sys:主要存储数据库服务器性能参数
(目标是把performance_schema的把复杂度降低)

创建数据库DDL

1. mysql -uroot -pQianfeng@123  -e "create database db2 default charset 'utf8'"
2. 直接去创建数据库目录并且修改权限
3. mysql>  create database school;

数据库命名规则:
    区分大小写
    唯一性
    不能使用关键字如 create SELECT
    不能单独使用数字
    不要使用中文

查看数据库
mysql> show databases;                   //查看所有数据库
mysql> show create database school;	 //查看创建的库信息
mysql> SELECT database();	     		//查看当前库

切换数据库
mysql> use school
mysql> show tables;

表的DDL操作:

 创建表 create table
    查看表结构 desc table_name,  show create table
    表完整性约束
    修改表 alter table
    复制表 create table ...
    删除表 drop table
    命令行操作数据库(脚本)
mysql>create database db2;
mysql>use db2
mysql>create table t1(name varchar(50),age int);

# mysql -u root -p1 -e "create database school"
# mysql -u root -p1 -e "use school;create table t2(name char(20),age int(3))"

在mysql客户端内执行系统命令
    mysql> system  ls
    mysql> \!  ls
	exit
	
语法:
create table 表名(
		字段名1  类型[(宽度) 约束条件],
		字段名2  类型[(宽度) 约束条件],
		字段名3  类型[(宽度) 约束条件]
)[存储引擎 字符集];
==在同一张表中,字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的
mysql> CREATE DATABASE school;
mysql> use school;
mysql> create table student1(
    -> id int,
    -> name varchar(50),                 
    -> sex enum('male','female'),
    -> 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) 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 id=6,math=65;

更新记录   
    mysql> update t1 set name="lili" WHERE id=5;

删除记录
    mysql> delete from  t1 WHERE id=6;
    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 5=4);
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 5=4);#将表的数据备份 不成立的
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 5=4);
mysql> create table t4(SELECT  id,name  from t3 WHERE 5=4);

复制记录
mysql> insert into t3 SELECT * from t10 WHERE id=9;  俩表格字段要相同

删除表
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 'male'
	age int unsigned NOT NULL default 20 		必须为正值(无符号) 不允许为空  默认是20

mysql数据类型

常见的数据类型

数值类型:
	    整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT	
	    整型可以指定是有符号的和无符号的,默认是有符号的
        可以通过UNSIGNED来说明某个字段是无符号的。
        
	    浮点数类型	     FLOAT DOUBLE
字符串类型:
	  CHAR系列  	CHAR  VARCHAR
      BINARY系列 	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(  );
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又称为精度和标度。

?oat表示的精度大约是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> create table t112(
    -> FROM1 double,
    -> FROM2 double(10,2)
    -> );
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)
注:?oat表示的精度大约是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()               |
+---------------------+
| 2023-08-09 17:33:11 |
+---------------------+
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                  |
+------------+----------+---------------------+
| 2023-08-09 | 17:33:47 | 2023-08-09 17:33:47 |
+------------+----------+---------------------+
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 c=10;
+-----------+
| length(c) |
+-----------+
|         3 |
+-----------+
1 row in set, 2 warnings (0.01 sec)

mysql> SELECT length(c) from binary_d WHERE c=10;
+-----------+
| 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
) ENGINE=MyISAM DEFAULT CHARSET=latin1

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 表2
					WHERE …;

例子:
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 id=8;		#修改全部
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 id=9 WHERE name="bob1";		指定条件
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 id=10,name="newrain" WHERE name="bob1";
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 name="jack";

delete from 表名; //删除表的全部数据
mysql> delete from student7;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * from student7;
Empty set (0.01 sec)

MySQL数据库查询操作

单表查询

字段解析字段名字段类型
雇员编号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;

避免重复DISTINCT
  SELECT post FROM employee5;
	SELECT DISTINCT post  FROM employee5;	

通过四则运算查询
    运算:
    mysql>SELECT 437.4384/5;
    mysql>SELECT 5>3;

    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 salary=5000;

多条件查询
    mysql> SELECT name from employee5 WHERE salary>5000 and salary<6000;


关键字BETWEEN AND
	    SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
	    SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

关键字IS NULL
	    SELECT 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 salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
	
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+'; 722222 7222222222
    SELECT * FROM employee5 WHERE name REGEXP '^ali';
    SELECT * FROM employee5 WHERE name REGEXP 'yun$';
    SELECT * FROM employee5 WHERE name REGEXP 'm{1,8}';
    
    
函数
    count()
    max()
    min()
    avg()
    database()
    user()
    now()
    sum()
    password()


	SELECT COUNT(*) FROM employee5;
	SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
	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_id=101; 
    

Mysql安全控制

  1. 确保MySQL运行用户为一般用户

     groupadd mysql
    # useradd -M -s /sbin/nologin -g mysql mysql 
    
    # vim /etc/my.cnf
    user = mysql
    
    #注意点:
    	改变拥有者和所属组对于mysql的安装目录
    
    
    
  2. 建议修改默认端口3306,改为其他的一些端口

    # vim /etc/my.cnf
    port = 3306 false
    port = 10086 true
    
    
    
  3. 开启mysql二进制日志,在误删除数据的情况下,可以通过二进制日志恢复到某个时间点

    # vim /etc/my.cnf
    log_bin = othername
    

    4.删除空口令账号

    #禁用匿名账号
    # vim /etc/my.cnf
    	skip-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 'newrain'@'localhost';	#(这是在做实验)创建空口令账户
    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 'newrain'@'localhost';	#这是删除空口令账户
    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 'root'@'10.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 'root'@'10.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_connections=256
    
    
    

DCL(Data Control Language 数据库控制语言)

用于数据库授权、角色控制等操作

GRANT 授权,为用户赋予访问权限

REVOKE 取消授权,撤回授权权限

用户管理
登录和退出MySQL
远程登陆:
客户端语法:mysql  -u用户名  -p密码  -hip地址   -P端口号:如果没有改端口号就不用-P指定端口
# mysql -h192.168.62.148 -P 3306 -uroot -p123

创建用户
    create user '用户名'@'客户端来源IP地址' identified by '密码';
    mysql> create user newrain@'192.168.62.%' identified by '123';
    
删除用户
    drop user '用户名'@'客户端来源IP地址';
    mysql> drop user newrain@'192.168.62.%';

修改用户
    rename user '用户名'@'客户端来源IP地址' to '新用户名'@'客户端来源IP地址' ;
    mysql> rename user newrain@'192.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_string=password('QFedu123!') WHERE user='root' and host='localhost';
    
===root修改自己密码
# mysqladmin -uroot -p'123' password 'new_password'	    //123为旧密码
案例:
# mysqladmin -uroot -p'123' password 'qf@123';
  
PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
权限管理
grant  权限 on 数据库.表  to  '用户'@'客户端来源IP地址' identified by '密码';   -- 授权并设置密码
revoke 权限 on 数据库.表 from '用户'@'客户端来源IP地址'    -- 取消权限
grant select on Company.employee to 'ehome'@'%' identified by 'Qianfeng@123'
revoke select on Company.employee from 'ehome'@'%'
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 user='root'\G

mysql> SELECT * from mysql.user WHERE user='ehome'\G
*************************** 1. row ***************************
                  Host: %
                  User: ehome
           SELECT_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2019-08-20 19:35:41
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

针对具体到库的权限,比如db_name.* 。 去 mysql.db 中查看

mysql> SELECT * from mysql.db  WHERE user='ehome'\G
*************************** 1. row ***************************
                 Host: %
                   Db: company
                 User: ehome
          SELECT_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_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;
#刷新权限,使用设置生效

文章来源:https://blog.csdn.net/2301_78195109/article/details/135099618
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。