登陆:mysql -u root -p
导出数据:mysqldump -u root -p test > D:\test.sql
mysqldump -h 192.168.1.5 -u root -p streetlight > e:\streetlight.sql
导入数据:source D:\test.sql
查看支持字符集:show character set;
显示当前时间:select now() from dual;
给用户分配数据库操作权限:grant all privileges on test.* to 'root'@'localhost' identified by'123';
整表查询:select * from table-name;
表字段查询:select * from table-name where name='wang';
新增记录:insert into table-name values('','','')[column_list];
如: insert into book values("1","白鹿原","01");
insert into dage(name)values("tongluowan");
修改表:update table_name set column where [condition]
如:update authors set author_name='wang' where authors_name=‘li’;
删除表某条数据/无条件时删除表内所有数据,保留表结构:delete table_name (from table_name) where search_condition;
删除表中所有数据:truncate table table_name;
or: delete table_name;
删除表定义及数据:drop table table_name;
************************************************
查询语句:
排序:
select * from book order by prince (DESC/ASC);
多表查询:
select table1.book_name,table2.author_name from table1,table2 where table1.author_id=table2.author_id;
消除重复的行:
select DISTINCT name from table;
************************************************
创建数据库:
CREATE DATABASE testdb
修改数据库:
ALTER DATABASE testdb
ADD FILE{
name = testdb2,
filename = 'd:\testdb2',
size=10 mb
}
删除数据库 drop database database_name;
创建数据表:
use test
CREATE TABLE book(
book_id int not null primary key,
book_name char(8) not null,
author_id char(50)
)
与其他表建立关联(添加外键):
CREATE TABLE authors(
author_id int not null primary key,
author_name char(8) not null,
adress char(50) null
);
CREATE TABLE book(
book_id int not null primary key,
book_name char(8) not null,
author_id int FOREIGN KEY
REFERENCES authors (author_id)
);
show create table tablename;
查看表结构:
describe table_name;
NULL 是否可以为空,可以为空就先视NULL 不可以就视NOT
Key 索引 主键是PRI 还有UNI IND FULL 什么的
DEFAULT 字段默认值 ,建表的时候设置的
Extra 类似余备注 一般就是 主键后面的 auto_increment
修改表:
ALTER TABLE table_name
ADD [column_name data_type]
[primary key | constrain]//约束
[foreign key(colum_name) referrnces ref_table_name (ref_column_name)]
删除表:
DROP [CONSTRAINT] consraint_name | COLUMN column_name
drop可以铲除限制或字段。constraint表示删除限制;column表示删除字段。
导出数据:mysqldump -u root -p test > D:\test.sql
mysqldump -h 192.168.1.5 -u root -p streetlight > e:\streetlight.sql
导入数据:source D:\test.sql
查看支持字符集:show character set;
显示当前时间:select now() from dual;
给用户分配数据库操作权限:grant all privileges on test.* to 'root'@'localhost' identified by'123';
整表查询:select * from table-name;
表字段查询:select * from table-name where name='wang';
新增记录:insert into table-name values('','','')[column_list];
如: insert into book values("1","白鹿原","01");
insert into dage(name)values("tongluowan");
修改表:update table_name set column where [condition]
如:update authors set author_name='wang' where authors_name=‘li’;
删除表某条数据/无条件时删除表内所有数据,保留表结构:delete table_name (from table_name) where search_condition;
删除表中所有数据:truncate table table_name;
or: delete table_name;
删除表定义及数据:drop table table_name;
************************************************
查询语句:
排序:
select * from book order by prince (DESC/ASC);
多表查询:
select table1.book_name,table2.author_name from table1,table2 where table1.author_id=table2.author_id;
消除重复的行:
select DISTINCT name from table;
************************************************
创建数据库:
CREATE DATABASE testdb
修改数据库:
ALTER DATABASE testdb
ADD FILE{
name = testdb2,
filename = 'd:\testdb2',
size=10 mb
}
删除数据库 drop database database_name;
创建数据表:
use test
CREATE TABLE book(
book_id int not null primary key,
book_name char(8) not null,
author_id char(50)
)
与其他表建立关联(添加外键):
CREATE TABLE authors(
author_id int not null primary key,
author_name char(8) not null,
adress char(50) null
);
CREATE TABLE book(
book_id int not null primary key,
book_name char(8) not null,
author_id int FOREIGN KEY
REFERENCES authors (author_id)
);
show create table tablename;
查看表结构:
describe table_name;
NULL 是否可以为空,可以为空就先视NULL 不可以就视NOT
Key 索引 主键是PRI 还有UNI IND FULL 什么的
DEFAULT 字段默认值 ,建表的时候设置的
Extra 类似余备注 一般就是 主键后面的 auto_increment
修改表:
ALTER TABLE table_name
ADD [column_name data_type]
[primary key | constrain]//约束
[foreign key(colum_name) referrnces ref_table_name (ref_column_name)]
删除表:
DROP [CONSTRAINT] consraint_name | COLUMN column_name
drop可以铲除限制或字段。constraint表示删除限制;column表示删除字段。