mysql5.5.54语法_mysql基本语法

1 MySQL`基本语法总结2

3 #进入与退出数据库4 mysql -uroot -ppassword;5 exit/\q6 #显示所有库7 show databases;8 #创建库9 create database db_name;10 #删除库11 drop database db_name;12 #使用数据库13 use db_name;14 #显示所有表15 show tables;16 #创建表17 create tabletb_name(create_difinition.....);18 #显示建表信息19 desctb_name;20 #删除表21 drop tabletb_name;22 #指定列插入23 insert into tb_name(column_name) values(column_values);24 #全列插入25 insert into tb_name values(all_values);26 #多行插入27 insert into tb_name(column_names) values(value_1),(value_2).....28 #指定列查询29 select column_name fromtb_name;30 #全列查询31 select * fromtb_name;32 #带条件查询33 select * from tb_name whereconditions;34 #修改数据35 update tb_name set field_1 = value_1,field_2 = value_2 where a_field =a_value;36 #删除数据37 delete from tb_name where a_field =a_value;38 #表结构修改39 #添加列40 alter table tb add name varchar(20) after 字段名/first;41 #删除列42 alter table tb dropname;43 #修改字段类型44 alter table tb modify gender char(10);45 #修改字段名称46 alter table tb change name new_name char(10)47 #修改表名48 alter table tb rename tonew_student;49

50 常用权限51

52 约束类型 默认 非空 唯一 自增长 主键 外键53 关键字 default not null unique key auto_increment primary key foreign key

54

55 默认56

57 create tabletb(58 id int,59 name varchar(20),60 age int default 18);61 desctb;62 -------+-------------+------+-----+---------+-------+

63 | Field | Type | Null | Key | Default | Extra |

64 +-------+-------------+------+-----+---------+-------+

65 | id | int(11) | YES | | NULL | |

66 | name | varchar(20) | YES | | NULL | |

67 | age | int(11) | YES | | 18 | |

68 +-------+-------------+------+-----+---------+-------+

69 insert into tb(name) values("郭康伦"),("无名"),("墨染"),("南北");70 select * fromtb;71 id | name | age |

72 +------+-----------+------+

73 | NULL | gkl | 18 |

74 | NULL | nh | 18 |

75 | NULL | 郭康伦 | 18 |

76 | NULL | 无名 | 18 |

77 | NULL | 墨染 | 18 |

78 | NULL | 南北 | 18 |

79 +------+-----------+------+

80

81

82

83

84 非空85

86 create tabletb_1(87 id int,88 name varchar(20) not null);89

90 insert into tb_1(id) values(1);91 ERROR 1364 (HY000): Field 'name' doesn't have a default value92

93

94

95

96 唯一97

98 CREATE TABLE tb2(99 id int UNIQUE KEY,100 name varchar(20)101 );102 #重复则报错103

104

105

106

107

108

109

110 自增长111

112 create table tb3(113 id int primary key auto_increment,114 name varchar(20)115 )auto_increment =100;116 insert into tb3(name) values("郭康伦"),("南北"),(" 无名");117 mysql> select * from tb3;118 +-----+-----------+119 | id | name |120 +-----+-----------+121 | 100 | 郭康伦 |122 | 101 | 南北 |123 | 102 | 无名 |124 +-----+-----------+125

126

127

128

129

130

131

132

133

134 主键135

136 主键的作用: 可以唯一标识 一条数据，每张表里面只能有一个主键,。137 主键特性： 非空且唯一。当表里没有主键的时，第一个出现的非空且为唯一的列，被当成主键。138

139 create table tb4(140 id int primary key,141 name varchar(20) not null142 );143 #删除主键约束144 alter table tb4 drop primary key;145 #添加主键约束146 mysql> alter table tb3147 -> add primary key(id);148

149

150

151

152

153

154 外键155

156 外键约束 ：保持数据一致性，完整性实现一对多关系。157 外键必须关联到键上面去，一般情况是，关联到另一张表的主键158 建立选课系统中的4张表: (学院表，学生表，课程表，选课表) , 并每张表插入4条数据159

160

161

162 1、学院表163

164 create table college(165 college_id int primary key auto_increment,166 college_name varchar(20) not null);167

168

169

170 2、学生表171

172 create table student(173 student_id int primary key auto_increment,174 student_name varchar(20),175 s_id int,176 foreign key(s_id) references college(college_id));177

178

179

180

181

182 3、课程表183

184 create table course(185 course_id int primary key auto_increment,186 course_name varchar(20) not null,187 c_id int,188 foreign key(c_id) references college(college_id));189

190

191

192 4、选课表193

194 create table choice(195 id_c int,196 id_s int,197 primary key(id_s,id_c),198 foreign key(id_s) references student(student_id),199 foreign key(id_c) references course(course_id));200

201

202

203

204

205

206

207 5、插入数据208

209 insert into college values(null,"外国语学院");210 insert into college values(null,"土木学院");211 insert into college values(null,"计算机学院");212 insert into college values(null,"经管学院");213

214 ---------------------------------------------------------------------------------------------215 college_id | college_name |216 +------------+-----------------+217 | 1 | 外国语学院 |218 | 2 | 土木学院 |219 | 3 | 计算机学院 |220 | 4 | 经管学院 |221 +------------+-----------------+222 ---------------------------------------------------------------------------------------------223

224

225

226 insert into student values(null,"小明",1);227 insert into student values(null,"小红",3);228 insert into student values(null,"郭康伦",2);229 insert into student values(null,"小白",4);230 select * from student join college on student.s_id = college.college_id;231 ---------------------------------------------------------------------------------------------232 student_id | student_name | s_id | college_id | college_name |233 +------------+--------------+------+------------+-----------------+234 | 1 | 小明 | 1 | 1 | 外国语学院 |235 | 2 | 小红 | 3 | 3 | 计算机学院 |236 | 3 | 郭康伦 | 2 | 2 | 土木学院 |237 | 4 | 小白 | 4 | 4 | 经管学院 |238 +------------+--------------+------+------------+-----------------+239

240 ---------------------------------------------------------------------------------------------241

242

243

244

245

246 insert into course values(null,"英语",1);247 insert into course values(null,"土木结构",2);248 insert into course values(null,"python",3);249 insert into course values(null,"经济管理学",4);250 select * from course join college on course.c_id = college.college_id;251 -----------+-----------------+------+------------+-----------------+252 | course_id | course_name | c_id | college_id | college_name |253 +-----------+-----------------+------+------------+-----------------+254 | 1 | 英语 | 1 | 1 | 外国语学院 |255 | 2 | 土木结构 | 2 | 2 | 土木学院 |256 | 3 | python | 3 | 3 | 计算机学院 |257 | 4 | 经济管理学 | 4 | 4 | 经管学院 |258 +-----------+-----------------+------+------------+-----------------+259

260

261 insert into choice values(1,2);262 insert into choice values(1,3);263 insert into choice values(2,2);264 insert into choice values(2,4);265 insert into choice values(3,4);266 insert into choice values(3,3);267 insert into choice values(4,4);268 insert into choice values(4,2);269 select * from choice join student on student_id = choice.id_s join course on course.course_id = id_c;270

271 id_c | id_s | student_id | student_name | s_id | course_id | course_name | c_id |272 +------+------+------------+--------------+------+-----------+-----------------+------+273 | 1 | 2 | 2 | 小红 | 3 | 1 | 英语 | 1 |274 | 1 | 3 | 3 | 郭康伦 | 2 | 1 | 英语 | 1 |275 | 2 | 2 | 2 | 小红 | 3 | 2 | 土木结构 | 2 |276 | 2 | 4 | 4 | 小白 | 4 | 2 | 土木结构 | 2 |277 | 3 | 3 | 3 | 郭康伦 | 2 | 3 | python | 3 |278 | 3 | 4 | 4 | 小白 | 4 | 3 | python | 3 |279 | 4 | 2 | 2 | 小红 | 3 | 4 | 经济管理学 | 4 |280 | 4 | 4 | 4 | 小白 | 4 | 4 | 经济管理学 | 4 |281 +------+------+------------+--------------+------+-----------+-----------------+------+282

283

284

285

286

287

288

289

290

291

292

293

294

295 子查询和连接查询296

297 数据准备298

299 create table students(300 number int UNIQUE KEY auto_increment,301 name varchar(20) UNIQUE KEY,302 age int not null,303 birth date not null304 )auto_increment=201804001;305

306 insert into students (name, age, birth) value307 ('刘一', 16,'2002-01-01'),308 ('陈二', 17,'2002-01-01'),309 ('张三', 18,'2002-01-01'),310 ('李四', 19,'2002-01-01'),311 ('王五', 20,'2002-01-01'),312 ('赵六', 21,'2002-01-01'),313 ('孙七', 22,'2002-01-01'),314 ('周八', 23,'2002-01-01'),315 ('吴九', 23,'2002-01-01'),316 ('郑十', 23,'2002-01-01');317 number | name | age | birth |318 +-----------+--------+-----+------------+319 | 201804001 | 刘一 | 16 | 2002-01-01 |320 | 201804002 | 陈二 | 17 | 2002-01-01 |321 | 201804003 | 张三 | 18 | 2002-01-01 |322 | 201804004 | 李四 | 19 | 2002-01-01 |323 | 201804005 | 王五 | 20 | 2002-01-01 |324 | 201804006 | 赵六 | 21 | 2002-01-01 |325 | 201804007 | 孙七 | 22 | 2002-01-01 |326 | 201804008 | 周八 | 23 | 2002-01-01 |327 | 201804009 | 吴九 | 23 | 2002-01-01 |328 | 201804010 | 郑十 | 23 | 2002-01-01 |329 +-----------+--------+-----+------------+330

331

332 create table subjects(333 number int(4) unsigned zerofill NOT NULL auto_increment,334 title varchar(20),335 duration int,336 PRIMARY KEY (number)337 );338

339

340 insert into subjects (title, duration) value341 ('python基础', 32),342 ('python进阶', 16),343 ('web前端', 16),344 ('python框架', 32),345 ('python项目', 32);346 -------+--------------+----------+347 | number | title | duration |348 +--------+--------------+----------+349 | 0001 | python基础 | 32 |350 | 0002 | python进阶 | 16 |351 | 0003 | web前端 | 16 |352 | 0004 | python框架 | 32 |353 | 0005 | python项目 | 32 |354 +--------+--------------+----------+355

356

357 create table grades(358 student_number int,359 subject_number int(4) unsigned zerofill NOT NULL,360 grade int not null361 );362

363

364

365 insert into grades values366 (201804001, 0001, 90),367 (201804002, 0001, 89),368 (201804003, 0001, 88),369 (201804004, 0001, 87),370 (201804005, 0001, 86),371 (201804006, 0001, 85),372 (201804007, 0001, 84),373 (201804008, 0001, 83),374 (201804009, 0001, 82),375 (201804010, 0001, 81),376 (201804001, 0002, 80),377 (201804002, 0002, 79),378 (201804003, 0002, 78),379 (201804004, 0002, 77),380 (201804005, 0002, 76),381 (201804006, 0002, 75),382 (201804007, 0002, 74),383 (201804008, 0002, 73),384 (201804009, 0002, 72),385 (201804010, 0002, 71);386 student_number | subject_number | grade |387 +----------------+----------------+-------+388 | 201804001 | 0001 | 90 |389 | 201804002 | 0001 | 89 |390 | 201804003 | 0001 | 88 |391 | 201804004 | 0001 | 87 |392 | 201804005 | 0001 | 86 |393 | 201804006 | 0001 | 85 |394 | 201804007 | 0001 | 84 |395 | 201804008 | 0001 | 83 |396 | 201804009 | 0001 | 82 |397 | 201804010 | 0001 | 81 |398 | 201804001 | 0002 | 80 |399 | 201804002 | 0002 | 79 |400 | 201804003 | 0002 | 78 |401 | 201804004 | 0002 | 77 |402 | 201804005 | 0002 | 76 |403 | 201804006 | 0002 | 75 |404 | 201804007 | 0002 | 74 |405 | 201804008 | 0002 | 73 |406 | 201804009 | 0002 | 72 |407 | 201804010 | 0002 | 71 |408 +----------------+----------------+-------+409

410

411 #查找张三的成绩412 select name,title,grade from students join grades on students.number = grades.student_number join subjects on subjects.number = grades.subject_number where name = "张三";413 name | title | grade |414 +--------+--------------+-------+415 | 张三 | python基础 | 88 |416 | 张三 | python进阶 | 78 |417 +--------+--------------+-------+418

419

420

421

422 用户权限423

424 #查看用户425 select user,host from mysql.user;426 #修改当前账号密码427 set password = password("123456");428 #创建用户429 create user "gkl"@"%"identified by "123";430 #查看用户权限431 show grants for "gkl"@"%";432 删除用户：433 drop user'test'@'localhost';434 drop user "gkl"@"%";435 #查看编码436 show variables like "%char%";437

438

439

440 mysql配置文件441

442 /etc/mysql/mysql.conf.d$ vim mysqld.cnf