MYSQL初级学习笔记二：数据表相关操作及MySQL存储引擎！（视频序号：初级_5,7-22|6)

知识点三：数据表相关操作(5,7-22)

  1 --------------------------------整型---------------------------------
  2 --测试整型
  3 CREATE TABLE test1(
  4 num1 TINYINT,
  5 num2 SMALLINT,
  6 num3 MEDIUMINT,
  7 num4 INT,
  8 num5 BIGINT
  9 );
 10 
 11 --向表中插入记录 INSERT tbl_name VALUE|VALUES(值,...);
 12 INSERT test1 VALUES(-128,-32768,-8388608,-2147483648,-9223372036854775808);
 13 
 14 --查询表中所有记录：SELECT * FROM tbl_name;
 15 SELECT * FROM test1;
 16 
 17 
 18 --无符号的整数值：UNSIGNED
 19 
 20 CREATE TABLE test2(
 21 num1 TINYINT UNSIGNED,
 22 num2 TINYINT
 23 );
 24 
 25 --插入值
 26 INSERT test2 VALUES(0,-12);
 27 
 28 
 29 --显示长度
 30  --零填充 ZEROFILL
 31 
 32 CREATE TABLE test3(
 33 num1 TINYINT ZEROFILL,
 34 num2 SMALLINT ZEROFILL,
 35 num3 MEDIUMINT ZEROFILL,
 36 num4 INT ZEROFILL,
 37 num5 BIGINT ZEROFILL
 38 );
 39 
 40 INSERT test3 VALUES(1,1,1,1,1);
 41 
 42 
 43 
 44 --------------------------------浮点类型---------------------------------
 45 
 46 CREATE TABLE test4(
 47 num1 FLOAT(6,2),
 48 num2 DOUBLE(6,2),
 49 num3 DECIMAL(6,2) --精度高
 50 );
 51 
 52 INSERT test4 VALUES(3.1415,3.1415,3.1416);
 53 
 54 INSERT test4 VALUES(3.1495,3.1495,3.1496);
 55 
 56 
 57 --------------------------------字符串类型---------------------------------
 58 
 59 --测试CHAR和VARCHAR
 60 CREATE TABLE test5(
 61 str1 CHAR(5),
 62 str2 VARCHAR(5)
 63 );
 64 INSERT test5 VALUES('1','1');
 65 INSERT test5 VALUES('12345','12345');
 66 INSERT test5 VALUES('123456','12345');
 67 INSERT test5 VALUES('','');
 68 INSERT test5 VALUES('1    ','1    ');
 69 INSERT test5 VALUES('123456','123456');
 70 INSERT test5 VALUES('  a','  a');
 71 INSERT test5 VALUES('啊','啊');
 72 INSERT test5 VALUES('啊啊啊啊啊','麦子学院好');
 73 --查看数据表，并在每行后面加’-‘
 74 SELECT CONCAT(str1,'_'),CONCAT(str2,'_') FROM test5;
 75 SELECT CONCAT('_',str1),CONCAT('_',str2) FROM test5;
 76 
 77 
 78 
 79 CREATE TABLE test6(
 80 str1 TEXT
 81 );
 82 INSERT test6 VALUES('JSDHAJSDAJSHDJKASDJ手机卡觉得凯撒多久啊是多久啊是的可视角度看啦');
 83 
 84 
 85 
 86 
 87 --------------------------------枚举类型---------------------------------
 88 
 89 
 90 --测试枚举类型
 91 CREATE TABLE test7(
 92 sex ENUM('男','女','保密')
 93 );
 94 
 95 INSERT test7 VALUES('男');
 96 INSERT test7 VALUES('女');
 97 INSERT test7 VALUES('保密');
 98 
 99 INSERT test7 VALUES(2);
100 
101 
102 --------------------------------集合类型---------------------------------
103 
104 --测试集合类型
105 CREATE TABLE test8(
106 FAV SET('A','B','C','D')
107 );
108 
109 INSERT test8 VALUES('A,C,D');
110 INSERT test8 VALUES('B,B,A');
111 INSERT test8 VALUES(3);
112 INSERT test8 VALUES(315);
113 
114 --------------------------------日期时间类型---------------------------------
115 
116 --测试YEAR类型
117 CREATE TABLE test9(
118 birth YEAR
119 );
120 INSERT test9 VALUES(1901);
121 INSERT test9 VALUES(2155);
122 
123 --测试TIME类型
124 CREATE TABLE test10(
125 test TIME
126 );
127 
128 INSERT test10 VALUES('1 12:12:12');
129 INSERT test10 VALUES('12:12');
130 INSERT test10 VALUES('1234');
131 
132 
133 --测试DATE类型
134 CREATE TABLE test11(
135 test DATE
136 );
137 
138 INSERT test11 VALUES('12-6-7');
139 INSERT test11 VALUES('12/6/7');
140 INSERT test11 VALUES('120607');
测试字段类型

如何创建数据表：

　　创建数据表：CREATE TAB  LE [IF NOT EXISTS] btl_name(字段名称 字段类型[完整性约束体条件]…) ENGINT=引擎名称 CHARSET=’编码方式’

 1 --创建学院表(user)
 2 --编号 id
 3 --用户名 username
 4 --年龄 age
 5 --性别 sex
 6 --邮箱 email
 7 --地址 addr
 8 --生日 birth
 9 --薪水 salary
10 --电话 tel
11 --是否结婚 married
12 --注意：当需要输入中文的时候，需要临时转换客户端的编码方式
13 --SET NAMES GBK;
14 --字段注释，通过COMMENT 注释内容，给字段添加注释
15 CREATE TABLE IF NOT EXISTS `user`(
16 id SMALLINT,
17 username VARCHAR(20),
18 age TINYINT,
19 sex ENUM('男','女','保密'),
20 email VARCHAR(50),
21 addr VARCHAR(200),
22 birth YEAR,
23 salary FLOAT(8.2),
24 tel INT,
25 --married TINYINT(1) COMMENT '0代表未结婚，非0代表结婚'
26 )ENGINE=INNODB CHARSET=UTF8;
27 
28 
29 
30 --创建课程表 course
31 --编号 cid
32 --课程名称 courseNmame
33 --课程描述 courseDesc
34 CREATE TABLE IF NOT EXISTS course(
35 cid TINYINT,
36 courseName VARCHAR(50),
37 courseDesc VARCHAR(200)
38 );
39 
40 
41 --创建新闻分类表cms_cate
42 --编号，分类名称，分类描述
43 CREATE TABLE IF NOT EXISTS cms_cate(
44 id TINYINT,
45 cateName VARCHAR(50),
46 cateDase VARCHAR(200)
47 )ENGINE=MyISAM CHARSET=UTF8;
48 
49 --创建新闻表cms_news
50 --编号，新闻标题，新闻内容，新闻发布时间，点击量，是否置顶，//新闻所属分类，发布人
51 CREATE TABLE IF NOT EXISTS cms_news(
52 id INT,
53 title VARCHAR(50),
54 content TEXT,
55 pubTime INT,
56 clickNum INT,
57 isTop TINYINT(1) COMMENT '0代表不置顶，1代表置顶'
58 );
练习

    完整性约束条件：

　　　PRIMARY KEY 主键

 1 --测试主键（单字段）
 2 CREATE TABLE IF NOT EXISTS user1(
 3 id INT PRIMARY KEY,
 4 username VARCHAR(20)
 5 );
 6 INSERT user1 VALUES(1,'king');
 7 INSERT user1 VALUES(13,'QUEEN');
 8 
 9 --测试主键（多字段）(复合主键）
10 CREATE TABLE IF NOT EXISTS user2(
11 id INT,
12 username VARCHAR(20),
13 card CHAR(18),
14 PRIMARY KEY(id,card)
15 );
16 INSERT user2 VALUES(1,'king','1111');
17 INSERT user2 VALUES(1,'queen','1112');
18 
19 
20 --查看创建表的定义(详细信息）
21 SHOW CREATE TABLE dbl_name;
测试主键

         AUTO_INCREMENT 自增长

 1 --测试自增长（配合主键使用，是主键不一定自增长，是自增长一定是主键）
 2 CREATE TABLE IF NOT EXISTS user5(
 3 id SMALLINT KEY AUTO_INCREMENT,
 4 username VARCHAR(20)
 5 );
 6 INSERT user5 VALUES(1,'king');
 7 INSERT user5(username) VALUES('queen');
 8 INSERT user5 VALUES(111,'king1');
 9 INSERT user5(username) VALUES('queen1');
10 INSERT user5 VALUES(NULL,'AAA');
11 INSERT user5 VALUES(DEFAULT,'AAA');
12 
13 
14 --建表时定义自增长起始值
15 CREATE TABLE IF NOT EXISTS user6(
16 id SMALLINT KEY AUTO_INCREMENT,
17 username VARCHAR(20)
18 )AUTO_INCREMENT=100;
19 
20 INSERT user6(username) VALUES('queen1');
21 
22 --修改自增长的值
23 ALTER TABLE user6 AUTO_INCREMENT=500;
24 INSERT user6(username) VALUES('queen2');
测试自增长

         FOREIGN KEY 外键（后面单独讲）

         NOT NULL 非空

1 --测试非空（NOT NULL)
2 CREATE TABLE IF NOT EXISTS user7(
3 id INT UNSIGNED KEY AUTO_INCREMENT,
4 username VARCHAR(20) NOT NULL,
5 passwd CHAR(32) NOT NULL,
6 age TINYINT UNSIGNED
7 );
8 INSERT user7(username,passwd) VALUES('KING','KING');
9 INSERT user7(username,passwd,age) VALUES('KING1','KING1',12);
测试非空

         UNSIGNED 无符号

         UNIQUE KEY 唯一

 1 --测试唯一性约束（UNIQUE KEY)
 2 CREATE TABLE IF NOT EXISTS user9(
 3 id TINYINT UNSIGNED KEY AUTO_INCREMENT,
 4 username VARCHAR(20) NOT NULL UNIQUE,
 5 card CHAR(18) UNIQUE
 6 );
 7 INSERT user9(username) VALUES('A');
 8 INSERT user9(username,card) VALUES('B','111');
 9 INSERT user9(username,card) VALUES('B1',NULL);
10 INSERT user9(username,card) VALUES('B2',NULL);
测试唯一性约束

         DEFAULT 默认值

 1 --测试默认值（DEFAULT)
 2 CREATE TABLE IF NOT EXISTS user8(
 3 id INT UNSIGNED KEY AUTO_INCREMENT,
 4 username VARCHAR(20) NOT NULL,
 5 passwd CHAR(32) NOT NULL,
 6 age TINYINT UNSIGNED DEFAULT 18,
 7 addr VARCHAR(50) NOT NULL DEFAULT '北京',
 8 sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
 9 );
10 INSERT user8(username,passwd) VALUES('KING','KING');
11 INSERT user8 VALUES(2,'QUEEN','QUEEN',22,'上海','保密');
12 INSERT user8 VALUES(DEFAULT,'QUEEN','QUEEN',DEFAULT,DEFAULT,'保密');
测试默认值

 

注意：当需要输入中文的时候，需要临时转换客户端的编码方式：SET NAMES GBK;查看是否成功：“\s”;

总结：

--------------------------------总结---------------------------------
--创建表的语法规则
--CREATE TABLE [IF NOT EXISTS] tbl_name(
--字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY|UNIQUE[KEY]] [AUTO_INCREMENT]
--)ENGINE=INNODB(选择存储引擎名称) CHARSET=utf8(选择编码方式) AUTO_INCREMENT=100(指定自增长值，表中有字段时自增长的);

 

查看数据库中的数据表以及表结构：

　　查看数据库下的数据表：SHOW TABLES;

　　查看指定表的表结构：

　　　　DESC tbl_name;

             DESCRIBE tbl_name;

             SHOW COLUMNS FROM tbl_name;

　　向表中插入记录：INSERT tbl_name VALUE|VALUES(值,…);

　　查询表中所有的记录：SELECT * FROM tbl_name;

　　创建无符号的：UNSIGNED

　　在表中显示长度（显示长度，不够用零填充）：ZEROFILL

　　查看创建表的标的定义(详细信息)：SHOW CREATE TABLE tbl_name;

 

修改表结构：

 1 --创建用户表
 2 CREATE TABLE IF NOT EXISTS user10(
 3 id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
 4 username VARCHAR(20) NOT NULL UNIQUE,
 5 password CHAR(32) NOT NULL,
 6 email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
 7 age TINYINT UNSIGNED DEFAULT 18,
 8 sex ENUM('男','女','保密') DEFAULT '保密',
 9 addr VARCHAR(200) NOT NULL DEFAULT '北京',
10 salary FLOAT(6.2),
11 regTime INT UNSIGNED,
12 face CHAR(100) NOT NULL DEFAULT 'default.jpg'
13 );
默认数据

    修改表名 ：

        (1):ATLER TABLE tbl_name RENAME [TO|AS] new_name;

        (2):RENAME TABLE tbl_name TO new_name;

1 --修改表名测试
2 --方法一
3 ALTER TABLE user10 RENAME TO user11;--将user10表名修改为user11
4 ALTER TABLE user11  RENAME AS user10;--将user11表名修改为user10
5 ALTER TABLE user10  RENAME user11;--将user10表名修改为user11
6 --方法二
7 RENAME TABLE user11 TO user10;--将user11表名修改为user10
测试修改表名

    添加字段：

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

 1 --给user10 添加card字段：CHAR(18)
 2 ALTER TABLE user10 ADD card CHAR(18);    --默认添加在末列
 3 --给user10表添加test1字段，非空且唯一
 4 ALTER TABLE user10  ADD test1 VARCHAR(100) NOT NULL UNIQUE;        --添加在末列
 5 --给user10表添加test2字段，非空
 6 ALTER TABLE user10  ADD test2 VARCHAR(20) NOT NULL FIRST;    --添加在首列
 7 --给user10表添加test1字段，非空，默认值为10
 8 ALTER TABLE user10  ADD test3 INT NOT NULL DEFAULT 100 AFTER username;    --添加在usename字段之后
 9 
10 
11 --选中一次表，完成多次操作
12 ALTER TABLE user10
13 ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
14 ADD test5 FLOAT(6,2) FIRST,
15 ADD test6 SET('A','B','C');
测试添加字段

    删除字段：

        ALTER TABLE tbl_name DROP 字段名称；

 1 --删除user10中的test6字段
 2 ALTER TABLE user10 DROP test6;    --删除test6字段
 3 ALTER TABLE user10 DROP test1;    --删除test1字段
 4 
 5 --选中一次表，完成多个字段
 6 ALTER TABLE user10
 7 DROP test2,--删除test2字段
 8 DROP test3,--删除test3字段
 9 DROP test4,--删除test4字段
10 DROP test5;--删除test5字段
11 
12 --添加test字段删除addr字段
13 ALTER TABLE user10
14 ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
15 DROP addr;
测试删除字段

    修改字段属性（还可以修改字段位置）：

        ALTER　TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称];

 1 --MODIFY测试
 2 --将user10 中email 改为VARCHAR(200)
 3 ALTER TABLE user10 MODIFY email VARCHAR(200);
 4 ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '1785143516@qq.com';
 5 
 6 
 7 --将card字段移动到test字段后
 8 ALTER TABLE user10 MODIFY card CHAR(10) AFTER test;
 9 
10 
11 --将test字段修改为varchar(32) NOT NULL DEFAULT '123' 移动到第一个位置上;
12 
13 ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;
MODIFY测试

    修改字段名称和属性（还可以修改字段位置）：

        ALER　TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|AFTER 字段名称];

1 --CHANGE测试
2 ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
3 
4 ALTER TABLE user10 CHANGE test1 test CHAR(200) NOT NULL DEFAULT '123' AFTER username;
5 
6 ALTER TABLE user10 CHANGE test test INT;
CHANGE测试

    添加默认值：

        ALTER TABLE tbl_name ALTER 字段名 SET DEFAULT 默认值；

 1 --添加默认值测试
 2 CREATE TABLE IF NOT EXISTS user11(
 3 id TINYINT UNSIGNED KEY AUTO_INCREMENT,
 4 username VARCHAR(20) NOT NULL UNIQUE,
 5 age TINYINT UNSIGNED
 6 );
 7 
 8 ALTER TABLE user11 ALTER age SET DEFAULT 18;
 9 ALTER TABLE user11 ADD email VARCHAR(50);
10 ALTER TABLE user11 ALTER email SET DEFAULT '382771946@qq.com';
添加默认值测试

    删除默认值：

        ALTER TABLE tbl_name ALTER 字段名 DROP DEFAULT；

1 --删除默认值测试
2 ALTER TABLE user11 ALTER age DROP DEFAULT;
3 ALTER TABLE user11 ALTER email DROP DEFAULT;
删除默认值测试

    添加主键：

        ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY[index_type](字段名称，…)

 1 --添加主键测试
 2 CREATE TABLE IF NOT EXISTS test12(
 3 id INT 
 4 );
 5 ALTER TABLE test12 ADD PRIMARY KEY(id);
 6 
 7 --添加多字段主键（复合主键）
 8 CREATE TABLE IF NOT EXISTS test13(
 9 id INT,
10 card CHAR(18),
11 username VARCHAR(20) NOT NULL
12 );
13 ALTER TABLE test13 ADD PRIMARY KEY(id,card);
添加主键测试

    删除主键：

        ALTER TABLE tbl_name DROP PRIMARY KEY;

 1 --删除主键测试
 2 ALTER TABLE test12 DROP PRIMARY KEY;
 3 ALTER TABLE test13 DROP PRIMARY KEY;
 4 ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);
 5 
 6 --易错点
 7 CREATE TABLE IF NOT EXISTS test14(
 8 id INT  UNSIGNED KEY AUTO_INCREMENT
 9 );
10 ALTER TABLE test14 DROP PRIMARY KEY;--(报错)
11 --正确做法，先去掉自增长然后去主键
12 ALTER TABLE test14 MODIFY id int UNSIGNED;
13 ALTER TABLE test14 DROP PRIMARY KEY;
删除主键测试

    添加唯一：

        ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY][索引名称][字段名称，…]

 1 --添加唯一索引测试
 2 CREATE TABLE IF NOT EXISTS user12(
 3 id TINYINT UNSIGNED KEY AUTO_INCREMENT,
 4 username VARCHAR(20) NOT NULL,
 5 card CHAR(18) NOT NULL,
 6 test VARCHAR(20) NOT NULL,
 7 test1 VARCHAR(32) NOT NULL
 8 );
 9 
10 ALTER TABLE user12 ADD UNIQUE(username);
11 ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card (card);
12 ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY mul_test_test1 (test,test1);
添加唯一测试

    删除唯一：

        ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

1 --删除唯一索引
2 ALTER TABLE user12 DROP INDEX username;
3 ALTER TABLE user12 DROP INDEX KEY uni_card;
4 ALTER TABLE user12 DROP KEY mul_test_test1;
删除唯一索引测试

    修改表的存储引擎：

        ALTER TABLE tbl_name ENGINE=存储引擎

1 --修改表的存储引擎为MyISAM：
2 ALTER TABLE user12 ENGINE=MyISAM;
3 ALTER TABLE user12 ENGINE=INNODB;--再修改为默认引擎
修改表的存储引擎测试

    设置自增长的值：

        ALTER TABLE tbl_name AUTO_INCREMENT=值;

1 --修改自增长值
2 ALTER TABLE user12 AUTO_INCREMENT=102;
修改自增长值测试

 

 

知识点四：MySQL存储引擎(6)

查看MYSQL的存储引擎：

    　　查看MySQL支持的存储引擎：SHOW ENGINES;(表格)| SHOW ENGINES\G;(网状)

    　　查看显示支持的存储引擎信息：SHOW VARIABLES LIKE “have%”;

   　　 查看默认的存储引擎：SHOW VARIABLES LIKE “%storage_engine%”; 

 

MYSQL常用存储引擎及特点：

    　　InnoDB存储引擎

   　　 MyISAM存储引擎

  　　  MEMORY存储引擎

 

posted @ 2018-05-05 15:51  达尔文在思考  阅读(279)  评论(0)  编辑  收藏  举报