d57
MySQL安装
Windows平台安装:
1.可执行文件安装 点点点 2.压缩包安装 放置任意目录 初始化 服务端:E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure # 用户名 root 密码:空 启动服务端: E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld 客户端连接: E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p 发送指令: show databases; create database db1; 环境变量的配置: E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin mysqld windows服务: E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install net start MySQL E:\shawn\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove net start MySQL net stop MySQL
常用操作命令
默认用户名:用户root
show databases;
use 数据库名称;
show tables;
select * from 表名;
select name,age,id from 表名;
mysql数据库user表
use mysql;
select user,host from user;
创建用户:
create user 'alex'@'192.168.1.1' identified by '123123';
create user 'alex'@'192.168.1.%' identified by '123123';
create user 'alex'@'%' identified by '123123';
授权grant/撤销revoke:
grant 权限 人
grant select,insert,update on db1.t1 to 'alex'@'%';
grant all privileges on db1.t1 to 'alex'@'%';
revoke all privileges on db1.t1 from 'alex'@'%';
学习SQL语法规则
数据库:创建/查看/删除
create database db2;
create database db2 default charset utf8; *****
show databases;
drop database db2;
表:创建/查看
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10))engine=innodb default charset=utf8;
create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8;
create table t1(
列名 类型 null,
列名 类型 not null,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
# innodb 支持事务,原子性操作
# myisam myisam
# auto_increment 表示:自增
# primary key: 表示 约束(不能重复且不能为空); 加速查找
# not null: 是否为空
数据类型:
数字:
tinyint
int
bigint
FLOAT
0.00000100000123000123001230123
DOUBLE
0.00000000000000000000100000123000123001230123
0.00000100000123000000000000000
decimal
0.1
字符串:
char(10) 速度快()
root
root
varchar(10) 节省空间
root
PS: 创建数据表定长的列往前放
text
时间类型
DATETIME
enum枚举
set集合
表:创建/清空/删除
创建表:
create table t1(
id int signed not null auto_increment primary key,
num decimal(10,5),
name char(10)
)engine=innodb default charset=utf8;
清空表:
delete from t1;
truncate table t1;
删除表:
drop table t1;
操作表中数据
插入数据:
insert into t1(id,name) values(1,'alex');
删除:
delete from t1 where id<6
修改:
update t1 set age=18;
update t1 set age=18 where age=17;
查看数据:
select * from t1;
外键:
create table userinfo(
uid int auto_increment primary key,
name varchar(32),
department_id int,
xx_id int,
constraint fk_user_depar foreign key (department_id) references color(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
#innodb原子操作
参考
今日内容参考博客:
refer:http://www.cnblogs.com/wupeiqi/articles/5713315.html
exercise
link:http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png
link:http://www.cnblogs.com/wupeiqi/articles/5729934.html