mysql big tables_mysql常见命令操作

MySql常用命令总结

1:使用SHOW语句找出在服务器上当前存在什么数据库：(大小写一样)

mysql> SHOW DATABASES; (展示数据库时候用databases)

2:2、创建一个数据库MYSQLDATA (展示一个数据时候用database)

创建带字符编码的数据库create database mydb character set utf8;(注意mysql中是utf8;不是utf-8)

mysql> CREATE DATABASE MYSQLDATA; (删除用drop database dbname)

3:选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！，当然没有；也提示成功)

4:查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5:创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构：

mysql> DESCRIBE(DESC) MYTABLE;

7:往表中加入记录

mysql> insert into MYTABLE values (”hyq”,”M”);

8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9:导入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex=”man” where name=’jack’;

--------------------------------------------

----------------字段操作----------------------

添加字段：alter table tablename add column age int;

删除一个字段：alter table tablename DROP COLUMN new2;

插入一个字段默认为：alter table tablename add column age int default 0;

修改一个字段的类型 alter table tablename MODIFY new1 VARCHAR(10);

修改一个字段的名称，此时一定要重新指定该字段的类型 alter table user CHANGE new1 new4 int;

添加主键： alter table tablename add primary key(col)

删除主键： alter table tablename drop primary key(col)

----------------自增长---------------

CREATE TABLE class

(

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(15) NOT NULL

)AUTO_INCREMENT = 100;从100开始

可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

插入数据insert into text values(null,'jack');要用null

没用AUTO_INCREMENT = 100;表示默认从1开始

----------------函数-------------------------

select now(),current_date()

select * from tablename where field1 like ’%value%’ --查找特定格式的数据，注意中间是

求和：select sum(field1) as sumvalue from tablename

平均：select avg(field1) as avgvalue from tablename

最大：select max(field1) as maxvalue from tablename

最小：select min(field1) as minvalue from tablename

排序：select * from tablename order by field1,field2 [desc]

选择前十条记录：select * from tablename limit 10; (注意不要用top)

分页：select * from tablename limit 2,4 ( 即取出第3条至第6条，4条记录)

select ASCII('A'); //对应65

返回二进制值N的一个字符串表示select bin(8);//8的二进制位1000

------------------------------------

导入.sql文件命令(例如D:/daoru.sql)所以可以直接在文本中写，在导入cmd中执行。。

create table daoru

(

name varchar(20),

password varchar(20)

);

insert into daoru values('JACK','123');

执行mysql>source d:/mysql.sql;

----------------------------

//补充

查询

过滤表中的重复数据

select distinct username from tablename(过滤掉重名的)

查询数学成绩分数为100.90，44分的人

select * from student where math in(100,90,44)

//商品分类

select username,sum(math) from tablename group by username(根据一样的username归为一组)

where后面不能跟合计函数，要用having

select username,sum(math) from tablename group by username having sum(math)>200

创建带字符编码的数据库create database mydb character

set utf8;(注意mysql中是utf8;不是utf-8)

查看数据库的信息show create database mydb(可以看数据库编码等信息)

如果数据库语句创建成功了，怎么添加编码为utf8

alter database mydb character set utf8;

重命名表名

alter table aa change column name username varchar(20)(name改为username)

备份数据库表中的数据

mysqldump -u用户名 -p密码 数据库名 >文件名.sql(备份带那里window命令)

恢复数据库

source 文件名.sql

//演示

备份

mysqldump -uroot -p123 mydb >D://mydb.sql (保存带了D:mysql.sql中)(数据库到文件)

恢复(先删掉数据库，在恢复)

要恢复库，要先创建库，因为只能恢复数据，不能恢复库

mysql -uroot -p123 mydb

//数据库约束，主键，自增长，非空，不重复，外键

create table a

(

name varchar(20) not null, //非空

password int unique //不能重复

);

//外键约束

比如(老公，老婆关系管理系统)

老公表

id name

1 jack

2 xilin

老婆表

id name husbandId(这列叫外键，该值来自于外面的数据，一定要加外键约束)

1 lucy 2 (外键数据来自老公表)

2 mily 1

create table husband

(

id int primary key auto_increment,

name varchar(20)unique

);

create table wife

(

id int primary key auto_increment,

name varchar(20)unique,

husbandId int,

constraint husbandId_FK foreign key(husbandId) references husband(id)

);

//constraint (约束名)foreign key(约束那个)references(参考那个表的那个值)

//数据库中表的设计

-----------一对多，多对一----------------------

部门关系管理系统(一个部门对于多个员工，多个员工对于一个部门)

//部门 员工

Department Employee

String id; String id,

String name, String name,double price;

Set employee=new HashSet(); Department department;

一对多的设计,那么部门数据如何往数据库里面存呢？

Department d=new Department();

d.setId(1);

d.setname("开发部");

Employee e=new Employee();

e.setId(1);

e.setName("xiaoming"); //部门对象

e.setPrice(100);

Employee e1=new Employee();

e1.setId(2); //员工数据

e1.setName("xiaoge");

e1.setPrice(200);

d.add(e1);

d.add(e2); //把员工数据增加到部门

----------------------思路----------------------

设计表的时候先不要考虑引用关系，设计基本表，在一对多的多的方那加入外键约束就行

department表

id name

1 开发部

Employee表

id name price department_id

1 xiaoming 100 1

2 xiaoge 200 1

------------多对多对象-------------------------

教学管理系统

Teacher对象

String id,

String name;

String price,

Set student=new HashSet();//老师保存学生信息

Student对象

String id,

String name;

Set teacher=new HashSet();//学生保存老师信息

//先不要考虑引用关系，先设计基本信息表

Teacher表

id name price

1 jack 3244

Student表

id name

1 lucy

2 mark

---------设计中介表-Teacher_Student---------

Teacher_Student 表

teacher_id(外键,来自老师id) student_id(外键，来自学生id)

1 1

1 2

-----------------------------------------------

create table teacher

(

id int primary key,

name varchar(20),

price int

);

create table student

(

id int primary key,

name varchar(20)

);

create table teacher_student

(

teacher_id int,

student_id int,

primary key(teacher_id,student_id),

constraint teacher_id_FK foreign key(teacher_id) references teacher(id),

constraint student_id_FK foreign key(student_id) references student(id)

);

create table teacher_student

(

teacher_id int,

student_id int,

primary key(teacher_id,student_id) //联合主键

constraint teacher_id_FK foreign key(teacher_id) references teacher(id)//外键约束

constraint student_id_FK foreign key(student_id) references student(id)//外键约束

)

----------一对一关系，必须有主从关系(人可以无身份证，身份证不能没人)-----------

身份证管理系统

person类

String id;

String name

IdCard idcard;

IdCard类

String id;

String city;

person p;

person p=new person();

p.setId(1);

p.setName("lin");

IdCard c=new IdCard();

c.setId(1);

c.setCity("nanjing");

p.setCard(c);

----------------------------

person表

id name

1 jack

2 lucy

IdCard表

id city person_id(外键约束，unique,not null约束)，但是person表已经是unique,not null,这变设计外键就行了

1001 nanjing 1

1021 beijing 2

create table person

(

id int primary key,

name varchar(20)

);

create table idcard

(

id int primary key,

city varchar(10),

constraint id_FK foreign key(id)references person(id)

);

-----------------------------

自连接的表,可以实现无限循环表(京东分类表：如电器类->电脑->平板。。)

person p

id name parent_id

1 林大大

2 林中中 1

3 林小小 2 (家族遗传)

create table myperson

(

id int primary key,

name varchar(20),

parent_id int,

constraint parent_id_FK foreign key(parent_id)references myperson(id)

)

---------级联删除-------------------

create database department_employee character set utf8;

use department_employee;

create table department

(

id int primary key,

name varchar(20)

);

create table employee

(

id int primary key,

name varchar(20),

salary double,

employee_id int,

constraint empoyee_id_FK foreign key(employee_id)references department(id)on delete set null

//如果是on delete set null的话，删除department主表的时候，从表employee_id置为空

//如果是on delete cascade,删除主表的时候，从表也删除(要小心)

);