mysql

●安装　　http://downloads.mysql.com/archives/get/file/mysql-5.6.17-winx64.zip

用的这个版本可以.默认下载的8.0几的不好使.

配置方法:https://blog.csdn.net/li_magic/article/details/70186609

完全按照上面博客的配置.成功实现了mysql的安装和系统变量的配置.

但是进入cmd不能直接双击cmd进入.需要用管理员权限来打开cmd之后才能用mysql -u root -p 这个命令进入mysql.

并且按照上面博客配置时候也需要管理员权限来开cmd!!!!

●使用:注意每个语句后面加; 才行

1.net start mysql :启动mysql服务器 mysqladmin -u root -p shutdown:关闭mysql服务器

2.mysql :进入mysql exit :退出mysql

3.创建用户zara,密码zara123 Grant命令即可

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

View Code

4.查看:

SHOW DATABASES:

USE 数据库名 : 选中这个数据库

SHOW TABLES: 会给出上面选择的数据库内部的tables

SHOW COLUMNS FROM 数据表: 从tables看数据的列标

●插入:

mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());

select * from runoob_tbl; 读取数据表runoob_tbl的全部数据

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程'; 用where 来加限制条件.读取数据

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3; 用update来修改数据

DELETE FROM runoob_tbl WHERE runoob_id=3; 删除一条数据

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'; %代表任意字符.选取数据用like

like:

like 匹配/模糊匹配，会与 % 和 _ 结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

合并且去重:column 是country

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

合并且保留相同的:column 是country

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

配合where做union:

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

排序:

SELECT * from runoob_tbl ORDER BY submission_date ASC; 排coluum是submission_date 并且升序ascend

SELECT * from runoob_tbl ORDER BY submission_date DESC; 排coluum是submission_date 并且升序decend

SELECT * FROM runoob_tbl ORDER BY CONVERT(runoob_title using gbk);  拼音排序

统计:

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;       从name中统计把connt结果返回.

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
JOIN 求交
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

统计重复值
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;
过滤重复数据:

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;
输出:

mysql> SELECT * FROM runoob_tbl INTO OUTFILE 'd:/tutorials.txt';
读入:

mysql>LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

SHOW INDEX FROM 数据表:

SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的性能统计信息
drop database RUNOOB; 删除数据库库  不会提示删除确认框
create database RUNOOB;建立数据库
●创建表:

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;