mysql 基础语句

一、用户管理：

1、新建用户：

>CREATE USER name IDENTIFIED BY 'ssapdrow';

2、更改密码：

>SET PASSWORD FOR name=PASSWORD('fdddfd');

3、权限管理

>SHOW GRANTS FOR name;    //查看name用户权限
>GRANT SELECT ON db_name.* TO name;　　　　//给name用户db_name数据库的所有权限
>REVOKE SELECT ON db_name.* TO name;　　　　//GRANT的反操作，去除权限；

二、数据库操作：　

1、查看数据库：

>SHOW DATABASES;

2、创建数据库：

>CREATE DATABASE db_name;　　//db_name为数据库名

3、使用数据库：

>USE db_name;

4、删除数据库：

>DROP DATABASE db_name;

三、创建表：

1、创建表：

>CREATE TABLE table_name(
>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　　　　//id值，无符号、非空、递增——唯一性，可做主键。
>name VARCHAR(60) NOT NULL
>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　　　　//设置默认列值
>PRIMARY KEY(id)
>)ENGINE=InnoDB　　　　//设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索
>DEFAULT charset=utf8;　　//设置默认的编码，防止数据库中文乱码

如果有条件的创建数据表还可以使用

  > CREATE TABLE IF NOT EXISTS tb_name(........

2、复制表：

>CREATE TABLE tb_name2 SELECT * FROM tb_name;

或者部分复制：

>CREATE TABLE tb_name2 SELECT id,name FROM tb_name;

3、创建临时表：

>CREATE TEMPORARY TABLE tb_name(这里和创建普通表一样);

4、查看数据库中可用的表：

>SHOW TABLES;

5、查看表的结构：

>DESCRIBE tb_name;

也可以使用：

>SHOW COLUMNS in tb_name; 　　　　//from也可以

6、删除表：

>DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tb_name[ ,tb_name2.......];

实例：

>DROP TABLE IF EXISTS tb_name;

7、表重命名：

>RENAME TABLE name_old TO name_new;

还可以使用：

>ALTER TABLE name_old RENAME name_new;

四、修改表：

1、更改表结构：

>ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...

实例：

>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
>ALTER TABLE tb_name DROP address;
>ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;

五、插入数据：

1、插入数据：

>INSERT INTO tb_name(id,name,score)VALUES(NULL,'张三',140),(NULL,'张四',178),(NULL,'张五',134);

这里的插入多条数据直接在后边加上逗号，直接写入插入的数据即可；主键id是自增的列，可以不用写。

2、插入检索出来的数据：

>INSERT INTO tb_name(name,score) SELECT name,score FROM tb_name2;

六、更新数据：

1、指定更新数据：

>UPDATE tb_name SET score=189 WHERE id=2;
>UPDATE tablename SET columnName=NewValue [ WHERE condition ]

七、删除数据：

1、删除数据：

>DELETE FROM tb_name WHERE id=3;

八、条件控制：

1、WHERE 语句：

>SELECT * FROM tb_name WHERE id=3;

2、HAVING 语句：

>SELECT * FROM tb_name GROUP BY score HAVING count(*)>2

3、相关条件控制符：

=、>、<、<>、IN(1,2,3......)、BETWEEN a AND b、NOT
AND 、OR
Like()用法中      %  为匹配任意、  _  匹配一个字符（可以是汉字）
IS NULL 空值检测

九、MySQL的正则表达式：

1、Mysql支持REGEXP的正则表达式：

>SELECT * FROM tb_name WHERE name REGEXP '^[A-D]'   //找出以A-D 为开头的name

2、特殊字符需要转义。

十、MySQL的一些函数：

1、字符串链接——CONCAT()

>SELECT CONCAT(name,'=>',score) FROM tb_name

2、数学函数：

AVG、SUM、MAX、MIN、COUNT；

3、文本处理函数：

TRIM、LOCATE、UPPER、LOWER、SUBSTRING

4、运算符：

+、-、*、

5、时间函数：

DATE()、CURTIME()、DAY()、YEAR()、NOW().....

十一、分组查询：

1、分组查询可以按照指定的列进行分组：

>SELECT COUNT(*) FROM tb_name GROUP BY score HAVING COUNT(*)>1;

2、条件使用Having；

3、ORDER BY 排序：

ORDER BY DESC|ASC　　　　=>按数据的降序和升序排列

十二、UNION规则——可以执行两个语句（可以去除重复行）

十三、全文检索——MATCH和AGAINST

1、SELECT MATCH(note_text)AGAINST('PICASO') FROM tb_name;
2、InnoDB引擎不支持全文检索，MyISAM可以；

十四、视图

1、创建视图

>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;

2、视图的特殊作用：

a、简化表之间的联结（把联结写在select中）；

b、重新格式化输出检索的数据（TRIM，CONCAT等函数）；

c、过滤不想要的数据（select部分）

d、使用视图计算字段值，如汇总这样的值。

十五、使用存储过程：

个人理解，存储过程就是一个自定义函数，有局部变量参数，可传入参数，可以返回值，不过这语法够呆滞的~~~

1、创建存储过程：

>CREATE PROCEDURE pro(
>IN num INT,OUT total INT)
>BEGIN
>SELECT SUM(score) INTO total FROM tb_name WHERE id=num;
>END;

***这里的  IN (传递一个值给存储过程)，OUT（从存储过程传出一个值），INOUT（对存储过程传入、传出），INTO（保存变量）

2、调用存储过程：

>CALL pro(13,@total)　　　　　　//这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错
>SELECT @total　　　　　　　　　//这里就可以看到结果了；

3、存储过程的其他操作：

>SHOW PROCEDURE STATUS;　　　　　　//显示当期的存储过程
>DROP PROCEDURE pro;　　　　　　　　　//删除指定存储过程

十六、使用游标：

对这个理解不是很懂，朋友多多指点哦~~~

1、游标的操作

>CREATE PROCEDURE pro()
>BEGIN
>DECLARE ordername CURSOR FOR
>SELECT order_num FROM orders;
>END;
>OPEN ordername;　　　　//打开游标
>CLOSE ordername;　　　　//关闭游标

十七、触发器：

触发器是指在进行某项指定操作时，触发触发器内指定的操作；

1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持

2、创建触发器：

>CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;
>INSERT语句，触发语句，返回一个值

3、删除触发器

>DROP TRIGGER trig;

十八、语法整理：

1、ALTER TABLE（修改表）

ALTER TABLE table_name

(　　ADD　　　　column　　datatype  　　[ NULL | NOT NULL ]　　[ CONSTRAINTS ]
CHANGE　　column 　　datatype 　　COLUMNS　　[ NULL | NOT NULL ]　　 [ CONSTRAINTS ]
DROP　　　 column，
。。。。
)

2、COMMIT(处理事务)

>COMMIT;

3、CREATE INDEX(在一个或多个列上创建索引)

CREATE INDEX index_name ON tb_name (column [ ASC | DESC ] , .......);

4、CREATE PROCEDURE (创建存储过程)

CREATE PROCEDURE pro([ parameters ])
BEGIN
........
END

5、CREATE TABLE(创建表)

CREATE TABLE 表名(
column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,
column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,
.......
PRIMARY KEY( column_name )
)ENGINE=[  InnoDB | MyiSAM ]DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

6、CREATE USER(创建用户)

CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] 'pass_word' ];

7、CREATE VIEW （在一个或多个表上创建视图）

CREATE [ OR REPLACE ] VIEW view_name AS SELECT。。。。。。

8、DELETE (从表中删除一行或多行)

DELETE FROM table_name [WHERE ......]

9、DROP(永久删除数据库及对象，如视图、索引等)

DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW  name

10、INSERT （给表添加行）

INSERT INTO 表名（列名1,...... )  VALUES(value1,............);

使用SELECT值插入：

INSERT INTO tb_name [ ( columns,...... ) ]
SELECT columns , .......   FROM tb_name [ WHERE ...... ] ;

11、ROLLBACK（撤销一个事务处理块）

ROLLBACK [  TO  savapointname  ];

12、SAVEPOINT(为ROLLBACK设置保留点)

SAVEPOINT sp1;

13、SELECT (检索数据，显示信息) // []代表可选使用，可连用；| 代表或关系 只选一个用，以下相同

SELECT 列名,.....FROM 表名 [ WHERE ]   [ UNION ]    [ RROUP BY ]   [ HAVING ]   [ ORDER BY ]

14、START TRANSACTION (一个新的事务处理块的开始)

START TRANSACTION

15、UPDATE(更新一个表中的一行或多行)

UPDATE 表名 SET 列名1=value1, 列名2=vaule2 where xxx=xxx

以下是自己的操作练习：

USE test_db;
CREATE TABLE test1(
id INT PRIMARY KEY NOT NULL,
NAME VARCHAR(100)
)
show TABLES;
SHOW COLUMNS FROM test1;


create table test_backup LIKE test1;
create table test_backup1 as select id,name FROM test1;


drop table test_backup1;
ALTER TABLE test1 add COLUMN text VARCHAR(200);
ALTER TABLE test1 CHANGE text TEXT1 VARCHAR(100);
ALTER TABLE test1 drop TEXT1;
ALTER TABLE test1 drop PRIMARY key(id);#?
ALTER TABLE test1 ADD PRIMARY KEY(id);#?
CREATE index id1 ON test1(id);
drop INDEX id1; #?


DROP VIEW viewgx AS SELECT id FROM test1;
drop VIEW viewname;


INSERT into test1(id,NAME) VALUES(1,'gongxue');
INSERT into test1(id,NAME) VALUES(2,'gx');
SELECT * FROM test1 WHERE id = '1';
delete FROM test1 where id = '2';
update test1 SET NAME='gdd' WHERE id = '2';
SELECT * FROM test1 WHERE name like '%g%'
SELECT * FROM test1 ORDER BY id DESC
select count as totalcount FROM test1
SELECT sum(id) as sumvalue from test1
SELECT avg(id) as avgvalue from test1
SELECT max(id) as maxd FROM test1
SELECT min(id) as mixd FROM test1


SELECT a.a,a.b,a.c,a.d,a.f FROM a LEFT JOIN b ON a.a = b.c #包括左表所有行
SELECT a.a,a.b,a.c,a.d,a.f FROM a right join b ON a.a = b.c
SELECT a.a,a.b,a.c,a.d,a.f FROM a FULL JOIN b ON a.a = b.c


SELECT count(name) from test1 GROUP BY name #只能得到组相关的信息






select a,b,c from a where a in(select d from b)
SELECT a,b,c from a WHERE a in(1,2,3)


SELECT top () * into b FROM a #复制表结构


SELECT * from table1 where time between time1 and time2
select * from table1 where time a not between value1 and value2
SELECT * from test1 where a no in('值1','值2','值3')


#两张关联表，删除主表中已经在附表中没有的信息
delete from test1 where not EXISTS (select * from test2 where test1.field1 = test2.filed1)








#删除重复记录
delete from test1 where id not in (select max(id) from test2 GROUP BY col1,col2)
select DISTINCT * into temp from tablename
delete from tablname
insert into tablename select * from temp






DROP TABLE user;
create table user(
userid INT PRIMARY KEY  ,
username varchar(100),
userage INT,
userthing varchar(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8  #设置编码utf8便于插入中文字符不是乱码
drop table colleagues;
create table colleagues(
collid  INT ,
collname varchar(100),
collsex VARCHAR(10),
collposition varchar(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8


insert INTO user(userid,username,userage,userthing) VALUES(1,'yaoshuting',21,'PKpepople');
insert INTO user(userid,username,userage,userthing) VALUES(2,'张珊珊',21,'yfriend');
insert INTO user(userid,username,userage,userthing) VALUES(3,'李潇潇',24,'yfriend');
insert INTO user(userid,username,userage,userthing) VALUES(4,'李小子',25,'yfriend');


insert into colleagues(collid,collname,collsex,collposition) values(1,'屈姐',30,'行政人员');
insert into colleagues(collid,collname,collsex,collposition) values(1,'yaoshuting',30,'人事专员');
insert into colleagues(collid,collname,collsex,collposition) values(1,'曹姐',21,'技术人员');


#内连接：以ON后面的为条件进行表结构及数据的连接，显示出左右两个表都有的数据
select * FROM user INNER JOIN colleagues ON user.username = colleagues.collname
#左连接：以ON后面的条件，将表连接，显示出左边左表所有行数据，右表就显示与左表相同的数据
SELECT * from user LEFT JOIN colleagues ON user.username = colleagues.collname
#右连接：以ON后面的条件，将表连接，显示出右边左表所有行数据，左表就显示与左表相同的数据
select * from user RIGHT JOIN colleagues ON user.username = colleagues.collname


select * from user ORDER BY userid limit 5
SELECT COUNT(*) FROM user GROUP BY userid HAVING count(*) >= 1;