mysq数据库相关操作

通过my.ini配置文件修改字符集:客户端字符集设置:[mysql]default-character-set=utf8
[mysqld] character-set-server=utf8
。设置之后保存,在重启mysql服务。登录mysql:mysql -uroot -p (-u用户名
-p密码 -h服务器名称(ip地址) -P端口号(3306)-D打开指定数据库
–prompt=name指定命令提示符(只针对当前连接有效)
–delimiter=name指定分隔符
-V输出版本星系)再输入密码。退出:exit,quit,\q 。

(一)常用SQL语句

得到mysql当前版本:select
version(); 显示当前日期时间:select now(); 显示当前用户:select
user();

1.SELECT USER()  得到登陆的用户

mysql语句的编码规范:关键字与函数名称全部大写;数据库名称,表名称,字段名称等全部小写;sql语句必须以分隔符结尾;sql语句支持折行操作,只要不把单词、标记和引号字符串分割成两部分,可以在下一行继续写;数据库名称、表名称、字段名称尽量不要用mysql的保留字,如果需要使用的时候需要使用反引号(“)将名称括起来。

2.SELECT VERSION()  得到mysql的版本信息

在命令行调出之前写过的命令,通过上下箭头键。

3.SELECT NOW()  得到当前的时间

开启mysql的输出日志:\T
后跟路径;\t 结束输出日志。

4.SELECT DATABASE()  得到打开的数据库名字

===================================数据库相关操作:

(二)数据库相关操作

创建数据库:(创建一个数据库相当于在磁盘总创建了一个目录)
CREATE
{DATABASE | SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER
SET[=]charset_name]

1.创建数据库(名称不要包含特殊字符或者是MySQL关键字)

查看上一步操作产生的警告:SHOW
WARNINGS;

(1)CREATE {DATABASE|SCHEMA} db_name;

查看当前数据库已有数据库:SHOW
DATABASES;

如:CREATE DATABASE dou

查看指定数据库的定义:SHOW
CREATE {DATABASE} db_name;

(2)CREATE {DATABASE|SCHEMA} db_name [IF NOT EXISTS ] db_name  
检测数据库名称是否存在,不存在则创建

修改指定数据库的编码方式:ALTER
{DATABASE} db_name [DEFAULT] CHARACTER SET [=]
charset_name;

如:CREATE DATABASE IF NOT EXISTS dou;

打开指定数据库:USE
db_name;打开数据库后就可以在指定数据库里面建表等操作。
得到当前打开的数据库名称:SELECT
DATABASE();

(3)CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET
[=] charset;  在创建数据库的同时指定编码方式

删除指定的数据库:DROP
{DATABASE} [IF EXISTS] db_name;

如:CREATE DATABASE IF NOT EXISTS dou DEFAULT CHARACTER SET ‘GBK’;

 

 2.查看当前服务器下全部数据库

====================数据表的操作

SHOW DATABASES|SCHEMAS;(注意s)

 

3.查看指定数据库的详细信息

先有表结构,再有数据,数据表名称要求唯一,而且不要包含特殊字符。数据表由行row和列column构成的二维网络。

SHOW CREATE DATABASE dou;

 

4.修改指定数据库的编码方式

MySQL中的数据类型:整数类型,浮点类型,字符串类型,日期时间类型,二进制类型。

ALTER DATABASE dou DEFAULT CHARACTER SET ‘UTF8’;

 

5.打开指定数据库

1、整数类型:TINYINT(-128~127,0~255,1字节),SMALLINT(-32768~32768,0~65535,2字节),MEDIUMINT,INT(2的32次方,0~4294967295,4字节),BIGINT(8字节),BOOL,BOOLEAN(TINYINT(1),O为false,其余为true,1字节)

USE dou

 

6.得到当前打开的数据库

MySQL帮助手册:HELP(? \h
) + (类型名称等其他命令):HELP CREATE TABLE;HELP CREATE
DATABASE;

SELECT DATABASE()|SCHEMA();(注意小括号)

 

7.删除指定数据库

2、浮点类型:FLOUT[(M,D)]:占4个字节,M是数字总位数,D是小数点后面的位数,如果M和D被省略,根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。
DOUBLE[M,D]:占8个字节,双精度浮点数。
DECIMAL[(M,D)]:定点数,和DOUBLE一样,但在内部以字符串形式存储数值,所以精度会更高,不会进行四舍五入。

DROP DATABASE dou;

 

DROP DATABASE IF EXIST dou;如果存在则删除

3、字符串类型:CHAR(M):定长字符串,M代表存储的字符数,M个字节,0<=M<=255;
VARCHAR(M):变长字符串,M个字节,L+1个字节,其中 L<=M
并且0<=M<=65535;TINYTEXT;TEXT:用于存储大量的文本字符串,MEDIUMTEXT;LONGTEXT;ENUM(‘value1′,’value2’,…):1个或2个字节,取决于枚举值的个数(最多65535个值),当我们的值可以被列举出来的时候选用枚举类型,枚举类型的值只能从列举出的值中选一个;SET(‘value1′,’value2’,…):集合,把值保存到集合里,集合里最多可以保存64个成员值,和枚举不同的是SET集合可以选择多个值。

(三)数据表相关操作

 

1.创建表

4、日期时间类型:TIME存储时间;DATE存储日期;DATETIME存储日期时间;TIMESTAMP存储时间戳;YEAR存储年份,占用1个字节大小;一般都是通过整型来保存时间戳,这样方便计算。

CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],

)ENGINE=存储引擎 CHARSET=编码方式;

 

如:

5、二进制类型:主要是用来保存二进制数据,用的少,一般视频和图片都以路径的形式加以保存。

CREATE TABLE IF NOT EXISTS dou(
id INT PRIMARY KEY,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM(‘男’,’女’,’保密’)
)ENGINE=INNODB CHARSET=UTF8;

 

完整性约束:

====================数据表的存储引擎
存储引擎就是表的类型。数据库的存储类型决定了表在计算机中的存储方式,用户可以根据不同的存储方式、是否进行事务处理等来选择合适的存储引擎。Mysql常用的存储引擎:InnoDB存储引擎(支持事务,回滚。并发控制,支持外键约束,读写效率低,占用空间大。默认引擎),MyISAM存储引擎(不支持事务),MEMORY存储引擎(存放在内存中,处理速度快,较少使用)。查看MySQL支持的存储引擎:SHOW
ENGINES;查看显示支持的存储引擎信息:SHOW VARIABLES LIKE
‘have%’;查看默认的存储引擎:SHOW VARIABLES LIKE ‘storage_engine’;

①UNSIGNED 无符号,没有负数,从0开始

====================数据表的创建
MySQL注释:#;–;
字段注释:COMMENT

②NOT NULL 非空约束,值不能为NULL,可以不填,不填即为空白

–创建maizi数据库
CREATE DATABASE IF NOT EXISTS `maizi` DEFAULT CHARACTER SET ‘UTF8’;

③DEFAULT 默认值 如果插入的时候没有给字段赋值则使用默认值

USE `maizi`;

  如:age TINYINT UNSIGNED DEFAULT 18

CREATE TABLE IF NOT EXISTS `user`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM(‘男’,’女’,’保密’),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1)
)ENGINE=INNODB CHARSET=UTF8;

④PRIMARY KEY 主键,值不能重复,一个表只能有一个主键,字段不能为空;

注意:当需要输入中文的时候,需要临时转换客户端的编码方式:SET NAMES
GBK;这个转换只针对当前连接有效,而且是在输入以上命令以后有效。

但是一个表里可以有复合主键

建表时多个字段间,以逗号分隔。

如:

查看当前数据库下的数据表:SHOW
TABLES;(创建一个表相当于在磁盘上创建了一个文件。)

— 复合主键
CREATE TABLE test_primary_key(
id INT UNSIGNED,
courseId VARCHAR(20),
username VARCHAR(20),
email VARCHAR(50),
PRIMARY KEY(id,courseId)
);

–课程表
CREATE TABLE IF NOT EXISTS course(
cid TINYINT,
courseName VARCHAR(50),
courseDesc VARCHAR(200)
);

此时只有当id和courseid同时和前面的相同时,才会出错,如果仅有一个重复也是可以正常添加的

–创建新闻分类表cms_cate
CREATE TABLE IF NOT EXISTS cms_cate(
id TINYINT,
cateName VARCHAR(50),
cateDesc VARCHAR(200)
)ENGINE=MyISAM CHARSET=UTF8;

⑤AUTO_INCREMENT
自动增长,只能用于数值列,而且配合索引使用,默认初始值为1,每次增长1

====================下个视频:7

如:

 

CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20)
);

INSERT test_auto_increment(username) VALUES(‘A’);
INSERT test_auto_increment(username) VALUES(‘B’);

INSERT test_auto_increment(id,username) VALUES(NULL,’E’);
INSERT test_auto_increment(id,username) VALUES(DEFAULT,’F’);
INSERT test_auto_increment(id,username) VALUES(15,’G’);

INSERT test_auto_increment(username) VALUES(‘C’);

此时最后一次的id为16

⑥UNIQUE KEY 唯一性,同样的值不能重复,但是NUL除外

⑦ZEROFILL
零填充,当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

如:

CREATE TABLE zero(
id INT(4) ZEROFILL
);

INSERT zero VALUES(1);

自动前面补零即 0001

⑧FOREIGN KEY 外键约束

2.查看当前数据库下已有数据表

SHOW TABLES;

3.查看指定数据表的详细信息

SHOW CREATE TABLE test_auto_increment

4.查看表结构

DESC|DESCRIBE test_auto_increment

SHOW COLUMNS FROM test_auto_increment

5.删除指定数据表

DROP TABLE [IF EXISTS] test_auto_increment

(四)表结构相关操作

①添加字段

ALTER TABLE tbl_name
ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]

如:

— 添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE
,并将email添加到username后面
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

②删除字段

— 删除test字段
ALTER TABLE user1
DROP username;

③修改字段类型、字段属性

ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]

如:

– 将email字段改为VARCHAR(50) NOT NULL FIRST

ALTER TABLE user1
MODIFY email VARCHAR(50) NOT NULL FIRST;

④修改字段名称、字段类型、字段属性

ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER
字段名称]

如:

— 将password 名称改为pwd
ALTER TABLE user1
CHANGE password pwd CHAR(40) NOT NULL;

⑤添加主键

ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称)

⑥添加唯一

ALTER TABLE tbl_name
ADD UNIQUE KEY(字段名称)

⑦删除唯一

ALTER TABLE tbl_name
DROP (字段名称)

⑧修改数据表名称

ALTER TABLE tbl_name

RENAME new_tbl_name

或者直接 RENAME TABLE tbl_name TO new_tbl_name