sql常用语句

1.like查询
SELECT 列名称 FROM 表名称

SELECT * FROM bill WHERE bill.bill_id LIKE "%99472%";

2.count

select count(*) from ra_info;

3.DELETE
DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM delivery WHERE delivery.id=1;

4.UPDATE
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE bill SET bill_status = 4 WHERE bill.id = 1331;

5.DISTINCT 和ORDER BY
SELECT DISTINCT 列名称 FROM 表名称

select DISTINCT bill.enterprise FROM bill;
SELECT * FROM bill order by bill.enterprise;

1、查询是否锁表

show OPEN TABLES where In_use > 0;

2、查询进程

show processlist

查询到相对应的进程===然后 kill id

查询查看正在锁的事务

select * from information_schema.INNODB_LOCKS;

查看等待锁的事务

select * from information_schema.INNODB_LOCK_WAITS;

查看具体的死锁信息

show engine innodb status;（根据mysql版本而异）

添加唯一性索引

ALTER TABLE ra_info ADD UNIQUE ( cert_id );

一个用户两个号码,加上_del

update user_info set m_phone=”13731651230_del” where id=11009;

itrusyes

mysql授权登录:

想myuser使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON . TO ‘myuser’@’%’IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON . TO ‘myuser’@’192.168.1.3’IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’IDENTIFIED BY ‘root’ WITH GRANT OPTION;

添加索引

1.添加PRIMARY KEY（主键索引）

ALTER TABLE table_name ADD PRIMARY KEY ( column )

2.添加UNIQUE(唯一索引)

ALTER TABLE table_name ADD UNIQUE ( column )

3.添加INDEX(普通索引)

ALTER TABLE table_name ADD INDEX index_name ( column )

4.添加FULLTEXT(全文索引)

ALTER TABLE table_name ADD FULLTEXT ( column)

5.添加多列索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )