- 3.1 Connecting to and Disconnecting from the Server
获取超级用户root的密码
sudo grep 'temporary password' /var/log/mysqld.log
下面用用户名root和密码进行登录
shell> mysql -h host -u user -p
Enter password: ********
连接本地的mysql使用下面简洁的写法：
shell> mysql -u user -p
断开连接：
QUIT
可能碰到的问题：
问题一：
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
解答：
# MySQL服务器基本操作
(linux)
启动：service mysqld start
停止：service mysqld stop
重启：service mysqld restart
查看状态：service mysql status
查看状态：systemctl status mysql.service
(Mac OS X)
mysql start
mysql.server start # 1. 启动
mysql.server stop # 2. 停止
mysql.server restart # 3. 重启
// 未测试
注意在>5.5中使用的是mysql,旧版本使用的是mysqld
问题二：
MYSQL5.7：Access denied for user 'root'@'localhost' (using password:YES)
1.打开MySQL目录下的my.ini文件，在文件的最后添加一行“skip-grant-tables”，保存并关闭文件;
2.重启MySQL服务;
3.通过cmd行进入MySQL的bin目录，输入“mysql -u root -p”(不输入密码)，回车即可进入数据库;
4.执行“use mysql;”，使用mysql数据库;
5.执行update mysql.user set authentication_string=password(‘123456’) where user='root' and Host = 'localhost';（修改root的密码,注意单引号和双引号）;
6.打开MySQL目录下的my.ini文件，删除最后一行的“skip-grant-tables”，保存并关闭文件;
7.mysql> flush privileges;
8.mysql> quit;
9.在命令行中输入“mysql -u root -p 123456”，即可成功连接数据库。
- 3.2 Entering Queries
mysql> SELECT VERSION(), CURRENT_DATE;
mysql 不区分大小写
mysql> SELECT VERSION(), CURRENT_DATE; #OK
mysql> select version(), current_date; # OK
mysql> SeLeCt vErSiOn(), current_DATE; # OK
mysql> SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT VERSION(); SELECT NOW();
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
mysql> SELECT
-> USER()
-> \c
- 3.3 Creating and Using a Database
mysql> SHOW DATABASES;
mysql> USE test
mysql> SELECT DATABASE(); // 显示当前所处的数据库
mysql> CREATE DATABASE menagerie;
连接数据库的时候就选定数据库
shell> mysql -h host -u user -p menagerie
Enter password: ********
mysql> SHOW TABLES;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
SHOW TABLES; //简单信息
mysql> DESCRIBE pet; //单个表的详细信息
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; // 插入数据（从文本文档中插入）
如果文本文档是windows中建立的则需要这样使用
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
不通过文本文档插入
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
# 从文档中检索信息
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
#修改
1.Edit the file `pet.txt` to correct the error, then empty the table and reload it using delete and load data;
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
2.Fix only the erroneous record with an UPDATE statement:
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
例子：
menagerie数据库中的表pet
pet
修改Bowser的年龄
If you do not want to see entire rows from your table, just name the columns in which you are interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns:
To find out who owns pets
DISTINCT 去重复
You can use a WHERE clause to combine row selection with column selection. For example, to get birth dates for dogs and cats only, use this query:
You may have noticed in the preceding examples that the result rows are displayed in no particular order. It is often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause.
You can force a case-sensitive sort for a column by using BINARY
like so: ORDER BY BINARY col_name.The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:
You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:
网友评论