mysql学习记录
安装:
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server
授权:
chown mysql:mysql -R /var/lib/mysql
初始化:
mysqld --initialize
启动(centos 7)
systemctl start mysqld
systemctl status mysqld
验证:
mysqladmin --version
设置root密码(Mysql安装成功后,默认的root用户密码为空)
[root@host]# mysqladmin -u root password "new_password";
登陆:
mysql -h 主机名 -u 用户名 -p
-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
windows:
启动:
cd c:/mysql/bin
mysqld --console
关闭mysqld;
cd c:/mysql/bin
mysqladmin -uroot shutdown
linux:
检查mysql服务是否启动:
ps -ef | grep mysqld
开启:
root@host# cd /usr/bin
./mysqld_safe &
关闭:
root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******
添加mysql用户:
mysql> INSERT INTO user
(host, user, authentication_string{or password},
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
MD5{or PASSWORD}('guest123'), 'Y', 'Y', 'Y');
password函数对密码进行加密 ,MD5()函数可以代替
注意:在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
如果你不使用该命令,你就无法使用新创建的用户来连接mysql服务器,除非你重启mysql服务器。
用户授权:
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';
命令:
use database_name;
SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM table_name;
SHOW INDEX FROM table_name;
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
mysql> SHOW TABLE STATUS FROM RUNOOB; # 显示数据库 RUNOOB 中所有表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G; # 加上 \G,查询结果按列打印
操作:
连接:
mysql -h 主机名 -u 用户名 -p
创建库:
CREATE DATABASE database_name;
or
[root@host]# mysqladmin -u root -p create RUNOOB
Enter password:******
删除库:
drop database database_name;
or
[root@host]# mysqladmin -u root -p drop RUNOOB
Enter password:******
使用库:
use database;
支持的数据类型:
integer(int)(4字节)、smallint(2字节)、decimal(dec)(对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2)、numeric 、float(4字节)、real、double(8字节) precision
date(3字节)、time(3字节)、year(1字节)、datetime(8字节)、timestamp(4字节)
char、varchar、tinyblob、tinytext --短文本 0-255
blob、text--长文本 0-65535
mediumblob、mediumtext、longblob、longtext --超长文本 0-4294967295
扩展支持:
tinyint(1字节)、 mediumint(3字节)、bigint(8字节)
关键字int 是integer的同义词,关键字dec 和decimal 是同义词
创建表:
CREATE TABLE table_name (column_name column_type);
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
ENGINE 设置存储引擎,CHARSET 设置编码。
注意:MySQL命令终止符为分号 ; 。
注意: -> 是换行符标识,不要复制。
删除表:
DROP TABLE table_name ;
插入表:
insert into table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
注意: 使用箭头标记 -> 不是 SQL 语句的一部分,它仅仅表示一个新行,如果一条SQL语句太长,我们可以通过回车键来创建一个新行来编写 SQL 语句,SQL 语句的命令结束符为分号 ;。
在以上实例中,我们并没有提供 runoob_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。
查询表:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
LIMIT 属性来设定返回的记录数;
OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
where 查询:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
使用命名:delete 和 update
操作符:= 、<> 、!=、>、<、 >= 、<=
使用主键作为where子句的查询条件是非常迅速的
BINARY关键字可以设定where子句的字符串区分大小写
例如:SELECT * from table_name WHERE BINARY runoob_author='AAA.CCC';
UPDATE(修改或者更新数据):
update table_name set key=value,key=value [where condition]
DELETE(删除表记录):
delete from table_name [where condition]
如果没有where 那么将会删掉这个表里所有的记录(不要和select * from 混为一谈)
LIKE(我称之为 匹配 ,其中模糊匹配符号:%):
可以使用like代替 =
LIKE中,可使用 and or 指定条件
多个命令下都可以使用
UNION操作符:
UNION操作符是连接两个以上的select语句的结果组合到一个结果集中,多个select语句会删除重复的数据
注意分析:多个select的结果返回,效果是对相同信息的数据进行去重
UNION ALL 与上面的区别就是不去重
ORDER BY(排序):
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
order by 可以设定多个字段排序
ASC 和DESC 是升序和降序的意思,放在order by 条件的最后。
GROUP BY(分组):
分组列上,我们可以使用count、sum、avg 等函数
例如:SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
使用with Rollup
可以实现分组统计数据上在进行相同的统计
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
结果的最后一行是NULL totle_number,我们可以使用 coalesce语法替代NULL
select coalesce(a,b,c)
参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。
以下实例中如果名字为空我们使用总数代替:
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
JOIN(联合查表):
使用场景:select、update、delete 均可使用join进行连表查询
inner join:存在于join左右两个表共同的数据,两表之间一定有一样的信息(inner join 可以直接写成join)
left join:Join左边的表搜索的所有信息
right join:查询join右边的表搜索的所有信息
NULL的处理:
处理NULL数据的三大判定运算符:IS NULL 、IS NOT NULL 、<=> -------- 返回均为true/false
Mysql正则表达:
^ 表示以什么开头: ^a 则找到以a开头的信息(开头放在前头)
$ 表示以什么结尾:a$ 则找出以a结尾的信息(结尾放在后头)
[a,b,c] 表示匹配[ ]中的元素任意一个即可
[^abc] 表示不包含[ ] 任意内容
* 表示匹配前面的式子0+次(包含0次)
+ 表示匹配前面的式子1+次(包含1次)
{n,m} 表示匹配前面的式子n(最少匹配次数)到m(最多匹配次数)次
Alter(修改数据表名/表字段):
ADD :alter table table_name add i int;(顺便定义类型)
DROP:无法操作只有一个字段的数据库:Alter table table_name drop i;
添加位置:first after || first放在最后,表示定义为第一列 ,after a放在列名a之前,表示该定义列放在a之后。
MODIFY:修改字段类型(类型及长度可以一起更改)及名称
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
CHANGE:更改字段名为新的字段名及类型
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
alter修改默认值
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; ---修改默认值
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; ----删除默认值
修改表名:
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
索引:
索引可以大大提高MySQL的检索速度。但同时会降低更新表的速度。(更新表时,不仅要保存数据,还要保存一下索引文件)
建立索引会占用磁盘空间的索引文件。
创建索引:create index index_name on table_name(coll_name(length));
添加索引:alter table table_name add index index_name(coll_name);
直接指定:create table table_name( )
序列(AUTO_INCREMENT):
序列实现非主键的自增形式
创建或修改表结构的时候,使用在自增值的最后;也可以通过alter修改直接添加:
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL, -> origin VARCHAR(30) NOT NULL )engine=innodb auto_increment=100 charset=utf8;
数据重复处理(primary Key 或 unique关键字):
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );
insert ignore into 和 insert into 区别在于忽视重复数据
如果数据库没有数据,直接插入新数据
如果数据库右数据,则跳过这条数据
replace into 插入数据,如果存在primary key和unique相同的记录,则先删除掉,再插入记录
HAVING repetitions > 1; 搜索过程中,设置重复数>1
过滤重复数据:select distinct coll_name from table_name;
删除重复数据:
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
持续更新中.....