【MySQL笔记】基础CRUD

• 创建/显示数据库和表
• 添加/修改数据库和表
• 删除数据库和表
• 查询数据

登录/退出

mysql -uroot -p123456

quit

创建/显示数据库和表：

创建数据库和表

创建数据库：

create database menageria;

如果已经存在某个数据库并且想使用这个数据库：

use menageria;

在数据库中创建新表：

 CREATE TABLE pet (name VARCHAR(20),owner VARCHAR(20),
  					species VARCHAR(20), sex CHAR(1), birth DATE,
  					primary key(name))default charset=utf8;

上面这条query创建了一张表，字段有：name, owner, species, sex, birth。其中name是主键。
表格默认的字体为utf8

将一个表中的数据复制到一个新表里面：

create table newtable select * from oldtable where id<10;

上面这条命令将旧表table中id小于10的数据都拷贝到了新表newtable中。

将query查询的结果存放一个新的表中：

create table newtable(id varchar(20),amount01 varchar(20),amount02 varchar(20))
select t1.id id,t1.amount amount1,t2.amount amount2
from table1 t1 left outer join table2 t2
on t1.id=t2.id;

上面的查询语句将表table1和表table2的查询结果存放到新表newtable中

数据库中常用的数据类型：

数据类型	大小	内容
tinyint	1字节	整数
smallint	2字节	整数
int/integer	4字节	整数
char	0-255字节	定长字符串
varchar	0-65535	不定长字符串
enum(‘F’,‘M’)		枚举类型
datetime		一个日期

字段关键字：

字段关键字	功能
AUTO_INCREMENT	每个表至多有一个字段可以使用AUTO_INCREMENT
DEFAULT	给字段设置关键字
UNSIGNED	字段的值不能为负
PRIMARY KEY	设置字段为主键
UNIQUE	字段中数据唯一 一个表可以有多个字段是UNIQUE，但只能有一个字段是PRIMARY KEY
FOREIGN KEY	外键 语法格式： constraint 约束名 foreign key(当前表的字段名) references 外联的父表名(外联父表的字段名) on delete 级联选项 on update 级联选项

创建一个新表时设置主键（primary key）：

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));

创立完表后再定义主键：

alter table  tablename add primary key(id);

显示数据库和表

所有数据库：

show databases;

当前数据库：

select database();

当前数据库下的所有表：

show tables;

显示某个表的字段有哪些：

describe pet;

显示某个表的内容有哪些：

select * from pet;

这里的*是指表中的所有字段

修改数据库和表:

添加新的字段：

alter table <表名> add address varchar(50) not null;

修改字段：

格式：
alter table <表名> change 原字段名 新字段名 数据类型；
alter table <表名> modify 字段名 数据类型；

alter table users change phone phone int unsigned default 0；
alter table users modify phone int unsigned default 0;
alter table tablename rename to new_tablename;

添加约束条件：

对phone添加唯一性约束，约束名为phone_unique

alter table users add constraint phone_unique unique(phone);

给字段添加外键：

alter table <表名> add constraint <约束名> foreign key(本表字段名) references <父表>(<父表字段名>);

修改一条数据中的某个值：

update course set course_hours=90 where course_name='MySQL'; 

修改表格文字的字符集为utf8：

alter table <表名> convert to character set utf8;

向表中插入数据：

只向单个字段插入数据：

insert into <表名> (<字段名>) values(<一个值>);

插入单条数据：

INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30');

单次插入多条数据：

INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

'insert'也可以用'replace'代替，二者的区别是：replace添加数据时，如果新数据的主键值或者唯一性约束的字段值与已有的数据相同，
则会删除已有的数据，再添加新的数据

删除数据库和表：

删除数据库和表

删除一个数据库：

drop database <数据库名>;

删除数据库中的一张表：

drop table <表名>;

删除表中的所有数据，但是保留这张表：

truncate table <表名>;

删除字段

alter table users drop address;

删除表中的某条数据

delete from course where course_name='MySQL';

删除主键/重新添加主键

删除主键：

alter table tablename drop primary key;

删除字段唯一性约束：

alter table users drop index phone_unique;

将txt文件中的数据传入数据库

LOAD DATA LOCAL INFILE 'D:/pet.txt' INTO TABLE pet；

填写文件路径时使用斜杠"/"，不要使用反斜杠""
如果出现报错ERROR 1148 (42000)可以参考：
https://blog.csdn.net/AXIMI/article/details/89054799

修改数据

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

原来的table：

修改完数据之后的table：

查询数据

查询指定数据：

select * from pet where name='Peppi';

模糊查询：

select * from pet where birth>='2000-1-1';

多种条件查询：

select * from pet where sex='f' and birth>='2010-4-5';

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');

查询指定字段的数据：

select name,birth from pet;

去掉重复的数据：

select distinct owner from pet;