mg4377娱乐娱城官网_mg4377娱乐手机版_www.mg4377.com

当前位置: mg4377娱乐娱城官网 > mg > 正文

mysql常用语句汇总,mysql常用的部分话语

时间:2019-06-17 06:36来源:mg
mysqld 常规MySQL服务器 mysqld-opt 优化mysql服务器,提供一些功能能够开采更加好的职能 mysqld-max 与mysqld同样,但足以支撑立异,更具实验性质的遵循(更不平静) 呈现全部的数据库 创建表 mysql语

mysqld 常规MySQL服务器
mysqld-opt 优化mysql服务器,提供一些功能能够开采更加好的职能
mysqld-max 与mysqld同样,但足以支撑立异,更具实验性质的遵循(更不平静)

  • 呈现全部的数据库

创建表

mysql语句汇总

最近和校友接的连串终于完工了,项目首就算做报表,涉及到了好多的sql语句,所以借此总括下。

安装mysql
参见自带的INSTALL-SOU本田UR-VCE文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8 ?with-extra-charsets=utf8,gb2312,utf8

create TABLE emp(
id INT(10) unsigned NOT NULL AUTO_INCREMENT,
emp_no VARCHAR(10) NOT NULL,
emp_name VARCHAR(50) NOT NULL,
emp_sex CHAR(1) default null,
emp_salary DOUBLE(10,2) default NULL,
birthday Date default null,
entry_date TIMESTAMP(8),
PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8

一、基础

启动/关闭mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown

show databases;

安顿数据
insert into emp(emp_no,emp_name,emp_sex,emp_salary,birthday)values('1001','zhansan','M',5000.345555,'2004-09-23');

1.数据库相关命令

a>.创造数据库

CREATE DATABASE 数据库名

b>.创造数据库并设置暗中认可字符集

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;

c>.删除数据库

DROP DATABASE 数据库名

修改root口令
$ mysqladmin -u root -p password ‘新密码’

  • 动用某三个数据库

增进表字段number在cityId后
alter table emp ADD number INT(10) after emp_name;

2.数据表相关命令

a>.删除表

DROP TABLE 表名;

b>.新建表

DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名 (
`id`  INT NOT NULL AUTO_INCREMENT,
`username`  VARCHAR(60) NOT NULL DEFAULT '' COMMENT '登录名',
`password`  CHAR(32) NOT NULL DEFAULT '' COMMENT '登录密码',
 PRIMARY KEY (`id`),   -- 主键
 INDEX (`username`)    -- 索引
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='新建表';

c>.修改表名

ALTER TABLE 表名1 RENAME TO 表名2; 

mysql常用语句汇总,mysql常用的部分话语。d>.添加列

ALTER TABLE 表名 ADD COLUMN 列名 列类型(如:VARCHAR(30)); 

e>.删除列

ALTER TABLE 表名 DROP COLUMN 列名; 

f>.几条轻松的sql语句

选择:SELECT * FROM 表名 WHERE 范围;
插入:INSERT INTO 表名(字段名1,字段名2) VALUES(字段值1,字段值2);
删除:DELETE FROM 表名 WHERE 范围;
更新:UPDATE 表名 SET 字段名=字段值 WHERE 范围;
查找:SELECT * FROM 表名 WHERE 字段名 LIKE ’% 字段值 %’ ;
排序:SELECT * FROM 表名 ORDER BY 字段名1,字段名2 [DESC];
总数:SELECT COUNT AS totalcount FROM 表名;
求和:SELECT SUM(字段值) AS sumvalue FROM 表名;
平均:SELECT AVG(字段名) AS avgvalue FROM 表名;
最大:SELECT MAX(字段名) AS maxvalue FROM 表名;
最小:SELECT MIN(字段名) AS minvalue FROM 表名;

g>.赋予数据库用户权限(每一种数据库都创立单独的用户)

CREATE USER '项目用户'@'localhost' IDENTIFIED BY '项目用户密码';  
GRANT ALL PRIVILEGES ON 数据库名称.* TO 项目用户@'localhost' ; 

或者

GRANT ALL PRIVILEGES ON 数据库名称.* TO 用户名@'%' IDENTIFIED BY '用户密码';(用’%'表示从任何地址连接)

h>.加多主键

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

i>.创设索引

CREATE INDEX 字段名 ON 表名;

j>.删除索引

DROP INDEX 字段名;

k>.创设视图

CREATE VIEW 视图名 AS
SELECT 语句;

l>.删除视图

DROP VIEW 视图名;

m>.联合查询(那块有相当的多剧情,笔者这边只提议基本的)

结果集列联合:JOIN ,这里本人常用的是LEFT JOIN ,格式如下:

SELECT a.a, a.b, b.c, b.d FROM a LEFT OUT JOIN b ON a.a = b.c;

结果集行联合:UNION或许UNION ALL,格式如下(注意列的称谓和个数一定要一致):

SELECT a.a as col1, a.b as col2 from a
UNION ALL
SELECT b.c as col1, b.d as col2 from b;

n>.分组:GROUP BY 一般与count,sum,max,min,avg联合利用

SELECT SUM(字段名1) FROM 表名 GROUP BY 字段名2;

o>.前10条记录

SELECT * FROM 表名 LIMIT 0,9;(mysql没有top语法,limit一般常用来分页)

p>.排序(desc降序;asc升序;暗许是升序)

SELECT * FROM 表名 ORDER BY 列名 DESC;

查看服务器状态
$ path/mysqladmin version -p

修改表的字段名字定义
alter table emp change emp_name  emp_namedd VARCHAR(55);

二、进阶

a>.去重,一般常用 DISTINCT

SELECT DISTINCT * FROM 表名;
SELECT DISTINCT(列名) FROM 表名;

b>.复制表

CREATE TABLE 表名2 AS   
(   
  SELECT * FROM 表名1   
)   

c>.查询剖析器:在sql前边加上EXPLAIN大概DESC,查看该语句执市价况

EXPLAIN  SELECT * FROM 表名;

连接远端mysql服务器
$ path/mysql -u 用户名 -p #老是本机
$ path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器

use mysql; // 使用数据库名为mysql的数据库

修改表的字段的定义地方
alter table emp modify emp_name VARCHAR(20)  default 'zhangsan';

三、贴一下做报表时写的某些黑心的sql

1.基于查询条件查询出符合条件的项目编号

SELECT DISTINCT(xp.Id)
FROM xmgl_finance_plan xfp LEFT JOIN xmgl_project xp ON xfp.proId = xp.Id WHERE 1=1
AND xp.proCategory IN ('ITER973')
AND xp.Id IN(0,1,2);

2.-- 查询项目安排表里对应的全部的预算科目对应的协商金额

SELECT
  t.Pid,
  xsb2.subjectName,
  SUM(t.planTotalAmount)    planTotalAmount,
  SUM(t.planCurrentAmount)    planCurrentAmount,
  SUM(t.total_amount)    total_amount,
  SUM(t.payedMoney)    payedMoney,
  SUM(t.finalPayment)    finalPayment,
  SUM(t.CurYearPayedMoney)    CurYearPayedMoney
FROM (SELECT
        xsb.Id,
        xsb.subjectName,
        xsb.Pid,
        SUM(xfp.planTotalAmount)    planTotalAmount,
        SUM(xfp.planCurrentAmount)    planCurrentAmount,
        SUM(xfp.total_amount)    total_amount,
        SUM(xfp.payedMoney)    payedMoney,
        SUM(xfp.finalPayment)    finalPayment,
        SUM(xfp.CurYearPayedMoney)    CurYearPayedMoney
      FROM xmgl_subject_budget xsb
        LEFT JOIN xmgl_project xp
          ON xsb.type = xp.subjectType
        LEFT JOIN xmgl_finance_plan xfp
          ON xsb.Id = xfp.subjectId
      WHERE xp.Id = 1
      GROUP BY xfp.subjectId,xsb.Id
      ORDER BY xsb.Id) AS t
  LEFT JOIN xmgl_subject_budget xsb2
    ON t.Pid = xsb2.Id
WHERE t.Pid <> 0
GROUP BY t.Pid;

3.-- 查询种种部门在特定科目下的决定额度及花费音信(优化)

SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney
,xcs2.amount-SUM(xfa.amount) leftAmount
FROM xmgl_control_subject xcs1
LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3
LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.Id
LEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')
GROUP BY xcs1.dept,xcs1.detailId;

4.近乎于QQ空间的音信列表

-- 赞了我
SELECT tf.id,1 AS typeId,c.avatar AS avatar, c.name,ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent,NULL AS replyContent, tf.create_time AS createTime,tf.read AS isRead
FROM trends_fork tf LEFT JOIN cust_trends ct ON tf.trends_id = ct.id LEFT JOIN customer c ON c.id = tf.customer_id
WHERE tf.master_id = 1
UNION ALL
-- 给我留言
SELECT cb.id,2 AS typeId,c.avatar AS avatar,c.name, 3 AS catalog,cb.id AS messageId,NULL AS trendsContent,cb.content AS replyContent,cb.create_time AS createTime,cb.read AS isRead
FROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1
UNION ALL
-- 评论我的动态
SELECT  tr.id,3 AS typeId, c.avatar AS avatar, c.name, ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent, tr.content AS replyContent,tr.create_time AS createTime,tr.read AS isRead
FROM trends_reply tr LEFT JOIN cust_trends ct ON tr.trends_id = ct.id LEFT JOIN customer c ON c.id = tr.customer_id
WHERE tr.master_id = 1
UNION ALL
-- 回复了我评论的消息(包括直接回复我的评论、在我的动态下评论别人的回复)
SELECT tra.id,4 AS typeId,c.avatar AS avatar, c.name, ct.catalog AS catalog, tra.trends_id AS messageId,ct.content AS trendsContent, tra.content AS replyContent, tra.create_time AS createTime,tra.read AS isRead
FROM trends_replyat tra LEFT JOIN  trends_reply tr ON tra.reply_id = tr.id LEFT JOIN customer c ON c.id = tra.customer_id LEFT JOIN cust_trends ct ON ct.id = tr.trends_id
WHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)
UNION ALL
-- 留言板中回复我的消息
SELECT cbr.id,5 AS typeId,c.avatar AS avatar, c.name, 3 AS catalog, cb.id AS messageId,cb.content AS trendsContent,cbr.content AS replyContent, cbr.create_time AS createTime,cbr.read AS isRead
FROM cust_board_reply cbr LEFT JOIN cust_board cb ON cbr.board_id = cb.id LEFT JOIN customer c ON cbr.customer_id = c.id
WHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1 ) OR (cbr.master_id = 1 AND cbr.customer_id != 1) ORDER BY  createTime DESC
LIMIT 2,10

方今和同班接的连串终于告竣了,项目重点是做报表,涉及到了成都百货上千的sql语句,所以借此计算下。 一、基础 1.数据库相关...

创建/删除 数据库或表
$ mysqladmin -u root -p create xxx
mysql> create database 数据库名;
mysql> create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql> drop database [if exists] 数据库名
mysql> create table 表名;
mysql> drop table 表名;

  • 安顿语句

删除表的字段
alter table emp drop emp_name;

查看数据库和查看数据库下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #查阅具体表结构消息
mysql> SHOW CREATE DATABASE db_name #展现创造db_name库的讲话
mysql> SHOW CREATE TABLE tbl_name #显示创设tbl_name表的口舌

删除表
drop table emp;

创制用户
mysql> grant select,insert,update,delete,alter on mydb.* to [email protected] identified by “abc”;
mysql> grant all privileges on *.* to [email protected]”%” identified by “abc”;
mysql> flush privileges;

insert into table_name (platform,employee_id) values ('employee',85);

清空表数据
truncate table emp;

用户管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111
mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #剔除用户帐号
mysql> SHOW GRANTS FOR user1; #突显创设user1用户的grant语句


获得表结构
desc emp;

mysql数据库的备份和回复
$ mysqldump -uuser -ppassword -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #导入表
$ mysqldump -u 用户名 -p 数据库名 表名> 导出的文书名 ##导出单独的表

未完待续。。。

本条是用来对表举办优化。这几个命令能够将表中的零碎实行合并,并且能够消除由于删除也许更新形成的长空的浪费。这一个命令只对MyISAM BDB,InnoDB起效果。
OPTIMIZE TABLE emp;

导出二个数据库结构
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc >wcnc_db.sql
-d 未有数据 ?add-drop-table 在各样create语句从前扩大贰个drop table

创办进度:向表emp中插入10000条数据
create PROCEDURE createEmpData()
BEGIN
set @x=1;
loop1:LOOP
set @x = @x 1;
  if @x = 10000 THEN
LEAVE loop1;
end IF;
insert into emp(emp_no,emp_name,emp_sex,emp_salary,birthday)values('1001','zhansan','M',5000.34,'2004-09-23');
end loop loop1;
END

编辑:mg 本文来源:mysql常用语句汇总,mysql常用的部分话语

关键词: MySQL