1、启动mysql
1.1、首先进入mysql下的bin目录
cd /usr/local/mysql/bin/
1.2、启动mysql
./mysql -u root -p
数据库启动成功
2、数据库操作
2.1、查看数据库
show databases;
2.2、创建数据库
create database shujuku;
2.2.1、设置编码格式
create database mydb character set = utf8;
2.2.2、修改编码格式
alter database mydb character set gb2312;
alter database shujuku character set utf8;
2.3、删除数据库
drop database mydb;
2.4、使用数据库
use shujuku;
2.5、表操作
2.5.1创建一个表名为employee,该表中含有id、name、sex、birthday、job字段
create table employee(id int,name varchar(40),sex char(4),birthday date,job varchar(50));
2.5.2 查看表结构
desc employee;
2.5.3 添加字段(image blob)
alter table employee add image blob;
2.5.4 修改字段【修改job的长度,使其长度为40(原先为50)】
alter table employee modify job varchar(40);
2.5.5 删除字段(sex)
alter table employee drop sex;
2.5.6 修改表名为user并列出当前数据库中的所有的表(原名employee)
rename table employee to user;
show tables;
2.5.7 修改表的字符集为utf-8
alter table user character set utf8;
2.5.8 列名name改成username
alter table user change column name username varchar(100);
2.5.9 删除表
drop table user;
2.6、表操作(增删改查)
2.6.1 创建表(employee)
create table employee(id int,name varchar(40),sex char(4),birthday date,job varchar(50));
2.6.2 修改字段
alter table employee add salary decimal(8,2);
alter table employee add resum text;
alter table employee add entry_date date;
2.6.2 插入数据
首先修改sex长度
alter table employee modify sex char(6);
insert into employee(id,name,sex,birthday,entry_date,salary,resum)values(1,'zhangsan','male','1993-03-04','2016-11-10','4000.00','i am a developer');
insert into employee(id,name,sex,birthday,entry_date,salary,resum)values(2,'lisi','female','1995-06-08','2017-11-10','3500.00','i am a cook');
insert into employee(id,name)values(3,'jack');
删除job字段
alter table employee drop job;
2.6.3 查询所有员工的信息
select * from employee;
2.6.4将所有员工的薪水改成5000;
update employee set salary=5000.0;
2.6.5将姓名为‘zhangsan’的员工薪水改成6000;
update employee set salary=6000.0 where name='zhangsan';
2.6.6将姓名为‘Jack’的员工薪水改成5500,性别改成male
update employee set salary=5500,sex='male' where name='jack';
2.6.7将‘lisi’的薪水在原有的基础上增加800元
update employee set salary = salary + 800 where name='lisi';
2.6.8查Jack的所有信息
select * from employee where name = 'jack';
2.6.9查所有人性别和薪资
select sex,salary from employee;
2.6.10查询薪资大于5500的员工的信息
select * from employee where salary>5500;
2.6.11查询薪资在5600-5900之间的员工的信息
select * from employee where salary>5600 and salary <= 5900;
2.6.12删除表中数据
delete from employee where name='zhangsan';
2.6.13删除表中所有的数据
delete from employee;
2.7常见Mysql字段含义
auto_increment 自增长
not null 非空
default 默认值
unique 唯一
charset 指定字符集
primary key 主键
2.7.1创建employee2和employee3
create table employee2(id int auto_increment primary key,name varchar(40)
unique,sex char(4)not null,birthday date,job varchar(50)) charset=utf8
create table employee3(id int auto_increment primary key,name varchar(40)unique,sex char(4)not null,birthday date default '1990-01-01',job varchar(50)) charset=utf8;
查看employee3
2.7.2向employee3中插入字段
insert into employee3 (name,sex,job) values ('Rose','male','teacher');
2.7.3向employee3中插入数据
insert into employee3 values(null,'Jack','male','1995-08-08','worker')