1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
| #---------------初始化------------------
net start MySQL # start the service
mysql -D samp_db -u root -p # login to the atabase
create database samp_db character set gbk;
show databases; # display current databases
use samp_db; # use the databse called samp_db
#---------------数据库操作---------------
source C:\Users\Link\Desktop\createtable.sql # 将sql文件导入
show tables; # 展示目前sql中的tables
describe students; # students是目前sql中的一个表，用describe可以展示其详细信息
# 假如说students的定义是这样
create table students
(
id int unsigned not null auto_increment primary key,
name char(8) not null,
sex char(4) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
);
# insert的用法: insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);
# 向students表中插入一行数据，分别对应id, name, sex, age, phone
insert into students values(NULL, "王刚", "男", 20, "13063716100")
# 或者也可以这么插入，只修改name, sex 和age,其他的两个参数用默认的
insert into students (name, sex, age) values("王刚", "男", 20)
# select的用法: select 列名称 from 表名称 [查询条件];
select name, age from students;
# 将所有的信息都打出来，包括id(primary key)
select * from students;
# 按照特定条件查询
select * from students where sex="女"; # 查询sex值为“女”的，注意，支持汉字是因为前面搞了gk
select * from students where age > 21;
select * from students where name like "%王%";
select * from students where id < 5 and age > 20;
# update的用法: update 表名称 set 列名称=新值 where 更新条件;
update students set name = "ass" where id = 2;
update students set tel = default where name like "%ass%";
update students set sex = "女" where tel like default;
# delete的用法: delete from 表名称 where 删除条件;
delete from students where id = 1;
# alter的用法：alter table 表名 add 列名 列数据类型 [after 插入位置];
# 这个是用来修改表的数据格式，例如students表只有name, sex, age和tel，我想再加一列，就用alter add
# 添加列
alter table students add address char(60); # 在表的最后追加一列address，是char(60)类型
alter table students add birthday date after age; # 在age后追加一列birthday，注意date是数据类型
alter table students add motherfucker char(60) null default "-";
# 修改列:alter table 表名 change 列名称 列新名称 新数据类型;
alter table students change tel telephone char(13) default "-";
# 删除列: alter table 表名 drop 列名称;
alter table students drop address;
# 重命名表： alter table 表名 rename 新表名;
alter table students rename student;
# 退出mysql登录，保存刚才的sql->samp_db(database)->student(table)
exit
mysqldump -u root -p samp_db > C:\Users\Link\Desktop\samp_db.sql
# 存储信息如下
;
;
;
SET NAMES utf8mb4 ;
;
;
;
;
;
;
DROP TABLE IF EXISTS `student`;
;
SET character_set_client = utf8mb4 ;
CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(8) NOT NULL,
`sex` char(4) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`birthday` date DEFAULT NULL,
`telephone` char(13) DEFAULT '-',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
;
LOCK TABLES `student` WRITE;
;
INSERT INTO `student` VALUES (2,'ass','boy',20,NULL,'-'),(3,'鐜嬫櫒鏅?,'濂?,21,NULL,'-'),(4,'鐜嬪垰','鐢?,20,NULL,'15578732010');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-05-19 21:37:23
|