一、 连接MySQL
格式: mysql -h主机地址 -u用户名 -p用户密码
1.1 连接到本机上的MYSQL
首先打开命令窗口,然后进入目录mysql\bin,再键入命令mysql -uroot -p,回车后提示你输密码.
注意:用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码。
如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>
1.2 连接到远程主机上的MYSQL
假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:
mysql -h110.110.110.110 -uroot -pabcd123;
**注意:**u与root之间可以不用加空格,其它也一样)
1.3 退出MYSQL命令
exit (回车)
二、 修改密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码
2.1 给root加个密码ab12
首先在DOS下进入目录mysql\bin,然后键入以下命令
mysqladmin -u root -password ab12
注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。
2.2 再将root的密码改为djg345
mysqladmin -u root -p ab12 password djg345
三、 增加新用户
**注意:**和上面命令不同,下面的命令因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符
格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”
3.1 增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL,然后键入以下命令:
grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;
但增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了,解决办法见2。
3.2 增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;
如果你不想test2有密码,可以再打一个命令将密码消掉。
grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;
四、 数据库操作
4.1 创建数据库
格式:create database 数据库名;
create database somnuszpli_db;
4.2 选择数据库
格式:use 数据库名;
use somnuszpli_db;
4.3 显示数据库
show databases;
4.4 当前选择的数据库
select database();
4.5 删除数据库
格式:drop database 数据库名;
假如删除的数据库不存在就会发生错误:ERROR 1008 (HY000): Can’t drop database ‘tencent_db’; database doesn’t exist
drop database somnuszpli_db;
格式:drop database if exists 数据库名;
if exists 判断数据库是否存在,不存在也不产生错误
drop database if exists tencent_db;
五、 数据表操作
5.1 创建数据表
5.1.1 创建表的形式
CREATE TABLE 表名 (
属性名 数据类型 [完整约束条件],
属性名 数据类型 [完整约束条件],
...
...
属性名 数据类型 [完整约束条件]
);
**温馨提示:**如果你很急的登陆进去就创建表,恭喜你,你会出现“No database selected”的错误,因为你没有告诉别人你要选择在哪个数据库创建表,所以在创建之前要选择数据库,格式:USE 数据库名;
示例创建一个student表:
CREATE TABLE student (
id int,
name varchar(20)
);
创建表的时候可以补充完整性约束条件,完整性约束条件如下表所示:
约束条件 | 说明 |
---|---|
PRIMARY KEY | 标识该属性为该表的主键,可以唯一的标识对应的元组 |
FOREIGN KEY | 标识该属性为该表的外键,是与之联系某表的主键 |
NOT NULL | 标识该属性的值不能为空 |
UNIQUE | 标识该属性的值是唯一的 |
AUTO_INCREMENT | 标识该属性的值是自动增加,这是MySQL的SQL语句的特色 |
DEFAULT | 为该属性设置默认值 |
完整性约束条件的应用:
5.1.2 单字段主键格式
单字段主键格式:属性名 数据类型 PRIMARY KEY
CREATE TABLE student (
id int PRIMARY KEY,
name varchar(20)
);
5.1.3 多字段主键格式
多字段主键格式:PRIMARY KEY(属性名1,属性名2…属性名n)
CREATE TABLE student (
id int,
stu_id int,
name varchar(20),
PRIMARY KEY(id,stu_id)
);
5.1.4 设置表的外键
格式:CONSTRAINT 外键别名 FOREIGN KEY(属性1,属性2,…属性n) REFERENCES 表名(属性1,属性2,…属性n)
备注:如果报错 ERROR 1215 (HY000): Cannot add foreign key constraint
可能有以下几个原因:
1、关联的两个字段的字段的类型不一致
2、设置外键删除时set null
3、两张表的引擎不一致
CREATE TABLE teacher (
id int PRIMARY KEY,
stu_id int,
name varchar(20),
CONSTRAINT STUID FOREIGN KEY(stu_id) REFERENCES student(id)
);
5.1.5 设置表的非空约束
简单的说就是不让这个属性的值为空,不填的话就会报错
格式:属性名 数据类型 NOT NULL
CREATE TABLE student (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20) NOT NULL
);
5.1.6 设置表的唯一性约束
就是这个属性的值是不能重复的
格式:属性名 数据类型 UNIQUE
CREATE TABLE student (
id int PRIMARY KEY AUTO_INCREMENT,
teacher_id int UNIQUE
);
5.1.7 设置表的属性值自动增加
AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLINT、INT和BIGINT),在默认的情况下,该字段的值是从1开始自增
格式:属性名 数据类型 AUTO_INCREMENT
CREATE TABLE student (
id int PRIMARY KEY AUTO_INCREMENT
name varchar(20) NOT NULL
);
5.1.8 设置表的属性的默认值
格式:属性名 数据类型 DEFAULT 默认值
CREATE TABLE student (
id int PRIMARY KEY AUTO_INCREMENT,
sex varchar(10) DEFAULT 'male'
);
5.2 查看表结构
5.2.1 查看表基本结构语句DESCRIBE
格式:DESCRIBE 表名 或者 DESC 表名;
通过查看表的结构,就很明确的对表进行解读,而且可以查看一下自己创建的表有没错误。
describe student;
5.2.2 查看表详细结构语句SHOW CREATE TABLE
格式:SHOW CREATE TABLE 表名;
通过这个SQL语句可以查看表的详细定义,除了字段名、字段的数据类型、约束条件外,还可以查看表的默认存储引擎和字符编码
SHOW CREATE TABLE student;
5.3 修改数据表
5.3.1 修改表名
表名可以在一个数据库中唯一的确定一张表。
格式:ALTER TABLE 旧表名 RENAME 新表名;
ALTER TABLE STUDENT1 RENAME STUDENT;
5.3.2 修改字段的数据类型
格式:ALTER TABLE 表名 MODIFY 属性名 数据类型;
ALTER TABLE STUDENT MODIFY NAME VARCHAR(50);
5.3.3 修改字段名
格式:ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
ALTER TABLE STUDENT CHANGE NAME STU_NAME VARCHAR(40);
这里我修改字段名的同时也修改了数据类型,如果你不想修改数据类型的话就按照原来的写就行了。
5.3.4 增加字段
格式:ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
其中,“属性名1”参数指需要增加的字段的名称;“FIRST”参数是可选参数,其作用是将新增字段设置为表的第一个字段;“AFTER”参数也是可选的参数,其作用是将新增字段添加到“属性名2”后面;“属性名2”当然就是指表中已经有的字段。
ALTER TABLE STUDENT ADD STU_AGE INT NOT NULL AFTER STU_ID;
5.3.5 删除字段
格式:ALTER TABLE 表名 DROP 属性名;
ALTER TABLE STUDENT DROP STU_AGE;
5.3.6 更改表的存储引擎
格式:ALTER TABLE 表名 ENGINE = 存储引擎名;
ALTER TABLE STUDENT ENGINE=MYISAM;
5.3.7 删除表的外键约束
格式:ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
ALTER TABLE TEACHER DROP FOREIGN KEY STUID;
5.4 删除数据表
格式:DROP TABLE 表名;
DROP TABLE STUDENT;
删除没有被关联的普通表:直接上面的SQL语句就行了。
删除被其他表关联的父表:
方法一:先删除子表,在删除父表
方法二:删除父表的外键约束(ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;),再删该表
六、 表数据操作
6.1 增
格式:INSERT INTO 表名 ( 字段名1, 字段名2,…字段名N ) VALUES ( 字段值1, 字段值2,…字段值N );
insert into student (id,stu_id,stu_name) values (1,2,'somnuszpli'),(2,3,'zhangsan');
6.2 删
格式:DELETE FROM 表名 [WHERE 条件];
备注:如果不加WHERE条件则表示删除表中的所有数据,慎重操作。
DELETE FROM STUDENT WHERE ID>=3;
6.3 改
格式:UPDATE 表名 SET 字段名1=字段值1, 字段名2=字段值2,…字段名N=字段值N [WHERE 条件];
备注:如果不加条件则表示将表中所有的数据都修改,慎重操作。
update student set stu_name='wangwu' where id=2;
6.4 查
格式:SELECT 字段名1, 字段名2,…字段名N FROM 表名1 [WHERE 条件 ]
备注:可以在字段和表的后边设置别名,用as连接或者直接省略as写别名。
select u.id as uid,u.stu_name u_name from student u;
七、 索引
7.1 创建索引
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
7.1.1 CREATE TABLE时创建索引
CREATE TABLE 表名(
字段名1 字段类型,
…字段名N 字段类型,
INDEX 索引名 (字段名1,…字段名N)
);
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
userage int,
INDEX name_age (username,userage)
);
7.1.2 使用ALTER TABLE创建索引
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
CREATE INDEX 索引名 ON 表名(字段名[]);
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
7.2 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。
附录:项目升级常用脚本
#新建表
CREATE TABLE `ww_order` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`product_id` int(11) NULL DEFAULT NULL COMMENT '商品ID',
`user_id` int(11) NULL DEFAULT NULL COMMENT '用户ID',
`order_sn` varchar(50) NULL DEFAULT NULL COMMENT '订单号',
`status` tinyint(1) NULL DEFAULT 1 COMMENT '状态:1=待完善信息,2=审核中,3=已通过,4=已驳回',
`createtime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`updatetime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
`deletetime` int(11) NULL DEFAULT NULL COMMENT '删除时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
#在 ww_order 添加一个 phone 字段
ALTER TABLE `ww_order` add `phone` int(11) DEFAULT NULL COMMENT '联系方式';
#修改 ww_order 表 的phone 字段的数据类型
ALTER TABLE ww_order MODIFY column phone varchar (20) default NULL;
#修改 ww_order 表 的 phone 字段默认值
ALTER TABLE ww_order ALTER `phone` SET DEFAULT 10086;
# 删除 ww_order 表 的 phone 字段
ALTER TABLE ww_order DROP phone;
#添加一个主键 这意味着索引值必须是唯一的且不能为NULL
ALTER TABLE ww_order ADD PRIMARY KEY (id);
#创建普通索引 注意 product_alias_name 为索引别名 删除索引时可以通过别名删除 可以不写别名
ALTER TABLE ww_order ADD INDEX product_alias_name (product_id);
#不写索引别名时 默认索引名为 product_id
ALTER TABLE ww_order ADD INDEX (product_id);
#创建唯一索引
ALTER TABLE ww_order ADD UNIQUE order_sn_alias_name (order_sn);
#不写索引别名时 默认索引名为 order_sn
ALTER TABLE ww_order ADD UNIQUE (order_sn);
#创建联合唯一索引 比如每人限购 user_id 联合 product_id 唯一
ALTER TABLE ww_order ADD UNIQUE user_product_id (user_id,product_id);
#删除索引 product_id 通过索引别名删除索引
ALTER TABLE ww_order DROP INDEX `product_alias_name`;