05. Web基础-后端开发基础-MySQL

登录MySQL

Tips: SQL对大小写不敏感；分号为语句结束

mysql -u <userid> -P

库操作

创建数据库
create database <base_name>;

查看数据库
show databases;

切换数据库
use <base_name>;

删除数据库
drop database <base_name>;

表操作

创建表
create table <table_name>(
	<field1> type1 ... , 
	<field2> type2 ... , 
	...
	<fieldn> typen ...
);

查看表
show tables;

插入表数据
insert into <table_name>(<field1>, <field2>, ... , <fieldn>) 
		values('', '', '', ..., '');
		
更新数据
update <table_name> set <field1> = '' where <fieldn> = '';

删除数据
delete from <table_name> where <field1> = '';

Example

create database test_db;

use test_db;

create table test_t01(
	id int(4) not null primary key auto_increment,
	name char(20) not null,
	sex char(10) not null,
	addr char(50));

插入一条的时候， 可以省略自动填入的field
insert into test_t01(name, sex, addr) value ('john', 'M', 'District PuDong');

插入多条的时候，不可以省略;
insert into test_t01(id, name, sex, addr) values
	('2', 'frank', 'M', 'District PuDong'),
	('3', 'ivanke', 'F', 'District HongQiao'),
	('4', 'will', 'M', 'District PuDong'),
	('5', 'carl', 'M', 'District PuDong');
	
select * from test_t01;

update test_t01 set addr = 'District JiaDing' where name = 'john';

排序 Order by

select <field1>, ..., <fieldn> from <table_name> where <field> = '' order by <field> ASC/DESC

合并UNION

该语句会将重复数据过滤掉，需要显示重复语句的话，使用 union all

select <fields1> from <table1> union select <fields2> from <table2>;

导入Source

导入sql文件，并执行
在引用Path的时候，注意需要转义

source <source>

内置函数

当前操作数据库	database()
select database();

当前操作用户		current_user
select current_user;

返回文件内容		load_file()
select load_file("source_link");

版本信息			version()
select version();

写入文件			into outfile
select * from <table> into outfile "file_absolute_path" {export_option};
	export_options:
		[ {FIELDS | COLUMNS}
			[ TERMINATED BY 'string']	// 每个FIELDS或COLUMNS  以string隔开
			[{OPTIONAL} ENCLOSED BY 'char']	//  每个FIELD或COLUMNS 以char包裹
			[ESCAPED BY 'char']	]
		[ LINES
			[STARTING BY 'string']
			[TERMINATED BY 'string']]
		

注释

select * from table1; #注释到该行结束

select * from table1; -- 注释到该行结束

select * /* 行中注释, 多行注释*/ from table1;